python 中SQL注入问题
在python 用pymysql基于数据库 来操作用户登录的时候会遇到一些问题。
问题1：只填写对用户名就可以登录成功
问题2：填写不对的用户名和密码也能登陆成功
SQL注入问题所在：
python中
sql ="insert into from userinfo(name,pwd) values( '%s','%s')"%(username,password)
在登录的时候可能在用户名后面添加了一些特殊符号 然后产生特殊含义 从而出现这种问题。
select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
"用户后面被添加了 -- 注释符号"
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd=''
"因为or运算且1=1恒成立，所以不需要用户名和密码直接登入。"
针对上述的SQL注入问题 核心在于手动拼接了关键数据 交给execute处理就能解决问题
注册：
sql = "insert into from userinfo(name,pwd) values( %s,%s)"
cursor.execute(sql, (username, password))
登录：
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))
executemany 批量插入数据
"""
execute方法补充(了解)
批量插入数据
sql = 'insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])
"""
视图
视图通过查询得到一张 临时的表，通过方法保存下来。下次直接使用
关键字
create view
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"查询的结果可以看成一张表"
注意事项：
""" 1. 视图只能用来查询不能做其他操作
2. 尽量少用 与原表产生混淆会干扰操作者"""
触发器
达到某个条件之后自动触发执行
针对表增，删，改，操作都能自动触发
主要有六种情况：
增前，增后，删前，删后，改前，改后
触发器语法：
create trigger 触发器的名字 before(前)/after(后) insert/update/delete on 表名 for each row
begin
sql语句
end
触发器的命名有规律：
tri_before_insert_t1 增前
tri_after_delete_t2 删后
tri_after_update_t2 改后
查看当前库下所有的触发器信息
show triggers\G;
删除当前库下指定的触发器信息
drop trigger 触发器名称;
案触发器例
"因触发器中可能会用到分号; 所以需要把语句结尾符切换成别的符号"
delimiter 设置的结束符号
1.先创建两张表
# 案例
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
);
2.需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
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 ; # 结束之后记得再改回来，不然后面结束符就都是$$了
3.仅仅往cmd表中插入数据
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');
NOW() # 获取当前时间
事物
事物的四大特性(ACID)
A:原子性
事务中的各项操作是不可分割的整体，要么同时成功要么同时失败
C:一致性
使数据库从一个一致性状态变道另一个一致性状态
I:隔离性
多个事物之间彼此不干扰
D:持久性
也称为永久性，指一个事物一旦提交，它对数据库中数据的改变应该是永久性的
其实事物就是我们在做一些事情中的操作。
eg：A欠了B一笔钱
还钱时，A拿着交行的银行卡去招商银行的ATM给B的建设银行转钱
1、朝交行的服务器发送请求，修改A的账户
2、朝建行的服务器发送请求，修改B的账户
事物案例
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;
"""事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
"""
事务相关关键字
start transaction; # 开启事物操作
rollback # 事物回滚
commit # 确认提交
savepoint # 保存事物节点 类似于游戏存档
创建节点可以使用savepoint
savepoint sp01;
回退到节点地址
rollback to sp01;
事物的知识点 （隔离级别与 MVCC）
在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值，这表示在事务开始之后这行记录才被删除。
"""
函数
# mysql内置函数只能在sql语句中使用
'''我们可以通过help 函数名 的方式查看帮组信息'''
# 移除指定字符
Trim 、LTrim(左移除) 、 RTrim(右移除)
# 大小写转换
Lower、Upper
# 获取左右起始指定个数字符
Left、Right
# 返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为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');
format(sub_time,'%Y-%m') # 切取时间 保留年月展示
1.where Date(sub_time) = '2015-03-01' # date 年月日 筛选条件
2.where Year(sub_time)=2016 AND Month(sub_time)=07; # Year 年 month月 筛选条件
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
...
存储过程
# 有点类似于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 tname from userinfo where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
select @res; 查看
call p2(1,5,@res) 调用
select @res 查看 # # 执行成功，@res变量值发生了变化
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10)) # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')
流程控制
在mysql中也有 流程控制，其逻辑与python中相差无几
# 分支结构
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 索引键
上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询
# ps:foreign key 是用来建立外键关系的
索引加快查询的本质
id int primary key auto_increment,
name varchar(32) unique,
province varchar(32)
age int
phone bigint
select name from userinfo where phone=18818888888; # 一页页的翻
select name from userinfo where id=99999; # 按照目录确定页数找
# 索引的注意事项
基于id查找数据很快 但是基于别的非主键字段查找数据就很慢
解决的措施可以是给非主键字段添加索引 推荐使用 index key
'''索引虽然好用 但是不能无限制的创建!!!'''
**索引的影响:**
* 在表中有大量数据的前提下，创建索引速度会很慢
* 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
聚集索引(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*树在叶节点与枝节点都添加了指向其他叶节点与枝节点的指针
通过学习这些 数据结构 得知：
辅助索引在查询数据的时候 因 辅助索引的叶子节点存放的是数据的主键 最后还是要利用主键做聚集索引
"""
有时候就算采用索引字段查询数据 也可能不会走索引!!!
函数运算 count
模糊查询 like "%_"
成员运算符 not in
数学运算
"""
慢查询优化
explain 查询表的 索引扫描类型 关键字
1. index 尽量避免
2. range # 我们能达到 range 就可以了
3. ref
4. eq_ref
5. const
6. system
7. null
添加索引方法
创建index索引：
alter table 表名 add index index_name(字段名); # 注意 添加索引的字段类型长度不能过长
create index indexname on 表名(字段名);
查询索引：
desc 表名;
show index from 表名;
删除index索引：
alter table 表名 drop key index_name; # 注意两种创建办法 一个是index_name 另一个是indexname
添加唯一性主键；
alter table 表名 add unique key uni_键名可以自己起(字段名);
