MySQL经典四表查询(教师，学生，成绩，课程表)多表查询

2024-06-02 15:41| 来源: 网络整理| 查看: 265

我用的工具：MySQL5.7+SQLyog-11.2.5-0 1、表架构呈现 student(sid,sname,sage,ssex) 学生表 teacher(tid,tname) 教师表 course(cid,cname,tid) 课程表 sc(sid,cid,score) 成绩表 2、建表SQL语句并插入数据 -- 1.学生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20), sage DATE, ssex ENUM ('男','女') ); -- 2.课程表中使用了外键教师编号，因而需要先建立教师表 CREATE TABLE teacher( tid INT PRIMARY KEY AUTO_INCREMENT, tname VARCHAR(20) ); -- 3.建立课程表 CREATE TABLE course( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20), tid INT, FOREIGN KEY (tid) REFERENCES teacher (tid) ); -- 4.建立成绩表 CREATE TABLE sc( sid INT, cid INT, score INT ); -- 先给student表插入数据 INSERT INTO student VALUES (1,'赵雷','1990-01-01','男'), (2,'钱电','1990-12-21','男'), (3,'孙风','1990-05-20','男'), (4,'李云','1990-08-06','男'), (5,'周梅','1991-12-01','女'), (6,'吴兰','1992-03-01','女'), (7,'郑竹','1989-07-01','女'), (8,'王菊','1990-01-20','女'); -- 给teacher表插入数据,这里不可以先给course表插入数据,因为course表外键连接到teacher的主键 INSERT INTO teacher VALUES(1,'张三'),(2,'李四'),(3,'王五'); -- 给course表插入数据 INSERT INTO course VALUES(1,'语文',2),(2,'数学',1),(3,'英语',3); -- 最后给sc表插入数据 INSERT INTO sc VALUES(1,1,90),(1,2,80),(1,3,90),(2,1,70),(2,2,60),(2,3,80),(3,1,80), (3,2,80),(3,3,80),(4,1,50),(4,2,30),(4,3,20),(5,1,76),(5,2,87),(6,1,31),(6,3,34),(7,2,89),(7,3,98); 3、问题集锦

（1）查询 1 课程比 2 课程成绩高的学生的信息及课程分数

SELECT s.sid,s.sname,s.sage,s.ssex,sc1.score,sc2.score FROM student s ,sc sc1,sc sc2 WHERE sc1.`cid`=1 AND sc2.`cid`=2 AND sc1.`score`>sc2.`score` AND sc1.`sid`=s.`sid`AND sc2.`sid`=s.`sid`;

（2）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.sid,s.sname,AVG(sc1.`score`) AS 'avg_score' FROM student s ,sc sc1 WHERE s.sid=sc1.`sid` GROUP BY s.sid HAVING avg_score>=60 ORDER BY avg_score DESC;

（3）查询名字中含有"风"字的学生信息

SELECT s.sid,s.sname,s.sage,s.ssex FROM student s WHERE sname LIKE '%风%';

（4）查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s.sname,sc1.score FROM student s ,sc sc1 WHERE s.sid=sc1.sid AND cid=2 AND sc1.score='1990-01-01' AND s.sage=70;

（18）查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩,并按照平均成绩降序排列

SELECT s.sid,s.sname,AVG(score) AS avg_score FROM student s,sc WHERE s.sid = sc.sid GROUP BY s.sid HAVING avg_score >=85 ORDER BY avg_score DESC;

（19）查询成绩不及格的课程和学生姓名

SELECT s.sname,c.cname,sc.score FROM student s,course c,sc WHERE s.sid=sc.sid AND sc.cid=c.cid AND sc.score=80 GROUP BY s.`sid`;

（21）求每门课程的学生人数

SELECT cid AS '课程编号',COUNT(sid)AS '课程人数' FROM sc GROUP BY sc.cid;

（22）统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数

SELECT cid AS '课程编号',COUNT(sid)AS '课程人数' FROM sc GROUP BY sc.cid HAVING COUNT(sid)>5 ORDER BY COUNT(sid),sc.cid ASC;

（23）查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT s1.sid,sc1.cid,sc1.score,s2.sid,sc2.cid,sc2.score FROM student s1,student s2,sc sc1,sc sc2 WHERE s1.sid=sc1.sid AND s2.sid=sc2.sid AND sc1.cid sc2.cid AND sc1.score = sc2.score;

（24）检索至少选修两门课程的学生学号

SELECT sid FROM sc GROUP BY sc.sid HAVING COUNT(cid)>=2;

（25）查询选修了全部课程的学生信息

SELECT * FROM student s,sc WHERE s.sid =sc.sid GROUP BY s.sid HAVING COUNT(cid)=3;

（26）查询各学生的年龄(年龄保留整数)

SELECT s.sname,ROUND((TO_DAYS('2020-06-11')-TO_DAYS(s.sage))/365) AS age FROM student s;

（27）查询本月过生日的学生姓名和出生年月

-- _____ :五个下划线长度 SELECT s.sname,s.sage FROM student s WHERE s.sage LIKE '_____07%';

（28）查询下月过生日的学生

SELECT s.sname,s.sage FROM student s WHERE s.sage LIKE '_____08%';

（29）查询学全所有课程的同学的信息

SELECT s.* FROM student s,sc WHERE s.sid=sc.sid GROUP BY s.sid HAVING COUNT(sc.cid)=3;

【本文地址】

公司简介

联系我们