02.26 MySQL數據庫聯合查詢

大家好，我是anyux。數據庫聯表查詢很重要，內聯查詢更是尤為重要。

需要將school.sql文件導入到數據庫中

數據庫多表聯合查詢，school庫下表的邏輯結構關係都在下圖中。可以通過驅動表加子表配合實現所有查詢要求。

統計zhang3,學習了幾門課

<code>select STU.sname as 姓名, count(SC.sno) as 數量 from student as STU join sc as SC on STU.sno= SC.sno where STU.sname='zhang3';/<code>

查詢zhang3,學習的課程名稱有哪些?

方法1

<code>select STU.sname as 姓名, COU.cname as 課程名  from student as STU join sc as SC on STU.sno = SC.sno join course as COU on SC.cno = COU.cno where STU.sname='zhang3';/<code>

方法2(推薦)

<code>select STU.sname as 姓名, group_concat(COU.cname) as 課程名  from student as STU join sc as SC on STU.sno = SC.sno join course as COU on SC.cno = COU.cno where STU.sname='zhang3' group by STU.sname;/<code>

查詢KING老師教的學生名.

<code>select TEA.tname as 教師名稱, group_concat(STU.sname) as 學生名稱 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where TEA.tname='KING' group by TEA.tname;/<code>

查詢KING所教課程的平均分數

<code>select COU.cname as 課程名稱,TEA.tname as 教師名稱, avg(SC.score) as 課程成績 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno = COU.cno where TEA.tname='KING' group by COU.cname,TEA.tname;/<code>

每位老師所教課程的平均分,並按平均分排序

<code>select TEA.tname as 教師名稱, COU.cname as 課程名稱, avg(SC.score) as 平均成績  from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno group by TEA.tname, COU.cname order by AVG(SC.score) DESC;/<code>

查詢KING所教的不及格的學生姓名

<code>select STU.sname as 學生姓名 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where TEA.tname='KING' and SC.score<60;/<code>

查詢所有老師所教學生不及格的信息

方法1

<code>select TEA.tname as 教師名稱,STU.sname as 學生姓名,SC.score as 學生成績 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where SC.score<60;/<code>

方法2(推薦,顯示優化)

<code>select TEA.tname as 教師名稱,group_concat(concat(STU.sname,":",SC.score)) as 不及格的 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where SC.score<60 group by TEA.tno;/<code>

查詢平均成績大於60分的同學的學號和平均成績；

<code>select STU.sno as 學號, avg(SC.score) as 平均成績 from student as STU join sc as SC on STU.sno=SC.sno group by STU.sno having avg(SC.score)>60;/<code>

查詢所有同學的學號、姓名、選課數、總成績；

<code>select STU.sno as 學號, STU.sname as 姓名, count(SC.cno) as 選課數, sum(SC.score) as 總成績  from student as STU join sc as SC on STU.sno=SC.sno group by STU.sno ;/<code>

查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

<code>select sc.cno as 課程ID, max(sc.score) as 最高分,min(sc.score) as 最低分 from sc group by sc.cno;/<code>

統計各位老師,所教課程的及格率

<code>select TEA.tname as 教師名稱,COU.cname as 課程名稱,concat((select count(sc.cno) from sc where sc.score>60)/(select count(sc.cno) from sc)*100,'%') as 及格率  from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno group by TEA.tno,COU.cname;/<code>

查詢每門課程被選修的學生數

<code>select COU.cno as 課程名稱, count(STU.sno) as 學生數量 from student as STU join sc as SC on STU.sno=SC.sno join course as COU on SC.cno=COU.cno group by COU.cno;/<code>

查詢出只選修了一門課程的全部學生的學號和姓名

<code>select STU.sno as 學號,STU.sname as 學生名稱,count(SC.cno) as 選修課程數量 from student as STU join sc as SC on STU.sno=SC.sno group by SC.sno having count(SC.sno)=1;/<code>

查詢選修課程門數超過1門的學生信息

<code>select STU.sno as 學號,STU.sname as 學生名稱,count(SC.cno) as 選修課程數量 from student as STU join sc as SC on STU.sno=SC.sno group by SC.sno having count(SC.sno)!=1;/<code>

統計每門課程:優秀(85分以上),良好(70-85),一般(60-70),不及格(小於60)的學生列表

<code>select STU.sname as 學生名稱,STU.sno as 學號,COU.cname as 課程名稱, case when SC.score>85 then '優秀' when SC.score>70 and SC.score<=85 then '良好' when SC.score>=60 and SC.score<=70 then '一般' when SC.score<60 then '不及格' end as 成績狀態 from student as STU join sc as SC on STU.sno=SC.sno join course as COU on SC.cno=COU.cno group by COU.cno,STU.sno,STU.sname,SC.score;/<code>

查詢平均成績大於85的所有學生的學號、姓名和平均成績

<code>select STU.sno as 學號,STU.sname as 學生名稱, avg(SC.score) as 平均成績 from student as STU join sc as SC on STU.sno=SC.sno group by SC.score,STU.sname,STU.sno having avg(SC.score)>85;/<code>

閱讀更多 anyux1 的文章

關鍵字: 聯合 數據庫 查詢