1. 数据完整性约束
1.1 数据完整性
数据完整性是数据库设计的核心原则之一 , 它确保了数据的精确性和可靠性 , 从而维护了数据库的质量 .
为了防止数据库中存储不符合语义规定的数据 , 以及防止由于错误信息的输入或输出导致的无效操作或错误信息的产生 , 引入了约束这一概念 .
SQL规范通过约束对表数据进行额外的条件限制 , 以确保数据的完整性和一致性 .
这些约束在多个层面上起到了关键作用 , 具体如下 :
* 1. 实体完整性 ( Entity Integrity ) :
实体完整性保证了表中的每一行数据都是唯一的 , 可以被准确地识别和区分 .
例如 , 在一个包含员工信息的表中 , 每个员工都应该有一个唯一的标识 , 如员工ID , 以确保不会有两条完全相同的记录存在 .
这有助于防止数据冗余和混淆 .
* 2. 域完整性 ( Domain Integrity ) :
域完整性关注的是数据列中值的范围或格式 . 它确保了数据列中的值符合预定义的标准或条件 .
例如 , 年龄列的值应该限制在 0 到 120 之间 , 性别列的值只能是 '男' 或 '女' .
这些限制有助于防止无效或不合理的数据进入数据库 .
* 3. 引用完整性 ( Referential Integrity ) :
引用完整性确保了两个表之间的关系得到正确的维护 .
当一个表中的记录引用另一个表中的记录时 , 引用完整性要求被引用的记录必须存在 .
例如 , 员工表中的部门字段必须对应于部门表中实际存在的部门记录 .
这有助于保持数据的一致性 , 并防止由于引用不存在的记录而导致的错误 .
* 4. 用户自定义完整性 ( User-defined Integrity ) :
除了上述标准约束外 , 用户还可以根据特定的业务需求定义自定义的完整性规则 .
这些规则可以根据特定的业务逻辑或条件对数据进行限制 .
例如 , 可以规定用户名必须是唯一的 , 密码字段不能为空 , 或者部门经理的工资不得超过本部门员工平均工资的某个倍数 .
这些自定义完整性规则有助于确保数据库中的数据符合特定的业务要求 .
综上所述 , 约束在数据库设计中扮演着至关重要的角色 .
它们确保了数据的完整性和一致性 , 从而提高了数据库的质量和可靠性 .
通过合理地应用这些约束 , 我们可以有效地防止数据错误和无效操作的发生 , 保护数据库免受潜在的风险和损害 .
1.2 约束类型
约束是数据库表级的重要强制规定 , 它们确保了表中数据的完整性和准确性 .
常见的约束类型包括 :
* 1. NOT NULL 非空约束 : 确保字段不能为空 , 即必须包含值 .
* 2. UNIQUE 唯一约束 : 确保字段或字段组合在表中的值是唯一的 , 但不排除NULL值 ( 除非列被定义为NOT NULL ) .
* 3. PRIMARY KEY 主键约束 : 标识表中的唯一记录 , 一个表只能有一个主键 , 且主键字段的值不能为空 .
* 4. FOREIGN KEY 外键约束 : 定义了两个表之间的关联关系 , 确保子表中的记录所引用的父表记录确实存在 .
* 5. DEFAULT 默认值约束 : 为列提供默认值 , 当插入新记录但没有为该列提供值时 , 将使用此默认值
这些约束类型可以根据具体的应用场景和需求进行选择和组合 , 以确保数据库中的数据完整性和准确性 .
根据不同的标准和需求 , 可以分为多种类型 :
* 1. 根据约束数据列的限制 , 约束可分为 :
1. 单列约束 : 这种约束作用于表中的单个列 , 确保该列的数据满足特定的条件或规则 .
例如 , NOT NULL约束就是一个典型的单列约束 , 它要求该列不能有NULL值 .
2. 多列约束 : 与单列约束不同 , 多列约束涉及表中的多个列 , 确保这些列的组合满足特定的条件或规则 .
例如 , UNIQUE约束可以应用于多个列的组合 , 以确保这些列的组合值在整个表中是唯一的 .
* 2. 根据约束的作用范围 , 约束可分为 :
1. 列级约束 : 列级约束仅对表中的单个列起作用 , 它们直接定义在列的数据类型之后 .
这种约束是针对特定列的 , 并且只影响该列的数据 .
列级约束既可以在创建表时定义 , 也可以在表创建后通过ALTER TABLE语句添加 .
2. 表级约束 : 与列级约束不同 , 表级约束可以作用于表中的多个列 , 它们不与特定的列一起定义 , 而是作为表的独立部分进行定义 .
表级约束通常用于定义跨多个列的关系或条件 , 例如外键约束 .
在创建表时 , 可以直接通过CREATE TABLE语句来定义这些约束 , 以在数据插入或更新时实施特定的规则和条件 .
同样地 , 如果表已经存在 , 也可以使用ALTER TABLE语句来添加 , 修改或删除约束 .
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为你想要查询的表名 .
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)
列名解释 :
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 .
2. 非空约束
2.1 特性
非空约束 ( NOT NULL Constraint ) : 是数据库中的一种约束 , 用于确保表中某列不允许为空值 .
其主要特性包括 :
* 1. 不可为空 : 设置了非空约束的字段在插入或更新数据时 , 必须提供有效的值 , 不能留空 .
如果尝试插入或更新一个空值 , 数据库系统会报错并拒绝这些操作 .
* 2. 维护数据完整性 : 非空约束通过确保特定列始终包含有效值 , 从而维护数据的完整性和一致性 .
它有助于防止因空值导致的数据错误或不一致问题 .
* 3. 强制实施 : 非空约束是强制性的 , 数据库系统会自动检查并应用这一约束 , 无需用户手动检查或干预 .
在实际应用中 , 非空约束常常用于那些必须提供值的字段 , 例如用户信息表中的用户名 , 邮箱等字段 .
通过设置非空约束 , 可以确保这些字段在插入或更新记录时始终包含有效的数据 , 从而提高数据的质量和可靠性 .
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将抛出一个错误 .
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 ) ,
id_card char ( 18 ) not null
) ;
Query OK, 0 rows affected ( 0.02 sec)
mysql> DESC emp0;
+
| 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)
mysql> INSERT INTO emp0 VALUES ( 2 , '李四' , '13710011002' , null ) ;
ERROR 1048 ( 23000 ) : Column 'id_card' cannot be 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)
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语句来添加非空约束 .
mysql> CREATE TABLE emp1(
id INT ( 10 ) NOT NULL ,
name VARCHAR ( 20 ) NOT NULL ,
gender VARCHAR ( 20 ) 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)
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 ( 如果未指定其他值 ) .
mysql> ALTER TABLE emp1 MODIFY COLUMN name VARCHAR ( 20 ) NULL ;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
3. 唯一约束
3.1 特性
唯一约束 ( Unique Key ) : 用于确保某个列或字段的值在该列或字段中是唯一的 .
它主要用于防止表中出现重复的值 , 确保数据的完整性和一致性 .
主要特性和作用如下 :
* 1. 确保数据唯一性 : 唯一约束确保表中的一列或多列的组合中的值是唯一的 .
这意味着在这些列上 , 不能有两行或更多行具有相同的值 .
* 2. 允许NULL值 : 唯一约束允许列中有多个NULL值 .
这是因为NULL在数据库中通常表示 '未知' 或 '缺失' 的数据 , 而两个未知的值并不一定是相同的 .
* 3. 可以在多列上定义 : 唯一约束不仅可以定义在单个列上 , 也可以定义在多个列的组合上 .
这确保了这些列的组合值是唯一的 .
* 4. 自动创建唯一索引 : 当在MySQL中为某个列或列组合定义唯一约束时 , 系统会自动为该列或列组合创建一个唯一索引 .
这个索引可以加速查询操作 , 提高数据库性能 .
* 5. 在插入和更新时强制执行 : 数据库管理器在插入和更新操作期间强制执行唯一约束 , 以确保数据完整性 .
如果尝试插入或更新违反唯一约束的数据 , 数据库将拒绝这些操作并返回错误 .
* 6. 可以命名或默认命名 : 当定义唯一约束时 , 如果不给约束命名 , MySQL会默认使用列名作为约束名 .
通过合理使用唯一约束 , 可以确保数据库中的数据准确性和一致性 , 避免重复数据的出现 .
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等列的组合值在表中是唯一的 .
* 在定义时没有写约束名称 , 系统默认将字段名设置为约束名称 , 这里先省略掉约束名称 , 后续单独说明 .
3.2.1 单列唯一
示例 , 创建一个emp员工信息表 , 其中邮箱 , 用户名不能够重复 :
CREATE TABLE emp (
id INT ,
name VARCHAR ( 50 ) UNIQUE
email VARCHAR ( 255 ) UNIQUE NOT NULL ,
) ;
在这个例子中 , name和email列都被定义为具有唯一约束 , 这意味着每个用户的name和email都必须是唯一的 .
mysql> CREATE TABLE emp2(
id INT ,
name VARCHAR ( 20 ) ,
phone CHAR ( 11 ) UNIQUE ,
id_card CHAR ( 18 ) UNIQUE KEY
) ;
Query OK, 0 rows affected ( 0.02 sec)
mysql> DESC emp2;
+
| 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'
mysql> INSERT INTO emp2 VALUES ( 3 , '王五' , '13710011003' , '101223199012015625' ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '13710011003' for key 'emp2.phone'
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中的单个列的值可以在其他行中重复 , 但是它们的组合必须是唯一的 .
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;
+
| 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)
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'
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. 如果你的表非常大 , 添加唯一约束可能会需要一些时间 , 并且可能会锁定表 , 影响性能 .
在这种情况下 , 最好在低峰时段执行此操作 .
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)
mysql> ALTER TABLE emp3 ADD UNIQUE ( name) ;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
mysql> INSERT INTO emp3 VALUES ( 2 , 'kid' , '13710011005' , '101223199012015625' ) ;
ERROR 1062 ( 23000 ) : Duplicate entry 'kid' for key 'emp3.name'
mysql> INSERT INTO emp3 VALUES ( 2 , 'qq' , '13710011004' , '101223199012015624' ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '13710011004-101223199012015624' for key 'emp3.phone'
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是唯一约束的自定义名称 .
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)
SHOW INDEX FROM emp3;
mysql> ALTER TABLE emp3 DROP INDEX name;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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
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)
4. 主键约束
4.1 特性
主键约束 ( PRIMARY KEY ) : 用于确保数据表中的数据唯一性的重要工具 .
当为一个表或表的列定义 PRIMARY KEY 约束时 , DBMS会自动为这些列创建一个唯一索引 , 该索引不允许任何重复值 , 并且不允许NULL值 .
它具备以下几个关键的约束特性 :
* 1. 唯一性 : 主键约束要求主键列的值在表中必须是唯一的 .
不允许有重复值这保证了表中每一行数据的唯一性 , 有助于维护数据的完整性和一致 .
* 2. 非空性 : 主键列的值不能为空 .
这是因为主键的主要作用是标识表中的每一行数据 , 如果允许主键值为空 , 那么将无法准确地标识每一行数据 .
* 3. 数据完整性保护 : 主键约束防止了表中出现重复的记录 , 确保了数据的完整性 .
无法插入具有相同主键值的记录 , 从而避免了数据冗余和不一致 .
* 4. 加速数据访问 : 由于主键的唯一性 , 数据库引擎可以使用主键来加速数据的查找和连接操作 , 提高数据访问的性能 .
这使得主键在查询优化中起到了关键的作用 .
* 5. 外键关联 : 主键约束通常用于定义外键关系 , 将不同表之间的记录关联起来 , 建立数据库的关联性 .
这使得可以实现更复杂的数据查询和管理操作 , 提高数据库的灵活性和可扩展性 .
* 6. 引用完整性 : 在关系数据库中 , PRIMARY KEY 常常与其他表的 FOREIGN KEY 约束一起使用 , 以确保引用完整性 .
综上所述 , 主键约束通过确保数据的唯一性 , 非空性 , 完整性以及加速数据访问等特性 , 为数据库的设计和查询优化提供了重要的支持 .
在数据库设计中 , 设置id列并将它设置为主键是一个常见的做法 , 主要基于以下几个原因 :
* 1. 唯一性 : 主键的主要作用是确保表中每一行数据的唯一性 .
通过为主键列 ( 如id ) 分配唯一的值 , 可以确保不会有两行数据具有相同的标识 .
* 2. 引用完整性 : 当在多个表之间建立关系时 ( 例如 , 通过外键约束 ) , 主键是确保数据引用完整性的关键 .
其他表可以通过外键引用这个主键 , 从而确保数据的关联性和一致性 .
* 3. 性能优化 : 数据库系统通常为主键列自动创建唯一索引 .
这个索引可以极大地加速基于主键的查询操作 , 因为数据库系统可以利用这个索引快速定位到所需的数据行 .
* 4. 简化数据操作 : 使用主键可以简化数据的插入 , 更新和删除操作 .
例如 , 当需要更新或删除特定行时 , 可以通过主键快速定位到该行 .
* 5. 易于理解 : 使用id作为主键列名是一个通用的命名约定 , 这使得其他开发者或数据库管理员更容易理解和维护数据库结构 .
* 6. 自增特性 : 在很多数据库系统中 , id列可以设置为自增 ( AUTO_INCREMENT ) ,
这意味着每当插入新行时 , 系统会自动为这个列生成一个新的 , 唯一的值 , 无需手动指定 /
这进一步简化了数据插入操作 .
* 7. 避免数据泄露 : 使用自增的整数id作为主键 , 而不是使用敏感信息 ( 如用户名或邮箱地址 ) 作为主键 , 有助于保护用户隐私和数据安全 .
综上所述 , 设置id列并将其设置为主键是数据库设计中的一个基本和重要的原则 , 它有助于确保数据的唯一性 , 完整性 , 安全性和查询性能 .
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. 主键值的修改 : 通常不建议修改主键字段的值 , 因为主键是数据记录的唯一标识 .
修改主键的值可能会破坏数据的完整性 , 并可能导致与其他表的外键约束冲突 .
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 会抛出一个错误 .
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)
mysql> INSERT INTO emp4 VALUES ( 1 , '张三' ) ;
ERROR 1062 ( 23000 ) : Duplicate entry '1' for key 'emp4.PRIMARY'
mysql> INSERT INTO emp4 VALUES ( null , '张三' ) ;
ERROR 1048 ( 23000 ) : Column 'id' cannot be null
4.2.2 联合主键
下面是一张成绩单 , 这个表格包含了id , 学生姓名 ( name ) , 课程 ( courses ) 以及学生在该课程中的得分 ( score ) .
每一行代表一个学生在一门课程中的得分情况 .
id
name
courses
score
1
张三
Java
89
2
张三
MySQL
90
3
李四
Java
88
4
李四
MySQL
56
表格中数据出现重复 , 可将表格拆分三张表 :
* 1. 存放学生信息的学生表 .
* 2. 存放课程信息的课程表 .
* 3. 存放学生信息与课程信息对应关系的关联表 .
学生表(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
差分后 , 当数据量庞大时能够避免数据的冗余 .
mysql> CREATE TABLE student0(
sid INT PRIMARY KEY ,
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)
mysql> CREATE TABLE courses0(
cid INT PRIMARY KEY ,
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)
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'
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. 多个约束 : 一个表可以有多个联合唯一约束 .
使用场景 :
联合主键通常用于那些自然就是唯一组合的场景 , 比如多对多关系中的中间表 , 其中两个外键的组合能够唯一标识一个关系 .
联合唯一则更多用于那些需要确保某些字段组合唯一 , 但不需要作为主键的场景 .
例如 , 你可能想要确保用户的邮箱和手机号组合是唯一的 , 但不需要将它们作为主键 .
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. 在尝试给已存在的表添加主键约束之前 , 确保表中的数据满足主键的唯一性和非空性要求 ; 否则 , 会收到一个错误 .
如果正在尝试添加的主键约束与表中现有的数据冲突 ( 例如 , 有重复的值或空值 ) ,
需要先更新或删除那些不符合主键要求的数据 , 然后再尝试添加主键约束 .
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)
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)
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)
4.4 删除主键约束
在MySQL中 , 不能直接删除一个主键 , 但可以通过修改表来移除它 .
以下是移除主键的示例语法 :
ALTER TABLE 表名 DROP PRIMARY KEY ;
移除主键约束 , 不需要指定主键名 , 因为一个表只有一个主键 , 删除主键约束后 , 非空特性还存在 .
注意事项 :
* 在删除主键之前 , 请确保没有依赖于该主键的外键约束 , 否则删除操作可能会失败 .
因为外键约束要求相关联的值必须存在于引用表中的主键列中 .
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)
5. 自增属性
自增属性 ( AUTO_INCREMENT ) : 在MySQL中是一个用于生成唯一标识符的特性 , 通常与主键一起使用 .
当为一个表的主键列设置自增属性时 , MySQL会自动为新插入的每一行生成一个唯一的数字 .
这使得开发者无需手动为每一行指定一个主键值 , 从而简化了数据插入的过程 .
在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.
5.1 定义自增属性
下面这个例子中 , id列是一个自增列 , 每次插入新记录时 , 它的值都会自动增加 .
CREATE TABLE example_table (
id INT PRIMARY KEY AUTO_INCREMENT ,
name VARCHAR ( 255 ) NOT NULL
) ;
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
mysql> CREATE TABLE emp5(
id INT PRIMARY KEY ,
name VARCHAR ( 20 ) AUTO_INCREMENT
) ;
ERROR 1063 ( 42000 ) : Incorrect column specifier for column 'name'
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)
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)
mysql> INSERT INTO emp5 VALUES ( 4 , 'qz' ) , ( null , 'ez' ) ;
Query OK, 2 rows affected ( 0.00 sec)
Records: 2 Duplicates: 0 Warnings : 0
mysql> SELECT * FROM emp5;
+
| id | name |
+
| 1 | kid |
| 2 | qq |
| 4 | qz |
| 5 | ez |
+
4 rows in set ( 0.00 sec)
5.2 添加自增属性
在MySQL中 , 可以使用ALTER TABLE语句来修改表结构 , 为表中的字段添加自增属性 ( AUTO_INCREMENT ) .
添加前 , 确保该字段是主键或具有唯一约束 , 因为AUTO_INCREMENT 属性只能应用于这样的字段 .
添加自增属性语法 :
ALTER TABLE table_name MODIFY column_name datatype AUTO_INCREMENT ;
这里 , table_name 是你的表名 , column_name 是你希望添加自增属性的字段名 , datatype 是该字段的数据类型 .
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
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)
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值 ( 如果允许 ) .
示例 , 假设你有一个名为emp的表 , 其中有一个名为id的字段 , 该字段具有自增属性 .
如果你想删除id字段的自增属性 , 你可以使用以下语句 :
ALTER TABLE emp MODIFY id INT ;
在这个例子中 , 我们假设id字段的数据类型是INT .
这条ALTER TABLE语句将修改id字段 , 移除它的自增属性 , 但保留它的数据类型 .
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)
5.4 自增变量的持久化
在MySQL中 , 自增主键的持久化问题主要涉及到自增值在服务器重启或表结构变更后的行为 .
在MySQL的不同版本中 , 对于自增值的处理有所不同 .
MySQL 8.0 之前的版本中 , AUTO_INCREMENT的值并不总是持久化存储的 ( 存储在内存中 ) .
当MySQL服务器重启或执行某些特定的ALTER TABLE操作时 , AUTO_INCREMENT的值可能会被重置为当前表中最大主键值加 1.
这意味着 , 如果之前手动插入了一个较大的自增值 , 然后在服务器重启后尝试插入新记录 , 可能会遇到主键冲突的问题 .
MySQL 8.0 及更高版本 , AUTO_INCREMENT的值得到了更好的持久化处理 .
在大多数情况下 , 即使服务器重启或执行ALTER TABLE操作 , AUTO_INCREMENT的值也会保持不变 .
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)
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 |
+
4 rows in set ( 0.00 sec)
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)
从结果可以看出 , 新插入的 0 值分配的是 4 , 按照重启前的操作逻辑 , 此处应该分配 6.
出现上述结果的主要原因是自增主键没有持久化 .
在MySQL 5.7 系统中 , 对于自增主键的分配规则 , 是由InnoDB数据字典内部一个计数器来决定的 ,
而该计数器只在内存中维护 , 并不会持久化到磁盘中 , 当数据库重启时 , 该计数器会被初始化 .
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)
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 |
+
4 rows in set ( 0.00 sec)
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)
MySQL 8.0 将自增主键的计数器持久化到重做日志中 .
每次计数器发生改变 , 都会将其写入重做日志中 .
如果数据库重启 , InnoDB会根据重做日志中的信息来初始化计数器的内存值 .
6. 外键约束
6.1 特性
外键约束 ( Foreign Key Constraint ) : 是数据库管理系统 ( DBMS ) 中用于确保数据引用完整性的一种规则 .
它指定一个字段 ( 或字段组合 ) 的值必须在另一个表的主键或唯一约束字段中存在 .
以下是外键约束的主要特性 :
* 1. 引用完整性 : 外键约束的主要目的是维护引用完整性 .
这意味着 , 在一个表中定义的外键列中的每个值 , 都必须在被引用的表的主键列或具有唯一约束的列中存在 .
这确保了数据之间的关系始终保持一致 .
* 2. 级联操作 : 当在外键所引用的主表 ( 父表 ) 中进行某些操作时 ( 如更新或删除 ) ,
可以配置外键约束以自动执行相应的操作在子表 ( 引用外键的表 ) 上 ,
这被称为级联操作 , 包括级联更新和级联删除 .
* 3. 阻止无效操作 : 如果尝试插入或更新子表中的记录 , 而该记录的外键值在被引用的父表中不存在 ,
那么数据库会拒绝该操作 , 并可能抛出一个错误 . 这有助于防止数据不一致的情况 .
* 4. 可选性与非空性 : 外键列可以是可选的 ( 允许NULL值 ) , 也可以是非空的 ( 不允许NULL值 ) , 这取决于业务规则和数据模型的需求 .
* 5. 索引 : 大多数数据库管理系统会自动为外键列创建索引 , 以加速引用完整性的检查过程 . 这有助于提高查询性能 .
* 6. 多对一与多对多关系 : 外键约束不仅支持多对一的关系 ( 一个子表的记录可以引用一个父表的记录 ) ,
还支持实现多对多的关系 ( 通过中间表 ) .
* 6. 可删除性与可更新性 : 外键约束可以配置为在父表的主键被删除或更新时采取特定的操作 ,
如限制删除 / 更新 , 级联删除 / 更新或设置NULL值 .
* 7. 增强数据质量 : 通过确保数据之间的正确关系 , 外键约束有助于减少数据冗余和错误 , 从而提高数据质量 .
需要注意的是 , 虽然外键约束在维护数据一致性和完整性方面非常有用 ,
但它们也可能对性能产生一定的影响 , 特别是在执行涉及大量数据的操作时 .
因此 , 在设计数据库和选择是否使用外键约束时 , 需要权衡这些因素 .
子表与从表 :
主表 ( 父表 ) : 作为数据参考基准的表 , 通常包含基础数据或核心信息 , 具有主键字段 , 该字段的值具有唯一性 .
从表 ( 子表 ) : 依赖主表数据的表 , 通过外键字段与主表的主键字段相关联 , 用于存储与主表相关的详细信息或扩展数据 .
举例 : 员工表和部门表 : 部门表存储部门的基本信息 , 作为主表 ; 员工表通过部门ID字段与部门表关联 , 作为从表 .
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. 索引与外键约束 : 创建外键约束时 , 系统默认会在外键列上建立对应的索引 , 以提高查询效率 .
如果删除了外键约束 , 应手动删除对应的索引 , 避免不必要的性能开销和存储空间占用 .
下面有一张部门表 :
员工编号
员工姓名
部门名称
1
张三
人力资源部
2
李四
财务部
3
王五
技术部
…
…
…
将部门信息拆分为独立的部门表 ( 如dept表 ) 和员工表 ( 如emp表 ) 是数据库设计中的常见做法 , 这样做的主要原因是为了规范化 ( Normalization ) .
规范化是数据库设计的一个核心原则 , 它旨在减少数据冗余和提高数据一致性 .
通过将部门信息单独存储在一个表中 , 并在员工表中通过外键引用它 , 可以确保部门信息只在一个地方存储和更新 .
这样 , 如果有任何变动 ( 例如部门名称的更改 ) , 只需要在一个地方进行更新 , 而不是在整个数据库的多个地方 .
emp表(从表)
dep表(主表)
id
name
dep_id
id
name
1
张三
1
1
人力资源部
2
李四
2
2
财务部
3
王五
3
3
技术部
下面我将详细解释如何定义外键约束 :
* 1. 定义主表dept , 它包含部门编号did和部门名称dname .
did列是主键 , 因此它必须是唯一的 , 并且不允许NULL值 .
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
* 2. 接下来 , 我们定义从表emp , 它包含员工编号id , 员工姓名name以及员工所在部门的编号dep_id .
这里 , id是主键,而dep_id是外键 , 它引用了dep表的主键id .
在定义外键约束时 , 需要使用FOREIGN KEY关键字 , 并明确指定外键列和所引用的父表及其列 .
mysql> CREATE TABLE emp8 (
id INT PRIMARY KEY ,
name VARCHAR ( 50 ) ,
dep_id INT ,
FOREIGN KEY ( dep_id) REFERENCES dep8( 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)
以下是Key列中可能出现的值及其详细解释 :
* 1. PRI ( 主键 ) : 如果Key是PRI , 表示该列是主键的组成部分 .
一个表只能有一个主键 , 主键的值必须是唯一的 , 并且不允许NULL值 .
主键用于唯一标识表中的每一行记录 .
* 2. UNI ( 唯一索引 ) : 如果Key是UNI , 表示该列是一个唯一索引的组成部分 , 且作为该唯一索引的第一列 ( 前导列 ) .
唯一索引 : 确保索引列的值是唯一的 , 但允许NULL值有多个 ( 除非索引被定义为UNIQUE NOT NULL ) .
唯一索引有助于快速检索数据 , 并确保数据的唯一性 .
* 3. MUL ( 非唯一索引 ) : 如果Key是MUL , 表示该列是一个非唯一索引的组成部分 .
非唯一索引 : 允许索引列的值重复 .
它可能是单列索引的一部分 , 也可能是多列索引 ( 复合索引 ) 的一部分 , 并且不一定是该索引的第一列 .
非唯一索引同样可以提高查询性能 , 但不如唯一索引那样严格保证数据的唯一性 .
* * 联合唯一显示MUL , 表示单独的列值可以重复 , 但是组合必须唯一 . * *
* 4. 空值 : 如果Key列是空值 , 表示该列没有索引 , 或者该列是非唯一复合索引的非前导列 .
没有索引的列在查询时可能需要进行全表扫描 , 这可能会影响查询性能 .
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)
mysql> INSERT INTO emp8 ( id, name, dep_id) VALUES ( 4 , '赵六' , 4 ) ;
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` ) )
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)
) ;
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' 的索引.
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' 不兼容.
6.2 添加外键约束
在MySQL中 , 可以使用ALTER TABLE语句为已存在的表添加外键约束 .
以下是一个基本的语法示例 :
ALTER TABLE child_table
ADD FOREIGN KEY ( child_column )
REFERENCES parent_table ( parent_column ) ;
注意事项 :
在添加外键约束之前 , 确保子表中的child_column列中的数据在父表的parent_column列中都有对应的值 , 否则添加外键约束会失败 .
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
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)
6.3 删除外键约束
要删除外键约束 , 可以使用 ALTER TABLE 语句配合 DROP FOREIGN KEY 子句 .
首先 , 需要知道外键约束的名称 .
这个名称通常在创建外键约束时指定 , 或者在自动创建时由数据库系统分配 .
注意事项 :
在删除外键约束时 , 如果外键约束还关联了索引 ( 某些数据库系统在外键约束创建时会自动创建索引 ) , 那么可能还需要单独删除这个索引 .
否则 , 即使删除了外键约束 , 索引可能仍然存在并占用存储空间 .
因此 , 在删除外键约束后 , 建议检查并删除任何不再需要的关联索引 .
可使用SHOW INDEX语句查看表的索引 : SHOW INDEX FROM table_name ; ,
其中table_name是你要查询的表名 .
执行这个命令后 , 会返回该表的索引信息 , 包括索引名称 , 索引类型 , 索引字段等 .
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)
mysql> ALTER TABLE emp11 DROP FOREIGN KEY emp11_ibfk_1;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 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> SHOW INDEX FROM emp11;
mysql> ALTER TABLE emp11 DROP INDEX dep_id;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 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 | | NULL | |
+
3 rows in set ( 0.00 sec)
6.4 外键约束等级
6.4.1 外键修改数据问题
当涉及到外键修改数据时 , 可能会遇到以下几种不同的情况 :
* 1. 修改外键列的值 : 如果想要修改一个包含外键的列的值 , 需要确保新的值在引用表中存在对应的记录 .
这是外键约束的基本要求 , 即外键列中的每个值都必须在引用表的被引用列中存在 .
如果试图将外键列的值更改为引用表中不存在的值 , 将会违反外键约束 , 导致操作失败 .
* 2. 修改主表的主键 : 如果其他表通过外键与当前表的主键关联 , 直接修改主键可能会违反外键约束 .
在修改主键之前 , 需要更新或删除所有依赖于这个主键的外键约束 .
* 3. 删除主表的记录 : 当尝试删除主表中的一个记录时 , 如果该记录被其他表的外键所引用 , 那么直接删除将会违反外键约束 .
这是因为外键约束要求引用列中的每个值都必须在被引用列中存在 , 删除被引用的记录将导致引用列中的某些值失去对应性 .
在这种情况下 , 需要先处理那些引用该记录的外键 , 例如通过更新外键列的值或删除引用该记录的记录 , 然后才能成功删除主表的记录 .
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)
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)
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)
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 ) : 无法'添加' 或'更新' 子行: 外键约束失败. . .
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 ) : 无法删除或更新父行: 外键约束失败. . .
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 ) : 无法删除或更新父行: 外键约束失败. . .
mysql> UPDATE dep12 SET id = 1002 WHERE id = 1003 ;
Query OK, 1 row affected ( 0.01 sec)
Rows matched : 1 Changed: 1 Warnings : 0
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等级 .
对于外键约束 , 最好是采用 : ON UPDATE CASCADE ON DELETE RESTRICT 的方式 .
可以使用 : SHOW CREATE TABLE Users ; 查看级联设置 .
6.4.3 演示1
级联更新 , 删除为NULL : ON UPDATE CASCADE ON DELETE SET NULL .
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)
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
) ;
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)
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 | 财务部 |
+
3 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp13;
+
| id | name | dep_id |
+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1004 |
+
3 rows in set ( 0.00 sec)
mysql> DELETE FROM dep13 WHERE ID = 1001 ;
Query OK, 1 row affected ( 0.01 sec)
mysql> SELECT * FROM dep13;
+
| id | name |
+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+
2 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp13;
+
| id | name | dep_id |
+
| 1 | 张三 | NULL |
| 2 | 李四 | NULL |
| 3 | 王五 | 1004 |
+
3 rows in set ( 0.00 sec)
6.4.4 演示2
更新为NULL , 级联删除 : on update set null on delete cascade
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)
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
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)
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 | 财务部 |
+
3 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp14;
+
| id | name | dep_id |
+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | NULL |
+
3 rows in set ( 0.00 sec)
mysql> DELETE FROM dep14 WHERE id = 1001 ;
Query OK, 1 row affected ( 0.00 sec)
mysql> SELECT * FROM dep14;
+
| id | name |
+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+
2 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp14;
+
| id | name | dep_id |
+
| 3 | 王五 | NULL |
+
1 row in set ( 0.00 sec)
6.4.5 演示3
级联更新 , 级联删除 : on update cascade on delete cascade
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)
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)
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 | 财务部 |
+
3 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp15;
+
| id | name | dep_id |
+
| 1 | 张三 | 1001 |
| 2 | 李四 | 1001 |
| 3 | 王五 | 1004 |
+
3 rows in set ( 0.00 sec)
mysql> DELETE FROM dep15 WHERE id= 1001 ;
Query OK, 1 row affected ( 0.01 sec)
mysql> SELECT * FROM dep15;
+
| id | name |
+
| 1003 | 咨询部 |
| 1004 | 财务部 |
+
2 rows in set ( 0.00 sec)
mysql> SELECT * FROM emp15;
+
| id | name | dep_id |
+
| 3 | 王五 | 1004 |
+
1 row in set ( 0.00 sec)
6.4.6 增加级联等级
如果已经外键约束 , 不能直接添加级联设置 .
需要先删除现有的外键约束 , 然后再重新创建它 , 包括所需的级联设置 .
这通常涉及两个步骤 :
* 1. 删除现有的外键约束
首先 , 需要知道现有外键约束的名称 .
可以通过查询数据库的系统表或信息模式来获取这个信息 .
* 2. 创建一个新的外键约束 , 并指定级联设置 .
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)
mysql> SELECT * FROM information_schema. table_constraints WHERE table_name = 'Users' ;
. . . CONSTRAINT_NAME
. . . users_ibfk_1
mysql> ALTER TABLE Users DROP FOREIGN KEY users_ibfk_1;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
mysql> SHOW INDEX FROM Users;
. . . Key_name
. . . det_id
mysql> ALTER TABLE Users DROP INDEX det_id;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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
mysql> SHOW CREATE TABLE Users;
. . . ON DELETE CASCADE ON UPDATE CASCADE
6.5 影响与考量
问题 1 : 如果两个表之间有关系 ( 一对一 , 一对多 ) , 比如员工表和部门表 ( 一对多 ) , 是否一定要建立外键约束?
答 : 不是一定要建立外键约束 .
建立外键约束是一种数据库设计决策 , 它用于强制实施引用完整性 , 但并非所有情况下都必须使用 .
问题 2 : 建立和不建立外键约束有什么区别?
答 : 建立外键约束后 , 数据库管理系统会强制执行引用完整性规则 .
这意味着 , 当你尝试执行违反引用完整性的操作时 ( 如在员工表中添加一个不存在的部门 ) , 数据库会报错并阻止操作 .
这有助于确保数据的准确性和一致性 .
不建立外键约束时 , 数据库不会对表之间的关系进行强制检查 .
你可以自由地添加 , 修改或删除数据 , 即使这样做可能导致引用不完整或数据不一致 .
在这种情况下 , 维护数据的完整性和一致性就完全依赖于应用程序的逻辑或程序员的自觉性 .
问题 3 : 建立和不建立外键约束与查询操作有关系吗?
答 : 建立和不建立外键约束与查询操作本身没有直接关系 .
无论是否建立外键约束 , 你都可以使用相同的SQL查询语句来检索数据 .
外键约束主要影响的是数据的插入 , 更新和删除操作 , 而不是查询操作 .
然而 , 需要注意的是 , 在某些情况下 , 没有外键约束的数据库设计可能导致数据冗余和不一致 , 这可能会影响查询结果的准确性和可靠性 .
因此 , 尽管外键约束与查询操作本身没有直接关系 , 但它们对于维护数据库的整体数据质量和一致性至关重要 .
在MySQL中 , 外键约束确实会消耗一定的系统资源 .
对于高并发的SQL操作 , 特别是大型网站的中央数据库 , 外键约束可能会成为性能瓶颈 .
因此 , MySQL允许你选择在应用层面完成数据一致性的检查逻辑 , 而不是依赖系统自带的外键约束 .
这意味着 , 即使不使用外键约束 , 你也应该通过应用程序的附加逻辑来确保数据的一致性 .
阿里开发规范
[ 强制 ] 不得使用外键与级联 , 一切外键概念必须在应用层解决 .
说明 : ( 概念解释 ) 学生表中的 student_id 是主键 , 那么成绩表中的 student_id 则为外键 .
如果更新学生表中的 student_id , 同时触发成绩表中的 student_id 更新 , 即为级联更新 .
外键与级联更新适用于单机低并发 , 不适合分布式 , 高并发集群 ;
级联更新是强阻塞 , 存在数据库更新风暴的风险 ; 外键影响数据库的插入速度 .
7. 检查约束
在MySQL 8.0 及更高版本中 , 引入了CHECK约束的功能 .
7.1 特性
CHECK约束是数据库中一种强大的特性 , 用于确保表中数据的一致性和完整性 .
以下是CHECK约束的主要特性 :
* 1. 数据验证 : CHECK约束允许开发人员定义在插入或更新数据时必须满足的条件 .
这些条件可以基于列中的数据值或格式 , 确保只有满足特定条件的数据才能被存储到表中 .
* 2. 灵活性和可定制性 : CHECK约束可以应用于一个或多个列 , 也可以将多个CHECK约束应用于一个列 .
这意味着开发人员可以根据具体需求为表的不同列设置不同的验证规则 .
* 3. 表达式支持 : CHECK约束可以包含SQL表达式 , 这使得数据验证更为灵活和强大 .
开发人员可以创建简单的约束表达式来检查单个条件下的数据 , 也可以使用布尔运算符创建复杂的约束表达式来在多种条件下检查数据 .
* 4. 自动执行 : 当尝试插入或更新数据时 , 数据库管理系统会自动执行CHECK约束 , 确保新数据满足所有定义的规则 .
如果数据不满足任何约束条件 , 操作将被拒绝 , 从而防止无效数据进入数据库 .
* 5. 提高数据质量 : 通过实施CHECK约束 , 数据库中的数据将始终保持预期的规则和限制 , 从而提高数据的质量和可靠性 .
综上所述 , CHECK约束是一种强大的数据库特性 , 它允许开发人员通过定义验证规则来确保数据的完整性和一致性 .
在数据库设计和管理中 , 合理使用CHECK约束可以有效提高数据质量并减少潜在的数据错误 .
7.2 定义检查约束
以下是CHECK约束的基本语法 :
CREATE TABLE table_name (
column datatype ,
. . .
CHECK ( conditional_expression )
) ;
在这个语法中 , 条件表达式 ( conditional_expression ) 是一个布尔表达式 , 它必须返回TRUE或FALSE .
当插入或更新数据 , MySQL会评估这个表达式 , 如果表达式返回FALSE , 则操作会失败 .
以下是定义检查约束时需要注意的一些事项 :
约束条件的正确性 : 在创建检查约束之前 , 首先要确保约束条件的正确性 .
约束条件应明确 , 准确 , 并符合业务规则 . 对于复杂的逻辑条件 , 应仔细测试以确保其正确性 .
性能考虑 : 虽然检查约束有助于提高数据质量 , 但它们也可能对性能产生一定的影响 .
在大量数据插入或更新时 , 检查约束会增加额外的处理时间 .
因此 , 在设计数据库和选择是否使用检查约束时 , 需要权衡性能和数据质量的需求 .
示例 , 假设有一个名为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会抛出一个错误 .
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)
mysql> INSERT INTO student4 VALUES ( 1 , 'kid' , 18 ) ;
Query OK, 1 row affected ( 0.01 sec)
mysql> INSERT INTO student4 VALUES ( 2 , 'qq' , 31 ) ;
ERROR 3819 ( HY000) : Check constraint 'student4_chk_1' is violated.
7.3 添加检查约束
在MySQL中 , 可以使用ALTER TABLE语句以存在的表要添加CHECK约束 .
语法格式如下 :
ALTER TABLE table_name
ADD CHECK ( conditional_expression ) ;
注意事项 :
在添加检查约束之前 , 检查表中是否已经有违反约束条件的数据 , 否则无法添加成功 .
假设已经有了一个students表 , 并且现在想要添加一个CHECK约束来确保score列的值在 0 到 100 之间 :
ALTER TABLE students
ADD CHECK ( score > = 0 AND score < = 100 ) ;
如果尝试更新或插入一个score不在 0 到 100 之间的学生记录 , MySQL同样会抛出一个错误 .
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)
mysql> INSERT INTO student5 VALUES ( 2 , 'qq' , 101 ) ;
ERROR 3819 ( HY000) : Check constraint 'student5_chk_1' is violated.
7.4 删除检查约束
在MySQL中 , 可以使用ALTER TABLE语句加DROP语句删除表中存在的CHECK约束 , 语法格式如下 :
ALTER TABLE table_name DROP CONSTRAINT constraint_name ; -- CONSTRAINT不能都省略
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)
8. 默认值约束
8.1 特性
默认值约束 ( Default Constraint ) : 是数据库中的一种约束类型 , 用于指定在插入新记录时 ,
如果某个字段没有提供值 , 则自动为该字段赋予一个默认值 .
这个默认值可以是常量 , 表达式或NULL .
默认值约束在数据库设计中具有以下几个特性 :
* 1. 自动填充 : 当在表中插入一条新记录时 , 如果未给具有默认值约束的字段赋值 , 数据库系统会自动为该字段插入默认值 .
这有助于确保数据的完整性 , 避免因为缺少值而导致的问题 .
* 2. 列级别约束 : 默认值约束可以针对表中的特定列进行设置 .
每一列只能有一个默认约束 , 这有助于确保每个字段在需要时都能得到正确的默认值 .
* 3. 多种默认值类型 : 默认值可以是常量值 , 函数或NULL等 .
这使得默认值约束非常灵活 , 可以根据实际需求来设置合适的默认值 .
* 4. 不参照其他列或表 : 默认值不能参照于其他列或其他表的值 .
这意味着默认值必须是静态的或基于当前列的某些函数计算得出的 , 而不能依赖于其他列或表的数据 . .
* 5. 不适用于某些数据类型 : 不能对数据类型为timestamp的列或具有标识列属性的列创建默认值 .
这是因为这些数据类型具有特殊的处理方式和约束 , 不支持使用默认值 .
通过合理设置默认值约束 , 可以简化数据插入操作 , 提高数据质量 , 并减少因缺少数据而导致的错误和问题 .
同时 , 默认值约束也有助于保持数据库的一致性和可维护性 .
8.2 字段填充规则
在数据库操作中 , 对于字段的填充 , 存在一种优先级顺序 .
这通常遵循以下规则 :
* 1. 插入值 : 当在插入语句中明确为字段指定了一个值时 , 这个值将具有最高的优先级 .
无论字段是否设置了默认值或是否允许NULL值 , 插入值都将直接用于填充该字段 .
* 2. 默认值 : 默认值是在创建表时为字段定义的一个自动填充值 , 用于在插入操作时字段没有被明确赋值时自动使用 .
默认值的优先级低于插入值 , 因为它只在字段没有被明确赋值时才会被考虑 .
然而 , 默认值并不是在所有情况下都会使用 .
* 3. NULL值 : NULL在数据库中表示字段没有值或值未知 .
如果一个字段在插入语句中没有被明确赋值 , 并且该字段允许NULL值且没有设置默认值 , 那么该字段的值将被设置为NULL .
NULL的优先级实际上取决于字段是否允许NULL值以及是否设置了默认值 .
如果字段允许NULL值但没有设置默认值 , NULL将是下一个被考虑的选项 .
因此 , 从优先级的角度来看 , 插入值具有最高优先级 , 因为它直接来自用户的输入或应用程序的指定 .
如果没有插入值 , 那么系统会检查字段是否设置了默认值 .
如果设置了默认值且字段不允许NULL值 , 默认值将被使用 .
如果字段允许NULL值且没有设置默认值 , 那么NULL将被用于填充该字段 .
在MySQL数据库中 , 插入值为NULL和不提供该字段在INSERT语句中的值 , 其结果并不总是相同的 .
这主要取决于该字段的定义和约束。
插入值为NULL : 当你明确地在INSERT语句中将某个字段的值设置为NULL时 ,
你正在告诉数据库该字段没有有效的值或你想表示该字段的缺失或未知状态 .
这种情况下 , 无论该字段是否允许NULL值或有默认值 , 该字段都将被赋予NULL值 .
不提供字段值 : 如果你在INSERT语句中没有为某个字段提供值 , 那么结果将取决于该字段的定义 .
如果该字段有默认值 , 并且你没有明确指定其他值 , 那么数据库将使用默认值填充该字段 .
如果该字段没有默认值 , 并且允许NULL值 , 那么该字段将被赋予NULL值 .
但是 , 如果该字段不允许NULL值且没有默认值 , 那么数据库通常会抛出一个错误 , 因为无法确定应该为该字段赋予什么值 .
因此 , 虽然在某些情况下 ) 例如字段允许NULL值且没有默认值 ) 插入NULL和不提供字段值的结果可能相同 ( 都是NULL ) ,
但在其他情况下 ( 例如字段有默认值 ) 它们可能会有所不同 .
为了确保数据的准确性和一致性 , 最好在编写INSERT语句时明确地为每个字段提供值或使用NULL ( 如果适用 ) .
此外 , 还需要注意NULL在数据库中的特殊含义 .
它不同于空字符串或零值 , 表示的是 '无值' 或 '未知值' .
因此 , 在处理数据时 , 应仔细考虑何时使用NULL以及何时使用其他值或默认值 .
8.3 定义默认值约束
以下是一个基本的默认值约束的语法格式 :
CREATE TABLE table_name (
column1 datatype DEFAULT default_value ,
. . .
) ;
注意事项 :
默认值列不能定义唯一属性 , 当默认值被多次使用时 , 会违反唯一约束 .
示例 , 假设有一个名为students的表 , 其中有一个名为age的列 , 我们想要为age列设置一个默认值 18 :
CREATE TABLE students (
id INT PRIMARY KEY ,
name VARCHAR ( 100 ) ,
age INT DEFAULT 18
) ;
在这个例子中 , age列被赋予了默认值 18.
这意味着 , 如果我们插入一个新的学生记录 , 但没有为age列提供值 , 那么age列的值将自动被设置为 18.
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 | |
+
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 |
+
1 row in set ( 0.00 sec)
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)
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)
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 ;
如果有一个已经存在的students表 , 并且想要改变age列的默认值为 25 , 可以修改表结构来添加默认值 :
ALTER TABLE students
MODIFY age INT DEFAULT 25 ;
在这个例子中 , 将age列的默认值更改为 25.
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)
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 | |
| phone | char ( 11 ) | NO | | NULL | |
+
4 rows in set ( 0.00 sec)
mysql> ALTER TABLE emp18 MODIFY age INT DEFAULT 18 ;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
8.5 删除默认值约束
在MySQL中 , 不能直接删除默认值约束 , 但可以更改列的定义以移除默认值 .
语法格式 :
ALTER TABLE table_name MODIFY column datatype DEFAULT NULL ;
这里 , datatype是列的数据类型 , 如果想要完全移除默认值 , 可以将其设置为NULL .
省略格式 :
ALTER TABLE table_name MODIFY column datatype ;
如果您省略了DEFAULT子句 , 那么该列将不再具有默认值 .
注意事项 :
如果列原先有NOT NULL约束 , 并且想要保留这个约束 , 同时在修改列定义时添加上 , 否则会一同被删除 .
mysql> ALTER TABLE emp18 MODIFY age INT ;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
9. 显示命名约束
在数据库中 , 当创建约束 ( 如唯一 , 主键 , 外键 , 检查约束 ) 时 , 可以选择显式地为这些约束命名 , 而不是让数据库系统自动生成名称 .
显式命名约束的做法有以下优点 :
* 1. 可读性 : 明确的约束名称能够清晰地表达约束的目的和它所应用的数据列 , 从而提高代码的可读性 .
* 2. 维护性 : 显式命名的约束更容易被识别和定位 , 这在需要修改或删除约束时尤为重要 .
* 3. 一致性 : 使用一致的命名规则可以确保整个数据库的命名风格统一 , 从而提高整体的可维护性 .
* 4. 避免冲突 : 在某些情况下 , 自动生成的约束名称可能会因为某种原因 ( 如约束的修改或重建 ) 而发生变化 .
显式命名可以避免这种情况的发生 .
通过显式命名这些约束 , 我们可以更轻松地识别 , 修改或删除它们 ,
特别是在大型数据库系统中 , 这样的命名规范能够极大地提高数据库的可维护性 .
以下是几个显式命名约束的示例 , 这些例子涵盖了不同类型和不同场景的约束使用 :
9.1 唯一约束
假设我们要创建一个名为employees的表 , 并希望email列的值是唯一的 .
我们可以这样定义唯一约束并显式命名它:
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)
) ;
在这个例子中 , 我们为email列的唯一约束显式命名为unique_email_constraint .
mysql> SHOW INDEX FROM employees1;
如果你有一个已经存在的表 , 并希望为某个列或列组合添加显式命名的唯一约束 , 你可以使用ALTER TABLE语句配合ADD CONSTRAINT子句 .
假设employees表已经存在 , 但现在我们想要为first_name和last_name列的组合添加显式命名的唯一约束 :
ALTER TABLE employees
ADD CONSTRAINT unique_name_constraint UNIQUE ( first_name, last_name) ;
在这个例子中 , 我们为first_name和last_name列的组合唯一约束显式命名为unique_name_constraint .
9.2 主键约束
假设我们有一个employees表 , 每个员工有一个唯一的employee_id .
我们可以显式命名主键约束如下 :
CREATE TABLE employees (
employee_id INT PRIMARY KEY CONSTRAINT pk_employees_id,
first_name VARCHAR ( 50 ) ,
last_name VARCHAR ( 50 ) ,
email VARCHAR ( 100 )
) ;
在这个例子中 , pk_employees_id是主键约束的显式名称 .
如果你有一个已经存在的表 , 并希望为某个列添加显式命名的主键约束 , 你可以使用ALTER TABLE语句配合ADD PRIMARY KEY子句 .
假设employees表已经存在 , 但现在我们想要为id列添加显式命名的主键约束 :
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY ( id) ;
在这个例子中 , 我们为id列的主键约束显式命名为pk_employees .
9.3 外键约束
假设我们还有一个departments表 , 并且employees表中的department_id列引用了departments表的主键 .
我们可以这样定义外键约束 :
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
) ;
在这个例子中 , fk_employees_department是外键约束的显式名称 .
如果你已经有两个存在的表 , 并希望为其中一个表添加显式命名的外键约束 , 你可以使用ALTER TABLE语句配合ADD FOREIGN KEY子句 .
假设departments和employees表已经存在 , 但现在我们想要为employees表的department_id列添加显式命名的外键约束 :
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments FOREIGN KEY ( department_id) REFERENCES departments( id) ;
在这个例子中 , 我们同样为employees表的department_id列的外键约束显式命名为fk_employees_departments .
9.4 检查约束
假设我们想要在employees表中添加一个检查约束 , 确保age列的值在 18 到 65 岁之间 ( 包含 18 和 65 ) .
我们可以这样定义这个检查约束并为其命名 :
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 )
) ;
在这个例子中 , 我们在创建employees表的同时 , 定义了主键约束pk_employees和检查约束chk_employees_age .
假设employees表已经存在 , 并希望为其中一个表添加显式命名的检查约束 , 你可以使用ALTER TABLE语句配合ADD CHECK子句 .
ALTER TABLE employees
ADD CONSTRAINT chk_employees_age CHECK ( age >= 18 AND age <= 65 ) ;
在这个例子中 , chk_employees_age是我们为检查约束自定义的名称 .
这个名称应该清晰地描述约束的目的和它所应用的列 .
在这个约束中 , 我们使用了逻辑表达式 ( age > = 18 AND age < = 65 ) 来指定age列的值必须在 18 到 65 之间 .
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是应用约束的列名列表 .
通常情况下 , 约束的名称并不会经常变动 , 因此在设计数据库和表结构时就应该仔细考虑并合理命名约束 .
示例 : 假设我们有一个名为employees的表 ,
它有一个名为uq_email的唯一约束 , 现在我们想要将这个约束的名称改为unique_email_constraint .
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
mysql> ALTER TABLE employees2 DROP CONSTRAINT uq_email;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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
mysql> SHOW INDEX FROM employees2;
. . . Key_name
. . . unique_email_constraint
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 ) 时 , 需要注意不同存储引擎之间的兼容性 .
外键约束用于保证数据的参照完整性 , 但如果表之间需要关联外键 , 却指定了不同的存储引擎 , 那么这些表之间是无法创建外键约束的 .
因此 , 在选择存储引擎时 , 我们需要考虑到表的使用场 , 、性能需求以及与其他表的关联关系 , 确保选择的存储引擎能够满足我们的需求 .
10.2 练习1
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)
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)
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)
mysql> ALTER TABLE emp2 ADD COLUMN dept_id INT ;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
10.3 练习2
创建数据库test04_Market , 在test04_Market中创建数据表customers .
customers表结构如下所示 , 按以下要求进行操作 .
字段名
数据类型
主键
外键
非空
自增
c_num
INT(11)
是
否
是
否
c_name
VARCHAR(50)
否
否
否
否
c_contact
VARCHAR(50)
否
否
否
否
c_city
VARCHAR(50)
否
否
否
否
c_birth
DATETIME
否
否
是
否
mysql> CREATE DATABASE test04_market;
Query OK, 1 row affected ( 0.00 sec)
mysql> USE test04_market;
Database changed
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)
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)
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)
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)
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)
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)
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)
在test04_Market中创建数据表orders .
orders表结构如下所示 , 按以下要求进行操作 .
字段名
数据类型
主键
外键
非空
唯一
自增
o_num
INT(11)
是
否
是
是
是
o_date
DATE
否
否
否
否
否
c_id
INT(11)
否
是
否
否
否
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)
mysql> SELECT * FROM information_schema. table_constraints WHERE table_name = 'orders' ;
. . . CONSTRAINT_NAME
. . . orders_ibfk_1
mysql> ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
mysql> SHOW INDEX FROM orders;
. . . Key_name
. . . c_id
mysql> ALTER TABLE orders DROP INDEX c_id;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
mysql> DROP TABLE customers_info;
Query OK, 0 rows affected ( 0.02 sec)
10.4 练习3
创建数据表pet , 并对表进行插入 , 更新与删除操作 .
pet表结构如下表所示 :
字段名
字段说明
数据类型
主键
外键
非空
自增
name
宠物名称
VARCHAR(20)
是
否
是
否
owner
宠物主人
VARCHAR(20)
否
否
否
否
species
种类
VARCHAR(20)
否
否
否
否
sex
性别
CHAR(1)
否
否
否
否
birth
出生日期
YEAR
否
否
否
否
death
死亡日期
YEAR
否
否
否
否
pet表中记录 :
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
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)
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)
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)
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)
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)
mysql> DELETE FROM pet;
Query OK, 4 rows affected ( 0.00 sec)
mysql> SELECT * FROM pet;
Empty set ( 0.00 sec)
10.5 练习4
mysql> CREATE DATABASE test_company;
Query OK, 1 row affected ( 0.01 sec)
mysql> USE test_company;
Database changed
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 ) .
其中雇员编号为主键 .
mysql> CREATE TABLE department(
depid INT PRIMARY KEY ,
depname VARCHAR ( 50 ) ,
deinfo VARCHAR ( 200 )
) ;
Query OK, 0 rows affected ( 0.01 sec)
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)
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)
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
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)
4. 添加数据如下 :
部门表 :
部门编号
部门名称
部门简介
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
员工表 :
雇员编号
姓名
性别
职称
出生日期
所在部门编号
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
工资表 :
雇员编号
基本工资
职务工资
扣除
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
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
-- 查询销售部门的雇员姓名及其基本工资:
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)
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)
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)
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)
mysql> SELECT COUNT ( * ) FROM employee;
+
| COUNT ( * ) |
+
| 4 |
+
1 row in set ( 0.01 sec)
mysql> SELECT COUNT ( * ) FROM department;
+
| COUNT ( * ) |
+
| 3 |
+
1 row in set ( 0.00 sec)
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)
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)
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)
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)
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)
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)
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)
mysql> SELECT COUNT ( * ) FROM employee
WHERE sex = '男' AND title LIKE '%工程师%' ;
+
| COUNT ( * ) |
+
| 3 |
+
1 row in set ( 0.00 sec)
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)
10.6 练习4
mysql> CREATE DATABASE test_school;
Query OK, 1 row affected ( 0.01 sec)
USE test_school;
2. 创建如下表格
表 1 Department表的定义 :
字段名
字段描述
数据类型
主键
外键
非空
唯一
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)
表 2 Teacher表的定义 :
字段名
字段描述
数据类型
主键
外键
非空
唯一
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)
3. 插入数据 :
表 1 Department表的数据 :
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
表 2 Teacher表的数据 :
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
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)
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)
SELECT Number, name
FROM teacher WHERE salary = (
SELECT MAX ( salary)
FROM teacher
) ;
+
| Number | name |
+
| 2001 | Tom |
+
1 row in set ( 0.00 sec)
mysql> SELECT number FROM teacher WHERE salary BETWEEN 2500 AND 4000 ;
+
| number |
+
| 2002 |
| 2004 |
+
2 rows in set ( 0.00 sec)
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)
10.7 练习5
mysql> CREATE DATABASE test_student; ;
Query OK, 1 row affected ( 0.01 sec)
mysql> USE test_student;
Database changed
2. 建立以下三张表 , 并插入记录 :
表 1 : 班级表 ( calsses )
专业
班级
姓名
姓别
座位
计算机网络
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
表 2 : 成绩表 ( Score )
姓名
英语
数学
语文
张三
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
表 3 : 记录 ( Records )
姓名
记录
小强
迟到
小强
事假
李四
旷课
李四
旷课
李四
迟到
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
mysql> UPDATE score SET chinese = 88 WHERE name = '张三' ;
Query OK, 1 row affected ( 0.01 sec)
Rows matched : 1 Changed: 1 Warnings : 0
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)
mysql> SELECT name FROM score
WHERE english < 60 OR maths < 60 OR chinese < 60 ;
+
| name |
+
| 李四 |
| 小强 |
+
2 rows in set ( 0.00 sec)
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)
10.8 练习6
mysql> CREATE DATABASE test_xuankedb;
Query OK, 1 row affected ( 0.01 sec)
USE test_xuankedb;
2. 建立如下三张表 :
1. 学生表Student由学号 ( Sno ) , 姓名 ( Sname ) , 性别 ( Ssex ) , 年龄 ( Sage ) , 所在系 ( Sdept ) 五个字段 , Sno为关键字 ( 主键 ) .
2. 课程表Course由课程号 ( Cno ) , 课程名 ( Cname ) , 选修课号 ( Cpno ) , 学分 ( Ccredit ) 四个字段 , Cno为关键字 ( 主键 ) .
3. 成绩表SG由学号 ( Sno ) , 课程号 ( Cno ) , 成绩 ( Grade ) 三个字段 , ( SNO , CNO ) 为关键字 ( 联合主键 , 并且分别关联其他两张表 ) .
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
mysql> ALTER TABLE student ADD Scome DATE ;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
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)
SELECT max ( grade) , AVG ( grade)
FROM SG WHERE Cno = 1 ;
+
| max ( grade) | AVG ( grade) |
+
| 92 | 86.3333 |
+
1 row in set ( 0.00 sec)
mysql> SELECT * FROM Student WHERE sdept IN (
SELECT Sdept FROM Student
WHERE sname = '李洋'
) ;
+
| sname |
+
| 李洋 |
+
1 row in set ( 0.00 sec)
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)
mysql> SELECT * FROM information_schema. table_constraints WHERE table_name = 'SG' ;
. . . CONSTRAINT_NAME
. . . sg_ibfk_1
mysql> ALTER TABLE SG DROP FOREIGN KEY sg_ibfk_1;
Query OK, 0 rows affected ( 0.02 sec)
Records: 0 Duplicates: 0 Warnings : 0
mysql> SHOW INDEX FROM SG;
. . . Key_name
. . . PRIMARY
. . PRIMARY
. . . cno
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
mysql> SHOW CREATE TABLE SG;
. . . ON DELETE CASCADE ON UPDATE CASCADE
mysql> DELETE FROM student WHERE sno = '5019' ;
Query OK, 1 row affected ( 0.00 sec)
DELETE FROM SG
WHERE sno IN (
SELECT sno FROM student
WHERE sdept = '计算机系'
) ;
Query OK, 4 rows affected ( 0.01 sec)
10.9 练习7
mysql> CREATE DATABASE test_library;
Query OK, 1 row affected ( 0.01 sec)
USE test_library;
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 )
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)
mysql> ALTER TABLE sort ADD describes VARCHAR ( 50 ) ;
Query OK, 0 rows affected ( 0.01 sec)
Records: 0 Duplicates: 0 Warnings : 0
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
mysql> SELECT * FROM book WHERE pressid = 100 ;
+
| bid | BNAME | bsortno | pressid |
+
| 1 | 红与黑 | 11 | 100 |
+
1 row in set ( 0.00 sec)
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)
mysql> SELECT * FROM sort WHERE scount > 100 ;
+
| sortno | scount | describes |
+
| 12 | 300 | 科幻 |
+
1 row in set ( 0.00 sec)
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)
10.10 练习8
mysql> CREATE DATABASE test_tour;
mysql> USE test_tour;
Database changed
2. 建立如下两个表
表 1 : 旅行社表 ( agency )
列名(英文名)
列名(中文名)
数据类型
允许空值
说明
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
表 2 : 旅行线路表 ( travel )
列名(英文名)
列名(中文名)
数据类型
允许空值
说明
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
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)
SELECT position FROM travel
WHERE ` count` = (
SELECT MAX ( ` count` )
FROM travel
) ;
+
| position |
+
| 水长城 |
+
1 row in set ( 0.00 sec)
mysql> SELECT Position FROM travel WHERE money < 5000 ;
+
| Position |
+
| 八达岭 |
+
1 row in set ( 0.01 sec)
mysql> SELECT name FROM agency
WHERE id = (
SELECT aid FROM travel
ORDER BY money DESC
LIMIT 1
) ;
+
| name |
+
| 天天旅行社 |
+
1 row in set ( 0.00 sec)
mysql> SELECT SUM ( time )
FROM travel
WHERE aid = (
SELECT id FROM agency
WHERE name = '青年旅行社'
) ;
+
| SUM ( time ) |
+
| 12 |
+