MySQL单表数据查询之分组数据查询之旅
-
简单分组查询
-
实现统计功能分组查询
-
实现多个字段分组查询
1、创建数据库
create database check_group;
2、使用刚刚创建的数据库
use check_group;
3、创建表boluo
create table boluo(
id int,
name varchar(20),
job varchar(20),
brithday date,
sal int,
depno int);
查看表的定义信息
desc boluo;
此处添加照片7
4、插入数据
insert into boluo values(001,'菠萝吹雪','洗完工',1900-01-01,1787,10), (002,'梨花诗','收银员',1900-07-07,1987,10);
insert into boluo values(001,'陆小果','洗完工',1900-02-01,1787,20), (002,'橙留香','送货员',1900-09-07,1987,20);
insert into boluo values(001,'上官子怡','洗完工',1900-02-01,1787,30), (002,'天下无贼','售货员',1900-09-07,1987,30);
查看菠萝表所有的数据记录
select * from boluo;
执行完上面的命令后,发现2出错误
1、发现出错brithday写错了,修改为birthday
alter table boluo
change brithday birthday varchar(20);
再次查看表的定义信息
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| birthday | varchar(20) | YES | | NULL | |
| sal | int(11) | YES | | NULL | |
| depno | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2、birthday数据没有成功插入,导致select为默认值
修改
update boluo set birthday="1900-01-01" where name='菠萝吹雪';
update boluo set birthday="1900-07-07" where name='梨花诗';
update boluo set birthday="1900-02-01" where name='陆小果';
update boluo set birthday="1900-09-07" where name='橙留香';
update boluo set birthday="1900-02-01" where name='上官子怡';
update boluo set birthday="1900-09-07" where name='天下无贼';
========================
分组数据查询的意义:
对数据库具有基于表的特定列对数据进行分析处理
例如:可以对所有数据进行分组,然后对分组后的数据记录进行统计计算
分组用SQL语句GROUP BY实现,基本语法格式如下:
select function() from table_name
where condition
group by field;
field字段的要求:field字段上的值一定要有重复值,否则没有意义
执行SQL语句GROUP BY对所有数据进行分组
select * from table_name
group by deptno;
实现统计功能分组查询
select group_concat(field)
from table_name
where condition
group by field;
==========================================
5、分组数据查询----简单分组查询
select * from boluo group by depno;
查询结果:
+------+--------------+-----------+------------+------+-------+
| id | name | job | birthday | sal | depno |
+------+--------------+-----------+------------+------+-------+
| 1 | 菠萝吹雪 | 洗完工 | 1900-01-01 | 1787 | 10 |
| 1 | 陆小果 | 洗完工 | 1900-02-01 | 1787 | 20 |
| 1 | 上官子怡 | 洗完工 | 1900-02-01 | 1787 | 30 |
+------+--------------+-----------+------------+------+-------+
3 rows in set (0.00 sec)
解释:根据depno字段进行分组 从boluo表中查询所有的字段
只实现简单的分组查询是没有任何实际意义的,因为关键字group by单独使用时,默认查询出每个分组中随机记录一条,具有很大的不确定性。
6、分组数据查询—实现统计功能分组查询
select depno,group_concat(name) isname
from boluo
group by depno;
解释:根据depno分组,命名isname来存储显示name的字段,select 后面的depno字段,就仅仅是普通的查询而已
查询结果:
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| depno | isname |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 10 | 菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗 |
| 20 | 陆小果,橙留香 |
| 30 | 上官子怡,天下无贼 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
select id,group_concat(name) isname from boluo group by depno;
查询结果:
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id | isname |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗,菠萝吹雪,梨花诗 |
| 1 | 陆小果,橙留香 |
| 1 | 上官子怡,天下无贼 |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
可以比较两条语句,能看到不同点的
7、分组数据查询----实现多个字段分组查询
select depno from boluo
group by depno;
+-------+
| depno |
+-------+
| 10 |
| 20 |
| 30 |
+-------+
select depno,birthday
from boluo
group by depno,birthday;
+-------+------------+
| depno | birthday |
+-------+------------+
| 10 | 1900-01-01 |
| 10 | 1900-07-07 |
| 20 | 1900-02-01 |
| 20 | 1900-09-07 |
| 30 | 1900-02-01 |
| 30 | 1900-09-07 |
+-------+------------+
select depno,birthday,group_concat(name),count(name)
from boluo
group by depno,birthday;
+-------+------------+-------------------------------------------------------------------------------+-------------+
| depno | birthday | group_concat(name) | count(name) |
+-------+------------+-------------------------------------------------------------------------------+-------------+
| 10 | 1900-01-01 | 菠萝吹雪,菠萝吹雪,菠萝吹雪,菠萝吹雪,菠萝吹雪,菠萝吹雪 | 6 |
| 10 | 1900-07-07 | 梨花诗,梨花诗,梨花诗,梨花诗,梨花诗,梨花诗 | 6 |
| 20 | 1900-02-01 | 陆小果 | 1 |
| 20 | 1900-09-07 | 橙留香 | 1 |
| 30 | 1900-02-01 | 上官子怡 | 1 |
| 30 | 1900-09-07 | 天下无贼 | 1 |
+-------+------------+-------------------------------------------------------------------------------+-------------+
8、分组数据查询–实现having字句限定分组查询
在MySQL软件中如果想实现对分组进行条件限制,决不能通过关键字where来实现,因为该关键字主要用来实现条件限制数据记录。为解决上述问题,MySQL软件专门提供了关键字having来实现条件限制分组数据记录。
having关键字查询语法形式如下
select function(field)
from table_name
where condition
group by field,field,field,...,fieldn
having condition;