mysql的理论知识补充
MySQL理论知识补充
- pymysql其他操作
- SQL注入
- 用户管理
- 事务
- 视图
- 触发器
- 存储过程
- 函数
- 索引
pymysql其他操作
import pymysql # 创建连结对象 con= pymysql.connect( # 回环地址，默认本机地址 host='127.0.0.1', # 端口号 port=3306, user='root', password='111', database='df', # 字符编码 charset='utf8' # 针对执行增删改操作,自动确认，无需第二次确认 autocommit=True ) # 生成游标对象等待输入命令 cursor = con.cursor(cursor=pymysql.cursors.DictCursor) # 默认可以执行查询操作,查询teacher表 sql='select * from teacher' # 插入操作，必须经过一次确认才可以执行，第一次或第二次确认在之后，否则会报错 sql2='insert into teacher(name) values("袁老师")' #
查找操作
# 自定义sql语句 sql='select * from teacher where tid=1' # 执行sql语句 cursor.execute(sql) # 获取执行结果 res=cursor.fetchall() print(res)
增加操作
# 插入操作，必须经过二次确认才可以执行，第一次第二次确认在之后，否则会报错
sql='insert into teacher(tname) values("袁老师")'
# 执行sql语句
cursor.execute(sql)
conn.commit() # 确认 提交
删除操纵
# 删除操作，必须经过二次确认才可以执行，第二次确认在之后，否则会报错 sql='delete from teacher where tid=6' # 执行sql语句 cursor.execute(sql) conn.commit() # 确认 提交
修改操作
# 修改操作，必须经过二次确认才可以执行，第二次确认在之后，否则会报错 sql='update teacher set tname="计划吧老师" where tid=5' # 执行sql语句 cursor.execute(sql) conn.commit() # 确认 提交
影响行数
sql='update teacher set tname="计划老师" where tid=5' # 执行sql语句 affect_rows=cursor.execute(sql) print(affect_rows) conn.commit() # 确认 提交
'''
输出为一
内部操作只改变了一行
'''
数据显示
显示所有数据
# 自定义sql语句 sql='select * from teacher' # 执行sql语句 cursor.execute(sql)
# 显示一条数据 print(cursor.fetchall())
# 但在此显示时为空，类似于文件独学而里的光标操作
print(cursor.fetchall())
显示一条数据
# 自定义sql语句 sql='select * from teacher' # 执行sql语句 cursor.execute(sql) # 显示一条数据 print(cursor.fetchone())
显示几条数据
# 语法 变量名.fetchmany(x) 显示x个值 sql='select * from teacher' # 执行sql语句 cursor.execute(sql) # 显示两条数据 print(cursor.fetchmany(2))
读取移动(类似于文件光标移动)
# 执行sql语句 cursor.execute(sql) # 当前位置左右移动 正数往右 负数往左 cursor.scroll(2,'absolute') print(cursor.fetchone()) # 起始位置左右移动 正数往右 负数往左 cursor.scroll(1,'relative') print(cursor.fetchone())
代替；
delimiter $$
SQL注入
前期准备
构造一个登录界面
import pymysql # 1.先获取用户名和密码 username = input('username>>>:').strip() password = input('password>>>:').strip() # 2.链接MySQL conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', # 支持简写passwd database='df', # 支持简写db charset='utf8', autocommit=True # 自动确认 ) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 3.拼接查询sql语句 sql = "select * from userinfo where name='%s' and password='%s'" %(username,password) cursor.execute(sql)# 判断输入是否成功
if res: print(res, '登录成功') else: print('用户名或密码错误')
数据库内数据
现象1
只输入用户名jason' -- adsdsfewfvwq
现象2
只输入用户名xxx' or 1=1 -- kjancjqdncjqcjk
注入理论
通过特殊字符和特俗语法的组合来实现登陆成功 # 如何解决上述现象 在sql注册过程中涉及到关键数据时不要手动输入 交由固定方法拼接
解决方法
# 将原先代码跟改成这样 sql = "select * from userinfo where name=%s and password=%s" # execute可以自动识别 cursor.execute(sql，(username,password))
补充多表插入
# 已登录为例，插入多个数据 cursor.executemany(sql, [('jason', 123), ('jason1', 123), ('jason2', 321), ('jason3', 222)])
用户管理
创建用户
creater user 用户名 identified by '密码'； # 修改密码 set password for 用户名 =password('新密码')； set password = password('新密码'); # 针对当前用户
重命名
rename user 新用户名 to 旧的名；
删除用户
drop user 用户名；
查看用户访问权限
show grants for 用户名;
# 新创建的用户没有任何权限
授予和撤销权限
# 授予用户对某数据库下所有表使用select权限 grant select on 库名.* to 用户名;
# 整个服务器
grant all/revoke all on ...
# 整个数据库
on 库名.*
# 特定表
on 库名.t1
# 撤销权限 revoke select on db1.* from 用户名;
事务
四大特性：ACID A：原子性 每一个事物都是整体，不可分割的，事物中的多条代码同时执行同时失败 C：一致性 事物会将内部数据从一种一致的状态转换到另一种一致的状态 I：独立性 事物之间互相独立，互不干涉 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);
数据修改
# 修改数据之前先开启事务操作 start transaction; # 修改操作 update user set balance=800 where name='jason'; update user set balance=1100 where name='kevin'; update user set balance=1100 where name='tony';# 回退到上一个状态 rollback; # 确认事物没有问题 commit; # 确认之后无法回退
select * from user;
修改数据前
修改数据后
视图
什么是视图
通过查询得到的表，进行保存，可以重复使用该表
为什么要使用视图
如果要频繁使用一张虚拟表，可以不用重复查询
如何使用试图
# 视图虽然好不推荐使用
eg：
# 将联表结果作为teabcher2course视图
create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器 # 为何要用触发器 触发器专门针对我们对某一张表数据增insert(前后)、删delete(前后)、改update(前后)的行为
这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码
语法：
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin sql语句 end 针对触发器的名字有一个小习惯 tri_before_insert_t1 触发器简写_之前或之后_操作_表名 # 删除触发器 drop 触发器的名字
存储过程
类似于python中的自定义函数
语法：
# 用‘$$’代替； delimiter $$ # 创建 create procedure p1() begin select * from cmd; end $$ delimiter ; # 调用 call p1()
函数
相对于pythpn中的内置方法 "ps:可以通过help 函数名 查看帮助信息!"
移除指定字符
Trim、LTrim、RTrim
trim语法：
mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
ltrim语法：
mysql> SELECT LTRIM(' barbar'); -> 'barbar'
rtrim语法：
mysql> SELECT RTRIM('barbar '); -> 'barbar'
大小写转换
Lower、Upper
获取左右起始指定个数字符
Left、Right
返回读音相似值(对英文效果)
eg:客户表中有一个顾客登记的用户名为J.Lee 但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的 where Soundex(name)=Soundex('J.Lee')
日期格式:date_format
'''
在MySQL中表示时间格式尽量采用2022-11-11形式
mysl中年月日识别与python中相同
''' 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 date_format(sub_time,'%Y-%m-%d'),count(id) from blog where date(sub_time) = '2015-03-01';
select date_format(sub_time,'%Y-%m'),count(id) from blog where Year(sub_time)=2016 AND Month(sub_time)=07;
其他操作
# 更多日期处理相关函数 adddate 增加一个日期 addtime 增加一个时间 datediff 计算两个日期差值 ...
# i等于1时执行 if i = 1 THEN SELECT 1; # i等于2时执行 ELSEIF i = 2 THEN SELECT 2; # 其他结果时 ELSE SELECT 7; # 结束if程序 END IF;
while循环语句
# 临界值num set num =0； # 当num小于10时循环 while num < 10 DO # 执行命令 select num ; # num值加一 set num = num + 1 ; # 结束循环 end while ;
索引
什么是索引
索引就是一种数据结构，类似于书的目录。
意味着以后在查数据应该先找目录再找数据，而不是用翻页的方式查询数据
索引表中的存在形式
索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构（b树，b*树，b+树，红黑树）
主键 primary key
除了可以加快查询之外还有其他的功能
唯一键 unique
除了可以加快查询之外还有其他的功能
索引键 index key
除了可以加快查询之外没有其他的功能
外键 foreign key
跟索引半毛钱关系都没有 也不存在提升查询速度一说
索引带来的消极影响
在表中有大量数据的前提下,创建索引速度会很慢
在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低
索引分类
聚集索引(primary key) 叶子结点放的一条条完整的记录 辅助索引(unique,index) 叶子结点存放的是辅助索引字段对应的那条记录的主键的值 # 覆盖索引 只在辅助索引的叶子节点中就已经找到了所有我们想要的数据 select name from user where name='jason'; # 非覆盖索引 虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找 select age from user where name='jason';
