前置 sql 语句
用来创建表、插入数据
代码语言：javascript复制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、没有报名孤独求败老师课程的学生信息
答案列表
代码语言：javascript复制#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;
