mysql｜多表连接查询

use test;
select * from students;
select * from courses;
select * from sc;
# 多表连接查询

-- 查询选修了【数据库原理】的计算机系的学生学号和姓名。 
select distinct students.Sno, Sname
	from students, courses, sc
		where Cname='数据库原理' and
			  sc.Sno=students.Sno and
              sc.Cno=courses.Cno;

select students.Sno, Sname
	from (students inner join sc on sc.Sno=students.Sno) inner join courses on courses.Cno=sc.Cno
		where Cname='数据库原理';
        
        
-- 查询每一门课的间接先行课(即先行课的先行课)，显示课程编号和该门课程的间接先行课编号。
select first.Cno, second.PreCno
	from courses first, courses second
		where first.PreCno is not null and
			  second.PreCno is not null and
			  first.PreCno=second.Cno;
                
select cx.Cno, cy.PreCno
	from courses as cx, courses as cy
		where cx.PreCno=cy.Cno and
			  cy.PreCno is not null;
              
              
-- 查询学生的学号、姓名、选修课程的名称和成绩。
select students.Sno, Sname, Cname, Grade
	from students, courses, sc
		where students.Sno=sc.Sno and
              courses.Cno=sc.Cno;
              
select students.Sno, Sname, Cname, Grade
	from (students inner join sc on students.Sno=sc.Sno) inner join courses 
		on courses.Cno=sc.Cno;
        
        
-- 查询选修了课程的学生姓名。
select distinct Sname
	from students, sc
		where students.Sno=sc.Sno;
        
select distinct Sname
	from students inner join sc
		on students.Sno=sc.Sno;
			  
              
-- 查询所有学生的信息和所选修的课程, 要求显示学生的学号、姓名、课程号及课程名, 没有选课的同学对应的选课信息为空。 
-- 左连接
select students.Sno, Sname, courses.Cno, Cname
	from (students left join sc on students.Sno=sc.Sno) left join courses
		on courses.Cno=sc.Cno;
-- 右连接
select students.Sno, Sname, courses.Cno, Cname
	from courses right join (sc right join students on students.Sno=sc.Sno)
		on courses.Cno=sc.Cno;


-- 查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示为0。
select courses.Cno, Cname, count(distinct sc.Sno) as 选课人数
	from courses left join sc
		on sc.Cno=courses.Cno
			group by courses.Cno;
            

-- 列出所有学生所有可能的选修情况，要求显示学生的学号、姓名、课程号及课程名。 
select students.Sno, Sname, courses.Cno, Cname
	from students, courses
		order by students.Sno;
        
        
-- 查找计算机系的学生选修课程数大于2 的学生的姓名、 平均成绩和选课门数，并按平均成绩降序排列。 
select Sname, avg(Grade) as 平均成绩, count(sc.Cno) as 选课门数
	from students, sc
		where students.Sno=sc.Sno and Sdept='计算机'
			group by Sname
				Having count(sc.Cno)
					order by avg(Grade) desc;