高级特性简介

最新推荐文章于 2024-06-25 23:17:49 发布

你是我世界的光

最新推荐文章于 2024-06-25 23:17:49 发布

阅读量1k

点赞数

分类专栏： ● 数据库 ------------【SQL】文章标签： mysql 索引

本文链接：https://blog.csdn.net/qq_16811963/article/details/51956153

版权

● 数据库同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

------------【SQL】

10 篇文章 0 订阅

订阅专栏

1.约束

1.主键约束

任意两行的主键值都不相同。
每行都具有一个主键值（不允许NULL值）。
主键值不能重用，即从表中删除一行，该行的主键值不分配给新行。
包含主键值的列不修改或更新。

下面我们创建一个学生表，并定义学号为主键：

mysql> CREATE TABLE student
    -> (
    -> id INT(20) UNSIGNED NOT NULL PRIMARY KEY,
    -> name VARCHAR(20) NOT NULL,
    -> sub VARCHAR(10)
    -> )
    -> ;
Query OK, 0 rows affected (0.25 sec)

mysql> desc student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(20) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
| sub   | varchar(10)      | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

上面id代表学号，用PRIMARY KEY 将其指定为主键。从而该列就具有了主键约束。

我们再新建一个表course，指定课程名称为其主键，我们2步完成。首先建表，然后修改表。过程如下：
建表：

mysql> CREATE TABLE course(name VARCHAR(15) ,count INT UNSIGNED NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.32 sec)

mysql> DESC course;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(15)      | YES  |     | NULL    |       |
| count | int(10) unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改约束：

mysql> ALTER TABLE course ADD CONSTRAINT PRIMARY KEY(name);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC course;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(15)      | NO   | PRI |         |       |
| count | int(10) unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

其中CONSTRAINT为约束关键字。

2.外键约束

外键：外键是用来实现数据关系完整性的KEY，当表Super的主键是表sub的一个字段时，这个字段就成为Subr的一个外键，其中，Super成为父表，即有外键的表称为子表。即一个表的外键是另一个表的主键。

规则：
①插入非空值时，如果主键表中没有这个值，那么不能插入。
②更新外键时，不能更新为主键表中不存在的值。
③删除或更改主键时，可以选择在建立外键时级联删除/更改还是拒绝删除/更改。
创建的语法：CREATE TABLE 表名（列名数据类型，id TINYINT ，····，FOREIGN KEY REFERENCE 表名（表的主键））；注意：我们要在构建好我们各字段之后，再在最后指定外键。

现在我们将student表的sub字段修改为外键。

mysql> ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(sub) REFERENCES course(nam
e) ON DELETE CASCADE;
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(20) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
| sub   | varchar(10)      | YES  | MUL | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

外键的作用：

帮助防止意外的删除。
级联删除。

例如，要删除一个学生的信息，如果不使用级联删除，我们需要先删除与该学生相关的所有成绩记录，然后删除该学生的信息（学生表为父表，成绩表为子表，其中含外键。）。如果我们开启级联删除，只要在父表中删除该学生的信息记录，那么与之相关的成绩表中的记录也会一并删除。

实例：

首先看一下student表：

mysql> select * from student;
+----+------+------+
| id | name | sub  |
+----+------+------+
| 20 | aaa  | ch   |
| 21 | aaa  | math |
| 22 | aaa  | eng  |
| 23 | aaa  | ch   |
| 24 | aaa  | ch   |
+----+------+------+
5 rows in set (0.00 sec)

course表：

mysql> select * from course;
+------+-------+
| name | count |
+------+-------+
| ch   |     2 |
| eng  |     8 |
| math |     3 |
+------+-------+
3 rows in set (0.00 sec)

启用级联删除：

mysql> ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(sub) REFERENCES course(nam
e) ON UPDATE CASCADE;
Query OK, 5 rows affected (0.74 sec)
Records: 5  Duplicates: 0  Warnings: 0

删除一个课程然后查看student表：

mysql> DELETE FROM course where nam='ch';
ERROR 1054 (42S22): Unknown column 'nam' in 'where clause'
mysql> DELETE FROM course where name='ch';
Query OK, 1 row affected (0.05 sec)

mysql> select * from student;
+----+------+------+
| id | name | sub  |
+----+------+------+
| 21 | aaa  | math |
| 22 | aaa  | eng  |
+----+------+------+
2 rows in set (0.00 sec)

我们可以看到，ch记录被删除了。
具体还有一些外键约束的参照操作，这里不做过多介绍。

3.唯一约束

唯一约束确保该字段的字段值在表中唯一，只需在表创建时在字段定义中加入UNQUE关键字既可。

例如创建一个雇员表：

mysql> CREATE TABLE employee(id INT UNIQUE NOT NULL,name VARCHAR(20));
Query OK, 0 rows affected (0.34 sec)

mysql> DESC employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

从上面我们也可以看出，唯一约束加非空约束其实就是主键约束。

4.检查约束

检查约束的作用是，保证某些字段的值满足某个条件，比如，人的性别只能为男或女。但是貌似在MYSQL中不存在检查约束。

5.非空约束

这个很好理解，一个NOT NULL即可。

2.索引

其实索引我感觉是个‘大头’，在阿里一面的时候我感觉我就是死在这个问题上，这里我先来写几个面试常会被问到的问题：

索引是用来排序数据以加快搜索和排序操作的速度。

索引改善检索操作的性能，但降低数据插入、修改和删除的性能。因为在执行这些操作时,MYSQL必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合索引。唯一性不好的数据从索引得到的好处不一定多。
可以在索引中定义多个列。这样的索引仅仅在多个列排序时有用，而按单个列排序，则这种索引没有用处。
索引必须唯一命名。

更详细可以阅读：

索引小讨论