MySQL函数大全
介绍
MySQL 函数会对传递进来的参数进行处理,并返回一个处理结果,也就是返回一个值。
MySQL 包含了大量并且丰富的函数,这套 MySQL 函数大全只收集了几十个常用的,剩下的比较罕见的函数我们就不再整理了,读者可以到「MySQL 官网」查询。
可以对 MySQL 常用函数进行简单的分类,大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。
MySQL 数值型函数
函数名称 | 作 用 |
---|---|
ABS | 求绝对值 |
SQRT | 求二次方根 |
MOD | 求余数 |
CEIL 和 CEILING | 两个函数功能相同,都是返回不小于参数的最小整数,即向上取整 |
FLOOR | 向下取整,返回值转化为一个BIGINT |
RAND | 生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列 |
ROUND | 对所传参数进行四舍五入 |
SIGN | 返回参数的符号 |
POW 和 POWER | 两个函数的功能相同,都是所传参数的次方的结果值 |
SIN | 求正弦值 |
ASIN | 求反正弦值,与函数 SIN 互为反函数 |
COS | 求余弦值 |
ACOS | 求反余弦值,与函数 COS 互为反函数 |
TAN | 求正切值 |
ATAN | 求反正切值,与函数 TAN 互为反函数 |
COT | 求余切值 |
ABS
中绝对值函数 ABS(x) 返回 x 的绝对值。正数的绝对值是其本身,负数的绝对值为其相反数,0 的绝对值是 0。
【实例】求 5、-2.4、-24 和 0 的绝对值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0);
+--------+-----------+----------+--------+
| ABS(5) | ABS(-2.4) | ABS(-24) | ABS(0) |
+--------+-----------+----------+--------+
| 5 | 2.4 | 24 | 0 |
+--------+-----------+----------+--------+
1 row in set (0.10 sec)
由运行结果可知,5 的绝对值为 5,-2.4 的绝对值为 2.4,-24 的绝对值为 24,0 的绝对值是 0。
SQRT
平方根函数 SQRT(x) 返回非负数 x 的二次方根。负数没有平方根,返回结果为 NULL。
【实例 1】求 25、120 和 -9 的二次平方根,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT SQRT(25),SQRT(120),SQRT(-9);
+----------+--------------------+----------+
| SQRT(25) | SQRT(120) | SQRT(-9) |
+----------+--------------------+----------+
| 5 | 10.954451150103322 | NULL |
+----------+--------------------+----------+
1 row in set (0.06 sec)
由运行结果可知,5 的平方等于 25,因此 25 的平方根为 5;120 的平方根为 10.954451150103322;而负数没有平方根,因此 -9 返回的结果为 NULL。
MOD
求余函数 MOD(x,y) 返回 x 被 y 除后的余数,MOD() 对于带有小数部分的数值也起作用,它返回除法运算后的余数。
【实例】对 MOD(63,8)、MOD(120,10)、MOD(15.5,3) 进行求余运算,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
+-----------+-------------+-------------+
| MOD(63,8) | MOD(120,10) | MOD(15.5,3) |
+-----------+-------------+-------------+
| 7 | 0 | 0.5 |
+-----------+-------------+-------------+
1 row in set (0.03 sec)
由运行结果可知,63 除以 8 余数是 7,120 除以 10 余数是 0,15.5 除以 3 余数是 0.5
CEIL 和 CEILING
CEIL(x) 和 CEILING(x) 的意义相同,返回不小于 x 的最小整数值,返回值转化为一个 BIGINT。 (即向上取整)
【实例】使用 CEILING 函数返回最小整数,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT CEIL(-2.5),CEILING(2.5);
+------------+--------------+
| CEIL(-2.5) | CEILING(2.5) |
+------------+--------------+
| -2 | 3 |
+------------+--------------+
1 row in set (0.00 sec)
由执行结果可知,
-2.5 为负数,不小于 -2.5 的最小整数为 -2,因此返回值为 -2;
不小于 2.5 的最小整数为 3,因此返回值为 3。
FLOOR
FLOOR(x) 函数返回小于 x 的最大整数值。 (向下取整,返回值转化为一个BIGINT)
【实例】求 小于 5,5.66,-4,-4.66 的最大整数
mysql> SELECT FLOOR(5),FLOOR(5.66),FLOOR(-4),FLOOR(-4.66);
+----------+-------------+-----------+--------------+
| FLOOR(5) | FLOOR(5.66) | FLOOR(-4) | FLOOR(-4.66) |
+----------+-------------+-----------+--------------+
| 5 | 5 | -4 | -5 |
+----------+-------------+-----------+--------------+
1 row in set (0.00 sec)
由运行结果可知,小于 5,5.66,-4,-4.66 的最大整数依次是 5,5,-4,-5。
RAND
RAND() 函数被调用时,可以产生一个在 0 和 1 之间的随机数。
mysql> SELECT RAND(), RAND(), RAND();
+------------------+-----------------+------------------+
| RAND() | RAND() | RAND() |
+------------------+-----------------+------------------+
| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |
+------------------+-----------------+------------------+
1 row in set (0.00 sec)
由运行结果可知,每次调用 RAND() 函数,都会随机生成一个 0~1 之间的随机数 。
当使用整数作为参数调用时,RAND() 使用该值作为随机数的种子发生器。每次种子使用给定值生成,RAND() 将产生一个可重复的系列数字:(入整数参数是,用来产生重复序列)
mysql> SELECT RAND(1),RAND(),RAND(1);
+---------------------+--------------------+---------------------+
| RAND(1) | RAND() | RAND(1) |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.7901452330780637 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)
ROUND
ROUND(x) 函数返回最接近于参数 x 的整数;ROUND(x,y) 函数对参数x进行四舍五入的操作,返回值保留小数点后面指定的y位。
【实例 1】使用ROUND(x)函数,输入 SQL 语句执行结果如下。
mysql> SELECT ROUND(-6.6),ROUND(-8.44),ROUND(3.44);
+-------------+--------------+-------------+
| ROUND(-6.6) | ROUND(-8.44) | ROUND(3.44) |
+-------------+--------------+-------------+
| -7 | -8 | 3 |
+-------------+--------------+-------------+
1 row in set (0.00 sec)
从执行结果可以看出,ROUND(x)函数将值x四舍五入之后保留了整数部分。
【实例 2】使用ROUND(x,y)函数,输入 SQL 语句执行结果如下。
mysql> SELECT ROUND(-6.66,1),ROUND(3.33,3),ROUND(88.66,-1),ROUND(88.46,-2);
+----------------+---------------+-----------------+-----------------+
| ROUND(-6.66,1) | ROUND(3.33,3) | ROUND(88.66,-1) | ROUND(88.46,-2) |
+----------------+---------------+-----------------+-----------------+
| -6.7 | 3.330 | 90 | 100 |
+----------------+---------------+-----------------+-----------------+
1 row in set (0.00 sec)
从执行结果可以看出,根据参数y值,将参数x四舍五入后得到保留小数点后y位的值,x值的小数位不够y位的补零;如果y为负值,则保留小数点左边y位,先进行四舍五入操作,再将相应的位数值取零。
SIGN
符号函数 SIGN(x) 返回参数的符号,x 的值为负、零和正时返回结果依次为 -1、0 和 1。
【实例】使用 SIGN 函数返回参数的符号,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT SIGN(-6),SIGN(0),SIGN(34);
+----------+---------+----------+
| SIGN(-6) | SIGN(0) | SIGN(34) |
+----------+---------+----------+
| -1 | 0 | 1 |
+----------+---------+----------+
1 row in set (0.00 sec)
由执行结果可知,SIGN(-6) 返回 -1,SIGN(0) 返回 0,SIGN(34) 返回 1。
POW 和 POWER
POW(x,y) 函数和 POWER(x,y) 函数用于计算 x 的 y 次方。
【实例】使用 POW(x,y) 和 POWER(x,y) 函数对参数 x 进行 y 次方的求值。输入 SQL 语句和执行结果如下。
mysql> SELECT POW(5,-2),POW(10,3),POW(100,0),POWER(4,3),POWER(6,-3);
+-----------+-----------+------------+------------+----------------------+
| POW(5,-2) | POW(10,3) | POW(100,0) | POWER(4,3) | POWER(6,-3) |
+-----------+-----------+------------+------------+----------------------+
| 0.04 | 1000 | 1 | 64 | 0.004629629629629629 |
+-----------+-----------+------------+------------+----------------------+
1 row in set (0.00 sec)
SIN
正弦函数 SIN(x) 返回 x 的正弦值,其中 x 为弧度值。
【实例】使用 SIN 函数计算正弦值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT SIN(1),SIN(0.5*PI());
+--------------------+---------------+
| SIN(1) | SIN(0.5*PI()) |
+--------------------+---------------+
| 0.8414709848078965 | 1 |
+--------------------+---------------+
1 row in set (0.15 sec)
提示:PI() 函数返回圆周率(3.141593)
ASIN
反正弦函数 ASIN(x) 返回 x 的反正弦值,若 x 不在 -1 到 1 的范围之内,则返回 NULL。
【实例】使用 ASIN 函数计算反正弦值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT ASIN(0.8414709848078965),ASIN(2);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(2) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
1 row in set (0.03 sec)
由结果可以看到,ASIN(2) 中的参数 2 超出了正弦值的范围,因此返回 NULL。
COS
余弦函数 COS(x) 返回 x 的余弦值,x 为弧度值。
【实例】使用 COS 函数计算余弦值,输入的 SQL 语句和执行结果如下所示
mysql> SELECT COS(1),COS(0),COS(PI());
+--------------------+--------+-----------+
| COS(1) | COS(0) | COS(PI()) |
+--------------------+--------+-----------+
| 0.5403023058681398 | 1 | -1 |
+--------------------+--------+-----------+
1 row in set (0.03 sec)
由结果可以看到,COS(0) 的值为 1,COS(PI()) 的值为 -1,COS(1) 的值为 0.5403023058681398。
ACOS
反余弦函数 ACOS(x) 。x 值的范围必须在 -1 和 1 之间,否则返回 NULL。
【实例】使用 ACOS 函数计算反余弦值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT ACOS(2),ACOS(1),ACOS(-1);
+---------+---------+-------------------+
| ACOS(2) | ACOS(1) | ACOS(-1) |
+---------+---------+-------------------+
| NULL | 0 | 3.141592653589793 |
+---------+---------+-------------------+
1 row in set (0.01 sec)
TAN
正切函数 TAN(x) 返回 x 的正切值,x 为给定的弧度值。
【实例】使用 TAN 函数计算正切值,输入的 SQL 语句和执行结果如下所示
mysql> SELECT TAN(1),TAN(0);
+--------------------+--------+
| TAN(1) | TAN(0) |
+--------------------+--------+
| 1.5574077246549023 | 0 |
+--------------------+--------+
1 row in set (0.03 sec)
由运行结果可知,TAN(1) 的值为 1.5574077246549023,TAN(0) 的值为 0。
ATAN
ATAN 函数计算反正切值,输入的 SQL 语句和执行结果如下所示。
【实例】使用 ATAN 函数计算反正切值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT ATAN(1.5574077246549023),ATAN(0);
+--------------------------+---------+
| ATAN(1.5574077246549023) | ATAN(0) |
+--------------------------+---------+
| 1 | 0 |
+--------------------------+---------+
1 row in set (0.05 sec)
COT
COT(x) 返回 x 的余切值,x 是给定的弧度值。
【实例】使用 COT 函数计算余切值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT COT(1);
+--------------------+
| COT(1) |
+--------------------+
| 0.6420926159343306 |
+--------------------+
1 row in set (0.00 sec)
由运行结果可知,COT(1) 的值为 0.6420926159343306。
MySQL 字符串函数
函数名称 | 作 用 |
---|---|
LENGTH | 计算字符串长度函数,返回字符串的字节长度 |
CONCAT | 合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个 |
INSERT | 替换字符串函数 |
LOWER | 将字符串中的字母转换为小写 |
UPPER | 将字符串中的字母转换为大写 |
LEFT | 从左侧字截取符串,返回字符串左边的若干个字符 |
RIGHT | 从右侧字截取符串,返回字符串右边的若干个字符 |
TRIM | 删除字符串左右两侧的空格 |
REPLACE | 字符串替换函数,返回替换后的新字符串 |
SUBSTRING | 截取字符串,返回从指定位置开始的指定长度的字符换 |
REVERSE | 字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串 |
LENGTH
LENGTH(str) 函数的返回值为字符串的字节长度,使用 uft8(UNICODE 的一种变长字符编码,又称万国码)编码字符集时,一个汉字是 3 个字节,一个数字或字母是一个字节。
【实例】使用 LENGTH 函数计算字符串长度,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT LENGTH('name'),LENGTH('数据库');
+----------------+---------------------+
|LENGTH('name') | LENGTH('数据库') |
+----------------+---------------------+
| 4 | 9 |
+----------------+---------------------+
1 row in set (0.04 sec)
由运行结果可以看到,一个汉字是 3 个字节,“数据库”字符串占 9 个字节。英文字符的个数和所占的字节相同,一个字符占 1 个字节。
CONCAT
CONCAT(sl,s2,…) 函数返回结果为连接参数产生的字符串,或许有一个或多个参数。
若有任何一个参数为 NULL,则返回值为 NULL。若所有参数均为非二进制字符串,则结果为非二进制字符串。若自变量中含有任一二进制字符串,则结果为一个二进制字符串。
【实例】使用 CONCAT 函数连接字符串,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL);
+-----------------------+----------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) |
+-----------------------+----------------------+
| MySQL5.7 | NULL |
+-----------------------+----------------------+
1 row in set (0.03 sec)
由运行结果可知,CONCAT(‘MySQL’,‘5.7’) 返回两个字符串连接后的字符串;CONCAT(‘MySQL’,NULL) 中有一个参数为 NULL,因此返回结果为 NULL。
INSERT
INSERT(s1,x,len,s2) 返回字符串 s1,子字符串起始于 x 位置,并且用 len 个字符长的字符串代替 s2。
若 x 超过字符串长度,则返回值为原始字符串。假如 len 的长度大于其他字符串的长度,则从位置 x 开始替换。若任何一个参数为 NULL,则返回值为 NULL。
【实例】使用 INSERT 函数进行字符串替换操作,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT INSERT('Football',2,4,'Play') AS col1,
-> INSERT('Football',-1,4,'Play') AS col2,
-> INSERT('Football',3,20,'Play') AS col3;
+----------+----------+--------+
| col1 | col2 | col3 |
+----------+----------+--------+
| FPlayall | Football | FoPlay |
+----------+----------+--------+
1 row in set (0.04 sec)
由执行结果可知:
- 第一个函数 INSERT(‘Football’,2,4,‘Play’) 将“Football”从第 2 个字符开始长度为 4 的字符串替换为 Play,结果为“FPlayall”;
- 第二个函数 (‘Football’,-1,4,‘Play’) 中的起始位置 -1 超出了字符串长度,直接返回原字符串;
- 第三个函数 INSERT(‘Football’,3,20,‘Play’) 替换长度超出了原字符串长度,则从第 3 个字符开始,截取后面所有的字符,并替换为指定字符 Play,结果为“FoPlay”。
LOWER
LOWER(str) 可以将字符串 str 中的字母字符全部转换成小写。
【实例】使用 LOWER 函数将字符串中所有的字母字符转换为小写,输入的 SQL 语句和执行结果如下所示
mysql> SELECT LOWER('BLUE'),LOWER('Blue');
+---------------+---------------+
| LOWER('BLUE') | LOWER('Blue') |
+---------------+---------------+
| blue | blue |
+---------------+---------------+
1 row in set (0.03 sec)
由结果可以看到,原来所有字母为大写的,全部转换为小写,如“BLUE”,转换之后为“blue”;大小写字母混合的字符串,小写不变,大写字母转换为小写字母,如“Blue”,转换之后为“bule”。
UPPER
UPPER(str) 可以将字符串 str 中的字母字符全部转换成大写。
【实例】使用 UPPER 函数将字符串中所有的字母字符转换为大写,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT UPPER('green'),UPPER('Green');
+----------------+----------------+
| UPPER('green') | UPPER('Green') |
+----------------+----------------+
| GREEN | GREEN |
+----------------+----------------+
1 row in set (0.03 sec)
由结果可以看到,原来所有字母字符为小写的,全部转换为大写,如“green”,转换之后为“GREEN”;大小写字母混合的字符串,大写不变,小写字母转换为大写字母,如“Green”,转换之后为“GREEN”。
LEFT
LEFT(s,n) 函数返回字符串 s 最左边的 n 个字符。
【实例】使用 LEFT 函数返回字符串中左边的字符,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT LEFT('MySQL',2);
+-----------------+
| LEFT('MySQL',2) |
+-----------------+
| My |
+-----------------+
1 row in set (0.04 sec)
由运行结果可知,返回字符串“MySQL”左边开始的长度为 2 的子字符串,结果为“My”。
RIGHT
RIGHT(s,n) 函数返回字符串 s 最右边的 n 个字符。
【实例】使用 RIGHT 函数返回字符串中右边的字符,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT RIGHT('MySQL',3);
+------------------+
| RIGHT('MySQL',3) |
+------------------+
| SQL |
+------------------+
1 row in set (0.00 sec)
由执行结果可知,函数返回字符串“MySQL”右边开始的长度为3的子字符串,结果为“SQL”。
TRIM
TRIM(s) 删除字符串 s 两侧的空格。
【实例】SELECT CONCAT(’[’,TRIM(‘mobile’),’]’);输入的 SQL 语句和执行结果如下所示。
mysql> SELECT '[ mobile ]',CONCAT('[',TRIM(' mobile '),']');
+----------------+--------------------------------------+
| [ mobile ] | CONCAT('[',TRIM(' mobile '),']') |
+----------------+--------------------------------------+
| [ mobile ] | [mobile] |
+----------------+--------------------------------------+
1 row in set (0.07 sec)
由执行结果可知,函数执行之后字符串“mobile”两边的空格被删除,结果为“mobile”。
REPLACE
REPLACE(s,s1,s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
【实例】使用 REPLACE 函数进行字符串替换操作,输入的 SQL 语句和执行过程如下所示。
mysql> SELECT REPLACE('aaa.mysql.com','a','w');
+----------------------------------+
| REPLACE('aaa.mysql.com','a','w') |
+----------------------------------+
| www.mysql.com |
+----------------------------------+
1 row in set (0.00 sec)
由运行结果可以看出,使用 REPLACE(‘aaa.mysql.com’,‘a’,‘w’) 将“aaa.mysql.com”字符串的“a”字符替换为“w”字符,结果为“www.mysql.com”。
SUBSTRING
SUBSTRING(s,n,len) 带有 len 参数的格式,从字符串 s 返回一个长度同 len 字符相同的子字符串,起始于位置 n。也可能对 n 使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的第 n 个字符,即倒数第 n 个字符,而不是字符串的开头位置。
【实例】使用 SUBSTRING 函数获取指定位置处的子字符串,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT SUBSTRING('computer',3) AS col1,
-> SUBSTRING('computer',3,4) AS col2,
-> SUBSTRING('computer',-3) AS col3,
-> SUBSTRING('computer',-5,3) AS col4;
+--------+------+------+------+
| col1 | col2 | col3 | col4 |
+--------+------+------+------+
| mputer | mput | ter | put |
+--------+------+------+------+
1 row in set (0.00 sec)
SUBSTRING(‘computer’,3) 返回从第 3 个位置开始到字符串结尾的子字符串,结果为“mputer”;SUBSTRING(‘computer’,3,4) 返回从第 3 个位置开始长度为 4 的子字符串,结果为“mput”;
SUBSTRING(computer,-3) 返回从倒数第 3 个位置到字符串结尾的子字符串,结果为“ter”;SUBSTRING(computer,-5,3) 返回从倒数第 5 个位置开始长度为 3 的子字符串,结果为“put”。
REVERSE
REVERSE(s) 可以将字符串 s 反转,返回的字符串的顺序和 s 字符串的顺序相反。
【实例】使用 REVERSE 函数反转字符串,输入的 SQL 语句和执行过程如下所示。
mysql> SELECT REVERSE('hello');
+------------------+
| REVERSE('hello') |
+------------------+
| olleh |
+------------------+
1 row in set (0.00 sec)
由运行结果可以看出,字符串“hello”经过 REVERSE 函数处理之后所有字符顺序被反转,结果为“olleh”。
MySQL 日期和时间函数
函数名称 | 作 用 |
---|---|
CURDATE 和 CURRENT_DATE | 两个函数作用相同,返回当前系统的日期值 |
CURTIME 和 CURRENT_TIME | 两个函数作用相同,返回当前系统的时间值 |
NOW 和 SYSDATE | 两个函数作用相同,返回当前系统的日期和时间值 |
UNIX_TIMESTAMP | 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数 |
FROM_UNIXTIME | 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数 |
MONTH | 获取指定日期中的月份 |
MONTHNAME | 获取指定日期中的月份英文名称 |
DAYNAME | 获取指定曰期对应的星期几的英文名称 |
DAYOFWEEK | 获取指定日期对应的一周的索引位置值 |
WEEK | 获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53 |
DAYOFYEAR | 获取指定曰期是一年中的第几天,返回值范围是1~366 |
DAYOFMONTH | 获取指定日期是一个月中是第几天,返回值范围是1~31 |
YEAR | 获取年份,返回值范围是 1970〜2069 |
TIME_TO_SEC | 将时间参数转换为秒数 |
SEC_TO_TIME | 将秒数转换为时间,与TIME_TO_SEC 互为反函数 |
DATE_ADD 和 ADDDATE | 两个函数功能相同,都是向日期添加指定的时间间隔 |
DATE_SUB 和 SUBDATE | 两个函数功能相同,都是向日期减去指定的时间间隔 |
ADDTIME | 时间加法运算,在原始时间上添加指定的时间 |
SUBTIME | 时间减法运算,在原始时间上减去指定的时间 |
DATEDIFF | 获取两个日期之间间隔,返回参数 1 减去参数 2 的值 |
DATE_FORMAT | 格式化指定的日期,根据参数返回指定格式的值 |
WEEKDAY | 获取指定日期在一周内的对应的工作日索引 |
CURDATE 和 CURRENT_DATE
CURDATE() 和 CURRENT_DATE() 函数的作用相同,将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回,具体格式根据函数用在字符串或数字语境中而定。
【实例】使用日期函数 CURDATE 和 CURRENT_DATE 获取系统当前日期,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
+------------+----------------+------------------+
| CURDATE() | CURRENT_DATE() | CURRENT_DATE()+0 |
+------------+----------------+------------------+
| 2017-04-01 | 2017-04-01 | 20170401 |
+------------+----------------+------------------+
1 row in set (0.03 sec)
由运行结果可以看到,两个函数的作用相同,返回了相同的系统当前日期,“CURDATE()+0”将当前日期值转换为数值型的。
CURTIME 和 CURRENT_TIME
CURTIME() 和 CURRENT_TIME() 函数的作用相同,将当前时间以“HH:MM:SS”或“HHMMSS”格式返回,具体格式根据函数用在字符串或数字语境中而定。
【实例】使用时间函数 CURTIME 和 CURRENT_TIME 获取系统当前时间,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0;
+-----------+----------------+------------------+
| CURTIME() | CURRENT_TIME() | CURRENT_TIME()+0 |
+-----------+----------------+------------------+
| 19:39:51 | 19:39:51 | 193951 |
+-----------+----------------+------------------+
1 row in set (0.04 sec)
由运行结果可以看出,两个函数返回的结果相同,都返回了当前的系统时间。CURRENT_TIME()+0 是将当前日期值转换为数值型的。
NOW 和 SYSDATE
NOW() 和 SYSDATE() 函数的作用相同,都是返回当前日期和时间值,格式为“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”,具体格式根据函数用在字符串或数字语境中而定。
【实例 1】使用日期时间函数 NOW 和 SYSDATE 获取当前系统的日期和时间,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW() | SYSDATE() |
+---------------------+---------------------+
| 2017-04-01 19:36:52 | 2017-04-01 19:36:52 |
+---------------------+---------------------+
1 row in set (0.04 sec)
由运行结果可以看到,NOW 函数和 SYSDATE 函数返回的结果是相同的。
虽然在 MySQL 中 NOW() 和 SYSDATE() 都表示当前时间,但是 NOW() 取的是语句开始执行的时间,而 SYSDATE() 取的是语句执行过程中动态的实时时间。
【实例 2】先查询了 NOW() 和 SYSDATE(),然后 sleep 了 3 秒,再查询 NOW() 和 SYSDATE(),结果如下:
mysql> select now(),sysdate(),sleep(3),now(),sysdate();
+-----------------------+------------------------+-------------+-----------------------+---------------------+
| now() | sysdate() | sleep(3) | now() | sysdate() |
+-----------------------+------------------------+-------------+------------------- ---+---------------------+
| 2019-02-27 10:59:39 | 2019-02-27 10:59:39 | 0 | 2019-02-27 10:59:39 | 2019-02-27 10:59:42 |
+-----------------------+------------------------+-------------+-----------------------+---------------------+
1 row in set (3.00 sec)
由运行结果可以看出,NOW() 函数始终获取的是 SQL 语句开始执行的时间,而 SYSDATE() 函数则是动态获取的实时时间。
UNIX_TIMESTAMP
UNIX_TIMESTAMP(date) 若无参数调用,返回一个无符号整数类型的 UNIX 时间戳('1970-01-01 00:00:00’GMT之后的秒数)。
若用 date 来调用 UNIX_TIMESTAMP(),它会将参数值以’1970-01-01 00:00:00’GMT后的秒数的形式返回。
【实例】使用 UNIX_TIMESTAMP() 函数返回 UNIX 格式的时间戳,输入 SQL 语句和执行结果如下。
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW() |
+------------------+-----------------------+---------------------+
| 1551251270 | 1551251270 | 2019-02-27 15:07:50 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME
FROM_UNIXTIME(date) 函数把 UNIX 时间戳转换为普通格式的日期时间值,与 UNIX_TIMESTAMP () 函数互为反函数。
【实例】使用 FROM_UNIXTIME(date) 函数将 UNIX 时间戳转换为普通格式时间。
mysql> SELECT FROM_UNIXTIME(1150051270);
+---------------------------+
| FROM_UNIXTIME(1150051270) |
+---------------------------+
| 2006-06-12 02:41:10 |
+---------------------------+
1 row in set (0.00 sec)
MONTH
MONTH(date) 函数返回指定 date 对应的月份,范围为 1~12。
mysql> SELECT MONTH('2017-12-15');
+---------------------+
| MONTH('2017-12-15') |
+---------------------+
| 12 |
+---------------------+
1 row in set (0.00 sec)
由运行结果可知,2017-12-15 对应的月份是 12。
MONTHNAME
MONTHNAME(date) 函数返回日期 date 对应月份的英文全名。
【实例】使用 MONTHNAME() 函数返回指定日期中月份的名称,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT MONTHNAME('2017-12-15');
+-------------------------+
| MONTHNAME('2017-12-15') |
+-------------------------+
| December |
+-------------------------+
1 row in set (0.00 sec)
由运行结果可知,2017-12-15 对应月份的英文全名是 December。
DAYNAME
DAYNAME(date) 函数返回 date 对应的工作日英文名称,例如 Sunday、Monday 等。
【实例】使用 DAYNAME(date) 函数返回指定日期的工作日名称。
mysql> SELECT DAYNAME('2006-06-12');
+-----------------------+
| DAYNAME('2006-06-12') |
+-----------------------+
| Monday |
+-----------------------+
1 row in set (0.00 sec)
由运行结果可知,2006-06-12 这一天是星期一,英文名称是 Monday。
DAYOFWEEK
DAYOFWEEK(d) 函数返回 d 对应的一周中的索引(位置)。1 表示周日,2 表示周一,……,7 表示周六。这些索引值对应于ODBC标准。
【实例】使用 DAYOFWEEK() 函数返回日期对应的周索引,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT DAYOFWEEK('2017-12-15');
+-------------------------+
| DAYOFWEEK('2017-12-15') |
+-------------------------+
| 6 |
+-------------------------+
1 row in set (0.04 sec)
WEEK
WEEK() 函数计算日期 date 是一年中的第几周。WEEK(date,mode) 函数允许指定星期是否起始于周日或周一,以及返回值的范围是否为 0~52 或 1~53。
WEEK函数接受两个参数:
- date是要获取周数的日期。
- mode是一个可选参数,用于确定周数计算的逻辑。
如果忽略 mode 参数,默认情况下 WEEK 函数将使用 default_week_format 系统变量的值。要获取 default_week_format 变量的当前值,请使用 SHOW VARIABLES 语句如下:
mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| default_week_format | 0 |
+---------------------+-------+
1 row in set
【实例】使用 WEEK(date) 函数查询指定日期是一年中的第几周。
mysql> SELECT WEEK('2018-10-25',1);
+----------------------+
| WEEK('2018-10-25',1) |
+----------------------+
| 43 |
+----------------------+
1 row in set (0.00 sec)
由运行结果可知,2018-10-25 是一年中的第 43 周。
DAYOFYEAR
DAYOFYEAR(d) 函数返回 d 是一年中的第几天,范围为 1~366。
【实例】使用 DAYOFYEAR() 函数返回指定日期在一年中的位置,输入的 SQL 语句和执行过程如下所示。
mysql> SELECT DAYOFYEAR('2017-12-15');
+-------------------------+
| DAYOFYEAR('2017-12-15') |
+-------------------------+
| 349 |
+-------------------------+
1 row in set (0.00 sec)
由运行结果可以看出,1、3、5、7、8、10月份各31天,2月份各28天,4、6、9、11月份各30天,再加上12月份15天,因此返回结果为349。
DAYOFMONTH
DAYOFMONTH(d) 函数返回 d 是一个月中的第几天,范围为 1~31。
【实例】使用 DAYOFMONTH() 函数返回指定日期在一个月中的位置,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT DAYOFMONTH('2017-12-15');
+--------------------------+
| DAYOFMONTH('2017-12-15') |
+--------------------------+
| 15 |
+--------------------------+
1 row in set (0.02 sec)
由运行结果可以看出,2017-12-15 是这个月的第 15 天。
YEAR
YEAR() 函数可以从指定日期值中来获取年份值。
YEAR() 函数需要接受 date 参数,并返回日期的年份。语法格式如下:
YEAR(date);
YEAR() 函数返回的年份值范围为 1000 到 9999,如果日期为零,YEAR() 函数返回 0。
【实例】使用 YEAR() 函数获取当前时间的年份,输入 SQL 语句和执行结果如下。
mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
| 2019 |
+-------------+
1 row in set (0.00 sec)
TIME_TO_SEC
TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值,转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。
【实例】使用 TIME_TO_SEC(time) 函数将时间值转换为秒值。
mysql> SELECT TIME_TO_SEC('15:15:15');
+-------------------------+
| TIME_TO_SEC('15:15:15') |
+-------------------------+
| 54915 |
+-------------------------+
1 row in set (0.00 sec)
由执行结果可以看出,根据计算公式“15×3600+15×60+25”得出结果秒数 54915。
SEC_TO_TIME
SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。
【实例】使用 SEC_TO_TIME(seconds) 函数将秒值转换为时间格式,输入 SQL 语句和执行结果如下。
mysql> SELECT SEC_TO_TIME('54925');
+----------------------+
| SEC_TO_TIME('54925') |
+----------------------+
| 15:15:25 |
+----------------------+
1 row in set (0.00 sec)
由执行结果可以看出,将上例中得到的秒数 54925 通过 SEC_TO_TIME(seconds) 函数计算,返回结果是时间值 15:15:25,为字符串型。
DATE_ADD和ADDDATE
DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同,都是用于执行日期的加运算。
DATE_ADD() 和 ADDDATE() 函数有两个参数:
- date 是 DATE 或 DATETIME 的起始值。
- INTERVAL expr type是要添加到起始日期值的间隔值。
【实例 1】使用 DATE_ADD(date,INTERVAL expr type) 函数执行日期的加运算。输入 SQL 语句和执行结果如下。
mysql> SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,
-> DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
-> ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
+---------------------+---------------------+---------------------+
| C1 | C2 | C3 |
+---------------------+---------------------+---------------------+
| 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
由执行结果可以看出,DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 函数的功能完全相同,
在原始时间 ‘2018-10-31 23:59:59’ 上加 1 秒之后结果都是 ‘2018-11-01 00:00:00’ ;
在原始时间上加 1 分钟 1 秒的写法是表达式 ‘1:1’,最终可得结果 ‘2018-11-01 00:01:00’。
【实例 2】使用 ADDDATE() 函数执行日期的加操作,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT ADDDATE('2017-11-30 23:59:59', INTERVAL 1 SECOND) AS col1,
-> ADDDATE('2017-11-30 23:59:59' ,INTERVAL '1:1' MINUTE_SECOND) AS col2;
+---------------------+---------------------+
| col1 | col2 |
+---------------------+---------------------+
| 2017-12-01 00:00:00 | 2017-12-01 00:01:00 |
+---------------------+---------------------+
1 row in set (0.02 sec)
由运行结果可以看到,
ADDDATE(‘2017-11-30 23:59:59’,INTERVAL 1 SECOND) 函数执行的结果将时间增加 1 秒后返回,结果为“2017-12-01 00:00:00”;
ADDDATE(‘2017-11-30 23:59:59’,INTERVAL’1:1’MINUTE_SECOND) 函数的日期运算类型是 MINUTE_SECOND,将指定时间增加 1 分 1 秒后返回,结果为“2017-12-01 00:01:00”。
DATE_SUB和SUBDATE
DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 两个函数作用相同,都是执行日期的减法运算。
DATE_SUB() 和 SUBDATE() 函数接受两个参数:
- date 是 DATE 或 DATETIME 的起始值。
- expr 是一个字符串,用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位,例如 DAY,HOUR 等
【实例】使用 DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 函数执行日期的减运算,输入 SQL 语句与执行结果如下。
mysql> SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS C1,
-> SUBDATE('2018-01-02',INTERVAL 31 DAY) AS C2,
-> DATE_SUB('2018-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3;
+------------+------------+---------------------+
| C1 | C2 | C3 |
+------------+------------+---------------------+
| 2017-12-02 | 2017-12-02 | 2017-12-31 23:59:59 |
+------------+------------+---------------------+
1 row in set (0.00 sec)
由执行结果可以看出,DATE_SUB(date,INTERVAL expr type) 和SUBDATE (date,INTERVAL expr type) 函数的功能完全相同。
提示:DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 函数在指定加减的时间段时也可以指定负值,加法的负值即返回原始时间之前的日期和时间,减法的负值即返回原始时间之后的日期和时间。
ADDTIME
ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。
其中:time 是一个时间或日期时间表达式,expr 是一个时间表达式。
【实例】使用 ADDTIME() 函数进行时间的加法运算,输入 SQL 语句和返回结果如下。
mysql> SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'),
-> ADDTIME('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| ADDTIME('2018-10-31 23:59:59','0:1:1') | ADDTIME('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2018-11-01 00:01:00 | 15:41:36 |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)
由执行结果可以看出,在原始日期时间 ‘2018-10-31 23:59:59’ 上加 0 小时 1 分 1 秒之后返回的日期时间是 ‘2018-11-01 00:01:00’;在原始时间 ‘10:30:59’ 上加 5 小时 10 分 37 秒之后返回的日期时间是 ‘15:41:36’。
SUBTIME
SUBTIME(time,expr) 函数用于执行时间的减法运算。
其中:函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式, expr 是一个时间。
【实例】使用 SUBTIME(time,expr) 函数进行时间的减法运算,输入 SQL 语句和执行结果如下。
mysql> SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'),SUBTIME('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| SUBTIME('2018-10-31 23:59:59','0:1:1') | SUBTIME('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2018-10-31 23:58:58 | 05:18:22 |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)
由执行结果可以看出,在原始日期时间 ‘2018-10-31 23:59:59’ 上减去 0 小时 1 分 1 秒之后返回的日期时间是 ‘2018-10-31 23:58:58’;在原始时间 ‘10:30:59’ 上减去 5 小时 12 分 37 秒之后返回的日期时间是 ‘05:18:22’。
DATEDIFF
DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。
【实例】使用 DATEDIFF() 函数计算两个日期之间的间隔天数,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
-> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
| 1 | -15 |
+------+------+
1 row in set (0.00 sec)
由运行结果可知,DATEDIFF() 函数返回 date1-date2 后的值,因此 DATEDIFF(‘2017-11-30’,‘2017-11-29’) 的返回值为 1,DATEDIFF(‘2017-11-30 23:59:59’,‘2017-12-15’) 的返回值为 -15。
DATE_FORMAT
DATE_FORMAT(date,format) 函数是根据 format 指定的格式显示 date 值。
DATE_FORMAT() 函数接受两个参数:
- date:是要格式化的有效日期值
- format:是由预定义的说明符组成的格式字符串,每个说明符前面都有一个百分比字符(%)。
主要的 format 格式如下表所示。
说明符 | 说明 |
---|---|
%a | 工作日的缩写名称(Sun~Sat) |
%b | 月份的缩写名称(Jan…Dec) |
%c | 月份,数字形式(0~12) |
%D | 带有英语后缀的该月日期(0th, 2st, 3nd,…) |
%d | 该月日期,数字形式(00~31) |
%e | 该月日期,数字形式((0~31) |
%f | 微秒(000000 …999999) |
%H | 以 2 位数表示 24 小时(00~23) |
%h, %I | 以 2 位数表示 12 小时(01~12) |
%i | 分钟,数字形式(00~59) |
%j | —年中的天数(001~366) |
%k | 以 24 小时(0~23)表示 |
%l | 以12小时(1~12)表示 |
%M | 月份名称(January~December) |
%m | 月份,数字形式(00~12) |
%p | 上午(AM) 或下午(PM) |
%r | 时间,12小时制(小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM) |
%S, %s | 以 2 位数形式表示秒(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 位数形式表示年份 |
%% | %一个文字字符 |
【实例】使用 DATE_FORMAT() 函数格式化输出日期和时间值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,
-> DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
+------------------------------+----------------------------+
| col1 | col2 |
+------------------------------+----------------------------+
| Wednesday November 15th 2017 | 09:i PM November 15th 2017 |
+------------------------------+----------------------------+
1 row in set (0.03 sec)
由运行结果可以看到,“2017-11-15 21:45:00”分别按照不同的参数已转换为不同格式的日期值和时间值。
WEEKDAY
WEEKDAY(d) 返回 d 对应的工作日索引。0 表示周一,1 表示周二,……,6 表示周日。
【实例】使用 WEEKDAY() 函数返回日期对应的工作日索引,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT WEEKDAY('2017-12-15');
+-----------------------+
| WEEKDAY('2017-12-15') |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
由运行结果可以看出,WEEKDAY() 和 DAYOFWEEK() 函数都是返回指定日期在某一周内的位置,只是索引编号不同。
MySQL 聚合函数
函数名称 | 作用 |
---|---|
MAX | 查询指定列的最大值 |
MIN | 查询指定列的最小值 |
COUNT | 统计查询结果的行数 |
SUM | 求和,返回指定列的总和 |
AVG | 求平均值,返回指定列数据的平均值 |
MAX
MAX() 函数是用来返回指定列中的最大值。
为了方便理解,首先创建一个学生成绩表 tb_students_score,学生成绩表的数据内容如下所示。
mysql> use test_db;
Database changed
mysql> SELECT * FROM tb_students_score;
+--------------+---------------+
| student_name | student_score |
+--------------+---------------+
| Dany | 90 |
| Green | 99 |
| Henry | 95 |
| Jane | 98 |
| Jim | 88 |
| John | 94 |
| Lily | 100 |
| Susan | 96 |
| Thomas | 93 |
| Tom | 89 |
+--------------+---------------+
10 rows in set (0.13 sec)
【实例 1】在 tb_students_score 表中查找最高的成绩,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT MAX(student_score)
-> AS max_score
-> FROM tb_students_score;
+-----------+
| max_score |
+-----------+
| 100 |
+-----------+
1 row in set (0.06 sec)
由运行结果可以看到,MAX() 函数查询出 student_score 字段的最大值为 100。
MAX() 函数不仅适用于查找数值类型,也可应用于字符类型。
【实例 2】在 tb_students_score 表中查找 student_name 的最大值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT MAX(student_name)
-> AS max_name
-> FROM tb_students_score;
+----------+
| max_name |
+----------+
| Tom |
+----------+
1 row in set (0.03 sec)
由运行结果可以看到,MAX() 函数可以对字母进行大小判断,并返回最大的字符或者字符串值。
注意:MAX() 函数还可以返回任意列中的最大值,包括返回字符类型的最大值。在对字符类型的数据进行比较时,按照字符的 ASCII 码值大小进行比较,从 a~z,a 的 ASCII 码最小,z 的最大。在比较时,先比较第一个字符,如果相等,继续比较下一个字符,一直到两个字符不相等或者字符结束为止。例如,b 与 t 比较时,t 为最大值;bcd 与 bca 比较时,bcd 为最大值。
MIN
MIN() 函数是用来返回查询列中的最小值。
为了便于理解,需要用到在上一节讲 MAX()
函数时创建的数据表 tb_students_score。
【实例】在 tb_students_score 表中查找最低的成绩,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT MIN(student_score)
-> AS min_score
-> FROM tb_students_score;
+-----------+
| min_score |
+-----------+
| 88 |
+-----------+
1 row in set (0.00 sec)
由结果可以看到,MIN() 函数查询出 student_score 字段的最小值为 88。
提示:MIN() 函数与 MAX() 函数类似,不仅适用于查找数值类型,也可应用于字符类型。
COUNT
COUNT() 函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数,使用方法有以下两种:
- COUNT(*) 计算表中总的行数,无论某列有数值或者为空值。
- COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行。
这里需要用到以下在介绍 MAX()
函数时创建的表 tb_students_score 。
【实例】查询 tb_students_score 表中总的行数,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT COUNT(student_name)
-> AS students_number
-> FROM tb_students_score;
+-----------------+
| students_number |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.03 sec)
由查询结果可以看到,COUNT(*) 返回 tb_students_score 表中记录的总行数,无论值是什么。返回的总数的名称为 students_number。
提示:在计算总数的时候对待 NULL 值的方式是,指定列的值为空的行被 COUNT() 函数忽略,但若不指定列,而在 COUNT() 函数中使用星号“*”,则所有记录都不忽略
SUM
SUM() 是一个求总和的函数,返回指定列值的总和。
SUM() 函数是如何工作的?
- 如果在没有返回匹配行 SELECT 语句中使用 SUM 函数,则 SUM 函数返回 NULL,而不是 0。
- DISTINCT 运算符允许计算集合中的不同值。
- SUM 函数忽略计算中的 NULL 值。
这里需要用到以下在介绍 MAX()
函数时创建的表 tb_students_score 。
【实例】在 tb_students_score 表中计算学生成绩的总分,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT SUM(student_score)
-> AS score_sum
-> FROM tb_students_score;
+-----------+
| score_sum |
+-----------+
| 942 |
+-----------+
1 row in set (0.00 sec)
由查询结果可以看到,SUM() 函数返回学生的所有成绩之和为 942。
提示:SUM() 函数在计算时,忽略列值为 NULL 的行。
AVG
AVG() 函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值。
这里需要用到以下在介绍 MAX()
函数时创建的表 tb_students_score 。
【实例】在 tb_students_score 表中,查询所有学生成绩的平均值,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT AVG(student_score)
-> AS score_avg
-> FROM tb_students_score;
+-----------+
| score_avg |
+-----------+
| 94.2000 |
+-----------+
1 row in set (0.03 sec)
提示:使用 AVG() 函数时,参数为要计算的列名称,若要得到多个列的平均值,则需要在每一列都使用 AVG() 函数。
MySQL 流程控制函数
函数名称 | 作用 |
---|---|
IF | 判断,流程控制 |
IFNULL | 判断是否为空 |
CASE | 搜索语句 |
IF
IF 语句允许您根据表达式的某个条件或值结果来执行一组 SQL 语句。
要在 MySQL 中形成一个表达式,可以结合文字,变量,运算符,甚至函数来组合。表达式可以返回 TRUE,FALSE 或 NULL,这三个值之一。
注意:
- 如果在增删改查 语句中结尾不要使用分号
;
不然相当于结束了 - AND OR … 这查询筛选关键字不能在语句中使用 ,只能使用= != > < …这些
语法结构如下:
IF(expr,v1,v2)
其中:表达式 expr 得到不同的结果,当 expr 为真是返回 v1 的值,否则返回 v2. (类似java三元表达式)
【实例】使用 IF(expr,v1,v2) 函数根据 expr 表达式结果返回相应值,输入 SQL 语句和执行结果如下。
mysql> SELECT IF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3 |
+----+----+-----+
| 1 | × | yes |
+----+----+-----+
1 row in set, 2 warnings (0.00 sec)
由执行结果可以看出,
在 c1 中,表达式 1<2 所得的结果是 TRUE,则返回结果为 v1,即数值 1;
在 c2 中,表达式 1>5 所得的结果是 FALSE,则返回结果为 v2,即字符串 ‘×’;
在 c3 中,先用 STRCMP(s1,s2) 函数比较两个字符串的大小,字符串 ‘abc’ 和 ‘ab’ 比较结果的返回值为 1,也就是表达式 expr 的返回结果不等于 0 且不等于 NULL,则返回值为 v1,即字符串 ‘yes’。
高级用法:
SELECT * FROM WHERE id='1' and IF(age>18,sex='男',sex='女')
IFNULL
IFNULL 函数是 MySQL 控制流函数之一,它接受两个参数,如果不是 NULL,则返回第一个参数。 否则,IFNULL 函数返回第二个参数。两个参数可以是文字值或表达式。
函数的语法:
IFNULL(v1,v2);
其中:如果 v1 不为 NULL,则 IFNULL 函数返回 v1; 否则返回 v2 的结果。
【实例】使用 IFNULL(v1,v2) 函数根据 v1 的取值返回相应值。输入 SQL 语句和执行结果如下。
mysql> SELECT IFNULL(5,8),IFNULL(NULL,'OK'),IFNULL(SQRT(-8),'FALSE'),SQRT(-8);
+-------------+-------------------+--------------------------+----------+
| IFNULL(5,8) | IFNULL(NULL,'OK') | IFNULL(SQRT(-8),'FALSE') | SQRT(-8) |
+-------------+-------------------+--------------------------+----------+
| 5 | OK | FALSE | NULL |
+-------------+-------------------+--------------------------+----------+
1 row in set (0.00 sec)
由执行结果可以看出,
IFNULL(v1,v2) 函数中的参数 v1=5、v2=8,都不为空,即 v1=5 不为空,返回 v1 的值为 5;
当 v1=NULL 时,返回 v2 的值,即字符串 ‘OK’;当 v1=SQRT(-8) 时,SQRT(-8) 函数的返回值为NULL,即 v1=NULL,所以返回 v2 为字符串 ‘false’。
CASE
除了 IF 函数,MySQL 还提供了一个替代的条件语句 CASE。 MySQL CASE 语句使代码更加可读和高效。
注意:
- 如果在增删改查 语句中结尾不要使用分号
;
不然相当于结束了 - AND OR … 这查询筛选关键字不能在语句中使用 ,只能使用= != > < …这些
CASE 语句有两种形式:简单的和可搜索 CASE 语句。
简单的 CASE 语句
简单的 CASE 语句就是指使用简单 CASE 语句来检查表达式的值与一组唯一值的匹配。
简单的 CASE 语句的语法:
CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END CASE;
其中:<表达式> 可以是任何有效的表达式。我们将 <表达式> 的值与每个 WHEN 子句中的值进行比较,
例如 <值1>,<值2> 等。如果 <表达式> 和 <值n> 的值相等,则执行相应的 WHEN 分支中的命令 <操作>。如果 WHEN 子句中的 <值n> 没有与 <表达式> 的值匹配,则 ELSE 子句中的命令将被执行。ELSE 子句是可选的。 **如果省略 ELSE 子句,并且找不到匹配项,MySQL 将引发错误。**如果您不想处理 ELSE 子句中的逻辑,同时又要防止 MySQL 引发错误,则可以在 ELSE 子句中放置一个空的 BEGIN END 块
。
【实例 1】使用 CASE 函数根据表达式的取值返回相应值,输入SQL 语句和执行结果如下
mysql>
SELECT CASE WEEKDAY(NOW())
WHEN 0 THEN '星期一'
WHEN 1 THEN '星期二'
WHEN 2 THEN '星期三'
WHEN 3 THEN '星期四'
WHEN 4 THEN '星期五'
WHEN 5 THEN '星期六'
ELSE '星期天' END AS COLUMN1,
NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW() | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
| 星期四 | 2019-02-28 13:45:43 | 3 | Thursday |
+---------+---------------------+----------------+----------------+
1 row in set, 7 warnings (0.00 sec)
由执行结果可以看出,NOW() 函数得到当前系统时间是 2019 年 2 月 28 日,DAYNAME(NOW()) 得到当天是 'Thursday ',WEEKDAY(NOW()) 函数返回当前时间的工作日索引是 3,即对应的是星期四。
可搜索的 CASE 语句
简单 CASE 语句仅允许将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配,如范围,则可以使用可搜索 CASE 语句。 可搜索 CASE 语句等同于 IF 语句,但是它的构造更加可读。
可搜索CASE语句的语法:
CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;
MySQL 分别计算 WHEN 子句中的每个条件,直到找到一个值为 TRUE 的条件,然后执行 THEN 子句中的相应 <命令>。如果没有一个条件为 TRUE,则执行 ELSE 子句中的 <命令>。如果不指定 ELSE 子句,并且没有一个条件为 TRUE,MySQL 将发出错误消息。MySQL 不允许在 THEN 或 ELSE 子句中使用空的命令。 如果您不想处理 ELSE 子句中的逻辑,同时又要防止 MySQL 引发错误,则可以在 ELSE 子句中放置一个空的 BEGIN END 块
。
【实例 2】使用 CASE 函数根据表达式的取值返回相应值,输入SQL 语句和执行结果如下
mysql>
SELECT CASE
WHEN WEEKDAY(NOW())=0 THEN '星期一'
WHEN WEEKDAY(NOW())=1 THEN '星期二'
WHEN WEEKDAY(NOW())=2 THEN '星期三'
WHEN WEEKDAY(NOW())=3 THEN '星期四'
WHEN WEEKDAY(NOW())=4 THEN '星期五'
WHEN WEEKDAY(NOW())=5 THEN '星期六'
WHEN WEEKDAY(NOW())=6 THEN '星期天'
END AS COLUMN1,
NOW(),WEEKDAY(NOW()),DAYNAME(NOW(
));
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW() | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
| 星期四 | 2019-02-28 14:08:00 | 3 | Thursday |
+---------+---------------------+----------------+----------------+
1 row in set, 7 warnings (0.00 sec)
此例跟上例的返回结果一样,只是使用了 CASE 函数的不同写法,WHEN 后面为表达式,当表达式的返回结果为 TRUE 时取 THEN 后面的值,如果都不是,则返回 ELSE 后面的值。