目录
一、简单查询
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)<(select count(distinct cid) from course))
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 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)最后就是进行字符串的处理


版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/115697.html