MySQL-Vorlesung 4: TCL-Transaktionskontrollanweisung

Empfohlenes kostenloses Lernen: MySQL-Video-Tutorial

Artikelverzeichnis

• 1. Einführung und Verwendung von Transaktionen
• 2. Probleme und Lösungen bei Transaktionen

---

1. Einführung und Verwendung von Transaktionen

Sie können die von MySQL unterstützten Speicher-Engines über show engine; anzeigen, darunter innodb unterstützt Transaktionen und myisam, <code>memory usw. unterstützen keine Transaktionen.

Transaktion: Eine oder mehrere SQL-Anweisungen bilden eine SQL-Einheit. Diese Ausführungseinheit führt entweder alle oder keine davon aus. 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 globalTransaktion hat vier Attribute von ACID

Atomizität

Eine Transaktion ist eine unteilbare Arbeitseinheit

• Konsistenz

Transaktionen müssen die Datenbank transformieren von einem Konsistenzzustand in einen anderen KonsistenzzustandIsolation (Isolation)Die Ausführung von Transaktionen kann nicht durch andere Transaktionen beeinträchtigt werden, und gleichzeitig ausgeführte Transaktionen können nicht miteinander interagieren. StörungenDauerhaftigkeitSobald eine Transaktion festgeschrieben ist, sind die Änderungen an den Daten in der Datenbank dauerhaft.: Es gibt kein offensichtliches Öffnen und Schließen der Transaktions-Tags wie Einfügen, Löschen und Aktualisieren Aussagen. Explizite Transaktionen: Transaktionen haben offensichtliche Öffnungs- und Schlussmarkierungen. Voraussetzung für die Verwendung ist, dass die Auto-Commit-Funktion deaktiviert ist.

		Implizite Transaktionen

🎜Nachdem die DELETE-Anweisung zurückgesetzt wurde, kann sie immer noch wiederhergestellt werden. Nach dem Zurücksetzen der TRUNCATE-Anweisung kann sie nicht wiederhergestellt werden. 🎜

【视图的创建】# 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';

🎜🎜2. Probleme und Lösungen bei der Parallelität von Transaktionen🎜🎜🎜Wenn mehrere Transaktionen gleichzeitig auf dieselben Daten in der Datenbank zugreifen und der erforderliche Isolationsmechanismus nicht übernommen wird, treten verschiedene Parallelitätsprobleme auf. 🎜🎜🎜Dirty Read: Eine Transaktion liest Daten, die durch andere Dinge aktualisiert, aber nicht festgeschrieben werden. 🎜🎜🎜🎜Nicht wiederholbares Lesen: Eine Transaktion wird mehrmals gelesen und die Ergebnisse sind unterschiedlich. 🎜🎜🎜🎜Phantom Read: Eine Transaktion liest Daten, die eine andere Transaktion einfügt, aber nicht festschreibt. 🎜🎜🎜🎜Die Lösung für Transaktions-Parallelitätsprobleme besteht darin, Parallelitätsprobleme zu vermeiden, indem die Isolationsstufe von Transaktionen festgelegt wird. 🎜🎜🎜Jedes Mal, wenn Sie ein MySQL-Programm starten, erhalten Sie eine separate Datenbankverbindung. Jede Datenbankverbindung verfügt über eine globale Variable @@tx_isolation, die die aktuelle Transaktionsisolationsstufe darstellt. 🎜🎜Aktuelle Isolationsstufe anzeigen: select @@tx_isolation;🎜🎜🎜🎜🎜Isolationsstufe der Transaktion🎜🎜Dirty Read🎜🎜Non-Repeatable Read🎜🎜Phantom Read🎜🎜🎜🎜🎜🎜🎜 Nicht festgeschrieben gelesen. Nicht festgeschrieben gelesen. Ungelöst. Ungelöst. Ungelöst 🎜🎜🎜🎜 🎜wiederholbare Lesewiederholbarkeit (MySQL-Standard) 🎜🎜🎜Gelöst√🎜🎜Gelöst√🎜🎜Ungelöst🎜🎜🎜🎜🎜serialisierbar Serialisierbar🎜🎜🎜Gelöst√🎜🎜Gelöst√🎜🎜Gelöst√🎜 🎜🎜🎜 🎜Aktuell festlegen Die Isolationsstufe der MySQL-Verbindung: 🎜Transaktionsisolation festlegen level read commit;🎜
Legen Sie die globale Isolationsstufe des Datenbanksystems fest: 🎜set global Transaktionsisolationsstufe read commit ;🎜🎜🎜🎜 3. Anzeigen 🎜 🎜🎜Die Bedeutung der Ansicht: Eine neue Funktion, die nach der Version mysql5.1 erschien, eine virtuelle Tabelle, Zeilen- und Spaltendaten zum Anpassen der Tabelle, die in der Abfrage der Ansicht verwendet wird und bei Verwendung der Ansicht dynamisch generiert wird. Es speichert nur die SQL-Logik und nicht die Abfrageergebnisse. 🎜🎜Anwendungsszenarien: 🎜🎜🎜Die gleichen Abfrageergebnisse werden an mehreren Stellen verwendet. 🎜🎜Die in diesem Abfrageergebnis verwendete SQL-Anweisung ist relativ komplex. 🎜🎜🎜🎜🎜🎜Vergleich von Ansichten und Tabellen🎜🎜Erstellungssyntax-Schlüsselwörter🎜🎜Physischer Platz belegt🎜🎜Mit🎜🎜🎜🎜🎜🎜Ansicht🎜🎜🎜speichert nur die SQL-Logik🎜🎜 Hinzufügung, Löschung, Änderung Prüfung, aber allgemein Unterstützt nur Abfrage🎜🎜🎜🎜Tabelle🎜🎜Tabelle erstellen🎜🎜gespeicherte Daten🎜🎜Unterstützt Hinzufügen, Löschen, Ändern und Abfragen🎜🎜🎜🎜

视图创建语法：

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

Das obige ist der detaillierte Inhalt vonMySQL-Vorlesung 4: TCL-Transaktionskontrollanweisung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!