sql 50面试题记录（前序）（T1-T10）

内容部分转自 https://www.jianshu.com/p/3f27a6dced16，以下为修改过的版本

准备工作

准备数据表

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);


create table teacher (
t_id varchar(10),
t_name varchar(20)
);

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10)
);

添加测试数据

insert into student (s_id, s_name, s_age, s_sex)
values  ('01' , '赵雷' , '1990-01-01' , '男'),
        ('02' , '钱电' , '1990-12-21' , '男'),
        ('03' , '孙风' , '1990-05-20' , '男'),
        ('04' , '李云' , '1990-08-06' , '男'),
        ('05' , '周梅' , '1991-12-01' , '女'),
        ('06' , '吴兰' , '1992-03-01' , '女'),
        ('07' , '郑竹' , '1989-07-01' , '女'),
        ('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '语文' , '02'),
        ('02' , '数学' , '01'),
        ('03' , '英语' , '03');

insert into teacher (t_id, t_name)
values  ('01' , '张三'),
        ('02' , '李四'),
        ('03' , '王五');

insert into score (s_id, c_id, score)
values  ('01' , '01' , 80),
        ('01' , '02' , 90),
        ('01' , '03' , 99),
        ('02' , '01' , 70),
        ('02' , '02' , 60),
        ('02' , '03' , 80),
        ('03' , '01' , 80),
        ('03' , '02' , 80),
        ('03' , '03' , 80),
        ('04' , '01' , 50),
        ('04' , '02' , 30),
        ('04' , '03' , 20),
        ('05' , '01' , 76),
        ('05' , '02' , 87),
        ('06' , '01' , 31),
        ('06' , '03' , 34),
        ('07' , '02' , 89),
        ('07' , '03' , 98);

题目

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

方法一：自联结（通过给一个表两个别名来对比相同学号下各科成绩的连结，再对课一和课二进行限制，同时满足课一的分数要高，最后和学生信息表对比获得学生信息）
SELECT a.s_id,a.score as '课程一成绩',b.score as '课程二成绩'
FROM score a,score b,student c
WHERE c.s_id = a.s_id 
and a.s_id = b.s_id
AND a.c_id = '01' 
and b.c_id = '02'
and a.score >b.score

方法二：分别查出课程一的成绩和课程二的成绩，再通过学号链接两个查询结果限定分数
SELECT a.s_id,s1 as '课程一成绩',s2 as '课程二成绩'  FROM
(SELECT s_id,score as s1 from score WHERE score.c_id="01") as a
INNER JOIN
(SELECT s_id,score as s2 from score WHERE score.c_id="02")as b
on a.s_id = b.s_id
WHERE s1>s2

查询结果：

知识点：自联结，left jion

2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数

同理，方法一：
SELECT a.s_id,s1 as '课程一成绩',s2 as '课程二成绩'from 
(SELECT s_id,score as s1 from score WHERE score.c_id="01") as a
LEFT JOIN
(SELECT s_id,score as s2 FROM score WHERE score.c_id="02")as b 
on a.s_id = b.s_id
WHERE a.s1<b.s2


方法二：
SELECT a.s_id,a.score as '课程一成绩',b.score as '课程二成绩'
FROM score a,score b,student c
WHERE c.s_id = a.s_id 
and a.s_id = b.s_id
AND a.c_id = '01' 
and b.c_id = '02' 
and a.score <b.score

结果：

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

使用分组通过对每个学号进行分组，得出平均成绩
SELECT a.s_id,a.s_name,AVG(b.score) as "平均成绩"
from student a LEFT JOIN score b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING AVG(b.score)>60

结果：

4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select score.s_id,student.s_name,avg(score) as "平均成绩"
from score left join student
on score.s_id = student.s_id 
group by student.s_id 
having avg(score)<60

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

select student.s_id,student.s_name,count(c_id) as '选课总数',sum(score) as '总成绩'
from student left join score on student.s_id = score.s_id
group by score.s_id
ORDER BY sum(score) desc

结果：

6.查询"李"姓老师的数量

SELECT COUNT(1) 
FROM teacher
WHERE teacher.t_name LIKE "李%"

结果：

考点：模糊查询

7.查询学过"张三"老师授课的同学的信息

方法一：
select student.*
from score,student,teacher,course
where teacher.t_id = course.t_id
and course.c_id = score.c_id
and score.s_id = student.s_id
and t_name = '张三'

方法二：
先查张三老师有哪些课
再查哪些学生上过这些课
在查学生的信息
SELECT DISTINCT * FROM student WHERE s_id in (
	SELECT s_id FROM score WHERE score.c_id in (
		SELECT c_id FROM course LEFT JOIN teacher ON course.t_id=teacher.t_id WHERE teacher.t_name="张三"
		)
	)
	

结果：

8.查询没学过"张三"老师授课的同学的信息

同理，把上题加工一下
SELECT DISTINCT * FROM student WHERE s_id not in (
	SELECT s_id FROM score WHERE score.c_id in (
		SELECT c_id FROM course LEFT JOIN teacher ON course.t_id=teacher.t_id WHERE teacher.t_name="张三"
		)
	)

结果：

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

方法一：先查选修过01的在查选修过02的在查两个并集
SELECT * 
FROM student 
WHERE student.s_id in(
	SELECT a.s_id FROM
		(SELECT s_id FROM score WHERE c_id = '01') as a
		JOIN
		(SELECT s_id FROM score WHERE c_id = '02') as b
	on a.s_id = b.s_id
)


方法二：给定条件从两个结果集中查询
select student.* 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')

结果：

10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

上面同理：
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')

结果：

考点总结

模糊查询

sql模糊查询提供了四种匹配模式
1.% ：表示任意0个或多个字符。
2._ ： 表示任意单个字符。
3.[ ] ：表示括号内所列字符中的一个。
4.[^ ] ：表示不在括号所列之内的单个字符。

自联结

指的是一个表自己和自己连接，多用于解决考点情况

各种join

a集合：【2，3，4，5，6】
b集合：【5，6，7，8，9】
1.inner join或者join：简单来说就是a集合和b集合公共的部分也就是【5，6】
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

2.left join:就是包括了a集合以及a集合和b集合的交集也就是【2，3，4，5，6】
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

3.right join:就是包括了b集合以及a集合和b集合的交集也就是【5，6，7，8，9】
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。