mySQL之多表查询

内联：inner join

两张表保留相同的部分，按从左表向右表的方式显示新表

左联：left join

两张表保留相同的部分，将左边的表剩余记录插入新表

右联：right join

两张表保留相同的部分，将右边的表剩余记录插入新表

全联：union

两张表保留相同的部分，将左右两张表剩余记录插入新表

exists：用来判断后面的条件成立不成立来决定前面的记录是否被查询

个人经验：在多表联合查询数据时如果用到聚合函数，必定会用到分组group by，having就有用到的可能~

1、查询所有的课程的名称以及对应的任课老师姓名

select course.cname,teacher.tname from course inner join teacher on teacher_id=tid;

2、查询学生表中男女生各有多少人

select gender,count(sid) from student group by gender;

3、查询物理成绩等于100的学生的姓名

select sname from student where sid in(select score.student_id from score inner join course on course_id=cid where cname=’物理’ and num=100);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

select student.sname,okok.num from student inner join (
select student_id,avg(num) as num from score group by student_id having avg(num)>80
)okok on student.sid=student_id;
难点在于如何给表起别名，对内部表进行处理

5、查询所有学生的学号，姓名，选课数，总成绩

select sname 姓名,sum(num) 总成绩,count(course_id) 选课数,student.sid 学号 from student inner join
score on student.sid=student_id group by sname,student.sid

6、 查询姓李老师的个数

select count(tid) 姓李老师的个数 from teacher where tname like ‘李%’;

7、 查询没有报李平老师课的学生姓名

select sname from student inner join score on student.sid=score.student_id where course_id not in
(select cid from teacher inner join course on teacher.tid=course.teacher_id where tname=’李平老师’)

8、 查询物理课程比生物课程高的学生的学号

select sname,max(num) from student inner join score on student.sid=score.student_id where course_id in (
select cid from course where cname in (‘物理’,’生物’)
) group by sname

9、 查询没有同时选修物理课程和体育课程的学生姓名

select sname from student where sname not in (select sname from student inner join (
select * from score inner join (
select * from course where cname in (‘物理’,’体育’)
)xxoo on score.course_id=xxoo.cid
)ooxx on student.sid=ooxx.student_id group by sname having count(cname)=2);

10、查询挂科超过两门(包括两门)的学生姓名和班级

select sname,caption,count(num)from class inner join
(select sname,class_id,num from student inner join (
select student_id,num from course inner join (
select student_id,course_id,num from score where num<60
)xxoo on course.cid=course_id
)ooxx on student.sid=student_id
)xoxo on class.cid=xoxo.class_id group by sname,caption having count(num)>=2;
group by 可以跟多个字段

11 、查询选修了所有课程的学生姓名

select sname from student where sid in (select student_id from score inner join course on course.cid=score.course_id)

12、查询李平老师教的课程的所有成绩记录

select tname,cname,group_concat(num) from score inner join (
select * from course inner join (
select tid,tname from teacher where tname=’李平老师’)okok on teacher_id=tid
)koko on score.course_id=koko.cid group by cname;

13、查询全部学生都选修了的课程号和课程名

select sname,cid,cname from student inner join (
select student_id,course.cid,course.cname from score inner join course on course.cid=score.course_id
)xxoo on sid=student_id;

14、查询每门课程被选修的次数

select cname,count(student_id) from score inner join course on course_id=cid group by cname;

15、查询只选修了一门课程的学生姓名和学号

select sid,sname from student inner join (
select student_id,count(cname) 门数,group_concat(cname) 课程 from score inner join
course on score.course_id=course.cid group by student_id having count(cname)=1
)xxoo on student.sid=student_id

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

select distinct num from student inner join (
select * from score order by num desc)ooxx on student.sid=student_id order by num desc

17、查询平均成绩大于85的学生姓名和平均成绩

select sname,avg(num) from student inner join score on student.sid=student_id group by sname having avg(num)>85;

18、查询生物成绩不及格的学生姓名和对应生物分数

select sname,num from student inner join(
select student_id,num from course inner join score on course.cid=score.course_id where cname=’生物’ and num<60
)okok on student.sid=okok.student_id;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

select 姓名, 平均成绩 from
(select sname 姓名,avg(num) 平均成绩 from student inner join
(select student_id,num from score inner join
(select * from course inner join (select tid from teacher where tname=’李平老师’
)xoxo on course.teacher_id= tid
)xxoo on score.course_id=cid)okok on student.sid=student_id group by sname
)ooxx order by 平均成绩 desc limit 0,1
你们相信查出来这个人均分100吗？学霸！

20、查询每门课程成绩最好的前两名学生姓名

(select * from student inner join (
select * from score inner join course on score.course_id=course.cid
)xoxo on student.sid=student_id where cname=’生物’ order by num desc limit 0,2)
union
(select * from student inner join (
select * from score inner join course on score.course_id=course.cid
)xoxo on student.sid=student_id where cname=’体育’ order by num desc limit 0,2)
union
(select * from student inner join (
select * from score inner join course on score.course_id=course.cid
)xoxo on student.sid=student_id where cname=’物理’ order by num desc limit 0,2)
union
(select * from student inner join (
select * from score inner join course on score.course_id=course.cid
)xoxo on student.sid=student_id where cname=’美术’ order by num desc limit 0,2)
。。。无语。。。

21、查询不同课程但成绩相同的学号，课程号，成绩

select student.sid,course_id,num from student inner join score on student.sid=student_id where num in (
select num from course inner join score on course.cid=course_id where cname=’生物’ and num in(
select num from course inner join score on course.cid=course_id where cname=’体育’ and num in(
select num from course inner join score on course.cid=course_id where cname=’物理’ and num in(
select num from course inner join score on course.cid=course_id where cname=’生物’
))))

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

select sname,cname from course inner join (select sname,course_id from student inner join score
on student.sid=score.student_id where course_id not in (
select cid from teacher inner join course on teacher.tid=course.teacher_id where tname=’叶平’
)
)xxoo on course.cid=course_id

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

select sid,sname from student where sid in(
select student_id from score inner join (
select cid from course inner join
(select xxoo.sid,sname,course_id,student_id from score inner join
(
select * from student where sid=1
)xxoo on score.student_id=xxoo.sid
)ooxx on course.cid=course_id
)xoxo on score.course_id=cid
)

24、任课最多的老师中学生单科成绩最高的学生姓名

1.各科都是最高的

select * from student inner join score on student.sid=student_id where num in
(select max(num) from score inner join (select * from course inner join
(select * from teacher where tname in
(select tname from (select tname,count(tid) tlcs from teacher inner join
course on teacher.tid=teacher_id group by tname)xoxo where tlcs>=(
select mxnum from (select max(cnum) mxnum from (select tid,tname,count(tid) cnum from
teacher inner join
course on teacher.tid=teacher_id group by tname,tid)xxoo
)
ooxx))
)ok on course.teacher_id=tid)okok on score.course_id=cid group by cname)
下步就是取出教授老师最多课程id 即cid：

2.教课最多的两门课cid

select cid from course inner join (
select cname from score inner join (select * from course inner join
(select * from teacher where tname in
(select tname from (select tname,count(tid) tlcs from teacher inner join
course on teacher.tid=teacher_id group by tname)xoxo where tlcs>=(
select mxnum from (select max(cnum) mxnum from (select tid,tname,count(tid) cnum from
teacher inner join
course on teacher.tid=teacher_id group by tname,tid)xxoo
)
ooxx))
)ok
on course.teacher_id=tid)okok on score.course_id=cid group by cname
)koko on course.cname=koko.cname

3.最终版本

select * from student inner join(
select * from
(select student_id,course_id from student inner join score on student.sid=student_id where num in
(select max(num) from score inner join (select * from course inner join
(select * from teacher where tname in
(select tname from (select tname,count(tid) tlcs from teacher inner join
course on teacher.tid=teacher_id group by tname)xoxo where tlcs>=(
select mxnum from (select max(cnum) mxnum from (select tid,tname,count(tid) cnum from
teacher inner join
course on teacher.tid=teacher_id group by tname,tid)xxoo
)
ooxx))
)ok on course.teacher_id=tid)okok on score.course_id=cid group by cname)
)kook where course_id in
(select cid from course inner join (
select cname from score inner join (select * from course inner join
(select * from teacher where tname in
(select tname from (select tname,count(tid) tlcs from teacher inner join
course on teacher.tid=teacher_id group by tname)xoxo where tlcs>=(
select mxnum from (select max(cnum) mxnum from (select tid,tname,count(tid) cnum from
teacher inner join
course on teacher.tid=teacher_id group by tname,tid)xxoo
)
ooxx))
)ok
on course.teacher_id=tid)okok on score.course_id=cid group by cname
)koko on course.cname=koko.cname)
)kkoo on student.sid=student_id