1、分组聚合
聚合函数（组函数）：
- count 个数
- max 最大值
- min 最小值
- sum 和
- avg 平均值
select 聚合函数 ，字段 where [ exp] group by 字段 having 聚合函数条件
count（*）：
mysql> select count(*) from score where number>75; #满足条件的行数，没有条件则统计所有行数 +----------+ | count(*) | +----------+ | 2 | +----------+
count（列字段）：
mysql> select count(student_id) as stu,number from score group by number; +-----+--------+ | stu | number | +-----+--------+ | 1 | 68 | | 1 | 75 | | 1 | 80 | | 1 | 90 | +-----+--------+
count（distinct 列字段）：非空，不重复行数
mysql> select count(distinct student_id) from score ; +----------------------------+ | count(distinct student_id) | +----------------------------+ | 3 | +----------------------------+
min（字段），max（字段）：
mysql> select max(number) from score ; +-------------+ | max(number) | +-------------+ | 90 | +-------------+ mysql> select min(number) from score ; +-------------+ | min(number) | +-------------+ | 68 | +-------------+
avg（字段），sum（字段）：
mysql> select sum(number) from score where number>80; +-------------+ | sum(number) | +-------------+ | 90 | +-------------+ mysql> select avg(number) from score ; +-------------+ | avg(number) | +-------------+ | 78.2500 | +-------------+
group：（重要）
mysql> select name,count(*) from students group by id;
+--------+----------+
| name | count(*) |
+--------+----------+
| 鲤鱼 | 1 |
| ee | 1 |
| mike | 1 |
| ldh | 1 |
| res | 1 |
+--------+----------+
2、连表（重要）
第一种：使用where
mysql> select * from score,class where score.student_id=class.cid; +-----+------------+-----------+--------+-----+----------+ | sid | student_id | course_id | number | cid | caption | +-----+------------+-----------+--------+-----+----------+ | 1 | 3 | 2 | 68 | 3 | 材料三班 | | 2 | 2 | 2 | 90 | 2 | 材料二班 | | 3 | 1 | 3 | 75 | 1 | 材料一班 | | 4 | 2 | 1 | 80 | 2 | 材料二班 | | 5 | 1 | 3 | 80 | 1 | 材料一班 | +-----+------------+-----------+--------+-----+----------+
第二种：left join 表 on 关系；left join左边全显示；right join右边全显示
mysql> select score.sid,sname,gender,cname,number,caption,tname from score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid
left join class on class.cid=student.class_id
left join teacher on teacher.tid=course.tearch_id;
+-----+-------+--------+-------+--------+----------+--------+ | sid | sname | gender | cname | number | caption | tname | +-----+-------+--------+-------+--------+----------+--------+ | 3 | 章鱼 | 男 | 物理 | 75 | 材料二班 | 张老师 | | 5 | 章鱼 | 男 | 物理 | 80 | 材料二班 | 张老师 | | 2 | 李杰 | 女 | 大化 | 90 | 材料一班 | 张老师 | | 4 | 李杰 | 女 | 高数 | 80 | 材料一班 | 任丘 | | 1 | 吴雨 | 女 | 大化 | 68 | 材料二班 | 张老师 | +-----+-------+--------+-------+--------+----------+--------+
第三种： inner join 内连接求交集，不显示null的行
第四种：union 上下连表
去重：select 查询内容 from 表名 UNION select 查询内容 from 表名
不去重：... UNION ALL ...
练习：1、检索‘物理’分数大于70，按分数降序排列的同学学号
mysql> select student.sid,sname,cname,number from score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid
left join class on class.cid=student.class_id
where number>70 and cname='物理' order by number; #条件放最后
+------+-------+-------+--------+ | sid | sname | cname | number | +------+-------+-------+--------+ | 1 | 章鱼 | 物理 | 75 | | 1 | 章鱼 | 物理 | 80 | +------+-------+-------+--------+
2、查询姓“李”的学生名单
mysql> select * from student; +-----+--------+--------+----------+ | sid | sname | gender | class_id | +-----+--------+--------+----------+ | 1 | 章鱼 | 男 | 2 | | 2 | 李杰 | 女 | 1 | | 3 | 吴雨 | 女 | 2 | | 4 | 李观星 | 男 | 2 | +-----+--------+--------+----------+ mysql> select sname from student where sname like '李%'; +--------+ | sname | +--------+ | 李杰 | | 李观星 | +--------+
3、按性别分类
SELECT gender,COUNT(gender) as '人数' FROM student GROUP BY gender;
4、临时表，测试数据
SELECT * FROM (SELECT sid,sname FROM student) as b; #as b 必须要有
5、查询平均成绩大于60分的同学的学号和平均成绩，姓名
SELECT student_id,sname,AVG(num) FROM score LEFT JOIN student ON student_id=student.sid GROUP BY student_id HAVING AVG(num)>60; 或
SELECT student_id,sname,aa FROM (SELECT *,AVG(num) as aa FROM score GROUP BY student_id HAVING AVG(num)>60) as b LEFT JOIN student ON student_id=student.sid;
6、没有选过刘老师的课的学生
SELECT student_id,sname FROM score LEFT JOIN student ON student.sid=student_id
WHERE student_id not in (SELECT DISTINCT(student_id) FROM score WHERE course_id in (2,4))
7、生物成绩高于物理成绩
SELECT A.student_id,A.num,B.num FROM (SELECT * FROM score WHERE score.course_id = (SELECT cid FROM course WHERE cname='生物')) as A INNER JOIN (SELECT * FROM score WHERE score.course_id = (SELECT cid FROM course WHERE cname='物理')) as B ON A.student_id = B.student_id WHERE A.num > B.num
8、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
SELECT student_id, (SELECT num FROM score as s2 WHERE s1.student_id=s2.student_id and course_id=1) as 生物, (SELECT num FROM score as s2 WHERE s1.student_id=s2.student_id and course_id=2) as 物理, (SELECT num FROM score as s2 WHERE s1.student_id=s2.student_id and course_id=3) as 体育, (SELECT num FROM score as s2 WHERE s1.student_id=s2.student_id and course_id=4) as 美术, COUNT(1) as 有效课程数,AVG(num) as 平均分 FROM score as s1 GROUP BY student_id ORDER BY 平均分 asc
9、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；还能运算，判断
SELECT course_id,MAX(num),MIN(num),MIN(num)+1,CASE WHEN MIN(num)<10 THEN 0 ELSE MIN(num) END FROM score GROUP BY course_id
10、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT course_id,AVG(num),SUM(CASE WHEN num>60 THEN 1 ELSE 0 END),sum(1),SUM(CASE WHEN num>60 THEN 1 ELSE 0 END)/SUM(1) as jgl
FROM score GROUP BY course_id ORDER BY AVG(num) ASC,jgl DESC
11、课程平均分从高到低显示（显示任课老师）
SELECT course_id,cname,AVG(num),tname FROM score
LEFT JOIN course ON course.cid=course_id
LEFT JOIN teacher on teacher.tid=course.teacher_id GROUP BY course_id ORDER BY AVG(num) DESC
或
SELECT course_id,cname,AVG(if(ISNULL(score.num),0,score.num)),tname FROM score #if 语句相当于python三元表达式，目的是防止空的值计入计算
LEFT JOIN course ON course.cid=course_id
LEFT JOIN teacher on teacher.tid=course.teacher_id GROUP BY course_id ORDER BY AVG(num) DESC