SQL Server資料庫培訓（SQL篇）----Select語句構成元素

標籤：

1.             Select語句構成元素1.1          Select *

避免使用select *

當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 ‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,SQL在解析的過程中, 會將’*’ 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間.

1.2          Select 語句中元素的執行順序

為了描述邏輯查詢處理和各種SELECT查詢子句，我們使用以下案例為大家介紹。

例：查詢出加拿大市場的所有綁定1個以上手機的使用者，並且按使用者ID排序。

select ACCTID,count(*) numPhones

from IFD_PHONE_LIST

where NATION_CODE=‘CA‘

group by ACCTID

having count(*) >1

order by ACCTID

執行順序如下：

1.       FROM           

2.       WHERE          

3.       GTOUP  BY      

4.       HAVING

5.       SELECT

6.       ORDER BY

這條查詢語句會完成以下功能

1.       從IFD_PHONE_LIST表中查詢資料行

2.       對錶中資料進行過濾，只保留市場為CA的記錄

3.       按照使用者ID對資料進行分組

4.       對分組後的資料進行過濾，只保留綁定多個手機的使用者

5.       返回每個分組使用者ID和手機數目

6.       按照使用者ID對輸出結果進行排序

所以如果sql改為

select ACCTID,count(*) numPhones

from IFD_PHONE_LIST

where NATION_CODE=‘CA‘ and numPhones >1

group by ACCTID

having count(*) >1

order by ACCTID

那麼該sql會報錯，因為這時欄位還沒有起別名，該操作是在where語句執行後才會執行。

1.3          DISTINCT

SELECT指令可以查出我們想要的所有資料，但是在這些資料中可能有很多重複的值。

當需要查詢某個表格中有哪些不同的值，而每個值出現的次數並不重要，那麼這時就可以使用DISTINCT

使用方法

SELECT  DISTINCT  “欄位名”  FROM “表格名”

1.4          GROUP BY

Group by 階段可以將前面邏輯查詢處理階段返回的行按“組”進行組合。

例：查詢某個使用者的近6月賬單

select  CYCLE_START as CycleStart,

                CYCLE_END as CycleEnd,

                sum(TOLL_CHARGE) as TollCharge,

                sum(Credit) as Credit,

                sum(Debit) as Debit,

                min(Due_Date) as DueDate

                from CYCLE_CALL_FEE_HISTORY

                where acctid = 39129 and cycle_start>=‘2010-1-1‘ and cycle_end<=‘2010-6-1‘

            group by CYCLE_START, CYCLE_END

因為彙總函式只為每個組返回一個值，所以一個元素如果不在group by 裡表中出現，就只能作為彙總函式（COUNT,SUM,AVG,MIN,MAX）的輸入。但是所有的彙總函式都會忽略NULL值，只有一個例外 COUNT（*）。例如某列的值為 30，10，NULL，10.COUNT（*）會返回4，而COUNT(列名) 將返回3.

1.5          ORDER BY

在一個表中的資料不一定是排序存放的。所以到需要排序時

ORDER BY 語句用於根據指定的列對結果集進行排序。

ORDER BY 語句預設按照升序對記錄進行排序。

如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。

1.6          IS NULL

當要查詢一個欄位為null的記錄時，要使用 is null 而不是 != nul

1.7          NOT

NOT運算子用於對搜尋條件的布爾值求反。這裡討論NOT運算子的使用、應用場合及其與<>運算子的區別。

與其他運算子不同，表示否定的NOT運算子不能單獨應用，而經常與其他運算子聯合使用。例如，NOT IN運算子實際上就是IN運算子與NOT運算子的聯合使用

例: 使用NOT運算子實現查詢

SELECT TNAME, DNAME,AGE, TSEX  FROM TEACHER   WHERE NOT DNAME=‘電腦‘  ORDER BY DNAME

NOT運算子對緊跟其後的條件取反，NOT DNAME=‘電腦’實際上就等價於DNAME<>‘電腦’或者DNAME!=‘電腦’。

需要強調一點，NULL值進行取反，結果仍是NULL.

例 NULL值的取反

如下面的代碼：

SELECT * FROM TEACHER WHERE NOT SAL >1500 OREDR BY SAL

上述執行個體代碼實際上是查詢所有工資不高於1500的教師資訊，從結果中發現，工資為NULL的教師記錄並沒有包括在結果表中。因為當SAL為NULL時，“SAL >1500”的執行結果也為NULL，而NOT NULL的運算結果仍然為NULL，也就不滿足查詢條件。

1.8          Between And

使用BETWEEN...AND 操作符可以選中排列於兩值之間的資料。這些資料可以是數字，文字或是日期。在SQL中 使用相當於 >= 和 <=

1.9          Top

例：查詢出前10掉記錄

select top 10 account_id,plan_id,status

from account_extension

order by account_id

這裡要注意執行順序，查詢出的資料會先進行排序再查出前10行記錄

 

在SQL Server 2005之前的傳統SQL語句中，top語句是不支援局部變數的。此時可以使用Set RowCount,但是在SQL Server 2005/2008中，TOP通常執行得更快，所以應該用TOP關鍵字來取代Set RowCount。

Declare @percentage floatset

@percentage=1

select Top (@percentage) percent PName from [Demo_Top] order by PName

例：利用top 和 percent分頁

查出前1%的資料

select Top 1 percent OrderID from Orders order by OrderID

查出前2%的資料

select Top 2 percent * from Orders  where OrderID not in

( select Top 1 percent OrderID from Orders order by OrderID) order by OrderID

1.10      Table Partition

資料庫結構和索引的是否合理在很大程度上影響了資料庫的效能，但是隨著資料庫資訊負載的增大，對資料庫的效能也發生了很大的影響。可能我們的資料庫在一開始有著很高的效能，但是隨著資料存放區量的急速增長—例如通話記錄資料—資料的效能也受到了極大的影響，一個很明顯的結果就是查詢的反應會非常慢。在這個時候，除了你可以最佳化索引及查詢外，你還可以建立分區表(Table Partition)，在某些場合下提高資料庫的效能，在SQL Server 2008中提供了嚮導形式來建立分區表。

例：在fact_sales表中針對date_id 以月為單位做分區後執行以下語句。

SELECT date_id, SUM(quantity*unit_price) AS total_price

FROM fact_sales

WHERE date_id BETWEEN 20080801 AND 20080831

這時對date_id進行查詢時，會從指定分區表內提取資料從而可以提高查詢效率

1.11      OVER

確定在應用關聯的開窗函數之前，行集的分區和排序

PARTITION BY 參數

將結果集分為多個分區。開窗函數分別應用於每個分區，並為每個分區重新啟動計算。

例：

select orderid,custid,val,

    sum(val) over() as totalvalue,

    sum(val) over(partition by custid) as custtotalval

from ordervalues

查詢結果：

orderid	custid	val	totalvalue	custtotalval
10001	1	800.50	1232323.23	4343.00
10002	1	340.23	1232323.23	4343.00
10003	1	123.31	1232323.23	4343.00
10004	2	343.34	1232323.23	1343.00
10005	2	123.32	1232323.23	1343.00

在Over中 使用 PARTITION BY，並且按照特定欄位排序

例2：

select orderid,custid,val,

    row_number() over(partition by custid

                    order by val) as rownum

from ordervalues

order by custid,val

查詢結果：

orderid	custid	val	rownum
10001	1	100.50	1
10002	1	340.23	2
10003	1	444.23	3
10004	2	123.32	1
10005	2	234.23	2
10006	2	435.34	3

SQL Server資料庫培訓（SQL篇）----Select語句構成元素