四、MySQL常用函数

作者: 码农工号9527 | 来源:发表于2020-09-09 21:16 被阅读0次

操作符优先级

• 下图展示了所有操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况下则从左到右执行

image.png

• 如果想改变优先级执行顺序，则可以使用括号

 mysql> SELECT 1+2*3;
 -> 7
 mysql> SELECT (1+2)*3;
 -> 9

对比操作符

• 对比操作符的执行结果为true,false,null三种
• Between A and B 代表检查值是否在A和B之间
• Coalesce()代表返回第一个非Null的值
• =代表相等操作符
• >代表大于操作符
• >=代表大于等于操作符
• Greatest()代表返回最大的值
• In()代表检查值是否在一系列的值之中
• Interval()代表返回比第一个参数小的参数的位置
• is/is not代表检查值是否与布尔值相同/不同
• Is not null代表检查值是否是非NULL
• Is null代表检查值是否是NULL
• Isnull()代表检查参数是NULL
• Least()代表返回最小的参数
• <代表小于操作符
• <=代表小于等于操作符
• Like代表字符匹配
• Not between A and B代表检查值是否不在A和B的范围之内
• !=/<>代表不等于操作符
• Not in()代表检查值是否不在一系列值的当中
• Not like代表检查值是否不匹配
• Strcmp()对比两个字符串
• =号对比操作符

 mysql> SELECT 1 = 0;
 -> 0
 mysql> SELECT '0' = 0;
 -> 1
 mysql> SELECT '0.0' = 0;
 -> 1
 mysql> SELECT '0.01' = 0;
 -> 0
 mysql> SELECT '.01' = 0.01;
 -> 1

• <>/!=号对比操作符

 mysql> SELECT '.01' <> '0.01';
 -> 1
 mysql> SELECT .01 <> '0.01';
 -> 0
 mysql> SELECT 'zapp' <> 'zappp';
 -> 1

• <=小于等于对比操作符

 mysql> SELECT 0.1 <= 2; 
 -> 1

• <小于对比操作符

 mysql> SELECT 2 < 2; 
 -> 0

• >=大于等于对比操作符

 mysql> SELECT 2 >= 2; 
 -> 1

• >大于对比操作符

 mysql> SELECT 2 > 2; 
 -> 0

• is操作符

 mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
 -> 1, 1, 1

• is not操作符

 mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
 -> 1, 1, 0

• is null对比操作符

 mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
 -> 0, 0, 1

• is not null对比操作符

 mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
 -> 1, 1, 0

• Expr between min and max对比操作符【相当于min <= expr AND expr <= max】

 mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
 -> 1, 0
 mysql> SELECT 1 BETWEEN 2 AND 3;
 -> 0
 mysql> SELECT 'b' BETWEEN 'a' AND 'c';
 -> 1
 mysql> SELECT 2 BETWEEN 2 AND '3';
 -> 1
 mysql> SELECT 2 BETWEEN 2 AND 'x-3';
 -> 0

• expr NOT BETWEEN min AND max【相当于NOT (expr BETWEEN min AND max)】
• COALESCE(value,...)对比操作符【返回第一个非NULL的值，如果没有非null值，则返回NULL】

 mysql> SELECT COALESCE(NULL,2);
 -> 2
 mysql> SELECT COALESCE(NULL,NULL,NULL);
 -> NULL

• GREATEST(value1,value2,...)【返回其中最大的值】

 mysql> SELECT GREATEST(2,0);
 -> 2
 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
 -> 767.0
 mysql> SELECT GREATEST('B','A','C');
 -> 'C'

• expr IN (value,...)对比操作符【当expr值能在values中找到，则返回1，否则返回0】

 mysql> SELECT 2 IN (0,3,5,7);
 -> 0
 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
 -> 1
 mysql> SELECT (3,4) IN ((1,2), (3,4));
 -> 1
 mysql> SELECT (3,4) IN ((1,2), (3,5));
 -> 0

• expr NOT IN (value,...)对比操作符
• isnull(expr)操作符【如果expr是null，则返回1，否则返回0】

 mysql> SELECT ISNULL(1+1);
 -> 0
 mysql> SELECT ISNULL(1/0);
 -> 1

• LEAST(value1,value2,...)【返回最小值，如果其中有值为null，则返回null】

 mysql> SELECT LEAST(2,0);
 -> 0
 mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
 -> 3.0
 mysql> SELECT LEAST('B','A','C');
 -> 'A'

• 逻辑操作符返回1 (TRUE), 0 (FALSE), 或者NULL

image.png
• NOT, !逻辑操作符代表非操作

 mysql> SELECT NOT 10;
 -> 0
 mysql> SELECT NOT 0;
 -> 1
 mysql> SELECT NOT NULL;
 -> NULL
 mysql> SELECT ! (1+1);
 -> 0
 mysql> SELECT ! 1+1;
 -> 1

• And,&&逻辑操作符

 mysql> SELECT 1 AND 1;
 -> 1
 mysql> SELECT 1 AND 0;
 -> 0
 mysql> SELECT 1 AND NULL;
 -> NULL
 mysql> SELECT 0 AND NULL;
 -> 0
 mysql> SELECT NULL AND 0;
 -> 0

• Or, ||逻辑操作符

 mysql> SELECT 1 OR 1;
 -> 1
 mysql> SELECT 1 OR 0;
 -> 1
 mysql> SELECT 0 OR 0;
 -> 0
 mysql> SELECT 0 OR NULL;
 -> NULL
 mysql> SELECT 1 OR NULL;
 -> 1

• Xor逻辑操作符【异或是一种逻辑运算,运算法则简言之就是:两个条件相同(同真或同假)即为假(0),两个条件不同即为真(1)】

 mysql> SELECT 1 XOR 1;
 -> 0
 mysql> SELECT 1 XOR 0;
 -> 1
 mysql> SELECT 1 XOR NULL;
 -> NULL
 mysql> SELECT 1 XOR 1 XOR 1;
 -> 1

• 分配操作符是指赋值操作

'=' : 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
':=' : 不只在set和update时时赋值的作用，在select也是赋值的作用

image.png

 mysql> SELECT @var1, @var2;
 -> NULL, NULL
 mysql> SELECT @var1 := 1, @var2;
 -> 1, NULL
 mysql> SELECT @var1, @var2;
 -> 1, NULL
 mysql> SELECT @var1, @var2 := @var1;
 -> 1, 1
 mysql> SELECT @var1, @var2;
 -> 1, 1
 mysql> SELECT @var1:=COUNT(*) FROM t1;
 -> 4
 mysql> SELECT @var1;
 -> 4

• :=操作符也可以用在update等语句

 mysql> SELECT @var1;
 -> 4
 mysql> SELECT * FROM t1;
 -> 1, 3, 5, 7
 mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0
 mysql> SELECT @var1;
 -> 1
 mysql> SELECT * FROM t1;
 -> 2, 3, 5, 7

• =操作符在两种情况下会被认为是赋值操作，而其他情况下会认为是对比操作符

在set语句中，=操作符会被认为是赋值操作

 mysql> set @a=1;
 mysql> select @a;
 +------+
 | @a |
 +------+
 | 1 |
 +------+

在update语句中的set子句中，=操作符会被认为是赋值操作

• 流程控制函数包含以下四种函数

image.png

• CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

当value等于compare_value时，则返回result，否则返回else里的result，如果没有else子句则返回null

 mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
 -> 'one'

• CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

当第一个condition满足时，则返回result，否则返回else里的result，如果没有else子句时则返回null

 mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
 -> 'true'
 mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
 -> NULL

• IF(expr1,expr2,expr3)

当expr1为1/true时，则返回expr2，否则返回expr3

 mysql> SELECT IF(1>2,2,3);
 -> 3
 mysql> SELECT IF(1<2,'yes','no');
 -> 'yes'
 mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
 -> 'no'

• NULLIF(expr1,expr2)

当expr1等于expr2时，则返回null，否则返回expr1

 mysql> SELECT NULLIF(1,1);
 -> NULL
 mysql> SELECT NULLIF(1,2);
 -> 1

字符串函数

• ASCII(str)

返回str字符串中最左边字符的ascii码值，如果是空串则返回0，如果str是null则返回null

 mysql> SELECT ASCII('2');
 -> 50
 mysql> SELECT ASCII(2);
 -> 50
 mysql> SELECT ASCII('dx');
 -> 100

• CHAR(N,... [USING charset_name])

将括号中的N转化成ascii码对应的字符，返回这些字符组成的字符串，其中的null会被忽略

 mysql> SELECT CHAR(77,121,83,81,'76');
 -> 'MySQL'
 mysql> SELECT CHAR(77,77.3,'77.3');
 -> 'MMM'
 mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));
 +----------------------+---------------------------------+
 | CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |
 +----------------------+---------------------------------+
 | binary               | utf8                            |
 +----------------------+---------------------------------+

• CHAR_LENGTH(str)

返回字符串的字符长度

• CONCAT(str1,str2,...)

返回括号里所有参数字符串连接在一起，当其中有参数为NULL时则返回NULL

 mysql> SELECT CONCAT('My', 'S', 'QL');
 -> 'MySQL'
 mysql> SELECT CONCAT('My', NULL, 'QL');
 -> NULL
 mysql> SELECT CONCAT(14.3);
 -> '14.3'

• CONCAT_WS(separator,str1,str2,...)

返回以第一个参数为分隔符的连接后的一个字符串，当有参数为NULL时则null被忽略

 mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
 -> 'First name,Second name,Last Name'
 mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
 -> 'First name,Last Name'

• INSERT(str,pos,len,newstr)

将str中从pos位置开始后的len个字符替换成newstr字符串
str：指定字符串
pos：开始被替换的位置
len：被替换的字符串长度
newstr：新的字符串
总结：替换掉 str 范围为 [ pos, pos+len ] 的字符串
注意：如果 pos > str 长度以内，则返回 str 不会被替换；如果 len > str 剩余字符串的长度，则将 str 剩下所有字符都替换成 newstr

 mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
 -> 'QuWhattic'
 mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
 -> 'Quadratic'
 mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
 -> 'QuWhat'

• INSTR(str,substr)

返回str字符串中第一个出现substr字符串的位置

 mysql> SELECT INSTR('foobarbar', 'bar');
 -> 4
 mysql> SELECT INSTR('xbar', 'foobar');
 -> 0

• LEFT(str,len)

返回str字符串中从左边开始的len个长度的字符

 mysql> SELECT LEFT('foobarbar', 5);
 -> 'fooba'

• LENGTH(str)

返回str字符串的byte字节长度

 mysql> SELECT LENGTH('text');
 -> 4

• LOAD_FILE(file_name)

读取文件且返回文件内容为字符串
mysql> UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;

• LOCATE(substr,str)

返回str字符串中第一次出现substr字符串的位置，如果没有则返回0

 mysql> SELECT LOCATE('bar', 'foobarbar');
 -> 4
 mysql> SELECT LOCATE('xbar', 'foobar');
 -> 0

• LOCATE(substr,str,pos)

返回str字符串中从pos位置开始第一次出现substr字符串的位置，如果没有则返回0

 mysql> SELECT LOCATE('bar', 'foobarbar', 5);
 -> 7

• LOWER(str)

返回将str字符串中所有字符变换成小写后的字符串，但对二进制文本无效

 mysql> SELECT LOWER('QUADRATICALLY');
 -> 'quadratically'
 mysql> SET @str = BINARY 'New York';
 mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
 +-------------+-----------------------------------+
 | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
 +-------------+-----------------------------------+
 | New York    | new york                          |
 +-------------+-----------------------------------+

• LPAD(str,len,padstr)

将str的左边补充为padstr，直到补充成len长度的字符串并返回；如果str的长度比len长，则返回str中最左边开始的len长度的字符

 mysql> SELECT LPAD('hi',4,'??');
 -> '??hi'
 mysql> SELECT LPAD('hi',1,'??');
 -> 'h'

• LTRIM(str)

将str最左边的空格去掉并返回

 mysql> SELECT LTRIM(' barbar');
 -> 'barbar‘

• REPEAT(str,count)

将str重复count并组合成字符串返回，如果count<1，则返回空串

mysql> SELECT REPEAT('MySQL', 0);
+--------------------+
| REPEAT('MySQL', 0) |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT REPEAT('MySQL', 3);
+--------------------+
| REPEAT('MySQL', 3) |
+--------------------+
| MySQLMySQLMySQL    |
+--------------------+
1 row in set (0.00 sec)

• REPLACE(str,from_str,to_str)

将所有str字符串中匹配from_str子串的地方都替换成to_str子字符串

 mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
 -> 'WwWwWw.mysql.com'

• REVERSE(str)

将str字符串中的字符按照倒序组合并返回

 mysql> SELECT REVERSE('abc');
 -> 'cba'

• RIGHT(str,len)

将str字符串中从右边开始的len个字符返回

 mysql> SELECT RIGHT('foobarbar', 4);
 -> 'rbar'

• RPAD(str,len,padstr)

将字符串str从右边开始补充为padstr直到整体长度为len，如果str的长度本身大于len，则返回str中len长度的字符串

 mysql> SELECT RPAD('hi',5,'?');
 -> 'hi???'
 mysql> SELECT RPAD('hi',1,'?');
 -> 'h'

• RTRIM(str)

将字符串str右边的空格去掉并返回

 mysql> SELECT RTRIM('barbar ');
 -> 'barbar‘

• SPACE(N)

返回N个长度的空格组成的空字符串

 mysql> SELECT SPACE(6);
 -> ' '

• SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)
• SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

如果没有len参数，则返回从pos位置开始的str中的子字符串；如果有len参数，则从pos位置开始返回str中长度为len的子字符串；如果pos为负值，则代表pos从右边开始数

 mysql> SELECT SUBSTRING('Quadratically',5);
 -> 'ratically'
 mysql> SELECT SUBSTRING('foobarbar' FROM 4);
 -> 'barbar'
 mysql> SELECT SUBSTRING('Quadratically',5,6);
 -> 'ratica'
 mysql> SELECT SUBSTRING('Sakila', -3);
 -> 'ila'
 mysql> SELECT SUBSTRING('Sakila', -5, 3);
 -> 'aki'
 mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
 -> 'ki'

• SUBSTRING_INDEX(str,delim,count)

当count为正数，则返回delim出现在str字符串中第count次之前的子字符串，如果是负数，则从右边开始计算

 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
 -> 'www.mysql'
 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
 -> 'mysql.com'

• UPPER(str)

返回将str字符串中所有字符转换成大写的字符串

 mysql> SELECT UPPER('Hej');
 -> 'HEJ'

字符串对比函数

image.png

通配符%表示匹配0个或多个字符
通配符_表示匹配1个字符

 mysql> SELECT 'David!' LIKE 'David_';
 -> 1
 mysql> SELECT 'David!' LIKE '%D%v%';
 -> 1
 当匹配字符中有特殊字符时，可以用 \或者escape来指定特殊字符为字符
 mysql> SELECT 'David!' LIKE 'David\_';
 -> 0
 mysql> SELECT 'David_' LIKE 'David\_';
 -> 1
#意思就是说|之后的_不作为通配符,|也不会放入匹配规则中，相当于SELECT 'David_' LIKE 'David\_';
 mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
 -> 1

• expr NOT LIKE pat [ESCAPE 'escape_char']

是上一个字符串对比函数的反义

• STRCMP(expr1,expr2)

当expr1等于expr2时等于0，当expr1小于expr2时为-1，反之为1

 mysql> SELECT STRCMP('text', 'text2');
 -> -1
 mysql> SELECT STRCMP('text2', 'text');
 -> 1
 mysql> SELECT STRCMP('text', 'text');
 -> 0
 mysql> SET @s1 = _latin1 'x' COLLATE latin1_general_ci;
 mysql> SET @s2 = _latin1 'X' COLLATE latin1_general_ci;
 mysql> SET @s3 = _latin1 'x' COLLATE latin1_general_cs;
 mysql> SET @s4 = _latin1 'X' COLLATE latin1_general_cs;
 mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
 +------------------+------------------+
 | STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
 +------------------+------------------+
 | 0                | 1                |
 +------------------+------------------+

数字函数之算数操作符

image.png

• /和DIV

/代表除法；div代表整数型除法，相除之后只取整数部分

 mysql> SELECT 3/5;
 -> 0.60
 mysql> SELECT 102/(1-1);
 -> NULL
 mysql> SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;
 -> 2, -2, -2, 2

数字函数

• ABS(X)

绝对值函数

 mysql> SELECT ABS(2);
 -> 2
 mysql> SELECT ABS(-32);
 -> 32

• CEILING(X)/CEIL(X)

返回>=X值的最小整数(向上取整)

 mysql> SELECT CEILING(1.23);
 -> 2
 mysql> SELECT CEILING(-1.23);
 -> -1

• FLOOR(X)

返回<=X值的最大整数(向下取整)

 mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
 -> 1, -2

• MOD(N,M), N % M, N MOD M

取余数操作，返回两者相除之后剩下的余数

 mysql> SELECT MOD(234, 10);
 -> 4
 mysql> SELECT 253 % 7;
 -> 1
 mysql> SELECT MOD(29,9);
 -> 2
 mysql> SELECT 29 MOD 9;
 -> 2
 mysql> SELECT MOD(34.5,3);
 -> 1.5

• RAND([N])

获取0到1之间的随机小数，比如当想获取7~12之间的随机整数是可以使用SELECT FLOOR(7 + (RAND() * 5));

 Select * from students order by rand() limit 1;

• ROUND(X), ROUND(X,D)

四舍五入为D位个小数，当D参数不存在时，则默认为0

 mysql> SELECT ROUND(-1.23);
 -> -1
 mysql> SELECT ROUND(-1.58);
 -> -2
 mysql> SELECT ROUND(1.58);
 -> 2
 mysql> SELECT ROUND(1.298, 1);
 -> 1.3
 mysql> SELECT ROUND(1.298, 0);
 -> 1
 mysql> SELECT ROUND(23.298, -1);
 -> 20

• TRUNCATE(X,D)

数字X只保留D位的小数，其余均舍弃

 mysql> SELECT TRUNCATE(1.223,1);
 -> 1.2
 mysql> SELECT TRUNCATE(1.999,1);
 -> 1.9
 mysql> SELECT TRUNCATE(1.999,0);
 -> 1
 mysql> SELECT TRUNCATE(-1.999,1);
 -> -1.9
 mysql> SELECT TRUNCATE(122,-2);
 -> 100
 mysql> SELECT TRUNCATE(10.28*100,0);
 -> 1028

日期和时间函数

• ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

当expr为正数时则为增加时间，为负数时则为减少时间
Unit参数可以是任意时间单位

• mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
• -> '2008-02-02'
• mysql> SELECT ADDDATE('2008-01-02', 31);
• -> '2008-02-02'

• ADDTIME(expr1,expr2)

将expr2的时间增加到expr1上

 mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
 -> '2008-01-02 01:01:01.000001'
 mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
 -> '03:00:01.999997'

• CONVERT_TZ(dt,from_tz,to_tz)

将时间dt从from_tz这个时区转换成to_tz这个时区并返回

 mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
 -> '2004-01-01 13:00:00'
 mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
 -> '2004-01-01 22:00:00'

• CURDATE()， CURRENT_DATE, CURRENT_DATE()

返回以yyyy-mm-dd或者yyyymmdd格式的当前时间

 mysql> SELECT CURDATE();
 -> '2008-06-13'
 mysql> SELECT CURDATE() + 0;
 -> 20080613

• CURTIME(),CURRENT_TIME,CURRENT_TIME()

按照hh:mm:ss或者hhmmss格式返回当前时间

 mysql> SELECT CURTIME();
 -> '23:50:26'
 mysql> SELECT CURTIME() + 0;
 -> 235026.000000

• Now(), CURRENT_TIMESTAMP,CURRENT_TIMESTAMP()

返回当前的日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss

 mysql> SELECT NOW();
 -> '2007-12-15 23:50:26'
 mysql> SELECT NOW() + 0;
 -> 20071215235026.000000

• DATE(expr)

获取expr中的日期

 mysql> SELECT DATE('2003-12-31 01:02:03');
 -> '2003-12-31‘

• DATEDIFF(expr1,expr2)

返回expr1和expr2之间的天数差异，忽略时分秒

 mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
 -> 1
 mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
 -> -31

• DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

增加或者减少时间

 mysql> SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
-> '2001-01-01 00:00:00'
 mysql> SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
-> '2011-01-01 23:59:59'
 mysql> SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
 mysql> SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
-> '2004-12-30 22:58:59'
 mysql> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
 mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
 mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
 mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH);
-> '2009-02-28'

image.png

• DATE_FORMAT(date,format)

将date日期时间转换成format格式

 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
 -> 'Sunday October 2009'
 mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
 -> '22:23:00'
 mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
 -> '%D %y %a %d %m %b %j');
 -> '4th 00 Thu 04 10 Oct 277'
 mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
 -> '%H %k %I %r %T %S %w');
 -> '22 22 10 10:23:00 PM 22:23:00 00 6'
 mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
 -> '1998 52'
 mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
 -> '00'

image.png

• DAY(date), DAYOFMONTH(date)

返回date中日期在当前月份中是第几天

 mysql> SELECT DAYOFMONTH('2007-02-03');
 -> 3

• DAYNAME(date)

返回date时间是星期几

 mysql> SELECT DAYNAME('2007-02-03');
 -> 'Saturday'

• DAYOFWEEK(date)

返回date时间是星期“几”

 mysql> SELECT DAYOFWEEK('2007-02-03');
 -> 7

• DAYOFYEAR(date)

返回date是一年中的第几天，取值范围在1~366

 mysql> SELECT DAYOFYEAR('2007-02-03');
 -> 34

• EXTRACT(unit FROM date)

Unit单元和date_add/date_sub函数中的一样，是获取date日期的unit部分

 mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
 -> 2009
 mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
 -> 200907
 mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
 -> 20102
 mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
 -> 123

• FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

根据给定的unixtime，返回yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss格式的具体时间，或者按照format返回时间

 mysql> SELECT FROM_UNIXTIME(1447430881);
 -> '2015-11-13 10:08:01'
 mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
 -> 20151113100801
 mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
 -> '2015 13th November 10:08:01 2015'

• LAST_DAY(date)

返回date日期所在月份的最后一天日期

 mysql> SELECT LAST_DAY('2004-02-05');
 -> '2004-02-29'
 mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
 -> '2004-01-31'
 mysql> SELECT LAST_DAY('2003-03-32');
 -> NULL

• SYSDATE()

返回当前日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss
和now()函数的区别在于now()返回的时间是语句执行的时间，而sysdate()返回的时间是该函数执行的时间

 mysql> SELECT NOW(), SLEEP(2), NOW();
 +---------------------+----------+---------------------+
 | NOW()               | SLEEP(2) | NOW()               |
 +---------------------+----------+---------------------+
 | 2006-04-12 13:47:36 | 0        | 2006-04-12 13:47:36 |
 +---------------------+----------+---------------------+
 mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
 +---------------------+----------+---------------------+
 | SYSDATE()           | SLEEP(2) | SYSDATE()           |
 +---------------------+----------+---------------------+
 | 2006-04-12 13:47:44 | 0        | 2006-04-12 13:47:46 |
 +---------------------+----------+---------------------+

• TIME(expr)

返回expr日期时间中的时间部分

 mysql> SELECT TIME('2003-12-31 01:02:03');
 -> '01:02:03'
 mysql> SELECT TIME('2003-12-31 01:02:03.000123');
 -> '01:02:03.000123'

• UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

如果没有date参数，则返回当前时间到1970-01-01 00:00:00之间的秒数，
如果有date参数，则表示date到1970-01-01 00:00:00之间的秒数

 mysql> SELECT UNIX_TIMESTAMP();
 -> 1447431666
 mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
 -> 1447431619

格式转换函数

• Cast()和convert()两个函数都可以用来转换数据类型或者转换字符集

允许转换的数据类型包括：
Binary[N]
char[N]
Date
Datetime
decimal[M,[D]]
Time
Signed [integer]
Unsigned [integer]

 SELECT CONVERT(_latin1'Müller' USING utf8);
 SELECT CONVERT('test', CHAR CHARACTER SET utf8);
 SELECT CAST('test' AS CHAR CHARACTER SET utf8);
 SELECT CAST('2000-01-01' AS DATE);
 SELECT CONVERT('2000-01-01', DATE);

聚合函数

• 用在存在group by子句的语句中
• AVG([DISTINCT] expr)

计算expr的平均值，distinct关键词表示是否排除重复值

 mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;

• COUNT(expr)

计算expr中的个数，如果没有匹配则返回0，注意NULL的区别

 mysql> SELECT student.student_name,COUNT(*)
 -> FROM student,course
 -> WHERE student.student_id=course.student_id
 -> GROUP BY student_name;

• COUNT(DISTINCT expr,[expr...])

计算有多少个不重复的expr值，注意是计算非NULL的个数

 mysql> SELECT COUNT(DISTINCT results) FROM student

• MAX([DISTINCT] expr),MIN([DISTINCT] expr)

返回expr中最大或者最小的值

 mysql> SELECT student_name, MIN(test_score), MAX(test_score)
 -> FROM student
 -> GROUP BY student_name;

• SUM([DISTINCT] expr)

返回expr的求和值

子查询

• 子查询是一个嵌套在外层语句中的完整select语句，通常用（）括起来，子查询可以范围一个单一的值，一行值，一个表格等等。
• 通常子查询的方式可以简化复杂Join表链接查询和Union结果合并查询，提高了语句的可读性

 SELECT (SELECT s1 FROM t2) FROM t1;
 SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);
 DELETE FROM t1 where column1 in (select column1 from t2);
 SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
 SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
 SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
 SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
 SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);
 SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
 SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;
 SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
 SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

四、MySQL常用函数

操作符优先级

对比操作符

字符串函数

字符串对比函数

数字函数之算数操作符

数字函数

日期和时间函数

格式转换函数

聚合函数

子查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读