MySQL 作业题及答案

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;

 

本文作者:hukey

本文链接:https://www.cnblogs.com/hukey/p/10384468.html

版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   hukey  阅读(1884)  评论(0编辑  收藏  举报
历史上的今天:
2017-02-15 [ kvm ] 进程的处理器亲和性和vCPU的绑定
点击右上角即可分享
微信分享提示
💬
评论
📌
收藏
💗
关注
👍
推荐
🚀
回顶
收起
  1. 1 彩虹 Jay
彩虹 - Jay
00:00 / 00:00
An audio error has occurred.

彩虹 + 轨迹 (Live) - 周杰伦 (Jay Chou)

彩虹

词:周杰伦

曲:周杰伦

哪里有彩虹告诉我

哪里有彩虹告诉我

能不能把我的愿望还给我

能不能把我的愿望还给我

为什么天这么安静

为什么天这么安静

所有的云都跑到我这里

有没有口罩一个给我

有没有口罩一个给我

释怀说了太多就成真不了

释怀说了太多就成真不了

也许时间是一种解药

也许时间是一种解药

也是我现在正服下的毒药

也是我现在正服下的毒药

看不见你的笑 我怎么睡得着

看不见你的笑 我怎么睡得着

你的声音这么近我却抱不到

你的声音这么近我却抱不到

没有地球太阳还是会绕

没有地球太阳还是会绕

没有理由我也能自己走

没有理由我也能自己走

你要离开 我知道很简单

你要离开 我知道很简单

你说依赖 是我们的阻碍

你说依赖 是我们的阻碍

就算放开 但能不能别没收我的爱

就算放开 但能不能别没收我的爱

当作我最后才明白

当作我最后才明白

看不见你的笑 要我怎么睡得着

看不见你的笑 要我怎么睡得着

你的声音这么近我却抱不到

没有地球太阳还是会绕 会绕

没有理由我也能自己走掉

释怀说了太多就成真不了

也许时间是一种解药 解药

也是我现在正服下的毒药

轨迹

词:黄俊郎

曲:周杰伦

我会发着呆然后忘记你

接着紧紧闭上眼

想着哪一天 会有人代替

想着哪一天 会有人代替

让我不再想念你

我会发着呆 然后微微笑

我会发着呆 然后微微笑

接着紧紧闭上眼

又想了一遍 你温柔的脸

又想了一遍 你温柔的脸

在我忘记之前