MySQL Lecture 4: TCL Transaction Control Statement

Free learning recommendation: mysql video tutorial

Article Directory

• 1. Introduction and use of transactions
• 2. Transaction concurrency problems and solutions
• 3. View

---

1 , Introduction and use of transactions

You can view the storage engines supported by mysql through show engines;, among which innodb supports transactions, and myisam, memory, etc. do not support transactions.

Transaction: One or a group of sql statements form a sql unit. This execution unit either executes all, or does not execute all .

Transactions have four attributesACID

Atomicity	A transaction is an indivisible Unit of Work
Consistency	The transaction must cause the database to change from a consistent state To another consistency state
Isolation	The execution of a transaction cannot be interfered with by other transactions, Concurrently executed transactions cannot interfere with each other
Durability(Durability)	Once a transaction is submitted, Changes to the data in the database are permanent

Implicit transactions: Transactions have no obvious opening and closing marks, such as insert , delete and update statements.

Explicit transactions: Transactions have obvious opening and closing marks. The prerequisite for use is that the auto-commit function must be disabled.

After the DELETE statement is rolled back, it can still be restored; after the TRUNCATE statement is rolled back, it cannot be restored.

【演示事务的使用步骤 】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; # 回滚到保存点

2. Transaction concurrency issues and solutions

For multiple transactions running at the same time, when these transactions access the same data in the database, if no necessary steps are taken The isolation mechanism will lead to various concurrency problems.

Dirty read: A transaction reads data updated by other things but does not commit .

Non-repeatable read: A transaction is read multiple times, and the results are different.

Phantom read: A transaction reads data inserted by another transaction but does not commit .

The solution to transaction concurrency problems is to avoid concurrency problems by setting the isolation level of the transaction.

Every time a mysql program is started, a separate database connection will be obtained. Each database connection has a global variable @@tx_isolation, indicating the current transaction isolation level.

View the current isolation level: select @@tx_isolation;

UnresolvedUnresolvedUnresolvedSolved√UnresolvedUnresolved##repeatable read Repeatability (Mysql default)serializable SerializableSet the isolation level of the current mysql connection:

Isolation level of transaction	Dirty read	Non-repeatable read	Phantom read
##read uncommitted read uncommitted
read committed Read committed (Oracle default)
Resolved√	Resolved√	Unresolved
Solution√	Solution√	Solution√

set transaction isolation level

read committed; Set the global isolation level of the database system: set
global transaction isolation level read committed;

三、View

The meaning of view: a new feature that appeared after mysql5.1 version, a virtual table, row and column data come from the table used in the query of the custom view, and It is dynamically generated when using the view. It only saves the SQL logic and does not save the query results.

Application scenarios:

The same query results are used in multiple places.

• The SQL statement used in this query result is relatively complex.

Comparison of views and tablesviewtable

视图创建语法：

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教程(视频)

Creation syntax keywords	Physical space occupied	Use
create view	just saves the sql logic	You can add, delete, modify and check, But generally only supports querying
create table	saved data	supports addition, deletion, modification and query

The above is the detailed content of MySQL Lecture 4: TCL Transaction Control Statement. For more information, please follow other related articles on the PHP Chinese website!