MySQL45題

數(shù)據(jù)表
--1.學(xué)生表 Student(SId,Sname,Sage,Ssex)
SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
--2.課程表 Course(CId,Cname,TId)
CId 課程編號(hào),Cname 課程名稱(chēng),TId 教師編號(hào)
--3.教師表 Teacher(TId,Tname)
TId 教師編號(hào),Tname 教師姓名
--4.成績(jī)表 SC(SId,CId,score)
SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)
創(chuàng)建測(cè)試數(shù)據(jù)
學(xué)生表 Student：
導(dǎo)入數(shù)據(jù)方法：將以下 mysql 語(yǔ)句，完整復(fù)制到 workbench 語(yǔ)句窗口（或者是 mysql 的黑窗口），然后運(yùn)行即可導(dǎo)入，不需要另外創(chuàng)建表益缠，下面表的操作一樣。這些語(yǔ)句第一條是創(chuàng)建表（create table）基公，后面都是插入數(shù)據(jù)到表中（insert into table ）幅慌。
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢(qián)電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '趙六' , '2017-01-01' , '女');
insert into Student values('13' , '孫七' , '2018-01-01' , '女');
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
教師表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成績(jī)表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1.查詢(xún)" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
select * from sc a left join student b
on a.sid=b.sid
inner join sc c
on a.cid !=c.cid and a.cid='01' and c.cid='02'
where a.score>c.score;

1.1.png

1.1. 查詢(xún)同時(shí)存在" 01 "課程和" 02 "課程的情況

select * from sc a inner join sc b

on a.sid=b.sid

and a.cid = '01' and b.cid = '02';

1.2.png

1.2. 查詢(xún)存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )
select * from sc a left join sc b
on a.sid=b.sid
and b.Cid='02' where a.Cid='01';

1.2.png

1.3. 查詢(xún)不存在" 01 "課程但存在" 02 "課程的情況
select * from sc
where sid not in (select sid from sc where Cid='01')
and Cid='02';

1.3.png

2.查詢(xún)平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
select s.Sid,Sname,avg(score) from student s left join sc
on s.Sid=sc.Sid
group by s.Sid having avg(score)>=60;

2.png

3.查詢(xún)?cè)?SC 表存在成績(jī)的學(xué)生信息
select s.* from sc left join student s on sc.Sid=s.Sid;

3.png

4.查詢(xún)所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名轰豆、選課總數(shù)胰伍、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為null)
select s.sid,sname,count(cid),sum(score) from student s left join sc

on s.sid=sc.sid group by s.sid;

4646.png

4.1. 查有成績(jī)的學(xué)生信息
select * from student a where
a.sid in (select sid from sc group by sid);

4.1.png

5.查詢(xún)「李」姓老師的數(shù)量
select count(*) from teacher where tname like '李%';

5.png

6.查詢(xún)學(xué)過(guò)「張三」老師授課的同學(xué)的信息

select s.* from sc left join student s on s.sid=sc.sid where cid in (select cid from
course where tid=(select tid from teacher where tname='張三')) group by sid;

6.png

7.查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息
select a.,count(cid) from student a inner join sc b on a.sid=b.sid group by a.sid
having count(cid)<(select count(cid) from course)

7.png

8.查詢(xún)至少有一門(mén)課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
select a. from student a inner join (select * from sc where cid in (select cid from sc where sid="01") group by sid) b on a.sid=b.sid;

8.png

9.查詢(xún)和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
(沒(méi)有1和2的。因?yàn)?IN"的作用可以篩選與條件完全相同酸休，或者有其中一個(gè)的）
SELECT s.*
FROM student s
INNER JOIN sc
ON s.sid = sc.sid
WHERE s.sid NOT IN(
SELECT sid
FROM sc #篩選沒(méi)有'01'同學(xué)的課程的同學(xué)sid
WHERE cid NOT IN (
SELECT cid
FROM sc
WHERE sid = '01'))
AND NOT s.sid = '01'
GROUP BY s.sid
HAVING COUNT(cid) = (
SELECT COUNT(1)
FROM sc
WHERE sid ='01'
GROUP BY sid);

9.png

10.查詢(xún)沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名
SELECT Sname FROM student WHERE sid NOT IN (
SELECT sid
FROM sc
WHERE cid IN(
SELECT CID
FROM course c
INNER JOIN teacher t
ON c.Tid = t.Tid
WHERE Tname='張三'));

10.png

11.查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)骂租，姓名及其平均成績(jī)
select s.sid,Sname,avg(score) from student as s
left join sc on s.sid=sc.sid where score<60
group by s.sid
having count(cid)>2;

11.png

12.檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息
select * from student as s left join sc on s.sid=sc.sid
where cid='01' and score<60
order by score desc;

12.png

13.按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
SELECT *
FROM student a
LEFT JOIN (
SELECT sc.,ag
FROM sc
INNER JOIN (
SELECT sid,AVG(score) ag
FROM sc
GROUP BY sid) s2
ON sc.sid=s2.sid) b
ON a.sid = b.sid
ORDER BY ag DESC;

13.png

14.查詢(xún)各科成績(jī)最高分斑司、最低分和平均分：以如下形式顯示：課程 ID渗饮，課程name，最高分宿刮，最低分互站，平均分，及格率僵缺，中等率胡桃，優(yōu)良率，優(yōu)秀率及格為>=60磕潮，中等為：70-80翠胰，優(yōu)良為：80-90，優(yōu)秀為：>=90 要求輸出課程號(hào)和選修人數(shù)自脯，查詢(xún)結(jié)果按人數(shù)降序排列之景，若人數(shù)相同，按課程號(hào)升序排列
select sc.cid,max(sc.score) as 最高分,min(sc.score) as 最低分,
avg(sc.score) as 平均分, count() as 選修人數(shù),
sum(case when sc.score>=60 then 1 else 0 end)/count() as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count() as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count() as 優(yōu)良率,
sum(case when sc.score>=90 and sc.score<100 then 1 else 0 end)/count() as 優(yōu)秀率 from sc
group by sc.cid order by count(*) desc,sc.cid asc;

14.png

15.按各科成績(jī)進(jìn)行排序冤今，并顯示排名闺兢， Score 重復(fù)時(shí)也繼續(xù)排名
select sid,cid,score, @rank:=@rank+1 as rn from sc ,(select @rank:=0) as t
order by score desc;

15.png

16.1. 查詢(xún)學(xué)生的總成績(jī)，并進(jìn)行排名戏罢，總分重復(fù)時(shí)不保留名次空缺
select s.* ,
@rank:=if (@sco=scos,'',@rank+1) as rn,
@sco:=scos from
(select sid,sum(score) as scos
from sc group by sid order by scos desc) s,
(select @rank:=0,@sco:=null)as t;

擴(kuò)展：賦值變量
第一種方法：
set @a：=1屋谭；
第二種：
select @b:=2;
修改：select @b:=4;

select s.* ,
@rank:=if (@sco=scos,@rank,@rank+1) as rn,
@sco:=scos from
(select sid,sum(score) as scos
from sc group by sid order by scos desc) s,
(select @rank:=0,@sco:=null)as t;

16.1.png

17.統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱(chēng)龟糕，[100-85]桐磁，[85-70]，[70-60]讲岁，[60-0] 及所占百分比
select * ,
concat(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end)/count()100,'%') as '[85-100]',
concat(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end)/count()100,'%') as '[70-85)',
concat(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end)/count()100,'%') as '[60-70)',
concat(sum(case when sc.score>=0 and sc.score<60 then 1 else 0 end)/count()100,'%') as '[0-60)'
from sc group by sc.cid;

17.png

18.查詢(xún)各科成績(jī)前三名的記錄
select * from sc a where(select count(*) from sc b where a.cid=b.cid
and b.score > a.score)<3;

18.png

19.查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)
select cid,count(1) from sc
group by cid;

19.png

20.查詢(xún)出只選修兩門(mén)課程的學(xué)生學(xué)號(hào)和姓名
select b.sid,b.Sname from sc a ,student b
where a.sid=b.sid group by a.sid having count() = 2;

20.png

21.查詢(xún)男生我擂、女生人數(shù)
select count() ,ssex from student group by ssex;

21.png

查詢(xún)名字中含有「風(fēng)」字的學(xué)生信息
select * from student where Sname like '%風(fēng)%';

22.png

23.查詢(xún)同名同性學(xué)生名單，并統(tǒng)計(jì)同名同姓人數(shù)
select * from student left join (select Sname ,Ssex,count(1)同名人數(shù) from student group by Sname,Ssex) as t1
on student.Sname=t1.Sname and student.Ssex=t1.Ssex
where t1.同名人數(shù)>1;

23.png
查詢(xún) 1990 年出生的學(xué)生名單
select * from student where year(Sage)='1990';

24.png
查查詢(xún)每門(mén)課程的平均成績(jī)校摩，結(jié)果按平均成績(jī)降序排列互妓，平均成績(jī)相同時(shí)浮禾，按課程編號(hào)升序排列
select cid,avg(score) as avg_score from sc group by cid
order by avg_score desc,cid asc;

25.png

查詢(xún)平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)卷扮、姓名和平均成績(jī)
select s.sid,Sname,avg(score) ag from student s left join sc
on s.sid = sc.sid group by s.sid
having ag >= 85;

26.png
查詢(xún)課程名稱(chēng)為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)
select sname,cname,score from sc inner join student s on s.sid=sc.sid and score < 60
inner join course c on sc.cid = c.cid and cname = '數(shù)學(xué)';

27.png

查詢(xún)所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)教沾，沒(méi)選課的情況）
select * from student s left join sc on s.sid=sc.sid;

28.png
查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?70 分以上的姓名蒲跨、課程名稱(chēng)和分?jǐn)?shù)
select a.sname, c.cname, sc.score
from sc left join student a on sc.sid=a.sid
left join course c on sc.cid=c.cid
where sc.score>70;

29.png

查詢(xún)存在不及格的課程
select distinct sc.cid from sc where score<60;

30.png
查詢(xún)課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名
SELECT s.sid,sname
FROM student s
INNER JOIN sc
ON s.sid = sc.sid
AND cid = '01'
AND score >= 80;

31.png
求每門(mén)課程的學(xué)生人數(shù)
select cid,count(1) from sc group by cid;

32.png
假設(shè)成績(jī)不重復(fù)，查詢(xún)選修「張三」老師所授課程的學(xué)生中详囤，成績(jī)最高的學(xué)生信息及其成績(jī)

select * from sc left join student s on s.sid=sc.sid
left join course c on sc.cid=c.cid
left join teacher d on c.tid=d.tid where Tname='張三'
order by sc.score desc limit 1;

33.png

假設(shè)成績(jī)有重復(fù)的情況下财骨，查詢(xún)選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
select sid ,score,cid,tname,rn
from
(select *,@rank:= if (@score=score,@rank,@rank+1) as rn ,
@score:=score from
(select a.sid , a.score ,c.cid ,d.Tname from sc a
left join student b on a.sid=b.sid
left join course c on a.cid=c.cid
left join teacher d on c.tid=d.tid where d.Tname='張三'
order by a.score desc ) a ,
(select @score:=null,@rank:=0) b) c
where rn = 1;

34.png
查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)藏姐、課程編號(hào)、學(xué)生成績(jī)
select *
from sc a
inner join sc b
on a.sid=b.sid
where a.cid != b.cid
and a.score=b.score
group by a.sid,a.cid;

35.png
查詢(xún)每門(mén)科目成績(jī)最好的前兩名
select sid ,cid,score,rank from (
select * ,
@rank:=if(@c_cid=cid, if (@sco=score,@rank,@rank+1),1) as rank,
@sco:=score,
@c_cid:=cid
from sc,
(select @sco:=null,@rank:=0,@c_cid:=null) b
order by cid,score desc) a
where rank<=2;

36.png

統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）
select ,count() from sc group by cid;

37.png
檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
select ,count() from sc group by sid
having count(*)>=2;

38.png

39该贾、查詢(xún)選修了全部課程的學(xué)生信息

select * ,count()from student s
inner join sc on s.sid=sc.sid
group by s.sid
having count(cid)=(select count() from course);

39.png

查詢(xún)各學(xué)生的年齡羔杨，只按年份來(lái)算
select *,
year(now())-year(sage) as age
from student;

40.png
按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則杨蛋，年齡減一
法1：使用timestampdiff函數(shù)
select sid,sname,
timestampdiff(year,sage,now()) as age
from student;
解法二：
select *,
case when substr(sage,6,5)<substr(now(),6,5) then year(now())-year(sage)
when substr(sage,6,5)>=substr(now(),6,5) then year(now())-year(sage) -1
end as age
from student;

41.png

42.查詢(xún)本周過(guò)生日的學(xué)生
week:查詢(xún)今年的第幾周兜材，參數(shù)為從星期幾開(kāi)始算新的一周
select * ,
week(sage),
week(now())
from student
where week(sage)=week(now());

由于now（）一直變，可能出現(xiàn)沒(méi)有數(shù)據(jù)集的生日日期逞力，所以可以挑一個(gè)數(shù)據(jù)集出現(xiàn)的數(shù)據(jù)放入
例如：select * ,
week(sage),
week(now())
from student
where week(sage)=week("2020-05-20");

42.png

查詢(xún)下周過(guò)生日的學(xué)生
select *,
week(sage),
week(now())
from student
where week(sage)=week(now())+1;
查詢(xún)本月過(guò)生日的學(xué)生
select *,
month(sage),
month(now())
from student
where month(sage)=month(now());

43.png

45.查詢(xún)下月過(guò)生日的學(xué)生
select *,
month(sage),
month(now())
from student
where month(sage)=month(now())+1;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末曙寡，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子寇荧，更是在濱河造成了極大的恐慌举庶，老刑警劉巖，帶你破解...
沈念sama閱讀 210,978評(píng)論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件揩抡，死亡現(xiàn)場(chǎng)離奇詭異户侥，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)峦嗤，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,954評(píng)論 2贊 384
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)蕊唐，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人烁设，你說(shuō)我怎么就攤上這事替梨。” “怎么了装黑？”我有些...
開(kāi)封第一講書(shū)人閱讀 156,623評(píng)論 0贊 345
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵副瀑，是天一觀(guān)的道長(zhǎng)。經(jīng)常有香客問(wèn)我曹体，道長(zhǎng)俗扇，這世上最難降的妖魔是什么？我笑而不...
開(kāi)封第一講書(shū)人閱讀 56,324評(píng)論 1贊 282
?港島之戀（遺憾婚禮）
正文為了忘掉前任箕别，我火速辦了婚禮铜幽，結(jié)果婚禮上滞谢，老公的妹妹穿的比我還像新娘。我一直安慰自己除抛，他們只是感情好狮杨，可當(dāng)我...
茶點(diǎn)故事閱讀 65,390評(píng)論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布。她就那樣靜靜地躺著到忽，像睡著了一般橄教。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上喘漏，一...
開(kāi)封第一講書(shū)人閱讀 49,741評(píng)論 1贊 289
城市分裂傳說(shuō)
那天护蝶，我揣著相機(jī)與錄音，去河邊找鬼翩迈。笑死持灰，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的负饲。我是一名探鬼主播堤魁，決...
沈念sama閱讀 38,892評(píng)論 3贊 405
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼返十！你這毒婦竟也來(lái)了妥泉？” 一聲冷哼從身側(cè)響起，我...
開(kāi)封第一講書(shū)人閱讀 37,655評(píng)論 0贊 266
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤洞坑，失蹤者是張志新（化名）和其女友劉穎盲链，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體检诗，經(jīng)...
沈念sama閱讀 44,104評(píng)論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡匈仗，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,451評(píng)論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了逢慌。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片悠轩。...
茶點(diǎn)故事閱讀 38,569評(píng)論 1贊 340
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖攻泼，靈堂內(nèi)的尸體忽然破棺而出火架，到底是詐尸還是另有隱情，我是刑警寧澤忙菠，帶...
沈念sama閱讀 34,254評(píng)論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布犹赖，位于F島的核電站竞漾，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜芭析，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,834評(píng)論 3贊 312
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一嫉父、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧，春花似錦犹菱、人聲如沸。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 30,725評(píng)論 0贊 21
一樁弒父案腊脱，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至龙亲，卻和暖如春陕凹，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背鳄炉。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 31,950評(píng)論 1贊 264
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工杜耙，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人拂盯。一個(gè)月前我還...
沈念sama閱讀 46,260評(píng)論 2贊 360
代替公主和親
正文我出身青樓泥技，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親磕仅。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,446評(píng)論 2贊 348