MySQL（第五篇）—数据的完整性约束（2）（索引、引用完整性）数据的完整性约束（2）三、索引四、引用完整性

数据的完整性约束（2）

数据的完整性约束（1）（实体完整性、域完整性）请点击跳转

三、索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的储存结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些页的逻辑指针清单。

是不是很晦涩，简单点，就像是每本书中的目录。

在数据库中查询某条数据，指定查询以后，数据库是从第一条数据开始遍历的，如果需要的数据在第1000条，就会一直遍历，直到找到这一条数据。这样，效率就会大打折扣，索引就是为了解决这个问题。

1.普通索引

（1）在已经创建的表中，为其某个字段创建索引

语法格式

示例

首先我先创建表stu

mysql> create table stu(
    -> id int,
    -> name varchar(50),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)
           

在表中给id字段创建索引

mysql> create index stu_id on stu(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

查看表的具体信息，可以看到id字段普通索引创建成功

mysql>  show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `stu_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
           

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

（2）创建表时创建索引

语法格式

示例

在创建表时为name字段创建索引，索引名为“stu_name”

mysql> create table student(
    -> id int,
    -> name varchar(50),
    -> age int,
    -> index stu_name (name)
    -> );
Query OK, 0 rows affected (0.03 sec)
           

查看表的具体信息，可以看到name字段普通索引创建成功

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `stu_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  | MUL | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

2.唯一索引

普通索引允许被索引的数据列包含重复的值，唯一索引可以保证数据的唯一性

（1）在已经创建的表中，为其某个字段创建索引

语法格式，用法与普通索引一致

（2）创建表时创建索引

语法格式，用法与普通索引一致

3.查看索引

语法格式

4.删除索引

（1）第一种方式

alter table 表名 drop index 索引名；
           

示例，删除stu表id字段的索引

mysql> alter table stu drop index stu_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

查看表的具体信息，stu表id字段的索引，成功删除

mysql>  show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

（2）第二种方式

示例，删除student表中，name 字段的索引

mysql> drop index stu_name on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

查看表的具体信息，可以看到name字段普通索引删除成功

mysql>  show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

四、引用完整性

引用完整性是对实体之间关系的描述，是定义外关键字与主关键字之间的引用规则，也就是外键约束。

如果要删除被引用的对象，也要删除引用它的所有对象，或把引用值设置为空。

外键指引用另一个表中的一列或多列，被引用的列应该具有主键约束或者唯一约束。

外键用于加强两个表，数据之间的连接。

1.添加外键约束

语法格式

示例

将具有唯一性约束字段age的stu表作为主表，将student表作为从表，为student表age字段添加外键约束

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

添加外键约束

mysql> alter table student add foreign key(age) references stu(age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

查看student表具体信息，如下，外键约束添加成功，‘student_ibfk_1’为外键约束名

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`age`) REFERENCES `stu` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
           

创建表时添加外键约束

首先创建一个含有主键的表student2

mysql>  create table student2(
    -> stu_id int primary key,
    -> stu_name varchar(50)
    -> );
           

创建表student3时添加外键约束

mysql>  create table student3(
    -> id int,
    -> name varchar(50),
    -> stu_id int,
    -> foreign key(stu_id) references student2(stu_id)
    -> );
Query OK, 0 rows affected (0.03 sec)
           

查看表的具体信息，如下，外键约束创建成功

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`age`) REFERENCES `stu` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
           

2.删除外键约束

即解除两个表之间的关联关系。

示例，删除student表外键约束

mysql> alter table student drop foreign key student_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

如上，外键约束删除成功

mysql>  show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
           

但是主键还没有彻底删除！

采用desc语句看一下表结构，可以看到age字段的key值仍然有值

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
           

这是因为MySQL在创建外键后会自动建一个同名的索引。

因此，采用上面语句只能删除外键约束，无法彻底删除外键。

所以，我们需要将同名索引也删除，才可以将外键彻底删除

首先，我们需要使用show index from语句查看索引，如下。

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          1 | age      |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
           

我们再使用删除索引的语句将索引删除

mysql> alter table student drop index age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

再次使用desc语句查看表结构，key列里的MUl消失了

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

           

这样主键就彻底删除了！

数据的完整性约束（1）（实体完整性、域完整性）请点击跳转