SQL语句之函数篇

SQL语句常用函数总结

1 合计统计函数

• AVG

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

• COUNT

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

• FIRST

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

• LAST

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

• MAX

SELECT MAX(OrderPrice) AS MaxOrderPrice FROM Orders

• MIN

SELECT MIN(OrderPrice) AS MinOrderPrice FROM Orders

• SUM

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

• GROUP BY
  语法：
  SELECT column_name, aggregate_function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name

示例：

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

• HAVING
  功能与WEHERE近似，放在GROUP BY后使用，筛选数据

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

2 其它

• UCASE
  把字段的值转换为大写

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

• LCASE
  把字段的值转换为小写

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

• MID
  从文本中提取字符
  语法：
  SELECT MID(column_name,start[,length]) FROM table_name

SELECT MID(City,1,3) as SmallCity FROM Persons

• LEN
  LEN 函数返回文本字段中值的长度

SELECT LEN(City) as LengthOfCity FROM Persons

• ROUND
  用于把数值字段舍入为指定的小数位数。
  语法：
  SELECT ROUND(column_name,decimals) FROM table_name

SELECT ProductName, ROUND(UnitPrice,2) as UnitPrice FROM Products

• NOW
  函数返回当前的日期和时间。

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

• FORMAT
  对字段的显示进行格式化

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products