DQL之多表查询—进阶练习

DQL之多表查询—进阶练习：

需要创建5张表，如图所示：

1，创建表，导入数据：

创建class表，导入数据

#创建表及插入记录
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
);

#插入班级数据
INSERT INTO class VALUES
(1, '三年二班'),
(2, '三年三班'),
(3, '一年二班'),
(4, '二年九班');

创建teacher表，导入数据

#创建teacher表
CREATE TABLE teacher(
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(32) NOT NULL,
PRIMARY KEY (tid)
);

#导入数据
INSERT INTO teacher VALUES
(1, '爱因斯坦老师'),
(2, '牛顿老师'),
(3, '老王老师'),
(4, '华罗庚老师'),
(5, '郎朗老师');

创建student表, 导入数据

#创建student
CREATE TABLE student(
sid int(11) NOT NULL AUTO_INCREMENT,
gender char(1) NOT NULL,
class_id int(11) NOT NULL,
sname varchar(32) NOT NULL,
PRIMARY KEY (sid),
KEY fk_class (class_id),
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
);

#导入数据
INSERT INTO student VALUES
(1, '男', 1, '理解'),
(2, '女', 1, '钢蛋'),
(3, '男', 1, '张三'),
(4, '男', 1, '张一'),
(5, '女', 1, '张二'),
(6, '男', 1, '张四'),
(7, '女', 2, '铁锤'),
(8, '男', 2, '李三'),
(9, '男', 2, '李一'),
(10, '女', 2, '李二'),
(11, '男', 2, '李四'),
(12, '女', 3, '如花'),
(13, '男', 3, '刘三'),
(14, '男', 3, '刘一'),
(15, '女', 3, '刘二'),
(16, '男', 3, '刘四');

创建course表，导入数据

#创建course
CREATE TABLE course(
cid int(11) NOT NULL AUTO_INCREMENT,
cname varchar(32) NOT NULL,
teacher_id int(11) NOT NULL,
PRIMARY KEY (cid),
KEY fk_course_teacher (teacher_id),
CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#导入数据
INSERT INTO course VALUES
(1, '生物', 1),
(2, '物理', 2),
(3, '体育', 3),
(4, '美术', 2);

创建score表，导入数据

#创建表
CREATE TABLE score (
sid int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
course_id int(11) NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (sid),
KEY fk_score_student (student_id),
KEY fk_score_course (course_id),
CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#导入数据
INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);

2 ,进阶查询练习18题：
1、查询所有的课程的名称以及对应的任课老师姓名

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

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

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

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

7、 查询没有报牛顿老师课的学生姓名

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

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

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

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

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

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

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

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

16、查询每门课程所有学生考出的成绩并按从高到低排序

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

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

3,参考练习答案：

--1、查询所有的课程的名称以及对应的任课老师姓名
select cname ,tname from course c left join teacher on(teacher_id = tid);

--2、查询学生表中男女生各有多少人
select  gender, count(sid) from student group by gender ;


--3、查询物理成绩等于100的学生的姓名
select  sname,cname, num from student st 
join score sc on(st.sid  = sc.student_id and num =100) 
join  course co on(sc.course_id = co.cid) where cname = "物理";


--4、查询所选课程的平均成绩大于80分的同学的姓名和平均成绩
select * from (select student_id , avg(num) avnum from score  group by student_id) as t  where t.avnum >80;


--5、查询所有学生的学号，姓名，选课数，总成绩
select st.sid '学号' , st.sname '姓名' , count(student_id) '选课数', sum(num) '总成绩'
from student st join score  sc   on(st.sid = sc.student_id) group by student_id 


--7、 查询没有报牛顿老师课的学生姓名
select stu.sname, stu.sid  from  student stu  where stu.sid  not in     
(select distinct st.sid  from student st left join score sc 
on(st.sid = sc.student_id) 
where sc.course_id 
in( select cid from course co left join teacher te 
on(co.teacher_id = te.tid)
where te.tname="牛顿老师") group by st.sid order by  st.sid);


--8、 查询物理课程比生物课程高的学生的学号
 select x1.t1 from 
 (select student_id t1 ,sc.num t2 from score sc left join course co on(sc.course_id = co.cid) where co.cname ="物理") as x1 
 join
 (select student_id t1 ,sc.num t2 from score sc left join course co on(sc.course_id = co.cid) where co.cname ="生物") as x2 
 on (x1.t1 = x2.t1) where  x1.t2 > x2.t2;
 

--9、 查询没有同时选修物理课程和体育课程的学生姓名 
select stud.sname  from student stud where stud.sid not in
(select  x1.t1 w from 
(select student_id t1  from score sc left join course co on(sc.course_id = co.cid) where co.cname ="物理") as x1 
inner join
(select student_id t1 from score sc left join course co on(sc.course_id = co.cid) where co.cname ="体育") as x2 
on (x1.t1 = x2.t1)) ;


--10、查询挂科超过两门(包括两门)的学生姓名和班级
select  sname,class_id,count(st.sid) t  from student st 
left join score sc on(st.sid = sc.student_id)
where sc.num<60 group by sc.student_id having t>=2;


--11,查询选修了所有课程的学生姓名
select sname from student  stu  where  stu.sid in(
    select student_id from score sc 
		group by student_id
		having count(student_id) >= (select count(1) from course)
) order by sname asc;


--12、查询牛顿老师教的课程的所有成绩记录
select sc.num from score sc where sc.course_id in (
  select  cid from course co where co.teacher_id in(
						 select  tid from teacher t where  t.tname = "牛顿老师"
				)
);


--13、查询每门课程被选修的次数
select co.cname , count(sc.course_id) from score sc 
left join course co  on(sc.course_id = co.cid) 
group by sc.course_id ;


--14、查询全部学生都选修了的课程号和课程名()
select cname ,co.cid from course co where co.cid in(
					 select  sc.course_id from score  sc 
					 group by sc.course_id 
					 having count(sc.course_id) = (select count(1) from student)
);



--15、查询之选修了一门课程的学生姓名和学号
select sname ,sid from student st where st.sid in(
           select  sc.student_id from score sc 
					 group by sc.student_id 
					 having count(sc.course_id) = 1
);



--16、查询每门课程所有学生考出的成绩并按从高到低排序(注意，mysql8.x版本以后，不支持分组之后自动排序，所以这里要自己加上排序)
select  sc.course_id, sc.student_id  
from score sc  
group by sc.course_id,sc.student_id 
order by sc.course_id ; 


--17、查询平均成绩大于80的学生姓名和平均成绩
select  sc.student_id,course_id ,avg(sc.num) from score sc 
group by  sc.student_id
having avg(sc.num) >80
order by num desc ;


--18、查询生物成绩不及格的学生姓名和对应生物分数
select sname, sc.student_id, sc.num
from score sc 
join student stu  on(stu.sid=sc.student_id) 
where sc.course_id in (
       select cid from course co 
			 where co.cname="生物" 
  ) group  by sc.student_id having  num<60;