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

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è)屬性

原子性(Atomicity) 事務(wù)是一個(gè)不可分割的工作單位
一致性(Consistency) 事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)
隔離性(Isolation) 事務(wù)的執(zhí)行不能被其他事務(wù)干擾,并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾
持久性(Durability) 事務(wù)一旦被提交,對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的

隱式事務(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;

事務(wù)的隔離級(jí)別 臟讀 不可重復(fù)讀 幻讀
read uncommitted 讀未提交 未解決 未解決 未解決
read committed 讀已提交(Oracle默認(rèn)) 解決√ 未解決 未解決
repeatable read 可重復(fù)度(Mysql默認(rèn)) 解決√ 解決√ 未解決
serializable 串行化 解決√ 解決√ 解決√

設(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ù)雜。
視圖和表的對(duì)比 創(chuàng)建語法的關(guān)鍵字 占用的物理空間 使用
視圖 create view 只是保存了sql邏輯 可以增刪改查,但一般只支持查詢
create table 保存了數(shù)據(jù) 支持增刪改查

視圖創(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í)題
MySQL精講之四:TCL事務(wù)控制語句
答案:
一、
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教程(視頻)

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊11 分享