PyMySQL其它操作与数据库知识补充
概要
-
pymysql其他操作
-
SQL注入
-
事务
-
用户管理
-
索引
-
辅助知识补充
详细
-
pymysql其他操作
pymysql.connect()中 参数password支持简写passwd 参数database支持简写db cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 1.游标对象默认可以执行查询操作 sql = 'select * from teacher' 2.游标对象执行涉及到操作数据的SQL语句不直接生效 sql = 'insert into teacher(tname) values("Leo")' 可以看到在数据库中数据并未增加
# 这是MySQL的一种安全机制,操作(即增删改)数据需要进行二次确认,在执行完后可利用代码提交 方法1:每次执行完操作数据后提交 conn.commit() 方法2:pymysql.connect中添加自动确认参数 autocommit=True 可以看到数据成功增加
游标对象有多种方法用来获取数据，以"select * from teacher"为例。
3.cursor.fetchall() #从结果中获取所有数据
4.cursor.fetchone() # 从结果中获取第一条数据
5.cursor.fetchmany(3) # 从结果中获取指定条数的数据
6.cursor.scroll(2,'relative') # 游标相对当前位置左右移动,第一个参数为正则向右,为负则向左 cursor.scroll(1,'absolute') # 游标相对于起始位置移动,第一个参数为正则向右,为负则向左
-
SQL注入
# SQL语句的拼接(一次插入多条记录) cursor.executemany(sql, [(记录1的参数),(记录2的参数)] # 直接简单地拼接SQL语句可能会导致SQL注入问题 sql = "select * from userinfo where name=%s and password=%s" 1.只需要用户名正确即可通过验证 select * from userinfo where name='jasonNB' -- ajsdkjaskldjklasd' and password='' 2.无论用户名密码是什么均可通过验证 select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password='' """ SQL注入的本质:利用一些特殊符号或特殊语法形式拼接处违背常理的语句 应对: 涉及到关键性的数据(用户名/密码等)不要手动拼接,使用能够过滤特殊符号的固定方法 """
-
事务
事物的四大特性:ACID 1.原子性---A 原子性是指事务包含的所有操作只能全部执行成功,只能全部执行失败并回滚，即成功指的是该事务涉及到的所有记录均更改成功,失败指的是该事务对数据库的数据无影响。 2.一致性---C 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。例如转账,转账前后双方的金额和必须是相同的。 3.独立性---I 独立性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰。例如转账,当一个用户需要转账给另一个用户的事务未结束时,第三方无法和这两个用户之间产生交易。 4.持久性---D 持久性是指一个事务被提交=以后，对数据库中的记录的改变就是永久性的，即使数据库系统遇到故障也不会丢失提交事务的操作。 eg: create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('jason',1000), ('kevin',1000), ('tony',1000);
事务操作步骤 1.先开启事务操作 start transaction; 2.进行修改操作 # 买支付100元 update user set balance=900 where name='jason'; # 中介拿走30元 update user set balance=1030 where name='kevin'; # 卖家拿到70元 update user set balance=1070 where name='tony'; 3.这时可以进行回滚操作 rollback; 4.如果确认数据没有问题,不需要回滚,则可以提交事务 commit;
-
用户管理
1.创建用户 create user 用户名 identified by '密码' eg:create user Leo identified by 'ultramanleo007' 2.修改密码 set password for 用户名 = Password('新密码');# 指定用户 set password = Password('新密码');# 当前登录用户 3.重命名用户 rename user 旧用户名 to 新用户名; eg:rename user Zero to Leo; 4.删除用户 drop user 用户名; 5.查看用户访问权限 show grants for 用户名; 6.授予访问权限 grant select on db_dbcx.* to 用户名; 7.撤销权限 revoke select on db_dbcx.* from 用户名; # 操作权限的单位 1.整个服务器:grant all/revoke all 2.整个数据库:on db.* 3.特定表:on db.t1
-
索引
1.索引的概念 类似于书的目录,一种能加快查询速度的数据结构。 2.索引也是键,能使存储引擎快速查询到记录 主键 primary key 可以加快查询,有其他的功能 唯一键 unique 可以加快查询,有其他的功能 索引键 index key 只能加快查询 外键 foreign key 与索引无关,不能提升查询速度 聚集索引 即主键,叶子结点放的一条条完整的记录
辅助索引 即唯一键和索引键,叶子结点存放的是该字段对应的那条记录的主键值 覆盖索引 只在辅助索引的叶子结点就找到了想要的数据 select name from user where name='Leo'; 非覆盖索引 在叶子结点可以查到记录对应的主键值,再使用主键的值通过聚集索引查找到需要的数据 select age from user where name='Leo'; 3.索引的使用 (1)在表中有大量数据的前提下,创建索引速度会很慢 (2)在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低 一般什么字段查询慢就将该字段制作成索引,但有例外需要整体考虑
-
其他辅助知识补充（了解为主）
视图
1.视图概念 用select查询得到的结果可被当成一张表,并被保存下来以供下次使用,该表称为视图 2.视图使用 使用视图可以避免重复查询同样的数据 3.语法 create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id; 一般不推荐使用视图 1.视图记录不会随着表的更改而更改 2.创建过多视图会导致使用上的不便
触发器
1.触发器的概念 在满足对某张表数据的增、删、改的情况下，自动触发的功能。 2.触发器使用场景 对某一张表数据增删改行为执行前后需自动执行的额外操作,即另一段SQL语句。 3.语法 create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin# 删除触发器 sql语句 end 4.触发器命名习惯 eg:tri_before_insert_test01 即触发器简写_前或后_操作_表名 5.删除触发器 drop trigger tri_after_insert_cmd;
存储过程
1.存储过程的概念 SQL中的自定义函数 2.语法 # SQL语句需要由分号结尾,故定义存储过程需要先重定义结束符 delimiter $$ # 类似创建函数 create procedure p1() # 存储过程需要执行的操作,前后需分别加begin和end begin select * from cmd; end $$ # 存储过程定义结束,将结束符修改回分号 delimiter ; # 像调用函数一样调用存储过程 call p1()
数据库中的一些函数（用于存储过程）
1.可以在cmd终端使用help 函数名查看相关信息 2.移除指定字符 Trim、Lrim、Rrim SELECT TRIM(' bar '); # 'bar' SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); # 'barxxx' SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); # 'bar' SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); # 'barx' 3.大小写转换 Lower、Upper 4.获取左右指定个数字符 Left、Right 5.返回读音相似的值(英文环境效果明显) eg: where Soundex(name)=Soundex('J.Lie') 6.日期格式 date_format(一般采用年-月-日) eg:记录发布博客时间的表 create table blog ( id int primary key auto_increment, name varchar(16), 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'); 其它时间函数 adddate 增加一个日期 addtime 增加一个时间 datediff 计算两个日期差值
流程控制（用于存储过程）
1.分支结构 if if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; 2.循环结构 while SET num = 0 ; WHILE num < 10 DO SELECT num; SET num = num + 1; END WHILE;
作业
1、基于搜索查询并总结归纳“数据库三大设计范式”
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。 1.第一范式 确保每列保持原子性 eg: 不满足 用户表（用户名,家庭地址） 原因:家庭地址可以进一步细分为查询频率更改的省市 满足 用户表（用户名,省,城市,详细地址） 2.第二范式 满足第一范式的同时,确保表中的每列都和主键相关而不是只和主键的某一部分相关(联合主键),即在一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中 eg: 不满足 学生选课表（学号,学生姓名,课程编号,课程名称） 原因:学号和课程编号作为联合主键,课程名称只依赖于课程编号,而和学号没有关系 满足 学生表(学号,学生姓名) 课程表(课程编号,课程名称) 选课表(学号,课程名称) 3.第三范式 满足第二范式的同时,确保表中的每列都和主键直接相关,而不是间接相关 eg:不满足 学生班级表(学号,学生姓名,班级编号,班级名称,班级信息) 满足 原因:班级相关的列与主键(学号)没有直接关系 学生表(学号,学生姓名,班级编号) 班级表(班级编号,班级名称,班级信息)
import pymysql def getUserInfo(): """获取输入的用户信息""" userName = input('请输入用户名:').strip() password = input('请输入密码:').strip() userInfo = {'userName': userName, 'password': password} return userInfo def isNone(userInfo): """校验用户信息是否为空""" if userInfo.get('userName') == '' or userInfo.get('password') == '': print('用户名或密码为空,请重新输入!!!') return True return False def createLink2MySQL(): """创建连接""" conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='ultra12345', database='db_dbcx', charset='utf8', # 自动提交 autocommit=True ) return conn def operateUserInfo(sql, userInfo, conn): """操作用户信息表""" cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute(sql, userInfo) res = cursor.fetchall() return res def register(conn): """注册""" # 1.获取用户信息 userInfo = getUserInfo() # 2.判断用户信息是否为空 if isNone(userInfo): return # 3.用户信息不为空,查询用户名是否已存在 sql = 'select * from user_info where user_name=%s' realUserInfo = operateUserInfo(sql, userInfo.get('userName'), conn) if not realUserInfo: # 写入 sql = 'insert into user_info(user_name,password) values(%s,%s)' res = operateUserInfo(sql, (userInfo.get('userName'), userInfo.get('password')), conn) print('用户{}注册成功!!!'.format(userInfo.get('userName'))) else: print('用户名已存在,请重新输入!!!') return False return def login(conn): """登录""" # 1.获取用户信息 userInfo = getUserInfo() # 2.判断用户信息是否为空 if isNone(userInfo): return # 3.用户信息不为空,查询是否有记录的用户名和密码均与输入的用户信息相同 sql = 'select * from user_info where user_name=%s and password=%s' res = operateUserInfo(sql, (userInfo.get('userName'), userInfo.get('password')), conn) if not res: print('用户名或密码不正确!!!请重新输入') return else: print('用户{}登录成功!!!'.format(userInfo.get('userName'))) return """主流程""" funcDict = {'1': register, '2': login} print('欢迎来到用户注册登录系统 ver4.1\n' '******** 1.注册 ***********\n' '******** 2.登录 ***********\n') # 先建立数据库连接 conn = createLink2MySQL() while True: funcNum = input('请输入想要执行的功能:').strip() if funcNum in funcDict: funcName = funcDict.get(funcNum) funcName(conn) else: print('该功能不存在,请重新选择!!!')
浙公网安备 33010602011771号