玖叶教程网

前端编程开发入门

2024年04月18日

MySQL 从入门到精通(八)之视图存储过程和触发器

本章节主要来讲解的是MySQL中的视图、存储过程、存储函数和触发器，这些都是MySQL数据库中的存储对象,学好能助你涨薪[偷笑]。

一、视图

视图(View)是一种虚拟存在的表，视图中的数据并不在数据库中真实存在，我们所有看到视图当中的行和列的数据都是来自于我们在定义视图之后所使用的这张表，并且是在使用视图时动态生成的。通俗来讲，视图只保存了查询的SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图的创建语法:

create [or replace] view 视图名[(列名)] as select 语句 [with[ cascaded | local ] check option ]

create or replace view sts_v_1 as select id,name from student where id=5; #or replace可以不加，但是修改必须加。

这条语句的意思是创建名为sts_v_1的视图，用来封装后面select语句查询返回的数据。

mysql> select * from sts_v_1;
+----+-----------+
| id | name |
+----+-----------+
| 5 | 韦一笑 |
+----+-----------+
1 row in set (0.00 sec)

视图的查询操作:

show create view 视图名;

select * from 视图名;

视图的修改操作:

create [or replace] view 视图名[(列名)] as select 语句 [with[ cascaded | local ] check option ]

mysql> create or replace view sts_v_1 as select id,name,s_no from student where id=5;

Query OK, 0 rows affected (0.03 sec)

alter view 视图名[(列名)] as select 语句 [with[ cascaded | local ] check option ]

mysql> alter view sts_v_1 as select id,name from student where id=5;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from sts_v_1;
+----+-----------+
| id | name |
+----+-----------+
| 5 | 韦一笑 |
+----+-----------+
1 row in set (0.00 sec)

视图的删除操作:

drop view 视图名 [,视图名称] 。。。

mysql> drop view sts_v_1;

Query OK, 0 rows affected (0.02 sec)


我们重新创建个视图，并往视图插入数据

mysql> create view sts_v_1 as select id,name,s_no from student where id<=13;

Query OK, 0 rows affected (0.04 sec)

mysql> insert into sts_v_1 values(9,'宋江','21113112'),(10,'刘浪','20225313'),(11,'章六','20215393');

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

我们往视图里插入的数据没有找到，是因为视图并不存储数据它只是个逻辑表，数据都存放在它的基表student中。还有我们创建的视图加了条件 id<=13 ，当插入id大于13的数据时，通过视图也是查不出来的。为了避免这种现象MySQL提供了在创建视图时尾部加上with cascaded check option 选项。

create or replace view sts_v_1 as select id,name,s_no from student where id<=13 with cascaded check option;

插入id大于13的会报错

mysql> create or replace view sts_v_1 as select id,name,s_no from student where id<=13 with cascaded check option;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into sts_v_1 values(12,'李广',20140987);
Query OK, 1 row affected (0.02 sec)

mysql> insert into sts_v_1 values(13,'李栋',20202987);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sts_v_1 values(14,'陈.栋',20202087);
ERROR 1369 (HY000): CHECK OPTION failed 'test.sts_v_1'

当通过with check option子句创建视图时，MySQL会通过视图检查正在更改的每一行，例如插入、更新、删除，以使其符合视图的定义，MySQL允许基于另一视图创建视图，它会检查依赖视图中的规则以保持数据一致性，为了确定检查的范围，mysql提供了两个选项:

cascaded和local，默认使用cascaded。

1、当我们创建视图时不加cascaded(级联)或local时,对视图的增删改mysql是不会去检查条件的

create view v_1 as select id,name from student where id <=20;

mysql> create or replace view v_1 as select id,name from student where id<=20;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into v_1 values(15,'暂定');
Query OK, 1 row affected (0.05 sec)
mysql> insert into v_1 values(25,'什么鬼');
Query OK, 1 row affected (0.01 sec)

# 插入不满足条件的值，视图查不出来
mysql> select * from v_1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 张无忌 |
| 2 | 哑婆婆 |
| 3 | 张三丰 |
| 4 | 成昆 |
| 5 | 韦一笑 |
| 6 | 刘邦 |
| 7 | 刘策 |
| 8 | 浏览 |
| 9 | 宋江 |
| 10 | 刘浪 |
| 11 | 章六 |
| 12 | 李广 |
| 13 | 李栋 |
| 15 | 暂定 |
+----+-----------+
14 rows in set (0.01 sec)

#
mysql> select * from student;
+----+-----------+----------+
| id | name | s_no |
+----+-----------+----------+
| 1 | 张无忌 | 20210112 |
| 2 | 哑婆婆 | 20210311 |
| 3 | 张三丰 | 20200112 |
| 4 | 成昆 | 20210223 |
| 5 | 韦一笑 | 20201230 |
| 6 | 刘邦 | 20110112 |
| 7 | 刘策 | 20220303 |
| 8 | 浏览 | 20212323 |
| 9 | 宋江 | 21113112 |
| 10 | 刘浪 | 20225313 |
| 11 | 章六 | 20215393 |
| 12 | 李广 | 20140987 |
| 13 | 李栋 | 20202987 |
| 15 | 暂定 | NULL |
| 25 | 什么鬼 | NULL |
+----+-----------+----------+
15 rows in set (0.00 sec)

2、如果创建视图v_2并关联了v_1 且加了cascaded，那么对视图的操作必须满足v_1和v_2 所定义的条件。

create view v_2 as select id,name from v_1 where id >=20 with cascaded check option;

mysql> insert into v_2 values(19,'落凤坡'); # 不满足id>=20, 但满足v_1同样不能插入

ERROR 1369 (HY000): CHECK OPTION failed 'test.v_2'

mysql> insert into v_2 values(20,'落凤坡'); # 满足v_1,v_2

Query OK, 1 row affected (0.03 sec)

mysql>

CASCADED级联方式:

local同样会递归去检查依赖条件。

视图的检查选项主要指的是当我们对视图当中的数据进行增删改操作时,检查选项with cascaded/local check option会去检查我们所操作的数据是否符合视图定义时的条件。

对于视图的更新，视图中的行与基表中的行之间必须存在一对一的关系，如果视图包含以下任何一项，则该视图不可更新。比如:1、聚合函数(sum()、min()、max()、count()等)；2、distinct、group by、having、union或者union all这些关键字。

mysql> create view v_3 as select count(*) from student;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into v_3 values(16);

ERROR 1471 (HY000): The target table v_3 of the INSERT is not insertable-into

使用了聚合函数后视图和基表已经不是一一对应的了，所以不能插入也不能更新。

那么视图到底有什么作用呢，首先视图是不是操作很简单，它不仅可以简化用户对数据的理解，也可以简化它们的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次都指定全部条件。其次是安全，数据库是可以授权的对吧，但是不能授权到数据库特定行和特点列上，通过视图用户只能查询和修改它们所能见到的数据。最后，视图可以帮助用户屏蔽真实表结构变化带来的影响，比如给字段取别名。

实际生产中为保证数据的安全，比如对开发人员在操作king_user表时，只能看到表的基本字段，屏蔽掉邮箱地址和手机号。

create view king_v as select id,name,profession,age,gender,status,createtime from king_user; 当开发要查询这张表的数据的时候，让他查king_v视图即可。

对于多表联查，特别适合适用视图，简化那一长串的SQL语句。

二、存储过程

存储过程就是事先经过编译并存储在数据库中的一段SQL语句的集合，通过调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的网络传输，对于提高数据处理的效率是很有帮助的。说的简单点就是SQL语句的封装和重用。

存储过程的特点有: sql语句的封装和复用，其次是可以接收参数，也可以返回数据，减少网络交互，提升效率。

存储过程的操作语法:

create procedure 存储过程名称([参数列表])

begin

。。。SQL语句

end;

调用存储过程： call 名称([参数])

命令行创建存储过程需要通过delimiter指定结束符,mysql是以分号结束的。

delimiter // # 指定什么字符结束

create procedure p()

begin

select count(*) from king_user;

end//

mysql> delimiter @@
mysql> create procedure pk()
-> begin
-> select count(*) from king_user;
-> end@@
Query OK, 0 rows affected (0.00 sec)
mysql>
  
  # 改回来 ，调用存储过程
 mysql> delimiter ;
mysql> call pk();
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

存储过程的查看和删除操作

SELECT * FROM information_schema.ROUTINES where ROUTINE_SCHEMA='test'

show create procedure pk;

drop procedure pk();

存储过程的系统变量和用户自定义变量。

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面的，分为全局变量global和会话变量session。语法如下:

1、查看系统变量

show [session|global] variables; # 查看索引系统变量

show [session|global] variables like '...'; # 模糊匹配查找系统变量

select @@[session|global] 系统变量名; # 查看指定变量的值

2、设置系统变量

set [session|global] 系统变量名=值;

set @@[session|global] 系统变量名=值;

提示: 如果没有指定session或者global，默认是session,mysql服务重启后所有设置的全局参 数会失效，要想不失效必须在全局设置的同时把设置内容添加到/etc/my.cnf中。

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

赋值:

set @var_name = expr;

set @var_name := expr; # 推荐使用:=

select @var_name := expr;

select 字段名 into @var_name from 表名;

使用方法: select @var_name; 即可

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

3、局部变量

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

声明: declare 变量名 变量类型 [default ...]; 其中变量类型就是数据库字段类型

赋值: set 变量名=值; set 变量名 := 值; select 字段名 into 变量名 from 表名 ...;

# 在存储过程中 声明一个stu_count局部变量，然后赋值及展示
create PROCEDURE pk2()
BEGIN
DECLARE stu_count int default 0;
SELECT count(*) into stu_count from king_user;
SELECT stu_count;
END;
CALL pk2();

if 条件判断的语法

if 条件1 then
。。。
elseif 条件2 then
。。。
else
。。。
end if;

# 判断 小明分数 例子
create PROCEDURE pk3()
BEGIN
   DECLARE score int default 58;
   DECLARE result VARCHAR(10);
   if score >= 85 THEN
      set result := '优秀';
   elseif score >= 60 THEN
      set result := '及格';
   ELSE
			set result := '不及格';
   end if;
   select result;
end;

# 结果为不及格；定义了score 默认值为58，此处代码不灵活
call pk3();

存储过程的参数:

in： 该参数作为输入，也就是需要调用时传入。

out: 该类参数作为输出，也就是该参数可以作为返回值。

inout: 既可以作为输入参数,也可以作为输出参数。

用法:

create procedure 存储过程名([in/out/inout 参数名 参数类型])

begin

SQL语句

end;

# 使用传参的方式
create PROCEDURE pk4(in score int,out result varchar(10))
BEGIN
#DECLARE score int default 58;
#DECLARE result VARCHAR(10);
if score >= 85 THEN
set result := '优秀';
elseif score >= 60 THEN
set result := '及格';
ELSE
set result := '不及格';
end if;
select result;
end;

# 传参测试
CALL pk4(80, @result);
select @result;

存储过程中的case|while 语句

case   case_value
when when_value1  then 语句1
when when_value2 then 语句2
else 其它语句
end case;

语法2:
case 
when search_condition1 then 语句1
when search_condition2 then 语句2
else 其它语句
end case;

案例: 使用存储过程实现，根据传入的月份，判定月份所属的季节(case结构实现)

1、1~3 月为第一季度

2、4~6月为第二季度

3、7~9月为第三季度

4、10~12月为第四季度

create PROCEDURE pk5(in month int)
BEGIN
DECLARE result VARCHAR(10);

CASE
WHEN month >=1 and month <=3 THEN
set result := '第一季度';

WHEN month >=4 and month <=6 THEN
set result := '第二季度';
WHEN month >=7 and month <=9 THEN
set result := '第三季度';
WHEN month >=10 and month <=12 THEN
set result := '第四季度';

ELSE
set result := '非法参数';
end case;

select CONCAT('输入的月份是: ',month,',所属的季度是: ',result);
end;

# 传参
call pk(11);

while循环是有条件的循环控制语句，满足条件后，再执行循环体中的SQL语句，语法如下:

while 条件

dosql 逻辑。。。

end while

 create PROCEDURE pk6(in n int)
BEGIN
DECLARE total int default 0;
WHILE n>0 DO
set total := total + n;
set n := n-1;
end WHILE;
SELECT total;
end;  

call pk6(10)

三、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的具体语法如下:

create function 存储函数名([参数列表])

returns type [characteristic ...]

begin

--SQL语句

return ...;

end;

characteristic:

deterministic: 相同的输入参数总是产生相同的结果

no sql: 不包含SQL语句

reads sql data: 包含读取数据的语句，但不包含写入数据的语句。

# 通过存储函数 完成从1累加到n的值，n为传入的参数值

create FUNCTION fun1(n int)
RETURNS int DETERMINISTIC
BEGIN
DECLARE total int DEFAULT 0;
while n>0 DO
set total := total + n;
set n := n - 1;
end while;
RETURN total;
end;
SELECT fun1(100)

四、触发器

触发器是与表结构有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这些特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其它的数据库是相似的，现在触发器还只支持执行级触发，不支持语句级触发。

insert型的触发器，new表示将要或者已经新增的数据

update型的触发器，OLD表示修改之前的数据，NEW表示将要或已经修改后的数据。

delete型触发器，OLD表示将要或者已经删除的数据。

触发器语法:

# 创建
create TRIGGER 触发器名
before/after insert/UPDATE/DELETE
ON 表名 for each row ... -- 行级触发器
BEGIN
trigger_stmt;
end;

# 查看
SHOW TRIGGER;

# 删除
drop TRIGGER [schema_name] 触发器名; -- 如果没有指定，schema_name，默认为当前数据库

创建一张日志表来监控 king_user的增删改操作，并将更新日志记录到user_logs表中。


create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型 insert/update/delete',
  operation_time datetime not null COMMENT '操作时间',
  operation_id int(11) not null COMMENT '操作的id',
  operation_params VARCHAR(500) COMMENT '操作参数',
  primary key(id)
)engine=innodb default charset=utf8;

-- 插入数据时的触发器
create trigger tb_king_user_insert_trigger
AFTER INSERT on king_user for each ROW
BEGIN
insert into user_logs VALUES(null,'insert',now(),new.id,CONCAT('插入的数字内容为: id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=,',new.profession));
END;

#插入数据测试
mysql> insert into king_user values(12,'卢俊义',38,'男','13298987381','[email protected]','二当家','2','2021-03-23 12:10:23');
Query OK, 1 row affected (0.04 sec)

mysql> select * from user_logs\G;
*************************** 1. row ***************************
              id: 1
       operation: insert
  operation_time: 2022-04-16 21:26:07
    operation_id: 12
operation_params: 插入的数字内容为: id=12,name=卢俊义,phone=13298987381,[email protected],profession=,二当家
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql>

-- 修改数据触发器

create trigger tb_king_user_update_trigger
AFTER update on king_user for each ROW
BEGIN
insert into user_logs VALUES(null,'update',now(),new.id,CONCAT('更新前的数居内容为: id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=,',old.profession)),
(null,'update',now(),new.id,CONCAT('更新后的数居内容为: id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=,',new.profession));
END;

UPDATE king_user set age=32 where id=1; # 验证是否记录到日志表里

-- 删除数据触发器

create trigger tb_king_user_delete_trigger
AFTER delete on king_user for each ROW
BEGIN
insert into user_logs VALUES(null,'delete',now(),old.id,CONCAT('删除前的数居内容为: id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=,',old.profession));
END;

mysql> delete from king_user where id=13;

Query OK, 1 row affected (0.11 sec)

你学费了吗

标签：mysqlor 

作者:gojiuye , 分类:技术精选 , 浏览:21 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言