文章目录

• • • 作用
    • 视图的更新
    • 存储过程
    • 参数
• create view 视图名 as select 创建视图
• 查看视图
• 查询视图
• create of replace view 视图名 as select 修改视图
• 删除视图
• 创建视图设置检查选项，修改、插入视图数据时会检查是否复合我们创建视图时的约束条件
• cascaded会传递依赖、local是局部的约束只适用于当前视图
• 创建存储过程
• 调用call
• 查看存储过程
• 删除存储过程
• 查看系统变量
• 设置系统变量，系统重启后设置的变量失效
• 用户自定义变量set @变量名 = '值'、set @变量名 := '值'
• 设置用户变量并赋值
• 局部变量，存储过程内使用
• if 条件1 then elseif 条件2 then else end if;
• 存储过程传参
• in、out、inout
• case
• while
• • repeat循环
• loop:leave(退出循环)、iterate(跳过当前循环，进入下一次循环)
• sum: 相当于goto
• 游标用来保存查询的结果集，一般使用游标对结果集进行循环处理cursor:open、fetch、close
• 先生命变量，在声明游标
• 触发器：在数据库插入、更新、删除之前或之后，触发并执行触发器中定义的sql语句集合
• 类型：old、new
• insert：new表示将要或者已经新增的数据
• update：old表示修改之前的数据，new表示将要或者已经修改后的数据
• delete: old表示将要或者已经删除的数据
• 查看触发器 show triggers;
• 删除触发器 drop trigger 触发器名称
• 创建触发器

作用

• 简单：简化数据
• 安全：数据库可以授权，但不能授权到特定的列上，通过视图让用户只能查看到指定的列
• 数据独立：视图可以屏蔽真实表结构所带来的变化

视图的更新

• 聚合函数或者窗口函数sum()、count()、max()等
• distinct
• group by
• having
• union或者union all

存储过程

存储过程可以减少数据在应用服务器和数据库之间的网络传输，提高对数据处理的效率，操作就是对sql语句进行封装

参数

• in：该参数作为输入，调用时传入值
• out：该参数作为输出，该参数可作为返回值
• inout：既可以作为输入，又可以做为输出

create view 视图名 as select 创建视图

create view view1 as select * from user;

查看视图

show create view view1;

查询视图

select * from view1;

create of replace view 视图名 as select 修改视图

create or replace view view1 as select id, name from user;

删除视图

drop view if exists view1;

创建视图设置检查选项，修改、插入视图数据时会检查是否复合我们创建视图时的约束条件

cascaded会传递依赖、local是局部的约束只适用于当前视图

create view view1 as select * from user with cascaded check option;
create view view1 as select * from user with local check option;

创建存储过程

create procedure p1()
begin
select count(*) from user;

end;

调用call

call p1();

查看存储过程

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = ‘user’;
show create procedure p1;

删除存储过程

drop procedure if exists p1;

查看系统变量

show global variables;
show session variables;

设置系统变量，系统重启后设置的变量失效

set session autocommit = 1;
select @@session.autocommit;

用户自定义变量set @变量名 = ‘值’、set @变量名 := ‘值’

set @test = ‘test’;

设置用户变量并赋值

select @test := “mysql”;

select @test;

drop procedure if exists test2;

局部变量，存储过程内使用

create procedure test2()
begin
declare temp int default 0;
# 赋值
#set temp := 9;
# 将查询结果赋值给变量
select count(*) into temp from user;
select temp;
end;

drop procedure if exists test3;

if 条件1 then elseif 条件2 then else end if;

create procedure test3()
begin
declare score int default 60;
declare result varchar(10);
if score = 100 then
set result := ‘A’;
elseif score >= 60 then
set result := ‘B’;
else
set result := ‘C’;
end if;
select result;
end;

drop procedure if exists test4;

存储过程传参

in、out、inout

create procedure test4(in score int, out result varchar(10))
begin
if score = 100 then
set result := ‘A’;
elseif score >= 60 then
set result := ‘B’;
else
set result := ‘C’;
end if;
#select result;
end;

call test4(78,@result);
select @result;

case

create procedure test5(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 := ‘error’;
end case;
select concat('输入月份: ‘, month, ’ 判定为:’, result);
end;

call test5(9);

drop procedure if exists test6;

while

create procedure test6(in i int)
begin
declare sum int default 0;
while i <= 100 do
set sum := sum + i;
set i := i+1;
end while;
select sum;
end;

call test6(0);
select @sum;

drop procedure if exists test7;

repeat循环

create procedure test7(in i int)
begin
declare sum int default 0;
repeat
set sum := sum + i;
set i := i + 1;
until
i > 100
end repeat;
select sum;
end;

call test7(0);

drop procedure if exists test8;

loop:leave(退出循环)、iterate(跳过当前循环，进入下一次循环)

sum: 相当于goto

create procedure test8(in i int)
begin
declare sum int default 0;
goto:loop

	if i <= 0 thenleave goto;end if;if i%2 = 1 thenset i := i-1;iterate goto;end if;set sum := sum + i;set i := i-1;end loop goto;select sum;

end;

#10之内偶数之和
call test8(10);

游标用来保存查询的结果集，一般使用游标对结果集进行循环处理cursor:open、fetch、close

#declare 游标名称 cursor for 查询语句 ：定义游标
#open 游标名称 ：打开游标
#fetch 游标名称 into 变量 ：获取游标记录
#close 游标名称 ：关闭游标
#条件处理程序
#declare handler_action handler for condition_value;

#handler_action: continue(继续执行当前程序)、exit(终止当前程序)
#condition_value：sqlstate(状态码)、sqlwarning(所有以01开头sqlstate代码的简写)、not found(所有以02开头的sqlstate代码的简写)、sqlexception(没有被sqlstate、not found捕获的代码的简写)

#使用条件处理程序解决游标跳出问题
drop procedure if exists test9;
create procedure test9(in n int)
begin

先生命变量，在声明游标

declare uname varchar(30);
declare uage int(3);
declare u_cursor cursor for select name,age from user where age <= n;
#02000是没数据
declare exit handler for sqlstate '02000' close u_cursor;drop table if exists user_pro;
create table if not exists user_pro(id int primary key  auto_increment,name varchar(30),age int(3)
);open u_cursor;
while true dofetch u_cursor into uname,uage;insert into user_pro values (null,uname,uage);
end while;close u_cursor;

end;

call test9(10);

#存储函数是有返回值的存储过程，存储函数的参数只能是in类型
/**
create function 函数名称(参数)
returns type [characteristic…]
begin
return …;
end;

characteristic: determinstic(相同的参数总是产生相同的结果)、no sql(不包含sql)、reads sql data(包含读取数据的语句，不包含写入数据的语句)
*/
drop function if exists sumTest;

create function sumTest(n int)
returns int deterministic
begin
declare sum int default 0;
declare i int default 0;

while i <= n doset sum = sum+i;set i = i+1;
end while;return sum;

end

select sumTest(100);

触发器：在数据库插入、更新、删除之前或之后，触发并执行触发器中定义的sql语句集合

类型：old、new

insert：new表示将要或者已经新增的数据

update：old表示修改之前的数据，new表示将要或者已经修改后的数据

delete: old表示将要或者已经删除的数据

查看触发器 show triggers;

show triggers;

删除触发器 drop trigger 触发器名称

创建触发器

drop table if exists user_logs;
create table if not exists user_logs(
id int(6) primary key auto_increment,
operation varchar(30) not null comment ‘操作’,
operation_time datetime not null comment ‘操作时间’,
operation_user varchar(30) not null comment ‘操作人’
)engine=innodb,default charset=utf8;

drop trigger if exists user_trigger;

create trigger user_trigger
after insert on user for each row
begin
insert into user_logs(id,operation,operation_time,operation_user) values
(null,‘insert’,now(),new.name);
end;

insert into user(id,name,age,email,type) values (null,‘wh’,3,‘3232’,‘student’);