全棧必備之SQL簡明手冊

作者：曹洪偉 2023-12-10 20:30:51

SQL是處理數據的強大工具，可以創建有效的查詢來檢索所需的數據。可以說，SQL是數據工程的基礎，熟悉SQL使工作更有效率，更易于維護，并提供在現代數據驅動的工作場所中所需的技能。

從編程語言的視角來看，SQL是一種強大而靈活的語言，具有嵌套特性，允許用戶以高效且簡潔的方式與數據庫進行交互。通過SQL，用戶可以輕松地對數據庫中的數據進行CRUD等操作，從而滿足各種數據處理需求。

1. SQL 的基本原理

作為一種高級的非過程化編程語言，SQL允許用戶在高層數據結構上工作，無需用戶指定對數據的存放方法或了解具體的數據存放方式。無論底層數據庫系統的結構如何不同，都可以使用相同的SQL作為數據輸入與管理的接口，與多種數據庫程序協同工作，如MS Access、DB2、MS SQL Server、Oracle、MySQL、PG等數據庫系統。

SQL的基本原理主要包括如下特點：

數據結構：SQL基于關系模型，數據被組織成表格的形式，每個表格由行和列組成。每行代表一個記錄，每列代表一個屬性。這種表格結構使得數據的存儲、查詢和處理變得方便和高效。
查詢語言：SQL提供了一種豐富的查詢語言，用戶可以通過編寫SQL語句來對數據庫進行操作。SQL語句可以根據用戶的需要進行組合和嵌套，以實現復雜的查詢和操作。
聲明式編程：采用聲明式編程范式，用戶只需指定所需的結果，而無需指定如何獲得這些結果。這意味著用戶只需關注查詢的邏輯，而不必關心具體的實現細節。數據庫系統會根據用戶的查詢語句，自動選擇最優的執行計劃來獲取數據。
數據獨立性：支持數據的物理獨立性和邏輯獨立性。物理獨立性是指數據的存儲結構與應用程序相互獨立，邏輯獨立性是指數據的邏輯結構與應用程序相互獨立。這種獨立性使得數據庫的設計和維護更加靈活和方便。

2. SQL 的基本語句

一般地，我們可以將SQL 語句/命令分成5類：

定義數據庫的元數據格式——DDL，Data Definition Language
操作數據庫中的數據——DML， Data Manipulation Language
數據權限控制——DCL，Data Control Language
數據事務處理——TCL，Transaction Control Lanaguae
數據查詢：DQL : Data Query Language

圖片

根據數據對象實體的不同，常見的28個SQL 語句如下：

建庫：create database db_name
用庫：use db_name
建表：create table table_name{ 列字段描述}
插值入表：insert into table_name values (一行記錄） ...
查表中記錄：select fieldname1,fieldname2,* from table_name
表中增新列：alter table table_name add 列字段描述
更新字段：update table_name set 列字段名= 賦值 where 條件
刪除表中一列：alter table table_name drop column 列字段名
刪除表中行：delete from table_name where 條件
刪除表：drop table table_name
更改某一列的數據類型：alter table table_name alter column 列字段描述
查看特殊行：select top 百分比 percent * from table_name
查看特殊列：select fieldname1, fieldname2 from tablename order by columnnumber desc
創建臨時表：select distinct fieldname1，fieldname2 into temp from tablename order by fieldname1
字段的字符串匹配：select * from tablename where 列字段名 like 'r%'
字段匹配：select * from table_name where 列字段名 in (數據列表)
范圍搜索：select * from table_name where 列字段名 > 目標
判斷字段是否為空：select * from table_name where 列字段名 IS NOT NULL
區間搜索：select * from table_name where 列字段名 between 邊界下限 and 邊界上限
計數：select count(*) as 定義別名 from table_name where 條件
求和/平均值：select sum/average(列字段名) as 定義別名 from table_name where 條件
求極值：select max/min(列字段名) as 定義別名 from table_name where 條件
條件過濾：select * from table_name where 條件1 group by 列字段名 having 條件2 order by 條件3
日期轉換：SELECT CAST('2023-10-20 00:00:00.000' AS date) ；SELECT CONVERT(date,'2023-11-22 00:00:00.000')
條件語句：select * when case1 then result1when case2 then result2else result3from table_name where 條件
字符串處理：TRIM，LTRIM，RTRIM，REPLACE，Substring，LOWER，UPPER
查詢嵌套：在 select 、from、where 中使用 select 語句
存儲過程創建與使用：CREATE PROCEDURE procname 目標語句；exec procname 條件

常見的SQL 語句匯總后如下圖所示(來自 Brij Kishore Pandey）：

圖片

3. 關于JOIN

JOIN用于根據兩個或多個表之間的列之間的關系，從這些表中查詢數據。它允許用戶將不同表中的相關數據連接起來，從而形成一個更完整和有意義的數據集。

圖片

JOIN基于表之間的關聯鍵進行連接操作。這些關聯鍵將不同的表聯系在一起，使得相關的數據能夠被準確地組合在一起。在涉及兩個或多個表時，用戶可以同時查詢多個表中的數據，從而獲得更廣泛和深入的結果。JOIN提供了多種連接類型，如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN等。這些連接類型允許用戶根據不同的需求和數據關系選擇適當的連接方式。

在使用JOIN時，用戶可以指定需要選擇的列，并應用篩選條件，以進一步細化查詢結果。這樣可以確保只返回感興趣的數據，并提高查詢效率。其操作可能會涉及大量的數據，因此在使用時需要考慮性能因素。合理的索引設計、查詢優化和數據庫設計可以提高JOIN操作的性能。

圖片

簡而言之，JOIN是用于關聯和查詢多個表中數據的重要工具，提供了靈活的連接方式和查詢選項，能夠滿足多種數據處理和分析的需求。

5. 關于UNION

在SQL中，JOIN和UNION是兩種不同的操作，盡管都用于合并和處理數據，但在使用方式和結果上存在一些重要的區別。

操作方式：JOIN操作是將兩個或多個表基于它們之間的關系連接起來，它依賴于表之間的關聯鍵。而UNION操作則是將兩個或多個查詢結果集組合成一個結果集。
結果展示：JOIN操作的結果是生成一個新的表，該表包含連接表的所有匹配行。相比之下，UNION操作的結果是將各個查詢結果集合并成一個結果集，不會生成新的表。
列數和數據類型：JOIN操作連接表的列數和數據類型必須匹配，因為它是在表的列之間進行連接。然而，UNION操作要求所有查詢結果集的列數和數據類型必須相同，因為UNION是在查詢結果集之間合并數據。
重復值處理：UNION操作中，默認會刪除重復的結果行，只保留唯一的行。如果需要包含重復的行，可以使用UNION ALL操作。而JOIN操作不會自動刪除重復的行，可能會返回重復的結果，除非使用DISTINCT關鍵字。

使用UNION 的3種典型用法：

結果并集，查看包括重復的記錄：select field1,field2 from tablename1 union all select field1,field2 from tablename2
結果交集，去重并展示相同的數據：select field1,field2 from tablename1 intersect select field1,field2 from tablename2
結果補集，去除相同的數據：select field1,field2 from tablename1 expect select field1,field2 from tablename2

簡單而言，JOIN是用于連接表，而UNION是用于合并查詢結果集。

6. 視圖與臨時表

視圖是一種虛擬表，它提供了一種簡化和安全的數據訪問方式。而臨時表是真實存在的表，它們用于暫存數據，通常在復雜的數據庫操作中使用。

視圖可以簡化復雜的SQL查詢，提供清晰且易于使用的接口。視圖是虛擬的，它不存儲實際的數據，只是對底層表的查詢結果的引用。視圖提供了一種安全機制，因為用戶只能通過視圖訪問特定的數據，而不能直接訪問底層表。另外，視圖可以嵌套，即一個視圖可以引用另一個視圖，這使得數據結構更清晰和模塊化。可以通過“create view view_name 查詢語句”創建視圖，然后就可以通過與表查詢類似的方式查詢數據了。

臨時表是用于在數據庫操作中暫存數據的表，例如用在多步驟的數據轉換過程中。臨時表是真實存在的表，它們存儲實際的數據。臨時表具有臨時性，它們會在會話結束或連接關閉后自動刪除，因此它們不適合存儲需要長期保存的數據。可以通過“CREATE TEMPORARY TABLE temptablename”創建臨時表，然后就可以通過與表查詢類似的方式操作數據了。因此，通常用于存儲中間結果或臨時數據，這樣可以提高復雜查詢的性能和效率。

7. 常見技巧

建立并使用索引

在WHERE子句中使用的列和JOIN子句中的使用列上創建索引，這樣可以加快數據檢索，索引是為了允許快速檢索數據頁而組織的。

CREATE INDEX 索引名 ON 表名 (列名);

使用查詢計劃

通過在實際執行查詢之前運行EXPLAIN命令，我們可以檢查數據庫引擎如何執行查詢，并確定任何潛在的性能瓶頸。查詢執行計劃提供了對數據庫檢索請求數據所需步驟的深入了解，包括使用哪些索引，如何過濾、排序和連接數據。

EXPLAIN SELECT 語句

使用查詢緩存

如果同一查詢多次運行，則可以使用緩存將結果保存在內存中，以加快查詢的執行時間。Enable query caching SET SESSION querycachetype = ON;SET SESSION querycachesize = 緩存大小; SELECT /*+ SQL_CACHE */ column1, column2, ... FROM tablename WHERE 條件;

使用事務

事務是作為單個工作單元執行的一系列操作。事務用于確保對數據庫的一組相關更改同時執行或根本不執行。例如，如果在兩個銀行賬戶之間轉賬，需要確保從一個賬戶提款和向另一個賬戶存款作為一筆交易一起執行。如果其中一個操作失敗，則需要回滾整個事務，以確保數據保持一致狀態。

事務確保了數據庫的一致性和完整性，提供了并發控制和恢復機制。以下是SQL中使用事務的一般步驟：

開始事務：使用BEGIN TRANSACTION語句開始一個新的事務。這標記了事務的起點。例如：BEGIN TRANSACTION;
執行數據庫操作：在事務中，執行需要的數據庫操作語句，如INSERT、UPDATE、DELETE等。這些語句將對數據庫進行更改。例如：INSERT INTO TableName (Column1, Column2) VALUES (Value1, Value2);UPDATE TableName SET Column1 = Value1 WHERE Condition;DELETE FROM TableName WHERE Condition;
判斷操作是否成功：根據需要，可以使用條件語句（如IF）來判斷數據庫操作是否成功。如果操作失敗，可以選擇回滾事務，撤銷之前的所有更改。例如：IF @@ERROR <> 0BEGINROLLBACK TRANSACTION;RETURN;END
提交事務：如果數據庫操作成功，并且你希望將其永久保存，使用COMMIT TRANSACTION語句提交事務。這將使事務中的所有更改永久生效。例如：COMMIT TRANSACTION;

需要注意的是，事務的使用取決于數據庫管理系統（DBMS）的支持和具體的SQL方言。上述步驟提供了一般的指導，但具體的語法和機制可能會因不同的DBMS而有所差異。

8.小結

9. 畫外音

SQL是處理關系型數據的有效方式，但是，數據庫系統的類型確實有很多，在什么場景使用怎樣的數據庫系統呢？下圖給出了一個簡要的指南。

責任編輯：武曉燕來源：喔家ArchiSelf

SQL 工具數據