一、视图
视图是一个虚拟表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。单表视图的数据变化会影响到基表
创建视图:
create view 视图名 as 查询语句
create view newresult
as
select
s.studentName as 姓名, sub.subjectName as 课程, r.examDate as 考试时间, r.studentResult as 成绩
from student s
inner join result r on s.studentNo=r.studentNo
inner join subject sub on r.subjectNo=sub.subjectNo;
select 课程,成绩 from newresult where 成绩>=80; ##视图的使用和表的使用一样
create view newstudent
as
select * from student where studentNo >25;修改视图:
alter view 视图名 as 查询语句
alter view newstudent
as
select * from subject;删除视图:
drop view 视图名;
drop view newstudent;
drop view newresult;二、存储过程
17.1 基础
储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
创建语法:
delimiter 标识符create procedure 储存过程名称(参数)beginsql语句集end标识符
调用语法:
call 储存过程名称(参数);
删除语法:
drop procedure 储存过程名称
注意：储存过程不支持修改,如果要修改，只能删除了重建
基础案例:
delimiter $$
create procedure querygradesub(in gn varchar(50))
begin
declare gid int default 0;
select gradeId into gid from grade where gradeName=gn;
select * from subject where gradeId=gid;
end
$$
call querygradesub('三年级');#调用存储过程17.2 定义参数
定义参数的语法:
(in 参数名1 数据类型,out 参数名2 数据类型,...,inout 参数名n 数据类型)
in 输入参数 （只接收用户数据传递）
delimiter !!
create procedure demo1(in d int)
begin
declare result varchar(50);
case
when d>=1 and d<=5 then set result="这是工作日";
when d=6 then set result='这是星期六';
when d=7 then set result='这是星期天';
else set result='数据错误';
end case;
select result;
end
!!
call demo1(7)out 输出参数 (不接收用户数据传递,但它会向用户传递数据)
delimiter !!
create procedure demo1(in d int,out result varchar(50))
begin
case
when d>=1 and d<=5 then set result="这是工作日";
when d=6 then set result='这是星期六';
when d=7 then set result='这是星期天';
else set result='数据错误';
end case;
end
!!
call demo1(7,@r); #@r直接就这样写上去，就相当于是引用数据类型，在存储过程中发生了变化 @r也会跟着发生变化
select @r;inout 输入输出参数 (既接收用户数据传递,又向用户传递数据)
delimiter **
create procedure demo2(inout sum int)
begin
declare i int default 1;
while i<=100 do
set sum=sum+i;
set i=i+1;
end while;
end
**
set @sum=50;
call demo2(@sum);
select @sum;17.3 定义局部变量
1、定义变量必须在begin end之间
2、定义变量要写在begin之下，其他代码之上
定义变量语法：
declare 变量名 数据类型 default 默认值;
declare i int; 类似于 int i;
declare i int default 1; 类似于 int i=1;
赋值:
set 变量名=值; 注意局部变量的使用不需要再写@ @变量名只是在存储过程之外时使用
select count(1) into 变量名 from 表名; *在存储过程中使用into进行赋值 , 在存储过程之外 select @c:=count(1) from 表名.
select gradeId into gid from grade where gradeName=gn;注意： 必须保证select只能返回一个结果
17.4 控制语句
1) 判断语句
if 条件 then
#SQL语句
elseif 条件 then
#SQL语句
elseif 条件 then
#SQL语句
....
else
#SQL语句
end if;案例:
delimiter &&
create procedure demo3()
begin
declare num int;
declare result varchar(50);
select count(1) into num from student;
if num<15 then set result="小班";
elseif num>=15 and num<=30 then set result="中班";
else set result="大班";
end if;
select result;
end
&&
call demo3();2) 循环结构
语法:
while 条件 do
#SQL语句
end while;#创建存储过程要求对bank表插入一千万条记录，插入记录之前需要先判断bank是否存在，不存在就创建
delimiter &&
create procedure createbankandinsert()
begin
declare i int default 1;
declare c int default 0;
create table if not exists bank(
id int primary key auto_increment not null,
name varchar(50),
bank decimal(11,2)
)engine=innodb charset=utf8;
start transaction;
while i<=1000000 do
insert into bank values(null,CONCAT("张三",i),i);
set c=c+ROW_COUNT();
set i=i+1;
end while;
if c=1000000 then commit;
else rollback;
end if;
end
&&
call createbankandinsert()