sql的函数列举

sql函数的语法：select function(列) from 表
分为aggregate函数（针对多值操作）和scalar函数（针对单值操作）。
如果select语句中有select语句嵌套，则必须使用group by

avg():

SELECT AVG(column_name) FROM table_name

count():

SELECT COUNT(column_name) FROM table_name  --or (distinct column_name)


first() or last():

SELECT FIRST(column_name) FROM table_name
SELECT LAST(column_name) FROM table_name

max() or min() or sum():

SELECT MAX(column_name) FROM table_name
SELECT MIN(column_name) FROM table_name
SELECT SUM(column_name) FROM table_name

group by:

SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name --按某个列名进行操作


having:

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

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


ucase() or lcase():

SELECT UCASE(column_name) FROM table_name
SELECT LCASE(column_name) FROM table_name

mid() or len():

SELECT MID(column_name,start[,length]) FROM table_name --mid:提取字符

 SELECT LEN(column_name) FROM table_name --len:返回文本长度

round():

SELECT ROUND(column,decimals) FROM table_name --:decimals:小数位，四舍五入
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

now():

SELECT NOW() FROM table_name  --显示当前时间


format():

SELECT FORMAT(column_name,format) FROM table_name

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