02. Functions, Constraints, Multi-table Queries and Transactions
02. 函数、约束、多表查询 与 事务
1. 函数
MySQL内置了许多函数，下面是常用的
1.1. 字符串函数
CONCAT(S1, S2, ... Sn)：字符串拼接
LOWER：转小写
UPPER：转大写
LPAD(str, n, pad)：左填充
RPAD(str, n, pad)：右填充
TRIM：去除头部、尾部空格
SUBSTRING(str, start, len)：字符串截取（start 从1开始）
使用方法：SELECT 函数(参数);
1.2. 数值函数
CEIL：向上取整
FLOOR：向下取整
MOD(x, y)：返回模，x / y 的余数
RAND：返回随机数，（0，1）之间
ROUND(x, y)：四舍五入 x，保留指定小数 y
1.3. 日期函数
CURDATE：当前日期
CURTIME：当前时间
NOW：现在的日期和时间
YEAR：年
MONTH：月
DAY：日
DATE_ADD：日期加减
DATEDIFF：日期相距
1.4. 流程函数
IF(value, t, f)：如果 value 为 true，返回 t， 否则返回 f。
IFNULL(value1, value2)：如果value1不为空，返回value1，否则返回value2
CASE WHEN [] THEN [] ... ELSE [] END：条件语句
2. 约束
是作用于表中字段上的规则，用于限制存储在表中的数据。
非空约束
NOT NULL：限制数据不能为 null唯一约束
UNIQUE：保证所有数据唯一不重复主键约束
PRIMARY KEY：一行数据的唯一表示默认约束
DEFAULT：如果没有指定值，使用默认值检查约束
CHECK：保证字段满足某一个条件外键约束
FOREIGN KEY：表间建立连接
2.1. 外键约束
用来让两张表的数据之间建立连接，保证数据的一致性和完整性。
子表：具有外键的表
父表：规定外键的表
CREAYE TABLE 表名(
字段名 数据类型
...
[CONSTRANT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);3. 多表查询
业务关系：
一对多：在多方建立外键，指向一方的主键
多对多：建立第三张表（中间表），中间表至少包含两个外键，分别关联两张表
一对一：在任意一方加入外键，关联另一方，并设置外键为唯一（UNIQUE）
3.1. 概述
多表查询需要消除无效的笛卡尔积关系。
3.2. 连接查询
内连接：相当于查询A、B交集部分数据
外连接：左外连接、右外连接。一张表的所有数据 + 交集数据
自连接：当前表与自身的连接查询，自连接必须使用表别名。（相当于两张表）
3.3. 联合查询 - union，union all
把多次查询的结果联合起来形成一张新的查询表
unionall- 不去重union- 去重
注意：多张表的列数要保持一致，字段类型也要保持一致。
3.4. 子查询
SQL 语句中嵌套 SELECT 语句，称为嵌套查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部可以是 INSERT/UPDATE/DELETE/SELECT 中的任何一个
标量子查询（结果为一个值）
行子查询（结果为一行值）
列子查询（结果为一列值）
表子查询（结果为一表）
4. 事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是当执行一条DML语句，MySQL会立即隐式提交事务。
4.1. 事务操作
查看/设置事务提交方式
SELECT @@autocommit;SET @@autocommit = 0;
提交事务
COMMIT;
回滚事务
ROLLBACK;
手动提交事务可以保证数据的正确性。报错的时候可以保证数据不会被修改。
开始事务
STRAT TRANSACTION / BEGIN;
4.2. 事务四大特性
原子性（Atomicity）：事务是不可分割的最小操作单元。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
4.3. 并发事务问题
赃读：一个事务读到另外一个事务（正在操作）还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻读”。
4.4. 事务隔离级别
隔离级别
赃读
不可重复读
幻读
Read uncommitted
✔️
✔️
✔️
Read committed
❌
✔️
✔️
Repeatable Read（默认）
❌
❌
✔️
Serializable
❌
❌
❌
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPETABLE READ | SERIALIZABLE}
Last updated