数据库（mysql）查询语句练习（高级）

观前提示

此查询练习的数据库来源于网上，可以自行百度！！！！
此次练习较有难度，请酌情考虑练习！！！！

数据库

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '三年二班');
INSERT INTO `class` VALUES ('2', '三年三班');
INSERT INTO `class` VALUES ('3', '一年二班');
INSERT INTO `class` VALUES ('4', '二年九班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '物理', '2');
INSERT INTO `course` VALUES ('3', '体育', '3');
INSERT INTO `course` VALUES ('4', '美术', '2');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '10');
INSERT INTO `score` VALUES ('2', '1', '2', '9');
INSERT INTO `score` VALUES ('5', '1', '4', '66');
INSERT INTO `score` VALUES ('6', '2', '1', '8');
INSERT INTO `score` VALUES ('8', '2', '3', '68');
INSERT INTO `score` VALUES ('9', '2', '4', '99');
INSERT INTO `score` VALUES ('10', '3', '1', '77');
INSERT INTO `score` VALUES ('11', '3', '2', '66');
INSERT INTO `score` VALUES ('12', '3', '3', '87');
INSERT INTO `score` VALUES ('13', '3', '4', '99');
INSERT INTO `score` VALUES ('14', '4', '1', '79');
INSERT INTO `score` VALUES ('15', '4', '2', '11');
INSERT INTO `score` VALUES ('16', '4', '3', '67');
INSERT INTO `score` VALUES ('17', '4', '4', '100');
INSERT INTO `score` VALUES ('18', '5', '1', '79');
INSERT INTO `score` VALUES ('19', '5', '2', '11');
INSERT INTO `score` VALUES ('20', '5', '3', '67');
INSERT INTO `score` VALUES ('21', '5', '4', '100');
INSERT INTO `score` VALUES ('22', '6', '1', '9');
INSERT INTO `score` VALUES ('23', '6', '2', '100');
INSERT INTO `score` VALUES ('24', '6', '3', '67');
INSERT INTO `score` VALUES ('25', '6', '4', '100');
INSERT INTO `score` VALUES ('26', '7', '1', '9');
INSERT INTO `score` VALUES ('27', '7', '2', '100');
INSERT INTO `score` VALUES ('28', '7', '3', '67');
INSERT INTO `score` VALUES ('29', '7', '4', '88');
INSERT INTO `score` VALUES ('30', '8', '1', '9');
INSERT INTO `score` VALUES ('31', '8', '2', '100');
INSERT INTO `score` VALUES ('32', '8', '3', '67');
INSERT INTO `score` VALUES ('33', '8', '4', '88');
INSERT INTO `score` VALUES ('34', '9', '1', '91');
INSERT INTO `score` VALUES ('35', '9', '2', '88');
INSERT INTO `score` VALUES ('36', '9', '3', '67');
INSERT INTO `score` VALUES ('37', '9', '4', '22');
INSERT INTO `score` VALUES ('38', '10', '1', '90');
INSERT INTO `score` VALUES ('39', '10', '2', '77');
INSERT INTO `score` VALUES ('40', '10', '3', '43');
INSERT INTO `score` VALUES ('41', '10', '4', '87');
INSERT INTO `score` VALUES ('42', '11', '1', '90');
INSERT INTO `score` VALUES ('43', '11', '2', '77');
INSERT INTO `score` VALUES ('44', '11', '3', '43');
INSERT INTO `score` VALUES ('45', '11', '4', '87');
INSERT INTO `score` VALUES ('46', '12', '1', '90');
INSERT INTO `score` VALUES ('47', '12', '2', '77');
INSERT INTO `score` VALUES ('48', '12', '3', '43');
INSERT INTO `score` VALUES ('49', '12', '4', '87');
INSERT INTO `score` VALUES ('52', '13', '3', '87');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', '理解');
INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋');
INSERT INTO `student` VALUES ('3', '男', '1', '张三');
INSERT INTO `student` VALUES ('4', '男', '1', '张一');
INSERT INTO `student` VALUES ('5', '女', '1', '张二');
INSERT INTO `student` VALUES ('6', '男', '1', '张四');
INSERT INTO `student` VALUES ('7', '女', '2', '铁锤');
INSERT INTO `student` VALUES ('8', '男', '2', '李三');
INSERT INTO `student` VALUES ('9', '男', '2', '李一');
INSERT INTO `student` VALUES ('10', '女', '2', '李二');
INSERT INTO `student` VALUES ('11', '男', '2', '李四');
INSERT INTO `student` VALUES ('12', '女', '3', '如花');
INSERT INTO `student` VALUES ('13', '男', '3', '刘三');
INSERT INTO `student` VALUES ('14', '男', '3', '刘一');
INSERT INTO `student` VALUES ('15', '女', '3', '刘二');
INSERT INTO `student` VALUES ('16', '男', '3', '刘四');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 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', '李杰老师');

题目

– 1、查询所有的课程的名称以及对应的任课老师

– 2、查询学生表中男女生各有多少人

– 3、查询物理成绩等于100的学生的姓名

– 4、查询平均成绩大于八十分的同学的姓名和平均成绩

– 5、查询所有学生的学号，姓名，选课数，总成绩

– 6、 查询姓李老师的个数

– 7、 查询没有报李平老师课的学生姓名

– 8、 查询物理课程比生物课程高的学生的学号

– 9、 查询没有同时选修物理课程和体育课程的学生姓名

– 10、查询挂科超过两门(包括两门)的学生姓名和班级

– 11、查询选修了所有课程的学生姓名

– 12、查询李平老师教的课程的所有成绩记录

– 13、查询全部学生都选修了的课程号和课程名

– 14、查询每门课程被选修的次数

– 15、查询只选修了一门课程的学生姓名和学号

– 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

– 17、查询平均成绩大于85的学生姓名和平均成绩

– 18、查询生物 成绩不及格 的学生姓名和对应生物分数

– 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩 最高的学生姓名

– 21、查询不同课程但成绩相同的学号，课程号，成绩

– 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；

– 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

解答

– 1、查询所有的课程的名称以及对应的任课老师
select c.cname,t.tname from course c join teacher t on c.teacher_id = t.tid;
select c.cname,t.tname from course c,teacher t where c.teacher_id = t.tid
– 2、查询学生表中男女生各有多少人
select gender,count() from student group by gender
– 3、查询物理成绩等于100的学生的姓名
select s.sname,c.cname,sc.num from student s join score sc on s.sid = sc.student_id join course c on sc.course_id = c.cid where c.cname = ‘物理’ and sc.num = 100
– 4、查询平均成绩大于八十分的同学的姓名和平均成绩
select s.sname,avg(sc.num) from score sc join student s on sc.student_id = s.sid group by student_id having avg(sc.num) > 80
– 5、查询所有学生的学号，姓名，选课数，总成绩
select s.sid,s.sname,count(sc.student_id),sum(sc.num) from student s JOIN score sc on s.sid = sc.student_id group by sc.student_id
– 6、 查询姓李老师的个数
select count() from teacher where tname like ‘李%’
– 7、 查询没有报李平老师课的学生姓名
– 第一步查询出报了李平老师课的学生编号，第二部取反
select sid,sname from student where sid not in (select DISTINCT s.sid from student s join score sc on s.sid = sc.student_id join course c on c.cid = sc.course_id join teacher t on t.tid = c.teacher_id where t.tname = ‘李平老师’)
– 8、 查询物理课程比生物课程高的学生的学号
select tab1.student_id from (
select * from score where course_id = (select cid from course where cname = ‘物理’)) tab1,
(select * from score where course_id = (select cid from course where cname = ‘生物’)) tab2
where tab1.num > tab2.num and tab1.student_id = tab2.student_id
– 9、 查询没有同时选修物理课程和体育课程的学生姓名
select s.sname from student s where s.sid not in(
select tab1.student_id from (
select * from score where course_id = (select cid from course where cname = ‘物理’)) tab1,
(select * from score where course_id = (select cid from course where cname = ‘体育’)) tab2
where tab1.student_id = tab2.student_id)
– 10、查询挂科超过两门(包括两门)的学生姓名和班级
select cl.caption,s.sname from student s join class cl on s.class_id = cl.cid where sid in(
select student_id from score where num < 60 GROUP BY student_id HAVING count(num) >= 2)
– 11、查询选修了所有课程的学生姓名
select sname from student where sid in(
select student_id from score group by student_id HAVING count(course_id) = (select count() from course))
– 12、查询李平老师教的课程的所有成绩记录
select student_id,num from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = ‘李平老师’)) order by student_id,num
– 13、查询全部学生都选修了的课程号和课程名
select cid,cname from course where cid in(select course_id from score group by course_id HAVING count(course_id) = (select count() from student))
– 14、查询每门课程被选修的次数
select c.cname,count(s.course_id) from course c join score s on s.course_id = c.cid GROUP BY s.course_id
– 15、查询只选修了一门课程的学生姓名和学号
select s.sname,s.sid from student s join score sc on s.sid = sc.student_id GROUP BY sc.student_id HAVING count(sc.course_id) = 1
– 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select DISTINCT s.sname,c.cname,sc.num from student s join score sc on s.sid = sc.student_id join course c on sc.course_id = c.cid ORDER BY s.sname,sc.num desc
– 17、查询平均成绩大于85的学生姓名和平均成绩
select s.sname,avg(sc.num) from student s join score sc on s.sid = sc.student_id GROUP BY sc.student_id having avg(sc.num) > 85
– 18、查询生物 成绩不及格 的学生姓名和对应生物分数
select s.sname,c.cname,sc.num from student s join score sc on s.sid = sc.student_id join course c on sc.course_id = c.cid where c.cname = ‘生物’ and sc.num < 60
– 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩 最高的学生姓名
select s.sname,avg(sc.num) from student s join score sc on s.sid = sc.student_id where sc.course_id in (select cid from course where teacher_id = (select tid from teacher where tname=‘李平老师’)) group by sc.student_id order by avg(sc.num) desc limit 0,1

select s.sname,avg(sc.num) from student s join score sc on s.sid = sc.student_id join course c on sc.course_id = c.cid where sc.course_id in(select c.cid from course c join teacher t on t.tid = c.teacher_id where t.tname = ‘李平老师’) group by sc.student_id order by avg(sc.num) desc limit 0,1

– 21、查询不同课程但成绩相同的学号，课程号，成绩
select DISTINCT sc.student_id, sc.course_id,sc.num from score sc join score sc2 on sc.student_id = sc2.student_id where sc.student_id = sc2.student_id and sc.num = sc2.num and sc.course_id != sc2.course_id
– 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；
select DISTINCT s.sname,c.cname from student s join score sc on s.sid = sc.student_id join course c on sc.course_id = c.cid where s.sid not in(select student_id from score where course_id in(select cid from course where teacher_id = (select tid from teacher where tname=‘李平老师’)))
– 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
select DISTINCT s.sid,s.sname from student s join score sc on s.sid = sc.student_id where sc.course_id in(select course_id from score where student_id = 1)