2021-05-11 第二阶段day28

最新推荐文章于 2024-08-22 10:10:35 发布

Sunrise.99

最新推荐文章于 2024-08-22 10:10:35 发布

阅读量450

点赞数

文章标签：数据库 python

本文链接：https://blog.csdn.net/weixin_44840038/article/details/116665200

版权

day28

一、事务隔离级别
1、读未提交read uncommitted
2、读已提交read committed
3、可重复读repeatable read(Mysql的级别)
4、串行化serializable
二、数据库优化
三、数据库安全和管理
四、pymysql模块

一、事务隔离级别

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

Sunrise.99

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2021-05-11 第二阶段day28

day28一、事务隔离级别1、读未提交read uncommitted2、读已提交read committed3、可重复读repeatable read(Mysql的级别)4、串行化serializable二、数据库优化1、范式（1）第一范式1NF（2）第二范式2NF（3）第三范式3NF（4）巴斯-科德范式BCNF（5）第四范式4NF（6）第五范式5NF（完美范式）2、MySQL存储引擎（1）常用引擎（2）操作3、字段数据类型和键的选择4、explain语句模拟优化器5、SQL优化6、表的拆分三、数据库安
复制链接

扫一扫