SQL

数据库约束与高级操作总结

约束

概念

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

目的: 保证数据库中数据的正确性、有效性和完整性。

分类:

  • 非空约束: 限制字段数据不能为 NULL

    1
    NOT NULL

  • 唯一约束: 保证字段数据唯一、不重复

    1
    UNIQUE

  • 主键约束: 主键是行数据的唯一标识，要求非空且唯一

    1
    PRIMARY KEY

  • 默认约束: 保存数据时未指定字段值，则采用默认值

    1
    DEFAULT

  • 检查约束 (8.0.16版本之后): 保证字段值满足条件

    1
    CHECK

  • 外键约束: 用来建立表间数据的关联，保证一致性与完整性

    1
    FOREIGN KEY

外键约束

概念

外键用于在两张表之间建立数据连接，保证数据的一致性和完整性。

语法

  • 添加外键

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE 表名 (
      字段名 数据类型,
      ...
      [CONSTRAINT 外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
    );

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

  • 删除外键

    1
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

  • NO ACTION: 检查外键关联，不允许删除/更新。
  • RESTRICT: 与 NO ACTION 类似，不允许删除/更新。
  • CASCADE: 删除/更新父表记录时，同时删除/更新子表记录。
  • SET NULL: 删除父表记录时，将子表外键字段设置为 NULL。
  • SET DEFAULT: 设置子表外键字段为默认值（InnoDB 不支持）。

示例:

1
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

概述

多表查询指从多张表中获取数据。
需要避免无效的 笛卡尔积，即两张表中所有数据的组合。

多表查询分类

  1. 连接查询

    • 内连接: 获取两张表交集部分的数据。
    • 左连接: 获取左表所有数据及交集部分数据。
    • 右连接: 获取右表所有数据及交集部分数据。
    • 自连接: 表与自身的连接查询，必须使用表别名。

  2. 子查询
    在 SQL 语句中嵌套 SELECT 语句，称为嵌套查询或子查询。

内连接

  • 隐式内连接

    1
    SELECT 字段列表 FROM 表1, 表2 WHERE 条件;

  • 显示内连接

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

自连接

自连接是将一张表视为两张表，设置表别名进行查询。

事务

概念

事务是一组操作的集合，是一个不可分割的工作单位。
事务中的所有操作要么全部成功，要么全部失败。

事务操作

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

    1
    2
    3
    SELECT @@autocommit; -- 1 表示自动提交，0 表示手动提交

    SET @@autocommit = 0;

  • 开启事务

    1
    START TRANSACTION;

  • 提交事务

    1
    COMMIT;

  • 回滚事务

    1
    ROLLBACK;

事务并发问题

  1. 脏读: 读取到未提交的数据。
  2. 不可重复读: 一次事务中，数据多次读取结果不同。
  3. 幻读: 一次事务中新增或删除数据导致总记录数不一致。

事务隔离级别

隔离级别 脏读 不可重复读 幻读
Read uncommitted 会 会 会
Read committed 不会 会 会
Repeatable Read 不会 不会 会
Serializable 不会 不会 不会

  • 查看事务隔离级别

    1
    SELECT @@TRANSACTION_ISOLATION;

  • 设置事务隔离级别

    1
    SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

说些什么吧！

valine