SQL数据库（视图、存储过程、变量）

一、视图

1. 视图时一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自自定义视图的查询中使 用的表，并且是在使用视图时动态生成的。
2. 视图只保存查询的sql逻辑，不保存查询的结果，所以我们在创建视图的时候，主要的工作就落在创建视图相 关的这条sql语句上。

语法

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

查询视图

查看创建视图的语句： show create view 视图名称
查看视图中的数据： select * from 视图名称；
修改视图：

方式一：create [or replace] view 视图名称[列名的列表] as select语句[with [cascaded|local]
check option]
方式二： alter view view 视图名称[列名的列表] as select语句[with [cascaded|local] check
option]

删除视图：

drop view [if exists] 视图名称;

-- 创建视图 查询部门编号为1 的部门下的所有员工
create or REPLACE view v_emp_1 AS SELECT * FROM emp where dept_id =1;
-- 查询视图
show create view v_emp_1;
-- 查看视图中的数据
select * from v_emp_1;
-- 查询视图中 managerid
select * from v_emp_1 where managerid =2;
-- 删除视图
drop view if exists v_emp_1;

-- 给视图是否可以执行insert操作呢？
INSERT into v_emp_1(`name`,age,job,salary,entrydate,managerid,dept_id)
VALUES('tom',22,'程序员',10000,now(),2,1);
INSERT into v_emp_1(`name`,age,job,salary,entrydate,managerid,dept_id)
VALUES('lucy',18,'程序员',12000,now(),2,2);

问：创建视图时，如果指定了条件，然后我们在插入 删除 修改数据时，是否必须满足条件才可以操作，否则不能
操作呢？

检查选项（了解）

当使用with check option子句创建视图时，mysql会通过视图检查正在更改的每个行，比如插入，更新，删
除，以使其符合视图的定义。
mysql允许基于另一个视图创建视图，他还会检查依赖视图中的规则以保持一致性。为了确定检查得到范
围，mysql提供了两个选项：cascaded|local 默认值为cascaded

1. cascaded级联

比如v2视图是基于v1视图创建的，如果在v2创建的时候制定了检查选项为cascaded，但是v1视图创建的时
候未指定检查选项，则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1；

create view v_emp_2 AS select * from v_emp_1 where managerid=2 WITH CASCADED CHECK
OPTION;
-- 创建一个基于student表的视图
create or replace VIEW v1 as select id,name from student where id <= 20;
-- 没有检查选项，所以插入id>20数据也会插入成功
insert into v1 VALUES(21,'jerry');
-- 基于v1 创建一个视图v2
create OR replace view v2 as select id ,name from v1 where id > 10 WITH CASCADED
CHECK OPTION;
insert into v1 VALUES(22,'ADMIN');
-- 基于v2 创建视图v3 可以插入的值的范围是10--20
create OR replace view v3 as select id ,name from v2 where id > 15 ;
insert into v1 VALUES(19,'aaaa');
insert into v1 VALUES(25,'bbbb');

2. local

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

-- 创建一个基于student表的视图
create or replace VIEW v1 as select id,name from student where id <= 20;
-- 没有检查选项，所以插入id>20数据也会插入成功
insert into v1 VALUES(21,'jerry');
-- 基于v1 创建一个视图v2
create OR replace view v2 as select id ,name from v1 where id > 10 WITH local CHECK
OPTION;
insert into v1 VALUES(22,'ADMIN');
-- 基于v2 创建视图v3 可以插入的值的范围是10--20
create OR replace view v3 as select id ,name from v2 where id > 15 ;
insert into v1 VALUES(19,'aaaa');
insert into v1 VALUES(25,'bbbb');

视图的更新

视图如果可更新，行必须和基础表之间存在一对一的关系，如果其中包含了函数或者关键字 ，则视图不可更
新

• 聚合函数（sum min max count）
• distinct
• group by
• having
• union或者union all

CREATE view stu_v_count AS select count(*) from student;
insert into stu_v_count VALUES(20);-- 报错 不允许

视图的作用

1. 简单
2. 安全
3. 数据独立

create view tb_stu_course_view
as select s.name student_name, s.no student_no,c.name course_name
FROM student s,student_course sc, course c
where s.id= sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

二、存储过程（掌握）

介绍

存储过程是实现经过编译并存储在数据库中的一段sql语句的集合。
特点：

• 封装，复用
• 可以接受参数 也可返回数据
• 减少网络的交互，提升效率

基本语法

create procedure 存储过程的名称([参数列表])
begin
-- sql语句
end;

调用

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

查看

show create procedure 存储过程名称;

删除

drop procedure [if exists] 存储过程名称;

存储过程

create PROCEDURE p1()
BEGIN
select count(*) from student;
END;
-- 调用
CALL p1();
-- 查看 某一个库中有的存储过程
select * from information_schema.ROUTINES WHERE ROUTINE_SCHEMA= 'studydb';
show create PROCEDURE p1;
-- 删除
drop PROCEDURE p1;

三、变量

在mysql中变量分为三种类型：系统变量 用户定义变量 局部变量

系统变量（了解）

系统变量是mysql服务器提供，不是用户定义的，属于服务器层面：分为全局变量（global）,会话变量（session）

会话：客户端与服务器端的一次完整的交互过程。

查看系统变量

-- 查看所有的系统变量
show session VARIABLES;
show global VARIABLES;

设置系统变量

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

如果没有指定[session|global] 默认是会话变量

-- 查看所有的系统变量
show session VARIABLES;
show global VARIABLES;
SHOW GLOBAL VARIABLES like 'auto%';
-- 设置系统变量
set SESSION autocommit =1;

用户定义变量

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

-- 赋值
set @var_name = expr[,@var_name =expr,...];
set @var_name :=expr[,@var_name =expr,...];
select @var_name := expr;
select 字段名 into @var_name from 表名;

2 使用

select @var_name;

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

-- 用户变量
-- 赋值
set @var_name = 'mysql';
set @myage := 10;
set @gender ='男' ,@hobby:='java';
select count(*) into @mycount from emp;
-- 使用
select @var_name,@myage,@gender,@hobby,@mycount;

局部变量

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

声明 declare 变量名 变量类型 [defult] 变量类型就是数据库字段的类型。

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

create PROCEDURE p2()
BEGIN
DECLARE emp_count int DEFAULT 0;
select count(*) into emp_count from emp;
select emp_count;
END;
call p2();

if

if用于做条件判断
具体语法：

if 条件 then
...条件成立执行的sql
elseif 条件2 then -- 可选
....
ELSE -- 可选
...
END IF;

• 案例： 根据定义的分数score变量，来判定当前成绩对应的等级。

CREATE PROCEDURE p3()
BEGIN
DECLARE score int DEFAULT 58;
DECLARE result VARCHAR(10);
IF score >= 80 THEN
set result = '优秀';
elseif score >= 60 THEN
set result = '及格';
ELSE
set result = '不及格';
end IF;
select result;
END;
call p3();

参数

1 参数类型：in 、 out、inout

• in 输入参数 也就是需要调用时传入值 默认
• out 输出参数， 也就是该参数可以作为返回值
• inout 该参数既可以作为输入参数，也可以作为输出参数
  2.用法

create procedure 存储过程名称(in/out/inout 参数名称 参数类型)
begin
end;

3.案例

create PROCEDURE p4(IN score int, out result VARCHAR(10))
BEGIN
IF score >= 80 THEN
set result = '优秀';
elseif score >= 60 THEN
set result = '及格';
ELSE
set result = '不及格';
end IF;
END;
call p4(82,@result);
select @result;

4. 将传入的200分制的分数，进行换算，换算成百分制。

create PROCEDURE p5(INOUT score double)
BEGIN
set score := score * 0.5;
END;
set @score = 198;
call p5(@score);
select @score;

case

case结构和作用，和我们基础部分所讲的流程控制函数很类似

case val
when when_val then statemnt_list
[when when_val then statemnt_list]
else statemnt_list
end case;

case
when search_condition then statement_list
[when search_condition then statement_list]
else statemnt_list
end case;

案例：
根据传入的月份，来判定月份所属的季度。
1- 3 第一季度
4-6 第二季度
7-9 第三季度
10-12 第四季度

create PROCEDURE p4(IN score int, out result VARCHAR(10))
BEGIN
IF score >= 80 THEN
set result = '优秀';
elseif score >= 60 THEN
set result = '及格';
ELSE
set result = '不及格';
end IF;
END;
call p4(82,@result);
select @result;
create PROCEDURE p5(INOUT score double)
BEGIN
set score := score * 0.5;
END;
set @score = 198;
call p5(@score);
select @score;
create PROCEDURE p6(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 p6(13);

while

while 条件 do
sql;
end while

案例： 计算1到N的累加值 n传入参数

create PROCEDURE p7(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 p7(100);

repeat

repeat
sql;
until 条件
end repeat;

create PROCEDURE p8(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 p8(100);

loop

loop实现简单循环
loop需要配一下两个语句使用：
leave：配合循环使用 退出循环
iterate： 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

create PROCEDURE p9(in n int)
BEGIN
declare sum int DEFAULT 0;
total:loop
if n <= 0 THEN
leave total;
end if;
set sum := sum + n;
set n := n-1;
end loop total;
select sum;
END;
CALL p9(100);

案例： 计算1到N的偶数的累加值 n传入参数

create PROCEDURE p10(in n int)
BEGIN
declare sum int DEFAULT 0;
total:loop
if n <= 0 THEN
leave total;
end if;
if n % 2 =1 THEN
set n := n-1;
ITERATE total;
end if;
set sum := sum + n;
set n := n-1;
end loop total;
select sum;
END;
CALL p10(100);