当前位置: 萬仟网 > IT编程>数据库>Mysql > MySQL(学生表、教师表、课程表、成绩表)多表查询

MySQL(学生表、教师表、课程表、成绩表)多表查询

2019年10月09日 17:51  | 萬仟网IT编程  | 我要评论

1、表架构

student(sid,sname,sage,ssex) 学生表 
course(cid,cname,tid) 课程表 
sc(sid,cid,score) 成绩表 
teacher(tid,tname) 教师表

2、建表sql语句

 

 1 create table student 
 2   ( 
 3      sid int primary key not null,
 4      sname varchar(30), 
 5      sage int, 
 6      ssex varchar(8) 
 7   )  
 8  
 9 create table course 
10   ( 
11      cid int primary key not null, 
12      cname varchar(30), 
13      tid int 
14   ) 
15  
16 create table sc 
17   ( 
18      sid int not null, 
19      cid int not null, 
20      score int 
21   )  
22  
23 create table teacher 
24   ( 
25      tid int primary key not null, 
26      tname varchar(30) 
27   )

 

3、问题:
(1)查询“30001”课程的所有学生的学号与分数; 

select sid,score from sc where cid="30001"

 

(2)查询“001”课程比“002”课程成绩高的所有学生的学号与分数;

select a.sid,a.score from (select sid,score from sc where cid="30001") a,

      (select sid,score from sc where cid="30002") b

     where a.score>b.score and a.sid=b.sid

 

(3)查询平均成绩大于60分的同学的学号和平均成绩;

select sid,avg(score)

from sc

group by sid having avg(score)>60

 

(4)查询所有同学的学号、姓名、选课数、总成绩

select s.sid as "学号", s.sname as "姓名", count(sc.cid) as "课程数目", sum(sc.score) as "总分数"

from student s, sc sc

where s.sid=sc.sid

group by s.sid

 

(5)查询姓“李”的老师的个数;

select count(distinct(tname))

  from teacher

  where tname like '李%';

 

(6)查询学过“张三”老师课的同学的学号、姓名

select s.sid as "学号", s.sname as "姓名"

from student s, sc sc, course c, teacher t

where s.sid=sc.sid and c.cid=sc.cid and t.tid=c.tid and t.tname="张三"


(7)查询没有学过“张三”老师课的同学的学号、姓名

select s.sid, s.sname

from student s

where s.sid not in (

select s.sid

from student s, sc sc, course c, teacher t

where s.sid=sc.sid and c.cid=sc.cid and t.tid=c.tid and t.tname="张三"

)


(8)查询学过“30001”并且也学过编号“30002”课程的同学的学号、姓名 

select s.sid, s.sname

from student s, sc sc

where s.sid=sc.sid and sc.cid="30001" and exists(

     select * from sc as sc2 where sc2.sid=sc.sid and sc2.cid="30002"

)


(9)查询学过“叶平”老师所教的所有课的同学的学号、姓名;

select sid, sname

from student

where sid in (

select sc.sid

from sc sc, course c, teacher t

where sc.cid=c.cid and c.tid=t.tid and t.tname="张二"

)


(10)查询所有课程成绩小于60分的同学的学号、姓名

select sid, sname from student

where sid not in (

select distinct(sc.sid) from student s, sc sc

where sc.sid=s.sid and sc.score>60)

 

(11)查询没有学全所有课的同学的学号、姓名;

select sid, sname from student 

where sid not in(

select s.sid from student s, sc sc

where sc.sid=s.sid

group by s.sid

having count(sc.cid)=(

select count(cid) from course))


(12)查询各科成绩最高和最低的分:以如下形式显示:课程id,最高分,最低分

select cid as "课程id", max(score) as "最高分", min(score) as "最低分"
from sc
group by cid


(13)按各科平均成绩从低到高和及格率的百分数从高到低顺序

(方式一)
select sc.cid as "课程id",c.cname as "课程名", avg(sc.score) as "平均成绩",
sum(case when sc.score > 60 then 1 else 0 end)/count(1)*100 as "及格百分数"
from sc sc, course c
where sc.cid=c.cid
group by sc.cid
order by avg(sc.score) asc,
sum(case when sc.score > 60 then 1 else 0 end)/count(1)*100 desc


(方式二)
select sc.cid as "课程id",c.cname as "课程名", ifnull(avg(sc.score),0) as "平均成绩",
100*sum(case when ifnull(sc.score,0)>=60 then 1 else 0 end)/count(*) as "及格百分数"
from sc sc, course c
where sc.cid = c.cid
group by sc.cid
order by avg(sc.score) asc,
100*sum(case when ifnull(sc.score,0)>=60 then 1 else 0 end)/count(*) desc


(14)查询所有学生的所有科目的成绩单(学号、姓名、语文、数学、英语、物理、平均分、总分(按照总分由高到低排序))

select s.sid as "学号", s.sname as "姓名",
sum(case c.cname when "语文" then sc.score else 0 end) as "语文",
sum(case c.cname when "数学" then sc.score else 0 end) as "数学",
sum(case c.cname when "英语" then sc.score else 0 end) as "英语",
sum(case c.cname when "物理" then sc.score else 0 end) as "物理",
ifnull(avg(sc.score),0) as "平均分",
ifnull(sum(sc.score),0) as "总分"
from student s
left outer join sc sc on s.sid=sc.sid
left outer join course c on sc.cid=c.cid
group by s.sid, s.sname
order by ifnull(sum(sc.score),0) desc


(15)查询总分排名在200-300(包含200和第300)之间的学生所有成绩单信息

select s.sid as "学号", s.sname as "姓名",
sum(case c.cname when "语文" then sc.score else 0 end) as "语文",
sum(case c.cname when "数学" then sc.score else 0 end) as "数学",
sum(case c.cname when "英语" then sc.score else 0 end) as "英语",
sum(case c.cname when "物理" then sc.score else 0 end) as "物理",
ifnull(avg(sc.score),0) as "平均分",
ifnull(sum(sc.score),0) as "总分"
from student s
left outer join sc sc on s.sid=sc.sid
left outer join course c on sc.cid=c.cid
group by s.sid, s.sname
having ifnull(sum(sc.score),0) between 200 and 300
order by ifnull(sum(sc.score),0) desc


(16)查询总分排名在前四名的学生所有成绩单信息

select s.sid as "学号", s.sname as "姓名",
sum(case c.cname when "语文" then sc.score else 0 end) as "语文",
sum(case c.cname when "数学" then sc.score else 0 end) as "数学",
sum(case c.cname when "英语" then sc.score else 0 end) as "英语",
sum(case c.cname when "物理" then sc.score else 0 end) as "物理",
ifnull(avg(sc.score),0) as "平均分",
ifnull(sum(sc.score),0) as "总分"
from student s
left outer join sc sc on s.sid=sc.sid
left outer join course c on sc.cid=c.cid
group by s.sid, s.sname
order by ifnull(sum(sc.score),0) desc
limit 0,4


(17)查询总分排名在前二名到四名的学生所有成绩单信息(limit 1,3表示从第二条数据开始,连续三条数据)

select s.sid as "学号", s.sname as "姓名",
sum(case c.cname when "语文" then sc.score else 0 end) as "语文",
sum(case c.cname when "数学" then sc.score else 0 end) as "数学",
sum(case c.cname when "英语" then sc.score else 0 end) as "英语",
sum(case c.cname when "物理" then sc.score else 0 end) as "物理",
ifnull(avg(sc.score),0) as "平均分",
ifnull(sum(sc.score),0) as "总分"
from student s
left outer join sc sc on s.sid=sc.sid
left outer join course c on sc.cid=c.cid
group by s.sid, s.sname
order by ifnull(sum(sc.score),0) desc
limit 1,3


(18)查询学生平均成绩及其名次

(非本人)
select 1+(select count( distinct 平均成绩)
from (
select sid,avg(score) as 平均成绩
from sc
group by sid ) as t1
where 平均成绩 > t2.平均成绩) as 名次, s# as 学生学号,平均成绩
from (select sid,avg(score) 平均成绩
from sc group by sid ) as t2
order by 平均成绩 desc


原文链接:https://blog.csdn.net/pgy0000/article/details/83002561

 

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com