设为首页 加入收藏

TOP

MySQL(学生表、教师表、课程表、成绩表)多表查询(一)
2019-10-09 20:06:04 】 浏览:122
Tags:MySQL 学生 教师 课程表 成绩 查询

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(CA
首页 上一页 1 2 3 下一页 尾页 1/3/3
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇LeetCode——Employees Earning M.. 下一篇LeetCode——Consecutive Numbers

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目