MySQL练习题及答案（复习）
新建一个叫做 review 的数据库，将测试数据脚本导进去。（可以使用Navicat查询功能）
/* Navicat MySQL Data Transfer Source Server : DB Source Server Version : 50723 Source Host : localhost:3306 Source Database : review Target Server Type : MYSQL Target Server Version : 50723 File Encoding : 65001 Date: 2019-02-25 23:48:25 */ 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` char(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('1', '高三1班'); INSERT INTO `class` VALUES ('2', '高三2班'); INSERT INTO `class` VALUES ('3', '高三3班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(8) NOT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `fk_cou_tea` (`teacher_id`), CONSTRAINT `fk_cou_tea` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', 'python', '1'); INSERT INTO `course` VALUES ('2', 'java', '2'); INSERT INTO `course` VALUES ('3', 'php', '3'); INSERT INTO `course` VALUES ('4', 'c', '1'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) DEFAULT NULL, `course_id` int(11) DEFAULT NULL, `mark` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_sco_stu` (`student_id`), KEY `fk_sco_cou` (`course_id`), CONSTRAINT `fk_sco_cou` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`), CONSTRAINT `fk_sco_stu` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '2', '79'); INSERT INTO `score` VALUES ('2', '2', '1', '58'); INSERT INTO `score` VALUES ('3', '2', '3', '66'); INSERT INTO `score` VALUES ('4', '2', '4', '80'); INSERT INTO `score` VALUES ('5', '3', '1', '63'); INSERT INTO `score` VALUES ('6', '3', '4', '95'); INSERT INTO `score` VALUES ('7', '4', '2', '88'); INSERT INTO `score` VALUES ('8', '4', '3', '62'); INSERT INTO `score` VALUES ('9', '5', '2', '59'); INSERT INTO `score` VALUES ('10', '5', '4', '100'); INSERT INTO `score` VALUES ('11', '1', '1', '55'); INSERT INTO `score` VALUES ('12', '3', '2', '81'); INSERT INTO `score` VALUES ('13', '4', '4', '50'); INSERT INTO `score` VALUES ('14', '5', '3', '77'); INSERT INTO `score` VALUES ('15', '1', '4', '58'); INSERT INTO `score` VALUES ('16', '1', '3', '91'); INSERT INTO `score` VALUES ('17', '6', '2', '75'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(2) NOT NULL, `gender` char(1) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_cou_cla` (`class_id`), CONSTRAINT `fk_cou_cla` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '德玛', '男', '1'); INSERT INTO `student` VALUES ('2', '妖姬', '女', '2'); INSERT INTO `student` VALUES ('3', '盲僧', '男', '3'); INSERT INTO `student` VALUES ('4', '蜘蛛', '女', '1'); INSERT INTO `student` VALUES ('5', '卡牌', '男', '2'); INSERT INTO `student` VALUES ('6', '露露', '女', '3'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '佩奇'); INSERT INTO `teacher` VALUES ('2', '大熊'); INSERT INTO `teacher` VALUES ('3', '路飞');
数据表结构如下图：
练习题及答案如下：
-- 1、查询所有的课程的名称以及对应的任课老师姓名 select course.name as "课程",teacher.name as "任课老师" from course left join teacher on course.teacher_id=teacher.id; ------------------------------------------------------------ -- 2、查询学习课程"python"比课程"java"成绩低的学生的学号 select python.student_id from (select score.student_id,course.name,score.mark from score inner join course on score.course_id=course.id where course.name="python") as python inner JOIN (select score.student_id,course.name,score.mark from score inner join course on score.course_id=course.id where course.name="java") as java on python.student_id=java.student_id where python.mark<java.mark; ------------------------------------------------------------ -- 3、查询平均成绩大于65分的同学的id和平均成绩(保留两位小数) select student_id,round(avg(mark),2) as m from score group by student_id having m>65; ------------------------------------------------------------ -- 4、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数); select student.name,round(avg(mark),2) as m from score inner join student on score.student_id=student.id group by student_id having m>65; ------------------------------------------------------------ -- 5、查询所有同学的姓名、选课数、总成绩 select student.name,count(score.course_id) as "选课数",sum(score.mark) as "总成绩" from score inner join student on score.student_id=student.id group by student_id; ------------------------------------------------------------ -- 6、查询没学过"路飞"老师课的同学的姓名 # 1)"路飞"老师任课的课程id select course.id from course inner join teacher on course.teacher_id=teacher.id where teacher.name="路飞" # 2)学过"路飞"老师的课的学生id select score.student_id from score where score.course_id in (select course.id from course inner join teacher on course.teacher_id=teacher.id where teacher.name="路飞") group by score.student_id # 3)最终结果 select student.name from student where id not in (select score.student_id from score where score.course_id in (select course.id from course inner join teacher on course.teacher_id=teacher.id where teacher.name="路飞") group by score.student_id); ------------------------------------------------------------ -- 7、查询学过"python"并且也学过"java"课程的同学的姓名 select student.name from score left join student on score.student_id=student.id where score.course_id=(select id from course where name="python") or score.course_id=(select id from course where name="java") group by score.student_id having count(1)>1; ------------------------------------------------------------ -- 8、查询学过"路飞"老师所教的全部课程的同学的姓名 select student.name from score inner join student on score.student_id=student.id where score.course_id in (select course.id from course inner join teacher on course.teacher_id=teacher.id where teacher.name="路飞") group by score.student_id; -- 9、查询有课程成绩小于60分的同学的姓名 select name from student where id in (select student_id from score where mark<60 group by student_id); ------------------------------------------------------------ -- 10、查询挂科超过两门(包括两门)的学生姓名 select name from student where id in (select student_id from score where mark<60 group by student_id having count(1)>=2); ------------------------------------------------------------ -- 11、查询选修了全部课程的学生姓名 select name from student where id in (select student_id from score group by student_id having count(1)=(select count(1) from course)); ------------------------------------------------------------ -- 12、查询至少有一门课程与"卡牌"同学所学课程相同的同学姓名 select name from student where id in (select student_id from score where course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="卡牌") group by student_id) and name!="卡牌"; ------------------------------------------------------------ -- 13、查询学过"蜘蛛"同学全部课程的其他同学姓名 # 1)"蜘蛛"同学学过的课程id select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛" # 2)score表连student表，并筛选出课程id在（"蜘蛛"同学学过的课程id），并且学生!="蜘蛛" select student.name from score inner join student on score.student_id=student.id where score.course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛") and student.name!="蜘蛛" # 3)接着对结果进行分组，再次筛选得到最终结果 select student.name from score inner join student on score.student_id=student.id where score.course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛") and student.name!="蜘蛛" group by student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛"); ------------------------------------------------------------ -- 14、查询和"蜘蛛"同学学习的课程完全相同的其他同学姓名； # 1)找出与"蜘蛛"学习课程数相同的学生id(你学两门，我也学两门) select * from score where score.student_id in (select student_id from score group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛")) # 2)找出学过"蜘蛛"课程的学生，剩下的一定是至少学过一门"蜘蛛"课程的学生 select * from score where score.student_id in (select student_id from score group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛")) and score.course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛") # 3)根据学生id进行分组，剩下学生数count(1)=貂蝉学生所学课程数 select student_id from score where score.student_id in (select student_id from score group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛")) and score.course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛") group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛") and score.student_id!=(select id from student where name="蜘蛛") # 4)最终结果 select name from student where id in (select student_id from score where score.student_id in (select student_id from score group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛")) and score.course_id in (select course_id from score inner join student on score.student_id=student.id where student.name="蜘蛛") group by score.student_id having count(1)=(select count(1) from score inner join student on score.student_id=student.id where student.name="蜘蛛") and score.student_id!=(select id from student where name="蜘蛛")); ------------------------------------------------------------ -- 15、按平均成绩倒序显示所有学生的"python"、"java"、"php"三门的课程成绩，按如下形式显示： 学生id,python,java,php,课程数,平均分 # 1)先查询单一学生的python课程分数 select mark from score left join course on score.course_id = course.id where course.name = "python" and score.student_id=1; # 2)将上面查询的结果作为列字段使用，得到最终结果 select student_id, (select mark from score left join course on score.course_id = course.id where course.name = "python" and score.student_id=sc.student_id) as "python", (select mark from score left join course on score.course_id = course.id where course.name = "java" and score.student_id=sc.student_id) as "java", (select mark from score left join course on score.course_id = course.id where course.name = "php" and score.student_id=sc.student_id) as "php", count(course_id) as "课程数", avg(mark) as "平均分" from score as sc group by student_id order by avg(mark) desc; ------------------------------------------------------------ -- 16、查询各科成绩最高和最低的分：以如下形式显示：课程id，最高分，最低分 select course_id,max(mark) as "最高分",min(mark) as "最低分" from score group by course_id; ------------------------------------------------------------ -- 17、统计各科各分数段人数，显示格式：课程id,课程名称,[100-85],[85-70],[70-60],[<60] select course_id,course.name, sum(case when mark between 85 and 100 then 1 else 0 end) as "[100-85]", sum(case when mark between 70 and 85 then 1 else 0 end) as "[85-70]", sum(case when mark between 60 and 70 then 1 else 0 end) as "[70-60]", sum(case when mark < 60 then 1 else 0 end) as "[<60]" from score inner join course on score.course_id=course.id GROUP BY score.course_id; ------------------------------------------------------------ -- 18、查询每门课程名字及其被选修的次数 select course.name,count(1) from score inner join course on score.course_id=course.id group by course_id; ------------------------------------------------------------ -- 19、查询只选修了一门课程的学生的学号和姓名 select student_id,student.name from score inner join student on score.student_id=student.id group by student_id having count(course_id)=1; ------------------------------------------------------------ -- 20、查询学生表中男生、女生各有多少人 select sum(case when gender="男" then 1 else 0 end) as "男生", sum(case when gender="女" then 1 else 0 end) as "女生" from student ------------------------------------------------------------ -- 21、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程id降序排列 select course.name,avg(mark) from score inner join course on score.course_id=course.id group by course_id order by avg(mark) asc,course_id desc; ------------------------------------------------------------ -- 22、查询课程名称为"python"且分数低于60的学生姓名和分数 select student.name,mark from score inner join student on score.student_id=student.id where mark<60 and course_id=(select id from course where name="python"); ------------------------------------------------------------ -- 23、求选了课程的学生人数 # 方式一： select count(distinct student_id) from score; # 方式二： select count(1) from (select count(student_id) from score group by student_id) as a;
