Mysql 基础（三）

约束

• 约束是作用在表中字段的规则，用于限制储存在表中的数据

• 目的：保证表中数据的正确，完整性和有效性

• 分类

约束	描述	关键字
非空约束	限制该字段数据不能为 NULL	NOT NULL
唯一约束	保证该字段的所有数据唯一，不重复	UNIQUE
默认约束	保存数据时，如果没有指定该数据的值，采用默认值	DEFAULT
检查约束	保证字段值满足条件（8.0.16版本后）	CHECK
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

示例

CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( age > 0 AND age <= 120) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

外键约束

• 外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

• 具有外键的表称为子表（从表），外键关联的表称为父表（主表）

• 语法

  • 添加外键

  #建表时定义
  CREATE TABLE 表名(
    ...,
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
  );
  #建表后添加
  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

  • 删除外键

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

• 若一张表被外键关联，删除该表数据时会检查该数据是否被子表中数据关联，这样可以保证数据的完整性

• 外键删除和更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先会检查该记录是否有对应外键，如果有则不允许删除/更新
RESTRICT	当在父表中删除/更新对应记录时，首先会检查该记录是否有对应外键，如果有则不允许删除/更新（同上）
CASCADE	当在父表中删除/更新对应记录时，首先会检查该记录是否有对应外键，如果有，删除/更新子表中对应记录
SET NULL	当在父表中删除/更新对应记录时，首先会检查该记录是否有对应外键，如果有，将子表中对应记录该外键设为 NULL
SET DEFAULT	父表有变更时，子表将外键列设为一个默认值（ innodb 不支持）

设置外键行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名) 
ON UPDATE [更新时行为] ON DELETE [删除时行为];

多表查询

表间关系

根据业务关系设计表间关系，分为三种

• 一对多（多对一）

  员工和部门的关系

  在多的一方（员工）建立外键，指向一的一方的主键

• 多对多

  学生和课程的关系

  建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 一对一

  学生和学生信息的关系

  可以进行表拆分，提升检索效率，在任意一方添加外键，关联另外一方的主键，设置外键为唯一（ UNIQUE ）

连接查询

从多张表中进行数据查询

内连接

查询两张表交集部分数据

• 隐式内连接

  SELECT 字段列表 FROM 表1 [别名1],表2 [别名2] WHERE 连接条件...;

• 显式内连接

  SELECT 字段列表 FROM 表1[别名1] [INNER] JOIN 表2 [别名2] ON 连接条件...;

外连接

• 左外连接，查询左表所有数据和两张表交集部分数据

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

• 右外连接，查询右表所有数据和两张表交集部分数据

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

  通过替换两表顺序，左外连接和右外连接可起到相同效果

自连接

当前表与自身连接查询，自连接时必须使用表别名，自连接既可使用内连接，也可使用外连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;

联合查询

• UNION : 把多次查询的结果合并起来，形成一个新的查询结果集

  SELECT 字段列表 FROM 表A...
  UNION [ALL]
  SELECT 字段列表 FROM 表B...;

  • 加 ALL 关键字时结果不去重，不加 ALL 关键字时结果去重
  • 多张表的查询字段列表必须保持一致

子查询

SQL 语句中嵌套 SQL 语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

• 字查询外部的语句可以是 INSERT/UPDATE/DELETE/SELECT 的任何一个

分类

根据子查询结果不同，分为

• 标量子查询：子查询返回结果为单个值
• 列子查询：子查询返回结果为一列
• 行子查询：子查询返回结果为一行
• 表子查询：子查询放回结果为多行多列

根据子查询位置不同，分为：WHERE 之后，FROM 之后，SELECT 之后

标量子查询

常用操作符： = <> > >= < <=

子查询通常放在 WHERE 之后

列子查询

常用操作符：IN NOT IN ANY SOME ALL

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	集合中任意一个满足即可（比…中任意一个…)
SOME	同 ANY
ALL	集合中所用值都必须满足（比…都…）

行子查询

常用操作符 = <> IN NOT IN

表子查询

通常把表子查询的结果作为临时表和其他表一起查询

事务

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
• MySQL 默认自动提交事务，当执行一条 DML 语句，MySQL 会立即隐式提交事务，一条 语句对应一次事务

事务操作

• 查看/设置事务默认提交方式

  #查询事务提交方式 0为手动提交，1为自动提交
  SELECT @@autocommit;
  #设置为手动提交
  SET @@autocommit = 0;

• 提交事务

  COMMIT;

• 回滚事务

  ROLLBACK;

  回滚到上一次提交事务时的状态

• 手动开启事务

  #两种方式
  START TRANSACTION;
  BEGIN;

事务四大特性 ACID

• 原子性( Automicity ): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性( Consistency ): 事务完成后，所有的数据都保持一致状态
• 隔离性( Isolation ): 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性( Durability ): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读到的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	☑️	☑️	☑️
Read committed	✖️	☑️	☑️
Repeatable Read(Mysql 默认)	✖️	✖️	☑️
Serializable(串行化)	✖️	✖️	✖️

• ☑️ 表示该隔离级别下会出现该问题，✖️表示该隔离级别下不会出现该问题

• 从上到下，隔离级别越高，性能越差

• 查看事务的隔离级别

  SELECT @@TRANSACTION_ISOLATION;

• 设置事务隔离级别

  SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { Read uncommitted | Read committed | Repeatable Read | Serializable }

  • 不区分大小写
  • SESSION 表示当前会话，GLOBAL 表示全局