2021-01-30

分组 与分组后的筛选

–分组与分组之后的筛选【group BY 分组 ;GROUP_CONCAT(…)查询每组的详细信息 】
–计算男生和女生中的人数
SELECT gender,COUNT() FROM students2 GROUP BY gender ;
–显示性别对应人数
SELECT gender,COUNT() FROM students2 GROUP BY gender ;
–男生最大年龄与女生最大年龄
SELECT gender,MAX() FROM students2 GROUP BY gender ;
–显示性别对应人数,还需显示总人数
SELECT gender,COUNT() FROM students2 GROUP BY gender WITH ROLLUP;

–查询同种性别（ 分组后）的姓名
SELECT gender , GROUP_CONCAT(NAME) FROM students2 GROUP BY gender ;
–查询组内年龄，姓名
SELECT gender , GROUP_CONCAT(NAME,":",age) FROM students2 GROUP BY gender ;

–分组后
–查询对应性别的总数之后 需取出总数》2的
SELECT gender ,COUNT() FROM students2 GROUP BY gender HAVING COUNT()>2 ;
–查询男生女生总数大于2的姓名
SELECT gender,COUNT(),GROUP_CONCAT(NAME) FROM students2 GROUP BY gender HAVING COUNT()>2;
–查询男生女生平均年龄超过18岁的性别，以及姓名
SELECT gender,AVG(age),GROUP_CONCAT(NAME) FROM students2 GROUP BY gender HAVING AVG(age)>18;

排序

–排序【order BY 默认升序；order BY ASC 指定升序 ；order BY DESC 指定降序】
–查询年龄在18到26之间的男同学，按照年龄从小到大排序
SELECT *FROM students2 WHERE (age BETWEEN 18 AND 26) AND gender=1 ORDER BY age ;
–查询年龄在18~20之间的女同学 ，id从高到低排序
SELECT *FROM students2 WHERE (age BETWEEN 18 AND 24) AND gender=2 ORDER BY id DESC;
–查询年龄在18~23之间的男性，年龄降序，当年龄相同时，按照身高从低到高升序。
SELECT NAME ,gender ,age,high FROM students2 WHERE (age BETWEEN 18 AND 23 ) AND gender=1 ORDER BY age DESC,high ASC;

限制

–限制【limit START COUNT 】且运行时只能写到最后;
SELECT * FROM students2 LIMIT 3,4;