视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，视图的主要目的是简化复杂的SQL查询，提高数据访问的安全性，以及隐藏数据的复杂性。。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以在创建视图的时候，主要的工作就在于创建这条SQL查询语句上。

1.2、语法

1、创建视图

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

2、查询视图

# 查看创建视图语句：

SHOW CREATE VIEW 视图名称;

#查看视图数据：
SELECT * FROM 视图名称 ...... ;

3、修改视图

方式一：
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]

方式二：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ]

4、删除视图

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

示列：

1、演示视图的创建、查询、修改、删除

-- 创建视图
create or replace view stu_view_1 as select id,name from t_student where id <= 10;

-- 查询视图
show create view stu_view_1 ;

select * from stu_view_1 ;
select * from stu_view_1 where id < 3;

-- 修改视图
create or replace view stu_view_1 as select id,name,no from t_student where id <= 10;

alter view stu_view_1 as select id,name from t_student where id <= 10;

-- 删除视图
drop view if exists stu_view_1 ;

2、对视图进行数据插入

注意：这里进行数据插入一定要符合源表的字段约束，比如，源表t_student中有一个age字段是not null 约束，那么下面的插入语句就会插入失败，因为age字段不能为空。

insert into stu_view_1 values(3,'张三');

insert into stu_view_1 values(11,'李四');

执行结果：执行上述的SQL发现，id为3和11的数据都是可以成功插入的。但是执行查询，查询出来的数据，却没有id为11的记录。

原因：因为在创建视图的时候，指定的条件为 id<=10，所以查询出来的数据没有id为11的数据。

解决方案：使用检查选项

1.3、检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。
MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。
当使用WITH CHECK OPTION子句创建视图时，MySQL会确保通过该视图进行的任何数据修改（如INSERT、UPDATE或DELETE操作）都满足视图的定义条件。换句话说，如果尝试通过视图插入或更新数据，但新的数据不满足视图的WHERE子句或其他定义条件，MySQL将拒绝这个操作。

1、CASCADED（级联）

说明：

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。那么在对v2视图进行更新操作时，不仅会检查v2，还会级联检查v2的关联视图v1。示例如下：可以看到在对v2进行更新时级联检查了v1的条件。

2、 LOCAL（本地）

说明：

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

示例：基于上个示例，修改v2视图的检查选项为local，然后再看对v2进行更新时是否会检查视图v1。

1.4、视图的更新

说明：要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION 或者 UNION ALL

示例：

1、创建一个使用了聚合函数sum的视图

create view v3 as select sum(age) from t_student;

2、向1中创建的视图插入数据

insert into v3 values(20);

分析：可见插入报错，可得如果视图包含了聚合函数，则视图不能进行更新操作。

1.5、视图的作用

1、简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3、数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

示列：

1、为了保证数据库表的安全性，开发人员在操作t_student表时，只能看到的用户的基本字段，屏蔽身份证和家庭住址两个字段。

create view t_student_view as select id,name,age,gender,createtime from t_student;

2、存储过程

2.1、概述

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。可以把存储过程理解为函数，MYSQL的函数。

特点：

封装：复用可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
可以接收参数，也可以返回数据，再存储过程中，可以传递参数，也可以接收返回值。
减少网络交互，效率提升，如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，只需要网络交互一次可能就可以了。

2.2、基本语法

1、创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
-- SQL语句;
END;

2、调用

CALL 存储过程名称([参数]);

3、查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称 ;

4、删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称；

特别注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

--创建存储过程p1
create procedure p1()
begin
select * from t_student;
end;

注意：上述创建过程不能在命令行中运行，因为在MySQL命令行中默认以分号";"为sql语句的结束
可以通过关键字delimiter指定SQL语句的结束符为：&&
delimiter && # 此SQL语句不能和下面的创建存储过程语句同时执行,需要分开执行。
create procedure p1()
begin
select * from t_student;
end&&

--调用存储过程
call p1();

--查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';
show create procedure p1;

--删除存储过程
drop procedure if exists p1;

2.3、变量

在MySQL中变量分为三种类型:

系统变量
用户定义变量
局部变量

1、系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

全局变量(GLOBAL): 全局变量针对于所有的会话。
会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

注意：

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

1、查看系统变量

-- 查看所有的系统变量
SHOW [SESSION | GLOBAL] VARIABLES;

-- 通过like模糊匹配的方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......';

-- 查看指定的变量
SELECT @@[SESSION | GLOBAL] 系统变量名;

2、设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL].系统变量名 = 值 ;

示列：

-- 查看系统变量
-- 查看当前会话系统变量
show session variables;
show session variables like 'auto%';

select @@session.autocommit;

--查看全局会话系统变量
show global variables like 'auto%';

select @@global.autocommit;

-- 设置系统变量
set session autocommit = 1;
set global autocmmit = 1;

2、用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。

1、赋值

赋值时，可以使用 = ，也可以使用 := 。

方式一：

SET @var_name = expr [, @var_name = expr] ... ;

或
SET @var_name := expr [, @var_name := expr] ... ;

方式二：

SELECT @var_name := expr [, @var_name := expr] ... ;

或
SELECT 字段名 INTO @var_name FROM 表名;

2、使用

SELECT @var_name ;

注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

示列：

-- 设置变量并赋值
方式一：
set @name = '张三';
set @age := 22;
set @sex:= '男',@status:= 1;

方式二：
select name into @name2 from t_student where id = 1;

-- 使用变量
select @name,@age,@sex,@status;

3、局部变量

局部变量：是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

1、声明局部变量

DECLARE 变量名变量类型 [DEFAULT ... ] ;

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

2、局部变量赋值

set 变量名 = 变量值;
set 变量名 := 变量值;

select 字段 into 变量名 from 表名 ...;

示列：

create procedure p2()
begin
-- 定义局部变量name
declare name varcahr(10) default "";
-- 给局部变量赋值
select name into @name from t_student where id = 1;
-- 使用局部变量
select name;

end;

# 调用存储过程

call p2();

2.4、条件控制语句

2.4.1、if

1、if 的语法结构

说明：在if条件判断的结构中，ELSEIF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

示列：

根据定义的分数score变量，判定当前分数对应的分数等级。

score >= 90分，等级为优秀
score >= 60分且 score < 90分，等级为及格
score < 60分，等级为不及格

create procedure grade()
begin
declare score int default 60;

-- 不指定字符集，注意如果数据库的字符集编码不是utf8，变量被赋值了中文，在调用存储过程的时候会出现编码错误

-- declare result varchar(10);
-- 指定字符集，不同版本的的MYSQL可能需要指定字符集，避免出现编码错误
declare result varchar(10) CHARACTER SET utf8mb4

if score >= 90 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
select result;
end;

call grade();

2.4.2、参数

1、参数类型

参数的类型，主要分为以下三种：IN、OUT、INOUT。具体的含义如下：

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

语法：

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名参数类型 ])
BEGIN
-- SQL语句
END ;

示列：

-- 创建存储过程函数

-- 同样的如果数据库字符集不是utf8编码，需要给变量赋值中文就需要指定字符集编码utf8mb4。

-- create procedure grade2(in score int,out result varchar(10) CHARACTER SET utf8mb4)
create procedure grade2(in score int,out result varchar(10))
begin

if score >= 90 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;

end;

-- 设置变量值
set @score := 90;

-- 函数调用，下面两句等价
call grade2(90,@result);

call grade2(@score,@result);

-- 获取返回值
select @result;

2.4.3、case

说明：条件分支判断语句。

语法1：

当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2，否则就执行 statement_list

CASE case_value
WHEN when_value1 THEN statement_list1
WHEN when_value2 THEN statement_list2
ELSE statement_list
END CASE;

语法2：

当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2，否则就执行 statement_list

CASE
WHEN search_condition1 THEN statement_list1
WHEN search_condition2 THEN statement_list2
ELSE statement_list
END CASE;

示列：

根据定义的分数score变量，判定当前分数对应的分数等级。

score >= 90分，等级为优秀
score >= 60分且 score < 90分，等级为及格
score < 60分，等级为不及格

create procedure grade5(in score int)
begin
declare result varchar(10) CHARACTER SET utf8mb4;
case
   when score >= 90 then
       set result = '优秀';
   when score >= 60 then
       set result = '及格';
   else
       set result = '不及格';
end case;
select concat('输入的成绩为: ', score, ' 成绩等级: ', result) as result;
end;

call grade5(90)

注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

2.5、循环控制语句

2.5.1、while

说明：while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

语法：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

示列：

1、计算从1累加到n的值，n为传入的参数值。

create procedure sum1(in n int)
begin
declare sum int default 0;
while n>=0 do
set sum := sum + n;
set n := n - 1;
end while;

select sum;

end;

call sum1(100);

2.5.2、repeat

说明：repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。

语法：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

示列：

1、计算从1累加到n的值，n为传入的参数值。(使用repeat实现)

create procedure sum2(in n int)
begin
declare sum int default 0;
repeat
set sum := sum + n;
set n := n - 1;
until n=0

end repeat;

select sum;

end;

call sum2(100);

2.5.3、loop

说明：LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用：

LEAVE ：配合循环使用，退出循环。
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

语法：

[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体

ITERATE label; -- 直接进入下一次循环

注意：上述语法中出现的 begin_label，end_label，label 指的都是自定义的标记。

示例：

1、计算从1到n之间的偶数累加的值，n为传入的参数值。

create procedure sum4(in n int)
begin
declare _sum int default 0;

-- 定义循环体标记sum
sum:loop
if n = 0 then

-- 使用自定义循环体标记sum，退出循环
leave sum;
end if;

if n%2 = 1 then
set n := n - 1;

-- 使用自定义循环体标记sum，跳过当前循环
iterate sum;
end if;

set _sum := _sum + n;
set n := n - 1;

-- 循环结束标识
end loop sum;

select _sum;

end;

call sum4(100)

2.6、游标

游标（CURSOR）：是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE。

语法分别如下：

1、声明游标

DECLARE 游标名称 CURSOR FOR 查询语句 ;

2、打开游标

OPEN 游标名称 ;

3、获取游标记录

FETCH 游标名称 INTO 变量 [, 变量 ] ;

4、关闭游标

CLOSE 游标名称 ;

示列：

1、根据传入的参数uage，来查询用户表t_student中，所有年龄小于等于uage的学生姓名（name）和性别（gender），并将用户的姓名、年龄和性别插入到所创建的一张新表t_temp_info(id,name,age,sex)中。

创建步骤：

声明游标, 存储查询结果集
准备: 创建表结构
开启游标
获取游标中的记录
插入数据到新表中
关闭游标

create procedure test_cursor(in uage int)
begin
-- 注意普通变量的声明必须要在声明游标之前
declare sname varchar(50);
declare sage int;

declare sgender char(1);
-- 声明游标
declare cur_student_info cursor for select name,age,gender from t_student where age <= uage ;

drop table if exists t_temp_info;

-- 创建表t_temp_info
create table if not exists t_temp_info(
id int primary key auto_increment,
name varchar(50),
age int,

gender char(1)
);

-- 开启游标
open cur_student_info ;

--此时的循环为死循环，需要解决死循环的问题

while true do
         fetch cur_student_info into sname,sage,sgender;
     insert into t_temp_info values(null,sname,sage,sgender);

end while;

-- 关闭游标

close cur_student_info;

end;

call test_cursor(30);

分析：上述的存储过程，最终在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。但是此时表 t_temp_info是存在数据的

解决办法：要想解决这个问题，就需要通过MySQL中提供的条件处理程序 Handler 来解决。

2.7、条件处理程序

条件处理程序（Handler）：可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

语法：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序

condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

示列：

1、解决上面的死循环问题

create procedure test_cursor(in uage int)
begin
-- 注意普通变量的声明必须要在声明游标之前

        declare sname varchar(50);

        declare sage int;

        declare sgender char(1);
-- 声明游标

        declare cur_student_info cursor for select name,age,gender from t_student where age <= uage ;

-- 声明条件处理程序，当SQL语句执行抛出的状态码为02000时，将关闭游标cur_student_info ,并退出程序。

        declare exit handler for SQLSTATE '02000' close cur_student_info ;

drop table if exists t_temp_info;

-- 创建表t_temp_info
create table if not exists t_temp_info(
        id int primary key auto_increment,
        name varchar(50),
        age int,

        gender char(1)
);

-- 开启游标
open cur_student_info ;

--此时的循环为死循环，需要解决死循环的问题

while true do
             fetch cur_student_info into sname,sage,sgender;
             insert into t_temp_info values(null,sname,sage,sgender);

end while;

-- 关闭游标

close cur_student_info;

end;

call test_cursor(30);

或者使用loop循环，并声明一个done变量来判断循环是否结束

-- 声明一个CONTINUE HANDLER来处理游标结束的情况

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

把while循环改为loop循环

-- 读取游标中的数据
read_loop: LOOP
-- 从游标中取数据
FETCH cur_student_info INTO sname, sage, sgender;
-- 检查是否已遍历完游标
IF done THEN
LEAVE read_loop;
END IF;
-- 插入到临时表中
INSERT INTO t_temp_info VALUES (NULL, sname, sage, sgender);
END LOOP;

3、触发器

3.1、简介

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

3.2、语法

3.2.1、创建触发器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;

3.2.2、查看触发器

SHOW TRIGGERS ;

3.2.3、删除触发器

-- 如果没有指定 schema_name，默认为当前数据库。
DROP TRIGGER [schema_name.]trigger_name ;

示列

需求：通过触发器记录 t_student表的数据变更日志，将变更日志插入到日志表t_student_logs中, 包含增加, 修改 , 删除 ;

1、创建t_student_logs表

CREATE TABLE t_student_logs(
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
operation VARCHAR(20) NOT NULL COMMENT '操作类型, insert/update/delete',
operate_time DATETIME NOT NULL COMMENT '操作时间',
operate_id INT(11) NOT NULL COMMENT '操作的ID',
operate_params VARCHAR(500) COMMENT '操作参数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、创建插入数据触发器

-- 创建插入数据触发器
create trigger t_student_insert_trigger
after insert
on t_student for each row
begin

insert into t_student_logs(operation,operate_time,operate_id,operate_params) values
('insert', now(), new.id, concat("插入的数据为：", "id=",new.id, " stuno=",new.stuno, " name=",new.name, " gender=",new.gender,
" age=",new.age, " idcard=",new.idcard," entrydate=",new.entrydate," addr=", new.addr));

end;

-- 测试数据
show triggers; -- 查看触发器

-- 插入数据测试

insert into t_student(id, stuno, name, gender, age, idcard, entrydate,addr) VALUES
(25,'025', '林枫','男',23,'12345678901234554X',now(),'深圳'),
(26,'026', '张三','男',24,'12322478901234554X',now(),'北京'),
(27,'027', '李四','男',25,'12345678901815454X',now(),'上海');

3、创建修改数据触发器

-- 修改数据触发器
create trigger t_student_update_trigger
after update
on t_student for each row
begin

insert into t_student_logs(operation,operate_time,operate_id,operate_params) values
('update',now(),new.id,concat(
"更新之前的数据为：", "id=",old.id, " stuno=",old.stuno, " name=",old.name, " gender=",old.gender, " age=",old.age, " idcard=",old.idcard," entrydate=",old.entrydate," addr=", old.addr,
" 更新之后的数据为：", "id=",new.id, " stuno=",new.stuno, " name=",new.name, " gender=",new.gender, " age=",new.age, " idcard=",new.idcard," entrydate=",new.entrydate," addr=", new.addr));

end;

-- 测试数据
show triggers;

-- 更新数据测试
update t_student set age=20 where name = '张三';

3、创建删除数据触发器

-- 删除数据触发器
create trigger t_student_delete_trigger
after delete on t_student for each row
begin
insert into t_student_logs(operation,operate_time,operate_id,operate_params) values
('delete',now(),old.id,concat("删除之前的数据为：", "id=",old.id, " stuno=",old.stuno, " name=",old.name, " gender=",old.gender, " age=",old.age, " idcard=",old.idcard," entrydate=",old.entrydate," addr=", old.addr));
end;

-- 测试数据

show triggers;

-- 删除数据测试

delete from t_student where id in (25,26,27);

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/617512