5.數(shù)據(jù)庫(kù)函數(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)換
語(yǔ)法: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()
功能:返回表達(dá)式列表中第一個(gè)非空值表達(dá)式的值
語(yǔ)法: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()
功能:把表達(dá)式值轉(zhuǎn)換為指定sytle的數(shù)據(jù)類型
語(yǔ)法: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替代
語(yǔ)法:ISNULL(check_expression, replacement_value)
代碼:
SELECT BillingDate,
ISNULL(BillingDate, '1900-01-01') AS NewDate
FROM Billings
-----------------------------------------------------------
5.1.5 NULLIF()
功能:兩個(gè)表達(dá)式相等，返回null,否則返回第1個(gè)表達(dá)式
語(yǔ)法: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
語(yǔ)法:select AggregateFunctions(column-name)
sum(column-name):計(jì)算字段總和
avg(column-name):計(jì)算字段平均值
min(column-name):計(jì)算字段最小值
max(column-name):計(jì)算字段最大值
count(column-name):計(jì)算字段非空值的個(gè)數(shù)
count(*):計(jì)算查詢結(jié)果的記錄個(gè)數(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ù)返回字符表達(dá)式最左端字符的ASCII 碼值
2. Char()
//函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符--如果沒(méi)有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數(shù)會(huì)返回一個(gè)NULL
3. CHARINDEX()
//函數(shù)返回字符串中某個(gè)指定的子串出現(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ù)返回一個(gè)重復(fù)指定次數(shù)的字符串
15.REVERSE()
//函數(shù)將指定的字符串的字符排列順序顛倒
16.Right()
17.RTRIM()
/函數(shù)把字符串尾部的空格去掉
18.SOUNDEX()
19.SPACE()
//函數(shù)返回一個(gè)有指定長(zhǎng)度的空白字符串
20.STR()
//函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)
21.STUFF()
//函數(shù)用另一子串替換字符串指定位置長(zhǎng)度的子串
22.SUBSTRING()
//函數(shù)返回子字符串
23.UNICODE()
24.UPPER()
//函數(shù)把字符串全部轉(zhuǎn)換為大寫
-----------------------------------------------------------
5.4日期函數(shù)Date Functions
5.4.1. CURRENT_TIMESTAMP
功能:
得到當(dāng)前數(shù)據(jù)庫(kù)的日期
代碼:
//直接得到當(dāng)前日期
SELECT CURRENT_TIMESTAMP
go
//調(diào)用變量中的當(dāng)前日期
DECLARE @today datetime
SELECT @today = current_timestamp
select @today
go
-----------------------------------------------------------
5.4.2. 日期計(jì)算Date calculation
功能:日期計(jì)算
代碼:
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))
--自動(dòng)補(bǔ)齊月份到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
運(yùn)行結(jié)果是mmddyyyy格式的字符串
-----------------------------------------------------------
5.4.3. DATEADD()
功能:日期相加或者相減n天后的日期
語(yǔ)法: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天后的日期
語(yǔ)法:DATEDIFF ( datepart , startdate , enddate )
datepart列表:
day:單位=天
month:單位=月
year:單位=年
hour:單位=小時(shí)
minute:單位=分
second:單位=秒
week:單位=周
代碼:
//10/01/2009國(guó)慶到今天的天數(shù)
SELECT DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國(guó)慶到今天的月數(shù)
SELECT DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國(guó)慶到今天的年數(shù)
SELECT DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國(guó)慶到今天的周數(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è)置日期格式
語(yǔ)法:SET DATEFORMAT <format>
format(ymd,mdy,dmy)
代碼:set dataformat mdy
-----------------------------------------------------------
7. DATENAME()
功能:日期date按datepart風(fēng)格之后變成字符串
語(yǔ)法: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)格之后變成字符串
語(yǔ)法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代碼:
-----------------------------------------------------------
9. Day()
功能:求日期的天
語(yǔ)法:day(date)
代碼:select day(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10. GETDATE()
功能:求當(dāng)前日期和時(shí)間
語(yǔ)法:GETDATE()
代碼:select GETDATE() 和select CURRENT_TIMESTAMP相同
-----------------------------------------------------------
11. GETUTCDATE()
-----------------------------------------------------------
12. ISDATE()
-----------------------------------------------------------
13. MONTH()
功能:求日期的月
語(yǔ)法:MONTH(date)
代碼:select month(CURRENT_TIMESTAMP)
-----------------------------------------------------------
14. Year()
功能:求日期的年
語(yǔ)法: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()
