1 事务

1.1 概念

  • 事务是由单独单元的一个或多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中的某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始之前的状态；如果单元中的所有SQL语句都执行成功，则事务执行成功。

    1.2 存储引擎

  • 在MySQL中的数据使用各种不同的存储引擎存储在文件或内存中。

  • 可以通过SHOW ENGINES;来查看MySQL支持的存储引擎。

MySQL基础（六） - 图1

  • 在MySQL中使用最多的存储引擎是innodb和myisam等，其中innodb支持事务，而myisam不支持事务。MySQL从5.5.5版本以上开始默认的存储引擎是innodb。

    1、基本操作

    1、查看所有存储引擎
    show engines;
    2、查看已有表存储引擎
    show create table 表名;
    3、创建表时指定
    create table 表名(…)engine=MyISAM;
    4、已有表
    alter table 表名 engine=InnoDB;

    2、常用存储引擎特点

    1、InnoDB特点

    1. 1、支持外键、行级锁、事务


    2、共享表空间
    表名.frm ：表结构和索引信息
    表名.ibd ：表记录

    2、MyISAM特点

    1. 1、支持表级锁


    2、独享表空间
    表名.frm ：表结构
    表名.MYD ：表记录
    表名.MYI ：索引信息

    3、MEMORY特点

    1. 1、表结构存储在硬盘,表记录存储在内存


    2、服务/主机重启后,表记录消失

    3、如何选择使用哪个存储引擎

    1、执行查询操作多的表用MyISAM(使用InnoDB浪费资源)
    2、执行写操作多的表用InnoDB


    https://blog.csdn.net/soonfly/article/details/70238902

    1.3 事务的属性（ACID）

  • 原子性（Atomicity）：原子性是指事务是一个不可分割的整体，事务中的操作要么全部成功，要么全部失败。

  • 一致性（Consistency）：事务必须使得数据库从一个一致性的状态转移到另一个一致性的状态。比如：张三有余额3000，李四有余额2000，张三向李四转账500，此时张三的余额是2500，李四的余额是2500，在这个过程中余额的总数5000是不变的。
  • 隔离型（Isolation）：事务的隔离性是指一个事务的执行不能被其它事务所干扰，即一个事务内存的操作及使用的数据对并发中的其它事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其它操作和数据库故障不应该对其有任何影响。

    1.4 事务的创建

    1.4.1 隐式事务

  • 事务没有明显的开启和结束标记。

  • 在MySQL中，DML（INSERT、UPDATE、DELETE）等语句是自动开启事务的。
  • 可以通过SHOW VARIABLES LIKE ‘%autocommit%’;命令查看MySQL是否是隐式事务。

MySQL基础（六） - 图2

1.4.2 显示事务

  • 事务有明显的开启和结束标记。
  • 显示事务的步骤：

— 关闭隐式事务
SET autocommit = 0;
— 开启事务
START TRANSACTION;
— 一系列的SQL操作
— 结束事务：提交事务(COMMIT)或回滚事务(ROLLBACK)
COMMIT[ROLLBACK];

  • 示例：张三向李四转账500。
  • 准备工作：

DROP TABLE IF EXISTS account;
CREATE TABLE account (
id bigint(20) NOT NULL AUTO_INCREMENT,
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
balance double NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO account VALUES (1, ‘张三’, 3000);
INSERT INTO account VALUES (2, ‘李四’, 2000);

  • SQL：

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE username = ‘张三’;
UPDATE account SET balance = balance + 500 WHERE username = ‘李四’;
COMMIT;

1.5 隔离级别

1.5.1 数据库并发访问问题

  • 对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
  • 脏读：对于两个事务T1、T2，T1读取了已经被T2更新但是还没提交的字段。之后，如果T2回滚，T1读取的内容就是临时且无效的。
  • 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段值，值就不同了。

  • 幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的数据。之后，如果T1再次读取同一个表，就会发现多出几行数据。

    1.5.2 数据库事务的隔离性

  • 数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题。

  • 一个事务和其他事务隔离的程序称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程序，隔离级别越高，数据一致性就越好，但是并发性就越低。

    1.5.3 数据库提供了4种事务隔离级别

    | 隔离级别 | 描述 | | —- | —- | | READ UNCOMMITTED（读未提交） | 会出现脏读、不可重复读、幻读 | | READ COMMITTED（读已提交） | 解决了脏读，会出现不可重复读、幻读 | | REPEATABLE READ（可重复读） | 解决了不可重复读、幻读，会出现幻读 | | SERIALIZABLE（串行化） | 解决了脏读、不可重复读、幻读，但性能十分低下 |

需要注意的是：
Oracle默认支持2种事务隔离级别：READ COMMITTED（读已提交）和SERIALIZABLE（串行化）。Oracle默认的事务隔离级别是READ COMMITTED（读已提交）。
MySQL支持4种事务隔离级别。MySQL默认的事务隔离级别是REPEATABLE READ（可重复读）。

1.5.4 在MySQL中设置隔离级别

  • 每启动一个MySQL客户端程序，就会获取一个单独的数据库连接。每个数据库连接都会有一个全局变量@@tx_isolation，表示当前的事务隔离级别。
  • 查看当前事务的隔离级别：

— 查看当前事务的隔离级别
SELECT @@tx_isolation;

  • 设置当前MySQL连接的隔离级别：

SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

  • 设置数据库系统的全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

2 视图（了解）

2.1 概述

  • MySQL从5.0.1版本开始提供了视图功能。一种虚拟存在的表，行和列的数据来自自定义视图的查询中使用的表，并且是在使用视图的时候动态生成的，只保存了SQL的逻辑，不保存查询结果。

    2.2 应用场景

  • 多个地方用到同样的查询结果。

  • 该查询结果使用的SQL语句较为复杂。

    2.3 好处

  • 重用SQL语句。

  • 简化复杂的SQL操作，不必知道它的查询细节。

  • 保护数据，提高安全性。

    2.4 创建视图

  • 语法：

CREATE VIEW 视图名
AS
查询语句;

  • 应用示例：
  • 查询姓名中包含a字符的员工名、部门名和工种信息。

CREATE VIEW v1
AS
SELECT
e.last_name,
d.department_name,
j.job_title
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
SELECT * FROM v1 WHERE last_name like ‘%a%’;

2.5 视图的修改

  • 语法1：

CREATE OR REPLATE VIEW 视图名
AS
查询语句;

  • 语法2：

ALTER VIEW 视图名
AS
查询语句;

2.6 删除视图

  • 语法：

DROP VIEW 视图名,视图名,……;

2.7 查看视图结构

  • 语法：

DESC 视图名;
SHOW CREATE VIEW 视图名;