MySQL高级SQL语句（聚合函数）

数据库函数

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

2、常用的函数分类

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

数学函数

常用的数学函数

• 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) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

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

返回0-1随机数

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

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

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

求余数

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

返回x的y次方

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

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

离x最近的整数

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

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

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

保留小数点后几位

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

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

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

平方根

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

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

返回截获y位小数

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

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

返回大于或等于x的最小整数

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

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

返回小于或等于x的最大整数

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

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

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

返回集合中最大或最小值

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

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

聚合函数

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

2、常用的聚合函数

• avg()返回指定列的平均值
• count()返回指定列中非NULL值的个数
• min()返回指定列的最小值
• max()返回指定列的最大值
• sum()返回指定列的所有值之和

项目二：聚合函数

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

mysql> select avg(chengji) from chengji;
+--------------+
| avg(chengji) |
+--------------+
|      72.0000 |
+--------------+
1 row in set (0.00 sec)

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

mysql> select count(chengji) from chengji;
+----------------+
| count(chengji) |
+----------------+
|              8 |
+----------------+
1 row in set (0.00 sec)

mysql> select count(*) from chengji;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

min()返回指定列的最小值与最大值

mysql> select min(chengji) from chengji;
+--------------+
| min(chengji) |
+--------------+
|           55 |
+--------------+
1 row in set (0.00 sec)

mysql> select max(chengji) from chengji;
+--------------+
| max(chengji) |
+--------------+
|           95 |
+--------------+
1 row in set (0.00 sec)

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

mysql> mysql> select sum(chengji) from chengji;
+--------------+
| sum(chengji) |
+--------------+
|          576 |
+--------------+
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反转

项目三：字符串函数

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

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

trim()返回去除指定格式的值

mysql> select trim('abc'); //顶格
+-------------+
| trim('abc') |
+-------------+
| abc         |
+-------------+
1 row in set (0.00 sec)

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

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

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

mysql> select concat('abc','def');
+---------------------+
| concat('abc','def') |
+---------------------+
| abcdef              |
+---------------------+
1 row in set (0.00 sec)

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

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

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

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

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

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

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

mysql> mysql> select left('aBcsdad',2);
+-------------------+
| left('aBcsdad',2) |
+-------------------+
| aB                |
+-------------------+
1 row in set (0.00 sec)

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

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

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

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

space(x)返回x个空格

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

mysql> select space(11);
+-------------+
| space(11)   |
+-------------+
|             |
+-------------+
1 row in set (0.00 sec)

mysql> select space(20);
+----------------------+
| space(20)            |
+----------------------+
|                      |
+----------------------+
1 row in set (0.00 sec)
//空间变大

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

mysql> select replace('abcdefg','a','t');
+----------------------------+
| replace('abcdefg','a','t') |
+----------------------------+
| tbcdefg                    |
+----------------------------+
1 row in set (0.00 sec)

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

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

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

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

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

mysql> select substring('abcdefg',2,3);
+--------------------------+
| substring('abcdefg',2,3) |
+--------------------------+
| bcd                      |
+--------------------------+
1 row in set (0.00 sec)

reverse(x)将字符串x反转

mysql> select reverse('abcdefg');
+--------------------+
| reverse('abcdefg') |
+--------------------+
| gfedcba            |
+--------------------+
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是本年的第几天

项目四：日期时间函数

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

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

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

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:23:15  |
+-----------+
1 row in set (0.00 sec)

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-26 09:23:48 |
+---------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select week('2020-12-26');
+--------------------+
| week('2020-12-26') |
+--------------------+
|                 51 |
+--------------------+
1 row in set (0.00 sec)

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

mysql> select hour('09:23:48');
+------------------+
| hour('09:23:48') |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

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

mysql> select minute('09:23:48');
+--------------------+
| minute('09:23:48') |
+--------------------+
|                 23 |
+--------------------+
1 row in set (0.00 sec)

second(x）返回x中的秒钟值

mysql> select second('09:23:48');
+--------------------+
| second('09:23:48') |
+--------------------+
|                 48 |
+--------------------+
1 row in set (0.00 sec)

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

mysql> select dayofweek('2020-12-26');
+-------------------------+
| dayofweek('2020-12-26') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select dayofmonth('2020-12-26');
+--------------------------+
| dayofmonth('2020-12-26') |
+--------------------------+
|                       26 |
+--------------------------+
1 row in set (0.00 sec)

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

mysql> select dayofyear('2020-12-26');
+-------------------------+
| dayofyear('2020-12-26') |
+-------------------------+
|                     361 |
+-------------------------+
1 row in set (0.00 sec)

附：在可联网情况下同步阿里云时间

[root@server1 ~]# ntpdate ntp.aliyun.com
26 Dec 09:32:55 ntpdate[81904]: adjust time server 203.107.6.88 offset 0.001071 sec