chapter11_约束、视图与事务

• 检查约束 CHECK

  (1) 限定填充进某一列数据的取值范围

  (2) 示例

    CREATE TABLE piggy_bank (
        id INT(4) AUTO_INCREMENT NOT NULL PRIMARY KEY,
        coin CHAR(1) CHECK(coin IN ('P','N','D','Q'))
    );
  

  或

    ALTER TABLE piggy_bank
    ADD CONSTRAINT CHECK coin IN ('P','N','D','Q');
  

  (3) Mysql中检查约束无效, 可以改用触发trigger机制实现

  (4) AND, OR, IN, NOT, BETWEEN等都可以用于CHECK条件

• 视图

  (1) 是一个虚拟表，它由真实的表抽出部分行、部分列得来。

  (2) 示例

  创建视图

    CREATE VIEW web_designers 
    AS
    SELECT mc.first_name, mc.last_name, mc.phone, mc.email FROM my_contacts AS mc NATURAL JOIN job_desired AS jd WHERE jd.title = 'Web Designer'; 
  

  使用视图

    SELECT * FROM web_designers;
  

  (3) 使用视图，表面的操作类似于操作一个表table，实际的方式类似于子查询

    SELECT * FROM web_designers (SELECT mc.first_name, mc.last_name, mc.phone, mc.email FROM my_contacts AS mc NATURAL JOIN job_desired AS jd WHERE jd.title = 'Web Designer') AS web_designers;
  

  由于FROM子句需要表，所以要取一个别名AS web_designers

  (4) 同样可以对视图做增删改查，结果会影响到原有表的数据

• 使用视图的优点

  (1) 简化查询

  (2) 改变数据库结构以后，不会影响依赖表的应用程序

  (3) 隐藏信息

• CHECK OPTION

  (1) 示例

    CREATE VIEW pb_dimes AS
    SELECT * FROM piggy_bank WHERE coin = 'b' WITH CHECK OPTION;
  

  可以作为替代Mysql的CHECK CONSTRAINT无效的替代方案，当使用UPDATE, INSERT或DELETE时，都会根据视图中的WHERE子句来判断这些查询是否可以执行(例如想插入一条coin = 'c’的记录会失败)

  注：WITH CHECK OPTION 表示只能更新当前视图可以看到的记录：如果要INSERT，必须插入后这条记录能被当前视图看到，否则不执行；如果要UPDATE，必须更新后这条记录能被当前视图看到，否则不执行；如果要DELETE，也只能删除该视图中能看到的记录

• 事务

  (1) 完成某项工作的一组SQL语句。在事务过程中，如果所有步骤无法不受干扰的完成，则不应该完成任何单一步骤

  (2) 用途：例如银行ATM突然断电

• 构成事务的四个原则 ACID

  (1) ATOMICITY原子性

  事务里的所有步骤，要么全部完成，要么一个都不完成

  (2) CONSISTENCY一致性

  事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。如果事务是并发多个，系统也必须如同串行事务一样操作。

  例如两个人同时给同一个账户转账100元，那么两个事务执行过后，那个账户应该增加的是200元

  (3) ISOLATION隔离性

  每次事务都会看到具有一致性的数据库，无论其他事务有什么行动。

  例如两个人几乎同时都从一个账户取款100元，之前本来有1000元。第一个人看到剩余1000元要取钱的时候，第二个人此时不应该看到剩余1000元，应该看到“交易正在处理中”

  (4) DURABILITY持久性

  事务完成后，数据库需要正确的存储数据并保护数据免受断电或其他威胁的伤害

• SQL的事务处理

  (1) START TRANSACTION

  表示一个事务开始，持续追踪后续所有SQL语句，直到输入COMMIT或ROLLBACK事务结束

  (2) COMMIT

  表示一个事务结束，对结果进行提交

  (3) ROLLBACK

  表示一个事务结束，且不想改变结果，回退到START TRANSACTION之前的版本

  (4) 示例

  示例1

    START TRANSACTION;
    SELECT * FROM piggy_bank;
    UPDATE piggy_bank SET coin = 'Q' WHERE coin = 'P';
    SELECT * FROM piggy_bank;
    ROLLBACK;
    SELECT * FROM piggy_bank;    //没有更改数据
  

  示例2

    START TRANSACTION;
    SELECT * FROM piggy_bank;
    UPDATE piggy_bank SET coin = 'Q' WHERE coin = 'P';
    SELECT * FROM piggy_bank;
    COMMIT;
    SELECT * FROM piggy_bank;    //更改了数据
  

• 对于MYSQL来说，存储引擎必须是__InnoDB或BDB__这两种支持事务的引擎

• 可以利用事务的方式进行数据测试，这样就起到了类似于git版本回退的效果