MySQL 测试题
一、 表关系：
请创建如下表，并创建相关约束
创建表sql如下：
/* Navicat MySQL Data Transfer Source Server : 192.168.118.14 Source Server Version : 50544 Source Host : 192.168.118.14:3306 Source Database : db3 Target Server Type : MYSQL Target Server Version : 50544 File Encoding : 65001 Date: 2019-02-15 16:34:46 */ 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) DEFAULT NULL, PRIMARY KEY (`cid`), UNIQUE KEY `caption` (`caption`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('3', '一年二班'); INSERT INTO `class` VALUES ('2', '三年三班'); INSERT INTO `class` VALUES ('1', '三年二班'); 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, `number` int(11) NOT NULL, PRIMARY KEY (`sid`), UNIQUE KEY `student_id` (`student_id`,`course_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=66 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'); INSERT INTO `score` VALUES ('58', '1', '3', '44'); INSERT INTO `score` VALUES ('59', '13', '1', '53'); INSERT INTO `score` VALUES ('60', '14', '1', '53'); INSERT INTO `score` VALUES ('61', '15', '1', '53'); INSERT INTO `score` VALUES ('62', '16', '1', '53'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` varchar(2) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_student_class` (`class_id`), CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=18 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', '刘四'); INSERT INTO `student` VALUES ('17', '女', '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=6 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. 查询“生物”课程比“物理”课程成绩高的所有学生的学号
select A.student_id, sw, wl from (SELECT score.student_id, number as sw from score LEFT JOIN course on score.course_id = course.cid where course.cname = '生物') as A LEFT JOIN (SELECT score.student_id, number as wl from score LEFT JOIN course on score.course_id = course.cid where course.cname = '物理') as B on A.student_id = B.student_id where sw > wl
2. 查询平均成绩大于60分的同学的学号和平均成绩
SELECT student_id, avg(number) from score GROUP BY score.student_id HAVING avg(number) > 60
3. 查询所有同学的学号、姓名、选课数、总成绩
SELECT score.student_id, student.sname, count(score.course_id), sum(number) from score LEFT JOIN student on score.student_id = student.sid GROUP BY score.student_id
4. 查询姓“李”的老师的个数
SELECT count(1) from teacher where teacher.tname like '李%'
5. 查询没学过“李平老师”课的同学的学号、姓名
SELECT sid, sname from student where student.sid not in ( SELECT DISTINCT score.student_id from score where score.course_id in ( SELECT course.cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师'))
6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECT sid, sname from student where student.sid in ( SELECT score.student_id from score where score.course_id = 1 or score.course_id = 2 GROUP BY score.student_id HAVING count(1) > 1);
7. 查询学过“李平老师”老师所教的所有课的同学的学号、姓名
SELECT sid, sname from student where student.sid in ( SELECT score.student_id from score where score.course_id in (SELECT cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师') GROUP BY score.student_id HAVING count(1) = (SELECT count(1) from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师'))
8. 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
SELECT sid, sname from student where student.sid in ( select A.student_id from (select student_id, number as num_2 from score where score.course_id = 2) as A LEFT JOIN (select student_id, number as num_1 from score where score.course_id = 1) as B on A.student_id = B.student_id where A.num_2 < B.num_1)
9. 查询有课程成绩小于60分的同学的学号、姓名
SELECT DISTINCT student.sid, student.sname from score LEFT JOIN student on score.student_id = student.sid where score.number < 60;
10. 查询没有学全所有课的同学的学号、姓名
SELECT sid, sname from student where sid in ( SELECT score.student_id from score GROUP BY student_id HAVING count(1) != (SELECT count(1) from course))
11. 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
SELECT sid, sname from student where sid in ( SELECT DISTINCT student_id from score where score.course_id in (SELECT course_id from score where student_id = 1)) and sid != 1;
12. 查询至少学过学号为“1”同学所有课的其他同学学号和姓名 *****
SELECT sid, sname from student where sid in ( SELECT student_id from score where score.course_id in (SELECT course_id from score WHERE score.student_id =1 ) GROUP BY student_id HAVING count(1) = (SELECT count(1) from score WHERE score.student_id =1))
13. 查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT student.sid, sname from score LEFT JOIN student on score.student_id = student.sid where score.student_id in ( SELECT score.student_id from score where score.student_id != 1 GROUP BY student_id HAVING count(1) = (SELECT count(1) from score where score.student_id = 1) and course_id in (SELECT course_id from score where score.course_id in (SELECT course_id from score where score.student_id = 1) GROUP BY student_id HAVING count(1) = (SELECT count(1) from score where score.student_id = 1) ))
14. 删除学习“叶平”老师课的score表记录
DELETE from score where course_id in ( SELECT course.cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师');
15. 向 score 表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“001”课程的同学学号；②插入“001”号课程的平均成绩
INSERT into score (student_id, course_id, number) SELECT sid, 1, (SELECT avg(number) from score where score.course_id = 1) from student where student.sid not in (select student_id from score where score.course_id = 1)
16. 按平均成绩从低到高 显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物、物理、体育,有效课程数,有效平均分
SELECT sc.student_id, (SELECT avg(number) from score LEFT JOIN course on score.course_id = course.cid where score.student_id = sc.student_id and course.cname = '生物') as sw, (SELECT avg(number) from score LEFT JOIN course on score.course_id = course.cid where score.student_id = sc.student_id and course.cname = '物理') as wl, (SELECT avg(number) from score LEFT JOIN course on score.course_id = course.cid where score.student_id = sc.student_id and course.cname = '体育') as ty, count(sc.course_id), avg(sc.number) as avg_n from score sc GROUP BY sc.student_id ORDER BY avg_n asc;
17. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select course_id, max(number), min(number) from score GROUP BY course_id
18. 按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT score.course_id, avg(number) as avg_n, (sum(case when score.number > 60 then 1 else 0 end) / count(1) * 100) as percent from score GROUP BY score.course_id ORDER BY avg_n asc, percent desc;
19. 课程平均分从高到低显示（现实任课老师）
SELECT score.course_id, avg(score.number) as avg_n, teacher.tname from score LEFT JOIN course on score.course_id = course.cid LEFT JOIN teacher on course.teacher_id = teacher.tid GROUP BY score.course_id ORDER BY avg_n desc;
20. 查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT sc.course_id, (SELECT number from score where score.course_id = sc.course_id ORDER BY number desc limit 0,1) as first_num, (SELECT number from score where score.course_id = sc.course_id GROUP BY number ORDER BY number desc limit 1,1) as second_num, (SELECT number from score where score.course_id = sc.course_id GROUP BY number ORDER BY number desc limit 2,1) as third_num from score sc GROUP BY sc.course_id
21. 查询每门课程被选修的学生数
SELECT course_id, count(student_id) from score GROUP BY course_id
22. 查询出只选修了一门课程的全部学生的学号和姓名
SELECT student.sid, student.sname from score LEFT JOIN student on score.student_id = student.sid GROUP BY student_id HAVING count(score.course_id) =1 ;
23. 查询男生、女生的人数
SELECT * from (SELECT count(1) as boy from student where student.gender = '男') as A, (SELECT count(1) as girl from student where student.gender = '女') as B
24. 查询姓“张”的学生名单
SELECT * from student where student.sname like '张%'
25. 查询同名同姓学生名单，并统计同名人数
SELECT sname, count(sname) from student GROUP BY sname HAVING count(1) > 1;
26. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT course_id, avg(number) as avg_n from score GROUP BY course_id ORDER BY avg_n asc, course_id desc;
27. 查询平均成绩大于80的所有学生的学号、姓名和平均成绩
SELECT score.student_id, student.sname, avg(number) from score LEFT JOIN student on score.student_id = student.sid GROUP BY student_id HAVING avg(number) > 80;
28. 查询课程名称为“生物”，且分数低于60的学生姓名和分数
SELECT * from score LEFT JOIN student on score.student_id = student.sid where score.number < 60 and score.course_id in (SELECT cid from course where course.cname = '生物')
29. 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
SELECT student.sid, student.sname from score LEFT JOIN student on score.student_id = student.sid where score.course_id = 3 and score.number > 80;
30. 求选了课程的学生人数
SELECT count(DISTINCT student_id) from score where score.course_id is not null;
31. 查询选修 刘海燕老师 所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT student.sname, max(score.number) from score LEFT JOIN student on score.student_id = student.sid where score.course_id in (SELECT cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '刘海燕老师')
32. 查询各个课程及相应的选修人数
SELECT course_id, count(1) from score GROUP BY course_id
33. 查询不同课程但成绩相同的学生的学号、课程号、学生成绩
SELECT DISTINCT s1.course_id, s2.course_id, s1.number, s2.number from score as s1, score as s2 where s1.number = s2.number and s1.course_id != s2.course_id order by s1.course_id
34. 查询每门课程成绩最好的前两名
SELECT sc.course_id, (SELECT number as f_n from score where course_id = sc.course_id ORDER BY number desc limit 0, 1) as first_num, (SELECT number as f_n from score where course_id = sc.course_id GROUP BY number ORDER BY number desc limit 1, 1) as second_num from score sc GROUP BY sc.course_id
35. 检索至少选修两门课程的学生学号
SELECT student_id, count(1) from score GROUP BY student_id HAVING count(1) > 1;
36. 查询全部学生都选修的课程的课程号和课程名
SELECT score.course_id, course.cname from score LEFT JOIN course on score.course_id = course.cid GROUP BY course_id HAVING count(1) = (SELECT count(1) from student)
37. 查询没学过 李平老师 讲授的任一门课程的学生姓名
SELECT sid, sname from student where sid not in ( SELECT DISTINCT student_id from score where score.course_id in ( SELECT cid from course LEFT JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师'))
38. 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT sc.student_id, (SELECT avg(number) from score where score.student_id = sc.student_id) as avg_n from score sc where sc.number < 60 GROUP BY student_id HAVING count(1) > 1;
39. 检索“004”课程分数小于60，按分数降序排列的同学学号
SELECT student_id from score where score.course_id = 4 and score.number < 60 ORDER BY score.number desc;
40. 删除“002”同学的“001”课程的成绩
DELETE from score where score.student_id = 2 and score.course_id = 1;