⑥?全網首發??數據有道之數據庫技術??干貨大全【持續更新】???

目錄

6.1　一般數據查詢功能擴展

考點1　使用TOP限制結果集

考點2　使用CASE函數

考點3　將查詢結果保存到新表中

6.2　查詢結果的并、交、差運算

考點1　并運算

考點2　交運算

考點3　差運算

6.3　相關子查詢

考點1　概　述

考點2　語法格式

考點3　用　途

6.4　其他形式的子查詢

考點1　替代表達式的子查詢

考點2　派生表

6.5　其他一些查詢功能

考點1　開窗函數

考點2　公用表表達式

每文一語

6.1　一般數據查詢功能擴展

考點1　使用TOP限制結果集

（1）適用情況

當使用SELECT語句進行查詢時，有時只希望列出結果集中的前幾行數據，而不是全部數據，這時就可以使用TOP謂詞來限制輸出的結果。

（2）語法格式

TOP n [percent] [WITH TIES]

其中，n為非負整數。

①TOP n：取查詢結果的前n行數據；

②TOP n percent：取查詢結果的前n％行數據；

③WITH TIES：表示包括最后一行取值并列的結果。

TOP謂詞寫在SELECT單詞的后邊（如果有DISTINCT的話，則TOP寫在DISTINCT的后邊）、查詢列表的前邊。

注意：在使用TOP謂詞時，應該與ORDER BY子句一起使用，這樣的前幾名才有意義。當使用WITH TIES選項時，則要求必須使用ORDER BY子句。

目前MySQL并不支持top n的語法，limit n 需要的更加的頻繁

考點2　使用CASE函數

（1）適用情況

可以在查詢語句中使用CASE函數，以達到分情況顯示不同類型的數據的目的。CASE函數是一種多分支表達式，它計算條件列表并返回多個可能的結果表達式中的一個。

（2）類型

簡單CASE函數和搜索CASE函數。

（3）簡單CASE函數

①語法格式

CASE測試表達式

WHEN簡單表達式1 THEN結果表達式1

WHEN簡單表達式2 THEN結果表達式2

…

WHEN簡單表達式n THEN結果表達式n

[EISE結果表達式n+1]

END

②執行過程

a．計算測試表達式，然后按從上到下的順序對每個WHEN子句的簡單表達式進行計算。

b．如果某個簡單表達式的值與測試表達式的值相匹配，則返回與第一個取值為True的WHEN對應的結果表達式的值。

c．如果所有的簡單表達式的值都不與測試表達式的值相匹配，則當指定了ELSE子句時，將返回ELSE子句中指定的結果表達式的值；若沒有指定ELSE子句，則返回NULL值。

（4）搜索CASE函數

①語法格式

搜索CASE函數的各個WHEN子句的布爾表達式可以使用比較運算符，也可以使用邏輯運算符。

②執行過程

搜索CASE函數的執行過程同簡單CASE函數。

③例題

【例1】查詢每種商品的銷售次數，如果銷售次數超過10次，則顯示“熱門商品”，如果銷售次數在5～10次之間，則顯示“一般商品”，如果銷售次數低于5次，則顯示“難銷商品”，如果商品沒有被銷售過，則顯示“滯銷商品”。

分析：由于要查詢的是全部商品的銷售情況，包括有銷售記錄的和沒有銷售記錄的，因此，應該用外連接實現。另外，在統計每種商品的銷售次數時，應該使用COUNT（列名）函數，不能使用COUNT（*）函數，而且應該對外連接后的銷售記錄表中的商品編號列進行統計。

【真題演練】

設SC表中記錄成績的列為：Grade，類型為int。若在查詢成績時，希望將成績按“優”、“良”、“中”、“及格”和“不及格”形式顯示，正確的Case函數是（??? ）。

A．

B．

C．

D．

【答案】C

考點3　將查詢結果保存到新表中

（1）適用情況

當使用SELECT語句查詢數據時，產生的結果是保存在內存中的。如果希望將查詢結果永久保存下來，比如保存在一個表中，則可以通過在SELECT語句中使用INTO子句實現。

（2）INTO子句的語法格式

SELECT查詢列表序列 INTO<新表名>

FROM數據源

…????????? - -其他行過濾、分組等子句

其中“新表名”是要存放查詢結果的表名。這個語句將查詢的結果保存在“新表名”所指定的表中。實際上這個語句包含如下三個功能：

①根據查詢語句列出的列以及其類型創建一個新表；

②執行查詢語句；

③將查詢的結果插入到新表中。

（3）INTO語句創建新表的分類

用INTO子句創建的新表可以是永久表（在執行這個語句時所使用的數據庫中被物理的創建，并且是存儲在磁盤上的表），也可以是臨時表（在tempdb數據庫中創建此表，其生存期是有限的）。臨時表又根據其使用范圍分為局部臨時表和全局臨時表兩種。

①局部臨時表

局部臨時表通過在表名前加一個“#”來標識，比如：#T1，表示“#T1”是一個局部臨時表。局部臨時表的生存期與創建此局部臨時表的用戶的連接生存期相同，它只能在創建此局部臨時表的當前連接中使用。

②全局臨時表

全局臨時表通過在表名前加兩個“#”來標識，比如：##T1，表示“##T1”是一個全局臨時表。全局臨時表的生存期與創建全局臨時表的用戶的連接生存期相同，并且在生存期內可以被所有的連接使用。

可以對局部臨時表和全局臨時表中的數據進行查詢，其方法同永久表一樣。

6.2　查詢結果的并、交、差運算

考點1　并運算

（1）概述

并運算可將兩個或多個查詢語句的結果集合并為一個結果集，這個運算可以使用UNION運算符實現。UNION是一個特殊的運算符，通過它可以實現讓兩個或更多的查詢產生單一的結果集。

（2）UNION與JOIN的區別

UNION操作與JOIN連接操作不同，UNION更像是將一個查詢結果追加到另一個查詢結果中（雖然各數據庫管理系統對UNION操作略有不同，但基本思想是一樣的）。JOIN操作是水平地合并數據（添加更多的列），而UNION是垂直地合并數據（添加更多的行）。

（3）語法格式

SELECT語句1

UNION[ALL]

SELECT語句2

UNION[ALL]

…

SELECT語句n

其中：ALL表示在結果集中包含所有查詢語句產生的全部記錄，包括重復的記錄。如果沒有指定ALL，則系統默認是刪除合并后結果集中的重復記錄。

（4）注意事項

①所有要進行UNION操作的查詢，其SELECT列表中列的個數必須相同，而且對應列的語義應該相同。

②各查詢語句中每個列的數據類型必須與其他查詢中對應列的數據類型是隱式兼容的，即只要它們能進行隱式轉換即可。例如，如果第一個查詢中第二個列的數據類型是char（20），而第二個查詢中第二個列的數據類型是varchar（40）是可以的。

③合并后的結果采用第一個SELECT語句的列標題。

④如果要對查詢的結果進行排序，則

ORDER BY子句應該寫在最后一個查詢語句之后

，且排序的依據列應該是第一個查詢語句中出現的列名。

【真題演練】

下述語句的功能是將兩個查詢結果合并為一個結果，其中正確的是（??? ）。[2014年3月真題]

A．

B．

C．

D．

【答案】B

考點2　交運算

（1）概述

交運算將返回同時在兩個集合中出現的記錄，即返回兩個查詢結果集中各個列的值均相同的記錄，并用這些記錄構成交運算的結果。實現交運算的運算符為INTERSECT。

（2）語法格式

SELECT語句1

INTERSECT

SELECT語句2

INTERSECT

…

SELECT語句n

INTERSECT運算對各查詢語句的要求同UNION運算。

考點3　差運算

（1）概述

差運算將返回在第一個集合中有但第二個集合中沒有的數據。實現差運算的SQL運算符為EXCEPT。

（2）語法格式

SELECT語句1

EXCEPT

SELECT語句2

EXCEPT

…

SELECT語句n

EXCEPT運算對各查詢語句的要求同UNION運算。

6.3　相關子查詢

考點1　概　述

（1）定義

在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。如果一個SELECT語句是嵌套在一個SELECT、INSERT、UPDATE或DELETE語句中，則稱為子查詢或內層查詢，包含子查詢的語句稱為主查詢或外層查詢。

（2）子查詢與外層查詢的關系

一個子查詢也可以嵌套在另外一個子查詢中。為了與外層查詢有所區別，總是把子查詢寫在圓括號中。與外層查詢類似，子查詢語句中也必須包含SELECT子句和FROM子句，并可以根據需要選擇WHERE子句、GROUP BY子句和HAVING子句。

考點2　語法格式

·WHERE表達式 [NOT] IN（子查詢）

·WHERE表達式 比較運算符（子查詢）

·WHERE [NOT] EXISTS（子查詢）

通常，子查詢一般用在外層查詢的WHERE子句或HAVING子句中，與比較運算符或邏輯運算符一起構成查詢條件。對于返回結果為單值的子查詢語句，可以出現在任何允許使用表達式的地方。

考點3　用　途

（1）使用子查詢進行基于集合的測試

①使用子查詢進行基于集合的測試時，通過運算符IN和NOT IN，將一個表達式的值與子查詢返回的結果集進行比較。

②形式為：

WHERE表達式 [NOT] IN（子查詢）

這種形式的子查詢的語句是分步驟實現的，即先執行子查詢，然后在子查詢的結果基礎上再執行外層查詢。子查詢返回的結果實際上就是一個集合，外層查詢就是在這個集合上使用IN運算符進行比較。

（2）使用子查詢進行比較測試

①使用子查詢進行比較測試時，通過比較運算符（=、<>、<、>、<=、>=），將一個表達式的值與子查詢返回的單值進行比較。如果比較運算的結果為True，則比較測試返回True；如果比較運算的結果為False，則比較測試返回False。

②形式為：

WHERE表達式 比較運算符（子查詢）

使用子查詢進行的比較測試要求子查詢語句必須是返回單值的查詢語句。

③注意：由于聚合函數不能出現在WHERE子句中，因此，當一個列的值與一個聚合函數的結果進行比較時，必須用子查詢先得到聚合函數的結果，然后在此結果基礎之上再執行外層查詢的比較。

子查詢的查詢條件不依賴于外層查詢，稱這樣的子查詢為不相關子查詢或嵌套子查詢。

（3）使用子查詢進行存在性測試

使用子查詢進行存在性測試時，通常使用EXISTS謂詞，其形式為：

WHERE [NOT] EXISTS（子查詢）

帶EXISTS謂詞的子查詢不返回查詢的結果，只產生邏輯真值和邏輯假值。

①EXISTS的含義是：當子查詢中有滿足條件的數據時，EXISTS返回真值，否則返回假值。

②NOT EXISTS的含義是：當子查詢中有滿足條件的數據時，NOT EXISTS返回假值，當子查詢中不存在滿足條件的數據時，NOT EXISTS返回真值。

6.4　其他形式的子查詢

考點1　替代表達式的子查詢

替代表達式的子查詢是指在SELECT語句的選擇列表中嵌入一個只返回一個標量值的 SELECT語句，這個查詢語句通常都是通過一個聚合函數來返回一個單值。

考點2　派生表

（1）概述

派生表（有時也稱為內聯視圖）是將子查詢作為一個表來處理，這個由子查詢產生的新表就被稱為“派生表”，可在查詢語句中用派生表來建立與其他表的連接關系，在生成派生表后，在查詢語句中對派生表的操作與普通表一樣。

（2）優點

使用派生表可以簡化查詢，從而避免使用臨時表，而且相比手動生成臨時表的方法性能更優越。

（3）出現

派生表與其他表一樣出現在查詢語句的FROM子句中。例如：

這里的temp就是派生表。

6.5　其他一些查詢功能

考點1　開窗函數

（1）概述

在SQL Server中，一組行被稱為一個窗口，開窗函數是指可以用于“分區”或“分組”計算的函數。這些函數結合OVER子句對組內的數據進行編號，并進行求和、計算平均值等統計。從這個角度來說，SUM、AVG以及ROW_NUMBER（對數據進行編號的函數）等都可以稱為開窗函數。

開窗函數可以分別應用于每個分區，把每個分區看成是一個窗口，并為每個分區進行計算。開窗函數必須放在OVER子句前邊。

（2）分類

排名開窗函數和聚合開窗函數。

（3）將OVER子句與聚合函數結合使用

①使用方法與語法格式

OVER子句用于確定在應用關聯的開窗函數之前對行集的分區和排序。

將OVER子句與聚合函數結合使用的語法格式為：

②參數說明

a．PARTITION BY：將結果集劃分為多個分區。開窗函數分別應用于每個分區，并為每個分區計算函數值。

b．value_expression：指定對行集進行分區所依據的列，該列必須是在FROM子句中生成的列，而且不能引用選擇列表中的表達式或別名。value_expression可以是列表達式、替代表達式的子查詢、標量函數或用戶定義的變量。

注意：可以在單個查詢中使用多個開窗函數，每個函數的OVER子句在分區和排序上可以不同。

（4）將OVER子句與排名函數一起使用

排名函數為分區中的每一行返回一個排名值。根據所用函數的不同，某些行可能與其他行具有相同的排名值。排名函數具有不確定性。

SQL Server提供了四個排名函數：RANK、DENSE_RANK、NTILE和ROW_NUMBER。下面分別介紹這些排名函數。

①RANK（）函數

a．語法格式

b．參數含義

：將FROM子句生成的結果集劃分成排名函數適用的分區。

：指定應用于分區中的行時所基于的排序依據列。

RANK（）函數返回結果集中每行數據在每個分區內的排名。每個分區內行的排名從1開始。

注意：如果排序時有值相同的行，則這些值相同的行具有相同的排名。例如，如果兩位銷售人員有相同的年銷售量，則他們將并列第一，并且下一個銷售人員的排名是第三。因此，RANK（）函數并不一定返回連續整數。

【例2】查詢訂單號、產品號、訂購數量以及每個產品在每個訂單中的訂購數量排名。

分析：該查詢需要用訂單號進行分區，用訂購數量作為排名依據列。

②DENSE_RANK（）函數

DENSE_RANK（）函數與RANK（）函數的作用基本一樣，使用方法也一樣，唯一的區別是DENSE_RANK（）函數的排名中間沒有任何間斷，即該函數返回的是一個連續的整數值。

【例3】將【例2】的查詢改為用DENSE_RANK（）函數實現。

③NTILE（）

a．作用

將有序分區中的行劃分到指定數目的組中，每個組有一個編號，編號從1開始。對于每一行，NTILE（）函數將返回此行所屬的組的編號。

b．語法格式

各參數含義同RANK（）函數。

考點2　公用表表達式

（1）定義

將查詢語句產生的結果集指定一個臨時命名的名字，這些命名的結果集就稱為公用表表達式（CTE）。命名后的公用表表達式后可以在SELECT、INSERT、UPDATE、DELETE等語句中被多次引用。公用表表達式還可以包括對自身的引用，這種表達式稱為遞歸公用表表達式。

（2）優點

①可以定義遞歸公用表表達式。

②使數據操作代碼更加清晰簡潔。

③GROUP BY子句可以直接作用在子查詢所得的標量列上。

④可以在一個語句中多次引用公用表表達式。

（3）語法格式

（4）參數說明

①expression_name：公用表表達式的標識符。expression_name必須與在同一WITH< common_table_expression>子句中定義的任何其他公用表表達式的名稱不同，但該名可以與基本表或視圖名相同。在查詢中對expression_name的任何引用都會使用公用表表達式。

②column_nanle：在公用表表達式中指定列名。在一個CTE定義中不允許出現重名的列名，

③SELECT語句：指定一個用其結果集填充到公用表表達式的SELECT語句。

每文一語

閑下來，才能惰性的靈魂

SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。