讀SQL學習指南(第3版)筆記09_條件邏輯與事務

来源:https://www.cnblogs.com/lying7/archive/2023/09/03/17674359.html
-Advertisement-
Play Games

![](https://img2023.cnblogs.com/blog/3076680/202309/3076680-20230902225017399-1042005891.png) # 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;

3.7.6. 創建保存點時,除了名稱,什麼都沒有保存。要想使事務具有持久性,最終必鬚髮出commit命令

3.7.6.1. SAVEPOINT my_savepoint;

3.7.7. 如果發出rollback命令的時候沒有指定保存點名稱,事務中的所有保存點都會被忽略,整個事務都會被撤銷

3.7.7.1. ROLLBACK TO SAVEPOINT my_savepoint;

3.7.8. 如果使用的是SQL Server,需要使用專有命令save transaction創建一個保存點,使用rollback transaction命令回滾到某個保存點

3.7.8.1. 後跟保存點的名稱


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 基於`.NET 6.0`創建的`WebAPI`項目,自動生成的`appsettings.json`配置模板,在當前項目(dll)中可以通過構造函數註入`Configuration`來讀取。 ...
  • 為了方便測試非同步,先加個計時 計時相關(可以直接跳過該部分) //開始計時 Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); // 停止計時 stopwatch.Stop(); //輸出計時毫秒數 stopwatch.ElapsedM ...
  • 下麵我們將講解在WPF中使用Blazor,並且使用Blazor做一些文件編輯操作,下麵是需要用到的東西 - WPF - Blazor - Masa Blazor - Monaco ## 安裝Masa Blazor模板 使用`CMD`指令安裝模板 ```shell dotnet new install ...
  • 付費才能得到的好資源,限今天“免費”領取,月薪超30k必備技能! 資源1:高薪熱門【WPF上位機+工業互聯網】從零手寫實戰回覆wpf免費領取 資源2:C#+Halcon機器視覺零基礎實戰教程回覆halcon免費領取 資源3:.Net7 CLR+JIT+MSIL頂級技術視頻教程免費分享回覆dotnet ...
  • # .NET 日誌系統-3 結構化日誌和集中日誌服務 # 系列文章 - [認識.NET 日誌系統 ](https://www.cnblogs.com/ZYPLJ/p/17663487.html) https://www.cnblogs.com/ZYPLJ/p/17663487.html - [.NE ...
  • ## 前言 從今天開始我們開始講【結構型】設計模式,【結構型】設計模式有如下幾種:**適配器模式、橋接模式、組合模式、裝飾模式、外觀模式、享元模式、代理模式**。【創建型】的設計模式解決的是對象創建的問題,那【結構型】設計模式解決的是類和對象的組合關係的問題。 今天我們就開始講【結構型】設計模式裡面 ...
  • # Keepalived高可用集群 ## 高可用集群簡介 **什麼是高可用集群?** 高可用集群 (High Availability;Cluster,簡稱HA Cluster) ,是指以減少服務中斷時間為目的的伺服器集群技術。它通過保護用戶的業務程式對外不間斷提供的服務,把因軟體、硬體、人為造成的 ...
  • 電機控制和Linux驅動開發哪個方向更好呢? 先說結論:任何一個領域,就像世間的五行,陰陽結合,虛實結合,利弊結合。對於哪個更好,不能一概而論,最重要的是要搞清楚,你更適合哪個? 1、共鳴 當我看到這個問題,也確實是我早些年時所面臨的抉擇,不由得過來回答一下,一來表達自己的看法,二來想以此在互聯網上 ...
一周排行
    -Advertisement-
    Play Games
  • 不廢話,直接代碼 private Stack<Action> actionStack = new Stack<Action>(); private void SetCellValues() { var worksheet = Globals.ThisAddIn.Application.ActiveS ...
  • OpenAPI 規範是用於描述 HTTP API 的標準。該標準允許開發人員定義 API 的形狀,這些 API 可以插入到客戶端生成器、伺服器生成器、測試工具、文檔等中。儘管該標準具有普遍性和普遍性,但 ASP.NET Core 在框架內預設不提供對 OpenAPI 的支持。 當前 ASP.NET ...
  • @DateTimeFormat 和 @JsonFormat 是 Spring 和 Jackson 中用於處理日期時間格式的註解,它們有不同的作用: @DateTimeFormat @DateTimeFormat 是 Spring 框架提供的註解,用於指定字元串如何轉換為日期時間類型,以及如何格式化日 ...
  • 一、背景說明 1.1 效果演示 用python開發的爬蟲採集軟體,可自動抓取抖音評論數據,並且含二級評論! 為什麼有了源碼還開發界面軟體呢?方便不懂編程代碼的小白用戶使用,無需安裝python、無需懂代碼,雙擊打開即用! 軟體界面截圖: 爬取結果截圖: 以上。 1.2 演示視頻 軟體運行演示視頻:見 ...
  • SpringBoot筆記 SpringBoot文檔 官網: https://spring.io/projects/spring-boot 學習文檔: https://docs.spring.io/spring-boot/docs/current/reference/html/ 線上API: http ...
  • 作為後端工程師,多數情況都是給別人提供介面,寫的好不好使你得重視起來。 最近我手頭一些活,需要和外部公司對接,我們需要提供一個介面文檔,這樣可以節省雙方時間、也可以防止後續扯皮。這是就要考驗我的介面是否規範化。 1. 介面名稱清晰、明確 顧名思義,介面是做什麼的,是否準確、清晰?讓使用這一眼就能知道 ...
  • 本文介紹基於Python語言,遍歷文件夾並從中找到文件名稱符合我們需求的多個.txt格式文本文件,並從上述每一個文本文件中,找到我們需要的指定數據,最後得到所有文本文件中我們需要的數據的合集的方法~ ...
  • Java JUC&多線程 基礎完整版 目錄Java JUC&多線程 基礎完整版1、 多線程的第一種啟動方式之繼承Thread類2、多線程的第二種啟動方式之實現Runnable介面3、多線程的第三種實現方式之實現Callable介面4、多線的常用成員方法5、線程的優先順序6、守護線程7、線程的讓出8、線 ...
  • 實時識別關鍵詞是一種能夠將搜索結果提升至新的高度的API介面。它可以幫助我們更有效地分析文本,並提取出關鍵詞,以便進行進一步的處理和分析。 該介面是挖數據平臺提供的,有三種模式:精確模式、全模式和搜索引擎模式。不同的模式在分詞的方式上有所不同,適用於不同的場景。 首先是精確模式。這種模式會儘量將句子 ...
  • 1 為啥要折騰搭建一個專屬圖床? 技術大佬寫博客都用 md 格式,要在多平臺發佈,圖片就得有外鏈 後續如博客遷移,國內博客網站如掘金,簡書,語雀等都做了防盜鏈,圖片無法遷移 2 為啥選擇CloudFlare R2 跳轉:https://dash.cloudflare.com/ 有白嫖額度 免費 CD ...