MySQL精讲之四：TCL事务控制语句

概述MySQL精讲之四：TCL事务控制语句

免费学习推荐：mysql视频教程

文章目录

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

---

一、事务的介绍与使用

通过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 AS(	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;

更多相关免费学习推荐：mysql教程(视频)

总结

以上是内存溢出为你收集整理的MySQL精讲之四：TCL事务控制语句全部内容，希望文章能够帮你解决MySQL精讲之四：TCL事务控制语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。