13.约束constraint

13.1 概述

13.1.1 背景

数据完整性（Data Integrity)是指数据的精确性（Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

1. 实体完整性(Entity Integrity)：例如，同一个表中，不能存在两条完全相同无法区分的记录。
2. 域完整性(Domain Integrity)：例如:年龄范围0-120，性别范围"男/女”。
3. 引用完整性(Referential Integrity)：例如:员工所在部门，在部门表中要能找到这个部门。
4. 用户自定义完整性(User-defined Integrity)：例如:用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

13.1.2 介绍

约束是表级的强制规定。

可以在创建表时规定约束(通过CREATE TABLE 语句)，或者在表创建之后通过ALTER TABLE语句规定约束。

13.1.3 分类

1. 角度1：约束的字段的个数
   • 单列约束
   • 多列约束
2. 角度2：约束的作用范围
   • 列级约束：将此约束声明在对应字段的后面
   • 表级约束：在表中所有字段声明完后，在所有字段的后面声明的约束
3. 角度3：约束的功能
   • not null：非空约束
   • unique：唯一性约束
   • primary key：主键约束
   • foreign key：外键约束
   • check：检查约束
   • default：默认值约束

13.1.4 添加约束

1. 在CREATE TABLE时添加约束。
2. ALTER TABLE时增加或删除约束。

13.1.5 查看约束

SELECT * FROM 数据库名.tale_constraints WHERE table_name='表名称';

13.2 not null非空约束

限定某个字段/某列的值不允许为空。

特点：

• 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型。
• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空。
• —个表可以有很多列都分别限定了非空。

1. 在CREATE TABLE时添加约束：CREATE TABLE test1( id INT NOT NULL, last_name VARCHAR(15) NOT NULL, email VARCHAR(25), salary DECIMAL(10,2) );
2. ALTER TABLE时增加约束：ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL;
3. ALTER TABLE时删除约束：ALTER TABLE test1 MODIFY email VARCHAR(25) NULL;

13.3 UNIQUE唯一性约束

用来限制某个字段/某列的值不能重复。

唯一约束允许出现多个空值。

特点：

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

1. 在CREATE TABLE时添加约束：CREATE TABLE test2( id INT UNIQUE, # 列级约束 last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2),#表级约束 CONSTRAINT uk_test2_email UNIQUE (email) );
2. 在CREATE TABLE时添加复合的唯一性约束：CREATE TABLE test2( id INT, last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), #表级约束 CONSTRAINT uk_test2_name_pwd UNIQUE (name,password) );
3. ALTER TABLE时增加约束：ALTER TABLE test1 MODIFY last_name VARCHAR(15) UNIQUE;
4. ALTER TABLE时删除约束：ALTER TABLE test1 DROP INDEX email;
   • 添加唯一性约束的列上也会自动创唯一索引。
   • 删除唯一约束只能通过删除唯一索引的方式删除。
   • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
   • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同;如果是组合列，那么默认和()中排在第一个的列明相同。也可以自定义唯一性约束名。

13.4 PRIMARY KEY约束

用来唯一标识表中的一行记录。

特点：

• 主键约束相当于唯一性约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
• 主键约束对应着表中的一列或者多列(复合主键)。
• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高)。如果删除主键约束了，主键约束对应的索引就自动册除了。
• 不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

1. 在CREATE TABLE时添加约束：CREATE TABLE test2( id INT PRIMARY KEY, # 列级约束 last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), #表级约束 CONSTRAINT uk_test2_id PRIMARY KEY (id) );
2. 在CREATE TABLE时添加复合的主键约束：CREATE TABLE test2( id INT, last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), PRIMCARY KEY(name,password) );
3. ALTER TABLE时增加约束：ALTER TABLE test1 ADD PRIMARY KEY (id);
4. ALTER TABLE时删除约束：ALTER TABLE test1 DROP PRIMARY KEY;

13.5 AUTO_INCREMENT自增列

某个字段的值自增。

特点和要求：

• 一个表最多只能有一个自增长列。
• 当需要产生唯一标识符或顺序值时，可设置自增长。
• 自增长列约束的列必须是键列（主键列，唯一键列）。
• 自增约束的列的数据类型必须是整数类型。
• 如果自增列指定了0和null，会在当前最大值的基础上自增;如果自增列手动指定了具体值，直接赋值为具体值。

1. 在CREATE TABLE时添加约束：CREATE TABLE test2( id INT PRIMARY KEY AUTO_INCREAMENT, last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2) );

2. 在CREATE TABLE时添加复合的主键约束：CREATE TABLE test2( id INT, last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), PRIMCARY KEY(name,password) );

3. ALTER TABLE时增加约束：ALTER TABLE test1 MODIFY id INT AUTO_INCREMENT;

4. ALTER TABLE时删除约束：ALTER TABLE test1 MODIFY id INT;

13.6 FOREIGN KEY外键约束

限定某个表的某个字段的引用完整性。

13.6.1 主表和从表/父表和子表

1. 主表（父表）：被引用的表，被参考的表。
2. 从表（子表）：引用别人的表，参考别人的表。

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

13.6.2 特点

1. 从表的外键列，必须引用/参考主表的键主键或唯一约束的列，因为被依赖/被参考的值必须是唯一的。
2. 在创建外键约束时，如果不给外键约束命名，默认名不是列名。而是自动产生一个外键名（例如student_ibfk_1;) ，也可以指定外键约束名。
3. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表。
4. 删表时，先删从表(或先删除外键约束)，再删除主表。
5. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
6. 在"从表"中指定外键约束。并且一个表可以建立多个外铣约束。
7. 从表的外键列与主表被参照的列名字可以不相同，但数据类型必须一样，逻辑意义一致。
8. 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引，但是索引名是列名，不是外键的的约束名。(根据外键询效率很高)。
9. 删除外键约束后，必须手动删除对应的索引。

13.6.3 创建方式

1. 在CREATE TABLE时添加约束：
2. ALTER TABLE时增加约束：
   
3. ALTER TABLE时删除约束：
   

13.6.4 约束等级

1. Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
2. Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null。
3. No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作。
4. Restrict方式：同no action，都是立即检查外键约束。
5. Set default方式(在可视化工具SQLyog中可能显示空白)：父表有变更时，子表将外键列设置成一个默认的值，但innodb不能识别。

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT的方式。

13.6.5 开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：(概念解释）学生表中的 student_id 是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单片机低并发，不适合分布式、高并发集群。级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

13.7 CHECK约束

检查某个字段的值是否符合要求，一般指的是值的范围。

13.8 DEFAULT约束

给某个字段某列指定默认值，一旦设置默认值，再插入数据时，如果此字段没有显式赋值，则赋值为默认值。

1. 在CREATE TABLE时添加约束：
   
2. 在ALTER TABLE时添加约束：
   
3. 在ALTER TABLE时删除约束：
   

14 视图

14.1 概述

1. 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL中的一个重要概念。
2. 视图建立在已有表的基础上.视图赖以建立的这些表称为基表。
3. 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
4. 向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的SELECT语句。
   • 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会对应地发生变化，反之亦然。
5. 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，也是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

14.2 创建视图

14.2.1 单表视图

CREATE VIEW empview AS SELECT employee_id,last_name FROM emps;

视图中字段名命名方式：

14.2.2 多表视图

14.2.3 利用视图对数据进行格式化

14.2.4 基于视图创建视图

14.3 查看视图

1. 查看数据库的表对象、视图对象：SHOW TABLES;。
2. 查看视图的结构：DESC 视图名;。
3. 查看视图的属性信息：SHOW TABLE STATUS LIKE 'vu_emp';。
4. 查看视图的详细定义信息：SHOW CREATE VIEW vu_emp; 。

14.4 更新视图中的数据

1. 更新/删除视图中的数据，会导致基表中数据的修改。
2. 更新/删除表中的数据，也会导致视图中的数据的修改。
3. 要使视图可更新，视图中的行和底层基本表中的行之间必须存在对的关系。另外当视图定义出现如下情况时，视图不支持更新操作：
   • 在定义视图的时候指定了ALGORITHM= TEMPTABLE，视图将不支持INSERT和DELETE操作。
   • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作。
   • 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支特INSERT和DELETE操作。
   • 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值。
   • 在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY、HAVING、UNION等，视图将不支持INSERT、UPDATE、DELETE。
   • 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE。
   • 视图定义基于—个不可更新视图。
   • 常量视图。

14.5 修改、删除视图

14.5.1 修改视图

1. 使用RELACE方式：
   
2. 使用ALTER方式：
   

14.5.2 删除视图

DROP VIEW IF EXISTS vu_emp1,vu_emp2;

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改。否则影响使用。

15.存储过程与存储函数

存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

15.1 存储过程概述

15.1.1 介绍

含义：存储过程的英文是Stored Procedure，它的思想很简单，就是一组经过预先编译的SQL语句的封装。

执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

好处：

1. 简化操作，提高了sql语句的重用性，减少了开发程序员的压力。
2. 减少操作过程中的失误，提高效率。
3. 减少网络传输量（客户端不需要把所有的SQL语句通过网络发给服务器)。
4. 减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性。

和视图、函数的对比：
它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的SQL，可以直接操作底数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

15.1.2 分类

存储过程的参数类型可以是IN、OUT和INOUT，根据这一点分类如下：

1. 没有参数
2. 仅带IN类型
3. 仅带OUT类型
4. 既带IN又带OUT类型
5. 带INOUT类型

15.2 创建和调用存储过程

1. 类型1：无参数无返回值场景。
   
2. 类型2：带OUT无参数有返回值场景。
   
3. 类型3：带IN有参数无返回值场景。
   
4. 类型4：既带IN又带OUT有参数有返回值场景。
   
5. 类型5：带INOUT有参数有返回值场景。
   

15.3 存储函数的使用

存储函数就是MySQL中的自定义函数，定义好后，调用方式与调用MySQL预定义的系统函数一样。

15.4 存储过程和存储函数的对比

15.5 存储过程和函数的查看、修改、删除

15.5.1 查看

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息。
   
2. 使用SHOW STATUS语句查看存储过程和函数的状态信息。
   
3. 从information_schema.Routines表中查看存储过程和函数的信息。
   

15.5.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性，使用ALTER语句实现。

15.5.3 删除

删除存储过程和函数，可以使用DROP语句。

16.变量、流程控制和游标

16.1 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在MySQL 数据库中，变量分为系统变量以及用户自定义变量。

16.1.1 系统变量

1. 分类：

   • 全局系统变量（添加GLOBEL关键字）
   • 会话系统变量（添加SESSION关键字），不添加关键字时系统默认变量为会话级别。

2. 全局系统变量针对于所有会话〔连接)有效，但不能跨重启。

3. 会话系统变量仅针对于当前会话（连接）有效，会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。

4. 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如max_connections 用于限制服务器的最大连接数;有些系统变量作用域既可以是全局也可以是会话，例如 character_set_client 用于设置客户端的字符集;有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的MySQL连接ID。

查看所有或部分系统变量：

修改系统变量的值：

1. 方式1：修改MySQL配置文件，进而修改系统变量的值，但此方式需要重启MySQL服务。
2. 方式2：在MySQL服务运行期间，使用set命令重新设置系统变量的值。
   

16.1.2 用户变量

1. 分类：用户变量是用户自己定义的，作为MySQL编码规范，MySQL中的用户变量以一个"@"开头。根据作用范围不同，又分为会话用户变量和局部变量。

   • 会话用户变量：作用域和会话变量一样，只对当前连接的会话有效。
   • 局部变量：只在BEGIN和END语句块中有效，即只能在存储过程和函数中使用。

2. 会话用户变量：
   

3. 局部变量：
   

16.2 定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

16.2.1 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

1. 错误码的说明：
   • MySQL_error_code和 sqlstate_value都可以表示MySQL的错误。
     • MySQL_error_code是数值类型错误代码。
     • sqlstate_value是长度为5的字符串类型错误代码。
   • 例如，在ERROR1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。
   • 例如，在ERROR1142 (42000）中，1142是MySQL_error_code，'42000’是sqlstate_value。
     

16.2.2 定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。

1. 处理方式：CONTINUE、EXIT、UNDO。

   • CONTINUE：表示遇到错误不处理，继续执行。
   • EXIT：表示遇到错误马上退出。
   • UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。·

2. 错误类型（即条件）可以有如下取值：

   • SQLSTATE’字符串错误码’：表示长度为5的sqlstate_value类型的错误代码。
   • MySQL_error_code：匹配数值类型错误代码。
   • 错误名称：表示DECLARE … CONDITION定义的错误条件名称。
   • SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。
   • NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。
   • SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。

3. 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句，语句可以是像"SET变量=值"这样的简单语句，也可以是使用BEGIN … END编写的复合语句。
   

16.3 流程控制

针对于MySQL的流程控制语句主要有3类。注意:只能用于存储程序。

1. 条件到断语句：IF语句和CASE语句
2. 循环语句：LOOF、WHILE和REPEAT语句
3. 跳转语句：ITERATE和LEAVE语句

16.3.1 分支结构之IF

语法格式：

根据表达式的结果为TRUE或FALSE执行相应的语句。
特点：

1. 不用的表达式对应不同的操作。
2. 使用在BEGIN END中。
   

16.3.2 分支结构之CASE

16.3.3 循环结构之LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复并行直到循环被退出（使用LEAVE子句)，跳出循环过程。

16.3.4 循环结构之WHILE

WHILE语句创建一个带条件判断的活环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。

16.3.5 循环结构之REPEAT

16.3.6 跳转语句之LEAVE语句

可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。
基本格式如下：
LEAVE 标记名：其中，label参数表示循环的标志，LEAVE和BEGIN…END或循环一起被使用。

16.3.7 跳转语句之ITERATE语句

只能用在循环语句(LOOP、REPEAT和WHILE语句)内，表示重新开始循环，将执行顺序转到语句段开头处。
语句基本格式如下：
ITERATE label：label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

16.4 游标

16.4.1 介绍

游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

MySQL中游标可以在存储过程和函数中使用。

16.4.2 使用游标的步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。
如果我们想要使用游标，一般需要经历四个步骤。不同的DBMS中，使用游标的语法可能存在不同。

1. 声明游标：
   • DECLARE cursor_name CURSOR FOR select_statement;：这个语法适用于MySQL，SQL Server，DB2和 MariaDB。
   • DECLARE cursor_name CURSOR IS select_statement;：适用于Oracle或者PostgreSQL。

这里select_statement代表的是SELECT语句，返回一个用于创建游标的结果集。

2. 打开游标：OPEN cursor_name：打开游标的时候SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。
3. 使用游标：FETCH cursor_name INTO var_name,var_name：使用cursor_name来读取当前行，并将数据保存到var_name变量中。游标的查询结果集中的字段数，必须和INTO后面的变量数一致。
4. 关闭游标：CLOSE cursor_name。
   

16.5 MySQL8.0新特性—全局变量的持久化

MySQL8.0版本新增了SET PERSIST命令。例如，设置服务器的最大连接数为1000;
SET PERSIST max _connections = 1000;
MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。