MySQL数据库

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认为必须检索的用来返回请求数据的行数

SQL优化

尽量选择数据类型占空间少,在where ,group by,order by中出现的频率高的字段建立索引
尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段
少使用like %查询,否则会全表扫描
子查询优化为join查询
控制使用自定义函数
单条查询最后添加 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;

表的拆分

垂直拆分 : 表中列太多,分为多个表,每个表是其中的几个列。将常查询的放到一起,blob或者text类型字段放到另一个表

水平拆分 : 减少每个表的数据量,通过hash key进行划分然后拆成多个表

数据库备份和用户管理

表的复制

1、表能根据实际需求复制数据
2、复制表时不会把KEY属性复制过来

语法

create table 表名 select 查询命令;

--  表复制
create  table hobby select s.name,s.age,i.hobby from cls as s,interest as i where s.name=i.name;

数据备份

1. 备份命令格式

mysqldump -u用户名 -p 源库名 > ~/stu.sql

–all-databases 备份所有库
db_name 备份单个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份指定库的多张表

2. 恢复命令格式
   mysql -uroot -p 目标库名 < stu.sql

-- 不是sql语句，在命令行执行
导出
mysqldump -u root -p stu > stu.sql

导入
mysql -u root -p student < stu.sql

用户权限管理

开启MySQL远程连接

更改配置文件,重启服务!
1.sudo su

2.cd /etc/mysql/mysql.conf.d
3.cp mysqld.cnf mysqld.cnf.bak
4.vi mysqld.cnf #找到44行左右,加 # 注释
#bind-address = 127.0.0.1
[mysqld]
character_set_server = utf8
5.保存退出
6.service mysql restart
7.修改用户表host值
use mysql;
update user set host=’%’ where user=‘root’;
8.刷新权限
flush privileges;

添加授权用户

1. 用root用户登录mysql
   mysql -uroot -p123456
2. 添加用户 % 表示自动选择可用IP
   CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
3. 授权
   grant 权限列表 on 库.表 to “用户名”@"%" identified by “密码” with grant option;
4. 刷新权限
   flush privileges;

权限列表

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,对db2库中所有表有所有权限
   mysql>grant all privileges on books.* to ‘duty’@’%’ identified by ‘123’ with grant option;
   mysql>flush privileges;

-- 用户管理
delete from mysql.user where user='zs';

pymysql模块

pymysql安装

sudo pip3 install pymysql

pymysql使用流程

1. 建立数据库连接(db = pymysql.connect(…))
2. 创建游标对象(cur = db.cursor())
3. 游标方法: cur.execute(“insert …”)
4. 提交到数据库或者获取数据 : db.commit()/db.fetchall()
5. 关闭游标对象 :cur.close()
6. 断开数据库连接 :db.close()

常用函数

参考代码 day16/mysql.py

参考代码 day16/read_db.py

参考代码 day16/write_db.py

db = pymysql.connect(参数列表)

host :主机地址,本地 localhost
port :端口号,默认3306
user :用户名
password :密码
database :库
charset :编码方式,推荐使用 utf8

数据库连接对象(db)的方法

cur = db.cursor() 返回游标对象,用于执行具体SQL命令
db.commit() 提交到数据库执行
db.rollback() 回滚,用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接

游标对象(cur)的方法

cur.execute(sql命令,[列表]) 执行SQL命令
cur.executemany(sql命令,[data]) 根据数据列表项多次执行SQL命令,一般用于写操作。
cur.fetchone() 获取查询结果集的第一条数据,查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录,返回结果为元组嵌套元组, ((记录1),(记录2))。
cur.fetchall() 获取所有查找到的记录,返回结果形式同上。
cur.close() 关闭游标对象

"""
mysql.py
pymysql 数据库操作流程
"""
import pymysql

# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生产游标对象 （操作数据库执行sql语句获取结果的对象）
cur = db.cursor()

# 利用游标对象执行各种sql语句
# 读操作 --》 fetch
# 写操作 --》 commit  rollback

# 关闭游标和数据库
cur.close()
db.close()

"""
mysql.py
pymysql 数据库读操作
"""
import pymysql

# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生产游标对象 （操作数据库执行sql语句获取结果的对象）
cur = db.cursor()

# 利用游标对象执行读操作 select语句
# 读操作 --》 fetch
name = input("Name:")

# 组合sql语句
# sql="select name,hobby from interest where name='%s';"%name
# print(sql)
# cur.execute(sql)

# 通过execute第二个参数为sql语句传递参量
sql="select name,age from cls where age>%s or sex=%s;"
cur.execute(sql,[17,'m'])


# 遍历游标对象获取查询记录
# for i in cur:
#     print(i)

# 获取一个查询结果
# print(cur.fetchone())

# 获取多个查询结果
# print(cur.fetchmany(2))

# 获取所有查询结果
print(cur.fetchall())

# 关闭游标和数据库
cur.close()
db.close()

"""
mysql.py
pymysql 数据库写操作
"""
import pymysql

# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生产游标对象 （操作数据库执行sql语句获取结果的对象）
cur = db.cursor()

# 利用游标对象执行写操作
# 写操作 --》 commit  rollback
try:
    # 插入操作
    # sql="insert into cls values (9,'Lily',17,'w','76');"
    # cur.execute(sql)
    # sql = "insert into cls values (%s,%s,%s,%s,%s);"
    # cur.execute(sql,[8,'Ala',18,'w',66])

    # 修改操作
    # sql = "update cls set score=84 where id=3;"
    # cur.execute(sql)

    # 删除操作
    # sql = "delete from cls where id=2;"
    # cur.execute(sql)

    # 通过executemany执行大量的sql语句
    list_ = [(17,1),(18,3),(19,4)]
    sql = "update cls set score=score-5,age=%s where id=%s;"
    # for i in list_:
    #     cur.execute(sql,[i])
    cur.executemany(sql,list_)


    db.commit() # 提交结果 立即刷新缓冲区将数据写入数据库
except Exception as e:
    print(e)
    db.rollback() # 回滚 没有写入数据库的操作召回


# 关闭游标和数据库
cur.close()
db.close()