MySQL聚合统计和内置函数

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

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

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

数学函数

abs

select abs(-12.3);
+------------+
| abs(-12.3) |
+------------+
| 12.3 |
+------------+

format

mysql> select format(3.1413123,2);
+---------------------+
| format(3.1413123,2) |
+---------------------+
| 3.14 |
+---------------------+

mod

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+

ceiling

select ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
| -3 |
+---------------+

floor

mysql> select floor(-4.5);
+-------------+
| floor(-4.5) |
+-------------+
| -5 |
+-------------+

其他函数

md5

mysql> create table user(
-> id bigint primary key auto_increment,
-> name varchar(20) not null,
-> password char(32) not null
-> );

mysql> insert into user (name,password) values ('李四' ,md5('12345'));

mysql> select * from user;
| id | name | password |
+----+--------+----------------------------------+
| 1 | 李四 | 827ccb0eea8a706c4c34a16891f84e7b |
+----+--------+--------------------------

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/428488.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！