mysql经典4张表问题详细讲解，
目录
- 1.数据库表结构关联图
- 2.问题：
- 3.源文件：
- 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 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号', `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名称', `tid` int(11) NULL DEFAULT NULL COMMENT '教师编号', PRIMARY KEY (`cid`) USING BTREE, INDEX `tid`(`tid`) USING BTREE, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, '语文', 2); INSERT INTO `course` VALUES (2, '数学', 1); INSERT INTO `course` VALUES (3, '英语', 3); -- ---------------------------- -- Table structure for sc -- ---------------------------- DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `sid` int(11) NOT NULL COMMENT '学生编号', `cid` int(11) NULL DEFAULT NULL COMMENT '课程编号', `score` int(11) NULL DEFAULT NULL COMMENT '分数' ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sc -- ---------------------------- INSERT INTO `sc` VALUES (1, 1, 90); INSERT INTO `sc` VALUES (1, 2, 80); INSERT INTO `sc` VALUES (1, 3, 90); INSERT INTO `sc` VALUES (2, 1, 70); INSERT INTO `sc` VALUES (2, 2, 60); INSERT INTO `sc` VALUES (2, 3, 80); INSERT INTO `sc` VALUES (3, 1, 80); INSERT INTO `sc` VALUES (3, 2, 80); INSERT INTO `sc` VALUES (3, 3, 80); INSERT INTO `sc` VALUES (4, 1, 50); INSERT INTO `sc` VALUES (4, 2, 30); INSERT INTO `sc` VALUES (4, 3, 20); INSERT INTO `sc` VALUES (5, 1, 76); INSERT INTO `sc` VALUES (5, 2, 87); INSERT INTO `sc` VALUES (6, 1, 31); INSERT INTO `sc` VALUES (6, 3, 34); INSERT INTO `sc` VALUES (7, 2, 89); INSERT INTO `sc` VALUES (7, 3, 98); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号', `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名', `sage` date NULL DEFAULT NULL COMMENT '出生年月', `ssex` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生性别', PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '赵雷', '1990-01-01', '男'); INSERT INTO `student` VALUES (2, '钱电', '1990-12-21', '男'); INSERT INTO `student` VALUES (3, '孙风', '1990-05-20', '男'); INSERT INTO `student` VALUES (4, '李云', '1990-08-06', '男'); INSERT INTO `student` VALUES (5, '周梅', '1991-12-01', '女'); INSERT INTO `student` VALUES (6, '吴兰', '1992-03-01', '女'); INSERT INTO `student` VALUES (7, '郑竹', '1989-07-01', '女'); INSERT INTO `student` VALUES (8, '王菊', '1990-01-20', '女'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师编号', `tname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师姓名', PRIMARY KEY (`tid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '教师表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (1, '张三'); INSERT INTO `teacher` VALUES (2, '李四'); INSERT INTO `teacher` VALUES (3, '王五'); SET FOREIGN_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;
总结
到此这篇关于mysql经典4张表问题的文章就介绍到这了,更多相关mysql 4张表问题内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。