文章目录
一、python操作MySQL1.pymysql模块1.基本使用 2.sql注入问题3.二次确认 二、修改表SQL语句1.修改表的名字 rename2.添加字段 add3.修改字段4.删除字段5.临时修改SQL语句的结束符 三、视图1.视图的概念2.视图的作用3.视图的创建 四、触发器1.触发器概念2.触发器作用3.触发器种类4.触发器创建 五、存储过程1.创建2.使用 六、事务1.事务的概念2.事务四大特性3.事务的创建 七、流程控制1.if判断2.while循环 八、常见函数1.移除指定字符2.大小写转换3.获取左右起始指定个数字符4.返回读音相似值(对英文效果)5.日期格式:date_format 九、索引一、python操作MySQL
1.pymysql模块
通过pymysql模块可以使用python控制数据库。
python中支持操作MySQL的模块很多 其中最常见的当属’pymysql’
pymysql属于第三方模块:
需要先下载才能使用:
pip install pymysql
1.基本使用
import pymysql 首先导入模块
### 2.链接服务端conn_obj = pymysql.connect(host='127.0.0.1', # MySQL服务端的IP地址port=3306, # MySQL默认PORT地址(端口号)user='root', # 用户名password='jason123', # 密码 也可以简写 passwddatabase='jp04_3', # 库名称 也可以简写 dbcharset='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') # 相对于起始位置往后移动一个单位
2.sql注入问题
sql注入问题就是利用mysql注释以及逻辑判断强行改变mysql语句的作用。
eg:
select_sql = “select username,password from user where username = %s and password = %s;”(username,password)
如果用户输入的字符中附带了 or 1=1、# 等可以修改mysql语句的符号会直接导致,数据库的运行逻辑偏离设计时的目的。
这时可以使用execute方法
execute(sql语句,参数1…参数n)
通过execute方法可以过滤调用用户中的特殊符号。
3.二次确认
在pymysql中操作数据库后是默认需要提交才能保存对数据库的增删改。
方式1:代码直接编写
affect_row = cursor.execute(sql)
mit() # 手动二次确认
方式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;
5.临时修改SQL语句的结束符
delimiter $$
三、视图
1.视图的概念
通过SQL语句的执行得到的一张虚拟表 保存下来之后就称之为'视图'
2.视图的作用
如果需要频繁的使用一张虚拟表 可以考虑制作成视图 降低操作难度视图虽然看似很好用 但是会造成表的混乱 毕竟视图不是真正的数据源视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)3.视图的创建
create view 视图名 as sql语句
四、触发器
1.触发器概念
在对表数据进行增、删、改的具体操作下,自动触发的功能
2.触发器作用
专门针对表数据的操作 定制个性化配套功能
3.触发器种类
表数据新增之前、新增之后
表数据修改之前、修改之后
表数据删除之前、删除之后
4.触发器创建
语法:
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语句组合起来,可以使用in创建接收变量,out创建返回变量
1.创建
语法:
类型1:不带参数
delimiter $ $
create procedure p1()
begin
sql语句
end $ $
delimiter ;
类型2:带参数
delimiter $ $
create procedure p1(in a int, out b int)
begin
select num as b from t1 where id = a;
end $ $
delimiter ;
2.使用
call 存储过程名
如果有in的变量需要在存储过程名后的()中写入。
eg:
call p1(1,2)
有out的需要先定义在用来接收返回
set @res; 定义call p1(@res)select @res 查看
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
六、事务
1.事务的概念
事务可以包含诸多SQL语句并且这些SQL语句要么同时执行成功 要么同时执行失败 这是事务的原子性特点
2.事务四大特性
ACID
A:原子性
一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
C:一致性
事务必须使数据库从一个一致性状态变到另外一个一致性状态
I:隔离性
并发编程中 多个事务之间是相互隔离的 不会彼此干扰
D:持久性
事务一旦提交 产生的结果应该是永久的 不可逆的
3.事务的创建
开启一个事务的操作
start transaction;
编写SQL语句(同属于一个事务)
update user set balance=90 where name=‘gg’;
update user set balance=10 where name=‘vv’;
update user set balance=1 where name=‘aa’;
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
七、流程控制
1.if判断
if 条件 then子代码elseif 条件 then子代码else子代码end if;
2.while循环
DECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECT num ;SET num = num + 1 ;END WHILE ;
八、常见函数
mysql内置的函数只能在sql语句中使用
1.移除指定字符
Trim、LTrim、RTrim
2.大小写转换
Lower、Upper
3.获取左右起始指定个数字符
Left、Right
4.返回读音相似值(对英文效果)
Soundex
5.日期格式:date_format
eg:
1.where Date(sub_time) = ‘-03-01’ # 年月日
2.where Year(sub_time)= AND Month(sub_time)=07; # year 年 month 月
九、索引
索引就是一种数据结构,创建索引在查找数据时查询速度会变快,相对的增改删速度会变慢,因为每次增改删都会重建索引。
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构
primary key 主键unique key 唯一键index key 索引键
上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一,unique key:唯一),而index key没有任何约束功能只会帮你加速查询
CREATE TABLE 表名(……INDEX|KEY [索引名](列名)); #创建表的同时创建索引。使用 INDEX 或者 KEY,索引名可以省略。根据先装数据,后建索引的原则,所以一般不建议在创建表的同时创建索引。CREATE INDEX 索引名 ON 表名(列名);或者ALTER TABLE 表名 ADD INDEX|KEY [索引名](列 名);#在已经存在的表上创建索引# 删除索引DROP INDEX 索引名 ON 表名;#或者ALTER TABLE 表名 DROP INDEX|KEY 索引名;# 显示该表的索引信息SHOW INDEX FROM 表名;# 或者SHOW KEYS FROM 表名;
数据库——python操作MySQL 修改表SQL语句 视图 触发器 存储过程 事务 流程控制 常见函数 索引