mysql基础篇

1. SQL概述

SQL分类：

• DDL（数据定义语言）：CREATE、DROP、ALTER —— 操作数据库和表结构
• DML（数据操作语言）：INSERT、DELETE、UPDATE、SELECT —— 操作数据
• DCL（数据控制语言）：GRANT、REVOKE —— 权限管理
• DQL（数据查询语言）：SELECT（从DML中单独拎出来）
• TCL（事务控制语言）：COMMIT、ROLLBACK、SAVEPOINT

基本规则：

• 关键字大写，表名/字段名小写
• 字符串和日期用单引号，别名用双引号
• 反引号 ` 用于保留字冲突的字段名/表名

2. 基本SELECT语句

SELECT 字段1, 字段2 FROM 表名;    -- 查指定列
SELECT * FROM 表名;               -- 查全部列（生产环境不推荐）
SELECT 字段 AS 别名 FROM 表名;    -- 起别名，AS可省略
SELECT DISTINCT 字段 FROM 表名;   -- 去重（DISTINCT必须放在最前面，对所有列组合去重）
SELECT 字段 FROM 表名 WHERE 条件; -- 过滤

• NULL参与运算结果全为NULL，用 IFNULL(字段, 默认值) 处理
• DESCRIBE 表名; 或 DESC 表名; 查看表结构
• SELECT '常数' AS 列名 可以查询常数列

3. 运算符

算术运算符

+ - * /(或DIV) %(或MOD)

• MySQL中 + 只做加法，字符串拼接用 CONCAT()
• 除以0结果为NULL

比较运算符

逻辑运算符

NOT(!) AND(&&) OR(||) XOR（异或）

• AND优先级高于OR

4. 排序与分页

-- 排序
SELECT ... FROM ... ORDER BY 字段 ASC|DESC;  -- 默认ASC升序
SELECT ... FROM ... ORDER BY 字段1 ASC, 字段2 DESC;  -- 多列排序，优先级从左到右
SELECT ... FROM ... ORDER BY 别名;           -- 可以用别名排序

-- 分页
SELECT ... FROM ... LIMIT 起始行, 条数;      -- LIMIT offset, count
SELECT ... FROM ... LIMIT 条数 OFFSET 起始行; -- MySQL 8.0语法
-- 第n页：LIMIT (n-1)*pageSize, pageSize

5. 多表查询（JOIN）

内连接：inner join 简写为join，查询两个表之间符合条件的结果集

外连接：outer join，分为 left outer join 和 right outer join，简写为 left join 或 right join

• 查询两个表之间，左表或右表所有结果集，以及另外表的结果集，如果另外表不符合连接条件，则字段为null

-- 隐式内连接（等值连接）
SELECT * FROM t1, t2 WHERE t1.id = t2.id

-- 显式内连接
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id

-- 左外连接（左表全保留，右表不匹配填NULL）
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id

-- 自连接
SELECT * FROM emp e JOIN emp m ON e.manager_id = m.id

-- 多表连接
SELECT * FROM t1 JOIN t2 ON t1.tid = t2.tid JOIN t3 ON t1.tname = t3.tname

-- 复合连接（多个条件）
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t1.type = t2.type

-- USING（连接字段同名时简写）
t1 JOIN t2 USING(id)  -- 等价于 t1 JOIN t2 ON t1.id = t2.id
t1 JOIN t2 USING(id, name)

-- 自然连接（自动匹配同名列，基本不用）
SELECT * FROM t1 NATURAL JOIN t2

-- 交叉连接（笛卡尔积）
t1 CROSS JOIN t2   -- 显式
FROM t1, t2        -- 隐式

UNION联合

合并多段SELECT查询（列数和类型必须一致）

• UNION：去重合并（效率低）
• UNION ALL：不去重合并（推荐）

SELECT id, name FROM t1
UNION ALL
SELECT id, name FROM t2;

6. 常用单行函数

字符串函数

数值函数

日期时间函数

常用格式符：%Y四位年 %m两位月 %d两位日 %H24小时 %i分钟 %s秒

流程控制函数

IF(条件, 真值, 假值)

IFNULL(字段, 默认值)  -- 字段为NULL时返回默认值

CASE 字段 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ELSE 默认结果 END

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 默认结果 END

7. 聚合函数与GROUP BY

聚合函数

• 聚合函数不能嵌套，如 AVG(SUM(...))

GROUP BY分组

SELECT 分组字段, 聚合函数(...) FROM 表 GROUP BY 分组字段; -- 只能出现分组字段和聚合函数
SELECT 字段1, 字段2, SUM(...) FROM 表 GROUP BY 字段1, 字段2;  -- 多字段分组
SELECT ... GROUP BY 字段 WITH ROLLUP;  -- 追加汇总行（与ORDER BY互斥）

-- group by 自带排序，若不需要排序可以置顶order by null ，提升性能

• SELECT中非聚合函数的字段，必须在GROUP BY中出现

HAVING过滤分组

SELECT 分组字段, COUNT(*) cnt FROM 表 GROUP BY 分组字段 HAVING cnt > 1; -- 对group by的结果过滤

• WHERE：分组前过滤行，不能用聚合函数，先过滤再连接（效率高）
• HAVING：分组后过滤组，可以用聚合函数，先连接再过滤（效率低）
• 两者可以同时使用，WHERE在前，HAVING在后

ORDER BY 排序

-- 无索引情况下走filesort 性能差，所以要尽量对order by 的列加联合索引；且顺序必须一致

SELECT完整执行顺序

FROM → ON → JOIN → WHERE → GROUP BY → 聚合函数 → WITH → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

8. 子查询

子查询（内查询）在主查询之前执行，结果被主查询使用。子查询要放在括号内，放在比较条件右侧。

按返回结果分类

• 单行子查询：返回一行，用 = > < >= <= <> 比较
• 多行子查询：返回多行，用 IN / ANY / ALL / SOME 比较

-- 单行
SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name='Abel');

-- 多行 IN
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id=1700);

-- ANY（满足任意一个）
SELECT * FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE department_id=90);

-- ALL（满足所有）
SELECT * FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE department_id=90);

按是否关联分类

• 不相关子查询：子查询独立执行一次，结果给主查询用
• 相关子查询：子查询依赖主查询，每执行主查询一行，子查询都重新执行

-- 相关子查询
SELECT e.last_name, e.salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- FROM中子查询（必须起别名）
SELECT e.last_name, e.salary FROM employees e,
  (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) d
WHERE e.department_id = d.department_id AND e.salary > d.avg_sal;

-- EXISTS / NOT EXISTS
SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);
SELECT * FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

相关更新与删除

-- 相关更新
UPDATE employees e SET e.department_name = (SELECT d.department_name FROM departments d WHERE e.department_id = d.department_id);

-- 相关删除
DELETE FROM employees e WHERE employee_id IN (SELECT employee_id FROM emp_history WHERE employee_id = e.employee_id);

注意： 自连接通常比子查询效率更高，优先使用自连接。

9. 创建和管理表（DDL）

数据库操作

CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET 字符集];
DROP DATABASE [IF EXISTS] 库名;
SHOW DATABASES;
USE 库名;
SHOW TABLES [FROM 库名];

创建表

CREATE TABLE [IF NOT EXISTS] 表名 (
  字段1 数据类型 [约束] [默认值],
  字段2 数据类型 [约束] [默认值],
  [表级约束]
);

-- 通过查询建表
CREATE TABLE emp_copy AS SELECT * FROM employees;
CREATE TABLE emp_empty AS SELECT * FROM employees WHERE 1=2;  -- 只复制结构

修改表

ALTER TABLE 表名 ADD 字段 类型 [FIRST|AFTER 字段];        -- 加列
ALTER TABLE 表名 MODIFY 字段 新类型 [DEFAULT 值];          -- 改类型/默认值
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新类型;          -- 改名
ALTER TABLE 表名 DROP 字段名;                              -- 删列

删除与清空

DROP TABLE [IF EXISTS] 表名;            -- 删表（结构和数据都没了，不能回滚）
TRUNCATE TABLE 表名;                     -- 清空数据，释放空间（不能回滚）
DELETE FROM 表名 [WHERE 条件];           -- 删数据（可以回滚）

TRUNCATE vs DELETE：

• TRUNCATE 速度快，不触发触发器，不能回滚
• DELETE 可以带WHERE，可以回滚，触发触发器

重命名表

RENAME TABLE 旧表名 TO 新表名;
ALTER TABLE 旧表名 RENAME TO 新表名;

MySQL8 DDL原子化

MySQL 8.0起，DDL操作支持事务完整性：要么全部成功，要么全部回滚。例如 DROP TABLE t1, t2 中如果t2不存在，t1也不会被删除（5.7中t1会被删除）。

10. 数据处理（增删改）

插入

-- 全字段插入
INSERT INTO 表名 VALUES (v1, v2, ...);

-- 指定字段插入
INSERT INTO 表名 (字段1, 字段2) VALUES (v1, v2);

-- 批量插入（效率高于多条INSERT）
INSERT INTO 表名 (字段1, 字段2) VALUES (v1,v2), (v3,v4), (v5,v6);

-- 将查询结果插入
INSERT INTO 目标表 (字段...) SELECT ... FROM 源表 WHERE ...;

更新

UPDATE 表名 SET 字段1=值1, 字段2=值2 [WHERE 条件];
-- 不加WHERE会更新所有行
-- 回滚需要先 SET AUTOCOMMIT = FALSE;

删除

DELETE FROM 表名 [WHERE 条件];
-- 不加WHERE会删除所有行
-- 有外键关联的父表数据不能直接删除

MySQL8 计算列

CREATE TABLE tb (
  id INT, a INT, b INT,
  c INT GENERATED ALWAYS AS (a + b) VIRTUAL  -- c的值自动由a+b计算
);

11. 数据类型要点

整数类型

• UNSIGNED：无符号，不能存负数，正数范围翻倍
• ZEROFILL：不够位数前补0，自动变UNSIGNED
• 选择原则：能用小类型就不用大类型，节省空间

小数类型

• M是总位数，D是小数位数
• 金额/货币一律用DECIMAL，不要用FLOAT/DOUBLE

日期时间

• DATETIME范围更大，TIMESTAMP有2038年问题但占空间小

字符串类型

• CHAR vs VARCHAR：CHAR定长查询快但浪费空间，VARCHAR变长省空间但需要额外1-2字节存长度。短且固定用CHAR，长度变化大用VARCHAR。
• VARCHAR最大65535字节（受行大小限制），实际UTF8下约21844字符。

其他

• BINARY/VARBINARY：二进制字符串
• BLOB：二进制大对象（图片、文件等）
• JSON：存储JSON数据，用 -> 或 ->> 访问字段

12. 约束

约束分类

创建表时加约束

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  gender CHAR(1) DEFAULT '男',
  age INT CHECK (age >= 0 AND age <= 150),
  dept_id INT,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES department(id)
    ON DELETE CASCADE    -- 父表删除时级联删除
    ON UPDATE CASCADE    -- 父表更新时级联更新
);

修改表时加约束

ALTER TABLE 表名 MODIFY 字段 类型 NOT NULL;                    -- 加非空
ALTER TABLE 表名 ADD UNIQUE(字段);                             -- 加唯一
ALTER TABLE 表名 ADD PRIMARY KEY(字段);                        -- 加主键
ALTER TABLE 表名 ADD FOREIGN KEY(字段) REFERENCES 表(字段);    -- 加外键
ALTER TABLE 表名 ADD CHECK(条件);                              -- 加检查
ALTER TABLE 表名 MODIFY 字段 类型 DEFAULT 值;                  -- 加默认值

删除约束

ALTER TABLE 表名 MODIFY 字段 类型 [NULL];                     -- 去非空
ALTER TABLE 表名 DROP INDEX 索引名;                            -- 去唯一
ALTER TABLE 表名 DROP PRIMARY KEY;                             -- 去主键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;                      -- 去外键
ALTER TABLE 表名 ALTER 字段 DROP DEFAULT;                      -- 去默认值

外键的级联操作

阿里巴巴规范建议： 不使用外键，在应用层控制数据完整性。

AUTO_INCREMENT

• 必须定义在KEY上（主键或唯一键）
• MySQL 8.0起，自增值持久化存储到redo log，重启不会丢失
• 不连续跳号是正常现象（删除、回滚等）

13. 视图

视图是一张虚拟表，本身不存数据，基于SELECT查询定义。

-- 创建
CREATE [OR REPLACE] VIEW 视图名 [(列名列表)] AS SELECT ...;

-- 查看
SHOW TABLES;                         -- 视图和表一起显示
DESC 视图名;
SHOW CREATE VIEW 视图名;

-- 修改
CREATE OR REPLACE VIEW 视图名 AS SELECT ...;
ALTER VIEW 视图名 AS SELECT ...;

-- 删除
DROP VIEW [IF EXISTS] 视图名;

可更新视图的条件

视图中的行和基表中的行必须有一对一关系，以下情况不可更新：

• 包含聚合函数（SUM, COUNT, AVG等）
• 包含DISTINCT、GROUP BY、HAVING
• 包含UNION/UNION ALL
• 包含子查询在SELECT列表
• 常量视图
• 包含JOIN的某些情况

视图的优缺点

• 优点：简化复杂查询、减少数据冗余、数据安全（限制访问列）、分解复杂查询
• 缺点：基表结构变更时需维护视图、可能影响性能

14. 存储过程与函数

存储过程

DELIMITER $  -- 自定义结束符

CREATE PROCEDURE 过程名 ([IN|OUT|INOUT] 参数名 类型, ...)
BEGIN
  -- SQL语句
END $

DELIMITER ;  -- 恢复默认结束符

• IN：输入参数（默认）
• OUT：输出参数（调用时用变量接收）
• INOUT：既输入又输出

-- 调用
CALL 过程名(参数);

-- 查看
SHOW CREATE PROCEDURE 过程名;
SHOW PROCEDURE STATUS LIKE '过程名';

-- 删除
DROP PROCEDURE [IF EXISTS] 过程名;

存储函数

CREATE FUNCTION 函数名 (参数名 类型, ...) RETURNS 返回类型
BEGIN
  RETURN 表达式;
END

-- 调用
SELECT 函数名(参数);

存储过程 vs 函数

争议： 阿里规范建议不使用存储过程，因为调试困难、移植性差、不利于版本管理。

15. 变量、流程控制与游标

系统变量

SHOW [GLOBAL|SESSION] VARIABLES [LIKE '模式'];  -- 查看
SET [GLOBAL|SESSION] 变量名 = 值;               -- 修改
-- 通过启动选项设置的都是GLOBAL级别

用户变量

-- 会话用户变量（@开头，当前连接有效）
SET @var = 值;
SELECT @var := 值;
SELECT 字段 INTO @var FROM 表 LIMIT 1;

-- 局部变量（DECLARE声明，只在BEGIN...END中有效）
DECLARE 变量名 类型 [DEFAULT 值];
SET 变量名 = 值;
SELECT 字段 INTO 变量名 FROM 表 LIMIT 1;

条件与异常处理

-- 定义条件
DECLARE 条件名 CONDITION FOR SQLSTATE '状态码';
DECLARE 条件名 CONDITION FOR 错误码;

-- 定义处理器
DECLARE CONTINUE HANDLER FOR 条件 SET 变量 = 值;   -- 继续执行
DECLARE EXIT HANDLER FOR 条件 SET 变量 = 值;       -- 退出BEGIN...END

流程控制

-- IF
IF 条件1 THEN ...
ELSEIF 条件2 THEN ...
ELSE ...
END IF;

-- CASE（两种写法）
CASE 表达式 WHEN 值1 THEN ... WHEN 值2 THEN ... ELSE ... END CASE;
CASE WHEN 条件1 THEN ... WHEN 条件2 THEN ... ELSE ... END CASE;

-- 循环
WHILE 条件 DO ... END WHILE;
REPEAT ... UNTIL 条件 END REPEAT;
[标签:] LOOP ... END LOOP [标签];

-- 跳出循环
LEAVE 标签;     -- 相当于break
ITERATE 标签;   -- 相当于continue

游标

DECLARE 游标名 CURSOR FOR SELECT语句;       -- 声明
OPEN 游标名;                                 -- 打开
FETCH 游标名 INTO 变量1, 变量2, ...;         -- 逐行取值
CLOSE 游标名;                                -- 关闭

游标配合HANDLER使用，当FETCH不到数据时触发NOT FOUND退出循环。

MySQL8 SET PERSIST

SET PERSIST max_connections = 1000;  -- 持久化修改，重启后仍有效

16. 触发器

触发器是与表事件关联的特殊存储过程，在INSERT/UPDATE/DELETE时自动执行。

CREATE TRIGGER 触发器名
  BEFORE|AFTER INSERT|UPDATE|DELETE
  ON 表名 FOR EACH ROW
BEGIN
  -- SQL语句，可用NEW和OLD引用行数据
END

• NEW：引用INSERT/UPDATE后的新值
• OLD：引用DELETE/UPDATE前的旧值

-- 查看
SHOW TRIGGERS;
SHOW CREATE TRIGGER 触发器名;
SELECT * FROM information_schema.TRIGGERS;

-- 删除
DROP TRIGGER [IF EXISTS] 触发器名;

优点： 保证数据完整性、记录操作日志、自动数据校验 缺点： 可读性差、对应用层隐藏、表结构变更可能导致触发器失效

17. 索引的创建与使用

索引是"空间换时间"的数据结构，提高查询效率，但增删改时需要维护索引。

索引分类

创建索引

-- 建表时创建
CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  UNIQUE INDEX idx_name (name),
  INDEX idx_age (age),
  INDEX idx_dept_salary (dept_id, salary)  -- 联合索引
);

-- 在已有表上创建
CREATE INDEX 索引名 ON 表名(字段);
CREATE UNIQUE INDEX 索引名 ON 表名(字段);
ALTER TABLE 表名 ADD INDEX 索引名(字段);
ALTER TABLE 表名 ADD UNIQUE(字段);
ALTER TABLE 表名 ADD FULLTEXT(字段);

删除索引

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY;

MySQL 8.0新特性

• 降序索引：CREATE INDEX idx ON t(c1 ASC, c2 DESC);
• 不可见索引：ALTER TABLE t ALTER INDEX idx INVISIBLE; 用于测试去掉索引的影响

索引使用注意事项

1. 只为WHERE、ORDER BY、GROUP BY中的字段建索引
2. 选择基数大的列建索引（区分度高，如用户ID，而非性别）
3. 索引类型尽量小，节省空间和比较时间
4. 字符串可以只建前缀索引 idx_name(name(10))
5. 索引列不要参与运算或使用函数，否则索引失效
6. 联合索引遵循最左前缀原则
7. 删除重复和冗余索引（如已有(a,b)就不需要再建(a)）
8. 单表索引数量不宜过多（建议不超过6个）

18. MySQL 8 新特性

窗口函数

窗口函数可以在分组内排序和计算，且不会减少行数。

语法：

函数名() OVER (PARTITION BY 分组字段 ORDER BY 排序字段 [ASC|DESC])

也支持命名窗口：

函数名() OVER w ... WINDOW w AS (PARTITION BY ... ORDER BY ...)

序号函数

-- 每个分类下价格最高的3个商品
SELECT * FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS rn, *
  FROM goods
) t WHERE rn <= 3;

分布函数

• PERCENT_RANK()：(rank-1)/(rows-1)，等级百分比
• CUME_DIST()：小于等于当前值的比例

前后函数

• LAG(expr, n)：当前行的前n行的值
• LEAD(expr, n)：当前行的后n行的值

-- 前一个商品的价格
SELECT LAG(price, 1) OVER(PARTITION BY category_id ORDER BY price) AS pre_price FROM goods;

首尾函数

• FIRST_VALUE(expr)：窗口内第一个expr值
• LAST_VALUE(expr)：窗口内最后一个expr值

其他

• NTH_VALUE(expr, n)：窗口内第n个expr值
• NTILE(n)：将数据分成n个桶，返回桶编号

公用表表达式（CTE）

-- 普通CTE（可替代子查询，且可多次引用）
WITH cte_name AS (SELECT ...)
SELECT * FROM cte_name;

-- 递归CTE（适合树形结构查询）
WITH RECURSIVE cte AS (
  SELECT ... FROM ... WHERE 初始条件        -- 种子查询
  UNION ALL
  SELECT ... FROM ... JOIN cte ON ...       -- 递归查询
)
SELECT * FROM cte WHERE ...;

-- 实例：查找所有下属（含下下属）
WITH RECURSIVE sub AS (
  SELECT employee_id, last_name, manager_id, 1 AS depth
  FROM employees WHERE employee_id = 100
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id, s.depth + 1
  FROM employees e JOIN sub s ON e.manager_id = s.employee_id
)
SELECT * FROM sub WHERE depth >= 3;

其他MySQL 8改进

• 默认字符集改为 utf8mb4
• 新增 caching_sha2_password 认证插件
• 查询缓存被移除
• DDL支持原子操作（要么全成功，要么全回滚）
• 支持隐藏索引和降序索引
• \N 不再等同于NULL，需用NULL
• 新增 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数
• 新增 ->> JSON路径操作符mysql基础篇 ========

1. SQL概述

SQL分类：

• DDL（数据定义语言）：CREATE、DROP、ALTER —— 操作数据库和表结构

• DML（数据操作语言）：INSERT、DELETE、UPDATE、SELECT —— 操作数据

• DCL（数据控制语言）：GRANT、REVOKE —— 权限管理

• DQL（数据查询语言）：SELECT（从DML中单独拎出来）

• TCL（事务控制语言）：COMMIT、ROLLBACK、SAVEPOINT

基本规则：

• 关键字大写，表名/字段名小写

• 字符串和日期用单引号，别名用双引号

• 反引号 ` 用于保留字冲突的字段名/表名

2. 基本SELECT语句

SELECT 字段1, 字段2 FROM 表名;    -- 查指定列
SELECT * FROM 表名;               -- 查全部列（生产环境不推荐）
SELECT 字段 AS 别名 FROM 表名;    -- 起别名，AS可省略
SELECT DISTINCT 字段 FROM 表名;   -- 去重（DISTINCT必须放在最前面，对所有列组合去重）
SELECT 字段 FROM 表名 WHERE 条件; -- 过滤

• NULL参与运算结果全为NULL，用 IFNULL(字段, 默认值) 处理

• DESCRIBE 表名; 或 DESC 表名; 查看表结构

• SELECT '常数' AS 列名 可以查询常数列

3. 运算符

算术运算符

+ - * /(或DIV) %(或MOD)

• MySQL中 + 只做加法，字符串拼接用 CONCAT()

• 除以0结果为NULL

比较运算符

逻辑运算符

NOT(!) AND(&&) OR(||) XOR（异或）

• AND优先级高于OR

4. 排序与分页

-- 排序
SELECT ... FROM ... ORDER BY 字段 ASC|DESC;  -- 默认ASC升序
SELECT ... FROM ... ORDER BY 字段1 ASC, 字段2 DESC;  -- 多列排序，优先级从左到右
SELECT ... FROM ... ORDER BY 别名;           -- 可以用别名排序
​
-- 分页
SELECT ... FROM ... LIMIT 起始行, 条数;      -- LIMIT offset, count
SELECT ... FROM ... LIMIT 条数 OFFSET 起始行; -- MySQL 8.0语法
-- 第n页：LIMIT (n-1)*pageSize, pageSize

5. 多表查询（JOIN）

内连接：inner join 简写为join，查询两个表之间符合条件的结果集

外连接：outer join，分为 left outer join 和 right outer join，简写为 left join 或 right join

• 查询两个表之间，左表或右表所有结果集，以及另外表的结果集，如果另外表不符合连接条件，则字段为null

-- 隐式内连接（等值连接）
SELECT * FROM t1, t2 WHERE t1.id = t2.id
​
-- 显式内连接
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
​
-- 左外连接（左表全保留，右表不匹配填NULL）
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
​
-- 自连接
SELECT * FROM emp e JOIN emp m ON e.manager_id = m.id
​
-- 多表连接
SELECT * FROM t1 JOIN t2 ON t1.tid = t2.tid JOIN t3 ON t1.tname = t3.tname
​
-- 复合连接（多个条件）
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t1.type = t2.type
​
-- USING（连接字段同名时简写）
t1 JOIN t2 USING(id)  -- 等价于 t1 JOIN t2 ON t1.id = t2.id
t1 JOIN t2 USING(id, name)
​
-- 自然连接（自动匹配同名列，基本不用）
SELECT * FROM t1 NATURAL JOIN t2
​
-- 交叉连接（笛卡尔积）
t1 CROSS JOIN t2   -- 显式
FROM t1, t2        -- 隐式

UNION联合

合并多段SELECT查询（列数和类型必须一致）

• UNION：去重合并（效率低）

• UNION ALL：不去重合并（推荐）

SELECT id, name FROM t1
UNION ALL
SELECT id, name FROM t2;

6. 常用单行函数

字符串函数

数值函数

日期时间函数

常用格式符：%Y四位年 %m两位月 %d两位日 %H24小时 %i分钟 %s秒

流程控制函数

IF(条件, 真值, 假值)
​
IFNULL(字段, 默认值)  -- 字段为NULL时返回默认值
​
CASE 字段 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ELSE 默认结果 END
​
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 默认结果 END

7. 聚合函数与GROUP BY

聚合函数

• 聚合函数不能嵌套，如 AVG(SUM(...))

GROUP BY分组

SELECT 分组字段, 聚合函数(...) FROM 表 GROUP BY 分组字段; -- 只能出现分组字段和聚合函数
SELECT 字段1, 字段2, SUM(...) FROM 表 GROUP BY 字段1, 字段2;  -- 多字段分组
SELECT ... GROUP BY 字段 WITH ROLLUP;  -- 追加汇总行（与ORDER BY互斥）
​
-- group by 自带排序，若不需要排序可以置顶order by null ，提升性能

• SELECT中非聚合函数的字段，必须在GROUP BY中出现

HAVING过滤分组

SELECT 分组字段, COUNT(*) cnt FROM 表 GROUP BY 分组字段 HAVING cnt > 1; -- 对group by的结果过滤

• WHERE：分组前过滤行，不能用聚合函数，先过滤再连接（效率高）

• HAVING：分组后过滤组，可以用聚合函数，先连接再过滤（效率低）

• 两者可以同时使用，WHERE在前，HAVING在后

ORDER BY 排序

-- 无索引情况下走filesort 性能差，所以要尽量对order by 的列加联合索引；且顺序必须一致

SELECT完整执行顺序

FROM → ON → JOIN → WHERE → GROUP BY → 聚合函数 → WITH → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

8. 子查询

子查询（内查询）在主查询之前执行，结果被主查询使用。子查询要放在括号内，放在比较条件右侧。

按返回结果分类

• 单行子查询：返回一行，用 = > < >= <= <> 比较

• 多行子查询：返回多行，用 IN / ANY / ALL / SOME 比较

-- 单行 SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name='Abel');

-- 多行 IN SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id=1700);

-- ANY（满足任意一个） SELECT * FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE department_id=90);

-- ALL（满足所有） SELECT * FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE department_id=90);

按是否关联分类

• 不相关子查询：子查询独立执行一次，结果给主查询用

• 相关子查询：子查询依赖主查询，每执行主查询一行，子查询都重新执行

-- 相关子查询 SELECT e.last_name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- FROM中子查询（必须起别名） SELECT e.last_name, e.salary FROM employees e, (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) d WHERE e.department_id = d.department_id AND e.salary > d.avg_sal;

-- EXISTS / NOT EXISTS SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id); SELECT * FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

相关更新与删除

-- 相关更新 UPDATE employees e SET e.department_name = (SELECT d.department_name FROM departments d WHERE e.department_id = d.department_id);

-- 相关删除 DELETE FROM employees e WHERE employee_id IN (SELECT employee_id FROM emp_history WHERE employee_id = e.employee_id);

注意： 自连接通常比子查询效率更高，优先使用自连接。

9. 创建和管理表（DDL）

数据库操作

CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET 字符集]; DROP DATABASE [IF EXISTS] 库名; SHOW DATABASES; USE 库名; SHOW TABLES [FROM 库名];

创建表

CREATE TABLE [IF NOT EXISTS] 表名 ( 字段1 数据类型 [约束] [默认值], 字段2 数据类型 [约束] [默认值], [表级约束] );

-- 通过查询建表 CREATE TABLE emp_copy AS SELECT * FROM employees; CREATE TABLE emp_empty AS SELECT * FROM employees WHERE 1=2; -- 只复制结构

修改表

ALTER TABLE 表名 ADD 字段 类型 [FIRST|AFTER 字段]; -- 加列 ALTER TABLE 表名 MODIFY 字段 新类型 [DEFAULT 值]; -- 改类型/默认值 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新类型; -- 改名 ALTER TABLE 表名 DROP 字段名; -- 删列

删除与清空

DROP TABLE [IF EXISTS] 表名; -- 删表（结构和数据都没了，不能回滚） TRUNCATE TABLE 表名; -- 清空数据，释放空间（不能回滚） DELETE FROM 表名 [WHERE 条件]; -- 删数据（可以回滚）

TRUNCATE vs DELETE：

• TRUNCATE 速度快，不触发触发器，不能回滚

• DELETE 可以带WHERE，可以回滚，触发触发器

重命名表

RENAME TABLE 旧表名 TO 新表名; ALTER TABLE 旧表名 RENAME TO 新表名;

MySQL8 DDL原子化

MySQL 8.0起，DDL操作支持事务完整性：要么全部成功，要么全部回滚。例如 DROP TABLE t1, t2 中如果t2不存在，t1也不会被删除（5.7中t1会被删除）。

10. 数据处理（增删改）

插入

-- 全字段插入 INSERT INTO 表名 VALUES (v1, v2, ...);

-- 指定字段插入 INSERT INTO 表名 (字段1, 字段2) VALUES (v1, v2);

-- 批量插入（效率高于多条INSERT） INSERT INTO 表名 (字段1, 字段2) VALUES (v1,v2), (v3,v4), (v5,v6);

-- 将查询结果插入 INSERT INTO 目标表 (字段...) SELECT ... FROM 源表 WHERE ...;

更新

UPDATE 表名 SET 字段1=值1, 字段2=值2 [WHERE 条件]; -- 不加WHERE会更新所有行 -- 回滚需要先 SET AUTOCOMMIT = FALSE;

删除

DELETE FROM 表名 [WHERE 条件]; -- 不加WHERE会删除所有行 -- 有外键关联的父表数据不能直接删除

MySQL8 计算列

CREATE TABLE tb ( id INT, a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL -- c的值自动由a+b计算 );

11. 数据类型要点

整数类型

• UNSIGNED：无符号，不能存负数，正数范围翻倍

• ZEROFILL：不够位数前补0，自动变UNSIGNED

• 选择原则：能用小类型就不用大类型，节省空间

小数类型

• M是总位数，D是小数位数

• 金额/货币一律用DECIMAL，不要用FLOAT/DOUBLE

日期时间

• DATETIME范围更大，TIMESTAMP有2038年问题但占空间小

字符串类型

• CHAR vs VARCHAR：CHAR定长查询快但浪费空间，VARCHAR变长省空间但需要额外1-2字节存长度。短且固定用CHAR，长度变化大用VARCHAR。

• VARCHAR最大65535字节（受行大小限制），实际UTF8下约21844字符。

其他

• BINARY/VARBINARY：二进制字符串

• BLOB：二进制大对象（图片、文件等）

• JSON：存储JSON数据，用 -> 或 ->> 访问字段

12. 约束

约束分类

创建表时加约束

CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, gender CHAR(1) DEFAULT '男', age INT CHECK (age >= 0 AND age <= 150), dept_id INT, CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES department(id) ON DELETE CASCADE -- 父表删除时级联删除 ON UPDATE CASCADE -- 父表更新时级联更新 );

修改表时加约束

ALTER TABLE 表名 MODIFY 字段 类型 NOT NULL; -- 加非空 ALTER TABLE 表名 ADD UNIQUE(字段); -- 加唯一 ALTER TABLE 表名 ADD PRIMARY KEY(字段); -- 加主键 ALTER TABLE 表名 ADD FOREIGN KEY(字段) REFERENCES 表(字段); -- 加外键 ALTER TABLE 表名 ADD CHECK(条件); -- 加检查 ALTER TABLE 表名 MODIFY 字段 类型 DEFAULT 值; -- 加默认值

删除约束

ALTER TABLE 表名 MODIFY 字段 类型 [NULL]; -- 去非空 ALTER TABLE 表名 DROP INDEX 索引名; -- 去唯一 ALTER TABLE 表名 DROP PRIMARY KEY; -- 去主键 ALTER TABLE 表名 DROP FOREIGN KEY 约束名; -- 去外键 ALTER TABLE 表名 ALTER 字段 DROP DEFAULT; -- 去默认值

外键的级联操作

阿里巴巴规范建议： 不使用外键，在应用层控制数据完整性。

AUTO_INCREMENT

• 必须定义在KEY上（主键或唯一键）

• MySQL 8.0起，自增值持久化存储到redo log，重启不会丢失

• 不连续跳号是正常现象（删除、回滚等）

13. 视图

视图是一张虚拟表，本身不存数据，基于SELECT查询定义。

-- 创建 CREATE [OR REPLACE] VIEW 视图名 [(列名列表)] AS SELECT ...;

-- 查看 SHOW TABLES; -- 视图和表一起显示 DESC 视图名; SHOW CREATE VIEW 视图名;

-- 修改 CREATE OR REPLACE VIEW 视图名 AS SELECT ...; ALTER VIEW 视图名 AS SELECT ...;

-- 删除 DROP VIEW [IF EXISTS] 视图名;

可更新视图的条件

视图中的行和基表中的行必须有一对一关系，以下情况不可更新：

• 包含聚合函数（SUM, COUNT, AVG等）

• 包含DISTINCT、GROUP BY、HAVING

• 包含UNION/UNION ALL

• 包含子查询在SELECT列表

• 常量视图

• 包含JOIN的某些情况

视图的优缺点

• 优点：简化复杂查询、减少数据冗余、数据安全（限制访问列）、分解复杂查询

• 缺点：基表结构变更时需维护视图、可能影响性能

14. 存储过程与函数

存储过程

DELIMITER $ -- 自定义结束符

CREATE PROCEDURE 过程名 ([IN|OUT|INOUT] 参数名 类型, ...) BEGIN -- SQL语句 END $

DELIMITER ; -- 恢复默认结束符

• IN：输入参数（默认）

• OUT：输出参数（调用时用变量接收）

• INOUT：既输入又输出

-- 调用 CALL 过程名(参数);

-- 查看 SHOW CREATE PROCEDURE 过程名; SHOW PROCEDURE STATUS LIKE '过程名';

-- 删除 DROP PROCEDURE [IF EXISTS] 过程名;

存储函数

CREATE FUNCTION 函数名 (参数名 类型, ...) RETURNS 返回类型 BEGIN RETURN 表达式; END

-- 调用 SELECT 函数名(参数);

存储过程 vs 函数

争议： 阿里规范建议不使用存储过程，因为调试困难、移植性差、不利于版本管理。

15. 变量、流程控制与游标

系统变量

SHOW [GLOBAL|SESSION] VARIABLES [LIKE '模式']; -- 查看 SET [GLOBAL|SESSION] 变量名 = 值; -- 修改 -- 通过启动选项设置的都是GLOBAL级别

用户变量

-- 会话用户变量（@开头，当前连接有效） SET @var = 值; SELECT @var := 值; SELECT 字段 INTO @var FROM 表 LIMIT 1;

-- 局部变量（DECLARE声明，只在BEGIN...END中有效） DECLARE 变量名 类型 [DEFAULT 值]; SET 变量名 = 值; SELECT 字段 INTO 变量名 FROM 表 LIMIT 1;

条件与异常处理

-- 定义条件 DECLARE 条件名 CONDITION FOR SQLSTATE '状态码'; DECLARE 条件名 CONDITION FOR 错误码;

-- 定义处理器 DECLARE CONTINUE HANDLER FOR 条件 SET 变量 = 值; -- 继续执行 DECLARE EXIT HANDLER FOR 条件 SET 变量 = 值; -- 退出BEGIN...END

流程控制

-- IF IF 条件1 THEN ... ELSEIF 条件2 THEN ... ELSE ... END IF;

-- CASE（两种写法） CASE 表达式 WHEN 值1 THEN ... WHEN 值2 THEN ... ELSE ... END CASE; CASE WHEN 条件1 THEN ... WHEN 条件2 THEN ... ELSE ... END CASE;

-- 循环 WHILE 条件 DO ... END WHILE; REPEAT ... UNTIL 条件 END REPEAT; [标签:] LOOP ... END LOOP [标签];

-- 跳出循环 LEAVE 标签; -- 相当于break ITERATE 标签; -- 相当于continue

游标

DECLARE 游标名 CURSOR FOR SELECT语句; -- 声明 OPEN 游标名; -- 打开 FETCH 游标名 INTO 变量1, 变量2, ...; -- 逐行取值 CLOSE 游标名; -- 关闭

游标配合HANDLER使用，当FETCH不到数据时触发NOT FOUND退出循环。

MySQL8 SET PERSIST

SET PERSIST max_connections = 1000; -- 持久化修改，重启后仍有效

16. 触发器

触发器是与表事件关联的特殊存储过程，在INSERT/UPDATE/DELETE时自动执行。

CREATE TRIGGER 触发器名 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW BEGIN -- SQL语句，可用NEW和OLD引用行数据 END

• NEW：引用INSERT/UPDATE后的新值

• OLD：引用DELETE/UPDATE前的旧值

-- 查看 SHOW TRIGGERS; SHOW CREATE TRIGGER 触发器名; SELECT * FROM information_schema.TRIGGERS;

-- 删除 DROP TRIGGER [IF EXISTS] 触发器名;

优点： 保证数据完整性、记录操作日志、自动数据校验 缺点： 可读性差、对应用层隐藏、表结构变更可能导致触发器失效

17. 索引的创建与使用

索引是"空间换时间"的数据结构，提高查询效率，但增删改时需要维护索引。

索引分类

创建索引

-- 建表时创建 CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(50), UNIQUE INDEX idx_name (name), INDEX idx_age (age), INDEX idx_dept_salary (dept_id, salary) -- 联合索引 );

-- 在已有表上创建 CREATE INDEX 索引名 ON 表名(字段); CREATE UNIQUE INDEX 索引名 ON 表名(字段); ALTER TABLE 表名 ADD INDEX 索引名(字段); ALTER TABLE 表名 ADD UNIQUE(字段); ALTER TABLE 表名 ADD FULLTEXT(字段);

删除索引

DROP INDEX 索引名 ON 表名; ALTER TABLE 表名 DROP INDEX 索引名; ALTER TABLE 表名 DROP PRIMARY KEY;

MySQL 8.0新特性

• 降序索引：CREATE INDEX idx ON t(c1 ASC, c2 DESC);

• 不可见索引：ALTER TABLE t ALTER INDEX idx INVISIBLE; 用于测试去掉索引的影响

索引使用注意事项

1. 只为WHERE、ORDER BY、GROUP BY中的字段建索引

2. 选择基数大的列建索引（区分度高，如用户ID，而非性别）

3. 索引类型尽量小，节省空间和比较时间

4. 字符串可以只建前缀索引 idx_name(name(10))

5. 索引列不要参与运算或使用函数，否则索引失效

6. 联合索引遵循最左前缀原则

7. 删除重复和冗余索引（如已有(a,b)就不需要再建(a)）

8. 单表索引数量不宜过多（建议不超过6个）

18. MySQL 8 新特性

窗口函数

窗口函数可以在分组内排序和计算，且不会减少行数。

语法：

函数名() OVER (PARTITION BY 分组字段 ORDER BY 排序字段 [ASC|DESC])

也支持命名窗口：

函数名() OVER w ... WINDOW w AS (PARTITION BY ... ORDER BY ...)

序号函数

-- 每个分类下价格最高的3个商品 SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS rn, * FROM goods ) t WHERE rn <= 3;

分布函数

• PERCENT_RANK()：(rank-1)/(rows-1)，等级百分比

• CUME_DIST()：小于等于当前值的比例

前后函数

• LAG(expr, n)：当前行的前n行的值

• LEAD(expr, n)：当前行的后n行的值

-- 前一个商品的价格 SELECT LAG(price, 1) OVER(PARTITION BY category_id ORDER BY price) AS pre_price FROM goods;

首尾函数

• FIRST_VALUE(expr)：窗口内第一个expr值

• LAST_VALUE(expr)：窗口内最后一个expr值

其他

• NTH_VALUE(expr, n)：窗口内第n个expr值

• NTILE(n)：将数据分成n个桶，返回桶编号

公用表表达式（CTE）

-- 普通CTE（可替代子查询，且可多次引用） WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

-- 递归CTE（适合树形结构查询） WITH RECURSIVE cte AS ( SELECT ... FROM ... WHERE 初始条件 -- 种子查询 UNION ALL SELECT ... FROM ... JOIN cte ON ... -- 递归查询 ) SELECT * FROM cte WHERE ...;

-- 实例：查找所有下属（含下下属） WITH RECURSIVE sub AS ( SELECT employee_id, last_name, manager_id, 1 AS depth FROM employees WHERE employee_id = 100 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, s.depth + 1 FROM employees e JOIN sub s ON e.manager_id = s.employee_id ) SELECT * FROM sub WHERE depth >= 3;

其他MySQL 8改进

• 默认字符集改为 utf8mb4

• 新增 caching_sha2_password 认证插件

• 查询缓存被移除

• DDL支持原子操作（要么全成功，要么全回滚）

• 支持隐藏索引和降序索引

• \N 不再等同于NULL，需用NULL

• 新增 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数

• 新增 ->> JSON路径操作符