数据库——python操作MySQL、修改表SQL语句、视图、触发器、存储过程、事务、流程控制、常见函数、索引

---

一、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',  # 密码  也可以简写 passwd
      database='jp04_3',  # 库名称  也可以简写 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')  # 相对于起始位置往后移动一个单位

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)
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;

5.临时修改SQL语句的结束符

delimiter $$

---

三、视图

1.视图的概念

通过SQL语句的执行得到的一张虚拟表 保存下来之后就称之为'视图'

2.视图的作用

1. 如果需要频繁的使用一张虚拟表 可以考虑制作成视图 降低操作难度
2. 视图虽然看似很好用 但是会造成表的混乱 毕竟视图不是真正的数据源
3. 视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)

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 DO
  SELECT 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) = ‘2015-03-01’ # 年月日
2.where Year(sub_time)=2016 AND Month(sub_time)=07; # year 年 month 月

九、索引

索引就是一种数据结构，创建索引在查找数据时查询速度会变快，相对的增改删速度会变慢，因为每次增改删都会重建索引。

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构

1. primary key 主键
2. unique key 唯一键
3. 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 表名;