MySQL 基础

1. 数据库编码

• utf8_bin 将字符串中的每一个字符用二进制数据存储，区分大小写（bin -> binary）
• utf8_genera_ci 大小写不敏感 (ci -> case insensitive)
• utf8_general_cs 大小写敏感(cs -> case sensitive)

character_set_server=utf8 -- 字符集
collation_server=utf8_general_ci -- 排序规则

2. SQL 执行顺序

2.1 常规顺序

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...
GROUP BY column_name(s)
HAVING condition
ORDER BY column1, column2, ... ASC|DESC
LIMIT number

执行顺序：
from -> where -> group by -> having -> select -> order by -> limit
(1) 首先执行 FROM
(2) where

2.2 复杂顺序

(8) SELECT (9) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) on <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

执行顺序：
from -> on -> join -> where -> group by -> sum、count、max、avg -> having -> select -> distinct -> order by -> limit

3. 关键字

3.1 group by

GROUP BY 可以和聚合函数 (COUNT(), MAX(), MIN(), SUM(), AVG()) 一起使用

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s)

3.2 order by

-- 按 columnA, columnB 的顺序排序
order by columnA, columnB

-- ASC / DESC
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC

3.3 limit 限制条目

• limit [offset,] N
  • offset: 偏移量
  • N：取出条目
• limit n 等价于 limit 0, n 比如 limit 5;
• 补充：MySQL的 limit 用法和分页查询的性能分析及优化

-- 检索记录行 6 ~ 15
select * from table limit 5, 10;

-- 检索记录行 96 ~ last, 指定 N 为 -1 
select * from table limit 95, -1;

3.4 distinct

• select distinct name from user

3.5 like

方式一：sql 拼接

String sql = "select * from tb_user where name like '%" + param + "%'";

方式二：占位符方式

Query query = session.createQuery("from tb_user where name like ?");
query.setParameter(0, "%" + param + "%");

4. 结构化查询语言（SQL）

• 数据定义语言DDL
• 数据操纵语言DML
• 数据控制语言DCL
• 事务控制语言TCL

4.1 DDL（Data Definition Languages）

• CREATE
• ALTER
• DROP
• TRUNCATE

4.2 DML（Data Manipulation Language）

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• EXPLAIN PLAN
• LOCK TABLE

4.3 DCL（Data Control Language）

• GRANT 授权
• REVOKE 取消授权

4.4 TCL（Transaction Control Language）

• SAVEPOINT 设置保存点
• ROLLBACK 回滚
• SET TRANSACTION 设置事务