前言

在本学期的《MySQL 数据库技术》课程中，我们系统学习了 SQL 的各类语句，也在实战中踩过不少 “坑”。为了巩固知识、复盘经验，我整理了这份 SQL 语句全解析笔记，包含语法规范、应用场景、易错点和实战示例，希望能帮到同样在学习 MySQL 的小伙伴，也方便自己后续复习回顾。

一、SQL 基础概念与分类

SQL（Structured Query Language，结构化查询语言）是关系型数据库的标准操作语言，MySQL 中 SQL 语句按功能可分为 5 大类：

表格

分类	全称	核心作用	典型语句
DDL	数据定义语言	定义数据库、表、视图等对象的结构	CREATE / ALTER / DROP / TRUNCATE
DML	数据操作语言	对表中的数据进行增删改查	INSERT / DELETE / UPDATE / SELECT
DQL	数据查询语言	专门用于数据查询（常被归为 DML）	SELECT（核心语句）
DCL	数据控制语言	管理用户权限、控制数据访问	GRANT / REVOKE
TCL	事务控制语言	管理数据库事务，保证数据一致性	COMMIT / ROLLBACK / SAVEPOINT

易错点：TRUNCATE属于 DDL 而非 DML，它会直接删除表并重建，无法回滚，且会重置自增主键，和DELETE FROM 表名有本质区别。

二、DDL（数据定义语言）详解

2.1 数据库操作语句

2.1.1 创建数据库 CREATE DATABASE

语法规范

sql

CREATE DATABASE [IF NOT EXISTS] 数据库名 
[CHARSET=字符集] 
[COLLATE=排序规则];

易错点

1. 不加IF NOT EXISTS时，数据库已存在会报错；
2. 不指定字符集易出现中文乱码，建议统一使用utf8mb4。

实战示例

sql

-- 创建学生管理数据库，指定utf8mb4字符集
CREATE DATABASE IF NOT EXISTS student_manage 
CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci;

2.1.2 查看数据库 SHOW DATABASES

sql

-- 查看所有数据库
SHOW DATABASES;
-- 查看创建数据库的语句
SHOW CREATE DATABASE 数据库名;

2.1.3 切换 / 使用数据库 USE

sql

USE 数据库名;

注意：操作表前必须先切换数据库，否则会提示表不存在。

2.1.4 删除数据库 DROP DATABASE

sql

DROP DATABASE [IF EXISTS] 数据库名;

高危操作：删除数据库会清空库内所有表与数据，生产环境务必提前备份。

2.2 表结构操作语句

2.2.1 创建表 CREATE TABLE

语法规范

sql

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束条件] [COMMENT '字段说明'],
    列名2 数据类型 [约束条件] [COMMENT '字段说明'],
    ...
    PRIMARY KEY (主键列),
    [FOREIGN KEY (外键列) REFERENCES 主表(主表主键)]
) [ENGINE=存储引擎] [CHARSET=字符集] [COMMENT '表说明'];

常用约束说明

表格

约束	作用	示例
NOT NULL	字段值不能为空	student_name VARCHAR(20) NOT NULL
UNIQUE	字段值唯一	student_phone CHAR(11) UNIQUE
PRIMARY KEY	主键，非空且唯一，单表仅一个	id INT PRIMARY KEY AUTO_INCREMENT
AUTO_INCREMENT	整数类型自增	id INT PRIMARY KEY AUTO_INCREMENT
DEFAULT	字段默认值	is_delete TINYINT DEFAULT 0
FOREIGN KEY	外键，关联主表主键	FOREIGN KEY (class_id) REFERENCES class(id)

实战示例

sql

-- 创建学生信息表
CREATE TABLE IF NOT EXISTS student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    student_no CHAR(10) NOT NULL UNIQUE COMMENT '学号',
    student_name VARCHAR(20) NOT NULL COMMENT '姓名',
    age TINYINT UNSIGNED COMMENT '年龄',
    gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
    class_id INT COMMENT '班级ID',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (class_id) REFERENCES class(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生信息表';

2.2.2 修改表结构 ALTER TABLE

sql

-- 添加列
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束] [COMMENT '说明'];
-- 修改列数据类型/约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型 [新约束];
-- 重命名列
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 数据类型 [约束];
-- 删除列
ALTER TABLE 表名 DROP COLUMN 列名;
-- 重命名表
ALTER TABLE 旧表名 RENAME TO 新表名;
-- 添加主键
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
-- 添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列) REFERENCES 主表(主表主键);

注意：表内已有数据时，修改字段类型可能因数据不兼容报错，删除列会永久丢失数据。

2.2.3 删除 / 清空表

1. DROP TABLE：删除表结构 + 全部数据

sql

DROP TABLE [IF EXISTS] 表名;

1. TRUNCATE TABLE：清空数据，保留表结构，重置自增主键

sql

TRUNCATE TABLE 表名;

三者对比

表格

特性	DELETE FROM 表名	TRUNCATE TABLE 表名	DROP TABLE 表名
所属类型	DML	DDL	DDL
能否回滚	支持事务回滚	不支持回滚	不支持回滚
自增主键	不重置	重置为初始值	表被删除，无主键
执行效率	逐行删除，效率低	直接重建表，效率高	直接删除表，效率高
风险等级	中等	高	极高

三、DML（数据操作语言）详解

3.1 插入数据 INSERT

基础语法

sql

-- 单条插入
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
-- 批量插入
INSERT INTO 表名 (列1, 列2, ...) VALUES 
(值1-1, 值1-2, ...),
(值2-1, 值2-2, ...);
-- 插入查询结果
INSERT INTO 目标表 (列1, 列2, ...) SELECT 列A, 列B, ... FROM 源表 [WHERE 条件];

实战示例

sql

-- 批量插入学生数据
INSERT INTO student (student_no, student_name, age, gender, class_id)
VALUES 
('2024001', '张三', 18, '男', 1),
('2024002', '李四', 17, '女', 1),
('2024003', '王五', 18, '男', 2);
-- 存在则更新，不存在则插入
INSERT INTO student (student_no, student_name, age)
VALUES ('2024001', '张三', 19)
ON DUPLICATE KEY UPDATE age=19;

注意：字符串、日期类型必须用单引号包裹，主键 / 唯一键重复会触发报错。

3.2 更新数据 UPDATE

语法

sql

UPDATE 表名 SET 列1=值1, 列2=值2, ... [WHERE 条件] [LIMIT 行数];

高危提醒：不加WHERE条件会更新全表数据，日常使用必须指定条件。

多表更新示例

sql

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

3.3 删除数据 DELETE

语法

sql

DELETE FROM 表名 [WHERE 条件] [LIMIT 行数];

高危提醒：无WHERE条件会删除全表数据，生产环境优先使用逻辑删除。

多表删除示例

sql

DELETE s FROM student s JOIN class c ON s.class_id = c.id WHERE c.id = 3;

四、DQL（数据查询语言）详解（核心）

4.1 完整语法结构

sql

SELECT [DISTINCT] 列名/表达式/聚合函数
FROM 表名 [JOIN 关联表 ON 关联条件]
[WHERE 行过滤条件]
[GROUP BY 分组列]
[HAVING 分组后过滤条件]
[ORDER BY 排序列 [ASC/DESC]]
[LIMIT [偏移量,] 行数];

4.2 基础查询与去重 DISTINCT

sql

-- 普通查询
SELECT student_name, age FROM student;
-- 去重查询
SELECT DISTINCT class_id FROM student;

说明：DISTINCT对查询的所有列联合去重。

4.3 条件查询 WHERE

常用运算符 比较符：=、!=、>、<、>=、<= 范围符：BETWEEN 最小值 AND 最大值 集合符：IN (值1,值2) 模糊查询：LIKE（%匹配任意字符，_匹配单个字符） 空值判断：IS NULL、IS NOT NULL（禁止使用=NULL） 逻辑符：AND、OR、NOT

示例

sql

-- 查询1班17岁以上女生
SELECT * FROM student 
WHERE class_id=1 AND age>17 AND gender='女';
-- 查询姓名以张开头的学生
SELECT * FROM student WHERE student_name LIKE '张%';
-- 年龄16-18岁学生
SELECT * FROM student WHERE age BETWEEN 16 AND 18;
-- 班级ID为1、2的学生
SELECT * FROM student WHERE class_id IN (1,2);

4.4 聚合函数与分组查询 GROUP BY

常用聚合函数 COUNT() 统计行数、SUM() 求和、AVG() 求平均、MAX() 最大值、MIN() 最小值

分组查询示例

sql

-- 统计每个班级人数、平均年龄
SELECT class_id, COUNT(*) AS student_count, AVG(age) AS avg_age
FROM student
GROUP BY class_id;
-- 筛选人数大于2的班级
SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id
HAVING COUNT(*) > 2;

区分：WHERE 分组前过滤，不能用聚合函数；HAVING 分组后过滤，可使用聚合函数。

4.5 排序 ORDER BY

sql

-- 年龄升序，同名按姓名降序
SELECT * FROM student ORDER BY age ASC, student_name DESC;

4.6 分页 LIMIT

sql

-- 查询前10条
SELECT * FROM student LIMIT 10;
-- 从第11条开始，取10条（偏移量从0开始）
SELECT * FROM student LIMIT 10, 10;

4.7 多表连接查询

表格

连接类型	说明
内连接 INNER JOIN	只查询关联匹配的数据
左连接 LEFT JOIN	以左表为准，右表无匹配数据显示 NULL
右连接 RIGHT JOIN	以右表为准，左表无匹配数据显示 NULL

示例

sql

-- 内连接：查询有班级的学生及班级名
SELECT s.id, s.student_name, c.class_name
FROM student s
INNER JOIN class c ON s.class_id = c.id;

-- 左连接：查询所有学生，包含无班级的学生
SELECT s.id, s.student_name, c.class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

五、DCL（数据控制语言）详解

5.1 创建用户

sql

-- 本地登录用户
CREATE USER 'student_user'@'localhost' IDENTIFIED BY '123456';
-- 任意主机登录用户
CREATE USER 'student_user'@'%' IDENTIFIED BY '123456';

5.2 授权

sql

-- 授予查询、插入权限
GRANT SELECT, INSERT ON student_manage.student TO 'student_user'@'localhost';
-- 授予全部权限（仅测试使用）
GRANT ALL PRIVILEGES ON student_manage.* TO 'student_user'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;

5.3 回收权限

sql

REVOKE INSERT ON student_manage.student FROM 'student_user'@'localhost';

5.4 删除用户

sql

DROP USER 'student_user'@'localhost';

原则：遵循最小权限分配，生产环境不开放%主机与全权限。

六、TCL（事务控制语言）详解

6.1 事务四大特性 ACID

1. 原子性：事务内操作要么全成功，要么全回滚；
2. 一致性：事务执行前后数据库约束保持完整；
3. 隔离性：多事务并发互不干扰；
4. 持久性：事务提交后数据永久生效。

6.2 事务操作语句

sql

-- 开启事务
START TRANSACTION;
-- 执行数据操作
UPDATE student SET age=19 WHERE id=1;
INSERT INTO student (student_no, student_name) VALUES ('2024004', '赵六');
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置保存点
SAVEPOINT savepoint1;
-- 回滚至指定保存点
ROLLBACK TO SAVEPOINT savepoint1;

说明：仅 InnoDB 引擎支持事务，DDL 语句会隐式提交事务，无法回滚。

七、学习总结与反思

通过整理这份 SQL 笔记，我梳理了 MySQL 各类语句的用法与坑点，同时也发现自身不足：

1. 对多表连接、大表查询的性能优化掌握不足，后续学习EXPLAIN执行计划分析；
2. 窗口函数、复杂子查询运用不够熟练，需要补充实战练习；
3. 数据库安全、事务死锁、SQL 注入等生产场景知识有待加强。

SQL 是数据库学习的核心基础，后续我会结合实操持续巩固，也欢迎大家一起交流学习。

八、附录：常用 SQL 速查表

表格

场景	语句
查看所有数据库	SHOW DATABASES;
查看表结构	DESC 表名; / SHOW CREATE TABLE 表名;
查看当前用户	SELECT USER();
查看当前数据库	SELECT DATABASE();
查看 MySQL 版本	SELECT VERSION();
查看表索引	SHOW INDEX FROM 表名;