20.8.7 事务操作 事务的四大特性 隔离级别 数据库的优化 数据库设计范式 mysql储存引擎 字段数据类型选择 explain语句 表的拆分 数据库的备份和用户管理 pymysql使用流程
前情回顾
- 表的关联查询 (放在from后面)
表名.字段名
内连接
tb1 inner join tb2 on [条件]
外连接(左右)
tb1 left join tb2 on [条件]
tb1 right join tb2 on [条件]
-
视图
视图作用: 简化复杂的操作,
获取部分数据提供给使用者create view [viewName] as select…
drop view [if exists] [view]
-
函数和存储过程
函数: 有返回值 获取一个结果
存储过程: 没有返回值 注重执行过程delimiter xx;
declare n int;
set @arg = 10;create function func(uid int,…)
returns [return_type]
begin
函数体
return val;
endcreate procedure proc(in uid int,…)
begin
sql语句
enddrop function/procedure [name]
3.13 事务控制
3.13.1 事务概述
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。
定义
一件事从开始发生到结束的过程
作用
确保数据操作过程中的安全。
3.13.2 事务操作
开启事务
mysql>begin; # 方法1
开始执行事务中的若干条SQL命令（增删改）
终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。
mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!
注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。
3.13.3 事务四大特性
原子性（atomicity）
一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功，要么全部失败回滚
一致性（consistency）
事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。
隔离性（isolation）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
3.13.4 事务隔离级别
事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别
隔离级别
读未提交：read uncommitted
事物A和事物B，事物A未提交的数据，事物B可以读取到 这里读取到的数据叫做“脏数据” 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
读已提交：read committed
事物A和事物B，事物A提交的数据，事物B才能读取到 这种隔离级别高于读未提交 换句话说，对方事物提交之后的数据，我当前事物才能读取到 这种级别可以避免“脏数据” 这种隔离级别会导致“不可重复读取”
可重复读：repeatable read
事务A和事务B，事务A提交之后的数据，事务B读取不到 事务B是可重复读取数据 这种隔离级别高于读已提交 MySQL默认级别 虽然可以达到可重复读取，但是会导致“幻像读”
串行化：serializable
事务A和事务B，事务A在操作数据库时，事务B只能排队等待 这种隔离级别很少使用，吞吐量太低，用户体验差 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发
3.14 数据库优化
3.14.1 数据库设计范式
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。
范式简介：
第一范式： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。
例如： 在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。
第二范式： 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。
第三范式： 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。
3.14.2 MySQL存储引擎
定义： mysql数据库管理系统中用来处理表的处理器
基本操作
1、查看所有存储引擎
mysql> show engines;
2、查看已有表的存储引擎
mysql> show create table 表名;
3、创建表指定
create table 表名(...)engine=MyISAM;
4、已有表指定
alter table 表名 engine=InnoDB;
常用存储引擎特点
InnoDB
1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进 行操作。
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中
1. 表名.frm ：表结构
2. 表名.ibd : 表记录及索引文件
MyISAM
1. 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则 读也不允许
2. 表字段和索引分开存储
1. 表名.frm ：表结构
2. 表名.MYI : 索引文件(my index)
3. 表名.MYD : 表记录(my data)
如何选择存储引擎
1. 执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2. 执行写操作多的表用 InnoDB
CREATE TABLE tb_stu(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
sex varchar(2) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM;
3.14.3 字段数据类型选择
优先程度 数字 > 时间日期 > 字符串
同一级别 占用空间小的 > 占用空间多的
字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快
对数据存储精确不要求 float > decimal
如果很少被查询可以用 TIMESTAMP（时间戳实际是整形存储）
3.14.4 键的设置
Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
外键的设置用于保持数据完整性，但是会降低数据导入和操作效率，特别是高并发情况下，而且会增加维护成本
虽然高并发下不建议使用外键约束，但是在表关联时建议在关联键上建立索引，以提高查找速度
3.14.5 explain语句
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
explain select * from class_1 where id <5;
EXPLAIN主要字段解析：
table：显示这一行的数据是关于哪张表的
type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。
type中包含的值：
- system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
- ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
- range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
- ALL： 全表扫描，应该尽量避免
possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
key：实际使用的索引。如果为NULL，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
rows：MySQL认为必须检索的用来返回请求数据的行数
3.14.6 SQL优化
尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引
尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段
少使用like %查询，否则会全表扫描
控制使用自定义函数
单条查询最后添加 LIMIT 1，停止全表扫描
where子句中不使用 != ,否则放弃索引全表扫描
尽量避免 NULL 值判断,否则放弃索引全表扫描
优化前：select number from t1 where number is null;
优化后：select number from t1 where number=0;
在number列上设置默认值0,确保number列无NULL值
尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替
优化前：select id from t1 where id=10 or id=20;
优化后： select id from t1 where id=10 union all select id from t1 where id=20;
尽量避免使用 in 和 not in,否则会全表扫描
优化前：select id from t1 where id in(1,2,3,4);
优化后：select id from t1 where id between 1 and 4;
3.14.7 表的拆分
垂直拆分 ： 表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表
水平拆分 ： 减少每个表的数据量，通过关键字进行划分然后拆成多个表
3.15 数据库备份和用户管理
3.15.1 表的复制
表能根据实际需求复制数据
复制表时不会把KEY属性复制过来
语法
create table 表名 select 查询命令;
3.15.2 数据备份
备份命令格式
mysqldump -u 用户名 -p 源库名 > ~/stu.sql
恢复命令格式
mysql -u root -p 目标库名 < stu.sql
3.15.3 用户权限管理
开启MySQL远程连接
更改配置文件，重启服务！
1.cd /etc/mysql/mysql.conf.d
2.sudo vi mysqld.cnf 找到43行左右,加 # 注释
# bind-address = 127.0.0.1
3.保存退出
4.sudo service mysql restart
5.进入mysql修改用户表host值
use mysql;
update user set host='%' where user='root';
6.刷新权限
flush privileges;
添加授权用户
1. 用root用户登录mysql
mysql -u root -p
2. 添加用户 % 表示自动选择可用IP
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
3. 权限管理
# 增加权限
grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
# 删除权限
revoke insert,update,select on 库.表 from 'user'@'%';
4. 刷新权限
flush privileges;
5. 删除用户
drop user "用户名"@"%"
权限列表
all privileges 、select 、insert ，update，delete，alter等。
库.表 ： *.* 代表所有库的所有表
示例
1. 创建用户
mysql>create user 'work'@'%' identified by '123';
2. 添加授权用户work,密码123,对所有库的所有表有所有权限
mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
mysql>flush privileges;
3. 添加用户duty，密码123,对books库中所有表有查看，插入权限
mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
mysql>flush privileges;
4. 删除work用户的删除权限
mysql>revoke delete on *.* from "work"@"%";
5. 删除用户duty
drop user "duty"@"%";
3.16 pymysql模块
pymysql是一个第三方库，如果自己的计算机上没有可以在终端使用命令进行安装。
sudo pip3 install pymysql
pymysql使用流程
建立数据库连接(db = pymysql.connect(…))
创建游标对象(cur = db.cursor())
游标方法: cur.execute(“insert …”)
提交到数据库或者获取数据 : db.commit()/cur.fetchall()
关闭游标对象 ：cur.close()
断开数据库连接 ：db.close()
常用函数
db = pymysql.connect(参数列表)
功能: 链接数据库
host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8
cur = db.cursor()
功能： 创建游标
返回值：返回游标对象,用于执行具体SQL命令
cur.execute(sql,list_)
功能： 执行SQL命令
参数： sql sql语句
list_ 列表，用于给sql语句传递参量
cur.executemany(sql命令,list_)
功能： 多次执行SQL命令，执行次数由列表中元组数量决定
参数： sql sql语句
list_ 列表中包含元组 每个元组用于给sql语句传递参量，一般用于写操作。
cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))，查询不到内容返回空元组。
cur.fetchall() 获取所有查找到的记录，返回结果形式同上。
cur.close() 关闭游标对象
db.commit() 提交到数据库执行
db.rollback() 回滚，用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接
pymysql 连接数据库模板
import pymysql
# 连接数据库 (连接本机可以不写host和port)
db = pymysql.connect(host = "localhost",
port = 3306,
user = "root",
password = "123456",
database = "stu",
charset = "utf8"
)
# 创建游标 (执行sql语句获取执行结果的对象)
cur = db.cursor()
# 执行各种sql语句,操作数据库
print("假设执行了sql")
# 关闭游标和数据库连接
cur.close()
db.close()
数据库数据读取实例
import pymysql
# 连接数据库 (连接本机可以不写host和port)
db = pymysql.connect(host = "localhost",
port = 3306,
user = "root",
password = "123456",
database = "stu",
charset = "utf8"
)
# 创建游标 (执行sql语句获取执行结果的对象)
cur = db.cursor()
# 执行select语句
sql = "select name,age,score from cls;"
cur.execute(sql)
# cur在执行查询之后 可以通过迭代获取结果
for row in cur:
print(row)
# # 获取一条记录
# one = cur.fetchone()
# print(one)
#
# # 获取多条记录
# many = cur.fetchmany(2)
# print(many)
#
# # 获取所有条记录
# all = cur.fetchall()
# print(all)
# 关闭游标和数据库连接
cur.close()
db.close()
import pymysql
# 连接数据库 (连接本机可以不写host和port)
db = pymysql.connect(host = "localhost",
port = 3306,
user = "root",
password = "123456",
database = "stu",
charset = "utf8"
)
# 创建游标 (执行sql语句获取执行结果的对象)
cur = db.cursor()
# 执行select语句
sql = "select name,age,score from cls " \
"where score>%s and sex=%s;"
# 第二个参数列表可以为sql语句传递数据
cur.execute(sql,[80,'m'])
# cur在执行查询之后 可以通过迭代获取结果
for row in cur:
print(row)
# 关闭游标和数据库连接
cur.close()
db.close()
数据库的写操作
注意 : 如果数据表支持事务则使用commit rollback
提交或回滚.
如果不支持事务择直接生效
import pymysql
# 连接数据库 (连接本机可以不写host和port)
db = pymysql.connect(host = "localhost",
port = 3306,
user = "root",
password = "123456",
database = "stu",
charset = "utf8"
)
# 创建游标 (执行sql语句获取执行结果的对象)
cur = db.cursor()
# 写数据库 insert update delete
try:
# 可以多次执行sql一次commit
sql = "update cls set score=%s where id=%s;"
cur.execute(sql,[91,1])
sql = "delete from cls where score<60;"
cur.execute(sql)
# 提交写操作语句结果
db.commit()
except Exception as e:
print(e)
db.rollback() # 回滚 所有操作失效
# 关闭游标和数据库连接
cur.close()
db.close()
练习: 写一个函数input 输入一位学生的姓名
返回这个学生的分数.
学生信息存在cls表中
import pymysql
class Database:
def __init__(self):
# 连接数据库 (连接本机可以不写host和port)
self.db = pymysql.connect(host = "localhost",
port = 3306,
user = "root",
password = "123456",
database = "stu",
charset = "utf8"
)
# 创建游标 (执行sql语句获取执行结果的对象)
self.cur = self.db.cursor()
def close(self):
# 关闭游标和数据库连接
self.cur.close()
self.db.close()
def get_score(self):
name = input("Name:")
sql = "select score from cls where name=%s;"
# print(sql)
self.cur.execute(sql,[name])
# result --> (score,)
result = self.cur.fetchone()
return result[0]
if __name__ == '__main__':
db = Database()
print(db.get_score())
db.close()
文件存储
存储文件路径
优点：节省数据库空间，提取方便
缺点：文件或者数据库发生迁移会导致文件丢失
存储文件本身
优点：安全可靠，数据库在文件就在
缺点：占用数据库空间大，文件存取效率低
下一篇： 详解MongoDB中的日志模块