Mysql进阶

视图

创建视图

create view 视图名
as 查询语句;

1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性
3、视图仅仅保存sql逻辑，不占用物理空间

更新视图

方式一：
create or replace view 视图名
as 查询语句;

方式二：
alter view 视图名
as 查询语句;

删除视图

1	drop view 视图名1,视图名2,...;

查看视图

1 2	desc 视图名; show create view 视图名;

视图数据增删改查

查看数据
select * from 视图名;

插入数据
insert into 视图名() values();

修改视图数据
update 视图名 set 字段名=新值

删除视图数据
delete from 视图名;

变量

系统变量

全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

查看所有全局变量
show global variables;
查看满足条件的部分系统变量
show global variables like '%char%';
查看指定的系统变量的值
select @@global.autocommit;
为某个系统变量赋值
set @@global.autocommit=0;
set global autocommit=0;

会话变量

作用域：针对于当前会话（连接）有效

查看所有会话变量
show session variables;
查看满足条件的部分会话变量
show session variables like '%char%';
查看指定的会话变量的值
select @@autocommit;
select @@session.tx_isolation;
为某个会话变量赋值
set @@session.tx_isolation='read-uncommitted';
set session tx_isolation='read-committed';

自定义变量

用户变量

声明并初始化
set @变量名=值;
set @变量名:=值;
select @变量名:=值;

赋值
方式一：一般用于赋简单的值
set 变量名=值;
set 变量名:=值;
select 变量名:=值;
方式二：一般用于赋表 中的字段值
select 字段名或表达式 into 变量
from 表;

使用
select @变量名;

局部变量

声明
declare 变量名 类型 【default 值】;

赋值
方式一：一般用于赋简单的值
set 变量名=值;
set 变量名:=值;
select 变量名:=值;
方式二：一般用于赋表 中的字段值
select 字段名或表达式 into 变量
from 表;

使用
select 变量名;

流程控制

分支

1、if函数
if(条件,值1,值2);

2、case语句
情况一：类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句，需要加分号) 
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

情况二：类似于多重if
case 
when 条件1 then 结果1或语句1(如果是语句，需要加分号) 
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

3、if语句
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

循环

while
【标签:】while 循环条件  do
	循环体;
end while 【标签】;

leave相当于break
iterate相当于continue

loop
【标签:】loop
	循环体;
end loop 【标签】;
可以模拟简单的死循环

repeat
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;

存储过程

含义：一组经过预先编译的sql语句的集合

优点：

提高了sql语句的重用性，减少了开发程序员的压力
提高了效率
减少了传输次数

创建存储过程

1、需要设置新的结束标记
delimiter 新的结束标记;
示例：
delimiter $;

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;
END $

2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

3、参数前面的符号的意思
in:该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出

调用存储过程

1	call 存储过程名(实参列表);

函数

创建函数

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END;

调用函数

1	SELECT 函数名（实参列表）;

注意：函数与存储过程的区别：

返回值：函数只能是一个，存储过程可以是0个或多个

应用场景：函数一般用于查询结果为一个值并返回，存储过程一般用于更新

游标

注意：游标只能用在存储过程和函数中

声明游标

1	declare 游标名 cursor for 查询语句;

打开游标

open 游标名;

使用游标

1	fetch 游标名 into 变量名

关闭游标

close 游标名

游标示例

delimiter $
create procedure processorders()
begin
	declare done boolean default 0;
    declare o int;
    declare a int;
	declare ordernumbers cursor 
    for
    select id from people;
    #无返回数据时触发该语句
    declare continue handler for sqlstate '02000' set done=1;
    #第二种方式，此外continue和exit各有不同作用
    #declare continue/exit handler for NOT FOUND set done=1;
    
    create table if not exists ordertotals
    (order_num int);
    
    #打开游标
    open ordernumbers;
    
    repeat
    	#使用游标
    	fetch ordernumbers into o;
    	insert into ordertotals values(o);
    until done end repeat;
    #关闭游标
    close ordernumbers;
end $

触发器

触发器是响应delete、insert、update语句时自动执行的一条或一组语句。

注意：只有表支持触发器

创建触发器

set @temp=0;
create trigger newPeople after insert on people
for each row select 'People added' into @temp;

每个表最多支持6个触发器(每条insert、delete、update的前后)，每个表每个事件每次只允许一个触发器

删除触发器

1	drop trigger 触发器名

触发器示例

insert
set @temp=0;
create trigger newPeople after insert on people
for each row select new.id=@temp;
在insert触发器内可以引用一个名为new的虚拟表，访问被插入的行
在before insert触发器中，new的值可以被更新
对于auto_increment列，new在执行前包含0，在insert后包含新的自动生成值

delete
create trigger deletepeople before delete on people
for each row
begin
	insert into archive_peoples(id,name,age) values(old.id,old.name,old.age)
end
在delete触发器代码内部，可以引用一个名为old的虚拟表，访问被删除的行
old的值都是只读，不能更新

update
create trigger updatepeople before update on people
for each row set new.name=upper(new.name)
update触发器代码中可以引用名为old的虚拟表访问更新前的数据，引用名为new虚拟表访问更新后的数据
在before update触发器中，new中的值可能被更新
old的值都是只读的，不能更新