MySQL 基础知识-03

目录

一、MySQL的视图

二、MySQL的存储过程（MySQL5.0之后支持）

  （1）概念

  （2）存储过程的创建

  （3）变量的定义

  （4）存储过程的传参 in传入参数

  （5）存储过程的传参 out传出参数

  （6）存储过程的传参 inout

  （7）流程控制语句-判断if.....elseif.....else.....

  （8）流程控制语句-case(当然case也是MySQL的一种函数)

  （9）流程控制语句-循环概述

  （10）流程控制语句-循环while

  （11）流程控制语句-循环repeat

  （12）流程控制语句-循环loop

  （13）MySQL存储过程--游标cursor

  （14）MySQL存储过程--异常处理handler句柄

三、MySQL的存储函数

一、MySQL的视图

（1）介绍及数据准备

        视图（view）是一个虚拟表，非真实存在，比如有一张原始表，通过select语句进行查询得到一种中间表，我们可以为这个中间表建立视图，用户只需要使用视图名即可获得结果集，并可以将其当作表来使用

        数据库中只存放视图的定义，不保存视图中的数据，这些数据还是存放在原始表中，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据，因此，视图中的数据时依赖于原来表中的数据的，一旦原表的数据发生改变，则视图显示的数据也会发生改变

        使用视图可以简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用，在安全方面，如果一张表有很多数据，但某些数据很隐私不希望让所有人看见，这时可以使用视图为不同的用户设置不同的视图

                                                                                                         emp表

                                                                                                       dept表

（2）视图的创建

# 创建视图
create or replace view emp_view1 
as 
select ename,job from emp;
# 查看表和视图
show tables;   # 只返回表和视图的名称
show full tables;   # 返回表和视图的名称及类型

（3）修改视图

对视图的select语句进行修改，当然create or replace view语句也可以修改视图（替换）

alter view emp_view1
as
select empno,ename,job from emp;

（4）更新视图

通过对视图中的数据进行增删改（insert、update、delete）操作来对原表进行更新（一般情况下视图是作为查询数据的虚拟表而存在），出现以下情况时视图无法更新：

创建视图的select语句中存在聚合函数、distinct、group by、having、union（上下拼接去重）、union all（拼接不去重）、join、子查询、仅引用文字值（select "张三" a,"李四" b）这种情况下没有可更新的原表。

（5）删除视图

drop   view   [if   exists]    视图名；

删除视图只能删除视图的定义，不会删除数据

（6）重命名视图

rename   table   旧视图名   to   新视图名；

二、MySQL的存储过程（MySQL5.0之后支持）

（1）概念

        存储过程就是一组SQL语句集（恨多很多SQL语句），这一组SQL语句实现了某种功能例如注册功能、转账功能，由于这些功能经常会被使用，每使用一次都需要重新写一次SQL语句集，很麻烦，因此对这一组SQL语句进行封装（类似于java语言中的方法/函数），然后起一个名字，以后使用时直接调用名字即可。

        存储过程实质上就是数据库SQL语言层面的代码封装与重用，与视图有点类似，视图是对一条SQL语句（只是某个虚拟表）的封装，存储过程是对一推SQL语句（实现了某种特定的功能）的封装。

        存储过程的特点：1）由于存储过程类似于方法或函数，所以它具有输入输出参数，可以声明变量，有if/else,case,while等控制语句。2）模块化、封装、代码复用。3）速度快，只有首次执行需要经过编译和优化步骤，后续使用可以直接执行。

        这里使用的数据还是上文中的emp表

（2）存储过程的创建

         delimiter   ： 设置当前SQL语句的结尾符号

        由于存储过程中有很多很多SQL语句，每条SQL语句都以"；"结尾，所以为了将存储过程加以区分需要设置自定义的结束符号（一般为$$、\\、//）以表明存储过程的结束，创建存储过程之后，还需要恢复原来的结束符号"；"。

delimiter $$
create procedure pro1()
begin
	select empno,ename,job from emp;
end $$
delimiter ;

# 调用存储过程
call pro1();

（3）变量的定义

1）局部变量

declare   变量名   变量类型    [default   默认值]；    ： 声明变量

set  变量名  =  值；                                                    ： 给变量赋值

delimiter $$
create procedure pro2()
begin 
	declare var_name01 varchar(20) default 'aaa';
	set var_name01 = 'zhangsan';
	select var_name01;
end $$
delimiter ;

call pro2();
select var_name01;   # 无法访问

 注：在begin内定义的变量是局部变量，只在begin/end快内有效

在MySQL中还可以使用select......into为变量赋值

select    字段名   into   变量名    from   表名    where   条件    :   将查询的结果赋值给变量（此select语句返回的结果必须是单行单列）

delimiter $$
create procedure pro3()
begin 
	declare var_name02 varchar(20) default 'aaa';
	select ename into var_name02 from emp where empno = 7902;
	select var_name02;
end $$
delimiter ;

call pro3();

2）用户变量

用户变量不需要提前声明，直接使用即声明，用户变量在当前会话（连接conn-localhost）中有效

语法：@变量名

delimiter $$
create procedure pro4()
begin 
	set @var_name03 = 'lisi';
	select @var_name03;
end $$
delimiter ;

call pro4();

select @var_name03;   # 可以访问

3）系统变量

        系统变量又分为全局变量与会话变量，全局变量在MySQL启动时由服务器自动将它们初始化为默认值，这些默认值可以通过my.ini文件来修改；会话变量在每次建立一个新的连接时，由MySQL初始化，MySQL会将当前所有全局变量的值复制一份，来作为会话变量。因此，如果建立会话以后，没有手动更改系统变量的值，那么所有这些变量的值都是一样的。

        全局变量的修改会影响到整个服务器，而对会话变量的修改只影响当前会话（即当前的数据库连接），有些系统变量可以使用set语句来修改，有的不能修改是只读的。

全局变量：由系统提供，在整个数据库有效

语法：@@global.变量名

show  global  variables;         :  查看所有全局变量

select  @@global.变量名；    ： 查看某全局变量

# 修改全局变量的值

set  global  变量名  = 值；

set  @@global.变量名  = 值 ；

会话变量：由系统提供，当前会话（连接）有效

语法：@@session.变量名

show  session  variables;       :  查看所有会话变量

select  @@session.变量名；  ： 查看某会话变量

# 修改会话变量的值

set  session  变量名  = 值；

set  @@session.变量名  = 值 ；

（4）存储过程的传参 in传入参数

可以传入数值或者变量

delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
	select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('7902')

delimiter $$
create procedure dec_param02(in dname varchar(20),in sal double)
begin
	select * from emp a, dept b where a.deptno = b.deptno and b.dname = dname and a.sal > sal;
end $$
delimiter ;
call dec_param02('accounting',1300);
call dec_param02('sales',2000);
call dec_param02('research',2000);

（5）存储过程的传参 out传出参数

delimiter $$
create procedure dec_param03(in empno int, out out_ename varchar(20))
begin
	select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter ;
call dec_param03(7902,@o_ename);
select @o_ename;

delimiter $$
create procedure dec_param04(in empno int, out out_ename varchar(20), out out_sal double)
begin
	select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
delimiter ;
call dec_param04(7902,@o_ename,@o_sal);
select @o_ename,@o_sal;

（6）存储过程的传参 inout

in关键字传入的参数在存储过程内部是无法修改的

inout关键字传入的参数在存储过程内部可以修改，并且可以自动传出

# 传入一个数，传出这个数的10倍
delimiter $$
create procedure dec_param05(inout num int)
begin 
	set num = num * 10;
end $$
delimiter ;

set @inout_num = 4;
call dec_param05(@inout_num);
select @inout_num;   # 40

delimiter $$
create procedure dec_param06(inout io_ename varchar(20), inout io_sal double)
begin
	set io_sal = io_sal * 12;
	select deptno into io_ename from emp where ename = io_ename;
end $$
delimiter ;

set @ioio_sal = 3000;
set @ioio_ename = 'ford';
call dec_param06(@ioio_ename,@ioio_sal);
select @ioio_ename,@ioio_sal;   #20 36000

# 传入员工名，前面拼接部门号(20_ford)，传入薪资，求出年薪
delimiter $$
create procedure dec_param07(inout io_ename varchar(20), inout io_sal double)
begin
	set io_sal = io_sal * 12;
	select concat_ws('_',deptno,ename) into io_ename from emp where ename = io_ename;
end $$
delimiter ;

set @ioio_sal = 3000;
set @ioio_ename = 'ford';
call dec_param07(@ioio_ename,@ioio_sal);
select @ioio_ename,@ioio_sal;

（7）流程控制语句-判断if.....elseif.....else.....

# 输入员工名字，判断其工资情况
delimiter $$
create procedure if_control01(in i_ename varchar(20))
begin 
	declare result varchar(20);
	declare var_sal decimal(7,2);
	select sal into var_sal from emp where ename = i_ename;
	if var_sal <2000
		then set result = '试用薪资';
	elseif var_sal <4000
		then set result = '转正薪资';
	else 
		set result = '元老薪资';
	end if;
	select result;
end $$
delimiter ;
call if_control01('allen');  # '试用薪资'
call if_control01('ford');   # '转正薪资'
call if_control01('king');   # '元老薪资'

delimiter $$
create procedure if_control02(in i_score double)
begin
	declare result varchar(20);
	if i_score < 60
		then set result = '不及格';
	elseif i_score < 80
		then set result = '及格';
	elseif i_score < 90
		then set result = '良好';
	elseif i_score < 100
		then set result = '优秀';
	else 
		set result = '成绩错误';
	end if;
	select result;
end $$
delimiter ;

call if_control02(20);   # '不及格'
call if_control02(66);   # '及格'
call if_control02(88);   # '良好'
call if_control02(99);   # '优秀'
call if_control02(122);  # '成绩错误'

（8）流程控制语句-case(当然case也是MySQL的一种函数)

 输入支付类型编号，输出支付类型（1：微信支付 2：支付宝支付 3：银行卡支付 4： 其它支付）

delimiter $$
create procedure case_control01(in num int)
begin 
	declare pay_way varchar(20);
	case num
		when 1 then set pay_way = '微信支付';
		when 2 then set pay_way = '支付宝支付';
		when 3 then set pay_way = '银行卡支付';
		else set pay_way = '其它支付';
	end case;
	select pay_way;
end $$
delimiter ;

call case_control01(1);
call case_control01(2);
call case_control01(3);
call case_control01(4);
# 或
delimiter $$
create procedure case_control02(in num int)
begin 
	declare pay_way varchar(20);
	case 
		when num = 1 then set pay_way = '微信支付';
		when num = 2 then set pay_way = '支付宝支付';
		when num = 3 then set pay_way = '银行卡支付';
		else set pay_way = '其它支付';
	end case;
	select pay_way;
end $$
delimiter ;

call case_control02(1);
call case_control02(2);
call case_control02(3);
call case_control02(4);

（9）流程控制语句-循环概述

在MySQL中循环有while、repeat、loop三类。存在两个循环控制关键字：

leave类似于break，结束当前所有循环

iterate类似于continue，结束本次循环，继续下一次循环

（10）流程控制语句-循环while

 注意：这里的标签可以随意起名，可以省略，表示while循环的开始位置与结束位置,当使用leave或iterate关键字时必须指明标签。

while是先判断，然后在执行循环体

use mydb2;
create table user(
	uid int primary key,    # 编号
	username varchar(50),   # 名字
	password varchar(50)    # 密码
);

# 向user表中循环插入指定条数的数据
# 方式1  while
delimiter $$
create procedure while_control01(in num int)
begin
	declare i int default 1;
	while i<=num do
		insert into user values(i,concat_ws('-','user',i),123456);
		set i = i + 1;
	end while;
end $$
delimiter ;

call while_control01(10);
truncate table user;
# 方式2  while+leave
# 注意使用leave关键字while语句的标签不可省略
delimiter $$
create procedure while_control02(in num int)
begin
	declare i int default 1;
	label:while i do
		insert into user values(i,concat_ws('-','user',i),123456);
		set i = i + 1;
		if i > num
			then leave label;   # 跳出label标记的循环
		end if;
	end while label;
end $$
delimiter;

call while_control02(10);

# 传入参数10只插入前5条数据
#   while+leave
delimiter $$
create procedure while_control03(in num int)
begin
	declare i int default 1;
	label:while i < num do
		insert into user values(i,concat_ws('-','user',i),123456);
		if i = 5
			then leave label;   # 跳出label标记的循环
		end if;
		set i = i + 1;
	end while label;
end $$
delimiter;

call while_control03(10);

# 传入参数10,只插入1  2  3  4  6  7  9  10数据
#   while+iterate
delimiter $$
create procedure while_control04(in num int)
begin
	declare i int default 0;
	label:while i < num do
		set i = i + 1;
		if i = 5 or i = 8
			then iterate label;# 跳出本次循环执行下一次循环
		end if;
		insert into user values(i,concat_ws('-','user',i),123456);
	end while label;
end $$
delimiter;

call while_control04(10);
truncate table user;

（11）流程控制语句-循环repeat

 repeat是先执行循环体，然后在判断，until 条件表达式之后不加；

truncate table user;
delimiter $$
create procedure repeat_control01(in num int)
begin
	declare i int default 1;
	repeat
		insert into user values(i,concat_ws('-','user',i),123456);
		set i = i + 1;
		until i > num
	end repeat;
end $$
delimiter;

call repeat_control01(10)

（12）流程控制语句-循环loop

truncate table user;
delimiter $$
create procedure loop_control01(in num int)
begin
	declare i int default 0;
	lable:loop
		set i = i + 1;
		insert into user values(i,concat_ws('-','user',i),123456);
		if i >= num then 
			leave lable;
		end if;
	end loop lable;
end $$
delimiter;

call loop_control01(10)

（13）MySQL存储过程--游标cursor

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理，光标的使用包括declare声明、open打开、fetch取值、close关闭。

如果涉及到将查询出的结果进行一行一行处理，就可以使用游标，因此游标一般和循环一起使用

fetch ： 表示从游标结果集中取一行数据分别赋值给变量var_name.......

 实例：数据为emp表、dept表

delimiter $$
create procedure cursor_practice01(in in_dname varchar(50))
begin
	declare var_empno varchar(20);
	declare var_ename varchar(20);
	declare var_sal decimal(7,1);
	
	declare my_cursor cursor for 
		select 
			empno, ename, sal
		from 
			dept a, emp b
		where a.deptno = b.deptno and a.dname = in_dname;
	
	open my_cursor;
	
	label:loop
		fetch my_cursor into var_empno, var_ename, var_sal;  # 在这里报错终止程序
		select var_empno, var_ename, var_sal;
	end loop label;
	# xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
	close my_cursor;
end $$
delimiter ;

call cursor_practice01('accounting');

loop里没有终止条件，那么loop是死循环，但是在这儿好像并没有陷入死循环，那是因为：my_cursor里没有数据了，报错，自动终止程序，如果报错位置之后还有其它的语句（xxxxxx），那么这些语句将不会在执行。后续我们可以通过句柄来处理这个异常。

（14）MySQL存储过程--异常处理handler句柄

handler_action                              : 用于控制在异常处理完之后干什么

        continue 继续执行

        exit 程序终止

condition_value                             : 指明捕获什么异常

        mysql_error_code                 : 错误码例如上文所说的错误1329

        condition_name(异常名字)    :  sqlwarning（sql警告）  |  not found（没找到数据）  |          sqlexception（sql异常）

statement                                      ： 异常触发后采取的措施

注意：必须按照变量声明、游标声明、handler声明的顺序书写，否则出错

实例：

delimiter $$
create procedure handler_practice01(in in_dname varchar(50))
begin
	declare var_empno varchar(20);
	declare var_ename varchar(20);
	declare var_sal decimal(7,1);
	declare flag int default 1;   # 默认情况下游标中有数据
	
	declare my_cursor cursor for 
		select 
			empno, ename, sal
		from 
			dept a, emp b
		where a.deptno = b.deptno and a.dname = in_dname;
		
	declare continue handler for not found 
		set flag = 0;
	
	open my_cursor;
	
	label:loop
		fetch my_cursor into var_empno, var_ename, var_sal;  # flag 标记是否出现异常
		if flag = 1 then
			select var_empno, var_ename, var_sal;
		else 
			leave label;
		end if;
	end loop label;
	select flag;   # 可以执行了
	close my_cursor;
end $$
delimiter ;

call handler_practice01('accounting');

三、MySQL的存储函数

 returns type：指定返回值的类型

characteristic：指定存储函数的特性

routine_body : sql代码内容

# 存储函数和存储过程都相当于是一种函数，将其视为函数直接用即可
# 创建函数权限信任，在mysql中有的时候系统不允许用户自己创建函数的
set global log_bin_trust_function_creators = TRUE;
# 无参数的存储函数,需求：求emp表员工数
delimiter $$
create function preserve_fun01()
returns int
begin
	declare cnt int default 0;
	select count(*) into cnt from emp;
	return cnt;
end $$
delimiter ;
# 调用
select preserve_fun01();
# 在navicat中fx代表存储函数，px代表存储过程

# 带参数的存储函数
# 需求：传入一个员工的编号，返回员工的名字
delimiter $$
create function preserve_fun02(i_empno int)
returns varchar(20)
begin
	declare o_ename varchar(20);
	select ename into o_ename from emp where empno = i_empno;
	return o_ename;
end $$
delimiter ;
# 调用
select preserve_fun02(7499);

那存储过程与存储函数有什么不同呢？

存储过程传入参数，传出参数需要关键字in、out，如果不指定out则存储过程是不会返回任何值的，而存储函数没有in、out关键字，且必然会自动返回一个参数，必须指定return。