day29:mysql事务，索引，视图，备份恢复，pymysql

事务

• 事务把⼀组操作看做⼀个整体，要不都操作成功，要不都操作失败。(ACID)
• 表的数据库引擎必须是innodb，innodb⽀持事物，myisam不⽀持事务
• 修改表引擎：alter table 表名engine = innodb

# 查询是否为⾃动提交
select @@autocommit (1为⾃动提交0为⼿动提交)
# 关闭⾃动提交
set autocommit = 0
# 开始事务
start transaction /begin
⼀组操作
commit/rollback
commit 提交会把数据写到硬盘  # 只有commit才会真正起作用
rollback 回滚撤销操作

授权管理

创建用户

create user '用户名'@'服务器地址' identified by "密码"

删除用户

drop user ‘用户名’@‘服务器地址’

修改密码

# mysql5.7之前的版本
# 修改当前登录⽤户
set password = password('123456');
#⼀般管理员可以修改任意⽤户密码
set password for 'db'@'localhost' = password('2333');

# mysql5.7之后的版本
use mysql;
update user set authentication_string=password('123'),plugin
='mysql_native_password' where user = root flush privileges
# 刷新
flush privileges 
 

索引

• 索引

索引是关系型数据库中⽤来提升查询性能最为重要的⼿段。关系型数据库中的索引
就像⼀本书的⽬录，我们可以想象⼀下，如果要从⼀本书中找出某个知识点，但是
这本书没有⽬录，这将是多么可怕的事情。创建索引虽然会带来存储空间上的开销，就像⼀本书的⽬录会占⽤⼀部分的篇幅⼀样，但是在牺牲空间后换来的查询时间的减少也是⾮常显著的。
MySQL中，所有数据类型的列都可以被索引，常⽤的存储引擎InnoDB和MyISAM
能⽀持每个表创建16个索引。InnoDB和MyISAM使⽤的索引其底层算法是Btree
（B树），B-tree是⼀种⾃平衡的树，类似于平衡⼆叉排序树，能够保持数据
有序。这种数据结构能够让查找数据、顺序访问、插⼊数据及删除的操作都在对数
时间内完成。

• 索引的优点

可以⼤⼤加快数据的检索速度
唯⼀索引可以保证数据的唯⼀性
可以降低分组、排序的时间
可以使⽤查询优化器提⾼系统性能

• 索引的缺点

• 建⽴索引会建⽴对应索引⽂件，占⽤⼤量空间
• 建⽴索引会降低增、删、改的效率

• 不建⽴索引

• 频繁更新的字段不要建⽴索引
• 没出现在where、having，不要建⽴索引
• 数据量少的表没有必要建⽴索引
• 唯⼀性⽐较差的字段不要建⽴索引

• 索引分类
  • 普通索引

  create index 索引名on 表名(字段asc/desc) 默认asc升序

  • 唯一索引(在唯⼀索引所在列不能有重复值，增加和修改会受影响)

  create unique index 索引名on 表名(字段asc/desc) 默认asc升序

  • 主键索引

  创建表，主键索引会⾃动添加，要求在主键上不能有重复值，不能有空值

  • 复合索引（联合索引）索引了多个列

  使⽤联合索引，必须包含左前缀（指第一个索引）。（a,b,c)
  a
  a,b
  a,b,c

• 全文索引
  ⼀般会⽤全⽂索引服务器(sphinx)，不会直接创建全⽂索引

create FULLTEXT index 索引名on 表名(字段asc/desc)

• 删除索引

drop index 索引名on 表

• 查看索引

show index from 表\G

• 查看sql性能

explain select sno,sname from student where class=‘1904’\G;

在上⾯的SQL执⾏计划中，有⼏项值得我们关注：
type：MySQL在表中找到满⾜条件的⾏的⽅式，也称为访问类型，包括：ALL（全
表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（⾮唯⼀索引扫
描）、eq_ref（唯⼀索引扫描）、const/system、NULL。在所有的访问类型中，
很显然ALL是性能最差的，它代表了全表扫描是指要扫描表中的每⼀⾏才能找到匹
配的⾏。
possible_keys：MySQL可以选择的索引，但是有可能不会使⽤。
key：MySQL真正使⽤的索引。
rows：执⾏查询需要扫描的⾏数，这是⼀个预估值。

• 其它创建索引的⽅式

alter table 表 add index(字段1,字段2,…)
alter table 表 add primary key(字段1,字段2,…)
alter table 表 add unique(字段1,字段2,…)
alter table 表 add fulltext(字段1,字段2,…)

• 不使⽤索引的情况

• 查询时的联合索引没有左前缀，不使⽤索引
• or条件⾥，如果⼀⽅字段没有索引，则不使⽤索引
• 类型不对应的不会使⽤索引
• like ‘%tom’ ,如果左边是通配符，不会使⽤索引
• 使⽤!=、<>、not in操作，不使⽤索引

视图

视图是关系型数据库中将⼀组查询指令构成的结果集组合成可查询的数据表的对
象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是⼀种实体结
构，⽽视图是⼀种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的
SQL语句。
使⽤视图可以获得以下好处：

1. 可以将实体数据表隐藏起来，让外部程序⽆法得知实际的数据结构，让访问者
   可以使⽤表的组成部分⽽不是整个表，降低数据库被攻击的⻛险。
2. 在⼤多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外
   部程序⽆法直接透过视图修改数据。
3. 重⽤SQL语句，将⾼度复杂的查询包装在视图表中，直接访问该视图即可取出
   需要的数据；也可以将视图视为数据表进⾏连接查询。
4. 视图可以返回与实体数据表不同格式的数据

创建视图
create view 视图名(字段列表) as select⼦句
删除视图
drop view 视图名

既然视图是⼀张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视
具体情况⽽定，以下类型的视图是不能更新的：

使⽤了聚合函数（SUM、MIN、MAX、AVG、COUNT等）、DISTINCT、
GROUP BY、HAVING、UNION或者UNION ALL的视图。
SELECT中包含了⼦查询的视图。
FROM⼦句中包含了⼀个不能更新的视图的视图。
WHERE⼦句的⼦查询引⽤了FROM⼦句中的表的视图。

数据库备份与恢复

• 备份

不⽤登录mysql，直接执⾏mysqldump命令,将指定数据库备份到家⽬录下的指定
⽂件
mysqldump –uroot –p 数据库名> 备份⽂件名.sql;

• 恢复

⾸先要创建⼀个mysql数据库，然后退出mysql，执⾏以下命令
mysql -uroot –p 数据库名< ~/备份⽂件.sql

pymysql操作数据库

• 操作步骤:

# ---coding:utf-8----
# 文件名： 0_模仿.py
# @Time：2020/3/25 16:14
import pymysql

conn = pymysql.connect(host= "120.24.*.*",
                       user="root",
                       password="******",
                       database="school",
                       port=3306,
                       charset="utf8"
                        )
# print(conn)

# 2.建立游标
cursor = conn.cursor()

# 返回的数据是[{}]
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 3.执行Sql
sql = "select sno,sname from student"
res = cursor.execute(sql)

print(res)

# 4.获取结果集
if res:
    # print(cursor.fetchone())
    print(cursor.fetchmany(5))
    # print(cursor.fetchall())

# 5.关闭连接和游标
cursor.close()
conn.close()

• 增加记录

# ---coding:utf-8----
# 文件名： 0_模仿.py
# @Time：2020/3/25 16:14
import pymysql

conn = pymysql.connect(host= "120.24.*.*",
                       user="root",
                       password="******",
                       database="school",
                       port=3306,
                       charset="utf8"
                        )
# print(conn)

# 2.建立游标
cursor = conn.cursor()

# 返回的数据是[{}]
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 3.执行Sql
try:
    sql = "insert into student(sno,sname,ssex) values('{}','{}','{}') "
    sql = sql.format("0016","马大脚",1)
    res = cursor.execute(sql)
    conn.commit()
    print(res)
except Exception as e:
    print(e)
    conn.rollback() #回滚

# 关闭连接和游标
cursor.close()
conn.close()

• 防止sql注入
  有注入漏洞的代码

import pymysql

conn = pymysql.connect(host= "120.24.*.*",
                       user="root",
                       password="******",
                       database="school",
                       port=3306,
                       charset="utf8"
                        )

# 返回的数据是[{}]
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 执行Sql
sno =input("请输入学号:")
sname = input("请输入姓名:")
sql = "select * from student where sno='{}' and sname='{}'" #要用单引号
sql = sql.format(sno,sname)
print(sql)
res = cursor.execute(sql)
print(cursor.fetchall())
print(res)

conn.rollback()
cursor.close()
conn.close()

执行:
请输入学号:adsf
请输入姓名:asd’ or '5
结果:会将表中所有记录查询输出
因为等于执行select * from student where sno=‘adsf’ and sname=‘asd’ or ‘5’

防注入:
方法一:使用cursor.execute(参数)

import pymysql

conn = pymysql.connect(host= "120.24.144.212",
                       user="root",
                       password="520ljx",
                       database="school",
                       port=3306,
                       charset="utf8"
                        )
# 返回的数据是[{}]
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行Sql
sno =input("请输入学号:")
sname = input("请输入姓名:")
#sql = "select * from student where sno='{}' and sname='{}'"
sql = "select * from student where sno=%s and sname=%s"  # 用本句代替上一句
# sql = sql.format(sno,sname)  #旧的去掉
print(sql)
res = cursor.execute(sql,[sno,sname])   # execute的参数
# res = cursor.execute(sql,%(sno,sname))#旧的去掉

print(cursor.fetchall())
print(res)

# print(res)
conn.rollback()

cursor.close()
conn.close()

方法二 使用

import pymysql

conn = pymysql.connect(host= "120.24.*.*",
                       user="root",
                       password="******",
                       database="school",
                       port=3306,
                       charset="utf8"
                        )

# 返回的数据是[{}]
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 执行Sql
sno =input("请输入学号:")
sname = input("请输入姓名:")
sql = "select * from student where sno='{}' and sname='{}'" #要用单引号
# sql = sql.format(sno,sname)
sql = sql.format(pymysql.escape_string(sno),pymysql.escape_string(sname))
# 上句改成本句,把要查询的字符进行转义
print(sql)
res = cursor.execute(sql)
print(cursor.fetchall())
print(res)

conn.rollback()
cursor.close()
conn.close()