百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

10道有深度的mysql查询语句面试题

wptr33 2024-11-09 15:00 54 浏览

介绍

很多查询都可以用经典的学生-课程-成绩案例来表示,下面是一些我在笔试或面试中遇到的经典题型。这些题目没有涉及到索引,函数,存储过程等高级功能,都是一些基本的查询语句,但难度却不小。

表结构

  • 学生表student(id,name)
  • 列表项目
  • 课程表course(id,name)
  • 学生课程表student_course(sid,cid,score)
  • sql
create table student(
id int unsigned primary key auto_increment,
name char(10) not null
);
insert into student(name) values('张三'),('李四');

create table course(
id int unsigned primary key auto_increment,
name char(20) not null
);
insert into course(name) values('语文'),('数学');

create table student_course(
sid int unsigned,
cid int unsigned,
score int unsigned not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id),
primary key(sid, cid)
);

insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

问题

  • 查询student表中重名的学生,结果包含id和name,按name,id升序
select id,name
from student
where name in (
select name from student group by name having(count(*) > 1)
) order by name;

我们经常需要查询某一列重复的行,一般通过group by(有重复的列)然后取count>1的值。 关系型数据库有他的局限性, 有些看似简单的查询写出来的sql很复杂,而且效率也会很低。

  • 在student_course表中查询平均分不及格的学生,列出学生id和平均分
select sid,avg(score) as avg_score
from student_course
group by sid having(avg_score<60);

group by和having是最常考的。 where子句中不能用聚集函数作为条件表达式,但是having短语可以,where和having的区别在于对用对象不同,where作用于记录,having作用于组。

  • 在student_course表中查询每门课成绩都不低于80的学生id
select distinct sid
from student_course
where sid not in (select sid from student_course where score < 80);

用到反向思想,其实就是数理逻辑中的?x:P和??x:?P是等价的。

  • 查询每个学生的总成绩,结果列出学生姓名和总成绩 如果使用下面的sql会过滤掉没有成绩的人
select name,sum(score) total
from student,student_course
where student.id=student_course.sid
group by sid;

更保险的做法应该是使用 左外连接

select name,sum(score)
from student left join student_course
on student.id=student_course.sid
group by sid;
  • 总成绩最高的学生,结果列出学生id和总成绩 下面的sql效率很低,因为要重复计算所有的总成绩。
select sid,sum(score) as sum_score
from student_course 
group by sid 
having sum_score>=all(
 select sum(score) from student_course group by sid
 );

因为order by中可以使用聚集函数,最简单的方法是:

select sid,sum(score) as sum_score
from student_course 
group by sid
order by sum_score desc 
limit 1;

同理可以查总成绩的前三名。

  • 在student_course表查询课程1成绩第2高的学生,如果第2高的不止一个则列出所有的学生

这是个查询 第N大数 的问题。 我们先查出第2高的成绩:

select min(score) from student_course 
where cid = 1 
group by score
order by score desc 
limit 2;

使用这种方式是错的,因为作用的先后顺序是group by->min->order by->limit,mysql提供了limit offset,size这种方式来取第N大的值,因此正确的做法是:

select score from student_course 
where cid = 1 
group by score 
order by score desc limit 1,1;

然后再取出该成绩对应的学生:

select * from student_course
where cid=1 and score = (
 select score from student_course 
 where cid = 1 
 group by score 
 order by score desc limit 1,1
);

类似的,可以查询 某个值第N高 的记录。

  • 在student_course表查询各科成绩最高的学生,结果列出学生id、课程id和对应的成绩 你可能会这样写:
select sid,cid,max(score) from student_course group by cid;

然而上面是不对的,因为 使用了group by的查询字段只能是group by中的字段或者聚集函数或者是每个分组内均相同的字段。 虽然不会报错,但是sid是无效的,如果去掉sid的话只能查出没门课程的最高分,不包含学生id。 本题的正确解法是使用相关嵌套查询:

select * from student_course as x 
where score>=(
 select max(score) from student_course as y 
 where cid=x.cid
 );

相关嵌套查询也就是在进行内层查询的时候需要用到外层查询,有一些注意事项:

  1. 子查询一定要有括号
  2. as可以省略
  3. 使用相关查询;>=max等价于>=all,但是聚合函数比使用any或all效率高
  • 在student_course表中查询每门课的前2名,结果按课程id升序,同一课程按成绩降序 这个问题也就是取每组的前N条纪录
 select * from student_course x 
 where 2>(
 select count(*) from student_course y 
 where y.cid=x.cid and y.score>x.score
 ) 
order by cid,score desc;

这也是一个相关嵌套查询,对于每一个分数,如果同一门课程下只有0个、1个分数比这个分数还高,那么这个分数肯定是前2名之一

  • 一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,两两进行比赛,用一条sql语句显示所有可能的比赛组合
  • select a.name, b.name
  • from team a, team b
  • where a.name < b.name

其实就是一个表和自己连接查询。

  • 题目:数据库中有一张如下所示的表,表名为sales。

年 季度 销售

1991 1 11

1991 2 12

1991 3 13

1991 4 14

1992 1 21

1992 2 22

1992 3 23

1992 4 24

要求:写一个SQL语句查询出如下所示的结果。

年 一季度 二季度 三季度 四季度

1991 11 12 13 14

1992 21 22 23 24

select 年, 
sum(case when 季度=1 then 销售量 else 0 end) as 一季度, 
sum(case when 季度=2 then 销售量 else 0 end) as 二季度, 
sum(case when 季度=3 then 销售量 else 0 end) as 三季度, 
sum(case when 季度=4 then 销售量 else 0 end) as 四季度 
from sales group by 年;

同理,如果要查询每个人的每门课的成绩可以使用如下sql

create view temp 
as 
select student.name as sname,course.name as cname,score
from student_course join (student,course)
on(student_course.sid=student.id and student_course.cid=course.id);
select sname,
sum(case when cname='语文' then score else 0 end) as 语文,
sum(case when cname='数学' then score else 0 end) as 数学
from temp
group by sname;

当然如果新增了一门课,第二条sql就需要跟着变。

推荐阅读:

SQL JOIN,你想知道的应该都有

原文:https://segmentfault.com/a/1190000015570878

相关推荐

MySQL进阶五之自动读写分离mysql-proxy

自动读写分离目前,大量现网用户的业务场景中存在读多写少、业务负载无法预测等情况,在有大量读请求的应用场景下,单个实例可能无法承受读取压力,甚至会对业务产生影响。为了实现读取能力的弹性扩展,分担数据库压...

Postgres vs MySQL_vs2022连接mysql数据库

...

3分钟短文 | Laravel SQL筛选两个日期之间的记录,怎么写?

引言今天说一个细分的需求,在模型中,或者使用laravel提供的EloquentORM功能,构造查询语句时,返回位于两个指定的日期之间的条目。应该怎么写?本文通过几个例子,为大家梳理一下。学习时...

一文由浅入深带你完全掌握MySQL的锁机制原理与应用

本文将跟大家聊聊InnoDB的锁。本文比较长,包括一条SQL是如何加锁的,一些加锁规则、如何分析和解决死锁问题等内容,建议耐心读完,肯定对大家有帮助的。为什么需要加锁呢?...

验证Mysql中联合索引的最左匹配原则

后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识。在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到...

MySQL索引解析(联合索引/最左前缀/覆盖索引/索引下推)

目录1.索引基础...

你会看 MySQL 的执行计划(EXPLAIN)吗?

SQL执行太慢怎么办?我们通常会使用EXPLAIN命令来查看SQL的执行计划,然后根据执行计划找出问题所在并进行优化。用法简介...

MySQL 从入门到精通(四)之索引结构

索引概述索引(index),是帮助MySQL高效获取数据的数据结构(有序),在数据之外,数据库系统还维护者满足特定查询算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构...

mysql总结——面试中最常问到的知识点

mysql作为开源数据库中的榜一大哥,一直是面试官们考察的重中之重。今天,我们来总结一下mysql的知识点,供大家复习参照,看完这些知识点,再加上一些边角细节,基本上能够应付大多mysql相关面试了(...

mysql总结——面试中最常问到的知识点(2)

首先我们回顾一下上篇内容,主要复习了索引,事务,锁,以及SQL优化的工具。本篇文章接着写后面的内容。性能优化索引优化,SQL中索引的相关优化主要有以下几个方面:最好是全匹配。如果是联合索引的话,遵循最...

MySQL基础全知全解!超详细无废话!轻松上手~

本期内容提醒:全篇2300+字,篇幅较长,可搭配饭菜一同“食”用,全篇无废话(除了这句),干货满满,可收藏供后期反复观看。注:MySQL中语法不区分大小写,本篇中...

深入剖析 MySQL 中的锁机制原理_mysql 锁详解

在互联网软件开发领域,MySQL作为一款广泛应用的关系型数据库管理系统,其锁机制在保障数据一致性和实现并发控制方面扮演着举足轻重的角色。对于互联网软件开发人员而言,深入理解MySQL的锁机制原理...

Java 与 MySQL 性能优化:MySQL分区表设计与性能优化全解析

引言在数据库管理领域,随着数据量的不断增长,如何高效地管理和操作数据成为了一个关键问题。MySQL分区表作为一种有效的数据管理技术,能够将大型表划分为多个更小、更易管理的分区,从而提升数据库的性能和可...

MySQL基础篇:DQL数据查询操作_mysql 查

一、基础查询DQL基础查询语法SELECT字段列表FROM表名列表WHERE条件列表GROUPBY分组字段列表HAVING分组后条件列表ORDERBY排序字段列表LIMIT...

MySql:索引的基本使用_mysql索引的使用和原理

一、索引基础概念1.什么是索引?索引是数据库表的特殊数据结构(通常是B+树),用于...