Mysql中的视图、变量、存储过程、流程控制结构、函数和触发器
一、视图
2.1 、什么是视图
视图mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
2.2、使用视图的好处
- 简化sql语句，用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 保护基表的数据，提高了安全性，使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
2.3、创建视图
CREATE [OR REPLACE] VIEW view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件(where语句)的记录。
LOCAL ： 只要满足本视图的条件就可以更新。
CASCADED(默认值) ： 必须满足所有针对该视图的所有视图的条件才可以更新。
2.4、修改视图
修改方式一：
CREATE [OR REPLACE] VIEW view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
方式二：
alter view view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
2.3、查看视图
查看视图的方式和查看表的方式差不多
desc view_name
show create view view_name
show tables;-- 不仅可以显示当前数据库下的表还可以显示出视图,没有show views
2.4、删除视图
drop view view_name1,view_name2
注意：视图一般是用于查询，而不是更新，具有以下特点的视图都不允许更新
- 包含分组函数、group by、distinct、having、union、join
- 视图字段来自字段表达式或常数，不可执行INSERT和UPDATE，但允许执行DELETE
- where后的子查询用到了from中的表
- 不可更新的视图导出的视图
2.5视图与表的比较
| 关键字 | 是否使用物理空间 | 作用于 | |
|---|---|---|---|
| 视图 | view | 只保留sql逻辑，占用空间小 | 一般用于查询 |
| 表 | table | 保存实际的数据 | 可用于增删改查 |
二、mysql中的变量
mysql中的变量按作用范围可以分为以下几个类型：局部变量，会话变量，全局变量。按是否是用户自定义的又可以分为系统变量和用户自定义变量。
2.1、系统变量
系统变量就是由系统提供的变量，不用用户自定义。
-
查看系统变量
-- 查看所有系统变量 show [global | session ] variables [like] -- 查看指定的系统变量 select @@[global | session].变量名 -- 类似与对象.属性 -- 给系统变量赋值 set [global | session] 变量名 = 值 set @@[global | session].变量名=值;
- global表示全局变量，服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效
- session表示会话变量，作用域为当前的会话(连接)
注意:local是session的近义词，session可以使用local代替，在不指定session或者global的情况下，默认是session
注意:无论是global变量还是session变量，在mysql服务重启之后，数据库的配置又会重新初始化，一切按照my.ini的配置进行初始化。global和session的配置都会失效了
2.2、用户自定义变量
-
局部变量
局部变量一般用在sql语句块中，比如存储过程的begin/end，只在当前begin/end代码块中有效，且只能放在begin end中，而且只能放在第一句。在该语句块执行完毕后，局部变量就消失了。declare语句专门用于定义局部变量，可以使用default来说明默认值。
声明方式：declare 变量名 类型 【default 值】;
赋值方式:
set 变量名 = 值； set 变量名: = 值； select xx into 变量名 from 表;CREATE PROCEDURE myprocedure() BEGIN declare age int default 0; set age = 5; select age; end -
用户变量(一次会话有效)
MySQL中用户变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。用户变量 在客户端链接到数据库实例整个过程中用户变量都是有效的。可以在begin end里面，也可以放在外面。
注意：无论使用还是赋值用户变量都需要在前面记得加上@
赋值方式:
set @变量名=值; set @变量名:=值; select @变量名:=值; select xx into @变量名 from 表; select @num:=字段名 from 表名;注意上面两种赋值符号，使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”
使用方式:
select @变量名;
三、存储过程
存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有
-
存储函数:是一个有返回值的存储过程
-
存储过程：是一个没有返回值的存储函数
3.2、创建存储过程
CREATE PROCEDURE procedure_name (参数模式 参数名 参数类型)
begin
SQL语句
end
-- 参数类型有三种
in(默认):表示输入的参数
out:表示输出的参数
inout:可以输入又可以输出
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾
在使用命令行写存储过程时，由于过程中的每一条sql都需要以‘;’结尾mysql会误以为是整个过程的结束，我们可以通过delimiter 重新定义一个分隔符
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
DELIMITER:
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
调用存储过程：
call procedure_name(参数)
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;
查看存储过程：
-- 查询某个存储过程的定义
show create procedure 存储过程名;
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
删除存储过程
DROP PROCEDURE [IF EXISTS] procedure_name ；
四、流程控制结构
4.1、分支结构
-
if函数
-- if函数实现简单的单双分支，可以放在表达式的任何位置 if(条件,值1,值2) -
if条件判断
实现多分支条件判断
语法:
-- id条件判断语句只能放在 begin end 语句中 if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; end if; -- 例子 create procedure procedure1() begin declare height int default 175; declare description varchar(50); if height >= 180 then set description = '身材高挑'; elseif height >= 170 and height < 180 then set description = '标准身材'; else set description = '一般身材'; end if; select description ; end -
case结构实现多分支条件判断
case结构可以放在任何位置，如果放在begin end 外面，作为表达式结合着其他语句使用如果放在begin end 里面，一般作为独立的语句使用
方式一：
case 表达式或字段 when 值1 then 语句1; when 值2 then 语句2； .. else 语句n; end [case]; -- 例子 create procedure procedure2(day int) begin declare result varchar(20); case day when 1 then set result = '星期一'; when 2 then set result = '星期二'; when 3 then set result = '星期三'; when 4 then set result = '星期四'; when 5 then set result = '星期五'; when 6 then set result = '星期六'; when 7 then set result = '星期七'; else set result = '输入错误'; end case; select concat('今天是 :',result) as dayofweek ; end方式二：
case when 条件1 then 语句1; when 条件2 then 语句2； .. else 语句n; end [case]; -- 例子 create procedure procedure3(month int) begin declare result varchar(20); 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 = '第四季度'; end case; select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ; end4.2、循环结构
-
while循环
while循环是满足条件就继续执行
语法： [名称:] while 循环条件 do 循环体 end while [名称]; -- 例子： -- 从0加到n create procedure procedure4(n int) begin declare total int default 0; declare num int default 1; name1:while num<=n do set total = total + num; set num = num + 1; end while name1; select total; end -
repeat循环
repeat是有条件的循环控制语句, 当满足条件的时候退出循环 。
【名称:】repeat 循环体 until 结束条件 end repeat 【名称】; -- 例子从1加到n create procedure procedure5(n int) begin declare total int default 0; repeat set total = total + n; set n = n - 1; until n=0 end repeat; select total ; end -- 知道until满足就退出循环 -
loop循环
loop 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：
-
【名称：】loop
循环体
end loop 【名称】;
-- 例子从0加到n
CREATE PROCEDURE procedure6(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
/* 注意这里使用了 leave来跳出循环，类似于break,
iterate可以用来结束本次循环开启下一次循环，类似于 continue,且使用了leave或者iterate都必须指定循 环的名称
*/
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END
循环控制语句
- leave：类似于break，用于跳出所在的循环
- iterate：类似于continue，用于结束本次循环，继续下一次
循环语句的总结:
- 三种循环只能放在begin end中，都能实现循环结构
- 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或 iterate）则必须添加名称
- loop 一般用于实现简单的死循环，while 先判断后执行repeat 先执行后判断，无条件至少执行一次。
五、存储函数
存储函数相对于存储过程最大的一个不同点就是，存储函数必须有返回值,另外函数的参数类型只能是in，不能是inout或者out.
CREATE FUNCTION function_name(参数模式 参数名 参数类型) --参数模式是in可以省略
-- 声明返回类型，这历史returns,不是return
RETURNS type
BEGIN
sql语句
return 返回值;
END;
-- 例子，将输入的数乘2并返回
create FUNCTION fun1(num int)
returns int
BEGIN
DECLARE cnum int default 0;
set cnum = num*2;
return cnum;
end
-- 使用函数
select fun1(5);
六、触发器
6.1、简介
触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容
| 触发器类型 | NEW 和 OLD的使用 |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
6.2创建触发器
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;
before/after ：触发器触发的时间
insert/update/delete：触发器触发的条件
根据出发时间和触发条件的组合(笛卡尔积)可以看出一种有6中触发器。
for each row：行级触发器，针对每一行的改动执行一次
在 InnoDB 数据库中，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。
注意:触发器使用的表需要支持事务（就是InnoDB）,创建触发器的表，不能再sql语句（就是BEGIN...END）中再次操作该张表。
-- 在emp表中插入数据后执行触发器
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end
6.3、删除触发器
-- 如果不指定schema_name，就默认当前的数据库
drop trigger [schema_name.]trigger_name
6.4、查看触发器
-- 可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers;
