函数的类型:在 SQL 中,基本的函数类型和种类有若干种。函数的基本类型是:Aggregate 函数、Scalar 函数。
Aggregate 函数:操作面向一系列的值,并返回一个单一的值,下面是SQL语句:
--计算 "OrderPrice" 字段的平均值 SELECT AVG(OrderPrice) AS OrderAverage FROM Orders --计算客户 "Carter" 的订单数 SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter' --计算 "Orders" 表中不同客户的数目 SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders --查找 "OrderPrice" 列的最大值 SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders --查找 "OrderPrice" 列的最小值 SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders --查找 "OrderPrice" 字段的总数 SELECT SUM(OrderPrice) AS OrderTotal FROM Orders --查找 "OrderPrice" 列的第一个值 SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders --查找 "OrderPrice" 列的最后一个值 SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
Scalar 函数:操作面向某个单一的值,并返回基于输入值的一个单一的值。下面是SQL语句:
--小写转大写 SELECT upper(name) as LastName FROM tb_test --大写转小写 SELECT LOWER(name) as LastName FROM tb_test --四舍五入 SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products --返回从字符串左边开始指定个数的字符 select LEFT('SQL_Server_2008',4 ); --返回字符串中从右边开始指定个数 select RIGHT('SQL_Server_2008',4 ); --提取指定字符串 select SUBSTRING('SQL_Server_2008',5 ,6); --取余数 select 45%8 --格式转换 select CONVERT(VARCHAR(19),GETDATE()) --字符串长度 select LEN('sqx') --第一个参数是否为空,为空就替代为第二个参数 ISNULL ( check_expression , replacement_value )
参考:SQL 函数