SQL 高级的SQL特性(约束、索引、触发器)

最新推荐文章于 2022-05-19 16:40:30 发布

Fe_cow丿

最新推荐文章于 2022-05-19 16:40:30 发布

阅读量412

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/Fe_cow/article/details/87905229

版权

SQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

高级的SQL特性

一、约束：

管理如何插入或处理数据库数据的规则。
DBMS 通过再数据库表上施加约束来实施引用完整性。

1.1：主键：

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而用不改动。
- 也就是说，表中的一列（或多个列）的值唯一标识表中的每一行。
表中任意列只要满足以下条件，都可以用于主键：

1.任意两行的主键值都不相同。

2.每行都具有一个主键值（即列中不允许NULL值）。

3.包含主键值的列从不修改或更新。

4.主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

定义主键的方法就是创建它：

CREATE TABLE PRIMARY_TABLE (
	id CHAR(10) NOT NULL PRIMARY KEY,  # PRIMARY KEY 指定该列为主键
	name CHAR(32) NOT NULL,
	age CHAR(16)  NOT NULL
);

mysql> DESC PRIMARY_TABLE;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | char(10) | NO   | PRI | NULL    |       |  #可以看到表的结构 Key PRI 代表该列是主键
| name  | char(32) | NO   |     | NULL    |       |
| age   | char(16) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

1.2：外键：

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
外键：foreign key，外面的键，即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键，那么将该字段称之为外键。每张表中，可以有多个外键。
外键的使用：
- 外键的作用，主要有两个：
  
  1.让数据库自己通过外键来保证数据的完整性和一致性。
  
  2.就是能够增加ER图的可读性。

新增外键：

外键既可以在创建表的时候增加，也可以在创建表之后增加（但是要考虑数据的问题）。

第一种：再创建表的时候，添加外键

基本语法：foreign key(外键字段) + references + 外部表名(主键字段);

# 首先创建一张CLASS表
CREATE TABLE CLASS(
	id varchar(20),  # 主键
	name varchar(32),
	primary key(id)
);
# 再创建一张带外键的表MY_FOREIGN
CREATE TABLE MY_FOREIGN(
	id int primary key auto_increment,
	name varchar(20) not null,
	c_id varchar(20) ,   # 外键的字段
	foreign key(c_id)  REFERENCES CLASS(id)  # 设置外键
);

创建表中到外键的时候，首先确定该设置的外键表是否存在(这里指的就是CLASS表先创建),并且指定外键的字段(MY_FOREIGN表中的c_id)，必须跟另一张表(CLASS)中主键ID，字段类型一致。

# 查看CLASS表中的结构
mysql> desc CLASS;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 查看MY_FOREIGN表中的结构
mysql> desc MY_FOREIGN;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| c_id  | varchar(20) | YES  | MUL | NULL    |                | #  外键
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

第二种：再创建表之后，增加外键

基本语法：alter table + 表名 + add[constraint + 外键名字] + foreign key(外键字段) + references + 外部表名(主键字段);

# 创建CLASS表过程略过，参考上面栗子

# 创建准备增加外键的表 MY_FOREIGN_1
CREATE TABLE MY_FOREIGN_1(
	id int primary key auto_increment,
	name varchar(20) not null,
	c_id varchar(20)  # 这个是准备变成外键的字段
);
# 给MY_FOREIGN_1表中c_id字段变成外键
ALTER TABLE MY_FOREIGN_1 add 
	-- 指定外键名 foreign_name
	constraint foreign_name
	-- 指定外键字段 c_id
	foreign key(c_id)
	-- 引用外部表主键(CLASS表中的ID主键)
	references CLASS(id)
;

查看MY_FOREIGN_1表中的结构：

mysql> desc MY_FOREIGN_1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| c_id  | varchar(20) | YES  | MUL | NULL    |                | # 外键字段
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

修改外键（删除外键)：

外键不能修改，只能先删除后增加。
基本语法：alter table + 表名 + drop foreign key + 外键名字;

查看该表的所有信息(这里需要找出外键的名字)：

SHOW CREATE TABLE MY_FOREIGN[表名]

mysql> show create table MY_FOREIGN;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                            |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MY_FOREIGN | CREATE TABLE `my_foreign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `c_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `my_foreign_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表中的MY_FOREIGN中外键的名字是my_foreign_ibfk_1

删除外键：

ALTER TABLE MY_FOREIGN DROP FOREIGN KEY my_foreign_ibfk_1;

mysql>  desc MY_FOREIGN; # 通过表结构可以看到，怎么还是有外键呢？为什么？
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| c_id  | varchar(20) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show create table MY_FOREIGN; # 通过所有表信息来看，就没有了外键

+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                           |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MY_FOREIGN | CREATE TABLE `my_foreign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `c_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

观察上图可知，删除外键不能通过查看表结构体现，而是应该通过表的语句查看。

若要删除CLASS表、MY_FOREIGN_1(里面有外键)这两个表，必须先删除MY_FOREIGN_1表，再删除CLASS表。
- 如果先删除CLASS表，会出现以下错误。
```
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
```

1.3：唯一约束：

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

1.表可包含多个唯一约束，但每个表只允许一个主键。

2.唯一约束列可包含NULL值。

3.唯一约束列可修改或更新。

4.唯一约束列的值可重复使用。

5.与主键不一样，唯一约束不能用来定义外键。
唯一约束的语法类似于其他约束的语法。
- 唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRAINT定义。

二、索引：

索引用来排序数据以加快搜索和排序操作的速度。
- 比如：想像一本书后的索引（如本书后的索引），可以帮助你理解数据库的索引。
  - 假如要找出本书中所有的“数据类型”这个词，简单的办法是从第1页开始，浏览每一行。虽然这样做可以完成任务，但显然不是一种好的办法。浏览少数几页文字可能还行，但以这种方式浏览整部书就不可行了。随着要搜索的页数不断增加，找出所需词汇的时间也会增加。
- 这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位置。为了搜索“数据类型”一词，可在索引中找出该词，确定它出现在哪些页中。然后再翻到这些页，找出“数据类型”一词。
数据库索引的作用。主键数据总是排序的，这是DBMS的工作。因此，按主键检索特定行总是一种快速有效的操作。
再开始创建索引前，应该记住以下几点：

1.索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

2.索引数据可能要占用大量的存储空间。

3.并非所有数据都适合做索引。取值不多的数据不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。

4.索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。

5.可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
没有严格的规则要求什么应该索引，何时索引。大多数DBMS提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。
索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）。
```
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
```
- 索引必须唯一命名。
- 这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。
- ON用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。
注意：检查索引
- 索引的效率随表数据的增加或改变而变化。
- 许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

三、触发器：

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。
触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。
与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联。
触发器内的代码具有以下数据的访问权：

1.INSERT操作中的所有新数据；

2.UPDATE操作中的所有新数据和旧数据；

3.DELETE操作中删除的数据；
根据所使用的DBMS的不同，触发器可在特定操作执行之前或之后执行。
下面是触发器的一些常见用途：

1.保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。

2.基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。

3.进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。

4.计算计算列的值或更新时间戳。
提示：约束比触发器更快
- 一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。

Fe_cow丿

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL 高级的SQL特性(约束、索引、触发器)

高级的SQL特性一、约束：管理如何插入或处理数据库数据的规则。DBMS 通过再数据库表上施加约束来实施引用完整性。1.1：主键：主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而用不改动。也就是说，表中的一列（或多个列）的值唯一标识表中的每一行。表中任意列只要满足以下条件，都可以用于主键：1.任意两行的主键值都不相同。2.每行都具有一个主键值（即列中...
复制链接

扫一扫