如何使用Python高效操作MySQL数据库及补充相关MySQL知识？

本文共计4412个文字，预计阅读时间需要18分钟。

目录

1.Python操作MySQL

2.SQL注入及解决方法

3.二次确认

4.修改表SQL语句补充

5.视图

6.触发器

7.事务

8.存储过程

9.函数

10.流程控制

11.索引

12.脏读、幻读、不可重复读

内容

1.Python操作MySQL

目录 1.python操作MySQL 2.SQL注入及解决方式 3.二次确认 4.修改表SQL语句补充 5.视图 6.触发器 7.事务 8.存储过程 9.函数 10.流程控制 11.索引 12.脏读、幻读、不可重复读 内容 1.python操作MySQL

python中支持操作MySQL的模块很多，其中最常见的当属：pymysql

属于第三方模块：pip install pymysql

基本使用

1.链接服务端

打印成功表示已经连接上database

2.产生获取命令的游标对象（打开终端输入命令的地方）

3.编写sql语句

4.执行sql语句（返回的结果是sql语句之后受影响的行数）

5.获取结果

通过这种方式来获取数据，如果字段很多并且是相同数据类型，无法知道对应的数据，所以需要更加精确（字典是存储数据最精确的）

而在第二步中，cursor括号内不写参数，结果是元组套元组，不够精确，添加参数之后会将数据处理成字典

完整代码

结果：

[{'cid': 1, 'caption': '三年二班'}, {'cid': 2, 'caption': '三年三班'}, {'cid': 3, 'caption': '一年二班'}, {'cid': 4, 'caption': '二年九班'}]

补充说明

fetchall()：一次性获取所有结果

fetchone()：获取结果集中的第一个；一个接着一个

fetchmany()：获取结果集中的结果个数

cursor.scroll(1, 'relative')：相对于当前位置往后移动一个单位

cursor.scroll(1, 'absolute')：相对于起始位置往后移动一个单位

SQL注入及解决方式 SQL注入问题

编写一个登录功能

第一种情况：写正确的用户名错误的密码也可以登录

第二种情况：用户名和密码都不需要也可以登录

用navicat查看

将sql语句在navicat中运行，两种情况利用的是MySQL注释语法及逻辑运算符

第一种情况：

第二种情况：

解决方式

想办法过滤掉特殊符号，execute方法自带校验SQL注入问题，自动处理特殊符号

sql = 'select * from userinfo where name = %s and password = %s;'
cursor.execute(sql,(name,passord))
二次确认

数据的增删改查四个操作除了查，其他三个没有效果

pymysql针对增、改、删三个操作，设置了二次确认，如果不确认则不会真正影响数据库

代码直接编写（手动二次确认）

affect_rows = cursor.execute(sql)
conn_obj.commit() # 二次确认 配置固定参数（自动二次确认）

conn_obj = pymysql.connect(
autocommit=True
) execute方法补充

批量插入数据（不要忘了二次确认）

修改表SQL语句补充 1.修改表的名字：rename

alter table 表名 rename 新表名；

2.添加字段：add（默认是尾部追加字段）

• alter table 表名 add 字段名 类型；
• alter table 表名 add 字段名 类型 after 字段名；指定追加位置
• alter table 表名 add 字段名 类型 first；指定头部添加字段

3.修改字段：change；modify

• change：名字类型都可；modify（只能改类型不能改名字）
• alter table 表名 change 旧字段名 新字段名 类型 约束条件；
• alter table 表名 modify 字段名 类型 约束条件；

4.删除字段：drop

alter table 表名 drop 字段名；

视图 概念

通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为视图

作用

如果需要频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度

制作

create view 视图名 as sql语句；

弊端

会造成表的混乱，毕竟视图不是真正的数据源

视图只能用于数据的查询，不能做增、删、改的操作，可能会影响原始数据

视图里面的数据是直接来源于原始表，而不是拷贝一份

触发器 概念

在对表数据进行增、删、改的具体操作下，自动触发的功能

作用

专门针对表数据的操作，定制个性化配套功能

种类

• 表数据新增之前、新增之后
• 表数据修改之前、修改之后
• 表数据删除之前、删除之后

创建

create trigger 触发器名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end 触发器名字

一般情况下建议采用下列布局形式：

• tri_after_insert_t1（给表t1添加数据之后触发）
• tri_before_update_t2（给表t2添加数据之前触发）
• tri_before_delete_t3（在表t3删除数据之前触发）

补充

临时修改SQL语句的结束符：delimiter $$（将MySQL默认的结束符号；改成$$）

临时修改的原因：因为触发器、存储过程等技术的代码中也需要使用分号， 如果不修改，则无法书写出完成的代码

查看当前库下所有的触发器信息：show triggers\G;

删除当前库下指定的触发器信息：drop trigger 触发器名称;

事务 概念

事务可以包含诸多SQL语句并且这些SQL语句要么同时执行成功，要么同时执行失败，这是事务的原子性特点

四大特性（ACID）

A：原子性

一个事务是一个不可分割的整体，里面的操作要么都成立要么都不成立

C：一致性

事务必须使数据库从一个一致性状态变到另外一个一致性状态

I：隔离性

并发编程中，多个事务之间是相互隔离的，不会彼此干扰

D：持久性

事务一旦提交，产生的结果应该是永久的，不可逆的

具体使用

1.创建表及录入数据

create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('zhou',1000),
('chen',1000),
('wu',1000);

2.事务操作

开启一个事务的操作
start transaction;
编写SQL语句(同属于一个事务)
update user set balance=900 where name='zhou';
update user set balance=1010 where name='chen';
update user set balance=1090 where name='wu';
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后,事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后,无法回滚,事务自动结束 存储过程

类似于python中的自定义函数

相当于定义函数

delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ; 相当于调用函数

call p1()

类似于有参函数

delimiter $$
create procedure p1(
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；定义
• select @res；查看
• call p1(1,5,@res)；调用

补充

查看存储过程具体信息：show create procedure pro1;

查看所有存储过程：show procedure status;

删除存储过程：drop procedure pro1;

函数

mysql内置的函数只能在sql语句中使用

help 函数名：查看帮助信息 方法

1.移除指定字符

Trim、LTrim、RTrim

2.大小写转换

Lower、Upper

3.获取左右起始指定个数字符

Left、Right

4.返回读音相似值(对英文效果)

Soundex

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');
​
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07; 更多日期处理相关函数

adddate：增加一个日期

addtime：增加一个时间

datediff：计算两个日期差值

流程控制 python if 判断

if 条件:
子代码
elif 条件:
子代码
else:
子代码 js if 判断

if(条件){
子代码
} else if(条件){
子代码
}else{
子代码
} MySQL if 判断

if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if; MySQL while 循环

DECLARE num INT ;
SET num = 0 ; # 声明一个变量
WHILE num < 10 DO # DO相当于问号
SELECT num ;
SET num = num + 1 ;
END WHILE ; 索引

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构

• primary key 主键（非空且唯一）
• unique key 唯一键（唯一）
• index key 索引键
• foreign key 不是用来加速查询用的

基本用法

字段名：id name pwd post_comment addr age

基于id查找数据很快，但是基于addr查找数据就很慢，解决的措施可以是给addr添加索引；索引虽然好用，但是不能无限制的创建

影响

在表中有大量数据的前提下，创建索引速度会很慢

在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低（因为要把索引销毁重新写）

底层数据结构

索引的底层数据结构是b+树，为了更好的基于树查找到相应的数据

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？（一个磁盘块儿存放占用空间比较小的数据项）

一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引(primary key)；辅助索引(unique key，index key)

查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引。

叶子结点存放的是辅助索引字段对应的那条记录的主键的值（比如：按照name字段创建索引，那么叶子节点存放的是：{name对应的值：name所在的那条记录的主键值}）

数据查找如果一开始使用的是辅助索引，那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引：只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='shun'；

非覆盖索引：虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

select age from user where name='shun'；

脏读、幻读、不可重复读

首先需要了解MySQL的数据隔离级别

MySQL数据隔离级别

首先 MySQL 里有四个隔离级别：

• Read uncommttied（可以读取未提交数据）
• Read committed（可以读取已提交数据）
• Repeatable read（可重复读）
• Serializable（可串行化）

在 InnoDB 中，默认为 Repeatable 级别，InnoDB 中使用一种被称为 next-key locking 的策略来避免幻读（phantom）现象的产生。

使用 select @@tx_isolation可以查看 MySQL 默认的事务隔离级别。

不同的事务隔离级别会导致不同的问题：

脏读

所谓脏读是指一个事务中访问到了另外一个事务未提交的数据

如果会话 2 更新 age 为 10，但是在 commit 之前，会话 1 希望得到 age，那么会获得的值就是更新前的值

或者如果会话 2 更新了值但是执行了 rollback，而会话 1 拿到的仍是 10

幻读

一个事务读取2次，得到的记录条数不一致

由于会话 2 在会话 1 之间插入了一个新的值，所以得到的两次数据就不一样了

不可重复读

一个事务读取同一条记录2次，得到的结果不一致

由于在读取中间变更了数据，所以会话 1 事务查询期间的得到的结果就不一样了

解决方案

解决方案也就是上文提到的四种隔离级别，他们可以最大程度避免以上三种情况的发生：

1.未授权读取

也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

2.授权读取

也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

3.可重复读取（Repeatable Read）

可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

4.序列化（Serializable）

序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。