当前位置：首页 > news >正文

MySQL聚合统计和内置函数

news 2024/9/22 21:30:14

【数据库】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

对分组聚合之后的结果进行条件筛选。

条件筛选的阶段不同

from emp

where ename != 'SMITH'

group by deptno,job

deptno,job,avg(sal) myavg

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 |
+----------------+

mysql> select concat ('考生姓名: ',name,', 总分: ',chinese+math+english,', 语文成绩: ',chinese,', 数学成绩',math,', 英语成绩: ',english) msg from exam_result;
+-------------------------------------------------------------------------------------------+
| msg |
+-------------------------------------------------------------------------------------------+
| 考生姓名: 唐三藏, 总分: 221, 语文成绩: 67, 数学成绩98, 英语成绩: 56 |
| 考生姓名: 猪悟能, 总分: 276, 语文成绩: 88, 数学成绩98, 英语成绩: 90 |
| 考生姓名: 曹孟德, 总分: 247, 语文成绩: 70, 数学成绩110, 英语成绩: 67 |
| 考生姓名: 刘玄德, 总分: 215, 语文成绩: 55, 数学成绩115, 英语成绩: 45 |
| 考生姓名: 孙权, 总分: 221, 语文成绩: 70, 数学成绩73, 英语成绩: 78 |
| 考生姓名: 宋公明, 总分: 200, 语文成绩: 75, 数学成绩95, 英语成绩: 30 |
+-------------------------------------------------------------------------------------------+

select length('汉字');
+------------------+
| length('汉字') |
+------------------+
| 6 |
+------------------+

一个汉字占3个字节。

replace

substring

select substring(ename,2,2), ename from emp;
+----------------------+--------+
| substring(ename,2,2) | ename |
+----------------------+--------+
| MI | SMITH |
| LL | ALLEN |
| AR | WARD |
| OR | FORD |
| IL | MILLER |
+----------------------+--------+

原始表结构不会发生变化。

以首字母小写的方式显示所有员工的姓名