MySQL面试50题【mysql】

前言

当时学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：原贴 评论区

1
2
3
4
5
6
7
8
9
10

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) –学生编号,课程编号,分数
1
2
3
4
5
6
7
8
9
10
11

根据以上信息按照下面要求写出对应的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`)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 插入学生表测试数据
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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

其中重点为：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
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

#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
) 
1
2
3
4
5
6
7
8
9
10
11

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

#zx
SELECT 
s_id,AVG(s_score)
FROM score
GROUP BY s_id HAVING AVG(s_score)>60
1
2
3
4
5

#me
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score) > 60
1
2
3
4
5

#查询平均成绩大于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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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
1
2
3
4
5

#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)
1
2
3
4
5
6
7

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

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

1
2
3
4
5
6

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='张三'
		)

	)


)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

#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='张三'
	) 
)
1
2
3
4
5
6
7
8
9
10
11

#错误答案；

#因为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 !='张三')
1
2
3
4
5
6
7
8

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='张三'
		)

	)


)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

#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='张三'
1
2
3
4
5
6

-- 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 ='张三')
1
2
3
4
5
6
7

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
	)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

#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
) 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 查询学过编号为"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')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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

#me
SELECT SUM(s_score)
FROM score
WHERE c_id=02;
1
2
3
4

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

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

同题目2
1

#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



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

#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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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
)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

#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
1
2
3
4
5
6

#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)


1
2
3
4
5
6

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'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#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')
1
2
3
4
5
6
7
8
9

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

#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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

以上结果不对 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'
1
2
3
4
5
6
7
8

修改如下

---

没选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不一样的课

1
2
3
4
5
6
7
8
9
10
11

---

这个不对

# 相关子查询 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)
	)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

另外

#查询选了所有课程的学生信息
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
		)
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

数据库原理相关理论：
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)
	);
1
2
3
4
5
6
7
8
9
10
11

从而用 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=条件为假或结论为真

真值表

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

(∀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)
	);
1
2
3
4
5
6
7
8
9
10
11
12

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))；

1
2
3
4
5
6
7
8
9
10
11

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='张三'
	)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

#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='张三')


1
2
3
4
5
6
7
8
9
10

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#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;
1
2
3
4
5
6
7
8
9

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
1
2
3
4
5
6
7

## 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#检索"01"课程分数小于60，按分数降序排列的学生信息
#zx me
SELECT * 
FROM (
	SELECT *
	FROM score
	WHERE c_id=01 
	AND s_score<60	
) a
ORDER BY a.s_score DESC
1
2
3
4
5
6
7
8
9
10
11

## 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
1
2
3
4
5

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




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

#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

1
2
3
4
5
6
7
8
9
10
11

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





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#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 

1
2
3
4
5
6
7
8
9
10
11
12
13

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 列）
1
2
3
4
5
6
7
8
9
10
11
12

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

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

#me ck
SELECT s_id,SUM(s_score)
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

总结：1.row_number () over (partition by 分组列 order by 排序列)

2.先分组后排序，作为一个表

3.从表中筛选出某行，用where

备注：如果想选出学生信息，那成绩，就是以列的形式出现，如果1.2.3名以行的形式出现，就不能有对应的学生信息。（见25题


1
2
3
4
5
6
7
8
9

#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)
1
2
3
4
5

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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

1
2
3
4
5
6

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);

1
2
3
4
5
6
7
8
9
10

正确答案

最大值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
2
3
4
5
6
7
8
9
10
11

备注: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

1
2
3
4
5
6

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

)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#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'
)

1
2
3
4
5
6
7
8

#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

1
2
3
4
5
6
7
8
9

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

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

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

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

30、题忽略掉

## 30题忽略掉
1

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

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

#ck

-- 31、1990年出生的学生名单（注: Student表中s_birth列的类型是datetime)
SELECT*
FROM student
WHERE YEAR(s_birth)=1990;
-- 或者
SELECT * FROM student
WHERE s_birth LIKE "1990%"
1
2
3
4
5
6
7
8
9

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
1
2
3
4
5
6
7

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
1
2
3
4
5
6
7

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
1
2
3
4
5
6
7
8

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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
1
2
3
4
5
6
7
8
9
10
11
12

#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
1
2
3
4
5

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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'
1
2
3
4
5
6
7

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

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

#me ck
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id
1
2
3
4
5
6

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
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

#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

1
2
3
4
5
6
7
8
9
10
11

另外

-- 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

1
2
3
4
5
6
7
8
9
10
11
12

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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)

1
2
3
4
5
6
7
8

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
1
2
3
4
5
6
7
8
9

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

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

1
2
3
4
5
6
7

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
)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

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='张三'
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#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


1
2
3
4
5
6
7
8
9
10
11
12
13
14

#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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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;

1
2
3
4
5
6
7

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

另外

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

也是写了1500+行

总结

最值查询

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

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

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

举例一：

#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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

---

举例二：

## 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

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）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

最后

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