[TOC]
日期函數(shù)
MySQL對時間的格式無具體要求艺糜,只要格式是 YYYY-MM-DD HH:MM:SS 其中:可用其它特殊字符替換着裹。
- ADDDATE(d,INTERVAL n type) : 在起始日期d加上n的日期(n 默認單位天)
SELECT ADDDATE("2019-04-10 11:11:11",10); -- 2019-04-20 11:11:11
SELECT ADDDATE("2019-04-10 11:11:11",INTERVAL 5 HOUR); -- 2019-04-10 16:11:11
- ADDTIME(t,n):在時間t加上秒數(shù)s(s可正可負,但在區(qū)間[60,99]和[-60,-99]結果會為null瑰妄?)
SELECT ADDTIME("2019-04-10 11:11:11",-59); -- 2019-04-10 11:10:12
- DATE_ADD(d,INTERVAL n type):在起始時間d加上n的日期(n無默認單位)
SELECT DATE_ADD('2019-04-15 11:11:11',INTERVAL 3 HOUR) -- 2019-04-15 14:11:11
- SUBDATE(d,n):在起始日期d減去n天的日期
SELECT SUBDATE("2019-04-15 11:11:11" , 5) -- 2019-04-10 11:11:11
- SUBTIME(d,n):在起始日期d減去n秒的時間
SELECT SUBTIME("2019-04-15 11:11:11" , 5) -- 2019-04-15 11:11:06
- DATE_SUB(d,INTERVAL n type):在起始時間d減去n的日期(n無默認單位)
SELECT DATE_SUB('2019-04-15 11:11:11',INTERVAL 3 HOUR) --2019-04-15 08:11:11
- CURRENT_TIME/CURTIME(): 返回當前時間
SELECT CURRENT_TIME(); -- 10:15:49
SELECT CURTIME(); -- 10:15:49
- UTC_DATE()/UTC_TIME()/UTC_TIMESTAMP(): 國際標準日期/時間
SELECT UTC_DATE(); -- 2019-04-17 國際標準日期
SELECT UTC_TIME() ; -- 03:37:19 國際標準時間
SELECT UTC_TIMESTAMP(); -- 2019-04-17 03:37:44 國際標準時間戳
- SYSDATE():返回系統(tǒng)時間
SELECT SYSDATE() -- 2019-04-15 09:01:20
- CURDATE()/CURRENT_DATE():返回當前日期
SELECT CURDATE(); -- 2019-04-13
SELECT CURRENT_DATE(); -- 2019-04-13
- CURRENT_TIMESTAMP()/SELECT NOW(): 返回當前時間戳
SELECT CURRENT_TIMESTAMP(); -- 2019-04-13 10:24:09
SELECT NOW(); -- 2019-04-13 10:24:09
- DATE(d):提取d里面的日期格式為 YYYY-MM-DD;
SELECT DATE("2019-11-13 11-11-11"); -- 2019-11-13
- DATEDIFF(d1,d2):計算 d1到d2間隔的天數(shù)陷嘴,d1-d2
SELECT DATEDIFF("2019-11-23","2019-11-13") ; -- 10
- DATE_FORMAT(d,format):根據(jù)格式format顯示日期d ,(format格式詳見@2)
SELECT DATE_FORMAT("2019-04-11 11:11:11",'%Y-%m-%d %r'); -- 2019-04-11 11:11:11 AM
- YEAR(d):返回日期值d的年份部分
SELECT YEAR("2019-04-13 11:11:11") -- 2019
- MONTH(d):返回日期值d的月份部分
SELECT DAY("2019-04-13 11:11:11") -- 4
- DAY(d):返回日期值d的日期部分
SELECT DAY("2019-04-13 11:11:11") -- 13
- HOUR(d):返回日期值d的小時部分
SELECT DAY("2019-04-13 08:09:11") -- 08
- MINUTE(d):返回日期值d的分鐘部分
SELECT MINUTE("2019-04-13 08:09:11") -- 09
- SECOND(d):返回日期值d的秒數(shù)部分
SELECT SECOND("2019-04-13 08:09:11") -- 11
- MICROSECOND(d):返回日期d是的毫秒數(shù)
SELECT MICROSECOND("2019-04-15 11:11:11.1234") -- 123400
- WEEK(d):返回日期值d的處于本年第幾周
SELECT WEEK("2019-04-13 08:09:11"); -- 14
- DAYNAME(d):返回日期值d處于星期幾(如 Monday,Tuesday)
SELECT DAYNAME("2019-04-13 11:21:11") -- Saturday
- DAYOFWEEK(d):日期 d 今天是星期幾间坐,1 星期日灾挨,2 星期一,以此類推
SELECT DAYOFWEEK("2019-04-13 11:21:11"); -- 6
- DAYOFMONTH(d):計算日期 d 是本月的第幾天
SELECT DAYOFMONTH("2019-04-13 11:21:11"); -- 13
- DAYOFYEAR(d):計算日期 d 是本年的第幾天
SELECT DAYOFYEAR("2019-04-13 11:21:11"); -- 103
- QUARTER(d):計算日期d是本年的第幾個季度
SELECT QUARTER("2019-04-15 11:11:11") -- 2
- LAST_DAY(d):返回給定日期的那一分月的最后一天
SELECT LAST_DAY("2019-04-15 11:11:11") -- 2019-04-30
- EXTRACT(type FROM d):從日期 d 中獲取指定的值竹宋,type 指定返回的值劳澄。(type類型詳見@1)
SELECT EXTRACT(**MINUTE** FROM "2019-04-13 08:21:11"); -- 21
- STR_TO_DATE(string,format_mask):將字符串轉(zhuǎn)換為日期
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -- 2019-04-15
- TO_DAYS(d):計算日期d距離0000年1月1日的天數(shù)
SELECT TO_DAYS("0001-04-15") -- 105
@ 1 TYPE類型
- MICROSECOND 毫秒
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
@2 format格式
%a | 縮寫星期名 |
---|---|
%b | 縮寫月名 |
%c | 月,數(shù)值 |
%D | 帶有英文前綴的月中的天 |
%d | 月的天蜈七,數(shù)值(00-31) |
%e | 月的天秒拔,數(shù)值(0-31) |
%f | 微秒 |
%H | 小時(00-23) |
%h | 小時(01-12) |
%I | 小時(01-12) |
%i | 分鐘,數(shù)值(00-59) |
%j | 年的天(001-366) |
%k | 小時(0-23) |
%l | 小時(1-12) |
%M | 月名 |
%m | 月飒硅,數(shù)值(00-12) |
%p | AM 或 PM |
%r | 時間砂缩,12-小時(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 時間, 24-小時(hh:mm:ss) |
%U | 周(00-53)星期日是一周的第一天 |
%u | 周(00-53)星期一是一周的第一天 |
%V | 周(01-53)星期日是一周的第一天,與 %X 使用 |
%v | 周(01-53)星期一是一周的第一天三娩,與 %x 使用 |
%W | 星期名 |
%w | 周的天(0=星期日, 6=星期六) |
%X | 年庵芭,其中的星期日是周的第一天,4 位雀监,與 %V 使用 |
%x | 年双吆,其中的星期一是周的第一天,4 位,與 %v 使用 |
%Y | 年好乐,4 位 |
%y | 年匾竿,2 位 |
字符串函數(shù)
ps:MySQL下標從1開始計算
- 返回字符c的ASCII編碼 ASCII(c)
SELECT ASCII('A'); -- 65
-
返回數(shù)字i的所對應的ASCII編碼字符 CHAR(i)
- SELECT CHAR(65); -- A
- 返回字符串長度:CHAR_LENGTH(s)/CHARACTER_LENGTH(s)
SELECT CHAR_LENGTH("HelloWorld"); -- 10
SELECT CHARACTER_LENGTH("HelloWorld"); -- 10
- 合并字符串s1,s2...sn為一個字符串:CONCAT(s1,s2...sn)
SELECT CONCAT("Hello","World","!") ; -- helloworld!
- 合并字符串并添加分隔符x:CONCAT_WS(x,s1,s2...sn)
SELECT CONCAT_WS("-","Hello","World","!") ; -- hello-world-!
- 返回字符串s在s1,s2...sn的位置(沒找到返回0):FIELD(s,s1,s2...sn)
SELECT FIELD("c","b","c","d") ; -- 2
- 返回在字符串s2中與s1匹配的字符串的位置(沒找到返回0):FIND_IN_SET(s1,s2)
SELECT FIND_IN_SET("ac", "a,b,ac,d,e"); -- 3
- 返回字符串s1在字符s開始的位置 POSITION(s1 IN s)
SELECT POSITION("o" in "helloworld"); -- 5
- 函數(shù)可以將數(shù)字 x 進行格式化 "#,###.##", 將 x 保留到小數(shù)點后 n 位,最后一位四舍五入:FORMAT(x,n)
SELECT FORMAT(20190415.35581, 2); -- 20,190,415.36
- 將字符串s2替換字符s中的字符串s1:REPLACE(s,s1,s2)
SELECT REPLACE("helloworld","ell","MySQL"); -- hMySQLoworld
- 字符串s2替換s1在x處的位置曹宴,替換長度為len:INSERT(s1,x,len,s2)
SELECT INSERT("helloworld",6,4,"MySQL") ; -- helloMySQLd
- 在字符串s1處填充字符串s2搂橙,使其長度為len: LPAD(s1,len,s2)
SELECT LPAD("helloworld",16,"MySQL"); -- MySQLMhelloworld
- 在字符串s1結尾處填充字符串s2歉提,使其長度為lenRPAD(s1,len,s2)
SELECT RPAD("helloworld",16,"MySQL"); -- helloworldMySQLM
- 從字符串s獲取s1的位置:LOCATE(s1,s)
SELECT LOCATE("ell","helloworld"); -- 2
- 將s中的字母全部轉(zhuǎn)換為小寫:LOWER(s)/LCASE(s)
SELECT LOWER("HELLOWORLD"); -- helloworld
SELECT LCASE("HELLOWORLD"); -- helloworld
- 將s中的字母全部轉(zhuǎn)換為大寫:UPPER(s)/UCASE(s)
SELECT UPPER("helloworld"); -- HELLOWORLD
SELECT UCASE("helloworld"); -- HELLOWORLD
- 去掉字符串s開始處的空格:LTRIM(s)
SELECT LTRIM(" hello") ; -- hellow
- 去掉字符串s結尾處空格: RTRIM(s)
SELECT RTRIM("hello "); -- hello
- 去掉字符串s開始和結尾處的空格: TRIM(s)
SELECT RTRIM(" hello "); -- hello
- 返回n個空格: SPACE(n)
SELECT SPACE(5); --
- 從左開始截取字符串: left(str, length)
SELECT LEFT("helloworld",7); --hellowo
- 從右開始截取字符串: right(str, length)
SELECT RIGHT("helloworld",7); --loworld
- 從字符串 s 的 start 位置截取長度為 length 的子字符串: MID(s,start ,len)/SUBSTRING(s,start ,len)
SELECT MID("helloworld", 2, 3) ; -- ell
SELECT SUBSTRING("helloworld", 3, 5); -- llowo
- 將字符串s重復n次:REPEAT(s,n)
SELECT REPEAT("hello",3); -- hellohellohello
- 反轉(zhuǎn)字符串s:REVERSE(s)
SELECT REVERSE("hello"); -- olleh
- 返回字符串s后n個字符:RIGHT("helloworld",6)
SELECT RIGHT("helloworld",6); -- oworld
- 比較字符串 s1 和 s2笛坦,如果 s1 與 s2 相等返回 0 ,如果 s1>s2 返回 1苔巨,如果 s1<s2 返回 -1:STRCMP(s1,s2)
SELECT STRCMP("runoob", "runooa"); -- 1
SELECT STRCMP("runoob", "runoob"); -- 0
SELECT STRCMP("runoob", "runooc"); -- -1
數(shù)學函數(shù)
- 取絕對值:ABS(x)
SELECT ABS(-1); -- 1
- 返回圓周率:PI()
SELECT PI(); --3.141593
-
返回0到1的偽隨機數(shù): RAND([seed])
seed:種子值版扩,相同的種子值會有相同的輸出
SELECT RAND(); --0.8503592115364589
SELECT RAND(); --0.26865792299495816
SELECT RAND(100); --0.17353134804734155 試幾次都一樣
- 返回大于或等于x的最小整數(shù): CEIL(x)/CEILING(x)
SELECT CEIL(1.5); -- 2
SELECT CEIL(-1.5); -- -1
-
返回小于或等于 x 的最大整數(shù): FLOOR(x)
- SELECT FLOOR(1.5); -- 1
- SELECT FLOOR(1.5) -- -2
返回離x最近的整數(shù): ROUND(x)
SELECT ROUND(1.1235); -- 1
SELECT ROUND(-1.5235); -- -2
-
返回數(shù)值 x 保留到小數(shù)點后 y 位的值(與 ROUND 最大的區(qū)別是不會進行四舍五入):TRUNCATE(x,y)
y:是n的舍入精度侄泽,如果y為整數(shù)礁芦,則n舍入到y(tǒng)指定的小數(shù)位數(shù),如果y為負數(shù)悼尾,則n小數(shù)點左邊舍入到y(tǒng)的指定位數(shù)
SELECT TRUNCATE(1.1235,3); -- 1.123
SELECT TRUNCATE(123.1235,-1); -- 120
SELECT TRUNCATE(-1.5235,3); -- -1.523
-
n 除 m: n DIV m
- SELECT 5 DIV 2 ; -- 2
返回n除以m以后的余數(shù): MOD(n,m)
SELECT MOD(5,2); -- 1
-
返回x的y次方 POW(x,y)/POWER(x,y)
- SELECT POW(2,3); -- 8
返回x的平方根:SQRT(x)
SELECT SQRT(0.25); -- 0.5
- 返回x的符號柿扣,x是負數(shù)返回-1,正數(shù)返回1,0返回0 SIGN(x)
SELECT SIGN(0.25); -- 1
SELECT SIGN(0); -- 0
SELECT SIGN(-0.25); -- -1
聚合函數(shù)
- 返回一個表達式的平均值闺魏,expression 是一個字段: AVG(expression)
SELECT AVG(FSL) FROM table-- 取table表FSL字段的平均值
- 返回查詢的總記錄數(shù)未状,expression 是一個字段或號:* COUNT(expression)
SELECT COUNT(*) form table -- 取table表的總記錄數(shù)
- 返回字段expression的總和:SUM(expression)
SELECT SUM(FSL) -- 返回table表字段FSL的總和
- 返回字段expression的最大值: MAX(expression)
SELECT MAX(FSL) FROM table; --返回table表FSL字段的最大值
- 返回字段expression的最小值: MIN(expression)
SELECT MIN(FSL) FROM table; --返回table表FSL字段的最小值
- HAVING子句僅用于帶有GROUP BY子句的查詢語句中,WHERE子句用于每一行(在變成一個組的某一部分之前)析桥,而HAVING子句用于分組的聚合值司草。 HAVING
-
分組合并字符串:GROUP_CONCAT()
根據(jù)名字分組,求出該名字的所有id值
mysql>SELECT SNAME,GROUP_CONCAT(SNO) as ids from student GROUP BY SNAME
| SNAME | ids |
| ----- | ----------- |
| 匡明 | 105 |
| 曾華 | 108 |
| 李軍 | 101,110,111 |
| 王麗 | 107,112 |
| 王芳 | 109 |
| 陸君 | 103 |根據(jù)名字分組泡仗,求出該名字的所有id值埋虹,并根據(jù)id的值從大到小排序,使用分隔符-隔開
mysql>SELECT SNAME,GROUP_CONCAT(SNO ORDER BY SNO DESC SEPARATOR '-') as ids from student GROUP BY SNAME
SNAME | ids |
---|---|
匡明 | 105 |
曾華 | 108 |
李軍 | 111-110-101 |
王麗 | 112-107 |
王芳 | 109 |
陸君 | 103 |
數(shù)據(jù)類型轉(zhuǎn)換函數(shù)
把x轉(zhuǎn)換成type類型:CAST(x AS type)/CONVERT(x,type)
- 字符串轉(zhuǎn)數(shù)字
SELECT CONVERT("123",SIGNED)娩怎; -- 123
SELECT CAST("123",SIGNED)搔课; -- 123
SELECT CONVERT("1a123",SIGNED); -- 返回1;從左往右匹配遇到非數(shù)字返回截亦,如果開頭沒數(shù)字返回0
SELECT CAST("sa123" as SIGNED) ; -- 返回 0
SELECT '123'+0; -- 返回數(shù)字類型 123
- 數(shù)字轉(zhuǎn)字符串
SELECT CONCAT(123) 爬泥; --返回字符串 "123"
SELECT CAST(123 AS CHAR); -- 返回"123"
- 字符串轉(zhuǎn)日期
SELECT CONVERT("20190415",DATE) -- 2019-04-15
SELECT CAST("20190415",DATE); -- 2019-04-15
type類型:
? 二進制,同帶binary前綴的效果 : BINARY
? 字符型,可帶參數(shù) : CHAR()
? 日期 : DATE
? 時間: TIME
? 日期時間型 : DATETIME
? 浮點數(shù) : DECIMAL
? 整數(shù) : SIGNED
? 無符號整數(shù) : UNSIGNED
排序函數(shù)
自定義排序, field(value,str1,str2,str3,str4)
mysql> select * from student order by field(Sname,'李軍','王麗','曾華');
SNO | SNAME | SSEX | SBIRTHDAY | CLASS |
---|---|---|---|---|
105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 |
101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 |
110 | 李軍 | 男 | 1974-06-03 00:00:00 | 95031 |
111 | 李軍 | 男 | 1974-06-03 00:00:00 | 95031 |
107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 |
112 | 王麗 | 女 | 1976-02-20 00:00:00 | 95033 |
108 | 曾華 | 男 | 1977-09-01 00:00:00 | 95033 |
沒有排序的字段放在最前面魁巩,排序字段根據(jù)field里的順序來進行排序急灭;
mysql> SELECT * from student ORDER BY FIELD(SNAME,'李軍','王麗','曾華') DESC;
SNO | SNAME | SSEX | SBIRTHDAY | CLASS |
---|---|---|---|---|
108 | 曾華 | 男 | 1977-09-01 00:00:00 | 95033 |
107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 |
112 | 王麗 | 女 | 1976-02-20 00:00:00 | 95033 |
101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 |
110 | 李軍 | 男 | 1974-06-03 00:00:00 | 95031 |
111 | 李軍 | 男 | 1974-06-03 00:00:00 | 95031 |
105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 |
當加上DESC后,先filed()參數(shù)從后往前谷遂,再是未經(jīng)field()排序的字段葬馋。
系統(tǒng)函數(shù)
- 查看版本信息:VERSION()
SELECT VERSION(); -- 5.5.28
- 查看服務器的連接數(shù):CONNECTION_ID()
SELECT CONNECTION_ID(); -- 27
- 查看當前用戶的名字: CURRENT_USER()/SYSTEM_USER()/USER()/SESSION_USER()
SELECT CURRENT_USER(); --root@localhost
- 查看當前數(shù)據(jù)庫的名字 DATABASE()/SCHEMA()
SELECT DATABASE(); --ycz_test
其它函數(shù)
- 從N1開始,比較N和N1的值,如果N>Ni返回i的下標(從N1開始從0計算),如果N最大返回0畴嘶,如果N為null返回-1:INTERVAL(N,N1,N2,N3,N4...Ni)
SELECT INTERVAL(1,2,3,4); -- 0
SELECT INTERVAL(5,2,3,4,5,6,7,8); -- 4
SELECT INTERVAL(null,2,3,4); -- -1
- 返回x的二進制編碼:BIN(x)
SELECT BIN(14); -- 110
- 返回x的八進制編碼:OCT(x)
SELECT OCT(14); -- 16
- 返回x的十六進制編碼:HEX(x)
SELECT HEX(14); -- E
- 將x從f1進制數(shù)變成f2進制數(shù):CONV(x,f1,f2)
SELECT CONV(14,10,2)蛋逾;--1110
- 如果表達式expr成立,返回r1,否則返回r2: IF(expr,r1,r2)
SELECT IF(1>2,"正確","錯誤"); -- 錯誤
- 如果 v1 的值不為 NULL窗悯,則返回 v1区匣,否則返回 v2。 IFNULL(r1,r2)
SELECT IFNULL(null,"helloworld"); -- helloworld
SELECT IFNULL("hello","helloworld"); -- hello
- 比較兩個字符串蒋院,如果相等返回null,否則返回r1: NULLIF(r1,r2)
SELECT NULLIF("hello","helloworld"); -- hello
SELECT NULLIF("helloworld","helloworld") ; --
-
IP地址與數(shù)字相互轉(zhuǎn)換的函數(shù)
把數(shù)字轉(zhuǎn)換為ip地址:INET_ATON(IP)
和
把ip地址轉(zhuǎn)換為數(shù)字:INET_NTOA(n)
SELECT INET_ATON('192.168.3.126') ; -- 3232236414
SELECT INET_NTOA(3232236414); -- 192.168.3.126
- 返回列表的最大值: GREATEST(expr1, expr2, expr3, ...)
SELECT GREATEST(34, 12, 26, 8, 55, 47); -- 55
SELECT LEAST("baidu", "alibaba", "tencent"); -- alibaba
- 返回列表中的最小值: LEAST(expr1, expr2, expr3, ...)
SELECT GREATEST(34, 12, 26, 8, 55, 47); -- 8
SELECT LEAST("baidu", "alibaba", "tencent"); -- tencent
加密函數(shù)
- 返回MD5加密后的字符串s MD5(s)
SELECT MD5("hello"); --5d41402abc4b2a76b9719d911017c592
- 對字符串s進行加密亏钩,一般用來給用戶密碼做加密的。PASSWORD(s)
SELECT PASSWORD("hello"); --*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119
使用字符串pswd_str作為密匙來加密字符串str欺旧。加密的結果是一個二進制數(shù)姑丑,必須使用BLOB類型的字段來保存它。ENCODE(str,pswd_str)
-
使用密匙pswd_str對二進制函數(shù)crypt_str進行解密辞友,和ENCODE()配對使用栅哀。DECODE(crypt_str,pswd_str)
- 使用ENCODE()加密,并使用DECODE()進行解密称龙。加密字符串為hello,密匙為world
SELECT DECODE(ENCODE("hello","world"),"world"); -- hello