mysql combat_MySQL数据库练习

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

select cname,tname from course inner join teacher on course.teacher_id =teacher.tid--2、查询学生表中男女生各有多少人

select gender,count(gender) from student group bygender--3、查询物理成绩等于100的学生的姓名--第四步：与学生表拼接，拿到学生姓名

select student.sid,gender,sname,num from student inner join(select * from(--第一步：查询分数为100的表

select * from score where num = 100 )ast1--第三步：条件筛选出物理分数100的表

where t1.course_id =(--第二步：查询物理的id

select cid from course where cname = '物理')

# 拼接

)as t2 on t2.student_id =student.sid--4、查询平均成绩大于八十分的同学的姓名和平均成绩--第二步：拼表

select sname, num from student inner join(--第一步：以学生id为分组，筛选分数表中的成绩

select student_id,avg(num)as 'num' from score group by student_id having avg(num)>80)as t1 on student.sid =t1.student_id--5、查询所有学生的学号，姓名，选课数，总成绩--第三步：拼表筛选

select t1.student_id,t2.sname,t1.数量,t1.总成绩 from(--第二部筛选分数表，并计数

select student_id,count(num)as '数量',sum(num)as '总成绩' from score group by student_id)ast1inner join(--第一步：筛选学生表

select sid,sname from student)as t2 on t1.student_id =t2.sid--6、 查询姓李老师的个数

select count(tname)as '姓李的数量' from teacher where tname like '李%'

--7、 查询没有报李平老师课的学生姓名--第四步：根据学生id取反拿到没有选择李平老师课程的学生

select sname from student where sid not in(--第三步：拿到选择李平老师课程的学生id

select distinct student_id from score where course_id in(--第二步：根据老师id拿到老师教的课程id

select cid from course where teacher_id =(--第一步:拿到李平老师的id

select tid from teacher where tname = '李平老师')))--8、 查询物理课程比生物课程高的学生的学号--第五步：拼接两张表

select * from(--第二步：查询物理的所有成绩

select student_id as '学生物理id',num as '物理成绩' from score where course_id =(--第一步：查询物理的id

select cid from course where cname = '物理' ))ast1inner join(--第四步：查询生物的所有成绩

select student_id as '学生生物id',num as '生物成绩' from score where course_id =(--第三步：查询生物的id

select cid from course where cname = '生物' ))ast2on t1.学生物理id =t2.学生生物id--第六步：筛选物理大于生物

where 物理成绩 >生物成绩--9、 查询没有同时选修物理课程和体育课程的学生姓名--第四步：根据拿到的学号从学生表中获取想要的数据

select sid,sname from student where sid in(--第三步：以学号为组，计数为1，意思是物理体育只有一门，筛选得到学号

select t1.student_id from(--第一步，简单粗暴，直接把相关的表拼起来

select * from score inner join course on score.course_id =course.cid--第二步，筛选课程

where cname in ('物理','体育')

)ast1group byt1.student_idhaving count(student_id)=1)--10、查询挂科超过两门(包括两门)的学生姓名和班级--第四步：和班级表拼接，拿到想要的字段

select caption,sname,挂科数 from class inner join(--第三步：与学生表拼接，拿出想要的字段

select * from student inner join(--第一步：筛选出小于60分的所有成绩名单

select student_id,count(student_id)as'挂科数' from score where num < 60

--第二步: 以学生为分组，查看这个学生60分以下出现了多少次，就是多少门不及格

group by student_id having count(student_id)>=1

--拼接

)as t1 on student.sid =t1.student_id

)as t2 on class.cid =t2.class_id--11、查询选修了所有课程的学生姓名--第四步：和学生表拼接，拿到想要的字段

select sname,课程数 from student inner join(--第二步：以学生为分组，计算所有学生选择的课程数

select student_id,count(student_id)as'课程数' from score group bystudent_id--第三步：筛选学生选择的课程数=课程总数

having count(student_id) =(--第一步：拿到所有课程的id

select count(cid)as'总课程数' fromcourse )--拼接

)as t1 on student.sid =t1.student_id--12、查询李平老师教的课程的所有成绩记录--第三步：在与分数表拼接，拿到想要的字段信息

select tname,student_id,cname,num from score inner join(--第二步：课程表和李平老师信息表拼接

select * from course inner join(--第一步：拿到李平老师的信息

select * from teacher where tname = '李平老师'

--拼接

)as t1 on course.teacher_id =t1.tid--拼接

)as t2 on score.course_id =t2.cid--13、查询全部学生都选修了的课程号和课程名--第二步：在和课程表拼接起来，选择想要的字段

select student_id,sname,gender,cid,cname,num from course inner join(--第一步：把分数表和学生表拼起来，选择想要的字段

select score.student_id,student.gender,student.sname,score.course_id,score.num from score inner join student on score.student_id =student.sid--拼接

)as t1 on course.cid =t1.course_id--14、查询每门课程被选修的次数

第二步：筛选过的表与课程表拼接，获取课程名select cid,cname, 被选次数 from course inner join(--第一步：以课程id为组，查询被选的次数

select course_id,count(course_id)as'被选次数' from score group bycourse_id

)as t1 on course.cid =t1.course_id--15、查询只选修了一门课程的学生姓名和学号--第二步：拼接学生表，获取信息

select sid,sname,选课门数 from student inner join(--第一步：以学生为分组，查询选课数为1的

select student_id,count(student_id)as '选课门数' from score group bystudent_idhaving count(student_id) = 1

--拼接

) as t1 on student.sid =t1.student_id--16、查询所有学生考出的成绩并按从高到低排序(成绩去重)--第二步：和学生表拼接，获取想要的字段信息

select sid,sname,总成绩 from student inner join(--第一步：查询所有学生的总成绩

select student_id,sum(num)as '总成绩' from score group bystudent_id--拼接

)as t1 on student.sid =t1.student_id--降序排名

order by 总成绩 desc

--17、查询平均成绩大于85的学生姓名和平均成绩--第二步：和学生表拼接，获取想要的字段

select sname,avg as '平均成绩' from student inner join(--第一步：以学生id为分组 查询大于85分的信息

select student_id,avg(num)as 'avg' from score group by student_id having avg(num)>85)as t1 on student.sid =t1.student_id--18、查询生物成绩不及格的学生姓名和对应生物分数--第四步：拼接学生表获取想要的信息

select sname,num as '不及格生物分数' from student inner join(--第二步：查询所有的生物成绩

select * from score where course_id =(--第一步：查询生物的id

select cid from course where cname = '生物')--第三步：筛选分数不及格的

and num<60)as t1 on student.sid =t1.student_id--19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

select sname,平均值 from student inner join(--第五步：根据所有学生的平均成绩选出最大成绩，排序，取第一名

select t2.student_id,平均值 from(--第四步：求出李平老师所有学生的平均成绩

select t1.student_id ,avg(num)as'平均值' from(--第三步：拿到选择李平老师课程的学生id

select * from score where course_id in(--第二步：根据老师id拿到老师教的课程id

select cid from course where teacher_id =(--第一步:拿到李平老师的id

select tid from teacher where tname = '李平老师'))--接第四步

)as t1 group byt1.student_id--接第五步

)as t2 order by 平均值 desc limit 1)as t3 on student.sid =t3.student_id;--20、查询每门课程成绩最好的前两名学生姓名--错误思路：按照课程id分组，排序，去前两名。分组之后，组里面的数据不能取出来--正确思路：把每一课的成绩分别拎出来，分别取前两名。最后合并表

# 最最后：和学生表拼接，拿到学生姓名select student.sid,student.sname,t1.num from student inner join(

# 最后一步，用unionall,不去重连接所有表

# 生物前两名--第二步：查看生物的所有成绩

(select * from score where course_id in(--第一步：获取所有生物的id号

select cid from course where cname= '生物')--第三步：排序,并只显示前两条

order by num desc limit 2)union all# 物理前两名--第二步：查看生物的所有成绩

(select * from score where course_id in(--第一步：获取所有生物的id号

select cid from course where cname= '物理')--第三步：排序,并只显示前两条

order by num desc limit 2)union all# 体育前两名--第二步：查看生物的所有成绩

(select * from score where course_id in(--第一步：获取所有生物的id号

select cid from course where cname= '体育')--第三步：排序,并只显示前两条

order by num desc limit 2)union all# 美术前两名--第二步：查看生物的所有成绩

(select * from score where course_id in(--第一步：获取所有生物的id号

select cid from course where cname= '美术')--第三步：排序,并只显示前两条

order by num desc limit 2)

)as t1 on student.sid =t1.student_id--21、查询不同课程但成绩相同的学号，课程号，成绩

--第二步：再次和分数表拼接，获取学号

select student_id,course_id,t1.num,相同数量 from score inner join(--第一步：以分数为分组，筛选出分数重复的

select num,count(num)as'相同数量' from score group by num having count(num) >=2)as t1 on score.num =t1.num--22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；--第四步：根据学生id取反拿到没有选择李平老师课程的学生

select sname from student where sid not in(--第三步：拿到选择李平老师课程的学生id

select distinct student_id from score where course_id in(--第二步：根据老师id拿到老师教的课程id

select cid from course where teacher_id =(--第一步:拿到李平老师的id

select tid from teacher where tname = '李平老师')))--23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

# 这题考小学语文--第三步：在学生表中筛选出学生

select sid,sname from student where sid in(--第二步：查询选修了学号为1的学生，选修过的课程的学生id

select student_id from score where course_id in(--第一步：查询学号为1的同学，选修的课程id

select course_id from score where student_id = 1))--24、任课最多的老师中学生单科成绩最高的学生姓名

# 这题考小学语文--实现思路：1.获取授课老师最多的老师id--2.查看该老师教授的课程id--3.查询教授课程对应的所有分数--(难点)4.从查询出的分数表，选出最大值的分数--(难点,分数相同)5.拿到最大分数再到第3分数表中，查询一致的分数对应的所有信息--6.再和学生表拼接，拿到学生姓名

# 这是第3步筛选之后的表select * from score where course_id in(--第四步：查看该老师教授的课程id

select cid from course where teacher_id in(--第三步：获取任课数量最多的老师id

select t1.teacher_id from(--第一步：在课程表当中，给选择的课程数量计数

select teacher_id,count(teacher_id)as '数量' from course group byteacher_id--第二步：排序

order by 数量 desc limit 1)ast1

))

# 这是最大分数select max(num) from score where course_id in(--第四步：查看该老师教授的课程id

select cid from course where teacher_id in(--第三步：获取任课数量最多的老师id

select t3.teacher_id from(--第一步：在课程表当中，给选择的课程数量计数

select teacher_id,count(teacher_id)as '数量' from course group byteacher_id--第二步：排序

order by 数量 desc limit 1)ast3

))

# 最终答案

# 与学生表拼接select student.sid,student.sname,t4.num from student inner join(

# 获取最大分数对应的信息select * from(select * from score where course_id in(--第四步：查看该老师教授的课程id

select cid from course where teacher_id in(--第三步：获取任课数量最多的老师id

select t1.teacher_id from(--第一步：在课程表当中，给选择的课程数量计数

select teacher_id,count(teacher_id)as '数量' from course group byteacher_id--第二步：排序,获取最大数量

order by 数量 desc limit 1)ast1

))

)as t2 where t2.num =(--第五步：查询分数表，教授课程对应的所有分数

# 这里获取最大值分数select max(num) from score where course_id in(--第四步：查看该老师教授的课程id

select cid from course where teacher_id in(--第三步：获取任课数量最多的老师id

select t3.teacher_id from(--第一步：在课程表当中，给选择的课程数量计数

select teacher_id,count(teacher_id)as '数量' from course group byteacher_id--第二步：排序,获取最大数量

order by 数量 desc limit 1)ast3

)))

)as t4 on student.sid = t4.student_id