MySQL（6）--数据库的设计、事务与视图

MySQL

数据库的设计

1.多表之间的关系

• 一对一（了解）

  如：人和身份证

  分析：一个人只有一个身份证，一个身份证只能对应一个人

• 一对多（多对一）

  如：部门和员工

  分析：一个部门有多个员工，一个员工只能对应一个部门

• 多对多

  如：学生和课程

  分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

2.实现关系

• 一对多（多对一）

  如：部门和员工

  实现方式：在多的一方建立外键，指向一另一方的主键。

• 多对多

  如：学生和课程

  实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，

  这两个字段作为第三张表的外键，分别指向两张表的主键。

• 一对一（了解）

  如：人和身份证

  实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

3.数据库设计的范式

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

基本表及其字段之间的关系, 应尽量满足第三范式。

但是，满足第三范式的数据库设计，往往不是最好的设计。

为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

在实际开发中最为常见的设计范式有三个：

• 1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

• 2.第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

• 3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

事务

1.事务的基本介绍

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

• 操作：

  1.开启事务：start transaction;

  2.回滚：rollback;

  3.提交：commit;

• 例子：

  --建表
  CREATE TABLE account(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(10),
     balance DOUBLE
  );
  
  --插入数据
  INSERT INTO account(NAME,balance) VALUES ('zhangsan',1000),('lisi',1000);
  
  SELECT * FROM account;
  
  -- 张三给李四转账500元
  -- 0.开启事务
  START TRANSACTION;
  -- 1.张三账户 -500
  UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
  -- 2.李四账户 + 500
  UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
  
  -- 出错了/没出错...
  
  -- 发现没有问题了，提交事务
  COMMIT;
  -- 发现出问题了，回滚事务
  ROLLBACK;
  

• MySQL数据库中事务默认自动提交，事务提交的两种方式：

  • 自动提交：

    mysql就是自动提交的

    例如：一条DML（增删改）语句会自动提交一次事务。

  • 手动提交：

    需要先开启事务，再提交

    Oracle数据库默认是手动提交事务

• 修改事务的默认提交方式：

  --查看事务的默认提交方式：
  SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
  
  --修改默认提交方式：
  SET @@autocommit = 0;   
  

2.事务的四大特征ACID

• 原子性（Atomicity）

  原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

• 一致性（Consistency）

  一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

  拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

• 隔离性（Isolation）

  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

  即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

• 持久性（Durability）

  持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

  例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

3.事务的隔离级别（了解）

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

隔离级别：

• Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

• Repeatable read (可重复读)（默认）：可避免脏读、不可重复读的发生。

• Read committed (读已提交)：可避免脏读的发生。

• Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

  注意：隔离级别从低到高到安全性越来越高，但是效率越来越低

  查询数据库隔离级别：

  select @@tx_isolation;

  数据库设置隔离级别：

  set global transaction isolation level 级别字符串;

存在问题：

• 1.脏读

  脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

  当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

  update account set money=money + 100 where name=’B’;  (此时A通知B)
  update account set money=money - 100 where name=’A’;
  

  当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

• 2.不可重复读

  不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

  例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。

  不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

  在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

• 3.虚读(幻读)

  幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

  幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

视图

1.什么是视图？

视图是基于 SQL 语句的结果集的可视化的表，即视图是一个虚拟存在的表，可以包含表的全部或者部分记录，也可以由一个表或者多个表来创建。使用视图就可以不用看到数据表中的所有数据，而是只想得到所需的数据。当我们创建一个视图的时候，实际上是在数据库里执行了SELECT语句，SELECT语句包含了字段名称、函数、运算符，来给用户显示数据。使用视图查询可以使查询数据相对安全，通过视图可以隐藏一些敏感字段和数据，从而只对用户暴露安全数据。视图查询也更简单高效，如果某个查询结果出现的非常频繁或经常拿这个查询结果来做子查询，将查询定义成视图可以使查询更加便捷。

2.视图创建及使用方法

• 完整创建语法：

  CREATE
      [OR REPLACE]
      [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
      [DEFINER = user]
      [SQL SECURITY { DEFINER | INVOKER }]
      VIEW view_name [(column_list)]
      AS select_statement
      [WITH [CASCADED | LOCAL] CHECK OPTION]
  

  语法解读（了解）：

  1）OR REPLACE：表示替换已有视图，如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同。

  2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表，一般该参数不显式指定。

  3）DEFINER：指出谁是视图的创建者或定义者，如果不指定该选项，则创建视图的用户就是定义者。

  4）SQL SECURITY：SQL安全性，默认为DEFINER。

  5）select_statement：表示select语句，可以从基表或其他视图中进行选择。

  6）WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED。

• 一般用法：

  CREATE VIEW 视图名 AS SELECT 查询语句;
  

• 修改视图

  ALTER VIEW 视图名 AS SELECT 查询语句;
  

• 删除视图

  DROP VIEW 视图名;
  

3.注意事项

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。

一般情况下不建议对视图做DML操作

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

• LIMIT关键字；

• 聚合函数；

• DISTINCT关键字；

• GROUP BY子句；

• ORDER BY子句；

• HAVING子句；

• UNION运算符；

• 位于选择列表中的子查询；

• FROM子句中包含多个表；

• SELECT语句中引用了不可更新视图；

• WHERE子句中的子查询，引用FROM子句中的表；

• ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）

4.为什么使用视图

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。比如说我们经常用到几个表的关联结果，那么我们就可以使用视图来处理，或者说第三方程序需要调用我们的业务库，可以按需创建视图给第三方程序查询