SQL基礎查詢方法

  • 2020 年 2 月 18 日
  • 筆記

正文共：8926 字 5 圖 預計閱讀時間：23 分鐘

本文目錄：

  • 4.1 查詢基礎知識
  • 4.2 SELECT語句組成
  • 4.3 SELECT 語句的邏輯處理順序
  • 4.4 選擇列表
  • 4.4 FROM子句
  • 4.5 PIVOT
  • 4.6 WHERE和HAVING篩選結果
  • 4.7 ORDER BY 排序

4. Select查詢

4.1 查詢基礎知識

查詢是對存儲在 SQL Server 中的數據的一種請求。可以使用下列幾種形式發出查詢：

  • MS Query 或 Microsoft Access 用戶可使用圖形用戶介面 (GUI) 從一個或多個 SQL Server 表中選擇想要查看的數據。
  • 使用 SQL Server Management Studio 或 osql 實用工具的用戶可發出 SELECT 語句。
  • 客戶端或基於中間層的應用程式（如 Microsoft Visual Basic 應用程式）可將 SQL Server 表中的數據映射到綁定控制項（如網格）。

儘管查詢使用多種方式與用戶交互，但它們都完成相同的任務：它們為用戶提供 SELECT 語句的結果集。即使用戶從不指定 SELECT 語句，與使用圖形化工具（如 Visual Studio Query Designer）所經常遇到的情況一樣，客戶端軟體可將每個用戶查詢轉換成發送到 SQL Server 的 SELECT 語句。

SELECT 語句從 SQL Server 中檢索出數據，然後以一個或多個結果集的形式將其返回給用戶。結果集是對來自 SELECT 語句的數據的表格排列。與 SQL 表相同，結果集由行和列組成。

大多數 SELECT 語句都描述結果集的四個主要屬性：

  • 結果集中的列的數量和屬性。對於每個結果集列來說，必須定義下列屬性：
  • 列的數據類型。
  • 列的大小以及數值列的精度和小數位數。
  • 返回到列中的數據值的源。
  • 從中檢索結果集數據的表，以及這些表之間的所有邏輯關係。（From）
  • 為了符合 SELECT 語句的要求，源表中的行所必須達到的條件。不符合條件的行會被忽略。(Where)
  • 結果集的行的排列順序。(Order by)

例如下列 SELECT 語句查找單價超過 $40

SELECT ProductID, Name, ListPrice  FROM Production.Product  WHERE ListPrice > $40  ORDER BY ListPrice ASC

在 SELECT 關鍵字之後所列出的列名（ProductID、Name 和 ListPrice）形成選擇列表。此列表指定結果集有三列，並且每一列都具有 Product 表中相關列的名稱、數據類型和大小。因為 FROM 子句僅指定了一個基表，所以 SELECT 語句中的所有列名都引用該表中的列。

FROM 子句僅列出 Product 這一個表，該表用來檢索數據。

WHERE 子句指定出條件：在 Product 表中，只有 ListPrice 列中的值大於 40的產品的產品ID、名稱以及標價¨G0G在SELECT關鍵字之後所列出的列名（ProductID、Name和ListPrice）形成選擇列表。此列表指定結果集有三列，並且每一列都具有Product表中相關列的名稱、數據類型和大小。因為FROM子句僅指定了一個基表，所以SELECT語句中的所有列名都引用該表中的列。FROM子句僅列出Product這一個表，該表用來檢索數據。WHERE子句指定出條件：在Product表中，只有ListPrice列中的值大於40，該值所在的行才符合 SELECT 語句的要求。

ORDER BY 子句指定結果集將基於 ListPrice 列中的值按照升序進行排序 (ASC)。

4.2 SELECT語句組成

Select的主要子句可歸納如下:

SELECT      [ ALL | DISTINCT ]      [TOP (expression) [PERCENT] [ WITH TIES ] ]      select_list      INTO new_table_name      FROM table_list  [ WHERE search_conditions ]  [ GROUP BY group_by_list ]  [ HAVING search_conditions ]  [ ORDER BY order_list [ ASC | DESC ] ]
  • select_list 描述結果集的列。它是一個逗號分隔的表達式列表。每個表達式同時定義格式（數據類型和大小）和結果集列的數據來源。通常,每個選擇列表表達式都是對數據所在的源表或視圖中的列的引用，但也可能是對任何其他表達式（例如，常量或 Transact-SQL 函數）的引用。在選擇列表中使用 * 表達式可指定返回源表的所有列。
  • INTO new_table_name 指定使用結果集來創建新表。new_table_name 指定新表的名稱。
  • FROM table_list 包含從中檢索到結果集數據的表的列表。這些來源可以是：
  • 運行 SQL Server 的本地伺服器中的基表。
  • 本地 SQL Server 實例中的視圖。SQL Server 在內部將一個視圖引用按照組成該視圖的基表解析為多個引用。
  • 鏈接表。它們是 OLE DB 數據源中的表，稱之為「分散式查詢」。通過將 OLE DB 數據源鏈接為鏈接伺服器，或在 OPENROWSET 或 OPENQUERY 函數中引用數據源，可以從 SQL Server 訪問 OLE DB 數據源。 FROM 子句還可以包含聯接規範。這些聯接規範定義了 SQL Server 在從一個表導航到另一個表時使用的特定路徑。 FROM 子句還用在 DELETE 和 UPDATE 語句中以定義要修改的表。
  • WHERE search_conditions WHERE 子句是一個篩選，只有符合條件的行才向結果集提供數據。 WHERE 子句還用在 DELETE 和 UPDATE 語句中以定義目標表中要修改的行。
  • GROUP BY group_by_list GROUP BY 子句根據 group_by_list 列中的值將結果集分成組。
  • HAVING search_conditions HAVING 子句是應用於結果集的附加篩選。從邏輯上講，HAVING 子句是從應用了任何 FROM、WHERE 或 GROUP BY 子句的 SELECT 語句而生成的中間結果集中篩選行。儘管 HAVING 子句前並不是必須要有 GROUP BY 子句，但 HAVING 子句通常與 GROUP BY 子句一起使用。
  • ORDER BY order_list[ ASC | DESC ] ORDER BY 子句定義了結果集中行的排序順序。order_list 指定組成排序列表的結果集列。關鍵字 ASC 和 DESC 用於指定排序行的排列順序是升序還是降序。 ORDER BY 之所以重要，是因為關係理論規定除非已經指定 ORDER BY，否則不能假設結果集中的行帶有任何序列。如果結果集行的順序對於 SELECT 語句來說很重要，那麼在該語句中就必須使用 ORDER BY 子句。

4.3 SELECT 語句的邏輯處理順序

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE 或 WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

4.4 選擇列表

結果集列的以下特性由選擇列表中的下列表達式定義：

  • 結果集列與定義該列的表達式的數據類型、大小、精度以及小數位數相同。
  • 結果集列的名稱與定義該列的表達式的名稱相關聯。可選的 AS 關鍵字可用於更改名稱，或者在表達式沒有名稱時為其分配名稱。這樣做可以增加可讀性。
  • 結果集列的數據值通過對結果集的每一行相應的表達式求值而得出。

選擇列表還可以包含下列控制結果集最終格式的關鍵字：

  • DISTINCT
    • DISTINCT 關鍵字可從 SELECT 語句的結果中消除重複的行。如果沒有指定 DISTINCT，將返回所有行，包括重複的行。
    • 空值將被認為是相互重複的內容。不論遇到多少個空值，結果中只返回一個 NULL。
  • TOP
    • TOP ( expression ) [ PERCENT ] [ WITH TIES ] ; expression 是指定返回行數的數值表達式，如果指定了 PERCENT，則是指返回的結果集行的百分比（由 expression 指定） TOP (120) /*返回120行*/ TOP (15) PERCENT /* 返回前15%的行結果 */. TOP(@n) /* 返回變數n指定數量的行結果，比如：DECLARE @n AS BIGINT; SET @n = 2 */.

選擇列表中的項包括下列內容：

  • 一個簡單表達式，例如：對函數、變數、常量或者表或視圖中的列的引用。
  • 一個標量子查詢。該 SELECT 語句將每個結果集行計算為單個值。
  • 一個複雜表達式，通過對一個或多個簡單表達式使用運算符而生成。這使結果集中得以包含基表中不存在，但是根據基表中存儲的值計算得到的值。這些結果集列被稱為派生列。
  • 表達式可以包含 $ROWGUID 關鍵字。它解析為對表中具有 ROWGUIDCOL 屬性的列的引用。
  • 對數值列或常量使用算術運算符或函數進行的計算和運算 SELECT ProductID, ROUND( (ListPrice * .9), 2) AS DiscountPrice FROM Production.Product WHERE ProductID = 748;
  • 數據類型轉換（cast） SELECT ( CAST(ProductID AS VARCHAR(10)) + ': ' + Name ) AS ProductIDName FROM Production.Product;
  • CASE 表達式 SELECT ProductID, Name, CASE Class WHEN 'H' THEN ROUND( (ListPrice * .6), 2) WHEN 'L' THEN ROUND( (ListPrice * .7), 2) WHEN 'M' THEN ROUND( (ListPrice * .8), 2) ELSE ROUND( (ListPrice * .9), 2) END AS DiscountPrice FROM Production.Product;
  • 子查詢 SELECT Prd.ProductID, Prd.Name, ( SELECT SUM(OD.UnitPrice * OD.OrderQty) FROM AdventureWorks2008R2.Sales.SalesOrderDetail AS OD WHERE OD.ProductID = Prd.ProductID ) AS SumOfSales FROM Production.Product AS Prd ORDER BY Prd.ProductID;
  • * 符號。如果沒有使用限定符指定，星號 (*) 將被解析為對 FROM 子句中指定的所有表或視圖中的所有列的引用。
  • 變數賦值的格式為：@local_variable = 表達式。SET @local_variable 語句也可用於變數賦值。

4.4 FROM子句

在每一個要從表或視圖中檢索數據的 SELCET 語句中，都需要使用 FROM 子句。使用 FROM 子句可以：

  • 列出選擇列表和 WHERE 子句中所引用的列所在的表和視圖。可以使用 AS 子句為表和視圖的名稱指定別名。
  • 聯接類型。這些類型由 ON 子句中指定的聯接條件限定。

FROM 子句是用逗號分隔的表名、視圖名和 JOIN 子句的列表。

Transact-SQL 具有擴展功能，支援在 FROM 子句中指定除表或視圖之外的其他對象。這些對象返回結果集，也就是 OLE DB 術語中所說的行集，該結果集構成了虛擬表。然後 SELECT 語句就像操作表一樣操作這些結果集。

FROM 子句可以指定

  1. 一個或多個表或視圖
  2. 兩個或多個表或視圖之間的聯接（join）
  3. 一個或多個派生表，這些派生表是 FROM 子句中的 SELECT 語句，由別名或用戶指定的名稱引用。FROM 子句中 SELECT 語句的結果集構成了外層 SELECT 語句所用的表。 SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID INNER JOIN (SELECT AddressID, City FROM Person.Address) AS d ON bea.AddressID = d.AddressID ORDER BY p.LastName, p.FirstName ;
  4. 使用 APPLY 運算符根據左側輸入表中的每一行計算右側的輸入（通常是表值函數），並將所有這些計算的結果合併起來。(資料庫兼容級別必須至少為 90)
  5. 使用 PIVOT 和 UNPIVOT 運算符來改造輸入表。PIVOT 通過將表達式某一列中的唯一值轉換為輸出中的多個列來旋轉表值表達式，並在必要時對最終輸出中所需的任何其餘列值執行聚合。UNPIVOT 與 PIVOT 執行相反的操作，將表值表達式的列轉換為列值。(資料庫的兼容級別需要90以上 )
  6. 用 sp_addlinkedserver 定義的鏈接伺服器中的一個或多個表或視圖。鏈接伺服器可以是任何 OLE DB 數據源。
  7. OPENROWSET 或 OPENQUERY 函數返回的 OLE DB 行集。

不需要 FROM 子句的 SELECT 語句是那些不從資料庫內的任何錶中選擇數據的 SELECT 語句。這些 SELECT 語句只從局部變數或不對列進行操作的 Transact-SQL 函數中選擇數據:

SELECT SYSDATETIME();  SELECT @MyIntVariable;  SELECT @@VERSION;

4.5 PIVOT

-- PIVOT 語法  SELECT <非透視的列>,      [第一個透視的列] AS <列名稱>,      [第二個透視的列] AS <列名稱>,      ...      [最後一個透視的列] AS <列名稱>,    FROM      (<生成數據的 SELECT 查詢>)      AS <源查詢的別名>    PIVOT  (      <聚合函數>(<要聚合的列>)  FOR    [<包含要成為列標題的值的列>]      IN ( [第一個透視的列], [第二個透視的列],      ... [最後一個透視的列])  ) AS <透視表的別名>  <可選的 ORDER BY 子句>;  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4  FROM  (SELECT PurchaseOrderID, EmployeeID, VendorID  FROM Purchasing.PurchaseOrderHeader) p  PIVOT  (  COUNT (PurchaseOrderID)  FOR EmployeeID IN  ( [250], [251], [256], [257] )  ) AS pvt  ORDER BY pvt.VendorID;    /* pivot result  VendorID    Emp1    Emp2    Emp3    Emp4  1492        2       5       4       4  1494        2       5       4       5  1496        2       4       4       5  1498        2       5       4       4  1500        3       4       4       5  */

4.6 WHERE和HAVING篩選結果

SELECT 語句中的 WHERE 和 HAVING 子句可以控制用於生成結果集的源表中的行。WHERE 和 HAVING 是篩選器。這兩個子句指定一系列搜索條件，只有那些滿足搜索條件的行才用於生成結果集。我們稱滿足搜索條件的行包含在結果集中。

HAVING 子句通常與 GROUP BY 子句一起使用來篩選聚合值的結果。但是，也可以不使用 GROUP BY 而單獨指定 HAVING。HAVING 子句指定在 WHERE 子句篩選之後應用的其他篩選器。這些篩選器可應用於選擇列表中使用的聚合函數。

理解應用 WHERE、GROUP BY 和 HAVING 子句的正確順序對編寫高效的查詢程式碼會有所幫助：

  • WHERE 子句用來篩選 FROM 子句中指定的操作所產生的行。
  • GROUP BY 子句用來分組 WHERE 子句的輸出。
  • HAVING 子句用來從分組的結果中篩選行。

WHERE 和 HAVING 子句中的搜索條件或限定條件可以包括：

  • 比較運算符，例如：=、< >、< 和 > SELECT ProductID, Name FROM Production.Product WHERE Class = 'H' ORDER BY ProductID;
  • 範圍（BETWEEN 和 NOT BETWEEN） -- 100到500之間 SELECT ProductID, Name FROM Production.Product WHERE ListPrice BETWEEN 100 and 500 ORDER BY ListPrice;
  • 列表（IN 和 NOT IN） -- 如果不適用IN，就需要用多個or -- 使用IN更簡潔 SELECT ProductID, Name FROM Production.Product WHERE Color IN ('Multi', 'Silver') ORDER BY ProductID;
  • 模式匹配（LIKE 和 NOT LIKE） SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Ch%' ORDER BY ProductID;

like通配符

使用通配符時應著重考慮對性能的影響。如果表達式以通配符開頭，則無法使用索引。（正如在電話簿中進行查找一樣，如果所給的名稱是「%mith」，而不是「Smith」，那麼您將不知道需從電話簿的何處開始搜索。）如果通配符位於表達式內部或位於表達式末尾，則可以使用索引。

  • Null 值（IS NULL 和 IS NOT NULL） SELECT s.Name FROM Sales.Customer c JOIN Sales.Store s ON c.CustomerID = s.CustomerID WHERE c.CustomerID IS NOT NULL ORDER BY s.Name;

比較 null 值時請謹慎從事。例如，指定 = NULL 與指定 IS NULL 是不同的。

  • 所有記錄（=ALL、>ALL、<= ALL、ANY） -- 從其中已發貨的產品量大於任何已發貨的 H 類產品量的 SalesOrderDetail 表中檢索訂單和產品 ID SELECT OrdD1.SalesOrderID, OrdD1.ProductID FROM Sales.SalesOrderDetail OrdD1 WHERE OrdD1.OrderQty > ALL (SELECT OrdD2.OrderQty FROM Sales.SalesOrderDetail OrdD2 JOIN Production.Product Prd ON OrdD2.ProductID = Prd.ProductID WHERE Prd.Class = 'H');
  • 條件的組合（AND、OR、NOT） SELECT ProductID, Name FROM Production.Product WHERE ListPrice < 500 OR (Class = 'L' AND ProductLine = 'S');

4.6.1 搜索通配符字元

當可以搜索通配符字元。有兩種方法可指定平常用作通配符的字元：

使用 ESCAPE 關鍵字定義轉義符。在模式中，當轉義符置於通配符之前時，該通配符就解釋為普通字元。例如，若要搜索字元串中所有的字元串 5%，請使用：

sql_like

  • 將通配符放在方括弧 ([ ]) 中。若要搜索連字元 (-) 而不是使用它指定搜索範圍，請將連字元作為方括弧內的第一個字元： SELECT ColumnA FROM your_table WHERE ColumnA LIKE '9[-]5';

通配符轉義

4.7 ORDER BY 排序

ORDER BY 子句按一列或多列（最多 8,060 個位元組）對查詢結果進行排序。

從 SQL Server 2005 開始，SQL Server 允許在 FROM 子句中指定對 SELECT 列表中未指定的表中的列進行排序。

ORDER BY 子句中引用的列名必須明確地對應於 SELECT 列表中的列或 FROM 子句中的表中的列。如果列名已在 SELECT 列表中有了別名，則 ORDER BY 子句中只能使用別名。同樣，如果表名已在 FROM 子句中有了別名，則 ORDER BY 子句中只能使用別名來限定它們的列。

排序可以是升序的 (ASC)，也可以是降序的 (DESC)。默認為 ASC。

  • 如果 ORDER BY 子句中指定了多個列，則排序是嵌套的。
  • 無法對數據類型為 text、ntext、image 或 xml 的列使用 ORDER BY。
  • ORDER BY 子句的準確結果取決於被排序的列的排序規則。對於 char、varchar、nchar 和 nvarchar 列，可以指定 ORDER BY 操作按照表或視圖中定義的列的排序規則之外的排序規則執行。可以指定 Windows 排序規則名稱或 SQL 排序規則名稱。 -- 使用 Traditional_Spanish 排序規則 SELECT LastName FROM Person.Person ORDER BY LastName COLLATE Traditional_Spanish_ci_ai ASC;

本文項目地址：

https://github.com/firewang/sql50

(喜歡的話，Star一下)

閱讀原文，或者訪問該鏈接可以在線觀看（該系列將更新至GitHub,並且託管到read the docs）

https://sql50.readthedocs.io/zh_CN/latest/

參考網址：

  • https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms190659(v=sql.105

Tags:

VirMach 便宜 VPS