5.數(shù)據(jù)庫函數(shù)Functions
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.2聚集函數(shù)Aggregate Functions
5.3字符函數(shù)char Functions
5.4日期函數(shù)Date Functions
5.5數(shù)學(xué)函數(shù)Math Functions
5.6分析函數(shù)Analytical Functions
-----------------------------------------------------------
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.1.1 CAST()
功能:數(shù)據(jù)類型轉(zhuǎn)換
語法: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()
功能:把表達式值轉(zhuǎn)換為指定sytle的數(shù)據(jù)類型
語法:CONVERT(data_ type(<length>), expression, <style>)
代碼:
//日期風(fēng)格轉(zhuǎn)換
datetime轉(zhuǎn)指定日期格式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)
//字符串轉(zhuǎn)數(shù)字
CONVERT (INTEGER , '12345')
//字符轉(zhuǎn)日期
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聚集函數(shù)Aggregate Functions
語法:select AggregateFunctions(column-name)
sum(column-name):計算字段總和
avg(column-name):計算字段平均值
min(column-name):計算字段最小值
max(column-name):計算字段最大值
count(column-name):計算字段非空值的個數(shù)
count(*):計算查詢結(jié)果的記錄個數(shù)
代碼:
//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字符函數(shù)char Functions
1. ASCII()
//函數(shù)返回字符表達式最左端字符的ASCII 碼值
2. Char()
//函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符--如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數(shù)會返回一個NULL
3. CHARINDEX()
//函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置
4. DIFFERENCE()
5. FORMATMESSAGE()
6. LEFT()
7. LEN()
8. LOWER()
//函數(shù)把字符串全部轉(zhuǎn)換為小寫
9. LTRIM()
//函數(shù)把字符串頭部的空格去掉
10.nchar()
11.PATINDEX()
12.QUOTENAME()
13.REPLACE()
//函數(shù)返回被替換了指定子串的字符串
14.REPLICATE()
/函數(shù)返回一個重復(fù)指定次數(shù)的字符串
15.REVERSE()
//函數(shù)將指定的字符串的字符排列順序顛倒
16.Right()
17.RTRIM()
/函數(shù)把字符串尾部的空格去掉
18.SOUNDEX()
19.SPACE()
//函數(shù)返回一個有指定長度的空白字符串
20.STR()
//函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)
21.STUFF()
//函數(shù)用另一子串替換字符串指定位置長度的子串
22.SUBSTRING()
//函數(shù)返回子字符串
23.UNICODE()
24.UPPER()
//函數(shù)把字符串全部轉(zhuǎn)換為大寫
-----------------------------------------------------------
5.4日期函數(shù)Date Functions
5.4.1. CURRENT_TIMESTAMP
功能:
得到當(dāng)前數(shù)據(jù)庫的日期
代碼:
//直接得到當(dāng)前日期
SELECT CURRENT_TIMESTAMP
go
//調(diào)用變量中的當(dāng)前日期
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
運行結(jié)果是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國慶到今天的天數(shù)
SELECT DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的月數(shù)
SELECT DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的年數(shù)
SELECT DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的周數(shù)
SELECT DATEDIFF(week,'10/1/2009',CURRENT_TIMESTAMP)
GO
-----------------------------------------------------------
5.4.5. DATEFIRST()
功能:設(shè)置或者查詢一周的第一天
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()
功能:設(shè)置日期格式
語法:SET DATEFORMAT <format>
format(ymd,mdy,dmy)
代碼:set dataformat mdy
-----------------------------------------------------------
7. DATENAME()
功能:日期date按datepart風(fēng)格之后變成字符串
語法: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風(fēng)格之后變成字符串
語法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代碼:
-----------------------------------------------------------
9. Day()
功能:求日期的天
語法:day(date)
代碼:select day(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10. GETDATE()
功能:求當(dāng)前日期和時間
語法: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數(shù)學(xué)函數(shù)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分析函數(shù)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()
