第四章 数据的完整性和索引
一、实体的完整性
1.简介
数据表是数据存储的基础单元,在从外界向数据表输入数据时可能会由各种原因造成输入数据的无效或者错误。为了防止数据库中存在不符合语义规定的数据或者由错误信息的输入/输出造成无效操作, MySQL 提供多种方法来保证数据的完整性。数据的完整性主要分为实体完整性、域完整性和引用完整性。

2.实体完整性
实体完整性是数据模型中三项完整性规则之一,主要包括主键约束、唯一约束和自动增长列。

3.主键与主键的约束
主键(又称为主关键字)是用于唯一确定表中每一行数据的标识符,是表中某一列或者多列的组合,多个列组成的主键称为复合主键。
主键约束是对主键的约束规则,如下所示。
(1)唯一性:每个表中只能存在一个主键,且主键的值能唯一标识表中的每一行,就像每个人的身份证号码是不同的,能唯一标识每一个人。
(2)非空性:主键可以由多个字段组成,且不受数据类型的限制。另外,字段所在的列中不能存在空值(NULL)。
需要注意的是,主键表示的是一个实体,而主键约束是针对这个实体所设定的规则或属性。为了使读者可以更加形象地理解主键的定义规则,这里将使用一张学生信息表来进行详细介绍,如图所示。
图片 10.png
图中,id字段所对应的值可以明确标识每一行的数据,例如,id=3对应的是学生王五的基本信息。id字段中的值不重复、不为空,因此id字段可以设置为主键。在MySQL中使用PRIMARY KEY字段来定义数据表中的主键。在创建表时可以为字段添加主键约束,具体的语法格式如下所示。
create table 表名(字段名 数据类型 PRIMARY KEY);
以上语法格式中, “字段名”表示需要设置为主键的列名, “数据类型”为该列的数据类型,PRIMARY KEY表示主键。另外,在表创建完成后,也可以为表添加主键约束,具体的语法格式如下所示。
alter table 表名 add PRIMARY KEY(列名);
接下来演示设置主键可能会出现的问题。首先创建一张订单表orders,表结构如表所示。
图片 11.png
在表中列出了订单表的字段、字段类型和说明,为了与先前的数据进行隔离,此处将新建一个名为Chapter_Five的数据库,具体代码如下所示。
MariaDB [yun]> create database Chapter_Five;
Query OK, 1 row affected (0.002 sec)
MariaDB [yun]> use Chapter_Five;
Database changed
MariaDB [Chapter_Five]>
然后根据表中的信息创建orders表,具体的SQL语句如下所示。
MariaDB [Chapter_Five]> create table orders(oid int,total double,name varchar(20),phone varchar(20),addr varchar(50));
Query OK, 0 rows affected (0.016 sec)
MariaDB [Chapter_Five]>
表创建完成后,向其中插入两条oid字段相同的数据,SQL语句如下所示。
MariaDB [Chapter_Five]> insert into orders(oid,total,name,phone,addr) values(1,100,'zhangsan',1366,'beijing'),(1,98,'lisi',1588,'shanghai');
Query OK, 2 rows affected (0.008 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
此时,将orders表中的oid字段设置为主键,代码如下所示。
MariaDB [Chapter_Five]> alter table orders add primary key(oid);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [Chapter_Five]>
从以上执行结果可以看出,添加主键失败。这是因为数据表中存在两条相同的数据,这与主键约束规则冲突。删除其中一条数据并尝试重新添加主键,SQL语句和执行过程如下所示。
MariaDB [Chapter_Five]> delete from orders where name='lisi';
Query OK, 1 row affected (0.006 sec)
MariaDB [Chapter_Five]> alter table orders add primary key(oid);
Query OK, 0 rows affected (0.026 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出主键设置成功。 为了进一步验证主键约束的规则,在表orders中插入一个空值(NULL),具体的SQL语句和执行过程如下所示。
MariaDB [Chapter_Five]> insert into orders (oid,total,name,phone,addr) values (null,100,'wangwu',1682,'henan');
ERROR 1048 (23000): Column 'oid' cannot be null
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,向orders表插入空值失败,这也证明了数据库中主键不为空的规则。
在实际的开发中,也可以删除表中的主键,具体的语法如下所示。
alter table 表名 drop primary key;
关于删除主键,此处不再进行演示,读者可以自行操作。

4.唯一约束
唯一约束用于限制不受主键约束的列上数据的唯一性,与主键约束不同的是,唯一约束可以为空值且一个表中可以放置多个唯一约束。MySQL中可以使用UNIQUE关键字添加唯一约束。在创建表时为某个字段添加唯一约束的具体语法格式如下所示。
create table 表名(字段名 数据库类型 unique .....);
以上语法格式中,“字段名”表示需要添加唯一约束的列名,“数据类型”和UNIQUE关键字之间需要使用空格隔开。另外,也可以使用ALTER命令将唯一约束添加到已经创建完成的表中,具体的语法格式如下所示。
alter table 表名 add unique(列名);
以orders表为例,将phone(收货人电话)字段设置为唯一约束的SQL语句及执行过程如下所示。
MariaDB [Chapter_Five]> alter table orders add unique(phone);
Query OK, 0 rows affected (0.016 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]> desc orders;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid | int(11) | NO | PRI | NULL | |
| total | double | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| phone | varchar(20) | YES | UNI | NULL | |
| addr | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.010 sec)
MariaDB [Chapter_Five]> select * from orders;
+-----+-------+----------+-------+---------+
| oid | total | name | phone | addr |
+-----+-------+----------+-------+---------+
| 1 | 100 | zhangsan | 1366 | beijing |
+-----+-------+----------+-------+---------+
1 row in set (0.001 sec)
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出,orders表中phone字段添加唯一约束成功。为了进一步验证唯一约束的规则,分别向表中插入两条不同的数据,具体代码和执行过程如下所示。
MariaDB [Chapter_Five]> insert into orders(oid,total,name,phone,addr) values(2,100,'lisi',1366,'beijing');
ERROR 1062 (23000): Duplicate entry '1366' for key 'phone'
MariaDB [Chapter_Five]> insert into orders(oid,total,name,phone,addr) values(2,100,'lisi',null,'beijing');
Query OK, 1 row affected (0.006 sec)
MariaDB [Chapter_Five]> select * from orders;
+-----+-------+----------+-------+---------+
| oid | total | name | phone | addr |
+-----+-------+----------+-------+---------+
| 1 | 100 | zhangsan | 1366 | beijing |
| 2 | 100 | lisi | NULL | beijing |
+-----+-------+----------+-------+---------+
2 rows in set (0.001 sec)
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出:第一条插入命令中phone字段的值与表中phone字段的值相同,导致数据插入失败;第二条插入命令将phone字段的值设置为NULL后插入成功。
在实际的开发中,也可以删除表中的唯一约束,具体的语法如下所示。
drop index index_name on tbl_name;
读者也可以使用下面的方式。
alter table tbl_name drop index index_name;

5.自动增长列
在创建表时,表中的id字段的值一般从1开始,当需要插入大量的数据时,这种做法不仅比较烦琐,而且还容易出错。为此,可以将id字段的值设置为自动增长。MySQL中可以使用AUTO_INCREMENT关键字设置表中字段值的自动增长,在创建表时将某个字段设置为自动增长列的语法格式如下所示。
create table 表名(字段名 数据类型 AUTO_INCREMENT,。。。。);
以上语法格式中,“字段名”表示需要设置字段值自动增长的列名,“数据类型”和AUTO_INCREMENT关键字之间需要使用空格隔开。另外,也可以将已经创建完成的表的字段设置成自动增长列,具体语法格式如下所示。
alter table 表名 modify 字段名 数据类型 AUTO_INCREMENT;
以orders表为例,将oid字段设置为自动增长列的SQL语句如下所示。
MariaDB [Chapter_Five]> alter table orders modify oid int auto_increment;
Query OK, 2 rows affected (0.042 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [Chapter_Five]> desc orders;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| oid | int(11) | NO | PRI | NULL | auto_increment |
| total | double | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| phone | varchar(20) | YES | UNI | NULL | |
| addr | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,orders表中的oid字段已经成功设置成自动增长列。

6.域完整性
域完整性指的是对数据表中单元格的约束,通过给列定义规则来约束单元格的属性。常见的域完整性约束包括数据类型约束、非空约束和默认值约束,其中数据类型约束就是在创建表时所指定的字段类型。

7.非空约束
非空约束用于保证数据表中的某个字段的值不为NULL,在MySQL中可以使用NOT NULL关键字为列添加非空约束。在创建表时,为某个字段添加非空约束的具体语法格式如下所示。
create table 表名(字段名 数据类型 null,。。。。);
以上语法格式中,“字段名”表示需要添加非空约束的列名,“数据类型”和NOT NULL关键字之间需要用空格隔开。另外,非空约束也可以添加到已经创建完成的表中,语法格式如下所示。
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
MariaDB [Chapter_Five]> alter table orders modify addr varchar(50) not null;
Query OK, 0 rows affected (0.029 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]> desc orders;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| oid | int(11) | NO | PRI | NULL | auto_increment |
| total | double | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| phone | varchar(20) | YES | UNI | NULL | |
| addr | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.003 sec)
MariaDB [Chapter_Five]>
从上方代码的执行过程可以看出,已成功为表orders中的addr字段添加非空约束。

8.默认值约束
默认值约束用于为数据表中的某个字段设置默认值,例如,订单的创建时间如果不进行手动填写,则默认为当前时间。在MySQL中使用DEFAULT关键字设置默认值约束,创建表时,为某个字段添加默认值约束的具体语法格式如下所示。
create table 表名(字段名 数据类型 default 默认值,。。。。);
以上语法格式中,“字段名”表示需要添加默认值约束的列名,“数据类型”和DEFAULT关键字之间需要使用空格隔开。另外,默认值约束也可以添加到已经创建完成的表中,语法格式如下所示。
ALTER TABLE 表名 MODIFY 字段名 数据类型 default 默认值;
下面具体讲解MySQL中为字段设置默认值约束的操作方法。在表orders中增加一列名为postalcode的字段,数据类型为INT,并将其默认值设置为100000,具体的SQL语句和操作流程如下所示。
MariaDB [Chapter_Five]> alter table orders add postalcode int default 100000;
Query OK, 0 rows affected (0.008 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]> desc orders;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| oid | int(11) | NO | PRI | NULL | auto_increment |
| total | double | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| phone | varchar(20) | YES | UNI | NULL | |
| addr | varchar(50) | NO | | NULL | |
| postalcode | int(11) | YES | | 100000 | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.002 sec)
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,在orders表中成功添加了postalcode字段并将其默认值设置为100000。

9.引用完整性
引用完整性是对实体之间关系的描述,如果要删除被引用的对象,那么也要删除引用它的所有对象或者把引用值设置为空值,如图所示。
图片 12.png
图中,B和C分别引用了A中的a字段(A被B和C引用),所以a1和a2受a的变化影响。如果要删除a,那么需要将a1和a2先删除,或者将a的值设置为 NULL。

10.外键与外键约束
外键的定义是具有相对性的。如果一个字段x在一张表中为主键,在另一张表中不为主键,那么字段x称为第二张表的外键。外键的主要作用是建立和加强两个表之间的数据连接,如图所示。
图片 13.png
在表A中的字段a被设置为主键的前提下,表B中引用了表A中的字段a的数据,所以表B中的字段a被称为表A的外键。
外键约束是针对外键设定的约束规则,接下来通过两张表来讲解外键约束规则。
首先创建学科表subject,其中有专业编号sub_id和专业名称sub_name 这2个字段,并将sub_id字段设置为该表的主键。具体的SQL语句和执行结果如下所示.
MariaDB [Chapter_Five]> create table subject(sud_id int primary key,sub_name varchar(20));
Query OK, 0 rows affected (0.010 sec)
MariaDB [Chapter_Five]>
然后创建学生表student,其中有学生编号stu_id、学生姓名stu_name和专业编号sub_id这3个字段。具体的SQL语句和执行结果如下所示。
MariaDB [Chapter_Five]> create table student(stu_id int primary key,stu_name varchar(20),sud_id int not null);
Query OK, 0 rows affected (0.017 sec)
MariaDB [Chapter_Five]>
需要注意的是,创建的subject表中sub_id为主键,student表中的sub_id字段是对subject表中的主键进行的引用,所以student表中的sub_id字段就是外键。sub_id字段被作为主键的表(subject)称为主表,而外键所在的表(student)称为从表(或子表),两表之间是主从关系。student表可以通过外键sub_id来获得subject表中的信息,从而建立两个表中数据的连接。在创建表时将某字段设置为外键的基本语法格式如下所示。
图片 14.png
另外,表创建完成后也可以添加外键,具体语法格式如下所示。
alter table 表名 add foreign key(外键字段名)references 主表表名(主键字段名);
使用相关命令将上面创建的student表中的sub_id字段设置为外键,具体的SQL语句如下所示。
MariaDB [Chapter_Five]> alter table student add foreign key (sud_id) references subject (sud_id);
Query OK, 0 rows affected (0.034 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出外键添加成功。下面根据不同的情况讲解外键所对应的外键约束规则。
(1)主表不存在数据时,从表无法插入数据。
尝试在从表student中插入数据,具体代码如下所示。
MariaDB [Chapter_Five]> insert into student(stu_id,stu_name,sud_id) values(1,'zhangsan',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Chapter_Five.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (sud_id) REFERENCES subject (sud_id))
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出添加数据失败,这是因为如果主表中没有数据,那么从表中将无法插入数据。为主表subject添加数据,具体代码如下所示。
MariaDB [Chapter_Five]> insert into subject(sud_id,sub_name) values(1,'math');
Query OK, 1 row affected (0.005 sec)
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出数据插入完成。接下来向从表student插入数据,具体代码如下所示。
MariaDB [Chapter_Five]> insert into student(stu_id,stu_name,sud_id) values(1,'zhangsan',1);
Query OK, 1 row affected (0.004 sec)
MariaDB [Chapter_Five]>
从执行结果可以看出,向从表插入数据成功。
(2)从表的数据删除后才可以删除主表中的数据。
尝试直接删除主表subject中的数据,具体代码如下所示。
MariaDB [Chapter_Five]> delete from subject;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Chapter_Five.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (sud_id) REFERENCES subject (sud_id))
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,删除主表中的数据失败。由于主表subject中的数据被从表student引用,因此无法删除主表subeject中的数据。此时可以先删除从表中的数据,再删除主表中的数据,具体的SQL语句如下所示。
MariaDB [Chapter_Five]> delete from student;
Query OK, 1 row affected (0.003 sec)
MariaDB [Chapter_Five]> delete from subject;
Query OK, 1 row affected (0.004 sec)
MariaDB [Chapter_Five]>
从上面代码的执行过程可以看出,数据删除成功。

11.删除外键约束
读者在实际的开发应用中可能会遇到需要解除两个表之间关联的情况,这时就需要删除外键约束,其具体的语法格式如下所示。
alter table 表名 drop foreige key 外键名;
下面演示将 student 表中的外键约束删除。首先查看表的外键名。
MariaDB [Chapter_Five]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE student (
stu_id int(11) NOT NULL,
stu_name varchar(20) DEFAULT NULL,
sud_id int(11) NOT NULL,
PRIMARY KEY (stu_id),
KEY sud_id (sud_id),
CONSTRAINT student_ibfk_1 FOREIGN KEY (sud_id) REFERENCES subject (sud_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.002 sec)
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出,student表中的sub_id字段为外键,关联的主表是subject,外键名为student_ibfk_1。然后删除student表中的外键约束,具体操作如下所示。
MariaDB [Chapter_Five]> alter table student drop foreign key student_ibfk_1;
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出外键删除成功。 为了进一步验证, 使用相关命令再次查看student表的结构信息。
MariaDB [Chapter_Five]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE student (
stu_id int(11) NOT NULL,
stu_name varchar(20) DEFAULT NULL,
sud_id int(11) NOT NULL,
PRIMARY KEY (stu_id),
KEY sud_id (sud_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
MariaDB [Chapter_Five]>
从以上代码的执行结果可以看出, student 表中的外键删除成功。

12.索引
如果把数据表比喻成一本书,那么索引就是这本书的目录,它可以帮助用户实现根据目录中的页码快速找到对应的内容。如果不添加索引,数据库每次执行查询操作时,都会从表的头部开始遍历数据,这无疑降低了数据库的运行效率。MySQL 中的索引分为普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引、组合索引等。接下来将介绍生产环境中常用的普通索引和唯一索引。

13.普通索引
普通索引是最基本的索引类型,它主要的作用是加快对数据的访问速度。在创建表时设置普通索引的语法格式如下所示。
create table 表名(字段名 数据类型,。。。。 index [索引名](字段名[(长度)]));
另外,对于已经创建完成的表,也可以为表中的某个字段创建普通索引,基本的语法格式如下所示。
create index 索引名 on 表名 (字段名[(长度)]);
以前面创建的student表为例,为stu_id字段添加索引的SQL语句如下所示。
MariaDB [Chapter_Five]> create index id on student(stu_id);
Query OK, 0 rows affected (0.022 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
以上语句的执行结果证明为student表中的stu_id字段创建普通索引成功。为了进一步验证,使用SHOW INDEX FROM命令查看表中的索引信息。
MariaDB [Chapter_Five]> show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: stu_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: sud_id
Seq_in_index: 1
Column_name: sud_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: stu_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.002 sec)
MariaDB [Chapter_Five]>
从以上执行结果可以看出,为stu_id字段成功创建了名为id的普通索引。结果列表中各字段的含义如表所示。
图片 15.png
需要注意, 在表中如果将某字段设置为主键或外键, 那么MySQL会自动为该字段设置一个索引。从上方代码的执行结果也可以看出,在表中分别创建了名为stu_id和sub_id的索引。在实际的应用场景中,也可以将普通索引删除,具体的SQL语句格式如下所示。
drop index 索引名 on 表名;
尝试将student表中名为stu_id的索引删除,具体的执行语句如下所示。
MariaDB [Chapter_Five]> drop index id on student;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
使用SHOW INDEX FROM命令查看student表中的索引。
MariaDB [Chapter_Five]> show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: stu_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: sud_id
Seq_in_index: 1
Column_name: sud_id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.002 sec)
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,student表中名为stu_id的索引被删除。另外,如果需要删除索引名为PRIMARY的主键索引,可以使用以下语句格式。
drop index 'primary' on 表名;
删除主键索引的操作此处不再赘述。

14.唯一索引
MySQL中使用UNIQUE关键字为某列定义唯一索引,被定义唯一索引的列不允许存在重复的值。当有新的数据插入时,MySQL会自动检查新记录中的字段值是否已经存在于数据表中,如果已经存在,系统将拒绝这条数据的插入。唯一索引不仅可以保证数据的唯一性,而且可以简化MySQL对索引的管理工作,使索引变得简单高效。在创建表时可以创建唯一索引,语法格式如下所示。
create table 表名(字段名 数据类型,。。。。unique index [索引名](字段名[(长度)]));
上方语法格式中,UNIQUE INDEX关键字表示唯一索引,“索引名”为可选项,括号中的“字段名”表示创建索引的字段,“长度”为可选项。另外,对于已经创建完成的表,也可以为某个字段创建唯一索引,语法格式如下所示。
create unique index 索引名 on 表名 (字段名[(长度)]);
以前面创建的subject表为例,为sub_name字段添加唯一索引的SQL语句如下所示。
MariaDB [Chapter_Five]> create unique index sub_name on subject(sub_name);
Query OK, 0 rows affected (0.016 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]>
以上语句的执行结果证明为subject表中的sub_name字段创建唯一索引成功。为了进一步验证,使用SHOW CREATE TABLE命令查看表的具体信息。
MariaDB [Chapter_Five]> show index from subject\G
*************************** 1. row ***************************
Table: subject
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: sud_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: subject
Non_unique: 0
Key_name: sub_name
Seq_in_index: 1
Column_name: sub_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.001 sec)
MariaDB [Chapter_Five]>
从以上结果可以看出,为sub_name字段成功创建了唯一索引。在实际的开发中,也可以使用ALTER语句删除表中的唯一索引,具体的语法格式如下所示。
alter table 表名 drop index 索引名;
下面演示使用 ALTER 语句删除唯一索引。
MariaDB [Chapter_Five]> alter table subject drop index sub_name;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Chapter_Five]> show index from subject\G
*************************** 1. row ***************************
Table: subject
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: sud_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.001 sec)
MariaDB [Chapter_Five]>
从上方代码的执行结果可以看出,名为sub_name的唯一索引被成功删除。

15.索引过多引发的问题
在MySQL中合理地创建索引不仅可以极大地提高在数据库中获取所需信息的速度,而且能提高服务器处理相关搜索请求的效率。建立索引的优点如下。
(1)可以加快数据的检索速度。
(2)可以保证数据库表中每一行数据的唯一性。
(3)加速表和表之间的连接。
(4)在使用分组或者排序子句时,可以减少查询中分组和排序的时间。
虽然索引在检索效率上具有诸多的积极作用,但过多的索引也会引起一些不必要的问题。
(1)在数据库建立过程中,需花费较多的时间去建立并维护索引,特别是随着数据总量的增加,所花费的时间将不断递增。
(2)过多的索引需要占大量的物理空间。
(3)当对数据进行增、删、改操作时,用户也需要对索引进行相应的维护,降低了数据的维护速度。

数据库表的完整性约束
1.创建数据库为runtime,并使用该数据库
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database runtime;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use runtime;
Database changed
MariaDB [runtime]>
2.在该库中创建一个表为runtime1并写入姓名、性别、年龄、日期、编号,并设置约束和单列主键(设置成主键的字段不允许重复,且不允许为null,所以说它是(unique+not null)。设置为unique的字段就不允许重复啦,但可以为空),同时查看该表
MariaDB [runtime]> create table runtime1(name varchar(20),sex enum('male','female')not null default 'male',age int not null default 18,employ_date date not null,anth_id varchar(18),unique(name),primary key(anth_id));
Query OK, 0 rows affected (0.019 sec)

MariaDB [runtime]> desc runtime1;
+-------------+-----------------------+------+-----+---------+-------+
| Field       | Type                  | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| name        | varchar(20)           | YES  | UNI | NULL    |       |
| sex         | enum('male','female') | NO   |     | male    |       |
| age         | int(11)               | NO   |     | 18      |       |
| employ_date | date                  | NO   |     | NULL    |       |
| anth_id     | varchar(18)           | NO   | PRI | NULL    |       |
+-------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.001 sec)
MariaDB [runtime]>
3.在该表中插入数据为jack,男,20,20101221,123,Alice,女,19,20111212,1234,并查看该数据是否插入成功
MariaDB [runtime]> insert into runtime1 values('jack','male',20,20101221,'123'),('alice','female',19,20111212,'1234');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [runtime]> select * from runtime1;
+-------+--------+-----+-------------+---------+
| name  | sex    | age | employ_date | anth_id |
+-------+--------+-----+-------------+---------+
| jack  | male   |  20 | 2010-12-21  | 123     |
| alice | female |  19 | 2011-12-12  | 1234    |
+-------+--------+-----+-------------+---------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
4.现假如插入一个alice同名的怎么办,(很明显报错,因为已经有alice啦),现在插入一个id为null的数据也是不可以的,(因为主键不能为空哦)
MariaDB [runtime]> insert into runtime1 values('alice','female',29,19901012,'1234');
ERROR 1062 (23000): Duplicate entry '1234' for key 'PRIMARY'
MariaDB [runtime]> insert into runtime1 values('aee','female',29,19901012,null);
ERROR 1048 (23000): Column 'anth_id' cannot be null
MariaDB [runtime]>
5.现在重新在runtime库中重新创建一张表为service并设置为复合型主键,要求有ip、service、port的参数,创建完成后并查看该表
MariaDB [runtime]> create table service(ip varchar(15),service varchar(10) not null,port int not null,allow enum('yes','no') default 'no',primary key (ip,port));
Query OK, 0 rows affected (0.003 sec)

MariaDB [runtime]> desc service;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| ip      | varchar(15)      | NO   | PRI | NULL    |       |
| service | varchar(10)      | NO   |     | NULL    |       |
| port    | int(11)          | NO   | PRI | NULL    |       |
| allow   | enum('yes','no') | YES  |     | no      |       |
+---------+------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [runtime]>
6.设置foreign外键,在上面几步基础上做,然后创建salary表,runtime1作为父表,salary子表中name为外键,关联父表( runtime1主键name ),同步更新,同步删除。
MariaDB [runtime]> create table salary(dep_id int not null auto_increment,name varchar(20),salary float(8,2) not null,primary key(dep_id),foreign key(name) references runtime1(name) on update cascade on delete cascade);
Query OK, 0 rows affected (0.005 sec)

MariaDB [runtime]> desc salary;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| dep_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  | MUL | NULL    |                |
| salary | float(8,2)  | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
MariaDB [runtime]>
7.在salary表中插入employee中两个数据,我们只选择name和salary字段,因为dep_id默认整数增长
MariaDB [runtime]> insert into salary(name,salary) values('alice',40000);
Query OK, 1 row affected (0.001 sec)

MariaDB [runtime]> insert into salary(name,salary) values('jack',20000);
Query OK, 1 row affected (0.001 sec)

MariaDB [runtime]> select * from salary;
+--------+-------+----------+
| dep_id | name  | salary   |
+--------+-------+----------+
|      1 | alice | 40000.00 |
|      2 | jack  | 20000.00 |
+--------+-------+----------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
8.下面我们开始测试父表和子表之间的连接问题啦,更新一下名字试试
MariaDB [runtime]> update runtime1 set name='jackson' where name='jack';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [runtime]> select * from salary;
+--------+---------+----------+
| dep_id | name    | salary   |
+--------+---------+----------+
|      1 | alice   | 40000.00 |
|      2 | jackson | 20000.00 |
+--------+---------+----------+
2 rows in set (0.000 sec)
MariaDB [runtime]>
9.删除用户Alice试试
MariaDB [runtime]> delete from  runtime1 where name='alice';
Query OK, 1 row affected (0.001 sec)

MariaDB [runtime]> select * from salary;
+--------+---------+----------+
| dep_id | name    | salary   |
+--------+---------+----------+
|      2 | jackson | 20000.00 |
+--------+---------+----------+
1 row in set (0.000 sec)
MariaDB [runtime]>
上一篇 下一篇