很楠不爱3

这个屌丝很懒，什么也没留下！

热门标签

25.10 MySQL 约束

作者：很楠不爱3 | 2024-04-05 19:47:05

踩

25.10 MySQL 约束

1. 数据完整性约束

1.1 数据完整性

数据完整性是数据库设计的核心原则之一, 它确保了数据的精确性和可靠性, 从而维护了数据库的质量.
为了防止数据库中存储不符合语义规定的数据, 以及防止由于错误信息的输入或输出导致的无效操作或错误信息的产生, 引入了约束这一概念.

SQL规范通过约束对表数据进行额外的条件限制, 以确保数据的完整性和一致性.
这些约束在多个层面上起到了关键作用, 具体如下:
* 1. 实体完整性(Entity Integrity):
     实体完整性保证了表中的每一行数据都是唯一的, 可以被准确地识别和区分.
     例如, 在一个包含员工信息的表中, 每个员工都应该有一个唯一的标识, 如员工ID, 以确保不会有两条完全相同的记录存在.
     这有助于防止数据冗余和混淆.

* 2. 域完整性(Domain Integrity):
     域完整性关注的是数据列中值的范围或格式. 它确保了数据列中的值符合预定义的标准或条件.
     例如, 年龄列的值应该限制在0到120之间, 性别列的值只能是'男'或'女'.
     这些限制有助于防止无效或不合理的数据进入数据库.

* 3. 引用完整性(Referential Integrity):
     引用完整性确保了两个表之间的关系得到正确的维护.
     当一个表中的记录引用另一个表中的记录时, 引用完整性要求被引用的记录必须存在.
     例如, 员工表中的部门字段必须对应于部门表中实际存在的部门记录.
     这有助于保持数据的一致性, 并防止由于引用不存在的记录而导致的错误.

* 4. 用户自定义完整性(User-defined Integrity):
     除了上述标准约束外, 用户还可以根据特定的业务需求定义自定义的完整性规则.
     这些规则可以根据特定的业务逻辑或条件对数据进行限制.
     例如, 可以规定用户名必须是唯一的, 密码字段不能为空, 或者部门经理的工资不得超过本部门员工平均工资的某个倍数.
     这些自定义完整性规则有助于确保数据库中的数据符合特定的业务要求.

综上所述, 约束在数据库设计中扮演着至关重要的角色.
它们确保了数据的完整性和一致性, 从而提高了数据库的质量和可靠性.
通过合理地应用这些约束, 我们可以有效地防止数据错误和无效操作的发生, 保护数据库免受潜在的风险和损害.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

1.2 约束类型

约束是数据库表级的重要强制规定, 它们确保了表中数据的完整性和准确性.
常见的约束类型包括:
* 1. NOT NULL 非空约束: 确保字段不能为空, 即必须包含值.
* 2. UNIQUE 唯一约束: 确保字段或字段组合在表中的值是唯一的, 但不排除NULL值(除非列被定义为NOT NULL).
* 3. PRIMARY KEY 主键约束: 标识表中的唯一记录, 一个表只能有一个主键, 且主键字段的值不能为空.
* 4. FOREIGN KEY 外键约束: 定义了两个表之间的关联关系, 确保子表中的记录所引用的父表记录确实存在.
* 5. DEFAULT 默认值约束: 为列提供默认值, 当插入新记录但没有为该列提供值时, 将使用此默认值
这些约束类型可以根据具体的应用场景和需求进行选择和组合, 以确保数据库中的数据完整性和准确性.
1
2
3
4
5
6
7
8

根据不同的标准和需求, 可以分为多种类型:
* 1. 根据约束数据列的限制, 约束可分为:
     1. 单列约束: 这种约束作用于表中的单个列, 确保该列的数据满足特定的条件或规则.
        例如, NOT NULL约束就是一个典型的单列约束, 它要求该列不能有NULL值.
     2. 多列约束: 与单列约束不同, 多列约束涉及表中的多个列, 确保这些列的组合满足特定的条件或规则.
        例如, UNIQUE约束可以应用于多个列的组合, 以确保这些列的组合值在整个表中是唯一的.

* 2. 根据约束的作用范围, 约束可分为:
     1. 列级约束: 列级约束仅对表中的单个列起作用, 它们直接定义在列的数据类型之后.
        这种约束是针对特定列的, 并且只影响该列的数据.
        列级约束既可以在创建表时定义, 也可以在表创建后通过ALTER TABLE语句添加.
     2. 表级约束: 与列级约束不同, 表级约束可以作用于表中的多个列, 它们不与特定的列一起定义, 而是作为表的独立部分进行定义.
        表级约束通常用于定义跨多个列的关系或条件, 例如外键约束.
1
2
3
4
5
6
7
8
9
10
11
12
13

在创建表时, 可以直接通过CREATE TABLE语句来定义这些约束, 以在数据插入或更新时实施特定的规则和条件.
同样地, 如果表已经存在, 也可以使用ALTER TABLE语句来添加, 修改或删除约束.
1
2

1.3 查看约束

information_schema是一个特殊的数据库, 它包含了关于其他所有数据库和表的元数据信息.
table_constraints表是information_schema中用于存储关于各个表的约束信息.

下面是一个SQL查询的示例, 该查询会返回指定表(比如your_table_name)的所有约束:
SELECT *   
FROM information_schema.table_constraints   
WHERE table_schema = 'your_database_name'   
AND table_name = 'your_table_name';
请注意, 需要替换your_database_name为你的实际数据库名称, 以及your_table_name为你想要查询的表名.
1
2
3
4
5
6
7
8
9

-- 查看employees表的约束:
mysql> SELECT *
FROM information_schema.table_constraints
WHERE table_schema = 'atguigudb'
AND table_name = 'employees';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | atguigudb         | emp_email_uk    | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | emp_emp_id_pk   | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | PRIMARY         | atguigudb    | employees  | PRIMARY KEY     | YES      |
| def                | atguigudb         | emp_dept_fk     | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_job_fk      | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_manager_fk  | atguigudb    | employees  | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

列名解释:
CONSTRAINT_CATALOG: 约束目录名(在MySQL中通常为def).
CONSTRAINT_SCHEMA: 约束所在的数据库名.
CONSTRAINT_NAME: 约束的名称.
TABLE_SCHEMA: 表所在的数据库名.
TABLE_NAME: 约束所属的表名.
CONSTRAINT_TYPE: 约束的类型(如PRIMARY KEY,  FOREIGN KEY, UNIQUE等).
ENFORCED: 约束是否被强制执行 (通常为YES).

行数据解释:
emp_email_uk:   唯一, 确保employees表中的email列的值唯一.
emp_emp_id_pk:  主键, 确保employees表中的emp_id列的值是唯一的, 并且不为NULL.
emp_dept_fk:    外键, 确保employees表中的department_id列的值在departments表的dept_id列中存在.
emp_job_fk:     外键, 确保employees表中的job_id列的值在jobs表的job_id列中存在.
emp_manager_fk: 外键, 确保employees表中的manager_id列的值在employees表的employee_id列中存在.
PRIMARY: 每个主键约束都应该有一个唯一的名称, 而不是简单地使用PRIMARY.
但在某些数据库系统或特定的数据库实现中, 如果主键约束没有被显式地命名, 系统可能会自动为其分配一个默认名称, 如PRIMARY.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2. 非空约束

2.1 特性

非空约束(NOT NULL Constraint): 是数据库中的一种约束, 用于确保表中某列不允许为空值.
其主要特性包括:
* 1. 不可为空: 设置了非空约束的字段在插入或更新数据时, 必须提供有效的值, 不能留空.
     如果尝试插入或更新一个空值, 数据库系统会报错并拒绝这些操作.
* 2. 维护数据完整性: 非空约束通过确保特定列始终包含有效值, 从而维护数据的完整性和一致性.
     它有助于防止因空值导致的数据错误或不一致问题.
* 3. 强制实施: 非空约束是强制性的, 数据库系统会自动检查并应用这一约束, 无需用户手动检查或干预.

在实际应用中, 非空约束常常用于那些必须提供值的字段, 例如用户信息表中的用户名, 邮箱等字段.
通过设置非空约束, 可以确保这些字段在插入或更新记录时始终包含有效的数据, 从而提高数据的质量和可靠性.
1
2
3
4
5
6
7
8
9
10

2024-03-29_110912

2.2 定义非空约束

在MySQL中, 定义非空约束的步骤通常是在创建表的过程中, 直接在列定义中加上NOT NULL关键字.

以下是定义非空约束的步骤:
* 1. 使用CREATE TABLE语句开始创建新表.
* 2. 在列定义中, 对于需要定义非空约束的列, 加上NOT NULL关键字.

语法格式:
CREATE TABLE table_name (  
    column1 datatype NOT NULL,  
    column2 datatype NOT NULL,  
    column3 datatype,  
    ...  
);

具体例子: 
例如, 我们要创建一个名为employees的表, 其中, emp_id, first_name, last_name和email列都不能有NULL值.
CREATE TABLE employees (  
    emp_id INT NOT NULL,  
    first_name VARCHAR(50) NOT NULL,  
    last_name VARCHAR(50) NOT NULL,  
    email VARCHAR(100) NOT NULL,  
    ...
);

注意事项:
一旦表被创建并且列被定义为非空, 你就不能再向这些列插入NULL值, 除非你先修改表结构来移除这些列的非空约束.
如果尝试插入NULL值到定义为非空的列中, MySQL将抛出一个错误.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 创建数据库:
mysql> CREATE DATABASE Integrity;
Query OK, 1 row affected (0.01 sec)

-- 使用数据库:
mysql> USE Integrity;
Database changed

-- 创建数据库:
mysql> CREATE TABLE emp0(
    id int,                     -- 主键编码, 可为空
    name varchar(20) not null,  -- 姓名, 不可为空
    phone char(11) ,            -- 手机号码, 11位, 可为空
    id_card char(18) not null   -- 身份证, 18位,不可为空
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp0;    -- ↓ 值为YES可为空
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| phone   | char(11)    | YES  |     | NULL    |       |
| id_card | char(18)    | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 插入正常数据:
mysql> INSERT INTO emp0 VALUES(1, '张三', '13710011002', '110222198912032545');
Query OK, 1 row affected (0.01 sec)

-- 非空字段插入NULL, 报错:
mysql> INSERT INTO emp0 VALUES(2, '李四', '13710011002', null);  -- 身份证不填写
ERROR 1048 (23000): Column 'id_card' cannot be null

-- 定义一个字段为可为空(即允许NULL值)并且没有为该字段指定默认值时, 
-- 如果在插入新记录时没有为该字段提供具体的值, 那么该字段的值会自动被设置为NULL.
-- 这是数据库管理系统的标准行为, 用于表示该字段在特定记录中没有有效的或已知的值.
mysql> INSERT INTO emp0(id, name, id_card) VALUES(2,'李四', '110222198912032546');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM emp0;
+------+------+-------------+--------------------+
| id   | name | phone       | id_card            |
+------+------+-------------+--------------------+
|    1 | 张三 | 13710011002 | 110222198912032545 |
|    2 | 李四 | NULL        | 110222198912032546 |
+------+------+-------------+--------------------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

2.3 添加非空约束

在SQL中, 如果想在已经存在的表中为某个列添加非空约束(NOT NULL Constraint)需要使用ALTER TABLE语句来修改表的结构.
以下是如何为现有表的列添加非空约束的步骤:
* 1. 使用ALTER TABLE语句指定要修改的表名.
* 2. 使用MODIFY COLUMN子句指定要修改的列.
* 3. 在列的数据类型之后添加NOT NULL约束.

语法格式:
ALTER TABLE table_name  
MODIFY COLUMN column1 datatype NOT NULL;

具体例子: 
示例, 假设我们有一个名为employees的表, 其中有一个名为email的列, 
现在我们想要为这个email列添加非空约束, 确保每个员工都有电子邮件地址.
ALTER TABLE employees  
MODIFY COLUMN email VARCHAR(255) NOT NULL;
在上面的示例中, employees是表名, email是我们想要修改的列名, 
VARCHAR(255)是该列的数据类型, 而NOT NULL则是我们添加的非空约束.

注意事项:
* 1. 在添加非空约束之前, 请确保该列中没有NULL值.
     如果存在NULL值, 需要首先更新这些记录, 将NULL值替换为有效的非空值.
* 2. 如果该列中有NULL值, 并且尝试直接添加非空约束, 数据库将抛出一个错误, 并阻止添加约束.
* 3. 处理NULL值: 在添加非空约束之前, 需要确保该列中的所有现有记录都包含有效的非空值.
     如果列中有NULL值, 可以使用UPDATE语句来替换这些NULL值. 例如:
     UPDATE employees  
     SET email = 'default@example.com'  
     WHERE email IS NULL;
     上面的语句将所有email列为NULL的记录更新为默认值'default@example.com'.
     当然, 应该根据实际情况提供一个合适的默认值或者从其他来源获取有效的电子邮件地址.
     完成更新后, 可以继续执行ALTER TABLE语句来添加非空约束. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- 创建表格:
mysql> CREATE TABLE emp1(
    id INT(10) NOT NULL, 
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20) NULL  -- 可省略NULL关键字
);
Query OK, 0 rows affected, 1 warning (0.02 sec)

-- 查看表结构:
mysql> DESC emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   |     | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| gender | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 为性别字段添加非空约束:
mysql> ALTER TABLE emp1
MODIFY gender VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   |     | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| gender | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2.4 删除非空约束

在MySQL中, 要删除一个列的非空约束, 需要使用ALTER TABLE语句配合MODIFY COLUMN子句来修改该列的定义, 并移除NOT NULL关键字.
以下是如何从MySQL表中删除非空约束的步骤:
* 1. TABLE语句指定要修改的表名.
* 2. 使用MODIFY COLUMN子句指定要修改的列, 并在列定义中移除NOT NULL.


以下是两种移除非空约束的语法格式:
* 1. 将NOT NULL改为NULL:
     ALTER TABLE table_name MODIFY COLUMN column_name datatype NULL;
     这条语句会将table_name表中的column_name列从非空(NOT NULL)修改为允许空值(NULL). 

* 2. 完全省略NOT NULL约束:
     ALTER TABLE table_name MODIFY COLUMN column_name datatype;
     与第一种方法类似, 这条语句也会移除column_name列的非空约束, 允许它包含NULL值.
     在定义列时, 如果不指定NOT NULL, 那么该列默认就是允许NULL值的.
     
具体例子: 
假设我们有一个名为students的表, 并且这个表有一个名为phone_number的列, 该列上设置了一个非空约束.
现在我们想要删除这个非空约束, 以便该列可以接受NULL值.
ALTER TABLE students  
MODIFY COLUMN phone_number VARCHAR(20) NULL;
在上面的示例中, students是表名, phone_number是列名, VARCHAR(20)是该列的数据类型,
而NULL则表示移除了非空约束, 允许该列包含NULL值.

注意事项:
* 1. 确保在删除非空约束之前, 已经考虑了该列中可能存在的NULL值对应用程序逻辑的影响.
* 2. 如果该列之前有非空约束, 删除该约束后插入或更新该列时可以不提供值, MySQL将自动将其设置为NULL(如果未指定其他值).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 修改name字段允许字段为空:
mysql> ALTER TABLE emp1 MODIFY COLUMN name VARCHAR(20) NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改gender字段允许字段为空:
mysql> ALTER TABLE emp1 MODIFY COLUMN gender VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3. 唯一约束

3.1 特性

唯一约束(Unique Key): 用于确保某个列或字段的值在该列或字段中是唯一的.
它主要用于防止表中出现重复的值, 确保数据的完整性和一致性.

主要特性和作用如下:
* 1. 确保数据唯一性: 唯一约束确保表中的一列或多列的组合中的值是唯一的.
     这意味着在这些列上, 不能有两行或更多行具有相同的值.
* 2. 允许NULL值: 唯一约束允许列中有多个NULL值.
     这是因为NULL在数据库中通常表示'未知'或'缺失'的数据, 而两个未知的值并不一定是相同的.
* 3. 可以在多列上定义: 唯一约束不仅可以定义在单个列上, 也可以定义在多个列的组合上.
     这确保了这些列的组合值是唯一的.
* 4. 自动创建唯一索引: 当在MySQL中为某个列或列组合定义唯一约束时, 系统会自动为该列或列组合创建一个唯一索引.
     这个索引可以加速查询操作, 提高数据库性能.
* 5. 在插入和更新时强制执行: 数据库管理器在插入和更新操作期间强制执行唯一约束, 以确保数据完整性.
     如果尝试插入或更新违反唯一约束的数据, 数据库将拒绝这些操作并返回错误.
* 6. 可以命名或默认命名: 当定义唯一约束时, 如果不给约束命名, MySQL会默认使用列名作为约束名.

通过合理使用唯一约束, 可以确保数据库中的数据准确性和一致性, 避免重复数据的出现.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 定义唯一约束

在MySQL中, 定义唯一约束的步骤通常包括在创建表的过程中直接定义.
下面是定义唯一约束的步骤:
* 1. 使用CREATE TABLE语句开始创建新表.
* 2. 在列定义中, 为需要定义唯一约束的列添加UNIQUE关键字.
* 3. 根据需要, 为其他列指定数据类型和其他约束.

常用两种定义方式如下:
* 1. 通用的格式, 用于在创建表时为单个列定义唯一约束:
     CREATE TABLE table_name (  
         column datatype UNIQUE,  
         ...  
     );
     在这个例子中, 列名是你想要添加唯一约束的列的名称, 数据类型是该列的数据类型.
     UNIQUE关键字表明这个列的值必须是唯一的, 即不能有重复值.

* 2. 表级约束的语法, 用于在创建表时为一组列定义唯一约束(即这些列的组合值必须是唯一的):
     CREATE TABLE table_name (  
         column1 datatype,  
         column2 datatype,  
         ...  
         UNIQUE (column1, column2, ...),   
     );
     在这个例子中, 它要求column1, column2等列的组合值在表中是唯一的.

* 在定义时没有写约束名称, 系统默认将字段名设置为约束名称, 这里先省略掉约束名称, 后续单独说明.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3.2.1 单列唯一

示例, 创建一个emp员工信息表, 其中邮箱, 用户名不能够重复:
CREATE TABLE emp(  
    id INT,  
    name VARCHAR(50) UNIQUE  
    email VARCHAR(255) UNIQUE NOT NULL,  
);
在这个例子中, name和email列都被定义为具有唯一约束, 这意味着每个用户的name和email都必须是唯一的.
1
2
3
4
5
6
7

-- 创建表格:
mysql> CREATE TABLE emp2(
    id INT,
    name VARCHAR(20),
    phone CHAR(11) UNIQUE,       -- 不能重复
    id_card CHAR(18) UNIQUE KEY  -- 完整写法 UNIQUE KEY
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp2;              -- ↓ UNI 标识
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| phone   | char(11)    | YES  | UNI | NULL    |       |
| id_card | char(18)    | YES  | UNI | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO emp2 VALUES(1, '张三', '13710011002', '101223199012015623');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp2 VALUES(2, '李四', '13710011003', '101223199012015624');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM emp2;
+------+------+-------------+--------------------+
| id   | name | phone       | id_card            |
+------+------+-------------+--------------------+
|    1 | 张三 | 13710011002 | 101223199012015623 |
|    2 | 李四 | 13710011003 | 101223199012015624 |
+------+------+-------------+--------------------+
2 rows in set (0.00 sec)

-- 插入重复的数据(身份证号重复):
mysql> INSERT INTO emp2 VALUES(3, '王五', '13710011004', '101223199012015624');
ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'emp2.id_card'
-- 'emp2.id_card'中id_card为约束的名称, 在定义时没有写约束名称, 系统默认将字段名设置为约束名称.

-- 插入重复的数据(手机号码):
mysql> INSERT INTO emp2 VALUES(3, '王五', '13710011003' ,'101223199012015625');
ERROR 1062 (23000): Duplicate entry '13710011003' for key 'emp2.phone'
-- 'emp2.phone'中phone为约束的名称.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.2.2 联合唯一

示例, 创建一个users用户表, 有三个列: id, name和password, 其中name和password的组合唯一:
CREATE TABLE users (  
    id INT,  
    name VARCHAR(20),  
    password VARCHAR(20),  
    UNIQUE (name, password) -- 联合唯一约束应用于name和password的组合  
);

在这个例子中, name和password中的单个列的值可以在其他行中重复, 但是它们的组合必须是唯一的.
1
2
3
4
5
6
7
8
9

-- 创建表格:
mysql> CREATE TABLE users(
    id INT,
    name VARCHAR(25),
    password VARCHAR(16),
    -- 使用表级约束语法, 表示用户名和密码组合不能重复:
    UNIQUE(name, password)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC users;               -- ↓ MUL 标识联合唯一
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(25) | YES  | MUL | NULL    |       |
| password | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 插入数据:
mysql> INSERT INTO users VALUES(1, 'kid', '123456');
Query OK, 1 row affected (0.01 sec)

-- 单列可重复:
mysql> INSERT INTO users VALUES(2, 'kid', '654321');
Query OK, 1 row affected (0.00 sec)

-- 单列可重复:
mysql> INSERT INTO users VALUES(3, 'qq', '123456');
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM users;
+------+------+----------+
| id   | name | password |
+------+------+----------+
|    1 | kid  | 123456   |
|    2 | kid  | 654321   |
|    3 | qq   | 123456   |
+------+------+----------+
3 rows in set (0.00 sec)

-- 插入联合唯一重复的数据:
mysql> INSERT INTO users VALUES(4, 'kid', '123456');
ERROR 1062 (23000): Duplicate entry 'kid-123456' for key 'users.name'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3.3 添加唯一约束

在MySQL中, 可以使用ALTER TABLE搭配ADD UNIQUE或MODIFY子句修改表结构, 为某个列或列组合添加唯一约束.

两种语法使用方式如下:
* 1. ADD UNIQUE添加唯一约束, ALTER TABLE table_name ADD UNIQUE (column);
     这条语句将为column列添加一个唯一约束, 确保每个用户的column值都是唯一的.
     这种方法适用于已经存在的列, 并且你想要为它添加一个唯一约束.

* 2. 使用MODIFY子句修改列并添加唯一约束: ALTER TABLE users MODIFY table_name datatype UNIQUE;
     这条语句将email列的数据类型更改为VARCHAR(20)，并添加了一个唯一约束。
     如果你不仅需要修改列的数据类型, 还要同时添加唯一约束, 可以使用MODIFY子句.

注意事项:
* 1. 如果列已经存在, 并且你只是想要添加唯一约束而不改变数据类型, 那么通常使用ADD UNIQUE是更直接的方法.
* 2. 在添加唯一约束之前, 确保列中没有重复的值, 否则操作会失败.
     可以使用SELECT语句和GROUP BY来检查重复值.
     如果可能的话, 在创建表的时候就定义唯一约束, 这样可以避免后续的数据完整性问题.
* 3. 如果你的表非常大, 添加唯一约束可能会需要一些时间, 并且可能会锁定表, 影响性能.
     在这种情况下, 最好在低峰时段执行此操作.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 创建表格:
mysql> CREATE TABLE emp3(
    id INT,
    name VARCHAR(20),
    phone CHAR(11),
    id_card CHAR(18)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| phone   | char(11)    | YES  |     | NULL    |       |
| id_card | char(18)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 为name字段添加单列唯一:
mysql> ALTER TABLE emp3 ADD UNIQUE(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将phone和id_card设置为联合唯一:
mysql> ALTER TABLE emp3 ADD UNIQUE(phone, id_card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  | UNI | NULL    |       |
| phone   | char(11)    | YES  | MUL | NULL    |       |
| id_card | char(18)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 添加数据:
mysql> INSERT INTO emp3 VALUES(1, 'kid', '13710011004', '101223199012015624');
Query OK, 1 row affected (0.01 sec)

-- 插入与name重复的数据:
mysql> INSERT INTO emp3 VALUES(2, 'kid', '13710011005', '101223199012015625');
ERROR 1062 (23000): Duplicate entry 'kid' for key 'emp3.name'

-- 插入与phone, id_card组合的重复的数据:
mysql> INSERT INTO emp3 VALUES(2, 'qq', '13710011004', '101223199012015624');
ERROR 1062 (23000): Duplicate entry '13710011004-101223199012015624' for key 'emp3.phone'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

3.4 删除唯一约束

在MySQL中, 要删除一个唯一约束, 需要知道该约束的名称.
通常, 当定义表格或使用ALTER TABLE ... ADD UNIQUE语句添加一个唯一约束时, 
MySQL会为该约束生成一个默认名称, 但这个名称可能并不直观, 因此难以直接引用.

如果你没有为唯一约束指定名称, 或者你不知道它的名称, 
可以通过查询information_schema数据库中的KEY_COLUMN_USAGE表来找到它.
以下是一个如何找到并删除唯一约束的步骤:
* 1. 查找唯一约束的名称.
     首先. 需要找到你想要删除的唯一约束的名称, 这可以通过查询information_schema数据库来实现:
     SELECT CONSTRAINT_NAME  
     FROM information_schema.TABLE_CONSTRAINTS  
     WHERE TABLE_SCHEMA = 'your_database_name'  
     AND TABLE_NAME = 'your_table_name'  
     AND CONSTRAINT_TYPE = 'UNIQUE';

     将your_database_name替换为你的数据库名称, 将your_table_name替换为你的表名.
     这个查询将返回该表上所有唯一约束的名称.

* 2. 删除唯一约束.
     一旦你知道了唯一约束的名称, 就可以使用ALTER TABLE ... DROP INDEX语句来删除它.
     注意, 即使你添加的是一个唯一约束, MySQL内部也是将它作为索引来处理的, 所以你需要使用DROP INDEX而不是DROP UNIQUE.
     ALTER TABLE your_table_name DROP INDEX constraint_name;
     将your_table_name替换为你的表名, 将constraint_name替换为你在第一步中找到的唯一约束的名称.
     
如果你在添加唯一约束时指定了自定义名称, 那么你可以直接使用这个名称来删除它.
例如: ALTER TABLE users DROP INDEX unique_email;
在这个例子中, unique_email是唯一约束的自定义名称.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 查看约束名称:
mysql> SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'integrity'
AND TABLE_NAME = 'emp3'
AND CONSTRAINT_TYPE = 'UNIQUE';
+-----------------+
| CONSTRAINT_NAME |
+-----------------+
| name            | -- 单列唯一
| phone           | -- 联合唯一
+-----------------+
2 rows in set (0.00 sec)

-- 查看表的索引(看Key_name列):
SHOW INDEX FROM emp3; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2024-03-29_204300

-- 删除单列唯一:
mysql> ALTER TABLE emp3 DROP INDEX name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 插入与name重复的数据:
mysql> INSERT INTO emp3 VALUES(2, 'kid', '13710011005', '101223199012015625');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM emp3;
+------+------+-------------+--------------------+
| id   | name | phone       | id_card            |
+------+------+-------------+--------------------+
|    1 | kid  | 13710011004 | 101223199012015624 |
|    2 | kid  | 13710011005 | 101223199012015625 |
+------+------+-------------+--------------------+
2 rows in set (0.00 sec)

-- 删除联合唯一:
mysql> ALTER TABLE emp3 DROP INDEX phone;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 插入与phone, id_card组合的重复的数据:
mysql> INSERT INTO emp3 VALUES(3, 'qq', '13710011004', '101223199012015624');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql>  SELECT * FROM emp3;
+------+------+-------------+--------------------+
| id   | name | phone       | id_card            |
+------+------+-------------+--------------------+
|    1 | kid  | 13710011004 | 101223199012015624 |
|    2 | kid  | 13710011005 | 101223199012015625 |
|    3 | qq   | 13710011004 | 101223199012015624 |
+------+------+-------------+--------------------+
3 rows in set (0.00 sec)

-- 查看表结构:
mysql> DESC emp3;             -- ↓ 被清除
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| phone   | char(11)    | YES  |     | NULL    |       |
| id_card | char(18)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

4. 主键约束

4.1 特性

主键约束(PRIMARY KEY): 用于确保数据表中的数据唯一性的重要工具.
当为一个表或表的列定义 PRIMARY KEY 约束时, DBMS会自动为这些列创建一个唯一索引, 该索引不允许任何重复值, 并且不允许NULL值.

它具备以下几个关键的约束特性:
* 1. 唯一性: 主键约束要求主键列的值在表中必须是唯一的.
     不允许有重复值这保证了表中每一行数据的唯一性, 有助于维护数据的完整性和一致.
* 2. 非空性: 主键列的值不能为空.
      这是因为主键的主要作用是标识表中的每一行数据, 如果允许主键值为空, 那么将无法准确地标识每一行数据.
* 3. 数据完整性保护: 主键约束防止了表中出现重复的记录, 确保了数据的完整性.
     无法插入具有相同主键值的记录, 从而避免了数据冗余和不一致.
* 4. 加速数据访问: 由于主键的唯一性, 数据库引擎可以使用主键来加速数据的查找和连接操作, 提高数据访问的性能.
     这使得主键在查询优化中起到了关键的作用.
* 5. 外键关联: 主键约束通常用于定义外键关系, 将不同表之间的记录关联起来, 建立数据库的关联性.
     这使得可以实现更复杂的数据查询和管理操作, 提高数据库的灵活性和可扩展性.
* 6. 引用完整性: 在关系数据库中, PRIMARY KEY 常常与其他表的 FOREIGN KEY 约束一起使用, 以确保引用完整性.
综上所述, 主键约束通过确保数据的唯一性, 非空性, 完整性以及加速数据访问等特性, 为数据库的设计和查询优化提供了重要的支持.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

在数据库设计中, 设置id列并将它设置为主键是一个常见的做法, 主要基于以下几个原因:
* 1. 唯一性: 主键的主要作用是确保表中每一行数据的唯一性.
     通过为主键列(如id)分配唯一的值, 可以确保不会有两行数据具有相同的标识.
* 2. 引用完整性: 当在多个表之间建立关系时(例如, 通过外键约束), 主键是确保数据引用完整性的关键.
     其他表可以通过外键引用这个主键, 从而确保数据的关联性和一致性.
* 3. 性能优化: 数据库系统通常为主键列自动创建唯一索引.
     这个索引可以极大地加速基于主键的查询操作, 因为数据库系统可以利用这个索引快速定位到所需的数据行.
* 4. 简化数据操作: 使用主键可以简化数据的插入, 更新和删除操作.
     例如, 当需要更新或删除特定行时, 可以通过主键快速定位到该行.
* 5. 易于理解: 使用id作为主键列名是一个通用的命名约定, 这使得其他开发者或数据库管理员更容易理解和维护数据库结构.
* 6. 自增特性: 在很多数据库系统中, id列可以设置为自增(AUTO_INCREMENT), 
     这意味着每当插入新行时, 系统会自动为这个列生成一个新的, 唯一的值, 无需手动指定/
     这进一步简化了数据插入操作.
* 7. 避免数据泄露: 使用自增的整数id作为主键, 而不是使用敏感信息(如用户名或邮箱地址)作为主键, 有助于保护用户隐私和数据安全.

综上所述, 设置id列并将其设置为主键是数据库设计中的一个基本和重要的原则, 它有助于确保数据的唯一性, 完整性, 安全性和查询性能.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2024-03-29_114816

4.2 定义主键约束

在创建表时, 可以直接在列定义后面使用PRIMARY KEY关键字来定义主键约束. 
常用的几种定义方式如下:
* 1. 在列级别定义主键约束.
     CREATE TABLE table_name (  
         column1 datatype PRIMARY KEY,  
         column2 datatype,  
         ...  
     );
     
* 2. 在表级别定义单列主键约束:
     CREATE TABLE table_name (  
         column1 datatype,  
         column2 datatype,  
         ...,  
         PRIMARY KEY (column_name)  
     );
     
* 3. 在表级别定义复合主键约束(多列组合):
     CREATE TABLE table_name (  
         column1 datatype,  
         column2 datatype,  
         ...,  
         PRIMARY KEY (column1, column2, ...)  
     );
     
注意事项:
* 1. 一个表只能有一个 PRIMARY KEY: 但可以使用复合主键, 即多个列的组合作为主键.
* 2. 列级别与表级别创建: 主键约束可以在列级别创建, 即在定义列的时候直接指定该列为主键;
     也可以在表级别创建, 即先定义列, 然后在表定义结束后使用PRIMARY KEY约束来指定主键.     
* 3. 主键名: 在MySQL中, 主键约束的名称默认为PRIMARY, 
     即使尝试为其指定其他名称, 系统也会忽略这一命名, 并始终使用PRIMARY作为主键约束的名称.
* 4. 加速查询: 由于 PRIMARY KEY 会自动创建唯一索引, 因此使用 PRIMARY KEY 列进行查询通常会更快.
     当创建主键约束时, 系统会自动在相应的列或列组合上创建一个唯一索引.
     这个索引可以加速基于主键的查询操作, 如果删除了主键约束, 相应的索引也会被自动删除.    
* 5. 主键值的修改: 通常不建议修改主键字段的值, 因为主键是数据记录的唯一标识.
     修改主键的值可能会破坏数据的完整性, 并可能导致与其他表的外键约束冲突.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

4.2.1 单列主键

例如, 有一个名为 student4 的表, 其中每个学生都有一个唯一的学号.
可以将 student_id 列设置为 PRIMARY KEY, 如下所示:
CREATE TABLE student4 (  
    student_id INT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT  
);

在这个例子中, student_id 列是 students 表的主键, 这意味着每个学生的 student_id 都必须是唯一的, 并且不能是 NULL.
如果你尝试插入具有重复 student_id 的行或具有 NULL student_id 的行, DBMS 会抛出一个错误.
1
2
3
4
5
6
7
8
9
10

-- 创建表格:
CREATE TABLE emp4(
    id INT PRIMARY KEY,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp4;             -- ↓ 主键标识
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO emp4 VALUES(1, '张三');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp4 VALUES(2, '李四');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM emp4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- 错误示例1, 插入主键重复的数据:
mysql> INSERT INTO emp4 VALUES(1, '张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'emp4.PRIMARY'
-- emp1.PRIMARY 为约束的名称

-- 错误示例2, 主键为空:
mysql> INSERT INTO emp4 VALUES(null, '张三');
ERROR 1048 (23000): Column 'id' cannot be null
1
2
3
4
5
6
7
8

4.2.2 联合主键

下面是一张成绩单, 这个表格包含了id, 学生姓名(name), 课程(courses)以及学生在该课程中的得分(score).
每一行代表一个学生在一门课程中的得分情况.
1
2

`id`	`name`	`courses`	`score`
`1`	`张三`	`Java`	`89`
`2`	`张三`	`MySQL`	`90`
`3`	`李四`	`Java`	`88`
`4`	`李四`	`MySQL`	`56`

表格中数据出现重复, 可将表格拆分三张表:
* 1. 存放学生信息的学生表.
* 2. 存放课程信息的课程表.
* 3. 存放学生信息与课程信息对应关系的关联表.
1
2
3
4

学生表(student)		课程表(courses)		关联表
`sid`	`sname`	`cid`	`cname`	`mid`	`sid`	`cid`	`score`
`1`	`张三`	`1`	`Java`	`1`	`1`	`1`	`89`
`2`	`李四`	`2`	`MySQL`	`2`	`1`	`2`	`90`
				`3`	`2`	`1`	`88`
				`4`	`2`	`2`	`56`

差分后, 当数据量庞大时能够避免数据的冗余.
1

-- 创建学生表:
mysql> CREATE TABLE student0(
    sid INT PRIMARY KEY,  -- 将id设置为主键
    sname VARCHAR(20)
);
Query OK, 0 rows affected (0.01 sec)

-- 查看表结构:
mysql> DESC student0;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int         | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- 插入数据
mysql> INSERT INTO student0 VALUES (1, '张三'), (2, '李四');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM student0;
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三  |
|   2 | 李四  |
+-----+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 创建课程表:
mysql> CREATE TABLE courses0(
    cid INT PRIMARY KEY,  -- 将id设置为主键
    cname VARCHAR(25)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC courses0;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | int         | NO   | PRI | NULL    |       |
| cname | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO courses0 VALUES (1, 'Java') , (2, 'Python');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM courses0;
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | Java   |
|   2 | Python |
+-----+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 创建第三张表:
mysql> CREATE TABLE student_course0(
    mid INT,
    sid INT,
    cid INT,
    score INT,
    PRIMARY KEY(sid, cid)  -- 联合主键, 他们的组合不能出现重复.
);
Query OK, 0 rows affected (0.03 sec)

-- 查看表结构:
mysql> DESC student_course0;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| mid   | int  | YES  |     | NULL    |       |
| sid   | int  | NO   | PRI | NULL    |       |
| cid   | int  | NO   | PRI | NULL    |       |
| score | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO student_course0 VALUES
(1, 1, 1, 89), (2, 1, 2, 90), (3, 2, 1, 88), (4, 2, 2, 56);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM student_course0;
+------+-----+-----+-------+
| mid  | sid | cid | score |
+------+-----+-----+-------+
|    1 |   1 |   1 |    89 |
|    2 |   1 |   2 |    90 |
|    3 |   2 |   1 |    88 |
|    4 |   2 |   2 |    56 |
+------+-----+-----+-------+
4 rows in set (0.00 sec)

-- 插入重复的数据, 报错:
mysql> INSERT INTO student_course0 values(5, 1, 1, 100);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'student_course0.PRIMARY'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

4.3.3 联合主键与联合唯一

联合主键(Composite Primary Key)和联合唯一(Composite Unique Constraint)在数据库设计中都用于确保表中数据的唯一性,
但它们有一些关键的区别:
* 1. 联合主键(Composite Primary Key): 联合主键是由多个字段组合而成的主键, 用于唯一标识表中的每一行记录.
     这意味着这些字段的组合值在表中必须是唯一的, 并且不允许为空.
     联合主键具有以下特点:
     1. 唯一性: 联合主键的字段组合值在表中必须是唯一的.
     2. 非空性: 联合主键的字段不允许为空.
     3. 唯一标识性: 联合主键是一个由多个字段组成的复合结构, 作为表中唯一的标识符, 用于唯一标识表中的每一行记录.
        在一个表中, 无论是使用单个字段还是多个字段作为主键, 都只能定义一个主键.
     
* 2. 联合唯一(Composite Unique Constraint): 联合唯一约束用于确保表中多个字段的组合值是唯一的, 
     但与联合主键不同, 联合唯一约束的字段可以包含空值(NULL), 并且一个表可以有多个联合唯一约束.
     联合唯一约束的特点如下:
     1. 唯一性: 联合唯一的字段组合值在表中必须是唯一的.
     2. 允许空值: 联合唯一的字段可以包含空值(NULL), 但空值之间不视为重复.
     3. 多个约束: 一个表可以有多个联合唯一约束.

使用场景:
联合主键通常用于那些自然就是唯一组合的场景, 比如多对多关系中的中间表, 其中两个外键的组合能够唯一标识一个关系.
联合唯一则更多用于那些需要确保某些字段组合唯一, 但不需要作为主键的场景.
例如, 你可能想要确保用户的邮箱和手机号组合是唯一的, 但不需要将它们作为主键.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4.3 增加主键约束

在MySQL中, 可以使用ALTER TABLE语句为以存在的表表添加一个主键约束(无论是单列还是多列联合).
以下是一个添加主键约束的示例语法:

* 1. 添加单列添加:
     ALTER TABLE table_name  
     ADD PRIMARY KEY (column1);
     
* 2. 添加联合主键:
     ALTER TABLE students  
     ADD PRIMARY KEY (column1, column2, ...);

注意事项:
* 1. 一个表只能有一个主键.
     如果你尝试为一个已经拥有主键的表添加另一个主键, 数据库管理系统(DBMS)会返回一个错误并指出该表已经有一个主键.
* 2. 在尝试给已存在的表添加主键约束之前, 确保表中的数据满足主键的唯一性和非空性要求; 否则, 会收到一个错误.
     如果正在尝试添加的主键约束与表中现有的数据冲突(例如, 有重复的值或空值), 
     需要先更新或删除那些不符合主键要求的数据, 然后再尝试添加主键约束.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 添加单列主键.
-- 创建一个表格:
mysql> CREATE TABLE student1(
    id INT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 将id设置为主键:
mysql> ALTER TABLE student1 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 添加联合主键.
-- 创建一张表:
mysql> CREATE TABLE student_course1(
    mid INT,
    sid INT,
    cid INT,
    score INT
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC student_course1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| mid   | int  | YES  |     | NULL    |       |
| sid   | int  | YES  |     | NULL    |       |
| cid   | int  | YES  |     | NULL    |       |
| score | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 设置联合主键:
mysql> ALTER TABLE student_course1 ADD PRIMARY KEY (sid, cid);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC student_course1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| mid   | int  | YES  |     | NULL    |       |
| sid   | int  | NO   | PRI | NULL    |       |
| cid   | int  | NO   | PRI | NULL    |       |
| score | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

4.4 删除主键约束

在MySQL中, 不能直接删除一个主键, 但可以通过修改表来移除它.
以下是移除主键的示例语法:
ALTER TABLE 表名 DROP PRIMARY KEY;

移除主键约束, 不需要指定主键名, 因为一个表只有一个主键, 删除主键约束后, 非空特性还存在.

注意事项:
* 在删除主键之前, 请确保没有依赖于该主键的外键约束, 否则删除操作可能会失败.
  因为外键约束要求相关联的值必须存在于引用表中的主键列中.
1
2
3
4
5
6
7
8
9

-- 创建表格:
mysql> CREATE TABLE student2(
    id INT PRIMARY KEY
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC student2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- 移除主键:
mysql> ALTER TABLE student2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看结构(非空特性还存在):
mysql> DESC student2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

5. 自增属性

自增属性(AUTO_INCREMENT): 在MySQL中是一个用于生成唯一标识符的特性, 通常与主键一起使用.
当为一个表的主键列设置自增属性时, MySQL会自动为新插入的每一行生成一个唯一的数字.
这使得开发者无需手动为每一行指定一个主键值, 从而简化了数据插入的过程.
1
2
3

在MySQL中, 自增列通常使用 AUTO_INCREMENT 关键字来定义.
其语法格式如下:
CREATE TABLE table_name (  
    column_name datatype AUTO_INCREMENT,  
    ...  
);

以下是关于自增属性的一些关键点:
* 1. 通常与主键一起使用: 自增属性经常与主键约束一起使用.
     主键是表中的一个或多个字段的组合, 用于唯一标识表中的每一行.
     通过将自增属性应用于主键字段, 可以确保主键值的唯一性和自动递增性.

* 2. 数据类型限制: 自增属性通常与整数数据类型(如INT, BIGINT等)一起使用.
     这是因为自增值需要是数值型的, 以便能够自动递增.
     
* 3. 一个表只能有一个自增字段: 在MySQL中, 一个表只能有一个字段被设置为自增.
     这是为了确保自增值的唯一性和一致性.
     
* 4. 唯一性: 每次插入新记录时, 如果没有为自增字段指定值或指定为NULL, 0, MySQL会自动为该字段生成一个唯一的数值.
     这个数值通常比前一条记录的自增值大1.

* 5. 自动递增: 自增属性的核心功能是自动递增.
     当向表中插入新记录时, 如果未明确为自增字段指定值, MySQL会自动为该字段分配下一个可用的自增值.
     这大大简化了插入操作, 因为开发者无需手动为每条记录分配唯一的标识符.

* 6. 指定指: 如果你手动指定了一个值, MySQL会使用你提供的值.
     
* 7. 跳过值: 虽然自增属性通常生成连续的数值, 但在某些情况下, 自增值可能会跳过某些数字.
     例如, 如果在插入过程中删除了某些记录, 那么自增值可能会继续从下一个可用的数字开始, 而不是填补被删除记录留下的空白.
     此外, 如果插入操作失败, 自增值也可能不会回退.
     
* 8. 可以重置起始值: 如果需要, 可以通过ALTER TABLE语句重置自增值的起始点.
     例如, 如果你删除了表中的所有记录并希望从1开始重新编号, 你可以重置自增值的起始点为1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

5.1 定义自增属性

下面这个例子中, id列是一个自增列, 每次插入新记录时, 它的值都会自动增加.
CREATE TABLE example_table (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(255) NOT NULL  
);
1
2
3
4
5

-- 错误示例1(没有设置为主键或唯一的字段不能使用):
mysql> CREATE TABLE emp5(
    id INT AUTO_INCREMENT,  
    name VARCHAR(20)
);
ERROR 1075 (42000): Incorrect table definition;
there can be only one auto column and it must be defined as a key

-- 错误示例2(非整形列不能使用):
mysql> CREATE TABLE emp5(
    id INT PRIMARY KEY,  
    name VARCHAR(20) AUTO_INCREMENT
);
ERROR 1063 (42000): Incorrect column specifier for column 'name'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 正确示例:
mysql> CREATE TABLE emp5(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 插入name字段数据:
mysql> INSERT INTO emp5(name)VALUES('kid'), ('qq');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM emp5;
+----+------+
| id | name |
+----+------+
|  1 | kid  |
|  2 | qq   |
+----+------+
2 rows in set (0.00 sec)

-- 手贱:
-- 插入id字段数据:
mysql> INSERT INTO emp5 VALUES(4, 'qz'), (null, 'ez');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 如果为 id 字段指定了 NULL 或者没有提供值, MySQL 会自动为 id 字段生成一个值, 这个值通常是前一个自增值加1:
mysql> SELECT * FROM emp5;
+----+------+
| id | name |
+----+------+
|  1 | kid  |
|  2 | qq   |
|  4 | qz   |
|  5 | ez   |
+----+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

5.2 添加自增属性

在MySQL中, 可以使用ALTER TABLE语句来修改表结构, 为表中的字段添加自增属性(AUTO_INCREMENT).

添加前, 确保该字段是主键或具有唯一约束, 因为AUTO_INCREMENT 属性只能应用于这样的字段.
添加自增属性语法:

ALTER TABLE table_name MODIFY column_name datatype AUTO_INCREMENT;
这里, table_name 是你的表名, column_name 是你希望添加自增属性的字段名, datatype 是该字段的数据类型.
1
2
3
4
5
6
7

-- 创建表格:
mysql> CREATE TABLE emp6(
    id INT PRIMARY KEY,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 添加数据:
mysql> INSERT INTO emp6 VALUES(1, 'kid'), (2, 'qq');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 为id主键增加自增数据:
mysql> ALTER TABLE emp6 MODIFY id INT AUTO_INCREMENT;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO emp6(name) VALUES('qz'), ('ez');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM  emp6;
+----+------+
| id | name |
+----+------+
|  1 | kid  |
|  2 | qq   |
|  3 | qz   |
|  4 | ez   |
+----+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

5.3 删除自增数据

在MySQL中, 可以使用ALTER TABLE语句删除一个字段的自增属性.

具体语法如下:
ALTER TABLE table_name MODIFY column_name datatype;
在这个语法中, table_name是你要修改的表名, column_name是包含自增属性的字段名, 而datatype是该字段的数据类型.
在数据类型后面不写AUTO_INCREMENT, 则是删除这个属性.
* NOT NULL和自增属性: 在删除时, 不需要指定它们的名称, 只需在列定义中去掉相应的关键字即可.

注意事项:
* 1. 移除自增属性不会影响字段中已有的数据.
* 2. 移除自增属性后, 如果你再次向表中插入新记录而不手动指定该字段的值, MySQL将不会自动为该字段生成自增值.
     你需要确保在插入新记录时为该字段提供一个有效的值, 或者将该字段设置为允许NULL值(如果允许).  
1
2
3
4
5
6
7
8
9
10
11
12

示例, 假设你有一个名为emp的表, 其中有一个名为id的字段, 该字段具有自增属性.
如果你想删除id字段的自增属性, 你可以使用以下语句:

ALTER TABLE emp MODIFY id INT;
在这个例子中, 我们假设id字段的数据类型是INT.
这条ALTER TABLE语句将修改id字段, 移除它的自增属性, 但保留它的数据类型.
1
2
3
4
5
6

-- 创建表格:
mysql> CREATE TABLE emp7(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp7;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 删除自增属性:
mysql> ALTER TABLE emp7 MODIFY COLUMN id INT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

5.4 自增变量的持久化

在MySQL中, 自增主键的持久化问题主要涉及到自增值在服务器重启或表结构变更后的行为.
在MySQL的不同版本中, 对于自增值的处理有所不同.

MySQL 8.0之前的版本中, AUTO_INCREMENT的值并不总是持久化存储的(存储在内存中).
当MySQL服务器重启或执行某些特定的ALTER TABLE操作时, AUTO_INCREMENT的值可能会被重置为当前表中最大主键值加1.
这意味着, 如果之前手动插入了一个较大的自增值, 然后在服务器重启后尝试插入新记录, 可能会遇到主键冲突的问题.

MySQL 8.0及更高版本, AUTO_INCREMENT的值得到了更好的持久化处理.
在大多数情况下, 即使服务器重启或执行ALTER TABLE操作, AUTO_INCREMENT的值也会保持不变.
1
2
3
4
5
6
7
8
9

-- 5.7版本.
-- 创建表格:
mysql> CREATE TABLE test1(
    id INT PRIMARY KEY AUTO_INCREMENT
);
Query OK, 0 rows affected (0.02 sec)

-- 插入四个空值:
mysql> INSERT INTO test1
VALUES(0),(0),(0),(0);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

-- 删除id为4的记录: 
mysql> DELETE FROM test1 WHERE id = 4;
Query OK, 1 row affected (0.01 sec)

-- 再次插入一个空值:
mysql> INSERT INTO test1 VALUES(0);
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |  -- 从结果可以看出, 虽然删除了id为4的记录, 但是再次插入空值时, 并没有重用被删除的4, 而是分配了5.
+----+
4 rows in set (0.00 sec)

-- 删除id为5的记录:
mysql> DELETE FROM test1 WHERE id=5;
Query OK, 1 row affected (0.01 sec)

-- 重启服务:
mysql> exit;
Bye

C:\Users\13600\Desktop>net stop  MySQL57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

C:\Users\13600\Desktop>net start  MySQL57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

-- 重新插入一个空值:
mysql> INSERT INTO test1 values(0);
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

从结果可以看出, 新插入的0值分配的是4, 按照重启前的操作逻辑, 此处应该分配6.
出现上述结果的主要原因是自增主键没有持久化.
在MySQL 5.7系统中, 对于自增主键的分配规则, 是由InnoDB数据字典内部一个计数器来决定的,
而该计数器只在内存中维护, 并不会持久化到磁盘中, 当数据库重启时, 该计数器会被初始化.
1
2
3
4

-- 8.1版本
-- 创建表格:
mysql> CREATE TABLE test1(
    id INT PRIMARY KEY AUTO_INCREMENT
);
Query OK, 0 rows affected (0.02 sec)

-- 插入四个空值:
mysql> INSERT INTO test1
VALUES(0),(0),(0),(0);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

-- 删除id为4的记录: 
mysql> DELETE FROM test1 WHERE id = 4;
Query OK, 1 row affected (0.01 sec)

-- 再次插入一个空值:
mysql> INSERT INTO test1 VALUES(0);
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |  -- 分配了5.
+----+
4 rows in set (0.00 sec)

-- 删除id为5的记录:
mysql> DELETE FROM test1 WHERE id=5;
Query OK, 1 row affected (0.01 sec)

-- 重启服务:
mysql> exit;
Bye

C:\Users\13600\Desktop>net stop  MySQL81
MySQL81 服务正在停止.
MySQL81 服务已成功停止。

C:\Users\13600\Desktop>net start  MySQL81
MySQL81 服务正在启动 .
MySQL81 服务已经启动成功。

-- 重新插入一个空值:
mysql> INSERT INTO test1 values(0);
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |   -- 从结果可以看出, 自增变量已经持久化了.
+----+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

MySQL 8.0将自增主键的计数器持久化到重做日志中.
每次计数器发生改变, 都会将其写入重做日志中.
如果数据库重启, InnoDB会根据重做日志中的信息来初始化计数器的内存值.
1
2
3

6. 外键约束

6.1 特性

外键约束(Foreign Key Constraint): 是数据库管理系统(DBMS)中用于确保数据引用完整性的一种规则.
它指定一个字段(或字段组合)的值必须在另一个表的主键或唯一约束字段中存在.

以下是外键约束的主要特性: 
* 1. 引用完整性: 外键约束的主要目的是维护引用完整性.
     这意味着, 在一个表中定义的外键列中的每个值, 都必须在被引用的表的主键列或具有唯一约束的列中存在.
     这确保了数据之间的关系始终保持一致.
* 2. 级联操作: 当在外键所引用的主表(父表)中进行某些操作时(如更新或删除), 
     可以配置外键约束以自动执行相应的操作在子表(引用外键的表)上,
     这被称为级联操作, 包括级联更新和级联删除.
* 3. 阻止无效操作: 如果尝试插入或更新子表中的记录, 而该记录的外键值在被引用的父表中不存在,
     那么数据库会拒绝该操作, 并可能抛出一个错误. 这有助于防止数据不一致的情况.
* 4. 可选性与非空性: 外键列可以是可选的(允许NULL值), 也可以是非空的(不允许NULL值), 这取决于业务规则和数据模型的需求.
* 5. 索引: 大多数数据库管理系统会自动为外键列创建索引, 以加速引用完整性的检查过程. 这有助于提高查询性能.
* 6. 多对一与多对多关系: 外键约束不仅支持多对一的关系(一个子表的记录可以引用一个父表的记录),
     还支持实现多对多的关系(通过中间表).
* 6. 可删除性与可更新性: 外键约束可以配置为在父表的主键被删除或更新时采取特定的操作, 
     如限制删除/更新, 级联删除/更新或设置NULL值.
* 7. 增强数据质量: 通过确保数据之间的正确关系, 外键约束有助于减少数据冗余和错误, 从而提高数据质量.

需要注意的是, 虽然外键约束在维护数据一致性和完整性方面非常有用, 
但它们也可能对性能产生一定的影响, 特别是在执行涉及大量数据的操作时.
因此, 在设计数据库和选择是否使用外键约束时, 需要权衡这些因素.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

子表与从表:
主表(父表): 作为数据参考基准的表, 通常包含基础数据或核心信息, 具有主键字段, 该字段的值具有唯一性.
从表(子表): 依赖主表数据的表, 通过外键字段与主表的主键字段相关联, 用于存储与主表相关的详细信息或扩展数据.
举例: 员工表和部门表: 部门表存储部门的基本信息, 作为主表; 员工表通过部门ID字段与部门表关联, 作为从表.
1
2
3
4

6.2 定义外键约束

外键约束的基本语法:
* 1. 单列外键约束, 其基本形式如下:
     CREATE TABLE child_table (  
         child_column1 datatype,  
         child_column2 datatype,  
         ...  
         FOREIGN KEY (child_column1)  
             REFERENCES parent_table(parent_column1)  
     );

* 2. 联合外键约束则(允许子表中的多个列组合起来引用父表中的多个列), 联合外键约束的语法如下:
     CREATE TABLE child_table (  
         child_column1 datatype,  
         child_column2 datatype,  
         ...  
         FOREIGN KEY (child_column1, child_column2, ...)  
             REFERENCES parent_table(parent_column1, parent_column2, ...)
     );

解释:
child_table 是子表, child_column1是子表中作为外键的列, 
parent_table 是父表,parent_column1是父表中被外键引用的列.    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

外键约束注意事项:
* 1. 数据类型一致性: 外键列的数据类型必须与父表被引用列的数据类型完全一致.
     这是外键约束的基本前提, 确保引用的一致性.
* 2. 引用完整性: 外键约束确保数据的引用完整性.
     这意味着子表不能插入父表中不存在的外键值, 同时也不能删除被子表引用的父表记录.
* 3. 唯一性引用: 从表的外键列必须引用主表的主键或具有唯一约束的列.
     这确保了引用的数据具有唯一性, 有助于维护数据的完整性和准确性.
* 4. 外键约束命名: 在创建外键约束时, 可以手动指定外键约束名以提高可读性.
     如果没有指定, 系统会自动生成一个默认的外键名.
* 5. 创建表的顺序: 应首先创建主表, 再创建从表, 确保从表能够正确引用主表的主键或唯一约束列.
* 6. 从表的多外键约束: 从表可以指定多个外键约束, 每个外键约束可以引用不同的主表或主表的不同列.
* 7. 逻辑含义与数据类型: 从表的外键列与主表被参照的列在逻辑上应具有相同的含义, 即使它们的列名可以不同.
     此外, 它们的数据类型必须完全一致.  
* 8. 删除表的顺序: 应先删除从表或先删除外键约束, 再删除主表, 以避免因依赖关系导致删除失败.
* 9. 主表记录的删除: 当主表的记录被从表参照时, 这些记录将不允许被删除,
     除非先从从表中删除依赖该记录的数据, 以保证数据的引用完整性.
* 10. 索引与外键约束: 创建外键约束时, 系统默认会在外键列上建立对应的索引, 以提高查询效率.
      如果删除了外键约束, 应手动删除对应的索引, 避免不必要的性能开销和存储空间占用.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

下面有一张部门表:
1

员工编号	员工姓名	部门名称
`1`	`张三`	`人力资源部`
`2`	`李四`	`财务部`
`3`	`王五`	`技术部`
…	…	…

将部门信息拆分为独立的部门表(如dept表)和员工表(如emp表)是数据库设计中的常见做法, 这样做的主要原因是为了规范化(Normalization).
规范化是数据库设计的一个核心原则, 它旨在减少数据冗余和提高数据一致性.
通过将部门信息单独存储在一个表中, 并在员工表中通过外键引用它, 可以确保部门信息只在一个地方存储和更新.
这样, 如果有任何变动(例如部门名称的更改), 只需要在一个地方进行更新, 而不是在整个数据库的多个地方.
1
2
3
4

emp表(从表)			dep表(主表)
id	name	dep_id	id	name
`1`	`张三`	`1`	`1`	`人力资源部`
`2`	`李四`	`2`	`2`	`财务部`
`3`	`王五`	`3`	`3`	`技术部`

下面我将详细解释如何定义外键约束:
* 1. 定义主表dept, 它包含部门编号did和部门名称dname.
     did列是主键, 因此它必须是唯一的, 并且不允许NULL值.
1
2
3

-- 创建主表:
mysql> CREATE TABLE dep8 (  
    id INT PRIMARY KEY,  -- 部门编号, 作为主键  
    name VARCHAR(50)     -- 部门名称  
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC dep8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| did   | int         | NO   | PRI | NULL    |       |
| dname | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- 插入数据:
mysql> INSERT INTO dep8 (id, name) VALUES  
(1, '人力资源部'),  
(2, '财务部'),  
(3, '技术部');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

* 2. 接下来, 我们定义从表emp, 它包含员工编号id, 员工姓名name以及员工所在部门的编号dep_id.
     这里, id是主键，而dep_id是外键, 它引用了dep表的主键id.
     在定义外键约束时, 需要使用FOREIGN KEY关键字, 并明确指定外键列和所引用的父表及其列.
1
2
3

-- 创建从表:
mysql> CREATE TABLE emp8 (  
    id INT PRIMARY KEY,    -- 员工编号, 作为主键  
    name VARCHAR(50),      -- 员工姓名  
    dep_id INT,            -- 员工所在部门的编号, 作为外键  
    FOREIGN KEY (dep_id) REFERENCES dep8(id) -- 定义外键约束, 引用dep表的id列  
    -- emp表的dep_id和dep表的id的数据类型一致, 意义都是表示部门的编号  
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp8; 
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| dep_id | int         | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

以下是Key列中可能出现的值及其详细解释:
* 1. PRI (主键): 如果Key是PRI, 表示该列是主键的组成部分.
     一个表只能有一个主键, 主键的值必须是唯一的, 并且不允许NULL值.
     主键用于唯一标识表中的每一行记录.
     
* 2. UNI (唯一索引): 如果Key是UNI, 表示该列是一个唯一索引的组成部分, 且作为该唯一索引的第一列(前导列).
     唯一索引: 确保索引列的值是唯一的, 但允许NULL值有多个(除非索引被定义为UNIQUE NOT NULL).
     唯一索引有助于快速检索数据, 并确保数据的唯一性.
     
* 3. MUL (非唯一索引): 如果Key是MUL, 表示该列是一个非唯一索引的组成部分.
     非唯一索引: 允许索引列的值重复.
     它可能是单列索引的一部分, 也可能是多列索引(复合索引)的一部分, 并且不一定是该索引的第一列.
     非唯一索引同样可以提高查询性能, 但不如唯一索引那样严格保证数据的唯一性.
     ** 联合唯一显示MUL, 表示单独的列值可以重复, 但是组合必须唯一. **
     
* 4. 空值: 如果Key列是空值, 表示该列没有索引, 或者该列是非唯一复合索引的非前导列.
     没有索引的列在查询时可能需要进行全表扫描, 这可能会影响查询性能.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 插入员工数据到emp表, 确保dep_id是dep表中已存在的id:  
mysql> INSERT INTO emp8 (id, name, dep_id) VALUES
    (1, '张三', 1),  -- 张三在人力资源部
    (2, '李四', 2),  -- 李四在财务部
    (3, '王五', 3);  -- 王五在技术部
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM emp8;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |      1 |
|  2 | 李四 |      2 |
|  3 | 王五 |      3 |
+----+------+--------+
3 rows in set (0.01 sec)

-- 插入一个不存在的deptid到emp表, 数据库会抛出一个错误, 因为外键约束不允许这样的操作:
mysql> INSERT INTO emp8 (id, name, dep_id) VALUES (4, '赵六', 4);  -- 部门编号4在dep8表中不存在
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`integrity`.`emp8`, 
CONSTRAINT `emp8_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep8` (`id`))
-- emp_ibfk_1是默认的约束名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 错误示例1,  外键不是主键:
mysql> CREATE TABLE dep9(
    id INT,  -- 不是主键
    name VARCHAR(20) 
);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE emp9 (  
    id INT PRIMARY KEY,    
    name VARCHAR(50),      
    dep_id INT,            
    FOREIGN KEY (dep_id) REFERENCES dep9(id) -- 定义外键约束, 引用dep9表的id列  
);
ERROR 1822 (HY000): Failed to add the foreign key constraint. 
Missing index for constraint 'emp9_ibfk_1' in the referenced table 'dep9'
错误 1822 (HY000): 添加外键约束失败.
在引用的表 'dep9' 中缺少用于约束 'emps1_ibfk_1' 的索引.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 错误示例2, 数据类型不一致:
mysql> CREATE TABLE dep10(
    id INT PRIMARY KEY,  
    name VARCHAR(20) 
);
Query OK, 0 rows affected (0.02 sec)

CREATE TABLE emp10(
    id INT primary key,  
    name VARCHAR(50),   
    dep_id CHAR, 
    foreign key (dep_id) references dep10(id)
);
ERROR 3780 (HY000): Referencing column 'dep_id' and referenced 
column 'id' in foreign key constraint 'emp10_ibfk_1' are incompatible.
错误 3780 (HY000): 在外键约束 'emps2_ibfk_1' 中, 引用列 'dep_id' 和被引用列 'id' 不兼容.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6.2 添加外键约束

在MySQL中, 可以使用ALTER TABLE语句为已存在的表添加外键约束.
以下是一个基本的语法示例:
ALTER TABLE child_table  
ADD FOREIGN KEY (child_column)  
REFERENCES parent_table(parent_column);

注意事项:
在添加外键约束之前, 确保子表中的child_column列中的数据在父表的parent_column列中都有对应的值, 否则添加外键约束会失败.
1
2
3
4
5
6
7
8

-- 创建主表(没有指定外键的话, 那么创建顺序是随意):
mysql> CREATE TABLE dep11 (  
    id INT PRIMARY KEY,  -- 部门编号, 作为主键  
    name VARCHAR(50)     -- 部门名称  
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC dep11;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| did   | int         | NO   | PRI | NULL    |       |
| dname | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- 插入数据:
mysql> INSERT INTO dep11 (id, name) VALUES  
(1, '人力资源部'),  
(2, '财务部'),  
(3, '技术部');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 创建从表:
mysql> CREATE TABLE emp11 (  
    id INT PRIMARY KEY,    -- 员工编号, 作为主键  
    name VARCHAR(50),      -- 员工姓名  
    dep_id INT
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp11; 
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| dep_id | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 添加数据:
mysql> INSERT INTO emp11 (id, name) VALUES
    (1, '张三'), 
    (2, '李四'), 
    (3, '王五');  
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM emp11;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   NULL |
|  2 | 李四 |   NULL |
|  3 | 王五 |   NULL |
+----+------+--------+
3 rows in set (0.00 sec)

-- 添加外键:
mysql> ALTER TABLE emp11 ADD FOREIGN KEY (dep_id) REFERENCES dep11(id);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp11;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| dep_id | int         | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 修改数据:
mysql> UPDATE emp11 SET dep_id = 1 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE emp11 SET dep_id = 2 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE emp11 SET dep_id = 3 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM emp11;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |      1 |
|  2 | 李四 |      2 |
|  3 | 王五 |      3 |
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

6.3 删除外键约束

要删除外键约束, 可以使用 ALTER TABLE 语句配合 DROP FOREIGN KEY 子句.
首先, 需要知道外键约束的名称.
这个名称通常在创建外键约束时指定, 或者在自动创建时由数据库系统分配.

注意事项:
在删除外键约束时, 如果外键约束还关联了索引(某些数据库系统在外键约束创建时会自动创建索引), 那么可能还需要单独删除这个索引.
否则, 即使删除了外键约束, 索引可能仍然存在并占用存储空间.
因此, 在删除外键约束后, 建议检查并删除任何不再需要的关联索引.

可使用SHOW INDEX语句查看表的索引: SHOW INDEX FROM table_name;,
其中table_name是你要查询的表名.
执行这个命令后, 会返回该表的索引信息, 包括索引名称, 索引类型, 索引字段等.
1
2
3
4
5
6
7
8
9
10
11
12

-- 1. 获取外键名称:
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp11';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | integrity         | PRIMARY         | integrity    | emp11      | PRIMARY KEY     | YES      |
| def                | integrity         | emp11_ibfk_1    | integrity    | emp11      | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.00 sec)

-- 2. 删除外键:
mysql> ALTER TABLE emp11 DROP FOREIGN KEY emp11_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 3. 查看表结构(还需要删索引, MUL才会消失):
mysql> DESC emp11;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| dep_id | int         | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 4. 查看索引(Key_name列显示了索引的名称):
mysql> SHOW INDEX FROM emp11;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 5. 删除索引:
mysql> ALTER TABLE emp11 DROP INDEX dep_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 6. 查看表结构:
mysql> DESC emp11;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| dep_id | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.4 外键约束等级

6.4.1 外键修改数据问题

当涉及到外键修改数据时, 可能会遇到以下几种不同的情况:
* 1. 修改外键列的值: 如果想要修改一个包含外键的列的值, 需要确保新的值在引用表中存在对应的记录.
     这是外键约束的基本要求, 即外键列中的每个值都必须在引用表的被引用列中存在.
     如果试图将外键列的值更改为引用表中不存在的值, 将会违反外键约束, 导致操作失败.

* 2. 修改主表的主键: 如果其他表通过外键与当前表的主键关联, 直接修改主键可能会违反外键约束.
     在修改主键之前, 需要更新或删除所有依赖于这个主键的外键约束. 

* 3. 删除主表的记录: 当尝试删除主表中的一个记录时, 如果该记录被其他表的外键所引用, 那么直接删除将会违反外键约束.
     这是因为外键约束要求引用列中的每个值都必须在被引用列中存在, 删除被引用的记录将导致引用列中的某些值失去对应性.
     在这种情况下, 需要先处理那些引用该记录的外键, 例如通过更新外键列的值或删除引用该记录的记录, 然后才能成功删除主表的记录.
1
2
3
4
5
6
7
8
9
10
11

-- 创建主表格:
mysql> CREATE TABLE dep12(
    id int PRIMARY KEY ,
    name VARCHAR(20) 
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO dep12 VALUES(1001, '教学部');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO dep12 VALUES(1003, '财务部');
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM dep12;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1003 | 财务部 |
+------+--------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 创建从表:
mysql> CREATE TABLE emp12(
    id INT PRIMARY KEY,  
    name VARCHAR(5),   
    dep_id INT, 
    FOREIGN KEY (dep_id) REFERENCES dep12(id) 
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO emp12 VALUES(1,'张三', 1001);
Query OK, 1 row affected (0.00 sec)

-- 添加从表记录失败, 主表dep12中没有1005部门:
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`integrity`.`emp12`,
CONSTRAINT `emp12_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep12` (`id`))

-- 查看数据:
mysql> SELECT * FROM emp12;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
+----+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 更新员工表数据, 将张三的部门改为1002:
mysql> UPDATE emp12 SET dep_id = 1002 WHERE id = 1;
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`integrity`.`emp12`,
CONSTRAINT `emp12_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep12` (`id`))

错误 1452 (23000): 无法'添加'或'更新'子行: 外键约束失败...

-- 部门表id字段现在没有1002的值, 所以员工表中不能修改员工所在部门dep_id为1002.
1
2
3
4
5
6
7
8
9

-- 更新部门表信息, 将1001部门号改为1002:
mysql> UPDATE dep12 SET id = 1002 WHERE id = 1001;
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`integrity`.`emp12`,
CONSTRAINT `emp12_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep12` (`id`))

错误 1451 (23000): 无法删除或更新父行: 外键约束失败...

-- 部门表did的1001字段已经被emp12引用了, 所以部门表的1001字段就不能修改了.

-- 删除1001部门信息:
mysql> DELETE FROM dep12 WHERE id=1001;
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`integrity`.`emp12`,
CONSTRAINT `emp12_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep12` (`id`))
错误 1451 (23000): 无法删除或更新父行: 外键约束失败...

-- 因为部门表id的1001字段已经被emp12引用了M 所以部门表的1001字段对应的记录就不能被删除.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 更新部门表信息, 将1003部门号改为1002:
mysql>  UPDATE dep12 SET id = 1002 WHERE id = 1003;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 修改主表成功, 因为部门表的1003部门没有被emp12表引用.
1
2
3
4
5

6.4.2 约束等级

外键约束等级决定了当父表(被引用的表)中的记录发生变化时, 子表(引用表)应如何响应这些变化.
以下是外键约束中常见的等级及其含义:
* 1. RESTRICT:
     更新 (UPDATE): 如果子表中有任何记录通过外键引用了父表中要更新的记录的主键, 那么将不允许对父表的主键进行更新操作.
     删除 (DELETE): 如果子表中有任何记录通过外键引用了父表中要删除的记录的主键, 那么将不允许删除父表中的这条记录.

* 2. CASCADE:
     更新 (UPDATE): 当父表的主键(或外键所引用的列)被更新时, 子表中所有匹配的外键列的值也会被自动更新, 以保持与父表的一致性.
     删除 (DELETE): 当父表中的记录被删除时, 子表中所有匹配的外键列所在的记录也会被自动删除.
     优点: 可以自动维护数据的引用完整性.
     缺点: 可能导致子表中大量数据的意外删除或修改, 需要谨慎使用.
     
* 3. SET NULL:
     更新 (UPDATE): 此操作不适用, 因为更新父表主键通常不允许设置为NULL.
     删除 (DELETE): 当父表中的记录被删除时, 子表中所有匹配的外键列的值会被设置为NULL(前提是该外键列允许NULL值).
     注意: 外键列必须允许NULL值, 否则这种操作会失败.

* 4. NO ACTION/RESTRICT:
     更新 (UPDATE): 如果子表中有匹配的记录, 则不允许对父表对应的主键进行更新操作.
     删除 (DELETE): 如果子表中有匹配的记录, 则不允许删除父表中的记录.
     优点: 严格维护数据完整性, 防止因父表变化而导致子表数据不一致.
     缺点: 可能限制对父表的操作, 尤其是在有大量数据关联的情况下.
* 5. SET DEFAULT:
     更新 (UPDATE): 此操作不适用, 因为更新父表主键通常不允许设置为默认值.
     删除 (DELETE): 当父表中的记录被删除时, 子表中所有匹配的外键列的值会被设置为默认值(如果该外键列有默认值的话).

如果没有指定等级, 就相当于Restrict方式.
通常情况下, 为了方便数据的维护和管理, 会选择CASCADE等级.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

对于外键约束, 最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
可以使用: SHOW CREATE TABLE Users; 查看级联设置.
1
2

6.4.3 演示1

级联更新, 删除为NULL: ON UPDATE CASCADE ON DELETE SET NULL .
1

-- 创建主表:
mysql> CREATE TABLE dep13(
    id int PRIMARY KEY, 
    name VARCHAR(50)
);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据:
mysql> INSERT INTO  dep13 VALUES(1001, '教学部');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  dep13 VALUES(1002, '财务部');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  dep13 VALUES(1003, '咨询部');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM dep13;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1002 | 财务部 |
| 1003 | 咨询部 |
+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 创建从表:
mysql> CREATE TABLE emp13(
    id int PRIMARY KEY, 
    name VARCHAR(5),  
    dep_id INT, 
    FOREIGN KEY(dep_id) references dep13(id)
    ON UPDATE CASCADE  -- 修改操作设置为级联修改等级
    ON DELETE SET NULL -- 删除操作设置为SET NULL等级
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据
mysql> INSERT INTO emp13 VALUES(1, '张三', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp13 VALUES(2, '李四', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp13 VALUES(3, '王五', 1002);
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM emp13;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   1002 |
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 修改主表id字段1002为1004, 从表的引用字段也一同更改为1004:
mysql> UPDATE dep13 SET id = 1004 WHERE id = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看主表数据:
mysql> SELECT * FROM dep13;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 |  -- 原来是1002, 修改为1004
+------+--------+
3 rows in set (0.00 sec)

-- 查看从表数据:
mysql> SELECT * FROM emp13;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   1004 |  -- 看1002, 一同被修改为1004
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 删除主表的记录, 从表对应的字段被修改为NULL:
mysql> DELETE FROM dep13 WHERE ID = 1001;
Query OK, 1 row affected (0.01 sec)

-- 查看主表数据:
mysql> SELECT * FROM dep13;
+------+--------+
| id   | name   |
+------+--------+
| 1003 | 咨询部 |  -- 1001部门被删除
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)

-- 查看从表数据:
mysql> SELECT * FROM emp13;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   NULL |  -- 引用1001部门的员工, dep_id字段变为NULL
|  2 | 李四 |   NULL |
|  3 | 王五 |   1004 |
+----+------+--------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

6.4.4 演示2

更新为NULL, 级联删除: on update set null on delete cascade
1

-- 创建主表:
mysql> CREATE TABLE dep14(
    id int PRIMARY KEY, 
    name VARCHAR(50)
);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据:
mysql> INSERT INTO  dep14 VALUES(1001, '教学部');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  dep14 VALUES(1002, '财务部');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  dep14 VALUES(1003, '咨询部');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM dep14;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1002 | 财务部 |
| 1003 | 咨询部 |
+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 创建从表:
mysql> CREATE TABLE emp14(
    id int PRIMARY KEY, 
    name VARCHAR(5),  
    dep_id INT, 
    FOREIGN KEY(dep_id) references dep14(id)
    ON UPDATE SET NULL  -- 修改操作设置为SET NULL等级 
    ON DELETE CASCADE   -- 删除操作设置为级联删除等级
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据
mysql> INSERT INTO emp14 VALUES(1, '张三', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp14 VALUES(2, '李四', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp14 VALUES(3, '王五', 1002);
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM emp14;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   1002 |
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 修改主表将1002部门id修改为1004, 从表对应的字段设置为NULL:
mysql> UPDATE dep14 SET id = 1004 WHERE id = 1002;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看主表数据:
mysql> SELECT * FROM dep14;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 |  -- 原来id是1002
+------+--------+
3 rows in set (0.00 sec)

-- 查看从表数据:
mysql> SELECT * FROM emp14;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   NULL |  -- 引用1002部门的员工, dep_id字段变为NULL
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 删除主表的1001部门的记录, 从表相应的记录也被删除:
mysql> DELETE FROM dep14 WHERE id = 1001;
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT * FROM dep14;
+------+--------+
| id   | name   |
+------+--------+
| 1003 | 咨询部 |  -- 部门表中1001部门被删除
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM emp14;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  3 | 王五 |   NULL |  -- 1001部门的员工也被删除
+----+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

6.4.5 演示3

级联更新, 级联删除: on update cascade on delete cascade
1

-- 创建主表:
mysql> CREATE TABLE dep15(
    id int PRIMARY KEY, 
    name VARCHAR(50)
);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据:
mysql> INSERT INTO  dep15 VALUES(1001, '教学部');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  dep15 VALUES(1002, '财务部');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  dep15 VALUES(1003, '咨询部');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM dep15;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1002 | 财务部 |
| 1003 | 咨询部 |
+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 创建从表:
mysql> CREATE TABLE emp15(
    id int PRIMARY KEY, 
    name VARCHAR(5),  
    dep_id INT, 
    FOREIGN KEY(dep_id) references dep15(id)
    ON UPDATE CASCADE  -- 修改操作设置为级联更新等级 
    ON DELETE CASCADE  -- 删除操作设置为级联删除等级
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO emp15 VALUES(1, '张三', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp15 VALUES(2, '李四', 1001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp15 VALUES(3, '王五', 1002);
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM emp15;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   1002 |
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 修改主表, 从表对应的字段自动修改:
mysql> UPDATE dep15 SET id = 1004 where id = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看主表:
mysql> SELECT * FROM dep15;
+------+--------+
| id   | name   |
+------+--------+
| 1001 | 教学部 |
| 1003 | 咨询部 |
| 1004 | 财务部 |  -- 部门1002修改为1004
+------+--------+
3 rows in set (0.00 sec)

-- 查看从表:
mysql> SELECT * FROM emp15;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 张三 |   1001 |
|  2 | 李四 |   1001 |
|  3 | 王五 |   1004 |  -- 级联修改, 更改为1004
+----+------+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 删除主表的1001号部门信息, 从表相应的记录也被删除:
mysql> DELETE FROM dep15 WHERE id=1001;
Query OK, 1 row affected (0.01 sec)

-- 查看主表:
mysql> SELECT * FROM dep15;
+------+--------+
| id   | name   |
+------+--------+
| 1003 | 咨询部 |  -- 1001部门被删除
| 1004 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)

-- 查看从表:
mysql> SELECT * FROM emp15;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  3 | 王五 |   1004 |  -- 1001部门的员工一同被删除
+----+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

6.4.6 增加级联等级

如果已经外键约束, 不能直接添加级联设置.
需要先删除现有的外键约束, 然后再重新创建它, 包括所需的级联设置.
这通常涉及两个步骤:
* 1. 删除现有的外键约束
     首先, 需要知道现有外键约束的名称.
     可以通过查询数据库的系统表或信息模式来获取这个信息.
     
* 2. 创建一个新的外键约束, 并指定级联设置.
1
2
3
4
5
6
7
8

-- 创建主表:
mysql> CREATE TABLE UserDetail(
    id INT PRIMARY KEY
);
Query OK, 0 rows affected (0.01 sec)

-- 创建从表:
CREATE TABLE Users(
    id INT PRIMARY KEY,
    det_id INT,
    FOREIGN KEY (det_id) REFERENCES UserDetail(id) 
);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 1. 获取外键名称:
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'Users';
... CONSTRAINT_NAME
... users_ibfk_1

-- 2. 删除外键:
mysql> ALTER TABLE Users DROP FOREIGN KEY users_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 3. 查看索引(Key_name列显示了索引的名称):
mysql> SHOW INDEX FROM Users;
... Key_name 
... det_id 

-- 4. 删除索引:
mysql> ALTER TABLE Users DROP INDEX det_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 5. 增加外键约束并设置级联:
mysql> ALTER TABLE Users ADD
FOREIGN KEY (det_id) REFERENCES UserDetail(id)  
ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 6. 查看级联设置:
mysql> SHOW CREATE TABLE Users;
... ON DELETE CASCADE ON UPDATE CASCADE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

6.5 影响与考量

问题1: 如果两个表之间有关系(一对一, 一对多), 比如员工表和部门表(一对多), 是否一定要建立外键约束?

答: 不是一定要建立外键约束.
建立外键约束是一种数据库设计决策, 它用于强制实施引用完整性, 但并非所有情况下都必须使用.

问题2: 建立和不建立外键约束有什么区别?

答: 建立外键约束后, 数据库管理系统会强制执行引用完整性规则.
这意味着, 当你尝试执行违反引用完整性的操作时(如在员工表中添加一个不存在的部门), 数据库会报错并阻止操作.
这有助于确保数据的准确性和一致性.

不建立外键约束时, 数据库不会对表之间的关系进行强制检查.
你可以自由地添加, 修改或删除数据, 即使这样做可能导致引用不完整或数据不一致.
在这种情况下, 维护数据的完整性和一致性就完全依赖于应用程序的逻辑或程序员的自觉性.

问题3: 建立和不建立外键约束与查询操作有关系吗?

答: 建立和不建立外键约束与查询操作本身没有直接关系.
无论是否建立外键约束, 你都可以使用相同的SQL查询语句来检索数据.
外键约束主要影响的是数据的插入, 更新和删除操作, 而不是查询操作.

然而, 需要注意的是, 在某些情况下, 没有外键约束的数据库设计可能导致数据冗余和不一致, 这可能会影响查询结果的准确性和可靠性.
因此, 尽管外键约束与查询操作本身没有直接关系, 但它们对于维护数据库的整体数据质量和一致性至关重要.

在MySQL中, 外键约束确实会消耗一定的系统资源.
对于高并发的SQL操作, 特别是大型网站的中央数据库, 外键约束可能会成为性能瓶颈.
因此, MySQL允许你选择在应用层面完成数据一致性的检查逻辑, 而不是依赖系统自带的外键约束.
这意味着, 即使不使用外键约束, 你也应该通过应用程序的附加逻辑来确保数据的一致性.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

阿里开发规范
[强制]不得使用外键与级联, 一切外键概念必须在应用层解决.
说明: (概念解释)学生表中的 student_id 是主键, 那么成绩表中的 student_id 则为外键.
如果更新学生表中的 student_id, 同时触发成绩表中的 student_id 更新, 即为级联更新.
外键与级联更新适用于单机低并发, 不适合分布式, 高并发集群; 
级联更新是强阻塞, 存在数据库更新风暴的风险; 外键影响数据库的插入速度.
1
2
3
4
5
6

7. 检查约束

在MySQL 8.0及更高版本中, 引入了CHECK约束的功能.
1

7.1 特性

CHECK约束是数据库中一种强大的特性, 用于确保表中数据的一致性和完整性.
以下是CHECK约束的主要特性:
* 1. 数据验证: CHECK约束允许开发人员定义在插入或更新数据时必须满足的条件.
     这些条件可以基于列中的数据值或格式, 确保只有满足特定条件的数据才能被存储到表中.
* 2. 灵活性和可定制性: CHECK约束可以应用于一个或多个列, 也可以将多个CHECK约束应用于一个列.
     这意味着开发人员可以根据具体需求为表的不同列设置不同的验证规则.
* 3. 表达式支持: CHECK约束可以包含SQL表达式, 这使得数据验证更为灵活和强大.
     开发人员可以创建简单的约束表达式来检查单个条件下的数据, 也可以使用布尔运算符创建复杂的约束表达式来在多种条件下检查数据.
* 4. 自动执行: 当尝试插入或更新数据时, 数据库管理系统会自动执行CHECK约束, 确保新数据满足所有定义的规则.
     如果数据不满足任何约束条件, 操作将被拒绝, 从而防止无效数据进入数据库.
* 5. 提高数据质量: 通过实施CHECK约束, 数据库中的数据将始终保持预期的规则和限制, 从而提高数据的质量和可靠性.
综上所述, CHECK约束是一种强大的数据库特性, 它允许开发人员通过定义验证规则来确保数据的完整性和一致性.
在数据库设计和管理中, 合理使用CHECK约束可以有效提高数据质量并减少潜在的数据错误.
1
2
3
4
5
6
7
8
9
10
11
12
13

7.2 定义检查约束

以下是CHECK约束的基本语法:

CREATE TABLE table_name (  
    column datatype,  
    ...  
    CHECK (conditional_expression)
);
在这个语法中, 条件表达式(conditional_expression)是一个布尔表达式, 它必须返回TRUE或FALSE.
当插入或更新数据, MySQL会评估这个表达式, 如果表达式返回FALSE, 则操作会失败.

以下是定义检查约束时需要注意的一些事项:

约束条件的正确性: 在创建检查约束之前, 首先要确保约束条件的正确性.
约束条件应明确, 准确, 并符合业务规则. 对于复杂的逻辑条件, 应仔细测试以确保其正确性.

性能考虑: 虽然检查约束有助于提高数据质量, 但它们也可能对性能产生一定的影响.
在大量数据插入或更新时, 检查约束会增加额外的处理时间.
因此, 在设计数据库和选择是否使用检查约束时, 需要权衡性能和数据质量的需求.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

示例, 假设有一个名为students的表, 想确保age列的值总是在18到30之间:
CREATE TABLE students (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    CHECK (age BETWEEN 18 AND 30)  
);
在这个例子中, 如果尝试插入一个age不在18到30之间的学生记录, MySQL会抛出一个错误.
1
2
3
4
5
6
7
8

-- 创建表格:
mysql> CREATE TABLE student4(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    CHECK (age BETWEEN 18 AND 30)  -- 设置检查约束
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC student4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

-- 添加age正常范围内的数据:
mysql> INSERT INTO student4 VALUES(1, 'kid', 18);
Query OK, 1 row affected (0.01 sec)

-- 添加age超出范围内的数据:
mysql> INSERT INTO student4 VALUES(2, 'qq', 31);
ERROR 3819 (HY000): Check constraint 'student4_chk_1' is violated.
-- student4_chk_1 是默认的约束名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

7.3 添加检查约束

在MySQL中, 可以使用ALTER TABLE语句以存在的表要添加CHECK约束.
语法格式如下:
ALTER TABLE table_name  
ADD CHECK (conditional_expression);

注意事项:
在添加检查约束之前, 检查表中是否已经有违反约束条件的数据, 否则无法添加成功.
1
2
3
4
5
6
7

假设已经有了一个students表, 并且现在想要添加一个CHECK约束来确保score列的值在0到100之间:
ALTER TABLE students  
ADD CHECK (score >= 0 AND score <= 100);
如果尝试更新或插入一个score不在0到100之间的学生记录, MySQL同样会抛出一个错误.
1
2
3
4

-- 创建表格:
mysql> CREATE TABLE student5(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
Query OK, 0 rows affected (0.02 sec)

-- 添加数据:
mysql> INSERT INTO student5 VALUES(1, 'kid', 18);
Query OK, 1 row affected (0.01 sec)

-- 添加检查约束:
mysql> ALTER TABLE student5 ADD CHECK(age BETWEEN 18 AND 30);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM student5;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
+----+------+------+
1 rows in set (0.00 sec)

-- 添加age超出范围内的数据:
mysql> INSERT INTO student5 VALUES(2, 'qq', 101);
ERROR 3819 (HY000): Check constraint 'student5_chk_1' is violated.
-- student5_chk_1 是默认的约束名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

7.4 删除检查约束

在MySQL中, 可以使用ALTER TABLE语句加DROP语句删除表中存在的CHECK约束, 语法格式如下:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;  -- CONSTRAINT不能都省略
1
2

-- 查看约束名称:
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'student5';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | integrity         | PRIMARY         | integrity    | student5   | PRIMARY KEY     | YES      |
| def                | integrity         | student5_chk_1  | integrity    | student5   | CHECK           | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.00 sec)

-- 删除检查约束:
mysql> ALTER TABLE student5 DROP CONSTRAINT student5_chk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 插入数据:
mysql> INSERT INTO student5 VALUES(2, 'qq', 101);
Query OK, 1 row affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM student5;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
|  2 | qq   |  101 |
+----+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

8. 默认值约束

8.1 特性

默认值约束(Default Constraint): 是数据库中的一种约束类型, 用于指定在插入新记录时, 
如果某个字段没有提供值, 则自动为该字段赋予一个默认值.
这个默认值可以是常量, 表达式或NULL.
1
2
3

默认值约束在数据库设计中具有以下几个特性:
* 1. 自动填充: 当在表中插入一条新记录时, 如果未给具有默认值约束的字段赋值, 数据库系统会自动为该字段插入默认值.
     这有助于确保数据的完整性, 避免因为缺少值而导致的问题.
* 2. 列级别约束: 默认值约束可以针对表中的特定列进行设置.
     每一列只能有一个默认约束, 这有助于确保每个字段在需要时都能得到正确的默认值.
* 3. 多种默认值类型: 默认值可以是常量值, 函数或NULL等.
     这使得默认值约束非常灵活, 可以根据实际需求来设置合适的默认值.
* 4. 不参照其他列或表: 默认值不能参照于其他列或其他表的值.
     这意味着默认值必须是静态的或基于当前列的某些函数计算得出的, 而不能依赖于其他列或表的数据..
* 5. 不适用于某些数据类型: 不能对数据类型为timestamp的列或具有标识列属性的列创建默认值.
     这是因为这些数据类型具有特殊的处理方式和约束, 不支持使用默认值.
通过合理设置默认值约束, 可以简化数据插入操作, 提高数据质量, 并减少因缺少数据而导致的错误和问题.
同时, 默认值约束也有助于保持数据库的一致性和可维护性.
1
2
3
4
5
6
7
8
9
10
11
12
13

8.2 字段填充规则

在数据库操作中, 对于字段的填充, 存在一种优先级顺序.
这通常遵循以下规则:
* 1. 插入值: 当在插入语句中明确为字段指定了一个值时, 这个值将具有最高的优先级.
     无论字段是否设置了默认值或是否允许NULL值, 插入值都将直接用于填充该字段.

* 2. 默认值: 默认值是在创建表时为字段定义的一个自动填充值, 用于在插入操作时字段没有被明确赋值时自动使用.
     默认值的优先级低于插入值, 因为它只在字段没有被明确赋值时才会被考虑.
     然而, 默认值并不是在所有情况下都会使用.

* 3. NULL值: NULL在数据库中表示字段没有值或值未知.
     如果一个字段在插入语句中没有被明确赋值, 并且该字段允许NULL值且没有设置默认值, 那么该字段的值将被设置为NULL.
     NULL的优先级实际上取决于字段是否允许NULL值以及是否设置了默认值.
     如果字段允许NULL值但没有设置默认值, NULL将是下一个被考虑的选项.

因此, 从优先级的角度来看, 插入值具有最高优先级, 因为它直接来自用户的输入或应用程序的指定.
如果没有插入值, 那么系统会检查字段是否设置了默认值.
如果设置了默认值且字段不允许NULL值, 默认值将被使用.
如果字段允许NULL值且没有设置默认值, 那么NULL将被用于填充该字段.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在MySQL数据库中, 插入值为NULL和不提供该字段在INSERT语句中的值, 其结果并不总是相同的.
这主要取决于该字段的定义和约束。

插入值为NULL: 当你明确地在INSERT语句中将某个字段的值设置为NULL时,
你正在告诉数据库该字段没有有效的值或你想表示该字段的缺失或未知状态.
这种情况下, 无论该字段是否允许NULL值或有默认值, 该字段都将被赋予NULL值.

不提供字段值: 如果你在INSERT语句中没有为某个字段提供值, 那么结果将取决于该字段的定义.
如果该字段有默认值, 并且你没有明确指定其他值, 那么数据库将使用默认值填充该字段.
如果该字段没有默认值, 并且允许NULL值, 那么该字段将被赋予NULL值.
但是, 如果该字段不允许NULL值且没有默认值, 那么数据库通常会抛出一个错误, 因为无法确定应该为该字段赋予什么值.
因此, 虽然在某些情况下)例如字段允许NULL值且没有默认值)插入NULL和不提供字段值的结果可能相同(都是NULL),
但在其他情况下(例如字段有默认值)它们可能会有所不同.
为了确保数据的准确性和一致性, 最好在编写INSERT语句时明确地为每个字段提供值或使用NULL(如果适用).

此外, 还需要注意NULL在数据库中的特殊含义.
它不同于空字符串或零值, 表示的是'无值'或'未知值'.
因此, 在处理数据时, 应仔细考虑何时使用NULL以及何时使用其他值或默认值.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8.3 定义默认值约束

以下是一个基本的默认值约束的语法格式:
CREATE TABLE table_name (  
    column1 datatype DEFAULT default_value,  
    ...  
);

注意事项:
默认值列不能定义唯一属性, 当默认值被多次使用时, 会违反唯一约束.
1
2
3
4
5
6
7
8

示例, 假设有一个名为students的表, 其中有一个名为age的列, 我们想要为age列设置一个默认值18:
CREATE TABLE students (  
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    age INT DEFAULT 18  
);
在这个例子中, age列被赋予了默认值18.
这意味着, 如果我们插入一个新的学生记录, 但没有为age列提供值, 那么age列的值将自动被设置为18.
1
2
3
4
5
6
7
8

-- 创建表格:
mysql> CREATE TABLE student6 (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT DEFAULT 18  
);
Query OK, 0 rows affected (0.01 sec)

-- 查看表结构:
mysql> DESC student6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |  -- 默认值为18
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

-- 插入数据:
mysql> INSERT INTO student6 (id, name) VALUES (1, 'kid');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM student6;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |  -- age列的值将自动被设置为18
+----+------+------+
1 row in set (0.00 sec)

-- 手动插入NULL:
mysql>  INSERT INTO student6 (id, name, age) VALUES (2, 'qq', null);
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM student6;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
|  2 | qq   | NULL |
+----+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- 示例2:
mysql> CREATE TABLE emp16(
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT DEFAULT 18,
    phone CHAR(11) NOT NULL DEFAULT ''  -- 默认是空字符串
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp16;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |
| phone | char(11)    | NO   |     |         |       |  -- 空字符串看不见
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 插入数据:
mysql> INSERT INTO emp16 VALUES(1, 'kid', 18, '13700102535');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp16(id, name) VALUES(2, 'qq');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM emp16;
+----+------+------+-------------+
| id | name | age  | phone       |
+----+------+------+-------------+
|  1 | kid  |   18 | 13700102535 |
|  2 | qq   |   18 |             |
+----+------+------+-------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

8.4 添加默认值约束

在MySQL中, 使用ALTER TABLE语句来添加或修改默认值约束.
在MySQL中, 当使用ALTER TABLE ... MODIFY COLUMN语句来修改列的定义时, 需要明确指定所有想要保留的约束.
如果您省略了某个约束(如NOT NULL或DEFAULT), 那么这个约束就会被移除.

如果列原先有NOT NULL约束, 并且想要保留这个约束, 同时在修改列定义时添加或修改默认值, 
需要在MODIFY COLUMN语句中同时包含NOT NULL和DEFAULT子句.

完整格式:
ALTER TABLE table_name  MODIFY column datatype DEFAULT default_values NOT NULL;
1
2
3
4
5
6
7
8
9

如果有一个已经存在的students表, 并且想要改变age列的默认值为25, 可以修改表结构来添加默认值:

ALTER TABLE students  
MODIFY age INT DEFAULT 25;
在这个例子中, 将age列的默认值更改为25.
1
2
3
4
5

-- 示例1.
-- 创建表格:
mysql> CREATE TABLE student7 (  
    id INT PRIMARY KEY,  
    name VARCHAR(20),  
    age INT 
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC student7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 添加默认值约束:
mysql> ALTER TABLE student7 MODIFY age INT DEFAULT 18;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC student7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 插入数据:
INSERT INTO student7(id, name) VALUES(1, 'kid');
Query OK, 1 row affected (0.01 sec)

-- 查看数据:
mysql> SELECT * FROM student7;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- 示例2:
mysql> CREATE TABLE emp18(
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT NOT NULL,
    phone CHAR(11) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp18;
mysql> DESC emp18;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | NO   |     | NULL    |       |  -- age和phone必须填写
| phone | char(11)    | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 为age字段增加默认值约束(不保留非空约束):
mysql> ALTER TABLE emp18 MODIFY age INT DEFAULT 18;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 为phone字段增加默认值约束(留非空约束):
mysql>  ALTER TABLE emp18 MODIFY phone CHAR(11) DEFAULT '' NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp18;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |
| phone | char(11)    | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

8.5 删除默认值约束

在MySQL中, 不能直接删除默认值约束, 但可以更改列的定义以移除默认值.

语法格式: 
ALTER TABLE table_name MODIFY column datatype DEFAULT NULL;
这里, datatype是列的数据类型, 如果想要完全移除默认值, 可以将其设置为NULL.

省略格式:
ALTER TABLE table_name MODIFY column datatype; 
如果您省略了DEFAULT子句, 那么该列将不再具有默认值. 

注意事项:
如果列原先有NOT NULL约束, 并且想要保留这个约束, 同时在修改列定义时添加上, 否则会一同被删除.
1
2
3
4
5
6
7
8
9
10
11
12

-- 删除age字段默认值约束(原本没有非空约束):
mysql> ALTER TABLE emp18 MODIFY age INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 删除phone字段默认值约束(原本有非空约束, 一同被删除):
mysql> ALTER TABLE emp18 MODIFY phone CHAR(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp18;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| phone | char(11)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

9. 显示命名约束

在数据库中, 当创建约束(如唯一, 主键, 外键, 检查约束)时, 可以选择显式地为这些约束命名, 而不是让数据库系统自动生成名称.
显式命名约束的做法有以下优点:

* 1. 可读性: 明确的约束名称能够清晰地表达约束的目的和它所应用的数据列, 从而提高代码的可读性.
* 2. 维护性: 显式命名的约束更容易被识别和定位, 这在需要修改或删除约束时尤为重要.
* 3. 一致性: 使用一致的命名规则可以确保整个数据库的命名风格统一, 从而提高整体的可维护性.
* 4. 避免冲突: 在某些情况下, 自动生成的约束名称可能会因为某种原因(如约束的修改或重建)而发生变化.
     显式命名可以避免这种情况的发生.

通过显式命名这些约束, 我们可以更轻松地识别, 修改或删除它们, 
特别是在大型数据库系统中, 这样的命名规范能够极大地提高数据库的可维护性.

以下是几个显式命名约束的示例, 这些例子涵盖了不同类型和不同场景的约束使用:
1
2
3
4
5
6
7
8
9
10
11
12
13

9.1 唯一约束

假设我们要创建一个名为employees的表, 并希望email列的值是唯一的.
我们可以这样定义唯一约束并显式命名它：
1
2

CREATE TABLE employees1 (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    email VARCHAR(100),  
    CONSTRAINT unique_email_constraint UNIQUE (email)  
);
1
2
3
4
5
6
7

在这个例子中, 我们为email列的唯一约束显式命名为unique_email_constraint.
1

mysql> SHOW INDEX FROM employees1;
1

如果你有一个已经存在的表, 并希望为某个列或列组合添加显式命名的唯一约束, 你可以使用ALTER TABLE语句配合ADD CONSTRAINT子句.
假设employees表已经存在, 但现在我们想要为first_name和last_name列的组合添加显式命名的唯一约束:
1
2

ALTER TABLE employees  
ADD CONSTRAINT unique_name_constraint UNIQUE (first_name, last_name);
1
2

在这个例子中, 我们为first_name和last_name列的组合唯一约束显式命名为unique_name_constraint.
1

9.2 主键约束

假设我们有一个employees表, 每个员工有一个唯一的employee_id.
我们可以显式命名主键约束如下:
1
2

CREATE TABLE employees (  
    employee_id INT PRIMARY KEY CONSTRAINT pk_employees_id,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    email VARCHAR(100)  
);
1
2
3
4
5
6

在这个例子中, pk_employees_id是主键约束的显式名称.
1

如果你有一个已经存在的表, 并希望为某个列添加显式命名的主键约束, 你可以使用ALTER TABLE语句配合ADD PRIMARY KEY子句.
假设employees表已经存在, 但现在我们想要为id列添加显式命名的主键约束:
1
2

ALTER TABLE employees    
ADD CONSTRAINT pk_employees PRIMARY KEY (id);
1
2

在这个例子中, 我们为id列的主键约束显式命名为pk_employees.
1

9.3 外键约束

假设我们还有一个departments表, 并且employees表中的department_id列引用了departments表的主键.
我们可以这样定义外键约束:
1
2

CREATE TABLE departments (  
    department_id INT PRIMARY KEY,  
    department_name VARCHAR(100)  
);  

CREATE TABLE employees (  
    employee_id INT PRIMARY KEY,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    department_id INT,  
    FOREIGN KEY (department_id) REFERENCES departments(department_id) CONSTRAINT fk_employees_department  
);
1
2
3
4
5
6
7
8
9
10
11
12

在这个例子中, fk_employees_department是外键约束的显式名称.
1

如果你已经有两个存在的表, 并希望为其中一个表添加显式命名的外键约束, 你可以使用ALTER TABLE语句配合ADD FOREIGN KEY子句.
假设departments和employees表已经存在, 但现在我们想要为employees表的department_id列添加显式命名的外键约束:
1
2

ALTER TABLE employees    
ADD CONSTRAINT fk_employees_departments FOREIGN KEY (department_id) REFERENCES departments(id);
1
2

在这个例子中, 我们同样为employees表的department_id列的外键约束显式命名为fk_employees_departments.
1

9.4 检查约束

假设我们想要在employees表中添加一个检查约束, 确保age列的值在18到65岁之间(包含18和65).
我们可以这样定义这个检查约束并为其命名:
1
2

CREATE TABLE employees (  
    id INT NOT NULL AUTO_INCREMENT,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    age INT,  
    CONSTRAINT pk_employees PRIMARY KEY (id),  
    CONSTRAINT chk_employees_age CHECK (age >= 18 AND age <= 65)  
);
1
2
3
4
5
6
7
8

在这个例子中, 我们在创建employees表的同时, 定义了主键约束pk_employees和检查约束chk_employees_age.
1

假设employees表已经存在, 并希望为其中一个表添加显式命名的检查约束, 你可以使用ALTER TABLE语句配合ADD CHECK子句.
1

ALTER TABLE employees  
ADD CONSTRAINT chk_employees_age CHECK (age >= 18 AND age <= 65);
1
2

在这个例子中, chk_employees_age是我们为检查约束自定义的名称.
这个名称应该清晰地描述约束的目的和它所应用的列.
在这个约束中, 我们使用了逻辑表达式(age >= 18 AND age <= 65)来指定age列的值必须在18到65之间.
1
2
3

9.5 修改约束名称

在MySQL中, 修改约束名称并不像添加或删除约束那样直接支持.
MySQL没有专门的SQL语句来直接重命名一个已经存在的约束.
然而, 可以通过以下步骤间接地实现修改约束名称的目的:
* 1. 删除原有的约束: 使用ALTER TABLE语句配合DROP CONSTRAINT子句来删除原有的约束.
     ALTER TABLE table_name DROP CONSTRAINT constraint_name;
     其中, table_name是表的名称, constraint_name是要删除的约束的名称.

* 2. 添加新的约束: 紧接着, 使用ALTER TABLE语句配合ADD CONSTRAINT子句来添加具有新名称的相同类型的约束.
     ALTER TABLE table_name ADD CONSTRAINT new_constraint_name CONSTRAINT_TYPE (column_list);
     其中, new_constraint_name是新的约束名称,
     CONSTRAINT_TYPE是约束的类型(如PRIMARY KEY, UNIQUE, FOREIGN KEY等),
     column_list是应用约束的列名列表.
     
通常情况下, 约束的名称并不会经常变动, 因此在设计数据库和表结构时就应该仔细考虑并合理命名约束.
1
2
3
4
5
6
7
8
9
10
11
12
13
14

示例: 假设我们有一个名为employees的表, 
它有一个名为uq_email的唯一约束, 现在我们想要将这个约束的名称改为unique_email_constraint.
1
2

-- 创建表格:
mysql> CREATE TABLE employees2 (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    email VARCHAR(100),  
    CONSTRAINT uq_email UNIQUE (email)  
);
Query OK, 0 rows affected (0.02 sec)
-- 查看约束名称:
mysql> SHOW INDEX FROM employees2;
... Key_name
... uq_email
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 1. 首先, 删除原有的约束:
mysql> ALTER TABLE employees2 DROP CONSTRAINT uq_email;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 2. 然后, 添加新的约束:
mysql> ALTER TABLE employees2 ADD CONSTRAINT unique_email_constraint UNIQUE (email);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 3. 查看约束名称:
mysql> SHOW INDEX FROM employees2;
... Key_name
... unique_email_constraint
1
2
3
4
5
6
7
8
9
10
11
12
13
14

10. 练习

10.1 面试题

问1: 为什么在建表时,  会加上NOT NULL DEFAULT ''或DEFAULT 0?
答: 我们这么做是为了确保表中不会出现NULL值.
NULL值在数据库中表示未知或缺失的数据, 但在某些情况下, 我们更希望字段有一个明确的默认值, 而不是未知的NULL.

问2: 为什么我们不希望表中出现NULL的值?
答: 不希望表中出现NULL的值主要有两个原因.
首先, NULL是一种特殊的值, 它在比较运算中需要使用特定的IS NULL和IS NOT NULL操作符, 这增加了比较的复杂性.
其次, NULL值可能影响查询的效率, 特别是在使用索引时, NULL值往往不会被索引所考虑, 从而降低了查询的性能.
因此, 在建表时, 我们通常会通过NOT NULL DEFAULT ''或DEFAULT 0来避免NULL值的出现.

问3: 带AUTO_INCREMENT约束的字段值是从1开始的吗?
答: 在MySQL中, 默认情况下, 带有AUTO_INCREMENT约束的字段值确实是从1开始的.
每当向表中插入一条新记录时, 该字段的值会自动加1.
但值得注意的是, 在设置AUTO_INCREMENT属性时, 我们也可以指定第一条插入记录的自增字段的初始值.
例如, 如果我们在插入第一条记录时指定id值为5, 那么之后插入的记录的id值就会从6开始递增.
此外, 添加主键约束时, 通常会设置字段的自动增加属性, 以确保主键的唯一性和自增性.

问4: 不是每个表都可以任意选择存储引擎?
答: 并非如此.
在MySQL中, 虽然支持多种存储引擎, 但并非每个表都可以任意选择存储引擎.
特别是当涉及到外键约束(FOREIGN KEY)时, 需要注意不同存储引擎之间的兼容性.
外键约束用于保证数据的参照完整性, 但如果表之间需要关联外键, 却指定了不同的存储引擎, 那么这些表之间是无法创建外键约束的.
因此, 在选择存储引擎时, 我们需要考虑到表的使用场, 、性能需求以及与其他表的关联关系, 确保选择的存储引擎能够满足我们的需求.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

10.2 练习1

-- 已经存在数据库test04_emp, 两张表emp2和dept2:
-- 创建数据库:
mysql> CREATE DATABASE test04_emp;
Query OK, 1 row affected (0.00 sec)

-- 使用数据库:
mysql> USE test04_emp;
Database changed

-- 创建员工表:
mysql> CREATE TABLE emp2(
    id INT,
    emp_name VARCHAR(15)
);
Query OK, 0 rows affected (0.02 sec
                           
-- 创建部门表:
mysql> CREATE TABLE dept2(
    id INT,
    dept_name VARCHAR(15)
);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 1. 向表emp2的id列中添加PRIMARY KEY约束
mysql> ALTER TABLE emp2 ADD CONSTRAINT pk_id PRIMARY KEY(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| emp_name | varchar(15) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 2. 向表dept2的id列中添加PRIMARY KEY约束:
mysql> ALTER TABLE dept2 ADD CONSTRAINT pk_id PRIMARY KEY(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC dept2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| dept_name | varchar(15) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 3. 向表emp2中添加列dept_id, 并在其中定义FOREIGN KEY约束, 与之相关联的列是dept2表中的id列:
-- 3.1 先添加列:
mysql> ALTER TABLE emp2 ADD COLUMN dept_id INT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 3.2 后添加约束:
mysql> ALTER TABLE emp2 ADD CONSTRAINT fk_id FOREIGN KEY(dept_id) REFERENCES dept2(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| emp_name | varchar(15) | YES  |     | NULL    |       |
| dept_id  | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

10.3 练习2

创建数据库test04_Market, 在test04_Market中创建数据表customers.
customers表结构如下所示, 按以下要求进行操作.
1
2

字段名	数据类型	主键	外键	非空	自增
`c_num`	`INT(11)`	`是`	`否`	`是`	`否`
`c_name`	`VARCHAR(50)`	`否`	`否`	`否`	`否`
`c_contact`	`VARCHAR(50)`	`否`	`否`	`否`	`否`
`c_city`	`VARCHAR(50)`	`否`	`否`	`否`	`否`
`c_birth`	`DATETIME`	`否`	`否`	`是`	`否`

-- 1. 创建数据库test04_Market:
mysql> CREATE DATABASE test04_market;
Query OK, 1 row affected (0.00 sec)

-- 使用数据库:
mysql> USE test04_market;
Database changed

-- 2. 创建数据表customers(不学任何人约束, 后面增加):
mysql> CREATE TABLE customers(
    c_num INT(11) PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(50),
    c_contact VARCHAR(50) ,
    c_city VARCHAR(50),
    c_birth DATETIME NOT NULL
);
Query OK, 0 rows affected, 1 warning (0.01 sec)

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int         | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- 3. 将c_contact字段插入c_birth字段后面:
mysql> ALTER TABLE customers MODIFY COLUMN c_contact VARCHAR(50) AFTER c_birth;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int         | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 4. 将c_name字段数据类型改为VARCHAR(70):
mysql> ALTER TABLE customers MODIFY COLUMN c_name VARCHAR(70);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int         | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(70) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 5. 将c_contact字段改名为c_phone:
mysql> ALTER TABLE customers CHANGE COLUMN c_contact c_phone VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| c_num   | int         | NO   | PRI | NULL    | auto_increment |
| c_name  | varchar(70) | YES  |     | NULL    |                |
| c_city  | varchar(50) | YES  |     | NULL    |                |
| c_birth | datetime    | NO   |     | NULL    |                |
| c_phone | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 6. 增加c_gender字段, 数据类型为CHAR(1):
mysql> ALTER TABLE customers ADD COLUMN c_gender CHAR(1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql>  DESC customers;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int         | NO   | PRI | NULL    | auto_increment |
| c_name   | varchar(70) | YES  |     | NULL    |                |
| c_city   | varchar(50) | YES  |     | NULL    |                |
| c_birth  | datetime    | NO   |     | NULL    |                |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 7. 将表名修改为customers_info:
mysql> RENAME TABLE customers TO customers_info;
Query OK, 0 rows affected (0.02 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test04_market |
+-------------------------+
| customers_info          |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 8. 删除字段c_city:
mysql> ALTER TABLE customers_info DROP COLUMN c_city;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int         | NO   | PRI | NULL    | auto_increment |
| c_name   | varchar(70) | YES  |     | NULL    |                |
| c_birth  | datetime    | NO   |     | NULL    |                |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在test04_Market中创建数据表orders.
orders表结构如下所示, 按以下要求进行操作.
1
2

字段名	数据类型	主键	外键	非空	唯一	自增
`o_num`	`INT(11)`	`是`	`否`	`是`	`是`	`是`
`o_date`	`DATE`	`否`	`否`	`否`	`否`	`否`
`c_id`	`INT(11)`	`否`	`是`	`否`	`否`	`否`

-- 1. 创建数据表orders, 在o_num字段上添加主键约束和自增约束, 在c_id字段上添加外键约束, 关联customers_info表中的主键c_num.
mysql> CREATE TABLE orders (
    o_num INT(11) PRIMARY KEY AUTO_INCREMENT,
    o_date DATE,
    c_id INT(11),
    FOREIGN KEY (c_id)
    REFERENCES customers_info(c_num)
);
Query OK, 0 rows affected, 2 warnings (0.02 sec)

-- 查看表结构:
mysql> DESC orders;
+--------+------+------+-----+---------+----------------+
| Field  | Type | Null | Key | Default | Extra          |
+--------+------+------+-----+---------+----------------+
| o_num  | int  | NO   | PRI | NULL    | auto_increment |
| o_date | date | YES  |     | NULL    |                |
| c_id   | int  | YES  | MUL | NULL    |                |
+--------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- 2. 删除orders表的外键约束, 然后删除表customers:
-- 2.1 查看外键约束:

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'orders';
... CONSTRAINT_NAME
... orders_ibfk_1

-- 2.2 删除外键约束:
mysql> ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 2.3 查看索引:

mysql> SHOW INDEX FROM orders;
... Key_name 
... c_id    

-- 2.4 删除索引
mysql> ALTER TABLE orders DROP INDEX c_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 3. 删除表格:
mysql> DROP TABLE customers_info;
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

10.4 练习3

创建数据表pet, 并对表进行插入, 更新与删除操作. 
pet表结构如下表所示:
1
2

字段名	字段说明	数据类型	主键	外键	非空	自增
`name`	`宠物名称`	`VARCHAR(20)`	`是`	`否`	`是`	`否`
`owner`	`宠物主人`	`VARCHAR(20)`	`否`	`否`	`否`	`否`
`species`	`种类`	`VARCHAR(20)`	`否`	`否`	`否`	`否`
`sex`	`性别`	`CHAR(1)`	`否`	`否`	`否`	`否`
`birth`	`出生日期`	`YEAR`	`否`	`否`	`否`	`否`
`death`	`死亡日期`	`YEAR`	`否`	`否`	`否`	`否`

pet表中记录:
1

name	owner	species	sex	birth	death
`Fluffy`	`Harold`	`cat`	`f`	`2003`	`2010`
`Claws`	`Gwen`	`cat`	`m`	`2004`	`NULL`
`Buffy`	`NULL`	`dog`	`f`	`2009`	`NULL`
`Fang`	`Benny`	`dog`	`m`	`2000`	`NULL`
`Bowser`	`Diane`	`dog`	`m`	`2003`	`2009`
`Chirpy`	`NULL`	`bird`	`f`	`2008`	`NULL`

-- 1. 首先创建数据表pet, 使用不同的方法将表记录插入到pet表中.
mysql> CREATE TABLE  pet(
    name VARCHAR(20) PRIMARY KEY,
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth YEAR,
    death YEAR
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | NO   | PRI | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | year        | YES  |     | NULL    |       |
| death   | year        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-- 2. 插入数据:
mysql> INSERT INTO pet VALUES
('Fluffy', 'Harold', 'cat', 'f', 2003, 2010),
('Claws', 'Gwen', 'cat', 'm', 2004, NULL),
('Buffy', NULL, 'dog', 'f', 2009, NULL),
('Fang', 'Benny', 'dog', 'm', 2000, NULL),
('Bowser', 'Diane', 'dog', 'm', 2003, 2009),
('Chirpy', NULL, 'bird', 'f', 2008, NULL);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM pet;
+--------+--------+---------+------+-------+-------+
| name   | owner  | species | sex  | birth | death |
+--------+--------+---------+------+-------+-------+
| Bowser | Diane  | dog     | m    |  2003 |  2009 |
| Buffy  | NULL   | dog     | f    |  2009 |  NULL |
| Chirpy | NULL   | bird    | f    |  2008 |  NULL |
| Claws  | Gwen   | cat     | m    |  2004 |  NULL |
| Fang   | Benny  | dog     | m    |  2000 |  NULL |
| Fluffy | Harold | cat     | f    |  2003 |  2010 |
+--------+--------+---------+------+-------+-------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

-- 3. 使用UPDATE语句将名称为Fang的狗的主人改为Kevin:
mysql> UPDATE pet SET owner = 'Kevin' WHERE name = 'Fang';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM pet;
+--------+--------+---------+------+-------+-------+
| name   | owner  | species | sex  | birth | death |
+--------+--------+---------+------+-------+-------+
| ...    | ...    | ...     | ...  |  ...  |  ...  |
| Fang   | Kevin  | dog     | m    |  2000 |  NULL |
| ...    | ...    | ...     | ...  |  ...  |  ...  |
+--------+--------+---------+------+-------+-------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 4. 将没有主人的宠物的owner字段值都改为Duck:
mysql> UPDATE pet SET owner = 'Duck' WHERE owner IS NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

-- 查看数据:
mysql>  SELECT * FROM pet;
+--------+--------+---------+------+-------+-------+
| name   | owner  | species | sex  | birth | death |
+--------+--------+---------+------+-------+-------+
| ...    | ...    | ...     | ...  |  ...  |  ...  |
| Buffy  | Duck   | dog     | f    |  2009 |  NULL |
| Chirpy | Duck   | bird    | f    |  2008 |  NULL |
| ...    | ...    | ...     | ...  |  ...  |  ...  |
+--------+--------+---------+------+-------+-------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 5. 删除已经死亡的宠物记录:
mysql> DELETE FROM pet WHERE death IS NOT NULL;
Query OK, 2 rows affected (0.01 sec)

-- 查看数据:
mysql>  SELECT * FROM pet;
+--------+-------+---------+------+-------+-------+
| name   | owner | species | sex  | birth | death |
+--------+-------+---------+------+-------+-------+
| Buffy  | Duck  | dog     | f    |  2009 |  NULL |
| Chirpy | Duck  | bird    | f    |  2008 |  NULL |
| Claws  | Gwen  | cat     | m    |  2004 |  NULL |
| Fang   | Kevin | dog     | m    |  2000 |  NULL |
+--------+-------+---------+------+-------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 6. 删除所有表中的记录:
mysql> DELETE FROM pet;
Query OK, 4 rows affected (0.00 sec)

-- 查看数据:
mysql> SELECT * FROM pet;
Empty set (0.00 sec)
1
2
3
4
5
6
7

10.5 练习4

-- 1. 创建数据库: test_company .
mysql> CREATE DATABASE test_company;
Query OK, 1 row affected (0.01 sec)

-- 使用数据库:
mysql> USE test_company;
Database changed
1
2
3
4
5
6
7

2. 在此数据库下创建如下3表, 数据类型, 宽度, 是否为空根据实际情况自己定义.
    A. 部门表(department): 部门编号(depid), 部门名称(depname), 部门简介(deinfo);
    其中部门编号为主键.
    
    B．员工表(emoloyee): 员工编号(empid), 姓名(name), 性别(sex), 职称(title), 出生日期(birthday), 所在部门编号(depid);
    其中雇员编号为主键; 部门编号为外键(连接部门表), 外键约束等级为(on update cascade 和on delete set null);
    性别默认为男;
    
    C．工资表(salary): 雇员编号(empid), 基本工资(basesalary), 职务工资(titlesalary), 扣除(deduction).
    其中雇员编号为主键.
1
2
3
4
5
6
7
8
9
10

-- 2.1 创建部门表:
mysql> CREATE TABLE department(
    depid INT PRIMARY KEY,
    depname VARCHAR(50),
    deinfo VARCHAR(200)
);
Query OK, 0 rows affected (0.01 sec)

-- 2.2 创建员工表:
mysql> CREATE TABLE employee(
    empid INT PRIMARY KEY,
    name VARCHAR(20),
    sex CHAR(1) DEFAULT '男' ,
    title VARCHAR(20),
    birthday DATE,
    depid INT,
    FOREIGN KEY (depid)
    REFERENCES department(depid)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);
Query OK, 0 rows affected (0.04 sec)

-- 2.3 创建工资表:
mysql> CREATE TABLE salary (  
    empid INT PRIMARY KEY, 
    basesalary DECIMAL(10, 2), 
    titlesalary DECIMAL(10,2), 
    deduction DECIMAL(10, 2)
);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 3. 给工资表(salary)的员员编号(empid)增加外键约束, 外键约束等级为(on update cascade 和on delete cascade).
mysql> ALTER TABLE salary ADD FOREIGN KEY (empid)  
REFERENCES employee(empid) 
ON UPDATE CASCADE 
ON DELETE CASCADE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7

-- 查看表结构:
mysql> DESC department;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| depid   | int          | NO   | PRI | NULL    |       |
| depname | varchar(50)  | YES  |     | NULL    |       |
| deinfo  | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESC employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empid    | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | 男      |       |
| title    | varchar(20) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| depid    | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>  DESC salary;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| empid       | int           | NO   | PRI | NULL    |       |  -- 主键是外键
| basesalary  | decimal(10,2) | YES  |     | NULL    |       |
| titlesalary | decimal(10,2) | YES  |     | NULL    |       |
| deduction   | decimal(10,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

4. 添加数据如下:
1

部门表:
1

部门编号	部门名称	部门简介
`111`	`生产部`	`Null`
`222`	`销售部`	`Null`
`333`	`人事部`	`人力资源管理`

mysql> INSERT INTO department VALUES
(111, '生产部', Null),
(222, '销售部', Null),
(333, '人事部', '人力资源管理');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2
3
4
5
6

员工表:
1

雇员编号	姓名	性别	职称	出生日期	所在部门编号
`1001`	`张三`	`男`	`高级工程师`	`1975-01-01`	`111`
`1002`	`李四`	`女`	`助工`	`1985-01-01`	`111`
`1003`	`王五`	`男`	`工程师`	`1978-11-11`	`222`
`1004`	`张六`	`男`	`工程师`	`1999-01-01`	`222`

mysql> INSERT INTO employee VALUES
(1001, '张三', '男', '高级工程师', '1975-01-01', 111),
(1002, '李四', '女', '助工', '1985-01-01', 111),
(1003, '王五', '男', '工程师', '1978-11-11', 222),
(1004, '张六', '男', '工程师', '1999-01-01', 222);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7

工资表:
1

雇员编号	基本工资	职务工资	扣除
`1001`	`2200`	`1100`	`200`
`1002`	`1200`	`200`	`NULL`
`1003`	`2900`	`700`	`200`
`1004`	`1950`	`700`	`150`

mysql> INSERT INTO salary VALUES
(1001, 2200, 1100, 200),
(1002, 1200, 200, NULL),
(1003, 2900, 700, 200),
(1004, 1950, 700, 150);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7

-- 5. 查询出每个雇员的雇员编号, 姓名, 职称, 所在部门名称, 应发工资(基本工资+职务工资), 实发工资(基本工资+职务工资-扣除):
mysql> SELECT emp.empid, emp.name, emp.title, dep.depname, 
sal.basesalary + sal.titlesalary AS `应发工资`,
sal.basesalary + sal.titlesalary - IFNULL(sal.deduction, 0) AS `实发工资`
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid;
+-------+------+------------+---------+----------+----------+
| empid | name | title      | depname | 应发工资 | 实发工资 |
+-------+------+------------+---------+----------+----------+
|  1001 | 张三 | 高级工程师 | 生产部  |  3300.00 |  3100.00 |
|  1002 | 李四 | 助工       | 生产部  |  1400.00 |  1400.00 |
|  1003 | 王五 | 工程师     | 销售部  |  3600.00 |  3400.00 |
|  1004 | 张六 | 工程师     | 销售部  |  2650.00 |  2500.00 |
+-------+------+------------+---------+----------+----------+
4 rows in set (0.00 sec)
4 rows in set (0.00 se
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 查询销售部门的雇员姓名及其基本工资:
mysql> SELECT emp.name, sal.basesalary 
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid
WHERE dep.depname = '销售部';
+------+------------+
| name | basesalary |
+------+------------+
| 王五 |    2900.00 |
| 张六 |    1950.00 |
+------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 7. 查询姓'张'且年龄小于40的员工的信息(名字, 部门名称, 年龄):
-- TIMESTAMPDIFF() 函数会返回两个日期之间的时间差, 以指定的单位(在这个例子中是年)表示.
-- 这会考虑到生日是在当前年份的哪一天, 从而给出更精确的年龄计算.
mysql> SELECT emp.name, dep.depname,
TIMESTAMPDIFF(YEAR, birthday, NOW()) AS age
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid
WHERE name LIKE '%张%' AND TIMESTAMPDIFF(YEAR, birthday, NOW()) < 40;
+------+---------+------+
| name | depname | age  |
+------+---------+------+
| 张六 | 销售部  |   25 |
+------+---------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 8. 查询所有男员工的基本工资和职务工资:
mysql> SELECT emp.name, emp.sex, sal.basesalary, sal.titlesalary
FROM employee AS `emp` 
INNER JOIN salary as `sal` ON sal.empid = emp.empid
WHERE sex = '男';
+------+------+------------+-------------+
| name | sex  | basesalary | titlesalary |
+------+------+------------+-------------+
| 张三 | 男   |    2200.00 |     1100.00 |
| 王五 | 男   |    2900.00 |      700.00 |
| 张六 | 男   |    1950.00 |      700.00 |
+------+------+------------+-------------+
3 rows in set (0.00 sec)
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 9. 查询基本工资低于2000的员工姓名和职称, 所在部门名称
mysql> SELECT emp.name, dep.depname, dep.depname, sal.basesalary
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid
WHERE basesalary < 2000;
+------+---------+---------+------------+
| name | depname | depname | basesalary |
+------+---------+---------+------------+
| 李四 | 生产部  | 生产部  |    1200.00 |
| 张六 | 销售部  | 销售部  |    1950.00 |
+------+---------+---------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 10. 查询员工总数:
mysql> SELECT COUNT(*) FROM employee;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8

-- 11. 查询部门总数:
mysql> SELECT COUNT(*) FROM department;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 12. 查询应发工资的平均工资和最高工资, 最低工资:
mysql> SELECT 
AVG(basesalary + titlesalary) AS `avg_sal`, 
MAX(basesalary + titlesalary) AS `max_sal`, 
MIN(basesalary + titlesalary)AS `min_sal`
FROM salary;
+-------------+---------+---------+
| avg_sal     | max_sal | min_sal |
+-------------+---------+---------+
| 2737.500000 | 3600.00 | 1400.00 |
+-------------+---------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 13. 按照部门统计应发工资的平均工资:
mysql> SELECT dep.depid, 
AVG(sal.basesalary + sal.titlesalary)
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid
GROUP BY dep.depid;
+-------+---------------------------------------+
| depid | AVG(sal.basesalary + sal.titlesalary) |
+-------+---------------------------------------+
|   111 |                           2350.000000 |
|   222 |                           3125.000000 |
+-------+---------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 14. 找出部门基本工资的平均工资低于2000的:
mysql> SELECT dep.depid, 
AVG(sal.basesalary) AS `avg_sal`
FROM employee AS `emp` 
INNER JOIN department AS `dep` ON emp.depid = dep.depid
INNER JOIN salary as `sal` ON sal.empid = emp.empid
GROUP BY dep.depid
HAVING avg_sal < 2000;
+-------+-------------+
| depid | avg_sal     |
+-------+-------------+
|   111 | 1700.000000 |
+-------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 15. 查询员工编号, 姓名, 基本工资, 职务工资, 扣除, 并按照职务工资升序排列, 如果职务工资相同, 再按照基本工资升序排列:
mysql> SELECT emp.depid, emp.name, 
sal.basesalary, sal.title, sal.deduction 
FROM employee AS `emp`
INNER JOIN salary AS `sal`
ON emp.empid = sal.empid
ORDER BY sal.title, sal.basesalary;
+-------+------+------------+---------+-----------+
| depid | name | basesalary | title   | deduction |
+-------+------+------------+---------+-----------+
|   111 | 李四 |    1200.00 |  200.00 |      NULL |
|   222 | 张六 |    1950.00 |  700.00 |    150.00 |
|   222 | 王五 |    2900.00 |  700.00 |    200.00 |
|   111 | 张三 |    2200.00 | 1100.00 |    200.00 |
+-------+------+------------+---------+-----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 16. 查询员工编号, 姓名, 出生日期, 及年龄段,
-- 其中, 如果80年之前出生的, 定为'老年'; 80后定为'中年', 90后定为'青壮年':
mysql> SELECT empid, name, birthday, 
CASE 
    WHEN YEAR(birthday) < 1980 THEN '老年'
    WHEN YEAR(birthday) < 1990 THEN '中年'
    WHEN YEAR(birthday) < 2000 THEN '青壮年'
    ELSE '其他'
END AS `年龄段`
FROM employee;
+-------+------+------------+--------+
| empid | name | birthday   | 年龄段 |
+-------+------+------------+--------+
|  1001 | 张三 | 1975-01-01 | 老年   |
|  1002 | 李四 | 1985-01-01 | 中年   |
|  1003 | 王五 | 1978-11-11 | 老年   |
|  1004 | 张六 | 1999-01-01 | 青壮年 |
+-------+------+------------+--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 17. 查询所有的员工信息, 和他所在的部门名称:
mysql> SELECT emp.empid, emp.name, dep.depname
FROM employee AS `emp`
LEFT JOIN department AS `dep`
USING(depid);
+-------+------+---------+
| empid | name | depname |
+-------+------+---------+
|  1001 | 张三 | 生产部  |
|  1002 | 李四 | 生产部  |
|  1003 | 王五 | 销售部  |
|  1004 | 张六 | 销售部  |
+-------+------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 18. 查询所有部门信息, 和该部门的员工信息:
mysql> SELECT emp.empid, emp.name, dep.depname
FROM employee AS `emp`
RIGHT JOIN department AS `dep`
USING(depid);
+-------+------+---------+
| empid | name | depname |
+-------+------+---------+
|  1001 | 张三 | 生产部  |
|  1002 | 李四 | 生产部  |
|  1003 | 王五 | 销售部  |
|  1004 | 张六 | 销售部  |
|  NULL | NULL | 人事部  |
+-------+------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 19. 查询所有职位中含'工程师'的男员工的人数:
mysql> SELECT COUNT(*) FROM employee
WHERE sex = '男' AND title LIKE '%工程师%';
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

-- 20, 查询每个部门的男生和女生的人数和平均基本工资:
mysql> SELECT emp.depid, emp.sex, COUNT(*), AVG(basesalary) 
FROM employee AS `emp`
INNER JOIN salary AS `sal`
USING(empid)
GROUP BY depid, emp.sex;
+-------+------+----------+-----------------+
| depid | sex  | COUNT(*) | AVG(basesalary) |
+-------+------+----------+-----------------+
|   111 | 男   |        1 |     2200.000000 |
|   111 | 女   |        1 |     1200.000000 |
|   222 | 男   |        2 |     2425.000000 |
+-------+------+----------+-----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

10.6 练习4

-- 1. 创建一个数据库: test_school:
mysql> CREATE DATABASE test_school;
Query OK, 1 row affected (0.01 sec)

-- 使用表格:
USE test_school;
1
2
3
4
5
6

2. 创建如下表格
表1 Department表的定义:
1
2

字段名	字段描述	数据类型	主键	外键	非空	唯一
`DepNo`	`部门号`	`int(10)`	`是`	`否`	`是`	`是`
`DepName`	`部门名称`	`varchar(20)`	`否`	`否`	`是`	`否`
`DepNote`	`部门备注`	`varchar(50)`	`否`	`否`	`否`	`否`

mysql> CREATE TABLE department(
    DepNo INT(10) PRIMARY KEY,
    DepName VARCHAR(20) NOT NULL,
    DepNote VARCHAR(50)
);
Query OK, 0 rows affected, 1 warning (0.02 sec)
1
2
3
4
5
6

表2 Teacher表的定义:
1

字段名	字段描述	数据类型	主键	外键	非空	唯一
`Number`	`教工号`	`int`	`是`	`否`	`是`	`是`
`Name`	`姓名`	`varchar(30)`	`否`	`否`	`是`	`否`
`Sex`	`性别`	`varchar(4)`	`否`	`否`	`否`	`否`
`Birth`	`出生日期`	`date`	`否`	`否`	`否`	`否`
`DepNo`	`部门号`	`int`	`否`	`是`	`否`	`否`
`Salary`	`工资`	`float`	`否`	`否`	`否`	`否`
`Address`	`家庭住址`	`varchar(100)`	`否`	`否`	`否`	`否`

mysql> CREATE TABLE teacher(
    Number INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Sex VARCHAR(4),
    Birth DATE,
    DepNo INT,
    Salary FLOAT,
    Address VARCHAR(100)
);
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10

3. 插入数据:
表1 Department表的数据:
1
2

DepNo	DepName	DepNote
`601`	`软件技术系`	`软件技术等专业`
`602`	`网络技术系`	`多媒体技术等专业`
`603`	`艺术设计系`	`广告艺术设计等专业`
`604`	`管理工程系`	`连锁经营管理等专业`

mysql> INSERT INTO department VALUES
(601, '软件技术系', '软件技术等专业'),
(602, '网络技术系', '多媒体技术等专业'),
(603, '艺术设计系', '广告艺术设计等专业'),
(604, '管理工程系', '连锁经营管理等专业');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7

表2 Teacher表的数据:
1

Number	Name	Sex	Birth	DepNo	Salary	Address
`2001`	`Tom`	`女`	`1970-01-10`	`602`	`4500.0`	`四川省绵阳市`
`2002`	`Lucy`	`男`	`1983-12-18`	`601`	`2500.0`	`北京市昌平区`
`2003`	`Mike`	`男`	`1990-06-01`	`604`	`1500.0`	`重庆市渝中区`
`2004`	`James`	`女`	`1980-10-20`	`602`	`3500.0`	`四川省成都市`
`2005`	`Jack`	`男`	`1975-05-30`	`603`	`1200.0`	`重庆市南岸区`

mysql> INSERT INTO teacher VALUES
(2001, 'Tom', '女', '1970-01-10', 602, 4500.0, '四川省绵阳市'),
(2002, 'Lucy', '男', '1983-12-18', 601, 2500.0, '北京市昌平区'),
(2003, 'Mike', '男', '1990-06-01', 604, 1500.0, '重庆市渝中区'),
(2004, 'James', '女', '1980-10-20', 602, 3500.0, '四川省成都市'),
(2005, 'Jack', '男', '1975-05-30', 603, 1200.0, '重庆市南岸区');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8

-- 4. 用SELECT语句查询Teacher表的所有记录:
mysql> SELECT * FROM teacher;
+--------+-------+------+------------+-------+--------+--------------+
| Number | Name  | Sex  | Birth      | DepNo | Salary | Address      |
+--------+-------+------+------------+-------+--------+--------------+
|   2001 | Tom   | 女   | 1970-01-10 |   602 |   4500 | 四川省绵阳市 |
|   2002 | Lucy  | 男   | 1983-12-18 |   601 |   2500 | 北京市昌平区 |
|   2003 | Mike  | 男   | 1990-06-01 |   604 |   1500 | 重庆市渝中区 |
|   2004 | James | 女   | 1980-10-20 |   602 |   3500 | 四川省成都市 |
|   2005 | Jack  | 男   | 1975-05-30 |   603 |   1200 | 重庆市南岸区 |
+--------+-------+------+------------+-------+--------+--------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 5. 找出所有其家庭地址中含有'北京'的教师的教工号及部门名称, 要求显示结果中各列标题用中文别名表示:
SELECT tea.Number AS `教工号`, dep.DepName AS `部门名称`
FROM teacher AS `tea`
INNER JOIN department AS `dep`
USING(DepNo)
WHERE Address LIKE '%北京%';
+--------+------------+
| 教工号 | 部门名称   |
+--------+------------+
|   2002 | 软件技术系 |
+--------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 6. 获得Teacher表中工资最高的教工号和姓名.
SELECT Number, name
FROM teacher WHERE salary = (
    SELECT MAX(salary)
    FROM teacher
);
+--------+------+
| Number | name |
+--------+------+
|   2001 | Tom  |
+--------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 6. 找出所有收入在2500～4000之间的教工号:
mysql> SELECT number FROM teacher WHERE salary BETWEEN 2500 AND 4000;
+--------+
| number |
+--------+
|   2002 |
|   2004 |
+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

-- 8. 查找在网络技术系工作的教师的姓名, 性别和工资.
mysql> SELECT name, sex, salary FROM teacher 
WHERE DepNo IN (
    SELECT DepNo FROM department
    WHERE DepName = '网络技术系'
);
+-------+------+--------+
| name  | sex  | salary |
+-------+------+--------+
| Tom   | 女   |   4500 |
| James | 女   |   3500 |
+-------+------+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

10.7 练习5

-- 1. 建立数据库 test_student:
mysql> CREATE DATABASE test_student;;
Query OK, 1 row affected (0.01 sec)

-- 2. 使用数据库:
mysql> USE test_student;
Database changed
1
2
3
4
5
6
7

2. 建立以下三张表, 并插入记录:
表1: 班级表(calsses)
1
2

专业	班级	姓名	姓别	座位
`计算机网络`	`1班`	`张三`	`男`	`8`
`软件工程`	`2班`	`李四`	`男`	`12`
`计算机维护`	`1班`	`王五`	`男`	`9`
`计算机网络`	`2班`	`LILY`	`女`	`15`
`软件工程`	`1班`	`小强`	`男`	`20`
`计算机维护`	`1班`	`CoCo`	`女`	`18`

-- 创建班级表:
mysql> CREATE TABLE classes (
    major VARCHAR(20),
    class VARCHAR(20),
    name VARCHAR(20),
    gender CHAR(1),
    seat INT
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO classes VALUES 
('计算机网络', '1班', '张三', '男', 8),
('软件工程', '2班', '李四', '男', 12),
('计算机维护', '1班', '王五', '男', 9),
('计算机网络', '2班', 'LILY', '女', 15),
('软件工程', '1班', '小强', '男', 20),
('计算机维护', '1班', 'CoCo', '女', 18);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

表2: 成绩表(Score)
1

姓名	英语	数学	语文
`张三`	`65`	`75`	`98`
`李四`	`87`	`45`	`86`
`王五`	`98`	`85`	`65`
`LILY`	`75`	`86`	`87`
`小强`	`85`	`60`	`58`
`CoCo`	`96`	`87`	`70`

-- 创建成绩表:
mysql> CREATE TABLE score(
    name VARCHAR(20),
    english INT,
    maths INT,
    chinese INT
);
Query OK, 0 rows affected (0.03 sec)

-- 插入数据:
mysql> INSERT INTO score VALUES
('张三', 65, 75, 98),
('李四', 87, 45, 86),
('王五', 98, 85, 65),
('LILY', 75, 86, 87),
('小强', 85, 60, 58),
('CoCo', 96, 87, 70);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

表3: 记录(Records)
1

姓名	记录
`小强`	`迟到`
`小强`	`事假`
`李四`	`旷课`
`李四`	`旷课`
`李四`	`迟到`
`CoCo`	`病假`
`LILY`	`事假`

-- 创建记录表:
mysql> CREATE TABLE records(
    name VARCHAR(20),
    record VARCHAR(20)
);
Query OK, 0 rows affected (0.01 sec)

-- 插入数据:
mysql> INSERT INTO records VALUES
('小强', '迟到'),
('小强', '事假'),
('李四', '旷课'),
('李四', '旷课'),
('李四', '迟到'),
('CoCo', '病假'),
('LILY', '事假');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 3. 写出将张三的语文成绩修改为88的SQL语句:
mysql> UPDATE score SET chinese = 88 WHERE name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4

-- 4. 搜索出计算机维护1班各门课程的平均成绩:
mysql> SELECT AVG(english), AVG(maths), AVG(chinese) 
FROM score
WHERE name IN (
    SELECT name FROM classes 
    WHERE major = '计算机维护' AND class = '1班'
);
+--------------+------------+--------------+
| AVG(english) | AVG(maths) | AVG(chinese) |
+--------------+------------+--------------+
|      97.0000 |    86.0000 |      67.5000 |
+--------------+------------+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 5. 搜索科目有不及格的人的名单:
mysql> SELECT name FROM score 
WHERE english < 60 OR maths < 60 OR chinese < 60;
+------+
| name |
+------+
| 李四 |
| 小强 |
+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

-- 6. 查询记录2次以上的学生的姓名和各科成绩:

mysql> SELECT * FROM score
WHERE name IN (
    SELECT name FROM (
        SELECT name, COUNT(*) AS `num`
        FROM records 
        GROUP BY name
        HAVING num > 2) AS `two_record`
);
+------+---------+-------+---------+
| name | english | maths | chinese |
+------+---------+-------+---------+
| 李四 |      87 |    45 |      86 |
+------+---------+-------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

10.8 练习6

-- 1. 建立数据库: test_xuankedb:
mysql> CREATE DATABASE test_xuankedb;
Query OK, 1 row affected (0.01 sec)

-- 使用数据库:
USE test_xuankedb;
1
2
3
4
5
6

2. 建立如下三张表: 
   1. 学生表Student由学号(Sno), 姓名(Sname), 性别(Ssex), 年龄(Sage), 所在系(Sdept)五个字段, Sno为关键字(主键).
   2. 课程表Course由课程号(Cno), 课程名(Cname), 选修课号(Cpno), 学分(Ccredit)四个字段, Cno为关键字(主键).
   3. 成绩表SG由学号(Sno), 课程号(Cno), 成绩(Grade)三个字段, (SNO, CNO)为关键字(联合主键, 并且分别关联其他两张表).
1
2
3
4

-- 创建学生表:
mysql> CREATE TABLE student(
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    ssex CHAR(1),
    sage INT,
    sdept VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES  
(5011, '张三', '男', 20, '计算机系'),  
(5012, '李四', '男', 21, '数学系'),  
(5013, '王五', '女', 19, '计算机系'),  
(5014, '赵六', '男', 20, '数学系'),  
(5019, '李洋', '男', 22, '物理系');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 创建课程表:
mysql> CREATE TABLE course(
    cno INT PRIMARY KEY,
    cname VARCHAR(20),
    cpno INT,
    ccredit INT
);
Query OK, 0 rows affected (0.02 sec)

mysql>  INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES  
(1, '数据库', 2, 4),  
(2, '计算机网络', 3, 3),  
(3, '数据结构', 4, 4),  
(4, '操作系统', 1, 3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 创建成绩表:
mysql> CREATE TABLE SG (
    sno INT,
    cno INT,
    grade INT,
    PRIMARY KEY(sno, cno),
    FOREIGN KEY (sno) REFERENCES student(sno),
    FOREIGN KEY (cno) REFERENCES course(cno)
);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO SG (Sno, Cno, Grade) VALUES  
(5011, 1, 85),  
(5011, 3, 90),  
(5012, 2, 78),  
(5013, 1, 92),  
(5013, 3, 88),  
(5014, 4, 76),  
(5019, 1, 82);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- 3. 向Student表增加'入学时间(Scome)'列, 其数据类型为日期型.
mysql> ALTER TABLE student ADD Scome DATE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4

-- 4. 查询选修了3号课程的学生的学号及其成绩, 查询结果按分数的降序排列.
mysql> SELECT sno, grade FROM SG
WHERE cno = 3
ORDER BY grade DESC;
+------+-------+
| sno  | grade |
+------+-------+
| 5011 |    90 |
| 5013 |    88 |
+------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

-- 5. 查询学习1号课程的学生最高分数, 平均成绩:
SELECT max(grade), AVG(grade) 
FROM SG WHERE Cno = 1;
+------------+------------+
| max(grade) | AVG(grade) |
+------------+------------+
|         92 |    86.3333 |
+------------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

-- 6. 查询与'李洋'在同一个系学习的学生信息:
mysql> SELECT * FROM Student WHERE sdept IN (
    SELECT Sdept FROM Student 
    WHERE sname = '李洋'
);
+-------+
| sname |
+-------+
| 李洋  |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

-- 7. 将计算机系全体学生的成绩置零:
mysql> UPDATE SG SET grade = 0 
WHERE sno IN (
    SELECT sno FROM student 
    WHERE Sdept = '计算机系'
);
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

-- 查看数据:
mysql> SELECT * FROM SG;
+------+-----+-------+
| sno  | cno | grade |
+------+-----+-------+
| 5011 |   1 |     0 |
| 5011 |   3 |     0 |
| 5012 |   2 |    78 |
| 5013 |   1 |     0 |
| 5013 |   3 |     0 |
| 5014 |   4 |    76 |
| 5019 |   1 |    82 |
+------+-----+-------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 8. 删除学生表中学号为5019的学生记录.
-- 先添加级联等级(ON UPDATE CASCADE ON DELETE SET NULL), 否则无法删除数据.
-- 8.1 获取外键名称:
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'SG';
... CONSTRAINT_NAME
... sg_ibfk_1

-- 8.2 删除外键:
mysql> ALTER TABLE SG DROP FOREIGN KEY sg_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 8.3 查看索引(Key_name列显示了索引的名称):
mysql> SHOW INDEX FROM SG;
... Key_name 
... PRIMARY
..  PRIMARY  -- PRIMARY：这是复合主键索引, 它包含了sno和cno两列
... cno      -- 这是一个额外的索引, 只针对cno列. 这个索引可能是由MySQL自动创建的, 用以优化涉及cno列的查询.
             -- 尤其是外键约束可能会用到这个索引。
-- 没有对应的sno, 不用删除了

-- 8.4 删除索引:
-- mysql> ALTER TABLE SG DROP INDEX sno;

-- 8.5 增加外键约束并设置级联:
mysql> ALTER TABLE SG ADD
FOREIGN KEY (sno) REFERENCES Student(sno)  
ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

-- 8.6 查看级联设置:
mysql> SHOW CREATE TABLE SG;
... ON DELETE CASCADE ON UPDATE CASCADE

-- 8.7 删除记录
mysql> DELETE FROM student WHERE sno = '5019';
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- 9. 删除计算机系所有学生的成绩记录:
DELETE FROM SG
WHERE sno IN (
    SELECT sno FROM student 
    WHERE sdept = '计算机系'
);
Query OK, 4 rows affected (0.01 sec)
1
2
3
4
5
6
7

10.9 练习7

-- 1. 建立数据库: test_library
mysql> CREATE DATABASE test_library;
Query OK, 1 row affected (0.01 sec)

-- 使用数据库:
USE test_library;
1
2
3
4
5
6

2. 建立如下三个表: 
   表1: press 出版社 
   属性: 编号pressid(int), 名称pressname(varchar), 地址address(varchar)
   
   表2: sort 种类 
   属性: 编号sortno(int), 数量scount(int)
   
   表3: book图书 
   属性: 编号bid(int), 名称 bname(varchar), 种类bsortno(int), 出版社编号pressid(int)
1
2
3
4
5
6
7
8
9

-- 创建出版社表
mysql> CREATE TABLE press(
    pressid INT,
    pressname VARCHAR(20),
    address VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)

-- 创建种类表:
mysql> CREATE TABLE sort(
    sortno INT,
    scount INT
)
Query OK, 0 rows affected (0.02 sec)

-- 创建图书表:
mysql> CREATE TABLE book(
    bid INT,
    BNAME varchar(20),
    bsortno INT,
    pressid INT
);
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 3. 给sort表中添加一列属性: 描述describes(varchar):
mysql> ALTER TABLE sort ADD describes VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4

-- 4. 向三个表中各插入几条数据
mysql> INSERT INTO press VALUES
(100, '外研社', '上海'),
(101, '北大出版社', '北京'),
(102, '教育出版社', '北京');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO sort VALUES
(11, 50, '小说'),
(12, 300, '科幻'),
(13, 100, '神话');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO book VALUES
(1, '红与黑', 11, 100),
(2, '幻城', 12, 102),
(3, '希腊神话', 13, 102);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- 5. 查询出版社id为100的书的全部信息:
mysql> SELECT * FROM book WHERE pressid = 100;
+------+--------+---------+---------+
| bid  | BNAME  | bsortno | pressid |
+------+--------+---------+---------+
|    1 | 红与黑 |      11 |     100 |
+------+--------+---------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 6. 查询出版社为外研社的书的全部信息:
mysql> SELECT * FROM book 
WHERE pressid IN(
    SELECT pressid FROM press 
    WHERE pressname = '外研社');
+------+--------+---------+---------+
| bid  | BNAME  | bsortno | pressid |
+------+--------+---------+---------+
|    1 | 红与黑 |      11 |     100 |
+------+--------+---------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

-- 7.查询图书数量(scount)大于100的种类:
mysql> SELECT * FROM sort WHERE scount > 100;
+--------+--------+-----------+
| sortno | scount | describes |
+--------+--------+-----------+
|     12 |    300 | 科幻      |
+--------+--------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 8. 查询图书种类最多的出版社信息:
mysql> SELECT pressname FROM press
WHERE pressid IN (
    SELECT pressid FROM book 
    WHERE bsortno IN (
        SELECT sortno FROM sort
        WHERE scount IN (
            SELECT MAX(scount) FROM sort
        )
    )
);
+------------+
| pressname  |
+------------+
| 教育出版社 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

10.10 练习8

-- 1. 建立数据库：test_tour
mysql> CREATE DATABASE test_tour;

-- 使用数据库:
mysql> USE test_tour;
Database changed
1
2
3
4
5
6

2. 建立如下两个表
表1: 旅行社表(agency)
1
2

列名（英文名）	列名（中文名）	数据类型	允许空值	说明
`Id`	`旅行社编号`	`int`	`no`	主键
`Name`	`旅行社名`	`varchar`	`no`
`Address`	`旅行社地址`	`varchar`	`no`
`Areaid`	`所属区域Id`	`int`	`yes`

-- 创建旅行社表:
mysql> CREATE TABLE agency(
   id INT PRIMARY KEY,
   name VARCHAR(20) NOT NULL,
   address VARCHAR(20) NOT NULL,
   areaid INT
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO agency(id, name, address) VALUES
(101, '青年旅行社', '北海海淀'),
(102, '天天旅行社', '天津海院');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

表2: 旅行线路表(travel)
1

列名（英文名）	列名（中文名）	数据类型	允许空值	说明
`Tid`	`旅行线路编号`	`int`	`no`	`主键`
`Time`	`所需时间`	`varchar`	`no`
`Position`	`目的地`	`varchar`	`no`
`Money`	`花费`	`Float`	`yes`
`Aid`	`所属旅行社id`	`Int`	`no`	`外键`
`Count`	`报名人数`	`Int`	`yes`

-- 创建旅行线路表:
mysql> CREATE TABLE travel(
    tid INT PRIMARY KEY,
    time VARCHAR(20) NOT NULL,
    position  VARCHAR(20) NOT NULL,
    money FLOAT,
    aid INT,
    Count INT,
    FOREIGN KEY (aid) REFERENCES agency(id)  
);
Query OK, 0 rows affected (0.04 sec)

-- 插入数据:
mysql> INSERT INTO travel VALUES
(1, '5天', '八达岭', 300, 101, 10),
(2, '7天', '水长城', 5000, 101, 14),
(3, '8天', '水长城', 6000, 102, 11);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 4. 查出旅行线路最多的旅社:
mysql> SELECT name FROM agency 
WHERE id IN (
    SELECT aid FROM (
        SELECT aid, COUNT(*) AS `count_num`
        FROM travel GROUP BY aid
        ORDER BY count_num DESC
        LIMIT 1
    ) AS `most_tab`
);
+------------+
| name       |
+------------+
| 青年旅行社 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 5. 查出最热门的旅行线路(也就是查询出报名人数最多的线路):
SELECT position FROM travel
WHERE `count` = (
    SELECT MAX(`count`)  -- count是关键字
    FROM travel
);
+----------+
| position |
+----------+
| 水长城   |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

-- 6. 花费少于5000的旅行线路:
mysql> SELECT Position FROM travel WHERE money < 5000;
+----------+
| Position |
+----------+
| 八达岭   |
+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8

-- 7. 找到一次旅行花费最昂贵的旅行社名:
mysql> SELECT name FROM agency
WHERE id = (  -- 这里只能使用=而不能使用IN
    SELECT aid FROM travel 
    ORDER BY money DESC
    LIMIT 1
);
+------------+
| name       |
+------------+
| 天天旅行社 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 8. 查出青年旅行社所有的旅行线路都玩一遍需要多少时间:
mysql> SELECT SUM(time)  -- 隐式转化
FROM travel 
WHERE aid = (
    SELECT id FROM agency 
    WHERE name = '青年旅行社'
);
+-----------+
| SUM(time) |
+-----------+
|        12 |
+-----------+
1
2
3
4
5
6
7
8
9
10
11
12

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/367654