linux办公网
MySQL面试经典50题
创始人
2024-03-30 11:24:59
0次

SQL面试50题【mysql】

  • 前言
  • 推荐
  • 说明
  • SQL面试50题
    • 50题
    • 测试数据
    • 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
    • 2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
    • 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
    • 4、查询姓“猴”的老师的个数（不重要）
    • 5、查询没学过“张三”老师课的学生的学号、姓名（重点）
    • 6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
    • 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
    • 8、查询课程编号为“02”的总成绩（不重点）
    • 9、查询所有课程成绩小于60分的学生的学号、姓名
    • 10、查询没有学全所有课的学生的学号、姓名(重点)
    • 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
    • 12、查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
    • 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）
    • 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
    • 16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
    • 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
    • 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    • 19、按各科成绩进行排序，并显示排名(重点row_number)
    • 20、查询学生的总成绩并进行排名（不重点）
    • 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
    • 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
    • 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)
    • 24、查询学生平均成绩及其名次（同19题，重点）
    • 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
    • 26、查询每门课程被选修的学生数(不重点)
    • 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
    • 28、查询男生、女生人数(不重点)
    • 29、查询名字中含有"风"字的学生信息（不重点）
    • 30、题忽略掉
    • 31、查询1990年出生的学生名单（重点year）
    • 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
    • 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
    • 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
    • 35、查询所有学生的课程及分数情况（重点）
    • 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
    • 37、查询不及格的课程并按课程号从大到小排列(不重点)
    • 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
    • 39、求每门课程的学生人数（不重要）
    • 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）
    • 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
    • 42、查询每门功成绩最好的前两名（同22和25题）
    • 43、统计每门课程的学生选修人数（超过5人的课程才统计）。
    • 44、检索至少选修两门课程的学生学号（不重要）
    • 45、 查询选修了全部课程的学生信息（重点划红线地方）
    • 47、查询没学过“张三”老师讲授的任一门课程的学生姓名
    • 48、查询两门以上不及格课程的同学的学号及其平均成绩
    • 46、查询各学生的年龄（精确到月份）
    • 47、查询本月过生日的学生（无法使用week、date(now()）
  • 另外
  • 总结
    • 最值查询
    • 7种SQL JOINS的实现
  • 最后

前言

当时学mysql的时候是自己看书学的，学的不是很深刻

经过一天的阅读MQL,大概懂了多表查询和子查询

经过两天的学习和练习，写了这篇博客

书中自有黄金屋，书中自有颜如玉

推荐的MySQL笔记是我当时暑假学mysql高级课程（blibli尚硅谷宋红康）发现的

有兴趣的或可以读一下，导航【mysql高级】【java提高】

如果想看基础博客，可以看推荐文章的作者的专栏 尚硅谷MySQL学习笔记

如果想看视频学习，可以看【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】

书中自有黄金屋，书中自有颜如玉

推荐

MySQL笔记：第06章_多表查询

MySQL笔记：第09章_子查询

SQL面试必会50题

说明

练习此篇之前，建议看一看推荐文章，了解MQL：如连接查询和子查询

或者了解一定数据库原理：如关系代数：选择、投影、连接、除运算


sql的答案并不是只有一种，有其他的方法可以在评论区讨论，有错误请担待答案说明me：我自己写的
zx：我的朋友 https://blog.csdn.net/weixin_51740288
ck：原贴 https://zhuanlan.zhihu.com/p/43289968
pl：原贴 评论区

SQL面试50题

50题

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号教师表：
Teacher(t_id,t_name) –教师编号,教师姓名成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

根据以上信息按照下面要求写出对应的SQL语句。

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画关联图，然后再编写对应的SQL语句就比较容易了。下图是我在草稿纸上画的这4张表的关系图，不好理解，你可以列举一些数据案例来辅助理解：

在这里插入图片描述

测试数据

-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);


-- 插入学生表测试数据
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' , '女');-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');-- 成绩表测试数据
INSERT INTO Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);

其中重点为：1/2/5/6/7/10/11/12/13/15/17/18/19/22/23/25/31/35/36/40/41/42/45/46 共16题

超级重点 18和23、 22和25 、 41、46

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

# 法一
#zx
SELECT a.s_id"s_no" ,c.`s_name`"s_name",a.s_score"s_01",b.s_score"s_02" FROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01')AS a
INNER JOIN
(SELECT s_id,c_id,s_score FROM score WHERE c_id='02') 
AS b  ON a.s_id=b.s_id
INNER JOIN student  AS c ON c.`s_id`=a.s_idWHERE a.s_score>b.s_score#1、查询"01""课程比"O2""课程成绩高的学生的信息及课程分数# 法二
SELECT student.*,score.c_id,score.s_score
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE student.s_id IN(SELECT a.s_id FROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01') AS aLEFT JOIN (SELECT s_id,c_id,s_score FROM score WHERE c_id='02') AS b  ON a.s_id=b.s_idWHERE a.s_score>b.s_score OR b.s_id IS NULL
)

#me
SELECT s_id
FROM student
WHERE s_id IN(SELECT a.s_idFROM (SELECT s_id,c_id,s_score FROM score WHERE c_id='01') AS a INNER JOIN (SELECT s_id,c_id,s_score  FROM score WHERE c_id='02') AS bON a.s_id=b.s_idWHERE a.s_score>b.s_score
)

2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

#zx
SELECT 
s_id,AVG(s_score)
FROM score
GROUP BY s_id HAVING AVG(s_score)>60

#me
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score) > 60

#查询平均成绩大于60分的学生的学号和平均成绩 类似的题目（重点）#法一：-- 9、查询所有课程成绩小于60分的学生的学号、姓名
SELECT *
FROM student
WHERE s_id NOT IN
(SELECT s_id FROM score WHERE s_score>=60);#错误的答案（不包含无成绩的）SELECT *
FROM student
WHERE s_id IN
(SELECT s_id FROM score WHERE s_score<60);# 法二-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
#IFNULL如果第一个值不为NULL返回第一个值，否则返回第二个值
SELECT  student.s_id,student.s_name,AVG(IFNULL(score.s_score,0)) AS AVG
FROM student 
LEFT JOIN score
ON student.s_id=score.s_id
GROUP BY student.s_id
HAVING AVG(score.s_score)IS NULL OR  AVG(score.s_score)<60

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

#me
SELECT s.s_id,s_name,COUNT(c_id),SUM(s_score)
FROM student s LEFT JOIN score sc
ON s.s_id=sc.s_id
GROUP BY s.s_id

#zx
-- 查询所有学生的学号、姓名、选课数、总成绩
#拿到 学号姓名课程号分数  组成的子表
SELECT a.s_id,s_name,c_id,s_score 
FROM (student AS a 
LEFT JOIN score AS  b 
ON a.s_id=b.s_id)

4、查询姓“猴”的老师的个数（不重要）

## 4、查询姓“猴”的老师的个数（不重要）
#注: 名字可能重复，所以用t_id
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '猴%'

5、查询没学过“张三”老师课的学生的学号、姓名（重点）

##5、查询没学过“张三”老师课的学生的学号、姓名（重点）#注;子查询#me
#先查学过“张三”老师课的学生SELECT s_id
FROM score
WHERE c_id IN(SELECT c_idFROM courseWHERE t_id=(SELECT t_idFROM teacherWHERE t_name='张三'))# 再查SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(SELECT s_idFROM scoreWHERE c_id IN(  # 张三不一定支教一个课SELECT c_idFROM courseWHERE t_id=(SELECT t_idFROM teacherWHERE t_name='张三')))

#zx
SELECT * FROM student
WHERE s_id NOT IN(SELECT s_id FROM scoreWHERE c_id=(SELECT c_id FROM(teacher AS a INNER JOIN course AS bON a.t_id=b.t_id)  WHERE t_name='张三') 
)

#错误答案；#因为te.t_name !='张三' 查找出另外两个老师，对应的学生SELECT*
FROM student
WHERE s_id IN(SELECT sc.s_id FROM score sc INNER JOIN Course co ON sc.c_id=co.c_id
INNER JOIN Teacher te ON co.t_id=te.t_id WHERE te.t_name !='张三')

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

## 6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）#meSELECT s_id,s_name
FROM student
WHERE s_id IN(SELECT s_idFROM scoreWHERE c_id IN(SELECT c_idFROM courseWHERE t_id=(SELECT t_idFROM teacherWHERE t_name='张三')))
#zx
SELECT st.s_id,st.s_name,sc.c_id,c.c_name,t.t_id,t.t_name
FROM student AS  st INNER JOIN score AS sc ON st.s_id=sc.s_id
INNER JOIN course AS c ON sc.c_id=c.c_id
INNER JOIN teacher AS t ON t.t_id=c.t_id
WHERE t.t_name='张三'

-- 6.查询没学过“张三“老师授课的同学的信息
SELECT *
FROM student
WHERE s_id IN(SELECT sc.s_id FROM score sc 
INNER JOIN Course co ON sc.c_id=co.c_id
INNER JOIN Teacher te ON co.t_id =te.t_id 
WHERE te.t_name ='张三')

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

## 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
#me
# 法一
SELECT s.s_id,s_name
FROM ((SELECT s_id FROM score WHERE c_id='01') AS aINNER JOIN (SELECT s_id FROM score WHERE c_id='02') AS bON a.s_id=b.s_id
) INNER JOIN student s
ON s.s_id=a.s_id#法二
SELECT s_id,s_name
FROM student
WHERE s_id IN(SELECT a.s_id FROM((SELECT s_id FROM score WHERE c_id='01') AS aINNER JOIN (SELECT s_id FROM score WHERE c_id='02') AS bON a.s_id=b.s_id		)) #法三
#查询学过编号为01和02课程的学生的学号和姓名 7
SELECT s_id,s_name 
FROM student
WHERE s_id IN(SELECT s_id FROM score WHERE c_id=01 AND s_id IN (SELECT s_id FROM score WHERE c_id=02)
)
#zx
#法一
#查询学过编号为01和02课程的学生的学号和姓名
SELECT s_id ,s_name 
FROM student,
(SELECT a.s_id "id" FROM(SELECT s_id FROM score WHERE c_id='01') AS aINNER JOIN(SELECT s_id FROM score WHERE c_id='02') AS bON  a.s_id=b.s_id
) t
WHERE t.id=student.s_id;#法二
SELECT s_id ,s_name 
FROM student 
WHERE s_id IN(SELECT a.s_id FROM(SELECT s_id FROM score WHERE c_id='01') AS aINNER JOIN(SELECT s_id FROM score WHERE c_id='02') AS bON  a.s_id=b.s_id
) 
-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
#me
SELECT * 
FROM student
WHERE s_id IN(SELECT s_idFROM scoreWHERE c_id='01'AND s_id NOT IN(SELECT s_idFROM scoreWHERE c_id='02'))-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
#ck
SELECT *
FROM student 
WHERE s_id IN
(SELECT s_id FROM score WHERE c_id='01')
AND s_id NOT IN (SELECT s_id FROM score WHERE c_id='02')

8、查询课程编号为“02”的总成绩（不重点）

#me
SELECT SUM(s_score)
FROM score
WHERE c_id=02;

#zx
SELECT t.c_id,SUM(t.s_score)
FROM (SELECT * FROM score WHERE c_id ='02') t
GROUP BY t.c_id

9、查询所有课程成绩小于60分的学生的学号、姓名

同题目2

#me# 法一 60大于成绩 所有的
#这个会把成绩为null也算上
SELECT s_id,s_name
FROM student 
WHERE 60> ALL(SELECT s_scoreFROM scoreWHERE score.s_id=student.s_id
)#修改
SELECT student.s_id,s_name,c_id,s_score
FROM student 
INNER JOIN score
ON student.s_id=score.s_id
WHERE 60> ALL(SELECT s_scoreFROM scoreWHERE score.s_id=student.s_id
) #最后
SELECT DISTINCT student.s_id,s_name
FROM student 
INNER JOIN score
ON student.s_id=score.s_id
WHERE 60> ALL(SELECT s_scoreFROM scoreWHERE score.s_id=student.s_id
) #法二
SELECT s_id,s_name
FROM student
WHERE 60> (SELECT MAX(s_score)FROM scoreWHERE score.s_id=student.s_idGROUP BY score.s_id
)# 法三 大于降序第一个成绩值 
SELECT s_id,s_name
FROM student
WHERE 60> (SELECT s_scoreFROM scoreWHERE score.s_id=student.s_idORDER BY s_score DESCLIMIT 1
)# 法四 内连接
SELECT s.s_id,s_name,MAX(s_score)
FROM student s INNER JOIN score sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING MAX(s_score)<60
#zx
SELECT a.s_id ,t.s_name 
FROM
(SELECT s_id,COUNT(c_id)"cnt" 
FROM score 
WHERE s_score<60
GROUP BY s_id) AS a
INNER JOIN
(SELECT s_id,COUNT(c_id) "cnt" 
FROM score
GROUP BY s_id) AS b
ON a.s_id=b.s_id
INNER JOIN student AS t 
ON a.s_id=t.s_id
WHERE a.cnt=b.cnt

10、查询没有学全所有课的学生的学号、姓名(重点)

#注: 1.最全的课程要从course中选择，而不是 score
#2.select可以没有和HAVING一样的聚合函数#查询有多少门课程
SELECT COUNT(c_id)
FROM course
#结果是3#me#以下结果少s_id=8 null
SELECT s_id,s_name
FROM student
WHERE s_id IN(SELECT s_idFROM scoreGROUP BY s_idHAVING COUNT(c_id)<3
)#反求法
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(SELECT s_idFROM scoreGROUP BY s_idHAVING COUNT(c_id)=3
)
#zx
-- 改为左连接
SELECT  st.s_id,s_name FROM student AS st
LEFT JOIN score AS sc
ON st.s_id=sc.s_id
GROUP BY s_id HAVING COUNT(DISTINCT c_id)<3

#ck
SELECT st.s_id,st.s_name
FROM student st INNER JOIN Score sc ON st.s_id=sc.s_id GROUP BY st.s_id,st.s_name
HAVING COUNT(c_id)<(SELECT COUNT(DISTINCT c_id)FROM Course)

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）


## 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
#me
#in =any  
#exists一般用在关联子查询中SELECT s_id,s_name
FROM student
WHERE s_id IN (SELECT DISTINCT s_idFROM scoreWHERE c_id = ANY (SELECT c_id FROM scoreWHERE s_id ='01' )
)AND s_id!='01'

#zx
SELECT s_id,s_name FROM student
WHERE S_id IN (SELECT DISTINCT s_id FROM scoreWHERE c_id IN( SELECT c_id FROM scoreWHERE s_id ='01'   )AND s_id!='01')

12、查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

重点是完全相同

以下结果不对 in


## 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
#me#查询学号“01”的学生所选课程
SELECT c_id 
FROM score
WHERE s_id=01#法一
SELECT DISTINCT s.s_id,s_name
FROM student s
INNER JOIN score sc
ON s.s_id=sc.s_id
WHERE c_id IN (SELECT c_id FROM scoreWHERE s_id=01
)AND s.s_id != '01'#法二
SELECT a.s_id,s_name 
FROM student AS a
INNER JOIN (SELECT DISTINCT s_id FROM scoreWHERE c_id IN(    SELECT c_id FROM scoreWHERE s_id ='01'   )AND s_id!='01') AS b
ON a.s_id=b.s_id
#zx
#法一
#查询至少有一门课跟学号“01”的学生所选课程相同的学生的学号和姓名
SELECT s_id,s_name FROM student
WHERE S_id IN
(SELECT DISTINCT s_id FROM scoreWHERE c_id IN(    SELECT c_id FROM scoreWHERE s_id ='01'   )
AND s_id!='01')#法二 用inner代替in
SELECT DISTINCT s1.s_id,s_name
FROM student s1 INNER JOIN score sc1
ON s1.s_id = sc1.s_id
WHERE sc1.s_id IN (SELECT DISTINCT s_id FROM scoreWHERE c_id IN(    SELECT c_id FROM scoreWHERE s_id ='01'   )
) AND s1.s_id != 01;

以上结果不对 in

这个也不对，count相同，内容不一定相同

#ck
-- 12.查询和"01"号同学所学课程完全相同的其他同学的学号
SELECT sc.s_id, st.s_name
FROM score sc 
INNER JOIN student st ON sc.s_id=st.s_id 
GROUP BY sc.s_id,st.s_name
HAVING COUNT(sc.c_id) IN(SELECT COUNT(c_id) FROM score WHERE s_id='01')
AND sc.s_id!='01'

修改如下

没选01没选的，并且选课数量相同

#pl
SELECT s_id FROM score
WHERE s_id != '01'
AND s_id NOT IN ( 								# 没有和01不一样的，但是有可能比01少SELECT s_id FROM score WHERE c_id NOT IN (   #选了和01不一样的SELECT c_id FROM score WHERE s_id = '01' #01选的课程)
)
GROUP BY s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM score WHERE s_id = '01') #数量一样并且没有和01不一样的课
# 相关子查询 exists
# a=b 互为子集 a-b=0 差集SELECT DISTINCT s_id
FROM score scx
WHERE NOT EXISTS(SELECT *FROM score scyWHERE scy.c_id='01'ANDNOT EXISTS(SELECT *FROM score sczWHERE scz.s_id=scx.s_id ANDscz.c_id=scy.c_id))

另外

#查询选了所有课程的学生信息
SELECT s_id,s_name
FROM student
WHERE NOT EXISTS(SELECT *FROM courseWHERE NOT EXISTS(SELECT *FROM scoreWHERE s_id=student.s_idAND c_id=course.c_id))

数据库原理相关理论：
EXISTS 代表存在量词“∃”。
可以利用EXISTS来判断x∈S,S⊆R,S=R，S∩R非空等是否成立

SQL中没有全称量词“∀”，但是可以把全称量词的谓词，转换为等价的带有存在量词的谓词：
(∀x)P ≡ ¬(∃x(¬P))

任意x都P=不存在x不P

[例3.61]：查询选修了全部课程的学生姓名。
等价于查询这样的学生，没有一门课程是他不学修的。

SELECT Sname 
FROM Student
WHERE NOT EXISTS(SELECT *FROM CourseWHERE NOT EXISTS(SELECT *FROM SCWHERE Sno=Student.SnoAND Cno=Course.Cno));

从而用 EXIST/NOT EXIST来实现带全称量词的查询。

[例3.63]：查询至少选修了学生201215122选修的全部课程的学生号码。

本查询可以用逻辑蕴涵来表达:查询学号为x的学生,对所有的课程y,只要201215122学生选修了课程y,则x也选修了y。
形式化表示如下:
用p表示谓词“学生201215122选修了课程y”
用q表示谓词“学生x选修了课程y”
则上述查询为
(∀y)p→q

sQL 语言中没有蕴涵（implication）逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为
p→q=¬ p∨q

离散数学p条件q=条件为假或结论为真

真值表

pqresult
001
011
100
111

该查询可以转换为如下等价形式:

(∀y)p→q ≡¬(∃y(¬(p→q ))≡ ¬(∃y (¬ ( ¬p∨q)))≡∃y(p ∧¬ q）

它所表达的语义为:不存在这样的课程y,学生201215122选修了y,而学生x没有选。
用SQL语言表示如下:

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno=' 201215122'ANDNOT EXISTS(SELECT *FROM SC SCZWHERE SCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno));

P110页

13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

#me
#查询"张三"老师讲授的任一门课程
SELECT c_id
FROM course c
INNER JOIN teacher t
ON c.t_id=t.t_id
WHERE t_name='张三'#查询学过
SELECT s_id
FROM score
WHERE c_id IN(SELECT c_idFROM course cINNER JOIN teacher tON c.t_id=t.t_idWHERE t_name='张三'
)#查询没学过SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(SELECT s_idFROM scoreWHERE c_id IN(SELECT c_idFROM course cINNER JOIN teacher tON c.t_id=t.t_idWHERE t_name='张三')
)
#ck
#14、查询没学过"张三"老师讲授的任一门课程的学生姓
SELECT *
FROM student
WHERE s_id NOT IN(SELECT sc.s_id FROM score scINNER JOIN Course co ON sc.c_id=co.c_idINNER JOIN Teacher te ON co.t_id=te.t_id WHERE te.t_name='张三')

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

## 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）#me
#查出所有小于六十的id,并且求出个数SELECT s_id,COUNT(s_id),AVG(s_score)
FROM score
WHERE s_score<60
GROUP BY s_id#内连接
SELECT s.s_id,s_name,avg_score
FROM student s
INNER JOIN(SELECT s_id,COUNT(s_id) "nopass",AVG(s_score) "avg_score"FROM scoreWHERE s_score<60GROUP BY s_id
) ss
ON s.s_id=ss.s_id
WHERE nopass>=2

#ck
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.s_id, st.s_name, AVG(sc.s_score)
FROM student st 
INNER JOIN score sc 
ON st.s_id=sc.s_id 
WHERE NOT sc.s_score >=60
GROUP BY st.s_id, st.s_name 
HAVING COUNT(c_id)>=2;

16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）

SELECT *
FROM student s
INNER JOIN score sc
ON s.s_id=sc.s_id
WHERE sc.s_score<60
AND sc.c_id=01
ORDER BY sc.s_score DESC

## 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数# me
SELECT s.s_id,s_name,s_score
FROM student s
INNER JOIN(SELECT s_id,s_scoreFROM scoreWHERE c_id=(SELECT c_idFROM courseWHERE c_name='数学')AND s_score<60
) a
ON s.s_id=a.s_id# 三表连接SELECT s.s_name,sc.s_score
FROM student s
INNER JOIN score sc ON s.s_id=sc.s_id
INNER JOIN course c ON sc.c_id=c.c_id
WHERE c.c_name='数学' AND sc.s_score<60


#检索"01"课程分数小于60，按分数降序排列的学生信息
#zx me
SELECT * 
FROM (SELECT *FROM scoreWHERE c_id=01 AND s_score<60	
) a
ORDER BY a.s_score DESC

## 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
#ck
SELECT st.s_name, sc.s_score
FROM score sc INNER JOIN student st ON sc.s_id=st.s_id
INNER JOIN course co ON co.c_id=sc.c_id WHERE co.c_name='数学' AND sc.s_score<60

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

#备注：1.因为要选出需要的字段 用case when 当c_id='01' then 可以得到对应的 s_core#2.因为GROUP UP 要与select 列一致，所以case when 加修饰max#3.因为最后要展现出每个同学的各科成绩为一行，所以用到case#me#在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
#包含在GROUP BY子句中的列不必包含在SELECT列表中
#这里用max巧妙的避开了两个条件SELECT s.s_id,
MAX(CASE WHEN c_id='01' THEN s_score ELSE 0 END) "语文",
MAX(CASE WHEN c_id='02' THEN s_score ELSE 0 END) "数学",
MAX(CASE WHEN c_id='03' THEN s_score ELSE 0 END) "英语",
AVG(s_score)
FROM score sc RIGHT JOIN student s ON s.s_id=sc.s_id
GROUP BY s_id
ORDER BY AVG(s_score) DESC#此处差个姓名
SELECT s.s_id,
MAX(CASE WHEN s.c_id='01' THEN s.s_score ELSE NULL END)"语文",
MAX(CASE WHEN s.c_id='02' THEN s.s_score ELSE NULL END)"数学",
MAX(CASE WHEN s.c_id='03' THEN s.s_score ELSE NULL END)"英语",
s.avg_score
FROM(SELECT sc.s_id,sc.c_id,s_score,a.avg_scoreFROM score scINNER JOIN (SELECT s_id,AVG(s_score) "avg_score"FROM scoreGROUP BY s_id) aON sc.s_id=a.s_id
) s
GROUP BY s.s_id
ORDER BY s.avg_score DESC# 姓名连接到内表
SELECT s.s_id,s.s_name,
MAX(CASE WHEN s.c_id='01' THEN s.s_score ELSE NULL END)"语文",
MAX(CASE WHEN s.c_id='02' THEN s.s_score ELSE NULL END)"数学",
MAX(CASE WHEN s.c_id='03' THEN s.s_score ELSE NULL END)"英语",
s.avg_score
FROM(SELECT st.s_name,sc.s_id,sc.c_id,s_score,a.avg_scoreFROM score scINNER JOIN student st ON sc.s_id=st.s_idINNER JOIN (SELECT s_id,AVG(s_score) "avg_score"FROM scoreGROUP BY s_id) aON sc.s_id=a.s_id
) s
GROUP BY s.s_id
ORDER BY s.avg_score DESC# 姓名连接到外表
SELECT s.s_id,st.s_name,
MAX(CASE WHEN s.c_id='01' THEN s.s_score ELSE NULL END)"语文",
MAX(CASE WHEN s.c_id='02' THEN s.s_score ELSE NULL END)"数学",
MAX(CASE WHEN s.c_id='03' THEN s.s_score ELSE NULL END)"英语",
s.avg_score
FROM(SELECT sc.s_id,sc.c_id,s_score,a.avg_scoreFROM score scINNER JOIN (SELECT s_id,AVG(s_score) "avg_score"FROM scoreGROUP BY s_id) aON sc.s_id=a.s_id
) s
INNER JOIN student st ON s.s_id=st.s_id
GROUP BY s.s_id
ORDER BY s.avg_score DESC# 或者只连接s_id，s_name
SELECT s.s_id,st.s_name,
MAX(CASE WHEN s.c_id='01' THEN s.s_score ELSE NULL END)"语文",
MAX(CASE WHEN s.c_id='02' THEN s.s_score ELSE NULL END)"数学",
MAX(CASE WHEN s.c_id='03' THEN s.s_score ELSE NULL END)"英语",
s.avg_score
FROM(SELECT sc.s_id,sc.c_id,s_score,a.avg_scoreFROM score scINNER JOIN (SELECT s_id,AVG(s_score) "avg_score"FROM scoreGROUP BY s_id) aON sc.s_id=a.s_id
) s
INNER JOIN (SELECT s_id,s_name FROM student
) st ON s.s_id=st.s_id 
GROUP BY s.s_id
ORDER BY s.avg_score DESC
#ck 它这里没有08学生
#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id,
MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END) "语文",
MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END) "数学",
MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END) "英语",
AVG(s_score)
FROM score
GROUP BY s_id
ORDER BY AVG (s_score) DESC

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

## 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：
## 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)#备注;1.
#平均数=各个数总和/总人数=3/ 4
#及格率及格个数/总人数=3/4
#总结:各种率都可以用avg平均值#2.then 后应该为1 ，因为SQL SERVER 的特殊性，所以用1.0#me
SELECT s.c_id,c_name,
MAX(s_score),MIN(s_score),AVG(s_score),
SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_id) "及格率",
SUM(CASE WHEN s_score>=70 THEN 1 ELSE 0 END)/COUNT(s_id) "中等率",
SUM(CASE WHEN s_score>=80 THEN 1 ELSE 0 END)/COUNT(s_id) "优良率",
SUM(CASE WHEN s.s_score>90 THEN 1 ELSE 0 END)/COUNT(s_id)"优秀率"
FROM score s
INNER JOIN course c
ON s.c_id=c.c_id
GROUP BY s.c_id
#zx
#查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，
#平均分，及格率，中等率，优良率，优秀率-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)
SELECT s.c_id,c.c_name,MAX(s.s_score),MIN(s.s_score),AVG(s.s_score)"平均分",
SUM(CASE WHEN s.s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_id)"及格率",
SUM(CASE WHEN s.s_score>=70 AND s.s_score<80 THEN 1 ELSE 0 END)/COUNT(s_id)"中等率",
SUM(CASE WHEN s.s_score>=80 AND s.s_score<90 THEN 1 ELSE 0 END)/COUNT(s_id)"优良率",
SUM(CASE WHEN s.s_score>90 THEN 1 ELSE 0 END)/COUNT(s_id)"优秀率"
FROM score AS s INNER JOIN course AS c ON s.c_id=c.c_id 
GROUP BY c_id

19、按各科成绩进行排序，并显示排名(重点row_number)


-- 19、按各科成绩进行排序，并显示排名(重点row_number)#row_number() over （order by 列）
#简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER()
#OVER(ORDER BY xlh DESC)是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。
#row_number(OVER(PARTITION BY COL1 ORDER BY COL2)表示根据COL1分组，在分组内部根据COL2排序，
#而此函数计算的值就表示每组内部排序后的顺序编好（一组内连续的唯一的）-- 19、按各科成绩进行排序，并显示排名(重点row_number)#row_number() over （order by 列）

20、查询学生的总成绩并进行排名（不重点）

## 20、查询学生的总成绩并进行排名（不重点）#me ck
SELECT s_id,SUM(s_score)
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC

21 、查询不同老师所教不同课程平均分从高到低显示(不重点)

## 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
#me
SELECT t.t_id,t.t_name,AVG(s.s_score)
FROM score s 
INNER JOIN course c ON s.c_id=c.c_id
INNER JOIN teacher t ON t.t_id=c.t_id
GROUP BY t.t_id,t.t_name
ORDER BY AVG(s_score) DESC#ck
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT te.t_id,te.t_name , AVG (sc.s_score)
FROM score sc 
INNER JOIN Course co ON sc.c_id=co.c_id 
INNER JOIN Teacher te ON co.t_id=te.t_id
GROUP BY te.t_id, te.t_name
ORDER BY AVG(sc.s_score)DESC;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）

总结：1.row_number () over (partition by 分组列 order by 排序列)2.先分组后排序，作为一个表3.从表中筛选出某行，用where备注：如果想选出学生信息，那成绩，就是以列的形式出现，如果1.2.3名以行的形式出现，就不能有对应的学生信息。（见25题
#ck
SELECT * 
FROM (SELECT st.s_id,st.s_name,st.s_birth,st.s_sex,c_id,s_score,row_number()over(PARTITION BY c_id ORDER BY s_score DESC) m
FROM score sc INNER JOIN student st ON sc.s_id=st.s_id) a
WHERE m IN (2,3)

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

## 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，
## 分别统计各分数段人数：课程ID和课程名称(重点和18题类似)# 备注：count（） else后为null 不能为0#ck
SELECT c_id,MAX(s_score) `max` ,MIN(s_score) `min` ,AVG(s_score) `avg` ,
AVG(CASE WHEN s_score>=0 AND s_score<60 THEN 1.0 ELSE 0.0 END) '及格率',
COUNT(CASE WHEN s_score>=0 AND s_score<60 THEN 1 ELSE NULL END) '及格人数',
AVG(CASE WHEN s_score>=60 AND s_score<70 THEN 1.0 ELSE 0.0 END) '中等率',
COUNT(CASE WHEN s_score>=60 AND s_score<70 THEN 1 ELSE NULL END) '中等人数',
AVG(CASE WHEN s_score>=70 AND s_score<85 THEN 1.0 ELSE 0.0 END) '优良率',
COUNT(CASE WHEN s_score>=70 AND s_score<85 THEN 1 ELSE NULL END) '优良人数',
AVG(CASE WHEN s_score>=85 AND s_score<100 THEN 1.0 ELSE 0.0 END) '优秀率',
COUNT(CASE WHEN s_score>=85 AND s_score<100 THEN 1 ELSE NULL END) '优秀人数'
FROM score
GROUP BY c_id

24、查询学生平均成绩及其名次（同19题，重点）

## 24、查询学生平均成绩及其名次（同19题，重点）
#me ck
SELECT s_id,AVG(s_score), row_number() over (ORDER BY AVG(s_score) DESC)
FROM score
GROUP BY s_id

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）

#ck
-- 25、查询各科成绩前三名的记录:如呆没有nax)
SELECT c_id,s_core,
(CASE WHEN m=l THEN s_score ELSE NULL END) '第一',
(CASE WHEN m=2 THEN s_score ELSE NULL END) '第二',
(CASE WHEN m=3 THEN s_score ELSE NULL END) '第三',
FROM(SELECT st.s_id,st.s_name, st.s_birth,st.s_sex,c_id,s_score,row_nunber() over(PARTITION BY c_id ORDER BY s_score DESC) m 
FROM score sc INNER JOIN student st ON sc.s_1d=st.s_id) a
WHERE m IN (1,2,3);

正确答案

最大值max 是这列的值 最大值（上图可看出）

-- 25、音询各科成绩须前三名的记录（有max）
-- 备注:把三行变成三列,取这行的最大数max
SELECT c_id,
MAX(CASE WHEN m=1 THEN s_score ELSE NULL END) "第一" ,
MAX(CASE WHEN m=2 THEN s_score ELSE NULL END) "第二" ,
MAX(CASE WHEN m=3 THEN s_score ELSE NULL END) "第三" ,
FROM (SELECT st.s_id, st.s_name, st.s_birth,st.s_sex,c_id,s_score,row_number() over(PARTITION BY c_id ORDER BY s_score DESC) m
FROM score sc INNER JOIN student st ON sc.s_id=st.s_id) a
WHERE m IN (l,2,3)
GROUP BY c_id;

备注:1.括号里是一个表 加括号命名为a

2。row_number

row_number( OVER(PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组，在分组内部根据COL2排序，
而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

26、查询每门课程被选修的学生数(不重点)

## 26、查询每门课程被选修的学生数(不重点)
#me
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id

27、 查询出只有两门课程的全部学生的学号和姓名(不重点)

## 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
# me
#查询出只选修两门课程的学生学号
SELECT s_id,COUNT(c_id)
FROM score
GROUP BY s_id
HAVING COUNT(c_id)=2# 信息
SELECT s_id,s_name
FROM student 
WHERE s_id IN (SELECT s_idFROM scoreGROUP BY s_idHAVING COUNT(c_id)=2)
#zx
SELECT s_id ,s_name FROM student 
WHERE s_id IN(SELECT s_id FROM scoreGROUP BY s_id HAVING COUNT(c_id)='2'
)
#ck
-- 27、查询出只选修了2门课程的全部学生的学号和姓名
SELECT st.s_id,st.s_name,COUNT(sc.c_id)
FROM student st 
INNER JOIN score sc 
ON st.s_id=sc.s_id
GROUP BY st.s_id,st.s_name
HAVING COUNT(sc.c_id)=2

28、查询男生、女生人数(不重点)

## 28、查询男生、女生人数(不重点)
#me ck
SELECT s_sex,COUNT(s_sex)
FROM student
GROUP BY s_sex

29、查询名字中含有"风"字的学生信息（不重点）

## 29 查询名字中含有"风"字的学生信息（不重点）
# me
SELECT * 
FROM student
WHERE s_name LIKE '%风%'

30、题忽略掉

## 30题忽略掉

31、查询1990年出生的学生名单（重点year）

## 31、查询1990年出生的学生名单（重点year）
#me
SELECT * 
FROM student
WHERE YEAR(s_birth)=1990

#ck-- 31、1990年出生的学生名单（注: Student表中s_birth列的类型是datetime)
SELECT*
FROM student
WHERE YEAR(s_birth)=1990;
-- 或者
SELECT * FROM student
WHERE s_birth LIKE "1990%"

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

## 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
#me ck
SELECT s.s_id,s_name,AVG(s_score)
FROM student s 
INNER JOIN score sc ON s.s_id=sc.s_id
GROUP BY s.s_id,s_name
HAVING AVG(s_score)>85

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）


## 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
#me ck
SELECT c_id,AVG(s_score)
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) ASC,c_id DESC

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）


## 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
#me ck
SELECT s_name,s_score
FROM student s 
INNER JOIN score sc ON s.s_id=sc.s_id
INNER JOIN course c ON c.c_id=sc.c_id
WHERE c_name='数学' AND s_score < 60

35、查询所有学生的课程及分数情况（重点）

## 35、查询所有学生的课程及分数情况（重点）
#备注:1.因为要选出需要的字段 用case when 当co.c_name='数学' then 可以得到对应的 sc.s_core#2.因为GROUP UP 要与select 列一致，所以case when 加修饰max#3.因为最后要展现出每个同学的各科成绩为一行，所以用到case#me 
#在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
#包含在GROUP BY子句中的列不必包含在SELECT列表中
#这里用max巧妙的避开了两个条件
SELECT s.s_id,
MAX(CASE WHEN c_id='01' THEN s_score  ELSE NULL END )"语文",
MAX(CASE WHEN c_id='02' THEN s_score  ELSE NULL END )"数学",
MAX(CASE WHEN c_id='03' THEN s_score  ELSE NULL END )"英语"
FROM student s
LEFT JOIN score sc
ON s.s_id = sc.s_id
GROUP BY s.s_id;```sql#ck
-- 35、查询所有学生的课程及分数情况;
SELECT student.s_id,student.s_name,
(SELECT s_score FROM score WHERE score.c_id='01' AND s_id=sc.s_id)AS '语文',
(SELECT s_score FROM score WHERE score.c_id='02' AND s_id=sc.s_id)AS '数学',
(SELECT s_score FROM score WHERE score.c_id='03' AND s_id =sc.s_id)AS '英语'
FROM
score AS sc
JOIN student ON sc.s_id = student.s_id 
GROUP BY sc.s_id

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

## 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
#me
SELECT c.c_id,s.s_name,c_name,sc.s_score
FROM student s
INNER JOIN(SELECT *FROM scoreWHERE s_score>70
) sc
ON s.s_id=sc.s_id
INNER JOIN course c
ON sc.c_id=c.c_id

#ck
-- 36、查询任何一门课程成绩在7o分以上的姓名、课程名称和分数
SELECT st.s_id,st.s_name, sc.s_score
FROM student st INNER JOIN score sc ON st.s_id=sc.s_id
INNER JOIN Course co ON sc.c_id=co.c_id WHERE sc.s_score>70

37、查询不及格的课程并按课程号从大到小排列(不重点)

## 37、查询不及格的课程并按课程号从大到小排列(不重点)
#me
SELECT *
FROM score
WHERE s_score<60
ORDER BY c_id DESC#me ck
SELECT s.s_id,s_name,c_name,s_score
FROM student s
INNER JOIN score sc ON s.s_id=sc.s_id
INNER JOIN course c ON sc.c_id=c.c_id
WHERE s_score<60
ORDER BY c.c_id

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

## 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）#me ck
SELECT s.s_id,s_name
FROM student s
INNER JOIN score sc ON s.s_id = sc.s_id
WHERE s_score>80 AND c_id='03'

39、求每门课程的学生人数（不重要）

## 39、求每门课程的学生人数（不重要）#me ck
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

#me
SELECT s_name,s_score
FROM student s
INNER JOIN(SELECT s_id,s_scoreFROM score scINNER JOIN(SELECT c.c_idFROM course cINNER JOIN teacher t ON c.t_id=t.t_idWHERE t_name='张三') aON sc.c_id=a.c_idORDER BY s_score DESCLIMIT 1
)b
ON s.s_id=b.s_id;#张三不一定只交一门课程 所有IN
SELECT s.s_id,s_name,s_score
FROM student s INNER JOIN score sc ON s.s_id=sc.s_id
WHERE s_score IN(SELECT MAX(s_score)FROM scoreWHERE c_id IN(SELECT c.c_idFROM course cINNER JOIN teacher t ON c.t_id=t.t_idWHERE t_name='张三')GROUP BY c_id
)
#ck
-- 40、查询选修”张三"老师所授课程的学生中成绩最高的学生姓名及其成绩
SELECT st.s_id, st.s_name ,sc.s_score
FROM Course co
INNER JOIN score sc ON sc.c_id=co.c_id
INNER JOIN student st ON st.s_id=sc.s_id
INNER JOIN teacher te ON te.t_id=co.t_id
WHERE te.t_name='张三'
ORDER BY sc.s_score DESC
LIMIT 1

另外

-- 40、查询选修“张三"老师所授课程的学生中成绩最低的学生姓名及其成绩#me ck
SELECT s.s_id,s.s_name,sc.s_score
FROM student s
INNER JOIN score sc ON s.s_id=sc.s_id
INNER JOIN course c ON sc.c_id=c.c_id
INNER JOIN teacher t ON c.t_id = t.t_id
WHERE t.t_name='张三'
ORDER BY sc.s_score ASC
LIMIT 1

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

## 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
# 意思是这个人的课程得了一样的分数#me 相关子查询
SELECT a.s_id,a.c_id,a.s_score
FROM score a 
WHERE a.s_score = ALL(SELECT s_scoreFROM score bWHERE a.s_id=b.s_id
)# ck
# 平均分=最大分
SELECT b.* FROM
score AS b 
INNER JOIN
(SELECT s_id FROM Score 
GROUP BY s_id 
HAVING SUM(s_score)/COUNT(s_score) = MAX(s_score)) 
AS a ON b.s_id = a.s_id

42、查询每门功成绩最好的前两名（同22和25题）

## 42、查询每门功成绩最好的前两名（同22和25题）#me
SELECT * 
FROM (SELECT st.s_id,st.s_name,st.s_birth,st.s_sex,c_id,s_score,row_number()over(PARTITION BY c_id ORDER BY s_score DESC) m
FROM score sc INNER JOIN student st ON sc.s_id=st.s_id) a
WHERE m IN (1,2)

43、统计每门课程的学生选修人数（超过5人的课程才统计）。

## 43、统计每门课程的学生选修人数（超过5人的课程才统计）。
## 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）#me ck
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id
HAVING COUNT(s_id)>5
ORDER BY COUNT(s_id) DESC,c_id ASC

44、检索至少选修两门课程的学生学号（不重要）

## 44、检索至少选修两门课程的学生学号（不重要）
#me ck
SELECT s_id,COUNT(c_id)
FROM score
GROUP BY s_id
HAVING COUNT(c_id)>=2

45、 查询选修了全部课程的学生信息（重点划红线地方）

## 45、 查询选修了全部课程的学生信息（重点划红线地方）#me
SELECT * 
FROM student
WHERE s_id IN
(SELECT s_idFROM scoreGROUP BY s_idHAVING COUNT(c_id)=(SELECT COUNT(*) FROM course)
)SELECT s.*
FROM student s
INNER JOIN(SELECT s_idFROM scoreGROUP BY s_idHAVING COUNT(c_id)=(SELECT COUNT(*) FROM course)
) b
ON s.s_id=b.s_idSELECT s.*
FROM student s
INNER JOIN score sc
ON s.s_id=sc.s_id
GROUP BY s_id
HAVING COUNT(c_id)=(SELECT COUNT(*) FROM course
)

47、查询没学过“张三”老师讲授的任一门课程的学生姓名

## 47、查询没学过“张三”老师讲授的任一门课程的学生姓名#me
#not inSELECT s_name
FROM student
WHERE s_id NOT IN(SELECT s.s_id FROM student sINNER JOIN score sc ON s.s_id=sc.s_idINNER JOIN course c ON sc.c_id=c.c_idINNER JOIN teacher tON c.t_id=t.t_idWHERE t.t_name='张三'
)

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

#me
SELECT s_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(c_id)>=2SELECT student.s_id,s_name,AVG(score.s_score)
FROM student 
INNER JOIN score ON student.s_id=score.s_id
WHERE student.s_id IN(SELECT s_idFROM scoreWHERE s_score<60GROUP BY s_idHAVING COUNT(c_id)>=2
)
GROUP BY s_id,s_name;

#zx
#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT b.s_id,b.s_name,a.aaa 
FROM(SELECT t.s_id ,COUNT(c_id) ,AVG(s_score)"aaa"FROM(SELECT * FROM  scoreWHERE s_score <60) AS tGROUP BY t.s_id HAVING COUNT(c_id)>=2 ) AS a
INNER JOIN student AS b
ON a.s_id=b.s_id
#ck
-- 48、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id, COUNT(s_score) ,AVG(s_score)
FROM score
WHERE s_score <60
GROUP BY s_id
HAVING COUNT(s_score) >=2;#还差姓名 inner一下SELECT *
FROM student t
INNER JOIN(SELECT s_id, COUNT(s_score),AVG(s_score)FROM scoreWHERE s_score <60GROUP BY s_idHAVING COUNT(s_score) >=2
) a
ON t.s_id=a.s_id

46、查询各学生的年龄（精确到月份）

备注：年份转换成月份，比如结果是1.9，ditediff 最后取1年

## 46、查询各学生的年龄（精确到月份）
#备注：年份转换成月份，比如结果是1.9，ditediff 最后取1年
#me
SELECT s_id,s_birth,
FLOOR(DATEDIFF('2018-6-19',s_birth)/365)
FROM student;

47、查询本月过生日的学生（无法使用week、date(now()）

## 47、查询本月过生日的学生（无法使用week、date(now()）
#ck
-- 47、查询本周过生日的学生
SELECT * 
FROM student
WHERE WEEK(s_birth)= WEEK(NOW())-- 下周
SELECT * 
FROM student
WHERE WEEK(s_birth)= WEEK(NOW())+1-- 本月
SELECT * 
FROM student
WHERE MONTH(s_birth)= MONTH(NOW())+1-- 下月
SELECT * 
FROM student
WHERE MONTH(s_birth)= MONTH(NOW())+1

另外

如需要此篇的SQL文件
可以联系我

也是写了1500+行
在这里插入图片描述

总结

最值查询

宋红康视频中相关知识的所用案例为：

在这里插入图片描述
在这里插入图片描述

一般最值查询有四种方法可以实现，但是逻辑是相同的

  1. =最值
  2. <(>)all
  3. limit 1
  4. 连接

举例一：

#8.查询平均工资最低的部门信息
#方式1：
SELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal));
#方式2：ALL
SELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) <= ALL(SELECT AVG(salary)FROM employeesGROUP BY department_id));#方式3： LIMITSELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) =(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 1		));#方式4：
SELECT d.*
FROM departments d,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 0,1) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id

举例二：

## 9、查询所有课程成绩小于60分的学生的学号、姓名 
# 法一 60大于成绩 所有的SELECT DISTINCT student.s_id,s_name
FROM student 
INNER JOIN score
ON student.s_id=score.s_id
WHERE 60> ALL(SELECT s_scoreFROM scoreWHERE score.s_id=student.s_id
) #法二 大于最大的
SELECT s_id,s_name
FROM student
WHERE 60> (SELECT MAX(s_score)FROM scoreWHERE score.s_id=student.s_idGROUP BY score.s_id
)# 法三 大于降序第一个成绩值 
SELECT s_id,s_name
FROM student
WHERE 60> (SELECT s_scoreFROM scoreWHERE score.s_id=student.s_idORDER BY s_score DESCLIMIT 1
)# 法四 内连接
SELECT s.s_id,s_name,MAX(s_score)
FROM student s INNER JOIN score sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING MAX(s_score)<60

7种SQL JOINS的实现

视频学习：P29 29-使用SQL99实现7种JOIN操作 21:40
0203

博客学习：MySQL笔记：第06章_多表查询
在这里插入图片描述

#中图：内连接 A∩B#左上图：左外连接#右上图：右外连接#左中图：A - A∩B#右中图：B-A∩B#左下图：满外连接
#左中图 + 右上图 A∪B#右下图
#左中图 + 右中图	A ∪B- A∩B 或者 (A -	A∩B) ∪ （B - A∩B）

最后

感谢所有支持我的，祝生活愉快

词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：Python UI自动化测试详解

下一篇：API接口测试-postman自动生成测试报告

相关内容

热门资讯

AWSECS：访问外部网络时出... 如果您在AWS ECS中部署了应用程序，并且该应用程序需要访问外部网络，但是无法正常访问，可能是因为...
银河麒麟V10SP1高级服务器... 银河麒麟高级服务器操作系统简介： 银河麒麟高级服务器操作系统V10是针对企业级关键业务...
【NI Multisim 14...   目录 序言 一、工具栏 🍊1.“标准”工具栏 🍊 2.视图工具...
不能访问光猫的的管理页面 光猫是现代家庭宽带网络的重要组成部分，它可以提供高速稳定的网络连接。但是，有时候我们会遇到不能访问光...
AWSElasticBeans... 在Dockerfile中手动配置nginx反向代理。例如，在Dockerfile中添加以下代码：FR...
月入8000+的steam搬砖... 大家好，我是阿阳 今天要给大家介绍的是 steam 游戏搬砖项目，目前...
​ToDesk 远程工具安装及... 目录 前言 ToDesk 优势 ToDesk 下载安装 ToDesk 功能展示 文件传输 设备链接 ...
北信源内网安全管理卸载 北信源内网安全管理是一款网络安全管理软件，主要用于保护内网安全。在日常使用过程中，卸载该软件是一种常...
AWS管理控制台菜单和权限 要在AWS管理控制台中创建菜单和权限，您可以使用AWS Identity and Access Ma...
AWR报告解读 WORKLOAD REPOSITORY PDB report (PDB snapshots) AW...