引言：SQL在现代数据驱动世界中的核心地位
SQL（Structured Query Language）作为关系型数据库的标准语言，已经成为数据分析师、后端开发人员、产品经理甚至运营人员必备的技能。在当今数据爆炸的时代，掌握SQL不仅能帮助我们从海量数据中提取有价值的信息，还能显著提升工作效率和解决复杂业务问题的能力。
本文将从SQL基础语法开始，逐步深入到高级查询技巧和性能优化策略，通过大量实际工作场景中的案例，帮助读者建立完整的SQL知识体系。无论你是刚接触编程的初学者，还是希望提升SQL技能的从业者，本文都能为你提供系统性的指导。
第一部分：SQL基础入门
1.1 数据库和表的基本操作
在开始查询之前，我们需要先了解如何创建和管理数据库及表。这是SQL操作的基础。
— 创建数据库
CREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
— 使用数据库
USE company_db;
— 创建员工表
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(20),
hire_date DATE NOT NULL,
job_id INT NOT NULL,
salary DECIMAL(10,2),
commission_pct DECIMAL(4,2),
manager_id INT,
department_id INT,
INDEX idx_department (department_id),
INDEX idx_job (job_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);
— 创建部门表
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(50) NOT NULL,
manager_id INT,
location_id INT
);
— 创建职位表
CREATE TABLE jobs (
job_id INT PRIMARY KEY AUTO_INCREMENT,
job_title VARCHAR(100) NOT NULL,
min_salary DECIMAL(10,2),
max_salary DECIMAL(10,2)
);
— 插入示例数据
INSERT INTO employees (first_name, last_name, email, hire_date, job_id, salary, department_id) VALUES
(‘张’, ‘伟’, ‘zhangwei@company.com’, ‘2020-01-15’, 1, 8000.00, 1),
(‘李’, ‘娜’, ‘lina@company.com’, ‘2019-03-22’, 2, 12000.00, 1),
(‘王’, ‘强’, ‘wangqiang@company.com’, ‘2021-06-10’, 3, 6000.00, 2),
(‘刘’, ‘敏’, ‘liumin@company.com’, ‘2018-11-05’, 2, 13000.00, 3),
(‘陈’, ‘杰’, ‘chenjie@company.com’, ‘2022-02-28’, 4, 5000.00, 2);
关键点说明：
CREATE DATABASE
用于创建数据库，
CHARACTER SET utf8mb4
支持emoji和生僻字
PRIMARY KEY
定义主键，
AUTO_INCREMENT
实现自增
VARCHAR
是可变长度字符串，
DECIMAL
适合存储精确数值（如薪资）
INDEX
创建索引提高查询速度
FOREIGN KEY
定义外键约束保证数据完整性
1.2 基础查询：SELECT语句详解
SELECT是SQL中最常用的语句，用于从数据库中检索数据。
— 1. 基本查询：选择所有列
SELECT * FROM employees;
— 2. 选择特定列
SELECT employee_id, first_name, last_name, salary FROM employees;
— 3. 列别名（AS关键字）
SELECT
employee_id AS 员工编号,
CONCAT(first_name, ‘ ‘, last_name) AS 员工姓名,
salary AS 月薪
FROM employees;
— 4. 去重查询（DISTINCT）
SELECT DISTINCT department_id FROM employees;
— 5. 条件查询（WHERE子句）
SELECT * FROM employees WHERE salary > 8000;
— 6. 多条件查询（AND/OR）
SELECT * FROM employees
WHERE department_id = 1 AND salary >= 8000;
— 7. 范围查询（BETWEEN）
SELECT * FROM employees
WHERE salary BETWEEN 6000 AND 10000;
— 8. 集合查询（IN）
SELECT * FROM employees
WHERE department_id IN (1, 2, 3);
— 9. 模糊查询（LIKE）
SELECT * FROM employees
WHERE first_name LIKE ‘张%’; — 以”张”开头
— 10. 空值判断（IS NULL）
SELECT * FROM employees
WHERE commission_pct IS NULL;
— 11. 排序（ORDER BY）
SELECT * FROM employees
ORDER BY salary DESC; — 降序
— 12. 限制结果数量（LIMIT）
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 5; — 最近入职的5名员工
实际工作场景示例：
假设你需要找出薪资在8000-15000之间，且属于1号或2号部门的员工，并按薪资从高到低排序，只显示前10条记录：
SELECT
employee_id,
CONCAT(first_name, ‘ ‘, last_name) AS name,
salary,
department_id
FROM employees
WHERE salary BETWEEN 8000 AND 15000
AND department_id IN (1, 2)
ORDER BY salary DESC
LIMIT 10;
1.3 聚合函数与分组统计
聚合函数用于对一组数据进行计算，返回单个结果。
— 1. 常用聚合函数
SELECT
COUNT(*) AS 总员工数, — 计数
COUNT(DISTINCT department_id) AS 部门数量,
AVG(salary) AS 平均薪资, — 平均值
MAX(salary) AS 最高薪资, — 最大值
MIN(salary) AS 最低薪资, — 最小值
SUM(salary) AS 薪资总和 — 求和
FROM employees;
— 2. 分组统计（GROUP BY）
SELECT
department_id,
COUNT(*) AS 员工数量,
AVG(salary) AS 平均薪资,
MAX(salary) AS 最高薪资
FROM employees
GROUP BY department_id;
— 3. 分组后条件筛选（HAVING）
SELECT
department_id,
COUNT(*) AS 员工数量,
AVG(salary) AS 平均薪资
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2 AND AVG(salary) > 7000;
— 4. 多列分组
SELECT
department_id,
job_id,
COUNT(*) AS 人数,
AVG(salary) AS 平均薪资
FROM employees
GROUP BY department_id, job_id;
— 5. WITH ROLLUP 生成汇总行
SELECT
department_id,
job_id,
COUNT(*) AS 人数,
AVG(salary) AS 平均薪资
FROM employees
GROUP BY department_id, job_id WITH ROLLUP;
实际工作场景：
生成部门薪资报表，找出平均薪资超过8000且员工数不少于2人的部门：
SELECT
d.department_name AS 部门名称,
COUNT(e.employee_id) AS 员工数量,
ROUND(AVG(e.salary), 2) AS 平均薪资,
SUM(e.salary) AS 总薪资
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) >= 2 AND AVG(e.salary) > 8000
ORDER BY 平均薪资 DESC;
1.4 连接查询（JOIN）
实际业务中，数据通常分布在多个表中，JOIN用于连接这些表。
— 1. 内连接（INNER JOIN）- 只返回匹配的行
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name,
j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
— 2. 左连接（LEFT JOIN）- 返回左表所有行，右表不匹配则为NULL
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
— 3. 右连接（RIGHT JOIN）- 返回右表所有行
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
— 4. 全外连接（FULL OUTER JOIN）- MySQL不支持，需用UNION模拟
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
— 5. 自连接（SELF JOIN）- 表与自身连接
SELECT
e.employee_id,
e.first_name AS 员工姓名,
m.first_name AS 经理姓名
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
— 6. 多表连接复杂查询
SELECT
e.employee_id,
e.first_name,
e.last_name,
j.job_title,
d.department_name,
l.city
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE e.salary > 8000
ORDER BY d.department_name, e.salary DESC;
实际工作场景：
找出每个部门中薪资高于该部门平均薪资的员工：
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.salary,
d.department_name,
dept_avg.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;
1.5 子查询
子查询是嵌套在其他查询中的查询，用于实现复杂逻辑。
— 1. 标量子查询（返回单个值）
SELECT
employee_id,
first_name,
salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
— 2. 列子查询（返回一列）
SELECT * FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id = 1700
);
— 3. 行子查询（返回一行多列）
SELECT * FROM employees
WHERE (department_id, job_id) = (
SELECT department_id, job_id FROM employees WHERE employee_id = 101
);
— 4. 表子查询（返回多行多列）
SELECT
e.employee_id,
e.first_name,
e.salary,
dept_stats.avg_salary,
dept_stats.max_salary
FROM employees e
JOIN (
SELECT
department_id,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
) dept_stats ON e.department_id = dept_stats.department_id;
— 5. EXISTS / NOT EXISTS
SELECT * FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.department_id
AND e.salary > 10000
);
— 6. 相关子查询（依赖外部查询）
SELECT
e1.employee_id,
e1.first_name,
e1.salary,
e1.department_id
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
实际工作场景：
找出所有没有员工的部门：
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.department_id
);
第二部分：SQL进阶技巧
2.1 窗口函数（Window Functions）
窗口函数是SQL高级查询的核心，允许在不减少行数的情况下进行聚合计算。
— 1. 排名函数
SELECT
employee_id,
first_name,
salary,
department_id,
— 行号（不考虑并列）
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
— 排名（考虑并列，跳过后续名次）
RANK() OVER (ORDER BY salary DESC) AS rank_num,
— 密集排名（考虑并列，不跳过后续名次）
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num,
— 分组内行号
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_row_num
FROM employees;
— 2. 分位函数
SELECT
employee_id,
first_name,
salary,
department_id,
— 分区内的百分比排名
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pct_rank,
— 分区内的分位数（0-1）
NTILE(4) OVER (PARTITION BY department_id ORDER BY salary) AS quartile
FROM employees;
— 3. 窗口聚合函数
SELECT
employee_id,
first_name,
salary,
department_id,
— 分区内累计薪资
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total,
— 分区内平均薪资（所有行）
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
— 分区内前N行平均值
AVG(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM employees;
— 4. LAG和LEAD函数
SELECT
employee_id,
first_name,
salary,
department_id,
— 获取前一行的薪资（按薪资排序）
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
— 获取后一行的薪资
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary,
— 获取前2行的薪资差值
salary – LAG(salary, 2) OVER (PARTITION BY department_id ORDER BY salary) AS salary_diff
FROM employees;
— 5. FIRST_VALUE和LAST_VALUE
SELECT
employee_id,
first_name,
salary,
department_id,
— 分区内第一个薪资值
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_salary,
— 分区内最后一个薪资值
LAST_VALUE(salary) OVER (
PARTITION BY department_id
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_salary
FROM employees;
实际工作场景：
找出每个部门薪资排名前三的员工：
SELECT * FROM (
SELECT
employee_id,
first_name,
salary,
department_id,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees
) ranked
WHERE dept_rank <= 3;
2.2 公用表表达式（CTE）
CTE使复杂查询更易读和维护，支持递归查询。
-- 1. 普通CTE
WITH dept_stats AS (
SELECT
department_id,
AVG(salary) AS avg_salary,
COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2
)
SELECT
d.department_name,
ds.avg_salary,
ds.emp_count
FROM dept_stats ds
JOIN departments d ON ds.department_id = d.department_id;
— 2. 多个CTE
WITH dept_stats AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id
),
job_stats AS (
SELECT job_id, AVG(salary) AS avg_salary
FROM employees GROUP BY job_id
)
SELECT
e.employee_id,
e.first_name,
e.salary,
ds.avg_salary AS dept_avg,
js.avg_salary AS job_avg
FROM employees e
JOIN dept_stats ds ON e.department_id = ds.department_id
JOIN job_stats js ON e.job_id = js.job_id;
— 3. 递归CTE（用于层级数据）
WITH RECURSIVE employee_hierarchy AS (
— 锚点：顶级经理
SELECT
employee_id,
first_name,
manager_id,
0 AS level,
CAST(employee_id AS CHAR(1000)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
— 递归部分：下属员工
SELECT
e.employee_id,
e.first_name,
e.manager_id,
eh.level + 1,
CONCAT(eh.path, ‘->’, e.employee_id)
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, path;
— 4. 递归CTE（生成数字序列）
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;
实际工作场景：
计算每个员工到顶级经理的层级关系：
WITH RECURSIVE org_chart AS (
SELECT
employee_id,
first_name,
manager_id,
0 AS level,
CAST(first_name AS CHAR(1000)) AS hierarchy_path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.first_name,
e.manager_id,
oc.level + 1,
CONCAT(oc.hierarchy_path, ' > ‘, e.first_name)
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT
employee_id,
first_name,
level AS 层级,
hierarchy_path AS 组织路径
FROM org_chart
ORDER BY level, employee_id;
2.3 高级条件表达式
— 1. CASE WHEN 条件分支
SELECT
employee_id,
first_name,
salary,
CASE
WHEN salary < 6000 THEN '低薪'
WHEN salary BETWEEN 6000 AND 10000 THEN '中等'
WHEN salary > 10000 THEN ‘高薪’
ELSE ‘未知’
END AS 薪资等级,
CASE department_id
WHEN 1 THEN ‘技术部’
WHEN 2 THEN ‘市场部’
WHEN 3 THEN ‘财务部’
ELSE ‘其他’
END AS 部门名称
FROM employees;
— 2. CASE WHEN 聚合统计
SELECT
department_id,
COUNT(*) AS 总人数,
SUM(CASE WHEN salary >= 10000 THEN 1 ELSE 0 END) AS 高薪人数,
SUM(CASE WHEN salary < 6000 THEN 1 ELSE 0 END) AS 低薪人数,
ROUND(
SUM(CASE WHEN salary >= 10000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
2
) AS 高薪比例
FROM employees
GROUP BY department_id;
— 3. COALESCE 和 NULLIF
SELECT
employee_id,
first_name,
salary,
COALESCE(commission_pct, 0) AS commission, — NULL转为0
NULLIF(salary, 5000) AS salary_nullif — 如果salary=5000则返回NULL
FROM employees;
— 4. IF 函数（MySQL特有）
SELECT
employee_id,
first_name,
salary,
IF(salary > 8000, ‘优秀’, ‘待提升’) AS performance
FROM employees;
实际工作场景：
生成员工绩效评估报告：
SELECT
e.employee_id,
e.first_name,
e.salary,
d.department_name,
j.job_title,
CASE
WHEN e.salary > j.max_salary * 0.9 THEN ‘卓越’
WHEN e.salary > j.max_salary * 0.7 THEN ‘优秀’
WHEN e.salary > j.max_salary * 0.5 THEN ‘合格’
ELSE ‘待提升’
END AS 绩效等级,
CASE
WHEN e.salary > j.max_salary THEN ‘超出上限’
WHEN e.salary < j.min_salary THEN '低于下限'
ELSE '正常范围'
END AS 薪资范围
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id;
2.4 集合操作
-- 1. UNION / UNION ALL
-- UNION 自动去重，UNION ALL 保留重复
SELECT employee_id, first_name, salary FROM employees WHERE salary > 10000
UNION ALL
SELECT employee_id, first_name, salary FROM employees WHERE department_id = 1;
— 2. INTERSECT（交集）- MySQL 8.0+ 支持
SELECT employee_id FROM employees WHERE department_id = 1
INTERSECT
SELECT employee_id FROM employees WHERE salary > 8000;
— 3. EXCEPT（差集）- MySQL 8.0+ 支持
SELECT employee_id FROM employees WHERE department_id = 1
EXCEPT
SELECT employee_id FROM employees WHERE salary > 8000;
— 4. 模拟INTERSECT（低版本MySQL）
SELECT DISTINCT e1.employee_id
FROM employees e1
INNER JOIN employees e2 ON e1.employee_id = e2.employee_id
WHERE e1.department_id = 1 AND e2.salary > 8000;
第三部分：SQL性能优化技巧
3.1 索引优化策略
索引是提升查询性能的关键，但不当使用会适得其反。
— 1. 创建合适的索引
— 单列索引
CREATE INDEX idx_salary ON employees(salary);
— 复合索引（注意顺序）
CREATE INDEX idx_dept_salary ON employees(department_id, salary);
— 唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);
— 全文索引（用于文本搜索）
CREATE FULLTEXT INDEX idx_ft_name ON employees(first_name, last_name);
— 2. 查看索引
SHOW INDEX FROM employees;
— 3. 删除索引
DROP INDEX idx_salary ON employees;
— 4. 分析查询计划
EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND salary > 8000;
— 5. 强制使用索引
SELECT * FROM employees FORCE INDEX (idx_dept_salary)
WHERE department_id = 1 AND salary > 8000;
— 6. 忽略索引
SELECT * FROM employees IGNORE INDEX (idx_salary)
WHERE salary > 8000;
索引使用原则：
最左前缀原则
：复合索引
(a,b,c)
能被
(a)
,
(a,b)
,
(a,b,c)
使用，但不能被
(b,c)
或
(c)
使用
选择性高的列优先
：唯一值多的列更适合建索引（如email）
避免过度索引
：每个索引都需要维护成本
覆盖索引
：索引包含所有查询列，避免回表
前缀索引
：对长文本列只索引前N个字符
— 前缀索引示例
CREATE INDEX idx_email_prefix ON employees(email(10));
— 覆盖索引示例
CREATE INDEX idx_cover ON employees(department_id, salary, first_name);
— 这个查询可以使用覆盖索引
SELECT first_name, salary FROM employees WHERE department_id = 1;
3.2 查询语句优化
— 1. 避免SELECT *
— 不推荐
SELECT * FROM employees WHERE department_id = 1;
— 推荐
SELECT employee_id, first_name, salary FROM employees WHERE department_id = 1;
— 2. 避免在WHERE子句中对字段进行函数操作
— 不推荐（无法使用索引）
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
— 推荐
SELECT * FROM employees WHERE hire_date BETWEEN ‘2020-01-01’ AND ‘2020-12-31’;
— 3. 避免在WHERE子句中使用OR（用UNION ALL替代）
— 不推荐
SELECT * FROM employees WHERE salary > 10000 OR department_id = 1;
— 推荐
SELECT * FROM employees WHERE salary > 10000
UNION ALL
SELECT * FROM employees WHERE department_id = 1 AND salary <= 10000;
-- 4. 慎用DISTINCT
-- 不推荐（可能产生临时表）
SELECT DISTINCT department_id FROM employees;
-- 推荐（如果不需要去重）
SELECT department_id FROM employees;
-- 5. 使用EXISTS替代IN（当子查询结果集大时）
-- 不推荐
SELECT * FROM employees WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700
);
-- 推荐
SELECT * FROM employees e
WHERE EXISTS (
SELECT 1 FROM departments d
WHERE d.department_id = e.department_id
AND d.location_id = 1700
);
-- 6. 使用JOIN替代子查询
-- 不推荐
SELECT * FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700
);
-- 推荐
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;
-- 7. 分页优化（避免大偏移量）
-- 不推荐（扫描大量数据）
SELECT * FROM employees ORDER BY employee_id LIMIT 1000000, 10;
-- 推荐（使用子查询）
SELECT e.* FROM employees e
JOIN (
SELECT employee_id
FROM employees
ORDER BY employee_id
LIMIT 1000000, 10
) tmp ON e.employee_id = tmp.employee_id;
-- 8. 批量操作
-- 不推荐（逐条插入）
INSERT INTO employees (first_name, last_name, salary) VALUES ('张', '三', 5000);
INSERT INTO employees (first_name, last_name, salary) VALUES ('李', '四', 6000);
-- 推荐（批量插入）
INSERT INTO employees (first_name, last_name, salary) VALUES
('张', '三', 5000),
('李', '四', 6000),
('王', '五', 7000);
-- 9. 使用UNION ALL替代UNION
-- 不推荐（自动去重，性能差）
SELECT * FROM employees WHERE salary > 10000
UNION
SELECT * FROM employees WHERE department_id = 1;
— 推荐（如果确认无重复）
SELECT * FROM employees WHERE salary > 10000
UNION ALL
SELECT * FROM employees WHERE department_id = 1;
— 10. 避免使用负向查询
— 不推荐
SELECT * FROM employees WHERE salary NOT IN (5000, 6000);
— 推荐
SELECT * FROM employees WHERE salary NOT IN (5000, 6000) AND salary > 0;
3.3 执行计划分析
— 1. 基本EXPLAIN
EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND salary > 8000;
— 2. 详细EXPLAIN（包含执行时间等信息）
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;
— 3. 查看执行计划详情
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 1;
— 4. 查看慢查询日志
SHOW VARIABLES LIKE ‘slow_query%’;
SHOW VARIABLES LIKE ‘long_query_time’;
— 5. 开启慢查询日志（临时）
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL long_query_time = 1; — 超过1秒的查询记录
SET GLOBAL slow_query_log_file = ‘/var/log/mysql/slow.log’;
— 6. 查看当前运行的查询
SHOW PROCESSLIST;
— 7. 查看表状态
SHOW TABLE STATUS LIKE ’employees’;
— 8. 查看索引使用情况
SHOW INDEX FROM employees;
执行计划关键字段解读：
type
：访问类型（ALL > index > range > ref > eq_ref > const > system）
possible_keys
：可能使用的索引
key
：实际使用的索引
rows
：预估扫描行数
Extra
：额外信息（Using index, Using where, Using filesort等）
3.4 事务与锁优化
— 1. 查看当前事务隔离级别
SELECT @@transaction_isolation;
— 2. 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
— 3. 事务示例
START TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1;
UPDATE employees SET salary = salary – 1000 WHERE employee_id = 2;
COMMIT; — 或 ROLLBACK;
— 4. 优化事务（短事务原则）
— 不推荐（长事务）
START TRANSACTION;
— 执行大量业务逻辑…
— 可能持续几分钟
COMMIT;
— 推荐（短事务）
START TRANSACTION;
UPDATE employees SET salary = 8000 WHERE employee_id = 1;
COMMIT;
— 5. 查看锁信息
SHOW ENGINE INNODB STATUS;
— 6. 查看当前锁
SELECT * FROM information_schema.INNODB_LOCKS;
— 7. 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
— 8. 优化锁（使用乐观锁）
— 添加版本号字段
ALTER TABLE employees ADD COLUMN version INT DEFAULT 0;
— 更新时检查版本
UPDATE employees
SET salary = 9000, version = version + 1
WHERE employee_id = 1 AND version = 0;
— 9. 使用SELECT FOR UPDATE（悲观锁）
START TRANSACTION;
SELECT * FROM employees WHERE employee_id = 1 FOR UPDATE;
— 处理业务逻辑…
UPDATE employees SET salary = 8000 WHERE employee_id = 1;
COMMIT;
3.5 数据库设计优化
— 1. 规范化设计（减少数据冗余）
— 不规范的设计
CREATE TABLE orders_bad (
order_id INT,
customer_name VARCHAR(100), — 冗余
customer_email VARCHAR(100), — 冗余
product_name VARCHAR(100), — 冗余
product_price DECIMAL(10,2) — 冗余
);
— 规范化设计
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
— 2. 适当反规范化（提升查询性能）
— 添加冗余字段避免JOIN
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
— 3. 分区表（大表优化）
CREATE TABLE employees_partitioned (
employee_id INT,
first_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(hire_date)) (
PARTITION p2018 VALUES LESS THAN (2019),
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
— 4. 分区表查询（自动只扫描相关分区）
SELECT * FROM employees_partitioned WHERE hire_date BETWEEN ‘2020-01-01’ AND ‘2020-12-31′;
— 5. 查看分区信息
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ’employees_partitioned’;
— 6. 垂直分表（大字段分离）
CREATE TABLE employees_core (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
salary DECIMAL(10,2)
);
CREATE TABLE employees_details (
employee_id INT PRIMARY KEY,
resume TEXT,
photo BLOB,
FOREIGN KEY (employee_id) REFERENCES employees_core(employee_id)
);
— 7. 水平分表（按业务拆分）
— 原表
CREATE TABLE orders (
order_id INT,
order_date DATE,
amount DECIMAL(10,2)
);
— 按年份分表
CREATE TABLE orders_2020 (LIKE orders);
CREATE TABLE orders_2021 (LIKE orders);
CREATE TABLE orders_2022 (LIKE orders);
3.6 数据库参数优化
— 1. 查看当前配置
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘query_cache_size’;
SHOW VARIABLES LIKE ‘max_connections’;
— 2. 优化InnoDB缓冲池（建议设置为物理内存的50-70%）
SET GLOBAL innodb_buffer_pool_size = 2147483648; — 2GB
— 3. 优化排序缓冲区
SET GLOBAL sort_buffer_size = 2097152; — 2MB
— 4. 优化连接数
SET GLOBAL max_connections = 500;
— 5. 优化查询缓存（MySQL 8.0已移除）
SET GLOBAL query_cache_size = 67108864; — 64MB
— 6. 查看性能指标
SHOW STATUS LIKE ‘Threads_running’;
SHOW STATUS LIKE ‘Slow_queries’;
SHOW STATUS LIKE ‘Innodb_buffer_pool_reads’;
— 7. 查看表统计信息
ANALYZE TABLE employees;
— 8. 更新表统计信息（优化器需要）
UPDATE statistics SET statistic_value = NOW() WHERE table_name = ’employees’;
3.7 实际工作中的性能优化案例
案例1：慢查询优化
— 问题：查询每月入职人数，执行缓慢
SELECT
DATE_FORMAT(hire_date, ‘%Y-%m’) AS hire_month,
COUNT(*) AS emp_count
FROM employees
GROUP BY hire_month;
— 优化1：添加索引
CREATE INDEX idx_hire_date ON employees(hire_date);
— 优化2：使用覆盖索引
CREATE INDEX idx_hire_cover ON employees(hire_date, employee_id);
— 优化3：如果数据量极大，使用物化视图或定期汇总表
CREATE TABLE monthly_hire_summary (
hire_month DATE,
emp_count INT,
PRIMARY KEY (hire_month)
);
— 定期更新汇总表
INSERT INTO monthly_hire_summary
SELECT
DATE_FORMAT(hire_date, ‘%Y-%m-01’) AS hire_month,
COUNT(*) AS emp_count
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY hire_month
ON DUPLICATE KEY UPDATE emp_count = VALUES(emp_count);
案例2：大表JOIN优化
— 问题：员工表（100万行）和部门表（100行）JOIN缓慢
— 不优化
SELECT e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;
— 优化1：确保关联字段有索引
CREATE INDEX idx_dept_id ON employees(department_id);
— 优化2：先过滤小表再JOIN
SELECT e.*, d.department_name
FROM departments d
JOIN employees e ON e.department_id = d.department_id
WHERE d.location_id = 1700;
— 优化3：使用子查询减少JOIN数据量
SELECT e.*, dept.department_name
FROM employees e
JOIN (
SELECT department_id, department_name
FROM departments
WHERE location_id = 1700
) dept ON e.department_id = dept.department_id;
案例3：分页查询优化
— 问题：查询第1000页数据，每页20条，执行缓慢
— 不优化
SELECT * FROM employees ORDER BY employee_id LIMIT 20000, 20;
— 优化1：使用延迟关联
SELECT e.* FROM employees e
JOIN (
SELECT employee_id
FROM employees
ORDER BY employee_id
LIMIT 20000, 20
) tmp ON e.employee_id = tmp.employee_id;
— 优化2：记录上次最大ID
— 第一次查询
SELECT * FROM employees WHERE employee_id > 0 ORDER BY employee_id LIMIT 20;
— 第二次查询（假设上次最大ID为1000）
SELECT * FROM employees WHERE employee_id > 1000 ORDER BY employee_id LIMIT 20;
— 优化3：使用游标（MySQL 8.0+）
DECLARE cur_id INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT employee_id FROM employees ORDER BY employee_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO cur_id;
IF done THEN
LEAVE read_loop;
END IF;
— 处理每条记录
END LOOP;
CLOSE cur;
第四部分：实际工作难题解决方案
4.1 数据清洗与转换
场景：处理脏数据
— 1. 清理重复数据（保留最新记录）
DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.employee_id < e2.employee_id
AND e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email;
-- 2. 填充缺失值
UPDATE employees
SET commission_pct = 0
WHERE commission_pct IS NULL;
-- 3. 数据标准化
UPDATE employees
SET first_name = TRIM(UPPER(first_name)),
last_name = TRIM(UPPER(last_name));
-- 4. 处理异常值
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM employees
WHERE department_id = employees.department_id
)
WHERE salary > 50000 OR salary < 1000;
-- 5. 数据验证
SELECT
employee_id,
first_name,
email,
CASE
WHEN email NOT LIKE '%@%.%' THEN '邮箱格式错误'
WHEN salary < 0 THEN '薪资为负数'
WHEN hire_date > CURDATE() THEN ‘入职日期错误’
ELSE ‘正常’
END AS 数据状态
FROM employees;
场景：数据格式转换
— 1. 日期格式转换
SELECT
employee_id,
hire_date,
DATE_FORMAT(hire_date, ‘%Y年%m月%d日’) AS hire_date_cn,
DATE_FORMAT(hire_date, ‘%Y-%m-%d’) AS hire_date_std,
YEAR(hire_date) AS hire_year,
MONTH(hire_date) AS hire_month,
DAY(hire_date) AS hire_day,
WEEKDAY(hire_date) AS weekday — 0=周一, 6=周日
FROM employees;
— 2. 字符串处理
SELECT
employee_id,
first_name,
last_name,
CONCAT(first_name, ‘ ‘, last_name) AS full_name,
CONCAT_WS(‘-‘, first_name, last_name, employee_id) AS unified_id,
UPPER(first_name) AS upper_name,
LOWER(email) AS lower_email,
LENGTH(email) AS email_length,
SUBSTRING(email, 1, 5) AS email_prefix,
REPLACE(email, ‘@company.com’, ‘@corp.com’) AS new_email
FROM employees;
— 3. 数值处理
SELECT
employee_id,
salary,
ROUND(salary, -3) AS rounded_salary, — 四舍五入到千位
FORMAT(salary, 2) AS formatted_salary, — 千分位格式化
CEIL(salary / 1000) AS salary_ceil,
FLOOR(salary / 1000) AS salary_floor,
MOD(salary, 1000) AS salary_mod
FROM employees;
4.2 复杂业务统计
场景：RFM分析（用户价值分析）
— 假设有订单表 orders(order_id, customer_id, order_date, amount)
WITH customer_rfm AS (
SELECT
customer_id,
DATEDIFF(CURDATE(), MAX(order_date)) AS recency, — 最近购买天数
COUNT(*) AS frequency, — 购买频次
SUM(amount) AS monetary — 购买金额
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY customer_id
),
rfm_score AS (
SELECT
customer_id,
recency,
frequency,
monetary,
CASE
WHEN recency <= 30 THEN 5
WHEN recency <= 60 THEN 4
WHEN recency <= 90 THEN 3
WHEN recency <= 180 THEN 2
ELSE 1
END AS r_score,
CASE
WHEN frequency >= 20 THEN 5
WHEN frequency >= 10 THEN 4
WHEN frequency >= 5 THEN 3
WHEN frequency >= 2 THEN 2
ELSE 1
END AS f_score,
CASE
WHEN monetary >= 10000 THEN 5
WHEN monetary >= 5000 THEN 4
WHEN monetary >= 2000 THEN 3
WHEN monetary >= 1000 THEN 2
ELSE 1
END AS m_score
FROM customer_rfm
)
SELECT
customer_id,
recency,
frequency,
monetary,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) AS rfm_code,
CASE
WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN ‘重要价值客户’
WHEN r_score >= 4 AND f_score < 3 AND m_score >= 4 THEN ‘重要发展客户’
WHEN r_score < 3 AND f_score >= 4 AND m_score >= 4 THEN ‘重要保持客户’
WHEN r_score < 3 AND f_score < 3 AND m_score >= 4 THEN ‘重要挽留客户’
WHEN r_score >= 4 AND f_score >= 4 AND m_score < 3 THEN '一般价值客户'
ELSE '一般客户'
END AS customer_segment
FROM rfm_score
ORDER BY monetary DESC;
场景：销售漏斗分析
-- 假设有用户行为表 user_events(event_id, user_id, event_type, event_time)
WITH user_funnel AS (
SELECT
user_id,
MAX(CASE WHEN event_type = 'visit' THEN 1 ELSE 0 END) AS has_visit,
MAX(CASE WHEN event_type = 'register' THEN 1 ELSE 0 END) AS has_register,
MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS has_cart,
MAX(CASE WHEN event_type = 'checkout' THEN 1 ELSE 0 END) AS has_checkout,
MAX(CASE WHEN event_type = 'payment' THEN 1 ELSE 0 END) AS has_payment
FROM user_events
WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY user_id
)
SELECT
COUNT(*) AS total_users,
SUM(has_visit) AS visit_users,
SUM(has_register) AS register_users,
SUM(has_cart) AS cart_users,
SUM(has_checkout) AS checkout_users,
SUM(has_payment) AS payment_users,
ROUND(SUM(has_register) * 100.0 / SUM(has_visit), 2) AS register_rate,
ROUND(SUM(has_cart) * 100.0 / SUM(has_register), 2) AS cart_rate,
ROUND(SUM(has_checkout) * 100.0 / SUM(has_cart), 2) AS checkout_rate,
ROUND(SUM(has_payment) * 100.0 / SUM(has_checkout), 2) AS payment_rate
FROM user_funnel;
4.3 数据同步与ETL
场景：增量数据同步
— 1. 创建同步日志表
CREATE TABLE sync_log (
log_id INT PRIMARY KEY AUTO_INCREMENT,
table_name VARCHAR(50),
last_sync_time DATETIME,
sync_count INT,
status ENUM(‘success’, ‘failed’)
);
— 2. 增量同步存储过程
DELIMITER $$
CREATE PROCEDURE sync_employee_data()
BEGIN
DECLARE last_sync DATETIME;
DECLARE sync_count INT DEFAULT 0;
— 获取上次同步时间
SELECT last_sync_time INTO last_sync
FROM sync_log
WHERE table_name = ’employees’ AND status = ‘success’
ORDER BY last_sync_time DESC LIMIT 1;
IF last_sync IS NULL THEN
SET last_sync = ‘1970-01-01 00:00:00′;
END IF;
— 同步新增数据
INSERT INTO employees_backup
SELECT * FROM employees
WHERE created_time > last_sync;
SET sync_count = ROW_COUNT();
— 更新同步日志
INSERT INTO sync_log (table_name, last_sync_time, sync_count, status)
VALUES (’employees’, NOW(), sync_count, ‘success’);
SELECT sync_count AS 同步记录数;
END$$
DELIMITER ;
— 3. 调用存储过程
CALL sync_employee_data();
— 4. 创建定时任务（Event）
CREATE EVENT sync_event
ON SCHEDULE EVERY 1 HOUR
DO
CALL sync_employee_data();
— 5. 查看事件
SHOW EVENTS;
场景：数据仓库ETL（Extract, Transform, Load）
— 1. 创建数据仓库表
CREATE TABLE dw_employee_summary (
summary_date DATE,
department_id INT,
employee_count INT,
avg_salary DECIMAL(10,2),
total_salary DECIMAL(15,2),
PRIMARY KEY (summary_date, department_id)
);
— 2. ETL过程
DELIMITER $$
CREATE PROCEDURE etl_employee_summary(IN etl_date DATE)
BEGIN
— 删除当天数据（幂等性）
DELETE FROM dw_employee_summary WHERE summary_date = etl_date;
— 抽取转换加载
INSERT INTO dw_employee_summary
SELECT
etl_date AS summary_date,
department_id,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
SUM(salary) AS total_salary
FROM employees
WHERE DATE(hire_date) <= etl_date
GROUP BY department_id;
SELECT CONCAT('ETL完成，处理了 ', ROW_COUNT(), ' 条记录') AS result;
END$$
DELIMITER ;
-- 3. 执行ETL
CALL etl_employee_summary('2023-10-01');
-- 4. 验证数据
SELECT * FROM dw_employee_summary WHERE summary_date = '2023-10-01';
4.4 数据安全与权限管理
-- 1. 创建用户
CREATE USER 'data_analyst'@'%' IDENTIFIED BY 'StrongPassword123!';
-- 2. 授予权限
-- 只读权限
GRANT SELECT ON company_db.* TO 'data_analyst'@'%';
-- 特定表权限
GRANT SELECT, INSERT ON company_db.employees TO 'data_analyst'@'%';
-- 列级权限（只能看部分列）
GRANT SELECT (employee_id, first_name, department_id)
ON company_db.employees
TO 'data_analyst'@'%';
-- 3. 查看权限
SHOW GRANTS FOR 'data_analyst'@'%';
-- 4. 回收权限
REVOKE INSERT ON company_db.employees FROM 'data_analyst'@'%';
-- 5. 创建角色（MySQL 8.0+）
CREATE ROLE 'readonly_role';
GRANT SELECT ON company_db.* TO 'readonly_role';
GRANT 'readonly_role' TO 'data_analyst'@'%';
-- 6. 设置行级权限（使用视图）
CREATE VIEW v_employees_dept1 AS
SELECT * FROM employees WHERE department_id = 1;
GRANT SELECT ON company_db.v_employees_dept1 TO 'manager1'@'%';
-- 7. 审计日志（MySQL企业版或MariaDB）
-- 查看审计日志配置
SHOW VARIABLES LIKE 'audit_log%';
-- 8. 数据加密
-- 加密敏感数据
ALTER TABLE employees MODIFY email VARBINARY(100);
UPDATE employees SET email = AES_ENCRYPT(email, 'encryption_key');
-- 解密查询
SELECT CAST(AES_DECRYPT(email, 'encryption_key') AS CHAR) AS email
FROM employees;
4.5 监控与维护
-- 1. 查看数据库大小
SELECT
table_schema AS 数据库名,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 总大小MB,
ROUND(SUM(data_length) / 1024 / 1024, 2) AS 数据大小MB,
ROUND(SUM(index_length) / 1024 / 1024, 2) AS 索引大小MB
FROM information_schema.tables
WHERE table_schema = 'company_db'
GROUP BY table_schema;
-- 2. 查看表大小排序
SELECT
table_name AS 表名,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS 总大小MB,
ROUND(data_length / 1024 / 1024, 2) AS 数据大小MB,
ROUND(index_length / 1024 / 1024, 2) AS 索引大小MB,
table_rows AS 行数
FROM information_schema.tables
WHERE table_schema = 'company_db'
ORDER BY (data_length + index_length) DESC;
-- 3. 查看慢查询
SELECT * FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC
LIMIT 10;
— 4. 查看未使用的索引
SELECT
object_schema,
object_name,
index_name,
count_star,
count_read,
count_write,
count_fetch,
count_insert,
count_update,
count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = ‘company_db’
AND index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
— 5. 查看表碎片
SELECT
table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_size_mb,
ROUND(data_free * 100 / (data_length + index_length), 2) AS fragmentation_ratio
FROM information_schema.tables
WHERE table_schema = ‘company_db’
AND data_free > 0
ORDER BY fragmentation_ratio DESC;
— 6. 优化表（消除碎片）
OPTIMIZE TABLE employees;
— 7. 修复表
REPAIR TABLE employees;
— 8. 备份策略（mysqldump命令行）
— 全量备份
mysqldump -u root -p company_db > company_db_backup.sql
— 增量备份（需要开启binlog）
— 查看binlog状态
SHOW VARIABLES LIKE ‘log_bin’;
— 9. 查看当前连接
SHOW PROCESSLIST;
— 10. 杀死慢查询
KILL [process_id];
第五部分：高级主题与最佳实践
5.1 存储过程与函数
— 1. 创建存储过程
DELIMITER $$
CREATE PROCEDURE get_employee_salary_range(
IN min_salary DECIMAL(10,2),
IN max_salary DECIMAL(10,2),
OUT emp_count INT
)
BEGIN
— 声明变量
DECLARE total_count INT DEFAULT 0;
— 计算符合条件的员工数
SELECT COUNT(*) INTO total_count
FROM employees
WHERE salary BETWEEN min_salary AND max_salary;
— 设置输出参数
SET emp_count = total_count;
— 返回结果集
SELECT
employee_id,
first_name,
salary,
department_id
FROM employees
WHERE salary BETWEEN min_salary AND max_salary
ORDER BY salary DESC;
END$$
DELIMITER ;
— 2. 调用存储过程
CALL get_employee_salary_range(6000, 12000, @count);
SELECT @count AS 员工数量;
— 3. 创建函数
DELIMITER $$
CREATE FUNCTION get_department_avg_salary(dept_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE avg_sal DECIMAL(10,2);
SELECT AVG(salary) INTO avg_sal
FROM employees
WHERE department_id = dept_id;
RETURN IFNULL(avg_sal, 0);
END$$
DELIMITER ;
— 4. 使用函数
SELECT
employee_id,
first_name,
salary,
get_department_avg_salary(department_id) AS dept_avg,
salary – get_department_avg_salary(department_id) AS diff
FROM employees;
— 5. 存储过程示例：批量更新薪资
DELIMITER $$
CREATE PROCEDURE update_salary_by_dept(
IN dept_id INT,
IN percent DECIMAL(5,2)
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE emp_id INT;
DECLARE cur_sal DECIMAL(10,2);
DECLARE emp_cursor CURSOR FOR
SELECT employee_id, salary
FROM employees
WHERE department_id = dept_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO emp_id, cur_sal;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE employees
SET salary = cur_sal * (1 + percent / 100)
WHERE employee_id = emp_id;
END LOOP;
CLOSE emp_cursor;
— 提交事务
COMMIT;
SELECT CONCAT(‘已更新部门 ‘, dept_id, ‘ 的员工薪资’) AS result;
END$$
DELIMITER ;
— 6. 调用批量更新
CALL update_salary_by_dept(1, 10.5);
5.2 触发器
— 1. 创建BEFORE INSERT触发器
DELIMITER $$
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
— 自动设置创建时间
SET NEW.created_time = NOW();
— 验证邮箱格式
IF NEW.email NOT LIKE ‘%@%.%’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘邮箱格式不正确’;
END IF;
— 自动设置manager_id（如果未提供）
IF NEW.manager_id IS NULL THEN
SET NEW.manager_id = (SELECT manager_id FROM departments WHERE department_id = NEW.department_id);
END IF;
END$$
DELIMITER ;
— 2. 创建AFTER UPDATE触发器（审计日志）
DELIMITER $$
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
— 记录薪资变更
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_audit_log (
employee_id,
old_salary,
new_salary,
change_time,
changed_by
) VALUES (
OLD.employee_id,
OLD.salary,
NEW.salary,
NOW(),
CURRENT_USER()
);
END IF;
END$$
DELIMITER ;
— 3. 创建BEFORE DELETE触发器（防止误删）
DELIMITER $$
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
— 检查是否为关键员工
IF OLD.salary > 15000 THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘不能删除高薪员工，请联系HR’;
END IF;
— 备份数据
INSERT INTO employees_backup
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.salary, NOW());
END$$
DELIMITER ;
— 4. 查看触发器
SHOW TRIGGERS;
— 5. 删除触发器
DROP TRIGGER before_employee_insert;
5.3 事件调度器
— 1. 开启事件调度器
SET GLOBAL event_scheduler = ON;
— 2. 创建每日统计事件
CREATE EVENT daily_employee_stats
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
INSERT INTO daily_stats (stat_date, emp_count, avg_salary)
SELECT CURDATE(), COUNT(*), AVG(salary) FROM employees;
— 3. 创建每周清理事件
CREATE EVENT weekly_cleanup
ON SCHEDULE EVERY 1 WEEK
STARTS CURRENT_TIMESTAMP
DO
DELETE FROM temp_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
-- 4. 创建一次性事件
CREATE EVENT one_time_sync
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
CALL sync_employee_data();
-- 5. 查看事件
SHOW EVENTS;
-- 6. 禁用事件
ALTER EVENT daily_employee_stats DISABLE;
-- 7. 删除事件
DROP EVENT daily_employee_stats;
5.4 JSON数据处理（MySQL 5.7+）
-- 1. 创建包含JSON字段的表
CREATE TABLE employee_details (
employee_id INT PRIMARY KEY,
basic_info JSON,
skills JSON,
performance JSON
);
-- 2. 插入JSON数据
INSERT INTO employee_details VALUES
(1,
'{"name": "张伟", "age": 30, "gender": "男", "email": "zhangwei@company.com"}',
'["Java", "SQL", "Python"]',
'{"2023": {"score": 95, "level": "A"}, "2022": {"score": 88, "level": "B"}}'
);
-- 3. 查询JSON字段
SELECT
employee_id,
JSON_EXTRACT(basic_info, '$.name') AS name,
JSON_EXTRACT(basic_info, '$.age') AS age,
JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.gender')) AS gender
FROM employee_details;
-- 4. 使用->和->>运算符（MySQL 5.7+）
SELECT
employee_id,
basic_info->’$.name’ AS name,
basic_info->>’$.email’ AS email — 自动去引号
FROM employee_details;
— 5. 查询JSON数组
SELECT
employee_id,
JSON_LENGTH(skills) AS skill_count,
JSON_CONTAINS(skills, ‘”Java”‘) AS has_java,
JSON_SEARCH(skills, ‘one’, ‘%SQL%’) AS sql_position
FROM employee_details;
— 6. 更新JSON字段
UPDATE employee_details
SET skills = JSON_ARRAY_APPEND(skills, ‘$’, ‘Go’)
WHERE employee_id = 1;
— 7. 修改JSON对象
UPDATE employee_details
SET basic_info = JSON_SET(basic_info, ‘$.age’, 31, ‘$.phone’, ‘13800138000’)
WHERE employee_id = 1;
— 8. 删除JSON字段
UPDATE employee_details
SET basic_info = JSON_REMOVE(basic_info, ‘$.age’)
WHERE employee_id = 1;
— 9. JSON聚合
SELECT
department_id,
JSON_ARRAYAGG(employee_id) AS employee_ids,
JSON_OBJECTAGG(first_name, salary) AS name_salary_map
FROM employees
GROUP BY department_id;
— 10. 实际应用：存储动态属性
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
attributes JSON
);
INSERT INTO products VALUES
(1, ‘笔记本电脑’, ‘{“color”: “银色”, “ram”: “16GB”, “storage”: “512GB”, “cpu”: “i7”}’),
(2, ‘手机’, ‘{“color”: “黑色”, “ram”: “8GB”, “storage”: “256GB”, “screen”: “6.5寸”}’);
— 查询特定属性的产品
SELECT * FROM products
WHERE attributes->>’$.color’ = ‘银色’
AND attributes->>’$.ram’ = ’16GB’;
5.5 最佳实践总结
5.5.1 命名规范
— 表名：小写+下划线，复数形式
CREATE TABLE employee_salaries (
id INT PRIMARY KEY,
employee_id INT,
effective_date DATE,
salary_amount DECIMAL(10,2)
);
— 列名：小写+下划线，不使用保留字
CREATE TABLE orders (
order_id INT,
order_date DATE,
customer_id INT,
total_amount DECIMAL(10,2)
);
— 索引名：idx_表名_列名
CREATE INDEX idx_employee_salaries_employee_id ON employee_salaries(employee_id);
5.5.2 代码规范
— 1. 使用标准格式
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 8000
AND e.department_id IN (1, 2, 3)
ORDER BY e.salary DESC;
— 2. 避免使用SELECT *
— 3. 使用表别名
— 4. 使用明确的JOIN语法
— 5. 使用事务保证数据一致性
— 6. 添加注释
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘员工ID’,
first_name VARCHAR(50) NOT NULL COMMENT ‘名’,
last_name VARCHAR(50) NOT NULL COMMENT ‘姓’,
salary DECIMAL(10,2) COMMENT ‘薪资’
) COMMENT=’员工信息表’;
5.5.3 性能优化清单
[ ] 所有查询都使用EXPLAIN分析
[ ] WHERE条件字段都有索引
[ ] 避免SELECT *
[ ] 避免在WHERE子句中对字段进行函数操作
[ ] 使用JOIN替代子查询
[ ] 使用EXISTS替代IN
[ ] 避免大偏移量分页
[ ] 事务尽可能短
[ ] 定期分析表和优化表
[ ] 监控慢查询日志
5.5.4 安全最佳实践
— 1. 最小权限原则
GRANT SELECT, INSERT ON db.table TO ‘user’@’host’;
— 2. 使用预处理语句防止SQL注入
— 不推荐（易受注入）
SELECT * FROM users WHERE username = ‘$username’ AND password = ‘$password’;
— 推荐（使用参数化查询）
SELECT * FROM users WHERE username = ? AND password = ?;
— 3. 审计敏感操作
CREATE TABLE security_log (
id INT PRIMARY KEY AUTO_INCREMENT,
user VARCHAR(50),
action VARCHAR(100),
table_name VARCHAR(50),
timestamp DATETIME,
details TEXT
);
— 4. 定期备份
— 全量备份：每周
— 增量备份：每天
— 测试恢复流程
— 5. 数据加密
ALTER TABLE employees MODIFY ssn VARBINARY(100);
UPDATE employees SET ssn = AES_ENCRYPT(ssn, ‘secret_key’);
结语
SQL作为数据处理的核心工具，其学习是一个持续深入的过程。从基础的增删改查到复杂的窗口函数、递归查询，再到性能优化和安全实践，每一步都需要理论与实践相结合。
关键要点回顾：
基础要扎实
：熟练掌握SELECT、JOIN、聚合函数等基础语法
理解执行原理
：学会分析执行计划，理解索引工作原理
优化思维
：养成写查询前先思考性能的习惯
安全意识
：始终考虑SQL注入和数据安全
持续学习
：关注新版本特性（如CTE、窗口函数、JSON支持）
推荐学习路径：
先在测试环境练习本文所有示例
在实际工作中应用所学技巧
定期回顾和优化已有查询
阅读数据库官方文档和性能调优指南
参与开源项目或技术社区讨论
记住，优秀的SQL开发者不仅写出能运行的代码，更要写出高效、安全、易维护的代码。希望本文能帮助你在SQL学习之路上更进一步！