数据库函数

■ 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(x 1， x 2…) ：返回集合中最大的值
least(x 1， x 2…)： 返回集合中最小的值

实例：

返回-99的绝对值
mysql> select abs(-99);
+---------+
| abs(-99) |
+---------+
|      99 |
+---------+
1 row in set (0.00 sec)

返回0到1的随机数
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.9157036365156096 |
+--------------------+
1 row in set (0.00 sec)

返回x除以y以后的余数
mysql> select mod(8,3);
+----------+
| mod(8,3) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

返回2的5次方
mysql> select power(2,5);
+------------+
| power(2,5) |
+------------+
|         32 |
+------------+
1 row in set (0.00 sec)

返回离7.2最近的整数
mysql> select round(7.2);
+------------+
| round(7.2) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

返回16的平方根
mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

保留随机数的两位小数，四舍五入后的值
mysql> select truncate(rand(),2);
+--------------------+
| truncate(rand(),2) |
+--------------------+
|               0.61 |
+--------------------+
1 row in set (0.01 sec)

保留1.234的两位小数
mysql> select round(1.234,2);
+----------------+
| round(1.234,2) |
+----------------+
|           1.23 |
+----------------+
1 row in set (0.01 sec)

返回大于或等于x的最小整数， 返回数字x截断为y位小数的值
mysql> select ceil(2.3),floor(2.4);
+-----------+------------+
| ceil(2.3) | floor(2.4) |
+-----------+------------+
|         3 |          2 |
+-----------+------------+
1 row in set (0.00 sec)

返回集合中最小的值，返回集合中最大的值
mysql> select least(12,43,22),greatest(13,24,11);
+-----------------+--------------------+
| least(12,43,22) | greatest(13,24,11) |
+-----------------+--------------------+
|              12 |                 24 |
+-----------------+--------------------+
1 row in set (0.00 sec)

二、聚合函数

■ 对表中数据记录进行集中概述而设计的一类函数
■ 常用的聚合函数
avg() ：返回指定列的平均值
count() ：返回指定列中非NULL值的个数
min() ：返回指定列的最小值
max() ：返回指定列的最大值
sum() ：返回指定列的所有值之和

实例：以下表为例

mysql> select * from a_player;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |    88 |
|  2 | lisi     |    89 |
|  3 | wangwu   |    67 |
|  4 | zhaoliu  |    90 |
|  5 | xuli     |    80 |
|  6 | keke     |    75 |
+----+----------+-------+
6 rows in set (0.00 sec)

mysql> select avg(score) from a_player;
+------------+
| avg(score) |
+------------+
|    81.5000 |
+------------+
1 row in set (0.00 sec)

mysql> select count(name) from a_player;
+-------------+
| count(name) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(score) from a_player;
+------------+
| min(score) |
+------------+
|         67 |
+------------+
1 row in set (0.00 sec)

mysql> select max(score) from a_player;
+------------+
| max(score) |
+------------+
|         90 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from a_player;
+------------+
| sum(score) |
+------------+
|        489 |
+------------+
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反转

实例：

返回字符串x的长度
mysql> select length ('adf');
+----------------+
| length ('adf') |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

返回去除指定格式的值
l> select trim(' adf');
+---------------+
| trim(' adf') |
+---------------+
| adf           |
+---------------+
1 row in set (0.00 sec)

拼接成一个字符串
mysql> select concat('adc','wef');
+---------------------+
| concat('adc','wef') |
+---------------------+
| adcwef              |
+---------------------+
1 row in set (0.00 sec)

将字符串x的所有字母变成大写字母
mysql> select upper ('qqq');
+---------------+
| upper ('qqq') |
+---------------+
| QQQ           |
+---------------+
1 row in set (0.00 sec)
将字符串x的所有字母变成小写字母
mysql> select lower ('AQ');
+--------------+
| lower ('AQ') |
+--------------+
| aq           |
+--------------+
1 row in set (0.00 sec)

返回字符串x的前2个字符
mysql> select left('wecwc',2);
+-----------------+
| left('wecwc',2) |
+-----------------+
| we              |
+-----------------+
1 row in set (0.00 sec)

返回字符串的后3个字符
mysql> select right('wecwc',3);
+------------------+
| right('wecwc',3) |
+------------------+
| cwc              |
+------------------+
1 row in set (0.00 sec)

mysql> select concat(left('adw',2),right('wccev',4));
+----------------------------------------+
| concat(left('adw',2),right('wccev',4)) |
+----------------------------------------+
| adccev                                 |
+----------------------------------------+
1 row in set (0.00 sec)

将字符串d重复4次
mysql> select repeat ('d',4);
+-----——）-----------+
| repeat ('d',4) |
+----------------+
| dddd           |
+----------------+
1 row in set (0.01 sec)

返回3个空格
mysql> select space (3);
+-----------+
| space (3) |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

用字符串ee替代字符串abcd中的ab
mysql> select replace('abcd','ab','ee');
+---------------------------+
| replace('abcd','ab','ee') |
+---------------------------+
| eecd                      |
+---------------------------+
1 row in set (0.00 sec)

比较x和y， 返回的值可以为-1（前者小于后者），0（等于）， 1（大于）
mysql> select strcmp('a','b');
+--------------+
| strcmp('a','b') |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

获取从字符串中的第2个位置开始长度为2的字符串
mysql> select substring('abcd',2,2);
+-----------------------+
| substring('abcd',2,2) |
+-----------------------+
| bc                    |
+-----------------------+
1 row in set (0.00 sec)

将字符串x反转
mysql> select reverse('abcde');
+------------------+
| reverse('abcde') |
+------------------+
| edcba            |
+------------------+
1 row in set (0.00 sec)
mysql> select upper(reverse('abcde'));
+-------------------------+
| upper(reverse('abcde')) |
+-------------------------+
| EDCBA                   |
+-------------------------+
1 row in set (0.00 sec)

四、日期时间函数

常用时间函数：

实例：

mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-11-04 | 14:41:54  | 2020-11-04 14:41:54 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)
mysql> select month('2020-11-02'),week('2020-11-02'),hour('21:11');
+---------------------+--------------------+---------------+
| month('2020-11-02') | week('2020-11-02') | hour('21:11') |
+---------------------+--------------------+---------------+
|                  11 |                 44 |            21 |
+---------------------+--------------------+---------------+
1 row in set (0.00 sec)
mysql> select minute(now()),second(now());
+---------------+---------------+
| minute(now()) | second(now()) |
+---------------+---------------+
|            34 |            41 |
+---------------+---------------+
1 row in set (0.01 sec)
mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
|                4 |                 4 |              309 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)