MySQL函数总结

作者: 阿怪_9653 | 来源:发表于2019-04-15 15:51 被阅读7次

[TOC]

日期函数

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加上秒数s（s可正可负，但在区间[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():返回系统时间

  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间隔的天数，d1-d2

  SELECT DATEDIFF("2019-11-23","2019-11-13") ;  --  10

DATE_FORMAT(d,format)：根据格式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的秒数部分

  SELECT SECOND("2019-04-13 08:09:11")  --  11

MICROSECOND(d)：返回日期d是的毫秒数

  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):将字符串转换为日期

  SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");  --  2019-04-15

TO_DAYS(d):计算日期d距离0000年1月1日的天数

  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	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值（00-31）
%e	月的天，数值（0-31）
%f	微秒
%H	小时（00-23）
%h	小时（01-12）
%I	小时（01-12）
%i	分钟，数值（00-59）
%j	年的天（001-366）
%k	小时（0-23）
%l	小时（1-12）
%M	月名
%m	月，数值（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 位

字符串函数

ps：MySQL下标从1开始计算

返回字符c的ASCII编码 ASCII(c)

  SELECT ASCII('A');  --  65

返回数字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

函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 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中的字母全部转换为小写：LOWER(s)/LCASE(s)

  SELECT LOWER("HELLOWORLD");  --  helloworld
  SELECT LCASE("HELLOWORLD");  --  helloworld

将s中的字母全部转换为大写：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

反转字符串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

数学函数

取绝对值：ABS(x)

  SELECT ABS(-1);  --  1

返回圆周率：PI()

  SELECT PI(); --3.141593

返回0到1的伪随机数： RAND([seed])
seed：种子值，相同的种子值会有相同的输出

  SELECT RAND(); --0.8503592115364589
  SELECT RAND(); --0.26865792299495816
  SELECT RAND(100)； --0.17353134804734155  试几次都一样

返回大于或等于x的最小整数： CEIL(x)/CEILING(x)

  SELECT CEIL(1.5);  --  2
  SELECT CEIL(-1.5);  --  -1

返回小于或等于 x 的最大整数: FLOOR(x)
- SELECT FLOOR(1.5); -- 1
- SELECT FLOOR(1.5) -- -2
返回离x最近的整数： ROUND(x)

  SELECT ROUND(1.1235);  --  1
  SELECT ROUND(-1.5235);  --  -2

返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）:TRUNCATE(x,y)

y:是n的舍入精度，如果y为整数，则n舍入到y指定的小数位数，如果y为负数，则n小数点左边舍入到y的指定位数

  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以后的余数： 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是负数返回-1，正数返回1,0返回0 SIGN(x)

  SELECT SIGN(0.25);  --  1
  SELECT SIGN(0);  --  0
  SELECT SIGN(-0.25);  --  -1

聚合函数

返回一个表达式的平均值，expression 是一个字段: AVG(expression)

  SELECT AVG(FSL) FROM table-- 取table表FSL字段的平均值

返回查询的总记录数，expression 是一个字段或号：* COUNT(expression)

  SELECT COUNT(*) form table  --  取table表的总记录数

返回字段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()
- 根据名字分组，求出该名字的所有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 |
- 根据名字分组，求出该名字的所有id值，并根据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

数据类型转换函数

把x转换成type类型：CAST(x AS type)/CONVERT(x,type)

字符串转数字

    SELECT CONVERT("123",SIGNED)；  --  123
    SELECT CAST("123",SIGNED)；  --  123
    SELECT CONVERT("1a123",SIGNED);  --  返回1；从左往右匹配遇到非数字返回，如果开头没数字返回0
    SELECT CAST("sa123" as SIGNED) ;  --  返回 0
    SELECT '123'+0;  --  返回数字类型 123

数字转字符串

  SELECT CONCAT(123) ； --返回字符串 "123"
  SELECT CAST(123 AS CHAR);  --  返回"123"

字符串转日期

  SELECT CONVERT("20190415",DATE)  --  2019-04-15
  SELECT CAST("20190415",DATE);  --  2019-04-15

type类型：

二进制,同带binary前缀的效果 : BINARY

字符型,可带参数 : CHAR()

日期 : DATE

时间: TIME

日期时间型 : DATETIME

浮点数 : DECIMAL

整数 : SIGNED

无符号整数 : UNSIGNED

排序函数

自定义排序， 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

没有排序的字段放在最前面，排序字段根据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()参数从后往前，再是未经field()排序的字段。

系统函数

查看版本信息：VERSION()

  SELECT VERSION();  --  5.5.28

查看服务器的连接数:CONNECTION_ID()

  SELECT CONNECTION_ID();  --  27

查看当前用户的名字： CURRENT_USER()/SYSTEM_USER()/USER()/SESSION_USER()

  SELECT CURRENT_USER();  --root@localhost

查看当前数据库的名字 DATABASE()/SCHEMA()

  SELECT DATABASE(); --ycz_test

其它函数

从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进制数变成f2进制数：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地址与数字相互转换的函数
把数字转换为ip地址：INET_ATON(IP)
和
把ip地址转换为数字：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

加密函数

返回MD5加密后的字符串s MD5(s)

  SELECT MD5("hello"); --5d41402abc4b2a76b9719d911017c592

对字符串s进行加密，一般用来给用户密码做加密的。PASSWORD(s)

  SELECT PASSWORD("hello"); --*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119

使用字符串pswd_str作为密匙来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。ENCODE(str,pswd_str)
使用密匙pswd_str对二进制函数crypt_str进行解密，和ENCODE()配对使用。DECODE(crypt_str,pswd_str)
- 使用ENCODE()加密，并使用DECODE()进行解密。加密字符串为hello,密匙为world

  SELECT DECODE(ENCODE("hello","world"),"world");  --  hello

MySQL函数总结

日期函数

字符串函数

数学函数

聚合函数

数据类型转换函数

排序函数

系统函数

其它函数

加密函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数析