mysql经典4张表问题

1.数据库表结构关联图

2.问题：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

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

4、查询名字中含有"风"字的学生信息

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

6、查询所有学生的课程及分数情况；

7、查询没学过"张三"老师授课的同学的信息

8.查询学过"张三"老师授课的同学的信息

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11、查询没有学全所有课程的同学的信息

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

15、查询出只有两门课程的全部学生的学号和姓名

16、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)17、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

19、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

20、查询不及格的课程

21、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

22、求每门课程的学生人数

23、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

25、检索至少选修两门课程的学生学号

26、查询选修了全部课程的学生信息

27、查询各学生的年龄

28、查询本月过生日的学生

29、查询下月过生日的学生

30、查询学全所有课程的同学的信息

3.源文件：

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50720
 Source Host           : localhost:3306
 Source Schema         : work

 Target Server Type    : MySQL
 Target Server Version : 50720
 File Encoding         : 65001

 Date: 16/02/2022 16:39:35
*/SETNAMES utf8mb4;SETFOREIGN_KEY_CHECKS=0;--------------------------------Table structure for course
------------------------------DROPTABLEIFEXISTS `course`;CREATETABLE `course`  (
  `cid` int(11)NOTNULLAUTO_INCREMENTCOMMENT'课程编号',
  `cname` varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULLCOMMENT'课程名称',
  `tid` int(11)NULLDEFAULTNULLCOMMENT'教师编号',PRIMARYKEY(`cid`)USINGBTREE,INDEX `tid`(`tid`)USINGBTREE,CONSTRAINT `course_ibfk_1` FOREIGNKEY(`tid`)REFERENCES `teacher` (`tid`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDBAUTO_INCREMENT=4CHARACTERSET= utf8 COLLATE= utf8_general_ci COMMENT='课程表'ROW_FORMAT=Dynamic;--------------------------------Records of course
------------------------------INSERTINTO `course` VALUES(1,'语文',2);INSERTINTO `course` VALUES(2,'数学',1);INSERTINTO `course` VALUES(3,'英语',3);--------------------------------Table structure for sc
------------------------------DROPTABLEIFEXISTS `sc`;CREATETABLE `sc`  (
  `sid` int(11)NOTNULLCOMMENT'学生编号',
  `cid` int(11)NULLDEFAULTNULLCOMMENT'课程编号',
  `score` int(11)NULLDEFAULTNULLCOMMENT'分数')ENGINE=InnoDBCHARACTERSET= utf8 COLLATE= utf8_general_ci COMMENT='成绩表'ROW_FORMAT=Dynamic;--------------------------------Records of sc
------------------------------INSERTINTO `sc` VALUES(1,1,90);INSERTINTO `sc` VALUES(1,2,80);INSERTINTO `sc` VALUES(1,3,90);INSERTINTO `sc` VALUES(2,1,70);INSERTINTO `sc` VALUES(2,2,60);INSERTINTO `sc` VALUES(2,3,80);INSERTINTO `sc` VALUES(3,1,80);INSERTINTO `sc` VALUES(3,2,80);INSERTINTO `sc` VALUES(3,3,80);INSERTINTO `sc` VALUES(4,1,50);INSERTINTO `sc` VALUES(4,2,30);INSERTINTO `sc` VALUES(4,3,20);INSERTINTO `sc` VALUES(5,1,76);INSERTINTO `sc` VALUES(5,2,87);INSERTINTO `sc` VALUES(6,1,31);INSERTINTO `sc` VALUES(6,3,34);INSERTINTO `sc` VALUES(7,2,89);INSERTINTO `sc` VALUES(7,3,98);--------------------------------Table structure for student
------------------------------DROPTABLEIFEXISTS `student`;CREATETABLE `student`  (
  `sid` int(11)NOTNULLAUTO_INCREMENTCOMMENT'学生编号',
  `sname` varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULLCOMMENT'学生姓名',
  `sage` date NULLDEFAULTNULLCOMMENT'出生年月',
  `ssex` char(4)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULLCOMMENT'学生性别',PRIMARYKEY(`sid`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=9CHARACTERSET= utf8 COLLATE= utf8_general_ci COMMENT='学生表'ROW_FORMAT=Dynamic;--------------------------------Records of student
------------------------------INSERTINTO `student` VALUES(1,'赵雷', '1990-01-01', '男');INSERTINTO `student` VALUES(2,'钱电', '1990-12-21', '男');INSERTINTO `student` VALUES(3,'孙风', '1990-05-20', '男');INSERTINTO `student` VALUES(4,'李云', '1990-08-06', '男');INSERTINTO `student` VALUES(5,'周梅', '1991-12-01', '女');INSERTINTO `student` VALUES(6,'吴兰', '1992-03-01', '女');INSERTINTO `student` VALUES(7,'郑竹', '1989-07-01', '女');INSERTINTO `student` VALUES(8,'王菊', '1990-01-20', '女');--------------------------------Table structure for teacher
------------------------------DROPTABLEIFEXISTS `teacher`;CREATETABLE `teacher`  (
  `tid` int(11)NOTNULLAUTO_INCREMENTCOMMENT'教师编号',
  `tname` varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULLCOMMENT'教师姓名',PRIMARYKEY(`tid`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=4CHARACTERSET= utf8 COLLATE= utf8_general_ci COMMENT='教师表'ROW_FORMAT=Dynamic;--------------------------------Records of teacher
------------------------------INSERTINTO `teacher` VALUES(1,'张三');INSERTINTO `teacher` VALUES(2,'李四');INSERTINTO `teacher` VALUES(3,'王五');SETFOREIGN_KEY_CHECKS=1;

4.答案：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
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=sc2.sid and s.sid=sc1.sid;3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,avg(sc.score) from student s,sc group by s.sid having avg(sc.score)>=60;4、查询名字中含有"风"字的学生信息
select * from student where sname like ‘%风%’;5、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname,score from student s,sc where s.sid=sc.sid and cid=2 and score<60;6、查询所有学生的课程及分数情况；
select cname,score from sc,course where sc.cid=course.cid;7、查询没学过"张三"老师授课的同学的信息
select s.* from student s where s.sid not in(select sc1.sid from sc sc1,course c,teacher t where t.tid=c.tid and sc1.cid=c.cid and t.tname=‘张三’);8.查询学过"张三"老师授课的同学的信息
select s.* from student s ,sc sc1,course c,teacher t where s.sid=sc1.sid and sc1.cid=c.cid and c.tid=t.tid and t.tname=‘张三’;9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
student(sid)sc(sid cid tid)sc2(sid cid tid)course(cid tid cname)
select s.* from student s,sc sc1,sc sc2 where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid=2;10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select distinct s.* 
    from student s,sc sc1,sc sc2,sc sc3 
        where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid!=2;11、查询没有学全所有课程的同学的信息
select s.* from student s where s.sid not in(select sc1.sid from sc sc1,sc sc2,sc sc3 where sc1.cid=1 and sc2.cid=2 and sc3.cid =3 and sc1.sid=sc2.sid and sc1.sid=sc3.sid) group by s.sid;12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct s.* from student s,sc sc1 where s.sid=sc1.sid and sc1.cid in(select cid from sc where sid=1) and s.sid<>1;13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select s.* from student s where s.sid in(select distinct sc.sid from sc where sid<>1 and sc.cid in(select distinct cid from sc where sid=1)group by sc.sid having count(1)=(select count(1) from sc where s.sid=1));14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s.* from student s where s.sid not in(select sc1.sid from sc sc1,course c,teacher t where sc1.cid=c.cid and c.tid=t.tid and t.tname=‘张三’);15、查询出只有两门课程的全部学生的学号和姓名
select s.* from student s,sc group by sc.sid having count(sc.sid)=2 and s.sid=sc.sid;16、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student where sage>=‘1900-01-01’ and sage<=‘1900-12-31’;
select s.* from student s where s.sage like ‘1900-%’;（方法2）

17、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.cid,avg(score) from sc group by sc.cid order by avg(score)DESC, sc.cid;18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score>70;19、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.sname,avg(score) from sc,student s where s.sid=sc.sid group by sc.sid having avg(score)>=85;20、查询不及格的课程
select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score<60;21、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select s.sid,s.sname from student s,sc where sc.sid=s.sid and sc.cid=1 and score>80;22、求每门课程的学生人数
select cid,count(sid) from sc group by sc.cid;23、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid,count(sid) from sc group by cid having count(sid)>5 order by count(sid),cid ASC;24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score from student s1,student s2,sc sc1,sc sc2 where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score;25、检索至少选修两门课程的学生学号
select sid from sc group by sid having count(cid)>=2;26、查询选修了全部课程的学生信息
select s.* from sc,student s where s.sid=sc.sid group by sid having count
(cid)=3;27、查询各学生的年龄
select s.sname,(TO_DAYS(‘2017-09-07’)-TO_DAYS(s.sage))/365 as age from student s;28、查询本月过生日的学生
select s.sname from student s where s.sage like ‘_____07%’;29、查询下月过生日的学生
select s.sname from student s where s.sage like ‘_____08%’;30、查询学全所有课程的同学的信息
select s.* from student s,sc sc1,sc sc2,sc sc3 where sc1.cid=1 and sc2.cid=2 and sc3.cid=3 and sc1.sid=sc2.sid and sc1.sid=sc3.cid and s.sid =sc1.sid group by s.sid;