SQL 必会50道面试题1.0
SQL 必会50道面试题
练习网站
数据库中单引号和双引号的局别
建议数据库内使用单引号，双引号一般语言中使用
临时表?
用临时表替代子查询
冷门知识
求长度
select length ('133')
select length (null)
建表插入数据
学生表： 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张表的关系图，不好理解，你可以列举一些数据案例来辅助理解：
案例数据建立参考如下 表名和字段 –1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数 测试数据
--建表
--学生表
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' , '[孙风](https://www.zhihu.com/search?q=孙风&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})' , '1990-05-20' , '男');
insert into Student values('04' , '[李云](https://www.zhihu.com/search?q=李云&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})' , '1990-08-06' , '男');
insert into Student values('05' , '[周梅](https://www.zhihu.com/search?q=周梅&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})' , '1991-12-01' , '女');
insert into Student values('06' , '[吴兰](https://www.zhihu.com/search?q=吴兰&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})' , '1992-03-01' , '女');
insert into Student values('07' , '[郑竹](https://www.zhihu.com/search?q=郑竹&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--[课程表](https://www.zhihu.com/search?q=课程表&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"article"%2C"sourceId"%3A43289968})测试数据
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);
第一题
-- 查询课程编号为01的课程比02的课程成绩高的所有学生的学号
SELECT
a.s_id AS "学号",
c.s_name AS "姓名",
a.s_score AS "01",
b.s_score AS "02"
FROM
( SELECT s_id, s_score FROM `score` WHERE c_id = 001 ) a
INNER JOIN ( SELECT s_id, s_score FROM `score` WHERE c_id = 002 ) b ON a.s_id = b.s_id
INNER JOIN student c ON a.s_id = c.s_id
WHERE
a.s_score > b.s_score
第二题
group by 后返回的字段除分组条件和条件聚合结果(统计值),其他字段无意义,可能报错?
-- 查询平均成绩大于60分的学生的学号和平均成绩
SELECT
s_id AS "学号",
avg( s_score ) AS score
FROM
`score`
GROUP BY
s_id
HAVING
score > 60
第三题
case when 的用法
-- 查询所有学生的学号,姓名,选课数,总成绩
SELECT
a.s_id AS "学号",
a.s_name AS "姓名",
CASE
WHEN b.count IS NULL THEN
0 ELSE b.count
END AS "课程数",
CASE
WHEN b.sum IS NULL THEN
0 ELSE b.sum
END AS "总成绩"
FROM
`student` a
LEFT JOIN ( SELECT s_id, count( c_id ) AS count, sum( s_score ) AS sum FROM `score` GROUP BY s_id ) b ON b.s_id = a.s_id
第四题
-- 查询姓猴的老师的个数
SELECT
count( DISTINCT ( t_name ) )
FROM
`teacher`
WHERE
t_name LIKE "张%";
第五题
先查学过的,后取反,not in
-- 没学过张三老师课的学生的学号和姓名
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score
WHERE
c_id =(
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 a
INNER JOIN course b ON a.c_id = b.c_id
INNER JOIN teacher c ON c.t_id = b.t_id
WHERE
c.t_name = "张三")
第六题
-- 查询学过张三老师所教的所有课的同学的学号,姓名
SELECT
a.s_id,
a.s_name
FROM
`student` a
INNER JOIN `score` b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
INNER JOIN `teacher` d ON c.t_id = d.t_id
WHERE
d.t_name = '张三'
第七题
求交集一般用inner join
-- 查询学过编号为01的课程且学过编号为02的课程的学生的学号,姓名
select b.s_id,c.s_name from(select s_id from score where c_id=01)a
inner join(select s_id from score where c_id=02)b on a.s_id=b.s_id
inner join student c on b.s_id=c.s_id
第八题
统计函数不分组也能用,统计函数后不要留空格
-- 查询课程编号为02的总成绩
select sum(s_score),avg(s_score)
from score where c_id='02'
第九题
-- 查询所有成绩小于60的分的学生学号和姓名
-- 第一步,查询成绩小于60分的成绩的同学及不及格课程数
select s_id,count(c_id)as cnt from score where s_score <60 group by s_id;
-- 第二步,查询每名同学所学课程的数量
select s_id,count(c_id) from score group by s_id;
select c.s_id,c.s_name from (select s_id,count(c_id)as cnt from score where s_score <60 group by s_id) a inner join
(select s_id,count(c_id)as cnt from score group by s_id) b on a.s_id=b.s_id inner join student c on c.s_id=b.s_id
where a.cnt<=b.cnt;
第十题
注意where和having的区别
-- 查询没有学全所有课的学生的学号,姓名
-- 第一步,查询没有学生学了几门课
SELECT
a.s_id,
a.s_name,
CASE
WHEN b.cnt IS NULL THEN
0 ELSE b.cnt
END AS cnt
FROM
student a
LEFT JOIN ( SELECT s_id, count( c_id ) AS cnt FROM score GROUP BY s_id ) b ON a.s_id = b.s_id
having
cnt <(
SELECT
count( 1 )
FROM
course)
错误答案
-- 查询没有学全所有课的学生的学号,姓名
-- 第一步,查询没有学生学了几门课
select s_id,count(c_id) as cnt from score group by s_id ;
-- 第二步
select b.s_id,b.s_name from (select s_id,count(c_id) as cnt from score group by s_id
having cnt<(select count(1) from course)) a
inner join student b
on a.s_id =b.s_id;
第十一题
-- 查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名
SELECT DISTINCT
a.s_id,
b.s_name
FROM
score a
INNER JOIN student b ON a.s_id = b.s_id
WHERE
a.c_id IN ( SELECT c_id FROM score WHERE s_id = 01 )
AND a.s_id != 01
第十二题
-- 查询和01号同学所学课程完全相同的其他同学的学号
SELECT
s_id
FROM
score
GROUP BY
s_id
HAVING
count( c_id ) =(
SELECT
count( c_id )
FROM
score
WHERE
s_id = 01
)
AND s_id != 01
AND s_id != (
SELECT
s_id
FROM
score
WHERE
c_id NOT IN ( SELECT c_id FROM score WHERE s_id = 01 ));-- 第一步,查询出与01号学生所学课程数相同的学生
SELECT
s_id
FROM
score
GROUP BY
s_id
HAVING
count( c_id ) =(
SELECT
count( c_id )
FROM
score
WHERE
s_id = 01
)
AND s_id != 01;-- 第二步,查询出所学课程不在01号学生所学的课程中的学生(排除)
SELECT
s_id
FROM
score
WHERE
c_id NOT IN ( SELECT c_id FROM score WHERE s_id = 01 );
第十五题
-- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT
a.s_id,
a.s_name,
avg( c.s_score )
FROM
student a
INNER JOIN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING count( c_id )>= 2 ) b ON a.s_id = b.s_id
INNER JOIN score c ON b.s_id = c.s_id
GROUP BY
c.s_id;-- 第一步,查询不及格课程有哪些
SELECT
s_id
FROM
score
WHERE
s_score < 60;
第十六题
-- 检索01号课程分数小于60,按分数降序排列的学生信息
SELECT
b.*,
a.s_score
FROM
score a
INNER JOIN student b ON a.s_id = b.s_id
WHERE
a.c_id = 01
AND a.s_score < 60
ORDER BY
a.s_score DESC
第十七题(重点)
-- 按平均成绩从高到底显示所有学生的所有课程的成绩及平均成绩
SELECT
s_id,
max(case when c_id=01 then s_score else null end) as "01",
max(case when c_id=02 then s_score else null end) as "02",
max(case when c_id=03 then s_score else null end) as "03",
max(case when c_id=04 then s_score else null end) as "04",
avg( s_score ) AS score
FROM
score
GROUP BY
s_id
ORDER BY
score DESC;
第十八题
多表查询时,如果字段不重复时,字段前不用加表前缀
-- 查询各科成绩最高分,最低分和平均分:以如下形式显示:课程id,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select a.c_id,b.c_name,max(s_score),min(s_score),avg(s_score),
sum(case when s_score>=60 then 1 else 0 end)/count(s_score) as'及格率',
sum(case when s_score>=70 and s_score <80 then 1 else 0 end)/count(s_score) as'中等率',
sum(case when s_score>=80 and s_score <90 then 1 else 0 end)/count(s_score) as'优良率',
sum(case when s_score>=90 then 1 else 0 end)/count(s_score) as'优秀率'
from score a inner join course b on a.c_id=b.c_id group by a.c_id
第十九题进阶
窗口函数
第二十题
中文也可以作字段别名的
-- 查询学生的总成绩并进行排名
SELECT
s_id,
sum( s_score ) AS score
FROM
score
GROUP BY
s_id
ORDER BY
score DESC
第二十一题
group by 的位置影响结果吗
-- 查询不同老师所教不同课程平均分从高到低显示
SELECT
b.t_id AS "工号",
c.t_name AS "老师",
b.c_name AS "课程",
avg( s_score ) AS 平均分
FROM
score a
INNER JOIN course b ON a.c_id = b.c_id
INNER JOIN teacher c ON c.t_id = b.t_id
GROUP BY
a.c_id
ORDER BY
平均分 DESC;
第二十二题
超纲
-- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select * from(select a.* ,b.c_id,s_score,row_number () over(partition by c_id order by s_score dese) m from score b inner join student a on a.s_id=b.s_id)c where m in (2,3)
第二十三题
-- 使用分段统计各科成绩,分别统计各分段人数:课程id和课程名称
第二十四题
第二十六题
-- 查询每门课程被选修的学生数
SELECT
a.c_id AS "课程id",
b.c_name AS "名称",
count( s_id ) AS "学生数"
FROM
score a
INNER JOIN course b ON a.c_id = b.c_id
GROUP BY
a.c_id
第二十七题
-- 查询出只有两门课程的全部学生的学号和姓名
SELECT
b.s_id,
b.s_name
FROM
score a
INNER JOIN student b ON a.s_id = b.s_id
GROUP BY
a.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 )
第二十八题重点
不用分组也可以用统计函数
-- 查询男生和女生的人数
SELECT
sum(
IF
( s_sex = '男', 1, 0 )) AS "男生人数",
sum(
IF
( s_sex = '女', 1, 0 )) AS "女生人数"
FROM
student ;
SELECT
sum( CASE WHEN s_sex = '男' THEN 1 ELSE 0 END ) AS "男生人数",
sum( CASE WHEN s_sex = '女' THEN 1 ELSE 0 END ) AS "女生人数"
FROM
student;
SELECT
s_sex,
count( s_id )
FROM
student
GROUP BY
s_sex;
-- 错误答案
SELECT
sum(
IF
( s_sex = '男', 1, 0 )) AS "男生人数",
sum(
IF
( s_sex = '女', 1, 0 )) AS "女生人数"
FROM
student
GROUP BY
s_sex;
SELECT
sum( CASE WHEN s_sex = '男' THEN 1 ELSE 0 END ) AS "男生人数",
sum( CASE WHEN s_sex = '女' THEN 1 ELSE 0 END ) AS "女生人数"
FROM
student
GROUP BY
s_sex;
第二十九题
-- 查询含有风的学生信息
第三十一题(year函数)
-- 查询 1990年出生的学生
SELECT
*
FROM
student
WHERE
YEAR ( s_birth )= 1990;
SELECT YEAR
( '190927' );
支持YYYY-MM-DD,YYYYMMDD,YYMMDD,YYYY/MM/DD
第三十二题
-- 查询平均成绩大于等于85的所有学生的学号,姓名,平均成绩
SELECT
a.s_id,
b.s_name,
avg( a.s_score ) AS score
FROM
score a
INNER JOIN student b ON a.s_id = b.s_id
GROUP BY
s_id
HAVING
score >= 85;
第三十二题
-- 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT
c_id,
avg( s_score ) AS score
FROM
score
GROUP BY
c_id
ORDER BY
score ASC,
c_id DESC;
第三十三题
-- 查询课程名称为数学,且分数低于60的学生姓名和分数
SELECT
s_name,
s_score
FROM
score a
INNER JOIN student b ON a.s_id = b.s_id
WHERE
c_id =(
SELECT
c_id
FROM
course
WHERE
c_name = '数学'
)
AND s_score < 60;
第二种解法
-- 查询课程名称为数学,且分数低于60的学生姓名和分数
SELECT
s_name,
c_name,
s_score
FROM
score a
INNER JOIN course b ON a.c_id = b.c_id
INNER JOIN student c ON a.s_id = c.s_id
WHERE
b.c_name = '数学'
AND a.s_score < 60
第三十四题重点
case when 后没有逗号
-- 查询课程名称为数学,且分数低于60的学生姓名和分数
SELECT
s_name,
max( CASE WHEN c_name = '语文' THEN a.s_score ELSE NULL END ) AS "语文",
max( CASE WHEN c_name = '数学' THEN a.s_score ELSE NULL END ) AS "数学",
max( CASE WHEN c_name = '英语' THEN a.s_score ELSE NULL END ) AS "英语",
max( CASE WHEN c_name = '化学' THEN a.s_score ELSE NULL END ) AS "化学"
FROM
score a
INNER JOIN course b ON a.c_id = b.c_id
INNER JOIN student c ON a.s_id = c.s_id
GROUP BY
a.s_id
用sum和max也可以
第三十六题
-- 查询课程成绩在70分以上课程名称,分数和学生姓名
SELECT
s_name,
c_name,
s_score
FROM
student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON c.c_id = b.c_id
WHERE
s_score > 70;
注意多对多的情况 join操作会造成数据增多
第三十七题
-- 查询不及格的课程并按课程号从大到小排列
SELECT
a.s_id,
c.s_name,
b.c_id,
c_name,
a.s_score
FROM
score a
INNER JOIN course b ON a.c_id = b.c_id
INNER JOIN student c ON a.s_id = c.s_id
WHERE
s_score < 60
ORDER BY
c_id DESC
第三十八题
-- 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
SELECT
a.s_id,
a.s_name,
c.c_id,
c.c_name,
b.s_score
FROM
student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
WHERE
b.c_id = 03
AND b.s_score > 80
第三十九题
-- 求每门课程的学生人数
第四十题重点
-- 查询选修张三老师所教授课程的学生中成绩最高的学生姓名及成绩
SELECT
d.s_id,
d.s_name,
b.c_id,
b.c_name,
c.s_score,
a.t_id,
a.t_name
FROM
teacher a
INNER JOIN course b ON a.t_id = b.t_id
INNER JOIN score c ON c.c_id = b.c_id
INNER JOIN student d ON d.s_id = c.s_id
WHERE
a.t_name = '张三'
ORDER BY
c.s_score DESC
LIMIT 0,
1;
时间转换函数
将字符串按模式转成date时间
str_to_date('2022-01-10 17:23:47','%Y-%m-%d %H:%i:%S')
参考资料https://www.cnblogs.com/xiaoxiao075/p/11949668.html
