MySQL数据查询（二）

标签：事务 name res 查询 索引 MySQL 数据 id select

• SQL注入问题
• 视图
• 触发器
• 存储过程
• 流程控制
• 内置函数
• 索引与慢查询优化

今日内容详细

使用pysql进行用户登录

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='123',
    db='day_44',
    charset='utf8',
	port=3306,
    autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标
username = input('请输入账号>>>:').strip()
password = input('请输入密码>>>:').strip()
sql = 'select * from userinfo where username=%s and password=%s'
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if res:
    print('登录成功')
else:
    print('登录失败')

使用pysql进行用户注册

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='123',
    db='day_44',
    charset='utf8',
	port=3306,
    autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标
username = input('请输入账号>>>:').strip()
password = input('请输入密码>>>:').strip()
sql = 'insert into userinfo(username,password) values(%s,%s)'
cursor.execute(sql, (username, password))
print('注册成功')

SQL注入问题

怪像1:输入用户名就可以登录成功
怪像2:不需要对的用户名和密码也可以登录成功
    
    
SQL注入:利用特殊符合的组合产生特殊的含义 从而避开正常的业务逻辑
select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd='' 

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

'''
补充说明
	executemany(sql,[(),(),(),()...])
'''

视图

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

creat 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.触发器命名有一定的规律
	tri_before_insert_t1
  	tri_after_delete_t2
 	tri_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') #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;

事务

事务的四大特性(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
"""
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别
1.read uncommitted(未提交读)
	事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读)		# MySQL默认隔离级别
	能够解决"脏读"问题，但是无法解决"幻读"
  所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，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列插入了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;')

函数

可以看成python中的内置函数

"可以通过help函数名  查看帮助信息"
1.移除执行字符（类似python中strip）
Trim、LTrim、RTirm

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');
重要:
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计算两个日期差值

流程控制

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

索引相关概念

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

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

索引加快查询的本质
	id int primary key auto_increment,
 	name varchar(32) unique,
  	province varchar(32)
 	age int
 	phone bigint
select name from userinfo where pthone=1881888888;
# 一页一页的翻
select name from userinfo where id=99999;
# 按照目录确定页数找

索引可以加快数据查询，但是会降低增删的速度
'原因是：我们在增删的时候索引也要发生改变，重新添加，所以速度会比较慢'
通常进口下我们频繁使用某些字段查询数据
	为了提升查询的速度可以将该字段建立索引
    
聚集索引(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*叶子节点和枝叶节点都有指向其他节点的指针
 
辅助索引在查询数据的时候最后还是需要借助于聚集索引
	辅助索引叶子节点存放的是数据的主键值

有时候就算采用索引字段查询数据，也可能不会走索引

慢查询优化

explain   SQL语句之前使用返回等级

1.index		最差的情况，尽量避免
2.range
3.ref
4.eq_ref
5.const
6.system
7.null

查询更详细的学习:https://www.cnblogs.com/Dominic-Ji/p/9311099.html

标签：事务,name,res,查询,索引,MySQL,数据,id,select
From： https://www.cnblogs.com/zhanghong1229/p/16936404.html