MySQL精講之四：TCL事務(wù)控制語句

免費(fèi)學(xué)習(xí)推薦：mysql視頻教程

文章目錄

• 一、事務(wù)的介紹與使用
• 二、事務(wù)并發(fā)問題及解決方案
• 三、視圖

一、事務(wù)的介紹與使用

通過show engines;可以查看mysql支持的存儲(chǔ)引擎,其中innodb支持事務(wù)，而myisam，memory等不支持事務(wù)。

事務(wù)：一個(gè)或一組sql語句組成一個(gè)sql單元，這個(gè)執(zhí)行單元要么全部都執(zhí)行,要么全部都不執(zhí)行。

事務(wù)具有ACID四個(gè)屬性

隱式事務(wù):事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記，如insert、delete、update語句。

顯式事務(wù):事務(wù)具有明顯的開啟和結(jié)束的標(biāo)記,使用前提是必須先設(shè)置自動(dòng)提交功能為禁用。

DELETE語句回滾之后，還可以還原；TRUNCATE語句回滾之后無法還原。

【演示事務(wù)的使用步驟 】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);# 第一步：關(guān)閉自動(dòng)提交SET autocommit=0;START TRANSACTION;# 可以省略# 第二步：編寫一組事務(wù)UPDATE account SET balance=balance+500 WHERE username='張無忌';UPDATE account SET balance=balance-500 WHERE username='趙敏';# 第三步：結(jié)束事務(wù)#commit;# 提交ROLLBACK; # 回滾SELECT * FROM account;【演示savepoint的使用】SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=1;SAVEPOINT a;# 設(shè)置保存點(diǎn)DELETE FROM account WHERE id=2;ROLLBACK TO a; # 回滾到保存點(diǎn)

二、事務(wù)并發(fā)問題及解決方案

對(duì)于同時(shí)運(yùn)行的多個(gè)事務(wù)，當(dāng)這些事務(wù)訪問數(shù)據(jù)庫(kù)中相同的數(shù)據(jù)時(shí)，如果沒有采取必要的隔離機(jī)制，就會(huì)導(dǎo)致各種并發(fā)問題。

臟讀：一個(gè)事務(wù)讀到了其他事物更新但沒有提交的數(shù)據(jù)。

不可重復(fù)讀：一個(gè)事務(wù)多次讀取，結(jié)果不一樣。

幻讀：一個(gè)事務(wù)讀取了其他事務(wù)插入但沒有提交的數(shù)據(jù)。

事務(wù)并發(fā)問題的解決方案是通過設(shè)置事務(wù)的隔離級(jí)別，進(jìn)而避免并發(fā)問題。

每啟動(dòng)一個(gè)mysql程序，就會(huì)獲得一個(gè)單獨(dú)的數(shù)據(jù)庫(kù)連接，每個(gè)數(shù)據(jù)庫(kù)連接都有一個(gè)全局變量@@tx_isolation，表示當(dāng)前的事務(wù)隔離級(jí)別。

查看當(dāng)前的隔離級(jí)別：select @@tx_isolation；

設(shè)置當(dāng)前mysql連接的隔離級(jí)別：set transaction isolation level read committed;
設(shè)置數(shù)據(jù)庫(kù)系統(tǒng)的全局的隔離級(jí)別：set global transaction isolation level read committed;

三、視圖

視圖的含義：mysql5.1版本后出現(xiàn)的新特性，一種虛擬存在的表，行和列的數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時(shí)動(dòng)態(tài)生成的，只保存了sql邏輯，不保存查詢結(jié)果。

應(yīng)用場(chǎng)景：

• 多個(gè)地方用到相同的查詢結(jié)果。
• 該查詢結(jié)果使用的sql語句較復(fù)雜。

視圖創(chuàng)建語法：

create view 視圖名
as(查詢語句);

視圖修改語法：
方式一：
create or replace view 視圖名
as (新的查詢語句);

方式二：
alter view 視圖名
as(新的查詢語句);’

視圖刪除語法：
DROP VIEW 視圖名,視圖名…;

視圖的更新：

視圖的可更新性和視圖中查詢的定義有關(guān)系，以下類型的視圖不能更新。

• ①包含以下關(guān)鍵字的sql語句：分組函數(shù)、distinct、group by、having、union或者union all
• ②常量視圖
• ③Select中包含子查詢
• ④join
• ⑤from一個(gè)不能更新的視圖
• ⑥where子句的子查詢引用了from子句中的表

視圖的優(yōu)點(diǎn)：

• 重用sql語句。
• 簡(jiǎn)化復(fù)雜的sql操作，不必知道查詢細(xì)節(jié)。
• 保護(hù)數(shù)據(jù)，提供安全性。

【視圖的創(chuàng)建】# 1.查詢郵箱中包含a字符的員工名、部門名、工種名CREATE VIEW myv1 # 將三個(gè)表的連接封裝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.查詢各部門的平均工資級(jí)別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';

學(xué)習(xí)了MySQL的視圖，嘗試完成下列習(xí)題

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

更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)