MySQL数据库02

多表联查

• 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) ;