MySQL数据库基础6

今日内容概要

• SQL注入问题
• 视图
• 触发器
• 事务
• 存储过程
• 函数
• 流程控制
• 索引相关概念
• 索引数据结构
• 慢查询优化

今日内容详细

SQL注入问题

用python编写简易的基于数据库的登陆注册

怪像1:输入用户名就可以登录成功
怪像2:不需要对的用户名和密码也可以登录成功

SQL注入:利用特殊符号的组合产生特殊的含义 从而避开正常的业务逻辑

select * from userinfo where name='jason' --ojowno' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdfo' and pwd=''

针对上述的SQL注入问题 核心在于手动拼接了关键数据 交给execute处理即可避免
sql = " select * from userinfo where name=%s and pwd=%s "
coursor.execute(sql,(username, password))

'''
补充说明
	也可以一次性传多个数据用executemany传列表加元祖即可
	executemany(sql, [(),(),(),()...])
'''

视图

视图就是通过查询得到一张虚拟表 然后保存下来 下次直接使用

create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;

'''
1.视图的表只能用来查询不能做其他增删改操作
2.视图尽量少用 会跟真正的表产生混淆 从而干扰操作者
'''

触发器

达到某个条件之后自动触发执行
在MySQL中更加详细的说明是触发器:针对表执行增 删 改操作后能够自动触发
主要有六种情况:增前 增后 删前 删后 改前 改后

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end

1.触发器命名有一定的规律
	tir_before_insert_t1
	tir_after_delete_t2
	tir_after_update_t2
2.临时修改SQL语句的结束符
	有些操作中需要使用分号

触发器实际应用
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')
);

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;

事务

事务的四大特性(ACID)
	A:原子性
		事务中的各项操作是不可分割的整体 要么同时成功要么同时失败
	C:一致性
		使数据库从一个一致性状态变到另一个一致性状态
	I:隔离性
		多个事务之间彼此不干扰
	D:持久性
		也称永久性 指一个事务一旦提交 它对数据库中数据的改变就应该是永久性的
        1. 原子性（Atomicity）：事务是一个原子操作，要么全部执行，要么全部不执行，不会出现部分执行的情况。

        2. 一致性（Consistency）：事务执行前后，数据必须保持一致性，即符合业务规则和完整性约束。

        3. 隔离性（Isolation）：多个事务同时执行时，每个事务都应该感觉不到其它事务的存在，即每个事务都是相互独立的。

        4. 持久性（Durability）：事务完成后，对数据库的修改是永久性的，即使出现了系统故障也不会丢失。

create table user(
	id int primary key autp_increment,
	name char(32),
	balance int
);

insert into user(name,balance) values('jason',1000),('kevin',1000),('tom',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='tom'; # 卖家收到90元

# 回滚到上一个状态
rollback;

# 开启事务之后 只要没有执行commit操作 数据其实都没有真正刷新到硬盘 而是暂时存放在内存中
commit;

'''
事务相关关键字
	start transaction  开启事务
	rollback  回滚
	commit   提交事务操作
	savepoint  保存一个事务点
'''

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

MVCC只能在read committed(提交读)和repeatable read(可重复读)两种隔离级别下工作 其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

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

例如
	刚插入第一条数据的时候 我们默认事务id为1 实际上是这样存储的
	username    create_version    delete_version
	jason          1
可以看到 我们在content列插入了kebe这条数据 在create_version这列存储了1 1就是这次插入操作的事务id
	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 ;

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

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

函数

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

'''可以通过help 函数名    查看帮助信息'''

# 移除指定字符
Trim
LTrim
RTrim

# 大小写转换
Lower
Upper

# 获取左右起始指定个数字符
Left
Right

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

# 日期格式: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');

where Date(sub_time)='2015-03-01';
where Year(sub_time)=2016 and Moth(sub_time)=07;

# 更多日期处理相关函数
	adddate 增加一个日期
	addtime 增加一个时间
	datediff 计算两个日期差值

流程控制

# 分支结构
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;

索引相关概念

索引就好比一本书的目录 它能够让你更快的找到自己想要的内容
让获取的数据更有目的性 从而提高数据库检索数据的性能

索引在MySQL中也叫作'键' 是存储引擎用于快速找到记录的一种数据结构
	primary key
	unique key
	index key
1.上述的三个key都可以加快数据查询
2.primary key和unique key除了可以加快查询外本身还自带限制条件而index key很单一 就是用来加快数据查询
3.外键不属于索引键的范围 是用来建立关系的 与加快查询无关

索引加快查询的本质
	select name from userinfo where phon=18888888888;  # 一页页的翻
	select name from userinfo where id=999;  # 按照目录确定页数找

索引可以加快数据查询 但是会降低增删速度
通常情况下我们频繁使用某些字段查询数据 为了提升查询的速度可以将该字段建立索引

聚焦索引(primary key)
	主键 主键索引
辅助索引(unique,index)
	除了主键以外的都是辅助索引
覆盖索引
	select name from user where name='jason';
非覆盖索引
	select age from user where name='jason';

索引数据结构

索引底层其实是树结构>>>:树是计算机底层的数据结构

树有很多类型
	二叉树 b树 b+树 b*树

二叉树
	二叉树里面还可以细分成很多领域 我们简单的了解即可
	二叉意味着每个节点最大只能分两个子节点
B树
	所有节点都可以存放完整的数据
B+\B*树
	只有叶子节点才会存放真正的数据 其他节点只存放索引数据
	B+叶子节点增加了指向其他叶子节点的指针
	B*叶子节点和枝节点都有指向叶子节点的指针

辅助索引在查询数据的时候最后还是需要借助于聚焦索引
	辅助索引叶子节点存放的是数据的主键值

有时候就算采用索引字段查询数据 也可能不会走索引
	最好能记三个左右的特殊情况

慢查询优化

	EXPLAIN是MySQl必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。在select语句之前增加explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql
    
1.ALL 
	全表扫描
2.index 
	索引全扫描
3.range 
	索引范围扫描，常用语<,<=,>=,between,in 等操作
4.ref 
	使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
5.eq_ref 
	类似 ref，区别在于使用的是唯一索引，使用主键的关联查询
6.const/system 
	单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
7.null
	不访问任何表或索引，直接返回结果