-
-
SQL注入问题
-
修改表SQL语句补充
-
视图、触发器、存储过程
-
事物
-
流程控制、函数
-
索引与慢查询优化
python操作MySQL
python中支持操作MySQL的模块很多 其中最常见的当属'pymysql'
# 属于第三方模块
pip3 install pymysql
# 基本使用
import pymysql
# 1.链接服务端
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='hgvwbq123', # 密码 也可以简写 passwd
database='tep', # 库名称 也可以简写 db
charset='utf8' # 字符编码 千万不要加杠utf-8
) # 要善于查看源码获取信息
# 2.产生获取命令的游标对象
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
) # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典
# 3.编写SQL语句
# sql1 = 'show tables;'
sql1 = 'select * from teacher;' # SQL语句会被高亮显示 不用惊慌
# sql1 = 'select * from score;' # SQL语句会被高亮显示 不用惊慌
# 4.执行SQL语句
affect_rows = cursor.execute(sql1)
print(affect_rows) # 执行SQL语句之后受影响的行数
# 5.获取结果
res = cursor.fetchall()
print(res)
'''补充说明'''
获取SQL语句执行的结果 跟读取文件内容的read方法几乎一致(光标)
fetchone()
fetchmany()
fetchall()
cursor.scroll(1, 'relative') # 相对于当前位置往后移动一个单位
cursor.scroll(1, 'absolute') # 相对于起始位置往后移动一个单位
SQL注入问题
# 写正确的用户名错误的密码也可以登录
用户名:root' -- hgvwbqoooo
密码:直接回车
# 用户名和密码都不需要也可以登录
用户名:xxx' or 1=1 -- hgvwbqoooo
密码:直接回车
"""上述现象就是典型的SQL注入问题"""
上述情况利用的是MySQL注释语法及逻辑运算符
# 解决SQL注入的问题其实也很简单 就是想办法过滤掉特殊符号
execute方法自带校验SQL注入问题 自动处理特殊符号
ps:设计到敏感数据的拼接 全部交给execute方法即可!!!
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');
4.触发器其他补充
查看当前库下所有的触发器信息
show triggers\G;
删除当前库下指定的触发器信息
drop