本博客SQL腳本地址：gitee
準備作業
已知有如下4張表：
學生表：student(學號,學生姓名,出生年月,性別)
成績表：score(學號,課程號,成績)
課程表：course(課程號,課程名稱,教師號)
教師表：teacher(教師號,教師姓名)
一、創建資料庫和表
為了演示題目的運行程序，我們先按下面陳述句在客戶端HeidiSQL中創建資料庫和表，
1、創建表
1.1、創建學生表（student）
1.2、創建成績表（score）
創建"成績表“，“課程表的“學號”和“課程號”一起設定為主鍵約束（聯合主鍵），“成績”這一列設定為數值型別（float，浮點數值）
1.3、創建課程表（course）
課程表的“課程號”設定為主鍵約束
1.4、教師表（teacher）
教師表的“教師號”列設定為主鍵約束，
教師姓名這一列設定約束為“null”（紅框的地方不勾選），表示這一列允許包含空值（null）
2、向表中添加資料
2.1、學生表
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' , '男');
2.2、成績表（score）
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);
2.3、課程表
insert into course(課程號,課程名稱,教師號)
values('0001' , '語文' , '0002');
insert into course(課程號,課程名稱,教師號)
values('0002' , '數學' , '0001');
insert into course(課程號,課程名稱,教師號)
values('0003' , '英語' , '0003');
2.4、教師表
insert into teacher(教師號,教師姓名)
values('0001' , '孟扎扎');
insert into teacher(教師號,教師姓名)
values('0002' , '馬化騰');
-- 這里的教師姓名是空值（null）
insert into teacher(教師號,教師姓名)
values('0003' , null);
-- 這里的教師姓名是空字串（''）
insert into teacher(教師號,教師姓名)
values('0004' , '');
50道面試題
為了方便學習，將50道面試題進行了分類
一、簡單查詢
查詢姓“猴”的學生名單
1.查詢姓“孟”老師的個數
select count(教師號)
from teacher
where 教師姓名 like '孟%';
二、匯總分析
2、查詢課程編號為“0002”的總成績
select sum(成績)
from score
where 課程號 = '0002';
3、查詢選了課程的學生人數
select count(distinct 學號) as 學生人數
from score;
4、查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分
select 課程號,max(成績) as 最高分,min(成績) as 最低分
from score
group by 課程號;
5、查詢每門課程被選修的學生數
select 課程號, count(學號) as 人數
from score
group by 課程號;
6、查詢男生、女生人數
select 性別,count(*) as 人數
from student
group by 性別;
7、查詢平均成績大于60分學生的學號和平均成績
select 學號, avg(成績) as 平均成績
from score
group by 學號
having avg(成績)>60;
8、查詢至少選修兩門課程的學生學號
select 學號, count(課程號) as 選修課程數目
from score
group by 學號
having count(課程號)>=2;
9、查詢同名同姓學生名單并統計同名人數
select 姓名,count(*) as 人數
from student
group by 姓名
having count(*)>=2;
10、查詢不及格的課程并按課程號從大到小排列
select 課程號
from score
where 成績<60
order by 課程號 desc;
11、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
select 課程號, avg(成績) as 平均成績
from score
group by 課程號
order by 平均成績 asc,課程號 desc;
12、檢索課程編號為“0004”且分數小于60的學生學號，結果按按分數降序排列
select 學號
from score
where 課程號='0004' and 成績 <60
order by 成績 desc;
13、統計每門課程的學生選修人數(超過2人的課程才統計)
要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序
select 課程號, count(學號) as '選修人數'
from score
group by 課程號
having count(學號)>2
order by count(學號) desc,課程號 asc;
14、查詢兩門以上不及格課程的同學的學號及其平均成績
select 學號, avg(成績) as 平均成績
from score
where 成績 <60
group by 學號
having count(課程號)>2;
.
15、查詢學生的總成績并進行排名
select 學號 ,sum(成績) as 總成績
from score
group by 學號
order by sum(成績);
16、查詢平均成績大于60分的學生的學號和平均成績
select 學號 ,avg(成績)
from score
group by 學號
having avg(成績 )>60;
三、復雜查詢
17、查詢所有課程成績小于60分學生的學號、姓名
select student.學號,姓名
from student,score
where 成績<60 and student.`學號`=score.`學號`;
18、查詢沒有學全所有課的學生的學號、姓名
select score.學號,姓名
from student,score
group by score.學號
having count(課程號)<(select count(課程號) from course);
19、查詢出只選修了兩門課程的全部學生的學號和姓名
select score.學號,姓名
from student,score
where student.`學號`=score.`學號`
group by score.`學號`
having count(課程號)=2;
20、1990年出生的學生名單
select 學號,姓名
from student
where year(出生日期)=1990;
21、查詢各學生的年齡（精確到月份）
select 學號,timestampdiff(month ,出生日期 ,now())/12 as 年齡
from student;
22、查詢本月過生日的學生
select *
from student
where month (出生日期 )=month(now());
四、多表查詢
23、查詢所有學生的學號、姓名、選課數、總成績
selecta.學號,a.姓名,count(b.課程號) as 選課數,sum(b.成績) as 總成績
from student as a left join score as b
on a.學號 = b.學號
group by a.學號;
24、查詢平均成績大于85的所有學生的學號、姓名和平均成績
select a.學號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b
on a.學號 = b.學號
group by a.學號
having avg(b.成績)>85;
25、查詢學生的選課情況：學號，姓名，課程號，課程名稱
select a.學號, a.姓名, c.課程號,c.課程名稱
from student a
inner join score b on a.學號=b.學號
inner join course c on b.課程號=c.課程號;
26、查詢出每門課程的及格人數和不及格人數
select 課程號,
sum(case when 成績>=60 then 1
else 0
end) as 及格人數,
sum(case when 成績 < 60 then 1
else 0
end) as 不及格人數
from score
group by 課程號;
27、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100
then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成績<85
then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70
then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b
on a.課程號=b.課程號
group by a.課程號,b.課程名稱;
28、查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名
select a.學號,a.姓名
from student as a inner join score as b on a.學號=b.學號
where b.課程號='0003' and b.成績>80;
.
29、檢索"0001"課程分數小于60，按分數降序排列的學生資訊
思路如圖：
.
select a.*,b.成績
from student as a
inner join score as b
on a.學號 =b.學號
where b.成績 <60 and b.課程號 =01
order by b.成績 desc;
30、查詢不同老師所教不同課程平均分從高到低顯示
【知識點】分組+條件+排序+多表連接，思路如圖
.
select a.教師號,a.教師姓名,avg(c.成績)
from teacher as a
inner join course as b
on a.教師號= b.教師號
inner join score c on b.課程號= c.課程號
group by a.教師姓名
order by avg(c.成績) desc;
31、查詢課程名稱為"數學"，且分數低于60的學生姓名和分數
【知識點】多表連接，思路如圖
select a.姓名,b.成績
from student as a
inner join score as b
on a.學號 =b.學號
inner join course c on b.課程號 =c.課程號
where b.成績 <60 and c.課程名稱 ='數學';
32、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數（與上題類似）
select a.姓名,c.課程名稱,b.成績
from student as a
Inner join score as b
on a.學號=b.學號
Inner join course c on b.課程號=c.課程號
where b.成績>70;
33、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
【知識點】分組+條件+多表連接
翻譯成大白話:計算每個學號不及格分數個數，篩選出大于2個的學號并找出姓名，平均成績，思路如圖：
select b.姓名,avg(a.成績),a.學號
from score as a
Inner join student as b
on a.學號=b.學號
where a.成績<60
group by a.學號
having count(a.學號)>=2;
34、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
select distinct a.學號,a.成績,a.課程號
from score as a
inner join score as b
on a.學號 = b.學號
where a.成績 = b.成績 and a.課程號 != b.課程號;
35、查詢課程編號為“0001”的課程比“0002”的課程成績高的所有學生的學號
select a.學號
from
(select 學號 ,成績 from score where 課程號=01) as a
inner join
(select 學號 ,成績 from score where 課程號=02) as b
on a.學號 =b.學號
inner join student c on c.學號 =a.學號
where a.成績 >b.成績 ;
36、查詢學過編號為“0001”的課程并且也學過編號為“0002”的課程的學生的學號、姓名
select a.學號
from
(select 學號 ,成績 from score where 課程號=01) as a
inner join
(select 學號 ,成績 from score where 課程號=02) as b
on a.學號 =b.學號
inner join student c on c.學號 =a.學號
where a.成績 >b.成績 ;
37、查詢學過“孟扎扎”老師所教的所有課的同學的學號、姓名
select s.學號,s.姓名,a.學號,b.課程號,c.教師號,c.教師姓名
from student as s
inner join score as a
on s.`學號`=a.學號
inner join course b on a.課程號=b.課程號
inner join teacher c on b.教師號= c.教師號
where c.教師姓名 ='孟扎扎';
38、查詢沒學過"孟扎扎"老師講授的任一門課程的學生姓名（與上題類似，"沒學過"用not in來實作)
select 姓名,學號
from student
WHERE 學號 NOT IN(
select a.學號
from student as a
inner join score AS b
on a.學號 =b.學號
INNER JOIN course AS c ON b.課程號=c.課程號
INNER JOIN teacher AS d ON c.教師號=d.教師號
where d.教師姓名 ='孟扎扎');
39、查詢沒學過“孟扎扎”老師課的學生的學號、姓名（與上題類似）
select 學號, 姓名
from student
where 學號 not in
(select 學號 from score where 課程號=
(select 課程號 from course where 教師號 =
(select 教師號 from teacher where 教師姓名 ='孟扎扎')
)
);
40、查詢選修“孟扎扎”老師所授課程的學生中成績最高的學生姓名及其成績（與上題類似,用成績排名，用 limit 1得出最高一個）
select a.姓名,b.成績
From student as a
Inner join score as b on a.學號=b.學號
Inner join course as c on b.課程號=c.課程號
Inner join teacher as d on c.教師號=d.教師號
where d.教師姓名='孟扎扎'
order by b.成績 desc limit 1;
41、查詢至少有一門課與學號為“0001”的學生所學課程相同的學生的學號和姓名
select 學號,姓名
from student
where 學號 in
(select distinct(學號) from score where 課程號 in
(select 課程號 from score where 學號=0001))
and 學號!=0001;
42、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
【知識點】多表連接 新建欄位 ，思路如圖
select a.學號,avg(a.成績),
max(case when b.課程名稱='數學' then a.成績 else null end) AS 數學,
max(case when b.課程名稱='語文' then a.成績 else null end) AS 語文,
max(case when b.課程名稱='英語' then a.成績 else null end) AS 英語
from score as a
Inner join course as b
on a.課程號=b.課程號
group by a.學號;
五、SQL高級功能：視窗函式
43、查詢學生平均成績及其名次
【知識點】視窗函式排名，思路如圖
select 學號,avg(成績) as 平均成績,row_number() over( order by avg(成績) DESC) AS 名次
from score
group by 學號;
44、按各科成績進行排序，并顯示排名
select 課程號,row_number() over(partition by 課程號 order BY 成績) as 排名
from score;
45、查詢每門功成績最好的前兩名學生姓名
【知識點】視窗函式排名+多表連接+條件
select a.課程號,b.姓名,a.成績,a.ranking from (
select 課程號,學號,成績,row_number() over(partition by 課程號 order by 成績 desc) as ranking
from score) as a
inner join student b on a.學號=b.學號
where a.ranking<3;
45、查詢所有課程的成績第2名到第3名的學生資訊及該課程成績（與上一題相似）
select b.姓名,a.課程號,a.成績
from (
select 課程號,學號,成績,row_number() over( partition by 課程號 order by 成績 desc) as ranking
from score ) as a
inner join student as b
on a.學號 =b.學號
where a.ranking in(2,3);
46、查詢各科成績前三名的記錄（不考慮成績并列情況）（與上一題相似）
select b.姓名,a.課程號,a.成績
from(
select 課程號,學號,成績,
row_number() over( partition by 課程號 order by 成績 desc) as 'ranking'
from score) as a
inner join student as b
on a.學號=b.學號
where a.ranking<4;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19717.html
標籤：MySQL
上一篇：Hadoop入門之hdfs
下一篇：Mariadb之事務隔離級別