学习内容总结
今日学习内容总结
在上周的学习中，我们已经将mysql的常用查询关键字学习完毕了。并且学会如何使用可视化软件Navicat。而今天的学习内容首先就是通过python操作mysql。
python操作mysql
前言
为什么要使用Python操作mysql，在做自动化的时候，有时候会由于某种原因，使系统上存在很多脏数据，这就需要每次自动化脚本执行结束时，需要人为去数据库中清除数据，每次都手动执行过于麻烦，于是这里就引进Python3 操作mysql数据库的概念。
python中支持操作MySQL的模块很多，其中最常见的当属'pymysql'。这个模块是第三方的。pymysql是python操纵mysql的一个模块，本质上是一个socket客户端。
安装
pip3 install pymysql
基本使用
import pymysql
连接数据库
import pymysql
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='root', # 密码 也可以简写 passwd
database='school_01', # 库名称 也可以简写 db
charset='utf8' # 字符编码 千万不要加杠utf-8
)产生获取命令的游标对象
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
) # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典数据库查询与使用
编写SQL语句
sql1 = 'select * from teacher;' # SQL语句会被高亮显示 不用惊慌执行SQL语句
affect_rows = cursor.execute(sql1)
print(affect_rows) # 执行SQL语句之后受影响的行数获取结果
res = cursor.fetchall()
print(res)
# 运行结果
'''
5
[{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
Process finished with exit code 0
'''补充了解
获取SQL语句执行的结果，跟读取文件内容的read方法几乎一致(光标)。
cursor.fetchone() : 获取首行数据
cursor.fetchmany(n) ： 获取指定数量的数据
cursor.fetchall() ： 获取全部数据
cursor.scroll(1, 'relative') # 相对于当前位置往后移动一个单位
cursor.scroll(1, 'absolute') # 相对于起始位置往后移动一个单位SQL注入问题
实现用户登录，如果用户存在则登录成功（假设该用户已在数据库中）
import pymysql
user = input('请输入用户名：').strip()
pwd = input('请输入密码：').strip()
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='root', # 密码 也可以简写 passwd
database='db', # 库名称 也可以简写 db
charset='utf8' # 字符编码 千万不要加杠utf-8
)
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
) # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典
sql1 = "select * from userinfo where username='%s' and password='%s'" % (user, pwd)
print('sql语句', sql1)
affect_rows = cursor.execute(sql1)
print(affect_rows) # 执行SQL语句之后受影响的行数
# 关闭连接，游标和连接都要关闭
cursor.close()
conn_obj.close()
if affect_rows:
print('登陆成功')
else:
print('登录失败')演示
请输入用户名：jason
请输入密码：123
sql语句 select * from userinfo where username='jason' and password='123'
1
登陆成功
Process finished with exit code 0sql注入的两种形式
1.用户名存在绕过密码。也就是说写正确的用户名错误的密码也可以登录。
# '--'测试
请输入用户名：jason' -- jhahsdjasdjasd
请输入密码：
sql语句 select * from userinfo where username='jason' -- jhahsdjasdjasd' and password=''
1
登陆成功
Process finished with exit code 0
# '#'测试
请输入用户名：jason' # jhahsdjasdjasd
请输入密码：
sql语句 select * from userinfo where username='jason' # jhahsdjasdjasd' and password=''
1
登陆成功
Process finished with exit code 02. 用户不存在绕过用户名和密码。也就是说用户名和密码都不需要也可以登录。
请输入用户名：xxx' or 1=1 -- asdjasjdkajsd
请输入密码：
sql语句 select * from userinfo where username='xxx' or 1=1 -- asdjasjdkajsd' and password=''
1
登陆成功
Process finished with exit code 0
# 注意
用户名后面有一个单引号
or前后都有空格
必须保证等式成立上述现象就是典型的SQL注入问题，利用的是MySQL注释语法及逻辑运算符。
解决SQL注入问题
解决SQL注入的问题其实也很简单，就是想办法过滤掉特殊符号。
#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
affect_rows=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。execute方法补充(了解)
批量插入数据
sql = 'insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])二次确认
数据的增删改查四个操作是有轻重之分的
查 不会影响真正的数据 重要程度最低
增、改、删 都会影响真正的数据 重要程度较高
pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库
方式1:代码直接编写
affect_row = cursor.execute(sql)
conn_obj.commit() # 手动二次确认
方式2:配置固定参数
conn_obj = pymysql.connect(
autocommit=True # 自动二次确认
)修改表SQL语句补充
# 1.修改表的名字 rename
alter table t1 rename ttt;
# 2.添加字段 add
alter table ttt add pwd int; '''默认是尾部追加字段'''
alter table ttt add tid int after name; '''指定追加位置'''
alter table ttt add nid int first; '''指定头部添加字段'''
# 3.修改字段 change(名字类型都可)/modify(只能改类型不能改名字)
alter table ttt change pwd password tinyint;
# 4.删除字段 drop
alter table ttt drop nid;视图
视图的概念
MySQL从5.0.1版本开始提供视图功能。
视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成。
视图的作用
1. 简单
使用视图的用户完全不需要关心后面对应的表的结构、关联条件、和筛选条件。
2. 安全
使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，而通过视图可以轻松实现。
3. 数据独立
一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，可通过修改视图来解决，不会对访问者造成影响。视图存在的问题
# 视图虽然看似很好用 但是会造成表的混乱 毕竟视图不是真正的数据源
# 视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)视图的创建
创建视图需要有CREATE VIEW的权限，并且对于查询涉及到的列有SELECT权限。如果使用CREATE OR REPLACE，那么哈需要有该视图的DROP权限。
语法：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name
[(column_list)]
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]触发器
触发器的概念
触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
触发器作用
专门针对表数据的操作 定制个性化配套功能。
触发器种类
表数据新增之前、新增之后
表数据修改之前、修改之后
表数据删除之前、删除之后触发器创建
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW] -- 行级触发器
BEGIN
触发器要执行的功能;
END$
DELIMITER ;触发器的名字一般情况下建议采用下列布局形式:
tri_after_insert_t1
tri_before_update_t2
tri_before_delete_t3DELIMITER $介绍
临时修改的原因是因为触发器，存储过程等技术点，代码中也需要使用分号。如果不修改，则无法书写出完成的代码。
使用案例
先创建两张表
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
);需求: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 ; # 结束之后记得再改回来，不然后面结束符就都是$$了只往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');查看触发器
SHOW TRIGGERS;查看当前库下所有的触发器信息。
删除触发器
DROP TRIGGER 触发器名称;删除当前库下指定的触发器信息。
事务
事务的概念
事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。
数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。
事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。
事务的四大特性
原子性
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
事务是一个原子操作, 由一系列动作组成。 组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。
事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。
一致性
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
一旦所有事务动作完成, 事务就被提交。数据和资源就处于一种满足业务规则的一致性状态，即数据不会被破坏。
比如a+b=100,一个事务改变了a比如增加了a的值，那么必须同时改变b，保证在事务结束以后a+b=100依然成立，这就是一致性。
隔离性
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说，并非要求做到完全无干扰。
数据库规定了多种事务隔离界别，不同的隔离级别对应不用的干扰程度。隔离级别越高，数据一致性越好，但并发行越弱。比如对于A对B进行转账，A没把这个交易完成的时候，B是不知道A要给他转钱。
持久性
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
事务的传播属性
即当前的事务方法被另外一个事务方法调用时如何使用事务，默认取值为required，即使用调用方法的事务。
事务传播行为类型 | 说明 |
REQUIRED | 如果有事务在运行，当前的方法就在这个事务内运行；否则，就启动一个新的事务，并在自己的事务内运行； |
REQUIRES_NEW | 当前的方法必须启动新事务，并在它自己的事务内运行；如果有事务正在运行，应该将它挂起。 |
MANDATORY | 当前的方法必须运行在事务内部，如果没有正在运行的事务，将抛出异常。 |
SUPPORTS | 如果有事务在运行，当前的方法就在这个事务内运行；否则它可以不运行在事务中。 |
NOT_SUPPORTED | 当前的方法不应该运行在事务中，如果有运行的事务，将它挂起。 |
NEVER | 当前的方法不应该运行在事务中，如果有运行的事务，就抛出异常。 |
NESTED | 如果有事务在运行，当前的方法就应该在这个事务的嵌套事务内运行。否则，就启动一个新的事务，并在它自己的事务内运行。 |
其中，最常使用的是 REQUIRED 、REQUIRES_NEW。
具体使用
创建数据
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;
编写SQL语句(同属于一个事务)
update user set balance=90 where name=‘gg’;
update user set balance=10 where name=‘vv’;
update user set balance=1 where name=‘aa’;
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束存储过程
存储过程中可以将多个sql语句组合起来，可以使用in创建接收变量，out创建返回变量。
存储过程的创建
类型1：不带参数
delimiter $ $
create procedure p1()
begin
sql语句
end $ $
delimiter ;
类型2：带参数
delimiter $ $
create procedure p1(in a int, out b int)
begin
select num as b from t1 where id = a;
end $ $
delimiter ;存储过程的使用
call 存储过程名。如果有in的变量需要在存储过程名后的（）中写入。
call p1(1,2)有out的需要先定义在用来接收返回
set @res; 定义
call p1(@res)
select @res 查看查看存储过程具体信息
show create procedure pro1;查看所有存储过程
show procedure status;删除存储过程
drop procedure pro1;函数
使用需要注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！可以通过help 函数名，查看帮助信息!
1. 移除指定字符
Trim、LTrim、RTrim2. 大小写转换
Lower、Upper3. 获取左右起始指定个数字符
Left、Right4.返回读音相似值(对英文效果)
Soundex5. 日期格式:date_format
1.where Date(sub_time) = ‘2015-03-01’ # 年月日
2.where Year(sub_time)=2016 AND Month(sub_time)=07; # year 年 month 月更多日期处理相关函数
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中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构：
1. primary key 主键
2. unique key 唯一键
3. index key 索引键上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询
索引的优劣势
优势
1. 检索:可以提高数据检索的效率，降低数据库的IO成本
2. 排序:通过索引列对数据进行排序，降低了CPU的消耗劣势
1. 占磁盘空间
2. 降低更新表的效率索引的基本使用
单列索引之普通索引
create index index_name on table(column(length));
alter table table_name add index index_name(column(length));单列索引之唯一索引
create unique index index_name on table (column(lnegth));
alter table table_name add unique index index_name(column);单列索引之全文索引
create fulltext index index_name on table(column(length));
alter table table_name add fulltext index_name(column);组合索引
alter table table_name add index index_name_(title(50),time(10),......);删除索引
drop index index_name on table;查看索引
show index from table_name索引的存储结构
1. 索引在存储引擎中实现(不同的引擎会只用不同的索引)
2. MyISAM和InnoDB存储引擎:只支持B+tree索引
3. MEMORY/HEAP存储引擎:支持HSAH和BTREE索引索引的底层数据结构是b+树。b树，红黑树，二叉树，b*树 b+树等结构是为了更好的基于树查找到相应的数据。
聚集索引(primary key)
1. 主键索引(聚集索引)的叶子结点会存储数据行，也就是说数据和索引在一起
2. 辅助索引只会存储主键值辅助索引(unique key,index key)
查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引。
覆盖索引
在辅助索引的叶子节点中就已经找到了所有我们想要的数据。
select name from user where name='jason';非覆盖索引
虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找。
select age from user where name='jason';
