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班');

INSERT INTO `class` VALUES ('', '高三2班');

INSERT INTO `class` VALUES ('', '高三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 ('', 'python', '');

INSERT INTO `course` VALUES ('', 'java', '');

INSERT INTO `course` VALUES ('', 'php', '');

INSERT INTO `course` VALUES ('', 'c', '');

-- ----------------------------

-- 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 ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

INSERT INTO `score` VALUES ('', '', '', '');

-- ----------------------------

-- 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 ('', '德玛', '男', '');

INSERT INTO `student` VALUES ('', '妖姬', '女', '');

INSERT INTO `student` VALUES ('', '盲僧', '男', '');

INSERT INTO `student` VALUES ('', '蜘蛛', '女', '');

INSERT INTO `student` VALUES ('', '卡牌', '男', '');

INSERT INTO `student` VALUES ('', '露露', '女', '');

-- ----------------------------

-- 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 ('', '佩奇');

INSERT INTO `teacher` VALUES ('', '大熊');

INSERT INTO `teacher` VALUES ('', '路飞');

测试数据脚本

数据表结构如下图：

练习题及答案如下：

-- 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;