今日内容概要
- python操作MySQL
- SQL注入问题
- 修改表SQL语句补充
- 视图、触发器、存储过程
- 事务
- 流程控制、函数
python 操作MySQL
python中支持操作MySQL的模块有很多，常用的是pymysql
这是一个第三方模块，需要下载安装使用
# python连接mysql
import pymysql
# 1.获取连接对象
conn_obj = pymysql.connect(
host='127.0.0.1', # 数据库地址
port=3306, # 端口号，mysql默认端口号为3306
user='root', # 登录mysql的用户名
password='', # 上面用户名对应的密码，可简写为passwd=''
database='user', # 要连接的数据库名，可简写为db=''
charset='utf8', # 编码 不可以写utf-8，会报错
cursorclass = pymysql.cursors.DictCursor # 结果集列表套字典
)
print(conn_obj) # <pymysql.connections.Connection object at 0x01B28ED0>
# 输出连接对象有结果即是连接成功
# 2.获取游标对象
cursor = conn_obj.cursor()
# 3.编写SQL语句
sql = 'select * from user_info'
# 4.执行sql语句
cursor.execute(sql)
# 5.获取结果
res = cursor.fetchall()
print(res)
'''
获取SQL语句执行的结果 跟读取文件内容的read方法几乎一致(光标)
fetchone() 返回一条记录
fetchmany() 返回多条记录，括号里填几就返回几条
fetchall() 返回查询到的全部记录
cursor.scroll(n, 'relative') 相对于当前位置向后移动光标n个位置
cursor.scroll(1, 'absolute') 相对于初始位置向后移动光标n个位置
'''
SQL注入问题
# 什么是SQL注入？
# 有以下代码
username = "petter' --dalkjfdjaskfdljas"
pwd = ''
sql = r'select * from user_info where username = {username} and pwd = {pwd}'
# 当我们执行后会发现sql语句变成了
'select * from user_info where petter' --dalkjfdjaskfdljas and pwd = '
# 可以看到petter后的语句由于--的存在，变成了注释，导致无需密码就查询成功，当username中出现'or 1=1'之类的语句时，甚至无需用户名也可以查询成功
'''利用注释语法，or条件判断使查询无需条件执行成功，这就是SQL注入问题'''
# 如何解决？
解决思路:过滤特殊符号和逻辑运算符
execute方法自带校验SQL注入问题 自动处理特殊符号
ps:设计到敏感数据的拼接 全部交给execute方法即可!!!
eg:
sql = 'select * from user_info where username = s% and pwd = s%'
cursor.execute(sql,(username,pwd))
# execute方法补充(了解)
'''
批量插入数据
eg:
sql1 = 'insert into user_info values(uuid(),"aa","aa",500,0),(uuid(),"bb","bb",500,0),(uuid(),"cc","cc",500,0)'
cursor.execute(sql1)
conn_obj.commit() # 二次确认
'''
二次确认
"""
数据的增删改查四个操作是有轻重之分的
查 不会影响真正的数据 重要程度最低
增、改、删 都会影响真正的数据 重要程度较高
pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库
"""
# 二次确认方式一:代码直接编写
cursor.execute(sql1)
conn_obj.commit() # 手动二次确认
'''哪里用到了增删改，就在哪里加一句commit'''
# 二次确认方式二:配置规定参数
conn_obj = pymysql.connect(
autocommit=True # 自动二次确认
)
'''在获取连接直接添加参数，此链接的增删改方法会自动二次确认'''
修改表SQL语句补充
# 1.修改表名 rename
alter table 表名 rename 新表名;
# 2.添加字段 add
alter table 表名 add 字段名 字段类型; '''默认尾部追加'''
alter table 表名 add 字段名 字段类型 after 已有字段名;'''指定位置追加'''
alter table 表名 add 字段名 字段类型 first;'''头部添加'''
# 修改字段 change(名字类型都可)/modify(只能改类型不能改名字)
alter table 表名 change 字段名 新字段名 新字段类型;
# 删除字段 drop
alter table 表名 drop 字段名;
视图
# 什么是视图？
视图就是把sql语句执行后的虚拟表保存下来，此时这个虚拟表就叫视图
# 视图的作用
当我们需要频繁使用相同的表查询结果时，可以考虑制作成视图，减少重复查询
# 视图的制作
create view 视图名 as sql语句
# 视图虽然看似很好用 但是会造成表的混乱 毕竟视图不是真正的数据源
# 视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)
触发器
# 什么是触发器？
在对表进行增、删、改的具体操作下，自动触发的功能
# 触发器的作用
监控表数据是否变化作出相应反应
# 触发器种类
表数据新增之前、新增之后
表数据修改之前、修改之后
表数据删除之前、删除之后
# 触发器创建
create trigger 触发器名字 before/after insert/update/delete
on 表名 for each row
begin
SQL语句
end
eg:
delimiter $$
create trigger tri_before_delete_t1 after delete on user_info for each row
begin
insert into t values(1);
end $$
delimiter ;
# 当user_info表数据删除之后，向t表添加一条数据
DELETE from user_info WHERE user_id = '11'
'''
补充:临时修改SQL语句的结束符
delimiter $$
临时修改的原因是因为触发器 存储过程等技术点 代码中也需要使用分号
如果不修改 则无法书写出完成的代码
'''
# 触发器补充
查看当前库下所有的触发器信息
show triggers\G;
删除当前库下指定的触发器信息
drop trigger 触发器名称;
事务
# 什么是事务？
一个工厂组装手机，有组装CPU的，有组装电池的...
只有所有的组装全部完成，手机才能使用
事物包含诸多SQL语句，并且这些SQL语句要么同时执行成功，要么同时执行失败
# 事物的作用
多条SQL语句必须同时执行时，可以使用事物
eg:
多多买菜，你下单和扣你钱两个功能，其实就是两个SQL语句
当你付钱的SQL执行完成了，但是服务器崩了，生成订单失败
这时你会发现没扣钱
这种情况很极端，这里就是用了事务
把两个SQL放到一个事务里面去，要么同时成功执行，要么同时失败
# 事务的四大特性(重点)
ACID
A:原子性
一个事务里面的所有SQL是一个整体，必须全部成功执行，否则一条执行失败其他的也执行不了
eg:付款和产生订单必须一起执行，有一个执行不了另一个也不能执行
C:一致性
事务必须使数据库从一个一致性状态编导另一个一致性状态
eg:订单表和账户表必须同时变化
I:隔离性
并发编程中，多个事务之间相互隔离，不会彼此干扰
eg:多个用户同时下单，彼此不受影响
D:持久性
事务一旦提交 产生的结果应该是永久的 不可逆的
eg:一旦付款完成订单生成，那么就无法修改这个订单，除非再开一个取消订单的事务
# 事务的使用
start transaction; # 开启事务
需要同时执行的SQL语句
rollback; # 回滚,执行这个数据库返回事务开启前的状态，执行完事务自动结束
commit; # 提交事务，二次确认，事务结束，提交完后无法再执行回滚
存储过程
类似于python中的定义函数
# 创建存储过程，类似于python创建函数
delimiter $$
create procedure 存储过程名字(
in 参数名 参数类型, # in表示该参数只能传入不能返回
out 参数名 参数类型, # out表示该参数可以被返回出去
inout 参数名 参数类型 # inout表示该参数既可以传入也可以返回
)
begin
SQL语句
end $$
delimiter ;
# 使用,类似于python调用函数
call 存储过程名字()
eg:
delimiter $$
create procedure p1(
in a int,
in b int,
out c int
)
begin
select * from user_info where balance >a and balance < b;
set c = 1;
end $$
delimiter ;
set @c = 0 # 定义参数
select @c; # 查看
call p1(10000,20000,@c);
select @c; # 1
'''
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
'''
内置函数
'''MySQL的内置函数只能在SQL语句中使用'''
# 1.移除指定字符
Trim、LTrim、RTrim
Trim(前面|后面|前后面（leading|trailing|both）'去除的字符' from '字段名') # 只能去除单个字符
LTrim(前面|后面|前后面（leading|trailing|both）'去除的字符' from '字段名') # 去除左边的字符，可以去除多个
RTrim(前面|后面|前后面（leading|trailing|both）'去除的字符' from '字段名') # 去除右边的字符，可以去除多个
# 2.大小写转换
Lower、Upper
Lower(字段名)
Upper(字段名)
# 3.获取左右起始指定个数字符
Left、Right
Left(字段名，长度)
Right(字段名，长度)
# 4.返回读音相似值(对英文效果)
Soundex
eg:where Soundex(name)=Soundex('J.Lie')
# 5.日期格式
date_format(字段名,'日期格式')
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
...
流程控制
# if条件判断
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
# while循环
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 name pwd post_comment addr age
基于id查找数据很快 但是基于addr查找数据就很慢
解决的措施可以是给addr添加索引
'''索引虽然好用 但是不能无限制的创建!!!'''
**索引的影响:**
* 在表中有大量数据的前提下，创建索引速度会很慢
* 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
索引的底层数据结构是b+树
b树 红黑树 二叉树 b*树 b+树
上述结构都是为了更好的基于树查找到相应的数据
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
"""
聚集索引(primary key)
辅助索引(unique key,index key)
查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据
覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='jason';
非覆盖索引:虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
select age from user where name='jason';
"""
作业
用户登陆注册
# 建库建表
create database user;
CREATE TABLE `user` (
`id` int(11) primary key auto_increment,
`username` varchar(32) ,
import pymysql
# 获取连接对象
conn_obj = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='',
database='user',
charset='utf8'
)
# 2.获取游标对象
cursor = conn_obj.cursor()
while True:
# 3.获取用户名密码
username = input('请输入用户名>>>').strip()
pwd = input('请输入密码>>>').strip()
# 4.验重
sql = 'select * from user where username = %s'
num = cursor.execute(sql,(username,))
if num != 0:
print(f'用户{username}已存在')
continue
# 5.添加到数据库
sql1 = 'insert into user(username,pwd) values (%s,%s)'
cursor.execute(sql1,(username,pwd))
conn_obj.commit()
print(f'用户{username}注册成功！')
break
`pwd` varchar(32)
) ;
幻读, 脏读, 不可重复读
脏读:
事务A修改了值a为b
这时事务B用到了值b
然后A回滚了,b的值又变成了a
此时b值不存在，B再读读不到了，这就是脏读
幻读:
事务A查询数据，查出来10条
这时事务B创建了两条数据，并提交了
A再用相同的查询代码查，结果有12条
这就是幻读
不可重复读:
事务A循环查询一个值
事务B修改这个值并提交，此时A并未提交，还在查询，发现值变了
事务C又修改了这个值并提交，此时A还未提交，查询值又变了
这一个字段的，A读到三个值，就是不可重复读