今日内容概要
SQL注入问题
在使用python中使用sql语句来操作数据库的时候在获取用户输入使用格式化输出 就会出现一个sql注入问题
eg:
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql)
#现象一 输对用户名就可以登入
select * from userinfo where name = '用户名' --snjandj'
#现象二 不需要对的用户名和密码也可以登入
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd=''
产生原因>>>利用sql语句中的注释输入--后 将会把后面的条件全部注释掉
sql注入:利用特殊符号的组合产生特殊的含义 从而避开正常的业务逻辑
解决:
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
# pymysql 会自动处理有特殊含义的符号
'''
补充:
如果想要一次性执行多个sql语句
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
# begin表示着开始 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(32),
cmd char(32),
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('xiaochen',1000),('aaaa',1000),('tack',1000);
#修改数据之前先开启事务操作
start transaction;
#修改操作
update user set balance=900 where name='xiaochen'; # 支付100
update user set balance=1010 where name='aaa'; # 中介收10元
update user set balance=1090 where name='tack'; #卖家收90元
# 回滚到上一个状态
rollback;
# 开启事务之后,只要没有执行commit操作,数据其实没有真正刷新到硬盘
commit;
'''
事务相关关键字
stare transaction >>> 开始事务
rollback >>> 回滚到上一个状态(事务开始时)
commit >>> 保存
savepoint >>> 节点操作 使rollback回退到节点位置
'''
隔离级别
在sql标准中定义了四种隔离级别，每一种级别都规定了一个事务所作的修改
InnoDB支持所有隔离级别
set transaction isolaation level 级别
#1.read uncommitted(未提交读)
事务中的修改其实没有提交 对其他事务也都是可见的 事务可以读取未提交的数据 这一现象称之为'脏读'
#2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他都是不可见的 这个级别也可以叫做'不可重复读'
#3.repeatable read(可重复读) MYSQL默认隔离级别
能够解决'脏读'问题 但是无法解决'幻读'
幻读:指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入新的记录，当之前的事务子再次读取该范围的记录会产生幻行，innodb和xtradb通过多版本并发控制(mvcc)之间隙锁策略解决该问题
#4.serilizable(可串行读)
强制事务串行执行 很少使用该级别
macc只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
没开始一个新的事务版本号都会自动递增 事务开始时刻的系统版本号会作为事务的版本号用来和查询到每行记录版本号进行比较
'''
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来:
1.当前事务id要大于或者等于当前行的create_version值 这表示在事务开始前这行数据已经存在了
2.当前事务id要小于delete_vresion值 这表示在事务开始之后这行记录才被删除
'''
存储过程
可以看成是python中的自定义函数
# 无参函数
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
'''使用完记得把结束符换回;'''
# 调用
call p1()
# 有参函数
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
out res int # out表示这个参数可以被返回出去 还有一个input表示既可以传入也可以被返回出去
)
begin
select * from cmd where id > m;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; #定义
select @res; #查看
call p1(1,5,@res) #调用
select @res #查看
>>>>不能直接传入 需要传入的是一个变量名 不让改值我们也无法知道
'''
查看存储过程具体信息
show crete procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
'''
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用
# 1.直接在mysql中调用
set @res=10 # 一定需要先定义
call p1(2,4,10); # 报错 修改了10的数据没有意义 无法查询
call p1(2,4,@res);
# 查看结果
select @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中的内置函数
'ps:可以通过help 函数名 查看帮助信息!'
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(只针对英文)
Soundex
'''
客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
'''
# 5.日期格式:data_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 Data(sub_time)='2015-03-01'
2.获取指定的年份和月份
where Year(sub_time)=2016 AND Monrh(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
detediff 计算两个日期差值
流程控制
# 分支结构
declare i int default 0;
IF i = 1 THEN
select 1;
ELSEIF i = 2 THEN
select 2;
ELSE
select 3;
END IF;
'''
注意:
在python中if后面用冒号结尾 而在sql中使用than作为if语句的结尾
在编写流程控制的时候 记得加上end if
'''
# 循环结构
DECLARE num INT;
set num=0;
WHILE num < 10 DO
select num;
set num = num + 1;
END WHILE;
'''
注意:
在python中where用冒号结尾 而在sql中使用的是do结尾
写完记得写上end while
'''
索引相关概念
索引相当于是一本书的目录 可以让你更快速的查找到你要的内容
让获取的数据更有目的性 从而提高数据库检索数据的性能
索引在MySQL中也叫做'键' 是存储引擎用于快速查找到记录的一种数据结构
* primary key
* unique key
* index key
#1.上述的三个key都可以加快数据的查询
#2.primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
#3.外键不属于索引键的范畴 是用来建立关系的 与加快查询无关
索引加快查询的本质
id int primary key auto_inctement,
name vacher(32) unique,
province varchar(32)
age int
phone bigint
select name from userinfo where phone=12121212;
#没有利用索引 相当于是一页一页的查找
select name from userinfo where id = 9999;
#利用索引 相当于是按照目录确定页数的查找
索引可以加快数据查询 但是会降低增删的速度
通常情况下我们频繁使用某些字段查询数据
为了提升查询的速度可以将该字段建立索引
#聚焦索引(primary key)
主键、主键索引
#辅助索引(unique,index)
除主键意外的都是辅助索引
#覆盖索引
select name from user where name='jaosn';
'''已知name的情况下 还是用name去索引找值'''
#非覆盖索引
select age from user where name='jason';
索引数据结构
索引底层就是树结构>>>:树是计算机底层的数据结构
二叉树
二叉树里面还可以细分成很多领域 我们简单的了解即可
二叉意味着每个节点最大只能分两个子节点
B树
所有的节点都可以存放完整的数据
B+\*树
只有叶子节点才会存放真正的数据 其他节点只存放索引数据
B+叶子节点增加了指向其他叶子节点的指针
B*叶子节点和枝节点都有指向其他节点的指针
辅助索引在查询数据的时候最会还是需要借助于聚集索引
辅助索引叶子节点存放的是数据的主键值
有时候就算采用索引字段查询数据 也可能不会走索引!!!
最好能记三个左右的特殊情况
慢查询优化
#我们怎么去判断自己写的sql语句效率到底如何?
在sql语句前面加上 explain 可以查看sql语句的等级
'''在sql语句中存在七个等级'''
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
从上至下等级逐渐更高 我们在编写sql语句时 至少做到等级在range及以上 尽量避免index等级的出现
# index等级代表着全局搜索 生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
全文检索
MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
id int primary key auto_increment,
content text
fulltext(content)
)engine=MyISAM;
# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''
# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
jason is handsome and cool,every one want to be cool,tony want to be more handsome;
二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""
# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);
# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略