MySQL面试50题【mysql】
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_id
WHERE 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 a
LEFT JOIN (SELECT s_id,c_id,s_score FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
WHERE 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_id
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
WHERE 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_id
FROM course
WHERE t_id=(
SELECT t_id
FROM teacher
WHERE t_name='张三'
)
)
# 再查
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(
SELECT s_id
FROM score
WHERE c_id IN( # 张三不一定支教一个课
SELECT c_id
FROM course
WHERE t_id=(
SELECT t_id
FROM teacher
WHERE t_name='张三'
)
)
)
#zx
SELECT * FROM student
WHERE s_id NOT IN
(SELECT s_id FROM score
WHERE c_id=(
SELECT c_id FROM
(teacher AS a INNER JOIN course AS b
ON 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、查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
#me
SELECT s_id,s_name
FROM student
WHERE s_id IN(
SELECT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course
WHERE t_id=(
SELECT t_id
FROM teacher
WHERE 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 a
INNER JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON 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 a
INNER JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON 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 a
INNER JOIN
(SELECT s_id FROM score WHERE c_id='02') AS b
ON 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 a
INNER JOIN
(SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
)
-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
#me
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'
)
)
-- 查询学过编号为"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_score
FROM score
WHERE 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_score
FROM score
WHERE 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_score
FROM score
WHERE score.s_id=student.s_id
)
#法二
SELECT s_id,s_name
FROM student
WHERE 60> (
SELECT MAX(s_score)
FROM score
WHERE score.s_id=student.s_id
GROUP BY score.s_id
)
# 法三 大于降序第一个成绩值
SELECT s_id,s_name
FROM student
WHERE 60> (
SELECT s_score
FROM score
WHERE score.s_id=student.s_id
ORDER BY s_score DESC
LIMIT 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_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)<3
)
#反求法
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(
SELECT s_id
FROM score
GROUP BY s_id
HAVING 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_id
FROM score
WHERE c_id = ANY (
SELECT c_id FROM score
WHERE s_id ='01'
)
)AND s_id!='01'
#zx
SELECT s_id,s_name FROM student
WHERE S_id IN (
SELECT DISTINCT s_id
FROM score
WHERE c_id IN
( SELECT c_id FROM score
WHERE 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 score
WHERE 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 score
WHERE c_id IN(
SELECT c_id FROM score
WHERE 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 score
WHERE c_id IN
( SELECT c_id FROM score
WHERE 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 score
WHERE c_id IN(
SELECT c_id FROM score
WHERE 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 scy
WHERE scy.c_id='01'AND
NOT EXISTS
(SELECT *
FROM score scz
WHERE scz.s_id=scx.s_id AND
scz.c_id=scy.c_id)
)
SELECT DISTINCT s_id
FROM score
WHERE NOT EXISTS
(SELECT *
FROM score scz
WHERE EXISTS
(SELECT * FROM score scx
WHERE scx.c_id=scz.c_id AND scx.s_id='01')
AND NOT EXISTS
(SELECT * FROM score scy
WHERE scy.s_id=score.s_id AND scy.c_id=scz.c_id)
)
GROUP BY s_id
HAVING COUNT(c_id)=3
AND s_id!='01';
另外
#查询选了所有课程的学生信息
SELECT s_id,s_name
FROM student
WHERE NOT EXISTS
(SELECT *
FROM course
WHERE NOT EXISTS
(
SELECT *
FROM score
WHERE s_id=student.s_id
AND 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 Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno
AND 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=条件为假或结论为真
真值表
p | q | result |
---|---|---|
0 | 0 | 1 |
0 | 1 | 1 |
1 | 0 | 0 |
1 | 1 | 1 |
该查询可以转换为如下等价形式:
(∀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 SCY
WHERE SCY.Sno=' 201215122'AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno)
);
SELECT Sno
FROM student
WHERE NOT EXISTS
(SELECT *
FROM course
WHERE EXISTS (SELECT * FROM SC SCX
WHERE SCX.Cno=course.Cno AND SCX.Sno='201215122')
AND NOT EXISTS
(SELECT * FROM SC SCY
WHERE SCY.Sno = student.Sno AND SCY.Cno=course.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_id
FROM course c
INNER JOIN teacher t
ON c.t_id=t.t_id
WHERE t_name='张三'
)
#查询没学过
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(
SELECT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course c
INNER JOIN teacher t
ON c.t_id=t.t_id
WHERE t_name='张三'
)
)
#ck
#14、查询没学过"张三"老师讲授的任一门课程的学生姓
SELECT *
FROM student
WHERE s_id NOT 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='张三')
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 score
WHERE s_score<60
GROUP 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_score
FROM score
WHERE c_id=(
SELECT c_id
FROM course
WHERE 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 score
WHERE 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_score
FROM score sc
INNER JOIN (
SELECT s_id,AVG(s_score) "avg_score"
FROM score
GROUP BY s_id
) a
ON 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_score
FROM score sc
INNER JOIN student st ON sc.s_id=st.s_id
INNER JOIN (
SELECT s_id,AVG(s_score) "avg_score"
FROM score
GROUP BY s_id
) a
ON 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_score
FROM score sc
INNER JOIN (
SELECT s_id,AVG(s_score) "avg_score"
FROM score
GROUP BY s_id
) a
ON 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_score
FROM score sc
INNER JOIN (
SELECT s_id,AVG(s_score) "avg_score"
FROM score
GROUP BY s_id
) a
ON 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_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)=2
)
#zx
SELECT s_id ,s_name FROM student
WHERE s_id IN(
SELECT s_id FROM score
GROUP 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 score
WHERE 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_score
FROM score sc
INNER JOIN(
SELECT c.c_id
FROM course c
INNER JOIN teacher t ON c.t_id=t.t_id
WHERE t_name='张三'
) a
ON sc.c_id=a.c_id
ORDER BY s_score DESC
LIMIT 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 score
WHERE c_id IN(
SELECT c.c_id
FROM course c
INNER JOIN teacher t ON c.t_id=t.t_id
WHERE 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_score
FROM score b
WHERE 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_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)=(
SELECT COUNT(*) FROM course
)
)
SELECT s.*
FROM student s
INNER JOIN(
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id)=(
SELECT COUNT(*) FROM course
)
) b
ON s.s_id=b.s_id
SELECT 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 in
SELECT s_name
FROM student
WHERE s_id NOT IN(
SELECT s.s_id
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='张三'
)
48、查询两门以上不及格课程的同学的学号及其平均成绩
#me
SELECT s_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(c_id)>=2
SELECT 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_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING 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 score
WHERE s_score <60) AS t
GROUP 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 score
WHERE s_score <60
GROUP BY s_id
HAVING 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+行
总结
最值查询
宋红康视频中相关知识的所用案例为:
一般最值查询有四种方法可以实现,但是逻辑是相同的
- =最值
- <(>)all
- limit 1
- 连接
举例一:
#8.查询平均工资最低的部门信息
#方式1:
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dept_avg_sal
)
);
#方式2:ALL
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
);
#方式3: LIMIT
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) =(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1
)
);
#方式4:
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 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_score
FROM score
WHERE score.s_id=student.s_id
)
#法二 大于最大的
SELECT s_id,s_name
FROM student
WHERE 60> (
SELECT MAX(s_score)
FROM score
WHERE score.s_id=student.s_id
GROUP BY score.s_id
)
# 法三 大于降序第一个成绩值
SELECT s_id,s_name
FROM student
WHERE 60> (
SELECT s_score
FROM score
WHERE score.s_id=student.s_id
ORDER BY s_score DESC
LIMIT 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
博客学习:MySQL笔记:第06章_多表查询
#中图:内连接 A∩B
#左上图:左外连接
#右上图:右外连接
#左中图:A - A∩B
#右中图:B-A∩B
#左下图:满外连接
#左中图 + 右上图 A∪B
#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
最后
感谢所有支持我的,祝生活愉快