04 约束和视图

约束

什么是约束

约束是表级的强制规定

为什么需要约束

添加数据可以保证数据的完整性，精准性，可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入造成无效操作或者错误信息而提出的。

约束的分类

• 根据约束的列的限制，约束可分为
  • 单列约束。每个约束只能约束一类列
  • 多列约束。每个约束可以约束多列
• 根据约束的作用范围，约束可分为
  • 列级约束。只能作用一个列上，跟在列的定义后面
  • 表级约束。可以作用在多个列上，不与列一起，而是单独定义

约束的类型

定义	含义
NOT NULL	非空约束，规定某个字段不能为空
UNQUE	唯一约束，规定某个字段在整个表中是唯一的
PAOMARY KEY	主键约束（非空，唯一）
FOREIGN KEY	外键约束
CHECK	检查约束
DEFAULT	默认值约束

查看表约束

#information_schema数据库名（系统库） 
#table_constraints表名称（专门存储各个表的约束） 
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

查看表索引

SHOW INDEX FROM 表名;

非空约束

作用

限制字段不能为 NULL，如果添加数据为 NULL，则会插入数据失败

关键字

NOT NULL

添加约束

CREATE TABLE emp01(
	id INT NOT NULL,
	last_name VARCHAR(25) NOT NULL,
	emal VARCHAR(30),
	salary DECIMAL(10,2)
);

修改约束

修改表字段属性时直接添加约束即可

ALTER TABLE emp01 MODIFY emal VARCHAR(30) NOT NULL;

删除约束

重新设置列的属性，不设置约束即可删除列的约束

-- 删除列的非空约束
ALTER TABLE emp01 MODIFY emal VARCHAR(30);

唯一约束

作用

用来限制某个字段不能重复

关键字

UNIQUE

特点

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

添加约束

添加列唯一约束

CREATE TABLE emp02 (
	id INT NOT NULL UNIQUE,
	last_name VARCHAR(15) NOT NULL,
	emal VARCHAR(25) UNIQUE
);

DESC emp02;

SELECT * FROM emp02;

INSERT INTO emp02
VALUES 
(1,"Time","time@123.com");

-- Duplicate entry '1' for key 'emp02.id' 
-- id 列是唯一约束，不能重复
INSERT INTO emp02
VALUES
(1,"Jary","jary@123.com");

添加组合约束

-- 创建列组合唯一约束
-- 组合约束，限制两个列的值组合起来不能重复
CREATE TABLE emp03(
	id INT UNIQUE, 
	sid INT, -- 学生id
	cid INT, -- 课程id
	grad DECIMAL(5,2), -- 分数
	
	CONSTRAINT uk_emp03_sid_cid UNIQUE(sid,cid)
);


INSERT INTO emp03
VALUES
(1,1001,3001,100);

-- Duplicate entry '1' for key 'emp03.id'
-- 学生id和课程id不能同时重复
INSERT INTO emp03
VALUES
(1,1001,3001,120);

SELECT * FROM emp03;

修改约束

-- 修改唯一约束
ALTER TABLE emp02 MODIFY last_name VARCHAR(20) UNIQUE;

删除约束

删除唯一约束时要找到对应的唯一索引，然后删除唯一索引

使用 SHOW INDEX FROM 表名; 查看唯一索引有哪些

查看 emp03 表的唯一索引有哪些

SHOW INDEX FROM emp03;

删除唯一索引

ALTER TABLE emp03 DROP INDEX uk_emp03_sid_cid;

删除后再次查看唯一索引

主键约束

作用

用来唯一标识表中的一行数据

关键字

PRIMARY KEY

特点

• 主键约束相当于 唯一约束 + 非空约束 的组合，主键的列不能重复，也不能为空值
• 一个表只能有一个主键约束，建立的主键约束可以是列级约束也可以是表级约束
• 主键约束可以对应表中的一个列，也可以是组合约束，对应多个列
• 如果是多列组合的主键约束，那么这些列都不能是空的，组合起来不能重复
• MySQL 的主键约束的主键名总是：PRIMARY，自己命名的主键约束名没用
• 不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性

添加约束

创建表的同时添加主键约束

CREATE TABLE emp05(
	id INT PRIMARY KEY,
	name VARCHAR(15),
	password VARCHAR(15)
);

DESC emp05;

SELECT * FROM emp05;

-- 插入数据，主键约束的字段不能为空和不能重复
INSERT INTO emp05
VALUES (1,"time","admin123456");

-- Duplicate entry '1' for key 'emp05.PRIMARY'
-- 主键字段不能重复
INSERT INTO emp05
VALUES (1,"jack","admin123456");

通过 ALTER 添加约束

CREATE TABLE emp06(
	id INT,
	name VARCHAR(15),
	password VARCHAR(15)
);

SHOW INDEX FROM emp06;

ALTER TABLE emp06 ADD PRIMARY KEY(id);

删除约束

实际开发中不会去删除主键约束

ALTER TABLE emp06 DROP PRIMARY KEY;

自增列

作用

设置列字段名自动网上递增

关键字

AUTO_INCREMENT

特点

• 只能作用在 int 类型的列上
• 一般和主键 PRIMARY KEY 同时使用

添加约束

CREATE TABLE emp07(
	id INT auto_increment PRIMARY KEY,
	last_name VARCHAR(15)
);

-- 自增的列会自动从0开始向上递增
INSERT INTO emp07(last_name)
VALUES ("time");

SELECT * FROM emp07;

-- 如果插入了一个跳过递增的列值，则在下次插入是会沿着最新插入的值继续递增
INSERT INTO emp07
VALUES(10,"jack");

删除约束

通过 MODIFY 关键字修改列属性，去掉 AUTO_INCREMENT 属性就相当于删除了自增约束

ALTER TABLE emp07 MODIFY id INT;

外键约束

作用

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

比如：员工表的员工所在的部门，必须在部门表中存在。如果添加的员工部门id在部门表中不存在则添加失败。

关键字

FOREIGN KEY ( 从表引用列名 ) REFERENCES 主表名( 主表被引用列名 )

特点

• 从表的外键列，必须引用主表的主键或者唯一约束列
• 在创建外键约束时，如果不给外键约束命名，默认不是列名，而是自动产生一个外键名，也可以指定外键约束名
• 如果想在创建表的同时指明外键约束，则应该创建主表，在创建从表
• 删除表时，先删除从表数据，再删除主表数据
• 当主表的记录被参照时，主表的记录不允许被直接删除，应该先删除从表中所有依赖主表的数据，然后再删除主表记录
• 一个从表可以有多个外键约束
• 从表的外键列的列名不需要和主表的参照列列名相同，但是两个列的数据类型必须相同。
• 当创建外键约束时，系统会自动的创建一个外键索引
• 删除外键约束后，也必须手动的删除外键索引

添加约束

添加数据

-- 创建一个部门表
CREATE TABLE dep(
	id INT PRIMARY KEY auto_increment,
	depname VARCHAR(15)
);

-- 创建人员表，人员表中有一个部门id，设置这个列为外键约束，和部门表中的id对应
CREATE TABLE stu(
	id INT PRIMARY KEY auto_increment,
	last_name VARCHAR(15),
	dep_id INT NOT NULL,
	FOREIGN KEY (dep_id) REFERENCES dep(id)
);

-- 往部门表中插入 IT 和 HR 两个部门
INSERT INTO dep(depname)
VALUES("IT"),("HR");

SELECT * FROM dep;

-- 1.往人员表中插入time，部门id是1
INSERT INTO stu(last_name,dep_id)
VALUES
("time",1);

-- 2.往人员表中插入jack，部门id是2
INSERT INTO stu(last_name,dep_id)
VALUES
("jack",2);

-- 3.往人员表中插入mali，部门id是3
-- Cannot add or update a child row: a foreign key constraint fails (`dbtest13`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
-- 插入失败，因为部门表里没有id为3的数据
INSERT INTO stu(last_name,dep_id)
VALUES
("mali",3);

SELECT * FROM stu;

如果直接删除主表中被参照的数据，则删除不成功

DELETE FROM dep WHERE id = 2;

直接更新部门id同样会更新失败

UPDATE dep SET id = 3 WHERE id = 2;

同样的修改人员部门id为部门表中不存在的值也会失败

UPDATE stu SET dep_id = 3 WHERE id = 2;

成功删除的方法

-- 先删除人员表中的数据
DELETE FROM stu WHERE dep_id = 2;
-- 再删除部门表中的数据
DELETE FROM dep WHERE id = 2;

约束等级

• Cascade：在父表上更新和删除记录时，同步更新和删除子表的数据
• Set null：在父表上更新和删除记录时，将子表上匹配的记录设置为null，但是要注意子表的外键列不能是 not null
• No action：如果子表中有匹配的记录，则不允许对父表对应列进行更新和删除
• Restrict：同No action，都是立即检查外键约束

如果没有指定等级，默认采用的是 Restrict

对于外键约束，最好采用 ON UPDATE CASCADE ON DELETE RESTRICT 的方式，意思是如果更新主表，则从表跟着更新，如果删除主表则子表相关联的数据变成 null

案例练习

-- 创建位置表
CREATE TABLE localhost(
	id INT PRIMARY KEY,
	address VARCHAR(50)
);

-- 创建员工表并设置外键约束
CREATE TABLE emp(
	id INT PRIMARY KEY,
	last_name VARCHAR(15),
	loc_id INT,
	FOREIGN KEY (loc_id) REFERENCES localhost(id) ON UPDATE CASCADE ON DELETE SET NULL
);

-- 插入位置数据
INSERT INTO localhost
VALUES
(1,"郑州"),
(2,"杭州"),
(3,"广州"),
(4,"苏州");

SELECT * FROM localhost;

-- 插入员工数据
INSERT INTO emp
VALUES
(1,"mali",1),
(2,"lili",2),
(3,"wangjun",2),
(4,"mingyue",3),
(5,"mengxiao",4);

SELECT * FROM emp;

此时员工表的数据如下

修改位置表中id等于2的id为5

UPDATE localhost SET id = 5 WHERE id = 2;

修改完成后员工表中原本 loc_id = 2 的数据现在自动跟着更新为 loc_id = 5

现在删除位置表中id是3的数据

DELETE FROM localhost WHERE id = 3;

删除后员工表中原本位置id是3的数据，现在位置id是null

删除约束

-- 1.查看表中的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = 'stu';

-- 2.根据约束名删除
ALTER TABLE stu DROP FOREIGN KEY stu_ibfk_1;

-- 3.查看表中的索引
SHOW INDEX FROM stu;

-- 4.删除表中索引
ALTER TABLE stu DROP INDEX dep_id;

开发场景

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以， MySQL 允许你不使用系统自带的外键约束，在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

阿里开发规范

【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。

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

检查约束

作用

显示列的字段必须符合一定的条件

关键字

CHECK

创建约束

CREATE TABLE emp08(
	id INT PRIMARY KEY auto_increment,
	last_name VARCHAR(15),
	salary DECIMAL(10,2) CHECK(salary > 2000)
);

-- 插入数据
INSERT INTO emp08(last_name,salary)
VALUES
("time",4500);

-- 当插入的员工工资小于2000时插入失败
--  Check constraint 'emp08_chk_1' is violated.
INSERT INTO emp08(last_name,salary)
VALUES
("time",1500);

SELECT * FROM emp08;

此时查看表中数据只有一条

默认值约束

作用

设置列字段的默认值，当插入数据时没有指明设置有默认值约束的列，则这个列的值自动为默认值

关键字

DEFAULT

创建约束

CREATE TABLE emp09(
	id INT PRIMARY KEY auto_increment,
	last_name VARCHAR(15),
	salary DECIMAL(10,2) DEFAULT(2000)
);

-- 主动插入一个非默认值数据
INSERT INTO emp09(last_name,salary)
VALUES
("time",2500);

-- 不插入 salary 的值时采用默认值
INSERT INTO emp09(last_name)
VALUES
("mali");

SELECT * FROM emp09;

查看数据，第二条记录的工资值是 2000

视图

视图的理解

• 视图可以看做是一个虚拟表，本身是不存储数据的，视图的本质，就可以看做是存储起来的 select 语句
• 视图中的查询语句设计到的表称为基表
• 针对视图的增删改操作会影响到对应的基表中的数据，反之亦然
• 视图本身的删除，不会导致基表中的数据删除
• 视图的应用场景：针对小型项目可以不使用视图。针对大型项目，可以考虑使用视图来简化数据操作
• 优点：简化查询，控制数据的访问

创建视图

创建语句

CREATE VIEW 视图名称
AS
SELECT 语句;

基于单表视图

CREATE VIEW emp_info01
AS
SELECT employee_id,last_name,salary
FROM employees;

SELECT * FROM emp_info01;

基于多表视图

CREATE VIEW emp_inf02
AS
SELECT t1.last_name,t2.department_name
FROM employees t1,departments t2
WHERE t1.department_id = t2.department_id;

SELECT * FROM emp_inf02;

基于视图的视图

CREATE VIEW emp_info03
AS
SELECT CONCAT(last_name,"(",department_name,")")
FROM emp_inf02;

SELECT * FROM emp_info03;

查看视图

方式一:查询表和视图

SHOW TABLES;

方式二

DESC emp_inf02;

方式三：查看视图信息，版本，存储引擎等

SHOW TABLE STATUS LIKE "emp_inf02";

方式四：查看视图的创建信息

SHOW CREATE VIEW emp_inf02;

更新视图

我们可以更新视图中的数据，基表中的数据也会跟着更新。但是在有些场景下更新会失败。

首先创建一个基于单表的视图

CREATE VIEW view_emp01
AS
SELECT employee_id,last_name,salary
FROM employees;

更新视图中id为101的工资为 20000

UPDATE view_emp01 SET salary = 20000 WHERE employee_id = 101;

查看视图中的数据

SELECT * FROM view_emp01 WHERE employee_id = 101;

查看表中的数据，发现也会跟着修改

SELECT employee_id,last_name,salary,department_id FROM employees WHERE employee_id = 101;

现在我们在来看一个更新失败的案例。

首先基于两张表创建一个视图。查询每个部门的基本工资

CREATE VIEW view_dep_avg_salary AS SELECT
t2.department_name,
AVG( t1.salary ) avg_salary 
FROM
	employees t1
	JOIN departments t2 ON t1.department_id = t2.department_id 
GROUP BY
	t1.department_id;
	
SELECT * FROM view_dep_avg_salary;

此时我们更新这个视图中部门名称为 IT 的平均工资为6000

-- The target table view_dep_avg_salary of the UPDATE is not updatable
UPDATE view_dep_avg_salary SET avg_salary = 6000
WHERE department_name = "IT";

会提示错误信息，无法更新视图。

总结：在开发中不会去修改视图中的数据，视图只会作为查看功能。需要修改数据时要去表中修改

删除视图

• 删除视图不会删除基表数据

语法如下：删除视图 emp_inf02

DROP VIEW emp_inf02;

视图总结

优点

• 操作简单
• 减少数据冗余
• 数据安全
• 适应灵活多变的查询逻辑
• 能够分解复杂的查询逻辑

不足

• 由于视图是基于表来创建的，当基表的表结构发生变化时，需要及时的对相关视图进行维护，特别是嵌套视图，维护会变得比较复杂。
• 实际项目中如果视图过多，会导致数据库维护成本变大

存储过程与函数

理解

含义：

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

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

好处：

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

和视图、函数的对比：

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

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

分类

关键字	含义
	无参无返回
IN	有参数无返回
OUT	没参数有返回
IN OUT	有参数又返回
INOUT	有参数又返回

DELIMITER 说明

DELIMITER 用来设置结束语句，在 MySQL 中默认分号 ; 是结束语，但是在创建函数时会有多个查询语句的情况，每个查询语句都要用分号结束，所以在创建函数前先用 DELIMITER 声明一个其他字符作为结束语，然后再创建函数结束之后在把分号 ; 设置为结束语。

创建存储过程

方式一：无参

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $
DELIMITER ;

-- 调用存储函数
CALL select_all_data();

显示查询结果

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

delimiter $
CREATE PROCEDURE avg_employee_salary()
BEGIN
	SELECT AVG(salary) FROM employees;
END$
delimiter ;

CALL avg_employee_salary();

方式二：IN

创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数 empname 输入员工姓名

delimiter $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(15))
BEGIN 
 SELECT salary FROM employees WHERE last_name = empname;
END $
delimiter ;

-- 调用参数
CALL show_someone_salary("Ernst");

方式三：OUT

举例一：创建存储过程 show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

delimiter $
CREATE PROCEDURE show_min_salary(OUT ms DECIMAL(15,2))
BEGIN 
	SELECT MIN(salary) INTO ms FROM employees;
END $
delimiter ;
-- 调用函数获得返回值
CALL show_min_salary(@ms);
-- 查看返回值
SELECT @ms;

举例二：创建存储过程 get_all_salary()，查看所有员工的工资总和，并通过 all_salary 参数输出

delimiter $
CREATE PROCEDURE get_all_salary(OUT all_salary DOUBLE(14,2))
BEGIN
	SELECT SUM(salary) INTO all_salary FROM employees;
END $
delimiter ;

CALL get_all_salary(@all_salary);

SELECT @all_salary;

方式四：IN OUT

创建一个方法 get_salary_from_name()，输入员工姓名，返回该员工的工资

delimiter //
CREATE PROCEDURE get_salary_from_name(IN `name` VARCHAR(25),OUT emp_sa DOUBLE(15,2))
BEGIN 
	SELECT salary INTO emp_sa FROM employees WHERE last_name = `name`;
END //
delimiter ;

-- 调用方法
CALL get_salary_from_name("Austin",@emp_sa);
-- 显示返回值
SELECT @emp_sa;

方式五：INOUT

创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名

delimiter //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(15))
BEGIN 
	SELECT
		last_name 
	INTO
		empname
	FROM
		employees 
	WHERE
		employee_id = ( SELECT manager_id FROM employees WHERE last_name = empname);
END //
delimiter ;

-- 设置入参值
SET @empname := "Hunold";
-- 调用函数
CALL show_mgr_name(@empname);
-- 显示返回值
SELECT @empname;