MySQL中的一些方法

触发器

触发器，字面意思达到某个条件后自动触发
在MySQL中对触发器的说明是:针对表继续进行增删改操作可以自动触发
    主要有六种情况:分别是增、删、改操作的前后、
create trigger 触发器名称 before/after insert/update/delete on 表名 for each row 
begin
    sql语句
end

1.触发器的命名规律  # tri(表名是触发器)_什么时候触发_什么操作_表名
    tri_before_insert_t1  
    tri_after_delete_t2
    tri_after_update_t2
2.临时修改SQL语句的结束符
    delimiter $$  # 修改结束符为$$
    
    
触发器的实际应用
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;

事物

1.事物的四大特性(ACID)(重点)
    A:原子性
        事务中的各项操作是一个不可分割的整体，要么同时成功要么同时失败
    C:一致性
        使数据库从一个一致性状态变为另一个一致性状态
    I:隔离性
        多个事物之间彼此互不干扰
    D:持久性
        也可以称之为永久性，一个事物一道提交，那么他对数据库中数据的改变就应该是永久的
        
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;

"""
事务相关关键字
	start transaction;  开启事物操作
	rollback            回滚到上一个状态
	commit              将数据真正刷新到硬盘
	savepoint           保存事物节点，就是保存事物当前状态
"""

2.四种隔离级别(重点)
    在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
    InnoDB支持所有隔离级别
        set transaction isolation level 级别
    1.read uncommitted(未提交读)
        事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
    2.read committed(提交读)
        大多数数据库系统默认的隔离级别
      一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
    3.repeatable read(可重复读)		# MySQL默认隔离级别
        能够解决"脏读"问题，但是无法解决"幻读"
      所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
    4.serializable(可串行读)
        强制事务串行执行，很少使用该级别
    
    
3.NVCC(了解即可)
	MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
	一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

存储过程

可以看成是python中自定义的函数

# 无参函数
delimiter $$
create procedure p1() 
begin
	select * from cmd;
end $$
delimiter ;

# 调用
call p1()


# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from cmd where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

"""
查看存储过程具体信息
	show create procedure pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""

# 大前提:存储过程在哪个库下面创建的就只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

流程控制

# 分支结构
declare i int default 0;
IF i = 1 THEN
	SELECT 1;
ELSEIF i = 2 THEN
	SELECT 2;
ELSE
	SELECT 7;
END IF;

# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
	SELECT num ;
	SET num = num + 1 ;
END WHILE ;

函数

可以看成是python中的内置函数

"ps:可以通过help 函数名    查看帮助信息!"
1.移除指定字符
Trim、LTrim、RTrim

2.大小写转换
Lower、Upper

3.获取左右起始指定个数字符
Left、Right

4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
		但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
		where Soundex(name)=Soundex('J.Lie')
"""

5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值