MySQL之分组查询

-- 按照用户所属身份分组proId
select * from cms_user group by proId;

-- 向用户表中添加性别字段 
alter table cms_user add sex enum('男','女','保密');

update cms_user set sex='男' where id in(1,3,5,7,9);

update cms_user set sex='女' where id in(2,4,6,8,10);

update cms_user set sex='保密' where id in(11,12);

-- 按照用户性别分组 
select * from cms_user group by sex;

-- 按照字段位置分组
select * from cms_user group by 7;

-- 按照多个字段分组 
select * from cms_user group by sex,proId;

-- 查询编号大于等于5的用户按照性别分组 
select * from cms_user where id>=5 group by sex;

分组查询配合聚合函数

-- 统计表中所有记录 
select count(*) AS totalUsers from cms_user;

-- count(字段) 不统计NULL值


-- 查询编号，性别，用户名详情，组中总人数，组中最大年龄，最小年龄
-- 平均年龄，以及年龄总和和按照性别分组
-- 配合聚合函数 count(),max(),min(),avg(),sum() 
select id,sex,group_concat(username),
count(*) AS totalUsers,
max(age) AS max_age,
min(age) AS min_age,
avg(age) AS avg_name,
sum(age) AS avg_name
from cms_user group by sex;

-- WITH ROLLUP  记录所有的记录的总和 
select id,sex,group_concat(username),
count(*) AS totalUsers,
max(age) AS max_age,
min(age) AS min_age,
avg(age) AS avg_name,
sum(age) AS avg_name
from cms_user group by sex with rollup;

having语句对分组结果进行二次查询

-- 查询组中人数大于2的 
select sex,group_concat(username) AS users,
count(*) AS totalUsers,
max(age) AS max_age,
sum(age) AS sum_age
from cms_user group by sex
having count(*)>2;

-- 查询组中人数大于2并且最大年龄大于60的 
select sex,group_concat(username) AS users,
count(*) AS totalUsers,
max(age) AS max_age,
sum(age) AS sum_age
from cms_user group by sex
having count(*)>2 and max(age)>60;

-- 查询编号大于等于2的用户 
select sex,group_concat(username) AS users,
count(*) AS totalUsers,
max(age) AS max_age,
sum(age) AS sum_age
from cms_user
where id>=2 
group by sex
having count(*)>2 and max(age)>60;

ORDER BY对查询结果进行排序

-- 按照id 降序排列desc 默认的是asc
-- 默认升序asc 
select * from cms_user order by id;
-- 升序
select * from cms_user order by id asc;
-- 降序
select * from cms_user order by id desc;

-- 按照年龄升序，id降序排列 
select * from cms_user order by age asc,id desc;

select id,age,sex,group_concat(username),count(*) AS totalUsers,sum(age) AS sum_age 
from cms_user 
where id>=2
group by sex
having count(*)>=2
order by age desc,id asc;

-- 实现记录随机 
select * from cms_user order by rand();

通过LIMIT限制显示条数
limit是实现分页的核心

-- 查询表中前三条记录  
select * from cms_user limit 3;

-- 查询表中前一条记录 
-- LIMIT 显示条数 
select * from cms_user limit 1;
-- LIMIT 偏移量，显示条数
select * from cms_user limit 0,1;

select id,sex,age,group_concat(username),
count(*) AS totalUsers,
max(age) AS max_age,
min(age) AS min_age,
avg(age) AS avg_age,
sum(age) AS sum_age
from cms_user
where id>=1
group by sex
having count(*)>=2
order by age desc 
limit 0,2;