mysql存储过程笔记（易查阅）

left join 左右连表

union 上下连表 自动去重 + all 不会去重

select * from t1
union
select * from t0;

1.视图

临时表 要有100sql语句一直都要使用这个语句

反复存在 就可以取一个别名 根据别名做操作 :视图的名称

create view v1 as select * from t0;  #视图就叫做 v1
alter view v1 sql
drop view v1

视图中可以进行插入数据

视图：虚拟表 本质上不可以进行修改

不常用 ，如果写视图在开发中 别人在接管时看不懂

2.触发器

某个操作执行之后触发 相当于装饰器

当对某张表做：增删改时，可以使用触发器自定义关联行为

insert into db() values();
用户注册一个，日志再去存储所有的信息

插入前
create trigger adf defore insert on tb1 for each row
begin
insert into tb2() values()
----------------------
----------------------
end

create trigger adf after insert on tb1 for each row
begin
insert into tb2() values()
end

删除前
create trigger adf defore delete insert on tb1 for each row
begin
insert into tb2() values()
end

create trigger adf after after delete on tb1 for each row
begin
insert into tb2() values()
end

更新前
create trigger adf after before update on tb1 for each row
begin
insert into tb2() values()
end

create trigger adf after after update on tb1 for each row
begin
insert into tb2() values()
end

每插入一行数据，就会触发插入一条数据

new   代指新数据

old   代指老数据
create trigger adf defore insert on tb1 for each row
begin
insert into tb2(tname) values(new.sname)  #表示新插入的数据
end

insert into tb1(sname) values('bon'),('hello') 同时插入到 tb1 tb2中

在mysql 中改变终止符 delimiter //

数据库级别

3.函数

聚合函数 max, min ,count ,avg ,sum, case when if else

内置函数：

​ 执行函数：

blog表

id	title	ctime
1	a	2019-11-11 11:11
2	b	2019-10-11 11:11
3	c	2019-10-11 01:11

如果我们想让blog的文章按照月份进行分组，就需要用到 date_format函数将日期变成年月进行分组

select ctime,count(1) from blog group by ctime
select ctime,count(1) from blog group by date_format(ctime,"%Y-%m") --变成年月。到内存中进行分组

select char_length('asd'); --字符长度
select concat('hello','world'); --字段串拼接

还有很多内置函数可以查看官网

自定义函数：有返回值

DELIMITER / 定义结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。

delimiter //
create function f1(
	i1 int,
	i2 int
)
returns int  #返回值的类型 头部执行完毕
begin       #函数体
	declare num int default 0;    #声明变量 num = 0
	set num = i1 + i2;   
	return (num);
end //
弱类型语言
delimiter ;

[报错处理]（https://blog.csdn.net/qq_43058911/article/details/102677088）

4.存储过程

存储过程：保存在msyql上的一个别名，很多sql语句，

自己使用别名() 查询结果

视图与存储过程的区别

视图：是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在

存储过程： 别名()  很多sql语句操作，可以大大节省程序员写sql语句
优点：将代码和sql语句耦合 DBA维护存储过程

delimiter //
create procedure pq()  #创建一个存储过程
begin
    select * from student;
    insert into teacher(tname) values("小小")
end //
delimiter ;

call pq(); 执行完毕直接查询到想要的内容
视图是通过： 
create view v1 as select * from t0;
select v1 ; 进行调用

在Pymysql中通过 cursor.callproc(‘pq’) 取出数据

需求：我们怎样通过pymysql 来与 存储过程进行交互的存取数据呢？

可以通过存储过程名中的参数 (in , out ,inout)

in : 输入参数：表示调用者想过程中传入值（传入值可以是字面量或者变量）

delimiter //
create procedure pq(
	in n1 int,
	in n2 int
)   
begin
    select * from student where id > n1;
    insert into teacher(tname) values("小小")
end //
delimiter ;

call pq(12,2) #第二个没有使用但是必须要传递
cursor.callproc('pq',(12,2))  #在python中的查询

out ： 输出参数，表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）

不接受输入的参数

delimiter //
create procedure pq(
	in n1 int,
	out n2 int
) 
begin
    select * from student where id > n1;  --  不会执行 
    set n2 = 2
    select * from student where id > n2;  
end //
delimiter ;

set @n2 = 0;  -- 创建一个session级别的变量
call pq(12,@n2)
select @n2  -- 2

@n2 = 123123   --外部可以获取到
cursor.callproc('pq',(12,3))  --任意输入，反正里面不接受
r1 = cursor.fetchall()
print(r1)
cursor.excute('select @_pq_0,@_pq_1') --第二次查询
r2 = cursor.fetchall()
print(r2)

inout:输入输出参数，既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

delimiter //
create procedure pq(
	inout n1 int,
)   
begin
    select * from student where id > n1;  -- id > 3
    set n1 = 2
    select * from student where id > n1;  -- id > 2
end //
delimiter ;

set @n1 = 3; --相当于从外部传递的变量
call pq(@n1);
select @n1;  2

mysql的事务

事务（Transaction）是一个操作序列，不可分割的工作单位，以BEGIN TRANSACTION开始，以ROLLBACK/COMMIT结束

特性（ACID）：
原子性（Atomicity）：逻辑上是不可分割的操作单元，事务的所有操作要么全部提交成功，要么全部失败回滚（用回滚日志实现，反向执行日志中的操作）；
一致性（Consistency）：事务的执行必须使数据库保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的；
隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其它事务是不可见的（并发执行的事务之间不能相互影响）；
持久性（Durability）：一旦事务提交成功，对数据的修改是永久性的

mysql事务流程

delimiter //
create procedure pq(
	out status int
) 
begin
1.声明如果出现异常则执行{
	set status = 1 --可以监测到
	rollback;   --回滚
}
--开始事务
     -----A账户 -100元
     -----B账户 +100元
     commit;
结束
set status = 2; 
end //
delimiter ;

--如果拿到2就说明成功了

实例：
delimiter //
create procedure pq(
	out status int
) 
begin
declare exit handler for sqlexception
	begin
		-- error
		set status = 1;
		rollback;
	end;

	start transaction;
		delete from tb1;
	commit
     
     -- success
     set status = 2;
end //
delimiter ;

需求：

将A表中所有数据导入B表,B中的Num是A中的 id + num 的值
思路：循环A中的每一行
1.声明一个游标
2.获取A表中想要的数据，打开文件进行循环

A

id	num
1	3
2	4
3	2

B

id	num
1	4
2	6
3	5

delimiter //
create procedure pq()
begin
    declare row_id int;
    declare row_num int;
    declare done int default false;
    declare temp int;    --声明变量

    declare A_cursor cursor for select id,num from A;   --创建游标
    declare continue handler for not found set done = true;     --如果没有数据done 为真则退出 

    open A_cursor;  --打开数据
        xxoo:loop
            fetch A_cursor into row_id ,row_num;   --去游标里面拿一行数据复制给row_id,row_num
            if done then
                leave xxoo; --如果为true跳出循环
            end if;
            set temp = row_id + row_num;
            insert into B(num) values(temp);
        end loop xxoo;
    close A_cursor;

end //
delimiter ;

如果在行中需要操作，在用游标，一般不适用，因为效率低

动态执行sql（防止sql注入）

delimiter //
create procedure pq(
	in n1 varchar(100),
	in arg int
)
begin
	-- 预检测某个东西 sql语句的合法性
	-- sql = 格式化 n1 + arg
	-- 执行sql语句
	
	set @xo = arg;  --设置一个会话级别
	prepare xxx from 'select * from student where sid > ?'; --先检测，防止sql注入
	execute xxx using @xo;
	deallocate prepare prod;
end //
delimiter ;

call pq("select * from student where sid > ?",9)

5.索引

delimiter //
create procedure pq(
	in n1 varchar(100),
	in arg int
)
begin
	-- 预检测某个东西 sql语句的合法性
	-- sql = 格式化 n1 + arg
	-- 执行sql语句
	
	set @xo = arg;  --设置一个会话级别
	prepare xxx from 'select * from student where sid > ?'; --先检测，防止sql注入
	execute xxx using @xo;
	deallocate prepare prod;
end //
delimiter ;

call pq("select * from student where sid > ?",9)

5.索引

待补充!