mysql中嵌套查询分数⼤于70分的⼈数_MySQL复习之50道经
典基础题
之前学了MySQL，之后在学python的数据分析及机器学习，基本数据处理都⽤python在做，对MySQL的查询语句有⼀点遗忘，便想通过做这50道题来巩固和复习之前学习到的知识，以备之后⼯作时的需要。
先插⼊数据
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-03-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-07-01' , '⼥');
insert into Student values('08' , '王菊' , '1990-01-20' , '⼥');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语⽂' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
react 阮一峰insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
⼀共4张表，分别对应学⽣信息（Student）、课程信息（Course）、教师信息（Teacher）以及成绩信息（SC）
现在开始做题
1.查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数；
先将课程为01和02的课程及对应分数筛选出来，再join，on为01.sid = 02.sid，条件为01.score >02.score，结果'存'为新表t3，再将Student表和t3表join
SELECT a.*,t3.score FROM
(SELECT t1.sid,t1.score from (SELECT sid,score from sc where cid = '01') as t1
JOIN
(SELECT sid,score from sc WHERE cid = '02' )as t2
ON
t1.sid = t2.sid WHERE t1.score > t2.score) as t3
JOIN
student as a ON t3.sid = a.sid
2.查询学⽣选课存在" 01 "课程但可能不存在" 02 "课程的情况（不存在时显⽰为 null）；
即出学⽣选了01课程没有选02课程的情况，⽤left join即可
SELECT * FROM
((SELECT * FROM sc WHERE cid = '01' )as a
LEFT JOIN
(SELECT * FROM sc WHERE cid = '02' )as b
ON
a.sid =
b.sid)
mysql面试题34道经典3.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩；
Student表和SC表join，select 后接聚合函数avg，再group by sid/sname 都⾏，再⽤having 筛选 成绩⼤于等于60分的
SELECT a.sid,a.sname,AVG(b.score) AS avg_sc
FROM student AS a
JOIN sc AS b
ON a.sid = b.sid
GROUP BY a.sid HAVING avg_sc >=60
4.查询在 SC 表存在成绩的学⽣信息；
直接join 再group by即可
SELECT a.*,b.score FROM student as a
JOIN sc AS b
ON a.sid = b.sid
GROUP BY a.sid
去掉b.score也可以
5.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的成绩总和；
两个聚合函数⼀个count(cid),⼀个sum(score)，同样join student表和sc表，再group by sid即可
SELECT a.sid,a.sname,count(b.cid) AS num_class,sum(b.score) AS total_score
FROM student AS a
JOIN sc AS b
ON a.sid = b.sid
GROUP BY a.sid
6.查询「李」姓⽼师的数量；
count加条件函数加通配符即可
SELECT count(*) FROM teacher WHERE tname LIKE '李%'
7.查询学过「张三」⽼师授课的同学的信息；
四表连接，teacher表⾥的tid与course表⾥的tid，条件为tname=‘张三’，再course表⾥的cid与sc表⾥的cid，最后sc表⾥的sid 与student⾥的sid
SELECT f.*,e.tname FROM
(SELECT d.ame FROM
(ame,b.cid FROM teacher AS a
JOIN course AS b
ON a.tid = b.tid
ame = '张三') AS c
JOIN sc AS d表格样式在哪里设置excel
ON c.cid = d.cid) AS e
JOIN student AS f
ON e.sid = f.sid
8.查询没有学全所有课程的同学的信息；
先查询总课程数，再查询所有同学的信息，筛选条件为其所学课程数⼩于总课程数
SELECT a.*,count(b.cid) AS num_class
FROM student AS a
JOIN sc AS b
ON a.sid = b.sid
GROUP BY b.sid
HAVING COUNT(b.cid)< (SELECT COUNT(c.cid) FROM course as c)
错误：发现少⼀⾏王菊的数据，是由于王菊⼀门课都没有选，sc表中没有王菊的sid，⽤join导致没有王菊的信息，改写成left join即 错误
可
9.查询⾄少有⼀门课与学号为" 01 "的同学所学相同的同学的信息;
先查询学号为01的同学所学的课程编号，筛选条件为sc.cid in 01同学所学编号，再group by sid 最后筛选sid 不等于01
SELECT b.* FROM student AS b
JOIN sc AS a
ON b.sid = a.sid
WHERE a.cid in
(SELECT a.cid FROM sc AS a WHERE a.sid = '01')
GROUP BY b.sid
HAVING b.sid != '01'
多层嵌套就⼀层⼀层慢慢嵌套就⾏了
10.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息;
数据库连接失败怎么办⾸先查询01同学学过哪些课程，以及课程数是多少，要完全相同即课程名称相同以及课程数相同，在⽤in的时候，例如 a in b，即b⾥⾯的字段个数>=a⾥⾯的字段个数，如果要a，b完全相同，⽤count(a)=count(b)&a in b。其余部分正常嵌套就⾏了
SELECT b.* FROM
(SELECT a.*,sc.cid FROM
(SELECT student.* FROM student WHERE student.sid != '01') as a
JOIN sc
ON a.sid = sc.sid) as b
WHERE b.cid IN
(SELECT cid FROM sc WHERE sid = '01')
GROUP BY b.sid
HAVING COUNT(b.cid) = (SELECT count(cid) FROM sc WHERE sid = '01')
11.查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名;
先出所有学⽣选课信息及sid，再出张三⽼师授课课程，将其连接，再⽤student⾥的sid not in 前⾯的sid
SELECT student.sname FROM student
WHERE student.sid NOT IN
(SELECT sc.sid FROM sc
JOIN course
ON sc.cid=course.cid
JOIN teacher
ON course.tid=teacher.tid
WHERE tname='张三' )windows控制台快捷键
12.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩;
先查询不及格情况，再count>=2，再连接student表
SELECT c.sname,b.* FROM student AS c
JOIN
((SELECT sid,count(cid) FROM sc
WHERE score <60
GROUP BY sid
HAVING COUNT(cid) >=2) AS a
JOIN
(SELECT sid,AVG(score) FROM sc
GROUP BY sid) AS b
ON a.sid = b.sid)
ON c.sid = b.sid
13.查询" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息;
where and语句，order by desc
SELECT a.*,b.score FROM student AS a
LEFT JOIN sc AS b
ON a.sid = b.sid
WHERE b.cid = '01' AND b.score <60
access union用法ORDER BY b.score DESC
居然有⼈点赞收藏
14.按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩;
先求平均成绩再进⾏连接即可
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论