【数据库】MySQL聚合统计
王笃笃-CSDN博客
https://blog.csdn.net/wangduduniubi?type=blog显示平均工资低于2000的部门和它的平均工资
mysql> select deptno,avg(sal) deptavg from emp group by deptno;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.01 sec)
mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
having 对聚合后的数据进行条件筛选。
having 和 where 的区别
条件筛选的阶段不同
mysql> select deptno,job,avg(sal) myavg from emp where ename != 'SMITH' group by deptno,job having myavg<2000;
+--------+----------+-------------+
| deptno | job | myavg |
+--------+----------+-------------+
| 30 | SALESMAN | 1400.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 10 | CLERK | 1300.000000 |
+--------+----------+-------------+
where
对具体的任意列进行条件筛选
having
对分组聚合之后的结果进行条件筛选。
条件筛选的阶段不同
1
from emp
2
where ename != 'SMITH'
3
group by deptno,job
4
deptno,job,avg(sal) myavg
5
having myavg<2000;
中间筛选出来的和最终结果——>全部可以看做是逻辑上的表-->mysql 中一切皆表
内置函数
日期函数
current_time();
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:49:57 |
+----------------+
1 row in set (0.00 sec)
current_timestamp();
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-15 20:50:16 |
+---------------------+
1 row in set (0.00 sec)
date_add
mysql> select date_add('2024-09-15',interval 97 day);
+----------------------------------------+
| date_add('2024-09-15',interval 97 day) |
+----------------------------------------+
| 2024-12-21 |
+----------------------------------------+
1 row in set (0.00 sec)
datediff
select datediff('2024-12-15','2024-09-15');
+-------------------------------------+
| datediff('2024-12-15','2024-09-15') |
+-------------------------------------+
| 91 |
+-------------------------------------+
案例
字符串函数
concat
mysql> select concat('a','b');
+-----------------+
| concat('a','b') |
+-----------------+
| ab |
+-----------------+
instr
mysql> select instr('abcd1234fe','1234');
+----------------------------+
| instr('abcd1234fe','1234') |
+----------------------------+
| 5 |
+----------------------------+
ucase
select ucase('abc');
+--------------+
| ucase('abc') |
+--------------+
| ABC |
+--------------+
left
mysql> select left('abcd1234',4);
+--------------------+
| left('abcd1234',4) |
+--------------------+
| abcd |
+--------------------+
length
select length('abdc');
+----------------+
| length('abdc') |
+----------------+
| 4 |
+----------------+