 # 1. 條件邏輯 ## 1.1. SQL邏輯根據特定列或表達式轉向不同的分支來處理 ## 1.2. 在程式執行 ...
1. 條件邏輯
1.1. SQL邏輯根據特定列或表達式轉向不同的分支來處理
1.2. 在程式執行時從多個路徑中選取一個路徑的能力
1.3. case表達式
1.3.1. 所有的主流資料庫伺服器都提供了旨在模擬大多數編程語言中if-then-else 語句的內建函數
1.3.1.1. Oracle的decode()函數
1.3.1.2. MySQL的if()函數
1.3.1.3. SQL Server的coalesce()函數
1.3.2. case表達式是SQL標準的一部分（SQL92發佈版）
1.3.2.1. 在Oracle Database、SQL Server、MySQL、PostgreSQL、IBM UDB等資料庫中實現
1.3.3. case表達式內建於SQL語法中
1.3.3.1. 用於select、insert、update和delete語句
1.3.4. 搜索型case表達式
1.3.4.1. sql
CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
...
WHEN CN THEN EN
[ELSE ED]
END
1.3.4.2. mysql
-＞ SELECT c.first_name, c.last_name,
-＞ CASE
-＞ WHEN active = 0 THEN 0
-＞ ELSE
-＞ (SELECT count(*) FROM rental r
-＞ WHERE r.customer_id = c.customer_id)
-＞ END num_rentals
-＞ FROM customer c;
1.3.5. 簡單的case表達式
1.3.5.1. simple case expression
1.3.5.2. 類似於搜索型case表達式，但是在靈活性上略遜
1.3.5.3. sql
CASE V0
WHEN V1 THEN E1
WHEN V2 THEN E2
...
WHEN VN THEN EN
[ELSE ED]
END
1.3.5.4. sql
CASE category.name
WHEN 'Children' THEN 'All Ages'
WHEN 'Family' THEN 'All Ages'
WHEN 'Sports' THEN 'All Ages'
WHEN 'Animation' THEN 'All Ages'
WHEN 'Horror' THEN 'Adult'
WHEN 'Music' THEN 'Teens'
WHEN 'Games' THEN 'Teens'
ELSE 'Other'
END
1.4. 結果集變換
1.4.1. mysql
-＞ SELECT monthname(rental_date) rental_month,
-＞ count(*) num_rentals
-＞ FROM rental
-＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01'
-＞ GROUP BY monthname(rental_date);
1.4.2. mysql
-＞ SELECT
-＞ SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1
-＞ ELSE 0 END) May_rentals,
-＞ SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1
-＞ ELSE 0 END) June_rentals,
-＞ SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1
-＞ ELSE 0 END) July_rentals
-＞ FROM rental
-＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';
1.4.3. 如果monthname()函數返回了所需的列值，case表達式返回1；否則，返回0
1.5. 檢查存在性
1.5.1. 只希望確定兩個實體之間是否存在某種關係，而並不關心數量
1.5.2. mysql
-＞ SELECT a.first_name, a.last_name,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'G') THEN 'Y'
-＞ ELSE 'N'
-＞ END g_actor,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'PG') THEN 'Y'
-＞ ELSE 'N'
-＞ END pg_actor,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'NC-17') THEN 'Y'
-＞ ELSE 'N'
-＞ END nc17_actor
-＞ FROM actor a
-＞ WHERE a.last_name LIKE 'S%' OR a.first_name LIKE 'S%';
1.6. 除零錯誤
1.6.1. 執行涉及除法的運算時，應該始終註意確保分母不能為0
1.6.2. mysql
-＞ SELECT c.first_name, c.last_name,
-＞ sum(p.amount) tot_payment_amt,
-＞ count(p.amount) num_payments,
-＞ sum(p.amount) /
-＞ CASE WHEN count(p.amount) = 0 THEN 1
-＞ ELSE count(p.amount)
-＞ END avg_payment
-＞ FROM customer c
-＞ LEFT OUTER JOIN payment p
-＞ ON c.customer_id = p.customer_id
-＞ GROUP BY c.first_name, c.last_name;
1.7. 條件更新
1.7.1. 在更新數據表中的行時，有時候需要根據條件邏輯生成列值
1.7.2. sql
UPDATE customer
SET active =
CASE
WHEN 90 ＜= (SELECT datediff(now(), max(rental_date))
FROM rental r
WHERE r.customer_id = customer.customer_id)
THEN 0
ELSE 1
END
WHERE active = 1;
1.8. 處理null值
1.8.1. null是在列值未知時存儲在數據表中的值，但是在檢索時顯示null值或者null作為表達式的組成部分時未必總是合適
1.8.2. sql
SELECT c.first_name, c.last_name,
CASE
WHEN a.address IS NULL THEN 'Unknown'
ELSE a.address
END address,
CASE
WHEN ct.city IS NULL THEN 'Unknown'
ELSE ct.city
END city,
CASE
WHEN cn.country IS NULL THEN 'Unknown'
ELSE cn.country
END country
FROM customer c
LEFT OUTER JOIN address a
ON c.address_id = a.address_id
LEFT OUTER JOIN city ct
ON a.city_id = ct.city_id
LEFT OUTER JOIN country cn
ON ct.country_id = cn.country_id;
2. 鎖定
2.1. 鎖是資料庫伺服器用來控制數據資源被同時使用的一種機制
2.2. 當部分資料庫被鎖定時，任何打算修改（也可能是讀取）相應數據的用戶必須等到鎖被釋放
2.3. 資料庫的寫操作必須向伺服器發出請求並獲得寫入鎖才能修改數據，而讀操作必鬚髮出請求並獲得讀取鎖才能查詢數據
2.3.1. 資料庫的寫操作必須向伺服器發出請求並獲得寫入鎖才能修改數據，而讀操作必鬚髮出請求並獲得讀取鎖才能查詢數據
2.3.2. 在有較多併發讀寫請求時等待時間過長
2.3.3. Microsoft SQL Server採用
2.3.4. 反映伺服器為報表應用程式發放讀取鎖時的資料庫狀態
2.4. 資料庫的寫操作必須向伺服器發出請求並獲得寫入鎖才能修改數據，而讀操作不需要任何類型的鎖就可以查詢數據，伺服器要確保從查詢開始到結束期間讀操作讀取到的是一致的數據視圖
2.4.1. 版本控制
2.4.2. 在修改數據時存在耗時查詢
2.4.3. Oracle Database採用
2.4.4. 反映報表開始生成時的資料庫狀態
2.5. MySQL則兩者皆用，取決於存儲引擎的選擇
2.6. 鎖的粒度
2.6.1. 數據表鎖
2.6.1.1. 避免多個用戶同時修改同一數據表中的數據
2.6.1.2. 鎖定整個數據表只需要很少的簿記（bookkeeping）
2.6.1.3. 隨著用戶量的增加，該方法會迅速產生無法接受的等待時間
2.6.2. 頁鎖
2.6.2.1. 避免多個用戶同時修改某數據表中同一頁的數據
2.6.2.2. 一頁通常是大小為2～16KB的記憶體段
2.6.3. 行鎖
2.6.3.1. 避免多個用戶同時修改某數據表中同一行的數據
2.6.3.2. 需要更多的簿記
2.6.3.3. 允許多個用戶修改同一數據表，前提是被處理的是不同的行
2.6.3.4. Oracle Database只有行鎖
2.6.3.5. Oracle Database不能提升鎖
2.6.4. 鎖選擇
2.6.4.1. Microsoft SQL Server採用數據表鎖、頁鎖和行鎖
2.6.4.2. SQL Server可以將鎖從行提升至頁，再從頁提升至數據表
2.6.4.3. MySQL則採用數據表鎖、頁鎖或行鎖（同樣取決於存儲引擎的選擇）
3. 事務
3.1. 應用程式邏輯通常包含多個需要作為邏輯工作單元共同執行的SQL語句
3.2. 語句作為整體，要麼執行成功，要麼執行失敗
3.2.1. 原子性
3.3. 資料庫伺服器上線前必須完成的任務之一就是查找伺服器宕機前正在運行但未完成的事務並將其回滾。
3.4. 如果程式完成了事務併發出了commit命令，但還沒有將變更應用於永久性存儲（修改的數據仍然位於記憶體，尚未被寫入磁碟）時伺服器就宕機了，那麼當伺服器重啟時，資料庫伺服器必須重新應用來自事務的變更
3.4.1. 持久性
3.5. 啟動事務
3.5.1. 一個活躍的事務總是和資料庫會話相關聯，所以沒有必要也沒有方法顯式地開始一個事務。當前事務結束後，伺服器會自動為會話啟動一個新的事務
3.5.1.1. Oracle Database採用
3.5.1.2. 哪怕只發出了一個SQL命令，如果對結果不滿意或是改變了主意，也能夠回滾變更
3.5.2. 除非顯式地開始一個事務，否則各個SQL語句會被自動提交，彼此獨立。要想啟動事務，必須先發出一個命令
3.5.2.1. Microsoft SQL Server和MySQL採用
3.5.2.2. 一旦你按下Enter鍵，你的SQL語句所帶來的變更將是永久性的
3.5.2.3. SQL:2003標準提供了start transaction命令，可用於顯式地啟動事務
3.5.2.3.1. MySQL遵守該標準
3.5.2.3.2. SQL Server用戶必須使用替代命令begin transaction
3.5.2.4. MySQL允許關閉單個會話的自動提交模式
3.5.2.4.1. SET AUTOCOMMIT=0
3.5.2.5. SQL Server都允許關閉單個會話的自動提交模式
3.5.2.5.1. SET IMPLICIT_TRANSACTIONS ON
3.5.2.6. 建議每次登錄時關閉自動提交模式，養成在事務內運行所有SQL語句的習慣
3.6. 結束事務
3.6.1. 必須顯式地結束事務，可以通過commit命令來實現
3.6.1.1. 指示伺服器將此變更標記為永久性的並釋放事務中使用的任何資源（頁鎖或者行鎖）
3.6.2. 如果打算撤銷自事務啟動後所做的所有變更，必鬚髮出rollback命令
3.6.2.1. 指示伺服器將數據恢復到事務之前的狀態
3.6.2.2. rollback命令完成後，會話所使用的資源全都會被釋放
3.6.3. 導致事務結束的其他場景
3.6.3.1. 伺服器宕機，在這種情況下，事務會在伺服器重啟時被自動回滾
3.6.3.2. 發出SQL模式語句
3.6.3.2.1. 會使當前事務被提交並啟動一個新的事務
3.6.3.2.2. 資料庫的更改，無論是增加一個新數據表或新索引，還是刪除數據表中的一列，都無法被回滾，因此，更改模式的命令必須出現在事務之外
3.6.3.2.3. 應該註意保護那些組成一個工作單元的語句不被伺服器意外地拆分成多個事務
3.6.3.2.4. 伺服器不會通知你到底發生了什麼
3.6.3.3. 發出另一個start transaction命令，這會造成前一個事務被提交
3.6.3.4. 如果伺服器檢測到死鎖並認定當前事務就是導致死鎖的根源，伺服器就會提前結束當前事務
3.6.3.4.1. 在這種情況下，該事務會被回滾，同時也會接收到錯誤消息
3.6.3.4.2. 如果檢測到死鎖，就要選擇一個事務（任意選擇或者根據某種標準）回滾，以便讓其他事務得以繼續
3.6.3.4.3. 被終止的事務可以重啟，如果沒有再遇到死鎖，就可以順利完成
3.6.3.4.4. 資料庫伺服器會顯示錯誤消息，告知你由於檢測到死鎖，該事務已經被回滾
3.6.3.4.5. 重新嘗試由於檢測到死鎖而被回滾的事務是一種合理的做法
3.6.3.4.6. 如果死鎖出現得過於頻繁，那麼可能需要對訪問資料庫的應用程式進行修改，以降低死鎖的可能性
3.6.3.4.7. 一個常用的策略是確保始終按照同樣的順序訪問數據資源，比如總是在插入交易數據之前修改賬戶數據
3.7. 事務保存點
3.7.1. 可以在事務內創建一個或多個保存點，利用其回滾到事務內的特定位置，而不必一路回滾到事務的啟動點
3.7.2. MySQL資料庫伺服器被設計成可以用多個存儲引擎提供低層級資料庫功能，包括資源鎖定和事務管理
3.7.3. MySQL 8.0版
3.7.3.1. MyISAM
3.7.3.1.1. 採用數據表鎖定的非事務引擎
3.7.3.2. MEMORY
3.7.3.2.1. 用於記憶體數據表（in-memory table）的非事務引擎
3.7.3.3. CSV
3.7.3.3.1. 在逗號分隔的文件（comma-separated file）中存儲數據的事務引擎
3.7.3.4. InnoDB
3.7.3.4.1. 採用行級鎖定的事務引擎
3.7.3.5. Merge
3.7.3.5.1. 使多個相同的MyISAM數據表以單表形式出現（也稱為表分區）的專用引擎
3.7.3.6. Archive
3.7.3.6.1. 用於存儲大量未索引數據的專用引擎，多作為歸檔之用
3.7.3.7. 允許以數據表為單位來選擇存儲引擎
3.7.4. 如果不知道數據表使用的是什麼引擎，可以使用show table命令
3.7.5. sql
START TRANSACTION;
UPDATE product
SET date_retired = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
SAVEPOINT before_close_accounts;
UPDATE account
SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
last_activity_date = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
ROLLBACK TO SAVEPOINT before_close_accounts;
COMMIT;
