day28
一、事务隔离级别
1、读未提交read uncommitted
事务B能看到事务A还没有提交的修改,会产生脏数据
2、读已提交read committed
事务B能看到事务A已提交的数据
3、可重复读repeatable read(Mysql的级别)
事务B看不到事务A已提交的数据。
4、串行化serializable
事务A在操作数据库时,事务B只能排队等待,可避免“幻像读”。
二、数据库优化
1、范式
(1)第一范式1NF
要求数据库中的表示是二维表,每个数据元素不可再分,不能是集合,数组,记录等组合的数据项。
(2)第二范式2NF
要求数据库表中的每个实例或记录必须可以被唯一地区分,所有属性依赖于主属性。
(3)第三范式3NF
属性不传递依赖,即每个属性不依赖其他非主属性。合理设计外键,只关联主表的主键。
(4)巴斯-科德范式BCNF
(5)第四范式4NF
(6)第五范式5NF(完美范式)
2、MySQL存储引擎
mysql数据库管理系统中用来处理表的处理器。
(1)常用引擎
- InnoDB:支持行级锁,外键,事务,事务回滚。表字段和索引存储在一个文件中,表结构在另一个文件中。(适用于查操作多时)
- MyISAM:表级锁,在锁定期间,其它进程无法对该表进行写操作。表字段和索引分开存储。(适用于写操作多时)
(2)操作
- 查看所有存储引擎
show engines;
- 查看已有表的存储引擎
show create table 表名;
- 创建表指定引擎
create table 表名(...) engine=MyISAM;
- 已有表指定引擎
alter table 表名 engine=InnoDB;
3、字段数据类型和键的选择
- 数据类型优先选择数字类型->时间日期类型->字符串类型
- 同一级别 占用空间小的->占用空间大的
- Innodb如果不设置主键也会自己设置隐含的主键,所以最好自己设置
- 尽量设置占用空间小的字段为主键
- 建立外键会自动建立索引,在表关联查询时建议使用外键子段作为关联条件
- 外键虽然可以保持数据完整性,但是会降低数据导入和操作效率,增加维护成本
4、explain语句 模拟优化器
可得到:
1、表的读取顺序
2、 数据读取操作的操作类型
3、 哪些索引可以使用
4、 哪些索引被实际使用
5、 表之间的引用
6、 每张表有多少行被优化器查询
eg:
explain select * from class where id <5;
5、SQL优化
- 尽量选择数据类型占空间少,在where ,group by,order by中出现的频率高的字段建立索引
- 尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段
- 尽量控制使用自定义函数
- 查询最后添加 LIMIT 会停止全表扫描
- 尽量避免 NULL 值判断,否则会进行全表扫描,默认值为空时可以用默认0代替
- 尽量避免 or 连接条件,否则会放弃索引进行全表扫描,可以用union代替
- 尽量避免使用 in 和 not in,否则会全表扫描,可以用between and代替
6、表的拆分
垂直拆分 : 表中列太多,分为多个表,每个表是其中的几个列。将常查询的放到一起,blob或者text类型字段放到另一个表。
水平拆分 : 减少每个表的数据量,通过关键字进行划分然后拆成多个表
三、数据库安全和管理
1、表的复制
复制表时不会把KEY属性复制过来。
语法:
create table 表名 select 查询命令;
eg:
create table student
select name,age,score from class
where score>=80;
2、数据库备份
- 备份命令格式
mysqldump -u 用户名 -p 源库名 > ~/stu.sql- 恢复命令格式
mysql -u root -p 目标库名 < stu.sql
eg:
mysqldump -u root -p student > stu.sql;
mysql -u root -p student < stu.sql;
3、mysql远程连接
(1)更改配置文件
1.cd /etc/mysql/mysql.conf.d
2.sudo vi mysqld.cnf
找到43行左右,加 # 注释
# bind-address = 127.0.0.1
3.保存退出 :wq
4.sudo service mysql restart
5.进入mysql修改用户表host值
use mysql;
update user set host='%' where user='root';
6.刷新权限
flush privileges;
(2)远程连接
mysql -h 主机号 -u 用户名 -p
4、添加用户和授权
(1)用root用户登录mysql
mysql -u root -p
(2)添加用户
% 表示自动选择可用IP
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
eg:
create user 'work'@'%' identified by "123";
(3)权限管理
# 增加权限
grant 权限列表 on 库.表 to "用户名"@"%" [identified by "密码"] with grant option;
# 删除权限
revoke insert,update,select on 库.表 from 'user'@'%';
权限列表:
- all privileges ,select ,insert ,update,delete,alter,create,drop等。
- 库.表 :
*.*
代表所有库的所有表
eg:
grant all privileges on *.* to 'work'@'%' with grant option;
revoke update,select on student.class from 'work'@'%';
(4)刷新权限
flush privileges;
(5)删除用户
drop user "用户名"@"%"
四、pymysql模块
1、安装pymysql模块
sudo pip3 install pymysql
2、pymysql使用流程
(1)建立数据库连接
db = pymysql.connect(参数列表)
参数详解:
- host :主机地址,本地 localhost port :端口号,默认3306
- user :用户名
- password :密码
- database :库
- charset :编码方式,推荐使用 utf8
eg:
kwargs = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "00000000",
"database": "school",
"charset": "utf8"
}
db = pymysql.connect(**kwargs)
(2)创建游标对象
游标:调用sql操作数据得到操作结果的对象
cur = db.cursor()
(3)游标方法
1. execute()
执行SQL命令
cur.execute(sql,args_list)
eg:
sql = "select name,age,score from class where score>80;"
cur.execute(sql) # 执行语句
2.executemany()
多次执行SQL命令,执行次数由列表中元组数量决定,一般用于写操作。
cur.executemany(sql,args_list)
eg:
data = [
("zhang",'19','w',70),
("wang",'18','m',71),
("li",'20','w',72),
]
try:
sql="insert into class (name,age,sex,score) " \
"values(%s,%s,%s,%s);"
cur.executemany(sql,data)
db.commit()
except Exception as e:
print(e)
db.rollback()
(4)提交到数据库或者获取数据
1.提交到数据库(写操作时)
db.commit() 提交到数据库执行,必须支持事务操作才有效
db.rollback() 回到原来的数据形态,必须支持事务操作才有效
2.读取数据库
1.fetchone()
获取查询结果集的第一条数据,查找到返回一个元组否则返回None。
注:fetch语句是连续读取的。即查询过一次后,下一次从下一数据开始读取。
cur.fetchone()
eg:
# 一个查询结果
one = cur.fetchone()
print(one)
2.fetchmany()
获取前n条查找到的记录,返回结果为元组嵌套元组, ((记录1),(记录2)),查询不到内容返回空元组。
cur.fetchmany(n)
eg:
# 多个查询结果
many = cur.fetchmany(2)
print(many)
3.fetchall()
获取所有查找到的记录,返回结果形式同上。
cur.fetchall()
eg:
# 所有查询结果(剩下的所有)
all = cur.fetchall()
print(all)
注:fetch语句是连续读取的。即查询过一次后,下一次从下一数据开始读取。
4.其他读取方式
迭代获取:
for row in cur:
print(row)
(5)关闭游标对象
cur.close()
(6)断开数据库连接
db.close()
3、读操作示例
import pymysql
kwargs = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "00000000",
"database": "school",
"charset": "utf8"
}
db = pymysql.connect(**kwargs)
cur = db.cursor()
# 不带参数
sql = "select name,age,score from class where score>80;"
cur.execute(sql) # 执行语句
# 带参数
# 使用第二个参数按照位置给sql语句%s传值
sql = "select name,age,score from class " \
"where score>%s and sex=%s;"
cur.execute(sql,[80,"m"]) # 执行语句
# 读操作
# 迭代读
for row in cur:
print(row)
# fetch读
all = cur.fetchall()
print(all)
# 关闭
cur.close()
db.close()
4、写操作示例
import pymysql
kwargs = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "00000000",
"database": "school",
"charset": "utf8"
}
db = pymysql.connect(**kwargs)
cur = db.cursor()
# 如果数据表支持事务会自动开启事务
# 需要commit提交才能生效
# 如果数据表不支持事务,则直接execute执行则生效
# 不带参数
try:
sql = "update class set score=99 where id=1;"
cur.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
# 带参数
data = [
("zhang",'19','w',70),
("wang",'18','m',71),
("li",'20','w',72),
]
try:
sql="insert into class (name,age,sex,score) " \
"values(%s,%s,%s,%s);"
cur.executemany(sql,data)
db.commit()
except Exception as e:
print(e)
db.rollback()
# 关闭
cur.close()
db.close()
5、数据库完整流程案例
'''
在school下创建一个数据表user 有三个字段 id user password 其中有若干条数据
编写一个函数,使用input分别输入用户名,密码
验证是否可以登录成功,如果可以则返回True,不可以返回False
'''
import pymysql
class User:
def __init__(self):
kwargs={
"host":"localhost",
"port":3306,
"user":"root",
"password":"00000000",
"database":"school",
"charset":"utf8"
}
self.db = pymysql.connect(**kwargs)
self.cur = self.db.cursor()
def close(self):
self.cur.close()
self.db.close()
# def login(self,user,pwd):
# sql = "select id,user,password from user;"
# self.cur.execute(sql)
# for row in self.cur:
# if row[1] == user and row[2] == pwd:
# return True
# return False
# 老师
def login(self,user,pwd):
sql = "select user from user where user=%s and password=%s;"
self.cur.execute(sql,[user,pwd])
if self.cur.fetchone():
return True
else:
return False
if __name__ == '__main__':
use = User()
user = input("请输入用户名:")
password = input("请输入密码:")
print(use.login(user,password))
use.close()