SQL經典實例

第1章檢索記錄

1.1 檢索所有行和列
- 知識點：使用SELECT *快速檢索表中所有列；顯式列出列名（如SELECT col1, col2）提高可讀性和可控性，尤其在編程場景中更清晰。
1.2 篩選行
- 知識點：通過WHERE子句過濾符合條件的行，支持常用運算符（=、<、>等），是數據篩選的基礎。
1.3 查找滿足多個查詢條件的行
- 知識點：使用AND、OR和括號組合多個條件，注意邏輯優先級，確保條件正確分組。
1.4 篩選列
- 知識點：在SELECT中指定具體列，避免返回無關數據，提升查詢效率，尤其在網絡傳輸數據時重要。
1.5 創建有意義的列名
- 知識點：通過AS關鍵字為列設置別名（如SELECT sal AS salary），增強結果可讀性，別名可用于后續引用。
1.6 在WHERE子句中引用別名列
- 知識點：WHERE子句無法直接引用SELECT中的別名，需通過內嵌視圖（子查詢）包裝查詢，使外層查詢可訪問別名列。
1.7 串聯多列的值
- 知識點：不同數據庫使用不同串聯方法，如DB2/Oracle/PostgreSQL用||，MySQL用CONCAT，SQL Server用+，實現多列合并。
1.8 在SELECT語句里使用條件邏輯
- 知識點：通過CASE表達式實現條件邏輯（如根據工資范圍返回狀態），替代傳統編程語言的IF-ELSE，增強查詢靈活性。
1.9 限定返回行數
- 知識點：不同數據庫限制行數的語法不同，如MySQL/PostgreSQL用LIMIT，Oracle用ROWNUM，SQL Server用TOP，DB2用FETCH FIRST，用于分頁或限制結果量。
1.10 隨機返回若干行記錄
- 知識點：結合ORDER BY與隨機函數（如RAND()、DBMS_RANDOM.VALUE()）實現隨機排序，再用行數限制語法獲取隨機行。
1.11 查找Null值
- 知識點：使用IS NULL判斷Null值，不能用=或!=，是處理缺失數據的基礎。
1.12 把Null值轉換為實際值
- 知識點：通過COALESCE函數（如COALESCE(comm, 0)）將Null替換為指定值，或用CASE表達式實現類似邏輯，確保計算正確。
1.13 查找匹配項
- 知識點：使用LIKE配合通配符%（匹配任意字符）和_（匹配單個字符）進行模式匹配，篩選符合特定字符串模式的行。

第2章查詢結果排序

2.1 以指定順序返回查詢結果
- 知識點：通過ORDER BY子句排序，默認升序（ASC），降序用DESC，可按列名或列位置序號排序。
2.2 多字段排序
- 知識點：在ORDER BY中用逗號分隔多個字段，按順序依次排序，支持不同字段不同排序方向。
2.3 依據子串排序
- 知識點：使用字符串函數（如SUBSTR、SUBSTRING）提取子串，按子串內容排序，例如按職位最后兩個字符排序。
2.4 對含有字母和數字的列排序
- 知識點：通過TRANSLATE和REPLACE函數清理非目標字符，保留字母或數字部分，再進行排序，處理混合數據列。
2.5 排序時對Null值的處理
- 知識點：Oracle支持NULLS FIRST/LAST直接控制Null值位置，其他數據庫通過CASE表達式標記Null值，調整其在排序中的順序。
2.6 依據條件邏輯動態調整排序項
- 知識點：在ORDER BY中使用CASE表達式，根據條件動態選擇排序字段，例如按職位類型切換排序依據。

第3章多表查詢

3.1 疊加兩個行集
- 知識點：使用UNION ALL合并多個表的行（包含重復），UNION去重，要求列數和數據類型匹配。
3.2 合并相關行
- 知識點：通過內連接（INNER JOIN或隱式連接）基于共同列合并表，返回匹配行，是多表數據關聯的基礎。
3.3 查找兩個表中相同的行
- 知識點：使用INTERSECT（DB2/PostgreSQL/Oracle）或連接查詢匹配多列，獲取兩表交集數據。
3.4 查找只存在于一個表中的數據
- 知識點：差集運算（DB2/PostgreSQL用EXCEPT，Oracle用MINUS）或NOT IN、NOT EXISTS子查詢，找出單表獨有數據。
3.5 從一個表檢索與另一個表不相關的行
- 知識點：外連接（左/右/全連接）保留主表不匹配行，過濾Null值后獲取無關聯數據，如查找無員工的部門。
3.6 新增連接查詢而不影響其他連接查詢
- 知識點：使用外連接（如LEFT JOIN）避免丟失主表數據，或標量子查詢添加額外信息，確保原有結果集完整。
3.7 確定兩個表是否有相同的數據
- 知識點：通過差集運算結合UNION ALL比較兩表差異，或先比較行數再逐行對比，確保數據一致性。
3.8 識別并消除笛卡兒積
- 知識點：笛卡兒積由缺少連接條件導致，通過n-1個連接條件（WHERE或JOIN）避免，確保結果正確。
3.9 組合使用連接查詢與聚合函數
- 知識點：聚合前注意連接可能產生的重復行，用DISTINCT去重或先聚合再連接，確保統計結果準確。
3.10 組合使用外連接查詢與聚合函數
- 知識點：外連接保留主表數據，聚合時處理Null值（如COALESCE），正確計算包含缺失關聯數據的分組統計。
3.11 從多個表中返回缺失值
- 知識點：全外連接（FULL OUTER JOIN）結合條件過濾，同時保留兩表不匹配行，如同時顯示無員工的部門和無部門的員工。
3.12 在運算和比較中使用Null
- 知識點：COALESCE將Null轉換為有效值參與運算，避免Null導致的邏輯錯誤，確保比較和計算正確。

第4章插入、更新和刪除

4.1 插入新記錄
- 知識點：使用INSERT INTO ... VALUES插入單行或多行，省略列名時需按順序提供所有列值，支持批量插入。
4.2 插入默認值
- 知識點：通過DEFAULT關鍵字顯式插入列默認值，或省略列名利用表定義的默認值，簡化插入操作。
4.3 使用Null覆蓋默認值
- 知識點：在VALUES中顯式指定NULL，即使列有默認值也強制插入Null，控制數據輸入。
4.4 復制數據到另一個表
- 知識點：INSERT INTO ... SELECT將查詢結果插入目標表，支持過濾條件，快速遷移數據。
4.5 復制表定義
- 知識點：DB2用CREATE TABLE ... LIKE，其他數據庫用SELECT * FROM ... WHERE 1=0創建空表，復制表結構。
4.6 多表插入
- 知識點：Oracle用INSERT ALL根據條件插入多表，DB2通過UNION ALL結合表約束實現，其他數據庫需分步插入。
4.7 禁止插入特定列
- 知識點：創建僅暴露允許插入列的視圖，限制用戶操作，通過視圖權限控制數據插入范圍。
4.8 更新記錄
- 知識點：UPDATE ... SET結合WHERE更新指定行，支持表達式計算（如加薪10%），預覽結果后執行。
4.9 當相關行存在時更新記錄
- 知識點：子查詢IN或EXISTS判斷關聯表存在性，針對性更新，如根據獎金表更新工資。
4.10 使用另一個表的數據更新記錄
- 知識點：連接兩表后更新（如UPDATE ... FROM ... JOIN），或子查詢提供新值，實現跨表數據同步。
4.11 合并記錄
- 知識點：Oracle的MERGE語句根據匹配條件自動插入或更新，簡化數據同步邏輯，其他數據庫需分步操作。
4.12 刪除全表記錄
- 知識點：DELETE FROM不帶WHERE刪除所有行，注意事務和性能影響，大表慎用。
4.13 刪除指定記錄
- 知識點：DELETE ... WHERE結合條件過濾，確保精確刪除，避免誤刪數據。
4.14 刪除單行記錄
- 知識點：基于主鍵或唯一鍵精確刪除，確保WHERE條件唯一，避免刪除多行。
4.15 刪除違反參照完整性的記錄
- 知識點：NOT EXISTS或NOT IN子查詢找出無關聯的孤立記錄，如刪除無對應部門的員工。
4.16 刪除重復記錄
- 知識點：按重復列分組，保留最小/最大標識的行，刪除其他重復行，確保數據唯一性。
4.17 刪除被其他表參照的記錄
- 知識點：先處理子表依賴數據（如級聯刪除），或直接刪除主表記錄（需數據庫支持外鍵級聯），處理外鍵約束。

第5章元數據查詢

5.1 列舉模式中的表
- 知識點：查詢系統表或視圖（如DB2的SYSCAT.TABLES，Oracle的ALL_TABLES，信息模式INFORMATION_SCHEMA.TABLES）獲取表列表。
5.2 列舉字段
- 知識點：通過系統視圖獲取列信息（如數據類型、位置），如DB2的SYSCAT.COLUMNS，Oracle的ALL_TAB_COLUMNS。
5.3 列舉索引列
- 知識點：查詢索引相關系統表（如DB2的SYSCAT.INDEXES，Oracle的ALL_IND_COLUMNS），了解表的索引結構。
5.4 列舉約束
- 知識點：通過系統視圖獲取約束信息（主鍵、外鍵、檢查約束等），如SYSCAT.TABCONST（DB2）、ALL_CONSTRAINTS（Oracle）。
5.5 列舉非索引外鍵
- 知識點：結合索引和外鍵系統表，篩選未建立索引的外鍵列，優化數據庫性能。
5.6 用SQL生成SQL
- 知識點：通過字符串拼接動態生成SQL腳本（如統計行數、禁用約束），實現自動化維護任務。
5.7 描述Oracle數據字典視圖
- 知識點：利用Oracle的DICTIONARY和DICT_COLUMNS視圖，快速了解數據字典視圖的結構和用途。

第6章字符串處理

6.1 遍歷字符串
- 知識點：通過笛卡兒積生成多行，用SUBSTR逐字符提取，模擬循環處理字符串，是字符串解析的基礎。
6.2 嵌入引號
- 知識點：在字符串中用兩個連續引號表示單個引號（如''），處理包含引號的數據。
6.3 統計字符出現的次數
- 知識點：通過LENGTH和REPLACE計算原字符串與替換后字符串的長度差，除以目標字符長度，統計出現次數。
6.4 刪除不想要的字符
- 知識點：TRANSLATE替換目標字符為統一符號，再用REPLACE刪除，或多次REPLACE逐個刪除指定字符。
6.5 分離數字和字符數據
- 知識點：TRANSLATE將數字或字符轉換為統一符號，REPLACE刪除非目標符號，分離混合數據列。
6.6 判斷含有字母和數字的字符串
- 知識點：TRANSLATE將字母數字轉換為單一字符，比較轉換后字符串是否全由該字符組成，或用正則表達式（如MySQL的REGEXP）篩選。
6.7 提取姓名的首字母
- 知識點：通過TRANSLATE、REPLACE處理非字母字符，提取首字母并拼接，處理不同格式的姓名。
6.8 按照子字符串排序
- 知識點：用SUBSTR提取子串，在ORDER BY中按子串排序，如按姓名最后兩個字符排序。
6.9 根據字符串里的數字排序
- 知識點：清理非數字字符（如TRANSLATE替換為數字），轉換為數值類型后排序，處理混合數字的字符串。
6.10 創建分隔列表
- 知識點：不同數據庫用不同方法，如MySQL的GROUP_CONCAT，Oracle的SYS_CONNECT_BY_PATH，拼接多行數據為逗號分隔字符串。
6.11 分隔數據轉換為多值IN列表
- 知識點：拆分分隔字符串為多行，轉換為數值后用于IN子句，處理輸入的列表數據。
6.12 按字母表順序排列字符
- 知識點：遍歷字符并排序，用聚合函數拼接，實現字符串內字符的排序。
6.13 識別字符串里的數字字符
- 知識點：TRANSLATE標記數字字符，篩選包含數字的行，或提取純數字部分，處理混合數據。
6.14 提取第n個分隔子字符串
- 知識點：利用SUBSTRING_INDEX（MySQL）、SPLIT_PART（PostgreSQL）或INSTR結合SUBSTR，按分隔符提取指定位置子串。
6.15 解析IP地址
- 知識點：按.分隔符拆分IP地址為四部分，用字符串函數提取各段數值，處理網絡地址數據。

第7章數值處理

7.1 計算平均值
- 知識點：AVG函數忽略Null值，分組計算（GROUP BY）各部門平均值，處理聚合統計。
7.2 查找最小值和最大值
- 知識點：MIN和MAX函數獲取列極值，支持分組統計，忽略Null值，是基本聚合函數。
7.3 求和
- 知識點：SUM函數累加數值列，支持分組（GROUP BY），忽略Null值，處理數據匯總。
7.4 計算行數
- 知識點：COUNT(*)統計所有行，COUNT(col)統計非Null值行數，分組統計各部門人數。
7.5 計算非Null值的個數
- 知識點：COUNT(col)自動忽略Null，直接獲取有效數據行數，如統計有獎金的員工數。
7.6 累計求和
- 知識點：窗口函數SUM OVER（DB2/Oracle）或標量子查詢（其他數據庫），按順序累加值，生成運行總計。
7.7 計算累計乘積
- 知識點：利用對數轉換和指數運算（LN+EXP）實現累計乘積，或Oracle的MODEL子句，處理數值連乘。

后續章節（8-14章及附錄）

第8-14章：涵蓋日期運算、日期處理、區間查詢、高級查詢、報表生成、層次查詢等，涉及窗口函數、遞歸查詢、數據透視等高級技術，針對不同數據庫的特性提供解決方案。
附錄A：窗口函數簡介，解釋分組、聚合、分區等概念，是理解高級查詢的基礎。
附錄B：重溫經典SQL問題，結合新特性（如窗口函數）提供優化方案，提升查詢效率。