SQL相关

SQL 系统学习与知识体系

本文适合软件测试工程师及数据库初学者。学习 SQL 不仅能帮你完成接口测试中的数据库断言、自动化测试的测试数据准备、性能测试前的造数，更是所有数据相关工作的基石。 全文以一个在线教育平台（学生选课成绩）数据库为贯穿示例，所有 SQL 均基于 MySQL 8.x 编写，可直接复制练习。

一、引言与概述

1.1 SQL 是什么？

• 定义：SQL（Structured Query Language，结构化查询语言）是一种专门用来管理关系型数据库（RDBMS）的编程语言。
• 用途：对数据库中的数据进行查询、插入、更新、删除，以及创建/修改数据库结构、控制访问权限、管理事务等。
• 历史与标准：
  • 1970 年代由 IBM 的 Donald Chamberlin 和 Raymond Boyce 在 System R 项目上发明，最初叫 SEQUEL。
  • ANSI（美国国家标准学会）于 1986 年发布第一个 SQL 标准，ISO 随后跟进。常见标准版本有 SQL-92、SQL:1999、SQL:2003 等。
  • 各厂商在标准之上做了扩展：微软 T-SQL、Oracle PL/SQL、PostgreSQL PL/pgSQL 等，基本语法大同小异，细节存在差异。

1.2 数据库基本概念

概念	说明	类比（Excel）
数据库 (Database)	存储数据的容器，由多张表组成	一个工作簿
表 (Table)	由行和列组成的二维结构	一个工作表
行/记录 (Row/Record)	表中的一条数据	一行数据
列/字段 (Column/Field)	表中的一个属性	一列数据
主键 (Primary Key)	唯一标识一行记录的列，不能为空、不能重复	序号列
外键 (Foreign Key)	引用其他表主键的列，用于建立表间关系	关联另一张表的编号
索引 (Index)	加速查询的”目录”结构	书末的索引页

关系模型核心：表与表之间通过”关系”关联，常见三种关系：

• 一对一 (1:1)：如”用户表”与”用户详情表”，一个用户只有一条详情记录。
• 一对多 (1:N)：如”班级表”与”学生表”，一个班级有多个学生。最常用。
• 多对多 (M:N)：如”学生表”与”课程表”，一个学生选多门课、一门课被多个学生选，通常通过中间表（如成绩表）实现。

1.3 SQL 语言分类总览

SQL 按功能分为五大类：

分类	全称	关键字	作用
DDL	Data Definition Language 数据定义语言	CREATE ALTER DROP TRUNCATE	定义/修改数据库结构
DML	Data Manipulation Language 数据操作语言	INSERT UPDATE DELETE SELECT	操作表中数据
DQL	Data Query Language 数据查询语言	SELECT	查询数据（常并入 DML）
DCL	Data Control Language 数据控制语言	GRANT REVOKE	管理用户权限
TCL	Transaction Control Language 事务控制语言	COMMIT ROLLBACK SAVEPOINT	管理事务

二、环境搭建与初步实践

2.1 常见数据库管理系统 (DBMS) 简介

DBMS	开源	特点	常见场景
MySQL	✅	最流行，社区活跃，性能好，InnoDB 支持事务	Web 应用（LAMP 等）
MariaDB	✅	MySQL 的 fork，完全兼容	与 MySQL 场景相同
PostgreSQL	✅	功能强大，支持丰富数据类型（JSON、数组、GIS）	复杂业务、数据分析
SQLite	✅	轻量嵌入式，单文件，无需安装服务	移动端、本地测试、临时工具
Microsoft SQL Server	❌	与 Windows/.NET 生态集成好，T-SQL	企业级 Windows 应用
Oracle Database	❌	功能最全、最稳，商业化	大型企业核心系统

学习建议：先精通 MySQL，其他数据库触类旁通。测试工作中 SQLite/MySQL 最常遇到。

2.2 选择与安装一个 DBMS（以 MySQL 为例，Windows）

1. 下载：到 MySQL Community Server 官网 下载 MSI 安装包。
2. 安装时选择 Developer Default，一路 Next；设置 root 密码（务必记住）。
3. 安装完成后，MySQL 会注册为 Windows 服务并随开机自启。
4. 验证安装：在终端执行 mysql --version，看到版本号即成功。

如果没有安装 MySQL，又想快速上手，推荐两个零安装方案：

• Docker 方式（已装 Docker 时）：

  docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:8
  docker exec -it mysql8 mysql -uroot -p
  

• 在线练习：直接使用 SQLZoo、W3Schools SQL 练习 等网页平台，无需本地环境。

2.3 使用图形化工具 (GUI)

工具	免费	跨平台	特点
DBeaver	✅	✅	开源、支持几乎所有数据库，社区版足够用，推荐
Navicat	收费	✅	功能强大、界面友好，测试/开发常用
DataGrip	收费	✅	JetBrains 出品，IDE 体验好
MySQL Workbench	✅	✅	MySQL 官方工具
pgAdmin	✅	✅	PostgreSQL 官方工具

GUI 工具核心能力：连接管理、SQL 编辑与执行、ER 图可视化、数据导入导出。

2.4 命令行客户端基础操作

# 登录（-p 后回车再输入密码，避免密码暴露在历史记录中）
mysql -u root -p

# 登录后查看所有数据库
SHOW DATABASES;

# 查看当前版本
SELECT VERSION();

-- 创建并进入一个练习库
CREATE DATABASE IF NOT EXISTS edu DEFAULT CHARSET utf8mb4;
USE edu;

-- 查看库中所有表
SHOW TABLES;

-- 查看某张表的建表语句
SHOW CREATE TABLE student \G

三、SQL 核心语法（按功能分类）

后续所有示例均基于下面的示例数据库。先在 MySQL 中执行一遍建表 + 插入语句：

USE edu;

-- 学生表
CREATE TABLE student (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(50) NOT NULL,
    gender     CHAR(1) DEFAULT '男',
    age        INT CHECK (age BETWEEN 1 AND 120),
    class_name VARCHAR(20)
);

-- 课程表
CREATE TABLE course (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL,
    credit  DECIMAL(2, 1) DEFAULT 2.0,
    teacher VARCHAR(20)
);

-- 成绩表（学生-课程 多对多的中间表，兼带成绩）
CREATE TABLE score (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    score      DECIMAL(5, 2),
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id),
    UNIQUE KEY uk_stu_course (student_id, course_id)
);

-- 插入示例数据
INSERT INTO student (name, gender, age, class_name) VALUES
('张三', '男', 20, '软件测试1班'),
('李四', '女', 21, '软件测试1班'),
('王五', '男', 22, '软件测试2班'),
('赵六', '女', 20, '软件测试2班'),
('孙七', '男', 23, '软件测试3班');

INSERT INTO course (name, credit, teacher) VALUES
('数据库原理', 3.0, '陈老师'),
('软件测试',   2.5, '李老师'),
('计算机网络', 3.0, '王老师'),
('Python编程', 2.0, '张老师');

INSERT INTO score (student_id, course_id, score) VALUES
(1, 1, 92.00), (1, 2, 85.00), (1, 3, 78.00),
(2, 1, 88.00), (2, 4, 95.00),
(3, 2, 60.00), (3, 3, 58.00),
(4, 1, 75.00), (4, 2, 80.00), (4, 3, 82.00), (4, 4, 90.00),
(5, 4, 55.00);

3.1 数据定义语言 (DDL)

CREATE — 创建数据库、表、索引、视图

-- 创建数据库
CREATE DATABASE IF NOT EXISTS edu DEFAULT CHARSET utf8mb4;

-- 创建表（详见上方示例库）
CREATE TABLE course (...);

-- 创建索引
CREATE INDEX idx_score_course ON score(course_id);

-- 创建视图（详见第七章）
CREATE VIEW v_student AS SELECT id, name FROM student;

ALTER — 修改表结构

-- 添加列
ALTER TABLE student ADD COLUMN phone VARCHAR(20) AFTER name;

-- 修改列类型 / 名称
ALTER TABLE student MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE student CHANGE COLUMN phone mobile VARCHAR(30);

-- 删除列
ALTER TABLE student DROP COLUMN mobile;

-- 重命名表
ALTER TABLE student RENAME TO stu;

DROP — 删除数据库、表、视图

DROP DATABASE IF EXISTS edu;
DROP TABLE IF EXISTS score;
DROP VIEW IF EXISTS v_student;

TRUNCATE — 快速清空表数据

-- 清空表数据且重置自增ID（速度比 DELETE 快，但不可回滚，且不走触发器）
TRUNCATE TABLE student;

对比	DELETE	TRUNCATE
性质	DML，逐行删除	DDL，整表重建
可回滚	✅ 可配合事务回滚	❌ 不可回滚
速度	慢	快
自增 ID	不清零	清零
触发触发器	✅	❌

3.2 数据操作语言 (DML)

INSERT — 插入数据

-- 插入单行（指定列）
INSERT INTO student (name, gender, age, class_name)
VALUES ('周八', '男', 19, '软件测试1班');

-- 插入多行
INSERT INTO course (name, credit) VALUES ('操作系统', 3.0), ('数据结构', 3.5);

-- 从一个表复制数据到另一个表
INSERT INTO student_backup (id, name)
SELECT id, name FROM student;

UPDATE — 更新数据

-- 基本更新（务必带上 WHERE！）
UPDATE student SET age = age + 1 WHERE name = '张三';

-- 多列更新 + 联表更新（MySQL 语法）
UPDATE score s
JOIN student st ON s.student_id = st.id
SET s.score = s.score + 5
WHERE st.class_name = '软件测试3班';

⚠️ 测试同学注意：执行 UPDATE/DELETE 前，先 SELECT 一遍同样的 WHERE 条件确认影响范围，或直接在事务中执行，确认无误后再 COMMIT。

DELETE — 删除数据

-- 删除指定行
DELETE FROM student WHERE name = '周八';

-- 删除所有行（保留表结构，等价 TRUNCATE 但可回滚）
DELETE FROM student;

-- 联表删除
DELETE s FROM score s JOIN student st ON s.student_id = st.id
WHERE st.class_name = '软件测试3班';

SELECT — 查询数据（详见第四章，最核心）

3.3 数据控制语言 (DCL)

-- 创建用户
CREATE USER 'tester'@'%' IDENTIFIED BY 'Test@123';

-- 授权：只读权限（测试环境常用的最小权限）
GRANT SELECT ON edu.* TO 'tester'@'%';

-- 授权：增删改查 + 刷新权限
GRANT SELECT, INSERT, UPDATE, DELETE ON edu.* TO 'tester'@'%';
FLUSH PRIVILEGES;

-- 回收权限
REVOKE DELETE ON edu.* FROM 'tester'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'tester'@'%';

3.4 事务控制语言 (TCL)

-- 开始事务
START TRANSACTION;

-- 模拟转账：扣钱 + 加钱 必须同时成功或同时失败
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

-- 都成功则提交
COMMIT;

-- 出错则回滚
ROLLBACK;

SAVEPOINT — 保存点（部分回滚）

START TRANSACTION;
INSERT INTO student (name) VALUES ('事务测试A');
SAVEPOINT sp1;
INSERT INTO student (name) VALUES ('事务测试B');
ROLLBACK TO SAVEPOINT sp1;   -- 回滚到 sp1，仅保留 A
COMMIT;                      -- 最终表中只有"事务测试A"

四、SELECT 查询深度解析

4.1 基础查询结构

-- 查询所有列
SELECT * FROM student;

-- 查询指定列
SELECT name, age FROM student;

-- 使用 AS 设置别名（AS 可省略）
SELECT name AS 姓名, age AS 年龄 FROM student;
SELECT name 姓名, age 年龄 FROM student;

-- 去重 DISTINCT
SELECT DISTINCT class_name FROM student;

-- 常量列 / 表达式列
SELECT name, age, '测试工程师' AS 职业 FROM student;
SELECT name, age * 2 AS 两倍年龄 FROM student;

⚠️ 生产环境避免 SELECT *：会返回无用列、浪费网络与内存；明确写出所需列也便于代码评审和优化。

4.2 过滤数据 (WHERE 子句)

-- 比较运算符
SELECT * FROM student WHERE age >= 21;
SELECT * FROM student WHERE gender != '女';

-- 逻辑运算符 AND / OR / NOT
SELECT * FROM student WHERE age >= 21 AND gender = '男';
SELECT * FROM student WHERE class_name = '软件测试1班' OR class_name = '软件测试2班';
SELECT * FROM student WHERE NOT class_name = '软件测试1班';

-- BETWEEN ... AND ...（闭区间）
SELECT * FROM student WHERE age BETWEEN 20 AND 22;

-- IN（列表匹配，也可用于子查询）
SELECT * FROM student WHERE id IN (1, 3, 5);

-- LIKE 与通配符
SELECT * FROM student WHERE name LIKE '张%';    -- 以"张"开头
SELECT * FROM student WHERE name LIKE '_四';    -- 两个字符且以"四"结尾
SELECT * FROM course WHERE name LIKE '%测试%';   -- 包含"测试"

-- IS NULL / IS NOT NULL（不能用 = NULL 判断！）
SELECT * FROM student WHERE class_name IS NULL;
SELECT * FROM student WHERE class_name IS NOT NULL;

通配符	含义	示例	结果
%	任意多个字符（含 0 个）	'张%'	张三、张伟
_	任意单个字符	'张_'	张三（不匹配张伟明）

⚠️ 常见坑：WHERE class_name = NULL 永远查不到数据，必须用 IS NULL。

4.3 排序与限制 (ORDER BY & LIMIT)

-- 单列排序
SELECT name, age FROM student ORDER BY age DESC;   -- 降序
SELECT name, age FROM student ORDER BY age ASC;    -- 升序（默认）

-- 多列排序：先按班级升序，同班再按年龄降序
SELECT name, class_name, age
FROM student
ORDER BY class_name ASC, age DESC;

-- 排序中可用列序号（不推荐，可读性差）
SELECT name, age FROM student ORDER BY 2 DESC;

-- LIMIT 取前 N 条
SELECT * FROM score ORDER BY score DESC LIMIT 3;

-- LIMIT + OFFSET 分页（第 2 页，每页 3 条）
SELECT * FROM score ORDER BY score DESC LIMIT 3 OFFSET 3;

-- 另一种等价写法（MySQL）
SELECT * FROM score ORDER BY score DESC LIMIT 3, 3;

分页语法差异：

数据库	语法
MySQL / PostgreSQL	LIMIT 3 OFFSET 3
SQL Server	OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY（需配 ORDER BY）
Oracle	FETCH FIRST 3 ROWS ONLY / ROWNUM <= 3

4.4 聚合函数与分组 (GROUP BY & HAVING)

常用聚合函数：

函数	作用	注意
COUNT(*)	统计行数	COUNT(*) 含 NULL 行，COUNT(列) 忽略 NULL
COUNT(DISTINCT 列)	统计去重后行数	常用
SUM(列)	求和	忽略 NULL
AVG(列)	平均值	忽略 NULL
MAX(列) / MIN(列)	最大 / 最小值	也可用于字符串、日期

-- 基础聚合
SELECT COUNT(*)        AS 学生总数,
       COUNT(DISTINCT class_name) AS 班级数,
       AVG(age)        AS 平均年龄,
       MAX(age)        AS 最大年龄,
       MIN(age)        AS 最小年龄
FROM student;

-- 分组统计：每个班级的平均年龄、人数
SELECT class_name, COUNT(*) AS 人数, AVG(age) AS 平均年龄
FROM student
GROUP BY class_name;

-- GROUP BY 多列：每门课每个班级……（示例：每班各课平均分）
SELECT st.class_name, c.name, AVG(s.score) AS 平均分
FROM score s
JOIN student st ON s.student_id = st.id
JOIN course c ON s.course_id = c.id
GROUP BY st.class_name, c.name;

-- HAVING 过滤分组：只保留平均分 >= 80 的课程
SELECT c.name, AVG(s.score) AS 平均分
FROM score s
JOIN course c ON s.course_id = c.id
GROUP BY c.name
HAVING AVG(s.score) >= 80;

-- 同时使用 WHERE 和 HAVING：先过滤数据行，再过滤分组
SELECT c.name, AVG(s.score) AS 平均分
FROM score s
JOIN course c ON s.course_id = c.id
WHERE s.score IS NOT NULL
GROUP BY c.name
HAVING AVG(s.score) >= 80;

HAVING vs WHERE 核心区别：

• WHERE：在分组前过滤原始行，不能使用聚合函数。
• HAVING：在分组后过滤分组，可以使用聚合函数。
• 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

4.5 连接查询 (JOIN) - 重中之重

JOIN 用于把多张表按关联字段组合起来。测试中 90% 的取数需求都需要 JOIN，务必熟练。

各类型 JOIN 对比：

-- INNER JOIN：只返回两张表都匹配的行（交集）
SELECT st.name, c.name AS course, s.score
FROM score s
INNER JOIN student st ON s.student_id = st.id
INNER JOIN course c ON s.course_id = c.id;

-- LEFT JOIN：左表全量 + 右表匹配，未匹配部分右表列填 NULL
SELECT st.name, c.name AS course, s.score
FROM student st
LEFT JOIN score s ON s.student_id = st.id
LEFT JOIN course c ON s.course_id = c.id;

-- RIGHT JOIN：右表全量 + 左表匹配（与 LEFT JOIN 互补，MySQL 支持）
SELECT st.name, c.name AS course, s.score
FROM student st
RIGHT JOIN score s ON s.student_id = st.id;

-- FULL OUTER JOIN：两表全量（MySQL 不支持，可用 UNION 模拟，见 4.7）
-- 模拟：所有学生 + 所有课程，能对上的显示成绩
SELECT st.name, c.name AS course, s.score
FROM student st
LEFT JOIN score s ON s.student_id = st.id
LEFT JOIN course c ON s.course_id = c.id
UNION
SELECT st.name, c.name AS course, s.score
FROM course c
LEFT JOIN score s ON s.course_id = c.id
LEFT JOIN student st ON s.student_id = st.id;

-- CROSS JOIN：笛卡尔积，两表行数相乘
SELECT st.name, c.name
FROM student st CROSS JOIN course c;

LEFT JOIN 的经典应用：找”没有匹配记录”的行

-- 找出从未选课的学生（score 表中不存在记录）
SELECT st.name
FROM student st
LEFT JOIN score s ON s.student_id = st.id
WHERE s.id IS NULL;

自连接 (Self Join)：表和自己关联，常用于树形结构（如员工-上级、分类-父分类）。

-- 示例：员工表，manager_id 指向同一张表的 id
SELECT e.name AS 员工, m.name AS 上级
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

多表连接：按需链式添加 JOIN 即可（示例见 4.4 分组统计）。

4.6 子查询 (Subquery)

按返回结果分类：

类型	返回值	示例场景
标量子查询	单个值	年龄大于平均年龄的学生
行子查询	单行多列	与某人同班同年龄
列子查询	单列多行	属于某班级的所有学生
表子查询	多行多列	作为临时表再次查询

-- 标量子查询：年龄大于平均年龄的学生
SELECT name, age FROM student
WHERE age > (SELECT AVG(age) FROM student);

-- 标量子查询也可放在 SELECT 列表中
SELECT name,
       (SELECT AVG(score) FROM score WHERE student_id = student.id) AS 个人平均分
FROM student;

-- 列子查询 + IN：选过"数据库原理"的学生姓名
SELECT name FROM student
WHERE id IN (
    SELECT student_id FROM score
    WHERE course_id = (SELECT id FROM course WHERE name = '数据库原理')
);

-- 列子查询 + ANY / ALL
SELECT name, age FROM student
WHERE age > ANY (SELECT age FROM student WHERE class_name = '软件测试1班');  -- 大于其中任意一个
SELECT name, age FROM student
WHERE age > ALL (SELECT age FROM student WHERE class_name = '软件测试1班');  -- 大于全部

-- 表子查询：把子查询结果当作临时表，必须起别名
SELECT t.name, t.avg_score
FROM (
    SELECT student_id, AVG(score) AS avg_score
    FROM score
    GROUP BY student_id
) AS t
JOIN student ON student.id = t.student_id
WHERE t.avg_score >= 80;

相关子查询 vs 非相关子查询：

• 非相关子查询：先执行内层，与外层无关，只执行一次（上面标量子查询第 1 个例子）。
• 相关子查询：外层每取一行，内层就执行一次（上面 SELECT 列表中带 student.id 的例子），性能较差，数据量大时慎用。
• EXISTS 关键字：判断子查询是否有结果，常比 IN 高效。

-- EXISTS：找出有选课记录的学生
SELECT name FROM student st
WHERE EXISTS (
    SELECT 1 FROM score s WHERE s.student_id = st.id
);

4.7 集合运算 (Set Operations)

-- UNION：去重并集（两边的列数、类型必须一致）
SELECT name FROM student WHERE class_name = '软件测试1班'
UNION
SELECT name FROM student WHERE age >= 21;

-- UNION ALL：并集（不去重，性能更好）
SELECT name FROM student WHERE class_name = '软件测试1班'
UNION ALL
SELECT name FROM student WHERE age >= 21;

-- INTERSECT：交集（MySQL 8.0 起支持，也可用 IN + 子查询模拟）
SELECT name FROM student WHERE class_name = '软件测试1班'
INTERSECT
SELECT name FROM student WHERE age >= 21;

-- EXCEPT：差集（MySQL 8.0 起支持；也可用 NOT IN 模拟）
SELECT name FROM student WHERE class_name = '软件测试1班'
EXCEPT
SELECT name FROM student WHERE age >= 21;

运算符	含义	MySQL 支持情况
UNION	并集（去重）	✅
UNION ALL	并集（不去重）	✅
INTERSECT	交集	✅ 8.0.31+
EXCEPT / MINUS	差集	✅ 8.0.31+

五、函数

5.1 标量函数 (处理单值，返回单值)

字符串函数：

SELECT CONCAT(name, '(', class_name, ')') FROM student;          -- 拼接
SELECT SUBSTRING('abcdef', 2, 3);                                 -- 子串 -> 'bcd'
SELECT UPPER('abc'), LOWER('ABC');                                -- 大小写
SELECT LENGTH('hello'), CHAR_LENGTH('你好');                      -- 字节数 vs 字符数
SELECT TRIM('  hi  '), LTRIM('  hi'), RTRIM('hi  ');              -- 去空格
SELECT REPLACE('hello world', 'world', 'sql');                    -- 替换
SELECT LEFT('abcdef', 3), RIGHT('abcdef', 2);                     -- 左侧/右侧截取

数值函数：

SELECT ROUND(3.14159, 2);   -- 3.14
SELECT CEIL(3.14), FLOOR(3.14);  -- 4, 3
SELECT ABS(-5), MOD(10, 3); -- 5, 1
SELECT RAND();              -- 0~1 随机数
SELECT GREATEST(1, 5, 3), LEAST(1, 5, 3);  -- 5, 1

日期时间函数：

SELECT NOW(), CURDATE(), CURTIME();                    -- 当前时间/日期/时刻
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);                -- 加 7 天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);              -- 减 1 个月
SELECT DATEDIFF('2026-08-18', '2026-01-01');           -- 相差天数
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());          -- 提取年/月/日
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');        -- 格式化
SELECT STR_TO_DATE('2026-08-18', '%Y-%m-%d');          -- 字符串转日期

💡 测试常用技巧：DATEDIFF(NOW(), create_time) 判断数据新鲜度；DATE_FORMAT 归一化时间用于统计。

转换函数：

SELECT CAST('123' AS SIGNED);          -- 字符串转整数
SELECT CAST('3.14' AS DECIMAL(4,2));   -- 转小数
SELECT CONVERT('2026-08-18', DATE);    -- 转日期

5.2 窗口函数 (Window Functions) - 高级主题

窗口函数可以在不合并行的情况下，对每一行计算”分组统计值”，是排名的首选。基本结构：函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)。

排名函数：

-- 每门课按成绩排名
SELECT st.name, c.name AS course, s.score,
       ROW_NUMBER() OVER (PARTITION BY s.course_id ORDER BY s.score DESC) AS 行号,
       RANK()       OVER (PARTITION BY s.course_id ORDER BY s.score DESC) AS 排名,
       DENSE_RANK() OVER (PARTITION BY s.course_id ORDER BY s.score DESC) AS 稠密排名,
       NTILE(4)     OVER (PARTITION BY s.course_id ORDER BY s.score DESC) AS 四分位
FROM score s
JOIN student st ON s.student_id = st.id
JOIN course c ON s.course_id = c.id;

三种排名区别（成绩 92/92/88）：

函数	92	92	88
ROW_NUMBER()	1	2	3
RANK()	1	1	3（跳跃）
DENSE_RANK()	1	1	2（不跳跃）

聚合函数作为窗口函数：

-- 每行显示该课程累计得分（累加和）
SELECT st.name, c.name, s.score,
       SUM(s.score)  OVER (PARTITION BY s.course_id ORDER BY s.score) AS 累计分,
       AVG(s.score)  OVER (PARTITION BY s.course_id) AS 课程平均分,
       COUNT(*)      OVER (PARTITION BY s.course_id) AS 选课人数
FROM score s
JOIN student st ON s.student_id = st.id
JOIN course c ON s.course_id = c.id;

取值函数 LAG / LEAD / FIRST_VALUE / LAST_VALUE：

-- 每门课按成绩升序，取上一名/下一名的成绩做对比
SELECT c.name, s.score,
       LAG(s.score)  OVER (PARTITION BY s.course_id ORDER BY s.score) AS 上一名成绩,
       LEAD(s.score) OVER (PARTITION BY s.course_id ORDER BY s.score) AS 下一名成绩,
       FIRST_VALUE(s.score) OVER (PARTITION BY s.course_id ORDER BY s.score) AS 最低分
FROM score s
JOIN course c ON s.course_id = c.id;

六、数据完整性、约束与高级表设计

6.1 约束 (Constraints)

约束	作用	示例
PRIMARY KEY	主键：唯一 + 非空	id INT PRIMARY KEY
FOREIGN KEY	外键：保证参照完整性	FOREIGN KEY (student_id) REFERENCES student(id)
UNIQUE	唯一：值不重复	UNIQUE KEY uk_stu_course (student_id, course_id)
NOT NULL	非空	name VARCHAR(50) NOT NULL
CHECK	检查约束，值必须满足条件	CHECK (age BETWEEN 1 AND 120)
DEFAULT	默认值	gender CHAR(1) DEFAULT '男'

约束的使用（建表时 / 建表后）：

-- 建表后追加外键
ALTER TABLE score ADD CONSTRAINT fk_score_student
FOREIGN KEY (student_id) REFERENCES student(id);

-- 删除约束
ALTER TABLE score DROP FOREIGN KEY fk_score_student;

💡 测试小技巧：验证外键约束——尝试插入一条不存在的 student_id 成绩记录，应报外键错误；这就是在测试”参照完整性”。

6.2 规范化 (Normalization) - 数据库设计理论

目标：消除数据冗余与更新异常（插入异常、更新异常、删除异常）。

反例表（未规范化，数据冗余严重）：

学号	姓名	班级	课程	课程教师	成绩
1	张三	测试1班	数据库	陈老师	92
1	张三	测试1班	软件测试	李老师	85

问题：姓名/班级/教师重复存储；改一次教师名要改多行；”教师”依赖的是”课程”而非”学号”。

三范式：

范式	要求	违反示例
1NF 第一范式	列不可再分（原子性），每一格只存一个值	把”张三,20”存进一列
2NF 第二范式	满足 1NF，且非主键列完全依赖主键（消除部分依赖）	上表”班级”只依赖学号，却与课程组合键绑定
3NF 第三范式	满足 2NF，且非主键列不传递依赖主键	上表”教师”依赖”课程”，而课程又由学号-课程组合决定

规范化后：拆成 student / course / score 三张表（即本文示例库结构），消除全部冗余。

⚠️ 实践注意：理论上”越规范越好”，但过度规范化会导致查询需要过多 JOIN、性能下降，电商等场景常故意反规范化（冗余字段、缓存表）换取读性能。设计是”规范 + 取舍”的平衡。

6.3 索引优化

索引的作用与原理：

• 没有索引时，查询要全表扫描（逐行比对，O(n)）。
• 索引相当于”目录”，MySQL 默认 InnoDB 引擎使用 B+ 树，查找复杂度 O(log n)。另有一种 哈希索引（Memory 引擎），适合等值查询。
• 使用 EXPLAIN 可查看 SQL 是否走索引（type 字段为 ref/range/const 等说明走了索引，ALL 表示全表扫描）。

创建索引：

-- 普通索引
CREATE INDEX idx_score_student ON score(student_id);

-- 唯一索引
CREATE UNIQUE INDEX uk_student_name ON student(name);

-- 复合索引（最左前缀原则：命中顺序需从最左列开始）
CREATE INDEX idx_name_class ON student(name, class_name);
-- 以下查询可用到该复合索引：
--   WHERE name = '张三'
--   WHERE name = '张三' AND class_name = '测试1班'
-- 以下查询用不到（跳过了最左列 name）：
--   WHERE class_name = '测试1班'

-- 删除索引
DROP INDEX idx_score_student ON score;

索引的优点与代价：

• ✅ 优点：极大加快查询、排序、分组速度；唯一索引保证数据唯一性。
• ❌ 代价：占用磁盘空间；INSERT/UPDATE/DELETE 时需同步维护索引，写入变慢。

何时使用 / 何时避免：

建议建索引	不建议建索引
WHERE / JOIN / ORDER BY 频繁使用的列	数据量很小的表
区分度高的列（如手机号、学号）	大量重复值的列（如性别）
频繁作为查询条件的列	频繁更新、几乎不查询的列

💡 测试实践：接口性能测试发现查询慢时，可用 EXPLAIN 定位是否全表扫描，这也是测试人员定位性能瓶颈的常用手段之一。

七、视图、存储过程、触发器

7.1 视图 (VIEW)

视图是虚拟表，本身不存数据，只是保存一条 SELECT 语句，每次查询时动态执行。

-- 创建视图：学生成绩明细（三表关联）
CREATE VIEW v_student_score AS
SELECT st.id, st.name, c.name AS course_name, c.teacher, s.score
FROM score s
JOIN student st ON s.student_id = st.id
JOIN course c ON s.course_id = c.id;

-- 查询视图（像查表一样）
SELECT * FROM v_student_score WHERE score >= 90;

-- 查看视图定义
SHOW CREATE VIEW v_student_score;

-- 删除视图
DROP VIEW IF EXISTS v_student_score;

视图优点：

• 简化查询：把复杂的多表 JOIN 封装成”一张表”。
• 安全性：只暴露需要的列，隐藏敏感字段（如密码、内部字段）。
• 逻辑独立性：底层表结构变化时，可通过修改视图来屏蔽影响。

可更新视图条件：基于单表、不含聚合/DISTINCT/GROUP BY 等的视图可以 INSERT/UPDATE；多表关联视图默认不可更新。

7.2 存储过程 (Stored Procedure)

存储过程是预编译的 SQL 语句集合，可封装业务逻辑，一次编写多处调用。

DELIMITER $$

CREATE PROCEDURE get_student_avg_score(
    IN  stu_name VARCHAR(50),     -- 输入参数
    OUT avg_score DECIMAL(5, 2)   -- 输出参数
)
BEGIN
    SELECT AVG(s.score) INTO avg_score
    FROM score s
    JOIN student st ON s.student_id = st.id
    WHERE st.name = stu_name;
END$$

DELIMITER ;

-- 调用
CALL get_student_avg_score('张三', @result);
SELECT @result;

流程控制语句：

DELIMITER $$

CREATE PROCEDURE judge_score(IN s DECIMAL(5,2))
BEGIN
    IF s >= 90 THEN
        SELECT '优秀' AS 评级;
    ELSEIF s >= 60 THEN
        SELECT '及格' AS 评级;
    ELSE
        SELECT '不及格' AS 评级;
    END IF;
END$$

DELIMITER ;

CALL judge_score(85);

-- WHILE 循环示例
DELIMITER $$
CREATE PROCEDURE batch_insert_demo(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        INSERT INTO student (name) VALUES (CONCAT('批量学生', i));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL batch_insert_demo(10);

💡 测试大杀器：存储过程非常适合批量造测试数据（循环插入、随机生成），可结合 RAND()、DATE_ADD() 等函数生成各种边界数据。

7.3 触发器 (TRIGGER)

触发器是自动执行的存储过程：在特定表上发生特定事件（INSERT/UPDATE/DELETE）时自动触发。

-- 示例：插入成绩前校验范围（0-100），不合法直接报错
DELIMITER $$
CREATE TRIGGER trg_score_before_insert
BEFORE INSERT ON score
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在 0-100 之间';
    END IF;
END$$
DELIMITER ;

-- 触发事件类型：
--   BEFORE / AFTER：事件之前 / 之后
--   INSERT / UPDATE / DELETE：对应操作
--   INSTEAD OF：用于视图（SQL Server 等支持）

-- 访问新旧数据（MySQL 语法）：
--   NEW.列名：INSERT 时的新值 / UPDATE 后的新值
--   OLD.列名：DELETE 时的旧值 / UPDATE 前的旧值

-- 删除触发器、查看触发器
DROP TRIGGER IF EXISTS trg_score_before_insert;
SHOW TRIGGERS;

⚠️ 触发器虽然方便，但隐式执行、难排查，且会影响写入性能；生产环境滥用会显著增加排障成本，建议谨慎使用。

八、高级主题与性能

8.1 事务与并发控制

ACID 属性：

属性	含义	说明
Atomicity 原子性	事务要么全成功要么全失败	靠 ROLLBACK 实现
Consistency 一致性	事务前后数据满足所有约束	如转账后总额不变
Isolation 隔离性	并发事务互不干扰	靠锁与隔离级别实现
Durability 持久性	提交后数据永久保存	靠 redo log 实现

事务隔离级别（从低到高）：

-- 查看 / 设置隔离级别（MySQL 8.x）
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	✅可能	✅可能	✅可能	能读到未提交数据，几乎不用
READ COMMITTED	❌	✅可能	✅可能	Oracle/SQL Server 默认
REPEATABLE READ	❌	❌	✅可能	MySQL 默认，InnoDB 用 MVCC 解决幻读
SERIALIZABLE	❌	❌	❌	完全串行，性能最差

三种并发问题：

• 脏读 (Dirty Read)：读到另一个事务未提交的数据，该事务回滚后读到的是”脏”数据。
• 不可重复读 (Non-Repeatable Read)：同一事务内两次查询同一条记录，结果不一致（被其他事务 UPDATE 了）。
• 幻读 (Phantom Read)：同一事务内两次查询同一范围的记录，结果行数不一致（被其他事务 INSERT 了）。

8.2 查询性能优化

使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT st.name, c.name, s.score
FROM score s
JOIN student st ON s.student_id = st.id
JOIN course c ON s.course_id = c.id
WHERE st.class_name = '软件测试1班' AND s.score > 80;

EXPLAIN 关键字段：

字段	含义	重点关注
type	访问类型	const/ref/range 走索引，ALL 全表扫描（需优化）
key	实际使用的索引	为 NULL 说明没走索引
rows	预估扫描行数	越小越好
Extra	附加信息	出现 Using filesort、Using temporary 常需优化

常见优化建议：

1. 避免 SELECT *，只取所需列，减少 IO 与网络传输。
2. WHERE 中避免对列使用函数：WHERE YEAR(create_time) = 2026 会让索引失效，应改为 WHERE create_time BETWEEN '2026-01-01' AND '2026-12-31'。
3. 避免隐式类型转换：WHERE phone = 13800000000（数字）会导致字符串列索引失效，应写成字符串。
4. LIKE 模糊查询：'%abc'、'%abc%' 无法走索引，只有 'abc%' 可以。
5. 小表驱动大表：JOIN 时让小表先执行，减少扫描量。
6. 合理使用索引：为 WHERE/JOIN/ORDER BY 的列建索引；复合索引遵循最左前缀。
7. 分页优化：深分页 LIMIT 1000000, 10 很慢，可改用”上次最大 ID 后翻页”：

   SELECT * FROM score WHERE id > 1000000 ORDER BY id LIMIT 10;
   

8. 避免在索引列上做运算：WHERE age + 1 = 20 无法用索引，应写成 WHERE age = 19。

8.3 常用数据库特有功能简介

• MySQL：
  • 存储引擎：InnoDB（默认，支持事务/外键/行锁）vs MyISAM（不支持事务，查询快）。
  • SHOW ENGINE INNODB STATUS 查看事务/锁状态。
  • JSON 数据类型、ON DUPLICATE KEY UPDATE（存在则更新）。
• PostgreSQL：
  • 高级数据类型：数组、JSON/JSONB、GIS（PostGIS）。
  • CTE（公用表表达式，WITH 子句）—— MySQL 8.0 也已支持。
  • RETURNING 子句：DML 后直接返回受影响行。
• SQL Server：
  • CTE（可用 WITH cte AS (...) SELECT ...）。
  • 分页：OFFSET ... FETCH NEXT ... ROWS ONLY。

CTE 示例（MySQL 8.0+ / PostgreSQL / SQL Server）：

-- 用 CTE 让"每门课平均分"逻辑可复用、可读性更强
WITH course_avg AS (
    SELECT course_id, AVG(score) AS avg_score
    FROM score
    GROUP BY course_id
)
SELECT c.name, ca.avg_score
FROM course_avg ca
JOIN course c ON ca.course_id = c.id
WHERE ca.avg_score >= 75;

九、实践与资源

9.1 学习路径建议

• 阶段 1（入门）：掌握 DDL/DML、基础 SELECT（WHERE、ORDER BY、LIMIT、LIKE）。
• 阶段 2（核心）：深入 JOIN、子查询、聚合函数与 GROUP BY/HAVING——能解决 80% 取数需求。
• 阶段 3（进阶）：索引原理、事务与隔离级别、规范化设计。
• 阶段 4（加分）：窗口函数、CTE、存储过程、EXPLAIN 性能调优。

9.2 在线练习平台

平台	特点
LeetCode	数据库题库，面试刷题首选
HackerRank	SQL 分级练习，有证书
SQLZoo	交互式教程，适合新手入门
SQLBolt	交互式闯关式教程
W3Schools / Codecademy / DataCamp	综合教程 + 练习

9.3 推荐书籍与文档

• 《SQL 必知必会》：最经典的入门小册子，薄而全，适合快速上手。
• 《高性能 MySQL》：进阶必读，索引与优化圣经。
• 《SQL 学习指南》：系统性讲透 SQL 标准，比入门书深一层。
• 官方文档：遇到语法细节问题，MySQL 官方文档 永远是最权威的参考。

9.4 项目实践想法

• 设计博客系统数据库：用户、文章、评论、标签（多对多）。
• 设计电商平台数据库：用户、商品、分类、订单、订单明细、购物车——重点练习 1:N、M:N 和事务（下单 = 扣库存 + 生成订单 + 支付记录，体会 ACID）。
• 用公开数据集（如 MySQL 官方 employees 库、Kaggle 数据集）导入本地做数据分析练习：统计、分组、窗口函数、报表。
• 测试向实践：写一个”批量造数”存储过程；用 SQL 验证接口返回数据与数据库一致（数据库断言）；用 EXPLAIN 排查慢查询并给出索引优化建议。