IT编程 > 数据库 > Mysql

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

22人参与2019-10-09

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

 

您希望与广大热心网友互动!!点此进行留言回复

相关推荐

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

10-09

LeetCode——Employees Earning More Than Their Managers

10-09

浅谈mysql的索引设计原则以及常见索引的区别

06-04

InnoDB On-Disk Structures(四)--Doublewrite Buffer (转载)

08-21

mysql获取本周五到上周四的数据SUBDATE()DATE_FORMAT()的方法

07-04

ubuntu16.04下安装petalinux 2017.4的方法

01-02

MySQL命令行导出与导入数据库

06-07

MySQL数据库优化经验详谈(服务器普通配置)第1/3页

06-05

在linux下,把程序设置systemctl服务,并开机启动

07-04

MySQL编译安装及启动

07-15

最近更新

Linux搭建MySQL主从

10-20

MySQL实战45讲

10-20

“GIS DICTIONARY A-Z” 查询页面开发(2)——INSERT INTO数据库

10-20

MySQL如何进行索引重建操作?

10-20

MySQL truncate含有外键约束的条目报错

10-20

MySQL复制从库建立-xtracebackup方式

10-19

Python学习日记(四十) Mysql数据库篇 八

10-19

mysql查询表大小

10-19

MySQL修炼之路一

10-19

MySQL修炼之路二

10-19

网友评论

已有0条评论