视图(VIEW)
- 是一种虚拟存在的表。视图中的数据并不在数据库中实际存在。视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要工作就落在创建这条SQL查询语句上。简单，安全，数据独立。
create [or replace] view 视图名称 [(列名列表)] as select 语句
- - 创建视图
create view stu_v as select id,name from student where id<=10;- - 查看视图
show create view stu_v;
select*from stu_v;
select*from stu_v where id<=3;- - 修改视图
create view stu_v as select id,name,no from student where id<=10;
alter view itcast.stu_v as select id,name from student where id<=10;- - 删除视图
drop view if exists itcast.stu_v;例题：
使用SQL语句CREATE VIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为0003的学生情况。
create view v_stu_c as select s.学号,姓名,课程编号 from student_info s.grate grade where s.学号=g.学号;
select*from v_stu_c where 学号=’0003’;视图的更新：视图中的行必须和基表中的行一一对应才可以进行插入和更新操作，如果视图包含下列中的一项都不可更新
聚合函数或窗口函数 distinct, group by,having,union或者union all
存储函数 (PROCEDURE)
- 存储函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
- 特点：封装、复用。 可以接收参数，也可以返回数据。减少网络交互，效率提升
- 变量，if,case,参数 (it/out/inout),while,repeat,loop,cursor,handler
创建. 调用. 查看. 删除
-- 创建存储过程
create procedure 存储过程名([参数列表])
begin
-- SQL封装语句；
end;
-- 调用存储过程
call 存储过程名([参数]);-- 创建存储过程，查询goods表中的记录数
create procedure p1()
begin
select count() from goods;
end;
-- 调用存储过程p1
call p1();
-- 查看存储过程
select*from information_schema.ROUTINES
where routine_schema='db_shop';
-- 查看存储过程的创建语句
show create procedure p1;
-- 查看建表语句
show create table goods;
-- 删除存储过程
drop procedure p1;变量
系统变量时MYSQL服务器提供的，不是用户自己定义的，系统变量分全局变量和会话变量
-- 用户定义变量
“@变量”使用就可以了。作用域为当前连接。
-- 用户定义变量 @变量名
-- 赋值 -- 如果不赋值，获取到的是空值。
set @myname='蔡徐坤';
set @myage='18';
set @myclass='五班',@myhobby='唱跳rap篮球';
-- 查询结果赋给一个变量 set @mynum=(select count() from goods); select count() into @mycount from goods; -- 使用 select @myname; select @myname,@myage,@myhobby;
-- 局部变量declare
声明 declare 变量名 变量类型 [default 默认值];
-- 创建存储过程p2，用于查询goods表中的记录数
create procedure p2()
begin
declare goods_count int; --声明int类型的变量
set goods_count=(select count(*) from goods);
select goods_count;
end;
-- 调用
call p2();-- if条件判断语法
IF 条件1 THEN .....
ELSEIF 条件2 THEN -- 可选 .....
ELSE -- 可选 .....
END IF;
例题：
-- 根据定义的分数score变量，判定当前分数对应的分数等级。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。
create procedure p3()
begin
declare score int default 58;
declare result varchar(10);
if score>=85 then
set result='优秀';
elseif score>=60 then
set result='及格';
else
set result='不及格';
end if;
select result;
end;
-- 调用p3存储过程
call p3();例题：
-- 根据（in)传入参数score,判定当前分数对应等级
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。
-- 传入参数，传出参数：
create procedure p4(in score int,out result varchar(10))
begin
if score>=85 then
set result='优秀';
elseif score>=60 then
set result='及格';
else
set result='不及格';
end if;
end;
-- 调用p4存储过程,接收返回值
call p4(58,@result);
select @result;
-- 传入参数80判断及格优秀
set @score=80;
call p4(@score,@result);
select @result;例题：
-- 将传入(inout)的200分制的分数，进行换算，换算成百分制，然后返回。
create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 200;
call p5(@score);
select @score;-- CASE
例题：
-- 根据传入月份，判定月份所属的季节（要求采用case结构)
-- 1-3:第一季度 4-6：第二季度 7-9：第三季度 10-12：第四季度
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(3);-- while循环，有条件的循环语句：
例题：
-- 计算从1累加到n的值，n为传入的参数值。
-- 定义i作为计数器，定义sum作为和
-- 当i>n时退出循环
create procedure p7(in n int)
begin
declare i int default 0;
declare sum int default 0;
while i<n do
set i=i+1;
set sum=sum+i;
end while;
select sum;
end;
DROP procedure p7;
call p7(10);create procedure p8(in n int)
begin
declare i int default 1;
declare sum int default 0;
repeat
set i=i+1;
set sum=sum+i;
until i>n
end while;
select sum;
end;存储函数 (FUNCTION)
- 存储函数是有返回值的存储过程，参数类型只能为in类型
- 存储函数可以被存储过程替代
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法：
create function 存储函数名称（[参数列表]） returns type[characteristic] begin --SQL语句 return...; end;
characteristic：使用下面其中一个
- deterministic:相同的输入参数总是产生相同的结果
- no sql:不包含sql语句
- reads sql data:包含读取数据的语句，但不包含写入数据的语句
案例：计算从1累加到n的值，n为传入的参数值。
-- 存储函数
create function fun1(n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total=total+n;
set n=n-1;
end while;
return total;
end;
DROP function fun1;触发器
- 触发器是与表有关的数据库对象，可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
有以下三种类型：insert(插入），update(更新），delete(删除）
- INSERT类触发器 NEW 表示将要或者已经新增的数据
- UPDATE类触发器 OLD表示修改之前的数据，NEW 表示将要或已经修改后的数据
- DELETE类触发器 OLD表示将要或者已经删除的数据
触发器 在insert/update/delete之前（before)或者之后（after)，触发并执行触发器中定义的sql语句集合。现在的触发器只支持行级触发，不支持语句触发。
- before:在表中数据发生改变前的状态
- after:在表中数据已经发生改变后的状态
- fpllows:新触发器在现有触发器之后激活
- precedes:新触发器在现有触发器之前激活
创建，查看，删除
-- 创建触发器语法
create trigger 触发器名称
before/after（出发时机） insert/update/delete（触发事件）
on 表名称 for each row fpllows/precedes（触发顺序） -- 行级触发器
begin
--sql语句（触发器操作的内容）
end;
-- 查看
show triggers;
-- 删除
-- 如果没有指定schema_name,默认为当前数据库。
drop trigger[schema_name.]trigger_name;案例：
选择studentsdb数据库，在向grade插入记录时，分数字段的值只能为空，或者取值0-100。
如果分数字段的值不满足要求，小于0则填入0，大于100则填入100。
触发事件 insert， 触发时机 before
use studentsdb;
create trigger t1
before insert on grade for each row
begin
if new.分数<0 then
set new.分数=0;
elseif new.分数>100 then
set new.分数=100;
end if;
end;
-- 触发验证
insert into grade(学号,课程编号,分数)
values('0009','0009',250);案例： 使用触发器实现检查约束，在修改grade表中的记录时，记录的分数字段值如果大于100或者小于0，则把分数填入原来的分数。
create trigger t2
before update on grade for each row
begin
if new.分数>100 or new.分数<0 then
set new.分数=old.分数;
end if;
end;
-- 触发验证
update grade
set 分数=250
where 学号='0009' and 课程编号='0009';案例：创建触发器t3,实现当删除student_info表中某个学生记录后，自动删除该学生的成绩信息
drop trigger[ if exists] 触发器名称， if exists可以避免因触发器不存在而导致删除触发器失败。
create trigger t3
after delete on student_info for each row
begin
delete from grade
where 学号=old.学号;
end;
-- 删除触发器
drop trigger 触发器名称;
-- 删除存储函数
drop function 存储函数名;案例：创建触发器t4,当删除某商品信息时，自动删除该商品的订单信息
use db_shop;
create trigger t4
after delete on goods for each row
begin
delete from orders
where goods_id=old.id;
end;
