mysql-04-数据库高级

1.python链接数据库

import pymysql

# host 指的是数据库服务器的主机地址
# user 连接数据库的用户

# 打开一个数据库的连接，获取一个db对象
# db = pymysql.connet()  # 结果是一个Connection类型的对象
# db.cursor()   获取到一个cursor对象，用它来操作数据库

# with 语句上下文管理器，会调用对象的 __enter__ 方法，获取enter方法的执行结果
# with pymysql.connect() as x  # x的结果就是一个cursor对象

db = pymysql.connect(host='localhost',
                     user='root',
                     password='abcd1234',
                     database='python201',
                     port=3306,
                     charset='utf8')
cursor = db.cursor()
cursor.execute('select * from student')
cursor.close()
db.commit()
db.close()

for info in cursor.fetchall():
    print(info)

2.sql 注入

import pymysql, hashlib

username = input('请输入用户名:')
password = input('请输入密码:')

# 进行 md5 加密
h = hashlib.md5()
h.update(password.encode('utf8'))
password = h.hexdigest()

db = pymysql.connect(host='localhost',
                     user='root',
                     password='abcd1234',
                     database='python201',
                     port=3306,
                     charset='utf8')
cursor = db.cursor()

sql = 'select * from user where name="%s" and password="%s"'
print(sql)
# 执行sql语句
cursor.execute(sql, (username, password))

cursor.close()
db.commit()
db.close()

result = cursor.fetchone()
if not result:
    print('用户名或者密码错误')
else:
    print('欢迎回来{},您的信息是{}'.format(username, result))

数据库高级特性

一、存储引擎

存储引擎就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。

MySQL 默认支持多种存储引擎，以适用于不同领域 的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储。

查看当前的存储引擎

show variables like '%storage_engine';
show engines;

MySQL 常用的存储引擎

1. InnoDB

   事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。

   InnoDB主要特性有：

   1. InnoDB 给 MySQL 提供了具有提交、回滚、崩溃恢复能力的事务安全存储引擎。
   2. InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率比其他基于磁盘的关系型数据库引擎高。
   3. InnoDB 存储引擎自带缓冲池，可以将数据和索引缓存在内存中。
   4. InnoDB 支持外键完整性约束。
   5. InnoDB 被用在众多需要高性能的大型数据库站点上
   6. InnoDB 支持行级锁

2. MyISAM

   MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。

   MyISAM主要特性有：

   1. 大文件支持更好
   2. 当删除、更新、插入混用时，产生更少碎片。
   3. 每个 MyISAM 表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
   4. 最大的键长度是1000字节。
   5. BLOB和TEXT列可以被索引
   6. NULL 被允许在索引的列中，这个值占每个键的0~1个字节
   7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
   8. MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快
   9. 可以把数据文件和索引文件放在不同目录
   10. 每个字符列可以有不同的字符集
   11. 有 VARCHAR 的表可以固定或动态记录长度
   12. VARCHAR 和 CHAR 列可以多达 64KB
   13. 只支持表锁

1. MEMORY

   MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

存储引擎的选择

一般来说，对插入和并发性能要求较高的，或者需要外键，或者需要事务支持的情况下，需要选择 InnoDB，

插入较少，查询较多的场景，优先考虑 MyISAM。

使用引擎

一般在建表时添加

create table abc (
    name char(10)
) engine=MyISAM charset=utf8;
​
create table xyz (
    name char(10)
) engine=InnoDB charset=utf8;

InnoDB 和 MyISAM 在文件方面的区别

1. InnoDB 将一张表存储为两个文件

   • demo.frm -> 存储表的结构和索引
   • demo.ibd -> 存储数据，ibd 存储是有限的, 存储不足自动创建 ibd1, ibd2
   • InnoDB 的文件创建在对应的数据库中, 不能任意的移动

2. MyISAM 将一张表存储为三个文件

   • demo.frm -> 存储表的结构
   • demo.myd -> 存储数据
   • demo.myi -> 存储表的索引
   • MyISAM 的文件可以任意的移动

二、关系与外键

关系

• 一对一

  • 在 A 表中有一条记录，在 B 表中同样有唯一条记录相匹配
  • 比如: 学生表和成绩表

• 一对多 / 多对一

  • 在 A 表中有一条记录，在 B 表中有多条记录一直对应
  • 比如: 博客中的用户表和文章表

• 多对多

  • A 表中的一条记录有多条 B 表数据对应, 同样 B 表中一条数据在 A 表中也有多条与之对应
  • 比如: 博客中的收藏表

外键

外键是一种约束。他只是保证数据的一致性，并不能给系统性能带来任何好处。

建立外键时，都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查，如果不满足外键约束，则禁止数据的插入或修改，这必然带来一个问题，就是在数据量特别大的情况下，每一次约束检查必然导致性能的下降。

出于性能的考虑，如果我们的系统对性能要求较高，那么可以考虑在生产环境中不使用外键。

1. 构造数据

   -- 用户表
   create table `user` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null
   ) charset=utf8;
   ​
   -- 商品表
   create table `product` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null unique,
       `price` float
   ) charset=utf8;
   ​
   -- 用户信息表: 一对一
   create table `userinfo` (
       `id` int unsigned primary key auto_increment,
       `phone` int unsigned unique,
       `age` int unsigned,
       `location` varchar(128)
   ) charset=utf8;
   ​
   -- 用户组表: 一对多
   create table `group` (
       `id` int unsigned primary key auto_increment,
       `name` char(32) not null unique
   ) charset=utf8;
   ​
   -- 订单表: 多对多
   create table `order` (
       `id` int unsigned primary key auto_increment,
       `uid` int unsigned,
       `pid` int unsigned
   ) charset=utf8;

2. 添加外键

   -- 为 user 和 userinfo 建立关联的外键
   alter table userinfo add constraint fk_user_id foreign key(id) references user(id);
   ​
   -- 建立用户与组的外键约束
   alter table `user` add `gid` int unsigned;
   alter table `user` add constraint `fk_group_id` foreign key(`gid`) references `group`(`id`);
   ​
   -- 建立用户、商品、订单的外键约束
   alter table `order` add constraint `fk_user_id` foreign key(`uid`) references `user`(`id`);
   alter table `order` add constraint `fk_prod_id` foreign key(`pid`) references `product`(`id`);

3. 尝试插入数据后在删除，分别先对主表和子表进行一次删除。

4. 删除外键。

alter table `表名` drop foreign key `外键名`

数据库事务及其他

一、事务

1. 事务简介

事务主要用于处理操作量大、复杂度高、并且关联性强的数据。

比如说, 在人员管理系统中, 你删除一个人员, 你即需要删除人员的基本资料, 也要删除和该人员相关的信息, 如信箱, 文章等等, 这样, 这些数据库操作语句就构成一个事务！

在 MySQL 中只有 Innodb 存储引擎支持事务。

事务处理可以用来维护数据库的完整性, 保证成批的 SQL 语句要么全部执行, 要么全部不执行。主要针对 insert, update, delete 语句而设置。

2. 事务四大特性

在写入或更新资料的过程中, 为保证事务 (transaction) 是正确可靠的, 所必须具备的四个特性 (ACID)：

1. 原子性 (Atomicity) ：

   • 事务中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节。
   • 事务在执行过程中发生错误, 会被回滚 (Rollback) 到事务开始前的状态, 就像这个事务从来没有执行过一样。

2. 一致性 (Consistency)：

   在事务开始之前和事务结束以后, 数据库的完整性没有被破坏。 这表示写入的资料必须完全符合所有的预设规则, 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

3. 隔离性 (Isolation)：

   并发事务之间互相影响的程度，比如一个事务会不会读取到另一个未提交的事务修改的数据。在事务并发操作时，可能出现的问题有：            脏读：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。                 不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这回导致锁竞争加剧，影响性能。另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。             幻读：在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。

   1. 读取未提交 (Read uncommitted)

      • 所有事务都可以看到其他未提交事务的执行结果
      • 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少
      • 该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据

   2. 读提交 (read committed)

      • 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）

      • 它满足了隔离的简单定义：一个事务只能看见已经提交事务做的改变

      • 这种隔离级别出现的问题是: 不可重复读(Nonrepeatable Read)：

        不可重复读意味着我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果。

        导致这种情况的原因可能有：

        • 有一个交叉的事务有新的commit，导致了数据的改变;
        • 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

   3. 可重复读 (repeatable read)

      • 这是MySQL的默认事务隔离级别
      • 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
      • 此级别可能出现的问题: 幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行
      • InnoDB 通过多版本并发控制 (MVCC，Multiversion Concurrency Control) 机制解决幻读问题；
      • InnoDB 还通过间隙锁解决幻读问题

   4. 串行化 (Serializable)

      • 这是最高的隔离级别
      • 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。MySQL锁总结
      • 在这个级别，可能导致大量的超时现象和锁竞争

4. 持久性 (Durability)：

   事务处理结束后, 对数据的修改就是永久的, 即便系统故障也不会丢失。

3. 语法与使用

• 开启事务: BEGIN或START TRANSACTION

• 提交事务: COMMIT, 提交会让所有修改生效

• 回滚: ROLLBACK, 撤销正在进行的所有未提交的修改

• 创建保存点: SAVEPOINT identifier

• 删除保存点: RELEASE SAVEPOINT identifier

• 把事务回滚到保存点: ROLLBACK TO identifier

• 查询事务的隔离级别: show variables like '%isolation%';

• 设置事务的隔离级别: SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

  InnoDB 提供的隔离级别有

  • READ
  • UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

4. 示例

create table `abc` (
    id int unsigned primary key auto_increment,
    name varchar(32) unique,
    age int unsigned
) charset=utf8;
​
begin;
insert into abc (name, age) values ('aa', 11);
insert into abc (name, age) values ('bb', 22);
-- 在事务中查看一下数据
-- 同时另开一个窗口，连接到 MySQL 查看一下数据是否一样
select * from abc;
commit;
​
begin;
insert into abc (name, age) values ('cc', 33);
insert into abc (name, age) values ('dd', 44);
update abc set age=77 where name='aa';
-- 在事务中查看一下数据
select * from abc;
rollback;
​
select * from abc;  -- 事务结束后在查看一下数据

二、存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1. 优点

   • 存储过程可封装，并隐藏复杂的商业逻辑。
   • 存储过程可以回传值，并可以接受参数。
   • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
   • 存储过程可以用在数据检验，强制实行商业逻辑等。

2. 缺点

   • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
   • 存储过程的性能调校与撰写，受限于各种数据库系统。

语法

1. 声明语句结束符，可以自定义:

   存储过程中有很多的SQL语句，SQL语句的后面为了保证语法结构必须要有分号（;），但是默认情况下分号表示客户端代码发送到服务器执行。必须更改结束符

   DELIMITER $$

   -- 或者

   DELIMITER //

2. 声明存储过程:

   CREATE PROCEDURE demo_in_parameter(IN p_in int)

3. 存储过程开始和结束符号:

   BEGIN .... END

4. 变量赋值:

   SET @p_in=1

5. 变量定义:

   DECLARE l_int int unsigned default 4000000;

6. 创建mysql存储过程、存储函数:

   create procedure 存储过程名(参数)

7. 存储过程体:

   create function 存储函数名(参数)

使用

1. 简单用法

   -- 定义
   -- 如果存储过程中就一条SQL语句，begin…end两个关键字可以省略
   create procedure get_info()
   select * from student;
   -- 调用
   call get_info();

2. 复杂一点的 （备注：只能在标准 mysql 客户端中执行，mycli 无法识别）

   delimiter // -- 定义前，将分隔符改成 //
   create procedure foo(in uid int)
   begin
   select * from student where `id`=uid;
   update student set `city`='北京' where `id`=uid;
   end//
   delimiter ;  -- 定义完以后可以将分隔符改回 分号
   call foo(3);

3. 查看存储过程

   show procedure status like "%foo%";
   show create procedure foo;

4. 删除存储过程

   drop procedure foo;

延伸阅读

• 大型系统必须得要存储过程和触发器吗？ - 知乎
• 为什么大家都不推荐使用MySQL触发器而用存储过程？ - SegmentFault 思否

三、Python操作

1. 安装: pip install pymysql

2. 使用

   import pymysql
   db = pymysql.connect(host='localhost',
                        user='user',
                        password='passwd',
                        db='db',
                        charset='utf8')
   ​
   try:
       with db.cursor() as cursor:
           # 插入
           sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
           cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
       # 需要手动提交才会执行
       db.commit()
   ​
       with db.cursor() as cursor:
           # 读取记录
           sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
           cursor.execute(sql, ('webmaster@python.org',))
           result = cursor.fetchone()
           print(result)
   finally:
       db.close()

四、sql注入

什么是SQL注入？

SQL注入（SQLi）是一种注入攻击，，可以执行恶意SQL语句。它通过将任意SQL代码插入数据库查询，使攻击者能够完全控制Web应用程序后面的数据库服务器。攻击者可以使用SQL注入漏洞绕过应用程序安全措施；可以绕过网页或Web应用程序的身份验证和授权，并检索整个SQL数据库的内容；还可以使用SQL注入来添加，修改和删除数据库中的记录。

思考：下面的代码有没有问题？

import pymysql
db = pymysql.connect(host='localhost',user='root',password='abcd1234',db='test',charset='utf8')
​
name=input('请输入用户名:')
password=input('请输入密码:')
try:
    with db.cursor() as cursor:
        sql = 'select * from user where name="%s" and password="%s"' %(name,password)
        print(sql)
        cursor.execute(sql)
        print(cursor.fetchone())
    db.commit()
finally:
    db.close()

五、数据备份与恢复

1. 备份

   mysqldump -h localhost -u root -p dbname > dbname.sql

2. 恢复

   mysql -h localhost -u root -p123456 dbname < ./dbname.sql