Mysql---pymysql，事务，触发器
- pymysql
- 安装
- 基本使用
- SQL注入问题
- 二次确认
- 修改表SQL语句补充
- 视图
- 视图的概念
- 视图的作用
- 视图的制作
- 触发器
- 触发器概念
- 触发器作用
- 触发器创建
- 具体案例(了解)
- 触发器其他补充
- 事务
- 事务的概念
- 事务的作用
- 事务的特性
- 具体使用
- 存储过程
- 函数
- 流程控制
- MySQL if判断
- MySQL while循环
- 索引
- 索引的基本用法
- 索引的底层数据结果
- 索引补充
- 作业
pymysql
python中支持操作MySQL的模块很多，其中最常见的当属'pymysql'。
安装
方法1：在pycharm下方选择终端模式，输入'pip3 install pymysql'回车安装。
方法2：先输入导入pymysql的语句，因为没有安装会报红，鼠标移上pymysql出现提示框，点击'Install package pymysql'安装pymysql。
基本使用
1.导入模块
import pymysql
2.链接服务端
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='2231207', # 密码 也可以简写 passwd
database='myzz', # 库名称 也可以简写 db
charset='utf8' # 字符编码 千万不要加杠utf-8
) # 要善于查看源码获取信息
3.产生获取命令的游标对象
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
) # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典
4.编写SQL语句
# SQL语句会被高亮显示 不用惊慌
sql1 = 'show tables;'
sql2 = 'select * from teacher;'
sql3 = 'select * from score;'
5.执行SQL语句
'''execute执行sql语句 会自动帮你加分号结束符 如果你没有写的话'''
affect_rows = cursor.execute(sql1)
print(affect_rows) # 执行SQL语句之后受影响的行数
6.获取结果
print(cursor.fetchall()) # 获取结果集中所有
print(cursor.fetchone()) # 获取结果集中的第一个
print(cursor.fetchmany(3)) # 获取结果集中的指定个数
print(cursor.fetchmany(3)) # 获取结果集中的指定个数
'''控制结果集中光标的移动'''
print(cursor.fetchone())
cursor.scroll(1, 'relative') # 相对于当前位置往后移动一个单位
print(cursor.fetchall())
cursor.scroll(3, 'absolute') # 相对于起始位置往后移动一个单位
print(cursor.fetchall())
SQL注入问题
注入问题就是利用的是MySQL注释语法及逻辑运算符，对代码的判断进行干扰。
示例：
# 写正确的用户名错误的密码也可以登录
用户名:jason' -- jhahsdjasdjasd
密码:直接回车
# 用户名和密码都不需要也可以登录
用户名:xxx' or 1=1 -- asdjasjdkajsd
密码:直接回车
解决SQL注入的问题其实也很简单，就是想办法过滤掉特殊符号。
方法：execute方法自带校验SQL注入问题，自动处理特殊符号。
sql = "select * from userinfo where name=%s and password=%s;"
cursor.execute(sql, (name, password))
# 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;
视图
视图的概念
? 通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为'视图'。
视图的作用
? 如果需要频繁的使用一张虚拟表，以考虑制作成视图，降低操作难度，eg: emp与dep表拼接。
视图的制作
create view 视图名 as sql语句
注意：
? 视图虽然看似很好用，但是会造成表的混乱，毕竟视图不是真正的数据源。
? 视图只能用于数据的查询，不能做增、删、改的操作，可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)。
触发器
触发器概念
? 在对表数据进行增、删、改的具体操作下，自动触发的功能。
触发器作用
? 专门针对表数据的操作，定制个性化配套功能。
? 触发器种类：表数据新增之前、新增之后；表数据修改之前、修改之后；表数据删除之前、删除之后。
触发器创建
create trigger 触发器名字 before/after insert/update/delete
on 表名 for each row
begin
SQL语句
end
'''
触发器的名字一般情况下建议采用下列布局形式
tri_after_insert_t1
tri_before_update_t2
tri_before_delete_t3
'''
具体案例(了解)
"""
补充:临时修改SQL语句的结束符
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');
触发器其他补充
查看当前库下所有的触发器信息
show triggers\G;
删除当前库下指定的触发器信息
drop trigger 触发器名称;
事务
事务的概念
? 事务可以包含诸多SQL语句并且这些SQL语句，要么同时执行成功，要么同时执行失败。这是事务的原子性特点
事务的作用
? 1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
? 2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
事务的特性
? 1 、原子性
? 事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
? 2 、一致性
? 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
? 3 、隔离性
? 一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
? 4 、持续性
? 也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
具体使用
# 1.创建表及录入数据
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);
# 2.事务操作
# 开启一个事务的操作
start transaction;
# 编写SQL语句(同属于一个事务)
update user set balance=900 where name='jason';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='tank';
# 事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
# 事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
存储过程
mysql的存储过程类似于python中的自定义函数。
# 相当于定义函数
delimiter $$
create procedure p1()
begin
select * from cmd;
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) 调用
select @res 查看
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
函数
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！.
ps:可以通过help 函数名 查看帮助信息!
1.移除指定字符
-
ltrim(str):去除字符串左边的空格
-
rtrim(str):去除字符串右边的空格
-
trim(str): 去除字符串两边的空格
SELECT TRIM(" RUNOOB ");
2.大小写转换
-
Lower(str):将字符串str的所有字母转换成小写字母
-
Upper(str):将字符串str的所有字母转换成大写字母
SELECT UPPER('hello world!');
3.获取左右起始指定个数字符
-
Left(str,length):返回具有指定长度的字符串的左边部分。
-
right(str,length):返回具有指定长度的字符串的右边部分。
select left('hello world',7);
4.返回读音相似值(对英文效果)
-
Soundex(str):返回读音和str相似的值
eg:客户表中有一个顾客登记的用户名为J.Lee 但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的 where Soundex(name)=Soundex('J.Lie')
5.日期格式:date_format
ps:在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');
# 查询某一天的日记
select * from blog where Date(sub_time) = '2015-03-01';
# 查看某个月的日记
select * from blog where Year(sub_time)=2016 AND Month(sub_time)=07;
流程控制
MySQL if判断
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
MySQL while循环
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
索引
索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。eg:primary key，主键；unique key ，唯一键；index key，索引键。
上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询。
索引的基本用法
比如说一个表里有id，name，pwd，post_comment，addr，age等字段。基于id查找数据很快，但是基于addr查找数据就很慢。
解决的措施：可以是给addr添加索引
ps：索引虽然好用 但是不能无限制的创建!!!
索引的影响：
- 在表中有大量数据的前提下，创建索引速度会很慢
- 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
索引的底层数据结果
索引的底层数据结构是b+树，有b树，红黑树，二叉树，b*树，b+树。是为了更好的基于树查找到相应的数据。具体理解可以参考https://blog.csdn.net/weixin_50563955/article/details/123498158。
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据。查询次数由树的层级决定，层级越低次数越少。一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的，尽量一个磁盘块儿存放占用空间比较小的数据项。所以我们可以把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';
作业
使用MySQL作为数据库 编写用户名注册登录功能
import pymysql
conn_obj = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='2231207',
database='myzz',
charset='utf8',
autocommit=True
)
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor)
while True:
re_name = input('注册的用户名>>>:').strip()
re_password = input('注册的密码>>>:').strip()
sql3='select * from pwduse where name=%s;'
cursor.execute(sql3,(re_name))
res1 = cursor.fetchall()
if res1:
print('用户名已存在')
continue
else:
sql = 'insert into pwduse value(%s,%s);'
cursor.execute(sql, (re_name, re_password))
print('注册成功')
break
while True:
name = input('请输入您的用户名>>>:').strip()
password = input('请输入您的密码>>>:').strip()
sql1 = "select * from pwduse where name=%s and password=%s;"
cursor.execute(sql1,(name, password))
res = cursor.fetchall()
if res:
print('登录成功')
break
else:
print('用户名或密码错误')
continue