多表联查
-
tb_stu
-
tb_course
-
r_cou_stu
直接插: 有许多无用信息, 需要假加入条件过滤
正确查法:
过滤无用信息
设置别名:
便于打字,避免表格过长
例如: 三表联查
select s.stuname ,c.couname from tb_stu s,tb_course c ,r_cou_stu r
where s.id = r.sid and c.id = r. cid;
JOIN ON
查询没有选课的学生
- 如果想查询没选课的学生可以用 is null
select stuname from tb_stu where id not in (select sid from r_cou_stu);
查询选了Java课程的学生姓名
select stuname from tb_stu where id in (select sid from r_cou_stu where cid in (select id from tb_course where couname='Java'));
select stuname from (select s.stuname , c.couname from tb_stu s, r_cou_stu r, tb_course c where s.id = r.sid and c.id = r.cid) t where couname = 'Java';
GROUP BY
找出选课人选的课程数
select s.stuname '姓名' , count(s.id) '选课个数' from
tb_stu s, r_cou_stu r where s.id = r.sid group by s.id;
找课程对应的选课人数
select c.couname '课程' , count(c.id) '选课个数' from
-> tb_course c, r_cou_stu r where c.id = r.sid group by c.id;
只显示人数大于2的课程
用子查询来做
用HAVING来做
作业
1.查询出来平均分最高的科目?
select c.couname,avg(r.score)
from r_cou_stu r, tb_course c
where r.cid = c.id
group by c.couname
ORDER BY AVG(r.score) DESC
LIMIT 1;
2.查询出来平均分最高的学生?
select s.stuname,avg(r.score) a
from r_cou_stu r, tb_stu s
where r.sid = s.id
group by s.stuname
ORDER BY a DESC
LIMIT 1;
3.查询出来每个挂科学生,挂了几科?
select s.stuname 学生, count(r.score) 科目
from tb_stu s, r_cou_stu r
where s.id = r.sid and r.score < 60
group by s.stuname;
4.查询出来每个挂的学生分别挂了哪些科目。
select s.stuname, c.couname
from tb_stu s, tb_course c, r_cou_stu r
where r.sid = s.id and r.cid = c.id and r.score < 60;
5.询出来指定科目的最高分是谁?
select s.stuname
from(
select max(r.score) m
from tb_course c, r_cou_stu r
where r.cid = c.id
GROUP BY c.couname
) t, r_cou_stu r,tb_stu s,tb_course c
where r.score = t.m and c.id = r.cid and c.couname = 'JAVA' and r.sid = s.id;
6.查询出来个人考的最高分的学科是哪个。
select c.couname
from(
select max(r.score) m
from r_cou_stu r, tb_stu s
where r.sid = s.id
group by s.stuname
) t,tb_stu s,tb_course c,r_cou_stu r
where r.score = t.m and r.sid = s.id and r.cid = c.id and s.stuname = '林俊杰';
7.查询出来每个科目的平均分并按照降序排序。
select couname, avg(score) as '平均分' from tb_course c, r_cou_stu r where c.id = r.cid group by c.couname order by avg(score) desc;
8.查询出来所有学生的平均分,并按照升序排序。
select stuname, avg(score) as '平均分' from tb_stu s, r_cou_stu r where s.id = r.sid group by s.stuname order by avg(score) ;