基本语法
- SELECT column-list FROM table_name
- WHERE [ conditions ]
- GROUP BY column1, column2
- HAVING [ conditions ]
- ORDER BY column1, column2
注意顺序 → WHERE - GROUP BY - HAVING - ORDER BY
SELECT classno,COUNT(studentname) FROM student GROUP BY classno; 按照 classno 字段, 分组汇总学生数量
SELECT classno,MIN(age),AVG(age) FROM student GROUP BY classno; 查看每个班级年龄最小值 / 均值
- INSERT INTO company3 VALUES(7,'pual',2000);
- INSERT INTO company3 VALUES(8,'allen',3000);
- INSERT INTO company3 VALUES(9,'teddy',20000);
SELECT name,SUM(salary) FROM company3 GROUP BY name; 多插入一些重复名称的数据, 汇总每个员工的总薪水
分组 + 排序
SELECT classno,COUNT(studentname) FROM student GROUP BY classno ORDER BY classno; 按照 classno 排序
SELECT name,SUM(salary) FROM company3 GROUP BY name ORDER BY SUM(salary); 按照总薪水排序
过滤分组, WHERE
- SELECT classno,COUNT(studentname) FROM student
- WHERE classno> 2
GROUP BY classno; WHERE 作用与分组前, 这里先筛选 classno>2 的数据, 再分组
注意, 这里如果书写: WHERE COUNT(studentname)> 1 就会报错
过滤分组, HAVING
- SELECT classno,COUNT(studentname) FROM student
- GROUP BY classno
HAVING COUNT(studentname)> 1; HAVING 主要用于过滤分组, 且是在分组后进行过滤
所以一般对于分组的条件过滤, 都用 HAVING
来源: http://www.bubuko.com/infodetail-3012312.html