SQL Server指令碼語句

標籤：

 

一、文法結構

select select_list[ into new_table ]from table_source[ where search_condition ][ group by broup_by_expression ][ having search_condition ][order by order_by_expression [ asc | desc ] 

select查詢語句中的主要參數說明如下

select_list:查詢的列或者運算式的列表，用逗號進行分隔。 new_table:新的表名。 table_source:要查詢的表。如果是多個表，用逗號進行分隔。 search_condition:查詢條件。 group_by_expression:分組運算式。 order_by_expression:排序運算式。 asc:升序排序。 desc：降序排序。

select查詢語句字句的功能列表

子句	主要功能	是否必需
select	指定由查詢返回的列	是
from	指定要查詢的表	是
into	建立新表並將結果行插入新表中	否
where	查詢條件	否
group by	對查詢結果進行分組	否
order by	對查詢結果進行排序	否
having	對查詢結果進行篩選	否

 

 

二、挑選清單

　　挑選清單用於定義select語句的結果集中的列

　　1、*　　查詢所有列：

　　select * from person　　

　　*就是結果集合，表示查詢person表中的所有列。

　　2、distinct　　去除重複資料：

　　　distinct是對所有列作用，也就是說，所有列都相同才算重複資料。

　　select distinct name from person

　　3、包含函數的查詢:

　　例如:

　　select count(*) from person

 三、from子句

from子句實際上就是用逗號分隔的表名、視圖名和join字句的列表。使用from子句可以實現如下功能：

1、列出挑選清單和where子句引用的列所在的表和視圖。可以使用as子句為表和視圖指定別名。

2、聯結類型。這些類型由on子句中指定的聯結條件限定。

分配表名時可以使用如下形式

• •  table_name  as  table alias
  •  table_name  as  table_alias

 需要特別說明的是，如果為表分配了別名，那麼T-SQL語句中對該表的所有顯示引用都必須使用別名，而不能使用別名。

 

四、where子句

 where子句可以篩選結果集的源表中的行。帶有where子句的select語句的結構如下：

 　　select <欄位列表> 　　from <表名> 　　where<條件運算式>

 其中，條件運算式是由各種欄位、常量、運算式、關係運算子、邏輯運算子和特殊的運算子組合起來的。

 where子句中的運算子:

 1、關係運算子

　　關係運算子用來表示兩個運算式之間的比較關係。

關係運算子	含義
=	等於
<	小於
>	大於
!=(或<>)	不等於
>=	大於等於
<=	小於等於
!>	不大於
!<	不小於

 

 　　2、邏輯運算子

　　　　邏輯運算子用於表示兩個運算式之間的邏輯關係：

邏輯運算子	含義
not	非(否)
and	與
or	或

 

　　3、特殊運算子

特殊運算子	含義
%	萬用字元，通常與like配合使用
_	萬用字元，代表嚴格的一個字元。where name like ‘_xxx‘將尋找以xxx結尾的所有4個字母的名字(sxxx,dxxx等)
[]	指定範圍([a-f])或集合([abcdefg])中的任何單個字元。where name like ‘[a-f]xxxx‘,將超找以abcdef開頭,xxxx結尾的字元。
[^]	不屬於指定範圍的([a-f])或集合([abcdefg])的任何單個字元。
between	定義一個取值範圍區間，使用and分開。between開始值與and結束值。
like	字串匹配
in	一個欄位的值是否在一組定義的值之中
exists	子查詢有結果集返回(則子查詢返回True)
not exists	子查詢沒有結果集返回(則子查詢返回True)
is null	欄位是否為null
is not null	欄位是否不為null

 在WHERE子句中使用EXISTS(如果使用得當的話)可以大大提高效能。因為使用EXISTS時，只要找到和條件匹配的記錄，SQL Server就立即停止。假設有一個包含一百萬條記錄的表，並且在第三個記錄中找到了匹配的記錄，那麼使用EXISTS選項將避免讀取999997條記錄！NOT EXISTS以同樣的方式工作。

 

五、group by子句

為了最簡單地說明問題，我特地設計了一張這樣的表。

　　

一、GROUP BY單值規則

　　規則1：單值規則，跟在SELECT後面的列表，對於每個分組來說，必須返回且僅僅返回一個值。

　　典型的表現就是跟在SELECT後面的列，如果沒有使用彙總函式，必須出現在GROUP BY子句後面。

　　如下面這個查詢報錯：

　　

　　因為對於按照部門分組之後，技術部分組有3個編號，銷售部分組有2個編號，你讓資料庫顯示哪個呢？

　　如果假設你使用彙總函式COUNT(編號)之後，對於每個部門分組，就只有一個值 - 該部門下的人數：

　　

　　下面來實戰下，我們希望查詢出每個部門，最高工資的那個人的姓名，部門，工資。

　　

　　Shit，出師不利。第一次實戰就錯誤了，我們來分析下。

　　很明顯，上面的姓名列是不符合單值規則的。我們的一廂情願想法是，MAX(工資)之後，SQL Server就能自動幫我們返回不符合單值規則的‘姓名‘。但是很遺憾,SQL Server並沒有這麼做。理由如下：

1. 　　如果兩個人的工資相同，那麼應該將哪個人的姓名返回?
2. 　　如果我們使用的不是MAX()彙總函式，而是SUM、AVG等彙總函式（沒有與之匹配的工資），那麼姓名返回哪個？
3. 　　如果在查詢語句中使用了兩個彙總函式，如MAX(),MIN()。那麼應該返回的是MAX工資的姓名，還是MIN工資的姓名呢？

　　綜上所述，資料庫是不可能能夠根據我們輸入的一個彙總函式，就協助我們判斷並顯示出不符合單值規則的列的。

　　對於MYSQL來說，當有這種不符合單值規則的列時，預設是返回這一組結果的第一條記錄。而SQLite是返回最後一條。

　　因此，對於以上查詢，我們要另尋解決方案。

　　解決方案1：關聯子查詢

SELECT 姓名,部門,工資 FROM 工資表 AS T1WHERE NOT EXISTS (SELECT NULL FROM 工資表 AS T2 WHERE T1.部門 = T2.部門 AND T2.工資 > T1.工資)

　　輸出如下：

　　

　　完全符合要求。對於上面的關聯子查詢，可以理解為：

　　遍曆工資表的所有記錄，尋找不存在比目前記錄部門相同且工資還大的記錄。

　　雖然，關聯子查詢的文法非常簡單，但是效能並不好。因為對於每一條記錄，都要執行一次子查詢。

　　解決方案2：衍生表

　　 使用衍生表的思路是，先執行一個子查詢，得到一個臨時結果集，然後用臨時結果集和原表進行INNER JOIN操作。就能得到最高工資的人的資訊。

　　

　　剛寫出這個SQL語句時，覺得非常妙，理解了之後覺得非常妙。

SELECT 姓名,T1.部門,工資 FROM 工資表 AS T1 INNER JOIN(    SELECT 部門,MAX(工資) AS 最高 FROM 工資表    --執行查詢，先記錄兩個欄位 部門-最高工資    GROUP BY 部門) AS T2        --衍生表T2ON T1.部門 = T2.部門 AND 工資 = 最高

　　衍生表的方式效能優於關聯子查詢，因為衍生表的方式只執行了一次子查詢。但是它需要一張暫存資料表來儲存臨時記錄。因此，這個方案也並不是最佳的解決方案。

　　解決方案3：使用JOIN + IS NULL

　　這是一個更妙的解決方案，當我們用一個外連接去匹配記錄時，當匹配的記錄不存在，就會用NULL來代替相應的列。

　　我們先來看一條非常簡答的SQL語句：

　　

　　從中你看到了什嗎？當T2表中，不存在比T1表中工資高的記錄時就返回NULL。

　　那麼，那麼，那麼一個IS NULL是不是就解決問題了呢？

　　

　　好妙，好妙的方法，讓人拍案叫絕的使用了OUTER JOIN。

　　JOIN解決方案適用於針對大量資料查詢並且可伸縮比較時。它總是能比基於子查詢的解決方案更好地適應資料量的變數。

　　解決方案4：對額外的列使用彙總函式

　　我們知道，GROUP BY時，SELECT列表必須返回的是單值，那麼我們可不可以通過使用彙總函式，讓這個列返回單值呢？答案是可以的。

　　

　　其實，返回的資料是有問題的，當工資相同時，它就返回按姓名從大到小排列的第一個姓名。也就是說，當工資相同時，它只能夠返回一條記錄。

　　我們將彙總函式換成MIN看看。

　　

　　解決方案5：Row_Number() + OVER

　　WITH B AS　　(    　　SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime    　　FROM xxx　　)　　SELECT * FROM B WHERE Part = 1

 

　　輸出如下：

　　

二、HAVING的理解

　　WHERE與HAVING的區別：

• 　　WHERE(分組前過濾)：WHERE不能對彙總函式列進行過濾，因為執行WHERE的時候，分組尚未執行，彙總函式也未執行。
• 　　HAVING(分組後過濾)：主要用於對彙總函式列進行過濾，因為HAVING實在分組之後執行的。HAVING子句只能配合GROUP BY子句使用。沒有GROUP BY子句時不能使用HAVING。

　　錯誤使用WHERE的樣本：

　　

　　正確使用WHERE與HAVING的樣本：

　　

為了最簡單地說明問題，我特地設計了一張這樣的表。

　　

一、GROUP BY單值規則

　　規則1：單值規則，跟在SELECT後面的列表，對於每個分組來說，必須返回且僅僅返回一個值。

　　典型的表現就是跟在SELECT後面的列，如果沒有使用彙總函式，必須出現在GROUP BY子句後面。

　　如下面這個查詢報錯：

　　

　　因為對於按照部門分組之後，技術部分組有3個編號，銷售部分組有2個編號，你讓資料庫顯示哪個呢？

　　如果假設你使用彙總函式COUNT(編號)之後，對於每個部門分組，就只有一個值 - 該部門下的人數：

　　

　　下面來實戰下，我們希望查詢出每個部門，最高工資的那個人的姓名，部門，工資。

　　

　　Shit，出師不利。第一次實戰就錯誤了，我們來分析下。

　　很明顯，上面的姓名列是不符合單值規則的。我們的一廂情願想法是，MAX(工資)之後，SQL Server就能自動幫我們返回不符合單值規則的‘姓名‘。但是很遺憾,SQL Server並沒有這麼做。理由如下：

1. 　　如果兩個人的工資相同，那麼應該將哪個人的姓名返回?
2. 　　如果我們使用的不是MAX()彙總函式，而是SUM、AVG等彙總函式（沒有與之匹配的工資），那麼姓名返回哪個？
3. 　　如果在查詢語句中使用了兩個彙總函式，如MAX(),MIN()。那麼應該返回的是MAX工資的姓名，還是MIN工資的姓名呢？

　　綜上所述，資料庫是不可能能夠根據我們輸入的一個彙總函式，就協助我們判斷並顯示出不符合單值規則的列的。

　　對於MYSQL來說，當有這種不符合單值規則的列時，預設是返回這一組結果的第一條記錄。而SQLite是返回最後一條。

　　因此，對於以上查詢，我們要另尋解決方案。

　　解決方案1：關聯子查詢

SELECT 姓名,部門,工資 FROM 工資表 AS T1WHERE NOT EXISTS (SELECT NULL FROM 工資表 AS T2 WHERE T1.部門 = T2.部門 AND T2.工資 > T1.工資)

　　輸出如下：

　　

　　完全符合要求。對於上面的關聯子查詢，可以理解為：

　　遍曆工資表的所有記錄，尋找不存在比目前記錄部門相同且工資還大的記錄。

　　雖然，關聯子查詢的文法非常簡單，但是效能並不好。因為對於每一條記錄，都要執行一次子查詢。

　　解決方案2：衍生表

　　 使用衍生表的思路是，先執行一個子查詢，得到一個臨時結果集，然後用臨時結果集和原表進行INNER JOIN操作。就能得到最高工資的人的資訊。

　　

　　剛寫出這個SQL語句時，覺得非常妙，理解了之後覺得非常妙。

SELECT 姓名,T1.部門,工資 FROM 工資表 AS T1 INNER JOIN(    SELECT 部門,MAX(工資) AS 最高 FROM 工資表    --執行查詢，先記錄兩個欄位 部門-最高工資    GROUP BY 部門) AS T2        --衍生表T2ON T1.部門 = T2.部門 AND 工資 = 最高

　　衍生表的方式效能優於關聯子查詢，因為衍生表的方式只執行了一次子查詢。但是它需要一張暫存資料表來儲存臨時記錄。因此，這個方案也並不是最佳的解決方案。

　　解決方案3：使用JOIN + IS NULL

　　這是一個更妙的解決方案，當我們用一個外連接去匹配記錄時，當匹配的記錄不存在，就會用NULL來代替相應的列。

　　我們先來看一條非常簡答的SQL語句：

　　

　　從中你看到了什嗎？當T2表中，不存在比T1表中工資高的記錄時就返回NULL。

　　那麼，那麼，那麼一個IS NULL是不是就解決問題了呢？

　　

　　好妙，好妙的方法，讓人拍案叫絕的使用了OUTER JOIN。

　　JOIN解決方案適用於針對大量資料查詢並且可伸縮比較時。它總是能比基於子查詢的解決方案更好地適應資料量的變數。

　　解決方案4：對額外的列使用彙總函式

　　我們知道，GROUP BY時，SELECT列表必須返回的是單值，那麼我們可不可以通過使用彙總函式，讓這個列返回單值呢？答案是可以的。

　　

　　其實，返回的資料是有問題的，當工資相同時，它就返回按姓名從大到小排列的第一個姓名。也就是說，當工資相同時，它只能夠返回一條記錄。

　　我們將彙總函式換成MIN看看。

　　

　　解決方案5：Row_Number() + OVER

　　WITH B AS　　(    　　SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime    　　FROM xxx　　)　　SELECT * FROM B WHERE Part = 1

 

　　輸出如下：

　　

二、HAVING的理解

　　WHERE與HAVING的區別：

• 　　WHERE(分組前過濾)：WHERE不能對彙總函式列進行過濾，因為執行WHERE的時候，分組尚未執行，彙總函式也未執行。
• 　　HAVING(分組後過濾)：主要用於對彙總函式列進行過濾，因為HAVING實在分組之後執行的。HAVING子句只能配合GROUP BY子句使用。沒有GROUP BY子句時不能使用HAVING。

　　錯誤使用WHERE的樣本：

　　

　　正確使用WHERE與HAVING的樣本：

　　

 

六、order by子句

 　　order by子句用於指定結果集的排序

　　1、文法結構：

　　　　select <欄位名列表>　　　　from 資料庫表名　　　　[where <條件運算式>]　　　　[order by[<欄位名或者運算式> [asc|desc],...]]

 　　order by子句可以搭配where子句，也可以和select、fromD搭配使用，而不需要where子句。

　　order by子句的文法如下：

    [ order by { order_by_expression [ asc | desc] } [ ,...n ] ]

其中主要的參數說明如下：

　　order_by_espression:指定要排序的列、列的別名、運算式或者指定為代表挑選清單內的名稱、別名或運算式的位置的負整數。

　　asc:按遞增順序對指定列中的值進行排序。

　　desc:按遞減順序對指定列中的值進行排序。

 

七、having篩選查詢

　　詳見地址：http://www.cnblogs.com/kissdodog/p/3365789.html

八、into查詢

　　into子句將查詢結果產生新表，新表的結構由查詢欄位列表組成。也可以將查詢的結果送入tempdb資料庫的暫存資料表中，這樣關閉伺服器之後暫存資料表會自動刪除。

　　into查詢的文法結構：

　　SELECT <欄位名列表>　　[ into 新的資料表名 ]　　FROM　資料庫表名　　[ where <條件運算式> ]

SQL Server指令碼語句