MYSQL練習二

前文連結：MYSQL練習一

表結構

在native中的表資料如下

-- 21.查詢學生平均成績及其名次
select s_id,avg(s_score),row_number() over(order by avg(s_score) desc)
from score
group by s_id;
-- 視窗函式是mysql8.0後支援的

-- 22.查詢每門課程被選修的學生數
select s.c_id,c.c_name,count(s_id) from score as s
inner join course as c on s.c_id=c.c_id
group by s.c_id,c.c_name;

-- 23.查詢出只有兩門課程的全部學生的學號和姓名
select sc.s_id,st.s_name from score as sc
inner join student st on sc.s_id=st.s_id
group by sc.s_id,st.s_name
having count(sc.c_id)=2;

-- 24.查詢男生、女生人數
select s_sex,count(s_id) from student
group by s_sex;
-- 方法2
select
sum(case when s_sex='男' then 1 else 0 end)"男生人數",
sum(case when s_sex='女' then 1 else 0 end)"女生人數"
from student;

-- 25.查詢名字中含由“風”字的學生資訊
select * from student
where s_name like "%風%";

-- 26.查詢1990年出生的學生名單(重點)
select * from student
where s_birth like "%1990%";
-- 方法2
select * from student
where year(s_birth)=1990;
-- 注意點,這種函式可以識別以下四種常用日期型別：
-- YYYY-MM-DD YYYYMMDD YYMMDD YYYY/MM/DD
select month('19980912')

-- 27.查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
select st.s_id,st.s_name,avg(s_score) as avg_score from student as st
inner join score as sc on st.s_id=sc.s_id
group by s_id
having avg_score>=85;

-- 28.查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
select s.c_id,c.c_name,avg(s_score) as avg_score from score as s
inner join course as c on s.c_id=c.c_id
group by s.c_id
order by avg_score asc,s.c_id desc;

-- 29.查詢課程名稱為“數學”，且分數低於60的學生姓名和分數
select st.s_id,st.s_name,s.s_score from score as s
inner join course as c on s.c_id=c.c_id
inner join student as st on st.s_id=s.s_id
where s.s_score<60 and c.c_name='數學'
group by s_id;

-- 30.查詢所有學生的課程及分數情況(重點)
select st.s_id,st.s_name,cs.c_id,cs.c_name,sc.s_score from student as st
inner join score as sc on st.s_id=sc.s_id
inner join course as cs on sc.c_id=cs.c_id;
-- 加強版
select st.s_id,st.s_name,
max(case when cs.c_name='語文' then sc.s_score else null end) "語文",
max(case when cs.c_name='數學' then sc.s_score else null end) "數學",
max(case when cs.c_name='英語' then sc.s_score else null end) "英語"
from student as st
inner join score as sc on st.s_id=sc.s_id
inner join course as cs on sc.c_id=cs.c_id
group by st.s_id,st.s_name;

-- 31.查詢課程成績在70分以上課程名稱，分數和學生姓名
select sc.c_id,cs.c_name,sc.s_score,st.s_name
from score as sc
inner join course as cs on sc.c_id=cs.c_id
inner join student as st on sc.s_id=st.s_id
where s_score>70;

-- 32.查詢不及格的課程並按課程號從大到小排列
select st.s_id,st.s_name,sc.c_id,cs.c_name,sc.s_score from score as sc
inner join student as st on sc.s_id=st.s_id
inner join course as cs on cs.c_id=sc.c_id
where sc.s_score<60
order by sc.c_id desc;

-- 33.查詢課程編號為03且課程成績在80分以上的學生的學號和姓名
select st.s_id,st.s_name,sc.c_id,sc.s_score from score as sc
inner join student as st on st.s_id=sc.s_id
where c_id='03' and s_score>80;

-- 34.求每門課程的學生人數
select sc.c_id,cs.c_name,count(s_id) from score as sc
inner join course as cs on sc.c_id=cs.c_id
group by c_id;

-- 35.查詢選修張三老師所授課程的學生中成績最高的學生姓名及其成績
select st.s_id,st.s_name,max(s_score) from score as sc
inner join student as st on sc.s_id=st.s_id
where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name='張三'));
-- 方法二
select s.s_id,st.s_name,s.s_score,c.c_id,c.c_name,t.t_name
from score as s
inner join course as c on s.c_id=c.c_id
inner join teacher as t on t.t_id=c.t_id
inner join student as st on st.s_id=s.s_id
where t.t_name='張三'
order by s.s_score desc limit 0,1;

-- 36.查詢不同課程成績相同的學生編號、課程編號、學生成績（重點）
select s_id,c_id,s_score from score where s_id in
(
    select c.s_id from
	(
        select a.s_id,a.s_score from score as a
        inner join 
        (select s_id from score group by s_id having count(distinct c_id)>1) as b
        on a.s_id=b.s_id
        group by a.s_id,a.s_score
	) as c
	group by c.s_id having count(c.s_id)=1
);
-- ps:要排除掉只選了一門課的學生

-- 37.統計每門課程的學生選修人數（超過5人的課程才統計），要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
select c_id,count(s_id) as count_sid from score
group by c_id
having count_sid>5
order by count_sid desc, c_id asc;

-- 38.檢索至少選修兩門課程的學生學號
select s_id,count(c_id) from score group by s_id having count(c_id)>1;

-- 39.查詢選修了全部課程的學生資訊
select st.* from score as sc
inner join student as st on sc.s_id=st.s_id
group by s_id
having count(c_id)=
(select count(distinct c_id) from score);

-- 40.查詢各學生的年齡(重點)
select s_id,s_birth,now(),floor(datediff(now(),s_birth)/365) from student;
-- ps:now()函式可以查當前時間，datediff(data1,data2)實現data1-data2，fllor()函式實現向下取整

-- 41.查詢沒學過張三老師講授的任一門課的學生姓名
-- 巢狀查詢
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name='張三')));
-- 連結串列查詢
select * from student where s_id not in(
	select s.s_id from score as s
    inner join course as c on c.c_id=s.c_id
    inner join teacher as t on t.t_id=c.t_id
    where t.t_name='張三'
);

-- 42.查詢下週過生日的學生（重點）
select * from student where week(concat('2020-',substring(s_birth,6,5)),1)
=week(now(),1)+1;
-- week(date,0)表示從0周開始算起到52周；week(date,1)表示從1周開始算起到53周
-- concat()函式用來連線字串，substring(a,b)用來分隔字串，從第a位取起，取b位

-- 43.查詢本月過生日的人
select * from student
where month(s_birth)=month(now());

-- 44.查詢下個月過生日的同學
select * from student where
case when month(now())=12 then month(s_birth)=1
else month(s_birth)=month(now())+1 end;