MySQL函数

数据库函数

常用的函数分类

• 数学函数
• 聚合函数
• 字符串函数
• 日期时间函数

常用的数学函数

abs(x)：返回x的绝对值
rand()：返回0到1的随机数
mod(x,y)：返回x除以y以后的余数
power(x,y)：返回x的y次方
round(x)：返回离x最近的整数
round(x,y)：保留x的y位小数四舍五入后的值
sqrt(x)：返回x的平方根
truncate(x,y)：返回数字x截断为y位小数的值
ceil(x)：返回大于或等于x的最小整数
floor(x)：返回小于或等于x的最大整数
greatest(x1,x2…)：返回集合中最大的值
least(x1,x2…)：返回集合中最小的值

mysql> select abs(1),abs(-1);
+--------+---------+
| abs(1) | abs(-1) |
+--------+---------+
|      1 |       1 |
+--------+---------+
1 row in set (0.00 sec)

mysql> select rand();
+----------------------+
| rand()               |
+----------------------+
| 0.033044188314158646 |
+----------------------+
1 row in set (0.00 sec)

mysql> select mod(3,2);
+----------+
| mod(3,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select power(2,4);
+------------+
| power(2,4) |
+------------+
|         16 |
+------------+
1 row in set (0.01 sec)

mysql> select round(1.7),round(3.3);
+------------+------------+
| round(1.7) | round(3.3) |
+------------+------------+
|          2 |          3 |
+------------+------------+
1 row in set (0.01 sec)

mysql> select round(1.162,2),round(1.268,2);
+----------------+----------------+
| round(1.162,2) | round(1.268,2) |
+----------------+----------------+
|           1.16 |           1.27 |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> select sqrt(2),sqrt(5);
+--------------------+------------------+
| sqrt(2)            | sqrt(5)          |
+--------------------+------------------+
| 1.4142135623730951 | 2.23606797749979 |
+--------------------+------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.1111,3),truncate(1.3367,3);
+--------------------+--------------------+
| truncate(1.1111,3) | truncate(1.3367,3) |
+--------------------+--------------------+
|              1.111 |              1.336 |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select ceil(1.1),ceil(1.8);
+-----------+-----------+
| ceil(1.1) | ceil(1.8) |
+-----------+-----------+
|         2 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select floor(1.4),floor(1.9);
+------------+------------+
| floor(1.4) | floor(1.9) |
+------------+------------+
|          1 |          1 |
+------------+------------+
1 row in set (0.00 sec)

mysql> select greatest(1,3,5);
+-----------------+
| greatest(1,3,5) |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> select least(1,3,5);
+--------------+
| least(1,3,5) |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

聚合函数

对表中数据记录进行集中概括而设计的一类函数
常用的聚合函数（只会产生一个值）

mysql> select * from cj;
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | aa   |    11 |
|    2 | bb   |    11 |
|    3 | cc   |    22 |
|    4 | dd   |    33 |
|    5 | ee   |    44 |
+------+------+-------+
5 rows in set (0.00 sec)

mysql> select avg(score) from cj;  #avg(字段名) 返回指定字段的平均值
+------------+
| avg(score) |
+------------+
|    24.2000 |
+------------+
1 row in set (0.00 sec)

mysql> select count(score) from cj;  #count(字段名) 返回指定字段中非NULL值的个数
+--------------+
| count(score) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

mysql> select max(score) from cj;  #max(字段名) 返回指定字段的最大值
+------------+
| max(score) |
+------------+
|         44 |
+------------+
1 row in set (0.00 sec)

mysql> select min(score) from cj;  #min(字段名) 返回指定字段的最小值
+------------+
| min(score) |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from cj;  #sum(字段名) 返回指定字段的所有值之和
+------------+
| sum(score) |
+------------+
|        121 |
+------------+
1 row in set (0.00 sec)

字符串函数

常用的字符串函数

length(x)     返回字符串x的长度
trim()        返回去除指定格式的值
concat(x,y)   将提供的参数x和y拼接成一个字符串
upper(x)      将字符串x的所有字母变成大写字母
lower(x)      将字符串x的所有字母变成小写字母
left(x,y)）   返回字符串x的前y个字符
right(x,y)    返回字符串x的后y个字符
repeat(x,y)   将字符串x重复y次
space(x)      返回x个空格
replace(x,y,z)将字符串z替代字符串x中的字符串y
strcmp(x,y)   比较x和y，返回的值可以为-1,0,1
substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串
reverse(x)    将字符串x反转

mysql> select length('1 2 3');
+-----------------+
| length('1 2 3') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> select trim(' 1 2 3 ');
+-----------------+
| trim(' 1 2 3 ') |
+-----------------+
| 1 2 3           |
+-----------------+
1 row in set (0.01 sec)

mysql> select concat('12','ab');
+-------------------+
| concat('12','ab') |
+-------------------+
| 12ab              |
+-------------------+
1 row in set (0.00 sec)

mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.01 sec)

mysql> select left('123456',3);
+------------------+
| left('123456',3) |
+------------------+
| 123              |
+------------------+
1 row in set (0.00 sec)

mysql> select right('123456',3);
+-------------------+
| right('123456',3) |
+-------------------+
| 456               |
+-------------------+
1 row in set (0.00 sec)

mysql> select repeat('123',2);
+-----------------+
| repeat('123',2) |
+-----------------+
| 123123          |
+-----------------+
1 row in set (0.01 sec)

mysql> select concat('abc',space(2),'123');
+------------------------------+
| concat('abc',space(2),'123') |
+------------------------------+
| abc  123                     |
+------------------------------+
1 row in set (0.00 sec)

mysql> select replace('123456','1','8');
+---------------------------+
| replace('123456','1','8') |
+---------------------------+
| 823456                    |
+---------------------------+
1 row in set (0.00 sec)

mysql> select strcmp(1,1),strcmp(1,2),strcmp(2,1);
+-------------+-------------+-------------+
| strcmp(1,1) | strcmp(1,2) | strcmp(2,1) |
+-------------+-------------+-------------+
|           0 |          -1 |           1 |
+-------------+-------------+-------------+
1 row in set (0.01 sec)

mysql> select substring('12345678',3,3);
+---------------------------+
| substring('12345678',3,3) |
+---------------------------+
| 345                       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select reverse('123');
+----------------+
| reverse('123') |
+----------------+
| 321            |
+----------------+
1 row in set (0.00 sec)

日期时间函数

常用的日期时间函数

curdate()      返回当前时间的年月日
curtime()      返回当前时间的时分秒
now()          返回当前时间的日期和时间
month(x)       返回日期x中的月份值
week(x)        返回日期x是年度第几个星期
hour(x)        返回x中的小时值
minute(x)      返回x中的分钟值
second(x）     返回x中的秒钟值
dayofweek(x)   返回x是星期几，1星期日，2星期一
dayofmonth(x)  计算日期x是本月的第几天
dayofyear(x)   计算日期x是本年的第几天

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-12-31 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:39:47  |
+-----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-31 14:39:56 |
+---------------------+
1 row in set (0.00 sec)

mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          14 |
+-------------+
1 row in set (0.01 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            41 |
+---------------+
1 row in set (0.00 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)

mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
|                    31 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  366 |
+----------------------+
1 row in set (0.00 sec)