表结构如图所示
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 12/08/2020 21:26:48 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (1, '高三1班'); INSERT INTO `class` VALUES (2, '高三2班'); INSERT INTO `class` VALUES (3, '高三3班'); INSERT INTO `class` VALUES (4, '高三4班'); INSERT INTO `class` VALUES (5, '高三5班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `teacherid` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `fk_course_teacher`(`teacherid`) USING BTREE, CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacherid`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, '语文', 3); INSERT INTO `course` VALUES (2, '数学', 1); INSERT INTO `course` VALUES (3, '英语', 5); INSERT INTO `course` VALUES (4, '物理', 4); INSERT INTO `course` VALUES (5, '生物', 3); INSERT INTO `course` VALUES (6, '化学', 2); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studentid` int(11) NOT NULL, `courseid` int(11) NOT NULL, `grade` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `fk_score_student`(`studentid`) USING BTREE, INDEX `fk_score_course`(`courseid`) USING BTREE, CONSTRAINT `fk_score_course` FOREIGN KEY (`courseid`) REFERENCES `course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_score_student` FOREIGN KEY (`studentid`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 59 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 1, 5, 30); INSERT INTO `score` VALUES (2, 1, 4, 40); INSERT INTO `score` VALUES (3, 1, 1, 25); INSERT INTO `score` VALUES (4, 1, 3, 68); INSERT INTO `score` VALUES (5, 2, 4, 66); INSERT INTO `score` VALUES (6, 2, 1, 35); INSERT INTO `score` VALUES (7, 3, 6, 65); INSERT INTO `score` VALUES (8, 3, 3, 68); INSERT INTO `score` VALUES (9, 3, 5, 64); INSERT INTO `score` VALUES (10, 4, 1, 77); INSERT INTO `score` VALUES (11, 4, 6, 66); INSERT INTO `score` VALUES (12, 4, 3, 87); INSERT INTO `score` VALUES (13, 4, 4, 99); INSERT INTO `score` VALUES (14, 4, 2, 79); INSERT INTO `score` VALUES (15, 5, 2, 45); INSERT INTO `score` VALUES (16, 5, 3, 67); INSERT INTO `score` VALUES (17, 6, 4, 82); INSERT INTO `score` VALUES (18, 6, 1, 79); INSERT INTO `score` VALUES (19, 6, 2, 29); INSERT INTO `score` VALUES (20, 7, 3, 67); INSERT INTO `score` VALUES (21, 7, 4, 100); INSERT INTO `score` VALUES (22, 8, 1, 37); INSERT INTO `score` VALUES (23, 8, 2, 98); INSERT INTO `score` VALUES (24, 8, 3, 67); INSERT INTO `score` VALUES (25, 9, 4, 89); INSERT INTO `score` VALUES (26, 9, 1, 56); INSERT INTO `score` VALUES (27, 10, 2, 77); INSERT INTO `score` VALUES (28, 11, 3, 67); INSERT INTO `score` VALUES (29, 11, 2, 88); INSERT INTO `score` VALUES (30, 10, 6, 41); INSERT INTO `score` VALUES (31, 12, 2, 100); INSERT INTO `score` VALUES (32, 14, 3, 67); INSERT INTO `score` VALUES (33, 16, 4, 88); INSERT INTO `score` VALUES (34, 10, 5, 91); INSERT INTO `score` VALUES (35, 20, 2, 88); INSERT INTO `score` VALUES (36, 15, 3, 67); INSERT INTO `score` VALUES (37, 11, 4, 22); INSERT INTO `score` VALUES (38, 10, 1, 82); INSERT INTO `score` VALUES (39, 10, 3, 77); INSERT INTO `score` VALUES (40, 14, 1, 43); INSERT INTO `score` VALUES (41, 16, 4, 87); INSERT INTO `score` VALUES (42, 13, 1, 93); INSERT INTO `score` VALUES (43, 17, 2, 77); INSERT INTO `score` VALUES (44, 19, 3, 43); INSERT INTO `score` VALUES (45, 20, 4, 87); INSERT INTO `score` VALUES (46, 16, 1, 90); INSERT INTO `score` VALUES (47, 18, 2, 77); INSERT INTO `score` VALUES (48, 13, 3, 43); INSERT INTO `score` VALUES (49, 12, 6, 73); INSERT INTO `score` VALUES (50, 14, 5, 87); INSERT INTO `score` VALUES (51, 14, 3, 60); INSERT INTO `score` VALUES (52, 12, 3, 100); INSERT INTO `score` VALUES (53, 7, 5, 67); INSERT INTO `score` VALUES (54, 4, 5, 50); INSERT INTO `score` VALUES (55, 8, 6, 80); INSERT INTO `score` VALUES (56, 8, 5, 70); INSERT INTO `score` VALUES (57, 8, 4, 75); INSERT INTO `score` VALUES (58, 10, 4, 86); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `class_id` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `fk_class`(`class_id`) USING BTREE, CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '张瑞政', '男', 1); INSERT INTO `student` VALUES (2, '杨思琦', '女', 2); INSERT INTO `student` VALUES (3, '郑大同', '男', 4); INSERT INTO `student` VALUES (4, '何香凝', '女', 3); INSERT INTO `student` VALUES (5, '李嘉欣', '男', 2); INSERT INTO `student` VALUES (6, '邓丽欣', '男', 1); INSERT INTO `student` VALUES (7, '张雨绮', '女', 3); INSERT INTO `student` VALUES (8, '张素晗', '男', 1); INSERT INTO `student` VALUES (9, '张云川', '男', 4); INSERT INTO `student` VALUES (10, '何其芳', '女', 2); INSERT INTO `student` VALUES (11, '许本谦', '男', 1); INSERT INTO `student` VALUES (12, '何家产', '男', 3); INSERT INTO `student` VALUES (13, '马季', '男', 2); INSERT INTO `student` VALUES (14, '李心洁', '女', 4); INSERT INTO `student` VALUES (15, '许椿生', '男', 3); INSERT INTO `student` VALUES (16, '马朴', '男', 2); INSERT INTO `student` VALUES (17, '何休', '男', 1); INSERT INTO `student` VALUES (18, '吴西', '女', 4); INSERT INTO `student` VALUES (19, '邓丽欣', '女', 3); INSERT INTO `student` VALUES (20, '杨采妮', '女', 4); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (1, '蔡文浩老师'); INSERT INTO `teacher` VALUES (2, '宋洁涵老师'); INSERT INTO `teacher` VALUES (3, '唐诗咏老师'); INSERT INTO `teacher` VALUES (4, '余景生老师'); INSERT INTO `teacher` VALUES (5, '冯英子老师'); SET FOREIGN_KEY_CHECKS = 1;
练习题及答案（使用Navicat操作）
#表course中老师任课的课数是多少 select teacherid,count(1) from course group by teacherid; #表student中男女个数分别是多少 select gender,count(id) from student group by gender; #临时表操作 select studentid,grade from (select * from score where grade>60) as B; #查询平均成绩大于60分的同学的"学号"和"平均成绩" select studentid,avg(grade) from score group by studentid having avg(grade)>60; #查询平均成绩大于60分的同学的"名字"和"平均成绩" SELECT student.name "姓名", AVG(grade) "平均成绩" FROM score INNER JOIN student ON student.id=score.studentid GROUP BY studentid HAVING AVG(grade)>60; #查询所有同学的学号、姓名、选课数、总成绩 SELECT studentid "学号", student.name "姓名", COUNT(1) "选课数", SUM(grade) "总成绩" FROM score INNER JOIN student ON student.id=score.studentid GROUP BY studentid; #查询没学过"冯英子"老师的课的同学的学号、姓名 "冯英子"老师任教的课程的id select course.id from course left join teacher on course.teacherid=teacher.id where teacher.name = "冯英子老师" 学过"冯英子"老师的课的所有同学的id select studentid from score where courseid in (select course.id from course left join teacher on course.teacherid=teacher.id where teacher.name = "冯英子老师") group by studentid; 最终结果 select student.id,student.name from student where id not in (select studentid from score where courseid in (select course.id from course left join teacher on course.teacherid=teacher.id where teacher.name = "冯英子老师") group by studentid); #查询"生物"课程比"物理"课程成绩低的所有学生的学号 select A.studentid from (select score.studentid,course.subject,score.grade from score left join course on score.courseid=course.id where course.subject="生物") as A inner join (select score.studentid,course.subject,score.grade from score left join course on score.courseid=course.id where course.subject="物理") as B on A.studentid=B.studentid where A.grade<B.grade; #查询学过编号"1"并且也学过编号"2"课程的同学的学号、姓名 select score.studentid,student.name from score left join student on score.studentid=student.id where score.courseid=1 or score.courseid=2 group by score.studentid having count(1)>1; #查询学过"唐诗咏"老师所教的所有课的同学的学号、姓名 select studentid from score where courseid in (select course.id from course left join teacher on course.teacherid=teacher.id where teacher.name="唐诗咏老师") group by studentid having count(courseid)=( select count(1) from course left join teacher on course.teacherid=teacher.id where teacher.name="唐诗咏老师") #查询学过"唐诗咏"老师所教的所有课的同学的学号、姓名 select student.id,student.name from (select studentid from score where courseid in (select course.id from course left join teacher on course.teacherid=teacher.id where teacher.name="唐诗咏老师") group by studentid having count(courseid)=( select count(1) from course left join teacher on course.teacherid=teacher.id where teacher.name="唐诗咏老师")) as B left join student on B.studentid=student.id; #查询没有学全所有课的同学的学号、姓名 select student.id,student.name from (select studentid,count(id) from score group by studentid having count(id)<(select count(id) from course)) as B left join student on B.studentid=student.id; #查询至少有一门课与学号为"3"的同学所学相同的同学的学号和姓名
SELECT sc.studentid,st.name FROM score sc INNER JOIN
student st
ON sc.studentid=st.id WHERE sc.courseid in
(SELECT sc.courseid FROM score sc WHERE sc.studentid=3) AND st.id!=3 GROUP BY st.id;
#查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 select courseid,max(grade),min(grade) from score group by courseid; #查询各科成绩最高和最低的分（如果分数小于30则显示0）：以如下形式显示：课程ID，最高分，最低分
SELECT sc.courseid "课程ID", CASE
WHEN MAX(sc.grade)<30 THEN
0
ELSE
MAX(sc.grade)
END "最高分",
CASE
WHEN MIN(sc.grade)<30 THEN
0
ELSE
MIN(sc.grade)
END "最低分" FROM score sc GROUP BY sc.courseid;
#按各科平均成绩从低到高排序及其及格率为多少 步骤1 select studentid,courseid,grade,case when grade<60 then 0 else 1 end from score; 步骤2 select studentid,courseid,grade,case when grade<60 then 0 else 1 end,1 from score; 步骤3 select courseid,avg(grade),sum(case when grade<60 then 0 else 1 end),sum(1) from score group by score.courseid; 最终结果：
select courseid "课程id",
avg(grade) "平均成绩",
sum(case when grade<60 then 0 else 1 end)/sum(1) "及格率"
from score group by score.courseid order by avg(grade) asc;
#课程平均分从高到低显示（显示任课老师）
SELECT te.name "任课老师", AVG(sc.grade) "平均成绩" FROM score sc INNER JOIN course co ON sc.courseid=co.id
INNER JOIN teacher te ON co.teacherid=te.id
GROUP BY sc.courseid ORDER BY AVG(sc.grade) DESC;
#查询每门课程成绩最好的前两名
SELECT a.* FROM score a WHERE 2 >
(SELECT COUNT(1) FROM score b
WHERE a.courseid=b.courseid AND b.grade>a.grade)
ORDER BY courseid, grade DESC;
#查询每门课程被选修的学生数 select courseid,count(1) from score group by courseid; #查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT st.name "姓名", sc.grade "分数" FROM score sc
INNER JOIN student st ON sc.studentid=st.id
INNER JOIN course co ON sc.courseid=co.id
WHERE sc.grade<60 AND co.subject="数学";