视图
创建视图
create or replace view 视图名 as sql语句
查看视图
show tables;
show create view 视图名;
删除视图
drop view if exists 视图名;
存储过程
创建存储过程
delimiter $ -- 修改结束符
create procedure 名称(args)
begin
sql语句;
end $
delimiter ;
调用存储过程
call 名称();
查看存储过程
show procedure status;
删除存储过程
drop procedure if exists 名称;
存储过程语法
变量声明[默认值]
declare 变量名 变量类型 [default value]
declare num int default 10
赋值
set 变量名 = 值 (可以四则运算) set num = num + 10
select count(*) into num from 表名 (into :将count(*) 赋值给 num)
判断if
delimiter $
create procedure if_test()
begin
declare s int default 100;
declare res varchar(40);
if s >= 90 then
set res = '优秀';
elseif s >= 80 and s < 90
set res = '不错';
else
set res = '一般';
end if;
set concat('你考的',res); -- 拼接输出
end$
delimiter ;
传递参数 in:输入参数 out:输出参数 inout:既可以是输入参数也可以是输出参数
create procedure procedure_name([in/out/inout] 参数名 参数类型)
begin
end
delimiter $
create procedure if_test(in s int)
begin
declare res varchar(40);
if s >= 90 then
set res = '优秀';
elseif s >= 80 and s < 90
set res = '不错';
else
set res = '一般';
end if;
set concat('你考的',res); -- 拼接输出
end$
delimiter ;
call if_test(100);
delimiter $
create procedure if_test(in s int, out res varchar(50))
begin
if s >= 90 then
set res = '优秀';
elseif s >= 80 and s < 90
set res = '不错';
else
set res = '一般';
end if;
set concat('你考的',res); -- 拼接输出
end$
delimiter ;
call if_test(100, @res); -- @变量名 声明会话变量
select @res; -- 查询会话变量
case命令 end case
case [case_value]
when [when_value]/表达式 then sql语句
when [when_value]/表达式 then sql语句
else sql语句
end case;
- 例1 当colume 与condition 条件相等时结果为result
case colume
when condition then 1
when 2 then 2
else 3
end case;
declare num int;
declare res int;
case
when num > 0 then
res = 1;
when num < 0 then
res = -1;
else
res = 0;
end case;
select
case 字段名
when 1 then 1
when 2 then 2
else 3
end
from 表名;
while 用法
while num <= n
sql语句;
end while;
declare total int default 0;
declare num int default 1;
while num <= 10
set total = total + num;
set mun = num + 1;
end while;
repeat 循环语句，满足条件退出循环
repeat
sql语句;
until 条件
end repeat;
declare total int default 0;
declare num int default 100;
repeat
set total = total + num;
set mun = num - 1;
until num = 0
end while;
[loop别名]loop 循环 end loop leave[loop别名]: 退出循环
loop
sql语句；
end loop;
declare total int default 0;
declare num int default 100;
loop_name: loop
set total = total + num;
set mun = num - 1;
if mun < 0 then
leave loop_name;
end if;
end loop;
curesor游标，光标：用来存储查询结果集的存储类型
声明游标
declare curesor_name curesor for sql语句;
open 开启游标
open curesor_name;
fetch 指针遍历赋值 游标
fetch curesor_name into 自定义变量名;
close 关闭游标
close curesor_name;
delimiter $
create procedure curesor_test()
begin
declare c_id int;
declare c_name varchar(50);
declare has_data int default 1;
declare curesor_name curesor for select id, name from user;
declare exit handler for not fount set has_data = 0; -- 声明退出条件，写在定义游标之后
open curesor_name;
loop_name:loop
if has_data = 0 then
leave loop_name;
end if;
fetch curesor_name into c_id, c_name;
select concat('c_id:', id, 'name:', c_name);
end loop;
close curesor_name;
end $
delimiter ;
存储函数
创建
create function fun(username varchar(50))
returns int
begin
declare num int;
select count(*) into num from t_user where username = username;
return num;
end;
select fun('11111');
触发器：在增删改之前或之后执行(insert触发器(new)；update触发器(old,new)；delete触发器(old))
创建触发器
create trigger trigger_name
before/after/ insert/update/delete
on 表名
[for each row] -- 行级触发器
begin
sql语句;
end;
- 例1：对t_user添加新增后日志：t_user表新增会自动执行 new.id 指t_user表中的id
delimiter $
create trigger insert_test
after insert
on t_user
for each row
begin
insert into t_userlog(id, option_id, option_des) values (null, new.id, concat('插入后', new.id,'内容', new.username) );
end $
delimiter ;
- 例2：对t_user添加更新前日志：更新t_user表会自动执行 old.id 指t_user表中的id
delimiter $
create trigger insert_test
after insert
on t_user
for each row
begin
insert into t_userlog(id, option_id, option_des) values (null, old.id, concat('修改前', old.id,'内容', old.username) );
end $
delimiter ;
查看触发器
show triggers \G;
删除触发器
drop trigger trigger_name
