2020-10-19 16:04 已编辑门头沟学院 Java

关注

Mysql索引、约束、级联操作（级联更新、删除）

一、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE
index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name
(column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX ©;

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER
TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G
…

索引的优缺点

优点： 提高查询（SELECT、GROUP BY、ORDER BY）速度；
唯一索引可保证数据唯一性；

缺点：

创建的索引需要占用物理存储空间；
建立并维护索引需要花费时间，且随着表中数据量的增加，花费的时间也会增加；

二、约束

*1、约束简介

约束（constraint）：
表中数据的限制条件。完整性有效性

约束的种类：
主键约束
外键约束
唯一约束
检查约束
非空约束

2、主键约束

主键约束：
唯一标识表中每一行的一列或多个列，可以定义一列或多列为主键；
主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）；
主健可作外健，唯一索引不可；
不能或者很难更新；

定义表的主键：
方法一：建表时定义
CREATE TABLE student (
student_id varchar(50) NOT
NULL COMMENT ‘学生编号’,
student_name varchar(100) NOT NULL DEFAULT ‘’
COMMENT ‘学生姓名’,
gender varchar(10) NOT NULL DEFAULT ‘’ COMMENT ‘性别’,
birth_day date NOT NULL COMMENT ‘生日’, age int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘年龄’,
class_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘班级编号’,
score decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘数学成绩’,
teacher_id
varchar(20) DEFAULT NULL COMMENT ‘老师编号’,
PRIMARY KEY(student_id)
)COMMENT=‘学生’;

方法二：建表后创建 REATE TABLE student ( student_id varchar(50) NOT NULL
COMMENT ‘学生编号’,
student_name varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘学生姓名’,
gender varchar(10) NOT NULL DEFAULT ‘’ COMMENT ‘性别’,
birth_day date NOT NULL COMMENT ‘生日’,
age int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘年龄’,
class_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘班级编号’,
score decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘数学成绩’,
teacher_id varchar(20) DEFAULT NULL COMMENT ‘老师编号’ )COMMENT='学生’;
ALTER TABLE student ADD PRIMARY KEY(student_id); 复制代码

3、外键约束

外键约束：
约定某一列的值必须出现在另一个表的主键中；
表的外键是另一表的主键, 外键可以有重复的, 可以是NULL；
用来和其他表建立联系用的；
一个表可以有多个外键；

方法一：建表时创建
CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',
FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
)COMMENT='学生';

方法二：建表后创建
CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生’;
ALTER TABLE student ADD FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id);

4、唯一约束

唯一约束：
    约定某一列或某几列的值在表中不重复。

唯一约束和主键约束的区别：
    唯一性约束允许在该列上存在NULL值，而主键约束的限制更为严格，不但不允许有重复，而且也不允许有空值。

 约束和索引：
    前者是用来检查数据的正确性，后者用来实现数据查询的优化，目的不同；

#创建唯一约束
方法一：UNIQUE关键字，这种直接定义在列名后，适合单字段
CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名' UNIQUE,
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

方法二：定义在最后，可以指定多个字段唯一
CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',
UNIQUE (calss_id,student_name)
)COMMENT='学生';

5、检查约束

检查约束：
    约定某一列的值必须满足指定的条件。

与其他约束不同，MySQL虽然支持CHECK约束，但并没有强制限制。也就是说，违反约束的数据也会被接受.

CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄' CHECK ( age >= 10 AND age <= 20 ),
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

6、非空约束

非空约束：
    约定某一列的值不可为NULL。
CREATE TABLE student (
student_id varchar(50) NOT NULL COMMENT '学生编号',
student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
birth_day date NOT NULL COMMENT '生日',
age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

三、级联操作

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

因为只有InnoDB引擎才允许使用外键，所以，我们的数据表必须使用InnoDB引擎。我所使用的版本是Mysql5.1版本的，过程如下：

创建数据库：

Create database test;

创建两个表，其中第一个表的”id”是第二个表（userinfo）的外键：

CREATE TABLE user (

id int(4) NOT NULL,

sex enum(‘f’,‘m’) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE userinfo (

sn int(4) NOT NULL AUTO_INCREMENT,

userid int(4) NOT NULL,

info varchar(20) DEFAULT NULL,

PRIMARY KEY (sn),

KEY userid (userid),

CONSTRAINT userinfo_ibfk_1 FOREIGN KEY (userid) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

注意：
1、存储引擎必须使用InnoDB引擎；
2、外键必须建立索引；
3、外键绑定关系这里使用了“ ON DELETE CASCADE ” “ON UPDATE CASCADE”，意思是如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新。更多信息请参考MySQL手册中关于InnoDB的文档；

好，接着我们再来插入数据测试：

INSERT INTO user (id,sex)
VALUES (‘1’, ‘f’), (‘2’, ‘m’), (‘3’, ‘f’);

INSERT INTO userinfo (sn,userid,info)
VALUES (‘1’, ‘1’, ‘2005054dsf’),
(‘2’, ‘1’, ‘fdsfewfdsfds’),
(‘3’, ‘1’, ‘gdsgergergrtre’),
(‘4’, ‘2’, ‘et34t5435435werwe’),
(‘5’, ‘2’, ‘435rtgtrhfghfg’),
(‘6’, ‘2’, ‘ret345tr4345’),
(‘7’, ‘3’, ‘fgbdfvbcbfdgr’),
(‘8’, ‘3’, ‘45r2343234were’),
(‘9’, ‘3’, ‘wfyhtyjtyjyjy’);

我们先看一下当前数据表的状态：

mysql> show tables;

±---------------+

| Tables_in_test |

±---------------+

| user |

| userinfo |

±---------------+

2 rows in set (0.00 sec)

User表中的数据：
mysql> select * from user;

±—±-----+

| id | sex |

±—±-----+

| 1 | f |

| 2 | m |

| 3 | f |

±—±-----+

3 rows in set (0.00 sec)

Userinfo表中的数据：

mysql> select * from userinfo;

±—±-------±------------------+

| sn | userid | info |

±—±-------±------------------+

| 1 | 1 | 2005054dsf |

| 2 | 1 | fdsfewfdsfds |

| 3 | 1 | gdsgergergrtre |

| 4 | 2 | et34t5435435werwe |

| 5 | 2 | 435rtgtrhfghfg |

| 6 | 2 | ret345tr4345 |

| 7 | 3 | fgbdfvbcbfdgr |

| 8 | 3 | 45r2343234were |

| 9 | 3 | wfyhtyjtyjyjy |

±—±-------±------------------+

9 rows in set (0.00 sec)

对于建立以上不表，相信对大家也没什么难度了。好的，下面我们就要试验我们的级联删除功能了。
我们将删除user表中id为2的数据记录，看看userinf表中userid为2的相关子纪录是否会自动删除：

执行删除操作成功！

mysql> delete from user where id=‘2’;

Query OK, 1 row affected (0.03 sec)

看看user表中已经没有id为2的数据记录了！

mysql> select * from user;

±—±-----+

| id | sex |

±—±-----+

| 1 | f |

| 3 | f |

±—±-----+

2 rows in set (0.00 sec)

再看看userinfo表中已经没有userid为2的3条数据记录了，对应数据确实自动删除了！

mysql> select * from userinfo;

±—±-------±---------------+

| sn | userid | info |

±—±-------±---------------+

| 1 | 1 | 2005054dsf |

| 2 | 1 | fdsfewfdsfds |

| 3 | 1 | gdsgergergrtre |

| 7 | 3 | fgbdfvbcbfdgr |

| 8 | 3 | 45r2343234were |

| 9 | 3 | wfyhtyjtyjyjy |

±—±-------±---------------+

6 rows in set (0.00 sec)

更新的操作也类似，因为我们在前面建表的时候已经定义外键删除、更新操作都是CASCADE，所以在这里可以直接测试数据。

将user表中原来id为1的数据记录更改为id为4，执行如下：

mysql> update user set id=4 where id=‘1’;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

现在去看看两个表中是数据是否发生了变化：

mysql> select * from user;

±—±-----+

| id | sex |

±—±-----+

| 3 | f |

| 4 | f |

±—±-----+

2 rows in set (0.00 sec)

mysql> select * from userinfo;

±—±-------±---------------+

| sn | userid | info |

±—±-------±---------------+

| 1 | 4 | 2005054dsf |

| 2 | 4 | fdsfewfdsfds |

| 3 | 4 | gdsgergergrtre |

| 7 | 3 | fgbdfvbcbfdgr |

| 8 | 3 | 45r2343234were |

| 9 | 3 | wfyhtyjtyjyjy |

±—±-------±---------------+

6 rows in set (0.00 sec)

比较原来的表可以发现它们的确已经更新成功了，测试完成！！！这也就实现了用外键对多个相关联的表做同时删除、更新的操作，从而保证了数据的一致性。

全部评论

推荐最新楼层

不愿透露姓名的神秘牛友

02-12 18:14

秋招分手的前女友找我过情人节

RT，这周五就是情人节了，前女友给我发了消息，我该不该回？

Yoswell：原则上来说让她滚，但是本着工作很累下班想吃瓜的心态，我觉得你可以回一下

点赞评论收藏

不愿透露姓名的神秘牛友

02-14 15:56

我恨这个虚伪的世界

昨晚下班的时候，同组超级帅气的男同事问我明天情人节有空吗？我连忙答应：有呀！然后他就和我换班了。。。。。。我很这个虚伪的世界

爱信息工程学院风：哈哈哈

点赞评论收藏

01-18 04:33

北京交通大学 Java

26简历求拷打

今年想找大厂的暑期实习备战明年校招，简历有什么需要优化的吗，荣誉奖项实在是没有，比赛确实没参加过

whopxx：哥我刚进抖音就刷到有人在直播点评你的简历

点赞评论收藏

02-09 15:27

东华大学自动化

真麻了，0offer🙄

有hr看一眼吗？自动化专业的。

点赞评论收藏

02-12 18:55

已编辑

山东大学用户运营

学会用DeepSeek修改简历，offer拿到手软！

大家好呀，我是求职offer姐，今天是元宵节，祝大家元宵节快乐，团团圆圆，生活幸福美满！最近一段时间DeepSeek真的太火爆了，随处可见大家的使用及讨论。这几天我也将DeepSeek运用到了求职方面，今天来和大家分享一下我是如何利用DeepSeek进行简历修改及优化，希望可以给大家一点帮助。一．利用DeepSeek告诉你制作简历的注意事项大家可以按照我下面的思路，简要概述自己的情况。具体结构是学历/专业+过往实习经历+求职意向+你的要求。例如：我是数学专业的211本科生，曾在一家小的互联网公司做过产品运营实习，目标求职方向是产品经理，准备制作一份适用于产品经理的高水平简历，在制作简历...

点赞评论收藏

全站热榜

创作者周榜

正在热议

# 一人推荐一个值得去的通信/硬件公司 #

# 产品实习，你更倾向大公司or小公司 #

# 大学四年该怎么过，才不算浪费时间？ #