SQL题（困难）

最新推荐文章于 2025-01-16 14:51:19 发布

@明天更美好

最新推荐文章于 2025-01-16 14:51:19 发布

阅读量523

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq1226546902/article/details/129323976

版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int(4) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 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`  (
  `cid` int(4) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `tearcher_id` int(4) NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 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, '英语', 4);
INSERT INTO `course` VALUES (5, '语文', 5);
INSERT INTO `course` VALUES (6, '数学', 1);
INSERT INTO `course` VALUES (7, '化学', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(4) NOT NULL AUTO_INCREMENT,
  `student_id` int(4) NULL DEFAULT NULL,
  `course_id` int(4) NULL DEFAULT NULL,
  `number` int(6) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 28 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 60);
INSERT INTO `score` VALUES (2, 1, 2, 59);
INSERT INTO `score` VALUES (3, 1, 3, 100);
INSERT INTO `score` VALUES (4, 1, 4, 80);
INSERT INTO `score` VALUES (5, 1, 5, 70);
INSERT INTO `score` VALUES (6, 1, 6, 50);
INSERT INTO `score` VALUES (7, 2, 1, 88);
INSERT INTO `score` VALUES (8, 2, 2, 60);
INSERT INTO `score` VALUES (9, 2, 3, 99);
INSERT INTO `score` VALUES (10, 2, 4, 40);
INSERT INTO `score` VALUES (11, 2, 5, 52);
INSERT INTO `score` VALUES (12, 2, 6, 80);
INSERT INTO `score` VALUES (13, 3, 1, 96);
INSERT INTO `score` VALUES (14, 3, 2, 50);
INSERT INTO `score` VALUES (15, 3, 3, 70);
INSERT INTO `score` VALUES (16, 3, 4, 80);
INSERT INTO `score` VALUES (17, 3, 5, 66);
INSERT INTO `score` VALUES (18, 3, 6, 70);
INSERT INTO `score` VALUES (19, 4, 1, 55);
INSERT INTO `score` VALUES (20, 4, 2, 66);
INSERT INTO `score` VALUES (21, 4, 3, 78);
INSERT INTO `score` VALUES (22, 4, 4, 68);
INSERT INTO `score` VALUES (23, 4, 5, 99);
INSERT INTO `score` VALUES (24, 4, 6, 88);
INSERT INTO `score` VALUES (25, 5, 1, 100);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(4) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `class_id` int(4) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '钢蛋', '女', 1);
INSERT INTO `student` VALUES (2, '铁锤', '女', 1);
INSERT INTO `student` VALUES (3, '山炮', '男', 2);
INSERT INTO `student` VALUES (4, '王五', '男', 2);
INSERT INTO `student` VALUES (5, '钢蛋1', '女', 3);
INSERT INTO `student` VALUES (6, '钢蛋2', '女', 3);
INSERT INTO `student` VALUES (7, '钢蛋', '女', 2);
INSERT INTO `student` VALUES (8, '铁锤', '女', 1);


  `areaId` int(20) NOT NULL AUTO_INCREMENT COMMENT '地区Id',
  `areaCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地区编码',
  `areaName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地区名',
  `level` tinyint(4) NULL DEFAULT -1 COMMENT '地区级别（1:省份province,2:市city,3:区县district,4:街道street）',
  `cityCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市编码',
  `center` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市中心点（即：经纬度坐标）',
  `parentId` int(20) NULL DEFAULT -1 COMMENT '地区父节点',
  PRIMARY KEY (`areaId`) USING BTREE,
  INDEX `areaCode`(`areaCode`) USING BTREE,
  INDEX `parentId`(`parentId`) USING BTREE,
  INDEX `level`(`level`) USING BTREE,
  INDEX `areaName`(`areaName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3260 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '地区码表' ROW_FORMAT = Dynamic;



-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(4) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

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

SET FOREIGN_KEY_CHECKS = 1;

题目

查询“生物”课程比“物理”课程成绩高的所有学生的学号；

思路：

获取所有有生物课程的人（学号，成绩） - 临时表

获取所有有物理课程的人（学号，成绩） - 临时表

根据【学号】连接两个临时表：

学号物理成绩生物成绩

然后再进行筛选

select A.student_id,生物,物理 from
    (select student_id,number as 生物 from score left join course on score.course_id = course.cid where course.cname = '生物') as A
        left join
    (select student_id,number  as 物理 from score left join course on score.course_id = course.cid where course.cname = '物理') as B
    on A.student_id = B.student_id where 生物 > if(isnull(物理),0,物理);
# 　　注释：if(isnull(ty),0,ty); 相当于三元运算

查询平均成绩大于60分的同学的学号和平均成绩；

思路：

根据学生分组，使用avg获取平均值，通过having对avg进行筛选

select student_id, avg(number)a  from score group by student_id  having a>60;

查询所有同学的学号、姓名、选课数、总成绩；

思路：先从score表中对学号分组，作为一个临时表 A ：【学号】，【选课数】，【总成绩】,

然后将A表与学生表进行连接，查出结果集。

select student.sid, sname,count(s.course_id),sum(number)
from `student`
left join `score` s on `student`.sid = s.student_id
group by student.sname;

查询姓“李”的老师的个数；

select count(tid) from teacher where tname like '李%'
select count(1) from (select tid from teacher where tname like '李%') as B
select distinct count(tname) from teacher where tname like '李%';

查询没学过“叶平”老师课的同学的学号、姓名；

思路：

先查到“叶平老师”老师教的所有课ID

获取选过课的所有学生ID

学生表中筛选

select  student.sid,sname from student
left join score s on student.sid = s.student_id
where s.course_id not in  (select course.cid from teacher left join course on tid = course.tearcher_id where tname='叶平')
group by student.sname;

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

思路：

先查到既选择001又选择002课程的所有同学

根据学生进行分组，如果学生数量等于2表示，两门均已选择

select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1


select distinct student.sid,sname from student
left join score s on student.sid = s.student_id
where s.course_id in (001,002);

查询学过“叶平”老师所教的所有课的同学的学号、姓名；

 select  student.sid,sname from student
left join score s on student.sid = s.student_id
where s.course_id in  (select course.cid from teacher left join course on tid = course.tearcher_id where tname='叶平');

select student.sid,student.sname  from student
    left join score s on student.sid = s.student_id
    left join course c on c.cid=s.course_id
    left join teacher t on c.tearcher_id = t.tid
where t.tname ='叶平';

查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select s.student_id,sname,number from student 
left join score s on student.sid = s.student_id 
where s.course_id =002
and s.number< all(select s.number from student 
left join score s on student.sid = s.student_id 
where s.course_id =001);

查询有课程成绩小于60分的同学的学号、姓名；

select sid,sname from student where sid in (
select distinct student_id from score where num < 60)

查询没有学全所有课的同学的学号、姓名；

思路：

在分数表中根据学生进行分组，获取每一个学生选课数量

select student_id,sname
from score left join student on score.student_id = student.sid
group by student_id HAVING count(course_id) != (select count(*) from course);

查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select student.sid,sname from student left join score s on student.sid = s.student_id
where course_id in (select score.course_id from score where student_id=001)
group by sname;

查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

select student.sid,sname from student left join score s on student.sid = s.student_id
where course_id in (select score.course_id from score where student_id=001) and student.sid!=001
group by sname;

查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
select student_id from score  where student_id != 2 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 2)
) and course_id in (select course_id from score where student_id = 2) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 2);

删除学习“叶平”老师课的SC表记录；

delete from score where course_id in (
select cid from course left join teacher on course.tearcher_id = teacher.tid where teacher.tname = '叶平'
);

向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

select * from score s left join course c on s.course_id = c.cid;
(select  sid,avg(number) from score s left join course c on s.course_id = c.cid
where cid = 002);
insert into score values (0,1,2,(select  avg(number) from score s left join course c on s.course_id = c.cid
                                 where cid = 002) );

按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；

select s.sid 学生ID,
       s.number,
       a.number 语文,
       b.number 数学,
       c.number 英语,
       dc 有效课程,
       da 有效平均分
       from student st
       left join score s on st.sid = s.sid
       left join (select student_id,number from score left join course on score.course_id = course.cid where cname='语文') a on s.student_id =a.student_id
       left join (select student_id,number from score left join course on score.course_id = course.cid where cname='数学') b on s.student_id =b.student_id
       left join (select student_id,number from score left join course on score.course_id = course.cid where cname='英语') c on s.student_id =c.student_id
       left join (select student_id,count(course_id) dc ,avg(number) da from score left join course on score.course_id = course.cid group by student_id) d on s.sid =d.student_id
       left join course c on  s.course_id = c.cid
       order by s.number desc ;

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select  score.course_id 课程ID ,a.最高分 ,b.最低分 from score
    left join (select  course_id,max(number) 最高分 from score group by course_id) a on a.course_id=score.course_id
    left join (select  course_id,min(number) 最低分  from score group by course_id) b on b.course_id=score.course_id
group by score.course_id;

按各科平均成绩从低到高和及格率的百分数从高到低顺序；

思路：case when .. then

select course_id, avg(number) as avgnum,
       sum(case when score.number >= 60 then 1 else 0 END)/count(1)*100 as percent
from score group by course_id order by avgnum asc,percent desc ;

select course_id 课程,avg(s.number)平均分,t.tname 现实任课老师 from score s
    left join course c on c.cid = s.course_id
    left join teacher t on c.tearcher_id = t.tid
group by course_id
order by 平均分 desc ;

课程平均分从高到低显示（现实任课老师）；

select course_id 课程,avg(s.number)平均分,t.tname 现实任课老师 from score s
    left join course c on c.cid = s.course_id
    left join teacher t on c.tearcher_id = t.tid
group by course_id
order by 平均分 desc ;

select course_id 课程,avg(if(isnull(score.number),0,score.number)) 平均分,teacher.tname from course
   left join score on course.cid = score.course_id
   left join teacher on course.tearcher_id = teacher.tid
group by score.course_id
order by 平均分 desc ;

查询各科成绩前三名的记录:(不考虑成绩并列情况)

select  student_id, course_id, number  from  score
order by course_id desc , number desc limit 0,3;#从0开始取三位

查询每门课程被选修的学生数；

select course_id,count(student_id) from score group by course_id;

查询出只选修了一门课程的全部学生的学号和姓名；

select  student_id, course_id from score
group by student_id having count(course_id)  =1;

查询男生、女生的人数；

select * from
(select count(gender)男 from student where gender='男')a,
(select count(gender)女 from student where gender='女')b;

查询姓“张”的学生名单；

select sid, sname  from student where sname like '张%';

查询同名同姓学生名单，并统计同名人数；

select sid, sname, gender,count(sname)同名同姓学生人数 from student group by sname;

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

select course_id,avg(number)a from score group by course_id order by a asc ,course_id desc ;
select course_id,avg(if(isnull(number), 0 ,number)) as avg from score group by course_id order by avg  asc,course_id desc;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

select student.sid,student.sname,avg(if(isnull(s.number),0,s.number))a from student
    left join score s on student.sid = s.student_id group by student_id having a>85;

查询课程名称为“数学”，且分数低于60的学生姓名和分数；

select  student_id 学号,sname 姓名,c.cname 课程名,score.number 分数 from score
    left join student s on score.student_id = s.sid
    left join course  c on score.course_id=c.cid
where number<60 and c.cname='数学';

查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select  student_id 学号,sname 姓名,score.course_id 课程编号,score.number 分数 from score
      left join student s on score.student_id = s.sid
where course_id=003;

求选了课程的学生人数

select count(distinct student_id) from score;

select count(c) from (
select count(student_id) as c from score group by student_id) as A;

查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select sname,max(number) 成绩 from student
left join score s on student.sid = s.student_id
left join teacher t on t.tid = s.student_id
where t.tname = '杨艳';

查询各个课程及相应的选修人数；

select course_id,count(student_id) from score group by course_id;

查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select distinct s1.student_id,s1.course_id,s1.number
from score s1,score s2 
where s1.number=s2.number and s1.course_id !=s2.course_id
order by number;

查询每门课程成绩最好的前两名；

select course_id,number from score
group by number
order by number desc
limit 0,2;

检索至少选修两门课程的学生学号；

select sid  from score group by course_id having count(course_id) >1;

查询全部学生都选修的课程的课程号和课程名；

select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);

查询没学过“叶平”老师讲授的任一门课程的学生姓名；

select distinct sname from student
left join score on student.sid = course_id
left join course c on course_id=c.cid
left join teacher t on t.tid=c.tearcher_id
where t.tname !='叶平';

查询两门以上不及格课程的同学的学号及其平均成绩；

select student_id,count(1) ,
       avg(number) from score
where score.number < 60 group by student_id having count(1) > 2;

检索“004”课程分数小于60，按分数降序排列的同学学号；

select  student_id, course_id, number from score
where course_id=004 and number<60
order by student_id desc ;

删除“002”同学的“001”课程的成绩；

delete from score where student_id =002 and course_id=001;

SQL题 （困难）

建表

题目

查询“生物”课程比“物理”课程成绩高的所有学生的学号；

查询平均成绩大于60分的同学的学号和平均成绩；

查询所有同学的学号、姓名、选课数、总成绩；

查询姓“李”的老师的个数；

查询没学过“叶平”老师课的同学的学号、姓名；

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

查询学过“叶平”老师所教的所有课的同学的学号、姓名；

查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

查询有课程成绩小于60分的同学的学号、姓名；

查询没有学全所有课的同学的学号、姓名；

查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

删除学习“叶平”老师课的SC表记录；

向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

按各科平均成绩从低到高和及格率的百分数从高到低顺序；

课程平均分从高到低显示（现实任课老师）；

查询各科成绩前三名的记录:(不考虑成绩并列情况)

查询每门课程被选修的学生数；

查询出只选修了一门课程的全部学生的学号和姓名；

查询男生、女生的人数；

查询姓“张”的学生名单；

查询同名同姓学生名单，并统计同名人数；

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

查询课程名称为“数学”，且分数低于60的学生姓名和分数；

查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

求选了课程的学生人数

查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

查询各个课程及相应的选修人数；

查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

查询每门课程成绩最好的前两名；

检索至少选修两门课程的学生学号；

查询全部学生都选修的课程的课程号和课程名；

查询两门以上不及格课程的同学的学号及其平均成绩；

检索“004”课程分数小于60，按分数降序排列的同学学号；

删除“002”同学的“001”课程的成绩；

SQL题（困难）

按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；