上一章的內容大家覺得怎么樣？
接下來，讓我們來一點一點繼續深入。
1.查詢成績大于60分的所有信息：
mysql>?select?*?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and ?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?where?score>60; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ |????1?|?劉一???|?福建???|???18?|?????2?|????2?|?數學???|????78?|?賀高???|?深圳???|?男???|??12345?| |????1?|?劉一???|?福建???|???18?|?????3?|????3?|?英語???|????67?|?楊艷???|?上海???|?男???|??12345?| |????2?|?錢二???|?深圳???|???19?|?????1?|????1?|?語文???|????79?|?葉平???|?福建???|?女???|??12346?| |????2?|?錢二???|?深圳???|???19?|?????2?|????2?|?數學???|????81?|?賀高???|?深圳???|?女???|??12346?| |????2?|?錢二???|?深圳???|???19?|?????3?|????3?|?英語???|????92?|?楊艷???|?上海???|?女???|??12346?| |????2?|?錢二???|?深圳???|???19?|?????4?|????4?|?物理???|????68?|?周磊???|?湖南???|?女???|??12346?| |????3?|?張三???|?上海???|???17?|?????1?|????1?|?語文???|????91?|?葉平???|?福建???|?男???|??12347?| |????3?|?張三???|?上海???|???17?|?????3?|????3?|?英語???|????88?|?楊艷???|?上海???|?男???|??12347?| |????4?|?李四???|?湖南???|???18?|?????2?|????2?|?數學???|????88?|?賀高???|?深圳???|?女???|??12348?| |????4?|?李四???|?湖南???|???18?|?????3?|????3?|?英語???|????90?|?楊艷???|?上海???|?女???|??12348?| |????4?|?李四???|?湖南???|???18?|?????4?|????4?|?物理???|????93?|?周磊???|?湖南???|?女???|??12348?| |????5?|?王五???|?江西???|???17?|?????3?|????3?|?英語???|????78?|?楊艷???|?上海???|?男???|??12349?| |????6?|?趙六???|?廣西???|???19?|?????2?|????2?|?數學???|????68?|?賀高???|?深圳???|?女???|??13349?| |????6?|?趙六???|?廣西???|???19?|?????4?|????4?|?物理???|????71?|?周磊???|?湖南???|?女???|??13349?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ 14?rows?in?set?(0.00?sec)
2.查詢年齡為18歲的男性學生考試成績大于60分的所有信息：
mysql>?select?*?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and ?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?where?(score>60?and?sage=18)and?ssex="男"; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ |????1?|?劉一???|?福建???|???18?|?????2?|????2?|?數學???|????78?|?賀高???|?深圳???|?男???|??12345?| |????1?|?劉一???|?福建???|???18?|?????3?|????3?|?英語???|????67?|?楊艷???|?上海???|?男???|??12345?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+ 2?rows?in?set?(0.00?sec)
3.查詢每個學生參加課程的總數，總分，平均分及其所有信息（CNAME字段除外）：
看上去很簡單，但是：
mysql>?select?count(t),sum(score),avg(score),student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from(select?student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone ?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1; ERROR?1054?(42S22):?Unknown?column?'student.s'?in?'field?list'
既然AS新表了，就不要再把查詢的字段歸到舊表下面了。
mysql>?select?count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone?from(select?student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from?student,teacher,sc,course, ?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1; +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |?count(t)?|?sum(score)?|?avg(score)?|?s????|?sname??|?cadd???|?sage?|?class?|?score?|?t????|?tname??|?tadd???|?ssex?|?cphone?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |???????21?|???????1483?|????70.6190?|????1?|?劉一???|?福建???|???18?|?????1?|????56?|????1?|?葉平???|?福建???|?男???|??12345?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ 1?row?in?set?(0.08?sec)
沒有指定分組，就直接把第一行的數據套過來了。
正解：
mysql>?select?a1.*,sum(score),count(t),avg(score)?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone?where?((((student.s=sc.s?and ?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?|?sum(score)?|?count(t)?|?avg(score)?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+ |????1?|?劉一???|?福建???|???18?|?????1?|????1?|?語文???|????56?|?葉平???|?福建???|?男???|??12345?|????????259?|????????4?|????64.7500?| |????2?|?錢二???|?深圳???|???19?|?????1?|????1?|?語文???|????79?|?葉平???|?福建???|?女???|??12346?|????????320?|????????4?|????80.0000?| |????3?|?張三???|?上海???|???17?|?????1?|????1?|?語文???|????91?|?葉平???|?福建???|?男???|??12347?|????????282?|????????4?|????70.5000?| |????4?|?李四???|?湖南???|???18?|?????2?|????2?|?數學???|????88?|?賀高???|?深圳???|?女???|??12348?|????????271?|????????3?|????90.3333?| |????5?|?王五???|?江西???|???17?|?????1?|????1?|?語文???|????46?|?葉平???|?福建???|?男???|??12349?|????????177?|????????3?|????59.0000?| |????6?|?趙六???|?廣西???|???19?|?????1?|????1?|?語文???|????35?|?葉平???|?福建???|?女???|??13349?|????????174?|????????3?|????58.0000?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+ 6?rows?in?set?(0.00?sec)
4.查詢每個學生參加課程的總數，總分，平均分及其所有信息后按照總分排降序后顯示評價分不小于80分的學生的所有信息：
mysql>??select?*?from(select?count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone?from(select?student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from?student,teacher,sc,course, ?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?order?by?sum(score)?desc)a3?having?avg(score)>=80; Empty?set?(0.00?sec)
SQL語句是沒有錯的（CNAME字段漏了），但是結果就……
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |?count(t)?|?sum(score)?|?avg(score)?|?s????|?sname??|?cadd???|?sage?|?class?|?score?|?t????|?tname??|?tadd???|?ssex?|?cphone?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |????????4?|????????320?|????80.0000?|????2?|?錢二???|?深圳???|???19?|?????1?|????79?|????1?|?葉平???|?福建???|?女???|??12346?| |????????4?|????????282?|????70.5000?|????3?|?張三???|?上海???|???17?|?????1?|????91?|????1?|?葉平???|?福建???|?男???|??12347?| |????????3?|????????271?|????90.3333?|????4?|?李四???|?湖南???|???18?|?????2?|????88?|????2?|?賀高???|?深圳???|?女???|??12348?| |????????4?|????????259?|????64.7500?|????1?|?劉一???|?福建???|???18?|?????1?|????56?|????1?|?葉平???|?福建???|?男???|??12345?| |????????3?|????????177?|????59.0000?|????5?|?王五???|?江西???|???17?|?????1?|????46?|????1?|?葉平???|?福建???|?男???|??12349?| |????????3?|????????174?|????58.0000?|????6?|?趙六???|?廣西???|???19?|?????1?|????35?|????1?|?葉平???|?福建???|?女???|??13349?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ 6?rows?in?set?(0.00?sec)
mysql>??select?*?from(select?count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone?from(select?student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from?student,teacher,sc,course, ?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?order?by?sum(score)?desc)a3?having?avg(score)>0; +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |?count(t)?|?sum(score)?|?avg(score)?|?s????|?sname??|?cadd???|?sage?|?class?|?score?|?t????|?tname??|?tadd???|?ssex?|?cphone?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |????????4?|????????320?|????80.0000?|????2?|?錢二???|?深圳???|???19?|?????1?|????79?|????1?|?葉平???|?福建???|?女???|??12346?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ 1?row?in?set?(0.00?sec)
而這個語句，這個結果的出現令我徹底蒙圈了。
最后想了一個以毒攻毒的辦法終于將之拿下。
mysql>??select?*?from(select?count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone?from(select?student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone?from?student,teacher,sc,course, ?cadd,tadd,cphone?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?order?by?sum(score)?desc)a3?having?sum(score)/4>=80; +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |?count(t)?|?sum(score)?|?avg(score)?|?s????|?sname??|?cadd???|?sage?|?class?|?score?|?t????|?tname??|?tadd???|?ssex?|?cphone?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ |????????4?|????????320?|????80.0000?|????2?|?錢二???|?深圳???|???19?|?????1?|????79?|????1?|?葉平???|?福建???|?女???|??12346?| +----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+ 1?row?in?set?(0.02?sec)
然而并沒有什么卵用……
當然還是有正解的：
mysql>?select?*?from?(select?a1.*,sum(score)?as?ss,count(t)?as?ct,avg(score)?as?a3?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone ?where?((((student.s=sc.s?and?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?desc)as?a2?where?a3=80?or?a3>80; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?|?ss???|?ct?|?a3??????| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |????4?|?李四???|?湖南???|???18?|?????2?|????2?|?數學???|????88?|?賀高???|?深圳???|?女???|??12348?|??271?|??3?|?90.3333?| |????2?|?錢二???|?深圳???|???19?|?????1?|????1?|?語文???|????79?|?葉平???|?福建???|?女???|??12346?|??320?|??4?|?80.0000?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ 2?rows?in?set?(0.00?sec)
出于科學嚴謹的態度（呃，好吧，是偶搞錯太多次了，怕了），讓我們一起來驗證一下：
mysql>?select?*?from?(select?a1.*,sum(score)?as?ss,count(t)?as?ct,avg(score)?as?a3?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone?where?((((student.s=sc.s?and ?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?desc)as?a2?where?a3=80?or?a3>60; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?|?ss???|?ct?|?a3??????| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |????4?|?李四???|?湖南???|???18?|?????2?|????2?|?數學???|????88?|?賀高???|?深圳???|?女???|??12348?|??271?|??3?|?90.3333?| |????3?|?張三???|?上海???|???17?|?????1?|????1?|?語文???|????91?|?葉平???|?福建???|?男???|??12347?|??282?|??4?|?70.5000?| |????2?|?錢二???|?深圳???|???19?|?????1?|????1?|?語文???|????79?|?葉平???|?福建???|?女???|??12346?|??320?|??4?|?80.0000?| |????1?|?劉一???|?福建???|???18?|?????1?|????1?|?語文???|????56?|?葉平???|?福建???|?男???|??12345?|??259?|??4?|?64.7500?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ 4?rows?in?set?(0.00?sec)
mysql>?select?*?from?(select?a1.*,sum(score)?as?ss,count(t)?as?ct,avg(score)?as?a3?from(select?student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone?from?student,teacher,sc,course,?cadd,tadd,cphone?where?((((student.s=sc.s?and ?teacher.t=course.t)?and?course.class=sc.class)and?cadd.s=student.s)and?tadd.t=teacher.t)?and?cphone.s=student.s)as?a1?group?by?s?desc)as?a2; +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |?s????|?sname??|?cadd???|?sage?|?class?|?t????|?cname??|?score?|?tname??|?tadd???|?ssex?|?cphone?|?ss???|?ct?|?a3??????| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ |????6?|?趙六???|?廣西???|???19?|?????1?|????1?|?語文???|????35?|?葉平???|?福建???|?女???|??13349?|??174?|??3?|?58.0000?| |????5?|?王五???|?江西???|???17?|?????1?|????1?|?語文???|????46?|?葉平???|?福建???|?男???|??12349?|??177?|??3?|?59.0000?| |????4?|?李四???|?湖南???|???18?|?????2?|????2?|?數學???|????88?|?賀高???|?深圳???|?女???|??12348?|??271?|??3?|?90.3333?| |????3?|?張三???|?上海???|???17?|?????1?|????1?|?語文???|????91?|?葉平???|?福建???|?男???|??12347?|??282?|??4?|?70.5000?| |????2?|?錢二???|?深圳???|???19?|?????1?|????1?|?語文???|????79?|?葉平???|?福建???|?女???|??12346?|??320?|??4?|?80.0000?| |????1?|?劉一???|?福建???|???18?|?????1?|????1?|?語文???|????56?|?葉平???|?福建???|?男???|??12345?|??259?|??4?|?64.7500?| +------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+ 6?rows?in?set?(0.00?sec)
通過這個案例我們可以清楚的看到七個表的聯合查詢還是很有挑戰性的，很多時候查詢出來的結果雖然是對的，甚至有些時候就連語句看起來也是對的……但是，呃，其實都是錯的。真正真確的語句是必須經得起反復驗證的（終于搞定了，鼓勵下）。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
