[TOC]
日期函數(shù)
SQL SERVER 的時(shí)間格式只能為YYYY-MM-DD HH:MM:SS或YYYY/MM/DD HH:MM:SS(暫時(shí)試出這兩個(gè))
SQL SERVER 的字符串只能使用單引號(hào)
| 字段 | 格式 | 精度 | 存儲(chǔ)大小 | 時(shí)間范圍 | 示例 |
|---|---|---|---|---|---|
| datetime | yyyy-MM-dd HH:mm:ss.fff | 3.33毫秒（ms) | 8字節(jié) | 1753-01-01~9999-12-31 | 2019-04-16 09:19:39.690 |
| DATETIME2 | yyyy-MM-dd HH:mm:ss.fffffff | 0.1微秒(μs） | 精度小于3的6個(gè)字節(jié)。精度3或4的7個(gè)字節(jié),所有其他精度需要8個(gè)字節(jié)。 | 0001-01-01~9999-12-31 | 2019-04-16 09:22:19.3397144 |
| smalldatetime | yyyy-MM-dd HH:mm | 秒數(shù)始終為0 | 4個(gè)字節(jié)，固定 | 1900-01-01~2079-06-06 | 2019-04-16 09:19:00 |
| date | YYYY-MM-DD | 3個(gè)字節(jié)，固定 | 0001-01-01~9999-12-31 | 2019-04-16 | |
| time | hh:mm:ss.[.nnnnnnn] | 默認(rèn)為0.1微妙(μs) | 固定的5個(gè)字節(jié)是默認(rèn)值 | 00：00：00.0000000~23：59：59.9999999 | 09:22:19.3397144 |
-
在起始日期d加上n的日期,n的類型為dp: DATEADD(dp,n,d)
SELECT DATEADD(Minute ,5,'2019-04-10 11:11:11'); -- 2019-04-10 11:16:11.000 -
返回當(dāng)前時(shí)間，為datetime類型：getDate()/ CURRENT_TIMESTAMP
- SELECT GETDATE(); --2019-04-15 16:49:11.740 - SELECT CURRENT_TIMESTAMP; --2019-04-15 16:54:05.677 -
返回世界標(biāo)準(zhǔn)時(shí)間(處于東八區(qū)，所以有八個(gè)小時(shí)時(shí)差)： getUTCDATE();
SELECT GETUTCDATE(); --2019-04-15 08:49:11.740 -
返回系統(tǒng)時(shí)間,為datetime2類型：SYSDATETIME()
SELECT SYSDATETIME(); -- 2019-04-15 16:52:48.9306200 -
計(jì)算 d1到d2間隔的dp單位的時(shí)間，d2-d1: DATEDIFF(dp,d1,d2)
SELECT DATEDIFF(day,'2019-11-23','2019-11-13') ;-- -10 -
返回表示指定時(shí)間d的指定 dp的字符串: DATENAME(d,dp)
SELECT DATENAME(DW,'2019-04-16 11:12:13'); --星期二 -
返回表示指定時(shí)間d 的指定 dp的整數(shù): DATEPART(d,dp)
SELECT DATEPART(DW,'2019-04-16 11:12:13'); -- 3 -
返回指定時(shí)間d的“日”部分的整數(shù)： DAY(d)
SELECT DAY('2019-04-16 11:12:13'); -- 16 -
返回指定時(shí)間d的“月”部分的整數(shù)： MONTH(d)
SELECT MONTH('2019-04-16 11:12:13'); -- 4 -
返回指定時(shí)間d的“年”部分的整數(shù)： YEAR(d)
SELECT YEAR('2019-04-16 11:12:13'); -- 2019 -
返回表示指定年、月、日的date值：DATEFORMPARTS(year,month,day)
SELECT DATEFROMPARTS(2019,04,16); -- 2019-04-16 -
為指定的日期和時(shí)間返回 datetime 值 DATETIMEFROMPARTS(year,month,day,hour,minute,seconds,milliseconds)
SELECT DATETIMEFROMPARTS(2019,04,16,09,09,50,101); -- 2019-04-16 09:09:50.100 -
為指定的日期和時(shí)間返回 datetime2 值 DATETIME2FROMPARTS(year,month,day,hour,minute,seconds,fractions,精度)
fractions表示秒的整數(shù)形式的整數(shù)表達(dá)式SELECT DATETIME2FROMPARTS(2019,04,16,09,09,50,20,7); -- 2019-04-16 09:09:50.0000020 -
為指定的日期和時(shí)間返回 smalldatetime 值。 SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
SELECT SMALLDATETIMEFROMPARTS(2019,04,16,10,02); -- 2019-04-16 10:02:00 -
對(duì)指定的時(shí)間返回 time 值（具有指定精度）: TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
SELECT TIMEFROMPARTS(10,05,32,5,3); -- 10:05:32.005 -
返回包含指定日期所在月份的最后一天（具有可選偏移量） EOMONTH(strart_date[,month_to_add])
SELECT EOMONTH('2019-04-16 11:11:11'); -- 2019-04-30 SELECT EOMONTH('2019-04-16 11:11:11',-1); -- 2019-03-31 -
如果表達(dá)式是有效的 date、time、或 datetime 值，則返回 1；否則返回 0。
如果表達(dá)式為 datetime2 值，則 ISDATE 返回 0。SELECT ISDATE('2019-04-16 11:11:11'); -- 1 SELECT ISDATE('2019-24-36 11:11:11'); -- 0 SELECT ISDATE('2019-04-16 10:00:42.3344381'); -- 0
datepart(dp)類型
| 值 | 縮寫（Sql Server） | Access 和 ASP | 說明 |
|---|---|---|---|
| Year | Yy | yyyy | 年 1753 ~ 9999 |
| Quarter | q | 季 1 ~ 4 | |
| Month | Mm | m | 月1 ~ 12 |
| Day of year | Dy | y | 一年的日數(shù),一年中的第幾日 1-366 |
| Day | Dd | d | 日，1-31 |
| Weekday | Dw | w | 一周的日數(shù)，一周中的第幾日 1-7 |
| Week | Wk | ww | 周，一年中的第幾周 0 ~ 51 |
| Hour | Hh | h | 時(shí)0 ~ 23 |
| Minute | Mi | n | 分鐘0 ~ 59 |
| Second | Ss | s | 秒 0 ~ 59 |
| Millisecond | Ms | - | 毫秒 0 ~ 999 |
字符串函數(shù)
ps：SQL SERVER下標(biāo)從1開始計(jì)算
-
返回字符c的ASCII編碼 ASCII(c)
SELECT ASCII('A'); -- 65 -
返回?cái)?shù)字i的所對(duì)應(yīng)的ASCII編碼字符 CHAR(i)
SELECT CHAR(65); -- A -
返回字符串s的長度 LEN(s)
SELECT LEN('helloworld'); -- 10 -
合并字符串s1,s2...sn為一個(gè)字符串：CONCAT(s1,s2...sn)
SELECT CONCAT('Hello','World','!') ; -- helloworld! -
合并字符串并添加分隔符x：CONCAT_WS(x,s1,s2...sn)
SELECT CONCAT_WS('-','Hello','World','!') ;-- hello-world-! -
返回字符串str1在字符串str2中最開始的位置,可以指定start為開始位置，沒找到返回0： CHARINDEX(str1,str2[,start])
SELECT CHARINDEX('rld','helloworld',7); -- 8 SELECT CHARINDEX('rld','helloworld')； -- 8 SELECT CHARINDEX('rld','helloworld',9)； -- 0 -
返回字符模板str1（可以使用通配符，通配符詳見下文@1）在字符串str2中最開始的位置,可以指定start為開始位置，沒找到返回0;
SELECT PATINDEX('w%','helloworld'); -- 查找以w開頭的，返回0 SELECT PATINDEX('%w%','helloworld'); -- 查找任意開頭，任意結(jié)尾的字符串w的位置 返回6 SELECT PATINDEX('%w','helloworld'); -- 查找以w結(jié)尾的字符串，返回 0 -
將日期/時(shí)間和數(shù)字值格式化為識(shí)別區(qū)域設(shè)置的字符串： FORMAT ( value, format [, culture ] )
SELECT FORMAT ( cast('10/01/2019'as date), 'D','en-us' ); -- Saturday, October 01, 2019 -
將字符串s2替換字符s中的字符串s1：REPLACE(s,s1,s2)
SELECT REPLACE('helloworld','ell','MySQL'); -- hMySQLoworld -
將字符串s重復(fù)n次： REOLICATE(s,n)
SELECT REPLACE('helloworld','ell','MySQL'); -- hMySQLoworld -
將字符串s反轉(zhuǎn): REVERSE(s)
SELECT REVERSE('hello') -
從左邊開始截取字符串s,長度為n: LEFT(s,n)
SELECT LEFT('helloworld',4); -- hell -
從左邊開始截取字符串s,長度為n: * RIGHT(s,n)
SELECT RIGHT('helloworld',4); -- orld -
截取指定位置的字符串： SUBSTRINg(s,start,len)
SELECT SUBSTRING('helloworld',3,6); --llowor -
去掉字符串s開始處的空格： LTRIM(s)
SELECT LTRIM(' hello'); -- hello -
去掉字符串s結(jié)尾處的空格：RTRIM(s)
SELECT RTRIM('hello '); -- hello -
去掉字符串s開始和結(jié)尾的空格： TRIM(s)
SELECT TRIM( ' hello '); -- hello -
將s中的字母全部轉(zhuǎn)換為小寫: LOWER()
SELECT LOWER('HELLOWORLD'); -- helloworld -
將s中的字母全部轉(zhuǎn)換為大寫： UPPER()
SELECT UPPER('helloworld'); -- HELLOWORLD -
字符串s2替換s1在x處的位置，替換長度為len: STUFF(s1,x,len,s2)
SELECT STUFF('helloworld',6,2,'SQL'); --helloSQLrld -
返回由數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換來的字符數(shù)據(jù)：STR(f[,len[,decimal]])
f:帶小數(shù)點(diǎn)的近似數(shù)字（float）數(shù)據(jù)類型的表達(dá)式
len:總長度，包含小數(shù)點(diǎn)、符號(hào)、數(shù)字、以及空格。默認(rèn)10.
decimal:decimal必須小于等于16，如果大于16會(huì)被截?cái)酁樾?shù)點(diǎn)右邊的16位。SELECT STR(123.45, 6, 1); -- 123.5 會(huì)四舍五入 SELECT STR(123.36,2,2); -- ** 如果長度過短，會(huì)顯示*號(hào) -
返回一個(gè)由四個(gè)字符組成的代碼 (SOUNDEX)，用于評(píng)估兩個(gè)字符串的相似性。 SOUNDEX(s)
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe'); -- S530 S530 -
該函數(shù)返回一個(gè)整數(shù)值，用于度量兩個(gè)不同字符表達(dá)式的 SOUNDEX()值之間的差異。 DIFFERENCE(s1,s2)
差異值范圍0-4,0表示SOUNDEX 值之間相似性較弱或不相似，4表示SOUNDEX 值之間非常相似，甚至完全相同。SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene'); -- G650 G650 4
通配符@1：
| 通配符 | 描述 |
|---|---|
| % | 替代一個(gè)或多個(gè)字符 |
| _ | 僅替代一個(gè)字符 |
| [charlist] | 字符列中的任何單一字符 |
| [^charlist]或者[!charlist] | 不在字符列中的任何單一字 |
數(shù)學(xué)函數(shù)
-
取絕對(duì)值：ABS(x)
SELECT ABS(-1); -- 1 -
返回圓周率：PI()
SELECT PI(); --3.141593 -
返回0到1的偽隨機(jī)數(shù)： RAND([seed])
seed:種子值，傳入相同的種子值會(huì)返回相同的結(jié)果。SELECT RAND(); --0.8503592115364589 SELECT RAND(); --0.28463380767982 SELECT RAND(100); -- 0.715436657367485 多試幾次也一樣 -
返回大于或等于x的最小整數(shù)： CEILING(x)
SELECT CEILING(-1.5) -- -1 SELECT CEILING(1.5); -- 2 -
返回小于或等于x的最大整數(shù): FLOOR(x)
SELECT FLOOR(1.5); 1 SELECT FLOOR(-1.5); -2 -
返回一個(gè)數(shù)值，舍入到指定的長度或精度: ROUND(n,len[,function])
len:是n的舍入精度，如果len為整數(shù)，則n舍入到len指定的小數(shù)位數(shù)，如果len為負(fù)數(shù)，則n小數(shù)點(diǎn)左邊舍入到len的指定位數(shù)
SELECT ROUND(-123.45,-3)； -- 0.00 SELECT ROUND(-123.45,-2)； -- -100.00 SELECT ROUND(-123.45,-1)； -- -120.00 SELECT ROUND(-123.45,0)； -- -123.00 SELECT ROUND(-123.45,1)； -- -123.50 SELECT ROUND(-123.45,2)； -- -123.45 SELECT ROUND(-123.45,3)； -- -123.45 -
返回指定浮點(diǎn)值的平方: SQUARE(f)
SELECT SQUARE(2.5); -- 6.25 -
返回指定浮點(diǎn)值的平方根: SQRT(f)
SELECT SQRT(100.00); -- 10.00 -
返回指定表達(dá)式的正號(hào) (+1)、零 (0) 或負(fù)號(hào) (-1): SIGN(n)
SELECT SIGN(100.00); -- 1 SELECT SIGN(0); -- 0 SELECT SIGN(-100.00); -- -1 -
返回指定表達(dá)式的指定冪的值: POWER(n)
SELECT POWER(2.0, 3); --8.0
聚合函數(shù)
...同mysql的AVG()\COUNT()\SUM()\MIN()\MAX()\HAVING
-
返回一個(gè)表達(dá)式的平均值，expression 是一個(gè)字段: AVG(expression)
SELECT AVG(FSL) FROM table-- 取table表FSL字段的平均值 ..........
返回查詢的總記錄數(shù),返回類型為BigInt，expression 是一個(gè)字段或號(hào)* COUNT_BIG()
轉(zhuǎn)換函數(shù)
CAST ( expression AS data_type [ ( length ) ] )
和
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
SELECT CAST(GETDATE() as varchar(29)); -- 04 16 2019 4:09PM
SELECT CONVERT(varchar(29),GETDATE(),0); -- 04 16 2019 4:05PM
SELECT CONVERT(varchar(29),GETDATE(),101); --04/16/2019
SELECT CAST('12.5' AS decimal(9,2)); --12.50
SELECT CONVERT( decimal(9,2),'12.50'); --12.50
| data_type ID | Style 格式 |
|---|---|
| 100 或者 0 | mon dd yyyy hh:miAM （或者 PM） |
| 101 | mm/dd/yy |
| 102 | yy.mm.dd |
| 103 | dd/mm/yy |
| 104 | dd.mm.yy |
| 105 | dd-mm-yy |
| 106 | dd mon yy |
| 107 | Mon dd, yy |
| 108 | hh:mm:ss |
| 109 或者 9 | mon dd yyyy hh:mi:ss:mmmAM（或者 PM） |
| 110 | mm-dd-yy |
| 111 | yy/mm/dd |
| 112 | yymmdd |
| 113 或者 13 | dd mon yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 120 或者 20 | yyyy-mm-dd hh:mi:ss(24h) |
| 121 或者 21 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-ddThh:mm:ss.mmm（沒有空格） |
| 130 | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yy hh:mi:ss:mmmAM |
排序函數(shù)
- row_number()
- 按照over()子句里面的order by進(jìn)行排序
sql>SELECT ROW_NUMBER() over(order by TIME) as row_id,* from card
| row_id | RId | NAME | GROUP TIME |
|---|---|---|---|
| 1 | 1325 | A | 1 |
| 2 | 1326 | C | 1 |
| 3 | 1327 | Z | 2 |
| 4 | 1328 | B | 2 |
| 5 | 1329 | G | 1 |
| 6 | 1330 | X | 1 |
| 7 | 1331 | K | 2 |
| 8 | 1332 | J | 3 |
| 9 | 1333 | A | 2 |
ps:over里頭的分組及排序的執(zhí)行晚于“where，group by，order by”的執(zhí)行。
sql>SELECT ROW_NUMBER() over(order by TIME),* from card order by CName
| row_id | RId | NAME | GROUP TIME |
|---|---|---|---|
| 1 | 1325 | A | 1 |
| 9 | 1333 | A | 2 |
| 4 | 1328 | B | 2 |
| 2 | 1326 | C | 1 |
| 5 | 1329 | G | 1 |
| 8 | 1332 | J | 3 |
| 7 | 1331 | K | 2 |
| 6 | 1330 | X | 1 |
| 3 | 1327 | Z | 2 |
- 使用row_number()函數(shù)進(jìn)行統(tǒng)計(jì)
根據(jù)partition by 來進(jìn)行分組，每個(gè)組的row_id都會(huì)重新計(jì)數(shù)，能直觀的看出每個(gè)組有多少條記錄。
sql>SELECT ROW_NUMBER() over(partition by GROUP order by TIME),* from card
| row_id | RId | NAME | GROUP TIME |
|---|---|---|---|
| 1 | 1325 | A | 1 |
| 2 | 1326 | C | 1 |
| 3 | 1329 | G | 1 |
| 4 | 1330 | X | 1 |
| 1 | 1327 | Z | 2 |
| 2 | 1328 | B | 2 |
| 3 | 1331 | K | 2 |
| 4 | 1333 | A | 2 |
| 1 | 1332 | J | 3 |
- RANK()
排名函數(shù)，比如一門課程有2個(gè)人成績相同，并列第一，那么其他人的名次就要從3開始計(jì)算
sql>SELECT RANK() OVER(order by GRADE desc),* from card
| Rank_id RID | NAME | GRADE | TIME |
|---|---|---|---|
| 1 | 1325 | A | 98 |
| 1 | 1326 | C | 98 |
| 1 | 1327 | Z | 98 |
| 4 | 1328 | B | 60 |
| 4 | 1329 | G | 60 |
| 6 | 1330 | X | 50 |
| 7 | 1331 | K | 40 |
| 7 | 1332 | J | 40 |
| 9 | 1333 | W | 2 |
(前三名并列第一，四五名并列第四，依次下推...)
- Dense_Rank()
dense_rank函數(shù)的功能與rank函數(shù)類似，dense_rank函數(shù)是連續(xù)的。也就是說，當(dāng)遇到相同排名時(shí)，將緊接著下一次的排名值增加。
sql>SELECT Dense_RANK() OVER(order by Operate desc),* from card
| Rank_id RID | NAME | GRADE | TIME |
|---|---|---|---|
| 1 | 1325 | A | 98 |
| 1 | 1326 | C | 98 |
| 1 | 1327 | Z | 98 |
| 2 | 1328 | B | 60 |
| 2 | 1329 | G | 60 |
| 3 | 1330 | X | 50 |
| 4 | 1331 | K | 40 |
| 4 | 1332 | J | 40 |
| 5 | 1333 | W | 2 |
(Rank_id連續(xù)增長)
- Ntile()
將結(jié)果集盡可能分到n組之內(nèi)，
sql>SELECT NTILE(4) OVER(order by GRADE desc),* from card
| Rank_id RID | NAME | GRADE | TIME |
|---|---|---|---|
| 1 | 1325 | A | 98 |
| 1 | 1326 | C | 98 |
| 1 | 1327 | Z | 98 |
| 2 | 1328 | B | 60 |
| 2 | 1329 | G | 60 |
| 3 | 1331 | K | 40 |
| 3 | 1332 | J | 40 |
| 4 | 1333 | A | 10 |
| 4 | 1330 | X | 10 |
（把結(jié)果(9條記錄)分成4組，第一組3條，其它3組兩天；3+2+2+2=9）
【分組約定】
1、每組的記錄數(shù)不能大于它上一組的記錄數(shù)，即編號(hào)小的組放的記錄數(shù)不能小于編號(hào)大的組。也就是說，第1組中的記錄數(shù)只能大于等于第2組及以后各組中的記錄數(shù)。
? 2、所有組中的記錄數(shù)要么都相同，要么從某一個(gè)記錄較少的組（命名為X）開始后面所有組的記錄數(shù)都與該組（X組）的記錄數(shù)相同。也就是說，如果有個(gè)組，前三組的記錄數(shù)都是9，而第四組的記錄數(shù)是8，那么第五組和第六組的記錄數(shù)也必須是8。
系統(tǒng)函數(shù)
-
如果s1不為空的話，返回s1,；如果s1為空的話，就返回s2 ISNULL ( s1, s2)
SELECT ISNULL(null,'hello'); -- hello SELECT ISNULL('world','hello'); -- world
-
確定表達(dá)式是否為有效的數(shù)值類型。 ISNUMERIC(expression )
SELECT ISNUMERIC('12a'); --0 SELECT ISNUMERIC(123); -- 1 SELECT ISNUMERIC(132.3); -- 1 SELECT ISNUMERIC('12'); -- 1 SELECT ISNUMERIC(0); -- 1 -
創(chuàng)建 uniqueidentifier 類型的唯一值，返回類型為uniqueidentifier。 NEWID ( )
SELECT NEWID() -- 9BA0EC6F-51B9-4FDD-BC02-6A4118A5A6BC -
返回工作站標(biāo)識(shí)號(hào)。 工作站標(biāo)識(shí)號(hào)是連接到 SQL Server 的客戶端計(jì)算機(jī)上的應(yīng)用程序的進(jìn)程 ID (PID)。 HOST_ID ()
SELECT HOST_ID(); --4480 -
返回工作站名 HOST_NAME ()
SELECT HOST_NAME (); --F24832A
