【資料庫基礎】02_MySQL文法進階

事務 transaction

什麼是事務

  資料庫事務(Database Transaction)，是指作為單個邏輯工作單元執行的一系列操作，要麼完全地執行，要麼完全地不執行。

  簡單的說：事務就是将一堆的SQL語句(通常是增删改操作)綁定在一起執行，要麼都執行成功，要麼都執行失敗，即都執行成功才算成功，否則就會恢複到這堆SQL執行之前的狀态。

  下面以銀行轉賬為例，A轉100塊到B的賬戶，這至少需要兩條SQL語句：

• 給A的賬戶減去100元；

  update 賬戶表 set money=money**-100** where name=‘A’;

• 給B的賬戶加上100元。

  update 賬戶表 set money=money**+100** where name=‘B’;

  如果在第一條SQL語句執行成功後，在執行第二條SQL語句之前，程式被中斷了（可能是抛出了某個異常，也可能是其他什麼原因），那麼B的賬戶沒有加上100元，而A卻減去了100元，在現實生活中這肯定是不允許的。

  如果在轉賬過程中加入事務，則整個轉賬過程中執行的所有SQL語句會在一個事務中，而事務中的所有操作，要麼全都成功，要麼全都失敗，不可能存在成功一半的情況。

也就是說給A的賬戶減去100元如果成功了，那麼給B的賬戶加上100元的操作也必須是成功的；否則，給A減去100元以及給B加上100元都是失敗的。

事務4個特性ACID

一般來說，事務是必須滿足4個條件（ACID）：原子性（Atomicity，或稱不可分割性）、一緻性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

• 原子性：原子意為最小的粒子，或者說不能再分的事物。資料庫事務的不可再分的原則即為原子性。一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中如果發生錯誤，會被復原（Rollback）到事務開始前的狀态，就像這個事務從來沒有執行過一樣。
• 一緻性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精确度、串聯性以及後續資料庫可以自發性地完成預定的工作。
• 隔離性：資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力（一個事務的執行不受另外一個事務的幹擾），隔離性可以防止多個事務并發執行時由于交叉執行而導緻資料的不一緻。
• 持久性：事務處理結束後，對資料的修改就是永久的，資料将持久化到本地,即便系統故障也不會丢失，除非其他事務對其進行修改。

事務的用法

DCL 用來控制資料庫的通路，包括如下 SQL 語句：

• GRANT：授予通路權限
• REVOKE：撤銷通路權限
• COMMIT：送出事務處理
• ROLLBACK：事務處理回退
• SAVEPOINT：設定儲存點
• LOCK：對資料庫的特定部分進行鎖定
• savepoint 斷點
• commit to 斷點

• rollback to 斷點

   

隐式事務，沒有明顯的開啟和結束事務的标志

比如 insert、update、delete 語句本身就是一個事務

顯式事務，具有明顯的開啟和結束事務的标志

相關步驟：

1. 開啟事務 start transaction（取消自動送出事務的功能）

2. 執行 sql 操作，編寫事務的一組邏輯操作單元（普通 sql 操作，多條sql語句）

   insert

   update

   delete

3. 送出事務或復原事務(commit/rollback)

注意:

• 建表的時候，選擇 Innodb 引擎才支援事務。
• 預設情況下，MySQL 是自動送出事務，每次執行一個 SQL 語句時，如果執行成功，就會向資料庫自動送出，而不能復原。如果某一組操作需要在一個事務中，那麼需要使用 start transaction，一旦 rollback 或 commit 就結束當次事務，之後的操作又自動送出。
• 如果需要在目前會話的整個過程中都取消自動送出事務，進行手動送出事務，就需要設定 set autocommit = false; 或 set autocommit = 0; 那樣的話每一句 SQL 都需要手動 commit 送出才會真正生效。rollback 或 commit 之前的所有操作都視為一個事務，之後的操作視為另一個事務，還需要手動送出或復原。
• 和 Oracle 一樣，DDL 語句是不能復原的，并且部分的 DDL 語句會造成隐式的送出，是以最好事務中不要涉及DDL 語句。

#開啟手動處理事務模式
#set autocommit = false;

#開始事務（推薦）
start transaction;
#檢視目前表的資料
select * from t_stu_detail;
#删除整張表的資料
delete from t_stu_detail;
#查詢該表資料，發現顯示删除後的結果
select * from t_stu_detail;

#復原
rollback
           

隔離級别

事務隔離分為不同級别，包括：

• 讀未送出（Read uncommitted） 安全性最差,可能發生并發資料問題,性能最好
• 讀送出（read committed） Oracle預設的隔離級别
• 可重複讀（repeatable read）MySQL預設的隔離級别,安全性較好,性能一般

• 串行化（Serializable） 表級鎖，讀寫都加鎖，效率低下,安全性高,不能并發事務并發問題如何發生？

  當多個事務同時操作同一個資料庫的相同資料時

事務的并發問題有哪些？

• 髒讀：一個事務讀取到了另外一個事務未送出的資料

  不可重複讀：同一個事務中，多次讀取到的資料不一緻

• 幻讀：一個事務讀取資料時，另外一個事務進行更新，導緻第一個事務讀取到了沒有更新的資料

   

如何避免事務的并發問題？

  通過設定事務的隔離級别

1. READ UNCOMMITTED
2. READ COMMITTED 可以避免髒讀
3. REPEATABLE READ 可以避免髒讀、不可重複讀和一部分幻讀

4. SERIALIZABLE可以避免髒讀、不可重複讀和幻讀

    

設定隔離級别：

set session|global transaction isolation level 隔離級别名;

 

檢視隔離級别：

select @@tx_isolation;

查詢mysql的隔離級别

在預設情況下，MySQL每執行一條SQL語句，都是一個單獨的事務。如果需要在一個事務中包含多條SQL語句，那麼需要手動開啟事務和結束事務。

• 開啟事務：start transaction；
• 結束事務：commit（送出事務）或rollback（復原事務）。

在執行SQL語句之前，先執行strat transaction，這就開啟了一個事務（事務的起點），然後可以去執行多條SQL語句，最後要結束事務，commit表示送出，即事務中的多條SQL語句所做出的影響會持久化到資料庫中。或者rollback，表示復原，即復原到事務的起點，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;

Repeatable Read（可重讀）

MySQL的預設事務隔離級别，它確定同一事務的多個執行個體在并發讀取資料時，會看到同樣的資料行。

事務處理

• 在 MySQL 中隻有使用了 Innodb 資料庫引擎的資料庫或表才支援事務
• 事務處理可以用來維護資料的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行
• 事務用來管理 insert、update、delete 語句，因為這些操作才會“破壞”資料，查詢select語句是不會的
• MySQL預設資料庫的事務是開啟的，執行SQL後自動送出。
• MySQL的事務也可以改成手動送出，那就有兩個步驟：先開啟，寫完SQL後，再手動送出。

送出 commit

#多條語句時，批量執行，事務送出

#有了事務，多步操作就形成了原子性操作，高并發下也不會引起資料錯亂

#mysql的事務預設就是開啟的 – 多條語句一起操作時,要麼一起成功要麼一起失敗

BEGIN; #關閉事務的自動送出

INSERT INTO user (id) VALUES(25);#成功

INSERT INTO user (id) VALUES(5);#已經存在5了，會失敗

COMMIT; #手動送出事務

復原 rollback

#多條語句，批量執行，insert插入重複的主鍵導緻失敗時，事務復原

BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事務復原，就不會再送出了

表關聯 association

概念

表table代表了生活中一個主體，如部門表dept，員工表emp。表關聯則代表了表之間的關系，如：部門和員工，商品和商品分類，老師和學生，教室和學生。

同時，也要知道，表并不都有關系，它們形成自己的小圈子。如商品和商品詳情一圈，部門和員工一圈，出圈就可能沒關系了，如商品和員工無關，商品和學生無關。

下面我們讨論表的關系分為四種：

• 一對一 one to one QQ和QQ郵箱，員工和員工編号
• 一對多 one to many 最常見，部門和員工，使用者和訂單
• 多對一 many to one 一對多反過來，員工和部門，訂單和使用者
• 多對多 many to many 老師和學生，老師和課程

表設計

• 外鍵：由子表出發向主表拖動滑鼠，到達主表後松手，PD會自動添加外鍵字段
• 講師表和課程表：一對多，兩張表。關聯關系展現：子表存儲主表的主鍵，稱外鍵
• 課程表和學生表：多對多，三張表。關聯關系展現：子表無法存儲主表的多條關聯資訊，隻能再建立一張表來存儲其資訊
• 中間表：存儲兩張表各自的主鍵，某一張表的主鍵無法辨別記錄的唯一性，兩個一起才可以辨別唯一，這種主鍵為多個字段的稱為複合主鍵

建立資料庫

建立表

表設計特點：

• 表都以s結束，辨別複數
• 字段多以表的首字母作為開頭，在多表聯查時，友善辨別出是哪個表的字段

/*==============================================================*/
 
/* DBMS name: MySQL 5.0 */
 
/* Created on: 2020 */
 
/*==============================================================*/
 
drop table if exists courses;
drop table if exists scores;
drop table if exists students;
drop table if exists teachers;
 
/*==============================================================*/
 
/* Table: courses */
 
/*==============================================================*/
 
create table courses
 
(
 
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);
 
/*==============================================================*/
 
/* Table: scores */
 
/*==============================================================*/
 
create table scores
 
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);
 
/*==============================================================*/
 
/* Table: students */
 
/*==============================================================*/
 
create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);
 
/*==============================================================*/
 
/* Table: teachers */
 
/*==============================================================*/
 
create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);
 
alter table courses add constraint FK_Reference_3 foreign key (tno)
references teachers (tno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_1 foreign key (sno)
references students (sno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_2 foreign key (cno)
references courses (cno) on delete restrict on update restrict;
           

插入測試資料

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君' ,'男' ,'1974-06-03',95031);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','講師','電子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陳冰','女','1977-08-14','助教','電子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機導論',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'作業系統' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟電路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'機率論' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數學' ,831);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78); 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
           

多表聯查 join

笛卡爾積 Cartesian product

多表查詢是指基于兩個和兩個以上的表的查詢。在實際應用中，查詢單個表可能不能滿足你的需求，如顯示員工表emp中不隻顯示deptno，還要顯示部門名稱，而部門名稱dname在dept表中。

#把兩個表的資料都拼接起來
SELECT * FROM dept,emp
           

上面這種查詢兩個表的方式稱為：笛卡爾積(Cartesian product)，又稱直積。一般笛卡爾積沒有實際的業務意義，但多表查詢都是先生成笛卡爾積，再進行資料的篩選過濾。

這點很值得注意，實際開發中盡量少用多表聯查，其根本原因就在這裡，查詢過程中，現在記憶體中建構一個大大的結果集，然後再進行資料的過濾。那這個建構過程，和所使用的記憶體資源，包括過濾時的判斷，都是既耗費資源，又浪費時間。

這就是阿裡規範中禁止3張表以上的聯查的原因。

三種連接配接 join

• 内連接配接 inner join
• 左（外）連接配接 left join
• 右（外）連接配接 right join

inner join、left join、right join的差別？

• INNER JOIN兩邊都對應有記錄的才展示，其他去掉
• LEFT JOIN左邊表中的資料都出現，右邊沒有資料以NULL填充
• RIGHT JOIN右邊表中的資料都出現，左邊沒有資料以NULL填充