SQL AVG 函數(shù)
AVG 函數(shù)返回?cái)?shù)值列的平均值歧强。NULL 值不包括在計(jì)算中鹃祖。
SELECT AVG(column_name) FROM table_name
在上表Orders 中溪椎，算出OrderPrice的平均值
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
找到 OrderPrice 值高于 OrderPrice 平均值的客戶
SELECT Customer FROM Orders?
WHERE OrderPrice > (SELECT AVG(OrderPrice) FROM Orders)
SQL COUNT() 函數(shù)（COUNT() 函數(shù)返回匹配指定條件的行數(shù)）
SQL COUNT(column_name) 語法
COUNT(column_name) 函數(shù)返回指定列的值的數(shù)目（NULL 不計(jì)入）
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) 語法
COUNT(*) 函數(shù)返回表中的記錄數(shù)
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 語法
COUNT(DISTINCT column_name) 函數(shù)返回指定列的不同值的數(shù)目
SELECT COUNT(DISTINCT column_name) FROM table_name
注釋：COUNT(DISTINCT) 適用于 ORACLE 和 Microsoft SQL Server普舆，但是無法用于 Microsoft Access。
舉個(gè)栗子時(shí)間?
計(jì)算客戶 "Carter" 的訂單數(shù)
SELECT COUNT(Customer)?AS CustomerNilsen FROM Orders WHERE Customer='Carter'
計(jì)算 "Orders" 表中不同客戶的數(shù)目
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomer FROM Orders
結(jié)果如下：
SQL FIRST() 函數(shù)
FIRST() 函數(shù)返回指定的字段中第一個(gè)記錄的值校读。
提示：可使用 ORDER BY 語句對(duì)記錄進(jìn)行排序沼侣。
SELECT FIRST(column_name) FROM table_name
查找 "OrderPrice" 列的第一個(gè)值
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
SQL LAST() 函數(shù)
LAST() 函數(shù)返回指定的字段中最后一個(gè)記錄的值。
提示：可使用 ORDER BY 語句對(duì)記錄進(jìn)行排序歉秫。
語法同F(xiàn)IRST()
SQL MAX() 函數(shù)和MIN() 函數(shù) （用法同LAST()）
MAX 函數(shù)返回一列中的最大值蛾洛。NULL 值不包括在計(jì)算中。
SELECT MAX(column_name) FROM table_name
注釋：MIN 和 MAX 也可用于文本列雁芙，以獲得按字母順序排列的最高或最低值轧膘。
SQL SUM() 函數(shù)
SUM() 函數(shù)
SUM 函數(shù)返回?cái)?shù)值列的總數(shù)（總額）。
SELECT SUM(column_name) FROM table_name
SQL GROUP BY 語句
GROUP BY 語句用于結(jié)合合計(jì)函數(shù)兔甘，根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組谎碍。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
查找每個(gè)客戶的總金額（總訂單）
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
GROUP BY 一個(gè)以上的列
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate
SQL HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無法與合計(jì)函數(shù)一起使用洞焙。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
在Orders 表中查找訂單總金額少于 2000 的客戶
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
查找客戶 "Bush" 或 "Adams" 擁有超過 1500 的訂單總金額
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
SQL UCASE() 函數(shù)
UCASE() 函數(shù)? ? ?把字段的值轉(zhuǎn)換為大寫蟆淀。
SELECT UCASE(column_name) FROM table_name
選取 "LastName" 和 "FirstName" 列的內(nèi)容，然后把 "LastName" 列轉(zhuǎn)換為大寫
SELECT UCASE(Lastname),Firstname FROM Persons
SQL LCASE() 函數(shù)??LCASE 函數(shù)把字段的值轉(zhuǎn)換為小寫澡匪。
SQL MID() 函數(shù)
MID 函數(shù)用于從文本字段中提取字符熔任。
SELECT MID(column_name,start[,length]) FROM table_name
從 "City" 列中提取前 3 個(gè)字符
SELECT MID(City,1,3) AS SmallCity FROM Persons
結(jié)果如下：
SQL LEN() 函數(shù)
LEN 函數(shù)返回文本字段中值的長度。
SELECT LEN(column_name) FROM table_name
取得 "City" 列中值的長度
SELECT LEN(City) AS Lengthofcity FROM table_name
SQL ROUND() 函數(shù)
ROUND 函數(shù)用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)唁情。
SELECT ROUND(column_name,decimals) FROM table_name
注釋：column_name必需疑苔。要舍入的字段；decimals必需甸鸟。規(guī)定要返回的小數(shù)位數(shù)惦费。
把名稱和價(jià)格舍入為最接近的整數(shù)
SELECT ProductName,ROUND(UnitPrice,0) as UnitPrice FROM Products
SQL NOW() 函數(shù)
NOW 函數(shù)返回當(dāng)前的日期和時(shí)間。
提示：如果使用 Sql Server 數(shù)據(jù)庫抢韭，用 getdate() 函數(shù)來獲得當(dāng)前的日期時(shí)間趁餐。
SELECT NOW() FROM table_name
在Products表中顯示當(dāng)天的日期所對(duì)應(yīng)的名稱和價(jià)格
SELECT ProductName,UnitPrice,NOW() AS Perdata FROM Products
SQL FORMAT() 函數(shù)
FORMAT 函數(shù)用于對(duì)字段的顯示進(jìn)行格式化。
SELECT FORMAT(column_name,format) FROM table_name
顯示每天日期所對(duì)應(yīng)的名稱和價(jià)格（日期的顯示格式是 "YYYY-MM-DD"）
SELECT ProductName,UnitPrice,FORMAT(NOW(),'YYYY-MM-DD') as Perdata
FROM Products
耶~ 終于學(xué)完啦篮绰，測試竟然竟然錯(cuò)了一道 .... sad
