SQL经典面试50题&思路


目前在学习SQL,简单过了一遍《MySQL必知必会》这本书,刷了一下经典的50题,现在做一些自己做题中的不完全的记录,本文参考博文地址— 图解SQL

前期准备

  1. 创建表

创建数据库db,创建4张表:
– 学生表包括学号、姓名、生日、性别;
– 成绩表包括学号、课程标号、成绩;
– 课程表包括课程标号、课程名称、教师号;
– 教师表包括教师号、教师、姓名;
SQL代码:

-- 学生表
CREATE TABLE student(
	s_id VARCHAR(20) PRIMARY KEY,
	s_name VARCHAR(20) NOT NULL,
	s_birth VARCHAR(20) NOT NULL,
	s_sex VARCHAR(20) NOT NULL
);
-- 课程表
CREATE TABLE course(
	c_id VARCHAR(20) PRIMARY KEY,
	c_name VARBINARY(20) NOT NULL,
	t_id VARBINARY(20) NOT NULL
);
-- 教师表
CREATE TABLE teacher(
	t_id VARCHAR(20) PRIMARY KEY,
	t_name VARBINARY(20) NOT NULL
);
-- 成绩表
CREATE TABLE score(
	s_id VARCHAR(20),
	c_id VARBINARY(20) ,
	s_score INT(3),
	PRIMARY KEY(s_id,c_id)
);
  1. 插入数据
-- 插入学生数据
INSERT INTO student VALUES('01','李四','1996-12-20','男');
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);

刷题思路

在SQL的查询中,建立清晰的思路是最重要的,可以按照以下顺序进行思考:

SELECT 查询结果
FROM 从哪张表中查找数据
WHERE 查询条件
GROUP BY 分组
HAVING 对分组结果指定条件
ORDER BY 对查询结果排序
LIMIT 从查询结果中	

部分题目

-- 1.简单查询(名称查询占位符)

-- 2.汇总分析
-- 查询课程编号为02的总成绩
SELECT SUM(s_score) FROM score WHERE c_id='02'
-- 查询选了课程的学生认数
SELECT COUNT(DISTINCT s_id) FROM score

-- 3.分组
-- 查询各科成绩最高和最低的分
SELECT c_id,MAX(s_score),MIN(s_score)
FROM score
GROUP BY c_id
-- 查询每门课被选修的学生数
SELECT c_id,COUNT(s_id)
FROM score 
GROUP BY c_id
-- 查询男生女生人数
SELECT s_sex,COUNT(*)
FROM student
GROUP BY s_sex

-- 4.分组结果的条件
-- 查询平均成绩大于60分的学生学号和平均成绩
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score) > 60
-- 查询至少选秀两门课程的学生学号
SELECT s_id,COUNT(c_id) AS 选修课程数目
FROM score
GROUP BY s_id
HAVING COUNT(c_id)>=2
-- 查询同名同姓学生名单并统计同名人数
SELECT s_name,COUNT(*)
FROM student
GROUP BY s_name
HAVING COUNT(*)>=2
-- 查询不及格的课程并按课程号从大到小排列
SELECT DISTINCT c_id
FROM score
WHERE s_score
ORDER BY  c_id DESC
-- 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT c_id,AVG(s_score) AS 平均成绩
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) ASC,c_id DESC
-- 检索课程编号为“01”且分数小于60的学生学号,结果按按分数降序排列
SELECT s_id,s_score
FROM score
WHERE c_id='01' AND s_score<60
ORDER BY s_score DESC
-- 统计每门课程的学生选修人数(超过2人的课程才统计)
SELECT c_id,COUNT(s_id) AS 选修人数
FROM score
GROUP BY c_id
HAVING COUNT(s_id)>2
-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id,AVG(s_score) AS 平均成绩
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(c_id)>2


-- 5.查询结构排序,分组特定条件
-- 查询学生总成绩并且进行排名
SELECT s_id,SUM(s_score) AS 总成绩
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC
-- 查询平均成绩大于60的学生的学号以及平均成绩
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60

-- 6.复杂查询
-- 查询所有课程成绩小于60分学生的学号、姓名
SELECT s_id,s_name
FROM student
WHERE s_id IN(
	SELECT s_id FROM score WHERE s_score<60
	)
-- 查询没有学全所有课的学生的学号、姓名
SELECT s_id,s_name
FROM student
WHERE s_id IN(
	SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id)<(SELECT COUNT(c_id) FROM course)
	)
-- 查询出只选修了两门课程的全部学生的学号和姓名
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
	)
-- 查找1990年出生的学生名单
SELECT * FROM student WHERE YEAR(s_birth)=1990

-- 7.多表查询,联结
-- 查询所有学生学号、姓名、选课数、总成绩
SELECT a.s_id,a.s_name,COUNT(b.c_id) AS 选课数,SUM(b.s_score) AS 总成绩
FROM student a 
LEFT JOIN score b
ON a.s_id=b.s_id
GROUP BY a.s_id
-- 查询平均成绩大于85的学生的学号、姓名、平均成绩
SELECT a.s_id,a.s_name,AVG(b.s_score)
FROM student a
JOIN score b 
ON a.s_id=b.s_id
GROUP BY a.s_id 
HAVING AVG(b.s_score)>85
-- 查询学生的选课情况:学号、姓名、课程号、课程名称
SELECT a.s_id,a.s_name,c.c_id,c.c_name
FROM student a
JOIN score b ON a.s_id=b.s_id
JOIN course c ON b.c_id=c.c_id
-- 查询出每门课程的及格人数和不及格人数
SELECT c_id,SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END) AS 及格人数,SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END) AS 不及格人数
FROM score 
GROUP BY c_id
-- 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT a.s_id,a.s_name
FROM student a
JOIN score b
ON a.s_id=b.s_id
WHERE b.c_id='03' AND b.s_score>80

-- 8.多表连接
-- 检索"0001"课程分数小于60,按分数降序排列的学生信息
SELECT a.*
FROM student a
JOIN score b
ON a.s_id=b.s_id
WHERE b.c_id='01' AND b.s_score <60
ORDER BY b.s_score DESC
-- 查询不同老师所教不同课程平均分从高到低显示
SELECT a.t_id,a.t_name,AVG(c.s_score) AS 平均成绩
FROM teacher a
JOIN course b 
ON a.t_id=b.t_id
JOIN score c
ON b.c_id=c.c_id
GROUP BY a.t_id
ORDER BY AVG(c.s_score) DESC
-- 查询课程名称为"数学",且分数低于60的学生姓名和分数
SELECT a.s_name,b.s_score
FROM student a
JOIN score b
ON a.s_id=b.s_id
JOIN course c
ON b.c_id=c.c_id
WHERE c.c_name='数学' AND b.s_score<60
-- ^查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT a.s_id,a.s_name,AVG(b.s_score)
FROM student a
JOIN score b
ON a.s_id=b.s_id
WHERE b.s_score<60
GROUP BY a.s_id
HAVING COUNT(a.s_id)>=2

-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 分析:复制两个成绩表,用学号连接,在两个表中查找不同课程号,成绩相同的学号
SELECT DISTINCT a.s_id,a.s_score,a.c_id
FROM score a
JOIN score b
ON a.s_id=b.s_id
WHERE a.s_score=b.s_score AND a.c_id!=b.c_id

-- 查询01课程比02课程成绩高的学生的信息以及课程分数
/*方法一分析:
	通过学号找出选择了01课程的学生的成绩作为b表;
	同理通过学号找出选择了02课程的学生的成绩作为c表;
	将b表连接到学生信息表,通过左外连接将c表连接起来;
	查询条件是01成绩>02成绩*/
	
SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score 
FROM student a 
JOIN score b ON a.`s_id`=b.s_id AND b.c_id='01'
LEFT JOIN score c ON a.`s_id`=c.s_id AND c.c_id='02'
WHERE b.s_score>c.s_score	

/*方法二分析:
	在成绩表中找出选了01课程的学号,结果为a表;
	在成绩表中找出选了02课程的学号,结果为b表;
	用学号连接a,b表;
	在a,b表中找出相同的学号,在学生表中找到对应的学生信息*/
SELECT a.s_id AS s_id,score1,score2 FROM
(SELECT s_id, s_score AS score1 FROM score WHERE c_id='01') a
INNER JOIN
(SELECT s_id, s_score AS score2 FROM score WHERE c_id='02') b
ON a.s_id=b.s_id
WHERE score1>score2;

-- 查询学过"张三"老师授课的同学的信息 
-- 思路:t_id---->c_id---->s_id----->student.*
SELECT a.s_id AS 学号,a.s_name AS 姓名
FROM student a
JOIN score b ON a.`s_id`=b.s_id
WHERE b.c_id IN(
	SELECT c_id FROM course WHERE t_id IN(SELECT t_id FROM teacher WHERE t_name = '张三')
	)
方法2SELECT a.s_id AS 学号,a.s_name AS 姓名,t.t_name AS 教师名称
FROM student a
JOIN score b ON a.s_id=b.s_id
JOIN course c ON b.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id
WHERE t.t_name='张三'

-- 查询没学过"张三"老师授课的同学的信息 
SELECT a.s_id,a.s_name
FROM student a
WHERE a.s_id NOT IN(
	SELECT a.s_id 
	FROM student a 
	JOIN score b ON a.s_id=b.`s_id`
	JOIN course c ON b.c_id=c.`c_id`
	JOIN teacher t ON c.`t_id`=t.`t_id`
	WHERE t.`t_name`='张三'
	)
方法2SELECT * FROM student a WHERE a.`s_id` NOT IN(
	SELECT b.s_id 
	FROM student b 
	JOIN score c ON b.s_id=c.s_id 
	WHERE c.c_id IN(
		SELECT cs.c_id FROM course cs JOIN teacher t ON cs.t_id=t.t_id WHERE t_name='张三'
		)
	)
-- 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(与上题类似,用成绩排名,用 limit 1得出最高一个)
SELECT a.s_id,a.s_name,b.s_score
FROM student a
JOIN score b ON a.s_id=b.s_id
JOIN course c ON b.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id
WHERE t.t_name='张三'
ORDER BY b.s_score DESC LIMIT 1
-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 查询范围:student,score表
 SELECT a.*
 FROM student a,score b,score c 
 WHERE a.`s_id`=b.s_id AND a.`s_id`=c.s_id AND b.c_id='01' AND c.c_id='02'
 
-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.* 
FROM student a 
WHERE a.`s_id` IN(
	SELECT s_id FROM score WHERE c_id='01') AND a.`s_id` NOT IN(
		SELECT s_id FROM score WHERE c_id='02'
		)

-- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
SELECT * 
FROM student 
WHERE s_id IN (
	SELECT DISTINCT(b.`s_id`) FROM score b WHERE b.c_id IN(
		SELECT b.c_id FROM score b WHERE b.s_id=01)
	)
AND s_id!='01'

-- 查询和"01"号的同学学习的课程完全相同的其他同学的信息 
SELECT * 
FROM student
WHERE s_id IN(
	SELECT s_id 
	FROM score 
	GROUP BY s_id 
	HAVING COUNT(s_id)=(SELECT COUNT(c_id) FROM score WHERE s_id='01')
	)
AND s_id NOT IN(
	SELECT s_id 
	FROM score 
	WHERE c_id IN(
		SELECT DISTINCT c_id 
		FROM score 
		WHERE c_id NOT IN(SELECT c_id FROM score WHERE s_id='01')
		)
	GROUP BY s_id
	)
AND s_id!='01'
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.s_id,AVG(a.s_score ) AS 平均成绩,
MAX(CASE WHEN b.c_name  = '数学' THEN a.s_score ELSE NULL END ) AS '数学',
MAX(CASE WHEN b.c_name  = '语文' THEN a.s_score ELSE NULL END ) AS '语文',
MAX(CASE WHEN b.c_name = '英语' THEN a.s_score ELSE NULL END ) AS '英语'
FROM score a
INNER JOIN course b ON a.c_id =b.c_id
GROUP BY a.s_id

简单的对sql面试50题中的部分题目做了总结,掌握以上题目,再去leetcode刷题,简单程度的无压力,再做几十道中等难度的题目即可,刷题是检验是否掌握了一门知识的最简洁途径。

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页