SQL50题(MySQL)

首先创建一下数据库以及题目需要用的表：

CREATE TABLE Student 
(		SId VARCHAR(6),
		Sname VARCHAR(10),
		Sage DATE,
		Ssex VARCHAR(10)
);

CREATE TABLE Course
(		CId VARCHAR(10),
		Cname VARCHAR(20),
		TId VARCHAR(6)
);

CREATE TABLE Teacher
(		TId VARCHAR(6),
		Tname VARCHAR(10)
);

CREATE TABLE SC
(		SId VARCHAR(6),
		CId VARCHAR(10),
		score FLOAT
);

向表中插入数据：

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('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');

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 SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

接下来开始写题：

1. 查询01课程比02课程成绩高的学生的信息和成绩

第一个想法是利用左连接找到所有选了01或02的同学，再从中找到01比02高的同学。这样做的困难点在于该表中，有些同学只选择了01，或只选了02，无法对同一个同学进行比较。

SELECT student.*, CId, score
FROM sc LEFT JOIN student ON sc.SId = student.SId WHERE sc.CId in ('01', '02');

查询结果为：

所以尝试另一种方法：分别查询出选了01的同学和选了02的同学，再连接成一张同时选了01和02的同学的表，随后进行成绩比较。

SELECT t1.SId, t1.CId AS course01, t1.score AS score01, t2.CId AS course02, t2.score AS score02, t1.Sname, t1.Sage, t1.Ssex
FROM 
(SELECT sc.*, student.Sname, student.Sage, student.Ssex FROM sc, student WHERE sc.SId = student.SId AND sc.CId = '01') AS t1,
(SELECT sc.*, student.Sname, student.Sage, student.Ssex FROM sc, student WHERE sc.SId = student.SId AND sc.CId = '02') AS t2
WHERE t1.SId = t2.SId AND t1.score > t2.score;

查询的结果为：

总结：这道题主要是考察各种连接，一定要对连接具体会生成什么样的表有清晰了解。可以看到，第一次查询结果每一列只有一门课程的成绩，第二次查询结果在同一条记录中存储了两门课程的成绩。也可以使用inner join等方法，基本思想都是要分别查询01和02两门课程的同学再连接。

1.1 查询同时存在01和02课程的情况

SELECT t1.SId, t1.CId AS class01, t1.score AS score01, t2.CId AS class02, t2.score AS score02
FROM
(SELECT * FROM sc WHERE CId = '01') as t1, (SELECT * FROM sc WHERE CId = '02') as t2
WHERE t1.SId = t2.SId

select a.*
from (select * from sc where CId in ('01', '02'))a
group by a.SId
having count(a.CId)>=2

第一种方法和上一题一样，利用连接，可以同时显示出两门课程的成绩。第二种方法是嵌套查询，结果中只有某一门课的成绩。

1.2 查询存在01课程但可能不存在02课程的情况(不存在时显示为null)

SELECT t1.SId, t1.CId AS class01, t1.score AS score01, t2.CId AS class02, t2.score AS score02
FROM 
(SELECT * FROM sc WHERE CId = '01')t1 LEFT JOIN (SELECT * FROM sc WHERE CId = '02')t2 ON t1.SId = t2.SId;

1.3 查询不存在01课程但存在02课程的情况

SELECT t2.SId, t1.CId AS class01, t1.score AS score01, t2.CId AS class02, t2.score AS score02
FROM 
(SELECT * FROM sc WHERE CId = '02')t2 LEFT JOIN (SELECT * FROM sc WHERE CId = '01')t1 ON t2.SId = t1.SId
WHERE t1.score IS NULL;

select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) 
AND sc.CId= '02';

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

1. 使用group by函数对sc表按照学生进行分组，并计算平均成绩
2. 查询平均成绩大于等于60分的SId
3. 与student表做右连接

SELECT * FROM student RIGHT JOIN (
		SELECT SId, ROUND(AVG(score), 2) as avgscore
		FROM sc
		GROUP BY SId
		HAVING avgscore >= 60
)r
ON Student.SId = r.SId;

3. 查询在SC表存在成绩的学生信息

SELECT DISTINCT student.*   
FROM student, sc
WHERE student.SId = sc.SId;

这一题主要是考察DISTINCT，这里如果没有使用DISTINCT关键字，将会出现多条重复的信息。

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

SELECT student.SId, student.Sname, r.classes, r.sum 
FROM student, (
		SELECT SId, COUNT(*) as classes, SUM(score) as sum 
		FROM sc
		GROUP By SId
)r 
WHERE student.SId = r.SId;

要注意的是，这样只能查询出选了课的学生的信息，而没有选课的学生不会出现在结果中，与题意不符，因此这里需要用到连接。

SELECT student.SId, student.Sname, r.classes, r.sum 
FROM student LEFT JOIN (
		SELECT SId, COUNT(*) as classes, SUM(score) as sum 
		FROM sc
		GROUP By SId
)r 
ON student.SId = r.SId;

查询结果：

这里可以进一步完善的点是如何将Null显示为0。

4.1 查有成绩的学生信息

延续上一题的思路：

SELECT a.SId as SId, Sname, Sage, Ssex
FROM (select DISTINCT SId from sc) a LEFT JOIN student
ON a.SId = student.SId;

但是这里存在一个嵌套查询一个连接，可能查询效率不高，因此可以考虑其他方法：

# 其他方法一：使用IN关键字
select * from student
where student.sid in (select sc.sid from sc);

# 其他方法二：使用EXISTS关键字
select * from student
where EXISTS 
(select SId from sc where student.SId = sc.SId);

5. 查询「李」姓老师的数量

SELECT COUNT(*) 
FROM teacher
WHERE Tname LIKE '李%';

6. 查询学过「张三」老师授课的同学的信息

1. 从teacher表中查出张三老师的TId
2. 从course表中查出该TId所授课程的CId
3. 从sc表中查出选择了这些课程的同学SId
4. 从student表中查询这些同学的信息

select student.* 
from student join (
		select SID
		from sc
		where CID IN (
			select CId from
				(select TId from teacher where Tname = '张三') a join course b on a.TId = b.TId)
) c 
on student.SId = c.SId;

如果对查询结果能够准确预判，这里则并不需要用这么复杂的方法，无需用到表的连接：

select student.* 
from student,teacher,course,sc
where 
    student.SId = sc.SId 
    and course.CId=sc.CId 
    and course.TId = teacher.TId 
    and Tname = '张三';

7. 查询没有学全所有课程的同学的信息

一开始想利用连接会产生Null来进行筛选，但是不会写…而且这样做效率肯定非常低，尤其是课程数量很多的情况下。
因此，尝试方法二：

1. 计算course表中共有多少课程
2. 按SId分组，计算sc表中每个学生上了多少门课
3. 选择课程少于总课程数量的学生的SId
4. 根据上述查询出的SId，查询student表中的学生信息

select student.*
from student,
(select SId from sc group by SId having count(*) < (select count(*) from course))a
where student.SId = a.SId;

很明显，这样做可能会导致有些同学一门课都没选，但是不会出现在结果集中。
因此对方法二进行改进：选择全部修完的学生的SId，再选择不属于这个结果集内的SId

select *
from student
where SId
not in (
		select SId from sc group by SId having count(*) = (select count(*) from course)
		);

需要注意的是这里重复出现SId，最好写清楚哪张表的SId。

8. 查询至少有一门课与学号为 “01” 的同学所学相同的同学的信息

select student.*
from student, 
(select DISTINCT SId from sc where CId in (select CId from sc where SId = '01'))a
where student.SId = a.SId;

9. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

这一题其实与第七题类似，只不过第七题可以使用count函数直接计算，不需要知道具体的课程，但是在这一题中需要确保课程数量和课程都相同：

1. 将sc表中每个同学选择的课程用group_concat函数拼接起来
2. 找到01所选的课程的拼接字符串
3. 查找选课字符串和01的字符串相同的学生SId
4. 根据这些SId，查询学生表中的信息

select student.*
from student, 
(select SId, group_concat(CId) as cstring from sc group by SId)a
where student.SId = a.SId and a.cstring in (
		select group_concat(CId) as cstring from sc group by SId having SId = '01');

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

1. 通过张三老师的TId，找到他所授的所有课程的CId
2. 找到所有学习了任意一门张三老师所授课程的学生SId
3. 在student表中，查询所有除了上述学生以外的学生姓名

select Sname
from student
where student.SId not in (
	select sc.SId from sc where sc.CId in (
		select course.CId from teacher, course where Tname = '张三' and teacher.TId = course.TId));

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.SId, Sname, r.avgscore
from student,
(select SId, score, avg(score)as avgscore
from sc
group by SId
having score < 60 and count(*) >= 2)r
where r.SId = student.SId;

12. 检索01课程分数小于60，按分数降序排列的学生信息

select student.SId, sname, sage, ssex, score
from student, sc
where student.SId = sc.SId and CId = '01' and score < 60
order by score DESC;

这里需要注意的是order by子句一定要放在最后。

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sc.*, avg(score) as avg
from sc
group by SId
order by avg DESC;

可以看到，查询结果中并没有显示所有课程的信息。因此需要改进：

# 改进方法一：使用窗口函数
select sc.*, avg(score) over (partition by SId) avg
from sc
order by avg DESC;

# 改进方法二：利用连接
select sc.*, avg
from sc left join (
	select sc.*, avg(score) as avg
	from sc
	group by SId
)r
on sc.SId = r.SId
order by avg DESC;