MySQL 系统结构与多表查询
本文合并自：01-系统结构与多表查询.md、2-3-MySQL系统结构与多表查询.md
适用版本：MariaDB 5.5 / MySQL 5.5 ~ 5.7（部分内容标注 5.6 / 5.7 / 8.0 差异） 核心内容：MySQL 体系结构与 SQL 执行流程、物理文件组成、存储引擎、系统数据库、内存结构、多表关系与连接查询、外连接与子查询、集合运算、视图、分组进阶、综合实战与练习
一、MySQL 系统结构
1.1 整体架构分层
SQL（Structured Query Language，结构化查询语言）是与数据库交互的标准语言。MySQL 在逻辑上最经典的划分是两层架构：SQL 层（SQL Layer）与存储引擎层（Storage Engine Layer）。
----------------
| SQL Layer | <-- 权限判断、SQL 解析、执行计划优化、查询缓存
----------------
| | |
------------------------
| Storage Engine Layer | <-- 数据存储与读取（InnoDB、MyISAM 等）
------------------------| 层级 | 名称 | 职责 |
|---|---|---|
| 第一层 | SQL Layer | 底层数据存取之前的所有工作：连接处理、权限判断、SQL 解析、执行计划优化、Query Cache 处理等 |
| 第二层 | Storage Engine Layer | 底层数据存取操作的具体实现，由多种存储引擎共同组成，可插拔（插件式选择） |
如果把两层再展开，可以细分为四层，这也是理解一条 SQL 如何被执行的基础：
| 层次 | 名称 | 包含组件 | 职责 |
|---|---|---|---|
| 1 | 连接层（Connectors / Connection Pool） | 通信协议、线程处理、连接池、认证与授权、SSL | 接收客户端连接，完成认证与授权，为每个连接分配线程 |
| 2 | 服务层（SQL Layer / Server Layer） | 查询缓存、解析器 Parser、预处理器、优化器 Optimizer、执行器 Executor、所有内置函数、跨引擎功能（存储过程、触发器、视图） | 解析 SQL、生成并选择执行计划、调用引擎接口执行 |
| 3 | 引擎层（Storage Engine Layer） | InnoDB、MyISAM、Memory、Archive、CSV、NDB… | 真正负责数据的存取，提供事务、锁、索引等能力，可插拔 |
| 4 | 存储层（File System） | 数据文件、日志文件、配置文件 | 将数据持久化到磁盘（或内存） |
要点：
- 连接层与服务层是所有引擎共用的；事务、锁、索引组织方式等由引擎层决定，所以"MySQL 支不支持事务"这个问题必须落到"用的哪个引擎"上。
- 存储引擎是插件式的，同一个库里的不同表可以使用不同引擎（但不推荐混用）。
1.2 一条 SQL 的完整执行流程
客户端
│ ① 建立连接（TCP / Socket）→ 认证授权 → 分配线程
▼
连接层
│ ② 查询缓存（MySQL 5.7 及以前，命中则直接返回结果）
▼
解析器 Parser
│ ③ 词法分析 + 语法分析 → 生成解析树；语法错误在此报出
▼
预处理器
│ ④ 语义检查：表/列是否存在、是否有权限、名称是否歧义
▼
优化器 Optimizer
│ ⑤ 生成执行计划：选择索引、决定多表连接顺序与连接算法
▼
执行器 Executor
│ ⑥ 按执行计划逐步调用存储引擎 API 读写数据
▼
存储引擎
│ ⑦ 从 Buffer Pool 或磁盘取数据，写操作同时写 redo / undo / binlog
▼
返回结果集给客户端（并按需写入查询缓存）分阶段说明：
- 连接（Connection）：客户端通过 TCP 3306 端口或本地 socket 文件连接服务端，服务端校验用户名、密码、来源主机与权限，通过后从线程池取出（或新建）一个线程专门服务该连接。
SHOW PROCESSLIST可查看当前所有连接及其状态。 - 查询缓存（Query Cache）：以 SQL 语句文本为 key 缓存结果集。命中则跳过解析、优化、执行直接返回。
- 解析（Parse）：词法分析把语句切成 token，语法分析构造解析树。此阶段报出
You have an error in your SQL syntax一类错误。 - 预处理与权限检查：解析树的语义校验，例如列
xx不存在、Column 'xx' in field list is ambiguous（列名歧义）、当前用户对该表无SELECT权限等。 - 优化（Optimize）：基于成本估算（CBO）选择索引、决定多表连接的驱动表顺序、决定是否使用临时表与排序（filesort）、决定子查询是否改写为半连接或物化。可以用
EXPLAIN查看优化器最终的选择。 - 执行（Execute）：执行器按计划调用引擎接口逐行取数据、过滤、连接、聚合、排序。
- 返回（Return）：结果集通常以流式方式边生成边发送给客户端。
关于查询缓存的历史与移除（重点）：
| 版本 | 查询缓存状态 |
|---|---|
| MySQL 5.5 / 5.6 | 存在，query_cache_type 默认可用，但默认大小为 0 或很小 |
| MySQL 5.7 | 存在但默认关闭（query_cache_type=OFF），官方标记为 deprecated |
| MySQL 8.0 | 彻底移除，相关变量与 SHOW STATUS 计数器一并删除 |
查询缓存被废弃的原因：
- 失效粒度太粗：只要表上发生任何写操作（INSERT / UPDATE / DELETE / DDL），该表相关的全部缓存立即失效，写多的表几乎永远命中不了。
- 加锁竞争严重：查询缓存本身是一把全局互斥锁，高并发下反而成为瓶颈。
- 命中条件苛刻：SQL 文本必须完全一致（包括空格、大小写、注释），含
NOW()、CURRENT_DATE()、RAND()、用户变量的语句不缓存。
查看与关闭（5.6/5.7 环境）：
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';# /etc/my.cnf
[mysqld]
query_cache_type=0
query_cache_size=0现代实践：把结果缓存交给应用层（Redis / Memcached），数据库端专注于索引与执行计划优化。
二、MySQL 物理文件组成
MySQL 的所有文件都以数据目录 datadir 为中心组织，默认 /var/lib/mysql（RPM 安装）或 /data（源码自定义）。
SHOW VARIABLES LIKE 'datadir';2.1 datadir 下的目录与文件
ls -l /var/lib/mysqldrwx------ 2 mysql mysql 4096 aaa # 每个数据库对应一个同名目录
drwx------ 2 mysql mysql 4096 mysql # 系统库
drwx------ 2 mysql mysql 4096 performance_schema
-rw-rw---- 1 mysql mysql 12582912 ibdata1 # InnoDB 共享表空间
-rw-rw---- 1 mysql mysql 5242880 ib_logfile0 # InnoDB redo 日志
-rw-rw---- 1 mysql mysql 5242880 ib_logfile1
-rw-rw---- 1 mysql mysql 126 mysql-bin.000001 # 二进制日志
-rw-rw---- 1 mysql mysql 19 mysql-bin.index # 二进制日志索引
-rw-r----- 1 mysql mysql 2456 localhost.err # 错误日志
-rw-rw---- 1 mysql mysql 6 localhost.pid # 进程号文件
srwxrwxrwx 1 mysql mysql 0 mysql.sock # 本地连接 socket 文件规律：一个数据库（schema）= datadir 下的一个目录；一张表 = 该目录下的若干文件。因此在操作系统层面 mv 目录即可实现"库改名"（MySQL 没有改库名的 SQL 命令），但仅对 MyISAM 等文件级引擎相对安全，InnoDB 因数据字典记录了表空间信息，直接 mv 会导致表无法访问。
2.2 数据文件
不同存储引擎有各自的数据文件，扩展名也不同：
| 扩展名 / 文件 | 所属引擎 | 用途 |
|---|---|---|
.frm |
所有引擎共有（MySQL 8.0 前） | 存放与表相关的元数据（表结构定义等），位于所属数据库目录下 |
.MYD |
MyISAM 专用 | 存放表数据（Data） |
.MYI |
MyISAM 专用 | 存放索引（Index），可被 key cache 缓存 |
.ibd |
InnoDB | 独享表空间数据文件（数据 + 索引），每表一个 |
ibdata1 / ibdata* |
InnoDB | 共享表空间数据文件，所有表共用一个或多个；即使开启独享表空间，仍保存数据字典、undo 段、双写缓冲、change buffer |
ib_logfile0 / ib_logfile1 |
InnoDB | 在线 redo 日志（重做日志），循环写 |
.ARZ / .ARM |
Archive | 数据压缩文件（ARZ）和元数据文件（ARM） |
.MRG |
Merge | 存放参与合并的基表表名，本身不存数据 |
.CSV |
CSV | 以标准 CSV 文本格式存放数据 |
db.opt |
所有 | 每个 schema 目录下记录默认字符集与排序规则 |
mysql.sock |
— | 本地客户端连接使用的 socket 文件 |
*.pid |
— | mysqld 进程号文件 |
查看 db.opt：
cat /data/aaa/db.optdefault-character-set=utf8
default-collation=utf8_general_ciMySQL 5.6 起
innodb_file_per_table默认开启，每个 InnoDB 表会对应一个表名.ibd文件。 MySQL 8.0 取消了.frm，表结构定义统一存入 InnoDB 数据字典（mysql.ibd）。
2.3 日志文件总览
| 日志类型 | 文件 / 配置 | 说明 |
|---|---|---|
| 错误日志 | --log-error，默认 hostname.err |
记录启动、关闭、严重警告与错误 |
| 查询日志 | general_log / general_log_file，默认 hostname.log |
记录所有 SQL（含 SELECT），性能开销大，谨慎开启 |
| 二进制日志 | log-bin，如 mysql-bin.000001 |
记录所有修改数据的 SQL，用于复制与恢复 |
| 慢查询日志 | slow_query_log / --log-slow-queries，默认 hostname-slow.log |
记录执行时间较长的 SQL |
| InnoDB redo 日志 | ib_logfile* |
保障事务安全（崩溃恢复） |
1. 错误日志（Error Log）
- 记录运行过程中较为严重的警告、错误，以及 MySQL Server 每次启动和关闭的详细信息。
- 默认情况下错误日志功能关闭，信息输出到标准错误输出（stderr）。
- 启动时加
--log-error[=file_name]可开启并指定文件，默认存放在数据目录下，文件名为hostname.err。
使用 mysqld_safe 启动时，会在首行输出错误日志路径：
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf &备注：MySQL 启动不成功时，优先查看错误日志，这是排障第一现场。
2. 查询日志（Query Log / General Log）
- 记录 MySQL 中所有的 Query，包括所有
select。 - 通过
general_log和general_log_file[=file_name]打开。 - 体积较大，对性能影响明显，建议仅在跟踪特殊 Query 性能问题时短暂开启。
- 默认文件名为
hostname.log。
# /etc/my.cnf 的 [mysqld] 段
general_log
general_log_file=/var/log/mariadb/mariadb_query.log3. 二进制日志（Binary Log & Binary Log Index）
- 类似 Oracle 的 redo 日志（作用层面类比，实现不同），通过
--log-bin[=file_name]开启。 - MySQL 会将所有修改数据库数据的 Query 以二进制形式记录，并记录执行时间、消耗资源、事务信息，是事务安全的。
- 同时生成
mysql-bin.index索引文件，记录当前有哪些 binlog 文件。
RPM 版配置：
# /etc/my.cnf
[mysqld]
log-bin=mysql-binsystemctl restart mariadb
ls /var/lib/mysql
ls /var/lib/mysql | grep mysql-bin
# 产生 mysql-bin.000001 等文件二进制日志不能用
cat查看，只能使用mysqlbinlog：
mysqlbinlog /data/mysql-bin.000001
mysqlbinlog /var/lib/mysql/mysql-bin.000001示例场景：在 aaa 库建 cc 表，插入 3 条数据后 drop table cc 模拟误删除。日志中可看到：
# at 978
#160825 8:17:59 server id 1 end_log_pos 1073 Query thread_id=1 exec_time=0 error_code=0
use `aaa`/*!*/;
SET TIMESTAMP=1472084279/*!*/;
create table cc(id int)
/*!*/;
# at 1073
...
insert into cc values(1)
...
insert into cc values(2)
...
# at 1481
DROP TABLE `cc` /* generated by server */另一段输出示例（可看到每个事件的起始位置 # at xxx 与执行过的操作）：
#181203 22:48:18 server id 1 end_log_pos 326 Query thread_id=2
...
create database abc按位置恢复：
mysqlbinlog --start-position=978 --stop-position=1481 /data/mysql-bin.000001 | mysql -p
mysqlbinlog --start-position=978 --stop-position=1481 \
/var/lib/mysql/mysql-bin.000001 | mysql -p
mysqlbinlog --start-position=4 --stop-position=798 \
mysql_bin.000001 | mysql -uroot -p按时间恢复：
mysqlbinlog \
--start-datetime='2018-09-20 00:00:00' \
--stop-datetime='2018-09-30 12:00:00' \
mysql_bin.000001 | mysql -uroot -p
mysqlbinlog --start-datetime='2024-01-01 00:00:00' \
--stop-datetime='2024-01-31 23:59:59' \
/var/lib/mysql/mysql-bin.000001 | mysql -pAB 复制：MySQL 可基于二进制日志实现主从（AB）复制，主库将变更写入 binlog，从库读取并生成 relay log 后重放，从而保持数据同步。详细配置涉及
server-id、log-bin、change master to等参数。
4. 慢查询日志（Slow Query Log）
- 记录执行时间较长的 Query，即 slow query。
- 通过
--log-slow-queries[=file_name]（新版本为slow_query_log）开启，默认文件名hostname-slow.log，位于数据目录。 - 文本格式，记录语句执行时刻、耗时、用户、连接主机等。
- MySQL 提供专门工具
mysqldumpslow分析慢查询日志。
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow.log
long_query_time=1mysqldumpslow -s t -t 10 /var/log/mariadb/slow.log5. InnoDB 在线 Redo 日志
- InnoDB 的事务安全主要通过在线 redo 日志和表空间中的 undo 信息共同保证：redo 用于崩溃后前滚（保证已提交事务不丢），undo 用于回滚与 MVCC 快照读。
- redo 日志记录所有物理变更和事务信息，循环写，写满切换到下一个文件。
- 默认存放在数据目录下：
innodb_log_group_home_dir：redo 日志目录；innodb_log_files_in_group：日志文件数量；innodb_log_file_size：单个日志文件大小。
innodb_log_group_home_dir=
innodb_log_files_in_group=4三、存储引擎
3.1 查看与管理存储引擎
查看数据库支持的引擎：
SHOW ENGINES;输出中 Support 列含义：
| 值 | 含义 |
|---|---|
DEFAULT |
默认引擎 |
YES |
支持，可以创建该引擎的表 |
NO |
禁用 |
其他常用查询与修改：
-- 查询所有表及其引擎
SELECT table_name, engine FROM information_schema.tables;
select table_name, engine from information_schema.tables;
-- 查询指定表的引擎
SELECT table_name, engine FROM information_schema.tables WHERE table_name='xxx';
select table_name, engine from information_schema.tables where table_name = "xxx";
-- 创建表时指定引擎
CREATE TABLE t1 (id INT) ENGINE=InnoDB;
-- 修改表引擎（会重建整张表，大表慎用）
ALTER TABLE t1 ENGINE=MyISAM;
alter table aaa engine=innodb;
-- 查看建表语句，包含引擎与字符集信息
SHOW CREATE TABLE t1;
show create table aaa;通过 /etc/my.cnf 的 [mysqld] 段启用/禁用引擎或修改默认引擎：
[mysqld]
federated -- 把 federated 引擎由 NO 改为 YES
skip-archive -- 把 archive 引擎由 YES 改为 NO
default-storage-engine=myisam -- 默认引擎改为 MyISAM重启数据库后生效：
systemctl restart mariadb3.2 InnoDB 与 MyISAM 全面对比（重点）
MyISAM 查询引擎
- MySQL 5.5 版本之前的默认存储引擎，是 ISAM 的升级版本，曾使用十分广泛。
- 表级锁，读性能高；不支持事务、不支持外键。
- 适用于读多写少的场景。
InnoDB 存储引擎
- MySQL 5.5 及之后的默认引擎，最初由第三方公司（Innobase）开发，遵守相同的开源许可协议。
- 特性：
- 支持事务安全（ACID，支持提交、回滚、崩溃恢复）；
- 数据多版本读取（MVCC），读不阻塞写、写不阻塞读；
- 改进的锁定机制（行级锁）；
- 实现外键约束。
- 表空间概念：
- 共享表空间：所有数据存放在同一个（或多个）数据文件
ibdata*中，通过innodb_data_file_path指定，增加数据文件需要停机重启。 - 独享表空间：每个表的数据和索引存放在单独的
.ibd文件中（MySQL 5.6+ 默认，由innodb_file_per_table控制）。
- 共享表空间：所有数据存放在同一个（或多个）数据文件
全面对比表
| 对比项 | InnoDB | MyISAM |
|---|---|---|
| 事务 | 支持（ACID，commit / rollback / savepoint） | 不支持，每条语句都是自动提交 |
| 锁粒度 | 行级锁（基于索引加锁；无索引时退化为全表扫描并锁住扫过的行） | 表级锁（读锁共享、写锁独占，写操作会阻塞全表读写） |
| 外键 | 支持 FOREIGN KEY 约束与级联操作 |
不支持（语法可写但被忽略） |
| MVCC / 一致性读 | 支持，通过 undo + read view 实现快照读 | 不支持 |
| 崩溃恢复 | 支持，依靠 redo 日志自动前滚 + undo 回滚，恢复快且可靠 | 不支持自动恢复，异常宕机后表可能损坏，需 REPAIR TABLE / myisamchk 修复 |
| 全文索引 | MySQL 5.6 起支持 FULLTEXT（5.5 及以前不支持） | 一直支持 FULLTEXT |
| 索引结构 | 聚簇索引（Clustered Index）：主键即数据，二级索引叶子存主键值，回表查询 | 非聚簇索引：索引与数据分离，索引叶子存数据行的物理地址 |
| 存储方式 | 数据 + 索引一起存放在表空间：独享为 表名.ibd，共享为 ibdata*；结构文件 .frm |
三个文件：.frm 结构、.MYD 数据、.MYI 索引 |
COUNT(*) |
不保存总行数，需实时统计（可借助二级索引扫描） | 保存表总行数，COUNT(*) 无 WHERE 时直接返回，极快 |
| AUTO_INCREMENT | 必须是索引列（可为联合索引的第一列） | 可以是联合索引中的任意列 |
| 缓存 | Buffer Pool 同时缓存数据页与索引页 | key cache 只缓存索引，数据依赖操作系统文件缓存 |
| 表空间占用 | 较大 | 较小，且支持压缩表（myisampack，只读） |
| 备份 | 支持热备（如 XtraBackup、--single-transaction 逻辑备份） |
需锁表或停库拷贝文件 |
| 适用场景 | 绝大多数业务场景：并发读写、需要事务与数据一致性、需要外键、有大量更新删除的 OLTP 系统 | 读多写少且不要求事务的场景：静态字典表、日志归档统计、只读报表、全文检索（5.5 前） |
结论：生产环境默认统一使用 InnoDB。MyISAM 只在特定只读、可重建的小表上考虑，MySQL 8.0 中系统表也已全部改为 InnoDB。
3.3 其他存储引擎
NDB Cluster 存储引擎
- 主要用于 MySQL Cluster 分布式集群环境。
- MySQL Cluster 是在无共享存储设备情况下实现的内存数据库集群，通过 NDB 存储引擎实现。
Memory 引擎简介
- 数据存放在内存中，磁盘上只保留
.frm表结构文件。 - MySQL 或主机崩溃/重启后数据全部丢失，仅剩表结构。
- 实现页级锁定（Hash 索引为默认索引类型，也支持 B-Tree 索引），内存消耗大。
- 不支持 BLOB / TEXT 类型；单表大小受
max_heap_table_size、tmp_table_size限制。 - 适用场景：临时中间结果表、只读的小型映射表、需要极高访问速度且可重建的数据。
Archive 引擎简介
- 用较小空间存放很少访问的历史数据，采用 zlib 压缩，压缩比通常可达 1:10。
- 不支持索引（5.5 起支持在自增列上建索引），不支持删除和修改，仅支持
INSERT和SELECT。 - 由
.frm、.ARZ（压缩数据）、.ARM（元数据）组成；行级锁定。 - 适用场景：日志归档、审计流水、只写入不修改的历史明细。
其他引擎汇总
| 引擎 | 说明 |
|---|---|
| Merge | 将结构完全相同（包括字段顺序、索引）的多个 MyISAM 基表包装为单一访问入口，本身不存储数据，仅生成 .frm 结构文件和 .MRG 文件（存放参与合并的表名） |
| Memory | 数据存放在内存中，磁盘上只保留 .frm 表结构文件；MySQL 或主机崩溃后数据仅剩结构；实现页级锁定，内存消耗大 |
| BDB (BerkeleyDB) | 第三方引擎，支持事务安全；与 Memory 一样实现页级锁定 |
| Federated | 类似 Oracle 的 DBLINK，用于访问远程 MySQL 服务器的数据；源码编译安装时默认未启用，需手工指定 |
| Archive | 用较小空间存放很少访问的历史数据；不支持索引，不支持删除和修改，仅支持插入和查询；由 .frm、.ARZ、.ARM 组成；行级锁定 |
| Blackhole | 类似 /dev/null，写入的数据不保存，但会记录到 binary log 中；常用于数据迁移中转服务器（binlog 分发中继），节省磁盘空间 |
| CSV | 操作标准 CSV 文件，不支持索引；可用于直接生成被多数软件支持的 CSV 报表文件 |
| NDB Cluster | 用于 MySQL Cluster 分布式集群 |
3.4 Memory 引擎功能验证实验
create database aaa; -- 或 create schema aaa;
use aaa;
create table aaa (id int) engine=memory;
show create table aaa;
insert into aaa values(1);
insert into aaa values(2);
select * from aaa;
create table bbb (id int); -- 使用默认引擎
show create table bbb;
insert into bbb values(1);
insert into bbb values(2);
select * from bbb;重启数据库：
systemctl restart mariadb
mysql -pselect * from aaa.aaa; -- Memory 表数据丢失，仅剩表结构（返回 Empty set）
select * from aaa.bbb; -- 默认引擎表数据仍在（返回 1、2 两行）实验结论：Memory 引擎的数据仅存在于内存，服务重启即丢失，切勿用于持久化业务数据。
四、系统数据库
MySQL 安装完成后自带若干系统库，SHOW DATABASES; 可见：
SHOW DATABASES;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+4.1 information_schema（信息架构）
只读的虚拟库，本身不占用磁盘数据文件，所有"表"都是从内存数据字典中即时生成的视图，用于查询元数据。
常用视图：
| 视图 | 作用 |
|---|---|
SCHEMATA |
所有数据库及其默认字符集、排序规则 |
TABLES |
所有表：引擎、行数估算、数据长度、索引长度、创建时间 |
COLUMNS |
所有列：类型、是否可空、默认值、字符集、注释 |
STATISTICS |
所有索引及其列、基数（Cardinality）、唯一性 |
KEY_COLUMN_USAGE |
主键、唯一键、外键的列约束关系 |
TABLE_CONSTRAINTS |
表上的约束（PRIMARY KEY / UNIQUE / FOREIGN KEY） |
VIEWS |
所有视图及其定义 SQL |
PROCESSLIST |
当前所有连接与正在执行的语句（等价 SHOW PROCESSLIST） |
INNODB_TRX |
当前 InnoDB 活跃事务 |
INNODB_LOCKS / INNODB_LOCK_WAITS |
锁与锁等待情况（5.7 及以前） |
PARTITIONS |
分区表的分区信息 |
USER_PRIVILEGES / SCHEMA_PRIVILEGES / TABLE_PRIVILEGES |
各级权限 |
查询表结构与索引的典型用法：
-- 查看 aaa 库所有表的引擎、行数、数据与索引大小（MB）
SELECT table_name, engine, table_rows,
ROUND(data_length/1024/1024, 2) AS data_mb,
ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'aaa'
ORDER BY data_length DESC;
-- 查看 emp 表的所有列定义（等价于 DESC emp）
SELECT column_name, column_type, is_nullable, column_default, column_key, extra
FROM information_schema.columns
WHERE table_schema = 'aaa' AND table_name = 'emp'
ORDER BY ordinal_position;
-- 查看 emp 表的索引组成（等价于 SHOW INDEX FROM emp）
SELECT index_name, seq_in_index, column_name, non_unique, cardinality
FROM information_schema.statistics
WHERE table_schema = 'aaa' AND table_name = 'emp'
ORDER BY index_name, seq_in_index;
-- 查找整库中没有主键的表（运维巡检常用）
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
AND t.table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND c.constraint_name IS NULL;
-- 查看所有外键关系
SELECT table_schema, table_name, column_name,
referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL;上面"查找没有主键的表"已经用到了本文后面要讲的左连接 + IS NULL 套路，可回头对照阅读。
4.2 performance_schema（性能架构）
MySQL 5.5 引入，用于收集服务器运行时的性能事件，存储引擎为 PERFORMANCE_SCHEMA（内存表，重启清空）。
| 常用表 | 作用 |
|---|---|
events_statements_summary_by_digest |
按 SQL 指纹汇总执行次数、总耗时、平均耗时、扫描行数——找慢 SQL 最有效 |
events_waits_current |
当前等待事件 |
table_io_waits_summary_by_table |
各表的读写次数与耗时 |
file_summary_by_instance |
各文件的 IO 统计 |
threads |
线程列表（比 PROCESSLIST 信息更全） |
setup_instruments / setup_consumers |
采集项开关配置 |
-- TOP 10 平均耗时最长的 SQL
SELECT digest_text, count_star,
ROUND(avg_timer_wait/1000000000, 2) AS avg_ms,
ROUND(sum_timer_wait/1000000000, 2) AS total_ms,
sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;4.3 mysql 库（核心系统库）
真正落盘的系统库，存放权限、系统配置、时区、计划任务等关键数据。
| 关键表 | 作用 |
|---|---|
user |
用户账号、密码（password / authentication_string）与全局级权限 |
db |
库级权限 |
tables_priv |
表级权限 |
columns_priv |
列级权限 |
procs_priv |
存储过程/函数权限 |
proc / func |
存储过程、函数、自定义函数定义 |
event |
事件调度器（计划任务）定义 |
general_log / slow_log |
当 log_output=TABLE 时，通用日志与慢日志写入这两张表 |
time_zone* |
时区信息（需用 mysql_tzinfo_to_sql 导入） |
innodb_table_stats / innodb_index_stats |
InnoDB 持久化统计信息，影响优化器选择 |
SELECT host, user, password FROM mysql.user
WHERE host='127.0.0.1' AND user='root';
SELECT user, host FROM mysql.user;直接 UPDATE
mysql.user后必须FLUSH PRIVILEGES;才生效；使用GRANT/REVOKE/SET PASSWORD则自动生效。
4.4 sys 库
MySQL 5.7 引入，本质是基于 performance_schema 和 information_schema 的一组视图 + 存储过程，把晦涩的原始数据整理成人类可读的形式。
| 常用视图 | 作用 |
|---|---|
sys.schema_table_statistics |
各表读写延迟统计 |
sys.schema_unused_indexes |
从未被使用过的索引（可考虑删除） |
sys.schema_redundant_indexes |
冗余/重复索引 |
sys.statements_with_full_table_scans |
触发全表扫描的语句 |
sys.statements_with_temp_tables |
使用了临时表的语句 |
sys.innodb_lock_waits |
锁等待与阻塞源（定位死锁与卡顿） |
sys.session |
可读性更好的会话列表 |
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.innodb_lock_waits;五、内存结构与关键参数概述
MySQL（InnoDB）的内存可分为全局共享与会话私有两类，全局部分只分配一份，会话部分每个连接各分配一份——这是配置内存参数时最容易踩坑的地方（会话参数 × 连接数 = 实际内存占用）。
5.1 InnoDB 全局内存结构
| 结构 | 参数 | 作用 |
|---|---|---|
| Buffer Pool（缓冲池） | innodb_buffer_pool_size |
InnoDB 最重要的内存区域，缓存数据页与索引页（16KB 一页），采用改良 LRU（分 young / old 子链表）淘汰；读命中直接返回，写操作先改内存页（脏页）再由后台线程刷盘 |
| Buffer Pool 实例数 | innodb_buffer_pool_instances |
将缓冲池拆成多个实例减少互斥锁竞争，缓冲池 ≥ 1GB 时建议设为 4~8 |
| Change Buffer（变更缓冲） | innodb_change_buffer_max_size（占 Buffer Pool 百分比，默认 25） |
当修改的二级非唯一索引页不在内存中时，先把变更记录在 Change Buffer，避免立刻随机读磁盘；后续该页被读入时执行 merge。对写多读少的表提升明显；唯一索引因必须校验唯一性无法使用 |
| Log Buffer（日志缓冲） | innodb_log_buffer_size |
redo 日志的内存缓冲区，事务提交时按 innodb_flush_log_at_trx_commit 策略刷到 ib_logfile*；有大事务或 BLOB 写入时可调大到 16~64MB |
| Adaptive Hash Index | innodb_adaptive_hash_index |
对热点索引页自动建立哈希索引加速等值查找 |
| Doublewrite Buffer | innodb_doublewrite |
防止页断裂（partial page write），先写双写区再写数据文件 |
| Log Group / redo 文件 | innodb_log_file_size × innodb_log_files_in_group |
redo 总量决定崩溃恢复时间与写入峰值承载能力 |
5.2 会话私有内存（每连接一份）
| 结构 | 参数 | 作用 |
|---|---|---|
| 排序缓冲 | sort_buffer_size |
ORDER BY / GROUP BY 无法用索引时的排序（filesort）内存；不够则使用磁盘临时文件归并排序 |
| 连接缓冲 | join_buffer_size |
多表连接无法使用索引时（Block Nested-Loop Join）缓存驱动表数据；多表连接可能分配多份 |
| 顺序读缓冲 | read_buffer_size |
全表顺序扫描的读缓冲 |
| 随机读缓冲 | read_rnd_buffer_size |
排序后按行 ID 回表随机读的缓冲 |
| 内部临时表 | tmp_table_size / max_heap_table_size |
内存临时表上限，超过则转为磁盘临时表（Created_tmp_disk_tables 增长说明超限） |
| 网络包缓冲 | max_allowed_packet |
单个 SQL 或 BLOB 的最大字节数 |
| 表缓存 | table_open_cache（全局） |
缓存已打开的表句柄，与 open_files_limit 相关 |
5.3 innodb_buffer_pool_size 经验值
| 服务器角色 | 建议值 |
|---|---|
| 数据库专用物理机 / 独占虚拟机 | 物理内存的 50% ~ 75%（内存 ≥ 32GB 时可取上限 70%~75%） |
| 与应用混跑的机器 | 物理内存的 30% ~ 50%，务必给操作系统与应用留足余量 |
| 小内存测试机（≤ 4GB） | 512MB ~ 1GB，避免 OOM |
| 理想目标 | 略大于热数据 + 索引的总量；数据量小于内存时，可将全部数据装入缓冲池 |
-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
-- 命中率评估：应尽量接近 100%
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%# /etc/my.cnf 示例（16GB 内存的专用库）
[mysqld]
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=8
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=1
sort_buffer_size=2M
join_buffer_size=2M
tmp_table_size=64M
max_heap_table_size=64M
max_connections=500粗略估算总内存：
innodb_buffer_pool_size + 其他全局缓冲 + max_connections × (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + net_buffer_length)。会话缓冲切勿设成几十 MB，否则连接数一高就会 OOM。
六、多表关系与拆表设计
6.1 为什么要拆表：范式基础
如果把员工与部门信息塞进一张宽表：
+-------+----------+--------+----------+-----------+
| empno | ename | deptno | deptname | location |
+-------+----------+--------+----------+-----------+
| 1 | boss | 1 | manager | beijing |
| 2 | zhangsan | 2 | it | shenzhen |
| 5 | maliu | 2 | it | shenzhen |
| 6 | tianqi | 2 | it | shenzhen |
+-------+----------+--------+----------+-----------+会带来三类问题：
- 插入异常：新部门还没招人，就无法记录这个部门；
- 更新异常：部门 2 搬到 guangzhou，必须同时改多行，漏改即数据不一致；
- 删除异常：删掉部门 2 的最后一名员工，部门信息随之丢失。
解决办法是按范式拆表，用连接查询把数据"拼回来"。
| 范式 | 要求 | 违反示例 | 改造方式 |
|---|---|---|---|
| 1NF（第一范式） | 每个字段都是原子值，不可再分，也不能是重复组 | phone 列存 '138xxx,139xxx'；或设 phone1、phone2、phone3 |
拆出 employee_phone 子表，一行一个号码 |
| 2NF（第二范式） | 满足 1NF，且非主键字段完全依赖于整个主键，不能只依赖主键的一部分（消除部分依赖） | 表 order_item(order_id, product_id, qty, product_name)，主键为 (order_id, product_id)，而 product_name 只依赖 product_id |
把 product_name 移入 product 表 |
| 3NF（第三范式） | 满足 2NF，且非主键字段之间不存在传递依赖 | emp(empno, ename, deptno, deptname) 中 deptname 依赖 deptno，deptno 依赖 empno |
拆出 dept(deptno, deptname, location)，emp 只保留 deptno 外键 |
补充：BCNF、4NF 等更高范式在教学与常规业务建模中很少直接使用，掌握到 3NF 即可覆盖绝大部分场景。
6.2 反范式的取舍
严格 3NF 会导致查询需要连接很多张表，在高并发大数据量下反而变慢。因此实践中常做适度反范式（冗余）：
| 手段 | 例子 | 收益 | 代价 |
|---|---|---|---|
| 冗余字段 | 订单表冗余 user_name、product_name 快照 |
列表页无需 JOIN；保留下单时刻的历史值 | 需要维护一致性（通常业务上"快照"本就不该跟着变） |
| 汇总字段 | 部门表冗余 emp_count、sal_total |
避免每次 COUNT/SUM 全表聚合 |
增删员工时要同步更新，需事务或定时校正 |
| 宽表 / 物化视图 | 报表库把多表打平成一张分析宽表 | 报表查询极快 | 需要 ETL 同步，存在延迟 |
取舍原则：先按 3NF 建模，再针对被证实的性能瓶颈做局部反范式，并明确"谁负责维护冗余数据的一致性"。反范式不是不做设计的借口。
6.3 表间关系
| 关系 | 说明 | 实现方式 | 例子 |
|---|---|---|---|
| 一对一（1:1） | A 的一行最多对应 B 的一行 | 在任一方加唯一外键，或两表共用同一主键（主键即外键） | 用户 user 与用户详情 user_profile；员工与员工档案 |
| 一对多（1:N） | A 的一行对应 B 的多行，B 的一行只对应 A 的一行 | 在多的一方（N 方）加外键指向"一"方主键 | 部门 dept 与员工 emp：一个部门多名员工，emp.deptno 是外键 |
| 多对多（M:N） | 双方都可对应多行 | 必须引入中间表（关联表 / 桥表），中间表持有两个外键，通常以两者组合为主键 | 学生与课程：student、course、student_course(sid, cid, score) |
| 自关联 | 同一张表内的行之间存在层级/上下级关系 | 表内加指向自身主键的外键 | emp.managerno 指向 emp.empno；地区表的 parent_id |
一对一为什么要拆表：把不常用的大字段（简历、备注、图片路径）拆到副表，主表更"窄"，一页能装更多行，扫描更快。
多对多中间表示例：
CREATE TABLE student (
sid INT PRIMARY KEY,
sname VARCHAR(20)
);
CREATE TABLE course (
cid INT PRIMARY KEY,
cname VARCHAR(20)
);
-- 中间表：两个外键 + 组合主键，还可携带关系自身的属性（如成绩）
CREATE TABLE student_course (
sid INT,
cid INT,
score DECIMAL(5,2),
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES student(sid),
FOREIGN KEY (cid) REFERENCES course(cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;6.4 外键约束的使用与取舍
-- 建表时声明
CREATE TABLE emp_fk (
empno INT PRIMARY KEY,
ename VARCHAR(20),
deptno TINYINT,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
-- 已有表追加外键
ALTER TABLE emp_fk
ADD CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno);
-- 删除外键（注意删的是约束名，不是列名）
ALTER TABLE emp_fk DROP FOREIGN KEY fk_emp_dept;
-- 查看外键定义
SHOW CREATE TABLE emp_fk;
-- 临时关闭外键检查（导数据时常用，务必记得恢复）
SET FOREIGN_KEY_CHECKS = 0;
-- ... 批量导入 ...
SET FOREIGN_KEY_CHECKS = 1;级联动作：
| 动作 | 含义 |
|---|---|
RESTRICT / NO ACTION |
默认。父表行被引用时禁止删除/更新 |
CASCADE |
父表删除/更新时，子表相应行一起删除/更新 |
SET NULL |
父表删除/更新时，子表外键列置 NULL（该列必须允许为 NULL） |
使用前提与限制：
- 只有 InnoDB 支持外键，MyISAM 语法能写但被忽略；
- 父表被引用列必须有索引（通常是主键或唯一键），子表外键列会自动建索引；
- 两侧列的数据类型、长度、有无符号、字符集必须一致，否则报
errno: 150。
| 观点 | 理由 |
|---|---|
| 支持使用外键 | 数据库层面强制保证参照完整性，任何入口（脚本、手工 SQL、多个应用）都无法写出脏数据；自动建索引；关系在 schema 中自文档化 |
| 反对使用外键（互联网大厂常见） | 每次写操作要检查父表并加锁，高并发下增加开销与死锁概率；分库分表后跨库外键无法实现；批量导入、归档、DDL 变更（如 gh-ost / pt-osc）都受外键干扰；级联删除可能引发意料之外的大范围删除 |
折中做法：内部管理系统、数据量不大的业务用外键；高并发互联网核心表不用外键，改由应用层 + 事务 + 定期一致性校验脚本保证。但无论是否声明外键，逻辑上的关联列都应该建索引，否则连接查询必然慢。
七、示例数据准备
以下建表与数据是本文所有查询演示的统一基础，请先完整执行一遍。
7.1 员工表 emp
CREATE DATABASE IF NOT EXISTS aaa;
USE aaa;
create table emp (
empno int(1), ename char(20), sex char(1),
birthday date, hiredate date,
sal decimal(10,2), deptno tinyint(1), managerno tinyint
);另一种等价写法（早期章节使用的结构，字段更完整地写成多行）：
create table emp (
empno int(11),
ename varchar(20),
sex char(1),
birthday date,
hiredate date,
sal decimal(10,2),
deptno tinyint(1),
managerno int(11)
) engine=innodb default charset=utf8;表结构：
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| empno | int(11) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | tinyint(1) | YES | | NULL | |
| managerno | int(11) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+插入数据：
insert into emp values
(1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),
(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),
(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),
(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),
(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),
(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),
(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),
(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),
(9,'mm','f','1990-06-08','2008-09-13','4000','4','4');也可写成脚本批量导入（注意第 1 行 boss 的 hiredate 在脚本版本中为 1996-01-01）：
# vim emp.sql
#!/bin/bash
insert into aaa.emp values
(1,'boss','m','1964-08-08','1996-01-01','20000','1','1'),
(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),
(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),
(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),
(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),
(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),
(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),
(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),
(9,'mm','f','1990-06-08','2008-09-13','4000','4','4');导入方法：
# 方法 1：命令行重定向
mysql -p < emp.sql-- 方法 2：source 命令
source /path/emp.sql;
SOURCE /path/emp.sql;导入后数据：
+-------+----------+------+------------+------------+----------+--------+-----------+
| empno | ename | sex | birthday | hiredate | sal | deptno | managerno |
+-------+----------+------+------------+------------+----------+--------+-----------+
| 1 | boss | m | 1964-08-08 | 1995-01-01 | 20000.00 | 1 | 1 |
| 2 | zhangsan | m | 1967-04-05 | 1995-04-11 | 15000.00 | 2 | 1 |
| 3 | lisi | f | 1973-01-28 | 1998-11-21 | 13000.00 | 3 | 1 |
| 4 | wangwu | f | 1975-06-03 | 1999-12-12 | 12000.00 | 4 | 1 |
| 5 | maliu | m | 1982-08-18 | 2001-07-03 | 8000.00 | 2 | 2 |
| 6 | tianqi | f | 1983-02-15 | 2002-11-01 | 7000.00 | 2 | 2 |
| 7 | mark | m | 1984-08-12 | 2003-10-02 | 6500.00 | 3 | 3 |
| 8 | john | m | 1985-09-14 | 2005-04-03 | 6000.00 | 3 | 3 |
| 9 | mm | f | 1990-06-08 | 2008-09-13 | 4000.00 | 4 | 4 |
+-------+----------+------+------------+------------+----------+--------+-----------+7.2 部门表 dept 与 emp1
源文档中出现了两份部门表，字段名略有不同，两份都保留：一份叫 dept(deptno, deptname, location)，一份叫 emp1(deptno, dname, loc)。后续示例会分别使用。
-- 部门表 dept，deptno 与 emp 表对应
create table dept (
deptno tinyint(1),
deptname varchar(30),
location varchar(50)
);
insert into dept values
(1,'manager','beijing'),
(2,'it','shenzhen'),
(3,'sale','shanghai'),
(4,'services','guangzhou');-- 部门表另一版本 emp1
create table emp1 (
deptno tinyint(1), dname char(20), loc char(5)
);
insert into emp1 values
('1','cook','bj'),('2','message','sz'),
('3','cook','gz'),('4','secretary','lz');为了演示外连接会用到"没有员工的部门"和"没有部门的员工"，再补两条数据（本文外连接章节使用）：
-- 新增一个空部门（暂时无人）
insert into dept values (5,'research','hangzhou');
-- 新增一个还未分配部门的员工（deptno 为 NULL）
insert into emp values
(10,'newbie','m','1995-03-03','2020-05-06','5000',NULL,NULL);7.3 别名演示用的 name / age 表
create table name(id int, name varchar(10));
create table age(id int, age int);
insert into name values
(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
insert into age values
(1,'18'),(2,'23'),(3,'55');八、连接查询基础
8.1 笛卡尔积与交叉连接 CROSS JOIN
不写连接条件时，两表的每一行都会与对方的每一行组合，结果行数 = 左表行数 × 右表行数，这就是笛卡尔积（Cartesian Product）。
-- 以下三种写法在 MySQL 中完全等价，都产生笛卡尔积
SELECT * FROM emp, dept;
SELECT * FROM emp CROSS JOIN dept;
SELECT * FROM emp JOIN dept; -- MySQL 中 JOIN 不带 ON 等同于 CROSS JOINSELECT COUNT(*) FROM emp, dept;+----------+
| COUNT(*) |
+----------+
| 50 | -- 10 行 emp × 5 行 dept
+----------+小规模示例更直观：
SELECT * FROM name CROSS JOIN age;+------+----------+------+------+
| id | name | id | age |
+------+----------+------+------+
| 1 | zhangsan | 1 | 18 |
| 2 | lisi | 1 | 18 |
| 3 | wangwu | 1 | 18 |
| 1 | zhangsan | 2 | 23 |
| 2 | lisi | 2 | 23 |
| 3 | wangwu | 2 | 23 |
| 1 | zhangsan | 3 | 55 |
| 2 | lisi | 3 | 55 |
| 3 | wangwu | 3 | 55 |
+------+----------+------+------+
9 rows in set要点：
- 笛卡尔积绝大多数情况下是忘写连接条件的 bug：两张 10 万行的表相乘会产生 100 亿行，直接把库拖死。
- 但它也有正当用途：生成组合数据，例如给每个部门都生成 12 个月的空壳行用于报表补零。
-- 正当用途：每个部门 × 每个性别，生成完整的统计维度框架
SELECT d.deptname, s.sex
FROM dept d
CROSS JOIN (SELECT 'm' AS sex UNION ALL SELECT 'f') s
ORDER BY d.deptno, s.sex;
CROSS JOIN在标准 SQL 中不允许带ON，但 MySQL 把CROSS JOIN、INNER JOIN、JOIN视为同义词，加了ON就变成内连接。为了可读性，要做笛卡尔积就明确写CROSS JOIN，要做内连接就写INNER JOIN ... ON。
8.2 内连接（INNER JOIN）
只返回两表连接条件都满足的行，不匹配的行全部丢弃。
标准写法（推荐）：
select a.ename, a.sal, b.deptno, b.loc
from emp a
inner join emp1 b on a.deptno = b.deptno;说明：
emp a、emp1 b：为表指定别名；- 从
a读取ename、sal，从b读取deptno、loc； a.deptno = b.deptno：通过两表重叠字段进行关联。
+----------+----------+--------+------+
| ename | sal | deptno | loc |
+----------+----------+--------+------+
| boss | 20000.00 | 1 | bj |
| zhangsan | 15000.00 | 2 | sz |
| lisi | 13000.00 | 3 | gz |
| wangwu | 12000.00 | 4 | lz |
| maliu | 8000.00 | 2 | sz |
| tianqi | 7000.00 | 2 | sz |
| mark | 6500.00 | 3 | gz |
| john | 6000.00 | 3 | gz |
| mm | 4000.00 | 4 | lz |
+----------+----------+--------+------+使用 dept 表的等价写法：
SELECT a.ename, a.sal, b.deptno, b.loc
FROM emp a
INNER JOIN dept b ON a.deptno = b.deptno;
-- 若部门表字段名为 deptname / location
SELECT a.ename, a.sal, b.deptname, b.location
FROM emp a
INNER JOIN dept b ON a.deptno = b.deptno;INNER 可省略，JOIN 等价于 INNER JOIN：
SELECT e.ename, d.deptname
FROM emp e JOIN dept d ON e.deptno = d.deptno;当两表关联列同名时，还可以用 USING，它会自动合并同名列（结果中该列只出现一次）：
SELECT ename, deptname, deptno
FROM emp INNER JOIN dept USING (deptno);注意：
NATURAL JOIN（自然连接）会自动用所有同名列做等值连接，一旦表结构增加同名列，语义会悄悄改变，生产禁用。
8.3 WHERE 隐式连接（SQL-89 旧写法）
在 FROM 后用逗号列出多张表，把连接条件写进 WHERE：
-- 方式 1：使用 AS 关键字
select a.id, a.name, a.address, b.math, b.english, b.chinese
from tb_demo065 AS a, tb_demo065_tel AS b
where a.id = b.id;
-- 方式 2：表名后直接加别名
select a.id, a.name, a.address, b.math, b.english, b.chinese
from tb_demo065 a, tb_demo065_tel b
where a.id = b.id;-- 方式一：直接空格别名
SELECT a.id, a.name, b.age
FROM tb_demo065 a, tb_demo065_tel b
WHERE a.id = b.id;
-- 方式二：AS 关键字
SELECT a.id, a.name, b.age
FROM tb_demo065 AS a, tb_demo065_tel AS b
WHERE a.id = b.id;用 name / age 两张表实际演示：
select a.id, a.name, b.age
from name AS a, age AS b
where a.id = b.id;+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 23 |
| 3 | wangwu | 55 |
+------+----------+------+员工与部门的隐式连接写法：
SELECT e.ename, e.sal, d.deptname
FROM emp e, dept d
WHERE e.deptno = d.deptno;两种写法对比
| 维度 | INNER JOIN ... ON（SQL-92，推荐） |
FROM a, b WHERE（SQL-89，旧） |
|---|---|---|
| 执行效率 | 完全相同，优化器会转换成同一个执行计划 | 完全相同 |
| 可读性 | 连接条件（ON）与筛选条件（WHERE）分离，一眼看清表之间的关系 | 连接条件和业务筛选混在 WHERE 里，表一多就难以分辨 |
| 漏写条件的后果 | 漏写 ON 语法上很显眼，容易发现 |
漏写一个等值条件就静默产生笛卡尔积，事故高发 |
| 外连接支持 | 支持 LEFT / RIGHT JOIN |
不支持标准外连接（Oracle 的 (+)、MySQL 早期的 *= 均已废弃） |
| 语义一致性 | 内外连接写法统一 | 只能写内连接 |
结论：统一使用
INNER JOIN ... ON。旧写法只需能读懂（大量老系统与教材仍在使用）。
8.4 多表连续连接
连接可以链式书写，每个 JOIN 配一个 ON，从左到右依次与前面的中间结果连接：
-- 三表连接：员工 → 部门 → 部门所在城市信息（假设有 city 表）
CREATE TABLE city (
cname VARCHAR(50),
province VARCHAR(50)
);
INSERT INTO city VALUES
('beijing','beijing'),('shenzhen','guangdong'),
('shanghai','shanghai'),('guangzhou','guangdong');
SELECT e.ename, e.sal, d.deptname, d.location, c.province
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
INNER JOIN city c ON d.location = c.cname
ORDER BY e.sal DESC;-- 学生 → 选课中间表 → 课程（多对多的标准三表连接）
SELECT s.sname, c.cname, sc.score
FROM student s
INNER JOIN student_course sc ON s.sid = sc.sid
INNER JOIN course c ON c.cid = sc.cid
ORDER BY s.sname, c.cname;要点：
- 连接是二元操作，逐步进行：
((emp ⋈ dept) ⋈ city)； - 优化器可以自行调整实际连接顺序（除
STRAIGHT_JOIN强制外），书写顺序不代表执行顺序； - 每增加一张表都要确认连接条件完整，否则中间结果会膨胀。
8.5 表别名
SELECT a.ename, b.deptname FROM emp AS a, dept AS b WHERE a.deptno = b.deptno;
SELECT a.ename, b.deptname FROM emp a, dept b WHERE a.deptno = b.deptno;别名使用注意事项：
- 别名通常是缩短的表名，用于在连接中引用表中的特定列；若多个表存在同名列，必须用表名或别名限定列名。
- 一旦定义了表别名，就不能再使用原表名（写
emp.ename会报Unknown column 'emp.ename' in 'field list'）。 AS关键字对表别名可省略，对列别名建议保留以提升可读性。- 自连接（同一张表连接自己）必须使用别名区分两个"副本"。
- 别名一般用有意义的缩写（
e、d、sc）而不是a、b，可维护性更好。
-- 列别名同样可以带 AS 或省略
SELECT ename AS 姓名, sal 工资, sal*12 AS 年薪 FROM emp;8.6 连接条件与筛选条件的区别：ON 与 WHERE（重点）
这是多表查询最容易出错的地方。理解它需要先记住 SQL 的逻辑执行顺序：
FROM / JOIN → ON（生成连接结果） → 外连接补 NULL 行 → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT关键点：ON 在"补 NULL 行"之前生效，WHERE 在之后生效。
情形一：内连接中，ON 与 WHERE 等价
内连接不会补 NULL 行，条件放 ON 还是 WHERE，结果完全一样，优化器生成的执行计划也一样：
-- 写法 A：条件放 ON
SELECT e.ename, e.sal, d.deptname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno AND e.sal > 10000;
-- 写法 B：条件放 WHERE —— 与 A 结果完全相同
SELECT e.ename, e.sal, d.deptname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
WHERE e.sal > 10000;两者都返回：
+----------+----------+----------+
| ename | sal | deptname |
+----------+----------+----------+
| boss | 20000.00 | manager |
| zhangsan | 15000.00 | it |
| lisi | 13000.00 | sale |
| wangwu | 12000.00 | services |
+----------+----------+----------+规范建议：即便等价，也应把表间关联条件写在 ON，把业务筛选条件写在 WHERE，语义清晰。
情形二：外连接中，ON 与 WHERE 结果完全不同（关键差异）
以"列出所有部门及其员工，没有员工的部门也要显示"为例：
-- 写法 A：筛选条件放在 ON —— 保留所有部门
SELECT d.deptname, e.ename, e.sal
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno AND e.sal > 10000;+----------+----------+----------+
| deptname | ename | sal |
+----------+----------+----------+
| manager | boss | 20000.00 |
| it | zhangsan | 15000.00 |
| sale | lisi | 13000.00 |
| services | wangwu | 12000.00 |
| research | NULL | NULL | <-- 空部门被保留
+----------+----------+----------+-- 写法 B：筛选条件放在 WHERE —— 外连接被"降级"为内连接
SELECT d.deptname, e.ename, e.sal
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.sal > 10000;+----------+----------+----------+
| deptname | ename | sal |
+----------+----------+----------+
| manager | boss | 20000.00 |
| it | zhangsan | 15000.00 |
| sale | lisi | 13000.00 |
| services | wangwu | 12000.00 |
+----------+----------+----------+
-- research 部门消失了！原因：LEFT JOIN 为 research 补出的那一行中 e.sal 是 NULL，而 NULL > 10000 的结果是 UNKNOWN（不是 TRUE），WHERE 便把这一行过滤掉了——外连接退化成了内连接。
再看一个更隐蔽的例子：
-- 想"排除某类员工，但保留所有部门"，写成 WHERE 就错了
SELECT d.deptname, e.ename
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.sex = 'm'; -- 错：空部门与女员工部门都会异常丢失
-- 正确：把针对右表的筛选下推到 ON
SELECT d.deptname, e.ename
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno AND e.sex = 'm';记忆口诀与判断规则
| 条件针对的对象 | 应写在 | 理由 |
|---|---|---|
表间关联关系（a.x = b.y） |
ON |
这是连接语义本身 |
| 内连接的任意筛选条件 | WHERE（放 ON 也对） |
等价，但 WHERE 更清晰 |
| 外连接中对被保留表（LEFT JOIN 的左表）的筛选 | WHERE |
先缩小主表范围，不影响补 NULL 逻辑 |
| 外连接中对可空表（LEFT JOIN 的右表）的筛选 | 必须 ON |
放 WHERE 会把补出的 NULL 行过滤掉，外连接失效 |
| 外连接中故意要"只留下没匹配上的行" | WHERE 右表列 IS NULL |
这是"反连接"套路，见下一章 |
一句话：LEFT JOIN 之后，凡是对右表列的条件（除
IS NULL）都应写在 ON 里。
九、外连接
外连接（OUTER JOIN）在内连接结果的基础上，保留某一侧表中没有匹配到的行，缺失的对方列用 NULL 填充。OUTER 关键字可省略。
9.1 左连接 LEFT JOIN
以左表为主，左表所有行都出现在结果里；右表无匹配则右侧列全为 NULL。
-- 列出所有部门及其员工，没有员工的部门也要列出
SELECT d.deptno, d.deptname, e.ename, e.sal
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
ORDER BY d.deptno, e.sal DESC;+--------+----------+----------+----------+
| deptno | deptname | ename | sal |
+--------+----------+----------+----------+
| 1 | manager | boss | 20000.00 |
| 2 | it | zhangsan | 15000.00 |
| 2 | it | maliu | 8000.00 |
| 2 | it | tianqi | 7000.00 |
| 3 | sale | lisi | 13000.00 |
| 3 | sale | mark | 6500.00 |
| 3 | sale | john | 6000.00 |
| 4 | services | wangwu | 12000.00 |
| 4 | services | mm | 4000.00 |
| 5 | research | NULL | NULL | <-- 补 NULL 行
+--------+----------+----------+----------+-- 列出所有员工及其部门，未分配部门的员工也要列出
SELECT e.empno, e.ename, e.deptno, d.deptname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
ORDER BY e.empno;+-------+----------+--------+----------+
| empno | ename | deptno | deptname |
+-------+----------+--------+----------+
| 1 | boss | 1 | manager |
| 2 | zhangsan | 2 | it |
| 3 | lisi | 3 | sale |
| 4 | wangwu | 4 | services |
| 5 | maliu | 2 | it |
| 6 | tianqi | 2 | it |
| 7 | mark | 3 | sale |
| 8 | john | 3 | sale |
| 9 | mm | 4 | services |
| 10 | newbie | NULL | NULL | <-- deptno 为 NULL，无法匹配
+-------+----------+--------+----------+关于 NULL 行的产生，务必记住三点：
- 补出的 NULL 行不是表里真实存在的 NULL 值，而是连接过程"造"出来的；
- 这些 NULL 行会影响聚合：
COUNT(e.ename)不计 NULL，而COUNT(*)会把补出的行算作 1； - 对 NULL 的判断只能用
IS NULL/IS NOT NULL，= NULL永远不成立。
-- 陷阱：统计每个部门人数，空部门应为 0
SELECT d.deptname, COUNT(*) AS wrong_cnt, -- 空部门算成了 1
COUNT(e.empno) AS right_cnt -- 空部门正确为 0
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.deptname;+----------+-----------+-----------+
| deptname | wrong_cnt | right_cnt |
+----------+-----------+-----------+
| manager | 1 | 1 |
| it | 3 | 3 |
| sale | 3 | 3 |
| services | 2 | 2 |
| research | 1 | 0 | <-- COUNT(*) 把补出的 NULL 行算成 1 人
+----------+-----------+-----------+9.2 右连接 RIGHT JOIN
以右表为主，语义与左连接镜像对称。
SELECT e.ename, e.sal, d.deptno, d.deptname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
ORDER BY d.deptno;结果与 9.1 的第一个左连接示例相同（research 部门保留、ename 为 NULL）。
任何右连接都可以改写成左连接，只需交换两表位置：
-- 下面两句完全等价
SELECT ... FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
SELECT ... FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno;工程建议：统一只用 LEFT JOIN。人的阅读习惯是从左往右，"以左表为准"最直观；多表连续连接中混用 LEFT / RIGHT 极易把自己绕晕。
9.3 用左连接找"没有匹配记录"的行（经典套路）
模式：A LEFT JOIN B ON 条件 WHERE B.主键 IS NULL，也叫反连接（Anti Join）。原理：左连接保留了 A 的全部行，其中匹配不上的行 B 侧全为 NULL，用 B.主键 IS NULL 把它们精确筛出来（主键本身不可能为 NULL，所以为 NULL 只能是"补出来的行"）。
-- 1. 找出没有任何员工的部门
SELECT d.deptno, d.deptname
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;+--------+----------+
| deptno | deptname |
+--------+----------+
| 5 | research |
+--------+----------+-- 2. 找出没有归属部门的员工（含 deptno 为 NULL 或指向不存在的部门）
SELECT e.empno, e.ename, e.deptno
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno IS NULL;+-------+--------+--------+
| empno | ename | deptno |
+-------+--------+--------+
| 10 | newbie | NULL |
+-------+--------+--------+-- 3. 找出没有选任何课程的学生
SELECT s.sid, s.sname
FROM student s
LEFT JOIN student_course sc ON s.sid = sc.sid
WHERE sc.sid IS NULL;
-- 4. 找出没被任何学生选的课程
SELECT c.cid, c.cname
FROM course c
LEFT JOIN student_course sc ON c.cid = sc.cid
WHERE sc.cid IS NULL;
-- 5. 清理孤儿数据：删除部门表中已不存在的部门下的员工（关联删除）
DELETE e FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno IS NULL AND e.deptno IS NOT NULL;三种等价写法及选择建议：
-- 写法 1：LEFT JOIN + IS NULL（推荐，MySQL 5.x 下通常最稳）
SELECT d.deptname FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;
-- 写法 2：NOT EXISTS（语义最清晰，性能与写法 1 接近）
SELECT d.deptname FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);
-- 写法 3：NOT IN（注意 NULL 陷阱！子查询结果含 NULL 时永远返回空集）
SELECT d.deptname FROM dept d
WHERE d.deptno NOT IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL);常见错误：把 IS NULL 条件误写进 ON：
-- 错：条件写在 ON 里，NULL 判断在补行之前执行，等于没过滤
SELECT d.deptname FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno AND e.empno IS NULL; -- 返回全部部门记住区别：筛掉右表某些行 → 写 ON；只要没匹配上的左表行 → 写 WHERE ... IS NULL。
9.4 全外连接在 MySQL 中的模拟
标准 SQL 提供 FULL OUTER JOIN（两侧不匹配的行都保留），Oracle、PostgreSQL、SQL Server 都支持，但 MySQL / MariaDB 不支持：
-- MySQL 报语法错误
SELECT * FROM dept d FULL OUTER JOIN emp e ON d.deptno = e.deptno;用 LEFT JOIN UNION RIGHT JOIN 模拟：
-- 方案 1：UNION（自动去重，把两侧重复的内连接部分合并）
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
UNION
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno;+--------+----------+-------+----------+
| deptno | deptname | empno | ename |
+--------+----------+-------+----------+
| 1 | manager | 1 | boss |
| 2 | it | 2 | zhangsan |
| 2 | it | 5 | maliu |
| 2 | it | 6 | tianqi |
| 3 | sale | 3 | lisi |
| 3 | sale | 7 | mark |
| 3 | sale | 8 | john |
| 4 | services | 4 | wangwu |
| 4 | services | 9 | mm |
| 5 | research | NULL | NULL | <-- 左侧独有
| NULL | NULL | 10 | newbie | <-- 右侧独有
+--------+----------+-------+----------+-- 方案 2：UNION ALL + 只取右侧独有部分（避免 UNION 的去重排序开销，效率更高）
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
UNION ALL
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno
WHERE d.deptno IS NULL;方案 2 更优：
UNION去重需要额外的临时表与排序；方案 2 用WHERE d.deptno IS NULL只补上"右表独有"的行，天然无重复。注意如果表中存在真实为 NULL 的连接列值，需换用其他可靠的非空列做 IS NULL 判断。
9.5 自连接（SELF JOIN）
同一张表连接自己，用别名把它当成两张不同的表。适用于表内行与行之间存在关系的场景。
经典案例一：员工与上级领导
emp.managerno 存放上级的 empno，属于自关联：
-- 查询每个员工及其领导姓名（内连接：无领导的员工不显示）
SELECT e.empno, e.ename AS 员工, e.sal AS 员工工资,
m.ename AS 领导, m.sal AS 领导工资
FROM emp e
INNER JOIN emp m ON e.managerno = m.empno
ORDER BY e.empno;+-------+----------+----------+----------+----------+
| empno | 员工 | 员工工资 | 领导 | 领导工资 |
+-------+----------+----------+----------+----------+
| 1 | boss | 20000.00 | boss | 20000.00 |
| 2 | zhangsan | 15000.00 | boss | 20000.00 |
| 3 | lisi | 13000.00 | boss | 20000.00 |
| 4 | wangwu | 12000.00 | boss | 20000.00 |
| 5 | maliu | 8000.00 | zhangsan | 15000.00 |
| 6 | tianqi | 7000.00 | zhangsan | 15000.00 |
| 7 | mark | 6500.00 | lisi | 13000.00 |
| 8 | john | 6000.00 | lisi | 13000.00 |
| 9 | mm | 4000.00 | wangwu | 12000.00 |
+-------+----------+----------+----------+----------+-- 用左连接保留"没有领导"的人（如 newbie，managerno 为 NULL）
SELECT e.ename AS 员工, IFNULL(m.ename, '无上级') AS 领导
FROM emp e
LEFT JOIN emp m ON e.managerno = m.empno
ORDER BY e.empno;-- 找出工资比自己领导还高的员工
SELECT e.ename AS 员工, e.sal AS 员工工资,
m.ename AS 领导, m.sal AS 领导工资
FROM emp e
INNER JOIN emp m ON e.managerno = m.empno
WHERE e.sal > m.sal;-- 统计每位领导手下的人数（不含自己）
SELECT m.ename AS 领导, COUNT(*) AS 下属人数
FROM emp e
INNER JOIN emp m ON e.managerno = m.empno
WHERE e.empno <> m.empno
GROUP BY m.empno, m.ename
ORDER BY 下属人数 DESC;+----------+----------+
| 领导 | 下属人数 |
+----------+----------+
| boss | 3 |
| zhangsan | 2 |
| lisi | 2 |
| wangwu | 1 |
+----------+----------+经典案例二：层级数据（多级递归）
CREATE TABLE region (
id INT PRIMARY KEY,
name VARCHAR(30),
parent_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO region VALUES
(1,'中国',NULL),
(2,'广东省',1),(3,'江苏省',1),
(4,'深圳市',2),(5,'广州市',2),(6,'南京市',3),
(7,'南山区',4),(8,'福田区',4);
-- 三级层级：省 - 市 - 区（每多一级就多连接一次自己）
SELECT p.name AS 省, c.name AS 市, d.name AS 区
FROM region p
LEFT JOIN region c ON c.parent_id = p.id
LEFT JOIN region d ON d.parent_id = c.id
WHERE p.parent_id = 1;+--------+--------+--------+
| 省 | 市 | 区 |
+--------+--------+--------+
| 广东省 | 深圳市 | 南山区 |
| 广东省 | 深圳市 | 福田区 |
| 广东省 | 广州市 | NULL |
| 江苏省 | 南京市 | NULL |
+--------+--------+--------+自连接要点：
- 必须给两个副本起不同别名，否则报
Not unique table/alias； - 自连接同样区分内/外连接：要保留"没有上级"的行就用
LEFT JOIN； - 层级不确定的无限递归查询：MySQL 8.0 可用递归 CTE（
WITH RECURSIVE），5.7 及以前只能逐级 JOIN、在应用层循环，或改用路径字段（如path='1/2/4/'）配合LIKE '1/2/%'查询整棵子树。
十、集合运算：UNION 与 UNION ALL
连接是横向拼接（加列），集合运算是纵向拼接（加行）。
10.1 UNION 与 UNION ALL 的区别
| 特性 | UNION |
UNION ALL |
|---|---|---|
| 去重 | 自动去除完全重复的行 | 不去重，全部保留 |
| 实现方式 | 需要临时表 + 排序/哈希去重 | 直接追加，几乎无额外开销 |
| 性能 | 数据量大时明显较慢 | 快 |
| 结果顺序 | 不保证（去重过程可能改变顺序） | 通常保持各分支先后顺序，但仍不保证 |
-- UNION：去重
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| 5 |
+--------+-- UNION ALL：不去重，行数 = 两个分支之和
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;共 10 + 5 = 15 行，其中 deptno=2 出现 4 次（emp 中 3 次 + dept 中 1 次）性能建议：能确定两个结果集不会重复，或业务上允许重复时，一律用
UNION ALL。UNION的去重开销随数据量急剧上升（大结果集会落磁盘临时表），而很多场景（如"男员工 + 女员工"这种天然互斥的分支）根本不可能产生重复行。
10.2 列数与类型要求
- 各分支的列数必须完全相同，否则报错：
-- 错误示例
SELECT ename, sal FROM emp
UNION
SELECT deptname FROM dept;
-- ERROR 1222 (21000): The used SELECT statements have a different number of columns- 对应位置的列类型应该兼容。MySQL 相当宽松，会做隐式类型转换，但语义会变得混乱，应尽量对齐：
-- 可以执行，但 sal 与 deptname 混在一列里，毫无意义
SELECT ename, sal FROM emp
UNION ALL
SELECT deptname, location FROM dept;- 结果集的列名取第一个分支的列名（或别名）。要控制列名就在第一个分支里写别名：
SELECT ename AS name, '员工' AS type FROM emp
UNION ALL
SELECT deptname AS name, '部门' AS type FROM dept;+----------+------+
| name | type |
+----------+------+
| boss | 员工 |
| zhangsan | 员工 |
| ... | ... |
| manager | 部门 |
| it | 部门 |
| sale | 部门 |
| services | 部门 |
| research | 部门 |
+----------+------+- 列数不足时可以用常量或
NULL补齐：
SELECT empno, ename, sal FROM emp WHERE deptno = 2
UNION ALL
SELECT deptno, deptname, NULL FROM dept WHERE deptno = 2;10.3 配合 ORDER BY 与 LIMIT
关键规则：ORDER BY / LIMIT 直接写在最后，作用于合并后的整个结果集；若要作用于单个分支，必须用括号把该分支包起来。
-- 正确：对合并后的总结果排序
SELECT ename, sal FROM emp WHERE sex = 'm'
UNION ALL
SELECT ename, sal FROM emp WHERE sex = 'f'
ORDER BY sal DESC
LIMIT 5;-- 分支内排序 + 限制：必须加括号，且分支内的 ORDER BY 需配合 LIMIT 才有意义
(SELECT ename, sal FROM emp WHERE sex = 'm' ORDER BY sal DESC LIMIT 3)
UNION ALL
(SELECT ename, sal FROM emp WHERE sex = 'f' ORDER BY sal DESC LIMIT 3);+----------+----------+
| ename | sal |
+----------+----------+
| boss | 20000.00 | -- 男性 TOP 3
| zhangsan | 15000.00 |
| maliu | 8000.00 |
| lisi | 13000.00 | -- 女性 TOP 3
| wangwu | 12000.00 |
| tianqi | 7000.00 |
+----------+----------+要点：
- 分支内单独的
ORDER BY（不带 LIMIT）会被优化器直接忽略，因为最终顺序由外层决定； - 外层
ORDER BY只能引用第一个分支的列名/别名； - 用括号包裹分支时，
UNION前后的括号必须成对，MySQL 允许(SELECT ...) UNION (SELECT ...)。
给合并结果加序号或标签的常用技巧：
-- 按类型分组展示，并保证同类聚在一起
SELECT 1 AS grp, ename AS name, sal AS val FROM emp WHERE sal >= 10000
UNION ALL
SELECT 2 AS grp, ename, sal FROM emp WHERE sal < 10000
ORDER BY grp, val DESC;10.4 MySQL 中集合运算的缺失与模拟
| 标准 SQL | MySQL 5.x/8.0 支持情况 | 模拟方案 |
|---|---|---|
UNION / UNION ALL |
支持 | — |
INTERSECT（交集） |
5.x / 8.0 不支持（MySQL 8.0.31+ 才支持） | 用 INNER JOIN 或 IN / EXISTS |
EXCEPT / MINUS（差集） |
5.x / 8.0 不支持（8.0.31+ 支持） | 用 LEFT JOIN ... IS NULL 或 NOT IN / NOT EXISTS |
-- 模拟 INTERSECT：既在 emp 中出现又在 dept 中存在的 deptno
SELECT DISTINCT e.deptno FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;
-- 模拟 EXCEPT：dept 有但 emp 中没有的 deptno
SELECT d.deptno FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.deptno IS NULL;十一、子查询
子查询（Subquery）是嵌套在另一条 SQL 语句中的 SELECT。按返回结果的形状分为标量、列、行、表四类；按是否引用外层分为非关联与关联子查询。
11.1 标量子查询（返回单行单列）
可以出现在任何期望单个值的位置：WHERE、SELECT 列表、HAVING。
-- 查出比 wangwu 工资高的人的姓名和工资
select ename, sal
from emp
where sal > (select sal from emp where ename='wangwu');
SELECT ename, sal FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='wangwu');+----------+----------+
| ename | sal |
+----------+----------+
| boss | 20000.00 |
| zhangsan | 15000.00 |
| lisi | 13000.00 |
+----------+----------+-- 工资高于全公司平均工资的员工
SELECT ename, sal FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
-- 工资最高的员工
SELECT ename, sal FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
-- 放在 SELECT 列表中：每个人的工资与平均工资的差额
SELECT ename, sal,
(SELECT ROUND(AVG(sal),2) FROM emp) AS 平均工资,
ROUND(sal - (SELECT AVG(sal) FROM emp), 2) AS 差额
FROM emp;-- 放在 SELECT 列表中的关联标量子查询：查每个部门的人数（与 JOIN + GROUP BY 等价）
SELECT d.deptname,
(SELECT COUNT(*) FROM emp e WHERE e.deptno = d.deptno) AS 人数
FROM dept d;+----------+------+
| deptname | 人数 |
+----------+------+
| manager | 1 |
| it | 3 |
| sale | 3 |
| services | 2 |
| research | 0 |
+----------+------+注意：如果标量子查询返回多行，会报错：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE sex='m');
-- ERROR 1242 (21000): Subquery returns more than 1 row如果返回零行，结果为 NULL，整个比较结果为 UNKNOWN（不报错，但查不到数据）——排查"莫名其妙查不到数据"时要想到这一点。
11.2 列子查询与 IN / NOT IN
返回一列多行，配合 IN、NOT IN、ANY、ALL 使用。
-- 查询所有位于 beijing 或 shenzhen 的部门的员工
SELECT ename, sal, deptno FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE location IN ('beijing','shenzhen'));+----------+----------+--------+
| ename | sal | deptno |
+----------+----------+--------+
| boss | 20000.00 | 1 |
| zhangsan | 15000.00 | 2 |
| maliu | 8000.00 | 2 |
| tianqi | 7000.00 | 2 |
+----------+----------+--------+-- 查询有员工的部门
SELECT deptno, deptname FROM dept
WHERE deptno IN (SELECT DISTINCT deptno FROM emp);
-- 查询工资等于各部门最高工资的员工（列子查询 + IN，注意可能跨部门误匹配）
SELECT ename, sal, deptno FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);NOT IN 遇到 NULL 的陷阱（重点）
-- 期望：找出没有员工的部门。实际：返回空集！
SELECT deptno, deptname FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);Empty set (0.00 sec)原因：emp 中存在 deptno 为 NULL 的行（员工 newbie）。NOT IN 会被展开成一串 AND 比较：
deptno NOT IN (1,2,3,4,NULL)
等价于 deptno <> 1 AND deptno <> 2 AND deptno <> 3 AND deptno <> 4 AND deptno <> NULL而 任何值 <> NULL 的结果是 UNKNOWN，TRUE AND UNKNOWN = UNKNOWN，整个条件永远不为 TRUE，因此一行都不返回。
三种正确写法：
-- 方案 1：在子查询中排除 NULL
SELECT deptno, deptname FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL);
-- 方案 2：改用 NOT EXISTS（对 NULL 免疫，推荐）
SELECT deptno, deptname FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);
-- 方案 3：改用 LEFT JOIN + IS NULL
SELECT d.deptno, d.deptname FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;对比：IN 遇到 NULL 不会有这个问题（IN 展开为 OR，一个 TRUE 即为 TRUE），只有 NOT IN 危险。
铁律：子查询的列可能为 NULL 时，绝不使用
NOT IN，改用NOT EXISTS。
11.3 行子查询（返回单行多列）
用 (列1, 列2) 构造行值表达式与子查询结果整体比较：
-- 查询与 maliu 在同一部门、且上级也相同的员工
SELECT ename, deptno, managerno FROM emp
WHERE (deptno, managerno) = (SELECT deptno, managerno FROM emp WHERE ename='maliu')
AND ename <> 'maliu';+--------+--------+-----------+
| ename | deptno | managerno |
+--------+--------+-----------+
| tianqi | 2 | 2 |
+--------+--------+-----------+-- 行子查询配合 IN：查询每个部门工资最高的员工（多列同时匹配，避免跨部门误匹配）
SELECT ename, sal, deptno FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno);+----------+----------+--------+
| ename | sal | deptno |
+----------+----------+--------+
| boss | 20000.00 | 1 |
| zhangsan | 15000.00 | 2 |
| lisi | 13000.00 | 3 |
| wangwu | 12000.00 | 4 |
+----------+----------+--------+这正是 11.2 中
sal IN (SELECT MAX(sal) ... GROUP BY deptno)写法的修正版：单列 IN 只比较工资，若 A 部门的最高工资恰好等于 B 部门某个非最高工资员工的工资，就会误把后者也查出来；行子查询把(deptno, sal)作为整体比较，结果准确。
11.4 表子查询与派生表（FROM 中的子查询）
子查询出现在 FROM 后面时称为派生表（Derived Table），可当成一张临时表继续参与连接、分组。
硬性规定：FROM 子句中的子查询必须起别名，否则报错：
-- 错误
SELECT * FROM (SELECT deptno, AVG(sal) FROM emp GROUP BY deptno);
-- ERROR 1248 (42000): Every derived table must have its own alias-- 正确：加别名 t
SELECT * FROM (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t;派生表的典型用途：
-- 1. 对聚合结果再做筛选/排序（等价于 HAVING，但更灵活）
SELECT t.deptno, t.avg_sal
FROM (SELECT deptno, ROUND(AVG(sal),2) AS avg_sal FROM emp GROUP BY deptno) t
WHERE t.avg_sal > 9000
ORDER BY t.avg_sal DESC;+--------+----------+
| deptno | avg_sal |
+--------+----------+
| 1 | 20000.00 |
| 4 | 8000.00 |
+--------+----------+-- 2. 派生表与实表连接：把部门平均工资拼到每个员工身上，比较个人与部门均值
SELECT e.ename, e.sal, d.deptname, t.avg_sal,
ROUND(e.sal - t.avg_sal, 2) AS 高出部门均值
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
INNER JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno
WHERE e.sal > t.avg_sal
ORDER BY 高出部门均值 DESC;+----------+----------+----------+--------------+--------------+
| ename | sal | deptname | avg_sal | 高出部门均值 |
+----------+----------+----------+--------------+--------------+
| zhangsan | 15000.00 | it | 10000.000000 | 5000.00 |
| wangwu | 12000.00 | services | 8000.000000 | 4000.00 |
| lisi | 13000.00 | sale | 8500.000000 | 4500.00 |
+----------+----------+----------+--------------+--------------+-- 3. 聚合的聚合：先按部门求平均，再求"部门平均工资"的最大值
SELECT MAX(t.avg_sal) AS 最高部门均值
FROM (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t;
-- 4. 派生表用于分页去重后再连接（大表深分页优化的常见手法）
SELECT e.*
FROM (SELECT empno FROM emp ORDER BY sal DESC LIMIT 100, 10) t
INNER JOIN emp e ON e.empno = t.empno;注意事项：
- MySQL 5.6 之前派生表一定会被物化（生成内存/磁盘临时表）且无索引；5.6+ 支持派生表合并（derived merge）与自动为派生表加索引，性能大幅改善；
- 派生表内不能引用外层查询的列（不能关联），需要关联时改用关联子查询或 JOIN；
- MySQL 8.0 可用
WITH ... AS（CTE，公用表表达式）替代，可读性更好且可复用。
11.5 关联子查询（Correlated Subquery）
子查询中引用了外层查询的列，因此必须对外层的每一行执行一次（逻辑上如此，优化器可能改写）。
-- 查询工资高于本部门平均工资的员工（经典关联子查询）
SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE e.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno = e.deptno);+----------+----------+--------+
| ename | sal | deptno |
+----------+----------+--------+
| zhangsan | 15000.00 | 2 |
| lisi | 13000.00 | 3 |
| wangwu | 12000.00 | 4 |
+----------+----------+--------+-- 查询每个部门工资最高的员工（关联子查询版）
SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE e.sal = (SELECT MAX(e2.sal) FROM emp e2 WHERE e2.deptno = e.deptno);
-- 查询入职时间早于本部门平均入职时间的员工
SELECT e.ename, e.hiredate, e.deptno
FROM emp e
WHERE e.hiredate < (SELECT AVG(e2.hiredate) FROM emp e2 WHERE e2.deptno = e.deptno);关联子查询与非关联子查询的区别：
| 非关联子查询 | 关联子查询 | |
|---|---|---|
| 是否引用外层列 | 否 | 是 |
| 执行次数 | 只执行一次，结果被缓存复用 | 逻辑上对外层每行执行一次 |
| 能否单独运行 | 能 | 不能（引用了外层别名） |
| 性能 | 通常较好 | 外层行数大时可能很慢，务必给子查询的关联列建索引 |
11.6 EXISTS 与 NOT EXISTS
EXISTS 只关心子查询是否返回至少一行，不关心返回什么内容，因此惯例写 SELECT 1。
-- 查询有员工的部门
SELECT d.deptno, d.deptname FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | manager |
| 2 | it |
| 3 | sale |
| 4 | services |
+--------+----------+-- 查询没有员工的部门
SELECT d.deptno, d.deptname FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);+--------+----------+
| deptno | deptname |
+--------+----------+
| 5 | research |
+--------+----------+-- 查询有工资超过 10000 的员工的部门
SELECT d.deptname FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno AND e.sal > 10000);
-- 查询选修了全部课程的学生（双重 NOT EXISTS，关系除法）
SELECT s.sname FROM student s
WHERE NOT EXISTS (
SELECT 1 FROM course c
WHERE NOT EXISTS (
SELECT 1 FROM student_course sc
WHERE sc.sid = s.sid AND sc.cid = c.cid
)
);EXISTS 与 IN 的性能差异
| 场景 | 推荐 | 原因 |
|---|---|---|
| 子查询结果集小，外层表大 | IN |
子查询结果一次性物化成小集合（MySQL 会转成哈希或排序去重），外层每行做一次快速查找 |
| 子查询结果集大，外层表小 | EXISTS |
外层行少，每行探测一次子查询即可，且子查询找到第一行就短路返回，不需要跑完 |
| 需要处理 NULL | EXISTS / NOT EXISTS |
对 NULL 免疫，没有 NOT IN 的陷阱 |
| 只判断存在性、无需取子表列 | EXISTS |
语义精确，不会因子表一对多而产生重复行 |
经验口诀：小表驱动大表——IN 适合"子查询小"，EXISTS 适合"外层小"。
需要补充的现实情况：MySQL 5.6 起优化器对
IN子查询引入了半连接（semi-join）优化，会自动改写为EXISTS、物化或 LooseScan 等形式，因此在新版本上两者性能差距已大幅缩小。MySQL 5.5 及以前的IN子查询是逐行重复执行的，性能极差，必须手工改写为 JOIN 或 EXISTS。 实际取舍应以EXPLAIN结果为准。
EXISTS 中的 SELECT 列表写什么都不影响结果：
-- 以下三句完全等价，习惯写 SELECT 1
SELECT ... WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);
SELECT ... WHERE EXISTS (SELECT * FROM emp e WHERE e.deptno = d.deptno);
SELECT ... WHERE EXISTS (SELECT NULL FROM emp e WHERE e.deptno = d.deptno);11.7 ANY / SOME / ALL
配合比较运算符使用列子查询。SOME 是 ANY 的同义词。
| 表达式 | 含义 | 等价写法 |
|---|---|---|
> ANY (子查询) |
大于子查询结果中的任意一个，即大于最小值 | > (SELECT MIN(x) ...) |
> ALL (子查询) |
大于子查询结果中的所有值，即大于最大值 | > (SELECT MAX(x) ...) |
< ANY (子查询) |
小于任意一个，即小于最大值 | < (SELECT MAX(x) ...) |
< ALL (子查询) |
小于所有值，即小于最小值 | < (SELECT MIN(x) ...) |
= ANY (子查询) |
等于其中任意一个 | IN (子查询) |
<> ALL (子查询) |
不等于其中任何一个 | NOT IN (子查询) |
-- 工资比 3 号部门任意一人高的员工（即比 3 号部门最低工资高）
SELECT ename, sal, deptno FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 3);
-- 工资比 3 号部门所有人都高的员工（即比 3 号部门最高工资高）
SELECT ename, sal, deptno FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 3);-- > ALL 的结果（3 号部门最高 13000）
+----------+----------+--------+
| ename | sal | deptno |
+----------+----------+--------+
| boss | 20000.00 | 1 |
| zhangsan | 15000.00 | 2 |
+----------+----------+--------+-- 用 = ANY 代替 IN
SELECT ename FROM emp WHERE deptno = ANY (SELECT deptno FROM dept WHERE location='shenzhen');
-- SOME 与 ANY 完全等价
SELECT ename, sal FROM emp WHERE sal > SOME (SELECT sal FROM emp WHERE deptno = 4);NULL 陷阱同样存在：<> ALL 与 NOT IN 一样，子查询含 NULL 时结果为空集。
-- 危险：emp.deptno 含 NULL，返回空集
SELECT deptname FROM dept WHERE deptno <> ALL (SELECT deptno FROM emp);实践建议：
ANY/ALL的可读性较差，团队协作时优先用> (SELECT MAX(...))、IN、NOT EXISTS等更直白的等价写法。
11.8 子查询与连接的等价改写与选择建议
同一个需求往往有多种写法，下面是"查询有员工的部门"的四种等价形式：
-- 1. IN 子查询
SELECT deptno, deptname FROM dept
WHERE deptno IN (SELECT deptno FROM emp);
-- 2. EXISTS 子查询
SELECT deptno, deptname FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);
-- 3. INNER JOIN + DISTINCT（注意：不加 DISTINCT 会因一对多而产生重复行）
SELECT DISTINCT d.deptno, d.deptname
FROM dept d INNER JOIN emp e ON d.deptno = e.deptno;
-- 4. JOIN 派生表（先在子表内去重，避免膨胀后再去重）
SELECT d.deptno, d.deptname
FROM dept d
INNER JOIN (SELECT DISTINCT deptno FROM emp) t ON d.deptno = t.deptno;选择建议：
| 需求特征 | 推荐写法 |
|---|---|
| 需要取出关联表的列（如要显示员工姓名） | JOIN（子查询无法在外层引用子表的列） |
| 只做存在性判断，不取关联表列 | EXISTS 或 IN（不会产生行数膨胀，无需 DISTINCT） |
| 判断不存在 | NOT EXISTS 或 LEFT JOIN ... IS NULL（不要用 NOT IN） |
| 需要聚合后再筛选/连接 | 派生表 + JOIN（比关联子查询快得多，聚合只算一次） |
| 一行一个统计值、结果列较少 | SELECT 列表中的标量子查询（写法简洁，但外层行多时较慢） |
MySQL 5.5 及以前的 IN 子查询 |
一律手工改写成 JOIN |
一个典型改写案例：
-- 慢：关联子查询，对每个员工都要算一次部门平均工资
SELECT e.ename, e.sal FROM emp e
WHERE e.sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno);
-- 快：派生表只聚合一次，然后做连接
SELECT e.ename, e.sal
FROM emp e
INNER JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno
WHERE e.sal > t.avg_sal;通用原则：先写出语义正确、可读性好的版本，再用
EXPLAIN验证，只在确有性能问题时才做等价改写。 不要凭"听说 JOIN 比子查询快"就盲目改写。
十二、视图（VIEW）
视图是一条被命名并保存下来的 SELECT 语句，本身不存储数据（普通视图），每次查询视图时都会执行其定义 SQL。它是多表查询最常用的封装手段。
本节为视图的主讲位置。若
11-04-索引事务与状态查询.md中也提及视图，以本文为准。
12.1 创建视图
CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sex, e.sal, e.deptno,
d.deptname, d.location
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;
-- 使用视图，与查普通表完全一样
SELECT * FROM v_emp_dept WHERE sal > 10000;
SELECT deptname, COUNT(*), AVG(sal) FROM v_emp_dept GROUP BY deptname;+-------+----------+------+----------+--------+----------+-----------+
| empno | ename | sex | sal | deptno | deptname | location |
+-------+----------+------+----------+--------+----------+-----------+
| 1 | boss | m | 20000.00 | 1 | manager | beijing |
| 2 | zhangsan | m | 15000.00 | 2 | it | shenzhen |
| 3 | lisi | f | 13000.00 | 3 | sale | shanghai |
| 4 | wangwu | f | 12000.00 | 4 | services | guangzhou |
+-------+----------+------+----------+--------+----------+-----------+自定义列名与完整语法：
-- 在视图名后指定列名（数量必须与 SELECT 列数一致）
CREATE VIEW v_dept_stat (部门, 人数, 平均工资, 总工资) AS
SELECT d.deptname, COUNT(e.empno), ROUND(AVG(e.sal),2), IFNULL(SUM(e.sal),0)
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.deptname;
SELECT * FROM v_dept_stat;+----------+------+--------------+----------+
| 部门 | 人数 | 平均工资 | 总工资 |
+----------+------+--------------+----------+
| manager | 1 | 20000.00 | 20000.00 |
| it | 3 | 10000.00 | 30000.00 |
| sale | 3 | 8500.00 | 25500.00 |
| services | 2 | 8000.00 | 16000.00 |
| research | 0 | NULL | 0.00 |
+----------+------+--------------+----------+12.2 CREATE OR REPLACE VIEW
-- 视图不存在则创建，存在则用新定义覆盖（最常用的迭代方式）
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sal, d.deptname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;注意
CREATE OR REPLACE会整体替换定义，不是合并；替换后依赖该视图的其他视图/程序若引用了被删掉的列会立即报错。
12.3 视图的作用
| 作用 | 说明 |
|---|---|
| 简化复杂查询 | 把五表连接 + 聚合封装成一个"表"，应用端只写 SELECT * FROM v_xxx |
| 安全隔离（列级 / 行级权限） | 只把视图的查询权限授予用户，用户看不到基表，也看不到未包含在视图中的敏感列或行 |
| 屏蔽结构变更 | 基表重构（改列名、拆表）后重建视图，对外接口保持不变，减少应用改动 |
| 统一业务口径 | 例如"有效订单"的定义统一在视图里，避免各部门口径不一致 |
| 逻辑数据独立性 | 数据库设计与外部使用解耦 |
安全隔离的典型用法：
-- 只暴露非敏感列（隐藏工资、生日）
CREATE VIEW v_emp_public AS
SELECT empno, ename, sex, deptno FROM emp;
-- 行级隔离：某部门经理只能看到本部门数据
CREATE VIEW v_emp_dept2 AS
SELECT * FROM emp WHERE deptno = 2;
-- 只授予视图权限，不授予基表权限
GRANT SELECT ON aaa.v_emp_public TO 'guest'@'localhost' IDENTIFIED BY '123';
FLUSH PRIVILEGES;配合 SQL SECURITY 子句控制权限校验身份：
-- DEFINER（默认）：以视图创建者的权限执行，调用者无需基表权限
CREATE DEFINER = 'root'@'localhost' SQL SECURITY DEFINER
VIEW v_emp_secure AS SELECT empno, ename, deptno FROM emp;
-- INVOKER：以调用者的权限执行，调用者必须对基表有权限
CREATE SQL SECURITY INVOKER
VIEW v_emp_invoker AS SELECT empno, ename FROM emp;12.4 可更新视图的限制
对满足条件的视图可以执行 INSERT / UPDATE / DELETE，操作会转换到基表上：
CREATE VIEW v_emp_dept2 AS SELECT * FROM emp WHERE deptno = 2;
UPDATE v_emp_dept2 SET sal = sal + 500 WHERE ename = 'maliu'; -- 可以
DELETE FROM v_emp_dept2 WHERE ename = 'tianqi'; -- 可以视图不可更新的情况（出现以下任一即变为只读）：
| 不可更新的原因 | 示例 |
|---|---|
| 含聚合函数 | SUM()、COUNT()、AVG()、MAX()、MIN() |
含 GROUP BY / HAVING |
分组统计视图 |
含 DISTINCT |
去重视图 |
含 UNION / UNION ALL |
集合运算视图 |
| 含窗口函数（8.0） | ROW_NUMBER() OVER (...) |
SELECT 列表含子查询 |
SELECT (SELECT MAX(sal) FROM emp) ... |
FROM 中只有常量、无基表 |
CREATE VIEW v AS SELECT 1 |
| 多表连接视图 | 部分可更新（只能更新到单张基表），INSERT 通常不允许 |
| 视图的视图，且底层视图不可更新 | 传递性 |
WITH CHECK OPTION：防止通过视图插入/更新出"视图自己都查不到"的行：
CREATE VIEW v_emp_dept2_chk AS
SELECT * FROM emp WHERE deptno = 2
WITH CHECK OPTION;
-- 报错：违反视图定义中的 deptno=2 条件
UPDATE v_emp_dept2_chk SET deptno = 3 WHERE ename = 'maliu';
-- ERROR 1369 (HY000): CHECK OPTION failed 'aaa.v_emp_dept2_chk'
INSERT INTO v_emp_dept2_chk (empno, ename, deptno) VALUES (11,'x',9);
-- 同样报 CHECK OPTION failed| 选项 | 含义 |
|---|---|
WITH LOCAL CHECK OPTION |
只检查本视图的条件 |
WITH CASCADED CHECK OPTION |
默认。同时检查本视图和所有底层视图的条件 |
实践建议：视图主要用于查询与权限隔离，不要通过视图做写操作，容易产生难以排查的行为。
12.5 修改、查看与删除视图
-- 修改视图定义（视图必须已存在，否则报错）
ALTER VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sal, e.deptno, d.deptname, d.location
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
-- 查看视图定义
SHOW CREATE VIEW v_emp_dept;
-- 查看视图结构（与查表结构一样）
DESC v_emp_dept;
SHOW COLUMNS FROM v_emp_dept;
-- 列出当前库的所有视图
SHOW FULL TABLES WHERE table_type = 'VIEW';
-- 从 information_schema 查看视图及其定义
SELECT table_name, view_definition, is_updatable, definer, security_type
FROM information_schema.views
WHERE table_schema = 'aaa';
-- 删除视图
DROP VIEW v_emp_dept;
DROP VIEW IF EXISTS v_emp_dept, v_dept_stat;要点辨析：
| 语句 | 视图不存在时 | 视图存在时 |
|---|---|---|
CREATE VIEW |
创建成功 | 报错 Table 'v_x' already exists |
CREATE OR REPLACE VIEW |
创建成功 | 覆盖定义 |
ALTER VIEW |
报错 | 覆盖定义 |
其他注意事项：
- 视图的
SELECT中不能引用系统变量或用户变量；FROM子句在 MySQL 5.7 前不能包含子查询（5.7+ 已支持）； - 视图不能建索引，视图上的查询性能完全取决于基表的索引；
- MySQL 视图有两种算法：
MERGE（把视图定义合并进外层查询，性能好）与TEMPTABLE（先物化成临时表，性能差）。含聚合、DISTINCT、UNION、LIMIT的视图会被迫使用TEMPTABLE；
CREATE ALGORITHM = MERGE VIEW v_emp_m AS SELECT * FROM emp WHERE sal > 5000;
CREATE ALGORITHM = TEMPTABLE VIEW v_emp_t AS SELECT * FROM emp WHERE sal > 5000;- 删除或改名基表后，视图不会自动失效，只会在查询时报错：
View 'aaa.v_x' references invalid table(s)。可用CHECK TABLE v_x;检测。
十三、分组进阶
13.1 聚合函数与分组基础回顾
| 函数/关键字 | 作用 |
|---|---|
count(*) |
统计行数（含 NULL 行） |
count(列) |
统计该列非 NULL 的行数 |
distinct |
去重 |
group by |
分组 |
sum() |
求和 |
max() |
最大值 |
min() |
最小值 |
avg() |
平均值 |
having |
配合 group by 对聚合结果进行过滤 |
select count(*) from emp;
SELECT COUNT(*) FROM emp;
-- 去重统计部门数
select count(distinct deptno) from emp;
select count(distinct deptno) deptcount from emp; -- 别名
SELECT COUNT(DISTINCT deptno) FROM emp;
-- 每个部门的人数
select deptno, count(*) from emp group by deptno;
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
-- 男女员工各多少人
select sex, count(*) from emp group by sex;
-- 每个部门男女各多少人
select deptno, sex, count(*) from emp group by deptno, sex;
SELECT deptno, sex, COUNT(*) FROM emp GROUP BY deptno, sex;
-- 人数大于 2 的部门
select deptno, count(*) from emp group by deptno having count(*) > 2;
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 2;
-- 每个部门工资统计
select deptno, sum(sal), max(sal), min(sal), avg(sal)
from emp group by deptno;
SELECT deptno, SUM(sal), MAX(sal), MIN(sal), AVG(sal) FROM emp GROUP BY deptno;WHERE 与 HAVING 的区别：
WHERE |
HAVING |
|
|---|---|---|
| 执行时机 | 分组之前，过滤原始行 | 分组之后，过滤分组结果 |
| 能否用聚合函数 | 不能 | 能 |
| 能否用列别名 | 不能（多数情况） | 能 |
| 性能 | 更早过滤，数据量小，更快 | 先聚合再过滤，开销大 |
-- 先用 WHERE 过滤原始行，再分组，再用 HAVING 过滤分组：这是最高效的组合
SELECT deptno, COUNT(*) AS cnt, AVG(sal) AS avg_sal
FROM emp
WHERE sex = 'm' -- 只统计男员工（分组前过滤）
GROUP BY deptno
HAVING cnt >= 2 -- 只保留人数 >= 2 的部门（分组后过滤）
ORDER BY avg_sal DESC;13.2 GROUP BY 多列
多列分组时，按列值组合分组，组合有几种就有几个分组：
-- 每个部门的男女人数与平均工资
SELECT d.deptname, e.sex, COUNT(*) AS 人数, AVG(e.sal) AS 平均工资
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptname, e.sex
ORDER BY d.deptname, e.sex;+----------+------+------+--------------+
| deptname | sex | 人数 | 平均工资 |
+----------+------+------+--------------+
| it | f | 1 | 7000.000000 |
| it | m | 2 | 11500.000000 |
| manager | m | 1 | 20000.000000 |
| sale | f | 1 | 13000.000000 |
| sale | m | 2 | 6250.000000 |
| services | f | 2 | 8000.000000 |
+----------+------+------+--------------+注意：GROUP BY 的列顺序不影响分组结果（组合相同），但影响 MySQL 5.7 及以前的隐式排序结果顺序。MySQL 8.0 已移除 GROUP BY 的隐式排序，需要有序就必须显式写 ORDER BY（GROUP BY x ASC/DESC 语法在 8.0 中已废弃）。
13.3 WITH ROLLUP：分组小计与总计
WITH ROLLUP 在分组结果末尾自动追加小计行与总计行，被"上卷"的列显示为 NULL。
-- 部门维度的合计
SELECT deptno, COUNT(*) AS 人数, SUM(sal) AS 工资总额
FROM emp
GROUP BY deptno WITH ROLLUP;+--------+------+----------+
| deptno | 人数 | 工资总额 |
+--------+------+----------+
| NULL | 1 | 5000.00 | -- deptno 为 NULL 的真实分组（newbie）
| 1 | 1 | 20000.00 |
| 2 | 3 | 30000.00 |
| 3 | 3 | 25500.00 |
| 4 | 2 | 16000.00 |
| NULL | 10 | 96500.00 | <-- ROLLUP 产生的总计行
+--------+------+----------+多列 ROLLUP 会产生多层小计（从右往左逐级上卷）：
SELECT d.deptname, e.sex, COUNT(*) AS 人数, SUM(e.sal) AS 工资总额
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptname, e.sex WITH ROLLUP;+----------+------+------+----------+
| deptname | sex | 人数 | 工资总额 |
+----------+------+------+----------+
| it | f | 1 | 7000.00 |
| it | m | 2 | 23000.00 |
| it | NULL | 3 | 30000.00 | <-- it 部门小计
| manager | m | 1 | 20000.00 |
| manager | NULL | 1 | 20000.00 | <-- manager 部门小计
| sale | f | 1 | 13000.00 |
| sale | m | 2 | 12500.00 |
| sale | NULL | 3 | 25500.00 | <-- sale 部门小计
| services | f | 2 | 16000.00 |
| services | NULL | 2 | 16000.00 | <-- services 部门小计
| NULL | NULL | 9 | 91500.00 | <-- 总计
+----------+------+------+----------+区分"真实 NULL 分组"与"ROLLUP 汇总行"：用 GROUPING() 函数（MySQL 8.0）或 IFNULL 加标签：
-- MySQL 8.0：GROUPING() 返回 1 表示该列被上卷
SELECT IF(GROUPING(deptno), '合计', deptno) AS 部门,
COUNT(*) AS 人数, SUM(sal) AS 工资总额
FROM emp GROUP BY deptno WITH ROLLUP;
-- MySQL 5.x 变通：把 NULL 先转成占位值
SELECT IFNULL(CAST(deptno AS CHAR), '未分配') AS 部门,
COUNT(*) AS 人数
FROM emp GROUP BY IFNULL(CAST(deptno AS CHAR), '未分配') WITH ROLLUP;限制：WITH ROLLUP 不能与 ORDER BY 同时使用（MySQL 5.x 报错 Incorrect usage of CUBE/ROLLUP and ORDER BY，8.0 已允许）。需要排序时把结果包成派生表再排。
SELECT * FROM (
SELECT deptno, COUNT(*) AS cnt FROM emp GROUP BY deptno WITH ROLLUP
) t ORDER BY (t.deptno IS NULL), t.deptno;13.4 GROUP_CONCAT：拼接分组明细
聚合函数只能返回一个值，GROUP_CONCAT 可以把组内多行的值拼成一个字符串，是"分组后看明细"的利器。
-- 每个部门有哪些人
SELECT d.deptname,
COUNT(*) AS 人数,
GROUP_CONCAT(e.ename) AS 成员
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno, d.deptname;+----------+------+------------------------+
| deptname | 人数 | 成员 |
+----------+------+------------------------+
| manager | 1 | boss |
| it | 3 | zhangsan,maliu,tianqi |
| sale | 3 | lisi,mark,john |
| services | 2 | wangwu,mm |
+----------+------+------------------------+完整语法与常用选项：
GROUP_CONCAT([DISTINCT] 列 [ORDER BY 列 ASC|DESC] [SEPARATOR '分隔符'])-- 按工资从高到低排列组内成员，用 " | " 分隔，并附带工资
SELECT d.deptname,
GROUP_CONCAT(CONCAT(e.ename, '(', e.sal, ')')
ORDER BY e.sal DESC SEPARATOR ' | ') AS 成员明细
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno, d.deptname;+----------+---------------------------------------------------+
| deptname | 成员明细 |
+----------+---------------------------------------------------+
| manager | boss(20000.00) |
| it | zhangsan(15000.00) | maliu(8000.00) | tianqi(7000.00) |
| sale | lisi(13000.00) | mark(6500.00) | john(6000.00) |
| services | wangwu(12000.00) | mm(4000.00) |
+----------+---------------------------------------------------+-- 去重拼接：每个部门有哪些不同的性别
SELECT deptno, GROUP_CONCAT(DISTINCT sex ORDER BY sex) AS 性别分布
FROM emp GROUP BY deptno;
-- 每位领导的下属名单
SELECT m.ename AS 领导, GROUP_CONCAT(e.ename) AS 下属
FROM emp e INNER JOIN emp m ON e.managerno = m.empno
WHERE e.empno <> m.empno
GROUP BY m.empno, m.ename;长度截断陷阱：GROUP_CONCAT 结果默认最长 1024 字节，超出会被静默截断（不报错）：
SHOW VARIABLES LIKE 'group_concat_max_len';
-- 会话级临时调大
SET SESSION group_concat_max_len = 102400;
-- 全局调大
SET GLOBAL group_concat_max_len = 102400;# /etc/my.cnf 永久生效
[mysqld]
group_concat_max_len=10240013.5 HAVING 中使用聚合结果
-- 平均工资高于 8000 的部门
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
HAVING AVG(sal) > 8000;
-- 可以直接用 SELECT 中定义的别名（MySQL 支持，标准 SQL 不保证）
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
HAVING avg_sal > 8000;
-- HAVING 中使用多个聚合条件
SELECT deptno, COUNT(*) AS cnt, MAX(sal) AS max_sal, MIN(sal) AS min_sal
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 2 AND MAX(sal) - MIN(sal) > 5000;+--------+-----+----------+----------+
| deptno | cnt | max_sal | min_sal |
+--------+-----+----------+----------+
| 2 | 3 | 15000.00 | 7000.00 |
| 3 | 3 | 13000.00 | 6000.00 |
| 4 | 2 | 12000.00 | 4000.00 |
+--------+-----+----------+----------+-- HAVING 中使用标量子查询：平均工资高于全公司平均工资的部门
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);
-- HAVING 也可以在没有 GROUP BY 时使用（此时全表为一组）
SELECT COUNT(*) FROM emp HAVING COUNT(*) > 5;13.6 分组后取每组最大值的多种写法
需求：取出每个部门工资最高的员工的全部信息。这是面试与实战高频题，注意"直接分组"是错的。
-- 【错误写法】MySQL 会随机返回组内某一行的 ename，与 MAX(sal) 不对应
SELECT deptno, ename, MAX(sal) FROM emp GROUP BY deptno;+--------+----------+----------+
| deptno | ename | MAX(sal) |
+--------+----------+----------+
| 1 | boss | 20000.00 |
| 2 | zhangsan | 15000.00 |
| 3 | lisi | 13000.00 |
| 4 | wangwu | 12000.00 |
+--------+----------+----------+
-- 本例恰好正确，但这是巧合！ename 与 MAX(sal) 之间没有任何保证写法 1：行子查询 + IN（推荐，简洁准确）
SELECT empno, ename, sal, deptno FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno);写法 2：派生表 + JOIN（性能通常最好）
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
INNER JOIN (SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal = t.max_sal;写法 3：关联子查询
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
WHERE e.sal = (SELECT MAX(e2.sal) FROM emp e2 WHERE e2.deptno = e.deptno);写法 4：NOT EXISTS（"不存在比我更高的"，反连接思路）
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
WHERE NOT EXISTS (
SELECT 1 FROM emp e2
WHERE e2.deptno = e.deptno AND e2.sal > e.sal
);写法 5：自左连接 + IS NULL（同样是"不存在更高的"）
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
LEFT JOIN emp e2 ON e2.deptno = e.deptno AND e2.sal > e.sal
WHERE e2.empno IS NULL;写法 6：窗口函数（MySQL 8.0，最优雅，且能处理并列）
SELECT empno, ename, sal, deptno FROM (
SELECT empno, ename, sal, deptno,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
FROM emp
) t WHERE rn = 1;
-- RANK() 会把并列第一的多人都取出来
SELECT empno, ename, sal, deptno FROM (
SELECT empno, ename, sal, deptno,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rk
FROM emp
) t WHERE rk = 1;写法 7：GROUP_CONCAT + SUBSTRING_INDEX（技巧型，MySQL 5.x 单条 SQL 取 Top1）
SELECT deptno,
SUBSTRING_INDEX(GROUP_CONCAT(ename ORDER BY sal DESC), ',', 1) AS 最高薪员工,
MAX(sal) AS 最高工资
FROM emp GROUP BY deptno;各写法对比：
| 写法 | 版本要求 | 优点 | 缺点 |
|---|---|---|---|
| 行子查询 IN | 5.x+ | 简洁准确 | 5.5 及以前的 IN 子查询性能差 |
| 派生表 JOIN | 5.x+ | 性能好，聚合只算一次 | 语句较长 |
| 关联子查询 | 5.x+ | 直观 | 外层行数大时慢，需 (deptno, sal) 索引 |
| NOT EXISTS | 5.x+ | 语义明确 | 同上 |
| LEFT JOIN + IS NULL | 5.x+ | 无子查询，优化器友好 | 可读性稍差 |
| 窗口函数 | 8.0+ | 最清晰，可取 TOP N、处理并列 | 5.x 不可用 |
| GROUP_CONCAT 技巧 | 5.x+ | 单条 SQL、无需子查询 | 受 group_concat_max_len 限制，只适合取 Top1/Top N 少量 |
注意"并列"问题：若某部门有两人工资同为最高，写法 1~5、7 会都返回（写法 7 只返回一个），
ROW_NUMBER()只返回一个，RANK()全部返回。按业务需求选择。
13.7 ONLY_FULL_GROUP_BY 带来的报错与解决
MySQL 5.7 起 sql_mode 默认包含 ONLY_FULL_GROUP_BY，要求 SELECT 列表、HAVING、ORDER BY 中出现的非聚合列，必须在 GROUP BY 中出现（或函数依赖于分组列）。
早期教学中常见的宽松写法：
-- 注意：select 中除聚合函数外的列，原则上都必须出现在 group by 中
-- 下面写法 MySQL 5.6 及以前可执行但无意义，Oracle 属于语法错误
select ename, deptno, count(*) from emp group by deptno;在 5.7+ 会直接报错：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'aaa.emp.ename' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by诊断与查看：
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
SHOW VARIABLES LIKE 'sql_mode';ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION解决方案（按推荐程度排序）：
方案 1（首选）：改写 SQL，让语义正确
-- 把非聚合列加入 GROUP BY
SELECT deptno, sex, COUNT(*) FROM emp GROUP BY deptno, sex;
-- 或者用聚合函数包裹（明确表达"随便取一个"的意图）
SELECT deptno, MAX(ename) AS 某个员工, COUNT(*) FROM emp GROUP BY deptno;
-- 或者用 GROUP_CONCAT 展示全部明细（推荐，信息完整）
SELECT deptno, GROUP_CONCAT(ename) AS 全部成员, COUNT(*) FROM emp GROUP BY deptno;
-- 需要"最大值对应的整行"就用 13.6 的正确写法，而不是关掉 sql_mode方案 2：使用 ANY_VALUE()（5.7+）显式声明"我知道会随机取值"
SELECT ANY_VALUE(ename) AS ename, deptno, COUNT(*)
FROM emp GROUP BY deptno;方案 3：临时/永久关闭 ONLY_FULL_GROUP_BY（不推荐，仅用于兼容老系统）
-- 会话级（只影响当前连接，断开即失效）
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
-- 全局级（对新建连接生效，重启后失效）
SET GLOBAL sql_mode = (SELECT REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''));# /etc/my.cnf 永久生效（注意不要留下多余逗号）
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONsystemctl restart mysqld强烈建议保留
ONLY_FULL_GROUP_BY：它拦下的都是语义不明确、结果不可预期的 SQL。老系统迁移时可先临时关闭并列出所有报错语句，逐条改写后再重新开启。
十四、综合实战
以下案例均基于第七章的 emp / dept 数据，题目 + 完整 SQL + 结果一并给出。
14.1 多表统计
案例 1：部门综合统计报表（含无人部门）
SELECT d.deptno, d.deptname, d.location,
COUNT(e.empno) AS 人数,
IFNULL(SUM(e.sal), 0) AS 工资总额,
IFNULL(ROUND(AVG(e.sal), 2), 0) AS 平均工资,
IFNULL(MAX(e.sal), 0) AS 最高工资,
IFNULL(MIN(e.sal), 0) AS 最低工资
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.deptname, d.location
ORDER BY 工资总额 DESC;+--------+----------+-----------+------+----------+----------+----------+----------+
| deptno | deptname | location | 人数 | 工资总额 | 平均工资 | 最高工资 | 最低工资 |
+--------+----------+-----------+------+----------+----------+----------+----------+
| 2 | it | shenzhen | 3 | 30000.00 | 10000.00 | 15000.00 | 7000.00 |
| 3 | sale | shanghai | 3 | 25500.00 | 8500.00 | 13000.00 | 6000.00 |
| 1 | manager | beijing | 1 | 20000.00 | 20000.00 | 20000.00 | 20000.00 |
| 4 | services | guangzhou | 2 | 16000.00 | 8000.00 | 12000.00 | 4000.00 |
| 5 | research | hangzhou | 0 | 0.00 | 0.00 | 0.00 | 0.00 |
+--------+----------+-----------+------+----------+----------+----------+----------+案例 2：按部门 + 性别的交叉统计（行转列）
SELECT d.deptname,
SUM(CASE WHEN e.sex = 'm' THEN 1 ELSE 0 END) AS 男,
SUM(CASE WHEN e.sex = 'f' THEN 1 ELSE 0 END) AS 女,
COUNT(e.empno) AS 合计,
ROUND(SUM(CASE WHEN e.sex='m' THEN e.sal ELSE 0 END), 2) AS 男员工工资,
ROUND(SUM(CASE WHEN e.sex='f' THEN e.sal ELSE 0 END), 2) AS 女员工工资
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.deptname WITH ROLLUP;+----------+------+------+------+------------+------------+
| deptname | 男 | 女 | 合计 | 男员工工资 | 女员工工资 |
+----------+------+------+------+------------+------------+
| manager | 1 | 0 | 1 | 20000.00 | 0.00 |
| it | 2 | 1 | 3 | 23000.00 | 7000.00 |
| sale | 2 | 1 | 3 | 12500.00 | 13000.00 |
| services | 0 | 2 | 2 | 0.00 | 16000.00 |
| research | 0 | 0 | 0 | 0.00 | 0.00 |
| NULL | 5 | 4 | 9 | 55500.00 | 36000.00 |
+----------+------+------+------+------------+------------+案例 3：每个部门的工资占公司总工资的比例
SELECT d.deptname,
SUM(e.sal) AS 部门工资,
(SELECT SUM(sal) FROM emp) AS 公司总工资,
CONCAT(ROUND(SUM(e.sal) / (SELECT SUM(sal) FROM emp) * 100, 2), '%') AS 占比
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno, d.deptname
ORDER BY 部门工资 DESC;+----------+----------+------------+--------+
| deptname | 部门工资 | 公司总工资 | 占比 |
+----------+----------+------------+--------+
| it | 30000.00 | 96500.00 | 31.09% |
| sale | 25500.00 | 96500.00 | 26.42% |
| manager | 20000.00 | 96500.00 | 20.73% |
| services | 16000.00 | 96500.00 | 16.58% |
+----------+----------+------------+--------+14.2 排名
案例 4：全公司工资排名（MySQL 5.x 用户变量实现）
SET @rank = 0;
SELECT (@rank := @rank + 1) AS 排名, ename, sal, deptno
FROM emp
WHERE deptno IS NOT NULL
ORDER BY sal DESC;+------+----------+----------+--------+
| 排名 | ename | sal | deptno |
+------+----------+----------+--------+
| 1 | boss | 20000.00 | 1 |
| 2 | zhangsan | 15000.00 | 2 |
| 3 | lisi | 13000.00 | 3 |
| 4 | wangwu | 12000.00 | 4 |
| 5 | maliu | 8000.00 | 2 |
| 6 | tianqi | 7000.00 | 2 |
| 7 | mark | 6500.00 | 3 |
| 8 | john | 6000.00 | 3 |
| 9 | mm | 4000.00 | 4 |
+------+----------+----------+--------+案例 5：部门内排名（用户变量 + 分组重置，MySQL 5.x）
SET @rank = 0, @prev_dept = NULL;
SELECT deptno, ename, sal,
@rank := IF(@prev_dept = deptno, @rank + 1, 1) AS 部门内排名,
@prev_dept := deptno AS dummy
FROM emp
WHERE deptno IS NOT NULL
ORDER BY deptno, sal DESC;+--------+----------+----------+------------+
| deptno | ename | sal | 部门内排名 |
+--------+----------+----------+------------+
| 1 | boss | 20000.00 | 1 |
| 2 | zhangsan | 15000.00 | 1 |
| 2 | maliu | 8000.00 | 2 |
| 2 | tianqi | 7000.00 | 3 |
| 3 | lisi | 13000.00 | 1 |
| 3 | mark | 6500.00 | 2 |
| 3 | john | 6000.00 | 3 |
| 4 | wangwu | 12000.00 | 1 |
| 4 | mm | 4000.00 | 2 |
+--------+----------+----------+------------+案例 6：不用变量的排名（自连接计数法，通用性最好）
-- 排名 = 比自己工资高的人数 + 1
SELECT e.ename, e.sal,
(SELECT COUNT(*) + 1 FROM emp e2 WHERE e2.sal > e.sal) AS 排名
FROM emp e
WHERE e.deptno IS NOT NULL
ORDER BY 排名;
-- 自连接版本
SELECT e.ename, e.sal, COUNT(e2.empno) + 1 AS 排名
FROM emp e
LEFT JOIN emp e2 ON e2.sal > e.sal
GROUP BY e.empno, e.ename, e.sal
ORDER BY 排名;案例 7：窗口函数排名（MySQL 8.0）
SELECT ename, sal, deptno,
ROW_NUMBER() OVER (ORDER BY sal DESC) AS 行号,
RANK() OVER (ORDER BY sal DESC) AS 排名并列跳号,
DENSE_RANK() OVER (ORDER BY sal DESC) AS 排名并列连续,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS 部门内排名
FROM emp;14.3 TOP N
案例 8：全公司工资 TOP 3
-- 工资最高的三个人
select * from emp order by sal desc limit 3;
-- 工资最低的三个人
select * from emp order by sal limit 3;
-- 只看姓名与工资
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 3;+----------+----------+
| ename | sal |
+----------+----------+
| boss | 20000.00 |
| zhangsan | 15000.00 |
| lisi | 13000.00 |
+----------+----------+案例 9：工资从高到低第 3~5 名（分页）
-- LIMIT M, N：从位置 M（从 0 开始）取 N 行
select ename, sal from emp order by sal desc limit 2, 3;
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 2,3;+--------+----------+
| ename | sal |
+--------+----------+
| lisi | 13000.00 |
| wangwu | 12000.00 |
| maliu | 8000.00 |
+--------+----------+案例 10：每个部门工资 TOP 2（分组 TOP N，MySQL 5.x）
-- 方法一：关联计数子查询（"组内比我高的不超过 1 个人"）
SELECT e.deptno, e.ename, e.sal
FROM emp e
WHERE (SELECT COUNT(*) FROM emp e2
WHERE e2.deptno = e.deptno AND e2.sal > e.sal) < 2
AND e.deptno IS NOT NULL
ORDER BY e.deptno, e.sal DESC;+--------+----------+----------+
| deptno | ename | sal |
+--------+----------+----------+
| 1 | boss | 20000.00 |
| 2 | zhangsan | 15000.00 |
| 2 | maliu | 8000.00 |
| 3 | lisi | 13000.00 |
| 3 | mark | 6500.00 |
| 4 | wangwu | 12000.00 |
| 4 | mm | 4000.00 |
+--------+----------+----------+-- 方法二：GROUP_CONCAT + FIND_IN_SET（技巧型）
SELECT e.deptno, e.ename, e.sal
FROM emp e
INNER JOIN (
SELECT deptno, GROUP_CONCAT(ename ORDER BY sal DESC) AS names
FROM emp WHERE deptno IS NOT NULL GROUP BY deptno
) t ON e.deptno = t.deptno
WHERE FIND_IN_SET(e.ename, t.names) <= 2
ORDER BY e.deptno, e.sal DESC;
-- 方法三：窗口函数（MySQL 8.0）
SELECT deptno, ename, sal FROM (
SELECT deptno, ename, sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
FROM emp WHERE deptno IS NOT NULL
) t WHERE rn <= 2 ORDER BY deptno, sal DESC;案例 11：工资最低的女员工
select ename, sal from emp where sex='f' order by sal limit 1;+-------+---------+
| ename | sal |
+-------+---------+
| mm | 4000.00 |
+-------+---------+14.4 按月/按年统计
案例 12：按入职年份统计人数
SELECT YEAR(hiredate) AS 入职年份, COUNT(*) AS 人数,
GROUP_CONCAT(ename ORDER BY hiredate) AS 名单
FROM emp
GROUP BY YEAR(hiredate)
ORDER BY 入职年份;+----------+------+----------------+
| 入职年份 | 人数 | 名单 |
+----------+------+----------------+
| 1995 | 2 | boss,zhangsan |
| 1998 | 1 | lisi |
| 1999 | 1 | wangwu |
| 2001 | 1 | maliu |
| 2002 | 1 | tianqi |
| 2003 | 1 | mark |
| 2005 | 1 | john |
| 2008 | 1 | mm |
| 2020 | 1 | newbie |
+----------+------+----------------+案例 13：按"年-月"统计（报表最常见形式）
SELECT DATE_FORMAT(hiredate, '%Y-%m') AS 年月, COUNT(*) AS 入职人数,
SUM(sal) AS 新增工资成本
FROM emp
GROUP BY DATE_FORMAT(hiredate, '%Y-%m')
ORDER BY 年月;-- 只统计月份分布（不分年份），观察招聘季节性
SELECT MONTH(hiredate) AS 月份, COUNT(*) AS 人数
FROM emp GROUP BY MONTH(hiredate) ORDER BY 月份;
-- 按年 + 部门的二维统计
SELECT YEAR(e.hiredate) AS 年份, d.deptname, COUNT(*) AS 人数
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno
GROUP BY YEAR(e.hiredate), d.deptno, d.deptname
ORDER BY 年份, d.deptno;案例 14：2001 年入职的员工（多种等价写法）
select * from emp where hiredate >= '2001-01-01' and hiredate <= '2001-12-31';
select * from emp where hiredate like '2001%';
select * from emp where year(hiredate) = 2001;
select * from emp where substr(hiredate, 1, 4) = 2001;
select * from emp where hiredate regexp '2001.*';性能提示：
WHERE YEAR(hiredate)=2001、LIKE、SUBSTR、REGEXP都会导致hiredate上的索引失效（对列使用函数）。生产环境应使用范围写法：hiredate >= '2001-01-01' AND hiredate < '2002-01-01'，这样才能走索引。
案例 15：工龄与年龄统计
SELECT ename, hiredate,
TIMESTAMPDIFF(YEAR, hiredate, CURDATE()) AS 工龄,
birthday,
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS 年龄,
sal
FROM emp
ORDER BY 工龄 DESC;
-- 按工龄区间分段统计
SELECT CASE
WHEN TIMESTAMPDIFF(YEAR, hiredate, CURDATE()) >= 25 THEN '25年以上'
WHEN TIMESTAMPDIFF(YEAR, hiredate, CURDATE()) >= 20 THEN '20-25年'
WHEN TIMESTAMPDIFF(YEAR, hiredate, CURDATE()) >= 15 THEN '15-20年'
ELSE '15年以下'
END AS 工龄段,
COUNT(*) AS 人数, ROUND(AVG(sal),2) AS 平均工资
FROM emp
GROUP BY 工龄段
ORDER BY 平均工资 DESC;14.5 关联更新与关联删除
关联更新（多表 UPDATE）——MySQL 支持在 UPDATE 中使用 JOIN：
-- 案例 16：给 shenzhen 的所有部门员工涨薪 10%
UPDATE emp e
INNER JOIN dept d ON e.deptno = d.deptno
SET e.sal = e.sal * 1.1
WHERE d.location = 'shenzhen';
-- 案例 17：把工资低于本部门平均工资的员工补到平均工资（派生表必须先物化）
UPDATE emp e
INNER JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno
SET e.sal = t.avg_sal
WHERE e.sal < t.avg_sal;
-- 案例 18：把部门表中的人数汇总字段同步为实际人数（反范式字段维护）
ALTER TABLE dept ADD emp_count INT DEFAULT 0;
UPDATE dept d
LEFT JOIN (SELECT deptno, COUNT(*) AS cnt FROM emp GROUP BY deptno) t
ON d.deptno = t.deptno
SET d.emp_count = IFNULL(t.cnt, 0);
-- 案例 19：用子查询做关联更新（等价写法）
UPDATE dept d
SET d.emp_count = (SELECT COUNT(*) FROM emp e WHERE e.deptno = d.deptno);
-- 案例 20：把未分配部门的员工统一划到 5 号部门
UPDATE emp SET deptno = 5 WHERE deptno IS NULL;重要限制：MySQL 不允许在
UPDATE/DELETE的子查询中直接引用正在被修改的同一张表：UPDATE emp SET sal = sal * 1.1 WHERE sal < (SELECT AVG(sal) FROM emp); -- ERROR 1093 (HY000): You can't specify target table 'emp' for update in FROM clause解决办法：把子查询再包一层派生表（强制物化），或改用 JOIN 写法：
UPDATE emp SET sal = sal * 1.1 WHERE sal < (SELECT avg_sal FROM (SELECT AVG(sal) AS avg_sal FROM emp) tmp);
关联删除（多表 DELETE）——注意 DELETE 后要写明删哪张表的行：
-- 案例 21：删除所有 shanghai 部门的员工（只删 emp，保留 dept）
DELETE e FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE d.location = 'shanghai';
-- 案例 22：同时删除两张表中的匹配行（e 和 d 都列出）
DELETE e, d FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 5;
-- 案例 23：删除孤儿员工（部门表中已无对应部门）
DELETE e FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno IS NULL;
-- 案例 24：用子查询做关联删除
DELETE FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE location = 'shanghai');
-- 案例 25：删除重复数据，只保留 empno 最小的一条（经典去重）
DELETE e FROM emp e
INNER JOIN (
SELECT ename, MIN(empno) AS keep_no FROM emp GROUP BY ename HAVING COUNT(*) > 1
) t ON e.ename = t.ename AND e.empno > t.keep_no;
-- 单表删除
delete from emp where empno=9;
DELETE FROM emp WHERE empno=9;关联更新/删除的安全操作规范：
- 先用相同条件写
SELECT验证影响范围，确认无误再改成UPDATE/DELETE； - 开启事务，确认后再提交：
START TRANSACTION;
DELETE e FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE d.location='shanghai';
SELECT ROW_COUNT(); -- 查看影响行数
-- 没问题就 COMMIT，有问题就 ROLLBACK
ROLLBACK;
-- COMMIT;- 生产环境开启
sql_safe_updates，防止无 WHERE 的全表误操作：
SET SESSION sql_safe_updates = 1;
DELETE FROM emp; -- ERROR 1175: You are using safe update mode ...- 大批量删除应分批进行（每批几千行），避免长事务与大量锁：
-- 循环执行直到影响行数为 0
DELETE FROM emp WHERE hiredate < '2000-01-01' LIMIT 1000;十五、多表查询的执行计划入门
EXPLAIN 是排查多表查询性能的第一工具，它展示优化器打算如何执行这条语句。
详见 11-04-索引事务与状态查询.md
EXPLAIN
SELECT e.ename, e.sal, d.deptname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 8000;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+15.1 看连接顺序
EXPLAIN输出中id相同的行，从上到下就是实际的连接顺序：上面的第一行是驱动表（外层表），后面的是被驱动表（内层表）。- 上例中优化器选择
d（5 行）作为驱动表、e（10 行）作为被驱动表——这符合"小表驱动大表"原则，因为驱动表要被完整扫描一遍，被驱动表则对每条驱动行探测一次（有索引时代价很低）。 - 书写顺序 ≠ 执行顺序。若要强制按书写顺序连接，可用
STRAIGHT_JOIN（仅在确认优化器判断失误时使用）：
EXPLAIN SELECT e.ename, d.deptname
FROM emp e STRAIGHT_JOIN dept d ON e.deptno = d.deptno;id不同表示存在子查询或派生表，id越大越先执行；select_type为DERIVED表示派生表，SUBQUERY表示非关联子查询，DEPENDENT SUBQUERY表示关联子查询（逐行执行，重点关注对象）。
15.2 看连接类型 type
type 从好到坏依次为：
| type | 含义 | 评价 |
|---|---|---|
system / const |
主键或唯一索引等值匹配，最多 1 行 | 最优 |
eq_ref |
连接时使用主键/唯一索引，每个驱动行只匹配 1 行 | 优秀，多表连接的理想状态 |
ref |
使用非唯一索引等值匹配，返回若干行 | 良好 |
range |
索引范围扫描（BETWEEN、>、IN） |
可接受 |
index |
全索引扫描（扫完整棵索引） | 较差 |
ALL |
全表扫描 | 最差，多表连接中出现在被驱动表上尤其致命 |
关键判断：被驱动表出现 ALL 是多表连接慢的头号原因。上例中 e 表是 ALL 且 Extra 出现 Using join buffer (Block Nested Loop)，说明连接列 deptno 上没有索引，MySQL 只能把驱动表数据装进 join_buffer 后对被驱动表反复全表扫描，复杂度接近 O(M×N)。
加索引后对比：
ALTER TABLE emp ADD INDEX idx_deptno (deptno);
ALTER TABLE dept ADD PRIMARY KEY (deptno);
EXPLAIN SELECT e.ename, d.deptname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| 1 | SIMPLE | e | ALL | idx_deptno | NULL | NULL | NULL | 10 | NULL |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 1 | aaa.e.deptno | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+被驱动表变成 eq_ref，rows=1，连接效率大幅提升。
15.3 Extra 列的关键信息
| Extra 值 | 含义与处理 |
|---|---|
Using index |
覆盖索引，无需回表，最理想 |
Using where |
在服务层做了额外过滤，通常正常 |
Using join buffer (Block Nested Loop) |
连接列无索引，必须加索引 |
Using temporary |
用了临时表（常因 GROUP BY / DISTINCT / UNION 无法用索引），大数据量下会落磁盘 |
Using filesort |
额外排序（ORDER BY 未用上索引），可考虑建合适的联合索引 |
Using index condition |
索引条件下推（ICP），是优化 |
Impossible WHERE |
条件恒假，无需执行 |
Select tables optimized away |
直接由索引得到结果（如 MAX() 走索引） |
15.4 多表查询优化的核心原则
- 连接列必须有索引（尤其是被驱动表的连接列），这是最重要的一条；
- 小表驱动大表：让筛选后行数少的表当驱动表；
- 只查需要的列，避免
SELECT *，有机会用上覆盖索引； - 尽早过滤：把能减少行数的条件放
WHERE（内连接）或ON（外连接的右表）； - 控制连接表数量：一般不超过 3~5 张表；
optimizer_search_depth会限制优化器的搜索深度，表太多时执行计划质量会下降； - 两侧连接列的类型、字符集、排序规则必须一致，否则会发生隐式转换导致索引失效。
-- 5.6+ 可查看优化器改写后的语句与成本
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;
-- 5.6+ JSON 格式，含成本估算
EXPLAIN FORMAT=JSON SELECT ...;
-- 8.0.18+ 查看真实执行耗时与实际行数
EXPLAIN ANALYZE SELECT ...;十六、排错与常见坑
16.1 Column 'xx' in field list is ambiguous
SELECT deptno, ename, deptname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
-- ERROR 1052 (23000): Column 'deptno' in field list is ambiguous原因：两张表都有 deptno 列，MySQL 不知道该取哪个。
解决：用表名或别名给列加前缀。
SELECT e.deptno, e.ename, d.deptname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
-- 或者用 USING 自动合并同名列（该列只出现一次，无需前缀）
SELECT deptno, ename, deptname FROM emp INNER JOIN dept USING (deptno);同类错误也会出现在 ORDER BY / GROUP BY / WHERE 中：
-- 错
... ORDER BY deptno;
-- 对
... ORDER BY e.deptno;规范建议：多表查询中所有列都加表别名前缀，既避免歧义，也让人一眼看出列的来源，日后表结构新增同名列时也不会突然报错。
其他相关报错：
| 报错 | 原因 | 解决 |
|---|---|---|
Unknown column 'emp.ename' in 'field list' |
已定义别名后仍用原表名 | 统一用别名 |
Not unique table/alias: 'emp' |
自连接未起别名，或同一别名用了两次 | 给每个副本起不同别名 |
Every derived table must have its own alias |
FROM 子查询没起别名 |
加别名 |
Duplicate column name 'deptno' |
创建视图/派生表时 SELECT * 导致重名列 |
显式列出列并起别名 |
16.2 连接后行数膨胀
SELECT COUNT(*) FROM dept; -- 5
SELECT COUNT(*) FROM emp; -- 10
SELECT COUNT(*) FROM dept d JOIN emp e ON d.deptno = e.deptno; -- 9，不是 5！原理：一对多连接时，"一"方的行会按匹配数复制。这会导致两类严重错误：
-- 错误：部门工资被重复累加（假设再连一张一对多的项目表）
SELECT d.deptname, SUM(e.sal) AS 工资总额, SUM(p.budget) AS 项目预算
FROM dept d
JOIN emp e ON d.deptno = e.deptno
JOIN project p ON d.deptno = p.deptno -- 第二个一对多，笛卡尔式膨胀
GROUP BY d.deptno;
-- 员工数 × 项目数 行，两个 SUM 都被放大了！正确做法：各自先聚合再连接（分别用派生表把多方压成一行）：
SELECT d.deptname, IFNULL(a.sal_sum,0) AS 工资总额, IFNULL(b.budget_sum,0) AS 项目预算
FROM dept d
LEFT JOIN (SELECT deptno, SUM(sal) AS sal_sum FROM emp GROUP BY deptno) a ON d.deptno = a.deptno
LEFT JOIN (SELECT deptno, SUM(budget) AS budget_sum FROM project GROUP BY deptno) b ON d.deptno = b.deptno;排查与规避手段：
| 手段 | 说明 |
|---|---|
先分别 COUNT(*) |
连接前后行数对比，判断是否膨胀 |
COUNT(DISTINCT 主键) |
判断真实实体数量，如 COUNT(DISTINCT d.deptno) |
SUM(DISTINCT ...) |
只是权宜之计，相同金额的行会被误去重，不可靠 |
只做存在性判断时用 EXISTS |
不会产生行复制，天然无膨胀 |
| 多个一对多分支 | 一律"先聚合成一行，再连接" |
16.3 NULL 参与运算与比较
| 表达式 | 结果 |
|---|---|
NULL + 1 |
NULL |
NULL * 100 |
NULL |
CONCAT('a', NULL) |
NULL |
NULL = NULL |
NULL（不是 TRUE） |
NULL <> NULL |
NULL |
NULL IS NULL |
TRUE |
NULL <=> NULL |
TRUE（<=> 是 MySQL 的 NULL 安全等号） |
COUNT(*) |
计入 NULL 行 |
COUNT(列) / SUM / AVG / MAX / MIN |
忽略 NULL 行 |
ORDER BY 列 ASC |
NULL 排在最前 |
NULL IN (1,2,NULL) |
NULL（不是 TRUE） |
1 NOT IN (2,NULL) |
NULL（导致查不到数据，见 11.2） |
源文档要点：未插入的列值为 NULL，NULL 不是 0，参与任何运算结果仍为 NULL。
典型坑与处理函数：
-- 坑 1：计算年薪，津贴为 NULL 时整个结果变 NULL
SELECT ename, sal * 12 + bonus AS 年薪 FROM emp; -- bonus 为 NULL 则年薪为 NULL
SELECT ename, sal * 12 + IFNULL(bonus, 0) AS 年薪 FROM emp; -- 正确
-- 坑 2：AVG 忽略 NULL，分母变小导致平均值偏高
SELECT AVG(bonus) AS 只算有津贴的人的平均值,
SUM(bonus)/COUNT(*) AS 按全员计算的平均值,
AVG(IFNULL(bonus,0)) AS 等价于按全员计算
FROM emp;
-- 坑 3：条件判断必须用 IS NULL
SELECT * FROM emp WHERE deptno = NULL; -- 永远 0 行
SELECT * FROM emp WHERE deptno IS NULL; -- 正确
SELECT * FROM emp WHERE deptno <=> NULL; -- 正确（NULL 安全比较）
-- 坑 4：想"排除某值"时会连 NULL 行一起丢掉
SELECT * FROM emp WHERE deptno <> 2; -- deptno 为 NULL 的行也不返回
SELECT * FROM emp WHERE deptno <> 2 OR deptno IS NULL; -- 正确常用 NULL 处理函数：
| 函数 | 作用 |
|---|---|
IFNULL(x, y) |
x 为 NULL 时返回 y |
COALESCE(x1, x2, ...) |
返回第一个非 NULL 的值 |
NULLIF(x, y) |
x = y 时返回 NULL，否则返回 x（常用于避免除零） |
IF(cond, a, b) |
条件表达式 |
x <=> y |
NULL 安全等值比较 |
-- 避免除零：分母为 0 时转成 NULL，结果自然为 NULL 而不是报错
SELECT SUM(sal) / NULLIF(COUNT(*), 0) FROM emp WHERE deptno = 99;16.4 大表连接慢的排查思路
按顺序逐项检查：
- 先看执行计划
EXPLAIN SELECT ...; -- 关注 type、key、rows、Extra
EXPLAIN FORMAT=JSON SELECT ...;- 检查连接列索引：被驱动表连接列必须有索引；
Extra出现Using join buffer即为缺索引的铁证。
SHOW INDEX FROM emp;
SELECT index_name, column_name FROM information_schema.statistics
WHERE table_schema='aaa' AND table_name='emp';- 检查隐式类型转换（极易被忽视的索引失效原因）
-- emp.deptno 是 tinyint，dept.deptno 是 varchar → 类型不一致，索引失效
-- 字符集/排序规则不一致（utf8 vs utf8mb4）同样会失效
SELECT table_name, column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema='aaa' AND column_name='deptno';-
检查驱动表选择：确认小表在前；必要时用
STRAIGHT_JOIN或调整索引引导优化器。 -
检查是否有临时表和排序：
Using temporary+Using filesort组合通常意味着GROUP BY/ORDER BY无索引可用。
SHOW STATUS LIKE 'Created_tmp%'; -- Created_tmp_disk_tables 增长说明落盘了
SHOW STATUS LIKE 'Sort_merge_passes'; -- 增长说明 sort_buffer 不足- 检查统计信息是否过期（优化器基于统计信息估算，过期会选错计划）
ANALYZE TABLE emp, dept;
SHOW TABLE STATUS LIKE 'emp';- 查看实时执行状态与锁等待
SHOW PROCESSLIST; -- 或 SHOW FULL PROCESSLIST
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM sys.innodb_lock_waits; -- 5.7+
SHOW ENGINE INNODB STATUS;- 开慢查询日志抓取真实慢 SQL
[mysqld]
slow_query_log=1
long_query_time=1
log_queries_not_using_indexes=1mysqldumpslow -s t -t 20 /var/log/mysql/slow.log- 用 profiling 定位耗时阶段（5.x）
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;- 仍然慢的最后手段：
- 拆分 SQL：把大连接拆成多次简单查询，在应用层组装（互联网业务常用）；
- 适度反范式：冗余需要的列，消灭连接；
- 加大
join_buffer_size/sort_buffer_size（仅缓解，不治本）； - 引入汇总表/物化视图，定时刷新；
- 分库分表后跨库连接无法进行，只能在应用层聚合。
常见坑速查表：
| 现象 | 可能原因 |
|---|---|
| 结果行数远多于预期 | 忘写连接条件（笛卡尔积）或一对多膨胀 |
LEFT JOIN 的空行不见了 |
右表筛选条件写在了 WHERE |
NOT IN 查不到任何数据 |
子查询结果含 NULL |
SUM / AVG 数值偏大 |
连接导致行复制被重复累加 |
| 加了索引仍然全表扫描 | 类型/字符集不一致、对列使用函数、% 前置模糊查询、隐式转换 |
| 报 ambiguous | 同名列未加表前缀 |
| 报 1093 | UPDATE/DELETE 子查询引用了同一张目标表 |
GROUP BY 报 1055 |
ONLY_FULL_GROUP_BY 生效 |
GROUP_CONCAT 结果不全 |
超过 group_concat_max_len 被静默截断 |
十七、练习
17.1 表结构练习
题目 1：将 emp 表由初始结构扩展为如下结构（源文档练习题）：
初始结构：
create table emp (
ename varchar(20),
sex char(1),
hiredate date,
sal decimal(10,2),
deptno tinyint(1)
);目标结构：
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| empno | int(11) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | tinyint(1) | YES | | NULL | |
| managerno | int(11) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+参考答案：
alter table emp add empno int first; -- empno 加到最前
alter table emp add birthday date after sex; -- birthday 加到 sex 之后
alter table emp add managerno int; -- managerno 加到最后（默认）
truncate table emp; -- 清空数据，保留结构
desc emp; -- 验证结构配套的 ALTER TABLE 子句速查：
| 子句 | 作用 |
|---|---|
add 字段 |
新增字段，默认在最后 |
add 字段 first |
新增字段到最前 |
add 字段 after 已有字段 |
新增到某字段之后 |
drop 字段 |
删除字段 |
modify 字段 类型 |
修改字段数据类型 |
change 旧字段 新字段 类型 |
修改字段名（也可同时改类型） |
modify与change都可修改数据类型；但只有change能修改列名，且必须写原列名。
-- 相关操作示例
alter table emp add age tinyint(1);
alter table emp drop age;
alter table emp add manager varchar(30) after hiredate;
alter table emp add manager varchar(30) first;
alter table emp modify manager varchar(40);
alter table emp change manager boss varchar(30);17.2 单表查询练习
题目 2：查询男员工的姓名和工资。
select ename, sal from emp where sex='m';题目 3：查询工资大于 8000 的员工。
select * from emp where sal > 8000;题目 4：查询工资在 4000~8000 之间（含边界）的员工，写出两种方式。
select * from emp where sal >= 4000 and sal <= 8000;
select * from emp where sal between 4000 and 8000;题目 5：查询 2001 年入职的员工，写出尽可能多的方式。
select * from emp where hiredate >= '2001-01-01' and hiredate <= '2001-12-31';
select * from emp where hiredate like '2001%';
select * from emp where year(hiredate) = 2001;
select * from emp where substr(hiredate, 1, 4) = 2001;
select * from emp where hiredate regexp '2001.*';题目 6：按性别升序、工资降序排列所有员工。
select * from emp order by sex, sal;
select * from emp order by sex desc, sal desc;题目 7：查询工资最低的三个人、工资最高的三个人。
select * from emp order by sal limit 3; -- 最低三人
select * from emp order by sal desc limit 3; -- 最高三人题目 8：查询工资从高到低第 3~5 名的姓名和工资。
select ename, sal from emp order by sal desc limit 2, 3;题目 9：统计公司有多少个部门（去重），并给结果起别名 deptcount。
select count(distinct deptno) deptcount from emp;题目 10：统计每个部门的人数；统计男女员工各多少人；统计每个部门男女各多少人。
select deptno, count(*) from emp group by deptno;
select sex, count(*) from emp group by sex;
select deptno, sex, count(*) from emp group by deptno, sex;题目 11：查询人数大于 2 的部门。
select deptno, count(*) from emp group by deptno having count(*) > 2;题目 12：统计每个部门的工资总额、最高、最低、平均工资。
select deptno, sum(sal), max(sal), min(sal), avg(sal)
from emp group by deptno;17.3 DML 练习
题目 13：完成下列修改操作。
-- wangwu 性别改为 m
update emp set sex='m' where ename='wangwu';
-- wangwu 工资加 500
update emp set sal=sal+500 where ename='wangwu';
-- 2 号部门所有员工工资涨 10%
update emp set sal=sal*1.1 where deptno=2;
-- mark 调到 2 号部门且工资加 1000
update emp set deptno=2, sal=sal+1000 where ename='mark';
-- 只有 john 和 mark 涨 10%（两种写法）
update emp set sal=sal*1.1 where ename='john' or ename='mark';
update emp set sal=sal*1.1 where ename in ('john','mark');
-- 除 john 和 mark 外都涨 10%（两种写法）
update emp set sal=sal*1.1 where ename != 'john' and ename != 'mark';
update emp set sal=sal*1.1 where ename not in ('john','mark');
and/or/not等逻辑操作符只能在where之后使用。
题目 14：删除 empno 为 9 的员工。
delete from emp where empno=9;17.4 多表查询练习
题目 15：查询每个员工的姓名、工资、所在部门编号和部门位置（内连接）。
select a.ename, a.sal, b.deptno, b.loc
from emp a
inner join emp1 b on a.deptno = b.deptno;题目 16：用表别名的两种写法查询两表关联数据。
-- 方式 1：使用 AS 关键字
select a.id, a.name, b.age
from name AS a, age AS b
where a.id = b.id;
-- 方式 2：表名后直接加别名
select a.id, a.name, b.age
from name a, age b
where a.id = b.id;题目 17：查出比 wangwu 工资高的人的姓名和工资（子查询）。
select ename, sal
from emp
where sal > (select sal from emp where ename='wangwu');题目 18：列出所有部门及其员工，没有员工的部门也要显示。
SELECT d.deptno, d.deptname, e.ename, e.sal
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
ORDER BY d.deptno, e.sal DESC;题目 19：找出没有任何员工的部门。
-- 写法 1：LEFT JOIN + IS NULL
SELECT d.deptno, d.deptname FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;
-- 写法 2：NOT EXISTS
SELECT d.deptno, d.deptname FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);题目 20：查询每个员工及其直接领导的姓名（自连接）。
SELECT e.ename AS 员工, m.ename AS 领导
FROM emp e
INNER JOIN emp m ON e.managerno = m.empno
ORDER BY e.empno;题目 21：查询工资高于本部门平均工资的员工。
-- 关联子查询版
SELECT e.ename, e.sal, e.deptno FROM emp e
WHERE e.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno = e.deptno);
-- 派生表 JOIN 版（更快）
SELECT e.ename, e.sal, e.deptno
FROM emp e
INNER JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno
WHERE e.sal > t.avg_sal;题目 22：取出每个部门工资最高的员工的完整信息（至少写两种）。
-- 写法 1：行子查询 + IN
SELECT empno, ename, sal, deptno FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno);
-- 写法 2：派生表 JOIN
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
INNER JOIN (SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal = t.max_sal;
-- 写法 3：NOT EXISTS
SELECT e.empno, e.ename, e.sal, e.deptno FROM emp e
WHERE NOT EXISTS (SELECT 1 FROM emp e2 WHERE e2.deptno = e.deptno AND e2.sal > e.sal);题目 23：统计每个部门的人数与成员名单（名单用逗号拼接）。
SELECT d.deptname, COUNT(e.empno) AS 人数,
IFNULL(GROUP_CONCAT(e.ename ORDER BY e.sal DESC), '无') AS 成员
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.deptname;题目 24：用 UNION 模拟全外连接，列出所有部门与所有员工的对应关系（包括空部门和无部门员工）。
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
UNION
SELECT d.deptno, d.deptname, e.empno, e.ename
FROM dept d RIGHT JOIN emp e ON d.deptno = e.deptno;题目 25：给 shenzhen 所在部门的全部员工涨薪 10%（关联更新）。
-- 先验证影响范围
SELECT e.ename, e.sal FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno WHERE d.location='shenzhen';
-- 再执行更新
UPDATE emp e
INNER JOIN dept d ON e.deptno = d.deptno
SET e.sal = e.sal * 1.1
WHERE d.location = 'shenzhen';题目 26：删除部门表中已不存在的部门下的员工（关联删除）。
DELETE e FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno IS NULL;题目 27：按入职年份统计人数与名单。
SELECT YEAR(hiredate) AS 入职年份, COUNT(*) AS 人数,
GROUP_CONCAT(ename ORDER BY hiredate) AS 名单
FROM emp GROUP BY YEAR(hiredate) ORDER BY 入职年份;题目 28：创建一个视图，封装"员工 + 部门"的常用查询，并用它统计各部门平均工资。
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sex, e.sal, e.hiredate,
e.deptno, d.deptname, d.location
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
SELECT deptname, COUNT(*) AS 人数, ROUND(AVG(sal),2) AS 平均工资
FROM v_emp_dept
GROUP BY deptname
ORDER BY 平均工资 DESC;17.5 概念思考题
题目 29：内连接中把筛选条件写在 ON 和写在 WHERE，结果是否相同？外连接呢？为什么？
答案：
- 内连接：相同。内连接不会产生补 NULL 的行，
ON与WHERE的过滤时机对结果没有影响，优化器生成的执行计划也一致。 - 外连接：不同。
ON在"补 NULL 行"之前生效，只影响哪些右表行参与匹配，左表所有行仍会保留；WHERE在补 NULL 行之后生效，针对右表列的条件会把补出的 NULL 行过滤掉，导致外连接退化为内连接。因此外连接中对右表列的筛选条件必须写在ON里。
题目 30：WHERE deptno NOT IN (SELECT deptno FROM emp) 为什么可能返回空集？如何修正？
答案：因为 emp.deptno 中存在 NULL。NOT IN 展开为一串 <> 的 AND 组合，任意值 <> NULL 结果是 UNKNOWN，与 TRUE 做 AND 仍为 UNKNOWN，条件永远不成立，故返回空集。修正方法：子查询加 WHERE deptno IS NOT NULL，或改用 NOT EXISTS、LEFT JOIN ... IS NULL。
题目 31：UNION 与 UNION ALL 该如何选择？
答案：UNION 会去重，需要临时表 + 排序/哈希，数据量大时开销明显；UNION ALL 直接追加，几乎无额外成本。只要业务上确定不会有重复行、或允许重复，就用 UNION ALL；确实需要去重时才用 UNION。
题目 32：MyISAM 与 InnoDB 最关键的三个区别是什么？生产该选哪个？
答案：① 事务：InnoDB 支持、MyISAM 不支持；② 锁粒度：InnoDB 行级锁、MyISAM 表级锁；③ 崩溃恢复与外键：InnoDB 有 redo/undo 可自动恢复并支持外键，MyISAM 无。生产环境统一选 InnoDB。
题目 33：Memory 引擎的表重启后会怎样？为什么？
答案：数据全部丢失，只剩表结构。因为 Memory 引擎的数据只存放在内存中，磁盘上仅保留 .frm 结构文件。
题目 34：为什么 WHERE YEAR(hiredate)=2001 比 WHERE hiredate >= '2001-01-01' AND hiredate < '2002-01-01' 慢？
答案：前者对索引列使用了函数，MySQL 无法直接利用 hiredate 上的 B+Tree 索引做范围定位，只能全表扫描逐行计算 YEAR()；后者是纯范围比较，可以走索引范围扫描（type=range）。
题目 35：多表连接后 SUM(sal) 结果偏大，可能是什么原因？如何解决？
答案：一对多连接导致"一"方的行被复制多份，SUM 把同一个值重复累加了（行数膨胀）。解决方法：把每个"多"方先各自 GROUP BY 聚合成一行，再作为派生表与主表连接；或改用 EXISTS 做存在性判断，避免行复制。
附录 A：SQL 语言分类与基础语法速查
本附录汇总源文档中的基础语法内容，作为多表查询的前置知识备查。
A.1 SQL 语言分类
SQL 由 ANSI 最早定义标准（SQL-86），各数据库通用但存在少量差异。
| 分类 | 全称 | 关键字 | 作用 |
|---|---|---|---|
| DQL | Data Query Language | select |
查询数据 |
| DDL | Data Definition Language | create、drop、alter、truncate、rename |
对库/表结构操作（定义数据库对象） |
| DML | Data Manipulation Language | insert、update、delete |
对表内容操作 |
| DCL | Data Control Language | grant、revoke |
权限授权与回收 |
A.2 SELECT 基本格式
select [*/字段] from 表名 where 字段 = 值 [or/and 字段 = 值];select host, user, password from mysql.user
where host='127.0.0.1' and user='root';order by 字段 [asc|desc] -- asc 升序（默认），desc 降序
limit N -- 取前 N 行
limit M, N -- 从位置 M（从 0 开始）取 N 行
where与order by一起使用时，where在前、order by在后。
A.3 数据类型
数值类型
| 类型 | 范围/说明 |
|---|---|
tinyint |
小整数，-128~127；unsigned 时 0~255 |
smallint |
短整数，unsigned 时 0~65535 |
mediumint |
中等整数 |
int |
标准整数，11 位 |
bigint |
大整数，用于大表字段 |
float |
单精度浮点数 |
double |
双精度浮点数 |
decimal(M,D) |
定点数；M 为总位数（整数位+小数位），D 为小数位；金额必须用 decimal |
字符类型
| 类型 | 说明 |
|---|---|
char(M) |
定长，每个值占用 M 字节；不足 M 时右侧补空格，检索时去掉填补的空格 |
varchar(M) |
变长，每个值占用实际字节 + 1 个记录长度的字节（L+1） |
char与varchar最高均支持 255 个字符。
create table ab(v varchar(4), c char(4));
insert into ab values('ab ', 'ab ');
select * from ab;+-----+-----+
| v | c |
+-----+-----+
| ab | ab |
+-----+-----+时间类型
| 类型 | 含义 |
|---|---|
date |
年月日 |
year |
年 |
time |
时分秒 |
datetime |
年月日 时分秒 |
timestamp |
时间戳，常用于自动插入或更新系统时间 |
A.4 DDL：库与表操作
-- 库操作
drop database aaa;
create database aaa;
CREATE SCHEMA aaa; -- 与 CREATE DATABASE 等价
show create database aaa;
-- 不存在则创建，存在则给警告
create database if not exists aaa;
show warnings; -- 只能查看上一句的警告+-------+------+-------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------+
| Note | 1007 | Can't create database 'aaa'; database exists |
+-------+------+-------------------------------------------------+rename table ccc to ab2; -- 修改表名
truncate table ab2; -- 截断表，数据不可恢复，结构保留库没有改名命令，可直接在操作系统层面
mv目录（InnoDB 慎用，见第二章）。
use aaa;
-- 建表（单行写法）
create table emp (
ename varchar(20),
sex char(1),
hiredate date,
sal decimal(10,2),
deptno tinyint(1)
);
-- 同样可以分行写，更易阅读，但仍是一条语句
create table emp
(ename varchar(20),
sex char(1),
hiredate date,
sal decimal(10,2),
deptno tinyint(1));
show create table emp;
DESC emp;
-- 修改引擎和字符集
alter table emp engine=memory;
alter table emp default charset=utf8;
rename table emp to emp1;
RENAME TABLE emp TO emp1;
drop table emp;
-- 建表时直接指定非默认引擎/字符集
create table emp (
ename varchar(20),
sex char(1),
hiredate date,
sal decimal(10,2),
deptno tinyint(1)
) engine=memory default charset=utf8;
CREATE TABLE emp (
ename VARCHAR(20),
sex CHAR(1),
hiredate DATE,
sal DECIMAL(10,2),
deptno TINYINT(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 部门表，deptno 与 emp 表对应
create table dept (
deptno tinyint(1),
deptname varchar(30),
location varchar(50)
);A.5 DML：INSERT / UPDATE / DELETE
-- 插入单条（完整列）
insert into aaa.emp values
(1,'boss','m','1964-08-08','1995-01-01','20000','1','1');
INSERT INTO emp VALUES (1,'boss','m','1964-08-08','1995-01-01',20000,1,1);
-- 一次插入多条
insert into emp values
(1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),
(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1');
INSERT INTO emp VALUES
(2,'zhangsan','m','1967-04-05','1995-04-11',15000,2,1),
(3,'lisi','f','1973-01-28','1998-11-21',13000,3,1);
-- 只插入指定列，其余列为 NULL
insert into emp (ename, sex) values ('lisi', 'm');
INSERT INTO emp (ename, sex) VALUES ('lisi','m');数字可不加引号，其他类型需加引号。未插入的列值为 NULL，NULL 不是 0，参与任何运算结果仍为 NULL。
UPDATE 与 DELETE 见 17.3 练习题，语法要点：
UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件;
DELETE FROM 表名 WHERE 条件;A.6 DQL 基础查询
select * from emp; -- 查看所有列
select ename, sal from emp; -- 查看指定列
select distinct deptno from emp; -- 去重
SELECT * FROM emp;
SELECT ename, sal FROM emp;
SELECT DISTINCT deptno FROM emp;
SELECT * FROM emp WHERE sex='m';
SELECT * FROM emp WHERE sal > 8000;
SELECT * FROM emp WHERE sal BETWEEN 4000 AND 8000;A.7 DCL：权限管理
GRANT 授权
-- 授权 aa@localhost 对 abab.emp 拥有 select、insert 权限
grant select, insert on abab.emp to 'aa'@'localhost' identified by '123';
flush privileges;
GRANT SELECT, INSERT ON abab.emp TO 'aa'@'localhost' IDENTIFIED BY '123';
FLUSH PRIVILEGES;
-- 授权 aaa 所有权限，可操作所有库所有表
grant all on *.* to 'aaa'@'localhost' identified by '123';
GRANT ALL ON *.* TO 'aaa'@'localhost' IDENTIFIED BY '123';
-- 授权 ccc 所有权限，可操作 abab 库下所有表
grant all on abab.* to 'ccc'@'localhost' identified by '123';REVOKE 回收权限
revoke select on abab.emp from 'aa'@'localhost';
flush privileges;
REVOKE SELECT ON abab.emp FROM 'aa'@'localhost';
FLUSH PRIVILEGES;远程登录授权
-- % 代表所有 IP
grant all on *.* to 'aa'@'%' identified by '123';
flush privileges;
GRANT ALL ON *.* TO 'aa'@'%' IDENTIFIED BY '123';
-- 指定单一 IP
grant all on *.* to 'aa'@'10.0.0.61' identified by '123';
flush privileges;
GRANT ALL ON *.* TO 'aa'@'10.0.0.61' IDENTIFIED BY '123';
FLUSH PRIVILEGES;客户端测试：
/usr/local/mysql56/bin/mysql -u aa -h10.0.0.61 -p123 -P 3307| 参数 | 含义 |
|---|---|
-u |
登录用户名 |
-h |
远程主机 IP |
-p |
密码 |
-P |
端口（默认 3306 可省略） |
-S |
sock 文件路径 |
附录 B：常用函数
B.1 信息函数
select user(); -- 当前登录用户
select database(); -- 当前所在数据库
select version(); -- 数据库版本
SELECT USER();
SELECT DATABASE();
SELECT VERSION();B.2 password() 加密函数
show variables like '%password%';+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON | -- ON 表示使用兼容老密码的 16 位格式
+---------------+-------+B.3 字符函数
| 函数 | 作用 |
|---|---|
concat() |
连接字符串 |
left() |
左截取 |
right() |
右截取 |
length() |
统计字段值长度 |
substr() |
截取字符串 |
upper() |
转大写 |
lower() |
转小写 |
select concat(ename, ' salary is ', sal) from emp;
select left(ename, 2) from emp;
select right(ename, 2) from emp;
select length(ename) from emp;
select substr(ename, 2, 2) from emp; -- 从第 2 个字符开始截取 2 个字符
select upper(ename) from emp;
select lower(ename) from emp;
select upper(lower(ename)) from emp; -- 函数嵌套
SELECT CONCAT(ename, ' salary is ', sal) FROM emp;
SELECT LEFT(ename, 2) FROM emp;
SELECT SUBSTR(ename, 2, 2) FROM emp;多表查询中常用的补充字符函数：
| 函数 | 作用 |
|---|---|
GROUP_CONCAT() |
分组内多行值拼接（见 13.4） |
SUBSTRING_INDEX(str, delim, n) |
按分隔符取第 n 段（配合 GROUP_CONCAT 取 Top1） |
FIND_IN_SET(str, strlist) |
返回 str 在逗号列表中的位置（配合 GROUP_CONCAT 取 TOP N） |
CONCAT_WS(sep, a, b, ...) |
带分隔符拼接，自动忽略 NULL |
B.4 数学函数
| 函数 | 作用 |
|---|---|
mod() |
取余 |
round() |
四舍五入 |
select mod(10, 3);
select round(4.444444);
select round(4.544444);
select round(4.544444, 2); -- 保留 2 位小数
SELECT MOD(10, 3);
SELECT ROUND(4.444444);
SELECT ROUND(4.544444, 2);B.5 日期函数
select current_time();
select current_date();
select current_timestamp();
select now();
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
SELECT NOW();
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());统计报表中常用的补充日期函数：
| 函数 | 作用 |
|---|---|
DATE_FORMAT(d, '%Y-%m') |
格式化日期，按月统计的关键 |
TIMESTAMPDIFF(unit, d1, d2) |
计算时间差（YEAR / MONTH / DAY） |
DATEDIFF(d1, d2) |
相差天数 |
DATE_ADD(d, INTERVAL n unit) / DATE_SUB() |
日期加减 |
LAST_DAY(d) |
当月最后一天 |
CURDATE() / CURTIME() |
当前日期 / 时间 |
附录 C：MySQL 的帮助系统
MySQL 自带三种帮助途径：官方文档、man 手册、命令行帮助。
help;
help create table;
? contents;
? CONTENTS;? contents 列出多个分类：
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers可逐级查询：
? Account Management;
? CREATE USER;
? CREATE TABLE;附录 D：运维建议
| 场景 | 建议 |
|---|---|
| 生产默认引擎 | 优先使用 InnoDB |
| 高并发写入 | 开启 binlog，使用行级锁引擎（InnoDB） |
| 误操作恢复 | 启用 binlog，定期全量备份 + binlog 增量恢复 |
| 慢查询优化 | 开启慢查询日志，使用 EXPLAIN 分析执行计划 |
| 字符集 | 统一使用 UTF-8（推荐 utf8mb4），避免乱码与连接列索引失效 |
| 多表连接 | 连接列必须建索引，小表驱动大表，连接表数量控制在 5 张以内 |
| 分组统计 | 保留 ONLY_FULL_GROUP_BY，用 GROUP_CONCAT 时调大 group_concat_max_len |
| 外键 | 内部系统可用；高并发核心表由应用层保证参照完整性，但关联列必须有索引 |
| 关联更新/删除 | 先 SELECT 验证影响范围，开事务执行，大批量分批处理 |
| 内存参数 | innodb_buffer_pool_size 取物理内存 50%~75%（专用库），会话级缓冲不可设过大 |
| 视图 | 用于查询封装与权限隔离，不要通过视图做写操作 |
相关文档：
- 索引、事务与状态查询：
11-04-索引事务与状态查询.md- MySQL 安装部署：
11-02-MySQL安装部署.md
此处评论已关闭