目录
一、简单查询
1. 查询姓“猴”的学生名单
2.查询姓“孟”老师的个数
二、汇总分析
1.查询课程编号为“0002”的总成绩
2.查询选了课程的学生人数
3.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
4.查询每门课程被选修的学生数
5.查询男生、女生人数
6.查询平均成绩大于60分学生的学号和平均成绩
7.查询至少选修两门课程的学生学号
8.查询同名同姓学生名单并统计同名人数
9.查询不及格的课程并按课程号从大到小排列
10.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
三、复杂查询
1.查询所有课程成绩小于60分学生的学号、姓名
2.查询没有学全所有课的学生的学号、姓名
3.查询出只选修了两门课程的全部学生的学号和姓名
4.日期函数
5.查询各科成绩前两名的记录(重要)
6.【行转列、列转行】问题(重要)
(1)行转列
(2)列转行
(3)单列拆分转行
题目来源：知乎猴子 常见的SQL面试题：经典50题
一、简单查询
1. 查询姓“猴”的学生名单
select *
from student
where sname like "猴%"
2.查询姓“孟”老师的个数
select count(*)
from teacher
where tname like "孟%"
二、汇总分析
1.查询课程编号为“0002”的总成绩
select sum(score)
from score
where cid="0002"
2.查询选了课程的学生人数
select count(distinct sid)
from score
where cid is not null and score is not null
3.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
select cid, max(score),min(score)
from score
group by cid
4.查询每门课程被选修的学生数
select cid,count(distinct sid)
from score
group by cid
5.查询男生、女生人数
select ssex,count(sid)
from student
group by ssex
6.查询平均成绩大于60分学生的学号和平均成绩
select sid, avg(score) as average
from score
group by sid
having average>60
7.查询至少选修两门课程的学生学号
select sid,count(distinct cid)
from score
group by sid
having count(distinct cid)>=2
8.查询同名同姓学生名单并统计同名人数
select sname, count(sid)
from student
group by sname
having count(sid)>=2
9.查询不及格的课程并按课程号从大到小排列
select distinct cid
from score
where score<60
order by cid desc
10.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select cid, avg(score) as average
from score
group by cid
order by average asc, cid desc
后面几题太简单了...不写了
三、复杂查询
1.查询所有课程成绩小于60分学生的学号、姓名
select s.sid, s.sname
from student s
where s.sid in(
select distinct sid
from score
where score<60)
2.查询没有学全所有课的学生的学号、姓名
select sid,sname
from student
where sid in(
select sid
from score
group by sid
having count(distinct cid)
3.查询出只选修了两门课程的全部学生的学号和姓名
select sid,sname
from student
where sid in(
select sid
from score
group by sid
having count(distinct cid)=2)
4.日期函数
文章图片
5.查询各科成绩前两名的记录(重要)
详细见mysql分组取TOP N个的问题
6.【行转列、列转行】问题(重要)
(1)行转列
下面是学生的成绩表(表名score，列名：学号、课程号、成绩)
文章图片
使用sql实现将该表行转列为下面的表结构
文章图片
思路：使用case when，具体思路参考文章sql面试题：行列如何互换？
select sid,
max(case when cid='0001' then score else 0 end) as '课程号0001',
max(case when cid='0002' then score else 0 end) as '课程号0002',
max(case when cid='0003' then score else 0 end) as '课程号0003'
from score
group by sid
不要忘记最后要group by
(2)列转行
原数据参考文章 MySQL行转列与列转行
文章图片
要求转化成：
文章图片
select user_name, '语文' as course, CN_SCORE as score from GRADE
union all
select user_name, '数学' as course, MATH_SCORE as score from GRADE
union all
【mysql|mysql sql语句面试经典50题_常见的SQL面试题（经典50题（简单））】select user_name, '英语' as course, EN_SCORE as score from GRADE
文章图片
(3)单列拆分转行
文章图片
思路：(1)先建立一个序列表 tb_sequence
文章图片
--创建自动递增的序列表
create table tb_sequence if not exists(id int auto_increment not null, primary key(id));
--插入数值，这里插入的个数=列拆分后的行数
insert into table tb_sequence values(),(),(),(),(),(),(),();
(2) 计算每一条记录将会拆分成多少行，得出size。计算方法：逗号的个数+1，逗号个数可以用(length(mobile)-length(replace(mobile, ',' , '')))/length(',') 得出。
再将得出size的原表与tb_sequence做cross join，选取id小于size取值的行，其实就是占坑。
select *
from tb_sequence a
cross join
(select b.*,((length(mobile)-length(replace(mobile, ',' , '')))/length(',')+1) as size
from user1 b) b on a.id<=b.size
--备注：原文不知道为什么mobile要重新concat，附上原文的代码：
select *
from tb_sequence a
cross join
(select user_name,concat(mobile,','),((length(mobile)-length(replace(mobile, ',' , '')))/length(',')+1) as size
from user1 b) b on a.id<=b.size
上面这一段代码输出结果：
文章图片
(3)最后就是进行字符串的处理
文章图片
推荐阅读
- 数据库|MySQL的四种事务隔离级别
- Mysql|深入理解Mysql事务隔离级别与锁机制
- 数据库|MySQL事务处理与事务隔离(锁机制)
- 算法|SpringBoot+Redis 实现一个微博热搜！
- mysql读写分离
- MySQL|MySQL读写分离详解（一）——基本原理
- MySQL|MySQL 读写分离的基本概念和实现方式
- MySQL主从复制+读写分离原理
- MysqL|MysqL读写分离原理