MySQLMySQL经典50题000.题⽬
已知有如下4张表：
学⽣表：student(学号，学⽣姓名，出⽣年⽉，性别)
成绩表：score(学号，课程号，成绩)
课程表：course(课程号，课程名称，教师号)
教师表：teacher(教师号，教师姓名)
根据以上信息按照下⾯要求写出对应的SQL语句。
001.创建表
分析表与表的关联关系：
创建学⽣表：
创建成绩表：
创建课程表：
创建教师表：
002.插⼊数据
insert into student(学号，姓名，出⽣⽇期，性别)
values('0001' , '猴⼦' , '1989-01-01' , '男');
insert into student(学号，姓名，出⽣⽇期，性别)
values('0002' , '猴⼦' , '1990-12-21' , '⼥');
insert into student(学号，姓名，出⽣⽇期，性别)
values('0003' , '马云' , '1991-12-21' , '男');
insert into student(学号，姓名，出⽣⽇期，性别)
values('0004' , '王思聪' , '1990-05-20' , '男');
同理插⼊其他表的数据：
成绩表：
insert into score(学号，课程号，成绩)
values('0001' , '0001' , 80);
insert into score(学号，课程号，成绩)
values('0001' , '0002' , 90);
insert into score(学号，课程号，成绩)
values('0001' , '0003' , 99);
insert into score(学号，课程号，成绩)
values('0002' , '0002' , 60);
insert into score(学号，课程号，成绩)
values('0002' , '0003' , 80);
insert into score(学号，课程号，成绩)
values('0003' , '0001' , 80);
insert into score(学号，课程号，成绩)
values('0003' , '0002' , 80);
insert into score(学号，课程号，成绩)
values('0003' , '0003' , 80);
课程表：
swiper横向滑动insert into course(课程号，课程名称，教师号)
values('0001' , '语⽂' , '0002');
insert into course(课程号，课程名称，教师号)
values('0002' , '数学' , '0001');
insert into course(课程号，课程名称，教师号)
values('0003' , '英语' , '0003');
教师表：
-- 教师表：添加数据
insert into teacher(教师号，教师姓名)
values('0001' , '孟扎扎');
insert into teacher(教师号，教师姓名)
values('0002' , '马化腾');
-- 这⾥的教师姓名是空值（null）
insert into teacher(教师号，教师姓名)
values('0003' , null);
-- 这⾥的教师姓名是空字符串（''）
insert into teacher(教师号，教师姓名)
values('0004' , '');
003.50道⾯试题
简单查询
1.查询姓“猴“的学⽣名单
SELECT * FROM student WHERE 姓名 LIKE '猴%';
2.查询姓名总最后⼀个字是‘猴’的学⽣ SELECT * FROM student WHERE 姓名 LIKE '%猴';
3.查询姓名中带‘猴’的学⽣名单
SELECT * FROM student WHERE 姓名 LIKE '%猴%';
4.查询姓“孟”⽼师的个数
SELECT * FROM teacher WHERE 教师姓名 LIKE '孟%'；汇总分析
5.查询课程编号为“0002”的总成绩
*
分析思路
select 查询结果 [总成绩：汇总函数sum]
from 从哪张表中查数据[成绩表score]
where 查询条件 [课程号是0002]
*/
SELECT SUM(成绩) FROM score WHERE 课程号 = '0002';
6.查询选了课程的学⽣⼈数
/*
这个题⽬翻译成⼤⽩话就是：查询有多少⼈选了课程
select 学号，成绩表⾥学号有重复值需要去掉
from 从课程表查score;
*/
SELECT COUNT(DISTINCT 学号) as 学⽣⼈数 FROM score;
分组
7.查询各科成绩最⾼和最低的分
/*
分析思路
select 查询结果 [课程ID：是课程号的别名，最⾼分：max(成绩) ，最低分：min(成绩)]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组];
*/
SELECT 课程号,MAX(成绩) as 最⾼分,MIN(成绩) as 最低分 FROM score GROUP BY 课程号;
8.查询每门课程被选修的学⽣数
/*
分析思路
select 查询结果 [课程号，选修该课程的学⽣数：汇总函数count]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [每门课程：按课程号分组];
*/
SELECT 课程号,COUNT(DISTINCT 学号) as 学⽣数 FROM score GROUP BY 课程号;
9.查询男⽣，⼥⽣⼈数
/
*
分析思路
select 查询结果 [性别，对应性别的⼈数：汇总函数count]
from 从哪张表中查数据 [性别在学⽣表中，所以查的是学⽣表student]
where 查询条件 [没有]
group by 分组 [男⽣、⼥⽣⼈数：按性别分组]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[没有];
*/
SELECT 性别,COUNT(性别) as 学⽣数 FROM student GROUP BY 性别;
SELECT 性别,COUNT(*) as 学⽣数 FROM student GROUP BY 性别;
分组结果的条件
10.查询平均成绩⼤于60分学⽣的学号和平均成绩
/*
题⽬翻译成⼤⽩话：
平均成绩：展开来说就是计算每个学⽣的平均成绩
这⾥涉及到“每个”就是要分组了
平均成绩⼤于60分，就是对分组结果指定条件
分析思路
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查数据 [成绩在成绩表中，所以查的是成绩表score]
where 查询条件 [没有]
group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
having 对分组结果指定条件 [平均成绩⼤于60分]
*/
SELECT 学号,AVG(成绩) as 平均成绩 FROM score GROUP BY 学号 HAVING AVG(成绩)>60;
11.查询⾄少选修两门课程的学⽣学号
/*
翻译成⼤⽩话：
第1步，需要先计算出每个学⽣选修的课程数据，需要按学号分组
第2步，⾄少选修两门课程：也就是每个学⽣选修课程数⽬>=2，对分组结果指定条件
分析思路
select 查询结果 [学号，每个学⽣选修课程数⽬：汇总函数count]
from 从哪张表中查数据 [课程的学⽣学号：课程表score]
where 查询条件 [⾄少选修两门课程：需要先计算出每个学⽣选修了多少门课，需要⽤分组，所以这⾥没有where⼦句] group by 分组 [每个学⽣选修课程数⽬：按课程号分组，然后⽤汇总函数count计算出选修了多少门课]
having 对分组结果指定条件 [⾄少选修两门课程：每个学⽣选修课程数⽬>=2]sql中select语句的使用方法
*/
SELECT 学号,COUNT(课程号) as 选修课程数 FROM score GROUP BY 学号 HAVING COUNT(课程号)>=2;
12.查询同名同姓学⽣名单并统计同名⼈数
/*
翻译成⼤⽩话，问题解析：
1）查出姓名相同的学⽣有谁，每个姓名相同学⽣的⼈数
查询结果：姓名,⼈数
条件：怎么算姓名相同？按姓名分组后⼈数⼤于等于2，因为同名的⼈数⼤于等于2
分析思路
select 查询结果 [姓名,⼈数：汇总函数count(*)]
from 从哪张表中查数据 [学⽣表student]
where 查询条件 [没有]
group by 分组 [姓名相同：按姓名分组]
having 对分组结果指定条件 [姓名相同：count(*)>=2]
order by 对查询结果排序[没有];
*/
SELECT 姓名,COUNT(姓名) as 同名⼈数 FROM student GROUP BY 姓名 HAVING COUNT(姓名)>=2;
13.查询不及格的课程并按课程号从⼤到⼩排列
/*
分析思路
select 查询结果 [课程号]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [不及格：成绩 <60]
group by 分组 [没有]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[课程号从⼤到⼩排列：降序desc];
*/
SELECT 课程号 FROM score WHERE 成绩<60 ORDER BY 课程号 DESC;
14.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列 /*
分析思路
select 查询结果 [课程号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [每门课程：按课程号分组]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc];
*/
SELECT 课程号,AVG(成绩) as 平均成绩 FROM score GROUP BY 课程号 ORDER BY 平均成绩 ASC，课程号 DESC;
15.检索课程编号为“0004”且分数⼩于60的学⽣学号，结果按按分数降序排列
/*
分析思路
select 查询结果 []
from 从哪张表中查数据 [成绩表score]
where 查询条件 [课程编号为“04”且分数⼩于60]
group by 分组 [没有]
having 对分组结果指定条件 []
order by 对查询结果排序[查询结果按按分数降序排列];
*/
SELECT 学号 FROM score WHERE 课程号='0004' AND 成绩<60 ORDER BY 成绩 DESC;
16.统计每门课程的学⽣选修⼈数(超过2⼈的课程才统计)，要求输出课程号和选修⼈数，查询结果按⼈数降序排序，若⼈数相同，按课程号升序排序/*
分析思路
select 查询结果 [要求输出课程号和选修⼈数]
from 从哪张表中查数据 []
where 查询条件 []
group by 分组 [每门课程：按课程号分组]
having 对分组结果指定条件 [学⽣选修⼈数(超过2⼈的课程才统计)：每门课程学⽣⼈数>2]
order by 对查询结果排序[查询结果按⼈数降序排序，若⼈数相同，按课程号升序排序];excel表格教学视频入门教程
*/
SELECT 课程号,COUNT(学号) as 选修⼈数 FROM score GROUP BY 课程号 HAVING 选修⼈数>2 ORDER BY 选修⼈数 DESC，课程号 ASC;
17.查询两门以上不及格课程的同学的学号及其平均成绩
/*
分析思路
先分解题⽬：
1）[两门以上][不及格课程]限制条件
2）[同学的学号及其平均成绩]，也就是每个学⽣的平均成绩，显⽰学号，平均成绩
分析过程：
第1步：得到每个学⽣的平均成绩，显⽰学号，平均成绩
第2步：再加上限制条件：
1）不及格课程
2）两门以上[不及格课程]：课程数⽬>2
/*
第1步：得到每个学⽣的平均成绩，显⽰学号，平均成绩
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查数据 [涉及到成绩：成绩表score]
where 查询条件 [没有]
group by 分组 [每个学⽣的平均：按学号分组]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[没有];
*/
select 学号, avg(成绩) as 平均成绩
from score
group by 学号;
/*
第2步：再加上限制条件：
1）不及格课程
2）两门以上[不及格课程]
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查数据 [涉及到成绩：成绩表score]
where 查询条件 [限制条件：不及格课程，平均成绩<60]
group by 分组 [每个学⽣的平均：按学号分组]
having 对分组结果指定条件 [限制条件：课程数⽬>2，汇总函数count(课程号)>2]
order by 对查询结果排序[没有];
*/
SELECT 学号,AVG(成绩) as 平均成绩 FROM score WHERE 成绩<60 GROUP BY 学号 HAVING COUNT(成绩)>2;
汇总分析
18.查询学⽣的总成绩并进⾏排名
【知识点】分组查询
/*
分析思路
select 查询结果 [总成绩：sum(成绩), 学号]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [学⽣的总成绩：按照每个学⽣学号进⾏分组]
order by 排序 [按照总成绩进⾏排序：sum(成绩)];
/*
SELECT 学号,SUM(成绩) as 总成绩 FROM score GROUP BY 学号 ORDER BY 总成绩 DESC;
19.查询平均成绩⼤于60分的学⽣的学号和平均成绩
【知识点】分组+条件
/*
分析思路
select 查询结果 [学号, 平均成绩: avg(成绩)]
from 从哪张表中查数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [学号]
having 分组条件 [平均成绩⼤于60分：avg(成绩 ) >60]
order by 排序 [没有];
/
*
SELECT 学号,AVG(成绩) as 平均成绩 FROM score GROUP BY 学号 HAVING 平均成绩>60;
复杂查询
20.查询所有课程成绩⼩于60分学⽣的学号、姓名
【知识点】⼦查询
1.翻译成⼤⽩话
1）查询结果：学⽣学号，姓名
2）查询条件：所有课程成绩 < 60 的学⽣，需要从成绩表⾥查，⽤到⼦查询
第1步，写⼦查询（所有课程成绩 < 60 的学⽣）
select 查询结果[学号]
from 从哪张表中查数据[成绩表：score]
pycharm注释快捷键
where 查询条件[成绩 < 60]
营养学常量元素group by 分组[没有]
having 对分组结果指定条件[没有]
order by 对查询结果排序[没有]
limit 从查询结果中取出指定⾏[没有];
select 学号
from student
where 成绩 < 60;
第2步，查询结果：学⽣学号，姓名，条件是前⾯1步查到的学号
select 查询结果[学号，姓名]
from 从哪张表中查数据[学⽣表:student]
where 查询条件[⽤到运算符in]
group by 分组[没有]
having 对分组结果指定条件[没有]
order by 对查询结果排序[没有]
limit 从查询结果中取出指定⾏[没有];
*/
SELECT 学号，姓名 FROM student WHERE 学号 in (SELECT 学号 FROM score WHERE 成绩<60);
21.查询没有学全所有课的学⽣的学号、姓名
/*
查出学号，条件：没有学全所有课，也就是该学⽣选修的课程数 < 总的课程数
【考察知识点】in，⼦查询
*/
SELECT 学号，姓名 FROM student WHERE 学号 IN ( SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(学号)<( SELECT COUNT(课程号) FROM course ));
22.查询出只选修了两门课程的全部学⽣的学号和姓名
SELECT 学号，姓名 FROM student WHERE 学号 IN ( SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)=2);
23.1990年出⽣的学⽣名单
/*
查1990年出⽣的学⽣名单
学⽣表中出⽣⽇期列的类型是datetime
*/
SELECT * FROM student WHERE 出⽣⽇期 LIKE "1990%";
SELECT * FROM student WHERE YEAR(出⽣⽇期) = 1990;
24.查询各科成绩前两名的记录
/*
第1步，查出有哪些组
我们可以按课程号分组，查询出有哪些组，对应这个问题⾥就是有哪些课程号
select 课程号,max(成绩) as 最⼤成绩
from score
group by 课程号;
第2步：先使⽤order by⼦句按成绩降序排序（desc），然后使⽤limt⼦句返回topN（对应这个问题返回的成绩前两名）
-- 课程号'0001' 这⼀组⾥成绩前2名
select *
from score
where 课程号 = '0001'
order by 成绩 desc
limit 2;
同样的，可以写出其他组的（其他课程号）取出成绩前2名的sql
第3步，使⽤union all 将每组选出的数据合并到⼀起
-- 左右滑动可以可拿到全部sql
(select * from score where 课程号 = '0001' order by 成绩 desc limit 2)
union all
(select * from score where 课程号 = '0002' order by 成绩 desc limit 2)
union all
(select * from score where 课程号 = '0003' order by 成绩 desc limit 2);
*/
(SELECT * FROM score WHERE 课程号 = '0001' ORDER BY 成绩 DESC LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号 = '0002' ORDER BY 成绩 DESC LIMIT 2)
UNION ALL
(SELECT * FROM score WHERE 课程号 = '0003' ORDER BY 成绩 DESC LIMIT 2);
25.查询各学⽣的年龄（精确到⽉份）
下⾯说明了TIMESTAMPDIFF函数的语法。
TIMESTAMPDIFF(unit,begin,end);
TIMESTAMPDIFF函数返回begin-end的结果，其中begin和end是或表达式。
TIMESTAMPDIFF函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。如果使⽤DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end-begin)的结果的单位，表⽰为整数。以下是有效单位：
MICROSECOND
mysql面试题sqlSECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
NOW() 函数返回当前的⽇期和时间。
# 这个⾃⼰写的，只计算了年，错误，下⾯才是正确答案
SELECT 学号,YEAR(CURRENT_DATE) - YEAR(出⽣⽇期) as 年龄 FROM student;
select 学号 ,timestampdiff(month ，出⽣⽇期 ,now())/12 from student ;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论