准备工作:建表
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,
`cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tno` int(11) NULL DEFAULT NULL,
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, 'Java基础', 1);
INSERT INTO `course` VALUES (2, 'Java高级', 1);
INSERT INTO `course` VALUES (3, 'C#', 2);
INSERT INTO `course` VALUES (4, '计算机网络', 2);
INSERT INTO `course` VALUES (5, '算法与结构', 4);
INSERT INTO `course` VALUES (6, '数字模拟电子', 3);
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sno` int(11) NOT NULL,
`cno` int(11) NULL DEFAULT NULL,
`score` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1001, 1, 90);
INSERT INTO `sc` VALUES (1001, 2, 80);
INSERT INTO `sc` VALUES (1001, 3, 60);
INSERT INTO `sc` VALUES (1002, 1, 56);
INSERT INTO `sc` VALUES (1002, 2, 90);
INSERT INTO `sc` VALUES (1002, 4, 66);
INSERT INTO `sc` VALUES (1003, 1, 90);
INSERT INTO `sc` VALUES (1003, 2, 70);
INSERT INTO `sc` VALUES (1003, 4, 60);
INSERT INTO `sc` VALUES (1003, 3, 80);
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`score` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('张三', '语文', 81);
INSERT INTO `score` VALUES ('张三', '数学', 80);
INSERT INTO `score` VALUES ('李四', '语文', 76);
INSERT INTO `score` VALUES ('王五', '语文', 81);
INSERT INTO `score` VALUES ('王五', '数学', 100);
INSERT INTO `score` VALUES ('王五', '英语', 90);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` int(11) NOT NULL,
`sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sage` int(255) NULL DEFAULT NULL,
`ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1001, 'tom', 22, '男');
INSERT INTO `student` VALUES (1002, 'jack', 23, '男');
INSERT INTO `student` VALUES (1003, 'rose', 24, '女');
-- ----------------------------
-- Table structure for student1
-- ----------------------------
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student1
-- ----------------------------
INSERT INTO `student1` VALUES (1, '张三');
INSERT INTO `student1` VALUES (2, '张三');
INSERT INTO `student1` VALUES (3, '李四');
INSERT INTO `student1` VALUES (4, '王五');
INSERT INTO `student1` VALUES (5, '王五');
INSERT INTO `student1` VALUES (6, '王五');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` int(11) NOT NULL,
`tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
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, '王强');
INSERT INTO `teacher` VALUES (4, '李晓燕');
SET FOREIGN_KEY_CHECKS = 1;
练习题:
1.查询课程1的成绩比课程2的成绩高的所有学生的信息
考点:表的自链接查询
思路:将sc表当成2份,一份视为课程1的成绩表,一份视为课程2的成绩表,两表连接,连接条件为学号相等,且cno=1表中的成绩大于cno=2表中的成绩,得到的学号即为查询的学号,最后和学生表联查,获取对应的学生信息即可。
SELECT s.*,sc1.score '课程1',sc2.score '课程2' FROM sc sc1 JOIN sc sc2 JOIN student s ON sc1.sno=sc2.sno AND
sc1.score>sc2.score AND sc1.sno=s.sno WHERE sc1.cno=1 AND sc2.cno=2
2.查询平均成绩大于60分的同学的学号和平均成绩
select sno,avg(score ) av from sc group by sno having av>60
3.查询学过'李四'老师所教课程的所有同学的学号丶姓名
注意: 需要对结果集去重操作
SELECT DISTINCT s.sno '学号',s.sname '姓名' FROM sc JOIN student s ON sc.sno=s.sno WHERE
sc.cno
IN (
SELECT c.cno
FROM course c JOIN teacher t ON c.tno=t.tno WHERE t.tname='李四'
)
4.查询每门课程的选修人数(课程名称,学生数量)---存在没有人选的课程
SELECT c.cno,cname,COUNT(sno) FROM course
c LEFT JOIN sc ON c.cno=sc.cno GROUP BY c.cno
5.删除学号为"1002"的同学的'1'课程的成绩
delete from sc where sno='1002' and cno=1
6.查询选修人数最多的课程(课程id,课程名称,学生数量)--考虑有多门课程都是选修最多的情况
分析:统计每门课程的选秀人数,选秀人数最多的是多少
获取选修人数=以上查询结果人数的课程
SELECT c.cno,cname,COUNT(sno) cou FROM sc JOIN course c ON
sc.cno=c.cno GROUP BY sc.cno HAVING
cou=(SELECT COUNT(sno) cou FROM sc GROUP BY cno ORDER BY cou DESC LIMIT 1)