mysql数据库应用①

数据库基础知识

在系统环境变量里面的path路径下添加MySQLservicesbin

简单来说，它是一种以 “表格（Table）” 为核心来存储数据的数据库，数据被组织成一张张类似 Excel 表格的结构，并且表与表之间可以通过 “关系”（比如外键）关联起来。
这里的 “关系” 有两层含义：
数据存储的 “表格关系”：数据按行（记录）和列（字段）整齐排列，就像 Excel 里的工作表，结构清晰。
表与表之间的关联关系：不同的表可以通过共同的字段（比如用户 ID）建立联系，避免数据重复存储，保证数据一致性。
举个例子：你做一个小程序的用户系统，会有两张表：
user 表：存用户的 ID、昵称、手机号
order 表：存订单的 ID、用户 ID、订单金额
两张表通过 user_id 字段关联，就能知道每个订单属于哪个用户，这就是 “关系” 的体现。

概念	通俗解释	作用举例
表（Table）	数据库里的“工作表”，就像 Excel 里的 Sheet	比如 user 表存所有用户数据
行（Row/记录）	表中的一条数据，对应 Excel 里的一行	比如 id=1、name=张三、phone=138xxxx 这一条数据
列（Column/字段）	表中的一个属性，对应 Excel 里的一列	比如 name、phone、age 这些属性
主键（Primary Key）	表中用来唯一标识一条记录的字段	比如 user_id，每个用户的ID都是唯一的，不会重复
外键（Foreign Key）	用来关联其他表的字段，建立表与表的关系	订单表的 user_id 关联用户表的 id，知道订单属于哪个用户
索引（Index）	类似书的目录，能大幅加快数据查询速度	给 phone 字段建索引，按手机号查用户会快很多
约束（Constraint）	给字段加的规则，保证数据的合法性	比如 age 字段设置 CHECK(age>0)，不能存负数年龄

常见的关系型数据库

数据库	特点	适用场景
MySQL	开源免费、轻量、社区活跃，性能优秀	中小项目、小程序、Web 后端（你正在装的就是它）
PostgreSQL	开源免费、功能强大，支持复杂查询和高级特性	中大型项目、数据仓库、地理信息系统
Oracle	功能极强、稳定性高，企业级首选	银行、金融、大型企业核心系统（收费昂贵）
SQL Server	微软出品，和 .NET 生态适配好	Windows 平台的企业项目、ASP.NET 开发
SQLite	轻量、无服务器，文件型数据库	手机APP、桌面软件、嵌入式设备（本地存储）

SQL

基础通用规则

1. 大小写不敏感
   关键字（SELECT/FROM）大写小写都行，推荐关键字大写，表名/列名小写，代码更清晰。
   例：select * from user 和 SELECT * FROM user 效果完全一样。
2. 语句必须以分号 ; 结尾
   这是 SQL 语句的结束标志，多条语句必须用分号分隔。
3. 注释写法（通用）
   • 单行注释：-- 注释内容（两个横杠 + 空格）
   • 多行注释：/* 注释内容 */
4. 空格/换行不影响执行
   为了好看可以随意换行，数据库只认语法和分号。
5. 字符串/文本用单引号 ' '
   例：'张三'、'13800138000'，双引号仅部分数据库支持，通用写法用单引号。

SQL 四大核心分类

缩写	全称	中文名称	核心作用	常用命令	大白话理解
DDL	Data Definition Language	数据定义语言	定义/修改数据库、表的结构	CREATE、ALTER、DROP、TRUNCATE	改「房子的户型、结构」，比如建库、建表、加字段、删表
DML	Data Manipulation Language	数据操纵语言	操作表内的行数据	INSERT、UPDATE、DELETE	改「房子里的家具物品」，比如新增数据、修改数据、删除数据
DQL	Data Query Language	数据查询语言	查询表内的数据	SELECT	看「房子里的东西」，90%的SQL都是查询，使用频率最高
DCL	Data Control Language	数据控制语言	管理用户、控制访问权限	CREATE USER、GRANT、REVOKE、DROP USER	管「谁能进房子、能碰什么东西」，比如创建用户、给权限、撤权限

补充分类（常用）

缩写	全称	中文名称	核心作用	常用命令
TCL	Transaction Control Language	事务控制语言	控制数据库事务的提交和回滚	COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点）

最通俗的一句话总结

• 改库表结构 → DDL
• 改表里数据 → DML
• 查表里数据 → DQL
• 管用户权限 → DCL
• 管事务提交回滚 → TCL

常见误区：部分旧教材会把DQL归到DML里，因为广义的DML包含增删改查，但现在行业通用的分类都是把DQL单独拿出来，因为查询的使用频率和复杂度远高于增删改。

最常用通用 SQL 语法

1. 查询数据（DQL）

通用格式：

1. -- 1. 查询指定列
2. SELECT 列名1, 列名2 FROM 表名;
4. -- 2. 查询所有列（* 代表所有）
5. SELECT * FROM 表名;
7. -- 3. 带条件查询
8. SELECT * FROM 表名 WHERE 条件;
10. -- 4. 排序（ASC升序/DESC降序）
11. SELECT * FROM 表名 ORDER BY 列名 DESC;

复制代码

示例：

1. -- 查询用户表中年龄大于18的所有数据，按ID倒序
2. SELECT * FROM user WHERE age > 18 ORDER BY id DESC;

复制代码

2. 新增数据（DML）

通用格式：

1. -- 给指定列插入数据
2. INSERT INTO 表名 (列名1, 列名2) VALUES ('值1', 值2);

复制代码

示例：

1. -- 插入用户姓名和年龄
2. INSERT INTO user (name, age) VALUES ('李四', 20);

复制代码

3. 修改数据（DML）

必须加 WHERE 条件，否则会修改全表数据！

1. UPDATE 表名 SET 列名1=新值1, 列名2=新值2 WHERE 条件;

复制代码

示例：

1. -- 修改ID=1的用户年龄为21
2. UPDATE user SET age=21 WHERE id=1;

复制代码

4. 删除数据（DML）

必须加 WHERE 条件，否则会清空全表！

1. DELETE FROM 表名 WHERE 条件;

复制代码

示例：

1. -- 删除ID=2的用户
2. DELETE FROM user WHERE id=2;

复制代码

5. 建表（DDL）

1. CREATE TABLE 表名 (
2. 列名1 数据类型 约束,
3. 列名2 数据类型 约束,
4. PRIMARY KEY (主键列) -- 主键（唯一标识一条数据）
5. );

复制代码

示例：

1. CREATE TABLE user (
2. id INT PRIMARY KEY, -- 整数，主键
3. name VARCHAR(20), -- 字符串
4. age INT
5. );

复制代码

通用条件运算符（WHERE 里用）

运算符	含义	示例
=	等于	name='张三'
>/<	大于/小于	age>18
>=/<=	大于等于/小于等于	age>=20
AND	并且（多个条件同时满足）	age>18 AND name='张三'
OR	或者（一个满足即可）	age=18 OR age=20
LIKE	模糊查询	name LIKE '%张%'

DDL

DDL 全称 Data Definition Language（数据定义语言），是 SQL 的核心分类之一。
核心作用：专门用来创建、修改、删除 数据库/表的结构（相当于盖房子、改房子、拆房子），不操作表里面的具体数据（数据操作是 DML 的事）。
核心关键字：CREATE（创建）、ALTER（修改）、DROP（删除）、TRUNCATE（清空）、RENAME（重命名）

DDL 管结构，DML 管数据

语言	作用	类比	关键字
DDL	定义库/表的结构（建库、建表、改表结构）	盖房子、改户型、拆房子	CREATE、ALTER、DROP
DML	操作表中的数据（增删改数据）	往房子里放家具、挪家具	INSERT、UPDATE、DELETE

DDL 两大核心操作对象

1. 数据库（Database）：存放所有表的容器
2. 表（Table）：存放具体数据的载体（最常用）

创建数据库

1. -- 通用语法
2. CREATE DATABASE 数据库名;
4. -- 示例：创建名为 test_db 的数据库
5. CREATE DATABASE test_db;
7. -- 进阶：如果不存在则创建（避免报错，推荐）
8. CREATE DATABASE IF NOT EXISTS test_db;

复制代码

查询所有数据库

查看当前 MySQL 中有哪些库

1. SHOW DATABASES;

复制代码

使用数据库

必须先选库，才能操作表！

1. USE 数据库名;
3. -- 示例
4. USE test_db;

复制代码

删除数据库

1. -- 通用语法
2. DROP DATABASE 数据库名;
4. -- 示例：删除 test_db 库
5. DROP DATABASE IF EXISTS test_db;

复制代码

表的 DDL 操作

表是关系型数据库的核心，DDL 负责表的创建、结构修改、删除。

创建表 CREATE TABLE

通用语法

1. CREATE TABLE 表名 (
2. 字段名1 数据类型 [约束], -- 字段=列
3. 字段名2 数据类型 [约束],
4. 字段名3 数据类型 [约束]
5. );

复制代码

通用数据类型

类型	含义	示例
INT	整数（年龄、ID、数量）	age INT
VARCHAR(n)	可变长度字符串（姓名、手机号） n=最大字符数	name VARCHAR(20)
DATE	日期（年月日）	birthday DATE
DATETIME	日期+时间（年月日时分秒）	create_time DATETIME
DECIMAL(m,n)	高精度小数（金额）	price DECIMAL(10,2)

变长和定长意思是
char(10)表示无论你存多少字符都直接占10个字符串空间，性能好
varchar（10）表示存一个字符就之占一个字符的长度，性能差

表约束（保证数据合法性）

约束是给字段加的规则，防止脏数据，DDL 核心：

1. PRIMARY KEY：主键（唯一标识一条数据，非空+唯一，一张表只能有一个）
2. NOT NULL：非空（字段必须填值，不能为 null）
3. UNIQUE：唯一（字段值不能重复）
4. DEFAULT：默认值（不填值时自动用默认值）
5. FOREIGN KEY：外键（关联其他表，新手前期少用）

完整建表示例

创建用户表 user：

1. CREATE TABLE user (
2. id INT PRIMARY KEY, -- 用户ID，主键（唯一非空）
3. name VARCHAR(20) NOT NULL, -- 姓名，非空
4. phone VARCHAR(11) UNIQUE, -- 手机号，唯一
5. age INT DEFAULT 18, -- 年龄，默认18岁
6. create_time DATETIME -- 创建时间
7. );

复制代码

查询表结构

查看表的字段、类型、约束

1. DESC 表名;
2. -- 示例
3. DESC user;

复制代码

快速查看一张表的「简洁结构信息」
它会以表格形式，展示这张表有哪些字段、每个字段是什么类型、能不能为空、是不是主键等核心结构，是你日常最常用的查看表结构命令。
你执行：

1. DESC user;

复制代码

会得到这样的结果（整洁的表格）：

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL
name	varchar(20)	NO		NULL
phone	varchar(11)	YES	UNI	NULL
age	int	YES		18
create_time	datetime	YES		NULL
你能看懂

• 有 id、name、phone 5个字段
• id 是主键（PRI），不能为空
• age 默认值是18
• 一目了然，简洁、快速、够用

查看建表的完整 SQL

1. SHOW CREATE TABLE 表名;

复制代码

查看创建这张表时，用的「完整、原始的SQL代码」
它会把建表的所有细节（字段、约束、字符集、存储引擎等）全部输出，是完整的建表语句。
你执行：

1. SHOW CREATE TABLE user;

复制代码

会直接输出完整的CREATE TABLE语句：

1. CREATE TABLE `user` (
2. `id` int NOT NULL,
3. `name` varchar(20) NOT NULL,
4. `phone` varchar(11) DEFAULT NULL,
5. `age` int DEFAULT 18,
6. `create_time` datetime DEFAULT NULL,
7. PRIMARY KEY (`id`),
8. UNIQUE KEY `phone` (`phone`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

• 这是可以直接复制、拿去重建这张表的完整代码
• 包含了所有约束、引擎、字符集等隐藏细节
• 适合复制修改、备份表结构

案例

根据需求创建表(设计合理的数据类型、长度)
设计一张员工信息表，要求如下：

1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

1. create database case1;
2. use case1;
3. create table wokers(
4. id int,
5. workerId char(10),
6. name varchar(10),
7. sex char(1),
8. age tinyint unsigned,
9. idcard char(18),
10. time date
11. );

复制代码

修改表结构 ALTER TABLE

表创建后，想加列、改列名、删列，都用 ALTER！

新增字段

1. ALTER TABLE 表名 ADD 字段名 数据类型 [约束];
3. -- 示例：给 user 表加地址字段
4. ALTER TABLE wokers ADD address VARCHAR(50);

复制代码

修改字段数据类型/约束

1. ALTER TABLE 表名 MODIFY 字段名 新数据类型 [新约束];
3. -- 示例：把 age 字段改为非空
4. ALTER TABLE wokers MODIFY age INT NOT NULL;

复制代码

重命名字段

1. ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
3. ALTER TABLE wokers CHANGE address home VARCHAR(11);

复制代码

删除字段

1. ALTER TABLE 表名 DROP 字段名;
3. -- 示例：删除 address 字段
4. ALTER TABLE wokers DROP address;

复制代码

重命名表

1. ALTER TABLE 旧表名 RENAME TO 新表名;
3. -- 示例：把 wokers 改名为 user_info
4. ALTER TABLE wokers RENAME TO user_info;

复制代码

删除表 DROP TABLE

1. DROP TABLE IF EXISTS 表名;
3. -- 示例：删除 wokers 表
4. DROP TABLE IF EXISTS wokers;

复制代码

清空表数据 TRUNCATE TABLE

删除表中所有数据，保留表结构，和 DELETE 不同：

1. TRUNCATE TABLE 表名;

复制代码

对比项	含义说明
命令	两个删除数据的SQL语句
类型（DML vs DDL）	决定了命令的底层逻辑和数据库的处理方式
能否回滚	执行后能不能通过ROLLBACK撤销操作、恢复数据
速度	清空数据的执行效率

TRUNCATE 与 DELETE 区别

对比项	DELETE FROM 表	TRUNCATE TABLE 表
支持WHERE条件	✅ 支持，可删除部分数据	❌ 不支持，只能清空全表
自增主键	保留当前值，不会重置	重置为初始值（如1）
触发器触发	✅ 会触发DELETE触发器	❌ 不会触发任何触发器
外键约束	只要外键允许，可正常执行	若被其他表外键引用，通常执行失败
日志记录	记录每一行的删除日志	仅记录表结构变更，不记录行日志

什么时候用哪个？

• 选DELETE：
  1. 要删除部分数据（加WHERE条件）
  2. 操作需要事务安全，可回滚
  3. 不想重置自增主键
  4. 需要触发删除触发器
• 选TRUNCATE：
  1. 要快速清空全表，数据量很大
  2. 不需要回滚，数据可以彻底丢弃
  3. 想要重置自增主键
  4. 不需要触发触发器

简单的事务回滚例子

1. -- 开启事务
2. BEGIN;
3. DELETE FROM user; -- 执行删除
4. ROLLBACK; -- 回滚，user表的数据会恢复
6. -- 但如果换成TRUNCATE
7. BEGIN;
8. TRUNCATE TABLE user; -- 执行时会隐式提交事务
9. ROLLBACK; -- 无效，数据无法恢复

复制代码

DDL 核心注意事项

1. DDL 执行后不可撤销（修改/删除库表结构，一旦执行无法恢复，谨慎操作！）
2. 必须先使用数据库 USE 库名，才能操作表
3. 所有符号（括号、分号、引号）必须用英文符号
4. 一张表必须有主键，这是设计规范
5. 关键字（如 USER、SELECT）不要做表名/字段名

DML

DML（Data Manipulation Language，数据操纵语言）是 SQL 中用于操作数据库表内数据记录的核心语法集合，是日常开发、运维中使用频率最高的 SQL 部分。

• 核心操作：增（INSERT）、改（UPDATE）、删（DELETE）、查（SELECT）
• 补充说明：部分技术体系将查询单独归类为 DQL（数据查询语言），但广义 DML 包含查询操作，本文按通用教学体系完整覆盖。
• 特点：只操作表内行数据，不修改表结构、索引、约束等（表结构操作属于 DDL 范畴）。

下文以学生表 student(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, gender CHAR(1), class_id INT) 为示例表，统一讲解语法。

INSERT：插入数据

用于向数据表中新增行记录。

1. 单行全字段插入

按表字段的默认顺序，为所有字段赋值。

1. INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

复制代码

示例：

1. INSERT INTO student VALUES (1, '张三', 20, '男', 101);

复制代码

注意：值的数量、顺序、数据类型必须和表结构完全一致；自增主键也必须占位填写，或用 NULL 让数据库自动生成。

2. 指定字段插入（推荐写法）

显式指定要赋值的字段，其余字段使用默认值或 NULL。

1. INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

复制代码

示例：

1. INSERT INTO student (name, age, class_id) VALUES ('李四', 21, 102);

复制代码

• 优点：字段顺序可自定义，不受表结构约束；代码可读性更强；非必填字段可省略。
• 约束：设置了 NOT NULL 且无默认值的字段必须显式赋值，否则报错。

3. 批量插入

一次 SQL 插入多行数据，性能远高于循环执行单行插入。

1. INSERT INTO 表名 (字段列表) VALUES
2. (行1值1, 行1值2, ...),
3. (行2值1, 行2值2, ...),
4. ...;

复制代码

示例：

1. INSERT INTO student (name, age, gender) VALUES
2. ('王五', 19, '男'),
3. ('赵六', 22, '女'),
4. ('孙七', 20, '男');

复制代码

4. 插入查询结果

将 SELECT 查询的结果直接写入目标表，常用于数据备份、数据迁移。

1. INSERT INTO 目标表 (字段列表)
2. SELECT 字段列表 FROM 源表 WHERE 筛选条件;

复制代码

示例：将 20 岁以上的学生备份到 student_backup 表

1. INSERT INTO student_backup (id, name, age)
2. SELECT id, name, age FROM student WHERE age > 20;

复制代码

注意：源查询与目标表的字段数量、数据类型必须一一匹配。

5. 进阶：主键/唯一键冲突处理

当插入的数据主键或唯一键重复时，可通过语法控制冲突行为：

• 忽略冲突：冲突则跳过当前行，不报错
  1. INSERT IGNORE INTO student (id, name) VALUES (1, '张三新');

  复制代码
• 冲突则更新：冲突时执行更新操作（常用于「不存在则插入，存在则更新」场景）
  1. INSERT INTO student (id, name, age) VALUES (1, '张三新', 22)
  2. ON DUPLICATE KEY UPDATE name = '张三新', age = 22;

  复制代码

这个语法生效的核心前提：student 表的 id 字段必须是主键（PRIMARY KEY） 或者唯一键（UNIQUE KEY）—— 只有主键 / 唯一键重复冲突时，才会触发后面的 UPDATE 逻辑，普通字段重复不会生效。

情况1：表中没有 id=1 的记录（无冲突）

正常执行 INSERT 插入操作，最终表中会新增一条记录：

id	name	age
1	张三新	22

✅ 情况2：表中已经存在 id=1 的记录（主键冲突）

不会执行插入，也不会报错，自动转为执行 UPDATE 更新操作：
把已存在的 id=1 这条记录的 name 改为 '张三新'，age 改为 22。
举个例子：
执行前表中已有数据：

id	name	age
1	张三	20

执行这条语句后，数据变为：

id	name	age
1	张三新	22
（不会新增行，只会修改原有行）
你上面的写法把值写了两遍（INSERT里写一次，UPDATE里又写一次），可以用 VALUES(字段名) 引用插入的值，简化代码，避免重复：

1. -- 效果和你写的完全一致，更简洁，不容易写错
2. INSERT INTO student (id, name, age) VALUES (1, '张三新', 22)
3. ON DUPLICATE KEY UPDATE
4. name = VALUES(name),
5. age = VALUES(age);

复制代码

VALUES(name) 就代表你 INSERT 时给 name 字段写的值 '张三新'，不需要重复写两遍。

2. 常用场景

• 数据同步/数据回填：比如每日同步用户数据，不管用户是否存在，直接写入最新数据
• 计数统计：比如统计访问量，不存在则插入初始值，存在则+1
  1. -- 经典例子：页面访问计数，不存在则新增，存在则访问量+1
  2. INSERT INTO page_view (page_id, view_count) VALUES (1001, 1)
  3. ON DUPLICATE KEY UPDATE view_count = view_count + 1;

  复制代码
• 批量数据导入：导入数据时无需判断重复，直接覆盖更新

注意事项

1. 必须有主键/唯一键：如果表没有主键或唯一键，这个语法永远只会执行INSERT，永远不会触发UPDATE
2. MySQL 特有语法：这个是MySQL专属语法，其他数据库不通用：
   • PostgreSQL 用 ON CONFLICT DO UPDATE
   • Oracle 用 MERGE INTO
3. 自增ID不会增长：触发UPDATE时，表的自增主键计数器不会增长，不会浪费ID
4. 影响行数返回值：
   • 执行了INSERT：返回影响行数1
   • 执行了UPDATE且数据有变化：返回影响行数2
   • 执行了UPDATE但数据没变化：返回影响行数0

6. INSERT 注意事项

• 字符串、日期类型的值必须用单引号包裹，数值类型无需引号。
• 自增主键建议不手动赋值，交由数据库自动生成。
• 大数据量插入时，优先使用批量插入，减少网络 IO 和事务开销。

UPDATE：更新数据

用于修改表中已存在的行数据。

1. 基础条件更新

1. UPDATE 表名 SET 字段1 = 新值1, 字段2 = 新值2 WHERE 筛选条件;

复制代码

示例：修改 id=1 的学生年龄和姓名

1. UPDATE student SET age = 21, name = '张三改' WHERE id = 1;

复制代码

核心红线：WHERE 子句必须添加。不带 WHERE 的 UPDATE 会更新全表所有行，是生产环境最高危操作之一。

2. 多表关联更新

基于关联表的条件，更新主表的数据。

1. UPDATE 表1 t1
2. JOIN 表2 t2 ON t1.关联字段 = t2.关联字段
3. SET t1.待更新字段 = 新值
4. WHERE 筛选条件;

复制代码

示例：将 101 班所有学生的班级名称同步更新

1. UPDATE student s
2. JOIN class c ON s.class_id = c.id
3. SET s.class_name = c.class_name
4. WHERE c.id = 101;

复制代码

支持 INNER JOIN、LEFT JOIN 等关联方式。

3. 限制行数更新

配合 ORDER BY 和 LIMIT，只更新符合条件的前 N 行，避免大表锁表。

1. UPDATE student SET age = age + 1
2. WHERE gender = '男'
3. ORDER BY id ASC
4. LIMIT 10;

复制代码

4. UPDATE 高危注意事项

1. 执行前先用 SELECT 验证 WHERE 条件，确认影响范围。
2. 生产环境重要操作建议先开启事务，确认无误再提交：
   1. START TRANSACTION; -- 开启事务
   2. UPDATE student SET ... WHERE ...;
   3. -- 确认结果正确后执行 COMMIT; 出错则执行 ROLLBACK;

   复制代码
3. 大表全量更新建议分批执行，避免长时间锁表影响业务。

DELETE：删除数据

用于删除表中的行记录。

1. 基础条件删除

1. DELETE FROM 表名 WHERE 筛选条件;

复制代码

示例：删除 id=1 的学生记录

1. DELETE FROM student WHERE id = 1;

复制代码

同 UPDATE 一样，不带 WHERE 会删除全表所有数据，属于高危操作。

2. 多表关联删除

根据关联表的条件，删除主表中匹配的行。

1. DELETE t1 FROM 表1 t1
2. JOIN 表2 t2 ON t1.关联字段 = t2.关联字段
3. WHERE 筛选条件;

复制代码

示例：删除所有「已毕业班级」的学生记录

1. DELETE s FROM student s
2. JOIN class c ON s.class_id = c.id
3. WHERE c.status = '已毕业';

复制代码

3. DELETE、TRUNCATE、DROP 的核心区别

三者都能删除数据，但本质完全不同，极易混淆：

操作	所属分类	作用	可加WHERE	可回滚	自增主键重置	执行速度
DELETE	DML	删除指定行数据	✅ 支持	✅ 事务内可回滚	❌ 不重置	慢（逐行删除）
TRUNCATE	DDL	清空整张表所有数据	❌ 不支持	❌ 不可回滚	✅ 重置为初始值	极快（直接释放表空间）
DROP	DDL	删除整张表（结构+数据+索引全删）	❌ 不支持	❌ 不可回滚	-	极快

4. DELETE 注意事项

• 清空全表优先使用 TRUNCATE，性能远高于 DELETE FROM 表名。
• 大表删除建议分批删除，避免长事务和锁表。
• 存在外键约束时，删除主表数据需先处理从表关联数据，否则会触发外键约束报错。

DQL

DQL（Data Query Language，数据查询语言）是 SQL 中专门用于检索、查询表中数据的语法集合，核心语法是 SELECT 语句。它是所有 SQL 语法中使用频率最高、体系最庞大、灵活度最高的部分，也是后端开发、数据分析、数据库运维的核心技能。

DQL 仅做数据读取，不会修改表中数据，也不会修改表结构。下文全程沿用之前的示例表逐层拆解：

• 学生表：student(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, gender CHAR(1), class_id INT)
• 班级表：class(id INT PRIMARY KEY, class_name VARCHAR(30), grade VARCHAR(10))

1. 完整 SELECT 书写顺序

1. SELECT [DISTINCT] 字段列表
2. FROM 表名
3. [JOIN 多表连接]
4. [WHERE 行级筛选条件]
5. [GROUP BY 分组字段]
6. [HAVING 分组后筛选条件]
7. [ORDER BY 排序字段]
8. [LIMIT 分页限制]

复制代码

[] 代表可选子句，实际使用时按需组合。

2. 数据库真实执行顺序

数据库引擎会按以下优先级逐阶段执行语句，理解了这个顺序，就能搞懂「WHERE 为什么不能用别名」「聚合函数为什么不能写在WHERE里」等核心问题。

1. 1. FROM/JOIN → 确定数据源，组装多表基础数据
2. 2. WHERE → 对原始行数据进行条件过滤
3. 3. GROUP BY → 对筛选后的数据按字段分组
4. 4. HAVING → 对分组后的统计结果二次筛选
5. 5. SELECT → 计算返回字段、表达式、别名
6. 6. DISTINCT → 对结果集去重
7. 7. ORDER BY → 对最终结果排序
8. 8. LIMIT → 截断返回的行数

复制代码

核心结论：

• WHERE 执行在 SELECT 之前，因此WHERE 中不能使用 SELECT 定义的字段别名
• 聚合函数在 GROUP BY 阶段计算，因此WHERE 中不能使用聚合函数，只能写在 HAVING 或 SELECT 中

---

单表基础查询

SELECT 子句：指定返回字段

查询指定字段（生产环境推荐写法）

显式指定需要的字段，性能更高、可读性更强，避免表结构变更引发业务异常。

1. SELECT id, name, age FROM student;

复制代码

查询全部字段

1. SELECT * FROM student;

复制代码

⚠️ 生产环境禁止使用 SELECT *：会查询无用字段增加网络开销；无法利用覆盖索引；表结构变更时容易引发异常。

字段别名

用 AS 给字段起别名，AS 可以省略；别名包含空格或特殊字符时，需要用反引号包裹。

1. SELECT name AS 姓名, age 年龄, id `学号` FROM student;

复制代码

常量、表达式与函数

SELECT 后可以跟常量、四则运算、函数调用，生成计算列。

1. SELECT
2. name,
3. age + 1 AS 明年年龄, -- 四则运算
4. '在校生' AS 身份, -- 常量值
5. UPPER(name) AS 大写姓名 -- 字符串函数
6. FROM student;

复制代码

结果去重 DISTINCT

对查询结果中完全重复的行去重，DISTINCT 必须写在所有字段最前面。

1. -- 查询所有不重复的班级编号
2. SELECT DISTINCT class_id FROM student;
4. -- 多字段去重：两个字段都相同才算重复
5. SELECT DISTINCT class_id, gender FROM student;

复制代码

这是一条 MySQL DQL 基础查询语句，用来演示 SELECT 子句的三种进阶用法，我逐行逐部分给你拆解解释：

---

完整语句

1. SELECT
2. name,
3. age + 1 AS 明年年龄, -- 四则运算
4. '在校生' AS 身份, -- 常量值
5. UPPER(name) AS 大写姓名 -- 字符串函数
6. FROM student;

复制代码

① name

直接返回 student 表中 name（姓名）字段的原始值，就是普通的字段查询。

② age + 1 AS 明年年龄, -- 四则运算

这是SELECT 中做四则运算的演示：

• age + 1：对表中 age（年龄）字段做计算，每个学生的年龄 +1，就是该学生明年的年龄
• AS 明年年龄：给这个计算出来的新列起一个易读的别名，查询结果中这一列的列名就叫「明年年龄」（AS 可以省略不写）
• -- 四则运算：这是SQL注释，给写代码的人看的，数据库执行时会忽略，这里说明这一行是「四则运算」的用法示例

③ '在校生' AS 身份, -- 常量值

这是SELECT 中写常量值的演示：

• '在校生'：这不是表中的字段，是一个写死的固定字符串
• 最终查询结果中，每一行的「身份」列都会固定显示「在校生」，相当于给所有查询到的学生统一加了一个身份标签
• 注释说明这一行是「常量值」的用法示例

④ UPPER(name) AS 大写姓名 -- 字符串函数

这是SELECT 中调用内置函数的演示：

• UPPER(name)：调用 MySQL 内置的字符串函数 UPPER()，作用是把 name 字段里的小写英文字母全部转为大写（中文、数字、符号不受影响）
  比如 name='zhangsan'，经过 UPPER() 处理后会变成 'ZHANGSAN'
• AS 大写姓名：给这个函数处理后的结果起别名为「大写姓名」
• 注释说明这一行是「字符串函数」的用法示例

⑤ FROM student;

指定本次查询的数据源是 student 这张表。

WHERE 子句：行级条件筛选

WHERE 是 DQL 的核心，用于从全量数据中过滤出符合条件的行，支持多种运算符。

比较运算符（行级条件匹配）

比较运算符	功能说明	语法示例
>	大于	WHERE age > 18
>=	大于等于	WHERE age >= 18
<	小于	WHERE age < 22
<=	小于等于	WHERE age <= 22
=	等于	WHERE gender = '男'
<> 或 !=	不等于（两者等价）	WHERE class_id != 101
BETWEEN ... AND ...	在闭区间范围内（包含最小值和最大值）	WHERE age BETWEEN 18 AND 22
IN(值1, 值2, ...)	匹配列表中的任意一个值，多选一	WHERE class_id IN (101, 102, 103)
LIKE 占位符	模糊匹配： _ 匹配恰好1个任意字符 % 匹配**任意长度（0个及以上）**任意字符	WHERE name LIKE '张%' WHERE name LIKE '__'
IS NULL	判断字段值为 NULL ⚠️ 空值判断必须用 IS/IS NOT，绝对不能用 =NULL	WHERE class_id IS NULL
IS NOT NULL	判断字段值不为 NULL	WHERE class_id IS NOT NULL

逻辑运算符

逻辑运算符	功能说明	优先级	语法示例
AND 或 &&	并且：多个条件必须同时成立	中	WHERE age > 18 AND gender = '女'
OR 或 `		`	或者：多个条件任意一个成立即可
NOT 或 !	非/取反：否定后面的条件	最高	WHERE NOT class_id = 101

优先级说明：NOT > AND > OR，复杂条件建议用括号明确优先级，避免逻辑错误。

1. -- 查询101班年龄大于18的女生
2. SELECT * FROM student
3. WHERE class_id = 101
4. AND age > 18
5. AND gender = '女';

复制代码

范围查询 BETWEEN … AND …

查询字段值在闭区间 [最小值, 最大值] 内的数据，等价于 >= 最小值 AND <= 最大值。

1. -- 查询年龄在18到22岁之间的学生（包含18和22）
2. SELECT * FROM student WHERE age BETWEEN 18 AND 22;

复制代码

集合查询 IN

查询字段值匹配集合中任意一个值的数据，是多个 OR 条件的简写。

1. -- 查询班级编号为101、102、103的学生
2. SELECT * FROM student WHERE class_id IN (101, 102, 103);

复制代码

注意：IN 集合中不能包含 NULL，否则会导致查询结果异常。

模糊查询 LIKE

用于字符串模糊匹配，配合两个通配符使用：

• %：匹配**任意长度（包括0个）**的任意字符
• _：匹配恰好1个任意字符

示例：

1. -- 查询姓张的学生（张开头，后面任意）
2. SELECT * FROM student WHERE name LIKE '张%';
4. -- 查询名字里包含"三"的学生
5. SELECT * FROM student WHERE name LIKE '%三%';
7. -- 查询名字是两个字的学生
8. SELECT * FROM student WHERE name LIKE '__';

复制代码

通配符	匹配规则	长度要求
_（下划线）	匹配恰好1个任意字符	✅ 严格固定：必须是1个，多一个少一个都不行
%（百分号）	匹配任意长度的任意字符	✅ 完全灵活：0个、1个、100个都可以

假设我们有这些名字：张、张三、张小明、张三四五、李三
匹配 '张_'（1个下划线）

规则：姓张，后面必须刚好有1个字符，总共2个字
✅ 能匹配：张三
❌ 不能匹配：张（只有1个字符）、张小明（3个字符）、李三（不姓张）

匹配 '张__'（2个下划线，就是你问的两个_）

规则：姓张，后面必须刚好有2个字符，总共3个字
✅ 能匹配：张小明
❌ 不能匹配：张三（只有2个字符）、张三四五（4个字符）、张（1个字符）

匹配 '张%'（百分号）

规则：姓张，后面有没有字符、有几个都可以，不管多少字
✅ 能匹配：张、张三、张小明、张三四五（所有姓张的，不管几个字全匹配）
❌ 不能匹配：李三（不姓张）

1. 下划线是严格长度匹配，差一个字符都不行：
   '张__' 绝对匹配不了2个字的张三，也匹配不了4个字的张三四五，只能匹配3个字的。

2. 百分号可以匹配空（0个字符）：
   '张%' 连只有一个字的张也能匹配，因为%可以匹配0个字符。

3. 两者可以组合使用：
   '张_三%' → 匹配姓张，第二个字任意，第三个字是三，后面随便有什么。

空值判断 IS NULL / IS NOT NULL

判断字段是否为 NULL，绝对不能用 = NULL 或 != NULL——因为 NULL 与任何值比较结果都是「未知」，永远返回假。

1. -- 查询没有分配班级的学生（class_id为NULL）
2. SELECT * FROM student WHERE class_id IS NULL;
4. -- 查询有班级的学生
5. SELECT * FROM student WHERE class_id IS NOT NULL;

复制代码

排序与分页

ORDER BY 结果排序

对查询的最终结果按指定字段排序，执行在 SELECT 之后，因此支持使用 SELECT 定义的别名。

• ASC：升序（从小到大），默认值，可省略
• DESC：降序（从大到小）

1. -- 按年龄降序排列
2. SELECT * FROM student ORDER BY age DESC;
4. -- 多字段排序：先按班级升序，班级相同按年龄降序
5. SELECT * FROM student ORDER BY class_id ASC, age DESC;

复制代码

LIMIT 分页查询

MySQL 特有的分页语法，用于截断结果集，只返回指定行数的数据，是分页功能的核心。

1. LIMIT 【跳过多少个】, 【拿多少个】

复制代码

语法格式

1. -- 格式1：LIMIT 偏移量, 返回行数（最常用）
2. LIMIT offset, row_count
4. -- 格式2：LIMIT 返回行数 OFFSET 偏移量（SQL标准写法，可读性更好）
5. LIMIT row_count OFFSET offset

复制代码

偏移量从 0 开始计数。

示例

1. -- 查询前5条学生记录
2. SELECT * FROM student LIMIT 5;
4. -- 从第3条开始，查询5条记录
5. SELECT * FROM student LIMIT 2, 5;

复制代码

分页公式

第 pageNum 页，每页 pageSize 条数据：

1. 偏移量 = (pageNum - 1) * pageSize

复制代码

例：第3页，每页10条 → LIMIT 20, 10

注意事项：

1. LIMIT 是 MySQL 专属语法，其他数据库语法不同（Oracle用ROWNUM，SQL Server用TOP）
2. 大表深分页（如 LIMIT 1000000, 10）性能极差，需要专门优化

聚合统计与分组查询

1. 聚合函数

聚合函数对一组数据进行统计计算，最终返回单个结果值，是数据统计的核心。所有聚合函数都会自动忽略 NULL 值。

函数	作用	说明
COUNT(*)	统计结果集的总行数	统计所有行，包含NULL行，最常用
COUNT(字段名)	统计该字段非空的行数	忽略NULL值
SUM(字段)	对数值字段求和	忽略NULL，非数值字段结果为0
AVG(字段)	对数值字段求平均值	忽略NULL
MAX(字段)	求字段最大值	支持数值、日期、字符串
MIN(字段)	求字段最小值	支持数值、日期、字符串

示例：

1. SELECT
2. COUNT(*) AS 总人数,
3. COUNT(class_id) AS 有班级的人数,
4. AVG(age) AS 平均年龄,
5. MAX(age) AS 最大年龄
6. FROM student;

复制代码

⚠️ 高频易错点：COUNT(*)、COUNT(1)、COUNT(字段) 的区别

• COUNT(*) / COUNT(1)：统计总行数，InnoDB 引擎下性能几乎无差别，推荐 COUNT(*)
• COUNT(字段)：统计该字段非NULL的行数，结果可能小于总行数

2. GROUP BY 分组查询

将数据按照指定字段分成多个组，每组返回一条统计结果，通常配合聚合函数使用。

语法示例

1. -- 统计每个班级的人数、平均年龄
2. SELECT
3. class_id,
4. COUNT(*) AS 人数,
5. AVG(age) AS 平均年龄
6. FROM student
7. GROUP BY class_id;

复制代码

SELECT 后面的字段，要么是 GROUP BY 的分组字段，要么是聚合函数，不能出现非分组、非聚合的普通字段，否则语法报错。

错误示例：SELECT name, class_id, COUNT(*) FROM student GROUP BY class_id;
错误原因：name 不是分组字段也不是聚合函数，分组后一个组对应多个name，无法确定返回哪一个
这是一个非常经典的分组统计SQL，我逐句给你拆解，结合例子一看就懂：

从 student（学生表）中，按班级分组，统计出「每个班级有多少个学生」和「每个班级的学生平均年龄」。
SELECT` 后面的内容：要返回的统计结果

语句	作用
class_id	返回班级编号，因为我们按班级分组，每个组对应一个班级
COUNT(*) AS 人数	COUNT(*) 统计每个组里有多少行数据，也就是「每个班级有多少个学生」，用AS给统计结果起别名叫「人数」
AVG(age) AS 平均年龄	AVG(age) 计算每个组里所有学生年龄的平均值，也就是「每个班级的平均年龄」，起别名叫「平均年龄」

GROUP BY class_id`

按 class_id（班级编号）分组，把所有同一个班级的学生，分到同一个小组里。
然后 COUNT(*) 和 AVG(age) 会对每个小组分别计算，而不是对全表计算。

举个实际例子
假设 student 表中有这些原始数据：

id	name	age	class_id
1	张三	20	101
2	李四	21	101
3	王五	19	102
4	赵六	22	102
5	孙七	20	102

执行这个SQL后，返回的结果是：

class_id	人数	平均年龄
101	2	20.5
102	3	20.333

你看：

• 101班有2个学生，平均年龄(20+21)/2=20.5
• 102班有3个学生，平均年龄(19+22+20)/3≈20.333

这就是分组统计的核心逻辑：先分组，再对每个组单独统计。

多字段分组

可以按多个字段组合分组，所有字段都相同才会被分到同一组。

1. -- 统计每个班级不同性别的人数
2. SELECT class_id, gender, COUNT(*) AS 人数
3. FROM student
4. GROUP BY class_id, gender;

复制代码

GROUP BY 字段1, 字段2 的分组规则：

只有当所有分组字段的值，全部都相同的时候，才会被分到同一个组里。
只要有一个字段的值不一样，就是不同的组。

举个简单的理解：

• 单字段分组（只按班级）：同一个班级的所有学生，不管男女，都在同一个组
• 多字段分组（班级+性别）：
  • 101班的男生 → 组1
  • 101班的女生 → 组2
  • 102班的男生 → 组3
  • 102班的女生 → 组4

1. SELECT class_id, gender, COUNT(*) AS 人数：返回每个组的「班级编号」「性别」「该组的学生人数」
2. FROM student：数据源是学生表
3. GROUP BY class_id, gender：按「班级」和「性别」两个字段组合分组

举实际例子
假设学生表有这些原始数据：

id	name	gender	class_id
1	张三	男	101
2	李四	男	101
3	小红	女	101
4	王五	男	102
5	小丽	女	102
6	小美	女	102

执行这个SQL后，返回的结果是：

class_id	gender	人数
101	男	2
101	女	1
102	男	1
102	女	2

你看：

• 101班男生2人、女生1人
• 102班男生1人、女生2人

完美实现了「每个班级按性别统计人数」的需求，这就是多字段分组最常用的场景。

HAVING 分组后筛选

对 GROUP BY 分组后的结果进行二次筛选，聚合函数只能写在 HAVING 中，不能写在 WHERE 中。

示例：

1. -- 筛选出人数大于30的班级
2. SELECT class_id, COUNT(*) AS 人数
3. FROM student
4. GROUP BY class_id
5. HAVING COUNT(*) > 30;

复制代码

先按班级分组，统计每个班级的总人数，然后只保留人数大于30人的班级，把人数不够30的班级全部过滤掉。
步骤1：FROM student
先拿到student表的所有学生数据，比如现在有10个班级，共300个学生。
步骤2：GROUP BY class_id
按班级编号分组，把每个班的学生分到一起，同时算出每个班的人数：

class_id	人数
101	35
102	28
103	40
104	25
…	…

步骤3：HAVING COUNT(*) > 30
对上面分组统计好的结果做二次筛选，只留下人数>30的班级，其他的扔掉：
✅ 留下101班（35人）、103班（40人）
❌ 扔掉102班（28人）、104班（25人）
步骤4：SELECT class_id, COUNT(*) AS 人数
把最终筛选后的结果返回给你：

class_id	人数
101	35
103	40

WHERE 与 HAVING 的核心区别

对比维度	WHERE	HAVING
执行阶段	分组前筛选，在GROUP BY之前执行	分组后筛选，在GROUP BY之后执行
筛选对象	原始表的行数据	分组后的统计结果
聚合函数	❌ 不能使用	✅ 核心使用场景
性能	可以利用索引，性能高	基于分组结果计算，性能较低

优化原则：能用 WHERE 过滤的条件，绝对不要放到 HAVING 里，先过滤再分组能大幅提升性能。

多表连接查询（JOIN）

实际业务中数据分散在多张表中，需要通过关联字段将多张表的数据联合查询，这就是 JOIN 的作用。

笛卡尔积与连接原理

如果直接查询两张表不加连接条件，会产生笛卡尔积：左表的每一行都会和右表的每一行组合，结果行数 = 左表行数 × 右表行数，通常是无意义的脏数据。
JOIN 的本质就是通过连接条件过滤掉笛卡尔积中无意义的行，只保留匹配成功的数据。

内连接 INNER JOIN

只保留两张表中完全匹配连接条件的行，两边匹配不上的都会被丢弃。INNER 可以省略，只写 JOIN 默认就是内连接。

1. -- 查询学生姓名和对应的班级名称
2. SELECT s.name, c.class_name
3. FROM student s
4. JOIN class c
5. ON s.class_id = c.id;

复制代码

结果中不会包含没有班级的学生，也不会包含没有学生的班级。

外连接 OUTER JOIN

外连接会保留某一张表的全部数据，另一张表匹配不上的字段显示为 NULL。分为左外连接和右外连接。

左外连接 LEFT JOIN（最常用）

保留左表的所有行，右表匹配成功则显示对应值，匹配失败则显示 NULL。

1. -- 查询所有学生及其班级名称，没有班级的学生也会显示，班级名称为NULL
2. SELECT s.name, c.class_name
3. FROM student s
4. LEFT JOIN class c
5. ON s.class_id = c.id;

复制代码

✅ 左表：永远是 FROM 后面跟的第一张表
✅ 右表：永远是 JOIN/LEFT JOIN/RIGHT JOIN 后面跟的表

（2）右外连接 RIGHT JOIN

保留右表的所有行，左表匹配失败显示 NULL。实际开发中很少使用，通常可以改写为 LEFT JOIN。

⚠️ 高频易错点：条件写在 ON 和 WHERE 的区别

• 写在 ON 中：在连接匹配时生效，不影响左表的全部行，只是右表不匹配的字段显示NULL
• 写在 WHERE 中：连接完成后对整体结果过滤，会过滤掉不满足条件的行，可能丢失左表数据

示例对比：

1. -- 语句1：条件写在ON中
2. SELECT s.name, c.class_name
3. FROM student s
4. LEFT JOIN class c
5. ON s.class_id = c.id AND c.grade = '大一';
6. -- 结果：保留所有学生，只有大一的班级会显示名称，其他班级的class_name为NULL
8. -- 语句2：条件写在WHERE中
9. SELECT s.name, c.class_name
10. FROM student s
11. LEFT JOIN class c
12. ON s.class_id = c.id
13. WHERE c.grade = '大一';
14. -- 结果：只会显示大一班级的学生，等价于内连接，丢失了其他学生

复制代码

条件写的位置	执行时机	对左表的影响
写在 ON 里	连接匹配阶段生效	✅ 永远保留左表的所有行，右表不匹配的字段显示NULL
写在 WHERE 里	连接完成后，全局过滤生效	❌ 不满足条件的行全部扔掉，包括左表的行，相当于变成内连接

学生表（左表）

name	class_id
张三	101
李四	102
王五	NULL

班级表（右表）

id	class_name	grade
101	一班	大一
102	二班	大二

语句1：条件写在ON中

最终结果：

name	class_name
张三	一班
李四	NULL
王五	NULL
✅ 所有学生都保留了，只是非大一班级的class_name显示为NULL。

语句2：条件写在WHERE中

最终结果：

name	class_name
张三	一班
❌ 李四和王五都丢失了，左连接白写了，结果和普通内连接一模一样。

LEFT JOIN中：

1. 过滤右表的条件，必须写在ON里，写在WHERE里会丢失左表数据
2. 过滤左表的条件，直接写在WHERE里
3. 只要LEFT JOIN的WHERE里出现了右表的非空判断，这个LEFT JOIN就失效了，等价于内连接

4. 自连接

一张表自己和自己连接，本质是把一张表当成两张不同的表来用，常用于处理树形结构、层级关系。

示例：员工表 emp(id, name, manager_id)，manager_id 是上级领导的id，查询每个员工及其上级姓名：

1. SELECT e.name AS 员工名, m.name AS 上级名
2. FROM emp e
3. LEFT JOIN emp m
4. ON e.manager_id = m.id;

复制代码

自连接的本质
就是把一张表，当成两张不同的表来用，仅此而已！
什么时候用？当你要关联的两个数据，都存在同一张表里的时候，就用自连接。

最典型的场景就是你图片里的「员工-上级」层级关系：

• 员工和他的上级领导，都是员工，都存在同一张emp员工表里
• 没有第二张表，所以只能自己和自己连接

给同一张表起两个不同的别名，就相当于变成了两张独立的表：

1. FROM emp e -- 别名e：代表「普通员工」这张表
2. LEFT JOIN emp m -- 别名m：代表「上级领导」这张表

复制代码

现在e和m虽然来自同一张表，但我们可以把它们当成两张完全不同的表来用。

连接条件：把员工和他的上级匹配上

1. ON e.manager_id = m.id

复制代码

• e.manager_id：员工表里，「员工的上级领导的id」
• m.id：上级表里，「领导自己的id」
  通过这个条件，就把员工和对应的上级领导匹配起来了。

假设员工表emp有这些数据：

id	name	manager_id	说明
1	张总	NULL	大老板，没有上级
2	李经理	1	上级是张总
3	王员工	2	上级是李经理

执行你图片里的SQL后，返回的结果是：

员工名	上级名
张总	NULL
李经理	张总
王员工	李经理

自连接没有任何特殊语法，就是普通的LEFT JOIN/内连接：

1. 给同一张表起两个不同的别名，当成两张表
2. 写连接条件，把两张表关联起来
3. 就和连接两张不同的表完全一样，没有任何区别

子查询（嵌套查询）

子查询指嵌套在其他 SQL 语句内部的 SELECT 语句，也叫嵌套查询，常用于复杂的条件判断。

1. 子查询分类

按返回结果的形式，分为4类：

类型	返回结果	常用位置
标量子查询	单个值（一行一列）	WHERE 后做比较
列子查询	一列多行	WHERE 后配合 IN 使用
行子查询	一行多列	WHERE 后做整行匹配
表子查询	多行多列	FROM 后做派生表

2. 标量子查询

返回单个数值，通常配合 =、>、< 等比较运算符使用。

1. -- 查询年龄大于全体平均年龄的学生
2. SELECT * FROM student
3. WHERE age > (SELECT AVG(age) FROM student);

复制代码

3. 列子查询

返回一列多行的结果，通常配合 IN、ANY、ALL 运算符使用。

1. -- 查询所有在大一班级的学生
2. SELECT * FROM student
3. WHERE class_id IN (
4. SELECT id FROM class WHERE grade = '大一'
5. );

复制代码

1. SELECT id FROM class WHERE grade = '大一'

复制代码

从班级表class中，查出所有「年级是大一」的班级编号，比如得到结果：(101, 102)（假设大一有两个班，编号101和102）。
把第一步查到的班级编号列表，代入到外面的SQL里，相当于执行：

1. SELECT * FROM student
2. WHERE class_id IN (101, 102);

复制代码

从学生表中，找出所有class_id（班级编号）在(101, 102)里的学生，也就是所有大一班级的学生。
假如班级表class的数据

id	class_name	grade
101	一班	大一
102	二班	大一
201	三班	大二

执行子查询后得到的班级id列表：(101, 102)
最终查询结果：所有class_id是101或102的学生，也就是所有大一学生。

这个写法和下面的JOIN写法结果100%相同，只是逻辑更直观，新手更容易理解：

1. SELECT s.*
2. FROM student s
3. JOIN class c ON s.class_id = c.id
4. WHERE c.grade = '大一';

复制代码

4. 表子查询（派生表）

返回多行多列的结果，放在 FROM 后面当作一张临时表使用，必须给子查询起别名。

1. -- 查询每个班级的平均年龄，并筛选出平均年龄大于20的班级
2. SELECT *
3. FROM (
4. SELECT class_id, AVG(age) AS avg_age
5. FROM student
6. GROUP BY class_id
7. ) AS t
8. WHERE t.avg_age > 20;

复制代码

5. EXISTS 相关子查询

EXISTS(子查询) 判断子查询是否有返回结果，有则返回 true，没有则返回 false。它是相关子查询，会依赖外层查询的字段。

1. -- 查询有学生的班级
2. SELECT * FROM class c
3. WHERE EXISTS (
4. SELECT * FROM student s
5. WHERE s.class_id = c.id
6. );

复制代码

EXISTS 的特点：只判断是否存在，不关心子查询返回什么内容，因此子查询里写 SELECT 1、SELECT * 性能几乎无差别。

EXISTS的核心规则
EXISTS(子查询) 只做一件事：
✅ 如果子查询能返回至少1行结果 → 返回true，外层的这条数据就保留
❌ 如果子查询返回空，一行都没有 → 返回false，外层的这条数据就扔掉

它完全不关心子查询返回什么内容，只关心「有没有结果」。

执行步骤：

1. 外层先遍历班级表的每一行：比如先拿到第一个班级，班级id=101
2. 把外层的班级id代入子查询：把子查询里的c.id替换成101，执行子查询：
   1. SELECT * FROM student s WHERE s.class_id = 101

   复制代码
3. 判断结果：
   • 如果这个班级有学生，子查询能返回数据 → EXISTS返回true，这个班级保留
   • 如果这个班级是空的，子查询一行都查不到 → EXISTS返回false，这个班级扔掉
4. 遍历下一个班级，重复步骤1-3，直到所有班级都判断完

举实际例子
班级表class的数据

id	class_name
101	一班
102	二班
103	三班

学生表student的数据
只有101班和103班有学生，102班是空班。

执行后的结果

id	class_name
101	一班
103	三班
✅ 空班102被扔掉了，完美实现了「查询有学生的班级」。

SELECT 1和SELECT *性能没区别

这是图片里强调的重点：
因为EXISTS只判断「有没有结果」，完全不关心结果是什么内容，所以：

• 子查询写SELECT 1 → 返回1，有结果
• 子查询写SELECT * → 返回所有字段，有结果
• 子查询写SELECT '随便什么' → 有结果

只要有结果，EXISTS就返回true，所以写什么都一样，甚至写SELECT 1还更省性能，因为不用读取表的字段。

比如

1. 写SELECT *
   子查询匹配到数据时，返回「一行所有字段」→ 数据库看到有行 → 返回 true
   相当于敲门，屋里人喊了一大串话 → 你知道有人
2. 写SELECT 1
   子查询匹配到数据时，返回「一行数字 1」→ 数据库看到有行 → 返回 true
   相当于敲门，屋里人只喊了个 “1” → 你还是知道有人
3. 写SELECT ‘随便什么’
   子查询匹配到数据时，返回「一行随便写的字符串」→ 数据库看到有行 → 返回 true
   相当于敲门，屋里人喊了句 “哈哈” → 你还是知道有人

和IN子查询的核心区别

类型	执行逻辑	适合场景
IN子查询	先执行子查询得到结果列表，再给外层匹配	子查询结果少、数据量小
EXISTS子查询	外层每一条数据，都执行一次子查询判断	子查询结果大、外层数据量小
这两个写法返回的学生数据完全一模一样，没有任何差别。

联合查询 UNION

将多条 SELECT 查询的结果纵向拼接成一个结果集，要求多条查询的字段数量、对应字段的数据类型必须一致。

两种语法区别

• UNION：对最终结果自动去重，性能较低
• UNION ALL：不去重，直接拼接，性能更高，推荐优先使用

示例：

1. -- 查询所有学生和老师的姓名
2. SELECT name FROM student
3. UNION ALL
4. SELECT name FROM teacher;

复制代码

注意：

1. 最终结果的字段名以第一条 SELECT 语句的字段名为准
2. ORDER BY 只能写在最后一条语句末尾，对整个联合结果排序

DQL 进阶补充

1. 窗口函数（MySQL 8.0+）

窗口函数可以在不减少行数的前提下，对分组数据进行计算，实现排名、累计、同比环比等复杂统计，是数据分析的高级语法。
常用窗口函数：ROW_NUMBER()、RANK()、DENSE_RANK()、SUM() OVER() 等。

2. 常用内置函数

SELECT 中可以使用大量内置函数处理数据，分为：

• 字符串函数：CONCAT()、SUBSTRING()、LENGTH()、REPLACE() 等
• 日期函数：NOW()、DATE_FORMAT()、DATEDIFF() 等
• 数值函数：ROUND()、FLOOR()、CEIL()、ABS() 等
• 流程控制函数：IF()、CASE WHEN 等

DQL 最佳实践与性能规范

1. 字段规范：永远使用显式字段查询，禁止 SELECT *
2. 条件优化：优先用 WHERE 过滤数据，减少后续处理的数据量；WHERE 条件尽量匹配索引
3. 连接规范：多表连接优先使用 INNER JOIN，其次 LEFT JOIN；避免产生笛卡尔积
4. 分组优化：分组字段尽量建索引；能用 WHERE 过滤的不要放到 HAVING
5. 分页优化：大表避免深分页，可使用「延迟关联」「ID游标」优化
6. 代码规范：SQL 关键字大写，表名字段小写；复杂语句换行缩进，提升可读性
7. 模糊查询优化：避免前缀通配符 LIKE '%xxx'，该写法无法利用索引，大数据量下性能极差

DCL

我们日常学习的增删改查属于DQL/DML，建库建表属于DDL，而用户创建、权限分配这些管理操作，都属于DCL的范畴。

用户管理（MySQL 环境）

MySQL的用户不是单纯的用户名，而是由 用户名@主机地址 共同组成的，主机地址决定了这个用户可以从哪台机器登录数据库：

• 'user'@'localhost'：只能在数据库本机登录
• 'user'@'%'：可以从任意远程主机登录
• 'user'@'192.168.1.%'：只能从192.168.1网段的机器登录

1. 创建用户

语法：

1. CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码';

复制代码

例子：

1. -- 创建一个只能本地登录的用户 test，密码是123456
2. CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
4. -- 创建一个可以任意远程登录的用户 test，密码是123456
5. CREATE USER 'test'@'%' IDENTIFIED BY '123456';

复制代码

⚠️ 注意：刚创建的用户默认没有任何权限，只能登录数据库，看不到任何业务数据。

2. 查看所有用户

MySQL的用户信息都存在系统库mysql的user表中：

1. SELECT user, host FROM mysql.user;

复制代码

1. USE mysql; -- 先切换当前数据库到 mysql 系统库
2. SELECT * FROM user; -- 再查当前库下的 user 表

复制代码

数据源完全一致：都是查询 MySQL 自带的系统库 mysql 里的 user 表，这张表存储了数据库所有的用户账号、权限、密码等信息。
两者的核心目的都是「查看数据库有哪些用户」。

1. USE mysql; -- 先切换当前数据库到 mysql 系统库
2. SELECT * FROM user; -- 再查当前库下的 user 表

复制代码

必须先执行 USE mysql 切换库，否则会报错找不到 user 表。

1. SELECT user, host FROM mysql.user;

复制代码

直接用 库名.表名 的全称写法，不需要提前切换数据库，一行就搞定，更方便。

返回结果的区别

• SELECT * FROM user：返回 user表的所有列，一共几十列，包含用户密码、各种权限开关、安全配置等，信息非常杂，大部分内容日常用不上。
• SELECT user, host FROM mysql.user：只返回 user（用户名）和 host（允许登录的主机地址）两列，结果简洁清晰，是日常查看用户最常用的写法。

99%的场景下，用你写的这种就够了：

1. SELECT user, host FROM mysql.user;

复制代码

既不用切换库，结果也干净，只看最核心的用户名和登录主机信息。
只有需要排查权限细节、密码策略的时候，才会去查全表字段。

3. 修改用户密码

语法：

1. ALTER USER '用户名'@'主机地址' IDENTIFIED BY '新密码';

复制代码

例子：

1. ALTER USER 'test'@'localhost' IDENTIFIED BY 'newpassword';

复制代码

4. 删除用户

语法：

1. DROP USER '用户名'@'主机地址';

复制代码

例子：

1. DROP USER 'test'@'%';

复制代码

三、权限管理

权限就是用户可以对数据库做什么操作，权限是分级别的：全局权限 > 库级权限 > 表级权限 > 列级权限。

1. 常见权限列表

权限	作用
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
CREATE	创建库/表
DROP	删除库/表
ALTER	修改表结构
ALL PRIVILEGES	除授权外的所有操作权限

2. 授予权限 GRANT

语法：

1. GRANT 权限1, 权限2 ON 库名.表名 TO '用户名'@'主机地址' [WITH GRANT OPTION];

复制代码

• *.* 代表所有库的所有表（全局权限）
• test.* 代表test库下的所有表（库级权限）
• test.student 代表test库下的student表（表级权限）
• WITH GRANT OPTION：该用户可以把自己拥有的权限再授予其他用户，生产环境慎用。

例子：

1. -- 给test用户授予 test 库下所有表的查询、插入权限
2. GRANT SELECT, INSERT ON test.* TO 'test'@'localhost';
4. -- 给test用户授予所有库所有表的全部权限（相当于超级管理员，生产环境严禁）
5. GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;

复制代码

⚠️ 注意：通过GRANT授权后，权限立即生效，不需要手动刷新。

ALL PRIVILEGES 包含哪些权限

它是一堆权限的集合打包，覆盖了绝大多数数据库操作，比如：

• 数据层面：查询SELECT、新增INSERT、修改UPDATE、删除DELETE
• 结构层面：建库建表CREATE、删库删表DROP、改表结构ALTER、操作索引INDEX
• 其他层面：执行存储过程EXECUTE、锁表、查看视图等等

简单说：拿到ALL PRIVILEGES的用户，对指定的库/表，自己想怎么操作就怎么操作，相当于这个库的“一把手”。

“除授权外”的「授权」是什么

这个“授权”指的是 GRANT OPTION 权限，也就是「把自己拥有的权限，再授予给其他用户」的能力。

权限配置	自己能操作数据库	能给其他用户分配权限
只给 ALL PRIVILEGES	✅ 所有操作都能做	❌ 不能给别人授权、不能创建新用户
ALL PRIVILEGES + WITH GRANT OPTION	✅ 所有操作都能做	✅ 可以把自己的权限分给别人，甚至创建新用户

例子1：只有ALL，没有授权能力

1. GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';

复制代码

test用户可以对test库做任何增删改查、建表删表操作，但不能给其他用户分配test库的权限。

例子2：ALL + 授权能力

1. GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' WITH GRANT OPTION;

复制代码

test用户不仅自己能操作test库，还能把test库的查询、修改等权限分给其他用户，相当于拥有了“二级管理员”的能力。

1. 普通业务账号绝对不要给ALL PRIVILEGES，只给必要的SELECT/INSERT/UPDATE/DELETE就够了，遵循最小权限原则。
2. WITH GRANT OPTION更是要严格控制，只有最高级别的管理员账号才能开启，普通账号开启会有极大的安全风险。

3. 撤销权限 REVOKE

语法：

1. REVOKE 权限1, 权限2 ON 库名.表名 FROM '用户名'@'主机地址';

复制代码

例子：

1. -- 撤销test用户对test库的插入权限
2. REVOKE INSERT ON test.* FROM 'test'@'localhost';
4. -- 撤销test用户的所有权限
5. REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';

复制代码

4. 查看用户权限

语法：

1. SHOW GRANTS FOR '用户名'@'主机地址';

复制代码

例子：

1. SHOW GRANTS FOR 'test'@'localhost';

复制代码

5. 刷新权限

如果是直接修改mysql.user等系统表来改权限，需要执行刷新命令才能生效：

1. FLUSH PRIVILEGES;

复制代码

正常使用GRANT/REVOKE不需要执行这条命令。

生产环境最佳实践

1. 最小权限原则：只给用户必须的权限，不要随便给ALL PRIVILEGES，普通业务用户只给SELECT/INSERT/UPDATE/DELETE即可。
2. 限制登录主机：尽量不要用%开放所有主机，限定业务服务器的IP段，降低安全风险。
3. 禁止root远程登录：root账号只保留本地登录，远程操作使用单独创建的管理员账号。
4. 定期清理无用用户：及时删除离职人员、废弃项目的数据库账号。
5. 密码强度要求：数据库账号必须设置强密码，禁止弱密码、空密码。