CodeWithMosh--mysql 学习笔记（3）

存储过程procedure

创建 – delimiter – call

delimiter $$ 
create procedure get_clients() -- () 用于添加参数为存储过程传递值
begin
	select * from clients;
end $$
delimiter ;

call get_clients  -- 使用 call 调用存储过程

也可用 'c#'，Java，python 等应用代码调用存储过程

delimiter $$
create procedure get_invoices_with_balance()
begin 
	select *
	from invoices
	where (invoice_total - payment_total) > 0;
end $$ 
delimiter ;

删除 – drop

drop procedure if exists get_clients -- if exists 防止重复删除

delimiter $$ 
create procedure get_clients ()
begin
	select *
	from clients
	where (invoice_total - payment_total)>0;
end $$
delimiter ;

添加参数

drop procedure if exists get_clients_by_state;

delimiter $$ 
create procedure get_clients_by_state(state char(2))
begin
	select *
	from clients c
	where c.state =state;
end $$
delimiter ;

-- call get_clients_by_state('CA')

drop procedure if exists get_invoices_by_client;
delimiter $$
create procedure get_invoices_by_client(client_id int) -- 类型看设计表
begin 
	select *
	from invoices i
	where i.client_id = client_id;
end $$
delimiter ;

默认值参数

drop procedure if exists get_clients_by_clients;

delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
	if state is null then 
		set state = 'CA'; 
	end if;
	
	select *
	from clients c
	where c.state = state ;

end
$$
delimiter ;

call get_clients_by_clients(null)

drop procedure if exists get_clients_by_clients;

delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
	if state is null then -- 未传参 显示全部
		select *
		from clients ;
	else
		select *
		from clients c
		where c.state = state ;
	end if;
end
$$
delimiter ;

call get_clients_by_clients(null)

drop procedure if exists get_clients_by_clients;

delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
	
	select *
	from clients c
	-- 若state 为空则 c.state = c.state 永久满足
	where c.state = ifnull(state,c.state) ; 

end
$$
delimiter ;

call get_clients_by_clients('CA')

drop procedure if exists get_payments;
delimiter $$
create procedure get_payments
				(client_id int,payment_method_id tinyint)
begin 
	select *
	from payments p
	where 
		p.client_id = ifnull(client_id,p.client_id) and 
		p.payment_method = ifnull(payment_method_id,p.payment_method);
end
$$
delimiter ;

call get_payments(null,2)

参数验证 – decimal、 singal sqlstate

drop procedure if exists make_payment;
delimiter $$
create procedure make_payment(
	invoice_id int,
	-- decimal 小数类型 9位数，其中2位小数
	payment_amount decimal(9,2),
	payment_date date
)
begin
	if payment_amount < = 0 then
		signal sqlstate '22003' -- signal 抛出异常
			-- 给开发设置错误信息
			set message_text = 'invalid payment amount';
	end if;
	update invoices i
	set
		i.payment_total = payment_amount,
		i.payment_date = payment_date
	where i.invoice_id = invoice_id;
end
$$
delimiter ;

call make_payment(2,-100,'2019-01-01');
	

输出参数 – 少用

drop procedure if exists get_uppaid_invoices_for_client;
delimiter $$
create procedure get_uppaid_invoices_for_client(
	client_id int,
	-- out 修正为输出参数
	out invoices_count int,	out invoices_tatal decimal(9,2)
)
begin
	select count(*),sum(invoice_total)
	into invoices_count,invoices_tatal
	from invoices i
	where i.client_id = client_id 
		and payment_total = 0;
end
$$
delimiter ;

变量

用户或会话 变量 @ – 用户断线时，变量清空

通常我们会在调用输出参数的存储过程时使用这些变量
set @invoices_count = 0

本地变量 declare decimal — 执行计算任务

drop procedure if exists get_risk_factor;
delimiter $$
create procedure get_risk_factor()
begin
	-- declare 声明本地变量
	declare risk_factor decimal(9,2) default 0;
	declare invoices_total decimal(9,2);
	declare invoices_count int;
	
	select count(*),sum(invoice_total)
	into invoices_count,invoices_total
	from invoices;
	
	set risk_factor = invoices_total / invoices_count * 5;
	select invoices_total,invoices_count,risk_factor;
end
$$
delimiter ;
call get_risk_factor();

函数 – 单一值

drop function if exists get_risk_factor_for_client;
delimiter $$
-- 函数 还能返回 单一值
create function get_risk_factor_for_client(client_id int)
returns integer  -- 确定返回值类型为 integer
-- 设置函数属性
-- deterministic -- 确定性 
reads sql data -- 存在选择语句 读取数据
-- modifies sql data -- 修改数据
begin 
	declare risk_factor decimal(9,2) default 0;
	declare invoices_total decimal(9,2);
	declare invoices_count int;
	
	select count(*),sum(invoice_total)
	into invoices_count,invoices_total
	from invoices i
	where i.client_id = client_id;
	
	set risk_factor = invoices_total / invoices_count * 5;
		-- 0 / null = null
	return ifnull(risk_factor,0);
end$$
delimiter ;

select client_id,
	`name`,
	-- 0 / null = null
	get_risk_factor_for_client(client_id) as risk
from clients

触发器 – 保证数据一致性

创建

drop trigger if exists payments_after_insert;
delimiter $$

create trigger payments_after_insert   -- 触发器在 payments 插入后 触发
	-- before delete/update on payments
	after insert on payments
	for each row -- 触发器会作用于每个受影响的行
begin
	update invoices
		-- new 返回刚插入的行 ；old 返回刚删除或更新的行
	set payment_total = payment_total + new.amount,
		payment_date = new.date
	where invoice_id = new.invoice_id;
end

$$
delimiter ;

insert into payments 
values(default,5,2,'2019-10-10',10,1)

drop trigger if exists payments_after_delete;
delimiter $$
create trigger payments_after_delete
	after delete on payments
	for each row
begin
	update invoices
	set payment_total = payment_total - old.amount
	where invoice_id = old.invoice_id;
end
$$
delimiter ;

delete from payments
where payment_id = 9

查看 – 命名习惯

show triggers like 'payments%'  

使用触发器进行审计

-- 创建一个审计表
drop table if exists payments_audit;
create table payments_audit
(
	client_id int not null,
	date date not null,
	amount decimal(9,2),
	action_type varchar(50) not null,
	action_date datetime not null
)

drop trigger if exists payments_after_insert;
delimiter $$

create trigger payments_after_insert   -- 触发器在 payments 插入后 触发
	-- before delete/update on payments
	after insert on payments
	for each row -- 触发器会作用于每个受影响的行
begin
	update invoices
		-- new 返回刚插入的行 ；old 返回刚删除或更新的行
	set payment_total = payment_total + new.amount,
		payment_date = new.date
	where invoice_id = new.invoice_id;
	
	-- 往审计表里添加记录
	insert into payments_audit
	values (new.client_id,new.date,new.amount,'insert',now());
end

$$
delimiter ;

insert into payments 
values(default,5,2,'2019-10-10',10,1)

事件 – MySQL事件调度器 – 自动启动

创建

-- 查看事件调度器
show variables like 'event%';
-- 设置调度器为开 / 关 on / off
set global event_scheduler = on ;

删除drop，更改 alter

drop event if exists yearly_delete_stale_audit_rows;
delimiter $$
-- alter event ...
create event yearly_delete_stale_audit_rows
on schedule 
-- 	at '2022-03-03' -- 单次定时操作
	-- every 循环工作
	every 1 day starts '2019-01-01' ends '2029-02-01'
do begin
	delete from payments_audit;
	-- where action_date < NOW() - interval 1 year;
end
$$
delimiter ;

-- 查看事件
show events like 'year%'
-- 修改 
alter event yearly_delete_stale_audit_rows disable ; -- enable

事务 – 多个操作保持一致

创建

start transaction; -- 创建事务
insert into orders(customer_id,order_date,status)
values(1,'2019-01-01',1);

insert into order_items
values(last_insert_id(),1,1,1)

commit; -- 提交事务事务 -- 把所有更改写入到数据库
-- rollback -- 回滚 -- 设置条件 取消处理

查看 自动提交引擎 autocommit 状态， 默认开启 on

show variables like 'autocommit'

并发和锁定 — 难点

常见并发问题

丢失更新

脏读

不可重复读

幻读 – 突然出现数据

事务隔离级别-- 默认为 repeatable read

设置

  		SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL isolation-level
  		其中的<isolation-level>可以是：
  	–	READ UNCOMMITTED
  	–	READ COMMITTED
  	–	REPEATABLE READ
  	–	SERIALIZABLE

first connection

-- 只会对下一个事务起作用
set transaction isolation level read uncommitted;  -- 存在脏读问题
-- mysql 执行的每一个语句 都会在事务中打包 并自动提交
select points from customers where customer_id = 1;

set transaction isolation level read committed; -- 克服脏读 但存在同一事件两次读 数据不一致
-- mysql 执行的每一个语句 都会在事务中打包 并自动提交
select points from customers where customer_id = 1;

set transaction isolation level repeatable read; -- 克服数据读取不一致，但存在幻读
start transaction;
select * from customers where state = 'VA';
commit;

set transaction isolation level serializable; -- 克服幻读
start transaction;
select * from customers where state = 'VA';
commit;

 在逐行运行到select时去运行第二连接的事务但不要提交，由于是序列级别，
 所以第一连接事务的select 会等待第二连接事务的提交 commit 

second connection

start transaction ;
update customers set points = 20 where customer_id = 1;
-- commit ;
rollback ;

start transaction ;
update customers set state = 'VA' where customer_id = 1;
commit ;

死锁 Deadlock 错误码：1213

如果事务以相反的顺序更新记录，很可能会发生死锁

 -- first connection
start transaction ;
update orders set status = 2 where order_id = 1;
update customers  set state = 'cc' where customer_id = 1;
commit;

-- second connection
start transaction ;
update customers  set state = 'cc' where customer_id = 1;
update orders set status = 2 where order_id = 1;
commit;

传送门

SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
SQL存储过程有什么用？
MySql中delimiter的作用是什么?
Navicat Premium 查询sql文件保存位置在哪里？
SQLSTATE values and common error codes
从SQL语言的分类谈COMMIT和ROLLBACK的用法
关于mysql的参数autocommit
SQL-数据库脏读、不可重复读、幻象读的解释
sql server 并发_并发问题– SQL Server中的理论和实验
MySQL查看和修改事务隔离级别
3分钟搞懂MySQL事务隔离级别及SET TRANSACTION影响事务