概念

一些可能会比较模糊的概念：

索引概念

index INDEX

  1. 只有是独一无二的列才可以作为索引，当列为 UNIQUE 时，默认添加此列为 INDEX。

  2. 索引是与表相关的一个可选结构：一个表中可以存在索引，也可以不存在索引，不做硬性要求。

  3. 用以提高 SQL 语句执行的性能：快速定位我们需要查找的表的内容（物理位置），提高 sql 语句的执行性能。

  4. 减少磁盘 I/O 取数据从磁盘上取到数据缓冲区中，再交给用户。磁盘 IO 非常不利于表的查找速度（效率的提高）。

约束概念

constraint CONSTRAINT

对数据的一种限制，用于实现数据库的数据完整性。

  • 独一无二：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 符合范围：例如：年龄范围0-120，性别范围“男/女”
  • 引用存在：例如：员工所在部门，在部门表中要能找到这个部门
  • 自定义完整性：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

键概念

key KEY

键（Key）就是用来识别实体（一行数据描述的物体，比如一个人的信息，一个岗位的信息）的。换句话说，键可以用来确定表中具体的一行记录。

主键 表示可以用它自己这一列来识别实体的 列。比如 UID 列，身份证列等等。主键同时拥有 索引 和 约束 的性质。primary key 既是索引（会作为优化的 index），也是约束（unique 和 not null）。换言之，被设置为 index，添加了 unique，not null 约束的列，就等同于 primary key。

约束概览

一共有下面的七种约束：

  • NOT NULL：非空约束，规定某个字段不能为空。
  • UNIQUE：唯一约束，规定某个字段在整个表中是唯一的。
  • PRIMARY KEY：主键(非空且唯一)约束。
  • AUTO_INCREMENT：自增约束。
  • FOREIGN KEY：外键约束。
  • CHECK：自定义检查约束。
  • DEFAULT：默认值约束。

约束分类

  • 根据约束数据列的限制，约束可分为：

    • 单列约束：每个约束只约束一列。
    • 多列约束：每个约束可约束多列数据。

  • 根据约束的作用范围，约束可分为：

    • 列级约束：只能作用在一个列上，跟在列的定义后面。
    • 表级约束：可以作用在多个列上，不与列一起，而是单独定义。

  1. 不支持多列约束有：NOT NULL（很好理解），AUTO_INCREMENT（只允许一列支持自增），DEFAULT（只能给一列设置默认）

  2. 支持多列约束有：UNIQUE（多列组合的独一无二），PRIMARY KEY（多列组合结果的主键），FOREIGN KEY（连接的外键是复合的话，约束的本表列也是多列），CHECK（自定义约束的判断表达式可以包含多个列的值）

  3. 所有约束都可以作为单列约束，只作用于一列。除了 FOREIGN KEY 以外（因为语法比较特殊，要指定外部表和列），都可以写在单列约束处，作用于此列。

  4. 所有支持多列约束的，都可以写在表级约束的地方。此时既可以作用多列，也可以作用一列。

约束操作命令

设置约束关键词为 XXXX（NOT NULL、UNIQUE、PRIMARY KEY、CHECK 等等）

创建时添加约束

定义表，建议作用单列的写在列约束处，作用多列的写在表约束处：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 除了 FOREIGN KEY 收到语法影响，其他都可以
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型 XXXX -- 列级约束
);

-- 支持多列约束的可以：UNIQUE PRIMARY FOREIGN CHECK
CREATE TABLE 表名称(
    字段名  数据类型,
    字段名  数据类型,
    [CONSTRAINT 约束名] XXXX(列名1, 列名2)
    -- 在表级约束可以选择给约束起名字。
);

后期新增约束

1
2
3
4
5
6
7
-- 等价于列级约束，除了 FOREIGN 以外都可以
-- NOT NULL UNIQUE PRIMARY AUTO CHECK DEFAULT
ALTER TABLE 表名称 MODIFY 字段名 数据类型 XXXX;

-- 等价于表级约束
-- 所有的多列约束都可以：UNIQUE PRIMARY FROEIGN CHECK
ALTER TABLE 表名称 ADD [CONSTRAINT 约束名] XXXX(列名1, 列名2);

后期删除约束

1
2
3
4
5
6
7
8
9
10
11
-- 只有三个只支持单列约束的可以通过去掉列后面的 约束 来删除约束。
-- 如果有多个下面的单列约束，那么记得写上要保留的约束
-- NOT NULL DEFAULT AUTO_INCREMENT
ALTER TABLE 表名称 MODIFY 字段名 数据类型; 

-- 只能用于 UNIQUE
-- UNIQUE 作用的列会自动变为表的索引，所以删掉索引就等于去掉 UNIQUE
ALTER TABLE 表名称 DROP INDEX 约束名;

-- 作用于 PRIMARY（主键约束只有一个，不需要写约束名） CHECK FOREIGN
ALTER TABLE 表名称 DROP XXXX 约束名;

对于删除需要约束名的，可以先查一查约束，再删除：

1
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';

非空约束 NOT NULL

  • 默认，所有的类型的值都可以是 NULL，包括 INT、FLOAT 等数据类型
  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
  • 一个表可以有很多列都分别限定了非空
  • 空字符串 ‘’ 不等于 NULL，0 也不等于 NULL
  • 单列约束，写在列定义的后面。

设置

新增：因为是只支持单列约束，所以只有一种。

1
ALTER TABLE test01 MODIFY username VARCHAR(10) NOT NULL;

删除：只支持单列约束，去掉即删除。

1
ALTER TABLE test01 MODIFY username VARCHAR(10);

唯一性约束 UNIQUE

  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
  • MySQL 会给唯一约束的列上默认创建一个唯一索引。
  • 约束多个列的复合唯一约束，作用为多个列拼起来的唯一。

设置

增加：同时支持单列和多列约束

1
2
3
ALTER TABLE test01 MODIFY id INT UNIQUE;

ALTER TABLE test01 ADD [CONSTRIANT 约束名] UNIQUE(id,username...);

删除：

  • 添加唯一性约束的列上也会自动创建唯一索引。
  • 删除唯一约束只能通过删除唯一索引的方式删除。
  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

删之前可以先看看有哪些约束：

1
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';

然后再删除：

1
ALTER TABLE 表名称 DROP INDEX 约束名称;

主键约束 PRIMARY KEY

  • 主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  • MySQL 的主键名总是 PRIMARY，就算自己命名了主键约束名也没用。
  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
  • 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

设置

增加：同时支持单列和多列约束

1
2
3
ALTER TABLE test01 MODIFY id INT PRIMARY KEY;

ALTER TABLE test01 ADD [CONSTRIANT 约束名] PRIMARY KEY(id,username...);

删除：不需要指定主键名，因为一个表只有一个主键，删除主键约束后，UNIQUE 和 INDEX 被去掉，非空约束还存在。

1
2
ALTER TABLE 表名称 DROP PRIMARY KEY; -- 删除主键
ALTER TABLE 表名称 MODIFY 列名 类型; -- 删除非空约束

自增列 AUTO_INCREMENT

  • 一个表最多只能有一个自增长列
  • 当需要产生唯一标识符或顺序值时，可设置自增长
  • 自增长列约束的列必须是键列（主键列 primary key，唯一键列 unique）
  • 自增约束的列的数据类型必须是整数类型
  • 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

设置

增加：同时支持单列约束（只有一个增长列！）

1
ALTER TABLE test01 MODIFY id INT AUTO_INCREMENT;

删除：

1
2
ALTER TABLE test91 MODIFY id int; 
--去掉 auto_increment 相当于删除

外键约束 FOREIGN KEY

用来限定某个表的某个字段的引用完整性。

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列。因为被依赖/被参考的值必须是唯一的

（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

（4）删表时，先删从表（或先删除外键约束），再删除主表

（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。例如：都是表示部门编号，都是int类型。

（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（所以除了通过主键定位数据，外键定位数据效率也很高）

（9）删除外键约束后，必须手动删除对应的索引

设置

新建表时：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table 主表名称(
    字段1  数据类型  primary key,
    字段2  数据类型
);
create table 从表名称(
    字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
-- (从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
-- (从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

新增：

1
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

删除：因为外键约束

1
2
3
4
5
6
7
8
9
-- (1) 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

-- （2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; 

ALTER TABLE 从表名 DROP INDEX 索引名;

约束等级

  • Cascade方式：在父表上 update/delete 记录时，同步 update/delete 掉子表的匹配记录

  • Set null方式：在父表上 update/delete 记录时，将子表上匹配记录的列设为 null，但是要注意子表的外键列不能为 not null

  • No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行 update/delete 操作

  • Restrict方式：同 no action， 都是立即检查外键约束

  • Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

默认是 Restrict 方式，报错就完了。

可以添加外键的时候，在最后加上 on update 等级名称 和 on delete 等级名称 来自定义约束等级。

举个例子：

1
2
3
4
5
6
7
8
9
10
11
12
create table dept(
    did int primary key,	-- 部门编号
    dname varchar(50)		-- 部门名称
);

create table emp(
    eid int primary key,    -- 员工编号
    ename varchar(5),       -- 员工姓名
    deptid int,				-- 员工所在的部门
    foreign key (deptid) references dept(did)  on update cascade on delete set null
    -- 把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

检查约束 CHECK

这个是 MySQL8 才支持的新功能。

1
2
3
4
5
create table 主表名称(
    字段1  数据类型  check(EXPRESSION), -- 列约束，表达式里只能出现此列的字段
    字段2  数据类型,
    check(EXPRESSION) -- 表约束，可以有很多列的综合表达式
);

比如:

1
2
3
4
5
6
7
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 18), -- 必须成年
PRIMARY KEY(id),
CHECK(age <= 30) -- 不能大于三十岁
);

设置

新增：支持两种

1
2
ALTER TABLE 表名 MODIFY 列名 类型 CHECK(表达式);
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] CHECK(表达式);

删除（删之前查一查约束名）：

1
ALTER TABLE 表名 DROP CHECK 约束名;

默认约束 DEFAULT

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

1
2
3
4
5
6
7
create table 表名称(
    字段名  数据类型 default 默认值 ,
    字段名  数据类型 not null default 默认值,  
    字段名  数据类型 not null default 默认值,
    primary key(字段名),
    unique key(字段名)
);

设置

新增：只支持列约束

1
ALTER TABLE 表名 MODIFY 列名 类型 DEFAULT 默认值;

删除：去掉即可

1
ALTER TABLE 表名 MODIFY 列名 类型;