mysql的经典试题
mysql sql
目录
一、简单查询
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题
创建数据表 teacher，并插入输入
DROP table if EXISTS teacher;
CREATE TABLE teacher(
id INT not null auto_increment,
tname CHARACTER(32) NOT NULL,
PRIMARY KEY(id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into teacher values (1,'张杰');
insert into teacher values (2,'孟子');
insert into teacher values (3,'李大钊');
insert into teacher values (4,'黄子健');
insert into teacher values (5,'孟川');
insert into teacher values (6,'张梁');
insert into teacher values (7,'连华敏');
insert into teacher values (8,'彭密');
insert into teacher values (9,'王子');
insert into teacher values (10,'王晓飞');
insert into teacher values (11,'汪小菲');
insert into teacher values (12,'梁浩宇');
insert into teacher values (13,'谭浩轩');
insert into teacher values (14,'黄小妮');
insert into teacher values (15,'周晓鸥');
SELECT * FROM teacher;
教师表：
成绩表：
一、简单查询
1. 查询姓“猴”的学生名单
select *from student where sname like "猴%"
2.查询姓“孟”老师的个数
select count(*) from teacher where tname like "孟%"
二、汇总分析
- 查询课程编号为“0002”的总成绩
先选出0002的成绩信息
select sum(score) from score where cid="0002"
- 查询选了课程的学生人数（判断依据：课程id（cid）and 成绩（score）不为空（空：=’’,非空!=’’）），去重用distinct
先选出符合条件的所有记录
SELECT * FROM score where cid != '' and score != '';
# select count(distinct sid) from score where cid is not null and score is not null #参考答案
SELECT count(distinct sname) FROM score where cid != '' and score != ''; #我的答案
3.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
select cid, max(score),min(score)
from score
group by cid
4.查询每门课程被选修的学生数
select cid,count(distinct sid)
from score
group by cid
验证一下
5.查询男生、女生人数（sid是学生id）
select ssex,count(sid)
from student
group by ssex
- 查询某个学科的平均成绩
SELECT AVG(score) as '平均成绩'FROM score where cid ='0001';
查看各科平均成绩：SELECT cid,AVG(score) as '平均成绩' FROM score GROUP BY cid ;
7.查询平均成绩大于60分学生的学号和平均成绩
select sid, avg(score) as average
from score
group by sid
having average>60
SELECT sname ,AVG(score) as '平均成绩' FROM score where cid ='0001' GROUP BY sname HAVING AVG(score) <60 ;
注意：这样写查询不到结果，所以as字段最好不要用中文
SELECT sname ,AVG(score) as '平均成绩' FROM score where cid ='0001' GROUP BY sname HAVING '平均成绩' <60 ; --不能写中文
7.查询至少选修两门课程的学生学号
select sid,count(distinct cid)
from score
group by sid
having count(distinct cid)>=2
SELECT sname,count(score) FROM score where score ='' group by sname HAVING count(score) <3;
8.查询同名同姓学生名单并统计同名人数
select sname, count(sid)
from student
group by sname
having count(sid)>=2
9.查询不及格的课程并按课程号从大到小（desc）排列---（从小到大：asc）
select distinct cid
from score
where score<60
order by cid desc
SELECT * from score where score <60 and score !='' ORDER BY cid desc;
10.查询每门课程的平均成绩，结果按平均成绩升序排序（order by avg asc），平均成绩相同时，按课程号降序排列
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)
SELECT s.s_no,s.sname from student s where s.sname in (SELECT sname FROM score where score <60 and score !=''); ---这两个字段要一样
注：工号在student表，学生成绩小于60的信息在score表。
2.查询没有学全所有课的学生的学号、姓名
select sid,sname
from student
where sid in(
select sid
from score
group by sid
having count(distinct cid)
SELECT s.sname, s.s_no from student s where s.s_no in
(SELECT DISTINCT(s_no) FROM score where score = '') ;
3.查询出只选修了两门课程的全部学生的学号和姓名
select sid,sname
from student
where sid in(
select sid
from score
group by sid
having count(distinct cid)=2)
SELECT sname,s_no from student where s_no in
(SELECT s_no FROM score where score != '' GROUP BY s_no HAVING COUNT(score) = 2);
4.日期函数？？
5.查询各科成绩前两名的记录(重要)
参考教程：https://www.bilibili.com/video/BV1Bp4y1n7Ah?p=37
思路：
SELECT * ,
dense_rank() over(PARTITION by cid ORDER BY score desc) as score_rank
from score ; #PARTITION按课程分区
MYSQL的版本是5.7.23
以上语句在5.7版本执行，不生效
6.【行转列、列转行】问题(重要)
(1)行转列（case when 字段名 then 字段名（值） else 0 end）
下面是学生的成绩表(表名score，列名：学号、课程号、成绩)
使用sql实现将该表行转列为下面的表结构
思路：使用case when，具体思路参考文章sql面试题：行列如何互换？
语法：
case when 条件1 then 字段名1/值1 ---符合条件1的时候就输出字段名1或者是值1
case when 条件2 then 字段名2/值2
.....
else end
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
先把要查的数据整理出来：
SELECT a.id,a.sname,b.score,c.course_name from student a,score b ,course c where a.id =b.s_no and c.id = b.cid;
SELECT a.id,a.sname,b.score,c.course_name ,
(case when course_name = '语文' then score else 0 end ) as 语文,
(case when course_name = '数学' then score else 0 end ) as 数学,
(case when course_name = '英语' then score else 0 end ) as 英语,
(case when course_name = '文综' then score else 0 end ) as 文综,
(case when course_name = '理综' then score else 0 end ) as 理综
from student a,score b ,course c
where a.id =b.s_no and c.id = b.cid;
再对上面的数据进行统计汇总group by----即多表查询，分组统计
先按照学号进行分组
分组之后报错如下：
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lwy课程表.b.score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法一
- 查询mysql 相关mode
select @@global.sql_mode;
- 1
可以看到模式中包含了ONLY_FULL_GROUP_BY，只要没有这个配置即可。
我的Mysql版本是5.7.23，默认是带了ONLY_FULL_GROUP_BY模式。
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- 重设模式值
在[mysqld]后面追加下面这句配置后重启服务解决问题；
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2、重启mysql 服务：service mysqld restart。
最终结果：
SELECT a.id,a.sname,score,course_name ,
sum(case when course_name = '语文' then score else 0 end ) as 语文,
sum(case when course_name = '数学' then score else 0 end ) as 数学,
sum(case when course_name = '英语' then score else 0 end ) as 英语,
sum(case when course_name = '文综' then score else 0 end ) as 文综,
sum(case when course_name = '理综' then score else 0 end ) as 理综,
sum(score),avg(score)
from student a ,score b,course c
where a.id =b.s_no and c.id = b.cid
GROUP BY id,a.sname
;
(2)列转行（union all）
原数据参考文章 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://blog.csdn.net/weixin_35559171/article/details/113594308
