SQL 语法完全指南：从零到精通（附大量实战案例）

无论你是数据分析师、后端开发还是运维工程师，SQL 都是你离不开的核心技能。本文将从最基础的增删改查开始，带你一步步掌握 SQL 的完整语法体系，每个知识点都配有可直接运行的案例。建议收藏，随时查阅。

---

目录

1. SQL 语言分类与基础概念
2. 数据定义语言（DDL）—— 库、表的创建与管理
3. 数据操作语言（DML）—— 增、删、改
4. 数据查询语言（DQL）—— 查（重点）
5. 数据控制语言（DCL）—— 权限管理
6. 事务控制语言（TCL）—— 提交与回滚
7. 高级查询技巧：连接、子查询、窗口函数
8. 索引与性能优化基础

---

一、SQL 语言分类与基础概念

SQL（Structured Query Language）按照功能分为五大类：

分类	英文缩写	作用	常见命令
数据定义语言	DDL	定义数据库结构（库、表、视图等）	CREATE, ALTER, DROP, TRUNCATE, RENAME
数据操作语言	DML	操作表中数据	INSERT, UPDATE, DELETE, REPLACE
数据查询语言	DQL	查询表中数据	SELECT
数据控制语言	DCL	权限与安全控制	GRANT, REVOKE, CREATE USER, DROP USER
事务控制语言	TCL	事务管理	COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

准备工作：本文所有案例基于如下几张示例表：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS demo_db;
USE demo_db;

-- 部门表
CREATE TABLE dept (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 员工表
CREATE TABLE emp (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M','F')),
    salary DECIMAL(10,2),
    hire_date DATE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

-- 插入基础数据
INSERT INTO dept VALUES (10, '技术部'), (20, '销售部'), (30, '人事部');
INSERT INTO emp (emp_name, gender, salary, hire_date, dept_id) VALUES
('张三', 'M', 8000, '2020-01-15', 10),
('李四', 'F', 9500, '2019-07-20', 10),
('王五', 'M', 6000, '2021-03-10', 20),
('赵六', 'F', 7200, '2018-11-05', 20),
('孙七', 'M', 5000, '2022-06-01', 30);

---

二、数据定义语言（DDL）—— 库、表的创建与管理

2.1 数据库操作

-- 创建数据库（指定字符集）
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

-- 修改数据库选项
ALTER DATABASE mydb CHARACTER SET utf8;

-- 删除数据库（慎用！）
DROP DATABASE mydb;

2.2 表操作

2.2.1 创建表（CREATE TABLE）

-- 基本创建
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,          -- 主键自增
    name VARCHAR(30) NOT NULL,
    age INT DEFAULT 18,                         -- 默认值
    email VARCHAR(100) UNIQUE,                  -- 唯一约束
    class_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 复合主键
CREATE TABLE course_selection (
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

-- 创建表时添加外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2.2.2 修改表结构（ALTER TABLE）

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

-- 修改列的数据类型或属性
ALTER TABLE student MODIFY phone VARCHAR(15);
ALTER TABLE student CHANGE phone mobile VARCHAR(20);  -- 同时改名

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

-- 添加索引
ALTER TABLE student ADD INDEX idx_name (name);
ALTER TABLE student ADD UNIQUE INDEX idx_email (email);
ALTER TABLE student ADD FULLTEXT idx_content (content);  -- 全文索引

-- 添加主键（前提是列非空且无重复）
ALTER TABLE student ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE student ADD CONSTRAINT fk_class 
FOREIGN KEY (class_id) REFERENCES class(id);

-- 删除索引
ALTER TABLE student DROP INDEX idx_name;

-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;

2.2.3 删除与清空

-- 删除表（结构和数据全部消失）
DROP TABLE student;

-- 清空表数据，但保留结构（自增列重置）
TRUNCATE TABLE student;

-- 重命名表
RENAME TABLE student to new_student;
-- 或
ALTER TABLE new_student RENAME TO student;

---

三、数据操作语言（DML）—— 增、删、改

3.1 插入数据（INSERT）

-- 插入完整一行（所有列按顺序）
INSERT INTO emp VALUES (6, '周八', 'M', 5500, '2023-01-01', 30);

-- 指定列插入（推荐，避免顺序错误）
INSERT INTO emp (emp_name, gender, salary, dept_id) 
VALUES ('吴九', 'F', 6800, 20);

-- 批量插入
INSERT INTO emp (emp_name, gender, salary, dept_id) VALUES
('郑十', 'M', 7200, 10),
('王十一', 'F', 6000, 20);

-- 插入查询结果（表复制）
INSERT INTO emp_backup (emp_id, emp_name, salary) 
SELECT emp_id, emp_name, salary FROM emp WHERE dept_id = 10;

-- 若主键冲突，则更新（ON DUPLICATE KEY UPDATE）
INSERT INTO emp (emp_id, emp_name, salary) VALUES (1, '张三', 8500)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

-- REPLACE 与 INSERT 类似，但若主键存在则先删除再插入
REPLACE INTO emp (emp_id, emp_name, salary) VALUES (1, '张三新', 9000);

3.2 更新数据（UPDATE）

-- 无条件更新（危险！）
UPDATE emp SET salary = salary * 1.1;  -- 全员涨薪10%

-- 带条件更新
UPDATE emp SET salary = 10000 WHERE emp_name = '张三';

-- 多列更新
UPDATE emp SET salary = 12000, gender = 'F' WHERE emp_id = 1;

-- 关联更新（根据另一张表的值更新）
UPDATE emp e JOIN dept d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.2
WHERE d.dept_name = '技术部';

-- 子查询更新
UPDATE emp 
SET salary = (SELECT AVG(salary) FROM emp WHERE dept_id = 10)
WHERE dept_id = 20;

3.3 删除数据（DELETE）

-- 删除所有数据（可回滚，慢）
DELETE FROM emp;

-- 带条件删除
DELETE FROM emp WHERE emp_id = 10;

-- 删除前100行
DELETE FROM emp LIMIT 100;

-- 关联删除（删除没有部门的员工）
DELETE e FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- 清空表（不可回滚，速度快，重置自增）
TRUNCATE TABLE emp;

TRUNCATE vs DELETE：TRUNCATE 是 DDL，无法触发 DELETE 触发器，不记录每行删除日志，速度极快，但无法通过事务回滚（部分数据库可回滚，MySQL 中 TRUNCATE 隐式提交）。

---

四、数据查询语言（DQL）—— 查（重点）

4.1 基础查询（SELECT）

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

-- 查询指定列
SELECT emp_name, salary FROM emp;

-- 去重查询
SELECT DISTINCT dept_id FROM emp;

-- 列运算（别名）
SELECT emp_name, salary * 12 AS annual_income FROM emp;

-- 常量列
SELECT emp_name, '在职' AS status, salary FROM emp;

-- 条件过滤（WHERE）
SELECT * FROM emp WHERE salary > 7000 AND dept_id = 10;
SELECT * FROM emp WHERE gender = 'F' OR salary >= 8000;

-- 范围查询
SELECT * FROM emp WHERE salary BETWEEN 6000 AND 8000;
SELECT * FROM emp WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31';

-- 集合查询
SELECT * FROM emp WHERE dept_id IN (10, 20);
SELECT * FROM emp WHERE dept_id NOT IN (30);

-- 模糊查询（LIKE）
-- % 代表任意多个字符，_ 代表单个字符
SELECT * FROM emp WHERE emp_name LIKE '张%';   -- 姓张
SELECT * FROM emp WHERE emp_name LIKE '_三';   -- 名字第二个字是“三”且只有两个字

-- 空值判断
SELECT * FROM emp WHERE salary IS NULL;
SELECT * FROM emp WHERE salary IS NOT NULL;

-- 排序
SELECT emp_name, salary FROM emp ORDER BY salary DESC;   -- 降序
SELECT * FROM emp ORDER BY dept_id ASC, salary DESC;     -- 多列排序

-- 分页查询（LIMIT + OFFSET）
SELECT * FROM emp ORDER BY emp_id LIMIT 5 OFFSET 0;   -- 第1~5条
SELECT * FROM emp ORDER BY emp_id LIMIT 5 OFFSET 5;   -- 第6~10条
-- 简写：LIMIT 5,5  （第一个数字是偏移量，第二个是行数）

4.2 聚合函数与分组（GROUP BY）

-- 常用聚合函数：COUNT, SUM, AVG, MAX, MIN
SELECT 
    COUNT(*) AS total_emp,          -- 总行数
    AVG(salary) AS avg_sal,         -- 平均薪资
    MAX(salary) AS max_sal,
    MIN(salary) AS min_sal,
    SUM(salary) AS total_sal
FROM emp;

-- 分组统计
SELECT 
    dept_id, 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM emp
GROUP BY dept_id;

-- 分组后过滤（HAVING，不能使用 WHERE 因为是对聚合结果过滤）
SELECT 
    dept_id, 
    AVG(salary) AS avg_salary
FROM emp
GROUP BY dept_id
HAVING avg_salary > 6500;

-- WHERE 和 HAVING 同时出现（WHERE 先过滤行，再分组，再 HAVING）
SELECT 
    dept_id, 
    COUNT(*) AS cnt
FROM emp
WHERE salary > 5000              -- 分组前过滤
GROUP BY dept_id
HAVING cnt > 2;                  -- 分组后过滤

4.3 高级查询（连接、子查询、联合）

此部分内容将在第七章详细展开。

---

五、数据控制语言（DCL）—— 权限管理

5.1 用户管理

-- 创建用户（仅本地访问）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123';

-- 创建用户（允许任何IP访问，生产环境慎重）
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';

-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewPass456';

-- 删除用户
DROP USER 'app_user'@'localhost';

5.2 权限赋予与回收

-- 授予指定库的所有权限
GRANT ALL PRIVILEGES ON demo_db.* TO 'app_user'@'localhost';

-- 授予特定表的查询、插入权限
GRANT SELECT, INSERT ON demo_db.emp TO 'app_user'@'localhost';

-- 授予全局权限（慎用）
GRANT SUPER, PROCESS ON *.* TO 'admin'@'%';

-- 允许将权限转授给他人
GRANT SELECT ON demo_db.* TO 'user1'@'%' WITH GRANT OPTION;

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 回收权限
REVOKE INSERT ON demo_db.emp FROM 'app_user'@'localhost';

-- 回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'localhost';

-- 刷新权限（部分操作自动生效，但习惯性执行）
FLUSH PRIVILEGES;

---

六、事务控制语言（TCL）—— 提交与回滚

事务保证一组 SQL 要么全部成功，要么全部失败。ACID 特性：原子性、一致性、隔离性、持久性。

-- 开始事务（两种写法）
START TRANSACTION;
-- 或
BEGIN;

-- 执行多个操作
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

-- 设置保存点（用于部分回滚）
SAVEPOINT before_transfer;

-- 假设发现错误，回滚到保存点
ROLLBACK TO SAVEPOINT before_transfer;

-- 如果没有错误，提交事务
COMMIT;

-- 任何时刻可以回滚全部（未提交前）
ROLLBACK;

-- 设置自动提交（默认开启，每条SQL立即提交）
SET autocommit = 0;   -- 关闭自动提交
SET autocommit = 1;   -- 开启

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

级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ (MySQL默认)	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置全局隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 设置会话隔离级别
SET SESSION transaction_isolation = 'REPEATABLE-READ';

---

七、高级查询技巧：连接、子查询、窗口函数

7.1 表连接（JOIN）

7.1.1 INNER JOIN（内连接）

返回两表匹配的行。

-- 查询员工及其部门名称
SELECT e.emp_name, e.salary, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.dept_id;

-- 多表连接
SELECT e.emp_name, d.dept_name, p.project_name
FROM emp e
JOIN dept d ON e.dept_id = d.dept_id
JOIN project p ON e.emp_id = p.lead_emp_id;

7.1.2 LEFT / RIGHT JOIN（外连接）

保留左表全部行，右表无匹配则填 NULL。

-- 列出所有员工及其部门（即使有些员工部门不存在，结果为NULL）
SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id;

-- RIGHT JOIN 效果等同于 LEFT JOIN 换顺序

7.1.3 FULL OUTER JOIN（MySQL 不支持，可用 UNION 模拟）

SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.dept_id;

7.1.4 CROSS JOIN（笛卡尔积，慎用）

SELECT * FROM emp CROSS JOIN dept;   -- 行数 = emp行数 × dept行数

7.2 子查询

子查询可以出现在 SELECT、FROM、WHERE、HAVING 子句中。

-- WHERE 子查询（标量）
SELECT emp_name, salary 
FROM emp 
WHERE salary > (SELECT AVG(salary) FROM emp);

-- 列子查询（IN / EXISTS）
SELECT emp_name FROM emp 
WHERE dept_id IN (SELECT dept_id FROM dept WHERE dept_name LIKE '技术%');

-- EXISTS 相关子查询（效率通常高于 IN 当子表很大时）
SELECT d.dept_name 
FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);

-- FROM 子句中的子查询（派生表）
SELECT dept_id, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id) AS t
WHERE avg_sal > 7000;

-- SELECT 子句中的子查询
SELECT emp_name, salary, 
    (SELECT AVG(salary) FROM emp) AS company_avg
FROM emp;

7.3 窗口函数（Window Functions，MySQL 8.0+）

窗口函数不改变行数，而是在每一行上基于一个“窗口”计算值。

-- 基本语法：函数 OVER (PARTITION BY ... ORDER BY ...)

-- 排名函数
SELECT 
    emp_name, 
    dept_id, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
    RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank
FROM emp;

-- 聚合窗口函数（移动平均）
SELECT 
    emp_name,
    salary,
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM emp;

-- 累计求和
SELECT 
    emp_name, 
    salary,
    SUM(salary) OVER (ORDER BY emp_id) AS cumulative_salary
FROM emp;

-- LAG / LEAD （前后偏移）
SELECT 
    emp_name,
    salary,
    LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY emp_id) AS next_salary
FROM emp;

7.4 联合查询（UNION / UNION ALL）

-- 合并两个查询结果（自动去重，效率低）
SELECT emp_name, salary FROM emp WHERE dept_id = 10
UNION
SELECT emp_name, salary FROM emp WHERE salary > 7000;

-- UNION ALL 不去重，速度快
SELECT emp_name FROM emp WHERE gender = 'M'
UNION ALL
SELECT emp_name FROM emp WHERE dept_id = 20;

---

八、索引与性能优化基础

8.1 索引的创建与删除

-- 创建普通索引
CREATE INDEX idx_salary ON emp(salary);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON user(email);

-- 创建复合索引（注意顺序）
CREATE INDEX idx_dept_salary ON emp(dept_id, salary);

-- 删除索引
DROP INDEX idx_salary ON emp;

-- 查看表索引
SHOW INDEX FROM emp;

8.2 索引使用原则

• 最左前缀原则：复合索引 (a, b, c) 能有效用于 a、a,b、a,b,c 的查询条件，但无法用于仅 b 或 c 的查询。
• 避免在索引列上使用函数或计算：WHERE YEAR(hire_date) = 2020 不会走索引，应改为 WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31'。
• 覆盖索引：若查询的所有列都在索引中，则无需回表，性能极高。

8.3 执行计划分析

EXPLAIN SELECT * FROM emp WHERE salary > 7000;

重点关注字段：

列	意义
type	访问类型：ALL（全表扫描）< index < range < ref < eq_ref < const（最好）
possible_keys	可能使用的索引
key	实际使用的索引
rows	预估扫描行数
Extra	Using index（覆盖索引）、Using filesort（需要优化）等

---

总结

本文完整覆盖了 SQL 语法的六大板块：DDL、DML、DQL、DCL、TCL 以及高级查询与优化。从最简单的 SELECT 到复杂的窗口函数和事务控制，每个知识点都配合了可以直接运行的案例。

学习建议：

1. 安装 MySQL 8.0 或使用在线 SQL 练习环境（如 SQL Fiddle、LeetCode）。
2. 按照本文的案例顺序，手动敲一遍所有命令。
3. 尝试在真实业务场景（如订单系统、博客系统）中编写 SQL。

SQL 是“手熟尔”的技术，多写多查，你会发现自己越来越得心应手。如果觉得本文有帮助，欢迎收藏与分享。

后续还可以继续深入学习存储过程、触发器、视图等内容，欲知更多，且听下回分解。