去重：
-
使用DISTINCT关键字去重
-
使用 GROUP BY 分组去重
1）语法： SELECT col_name FROM table1 GROUP BY col_name
2）支持多个分组条件
3）SELECT 中的字段只能是GROUP BY中的字段或聚合函数
4）GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 子句之前
-
HAVING子句
作用：在分组后进行条件筛选（过滤分组）
注意：WHERE 过滤行，而HAVING过滤分组
语法：SELECT col_name FROM table1 GROUP BY col_name HAVING COUNT(1) > 2
SELECT
select_expr, ...
FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | postion}]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count}]
SELECT查询可以包含WHERE和HAVING子句， 但是在这种情况下，
- WHERE子句必须出现在GROUP BY子句之前，
- 而HAVING子句必须出现在GROUP BY子句之后但在ORDER BY子句之前。
SELECT * FROM school_student_info;
SELECT DISTINCT address FROM school_student_info;
SELECT address FROM school_student_info GROUP BY address;
SELECT class_name, address FROM school_student_info GROUP BY class_name, address;
SELECT class_name, address, COUNT(*) FROM school_student_info GROUP BY class_name, address;
SELECT class_name, address, COUNT(*)
FROM school_student_info
WHERE class_name='三年级一班'
GROUP BY class_name, address;
-- 统计每个班的学生人数
SELECT class_name, COUNT(*) FROM school_student_info GROUP BY class_name;
-- 统计每个班男生、女生的人数
SELECT class_name, sex, COUNT(*) FROM school_student_info GROUP BY class_name, sex;
-- 查找三门成绩都及格（>= 60）的学生
SELECT * FROM school_student_grade WHERE score >= 60;
SELECT student_id, COUNT(*) FROM school_student_grade WHERE score >= 60 GROUP BY student_id HAVING COUNT(*) = 3;
