系列七高级的查询功能
#第三十一课时
--分组查询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;