[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格式

字符串函數(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

數(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,'李軍','王麗','曾華');

沒有排序的字段放在最前面魁巩，排序字段根據(jù)field里的順序來進行排序急灭；

mysql> SELECT * from student ORDER BY FIELD(SNAME,'李軍','王麗','曾華') DESC;

當加上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