5.数据库函数Functions
5.1转换函数Data Convert Functions
5.2聚集函数Aggregate Functions
5.3字符函数char Functions
5.4日期函数Date Functions
5.5数学函数Math Functions
5.6分析函数Analytical Functions
-----------------------------------------------------------
5.1转换函数Data Convert Functions
5.1.1 CAST()
功能:数据类型转换
语法:CAST(expression AS data_type)
代码:
SELECT BillingDate,
BillingTotal,
CAST(BillingDate AS varchar) AS varcharDate,
CAST(BillingTotal AS int) AS integerTotal,
CAST(BillingTotal AS varchar) AS varcharTotal
FROM Billings
-----------------------------------------------------------
5.1.2 COALESCE()
功能:返回表达式列表中第一个非空值表达式的值
语法:COALESCE(expression1, expression2, ... expressionN)
代码:
SELECT BankerName,
COALESCE(CAST(BillingTotal AS varchar), 'No Billings') AS BillingTotal
FROM Bankers LEFT JOIN Billings
ON Bankers.BankerID = Billings.BankerID
ORDER BY BankerName
-----------------------------------------------------------
5.1.3 CONVERT()
功能:把表达式值转换为指定sytle的数据类型
语法:CONVERT(data_ type(<length>), expression, <style>)
代码:
//日期风格转换
datetime转指定日期格式style number清单
Number Style Number Output Type Style
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yyyy
2 102 ANSI yyyy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yyyy
5 105 Italian dd-mm-yyyy
6 106 - dd mon yyyy
7 107 - mon dd, yyyy
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
14 114 - hh:mi:ss:mmm (24h)
//字符串转数字
CONVERT (INTEGER , '12345')
//字符转日期
CONVERT(datetime, '20000704')
CREATE TABLE my_date (Col1 datetime)
GO
INSERT INTO my_date VALUES (CONVERT(char(10), GETDATE(), 112))
GO
drop table my_date;
GO
-----------------------------------------------------------
5.1.4 ISNULL()
功能:检查check_expression是空值，就用replacement_value替代
语法:ISNULL(check_expression, replacement_value)
代码:
SELECT BillingDate,
ISNULL(BillingDate, '1900-01-01') AS NewDate
FROM Billings
-----------------------------------------------------------
5.1.5 NULLIF()
功能:两个表达式相等，返回null,否则返回第1个表达式
语法:ISNULL(expression1, expression2)
代码:
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 955
SELECT NULLIF(@Value1, @Value2)
GO
输出
55
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 55
SELECT NULLIF(@Value1, @Value2)
GO
输出
NULL
-----------------------------------------------------------
5.2聚集函数Aggregate Functions
语法:select AggregateFunctions(column-name)
sum(column-name):计算字段总和
avg(column-name):计算字段平均值
min(column-name):计算字段最小值
max(column-name):计算字段最大值
count(column-name):计算字段非空值的个数
count(*):计算查询结果的记录个数
代码:
//use pubs
select sum(qty) as sum_qty,
avg(qty) as avg_qty,
min(qty) as min_qty,
max(qty) as max_qty,
count(qty) as count_qty,
count(*) as total_qty
from sales
-----------------------------------------------------------
5.3字符函数char Functions
1. ASCII()
//函数返回字符表达式最左端字符的ASCII 码值
2. Char()
//函数用于将ASCII 码转换为字符--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL
3. CHARINDEX()
//函数返回字符串中某个指定的子串出现的开始位置
4. DIFFERENCE()
5. FORMATMESSAGE()
6. LEFT()
7. LEN()
8. LOWER()
//函数把字符串全部转换为小写
9. LTRIM()
//函数把字符串头部的空格去掉
10.nchar()
11.PATINDEX()
12.QUOTENAME()
13.REPLACE()
//函数返回被替换了指定子串的字符串
14.REPLICATE()
/函数返回一个重复指定次数的字符串
15.REVERSE()
//函数将指定的字符串的字符排列顺序颠倒
16.Right()
17.RTRIM()
/函数把字符串尾部的空格去掉
18.SOUNDEX()
19.SPACE()
//函数返回一个有指定长度的空白字符串
20.STR()
//函数把数值型数据转换为字符型数据
21.STUFF()
//函数用另一子串替换字符串指定位置长度的子串
22.SUBSTRING()
//函数返回子字符串
23.UNICODE()
24.UPPER()
//函数把字符串全部转换为大写
-----------------------------------------------------------
5.4日期函数Date Functions
5.4.1. CURRENT_TIMESTAMP
功能:
得到当前数据库的日期
代码:
//直接得到当前日期
SELECT CURRENT_TIMESTAMP
go
//调用变量中的当前日期
DECLARE @today datetime
SELECT @today = current_timestamp
select @today
go
-----------------------------------------------------------
5.4.2. 日期计算Date calculation
功能:日期计算
代码:
DECLARE @MonthChar VarChar(2), @DayChar VarChar(2), @DateOut Char(8)
SET @MonthChar = CAST(MONTH(GETDATE()) AS VarChar(2))
SET @DayChar = CAST(DAY(GETDATE()) AS VarChar(2))
--自动补齐月份到2位
IF LEN(@MonthChar) = 1
SET @MonthChar = '0'+@MonthChar
IF LEN(@DayChar) = 1
SET @DayChar = '0' + @DayChar
--生成日期字符串
SET @DateOut = @MonthChar + @DayChar + CAST(YEAR(GETDATE()) AS Char(4))
SELECT @DateOut
GO
运行结果是mmddyyyy格式的字符串
-----------------------------------------------------------
5.4.3. DATEADD()
功能:日期相加或者相减n天后的日期
语法:DATEADD(what_to_add,number_to_add,date_to_add_it_to)
代码:
//4-29-2009加90天，保存到day
SELECT DATEADD(DY, 90,'4-29-2009')
GO
//4-29-2009减60天，保存到day
SELECT DATEADD(DY, -60,'4-29-2009')
GO
-----------------------------------------------------------
5.4.4. DATEDIFF()
功能:日期相加或者相减n天后的日期
语法:DATEDIFF ( datepart , startdate , enddate )
datepart列表:
day:单位=天
month:单位=月
year:单位=年
hour:单位=小时
minute:单位=分
second:单位=秒
week:单位=周
代码:
//10/01/2009国庆到今天的天数
SELECT DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到今天的月数
SELECT DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到今天的年数
SELECT DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到今天的周数
SELECT DATEDIFF(week,'10/1/2009',CURRENT_TIMESTAMP)
GO
-----------------------------------------------------------
5.4.5. DATEFIRST()
功能:设置或者查询一周的第一天
SELECT @@DATEFIRST 'First Day of the Week'
GO
value is 7
SELECT DATEPART(weekday, CAST('20091001' AS DATETIME) + @@DATEFIRST);
GO
value is 3
-----------------------------------------------------------
6. DATEFORMAT()
功能:设置日期格式
语法:SET DATEFORMAT <format>
format(ymd,mdy,dmy)
代码:set dataformat mdy
-----------------------------------------------------------
7. DATENAME()
功能:日期date按datepart风格之后变成字符串
语法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代码:
select datename(day,CURRENT_TIMESTAMP)
select datename(month,CURRENT_TIMESTAMP)
select datename(year,CURRENT_TIMESTAMP)
select datename(hour,CURRENT_TIMESTAMP)
select datename(minute,CURRENT_TIMESTAMP)
select datename(week,CURRENT_TIMESTAMP)
select datename(weekday,CURRENT_TIMESTAMP)
-----------------------------------------------------------
8. DATEPART()
功能:日期date按datepart风格之后变成字符串
语法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代码:
-----------------------------------------------------------
9. Day()
功能:求日期的天
语法:day(date)
代码:select day(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10. GETDATE()
功能:求当前日期和时间
语法:GETDATE()
代码:select GETDATE() 和select CURRENT_TIMESTAMP相同
-----------------------------------------------------------
11. GETUTCDATE()
-----------------------------------------------------------
12. ISDATE()
-----------------------------------------------------------
13. MONTH()
功能:求日期的月
语法:MONTH(date)
代码:select month(CURRENT_TIMESTAMP)
-----------------------------------------------------------
14. Year()
功能:求日期的年
语法:Year(date)
代码:select Year(CURRENT_TIMESTAMP)
-----------------------------------------------------------
5.5数学函数Math Functions
1. ABS()
2. ACOS()
3. ASIN()
4. ATAN()
5. CEILING()
6. COS()
7. COT()
8. DEGREES()
9. EXP()
10. FLOOR()
11. ISNUMERIC()
12. LOG()
13. LOG10()
14. PI()
15. Power()
16. RADIANS()
11. 17. RAND()
18. ROUND()
19. SIGN()
20. Sin()
21. SQRT()
22. SQUARE()
23. TAN()
-----------------------------------------------------------
5.6分析函数Analytical Functions
1. COMPUTE()
2. CUBE()
3. DENSE_RANK()
4. GROUPING()
5. NTILE()
6. PARTITION()
7. PIVOT()
8. ROLLUP()
9. ROW_NUMBER()
10. STDEV()
11. STDEVP()
12. VAR()
13. VARP()
