MySQL中TCL事务控制语句的示例分析

64次阅读

没有评论

共计 4649 个字符，预计需要花费 12 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 MySQL 中 TCL 事务控制语句的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

一、事务的介绍与使用

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

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

事务具有 ACID 四个属性

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

隐式事务: 事务没有明显的开启和结束的标记，如 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；

事务的隔离级别脏读不可重复读幻读 read uncommitted 读未提交未解决未解决未解决 read committed 读已提交 (Oracle 默认) 解决√未解决未解决 repeatable read 可重复度 (Mysql 默认) 解决√解决√未解决 serializable 串行化解决√解决√解决√

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

三、视图

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

应用场景：

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

该查询结果使用的 sql 语句较复杂。

视图和表的对比创建语法的关键字占用的物理空间使用视图 create view 只是保存了 sql 逻辑可以增删改查，但一般只支持查询表 create 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 #  各部门平均工资和部门 idAS(SELECT AVG(Salary) ag,department_id FROM employees GROUP BY department_id);SELECT myv2.ag,grade_levelFROM myv2JOIN job_grades jON myv2.ag BETWEEN j.lowest_sal AND j.highest_sal;# 3. 查询平均工资最低的部门 id 和平均工资 SELECT * FROM myv2 ORDER BY ag LIMIT 1;# 4. 查询平均工资最低的部门名和工资 CREATE VIEW myv3 
 SELECT * FROM myv2 ORDER BY ag LIMIT 1);SELECT department_name,agFROM departmentsJOIN myv3ON myv3.department_id=departments.department_id;------------------------------------------------------------------------------------------【视图的修改】#  方式一:CREATE OR REPLACE VIEW myv3AS(SELECT AVG(Salary),job_id FROM employees GROUP BY job_id);#  方式二：ALTER VIEW myv3AS(SELECT * FROM employees);SELECT * FROM myv3;------------------------------------------------------------------------------------------【视图的删除】DESC myv1;#  查看视图 SHOW CREATE VIEW myv1;#  查看视图 DROP VIEW myv1,myv2,myv3; #  删除视图 ------------------------------------------------------------------------------------------【视图的更新】CREATE OR REPLACE VIEW myv4AS(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 的视图，尝试完成下列习题

答案：
一、
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;