MySQL 数据库与 SQL 基础：从存数据到存储过程

学数据库，可以先抓住两个问题：数据放在哪里，怎样把需要的数据取出来。比如学生的姓名和成绩，不能只存在 Java 程序的变量里；程序关闭后，我们还希望下次能继续查看和修改它们。

这篇先认识数据库，再按建库、建表、查询、表设计的顺序往下学，最后用小例子理解事务、视图、触发器和存储过程。SQL 示例使用 MySQL 8.0 / 8.4，存储引擎为 InnoDB。代码放在 SQL 客户端中执行，Java 程序怎样连接数据库是后续 JDBC 的内容。

1. 数据库、数据库管理系统和工具

数据库（Database，简称 DB）就是按一定结构组织起来的数据集合。 学生名单、订单和商品信息，都可以保存到数据库里，之后再进行增、删、改、查。

我们通常希望数据能长期保存，但不能把数据库理解成“只能把数据放到磁盘上的仓库”。不同数据库会采用不同的存储方式，内存也经常参与查询和存储。

1.1 关系型数据库

关系型数据库主要用表组织数据。表里有字段和记录，表与表之间还能建立关系。

例如，student 表保存学生，class 表保存班级。学生所属的班级，可以用一个班级编号联系起来。

常见产品有 MySQL、Oracle Database、SQL Server。MySQL 提供开源社区版，在 Java Web 项目中很常见。它适合很多业务场景，但查询快不快，仍然要看表设计、SQL、索引和数据量。

1.2 非关系型数据库

非关系型数据库通常统称为 NoSQL。它们不都按关系型表的方式组织数据，也不都只是缓存。

原来笔记中的 Redis 是一个典型例子：它常用来保存热点数据、短信验证码等。比如验证码只需要短时间有效，就可以给 Redis 中的验证码设置过期时间。

实际项目常见 MySQL + Redis：MySQL 保存订单等业务数据，Redis 保存经常访问的数据或短期状态。两者各做合适的事；加入缓存以后，还要考虑数据更新和缓存失效的问题。

1.3 DB、DBMS 和管理工具有什么区别

先安装并启动 MySQL Server，再使用客户端连接它。工具是操作入口，服务器才负责执行 SQL、管理数据。

学习数据库也分成两部分：一部分是使用，包括建库建表、增删改查，以及事务、索引、视图、触发器和存储过程；另一部分是设计，根据业务确定需要哪些表、字段和关联关系。

例如做选课系统，先想清楚学生、课程和选课记录怎样对应，再写 Java 代码，通常比边写代码边随意加字段更容易维护。数据库设计很重要，但也应随着实际需求逐步调整。

2. 存储引擎与 SQL 的分类

2.1 存储引擎是什么

存储引擎负责具体怎样存储表数据、维护索引，以及执行查询和更新。在 MySQL 中，可以为不同的表选择不同的存储引擎，因此也常把它理解为表的存储和操作方式。

先看看服务器支持哪些引擎：

SHOW ENGINES;

结果中的 Support 表示支持情况，DEFAULT 表示默认引擎。MySQL 8.0 / 8.4 默认使用 InnoDB。

InnoDB 支持事务、外键和自增列，并提供崩溃恢复、并发控制等能力。比如转账涉及扣钱和加钱，事务可以把两步组织成一个整体。它也需要维护日志、索引等结构，会占用资源；不能简单地说它“读写效率差”，实际效果要结合工作负载判断。

自增列通常设为主键，但 MySQL 并没有要求自增列必须是主键。对 InnoDB 来说，自增列需要是某个索引的第一列；自增本身也不等于唯一约束。入门时用“整数主键 + 自增”最容易理解，第 8 节会实际写一次。

2.2 事务和分布式事务

事务把一组操作放在一起管理，让它们一起提交，或者撤销未提交的修改。它不是只有单体应用才需要，通常先从一个数据库连接上的事务学起。

如果一个业务步骤跨越多个独立的数据库或服务，比如订单服务和库存服务各自保存数据，就可能需要分布式事务方案。部署了多台服务器，不代表每次操作都自动成为分布式事务。 这里先学单个 MySQL 实例中的事务，后面再用转账例子说明。

2.3 SQL 用来做什么

SQL 是与关系型数据库打交道的语言。工具里执行的是 SQL，Java 程序连接数据库后，也可以向服务器发送 SQL。

学习时常按用途分成下面几类：

这是一种便于学习的划分，有些资料把 SELECT 也归入 DML。需要分清的是：提交和回滚属于事务控制，授权和撤销授权属于权限控制。

3. 创建 MySQL 数据库：字符集与排序规则

安装 MySQL 并连接成功后，可以为项目创建一个数据库。运行中的 MySQL 服务器实例，可以管理多个这样的数据库；CREATE DATABASE 创建的是逻辑数据库，不是启动一个新服务器。

3.1 字符集决定能保存哪些字符

字符集规定字符怎样编码。学习时推荐使用 utf8mb4，它可以保存中文和 emoji 等 Unicode 字符。

MySQL 中旧的 utf8 名称通常指 utf8mb3，最多用三个字节编码一个字符，不能完整表示所有 Unicode 字符。因此，新建库时直接写 utf8mb4 更清楚。

3.2 排序规则决定字符串怎样比较

COLLATE 指定排序规则，它会影响字符串比较、排序，以及唯一性判断。例如，有的规则不区分字母大小写，有的区分。

这里沿用 mytest1、mytest2，比较两种规则：

-- general_ci 中的 ci 表示比较时不区分大小写。
CREATE DATABASE mytest1
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

-- bin 规则按字符的二进制编码值比较，区分大小写。
CREATE DATABASE mytest2
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_bin;

这两种规则是为了看清差别。MySQL 8 的其他排序规则，例如 utf8mb4_0900_ai_ci，也很常用，应按项目对大小写、重音符号等的要求选择。

先在 mytest1 中存入 a 和 B：

USE mytest1;
CREATE TABLE letters (letter VARCHAR(1));
INSERT INTO letters (letter) VALUES ('a'), ('B');

-- 必须写 ORDER BY，才能要求结果按指定规则排序。
SELECT letter FROM letters ORDER BY letter ASC;

结果依次是 a、B。不区分大小写时，可以把这里的 B 按 b 来理解，于是 a 排在前面。

再在 mytest2 中做同样的操作：

USE mytest2;
CREATE TABLE letters (letter VARCHAR(1));
INSERT INTO letters (letter) VALUES ('a'), ('B');
SELECT letter FROM letters ORDER BY letter ASC;

结果依次是 B、a。这两个 ASCII 字符的编码值分别是 66 和 97，所以 B 在前。其他 Unicode 字符不能只用 ASCII 来解释。

排序规则不保证查询结果自动有序。 没有 ORDER BY 时，不要依赖看到的记录顺序。后面的主要例子放回 mytest1：

USE mytest1;

4. 建表前，先选合适的数据类型

表中的字段，需要声明数据类型。姓名用字符串，成绩用数字，生日用日期；选对类型，数据库才能正确存储、比较和计算。

建表的基本形式是 CREATE TABLE 表名 (字段名 数据类型, ...);。先认识类型，第 5 节再建一张实际的学生表。

4.1 整数、浮点数和定点数

整数默认有正负范围，例如 INT 的有符号范围是 −2,147,483,648 到 2,147,483,647。加上 UNSIGNED 可以表示非负整数，并扩大正数范围，但不能再保存负数。

FLOAT、DOUBLE 是近似值，不能保证任意十进制小数都精确。金额一般用 DECIMAL。

DECIMAL(M, D) 的 M 是总位数，D 是小数位数，不是“数字最大值”。例如：

-- 共 8 位数字，其中 2 位在小数点后：整数部分最多 6 位。
CREATE TABLE price_demo (price DECIMAL(8, 2));
INSERT INTO price_demo (price) VALUES (19.90);
SELECT price FROM price_demo;

结果是 19.90。正负号和小数点不计入这 8 位，有符号 DECIMAL(8, 2) 可表示的范围是 −999999.99 到 999999.99。

4.2 日期与时间

下表的存储大小按 MySQL 8.0 / 8.4、不含小数秒精度来说明：

TIME 可以表示超过 24 小时的时长，所以也允许负数。DATETIME 适合表示一个日历上的日期时间；TIMESTAMP 在存取时会根据会话时区与 UTC 转换，且范围较小。

TIMESTAMP 不是“从 1970 年到现在的毫秒数”。 SQL 中看到的值仍然是日期时间形式。如果要保存时间戳数字，需要另选整数类型并明确单位。

TIME、DATETIME、TIMESTAMP 可以指定 0～6 位小数秒精度，例如 DATETIME(3) 保留毫秒。带小数秒会额外占用 0～3 字节。实际写入时，还会受 SQL 模式和合法日期检查的影响，学习时不要依赖零日期等特殊输入。

4.3 字符串

不能把 VARCHAR(20) 理解为固定占用 21 字节。它的存储与实际内容、字符集有关，还需要 1 或 2 字节记录长度。utf8mb4 中一个字符最多需要 4 字节，因此“字符数”和“字节数”要分开。

CHAR 是定长字符串，涉及补空格和取值时尾随空格的处理；不是所有字符集下都简单地占用 M 字节。VARCHAR 也不能只看单列声明，还要满足 MySQL 的整行大小限制。

4.4 位与二进制数据

TEXT 按字符集处理文本；BLOB 保存二进制内容。BLOB 表中的大小是类型的理论内容上限，真正传输、写入还会受配置和资源限制。图片、视频等大文件也常放到文件系统或对象存储，数据库只保存地址。

5. 数据库和表的基本操作

这一节把常用操作连起来。主要例子使用第 3 节创建的 mytest1，后面没有特别说明时都在这个库中执行。

5.1 查看、选择、创建和删除数据库

SHOW DATABASES;
USE mytest1;

SHOW DATABASES 看当前账号能看到哪些数据库，USE 选择接下来操作的数据库。

创建和删除可以先用一个练习库理解：

CREATE DATABASE practice_db DEFAULT CHARACTER SET utf8mb4;

-- 删除整个练习库，包括其中的表和数据。
DROP DATABASE practice_db;

DROP DATABASE 不是清空一个变量，它会删除库中的对象和数据，执行前要确认名称。

5.2 创建和查看学生表

用 student 保存编号、姓名和成绩：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    score DECIMAL(5, 2)  -- 没录入成绩时，允许保存 NULL。
) ENGINE = InnoDB;

id 用来区分学生；name 不允许为 NULL；score 保留两位小数。PRIMARY KEY 和 AUTO_INCREMENT 在第 8 节继续讲。

SHOW TABLES;
DESC student;

SHOW TABLES 查看当前库里的表，DESC 查看字段、类型、是否允许为空等结构。

5.3 修改表结构

假设想临时加入年龄，再调整字段名和类型：

ALTER TABLE student ADD COLUMN age INT;

-- CHANGE 可以同时改字段名和类型。
ALTER TABLE student CHANGE COLUMN age student_age SMALLINT;

-- MODIFY 不改字段名，只重新声明字段的类型及相关属性。
ALTER TABLE student MODIFY COLUMN student_age TINYINT;

ALTER TABLE student DROP COLUMN student_age;

执行完后，学生表恢复到编号、姓名、成绩三个字段。修改类型时要考虑现有数据是否能转换；CHANGE、MODIFY 中需要保留的 NOT NULL、默认值等属性，也应重新写清楚。

删除表可以单独用一个空表练习：

CREATE TABLE table_to_drop (id INT);
DROP TABLE table_to_drop;

5.4 插入、查询、修改和删除记录

先准备少量数据，后面的函数和运算符就能直接使用：

INSERT INTO student (name, score) VALUES
    ('张三', 80),
    ('李四', 20),
    ('Alice', 90),
    ('王五', NULL);

在刚创建的空表中，它们的编号依次是 1～4。查看时明确要求按编号排序：

SELECT id, name, score FROM student ORDER BY id;

id  name   score
1   张三   80.00
2   李四   20.00
3   Alice  90.00
4   王五   NULL

修改一名学生的成绩：

-- WHERE 指定只修改编号为 1 的学生。
UPDATE student SET score = 75 WHERE id = 1;
SELECT score FROM student WHERE id = 1;

查到 75.00。为了让后面的结果方便对照，再改回 80：

UPDATE student SET score = 80 WHERE id = 1;

删除时也使用条件。这里先新增一条练习记录，再删掉它：

INSERT INTO student (name, score) VALUES ('临时学生', 60);
DELETE FROM student WHERE name = '临时学生';

这段代码只是用来练习删除。实际业务里，姓名可能重复，通常应根据主键删除。UPDATE 和 DELETE 没写 WHERE 时，会修改或删除表中的所有记录。

6. SQL 函数：对查询到的数据做处理

函数可以对数字、字符串、日期做处理，也可以汇总多行数据。例如统计平均成绩，用 AVG 比先把所有成绩取到 Java 再逐个相加更直接。

复杂业务流程通常放在 Java 等应用代码里，数据库负责适合它的数据查询、计算和约束。不是“SQL 函数都耗资源所以不能用”，而是要看需求、数据量和执行效果。

这一节继续使用上面的 student 表。普通 SELECT 中使用这些函数，只改变查询结果，不会改动表里的原值。

6.1 数学函数

“不大于”和“不小于”包括相等，例如 FLOOR(2)、CEIL(2) 都是 2。

SELECT ABS(-3), FLOOR(2.8), CEIL(2.1);

-- 也可以把字段的值传给函数。
SELECT ABS(score), FLOOR(score), CEIL(score)
FROM student WHERE id = 1;

第二条语句中的成绩是 80.00，三个函数计算出的数值都是 80。负数要格外注意：FLOOR(-2.8) 是 −3，CEIL(-2.8) 是 −2。

6.2 字符串函数

替换一段字符： INSERT(s1, index, len, s2) 从 s1 的第 index 个字符开始，把 len 个字符替换成 s2。这里的位置从 1 开始。

-- 把“张三”中的两个字符替换成“小红”。
SELECT INSERT(name, 1, 2, '小红') FROM student WHERE id = 1;

结果是 小红，学生表里仍然是 张三。函数名虽然叫 INSERT，这里不是插入一行记录的 INSERT INTO。

转成大写或小写： UPPER 与 UCASE 是同义函数，LOWER 与 LCASE 也是。

SELECT UPPER(name), UCASE(name) FROM student WHERE id = 3;
SELECT LOWER(name), LCASE(name) FROM student WHERE id = 3;

Alice 转大写得到 ALICE，转小写得到 alice。中文没有英文字母那样的大小写，处理中文姓名时不会出现这种变化。

截取与反转：

SELECT LEFT(name, 1) FROM student WHERE id = 1;         -- 张
SELECT RIGHT(name, 1) FROM student WHERE id = 1;        -- 三
SELECT SUBSTRING(name, 2, 1) FROM student WHERE id = 1; -- 三
SELECT REVERSE(name) FROM student WHERE id = 1;        -- 三张

LEFT 从左边取，RIGHT 从右边取，SUBSTRING 从指定位置取指定长度，REVERSE 反转字符顺序。这里截取的是字符，不是把中文的某一个字节切出来。

6.3 日期函数

获取当前日期、时间：

SELECT CURDATE(), CURRENT_DATE(); -- 当前日期，两者是同义写法。
SELECT CURTIME(), CURRENT_TIME(); -- 当前时间。
SELECT NOW();                    -- 当前日期和时间。

具体结果取决于执行时间和会话时区，不应把某一天的结果当成固定输出。

日期相差多少天、往后或往前多少天，可以这样算：

-- 前一个日期减后一个日期，只比较日期部分。
SELECT DATEDIFF('2026-10-09', '2026-10-01'); -- 8

SELECT ADDDATE('2026-10-01', 3); -- 2026-10-04
SELECT SUBDATE('2026-10-09', 3); -- 2026-10-06

DATEDIFF 是有方向的，交换两个参数会得到 −8。ADDDATE(d, n) 和 SUBDATE(d, n) 的这组写法中，n 表示天数。

6.4 聚合函数

聚合函数把多行记录汇总成一个结果，比如人数、总成绩和平均成绩。

SELECT COUNT(*) AS student_count,
       COUNT(score) AS scored_count
FROM student;

结果分别是 4 和 3。COUNT(*) 统计记录数，COUNT(score) 只统计成绩不为 NULL 的记录，王五没有录入成绩，所以不算进第二项。

SELECT SUM(score) AS total_score,
       AVG(score) AS average_score,
       MAX(score) AS highest_score,
       MIN(score) AS lowest_score
FROM student;

总成绩是 190，平均成绩约 63.33，最高 90，最低 20。它们忽略 NULL，平均成绩按 190 ÷ 3 计算，不是除以 4。AS 为结果列起一个便于阅读的名字。

如果没有可参与计算的记录，COUNT 返回 0，SUM、AVG、MAX、MIN 通常返回 NULL，不能都理解成 0。

7. 运算符：计算与筛选条件

运算符既能算出一个值，也能用来判断“这行数据是否满足条件”。SQL 的条件结果通常用 1 表示真、0 表示假，还可能出现 NULL，表示未知。

7.1 算术运算符

继续使用编号为 1、成绩为 80 的学生：

SELECT score + 10 FROM student WHERE id = 1; -- 90
SELECT score - 10 FROM student WHERE id = 1; -- 70
SELECT score * 10 FROM student WHERE id = 1; -- 800
SELECT score / 10 FROM student WHERE id = 1; -- 8

这里仍是查询，不会把成绩改成 90。要保存修改，需使用 UPDATE。

MySQL 的 / 可以得到小数；如果需要整数除法，可以用 DIV，余数可以用 % 或 MOD。

7.2 比较运算符

SELECT score > 10 FROM student WHERE id = 1;  -- 1
SELECT score < 10 FROM student WHERE id = 1;  -- 0
SELECT score = 10 FROM student WHERE id = 1;  -- 0
SELECT score != 10 FROM student WHERE id = 1; -- 1

不等于也可以写成 <>。注意 SQL 的相等判断用 =，不是 Java 的 ==。

放到 WHERE 中，就可以筛选记录：

SELECT name, score FROM student WHERE score > 60 ORDER BY id;

结果是张三和 Alice。王五的成绩为 NULL，比较结果是未知，不满足 WHERE 要求的真条件。

7.3 逻辑运算符

AND 表示同时满足，OR 表示至少满足一个，NOT 表示取反。

SELECT score > 10 AND score != 10 FROM student WHERE id = 1;      -- 1
SELECT score > 10 OR score != 10 FROM student WHERE id = 1;       -- 1
SELECT NOT (score > 10 AND score != 10) FROM student WHERE id = 1; -- 0

原来常见的 &&、||、! 在 MySQL 中有相应用法，但 &&、|| 作为逻辑操作符已不推荐，|| 还会受 PIPES_AS_CONCAT SQL 模式影响，变成字符串拼接。学习和项目代码直接写 AND、OR、NOT 更清楚，混合条件时加括号说明意图。

7.4 空值、区间、集合和模糊查询

判断空值用 IS NULL，不要用 = NULL：

SELECT score IS NULL FROM student WHERE id = 1; -- 0
SELECT name FROM student WHERE score IS NULL;  -- 王五
SELECT name FROM student WHERE score IS NOT NULL ORDER BY id;

NULL 表示没有值或未知，不等于空字符串 ''，也不等于数字 0。

BETWEEN ... AND ... 包含两个端点：

SELECT score BETWEEN 15 AND 20 FROM student WHERE id = 2; -- 1

李四的成绩是 20，刚好等于上限，也在这个闭区间内。

IN 表示属于给定的集合：

SELECT id, score FROM student WHERE id IN (1, 2, 3) ORDER BY id;

查到这三个编号的成绩：80、20、90。

LIKE 用来做简单的模式匹配：

SELECT * FROM student WHERE name LIKE '%三%'; -- 张三

% 匹配零个或多个字符，所以这个条件表示姓名中包含“三”。_ 匹配一个字符，例如 LIKE '张_' 可以匹配两个字符的“张三”。大小写是否敏感也受排序规则影响。

8. 表设计：主键、自增和外键

建表不只是把字段列出来，还要回答：怎样准确找到一条记录，怎样避免表之间出现不合理的数据？主键和外键就是两种常见的约束。

8.1 主键：给每行一个明确的身份

主键是唯一标识一行记录的一列或一组列，要求 不重复，也不能为 NULL。

例如两名学生都叫张三，只看姓名无法确定要修改谁，但主键编号可以区分他们。一张表只能有一个主键约束，这个主键可以包含多列，称为联合主键。

MySQL 允许创建没有显式主键的表，不过业务表通常应该主动设计主键，不要以为数据库会自动为每张表加上我们能使用的 id 字段。

常见做法是用与业务无关的编号作为主键，这叫 代理主键。记录量适中时可以用 INT，规模更大时可以考虑 BIGINT；类型选择要留出增长空间，不能只看眼下占用的字节数。

8.2 自增：让数据库生成编号

沿用账户表的例子：

CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(11)
) ENGINE = InnoDB;

插入时不指定编号：

INSERT INTO account (name) VALUES ('张三'), ('李四');
SELECT id, name FROM account ORDER BY id;

在新建的空表中，编号依次是 1 和 2。AUTO_INCREMENT 帮我们分配编号，PRIMARY KEY 负责保证唯一性和非空。

自增编号不保证连续。 删除记录、回滚插入、并发插入等都可能留下空缺，因此不能把它理解成“永远是上一行编号加 1”，更不能用最大编号代替记录数量。第 5 节删掉临时学生后，后续插入也不会自动填补那个编号。

已有主键补上自增，可以用 MODIFY。先用一张独立的 test 表练习：

CREATE TABLE test (id INT PRIMARY KEY);

-- id 已有主键索引，这里补上自增属性并明确保留非空。
ALTER TABLE test MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
INSERT INTO test () VALUES ();
SELECT id FROM test; -- 1

这条修改语句有前提：字段类型合适，现有数据满足约束，并且自增列符合索引要求。不是给任意字段加上 AUTO_INCREMENT 就一定成功。

8.3 外键：保证引用的数据存在

外键让一张表中的字段引用另一张表的键。比如学生的 cid 表示班级编号，就不应该填入一个不存在的班级。

这里保留 student 与 class 的场景，放到单独的 school_demo 库，这样可以看清外键版本的建表方式，也不会和前面的学生表示例混在一起。

先创建被引用的班级表：

CREATE DATABASE school_demo DEFAULT CHARACTER SET utf8mb4;
USE school_demo;

CREATE TABLE class (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
) ENGINE = InnoDB;

再创建引用班级的学生表：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    cid INT,
    CONSTRAINT fk_student_class
        FOREIGN KEY (cid) REFERENCES class(id)
) ENGINE = InnoDB;

REFERENCES class(id) 表示 cid 引用班级表的 id。先有班级，再插入属于这个班级的学生：

INSERT INTO class (id, name) VALUES (1, '一班');
INSERT INTO student (name, cid) VALUES ('张三', 1);
SELECT name, cid FROM student; -- 张三，1

如果把 cid 改为不存在的 99，插入就会失败；这个定义允许 cid 为 NULL，可以表示暂时未分班。如果一个班级还有学生引用它，默认也不能直接删除该班级，需先处理关联记录，或明确设计级联规则。

外键两侧的类型要兼容，例如整数的类型和有无 UNSIGNED 应一致。入门时让外键引用对方的主键或唯一键最清楚，也符合较新版本的推荐约束。

实际项目是否使用外键，需要权衡。它能在数据库里保护数据关系，也会增加检查和关联维护的成本。有些项目为便于分库或控制写入路径，选择由应用维护关系；这不代表外键普遍不能用。若不用外键，应用也必须处理并发、删除和异常情况下的数据一致性。

后面的例子回到原来的练习库：

USE mytest1;

9. 数据表之间的三种关系

表关系来自业务，而不是先决定“一定要做几张表”，再把数据硬塞进去。

9.1 一对一

一条 A 记录最多对应一条 B 记录，反过来也一样。比如用户和用户详情：一个用户只有一份详情，一份详情只属于一个用户。

可以让详情表的 user_id 同时作为主键和外键，这样同一用户不能出现两份详情。实际业务也可能允许某个用户暂时没有详情，所以“一对一”不必表示两边每条记录都已经有对应项。

9.2 一对多

一条 A 记录可以对应多条 B 记录，每条 B 记录只对应一条 A 记录。比如 一个班级有多个学生，一个学生属于一个班级。

外键通常放在“多”的一方，也就是把 cid 放在学生表中。第 8 节的班级与学生就是这个关系。

9.3 多对多

一条 A 记录可以对应多条 B 记录，反过来也可以。比如一个学生可以选多门课，一门课也可以被多个学生选择。

通常增加一张中间表 student_course，每行记录一次选课：

可以把 (student_id, course_id) 设为联合主键，避免同一个学生重复选择同一门课。两个字段分别引用学生和课程的键，这也是联合主键的一个实际用途。

10. 索引：帮助数据库更快找到数据

索引可以先理解成书的目录：想找某个姓名，不必总是从第一行看到最后一行，而是借助专门的数据结构定位记录。

它通常能帮助查询，但也要占用空间。插入、更新、删除记录时，相应索引也可能需要维护，所以不是索引越多越好。

10.1 几种索引名称怎么区分

下面的分类角度不同，可以重叠。例如，一个索引既可以是唯一索引，也可以是多列索引。

MySQL 8.0 / 8.4 的 InnoDB 支持全文索引和空间索引。全文查询使用 MATCH ... AGAINST 等语法，中文检索还需考虑分词；它不是给 LIKE '%文字%' 随便加一个全文索引就能自动提速。

普通索引也不是“任意类型、任意长度都能直接建立”：例如 TEXT、BLOB 的普通索引通常需要指定前缀长度，索引总长度也有限制。空间索引有空间列非空等要求，要按对应类型和版本规则创建。

10.2 创建和删除姓名索引

学生表的主键已经有索引。如果经常按姓名查询，可以再建姓名索引：

ALTER TABLE student ADD INDEX in_name (name);

这样定义完以后，不需要在每次查询里显式指定索引。是否使用，由优化器根据 SQL、数据等选择。

-- 查看索引定义。
SHOW INDEX FROM student;

-- 查看查询计划，观察 possible_keys、key 等信息。
EXPLAIN SELECT id, name FROM student WHERE name = '张三';

小表中扫描全表可能也很便宜，不应保证每次都选择某个索引。possible_keys 是候选索引，key 才是这次选择的索引。

下面是另一种创建写法。同一个索引二选一创建即可；如果想顺着练习第二种，先删掉已经创建的索引：

ALTER TABLE student DROP INDEX in_name;
CREATE INDEX in_name ON student (name);

不需要时再删除：

ALTER TABLE student DROP INDEX in_name;

10.3 联合索引与设计原则

联合索引的列有先后顺序，例如 (name, score)，常见查询可以利用它的最左侧部分：

CREATE INDEX idx_name_score ON student (name, score);

-- 条件用到了最左边的 name，且只查询索引包含的列。
EXPLAIN SELECT name, score FROM student WHERE name = '张三';

按 name 查询，或者同时按 name、score 查询，通常比较适合这个索引。只按 score 查询则不能把它当成一个普通的 score 单列索引；有些优化策略可能采用其他访问方式，所以仍要看查询计划，不能只凭字段名断言“一定触发索引”。这叫 最左前缀原则。

选索引时，可以从下面几点入手：

1. 优先观察经常用于 WHERE、关联和排序的列，结合实际查询组合设计。
2. 区分度高的值通常更有帮助。例如按唯一编号找一行，比按“是否启用”筛出半张表更容易减少读取量。
3. 查询列也可能影响设计。如果所需列都在索引里，就可能不必再取完整行，这叫覆盖索引。
4. 不要为每个字段都建索引，写入和维护都有成本。
5. 留意查询写法。普通 B-tree 索引通常不能直接对 LIKE '%三%' 做有效的范围定位，而 LIKE '张%' 更有机会利用姓名索引。

11. 事务：把多步修改作为一个整体

事务适合处理“几步操作必须配套成功”的事情。比如张三给李四转 100 元，张三扣了钱，李四也必须收到钱，不能只完成一半。

11.1 ACID 四个特点

一致性不是“执行前后每个值都不变”。转账之后两人的余额当然变了，只是总额和相关规则仍然成立。事务也不会自动识别所有业务规则，应用仍要检查余额、处理失败。

隔离性也不是“事务之间完全没有联系”。MySQL 提供不同隔离级别，控制能看到哪些变化。InnoDB 默认是 REPEATABLE READ；其他常见级别有 READ UNCOMMITTED、READ COMMITTED、SERIALIZABLE，以后再结合并发例子深入学习。

11.2 用账户转账观察回滚

继续使用第 8 节的 account，里面已经有张三和李四。先补上余额字段，并设置各自余额：

ALTER TABLE account ADD COLUMN balance DECIMAL(10, 2) NOT NULL DEFAULT 0;
UPDATE account SET balance = 1000 WHERE id = 1;
UPDATE account SET balance = 500 WHERE id = 2;

开启事务后转账，再查看修改：

START TRANSACTION;

-- 两步操作都在当前连接的同一个事务里。
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

SELECT name, balance FROM account ORDER BY id;

当前连接看到张三余额 900，李四余额 600。此时还没有提交，先试一次回滚：

ROLLBACK;
SELECT name, balance FROM account ORDER BY id;

余额恢复为 1000 和 500。撤销的是当前事务中尚未提交的修改。

11.3 再执行一次，这次提交

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT name, balance FROM account ORDER BY id;

这次余额保存为 900 和 600。提交后，不能再用一次普通 ROLLBACK 撤销刚才已经提交的事务。

这只是观察事务的例子，假设两个账户存在且余额足够。真正转账时，还要检查扣款条件、每次更新是否成功，并正确处理并发。比如扣款可增加 AND balance >= 100，应用确认影响了一行后再加钱；若没有扣款成功，就应回滚，不能继续给对方加钱。

还要记住三个容易误会的地方：

• MySQL 通常开启自动提交，单独的一条修改语句会自行提交；START TRANSACTION 可以显式组织多步操作。
• 一条 SQL 报错，不代表整个事务一定自动回滚。有些错误只撤销那条语句，应用遇到失败时应按业务需要执行回滚；连接池中的连接尤其不能遗留未结束的事务。
• CREATE TABLE、ALTER TABLE 等很多 DDL 会隐式提交，不能把它们当成普通数据修改混进转账，然后期待 ROLLBACK 撤销一切。

12. 视图：给同一份数据不同的查看方式

视图可以理解为保存了一条查询定义的“虚拟表”。使用时像查表一样查它，但普通视图不会另存一份查询结果。

比如 people 表有姓名和薪资，普通查看只需要姓名，管理人员才需要查看薪资。可以分别建立两个视图，避免每次重复写列清单。

12.1 准备人员表

CREATE TABLE people (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO people (id, name, salary) VALUES (1, '张三', 8000);

12.2 创建并查询视图

CREATE VIEW view_common AS SELECT id, name FROM people;
CREATE VIEW view_all AS SELECT * FROM people;

SELECT * FROM view_common; -- 只有 id、name。
SELECT * FROM view_all;    -- 还有 salary。

第一个结果是 1、张三，第二个结果是 1、张三、8000.00。基础表数据改变后，之后查询视图也会看到相应变化。

这里保留 SELECT * 演示全字段视图。实际项目更适合明确列出需要的字段；MySQL 在创建视图时确定字段列表，后来给基础表增加一列，并不会自动把新列补进旧视图。

视图只展示部分字段，不等于权限已经限制好了。 如果普通账号还可以直接查询 people，仍然能看到薪资。要真正控制访问，必须配合数据库权限，让账号只访问允许的对象。

有些简单视图可以更新，但含聚合等结构的视图往往不能直接更新，不能把所有视图都当作完全等同于实体表。

12.3 删除视图

DROP VIEW view_common;
DROP VIEW view_all;

删除的是视图定义，people 表及其中的数据仍在。

13. 触发器：修改表时自动执行操作

触发器是一段绑定到表上的逻辑。当这张表执行指定的插入、更新或删除操作时，数据库自动运行它，不需要 Java 再单独调用。

例如，向 tab1 插入一个编号时，希望 tab2 也保存相同编号；从 tab1 删除时，tab2 也删掉对应编号。沿用这个例子，就能直接看出触发器什么时候起作用。

13.1 触发时间和新旧行

FOR EACH ROW 表示针对每一行触发，不是整条语句只触发一次。一次插入三行，就会对这三行分别执行触发逻辑。

13.2 准备两张表

CREATE TABLE tab1 (tab1_id INT PRIMARY KEY) ENGINE = InnoDB;
CREATE TABLE tab2 (tab2_id INT PRIMARY KEY) ENGINE = InnoDB;

接下来的触发器包含 BEGIN ... END。在 mysql 命令行等支持它的客户端中，先用 DELIMITER 临时换结束符，避免客户端看到内部的分号就提前发送整段代码。

DELIMITER 是客户端命令，不是服务器 SQL 语句。一些图形工具会自己识别语句边界，或者要求“执行脚本”；若工具不支持这条命令，应按工具方式整体执行定义，不能把它作为普通 SQL 发给 JDBC。

13.3 插入后同步编号

DELIMITER $$
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
    -- NEW.tab1_id 是本次插入行的编号。
    INSERT INTO tab2 (tab2_id) VALUES (NEW.tab1_id);
END$$
DELIMITER ;

使用时只插入 tab1：

INSERT INTO tab1 (tab1_id) VALUES (1);
SELECT * FROM tab2; -- 自动出现 tab2_id = 1。

我们没有手动向 tab2 插入，数据却出现了，说明触发器已经执行。

13.4 删除后同步移除编号

DELIMITER $$
CREATE TRIGGER t_afterdelete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
    -- 删除操作没有 NEW，用 OLD 获取被删除行的编号。
    DELETE FROM tab2 WHERE tab2_id = OLD.tab1_id;
END$$
DELIMITER ;

DELETE FROM tab1 WHERE tab1_id = 1;
SELECT * FROM tab2; -- 结果为空，对应编号已经被删除。

删除触发器：

DROP TRIGGER t_afterinsert_on_tab1;
DROP TRIGGER t_afterdelete_on_tab1;

13.5 什么时候适合使用

触发器可以减少应用里的重复操作：无论哪个应用修改这张表，相同的数据库逻辑都会执行；某些简单规则也能集中维护。

但自动执行也意味着，读 Java 代码时不一定能看出背后的额外操作。触发器出错，还可能导致原来的数据修改失败，所以“只改触发器就完全不影响业务”并不成立。更适合放少量明确的数据库规则，不宜把复杂业务悄悄堆在里面。

这个同步编号例子只演示触发机制，不是完整的数据同步方案。InnoDB 表上的这些触发操作通常和触发它的语句处于同一事务中；普通表操作的 DELETE 会触发删除触发器，但 TRUNCATE TABLE 不会，外键级联操作也不会像直接操作那样触发它们。

14. 存储过程：给一组 SQL 起个名字再调用

存储过程是保存在数据库中的一组 SQL 语句，可以按名字调用，并传入或取回参数。比如反复统计学生人数，不必每次由客户端重新组织那组操作。

14.1 为什么使用，又有哪些限制

存储过程可以把重复 SQL 组织成一个可调用的单元，一次定义、多次调用。多步操作在服务器端完成，也可能减少客户端和服务器之间的往返。

但它不是“编译成一个程序，所以一定比普通 SQL 快”。速度仍要看内部查询、索引和调用方式；复杂过程还会增加调试、版本管理和数据库迁移的难度。Java 项目通常把业务流程放在应用层，把合适的数据库操作留给 SQL 或存储过程。

权限方面，可以授予账号执行指定存储过程的 EXECUTE 权限，让它通过过程完成允许的操作。调用者仍需获得执行权限；内部以定义者还是调用者的权限执行，取决于 SQL SECURITY 等设置，并不是“没有权限也能随意调用”。

基本形式是 CREATE PROCEDURE 名称(参数列表) BEGIN ... END。参数按“方向、名称、类型”声明：

下面仍使用 mytest1.student，不要切换到外键示例的 school_demo。多个语句的定义继续使用 DELIMITER，定义和调用分开阅读。

14.2 输入参数：根据参数添加姓名

沿用 add_name：传入 1 时添加 MySQL，否则添加 Java。

DELIMITER $$
CREATE PROCEDURE add_name(IN target INT)
BEGIN
    -- 局部变量与表字段区分命名，避免 name 指代不清。
    DECLARE new_name VARCHAR(20);

    IF target = 1 THEN
        SET new_name = 'MySQL';
    ELSE
        SET new_name = 'Java';
    END IF;

    INSERT INTO student (name) VALUES (new_name);
END$$
DELIMITER ;

调用：

CALL add_name(2); -- 添加一条 name 为 Java 的记录。
CALL add_name(1); -- 再添加一条 name 为 MySQL 的记录。
SELECT id, name, score FROM student ORDER BY id;

这两条新记录的成绩是 NULL，编号由自增生成。它们使用技术名称只是为了看清分支，真实学生业务当然应该传入学生姓名。

不需要这个过程时删除定义：

DROP PROCEDURE add_name;

已经插入的学生记录不会跟着删除。

14.3 输出参数：取回学生人数

DELIMITER $$
CREATE PROCEDURE count_of_student(OUT count_num INT)
BEGIN
    -- INTO 把查询得到的人数交给输出参数。
    SELECT COUNT(*) INTO count_num FROM student;
END$$
DELIMITER ;

调用时准备一个会话变量来接收：

CALL count_of_student(@count_num);
SELECT @count_num;

@count_num 属于当前连接，不是 Java 变量。如果按前面的步骤执行，最初有 4 名学生，临时学生已经删除，又添加了 Java 和 MySQL，所以返回 6。

不再使用时可以删除：

DROP PROCEDURE count_of_student;

14.4 IF：按条件查询不同字段

IF 适合需要依次判断条件的情况。这里保留“1 查姓名，2 查编号”的例子：

DELIMITER $$
CREATE PROCEDURE example_if(IN x INT)
BEGIN
    IF x = 1 THEN
        SELECT name FROM student ORDER BY id;
    ELSEIF x = 2 THEN
        SELECT id FROM student ORDER BY id;
    END IF;
END$$
DELIMITER ;

CALL example_if(1); -- 返回姓名列。
CALL example_if(2); -- 返回编号列。
DROP PROCEDURE example_if;

当 x 既不是 1 也不是 2 时，因为没有 ELSE，这个过程不会执行查询分支。

14.5 CASE：按一个值选择分支

当同一个参数需要与几个固定值比较时，CASE 可以把分支排得更直观：

DELIMITER $$
CREATE PROCEDURE example_case(IN x INT)
BEGIN
    CASE x
        WHEN 1 THEN
            SELECT name FROM student ORDER BY id;
        WHEN 2 THEN
            SELECT id FROM student ORDER BY id;
        ELSE
            SELECT * FROM student ORDER BY id;
    END CASE;
END$$
DELIMITER ;

CALL example_case(1); -- 查姓名。
CALL example_case(2); -- 查编号。
CALL example_case(3); -- 进入 ELSE，查全部字段。
DROP PROCEDURE example_case;

这里是存储过程中的 CASE 语句，结束写 END CASE；查询中也有返回一个值的 CASE 表达式，两者不要混淆。

14.6 WHILE：计算 1 到 100 的和

循环适合在条件成立时重复执行某些语句。用 1 到 100 求和，可以看清“累计”和“推进循环”这两步。

DELIMITER $$
CREATE PROCEDURE example_while(OUT sum_num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE s INT DEFAULT 0;

    WHILE i <= 100 DO
        SET s = s + i; -- 把当前数字加进总和。
        SET i = i + 1; -- 进入下一个数字，最终让循环结束。
    END WHILE;

    SET sum_num = s; -- 把计算结果交给输出参数。
END$$
DELIMITER ;

CALL example_while(@sum_num);
SELECT @sum_num; -- 5050
DROP PROCEDURE example_while;

第一次把 1 加进去，第二次加 2，最后一次加 100。之后 i 变成 101，不再满足条件，循环结束。

如果循环里写的是 SET s = s + 1，它表达的是“每循环一次就计数加 1”，循环 100 次得到 100。求和要加当前的 i，计数才是每次加 1，两种写法用途不同。

这些流程控制是为了认识存储过程。真正统计表里已有数据时，通常先考虑 COUNT、SUM 等集合操作，而不是一行行循环处理。

15. 官方资料

遇到更详细的范围、限制和版本差异，可以继续查 MySQL 8.4 官方手册。下面按本文主题列出入口，便于对应查询：

• 存储引擎与 InnoDB
• utf8mb4 字符集与排序规则的影响
• 数据类型、存储大小与日期时间类型
• 自增列示例与外键约束
• 内置函数与聚合函数
• 索引与联合索引
• 提交与回滚及隐式提交
• 视图、触发器与存储过程