目录
一:MySQL的视图
1.视图的介绍
概念:视图(view)是一个虚拟表,非真实存在其本质是根据SQL语句获取动态的数据集
作用:简化代码 数据安全
特点:视图本身不存在具体的数据,只存储数据的逻辑,这些数据存放在原来的表中
一旦表中的数据发生改变,显示在视图中的数据也会发生改变
2.创建视图
操作:
create or replace view view_emp
as
select ename,job from emp;
-- 查看表和视图
show full tables;
解释:创建或代替视图view_emp,view_emp是视图的名字,视图的内容是表emp里的ename和job两列内容。
emp表如下,表的创建和数据的准备就省略了,直接展示结果
3.修改视图
MySQL可以通过create or replace view语句或alter view 语句来修改视图
格式:alter view 视图名 as select 语句
操作:查询语句根据需要自己改就行了
alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a,emp b
where a.deptno = b.deptno;
4.更新视图
某些视图是可以更新的。也就是说,可以在update,delete或insert等语句中使用他们,以更新基表的内容,在视图中的行和基表中的行之间必须有一对一的关系。如果视图中包含下述结构中的任意一种,那么他就是不可更新的:
* 聚合函数 sum(),min(),max(),count()等
* distinct 去重
* group by 分组
* having
* union 或 union all
* 位于选择列表中的子查询
* join
* from 子句中的不可更新视图
* where 中的子查询,引用from 子查询中的表
* 仅引用文字值(在该情况下,没有要更新的基本表)
注意:视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,在更新数据时,如果没有全面考虑在视图中更新视图的限制,就可能会造成数据更新失败!
5.重命名视图
-- rename table 视图名 to 新视图名
rename table view1_emp to my_view1;
6.删除视图
-- drop view 视图名
drop view if exists view_student;
二:MySQL的存储过程
1.入门案例
格式:
delimiter 自定义结束符号
create procedure 储存名 ([in,out,inout]参数名 数据类型)
begin sql语句
end 自定义的结束符号
delimiter ;
创建存储过程:
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
调用存储过程:
call proc01();
2.变量定义
2.1局部变量
用户自定义,在begin/end中有效
语法:声明变量 declare var_name type [default 'aaa'];
举例:declare nickname varchar (32);
操作:
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa' -- 声明/定义变量
set var_name01 = 'zhangsan' -- 给变量赋值
select var_name01; -- 输出变量的值
end $$
delimiter ;
-- 调用存储过程
call proc02;
注意:给变量赋值除了set,还有另一种方式selct into,也可以为变量赋值
select ename into my_name from emp where empno = 1001;
解释:就是将ename这一列员工号为1001的员工名赋给了my_name
2.2用户变量
格式:用户自定义,当前会话(连接)有效。类比java的成员变量。
语法:@var_name
不需要提前声明,直接使用即可。
操作:
delimiter $$
create procedure proc03()
begin
set @var_name01 = 'beijing';
select @var_name01;
end $$
delimiter ;
call proc03;
select @var_name01; -- 在begin/and外也可以使用用户变量
2.3系统变量
全局变量---由系统提供,在整个数据库有效
语法:@@global.var_name
操作:
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size=40000;
set @@global.sort_buffer_size=40000;
会话变量---由系统提供,在当前会话(连接)有效
语法:@@session.var_name
操作:
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=50000;
3.参数传递
3.1参数传递--in
in表示传入的参数,可以传入数值或变量,即使传入变量,并不会更改变量的值,可以内部更改,仅仅作用在函数范围内。
举例说明:
-- 封装有参数的存储过程,传入员工编号,查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
3.2参数传递--out
out表示从存储过程内部传值给调用者
举例说明:
-- 封装有参数的存储过程,传入员工编号,返回员工名字
delimiter $$
create procedure proc04(in empno int,out out_name varchar(50))
begin
select ename into out_name from emp where emp.empnno = empno;
end $$
delimiter ;
call proc04(1001,@o_ename);
select @o_name;
对代码做简要解释:首先传入参数1001,则emp.empno=empno=1001,根据它ename会得到一个值(名字),然后这个值会赋给out_name,最后out_name的值会传出去,传给变量@o_name
3.3参数传递--inout
inout表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入变量的值也可以修改变量的值(即使函数执行完)
举例说明:
-- 传入一个数字,传出这个数字10倍的值
delimiter $$
create procedure proc05(inout num int)
begin
set num=num*10;
end $$
delimiter ;
set @inout_num = 2;
call proc05(@inout_num);
select @inout_num; -- 20
4.流程控制
4.1分支语句--if
if语句包含多个条件判断,根据结果为true/false执行语句,与编程语言中的if,elseif,else语法类似,其语法格式如下:
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2]...
[else statement_list_n]
end if
举例说明:
-- 输入学生的成绩,来判断成绩的级别
delimiter $$
create procedure proc_if(in score int)
begin
if score < 60
then
select '不及格';
elseif score >= 60 and score < 80
then
select '及格';
elseif score >= 80 and score < 90
then
select '良好';
elseif score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end if;
end $$
delimiter ;
call pro_if(88); -- 良好
4.2分支语句--case
case是另一个条件判断的语句,类似于编程语言中的switch语法
-- 语法一
case case_value
when when_value then statement_list
[when when_value then statement_list]...
[else statement_list]
end case
-- 语法二
case
when search_condition then statement_list
[when search_condition then statement_list]...
[else statement_list]
end case
用法和上面的if语句基本相同,这里就不再举例说明了。
4.3循环语句--介绍
概述:循环是一段在程序中只出现一次,但可能会连续运行多次的代码,循环中的代码会运行特定次数,或许是运行到特定条件成立时结束循环。
分类:*while *repeat *loop
循环控制:leave类似于break,跳出,结束当前的循环
iterate类似于continue,继续,结束本次循环,继续下一次循环
4.4循环语句--while循环
格式:标签可写可不写,根据自己需要
[标签:] while 循环条件 do
循环体;
end while [标签];
具体代码展示:
-- 创建测试表
create table user(
uid int primary_key,
username varchar(50),
password varchar(50)
);
-- 向表中添加指定条数的数据
delimiter $$
create procedure proc_while_leave(in insertCount int)
begin
declare i int default 1;
label:while i < = insertCount do
insert into user(uid,username,password) values(i,concat('user-',i),'123456');
if i = 5 then
leave label; -- 跳出循环
end if;
set i = i + 1;
end while label;
end $$
delimiter ;
call proc_while_leave(10);
插入数据后的表格如下:
4.5循环语句--repeat循环
格式:
[标签:] repeat
循环体;
until 条件表达式
end repeat [标签];
4.6循环语句--loop循环
格式:
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
说明一下:repeat循环和loop循环和while循环他们都差不多,就不在用例子说明了
4.7游标cursor的使用
游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用对结果集进行循环处理。光标的使用包括光标的声明,open,fetch和close.
举例说明:
-- 需求:输入一个部门名,查询该部门员工的编号,名字和薪资,将查询结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a,emp b
where a.deptno = b.deotno and a.dname = in_name;
-- 打开游标
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;
-- 关闭游标
close my_cursor;
end $$
delimiter ;
call proc_cursor('销售部');
注意上面的loop是死循环,会有一个异常,我们会在后面进行解决
4.8异常处理--handler句柄
MySQL存储过程也提供了对异常处理的功能:通过定义句柄handler来完成异常声明的实现。
直接上代码:
-- 需求:输入一个部门名,查询该部门员工的编号,名字和薪资,将查询结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a,emp b
where a.deptno = b.deotno and a.dname = in_name;
-- 定义句柄:定义异常的处理方式
/*
1.异常处理后程序该怎么执行
continue:继续执行剩余代码
exit:直接终止程序
undo:不支持
2.条件触发
条件码:1329
条件名:SQLWARNING
NOT FOUND
SQLEXCEPTION
3.异常触发之后执行什么代码
设置flag的值---》0
*/
declare continue handler for 1329 set flag = 0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
-- 判断flag,如果flag为1则执行,否则不执行
if flag = 1 then
select var_empno,var_ename,var_sal;
else leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter ;
call proc_cursor('销售部');