知乎sql50题

哔哩哔哩题目讲解

知乎题目

表之间的联系

  • 学生表：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) –学生编号,课程编号,分数

建表语句


-- 测试数据
--建表
--学生表
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' , '孙风' , '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. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
# 先把学生01和02的课程成绩找出来,01和02的成绩拼接成一张表，前提是学生id一样
select a.s_id "学号",c.s_name,a.s_score "01",b.s_score "02"  from
(
select * from score where c_id='01'
) as a
INNER JOIN
(
select * 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. 查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
# 使用 groupby 是以having作为过滤条件的
select s_id,avg(s_score) from score 
GROUP BY s_id HAVING avg(s_score)>60
  1. 查询所有学生的学号、姓名、选课数、总成绩（不重要）

统计一般用到 groupby，求个数 count，求和sum，使用groupby后，能出现在select中的只有统计值

select a.s_id,a.s_name,count(b.c_id),SUM(case when b.s_score is NULL then 0 else B.s_score END)
from student as a
LEFT JOIN score as b 
on a.s_id = b.s_id
GROUP BY s_id,a.s_name
  1. 查询姓“张”的老师的个数（不重要）
    主要考察like的使用，%的使用
select  count(t_id) from teacher where t_name like '张%'
#根据名字查询，除去同名的，去重
select  count(DISTINCT t_name) from teacher where t_name like '张%'
  1. 查询没学过“张三”老师课的学生的学号、姓名（重点）
    先查询出张三老师的id，查询成绩表中有张三老师教的课程编号，根据课程编号查询出选过这门课的学生id（学号）,然后再从学生表中查根据学生id询出没有选过这门课的学生信息
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 as s
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN teacher as t on c.t_id=t.t_id
WHERE t.t_name='张三')
  1. 查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
SELECT st.s_id,st.s_name,s.c_id,c.c_name,c.t_id,t.t_name
FROM student as st
INNER JOIN score as s on s.s_id=st.s_id
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN teacher as t on t.t_id=c.t_id 
WHERE t.t_name='张三'
ORDER BY st.s_id
  1. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
    运用子查询，先用子查询查询出需要的数据，然后作为查询条件继续查询，工作中把子查询换为临时表即可 innerjoin很大程度上是求交集
 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
)


SELECT LENGTH('123') 结果为3
SELECT LENGTH(NULL) 结果为 NULL

8.

查询课程编号为“02”的总成绩（不重点），其实很多统计都是直接在sql中完成的

select c_id,SUM(s_score),AVG(s_score),COUNT(DISTINCT s_id) FROM score GROUP BY c_id HAVING c_id='02'

第二种方法：

select c_id,SUM(s_score),AVG(s_score),COUNT(DISTINCT s_id) FROM score  WHERE c_id='02'

9.

查询所有课程成绩小于60分的学生的学号、姓名
先查询小于60分的课程数 ，在统计一共学了几门课，做交集，有一样的说明都小于60分

select a.s_id,t.s_name
from
(
select s_id,COUNT(c_id) as cnt
from score WHERE s_score<60 GROUP BY s_id
) as a
INNER JOIN
(
select s_id,COUNT(c_id) as 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

10.

查询没有学全所有课的学生的学号、姓名(重点)

考生科目没有三门的说明没有学全,用分组查询即可,根据一共有几门课，小于这几门课分组说明没有学完
左连接左边的数据肯定会全部出现，

select st.s_id,st.s_name
from student as st
LEFT JOIN score as sc on st.s_id=sc.s_id
GROUP BY st.s_id 
HAVING COUNT(DISTINCT sc.c_id<
(select COUNT(DISTINCT c_id) from course))

11.

查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

先查询01学了什么课,然后查询选的课一样的学生id去掉重复的，最后在关联学生表，查询粗名称

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

#改用 innerjoin 查询

select st.s_id,st.s_name from student  as st
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 st.s_id=b.s_id

12.

查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

01号学了01 02 03 ，选出所学的课不在这三门课中的人，判断剩下的人选的课程不在三门中的人，

select * FROM student WHERE s_id in(
select s_id FROM score WHERE s_id!='01' GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT count(DISTINCT c_id) from score WHERE s_id='01')
) and s_id NOT in(
select DISTINCT s_id FROM score WHERE c_id not in 
(select c_id from score WHERE s_id='01'))

15.

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

select a.s_id,a.s_name,AVG(b.s_score) FROM student as a
INNER JOIN score as b on a.s_id=b.s_id
WHERE a.s_id in(
select s_id from score WHERE s_score<60 GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2) GROUP BY s_id,s_name

16.

检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）考察 orderby

select t.*,s.s_score,s.c_id from student as t
INNER JOIN score as s on t.s_id=s.s_id
WHERE s.c_id='01' AND s.s_score<60 ORDER BY s.s_score DESC

17.

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)


SELECT s.s_id,s.s_score,s.c_id,b.avg_s_score FROM score as s 
INNER JOIN(
select s_id,AVG(s_score) as avg_s_score FROM score
GROUP BY s_id ) as b on s.s_id=b.s_id
ORDER BY b.avg_s_score DESC 

#第二种

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

18.

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程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

20.

查询学生的总成绩并进行排名（不重点）
字段别名就相当于是 as xx ，字段别名可以作为查询条件

select s_id "学号",sum(s_score) "总成绩"
from score GROUP BY s_id ORDER BY 总成绩 DESC

21.

查询不同老师所教不同课程平均分从高到低显示(不重点)

 # 以课程为主题，求平均分
select sc.c_id,avg(sc.s_score) as avg_score,c.c_name
FROM score as sc
INNER JOIN course as c 
on sc.c_id=c.c_id
GROUP BY sc.c_id ORDER BY avg_score

#以老师为主题，求平均分
SELECT t.t_id,t.t_name,AVG(sc.s_score) as avg_score
from score as sc
INNER JOIN course as c on sc.c_id=c.c_id
INNER JOIN teacher as t on t.t_id=c.t_id
GROUP BY t.t_id,t.t_name ORDER BY avg_score DESC
  1. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似) case when
select c.c_id,c.c_name,
sum(case when sc.s_score<=100 and sc.s_score >85 then 1 else 0 END) as "[100-85]",
count(case when sc.s_score<=85 and sc.s_score >70 then 1 else NULL END) as "[85-70]",
sum(case when sc.s_score<=70 and sc.s_score >=60 then 1 else 0 END) as "[70-60]",
sum(case when sc.s_score<60  then 1 else 0 END) as "[<60]"
FROM score as sc
INNER JOIN course as c on sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name

26.

查询每门课程被选修的学生数(不重点)

select c.c_id,c.c_name,COUNT(DISTINCT sc.s_id)
from score as sc
INNER JOIN course as c on sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name

27.

查询出只有两门课程的全部学生的学号和姓名(不重点) innerjoin 要比 in 效率高点

SELECT s_id,s_name
FROM student WHERE s_id in(
SELECT s_id
FROM score GROUP BY s_id HAVING count( DISTINCT c_id)=2)

#第二种
SELECT sc.s_id,st.s_name 
FROM score as sc 
INNER JOIN student as st on sc.s_id=st.s_id
GROUP BY sc.s_id HAVING COUNT(DISTINCT sc.c_id)=2

28.

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

SELECT  s_sex,count(s_id)
from student
GROUP BY s_sex

# 第二种
SELECT  s_sex,count(s_sex)
from student
GROUP BY s_sex

# 第三种

select 
SUM(CASE when s_sex='男' then 1 else 0 END) "男生人数",
SUM(CASE when s_sex='女' then 1 else 0 END) "女生人数"
from student

# 第四种
select 
count(CASE when s_sex='男' then 1 else NULL END) "男生人数",
COUNT(CASE when s_sex='女' then 1 else NULL END) "女生人数"
from student
  1. 查询名字中含有"风"字的学生信息（不重点）
    like 模糊查询， %为通配符
select *
FROM student WHERE s_name like '%风%'
  1. 查询1990年出生的学生名单（重点year）year函数
select  *
FROM student WHERE YEAR(s_birth)=1990

# select MONTH(CURRENT_DATE())
#select MONTH('2020-1-16')
#时间的几个格斯
YYYY-MM-DD
YYYYMMDD
YYY/MM/DD
YYMMDD
  1. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
#统计函数需要用having，不是统计函数直接先用where过滤条件之后使用groupby分组

select a.s_id,a.s_name,b.avg_score FROM student as a INNER JOIN(
select s_id,AVG(s_score) as avg_score
FROM score GROUP BY s_id HAVING avg_score>=85)as b on a.s_id=b.s_id
  1. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
select s.c_id,c.c_name,AVG(s_score) as avg_score from score as s
INNER JOIN course as c on s.c_id=c.c_id
GROUP BY s.c_id
ORDER BY avg_score asc ,s.c_id DESC
  1. 查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
select sc.s_id,sc.s_score,st.s_name
from score as sc
INNER JOIN course as c on sc.c_id=c.c_id
INNER JOIN student as st on st.s_id=sc.s_id
where c.c_name='数学' and sc.s_score<60
  1. 查询所有学生的课程及分数情况（重点）
    用case when 之后，往往都会和统计函数在一起使用
select st.s_id,st.s_name,
max(case when c.c_name='语文' then s.s_score else NULL END) as '语文',
max(case when c.c_name='数学' then s.s_score else NULL END) as '数学',
max(case when c.c_name='英语' then s.s_score else NULL END) as '英语'
from score as s
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN student as st on st.s_id=s.s_id
GROUP BY st.s_id,st.s_name
  1. 查询课程成绩在70分以上的课程名称，分数和学生姓名
#一对一的操作，innerjoin不会产生笛卡尔积的结果，如果是多对多，可能会产生笛卡尔积的结果
select s.s_id,st.s_name,c.c_name,s.s_score
from score as s
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN student as st on st.s_id=s.s_id
where s.s_score>70
  1. 查询不及格的课程并按课程号从大到小排列(不重点)
select s.s_id,st.s_name,c.c_id,c.c_name,s.s_score
from score as s
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN student as st on st.s_id=s.s_id
where s.s_score<60
ORDER BY c.c_id DESC
  1. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
SELECT st.s_id,st.s_name,sc.c_id,c.c_name,sc.s_score
FROM score as sc
INNER JOIN student as st on sc.s_id=st.s_id
INNER JOIN course as c on c.c_id=sc.c_id
WHERE sc.c_id='03' and sc.s_score>80
  1. 求每门课程的学生人数（不重要）
select c.c_id,c.c_name,COUNT(DISTINCT s_id)
FROM score  as sc
INNER join course as c on c.c_id=sc.c_id
GROUP BY c_id,c.c_name
  1. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）
select sc.s_id,st.s_name,sc.s_score,c.c_id,c.c_name,t.t_name
FROM score as sc
INNER JOIN course as c on sc.c_id=c.c_id
INNER JOIN teacher as t on c.t_id=t.t_id
INNER JOIN student as st on st.s_id=sc.s_id
WHERE t.t_name='张三'
ORDER BY sc.s_score DESC LIMIT 0,1
  1. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
select s_id from
(SELECT b.s_id,b.s_score from score as b
INNER JOIN(SELECT s_id FROM score GROUP BY s_id having count(DISTINCT c_id)>1) as c on b.s_id=c.s_id
GROUP BY b.s_id,b.s_score
) as a
GROUP BY s_id HAVING count(s_id)=1

42.

查询每门功成绩最好的前两名（同22和25题）

43.

统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）

select c_id,COUNT(DISTINCT s_id) as cnt
FROM score
GROUP BY c_id HAVING cnt>5
ORDER BY cnt DESC,c_id ASC

44.

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

 SELECT s_id,COUNT(DISTINCT c_id) as cnt
 FROM score
 GROUP BY s_id 
 HAVING cnt>=2

45.

查询选修了全部课程的学生信息（重点划红线地方）

select * from student WHERE s_id in 
(
select s_id
FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(select count(c_id) from course))

46.

查询各学生的年龄（精确到月份）DATEDIFF()函数的运用，floor向下取整

SELECT s_id,s_birth,floor( DATEDIFF('2020-1-16',s_birth)/365) from student

47.

查询没学过“张三”老师讲授的任一门课程的学生姓名（还可以，自己写的，答案中没有）

SELECT * FROM student WHERE s_id not in(
SELECT sc.s_id
FROM score as sc
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. 查询下周过生日的同学
#有一定的漏洞，问题
select * FROM student where week(s_birth,1)=WEEK(date(now()),1)+1
  1. 查询本月过生日的人
#主要用到了month函数
select * from student
where MONTH(s_birth)=MONTH(now())
  1. 查询下个月过生日的同学
#select now(); 当前时间包括年月日
#select MONTH(now()) 当前日期月份;
#select date(now()); 当前日期

select * from student where
case when MONTH(s_birth)=12  then MONTH(s_birth)+1 else  MONTH(s_birth)=MONTH(now())+1 END
最后修改：2020 年 08 月 27 日
© 允许规范转载
哇卡哇卡