7 高级表查询

系列七 高级的查询功能

• 分组查询group by
• 聚合函数avg
• 分组筛选having
• 结果排序order by
• 限制数量limit

#第三十一课时
--分组查询GROUP BY

--按照用户所属省分进行分组
SELECT * FROM cms_user GROUP BY proId;

--按照字段位置进行分组
SELECT * FROM cms_user GROUP BY 7;

--按照多个字段进行分组
SELECT F FROM cms_user GROUP BY sex,proId;

--先写条件，后对满足条件的记录进行分组
SELECT * FROM cms_user WHERE id > 5 GROUP BY sex;


#第三十二课时
--分组查询配合聚合函数
--配合GROUP_CONCAT()函数进行使用
--查询id，sex，username的详情 按照性别分组
--通过性别分组，分组后得到username的分组详情
SELECT id, sex, GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

--查询ProID，性别详情，注册时间详情，用户名详情 按照ProID进行分组
SELECT proId ,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;

--常见的聚合函数
COUNT()
MAX()
MIN()
AVG()
SUM()
--查询编号，sex，用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username)AS user ,COUNT(*)AS totalUsers FROM cms_user GROUP BY sex;
--COUNT(字段)不统计NULL值
SELECT COUNT(id) AS totalUsers FROM cms_user;

--查询编号，sex，用户名详情以及组中总人数，组中最大年龄，最小年龄，平均年龄，年龄总和按照sex分组
SELECT id,sex,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 GROUP BY sex;


--配合WITH ROLLUP记录上面所有记录的总和
--在末尾加上WITH ROLLUP 属于聚合统计次字段的总的内容

SELECT id,sex,
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 GROUP BY sex WITH ROLLUP;


#第三十三课时
--having语句对分组结果进行二次筛选
SELECT id,sex,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 GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;

#第三十四课时
--ORDER BY 对查询结果进行排序；

--查询按照id降序进行排列DESC /ASC
SELECT * FROM cms_user ORDER BY id ASC;

--按照多个字段进行排序
SELECT *FROM cms_user ORDER BY age ASC ,id DESC;

--实现随机提取记录
ORDER BY RAND();

#第三十五课时
--通过limit限制显示条数
--LIMIT 显示条数
--LIMIT偏移量，显示条数

--查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

SELECT * FROM cms_user LIMTI 10, 5;