MySQL（学生表、教师表、课程表、成绩表）多表查询

1、表架构

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

2、建表sql语句

复制代码
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL,
  `cname` varchar(30) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3001', '语文', '4');
INSERT INTO `course` VALUES ('3002', '数学', '2');
INSERT INTO `course` VALUES ('3003', '英语', '1');
INSERT INTO `course` VALUES ('3004', '物理', '3');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('101', '3001', '90');
INSERT INTO `sc` VALUES ('102', '3001', '85');
INSERT INTO `sc` VALUES ('103', '3001', '76');
INSERT INTO `sc` VALUES ('105', '3001', '87');
INSERT INTO `sc` VALUES ('106', '3001', '66');
INSERT INTO `sc` VALUES ('108', '3001', '96');
INSERT INTO `sc` VALUES ('101', '3002', '92');
INSERT INTO `sc` VALUES ('102', '3002', '81');
INSERT INTO `sc` VALUES ('103', '3002', '93');
INSERT INTO `sc` VALUES ('104', '3002', '73');
INSERT INTO `sc` VALUES ('105', '3002', '65');
INSERT INTO `sc` VALUES ('108', '3002', '96');
INSERT INTO `sc` VALUES ('101', '3003', '96');
INSERT INTO `sc` VALUES ('102', '3003', '85');
INSERT INTO `sc` VALUES ('103', '3003', '76');
INSERT INTO `sc` VALUES ('104', '3003', '63');
INSERT INTO `sc` VALUES ('105', '3003', '59');
INSERT INTO `sc` VALUES ('106', '3003', '56');
INSERT INTO `sc` VALUES ('107', '3003', '91');
INSERT INTO `sc` VALUES ('108', '3003', '86');
INSERT INTO `sc` VALUES ('101', '3004', '100');
INSERT INTO `sc` VALUES ('102', '3004', '83');
INSERT INTO `sc` VALUES ('103', '3004', '75');
INSERT INTO `sc` VALUES ('104', '3004', '69');
INSERT INTO `sc` VALUES ('105', '3004', '50');
INSERT INTO `sc` VALUES ('106', '3004', '52');
INSERT INTO `sc` VALUES ('107', '3004', '87');
INSERT INTO `sc` VALUES ('108', '3004', '78');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL,
  `sname` varchar(30) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', '龙大', '18', '男');
INSERT INTO `student` VALUES ('102', '熊二', '19', '男');
INSERT INTO `student` VALUES ('103', '张三', '18', '男');
INSERT INTO `student` VALUES ('104', '李四', '19', '女');
INSERT INTO `student` VALUES ('105', '王五', '20', '男');
INSERT INTO `student` VALUES ('106', '李华', '19', '男');
INSERT INTO `student` VALUES ('107', '李红', '19', '女');
INSERT INTO `student` VALUES ('108', '李明', '20', '男');
INSERT INTO `student` VALUES ('109', '贝贝', '19', '女');
INSERT INTO `student` VALUES ('110', '娜娜', '20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL,
  `tname` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '李龙');
INSERT INTO `teacher` VALUES ('3', '李逍遥');
INSERT INTO `teacher` VALUES ('4', '朱钊');
复制代码

 

3、问题：
（1）查询“3001”课程的所有学生的学号与分数； 

SELECT sid,score FROM sc WHERE cid="3001"

 

（2）查询“3001”课程比“3002”课程成绩高的所有学生的学号与分数；

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,

      (SELECT sid,score FROM sc WHERE cid="3002") 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 INNER JOIN sc sc
on 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）查询学过“3001”并且也学过编号“3002”课程的同学的学号、姓名 

复制代码
SELECT s.sid, s.sname

FROM student s, sc sc

WHERE s.sid=sc.sid AND sc.cid="3001" AND EXISTS(

     SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="3002"

)
复制代码


（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 名次, sid as 学生学号,平均成绩
FROM (SELECT sid,AVG(score) 平均成绩
FROM sc GROUP BY sid ) AS T2
ORDER BY 平均成绩 desc
复制代码

 

思考：

1、sql如何进行优化？

2、distinct的作用和用法？

3、case when then else end 用法？

4、什么时候用group by？

5、什么时候用having，having与where的区别？

6、内连接、外连接、左连接、右连接、全连接之间的关系？

posted @   donleo123  阅读(21889)  评论(0)  编辑  收藏  举报
编辑推荐：
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
· 一个奇形怪状的面试题：Bean中的CHM要不要加volatile？
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
点击右上角即可分享
微信分享提示