用来创建表、插入数据
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `Cno` int(11) NOT NULL COMMENT ‘课程编号‘, `Cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘课程名称‘, `Tno` int(11) NULL DEFAULT NULL COMMENT ‘老师编号‘, PRIMARY KEY (`Cno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES (1, ‘数学‘, 1);INSERT INTO `course` VALUES (2, ‘语文‘, 2);INSERT INTO `course` VALUES (3, ‘英文‘, 1);-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` ( `Sno` int(11) NOT NULL COMMENT ‘学号‘, `Cno` int(11) NOT NULL COMMENT ‘课程编号‘, `score` int(255) NULL DEFAULT NULL COMMENT ‘分数‘, PRIMARY KEY (`Sno`, `Cno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ------------------------------ Records of sc-- ----------------------------INSERT INTO `sc` VALUES (1, 1, 99);INSERT INTO `sc` VALUES (1, 2, 80);INSERT INTO `sc` VALUES (1, 3, 50);INSERT INTO `sc` VALUES (2, 1, 70);INSERT INTO `sc` VALUES (2, 2, 90);INSERT INTO `sc` VALUES (3, 1, 90);INSERT INTO `sc` VALUES (4, 1, 60);INSERT INTO `sc` VALUES (4, 2, 50);INSERT INTO `sc` VALUES (4, 3, 40);-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `Sno` int(255) NOT NULL COMMENT ‘学号‘, `Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘姓名‘, `Sage` int(255) NULL DEFAULT NULL COMMENT ‘年龄‘, `Ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘性别‘, PRIMARY KEY (`Sno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES (1, ‘张三丰‘, 108, ‘男‘);INSERT INTO `student` VALUES (2, ‘李小龙‘, 20, ‘男‘);INSERT INTO `student` VALUES (3, ‘小龙女‘, 17, ‘女‘);INSERT INTO `student` VALUES (4, ‘白发魔女‘, 18, ‘女‘);INSERT INTO `student` VALUES (5, ‘韦小宝‘, 19, ‘男‘);-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `Tno` int(11) NOT NULL COMMENT ‘老师编号‘, `Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘老师名称‘, PRIMARY KEY (`Tno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES (1, ‘无崖子‘);INSERT INTO `teacher` VALUES (2, ‘孤独求败‘);INSERT INTO `teacher` VALUES (3, ‘洪七公‘);SET FOREIGN_KEY_CHECKS = 1;
1、查询年龄小于18岁的学员信息
2、查询无崖子授课信息
3、查询没有参与任意课程的学生信息
4、查询无崖子每个授课课程的学员人数 统计 + 分组
5、查询张三丰数学成绩
6、查询出语文最高分【待完成】
7、查询没有参与语文考试的学生信息
8、查询语数外三门成绩的平均分
9、查询报名孤独求败老师课程的学生信息
10、没有报名孤独求败老师课程的学生信息
#1 查询年龄小于18岁的学员信息
select * from student where Sage<18;#2 查询无崖子授课信息select * from teacher t join course c on c.Tno = t.Tno where t.Tname="无崖子";#3 查询没有参与任意课程的学生信息select * from student s left join sc on s.Sno = sc.Sno where sc.score is null;#4 查询无崖子每个授课课程的学员人数 统计 + 分组 select t.Tname,c.Cname,count(1) as "学员人数" from teacher t join course c on t.Tno = c.Tno join sc on sc.Cno = c.Cno group by sc.Cno HAVING t.Tname="无崖子";#5 查询张三丰数学成绩select s.Sname,c.Cname,sc.score from student s join sc on s.Sno = sc.Sno join course c on c.Cno = sc.Cno where s.Sname="张三丰" and c.Cname="数学";#6 查询出语文最高分【待完成】select s.Sname,max(sc.score) as "分数" from sc join course c on sc.Cno = c.Cno join student s on s.Sno = sc.Sno where c.Cname="语文";#7 查询没有参与语文考试的学生信息select * from student s join sc on sc.Sno = s.Sno right join course c on c.Cno = sc.Cno and c.Cname = "语文" and sc.score is null;select * from course c join sc on c.Cno = sc.Cno and c.Cname="语文" right join student s on s.Sno = sc.Sno where sc.score is null;#8 查询语数外三门成绩的平均分 select c.Cname,avg(sc.score) from course c join sc on c.Cno = sc.Cno GROUP BY c.Cno;#9 查询报名孤独求败老师课程的学生信息select * from student s join sc on sc.Sno = s.Sno join course c on c.Cno= sc.Cno join teacher t on t.Tno = c.Tno where t.Tname="孤独求败" ;select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤独求败" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is not null;#10 没有报名孤独求败老师课程的学生信息select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤独求败" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is null;
