Python 17 数据库 其他操作

视图、事务、索引、用户管理

2.7 数据库其它操作

学习目标

  1. 能够说出视图的作用

  2. 能够说出索引的作用

  3. 能够说出事务的作用

--------------------------------------------------------------------------------

2.7.1 视图

视图是一种基于查询结果产生的虚拟表。

视图总结：

  视图封装了对多张基本表的复杂操作，简化用户操作

  视图只是一个虚表,并不存储任何基本表的表数据,当用户使用视图的时候 视图会从基本表中取出

  通过视图可以对用户展示指定字段从而屏蔽其他字段数据，更加安全

<1>为什么要有视图？

当在执行查询操作时，经常会出现查询频率高并且查询语句非常复杂的情况。 每次都要将复杂的SQL语句重新书写，非常不便。

如果能将这个查询的结果保存下来，下次在使用时，直接使用结果就非常完美了。这个需求通过视图就可以完成。

<2>视图的特性

  1>视图是一种基于查询结果产生的虚拟表，实际它就是一条被封装起来的 SQL 查询语句。

  2>在使用视图时，就相当执行了被封装的复杂SQL查询语句。

  3>视图不存储具体的数据。

  4>视图的基本表发生变化，那么视图也随之变化

<3>增--->定义视图

  视图在定义时，建议视图名称以 v_xxx 形式命名，以便和普通的表区分。

  语法：create view 视图名称 as select语句;

      create view v_goods_info as select goods.name as gname,goods_cates.name as gcname,goods_brands.name as gbname from goods inner join goods_cates on goods.cate_id = goods_cates.id inner join goods_brands on goods.brand_id = goods_brands.id;

<4>查看视图

  视图以表的形式体现，通过 show tables 即可查看视图。

<5>使用视图

  视图的作用就是用来查询，视图的作用类似将子查询做了封装。

  语法：select 字段名[,(字段名…)] from 视图名称;

      select * from 视图名称;

      select * from v_goods_info;

<6>删除视图

  语法： drop view 视图名称

      drop view v_goods_info;

2.7.2 事务

<1>什么是事务？

  事务Transaction，是指作为一个基本工作单元执行的一系列SQL语句的操作，要么完全地执行，要么完全地都不执行。

<2>为什么要有事务？

事务总结：

  事务的存在是解决数据在操作过程中的 ACID 问题。

以下内容出自《高性能MySQL》第三版，了解事务的ACID有助于我们更好的理解事务运作。 下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

> 1. 检查支票账户的余额高于或者等于200美元。

> 2. 从支票账户余额中减去200美元。

> 3. 在储蓄帐户余额中增加200美元。

上述所有步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交将修改的数据持久保存，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

> 1. start transaction;

> 2. select balance from checking where customer_id = 10233276;

> 3. update checking set balance = balance - 200.00 where customer_id = 10233276;

> 4. update savings set balance = balance + 200.00 where customer_id = 10233276;

> 5. commit;

<3>事务的四大特性 ACID

  1>原子性(Atomicity)

      一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

  2>一致性(Consistency)

      数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

  3>隔离性(Isolation)

      通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

  4>持久性(Durability)

      一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

<4>事务操作

MySQL使用的InnoDB引擎支持事务操作。(ENGINE=InnoDB)

  1>开启事务

      开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

      语句：begin; 或 start transaction;

  2>提交事务

      将缓存中的数据变更维护到物理表中

      语句：commit;

  3>回滚事务

      放弃缓存中变更的数据 表示事务执行失败 应该回到开始事务前的状态

      语句：rollback;

<5>验证事务提交

为了查看效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表。

step 1:

两个终端同时连接同一个数据库并查询同一张表

`select * from goods_cates;`

step 2:

终端1开启事务，插入数据，查看

```sql

begin;

insert into goods_cates(name) values('手机')

select * from goods_cates;  -- 插入的数据显示插入成功了

```

setp 3：

终端2查询数据

```sql

select * from goods_cates; -- 并没有看到新数据，因为终端1的操作缓存在本地，还没有提交。隔离性

```

step 4：

终端1提交数据,并查询

```sql

commit; -- 提交后，事务完成，原子性操作结束

select * from goods_cates;

```

step 5:

终端2查询

```sql

select * from goods_cates; -- 可以查到新数据，一致性，持久性。

```

<6>验证事务回滚

为了查看效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表。

step 1:

两个终端同时连接同一个数据库并查询同一张表

`select * from goods_cates;`

step 2:

终端1开启事务，删除所有数据，查看

```sql

begin;

insert into goods_cates(name) values('HIFI播放器');

select * from goods_cates;  -- 插入的数据显示插入成功了

```

setp 3：

终端2查询数据

```sql

select * from goods_cates; -- 并没有看到新数据，因为终端1的操作缓存在本地，还没有提交。隔离性

```

step 4：

终端1因为各种原因不想或不能提交数据，取消之前的操作

```sql

rollback;

```

step 5:

终端2查询

```sql

select * from goods_cates; -- 没有查到新数据，一致性，持久性。

```

2.7.3 索引

索引总结：

  1>索引可以明显提高某些字段的查询效率。

  2>但不是所有的表都需要建立索引

  3>如果表中数据很少，没有必要建立索引

  4>如果一个表中的数据增删很频繁，不能建立索引 ，因为只要数据发生增减，索引就要重新建立。增加了系统开销，反而慢了。

  5>索引只适合查询操作频繁的表。

<1>什么是索引？

  能够快速查询数据的线索就称之为索引。

<2>为什么需要索引？

思考: 如何在一个图书馆中找到一本书的？

在图书馆中如果没有其他辅助手段只能一条道走到黑，一本书->一本书的扫。 终于经过1个小时的连续扫描发现你需要看的那本书在一分钟之前被人借走了。这种就是顺序查找。 图书馆管理员发现这个问题，于是决定减少这样的(＞﹏＜)悲剧故事。 为同学们购置了一套图书馆管理系统。 大家要找书籍先在系统上查找到书籍所在的房屋编号和货架编号，然后就可以直接大摇大摆的去取书了。 这个房屋编号和货架编号就是索引。

<3>索引目的

  索引的目的在于提高查询效率。

<4>索引原理

  通过不断的缩小想要获得数据的范围来筛选出最终想要的结果。

<5>索引的使用(增、删、查)

  1>查看表中已有索引

      show index from 表名

  2>创建索引

      create index 索引名称 on 表名(字段名称(长度))`

          ■ 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致

          ■ 字段类型如果不是字符串，可以不填写长度部分

  3>删除索引：

      drop index 索引名称 on 表名;

<6>验证索引的效率

创建一个新表（不带索引）

```sql

create table test_index(title varchar(10));

```

准备10万条数据

```python

from pymysql import connect

def main():

    # 创建Connection连接

    conn = connect(host='localhost',port=3306,database='jddb',user='root',password='123123',charset='utf8')

    # 获得Cursor对象

    cursor = conn.cursor()

    # 插入10万次数据

    for i in range(100000):

        cursor.execute("insert into test_index values('ha-%d')" % i)

    # 提交数据

    conn.commit()

if __name__ == "__main__":

    main()

```

查询验证

    开启运行时间监测

    `set profiling=1;`

    - 查询 ha-99999 的数据

    `select * from test_index where title='ha-99999;`

    - 为表 test_index 创建索引

    `create index title_index on test_index(title(10));`

    -  再次查询 ha-99999 的数据

    `select * from test_index where title='ha-99999;`

    - 查看执行时间

    `show profiles;`

    <img src='images/118.png'>

    - 使用 desc 命令也可以查看索引的效率

    ` desc select * from test_index where title='ha-99999';`

    <img src='images/119.png'>

2.7.4 用户管理

root用户是数据库系统中的超级管理员，可以对数据库做任何操作。root用户一般会由DBA(数据库管理员)或运维人员统一管理。

给开发人员针对项目的数据库建立专门的用户来操作。

<1>查看所有用户

MySQL中所有的用户及权限信息都存储在MySQL数据库的user表中。

  通过 desc user; 可以查看user表的结构

      主要字段：

          ■ Host 允许访问的主机地址  localhost 为本机， % 为任何主机

          ■ User 用户名

          ■ authentication_string    (身份验证-字符串)密码，保存的是加密后的值

  查看所有的用户

      select host ,user,authentication_string from user;

<2>创建帐户、授权

注意：用户管理操作的权限 都是管理员来操作的，只能使用 root 用户操作。

  常用权限： create、alter、drop、insert、update、delete、select

  全部权限： all privileges

  语法：grant 权限列表 on 数据库.表名(或者*.*或数据库.*或者直接表名(当前正在使用的数据库下的表)) to '用户名'@'访问主机' identified by '密码';

      多个权限用,隔开

      创建账户并授予查询权限

      如果是给一个数据库下的多个表（又不是全部表）授权，需要授权多次，每次数据库.表名或者直接表名

      加入testuser用户，密码123123，只能对jddb中的表做查询操作

      grant select on jddb.* to 'testuser'@'localhost' identified by '123123';

  验证用户权限

<3>查看权限

  语法： show grants for 用户名@主机地址

      show grants for testuser@localhost;

<4>修改权限

  语法：grant 权限名称 on 数据库 to 账户@主机 with grant option;

      - 将testuser权限改为对所有库有所有权限

      grant all privileges on *.* to 'testuser'@'localhost' with grant option;

      - 刷新，让修改权限的操作即时生效

      flush privileges;

<5>删除用户

  语法：drop user 用户名@主机地址 drop user 'testuser'@'localhost';

<6>撤销用户权限revoke

  撤销已经赋予给 MySQL 用户权限的权限。 revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

  revoke 权限名称 on 数据库 from 账户@主机;

<7>用户管理小结：

用户管理是MySQL当中一块非常重要的内容，它涉及到了数据的安全问题。 但是对于开发人员来讲，这块内容在实际工作中涉及又不多，因为数据库的维护会有专门的运维人员或DBA来完成。 一般在操作数据时，不会使用root用户。DBA会针对不同项目需求给你专门创建用户，甚至是视图。

数据库高级部分的内容，大家主要做一个了解即可。当遇到问题时，要知道如何解决。或者可以快速找到解决办法。 对于数据库编程。对表的CURD才是重要的。

2.8 主从配置（了解 ）

2.8.1 学习目标

  能够说出主从配置的意义

2.8.2 主从配置

为什么需要主从配置 一台计算机，不管是从硬件上还是软件上都可能产生崩溃。一旦作为提供数据服务的数据库服务器崩溃了，那么整个后台系统将会陷入停滞的状态而无法继续执行，对公司的业务产生停业的影响。

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器

（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文

件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

  1>提供服务可用性

  2>通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

  3>提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据

  4>通过主从服务器，实现读写分离，负载均衡

2.8.2 主从同步的机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

2.8.3 备份恢复-配置主从需要了解的一个小知识

  <1>备份

      运行mysqldump命令

      mysqldump –uroot –p 数据库名 > python.sql;

          ■ # 按提示输入mysql的密码

  <2>恢复

      连接mysql，创建新的数据库

      退出连接，执行如下命令

      mysql -uroot –p 新数据库名 < python.sql

          ■ # 根据提示输入mysql密码