MySQL——数据库函数(数学函数、聚合函数、字符串函数、日期时间函数)

MySQL——数据库函数

MySQL提供了实现各种功能的函数

一. 常用的函数分类

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

二. 常用的数学函数

1 abs(x) 返回x的绝对值

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

2 rand() 返回0到1的随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6640812686309975 |
+--------------------+
1 row in set (0.01 sec)

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

mysql> 

3 mod(x,y) 返回x除以y以后的余数

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

4 power(x,y) 返回x的y次方

mysql> select power(12,3);
+-------------+
| power(12,3) |
+-------------+
|        1728 |
+-------------+
1 row in set (0.00 sec)

5 round(x) 返回离x最近的整数

mysql> select round(1.6);
+------------+
| round(1.6) |
+------------+
|          2 |
+------------+

6 round(x,y) 保留x的y位小数四舍五入后的值

mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
|           1.5 |
+---------------+

7 sqrt(x) 返回x的平方根

mysql> select sqrt(8);
+--------------------+
| sqrt(8)            |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.00 sec)

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

8 truncate(x,y) 返回数字x截断为y位小数的值

mysql> select truncate(3.12345,3);
+---------------------+
| truncate(3.12345,3) |
+---------------------+
|               3.123 |
+---------------------+
1 row in set (0.00 sec)

9 ceil(x) 返回大于或等于x的最小整数

mysql> select ceil(1.09);  // 向下取整
+------------+
| ceil(1.09) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(1.4);
+-----------+
| ceil(1.4) |
+-----------+
|         2 |
+-----------+

10. floor(x) 返回小于或等于x的最大整数

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

mysql> select floor(1.99);  //向上取整
+-------------+
| floor(1.99) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

11. greatest(x1,x2…) 返回集合中最大的值

mysql> select greatest(10,30),least(10,20);
+-----------------+--------------+
| greatest(10,30) | least(10,20) |
+-----------------+--------------+
|              30 |           10 |
+-----------------+--------------+
1 row in set (0.01 sec)

12. least(x1,x2…) 返回集合中最小的值

mysql> select greatest(10,30),least(10,20);
+-----------------+--------------+
| greatest(10,30) | least(10,20) |
+-----------------+--------------+
|              30 |           10 |
+-----------------+--------------+
1 row in set (0.01 sec)

四. 聚合函数

对表中数据记录进行集中概括而设计的一类函数

常用的聚合函数

1. avg() 返回指定列的平均值

mysql> select id,avg(score) as '平均值' from info;
+----+-----------+
| id | 平均值    |
+----+-----------+
|  1 | 77.222222 |
+----+-----------+
1 row in set (0.00 sec)

mysql> 

2. count() 返回指定列中非NULL值的个数

mysql> select id,count(score) as '个数' from info;
+----+--------+
| id | 个数   |
+----+--------+
|  1 |      9 |
+----+--------+
1 row in set (0.00 sec)

3. min() 返回指定列的最小值

mysql> select name,min(score) as '最小值' from info;
+--------+-----------+
| name   | 最小值    |
+--------+-----------+
| 张三   |     56.00 |
+--------+-----------+
1 row in set (0.01 sec)

4. max() 返回指定列的最大值

mysql> select name,max(score) as '最大值' from info;
+--------+-----------+
| name   | 最大值    |
+--------+-----------+
| 张三   |     98.00 |
+--------+-----------+
1 row in set (0.00 sec)

5. sum() 返回指定列的所有值之和

mysql> select name,sum(score) as '总计' from info;
+--------+--------+
| name   | 总计   |
+--------+--------+
| 张三   | 695.00 |
+--------+--------+
1 row in set (0.00 sec)

常用的字符串函数

1. length(x) 返回字符串x的长度

mysql> select length('abjcoi');
+------------------+
| length('abjcoi') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> select length('a bjc o i');
+---------------------+
| length('a bjc o i') |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.01 sec)

mysql> 

2. trim() 返回去除指定格式的值(只能去除前后字符的空格)

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

mysql> select trim('  d js k w ');
+---------------------+
| trim('  d js k w ') |
+---------------------+
| d js k w            |
+---------------------+
1 row in set (0.00 sec)

3. concat(x,y) 将提供的参数x和y拼接成一个字符串

mysql> select concat('x','hfiwo');
+---------------------+
| concat('x','hfiwo') |
+---------------------+
| xhfiwo              |
+---------------------+
1 row in set (0.00 sec)

mysql> 

4. upper(x) 将字符串x的所有字母变成大写字母

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

mysql> 

5. lower(x) 将字符串x的所有字母变成小写字母

mysql> select lower('AHIBD');
+----------------+
| lower('AHIBD') |
+----------------+
| ahibd          |
+----------------+
1 row in set (0.00 sec)

mysql> 

6. left(x,y) 返回字符串x的前y个字符

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

mysql> 

7. right(x,y) 返回字符串x的后y个字符

mysql> select right('feiowv',2);
+-------------------+
| right('feiowv',2) |
+-------------------+
| wv                |
+-------------------+
1 row in set (0.00 sec)

mysql> 

8. repeat(x,y) 将字符串x重复y次

mysql> select repeat('adc',2);
+-----------------+
| repeat('adc',2) |
+-----------------+
| adcadc          |
+-----------------+
1 row in set (0.00 sec)

mysql> 

9. space(x) 返回x个空格

mysql> select concat('a',space(5),'b');
+--------------------------+
| concat('a',space(5),'b') |
+--------------------------+
| a     b                  |
+--------------------------+
1 row in set (0.00 sec)

mysql> 

10. replace(x,y,z) 将字符串z替代字符串x中的字符串y

mysql> select replace('tress','ss','aa');
+----------------------------+
| replace('tress','ss','aa') |
+----------------------------+
| treaa                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> 

11. strcmp(x,y) 比较x和y，返回的值可以为-1,0,1

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

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

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

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

12. substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串

mysql> select substring('mysqldiwnwknfs',3,4);
+---------------------------------+
| substring('mysqldiwnwknfs',3,4) |
+---------------------------------+
| sqld                            |
+---------------------------------+
1 row in set (0.00 sec)

mysql> 

13. reverse(x) 将字符串x反转

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

mysql> 

五. 日期时间函数

常用的日期时间函数

1. curdate() 返回当前时间的年月日

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

2. curtime() 返回当前时间的时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:57:44  |
+-----------+
1 row in set (0.02 sec)

3. now() 返回当前时间的日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-25 15:58:34 |
+---------------------+
1 row in set (0.01 sec)

mysql> 

4. month(x) 返回日期x中的月份值

mysql> select month('2020-8-23');
+--------------------+
| month('2020-8-23') |
+--------------------+
|                  8 |
+--------------------+
1 row in set (0.00 sec)

mysql> 

5. week(x) 返回日期x是年度第几个星期

mysql> select week('2020-8-24');
+-------------------+
| week('2020-8-24') |
+-------------------+
|                34 |
+-------------------+
1 row in set (0.00 sec)

mysql> 

6. hour(x) 返回x中的小时值

mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
|              16 |
+-----------------+
1 row in set (0.00 sec)

mysql> 
mysql> select hour('15:57:53');
+------------------+
| hour('15:57:53') |
+------------------+
|               15 |
+------------------+
1 row in set (0.00 sec)

7. minute(x) 返回x中的分钟值

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

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

8. second(x) 返回x中的秒钟值

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

9. dayofweek(x) 返回x是星期几，1星期日，2星期一

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

mysql> 

10.dayofmonth(x) 计算日期x是本月的第几天

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

mysql> 

11.dayofyear(x) 计算日期x是本年的第几天

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

mysql>