SQL 数据库入门教程

🎯 本教程面向零基础读者，从数据库基本概念出发，逐步讲解 SQL 核心语法，最终通过一个电商数据库实战项目串联所有知识点。

1. 数据库基础概念

1.1 什么是数据库？

数据库（Database）是一个有组织地存储和管理数据的系统。你可以把它想象成一个电子化的档案柜——每个抽屉里放着不同类别的文件，文件之间还有交叉引用的标签。

1.2 关系型数据库

本教程聚焦于关系型数据库（RDBMS），其核心思想是：数据以表（Table） 的形式存储，表与表之间通过关系（Relation） 关联。

常见的关系型数据库包括：

数据库	特点
MySQL	开源、社区活跃、Web 开发首选
PostgreSQL	功能强大、支持复杂数据类型
SQLite	轻量级、无需服务器、适合嵌入式
SQL Server	微软产品、企业级应用
Oracle	大型企业级、功能最全面

1.3 核心术语

数据库（Database）：多个表的集合
表（Table）：数据的结构化存储，由行和列组成
行（Row）/ 记录（Record）：表中的一条数据
列（Column）/ 字段（Field）：数据的一个属性
主键（Primary Key）：唯一标识一行数据的列（如学号、ID）
外键（Foreign Key）：指向另一张表主键的列，用于建立表间关系

1.4 数据模型示例

以一个简单的"学生选课"场景为例：

学生表 (students)
┌────┬──────┬────┬──────┐
│ id │ name │ age│ class│
├────┼──────┼────┼──────┤
│  1 │ 张三 │ 20 │ A班  │
│  2 │ 李四 │ 21 │ B班  │
│  3 │ 王五 │ 20 │ A班  │
└────┴──────┴────┴──────┘

课程表 (courses)
┌────┬──────────┬───────┐
│ id │   name   │ credit│
├────┼──────────┼───────┤
│  1 │ 数据库   │   3   │
│  2 │ 操作系统 │   4   │
│  3 │ 计算机网络│  3   │
└────┴──────────┴───────┘

选课表 (enrollments)
┌────┬──────────┬──────────┬───────┐
│ id │student_id│course_id │ score │
├────┼──────────┼──────────┼───────┤
│  1 │    1     │    1     │  92   │
│  2 │    1     │    2     │  85   │
│  3 │    2     │    1     │  78   │
│  4 │    3     │    3     │  90   │
└────┴──────────┴──────────┴───────┘

选课表中的 student_id 和 course_id 就是外键，分别指向学生表和课程表的主键。

2. 环境准备

2.1 安装 MySQL（推荐新手）

macOS（使用 Homebrew）：

brew install mysql
brew services start mysql

Ubuntu/Debian：

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql

Windows： 前往 MySQL 官网下载安装包，按向导完成安装。

2.2 使用 SQLite（零配置方案）

如果你不想安装完整数据库，SQLite 是最轻量的选择：

# macOS 自带 SQLite
sqlite3 mydb.db

# Ubuntu
sudo apt install sqlite3
sqlite3 mydb.db

2.3 图形化工具

DBeaver（免费，跨平台，支持多种数据库）
Navicat（付费，功能强大）
phpMyAdmin（Web 端，适合 MySQL）

3. SQL 语法基础

3.1 什么是 SQL？

SQL（Structured Query Language，结构化查询语言）是与关系型数据库交互的标准语言。它不是编程语言（没有循环、变量等概念），而是声明式语言——你告诉数据库"要什么"，而不是"怎么做"。

3.2 SQL 语句分类

分类	全称	用途	关键字示例
DDL	Data Definition Language	定义数据结构	`CREATE`, `ALTER`, `DROP`
DML	Data Manipulation Language	操作数据	`INSERT`, `UPDATE`, `DELETE`
DQL	Data Query Language	查询数据	`SELECT`
DCL	Data Control Language	权限控制	`GRANT`, `REVOKE`
TCL	Transaction Control Language	事务控制	`COMMIT`, `ROLLBACK`

3.3 基本书写规范

SQL 语句以分号 ; 结尾
关键字不区分大小写（SELECT 和 select 等效），但建议大写以提高可读性
表名和列名区分大小写取决于数据库配置（MySQL 默认不区分，PostgreSQL 默认区分）
字符串使用单引号 '...' 包裹

4. 创建与管理数据库/表

4.1 创建数据库

-- 创建数据库
CREATE DATABASE school;

-- 使用数据库
USE school;

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

执行结果：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| school             |
| performance_schema |
+--------------------+

4.2 创建表

-- 创建学生表
CREATE TABLE students (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(50) NOT NULL,
    age       INT CHECK (age > 0 AND age < 150),
    class     VARCHAR(20) DEFAULT '未分班',
    email     VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

常用数据类型：

类型	说明	示例
`INT`	整数	年龄、数量
`BIGINT`	大整数	大数据量的 ID
`DECIMAL(M,D)`	精确小数	金额（`DECIMAL(10,2)`）
`VARCHAR(N)`	可变长度字符串	姓名、邮箱
`TEXT`	长文本	文章内容
`DATE`	日期	出生日期
`TIMESTAMP`	时间戳	创建时间
`BOOLEAN`	布尔值	是否启用

约束（Constraint）说明：

PRIMARY KEY：主键，唯一且非空
AUTO_INCREMENT：自动递增（MySQL 特有）
NOT NULL：不允许为空
UNIQUE：值必须唯一
CHECK：值必须满足条件
DEFAULT：默认值
FOREIGN KEY：外键约束

4.3 修改表结构

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

-- 修改列类型
ALTER TABLE students MODIFY phone VARCHAR(30);

-- 删除列
ALTER TABLE students DROP COLUMN phone;

-- 重命名表
ALTER TABLE students RENAME TO learners;

-- 删除表（慎用！）
DROP TABLE IF EXISTS learners;

4.4 查看表结构

DESCRIBE students;
-- 或
SHOW COLUMNS FROM students;

执行结果：

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int          | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50)  | NO   |     | NULL              |                |
| age        | int          | YES  |     | NULL              |                |
| class      | varchar(20)  | YES  |     | 未分班             |                |
| email      | varchar(100) | YES  | UNI | NULL              |                |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

5. CRUD 操作

CRUD 是数据库最基础的四种操作：Create（创建）、Read（读取）、Update（更新）、Delete（删除）。

5.1 Create —— 插入数据

-- 插入单条记录
INSERT INTO students (name, age, class, email)
VALUES ('张三', 20, 'A班', 'zhangsan@example.com');

-- 插入多条记录
INSERT INTO students (name, age, class, email) VALUES
('李四', 21, 'B班', 'lisi@example.com'),
('王五', 20, 'A班', 'wangwu@example.com'),
('赵六', 22, 'B班', 'zhaoliu@example.com'),
('钱七', 19, 'A班', 'qianqi@example.com');

-- 从查询结果插入（复制数据）
INSERT INTO students_backup (name, age)
SELECT name, age FROM students WHERE class = 'A班';

验证插入结果：

SELECT * FROM students;

+----+------+-----+------+----------------------+---------------------+
| id | name | age | class| email                | created_at          |
+----+------+-----+------+----------------------+---------------------+
|  1 | 张三 |  20 | A班  | zhangsan@example.com | 2026-05-28 08:00:00 |
|  2 | 李四 |  21 | B班  | lisi@example.com     | 2026-05-28 08:00:01 |
|  3 | 王五 |  20 | A班  | wangwu@example.com   | 2026-05-28 08:00:02 |
|  4 | 赵六 |  22 | B班  | zhaoliu@example.com  | 2026-05-28 08:00:03 |
|  5 | 钱七 |  19 | A班  | qianqi@example.com   | 2026-05-28 08:00:04 |
+----+------+-----+------+----------------------+---------------------+

5.2 Read —— 查询数据

查询是 SQL 中最复杂也最常用的操作。

基础查询

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

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

-- 去重查询
SELECT DISTINCT class FROM students;

+------+
| class|
+------+
| A班  |
| B班  |
+------+

WHERE 条件过滤

-- 比较运算
SELECT * FROM students WHERE age > 20;

-- 多条件（AND / OR）
SELECT * FROM students WHERE age >= 20 AND class = 'A班';

-- IN 操作符
SELECT * FROM students WHERE class IN ('A班', 'B班');

-- BETWEEN 范围查询
SELECT * FROM students WHERE age BETWEEN 19 AND 21;

-- LIKE 模糊查询（% 匹配任意字符，_ 匹配单个字符）
SELECT * FROM students WHERE name LIKE '张%';  -- 以"张"开头

-- IS NULL / IS NOT NULL
SELECT * FROM students WHERE email IS NOT NULL;

示例查询及结果：

SELECT name, age, class FROM students WHERE age >= 20 AND class = 'A班';

+------+-----+------+
| name | age | class|
+------+-----+------+
| 张三 |  20 | A班  |
| 王五 |  20 | A班  |
+------+-----+------+

排序与分页

-- 按年龄升序排列（ASC 可省略）
SELECT * FROM students ORDER BY age ASC;

-- 按年龄降序排列
SELECT * FROM students ORDER BY age DESC;

-- 多列排序：先按班级升序，再按年龄降序
SELECT * FROM students ORDER BY class ASC, age DESC;

-- 分页：跳过前 2 条，取 2 条（第 2 页，每页 2 条）
SELECT * FROM students ORDER BY id LIMIT 2 OFFSET 2;

+----+------+-----+------+
| id | name | age | class|
+----+------+-----+------+
|  3 | 王五 |  20 | A班  |
|  4 | 赵六 |  22 | B班  |
+----+------+-----+------+

GROUP BY 分组

-- 按班级分组，统计每班人数
SELECT class, COUNT(*) AS student_count
FROM students
GROUP BY class;

+------+--------------+
| class| student_count|
+------+--------------+
| A班  |      3       |
| B班  |      2       |
+------+--------------+

-- HAVING：对分组结果进行过滤（WHERE 在分组前过滤，HAVING 在分组后过滤）
SELECT class, AVG(age) AS avg_age
FROM students
GROUP BY class
HAVING AVG(age) > 20;

+------+---------+
| class| avg_age |
+------+---------+
| B班  | 21.5000 |
+------+---------+

5.3 Update —— 更新数据

-- 更新单条记录
UPDATE students SET age = 21 WHERE name = '张三';

-- 更新多列
UPDATE students SET class = 'C班', email = 'updated@example.com' WHERE id = 5;

-- 批量更新（危险！务必加 WHERE 条件）
UPDATE students SET age = age + 1 WHERE class = 'A班';

更新后验证：

SELECT * FROM students WHERE class = 'A班';

+----+------+-----+------+----------------------+-----+
| id | name | age | class| email                | ... |
+----+------+-----+------+----------------------+-----+
|  1 | 张三 |  21 | A班  | zhangsan@example.com | ... |
|  3 | 王五 |  21 | A班  | wangwu@example.com   | ... |
|  5 | 钱七 |  20 | A班  | updated@example.com  | ... |
+----+------+-----+------+----------------------+-----+

5.4 Delete —— 删除数据

-- 删除指定记录
DELETE FROM students WHERE name = '赵六';

-- 删除满足条件的所有记录
DELETE FROM students WHERE age < 20;

-- 清空表（保留表结构）
TRUNCATE TABLE students_backup;

⚠️ 警告：不带 WHERE 的 DELETE 会删除所有数据！操作前务必三思。

6. 聚合函数

聚合函数对一组值执行计算并返回单个值，通常与 GROUP BY 配合使用。

6.1 常用聚合函数

函数	说明	示例
`COUNT()`	计数	`COUNT(*)` 统计行数
`SUM()`	求和	`SUM(salary)` 工资总和
`AVG()`	平均值	`AVG(score)` 平均分
`MAX()`	最大值	`MAX(age)` 最大年龄
`MIN()`	最小值	`MIN(price)` 最低价格

6.2 综合示例

假设有一张成绩表 scores：

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score DECIMAL(5,2)
);

INSERT INTO scores (student_name, subject, score) VALUES
('张三', '数学', 92.5),
('张三', '英语', 85.0),
('张三', '物理', 88.5),
('李四', '数学', 78.0),
('李四', '英语', 90.0),
('李四', '物理', 82.0),
('王五', '数学', 95.0),
('王五', '英语', 88.0),
('王五', '物理', 91.5);

-- 统计每科的平均分、最高分、最低分
SELECT
    subject AS 科目,
    COUNT(*) AS 参考人数,
    ROUND(AVG(score), 2) AS 平均分,
    MAX(score) AS 最高分,
    MIN(score) AS 最低分,
    ROUND(SUM(score), 2) AS 总分
FROM scores
GROUP BY subject
ORDER BY 平均分 DESC;

+------+----------+--------+--------+--------+--------+
| 科目 | 参考人数 | 平均分 | 最高分 | 最低分 | 总分   |
+------+----------+--------+--------+--------+--------+
| 数学 |    3     | 88.50  | 95.00  | 78.00  | 265.50 |
| 物理 |    3     | 87.33  | 91.50  | 82.00  | 262.00 |
| 英语 |    3     | 87.67  | 90.00  | 85.00  | 263.00 |
+------+----------+--------+--------+--------+--------+

-- 查找平均分高于 85 分的学生
SELECT
    student_name AS 姓名,
    ROUND(AVG(score), 2) AS 平均分,
    COUNT(*) AS 科目数
FROM scores
GROUP BY student_name
HAVING AVG(score) > 85
ORDER BY 平均分 DESC;

+------+--------+--------+
| 姓名 | 平均分 | 科目数 |
+------+--------+--------+
| 王五 | 91.50  |   3    |
| 张三 | 88.67  |   3    |
+------+--------+--------+

7. JOIN 连接查询

JOIN 用于根据关联条件合并多张表的数据，是关系型数据库最强大的特性之一。

7.1 JOIN 类型

INNER JOIN     只返回两表都匹配的行
LEFT JOIN      返回左表所有行，右表无匹配则填 NULL
RIGHT JOIN     返回右表所有行，左表无匹配则填 NULL
CROSS JOIN     返回两表的笛卡尔积（所有组合）

7.2 示例数据

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO departments VALUES
(1, '技术部'),
(2, '市场部'),
(3, '财务部'),
(4, '人事部');  -- 暂无员工

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, '张三', 1, 15000),
(2, '李四', 1, 18000),
(3, '王五', 2, 12000),
(4, '赵六', 2, 13000),
(5, '钱七', 3, 16000),
(6, '孙八', NULL, 10000);  -- 未分配部门

7.3 INNER JOIN（内连接）

SELECT
    e.name AS 员工姓名,
    d.dept_name AS 部门名称,
    e.salary AS 薪资
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

+----------+----------+-----------+
| 员工姓名 | 部门名称 | 薪资      |
+----------+----------+-----------+
| 张三     | 技术部   | 15000.00  |
| 李四     | 技术部   | 18000.00  |
| 王五     | 市场部   | 12000.00  |
| 赵六     | 市场部   | 13000.00  |
| 钱七     | 财务部   | 16000.00  |
+----------+----------+-----------+

注意：孙八（未分配部门）和人事部（无员工）都不会出现在结果中。

7.4 LEFT JOIN（左连接）

SELECT
    e.name AS 员工姓名,
    d.dept_name AS 部门名称,
    e.salary AS 薪资
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

+----------+----------+-----------+
| 员工姓名 | 部门名称 | 薪资      |
+----------+----------+-----------+
| 张三     | 技术部   | 15000.00  |
| 李四     | 技术部   | 18000.00  |
| 王五     | 市场部   | 12000.00  |
| 赵六     | 市场部   | 13000.00  |
| 钱七     | 财务部   | 16000.00  |
| 孙八     | NULL     | 10000.00  |
+----------+----------+-----------+

孙八出现了，但部门名称为 NULL。

7.5 RIGHT JOIN（右连接）

SELECT
    e.name AS 员工姓名,
    d.dept_name AS 部门名称
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

+----------+----------+
| 员工姓名 | 部门名称 |
+----------+----------+
| 张三     | 技术部   |
| 李四     | 技术部   |
| 王五     | 市场部   |
| 赵六     | 市场部   |
| 钱七     | 财务部   |
| NULL     | 人事部   |
+----------+----------+

人事部出现了，但员工姓名为 NULL。

7.6 多表 JOIN

-- 假设还有薪资等级表
CREATE TABLE salary_grades (
    grade INT,
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2)
);

INSERT INTO salary_grades VALUES
(1, 0, 12000),
(2, 12001, 16000),
(3, 16001, 999999);

SELECT
    e.name AS 姓名,
    e.salary AS 薪资,
    d.dept_name AS 部门,
    s.grade AS 薪资等级
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
LEFT JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;

+------+-----------+------+----------+
| 姓名 | 薪资      | 部门 | 薪资等级 |
+------+-----------+------+----------+
| 张三 | 15000.00  | 技术部 |    2    |
| 李四 | 18000.00  | 技术部 |    3    |
| 王五 | 12000.00  | 市场部 |    1    |
| 赵六 | 13000.00  | 市场部 |    2    |
| 钱七 | 16000.00  | 财务部 |    2    |
| 孙八 | 10000.00  | NULL  |    1    |
+------+-----------+------+----------+

8. 子查询

子查询是嵌套在其他 SQL 语句中的查询，可以出现在 WHERE、FROM、SELECT 子句中。

8.1 WHERE 子句中的子查询

-- 查找薪资高于平均薪资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

+------+-----------+
| name | salary    |
+------+-----------+
| 李四 | 18000.00  |
| 钱七 | 16000.00  |
+------+-----------+

8.2 IN 子查询

-- 查找技术部和市场部的所有员工
SELECT name, salary
FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE dept_name IN ('技术部', '市场部')
);

+------+-----------+
| name | salary    |
+------+-----------+
| 张三 | 15000.00  |
| 李四 | 18000.00  |
| 王五 | 12000.00  |
| 赵六 | 13000.00  |
+------+-----------+

8.3 EXISTS 子查询

-- 查找有员工的部门
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

+----------+
| dept_name|
+----------+
| 技术部   |
| 市场部   |
| 财务部   |
+----------+

8.4 FROM 子句中的子查询（派生表）

-- 查找每个部门中薪资最高的员工
SELECT d.dept_name, t.name, t.max_salary
FROM (
    SELECT dept_id, MAX(salary) AS max_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
) t
JOIN departments d ON t.dept_id = d.id
JOIN employees e ON e.dept_id = t.dept_id AND e.salary = t.max_salary;

+----------+------+------------+
| dept_name| name | max_salary |
+----------+------+------------+
| 技术部   | 李四 | 18000.00   |
| 市场部   | 赵六 | 13000.00   |
| 财务部   | 钱七 | 16000.00   |
+----------+------+------------+

8.5 标量子查询（SELECT 中的子查询）

-- 在结果中附加公司平均薪资作为参考
SELECT
    name,
    salary,
    (SELECT ROUND(AVG(salary), 2) FROM employees) AS 公司平均薪资,
    salary - (SELECT AVG(salary) FROM employees) AS 与平均值差距
FROM employees;

+------+-----------+--------------+--------------+
| name | salary    | 公司平均薪资  | 与平均值差距  |
+------+-----------+--------------+--------------+
| 张三 | 15000.00  | 14000.00     | 1000.00      |
| 李四 | 18000.00  | 14000.00     | 4000.00      |
| 王五 | 12000.00  | 14000.00     | -2000.00     |
| 赵六 | 13000.00  | 14000.00     | -1000.00     |
| 钱七 | 16000.00  | 14000.00     | 2000.00      |
| 孙八 | 10000.00  | 14000.00     | -4000.00     |
+------+-----------+--------------+--------------+

9. 索引

9.1 什么是索引？

索引就像一本书的目录——没有目录时，找某个主题需要翻遍全书；有了目录，直接翻到对应页码。

在数据库中，索引是一种额外的数据结构（通常是 B-Tree），它让数据库可以快速定位到目标行，而不需要全表扫描。

9.2 创建索引

-- 创建普通索引
CREATE INDEX idx_students_name ON students(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_students_email ON students(email);

-- 创建复合索引（多列索引）
CREATE INDEX idx_scores_student_subject ON scores(student_name, subject);

-- 查看表的索引
SHOW INDEX FROM students;

9.3 索引效果对比

-- 无索引时：全表扫描
EXPLAIN SELECT * FROM students WHERE name = '张三';

+----+------+------+------+---------+------+
| id | table| type | rows | filtered| Extra|
+----+------+------+------+---------+------+
|  1 | stude| ALL  | 1000 |  10.00  | NULL |  -- ALL 表示全表扫描
+----+------+------+------+---------+------+

-- 有索引时：索引查找
EXPLAIN SELECT * FROM students WHERE name = '张三';

+----+------+------+------+---------+------+
| id | table| type | rows | filtered| Extra|
+----+------+------+------+---------+------+
|  1 | stude| ref  |    1 | 100.00  | NULL |  -- ref 表示使用了索引
+----+------+------+------+---------+------+

9.4 索引使用原则

适合创建索引的场景：

WHERE 条件中频繁出现的列
JOIN 连接条件的列
ORDER BY 排序的列
高选择性的列（如邮箱、手机号，不同值越多越好）

不适合创建索引的场景：

频繁更新的列（索引也需要维护成本）
数据量很小的表
选择性很低的列（如性别，只有男/女两个值）

-- 删除索引
DROP INDEX idx_students_name ON students;

10. 事务

10.1 什么是事务？

事务是一组要么全部成功、要么全部失败的操作。最经典的例子是银行转账：A 向 B 转账 100 元，必须保证 A 扣款和 B 收款同时成功或同时失败。

10.2 ACID 特性

特性	说明	示例
Atomicity（原子性）	事务是不可分割的最小单位	转账的两步操作不可拆分
Consistency（一致性）	事务前后数据保持一致	转账前后总金额不变
Isolation（隔离性）	并发事务之间互不干扰	两个转账操作互不影响
Durability（持久性）	事务提交后数据永久保存	断电后数据不丢失

10.3 事务操作

-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2) CHECK (balance >= 0)
);

INSERT INTO accounts VALUES
(1, '张三', 5000.00),
(2, '李四', 3000.00);

-- 开启事务
START TRANSACTION;

-- 第一步：张三扣款 1000
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

-- 第二步：李四收款 1000
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 验证中间状态（事务内可见）
SELECT * FROM accounts;

+----+------+----------+
| id | name | balance  |
+----+------+----------+
|  1 | 张三 | 4000.00  |
|  2 | 李四 | 4000.00  |
+----+------+----------+

-- 确认无误，提交事务
COMMIT;

-- 如果出错，回滚事务（取消所有操作）
-- ROLLBACK;

10.4 事务中的异常处理

-- 模拟转账失败的场景
START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
-- 如果张三余额不足，这里会因为 CHECK 约束而失败

-- 检查是否成功
-- 如果失败，回滚
ROLLBACK;

-- 验证数据未被修改
SELECT * FROM accounts;

+----+------+----------+
| id | name | balance  |
+----+------+----------+
|  1 | 张三 | 5000.00  |  -- 余额不变
|  2 | 李四 | 3000.00  |
+----+------+----------+

10.5 SAVEPOINT（保存点）

START TRANSACTION;

INSERT INTO accounts VALUES (3, '王五', 2000.00);
SAVEPOINT sp1;

INSERT INTO accounts VALUES (4, '赵六', 1500.00);
SAVEPOINT sp2;

-- 回滚到 sp1（撤销赵六的插入，保留王五）
ROLLBACK TO sp1;

COMMIT;

11. 实战项目：电商数据库设计

下面我们设计一个简化版的电商数据库，综合运用前面学到的所有知识点。

11.1 需求分析

一个电商系统需要管理：

用户信息
商品分类与商品
订单与订单详情
商品评价

11.2 数据库设计（ER 图）

用户 (users) ──1:N──> 订单 (orders) ──1:N──> 订单详情 (order_items)
                                              │
                                              └──N:1──> 商品 (products) ──N:1──> 分类 (categories)

用户 (users) ──1:N──> 评价 (reviews) ──N:1──> 商品 (products)

11.3 建表语句

-- 创建数据库
CREATE DATABASE ecommerce;
USE ecommerce;

-- ========================
-- 1. 用户表
-- ========================
CREATE TABLE users (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    phone       VARCHAR(20),
    address     TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ========================
-- 2. 商品分类表
-- ========================
CREATE TABLE categories (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50) NOT NULL,
    parent_id   INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- ========================
-- 3. 商品表
-- ========================
CREATE TABLE products (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock       INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id INT,
    status      ENUM('active', 'inactive', 'sold_out') DEFAULT 'active',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- ========================
-- 4. 订单表
-- ========================
CREATE TABLE orders (
    id              INT PRIMARY KEY AUTO_INCREMENT,
    user_id         INT NOT NULL,
    total_amount    DECIMAL(10,2) NOT NULL,
    status          ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ========================
-- 5. 订单详情表
-- ========================
CREATE TABLE order_items (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- ========================
-- 6. 商品评价表
-- ========================
CREATE TABLE reviews (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT NOT NULL,
    product_id  INT NOT NULL,
    rating      INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

11.4 插入测试数据

-- 插入用户
INSERT INTO users (username, email, password, phone, address) VALUES
('alice',   'alice@example.com',   'hashed_pw_1', '13800001111', '北京市朝阳区XX路1号'),
('bob',     'bob@example.com',     'hashed_pw_2', '13800002222', '上海市浦东新区YY路2号'),
('charlie', 'charlie@example.com', 'hashed_pw_3', '13800003333', '广州市天河区ZZ路3号');

-- 插入分类
INSERT INTO categories (name, parent_id) VALUES
('电子产品', NULL),
('手机', 1),
('笔记本电脑', 1),
('服装', NULL),
('男装', 4),
('女装', 4);

-- 插入商品
INSERT INTO products (name, description, price, stock, category_id) VALUES
('iPhone 16 Pro',       '苹果最新旗舰手机',         8999.00, 100, 2),
('MacBook Pro 14"',     'M4 芯片，16GB 内存',       14999.00, 50,  3),
('小米 15 Ultra',       '骁龙 8 至尊版，徕卡影像',   5999.00, 200, 2),
('ThinkPad X1 Carbon',  '商务轻薄本',               9999.00, 80,  3),
('纯棉T恤',             '舒适透气，多色可选',         99.00,  500, 5),
('连衣裙',              '夏季新款，碎花设计',         199.00, 300, 6);

-- 插入订单
INSERT INTO orders (user_id, total_amount, status, shipping_address) VALUES
(1, 8999.00, 'delivered', '北京市朝阳区XX路1号'),
(1, 198.00,  'paid',      '北京市朝阳区XX路1号'),
(2, 14999.00,'shipped',   '上海市浦东新区YY路2号'),
(3, 6098.00, 'pending',   '广州市天河区ZZ路3号');

-- 插入订单详情
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 8999.00),   -- 订单1：1台 iPhone
(2, 5, 2, 99.00),     -- 订单2：2件 T恤
(3, 2, 1, 14999.00),  -- 订单3：1台 MacBook
(4, 3, 1, 5999.00),   -- 订单4：1台 小米15
(4, 5, 1, 99.00);     -- 订单4：1件 T恤

-- 插入评价
INSERT INTO reviews (user_id, product_id, rating, comment) VALUES
(1, 1, 5, '非常好用，拍照效果惊艳！'),
(1, 5, 4, '质量不错，就是颜色偏暗'),
(2, 2, 5, '开发利器，续航给力'),
(3, 3, 4, '性价比很高，拍照进步明显');

11.5 实战查询

查询 1：查看所有商品及其分类

SELECT
    p.name AS 商品名称,
    c.name AS 分类,
    p.price AS 价格,
    p.stock AS 库存,
    p.status AS 状态
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.name, p.price DESC;

+-------------------+----------+-----------+------+--------+
| 商品名称          | 分类     | 价格      | 库存 | 状态   |
+-------------------+----------+-----------+------+--------+
| MacBook Pro 14"   | 笔记本电脑| 14999.00 |  50  | active |
| ThinkPad X1 Carbon| 笔记本电脑|  9999.00 |  80  | active |
| iPhone 16 Pro     | 手机     |  8999.00 | 100  | active |
| 小米 15 Ultra     | 手机     |  5999.00 | 200  | active |
| 连衣裙            | 女装     |   199.00 | 300  | active |
| 纯棉T恤           | 男装     |    99.00 | 500  | active |
+-------------------+----------+-----------+------+--------+

查询 2：用户订单汇总

SELECT
    u.username AS 用户名,
    COUNT(o.id) AS 订单数,
    SUM(o.total_amount) AS 总消费,
    ROUND(AVG(o.total_amount), 2) AS 平均订单金额
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY 总消费 DESC;

+----------+--------+-----------+----------------+
| 用户名   | 订单数 | 总消费    | 平均订单金额   |
+----------+--------+-----------+----------------+
| alice    |   2    | 9197.00   | 4598.50        |
| bob      |   1    | 14999.00  | 14999.00       |
| charlie  |   1    | 6098.00   | 6098.00        |
+----------+--------+-----------+----------------+

查询 3：热销商品排行（按销量）

SELECT
    p.name AS 商品名称,
    SUM(oi.quantity) AS 总销量,
    SUM(oi.quantity * oi.unit_price) AS 总销售额
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY 总销量 DESC;

+-------------------+--------+-----------+
| 商品名称          | 总销量 | 总销售额  |
+-------------------+--------+-----------+
| 纯棉T恤           |   3    | 297.00    |
| iPhone 16 Pro     |   1    | 8999.00   |
| MacBook Pro 14"   |   1    | 14999.00  |
| 小米 15 Ultra     |   1    | 5999.00   |
+-------------------+--------+-----------+

查询 4：商品评分统计

SELECT
    p.name AS 商品名称,
    COUNT(r.id) AS 评价数,
    ROUND(AVG(r.rating), 1) AS 平均评分,
    CONCAT(REPEAT('★', ROUND(AVG(r.rating))), REPEAT('☆', 5 - ROUND(AVG(r.rating)))) AS 星级
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(r.id) > 0
ORDER BY 平均评分 DESC;

+-------------------+--------+----------+-------+
| 商品名称          | 评价数 | 平均评分 | 星级  |
+-------------------+--------+----------+-------+
| MacBook Pro 14"   |   1    | 5.0      | ★★★★★ |
| iPhone 16 Pro     |   1    | 5.0      | ★★★★★ |
| 小米 15 Ultra     |   1    | 4.0      | ★★★★☆ |
| 纯棉T恤           |   1    | 4.0      | ★★★★☆ |
+-------------------+--------+----------+-------+

查询 5：子查询——从未下单的用户

SELECT username, email
FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

Empty set  -- 所有用户都有订单

查询 6：事务示例——模拟下单流程

START TRANSACTION;

-- 1. 创建订单
INSERT INTO orders (user_id, total_amount, status, shipping_address)
VALUES (1, 5999.00, 'pending', '北京市朝阳区XX路1号');

-- 获取新订单 ID
SET @new_order_id = LAST_INSERT_ID();

-- 2. 创建订单详情
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@new_order_id, 3, 1, 5999.00);

-- 3. 扣减库存
UPDATE products SET stock = stock - 1 WHERE id = 3 AND stock > 0;

-- 4. 检查库存是否扣减成功
SELECT ROW_COUNT();  -- 如果返回 1，说明扣减成功

-- 全部成功，提交事务
COMMIT;

查询 7：复合索引优化

-- 为高频查询创建复合索引
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
CREATE INDEX idx_products_category_status ON products(category_id, status);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 验证索引被使用
EXPLAIN SELECT * FROM order_items WHERE order_id = 1 AND product_id = 1;

12. 常见问题与最佳实践

12.1 常见错误

错误	原因	解决方案
`Unknown column`	列名拼写错误	`DESCRIBE table_name` 检查列名
`Column count doesn't match`	INSERT 列数与值数不匹配	检查列和值的数量
`Duplicate entry`	违反唯一约束	检查数据是否重复
`Cannot delete or update a parent row`	外键约束阻止操作	先删除子表数据，或使用 `ON DELETE CASCADE`
`Data truncated`	数据超出列定义长度	扩大列长度或缩短数据

12.2 性能优化建议

**避免 `SELECT ***：只查询需要的列，减少数据传输
合理使用索引：为高频查询条件创建索引，但不要过度索引
避免在 WHERE 中对列使用函数：如 WHERE YEAR(created_at) = 2026 无法使用索引，改为 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
小表驱动大表：在 JOIN 中，用小结果集驱动大结果集
分页优化：深分页用 WHERE id > last_id LIMIT n 替代 OFFSET

12.3 安全注意事项

SQL 注入防护：永远使用参数化查询，不要拼接 SQL 字符串
最小权限原则：应用连接数据库的账户只赋予必要的权限
敏感数据加密：密码使用哈希存储（如 bcrypt），不要明文保存
定期备份：使用 mysqldump 或类似工具定期备份数据库

12.4 学习路径建议

入门 ──> 基础查询（SELECT, WHERE, ORDER BY）
  │
  ├──> 进阶（JOIN, 子查询, 聚合函数）
  │
  ├──> 高级（索引优化, 事务, 存储过程, 视图）
  │
  └──> 实战（设计数据库, 性能调优, 数据迁移）

推荐练习平台：

LeetCode 数据库题（中文）
SQLZoo（交互式教程）
HackerRank SQL（挑战模式）

总结

本教程覆盖了 SQL 数据库的核心知识点：

知识点	核心内容
数据库概念	表、行、列、主键、外键
DDL	`CREATE`, `ALTER`, `DROP`
CRUD	`INSERT`, `SELECT`, `UPDATE`, `DELETE`
聚合函数	`COUNT`, `SUM`, `AVG`, `MAX`, `MIN` + `GROUP BY`
JOIN	`INNER`, `LEFT`, `RIGHT`, 多表连接
子查询	`WHERE` 子查询、`EXISTS`、派生表、标量子查询
索引	创建索引、复合索引、索引优化原则
事务	`START TRANSACTION`, `COMMIT`, `ROLLBACK`, ACID

掌握这些内容后，你就具备了使用 SQL 进行日常数据操作的能力。接下来，建议动手完成电商项目的扩展练习，比如添加购物车表、优惠券系统、库存预警等功能，在实践中加深理解。

💡 学习秘诀：SQL 是一门实践性很强的技能，光看不练等于没学。打开你的数据库，跟着教程一步步敲代码，遇到报错不要怕——错误信息是最好的老师。

SQL数据库入门教程

教程简介