mySQL多表查詢與事務

HankPeng發表於2019-05-11
MySql

一、正規化

1. 什麼是正規化

1.1 什麼是正規化

  • 正規化：設定一個科學的、規範的資料庫，需要滿足的一些規則

1.2 有哪些正規化

  • 共有：6大正規化

    • 第1正規化：1NF　　滿足最基本的要求

    • 第２正規化：２NF　在１NF基礎上，滿足更多要求

    • 第３正規化：３NF　在２NF基礎上，滿足更多要求

    • 巴斯－科德正規化：BCNF　　在３NF基礎上，滿足更多要求

    • 第４正規化：４NF　　在BCNF基礎上，滿足更多要求

    • 第５正規化：５NF　　在４NF基礎上，滿足更多要求

2. 常用正規化

2.1 第一正規化1NF

  • 所有列不可拆分

2.2 第二正規化2NF

  • 所有列不可拆分

  • 每張表所有列完全依賴於主鍵

2.3 第三正規化3NF

  • 所有列不可拆分

  • 每張表所有列完全依賴於主鍵

  • 每張表裡的欄位，如果要引用其它表資料，要引用其它表的主鍵

二、==多表查詢==

多表查詢的技巧：根據一些條件，把多張表關聯合成一張表，從而把查詢變成單表查詢

-- 1.1 查詢每個員工的資訊，和所有的部門名稱
-- 步驟1：確定資料在哪些表裡  dept, emp
-- 步驟2：使用關聯條件，關聯這些表，合成一張表  dept.id = emp.dept_id
-- 步驟3：從關聯後的表裡，挑、運算、分組、統計 想要的資料
SELECT emp.*, dept.name FROM dept, emp WHERE dept.id = emp.dept_id

1. 迪卡爾積

  • 迪卡爾積：多表查詢時，沒有關係條件，導致了 表的資料毫無意義的排列組合，這個結果叫迪卡爾積

  • 迪卡爾積裡有大量的髒資料，是一定要避免

  • 怎樣避免迪卡爾積：表關聯時，一定要有關聯的條件

2. 查詢方式

2.1 內連線查詢

  • 內連線查詢的是：表之間必定關聯的資料

  • 隱式內連線：select 欄位 from 表1, 表2, ... where 表關聯條件 and 過濾條件

  • 顯式內連線：select 欄位 from 表1 inner join 表2 on 表關聯條件 where 過濾條件

-- 2.1 查詢唐僧的 員工id、姓名、工資、性別、部門名稱：  dept, emp； 關聯條件：dept.id = emp.dept_id
-- 2.1.1 隱式內連線查詢
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept, emp WHERE dept.id = emp.dept_id AND emp.name = '唐僧';
SELECT e.id, e.name, e.salary, e.gender, d.name deptname FROM dept d, emp e WHERE d.id = e.dept_id AND e.name = '唐僧';
​
-- 2.1.2 顯式內連線查詢
SELECT emp.id, emp.name, emp.salary, emp.gender, dept.name deptname FROM dept INNER JOIN emp ON dept.id = emp.dept_id WHERE emp.name = '唐僧';

 

2.2 外連線查詢

  • 外連線查詢的效果是：查詢一張表的全部資料，以及另外一張表的關聯資料

  • 左外連線：

    • 查詢左表的全部資料，及右表的關聯資料

    • select 欄位 from 表1 left join 表2 on 表關聯條件 where 過濾條件

  • 右外連線：

    • 查詢右表的全部資料，及左表的關聯資料

    • select 欄位 from 表1 right join 表2 on 表關聯條件 where 過濾條件


-- 3. 外連線查詢：查詢一張表的全部資料，以及另外一張表的關聯資料
-- 3.1 查詢所有的員工，以其部門資訊
-- 3.1.1 使用左外連線查詢
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
-- 3.1.2 使用右外連線查詢
SELECT * FROM dept d RIGHT JOIN emp e ON e.dept_id = d.id;
-- 3.2 查詢所有的部門，及這個部門裡的員工資訊
-- 3.2.1 使用左外連線查詢
SELECT * FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;
-- 3.2.2 使用右外連線查詢
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

2.3 子查詢

  • 子查詢結果是一個值（一行一列）

  • 子查詢結果是一個集合（多行一列）

  • 子查詢結果是一張虛擬表（多行多列）


-- 3. 子查詢：一種查詢技巧，select巢狀
-- 3.1 查詢最大工資的那個員工資訊
-- 3.1.1 分步驟查詢方式：
SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary = 9000;
-- 3.1.2 把多條語句合併成一條
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
​
-- 3.2 查詢工資大於5000的員工 的部門名稱
-- 3.2.1 使用外連線方式查詢
SELECT d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.2.2 使用子查詢方式
SELECT dept_id FROM emp WHERE salary > 5000;
SELECT NAME FROM dept WHERE id IN (1, 2);
​
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);
​
-- 3.3 查詢工資大於5000的員工資訊和部門名稱
-- 3.3.1 使用外連線方式查詢
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.salary > 5000;
-- 3.3.2 使用子查詢方式
-- 步驟1：先查詢出來工資大於5000的員工資訊：多行多列的結果，相當於一張虛擬表
SELECT * FROM emp WHERE salary > 5000;
-- 步驟2：拿部門表，和工資大於5000的員工資訊虛擬表，進行內連線查詢
SELECT * FROM dept d, (SELECT * FROM emp WHERE salary > 5000) t  WHERE d.id = t.dept_id;

 

三、事務

1. 事務簡介

  • 事務：組成一個事務的多個操作單元，要麼全部成功，要麼全部失敗。

  • 作用：保證組成事務的多個操作，要麼全部成功，要麼全部失敗。

  • 經典使用場景：jack給rose轉賬1000

    1. 開啟事務

    2. jack的帳戶扣錢1000：update

    3. rose的帳戶加錢1000：update

    4. 關閉事務：

      1. 提交事務：事務裡所有的資料變更會生效

      2. 回滾事務：事務裡所有的資料變更會撤消

  • 什麼時候使用事務：

    • 要執行資料變更（無關查詢）

    • 多條SQL的資料變更

2. 事務管理

2.1 手動的事務管理


-- 1. 手動事務管理：jack給rose轉賬100
-- 1.1 開啟事務
START TRANSACTION;
​
-- 1.2 jack扣錢100--- 開啟事務後，執行資料變更，是在快取裡，沒有真正生效
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
​
-- 1.3 rose加錢100
UPDATE account SET balance = balance + 100 WHERE NAME = 'rose';
​
-- 1.4 關閉事務：提交事務，事務裡所有的資料變更會真正的生效
-- commit;
-- 1.4 關閉事務：回滾事務，事務裡所有的資料變更會撤消，不會生效
ROLLBACK;

2.2 自動的事務管理

  • MySql的事務，預設情況下，自動提交是開啟狀態的


-- 2. 自動事務提交
-- 2.1 MySql的事務自動提交的開關操作
-- 2.1.1 關閉自動提交：僅僅是本次連線有效
SET autocommit = 0;
-- 2.1.2 查詢自動提交的開關
SELECT @@autocommit;
-- 2.1.3 開啟自動提交
SET autocommit = 1;
​
​
-- 2.2 自動提交的事務管理
-- 2.2.1 關閉自動提交
SET autocommit = 0;
-- 2.2.2 執行jack扣錢100 -- 資料變更快取起來了
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
-- 2.2.3 執行rose加錢100
UPDATE account SET balance = balance + 100 WHERE NAME = 'rose';
-- 2.2.4 關閉事務：提交事務
-- commit;
-- 2.2.4 關閉事務：回滾事務
ROLLBACK;
​

2.3 事務的回滾點

  • 設定回滾點：savepoint 回滾點名稱

  • 回滾到回滾點：rollback to 回滾點名稱


-- 3.事務的回滾點
-- 3.1 開啟事務
START TRANSACTION;
​
-- 3.2 jack扣錢100, 餘額成：900
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
​
-- 3.3 設定一個回滾點，名稱為point1
SAVEPOINT point1;
​
-- 3.4 jack扣錢100，餘額成：800
UPDATE account SET balance = balance - 100 WHERE NAME = 'jack';
​
-- 3.5 回滾到回滾點point1
ROLLBACK TO point1;
​
-- 3.5 結束事務
COMMIT;

3. 事務的特性和隔離（概念性的）

3.1 ==事務的ACID四大特性(面試題)==

  • A：Atomicity，原子性。事務不可分割，即：事務不可能存在成功一半的情況

  • C：Consistency，一致性。事務提交前後，資料是一致的。

  • I：Isolation，隔離性。多事務併發時，理論是事務之間是完全隔離，互不干擾的

  • D：Durability，永續性。事務一旦提交，就會永久儲存到磁碟檔案上，除非再次更改。

3.2 事務併發的問題

  • 髒讀：一個事務讀取到了另外一個事務未提交的資料。最嚴重的

  • 不可重複讀：一個事務裡多次讀取的資料不一致。受到了其它事務的update干擾

  • 虛讀/幻讀：一個事務裡多次讀取的資料不一致。受到了其它事務的insert/delete干擾

3.3 事務的隔離級別

隔離級別髒讀問題不可重複讀問題虛讀/幻讀問題
read uncommitted ★ ★ ★
read committed ☆ ★ ★
repeatable read ☆ ☆ ★
serializable ☆ ☆ ☆

3.4 事務併發問題的解決（演示）


-- 1. 查詢隔離級別
SELECT @@tx_isolation;
-- 2. 設定隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3.4.1 read uncommitted

  • 準備兩個連線：A（效果演示事務）和B（干擾事務）

  • 設定A的隔離級別為：read uncommitted

  • A和B同時開啟事務

  • A查詢jack的餘額

  • B更新jack的餘額：減100；但是不提交

  • A再次查詢jack的餘額

    • 如果資料變了，演示了髒讀的效果

3.4.2 read committed

  • 準備兩個連線：A（效果演示事務）和B（干擾事務）

  • 設定A的隔離級別為：read committed

  • A和B同時開啟事務

  • A查詢jack的餘額

  • B更新jack的餘額：減100；但是不提交

  • A再次查詢jack的餘額

    • 如果資料沒有變，說明髒讀問題沒有了

  • B提交事務

  • A再次查詢jack的餘額

    • 如果資料變了，說明存在不可重複讀問題

3.4.3 repeatable read

  • 準備兩個連線：A（效果演示事務）和B（干擾事務）

  • 設定A的隔離級別為：repeatable read

  • A和B同時開啟事務

  • A查詢jack的餘額

  • B更新jack的餘額：減100；但是不提交

  • A再次查詢jack的餘額

    • 如果資料沒有變，說明髒讀問題不存在

  • B提交事務

  • A再次查詢jack的餘額

    • 如果資料不變，說明不可重複讀問題解決了

3.4.4 serializable

  • 準備兩個連線：A（效果演示事務）和B（干擾事務）

  • 設定A的隔離級別為serializable

  • A和B同時開啟事務

  • A執行一次查詢；不結束事務

  • B執行一次修改

    • 等待A事務結束，只有A結束了，B才會執行

相關文章