經典sql函數大全

　　内建 SQL 函數的文法是：

SELECT function(列) FROM 表

　　在 SQL 中，基本的函數類型和種類有若幹種。函數的基本類型是：

Aggregate 函數 Scalar 函數

　　Aggregate 函數的操作面向一系列的值，并傳回一個單一的值。

注釋：如果在 SELECT 語句的項目清單中的衆多其它表達式中使用 SELECT 語句，則這個 SELECT 必須使用 GROUP BY 語句！

"Persons" table (在大部分的例子中使用過)

Name

Age

Adams, John

38

Bush, George

33

Carter, Thomas

28

函數

描述

AVG(column)

傳回某列的平均值

COUNT(column)

傳回某列的行數（不包括 NULL 值）

COUNT(*)

傳回被選行數

FIRST(column)

傳回在指定的域中第一個記錄的值

LAST(column)

傳回在指定的域中最後一個記錄的值

MAX(column)

傳回某列的最高值

MIN(column)

傳回某列的最低值

STDEV(column)

STDEVP(column)

SUM(column)

傳回某列的總和

VAR(column)

VARP(column)

傳回某列的行數

BINARY_CHECKSUM

CHECKSUM

CHECKSUM_AGG

傳回某列的行數（不包括NULL值）

COUNT(DISTINCT column)

傳回相異結果的數目

傳回在指定的域中第一個記錄的值（SQLServer2000 不支援）

傳回在指定的域中最後一個記錄的值（SQLServer2000 不支援）

　　Scalar 函數的操作面向某個單一的值，并傳回基于輸入值的一個單一的值。

UCASE(c)

将某個域轉換為大寫

LCASE(c)

将某個域轉換為小寫

MID(c,start[,end])

從某個文本域提取字元

LEN(c)

傳回某個文本域的長度

INSTR(c,char)

傳回在某個文本域中指定字元的數值位置

LEFT(c,number_of_char)

傳回某個被請求的文本域的左側部分

RIGHT(c,number_of_char)

傳回某個被請求的文本域的右側部分

ROUND(c,decimals)

對某個數值域進行指定小數位數的四舍五入

MOD(x,y)

傳回除法操作的餘數

NOW()

傳回目前的系統日期

FORMAT(c,format)

改變某個域的顯示方式

DATEDIFF(d,date1,date2)

用于執行日期計算

　　

　　AVG 函數傳回數值列的平均值。NULL 值不包括在計算中。

SELECT AVG(column_name) FROM table_name

　　我們擁有下面這個 "Orders" 表：

O_Id

OrderDate

OrderPrice

Customer

1

2008/12/29

1000

Bush

2

2008/11/23

1600

Carter

3

2008/10/05

700

4

2008/09/28

300

5

2008/08/06

2000

Adams

6

2008/07/21

100

現在，我們希望計算 "OrderPrice" 字段的平均值。

我們使用如下 SQL 語句：

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders結果集類似這樣：

OrderAverage

950

現在，我們希望找到 OrderPrice 值高于 OrderPrice 平均值的客戶。

SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)結果集類似這樣：

COUNT() 函數COUNT() 函數傳回比對指定條件的行數。

COUNT(column_name) 函數傳回指定列的值的數目（NULL 不計入）：

SELECT COUNT(column_name) FROM table_name

COUNT(*) 函數傳回表中的記錄數：

SELECT COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 函數傳回指定列的不同值的數目：

SELECT COUNT(DISTINCT column_name) FROM table_name注釋：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是無法用于 Microsoft Access。

　　我們擁有下列 "Orders" 表：

現在，我們希望計算客戶 "Carter" 的訂單數。 我們使用如下 SQL 語句： 

SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter'以上 SQL 語句的結果是 2，因為客戶 Carter 共有 2 個訂單：

CustomerNilsen

SQL COUNT(*) 執行個體 如果我們省略 WHERE 子句，比如這樣： 

SELECT COUNT(*) AS NumberOfOrders FROM Orders結果集類似這樣：

NumberOfOrders

這是表中的總行數。

　　現在，我們希望計算 "Orders" 表中不同客戶的數目。

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders結果集類似這樣：

NumberOfCustomers

這是 "Orders" 表中不同客戶（Bush, Carter 和 Adams）的數目。

FIRST() 函數FIRST() 函數傳回指定的字段中第一個記錄的值。

提示：可使用 ORDER BY 語句對記錄進行排序。

SELECT FIRST(column_name) FROM table_name

現在，我們希望查找 "OrderPrice" 列的第一個值。

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders結果集類似這樣：

FirstOrderPrice

　　LAST() 函數傳回指定的字段中最後一個記錄的值。

SELECT LAST(column_name) FROM table_name

現在，我們希望查找 "OrderPrice" 列的最後一個值。

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders結果集類似這樣：

LastOrderPrice

　　MAX 函數傳回一列中的最大值。NULL 值不包括在計算中。

SELECT MAX(column_name) FROM table_name注釋：MIN 和 MAX 也可用于文本列，以獲得按字母順序排列的最高或最低值。

現在，我們希望查找 "OrderPrice" 列的最大值。

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders結果集類似這樣：

LargestOrderPrice

　　MIN 函數傳回一列中的最小值。NULL 值不包括在計算中。

SELECT MIN(column_name) FROM table_name注釋：MIN 和 MAX 也可用于文本列，以獲得按字母順序排列的最高或最低值。

現在，我們希望查找 "OrderPrice" 列的最小值。

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders結果集類似這樣：

SmallestOrderPrice

　　SUM 函數傳回數值列的總數（總額）。

SELECT SUM(column_name) FROM table_name

現在，我們希望查找 "OrderPrice" 字段的總數。

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders結果集類似這樣：

OrderTotal

5700

GROUP BY 語句合計函數 (比如 SUM) 常常需要添加 GROUP BY 語句。

　　GROUP BY 語句用于結合合計函數，根據一個或多個列對結果集進行分組。

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

現在，我們希望查找每個客戶的總金額（總訂單）。

我們想要使用 GROUP BY 語句對客戶進行組合。

我們使用下列 SQL 語句：

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer結果集類似這樣：

SUM(OrderPrice)

1700

很棒吧，對不對？

讓我們看一下如果省略 GROUP BY 會出現什麼情況：

SELECT Customer,SUM(OrderPrice) FROM Orders結果集類似這樣：

上面的結果集不是我們需要的。

那麼為什麼不能使用上面這條 SELECT 語句呢？解釋如下：上面的 SELECT 語句指定了兩列（Customer 和 SUM(OrderPrice)）。"SUM(OrderPrice)" 傳回一個單獨的值（"OrderPrice" 列的總計），而 "Customer" 傳回 6 個值（每個值對應 "Orders" 表中的每一行）。是以，我們得不到正确的結果。不過，您已經看到了，GROUP BY 語句解決了這個問題。

　　我們也可以對一個以上的列應用 GROUP BY 語句，就像這樣：

SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDate

　　在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與合計函數一起使用。

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

現在，我們希望查找訂單總金額少于 2000 的客戶。

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000結果集類似：

現在我們希望查找客戶 "Bush" 或 "Adams" 擁有超過 1500 的訂單總金額。

我們在 SQL 語句中增加了一個普通的 WHERE 子句：

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500結果集：

　　UCASE 函數把字段的值轉換為大寫。

SELECT UCASE(column_name) FROM table_name

　　我們擁有下面這個 "Persons" 表：

Id

LastName

FirstName

Address

City

John

Oxford Street

London

George

Fifth Avenue

New York

Thomas

Changan Street

Beijing

現在，我們希望選取 "LastName" 和 "FirstName" 列的内容，然後把 "LastName" 列轉換為大寫。

SELECT UCASE(LastName) as LastName,FirstName FROM Persons結果集類似這樣：

ADAMS

BUSH

CARTER

　　LCASE 函數把字段的值轉換為小寫。

SELECT LCASE(column_name) FROM table_name

現在，我們希望選取 "LastName" 和 "FirstName" 列的内容，然後把 "LastName" 列轉換為小寫。

SELECT LCASE(LastName) as LastName,FirstName FROM Persons結果集類似這樣：

adams

bush

carter

　　MID 函數用于從文本字段中提取字元。

SELECT MID(column_name,start[,length]) FROM table_name

參數

column_name

必需。要提取字元的字段。

start

必需。規定開始位置（起始值是 1）。

length

可選。要傳回的字元數。如果省略，則 MID() 函數傳回剩餘文本。

現在，我們希望從 "City" 列中提取前 3 個字元。

SELECT MID(City,1,3) as SmallCity FROM Persons結果集類似這樣：

SmallCity

Lon

New

Bei

　　LEN 函數傳回文本字段中值的長度。

SELECT LEN(column_name) FROM table_name

現在，我們希望取得 "City" 列中值的長度。

SELECT LEN(City) as LengthOfAddress FROM Persons結果集類似這樣：

LengthOfCity

8

7

　　ROUND 函數用于把數值字段舍入為指定的小數位數。

SELECT ROUND(column_name,decimals) FROM table_name

必需。要舍入的字段。

decimals

必需。規定要傳回的小數位數。

　　我們擁有下面這個 "Products" 表：

Prod_Id

ProductName

Unit

UnitPrice

gold

1000 g

32.35

silver

11.56

copper

6.85

現在，我, 們希望把名稱和價格舍入為最接近的整數。

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products結果集類似這樣：

32

12

　　NOW 函數傳回目前的日期和時間。

SELECT NOW() FROM table_name

現在，我們希望顯示當天的日期所對應的名稱和價格。

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products結果集類似這樣：

PerDate

12/29/2008 11:36:05 AM

　　FORMAT 函數用于對字段的顯示進行格式化。

SELECT FORMAT(column_name,format) FROM table_name

必需。要格式化的字段。

format

必需。規定格式。

現在，我們希望顯示每天日期所對應的名稱和價格（日期的顯示格式是 "YYYY-MM-DD"）。

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD, ') as PerDateFROM Products結果集類似這樣：

12/29/2008

本文轉自linzheng 51CTO部落格，原文連結:http://blog.51cto.com/linzheng/1081563