下面講講關(guān)于MySQL多表的查詢的簡(jiǎn)單示例，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完mysql多表的查詢的簡(jiǎn)單示例這篇文章你一定會(huì)有所受益。

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供蕪湖網(wǎng)站建設(shè)、蕪湖做網(wǎng)站、蕪湖網(wǎng)站設(shè)計(jì)、蕪湖網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、蕪湖企業(yè)網(wǎng)站模板建站服務(wù)，10多年蕪湖做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

導(dǎo)入hellodb.sql生成數(shù)據(jù)庫(kù)，并進(jìn)入到數(shù)據(jù)庫(kù)之中

mysql -uroot < hellodb.sql
use mysql

(1)以ClassID分組，顯示每班的同學(xué)的人數(shù)
`select classid,count(stuid) from students group by classid``

(2)以Gender分組，顯示其年齡之和
select gender,sum(age) from students group by gender

(3)以ClassID分組，顯示其平均年齡大于25的班級(jí)
select classid,avg(age) as new_age from students group by classid having new_age>25

(4)以Gender分組，顯示各組中年齡大于25的學(xué)員的年齡之和
select gender,sum(age) from students group by gender

(5)顯示前5位同學(xué)的姓名、課程及成績(jī)
select name,course,score from (select name,score,courseid from (select * from students where stuid<=5) as s inner join scores on scores.stuid=s.stuid)as t  inner join courses on courses.courseid=t.courseid

(6)顯示其成績(jī)高于80的同學(xué)的名稱及課程
select name,course from (select name,score,courseid from (select from scores where Score>80) as t inner join students on students.stuid=t.stuid) as t inner join courses on courses.courseid=t.courseid

(7)求前8位同學(xué)每位同學(xué)自己兩門課的平均成績(jī)，并按降序排列
select t.stuid,avg(score) from (select stuid,courseid from (select from students where stuid<=8) as s inner join coc on s.classid=coc.classid) as t inner join scores on s  cores.stuid=t.stuid group by t.stuid

(8)取每位同學(xué)各門課的平均成績(jī)，顯示成績(jī)前三名的同學(xué)的姓名和平均成績(jī)
select name,avg(score) as 平均分?jǐn)?shù) from (select name,courseid from students inner join coc on students.classid=coc.classid) as s inner join scores on s.courseid=scores.courseid group by stuid order by 平均分?jǐn)?shù) desc limit 3

(9)顯示每門課程課程名稱及學(xué)習(xí)了這門課的同學(xué)的個(gè)數(shù)
select courseid,count(CourseID) from students inner join coc on coc.classid=students.classid group by courseid

(10)顯示其年齡大于平均年齡的同學(xué)的名字
select * from students where age>(select avg(age) from students)

(11)顯示其學(xué)習(xí)的課程為第1、2，4或第7門課的同學(xué)的名字
select name,courseid from (select * from coc where CourseIDin ('1','2','4','7')) as new inner join students on students.classid=new.classid

(12)顯示其成員數(shù)最少為3個(gè)的班級(jí)的同學(xué)中年齡大于同班同學(xué)平均年齡的同學(xué)
select * from (select name,classid,age from students) as s inner join (select new.classid,avg(age) as cc from (select classid from students group by ClassID having count(stuid) >= 3) as new inner join students on students.classid=new.classid group by new.classid) as a on a.classid=s.classid where cc

對(duì)于以上mysql多表的查詢的簡(jiǎn)單示例相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。