讀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
  • 前言 微服務架構已經成為搭建高效、可擴展系統的關鍵技術之一,然而,現有許多微服務框架往往過於複雜,使得我們普通開發者難以快速上手並體驗到微服務帶了的便利。為瞭解決這一問題,於是作者精心打造了一款最接地氣的 .NET 微服務框架,幫助我們輕鬆構建和管理微服務應用。 本框架不僅支持 Consul 服務註 ...
  • 先看一下效果吧: 如果不會寫動畫或者懶得寫動畫,就直接交給Blend來做吧; 其實Blend操作起來很簡單,有點類似於在操作PS,我們只需要設置關鍵幀,滑鼠點來點去就可以了,Blend會自動幫我們生成我們想要的動畫效果. 第一步:要創建一個空的WPF項目 第二步:右鍵我們的項目,在最下方有一個,在B ...
  • Prism:框架介紹與安裝 什麼是Prism? Prism是一個用於在 WPF、Xamarin Form、Uno 平臺和 WinUI 中構建鬆散耦合、可維護和可測試的 XAML 應用程式框架 Github https://github.com/PrismLibrary/Prism NuGet htt ...
  • 在WPF中,屏幕上的所有內容,都是通過畫筆(Brush)畫上去的。如按鈕的背景色,邊框,文本框的前景和形狀填充。藉助畫筆,可以繪製頁面上的所有UI對象。不同畫筆具有不同類型的輸出( 如:某些畫筆使用純色繪製區域,其他畫筆使用漸變、圖案、圖像或繪圖)。 ...
  • 前言 嗨,大家好!推薦一個基於 .NET 8 的高併發微服務電商系統,涵蓋了商品、訂單、會員、服務、財務等50多種實用功能。 項目不僅使用了 .NET 8 的最新特性,還集成了AutoFac、DotLiquid、HangFire、Nlog、Jwt、LayUIAdmin、SqlSugar、MySQL、 ...
  • 本文主要介紹攝像頭(相機)如何採集數據,用於類似攝像頭本地顯示軟體,以及流媒體數據傳輸場景如傳屏、視訊會議等。 攝像頭採集有多種方案,如AForge.NET、WPFMediaKit、OpenCvSharp、EmguCv、DirectShow.NET、MediaCaptre(UWP),網上一些文章以及 ...
  • 前言 Seal-Report 是一款.NET 開源報表工具,擁有 1.4K Star。它提供了一個完整的框架,使用 C# 編寫,最新的版本採用的是 .NET 8.0 。 它能夠高效地從各種資料庫或 NoSQL 數據源生成日常報表,並支持執行複雜的報表任務。 其簡單易用的安裝過程和直觀的設計界面,我們 ...
  • 背景需求: 系統需要對接到XXX官方的API,但因此官方對接以及管理都十分嚴格。而本人部門的系統中包含諸多子系統,系統間為了穩定,程式間多數固定Token+特殊驗證進行調用,且後期還要提供給其他兄弟部門系統共同調用。 原則上:每套系統都必須單獨接入到官方,但官方的接入複雜,還要官方指定機構認證的證書 ...
  • 本文介紹下電腦設備關機的情況下如何通過網路喚醒設備,之前電源S狀態 電腦Power電源狀態- 唐宋元明清2188 - 博客園 (cnblogs.com) 有介紹過遠程喚醒設備,後面這倆天瞭解多了點所以單獨加個隨筆 設備關機的情況下,使用網路喚醒的前提條件: 1. 被喚醒設備需要支持這WakeOnL ...
  • 前言 大家好,推薦一個.NET 8.0 為核心,結合前端 Vue 框架,實現了前後端完全分離的設計理念。它不僅提供了強大的基礎功能支持,如許可權管理、代碼生成器等,還通過採用主流技術和最佳實踐,顯著降低了開發難度,加快了項目交付速度。 如果你需要一個高效的開發解決方案,本框架能幫助大家輕鬆應對挑戰,實 ...