MySQL精讲(四) |TCL事务控制语句全解析|视图

超、凢脫俗 2023-02-16 00:41 18阅读 0赞

> **MySQL精讲系列文章(更新完毕)**  
> [《MySQL入门必看知识》][MySQL]  
> [《MySQL精讲(一) | DQL数据查询语句全解析》][MySQL_ _ DQL]  
> [《MySQL精讲(二) | DML数据操作语句全解析》][MySQL_ _ DML]  
> [《MySQL精讲(三) | DDL数据定义语句全解析 |数据类型和约束》][MySQL_ _ DDL_]  
> [《MySQL精讲(四) | TCL事务控制语句全解析|视图》][MySQL_ _ TCL]  
> [《MySQL精讲(五) | 存储过程和函数》][MySQL_ _]  
> [《MySQL精讲(六) | 流程控制》][MySQL_ _ 1]  
>                                                                                                [– by 凡尘阿凉][by]

### 文章目录 ###

*  一、事务的介绍与使用
 *  二、事务并发问题及解决方案
 *  三、视图

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

# 一、事务的介绍与使用 #

通过`show engines;`可以查看mysql支持的存储引擎,其中`innodb`支持事务，而`myisam`，`memory`等不支持事务。

事务：一个或一组sql语句组成一个sql单元，这个执行单元`要么全部都执行,要么全部都不执行`。

事务具有`ACID`四个属性

<table> 
 <thead> 
  <tr> 
   <th>原子性(Atomicity)</th> 
   <th>事务是一个不可分割的工作单位</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>一致性(Consistency)</strong></td> 
   <td><strong>事务必须使数据库从一个一致性状态变换到另一个一致性状态</strong></td> 
  </tr> 
  <tr> 
   <td><strong>隔离性(Isolation)</strong></td> 
   <td><strong>事务的执行不能被其他事务干扰，并发执行的各个事务之间不能互相干扰</strong></td> 
  </tr> 
  <tr> 
   <td><strong>持久性(Durability)</strong></td> 
   <td><strong>事务一旦被提交，对数据库中数据的改变就是永久性的</strong></td> 
  </tr> 
 </tbody> 
</table>

**隐式事务**:事务没有明显的开启和结束的标记，如insert、delete、update语句。

**显式事务**:事务具有明显的开启和结束的标记,使用前提是必须先设置自动提交功能为禁用。

DELETE语句回滚之后，还可以还原；TRUNCATE语句回滚之后无法还原。

【演示事务的使用步骤 】
    
    DROP TABLE IF EXISTS account;
    CREATE TABLE account(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	username VARCHAR(20),
    	balance DOUBLE
    );
    
    INSERT INTO account(username,balance)
    VALUES('张无忌',1000),('赵敏',1000);
    
    # 第一步：关闭自动提交
    SET autocommit=0;
    START TRANSACTION;# 可以省略
    # 第二步：编写一组事务
    UPDATE account SET balance=balance+500 WHERE username='张无忌';
    UPDATE account SET balance=balance-500 WHERE username='赵敏';
    # 第三步：结束事务
    #commit;# 提交
    ROLLBACK; # 回滚
    
    SELECT * FROM account;
    
    【演示savepoint的使用】
    SET autocommit=0;
    START TRANSACTION;
    DELETE FROM account WHERE id=1;
    SAVEPOINT a;# 设置保存点
    DELETE FROM account WHERE id=2;
    ROLLBACK TO a; # 回滚到保存点

# 二、事务并发问题及解决方案 #

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。

**`脏读`：一个事务读到了其他事物`更新但没有提交`的数据。**

**`不可重复读`：一个事务多次读取，结果不一样。**

**`幻读`：一个事务读取了其他事务`插入但没有提交`的数据。**

**事务并发问题的解决方案是通过设置事务的隔离级别，进而避免并发问题。**

每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量`@@tx_isolation`，表示当前的事务隔离级别。

查看当前的隔离级别：`select @@tx_isolation；`

<table> 
 <thead> 
  <tr> 
   <th>事务的隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>read uncommitted 读未提交</strong></td> 
   <td>未解决</td> 
   <td>未解决</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td><strong>read committed 读已提交(Oracle默认)</strong></td> 
   <td>解决√</td> 
   <td>未解决</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td><strong>repeatable read 可重复度(Mysql默认)</strong></td> 
   <td>解决√</td> 
   <td>解决√</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td><strong>serializable 串行化</strong></td> 
   <td>解决√</td> 
   <td>解决√</td> 
   <td>解决√</td> 
  </tr> 
 </tbody> 
</table>

设置当前mysql连接的隔离级别：**set transaction isolation level `read committed`;**  
设置数据库系统的全局的隔离级别：**set `global` transaction isolation level read committed;**

# 三、视图 #

视图的含义：mysql5.1版本后出现的新特性，一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

应用场景：

*  多个地方用到相同的查询结果。
 *  该查询结果使用的sql语句较复杂。

<table> 
 <thead> 
  <tr> 
   <th>视图和表的对比</th> 
   <th>创建语法的关键字</th> 
   <th>占用的物理空间</th> 
   <th>使用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>视图</td> 
   <td>create view</td> 
   <td>只是保存了sql逻辑</td> 
   <td>可以增删改查，但一般只支持查询</td> 
  </tr> 
  <tr> 
   <td>表</td> 
   <td>create table</td> 
   <td>保存了数据</td> 
   <td>支持增删改查</td> 
  </tr> 
 </tbody> 
</table>

**视图创建语法：**

`create view 视图名`  
`as(查询语句);`

**视图修改语法：**  
方式一：  
`create or replace view 视图名`  
`as (新的查询语句);`

方式二：  
`alter view 视图名`  
`as(新的查询语句);’`

**视图删除语法：**  
`DROP VIEW 视图名,视图名...;`

**视图的更新：**

视图的可更新性和视图中查询的定义有关系，以下类型的视图不能更新。

*  ①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
 *  ②常量视图
 *  ③Select中包含子查询
 *  ④join
 *  ⑤from一个不能更新的视图
 *  ⑥where子句的子查询引用了from子句中的表

视图的优点：

*  重用sql语句。
 *  简化复杂的sql操作，不必知道查询细节。
 *  保护数据，提供安全性。

【视图的创建】
    # 1.查询邮箱中包含a字符的员工名、部门名、工种名
    CREATE VIEW myv1 # 将三个表的连接封装
    AS (
    	SELECT Last_name,department_name,job_title
    	FROM employees e
    	JOIN departments d ON e.department_id=d.department_id
    	JOIN jobs j ON j.job_id=e.job_id
    );
    
    SELECT * FROM myv1 WHERE Last_name LIKE '%a%';
    
    # 2.查询各部门的平均工资级别
    CREATE VIEW myv2 # 各部门平均工资和部门id
    AS(
    	SELECT AVG(Salary) ag,department_id
    	FROM employees
    	GROUP BY department_id
    );
    
    SELECT myv2.ag,grade_level
    FROM myv2
    JOIN job_grades j
    ON myv2.ag BETWEEN j.lowest_sal AND j.highest_sal;
    
    # 3.查询平均工资最低的部门id和平均工资
    SELECT * FROM myv2 ORDER BY ag LIMIT 1;
    
    # 4.查询平均工资最低的部门名和工资
    CREATE VIEW myv3 
    AS(
    	SELECT * FROM myv2 ORDER BY ag LIMIT 1
    );
    
    SELECT department_name,ag
    FROM departments
    JOIN myv3
    ON myv3.department_id=departments.department_id;
    ------------------------------------------------------------------------------------------
    【视图的修改】
    
    # 方式一:
    CREATE OR REPLACE VIEW myv3
    AS(
    	SELECT AVG(Salary),job_id
    	FROM employees
    	GROUP BY job_id
    );
    # 方式二：
    ALTER VIEW myv3
    AS(SELECT * FROM employees);
    
    SELECT * FROM myv3;
    ------------------------------------------------------------------------------------------
    【视图的删除】
    DESC myv1;# 查看视图
    SHOW CREATE VIEW myv1;# 查看视图
    DROP VIEW myv1,myv2,myv3; # 删除视图
    ------------------------------------------------------------------------------------------
    【视图的更新】
    CREATE OR REPLACE VIEW myv4
    AS(
    	SELECT Last_name,email
    	FROM employees
    );
    
    # 1.插入
    INSERT INTO myv4 VALUES('花花','huahua@163.com');
    
    SELECT * FROM myv4;
    SELECT * FROM employees;
    
    # 2.修改
    UPDATE myv4 SET Last_name='Hudie' WHERE Last_name='花花';
    
    # 3.删除
    DELETE FROM myv4 WHERE Last_name='Hudie';

> 学习了MySQL的视图，尝试完成下列习题  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]  
> 答案：  
> `一、`  
> CREATE OR REPLACE VIEW emp\_v1  
> AS(  
> SELECT Last\_name,Salary,email  
> FROM employees  
> WHERE phone\_number LIKE ‘011%’  
> );  
> SELECT \* FROM emp\_v1;  
> `二、`  
> ALTER VIEW emp\_v1  
> AS(  
> SELECT Last\_name,Salary,email  
> FROM employees  
> WHERE phone\_number LIKE ‘011%’ AND email LIKE ‘%e%’  
> );  
> `三、`  
> CREATE OR REPLACE VIEW emp\_v2 \# 部门最高工资高于12000的部门id、部门最高工资  
> AS(  
> SELECT MAX(Salary) mx,department\_id  
> FROM employees  
> GROUP BY department\_id  
> HAVING MAX(Salary)>12000  
> );  
> SELECT d.\*,m.mx  
> FROM departments d  
> JOIN emp\_v2 m  
> ON m.department\_id=d.department\_id;  
> `四、`  
> CREATE TABLE Book(  
> bid INT PRIMARY KEY,  
> bname VARCHAR(20) UNIQUE NOT NULL,  
> price FLOAT DEFAULT 10,  
> btypeId INT,  
> FOREIGN KEY(byteId) REFERENCES bookType(id)  
> );  
> `五、`  
> SET autocommit=0;  
> INSERT INTO book(bid,bname,price.btypeId) VALUES(1,‘大败局’,100,1);  
> COMMIT;  
> `六、`  
> CREATE VIEW myv1  
> AS(  
> SELECT bname,NAME  
> FROM book b  
> JOIN bookType t  
> ON b.btypeid=t.id  
> WHERE price>100  
> );  
> `七、`  
> CREATE OR REPLACE VIEW myv1(  
> SELECT bname,price  
> FROM book  
> WHERE price BETWEEN 90 AND 120  
> );  
> `八、`  
> DROP VIEW myv1;

[MySQL]: https://guoqianliang.blog.csdn.net/article/details/106447977
[MySQL_ _ DQL]: https://guoqianliang.blog.csdn.net/article/details/106456675
[MySQL_ _ DML]: https://guoqianliang.blog.csdn.net/article/details/106560523
[MySQL_ _ DDL_]: https://guoqianliang.blog.csdn.net/article/details/106571679
[MySQL_ _ TCL]: https://guoqianliang.blog.csdn.net/article/details/106589677
[MySQL_ _]: https://guoqianliang.blog.csdn.net/article/details/106604374
[MySQL_ _ 1]: https://guoqianliang.blog.csdn.net/article/details/106627351
[by]: https://guoqianliang.blog.csdn.net/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200607100838768.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70