MySQL數(shù)據(jù)庫管理與應(yīng)用任務(wù)式教程（微課版）單元5 查詢數(shù)據(jù)

單元5查詢數(shù)據(jù)《MySQL數(shù)據(jù)庫管理與應(yīng)用任務(wù)式教程（慕課版）》學(xué)習(xí)內(nèi)容任務(wù)1實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢

2任務(wù)2實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢數(shù)據(jù)庫系統(tǒng)的組成任務(wù)3實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的子查詢?nèi)蝿?wù)4卸載MySQL任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢使用SELECT語句既可以完成簡單的單表查詢，也可以完成復(fù)雜的連接查詢和子查詢。

SELECT語句的基本語法格式如下。SELECT[ALL|DISTINCT|DISTINCTROW]select_expr[,select_expr]...[FROMtable_references[PARTITIONpartition_list]][WHEREwhere_condition][GROUPBY{col_name|expr|position}[ASC|DESC],…[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC],…][LIMIT{[offset,]row_count|row_countOFFSEToffset}]單表查詢是指僅涉及一個(gè)表的查詢。5.1.1選擇列最基本的SELECT語句僅有要返回的列和這些列的來源表，這種不使用WHERE子句的查詢稱為無條件查詢，也稱作投影查詢。1．查詢表中所有的列使用SELECT語句查詢表中所有的列時(shí)，不必逐一列出列名，可用“*”通配符代替所有列名，但此時(shí)只能按照數(shù)據(jù)表中列的原有順序進(jìn)行排列。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.1】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)生表xs中每位學(xué)生的信息。打開MySQLWorkbench，在代碼編輯區(qū)輸入如下語句。USEcjgl;SELECT*FROMxs;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢2．查詢表中指定的列許多情況下，用戶只對表中的部分列感興趣，可以使用SELECT語句查詢表中指定的列，各列名之間要以英文逗號分隔，列的顯示順序可以改變。當(dāng)列名很長或者涉及計(jì)算列時(shí)，為了方便閱讀，可以在列名之后使用AS子句來自定義列標(biāo)題（別名）以取代原來的列名。AS關(guān)鍵字可以省略，省略后列名和別名用空格隔開?！纠}5.2】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)生表xs中每位同學(xué)的姓名、學(xué)號和專業(yè)名。可以通過MySQL命令行客戶端執(zhí)行如下語句來實(shí)現(xiàn)查詢，結(jié)果如圖5-2所示。

mysql>USEcjgl;

mysql>SELECT姓名,學(xué)號,專業(yè)名AS專業(yè)FROMxs;可以用同樣的方式為數(shù)據(jù)表指定別名。表別名只在執(zhí)行查詢時(shí)使用，并不在返回結(jié)果中顯示。例如下面的語句。

SELECT姓名,學(xué)號,專業(yè)名'專業(yè)'FROMxsASa;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢3．查詢經(jīng)過計(jì)算的列SELECT子句中的列名列表可以是表達(dá)式，如例題5.3中用到了日期函數(shù)year()，用于輸出對列值計(jì)算后的值?！纠}5.3】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)生表xs中每位同學(xué)的學(xué)號、姓名和年齡。執(zhí)行如下語句，結(jié)果如圖5-3所示。USEcjgl;SELECT學(xué)號,姓名,year(now())-year(出生時(shí)間)AS年齡FROMxs;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢4．消除重復(fù)行關(guān)鍵字DISTINCT可用于消除查詢結(jié)果中以某列為依據(jù)的重復(fù)行，以保證行的唯一性。DISTINCT關(guān)鍵字必須放在列名列表的前面，如果涉及多個(gè)列，則會對多個(gè)列進(jìn)行組合去重?！纠}5.4】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢選修了課程的學(xué)生的學(xué)號。執(zhí)行如下語句，結(jié)果如圖5-4所示。USEcjgl;SELECTDISTINCT學(xué)號FROMcj;成績表中相同學(xué)號的記錄可能有多行，要查詢選修了課程的學(xué)生的學(xué)號，只需要保留一條選課記錄。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5．限制返回的行數(shù)當(dāng)數(shù)據(jù)表中有很多行數(shù)據(jù)時(shí)，一次性查詢出表中的全部數(shù)據(jù)會降低數(shù)據(jù)返回的速度?？梢杂肔IMIT子句來限制查詢結(jié)果返回的行數(shù)。LIMIT子句可用于指定查詢結(jié)果從哪條記錄開始顯示多少條記錄，其基本語法格式如下。LIMIT[offset,]row_count|row_countOFFSEToffset①row_count：表示顯示的記錄條數(shù)。②OFFSET：表示偏移量；偏移量為0表示從第1條記錄開始顯示，偏移量為1表示從第2條記錄開始顯示，以此類推。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.5】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢選修了課程的前6位學(xué)生的學(xué)號，返回從第3條記錄開始的4條記錄。執(zhí)行如下語句，結(jié)果如圖5-5所示。USEcjgl;SELECT學(xué)號FROMcjLIMIT6;SELECT學(xué)號FROMcjLIMIT2,4;或SELECT學(xué)號FROMcjLIMIT4OFFSET2;5.1.2選擇行當(dāng)要在表中查找出滿足某些條件的行時(shí)，需要使用WHERE子句指定查詢條件，這種查詢稱為選擇查詢，其語法格式如下。WHERE<search_condition>其中，查詢條件可以是表達(dá)式比較、范圍比較、確定集合、模糊查詢、空值判斷和子查詢等表達(dá)式，其結(jié)果為TRUE、FALSE或UNKNOWN。1．表達(dá)式比較比較運(yùn)算符用于比較兩個(gè)表達(dá)式的值。比較運(yùn)算的語法格式如下。expression{=|<|<=|>|>=|<>}expression其中，expression是除TEXT、NTEXT和IMAGE類型外的表達(dá)式。當(dāng)需要通過WHERE子句指定一個(gè)以上的查詢條件時(shí)，則需要使用邏輯運(yùn)算符AND、OR、XOR和NOT將其連成復(fù)合的邏輯表達(dá)式。AND表示記錄滿足所有查詢條件時(shí)，才會被查詢出來。OR表示記錄滿足任意一個(gè)查詢條件時(shí)，就會被查詢出來。XOR表示記錄滿足其中一個(gè)條件，并且不滿足另一個(gè)條件時(shí)，才會被查詢出來。NOT表示記錄不滿足查詢條件時(shí)，才會被查詢出來。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.6】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢軟件技術(shù)專業(yè)的學(xué)生的信息。執(zhí)行如下語句，結(jié)果如圖5-6所示。SELECT*FROMxsWHERE專業(yè)名='軟件技術(shù)';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.7】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢軟件技術(shù)專業(yè)的男同學(xué)的信息。執(zhí)行如下語句，結(jié)果如圖5-7所示。USEcjgl;SELECT*FROMxsWHERE專業(yè)名='軟件技術(shù)'AND性別='男';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢2．范圍比較當(dāng)要查詢的條件是某個(gè)值的范圍時(shí)，可以使用關(guān)鍵字BETWEEN。BETWEEN用于檢查某個(gè)值是否在兩個(gè)值之間，其語法格式如下。expression[NOT]BETWEENexpression1ANDexpression2

注意：expression1的值不能大于expression2的值?！纠}5.8】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢2000年出生的學(xué)生的信息。執(zhí)行如下語句，結(jié)果如圖5-8所示。USEcjgl;SELECT*FROMxsWHERE出生時(shí)間BETWEEN'2000-1-1'AND'2000-12-31';或SELECT*FROMxsWHEREyear(出生時(shí)間)=2000;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢3．確定集合IN運(yùn)算符用來查詢屬性值屬于指定集合的元組，主要用于表達(dá)子查詢，其語法格式如下。expression[NOT]IN(subquery|expression[,…n])使用IN運(yùn)算符時(shí)不允許值列表中出現(xiàn)空值。【例題5.9】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找選修了課程號為“101”或“102”的同學(xué)的信息。執(zhí)行如下語句，結(jié)果如圖5-9所示。USEcjgl;SELECT學(xué)號,課程號FROMcjWHERE課程號IN('101','102');或SELECT學(xué)號,課程號FROMcjWHERE課程號='101'OR課程號='102';由此可以看出，IN運(yùn)算符實(shí)際上是多個(gè)OR運(yùn)算符的縮寫。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢4．模糊查詢當(dāng)不知道精確的值時(shí)，可以使用LIKE關(guān)鍵字進(jìn)行部分匹配查詢，也稱模糊查詢。LIKE關(guān)鍵字用于判斷一個(gè)字符串是否與指定的字符串匹配，其運(yùn)算對象可以是CHAR、VARCHAR、TEXT、NTEXT、DATETIME和SMALLDATETIME類型的數(shù)據(jù)，返回邏輯值True或False。模糊查詢的一般語法格式如下。string_expression[NOT]LIKEstring_expression[ESCAPEescape_character]字符串常量可以包含表5-1所示的MySQL通配符。“%”通配符不能用于匹配NULL。若要匹配用作通配符的字符，可用關(guān)鍵字ESCAPE，ESCAPE

escape_character表示將字符escape_character作為實(shí)際的字符對待。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.10】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找所有王姓同學(xué)的學(xué)號和姓名；查詢姓名中第2個(gè)漢字是“長”的同學(xué)的學(xué)號和姓名。執(zhí)行如下語句，結(jié)果如圖5-10所示。USEcjgl;SELECT學(xué)號,姓名FROMxsWHERE姓名LIKE'王%';SELECT學(xué)號,姓名FROMxsWHERE姓名LIKE'_長%';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5．空值判斷當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，可使用ISNULL關(guān)鍵字，其語法格式如下。expressionIS[NOT]NULL【例題5.11】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢沒有考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。在查詢窗口中輸入如下SQL語句并執(zhí)行。USEcjgl;SELECT學(xué)號,課程號FROMcjWHERE成績ISNULL;這里的空值條件不能寫成“成績=NULL”。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5.1.3對查詢結(jié)果進(jìn)行排序

使用ORDERBY子句可以對查詢結(jié)果進(jìn)行排序，其語法格式如下。ORDERBY{col_name|expr|position}[ASC|DESC][,...n]

其中，列名、表達(dá)式和位置值用于指定排序關(guān)鍵字。位置值表示排序列在選擇列表中所處位置的序號。多個(gè)列名間以英文逗號分隔，查詢結(jié)果先按指定的第一列進(jìn)行排序，然后按指定的下一列進(jìn)行排序。排序方式可以指定為降序（DESC）或升序（ASC），默認(rèn)為升序。

ORDERBY子句必須出現(xiàn)在其他子句之后。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.12】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，將軟件技術(shù)專業(yè)的學(xué)生按出生時(shí)間降序排列。執(zhí)行如下語句，結(jié)果如圖5-11所示。USEcjgl;SELECT*FROMxsWHERE專業(yè)名='軟件技術(shù)'ORDERBY出生時(shí)間,姓名DESC;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5.1.4使用聚合函數(shù)查詢在SELECT語句中，可以利用聚合函數(shù)對查詢結(jié)果進(jìn)行統(tǒng)計(jì)。聚合函數(shù)也稱為統(tǒng)計(jì)函數(shù)，主要用于對數(shù)據(jù)集合進(jìn)行統(tǒng)計(jì)，返回單個(gè)計(jì)算結(jié)果，如總和、平均值、最大值、最小值、行數(shù)，一般用于SELECT子句、HAVING子句和ORDERBY子句中。MySQL提供的聚合函數(shù)如表5-2所示。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢1．SUM()和AVG()

SUM()和AVG()分別用于求表達(dá)式中所有值的總和與平均值，忽略空值。其語法格式如下。SUM/AVG([ALL|DISTINCT]expression)【例題5.13】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)號為“001101”的學(xué)生的總分和平均分。執(zhí)行如下語句，結(jié)果如圖5-12所示。USEcjgl;SELECTSUM(成績)AS總分,AVG(成績)AS平均分FROMcjWHERE學(xué)號='001101';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢2．MAX()和MIN()

MAX()和MIN()分別用于求表達(dá)式中所有值的最大值與最小值，忽略空值。其語法格式如下。MAX/MIN([ALL|DISTINCT]expression);【例題5.14】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢選修了課程號為“206”課程的學(xué)生的最高分和最低分。執(zhí)行如下語句，結(jié)果如圖5-13所示。USEcjgl;SELECTMAX(成績)AS最高分,MIN(成績)AS最低分FROMcjWHERE課程號='206';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢3．COUNT()

COUNT()用于統(tǒng)計(jì)滿足條件的行數(shù)或總行數(shù)，COUNT()函數(shù)對空值不進(jìn)行計(jì)算，但會對0進(jìn)行計(jì)算。其語法格式如下。COUNT({[ALL|DISTINCT]expression}|*)【例題5.15】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)生的總?cè)藬?shù)。執(zhí)行如下語句，結(jié)果如圖5-14所示。USEcjgl;SELECTCOUNT(*)AS學(xué)生總數(shù)FROMxs;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5.1.5分組統(tǒng)計(jì)查詢在SELECT語句中，可以利用GROUPBY子句和HAVING子句等實(shí)現(xiàn)分組查詢。1．GROUPBY子句使用GROUPBY子句可以將查詢結(jié)果按列或列的組合在行的方向上進(jìn)行分組或分組統(tǒng)計(jì)，如對各個(gè)分組求總和、平均值、最大值、最小值、行數(shù)，每組在列或列的組合上具有相同的聚合值。GROUPBY子句的語法格式如下。GROUPBY[ALL]group_by_expression[,…,n][WITH{CUBE|ROLLUP}]注意：使用GROUPBY子句后，SELECT子句的列表中只能包含聚合函數(shù)中指定的列或GROUPBY子句中指定的列?！纠}5.16】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，將學(xué)生表xs中的數(shù)據(jù)按性別分組；查詢各專業(yè)的學(xué)生人數(shù)；查詢每位學(xué)生的學(xué)號及其選課的數(shù)量。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢執(zhí)行如下語句，結(jié)果如圖5-15所示。USEcjgl;SELECT學(xué)號,姓名,性別FROMxsGROUPBY性別;SELECT專業(yè)名,COUNT(*)AS學(xué)生人數(shù)FROMxsGROUPBY專業(yè)名;SELECT學(xué)號,COUNT(*)AS選課門數(shù)FROMcjGROUPBY學(xué)號;單獨(dú)使用GROUPBY子句時(shí)，查詢結(jié)果只顯示每個(gè)分組的第一條記錄，在實(shí)際應(yīng)用中意義不大。因此，GROUPBY子句通常和聚合函數(shù)配合使用，以達(dá)到分組統(tǒng)計(jì)的目的。在本例題中，GROUPBY子句按學(xué)號分組，所有具有相同學(xué)號的記錄為一組，對每一組使用函數(shù)COUNT()進(jìn)行計(jì)算，統(tǒng)計(jì)出各位學(xué)生選課的數(shù)量。任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢2．HAVING子句使用GROUPBY子句對數(shù)據(jù)進(jìn)行分組后，還可以使用HAVING子句對分組數(shù)據(jù)集合進(jìn)行篩選。HAVING子句支持WHERE子句中所有的操作符和語法。HAVING子句須與GROUPBY配合使用，不能單獨(dú)使用?！纠}5.17】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢平均成績大于85的學(xué)生的學(xué)號及平均成績。執(zhí)行如下語句，結(jié)果如圖5-16所示。USEcjgl;SELECT學(xué)號,AVG(成績)AS平均成績FROMcjGROUPBY學(xué)號HAVINGAVG(成績)>85;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.18】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢選課數(shù)在3門以上且各門課程均及格的學(xué)生的學(xué)號及其總分。執(zhí)行如下語句，結(jié)果如圖5-17所示。USEcjgl;SELECT學(xué)號,SUM(成績)AS總分FROMcjWHERE成績>=60GROUPBY學(xué)號HAVINGCOUNT(*)>=3;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢WHERE和HAVING子句的區(qū)別有如下幾點(diǎn)。一般情況下，WHERE子句用于過濾數(shù)據(jù)行，HAVING子句用于過濾分組結(jié)果。WHERE子句的查詢條件中不可以使用聚合函數(shù)，HAVING子句的查詢條件中可以使用聚合函數(shù)。WHERE子句在對數(shù)據(jù)分組前進(jìn)行過濾，而HAVING子句在對數(shù)據(jù)分組后進(jìn)行過濾。WHERE子句的查詢條件中不可以使用列的別名，而HAVING子句的查詢條件中可以使用列的別名。3．WITHROLLUPWITHROLLUP關(guān)鍵字用來在所有記錄的最后加上一條記錄，該記錄是前面所有記錄的總和，起到總計(jì)的作用?！纠}5.19】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，進(jìn)行如下操作。（1）查找各專業(yè)的學(xué)生人數(shù)，并生成一個(gè)學(xué)生總?cè)藬?shù)行。（2）查找各專業(yè)的學(xué)生人數(shù)和學(xué)生姓名，并生成一個(gè)學(xué)生總?cè)藬?shù)行。執(zhí)行如下語句，操作（1）的結(jié)果如圖5-18（a）所示。USEcjgl;SELECT專業(yè)名,COUNT(*)AS人數(shù)FROMxsGROUPBY專業(yè)名WITHROLLUP;執(zhí)行如下語句，操作（2）的結(jié)果如圖5-18（b）所示。函數(shù)GROUP_CONCAT(name)用于顯示每個(gè)分組的所有name列的值。SELECT專業(yè)名,COUNT(*)AS人數(shù),GROUP_CONCAT(姓名)該專業(yè)學(xué)生姓名FROMxsGROUPBY專業(yè)名WITHROLLUP;任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5.1.6用查詢結(jié)果生成新表使用CREATETABLE語句可以將通過SELECT語句查詢所得的結(jié)果保存到一個(gè)新建的表中，其語法格式如下。CREATETABLEnew_table[AS]SELECT*FROMtable;其中，new_table是要?jiǎng)?chuàng)建的新表名?！纠}5.20】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，依據(jù)學(xué)生表xs創(chuàng)建軟件技術(shù)專業(yè)學(xué)生表rjxs1，其中包括學(xué)號、姓名和性別列。執(zhí)行如下語句，結(jié)果如圖5-19所示。USEcjgl;CREATETABLErjxs1SELECT學(xué)號,姓名,性別FROMxsWHERE專業(yè)名='軟件技術(shù)';任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢5.1.7合并結(jié)果表兩個(gè)或多個(gè)SELECT查詢的結(jié)果可以合并到一個(gè)表中，并且不需要對這些行進(jìn)行任何修改，但要求所有查詢結(jié)果中的列數(shù)和列的順序必須相同、數(shù)據(jù)類型必須兼容，這種操作稱為聯(lián)合查詢。聯(lián)合查詢常用于歸檔數(shù)據(jù)，其運(yùn)算符為UNION，語法格式如下。{<queryspecification>|(<queryexpression>)}UNION[ALL]{<queryspecification>|(<queryexpression>)}[…n]任務(wù)1

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的單表查詢【例題5.21】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，新建軟件技術(shù)專業(yè)學(xué)生表rjxs、網(wǎng)絡(luò)技術(shù)專業(yè)學(xué)生表wlxs，分別存儲兩個(gè)專業(yè)的學(xué)生信息，表結(jié)構(gòu)與學(xué)生表xs相同，將這兩個(gè)表的數(shù)據(jù)合并到學(xué)生表xs中。執(zhí)行如下語句。USEcjgl;SELECT*FROMxsUNIONALLSELECT*FROMrjxsUNIONALLSELECT*FROMwlxs;5.2.1交叉連接查詢交叉連接（CROSSJOIN）又稱笛卡兒連接，實(shí)際上是將兩個(gè)表進(jìn)行笛卡兒積運(yùn)算，結(jié)果表是由第1個(gè)表的每行與第2個(gè)表的每行拼接后形成的表，因此，結(jié)果表的行數(shù)等于兩個(gè)表的行數(shù)之積。交叉連接查詢的語法格式如下。SELECT查詢列表FROM表1CROSSJOIN表2;或SELECT查詢列表FROM<表1>,<表2>;說明：（1）以上兩種語法的返回結(jié)果是相同的，但是第一種語法是官方建議的標(biāo)準(zhǔn)寫法。（2）當(dāng)進(jìn)行多個(gè)表的交叉連接時(shí)，在FROM后連續(xù)使用CROSSJOIN或,即可。任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢【例題5.22】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，列出學(xué)生所有可能的選課情況。執(zhí)行如下語句。USEcjgl;SELECT*FROMxsCROSSJOINkc;由此例可知，當(dāng)表中的數(shù)據(jù)較多時(shí)，通過交叉連接查詢得到的結(jié)果非常長，而且得到的許多記錄也沒太大的意義。所以，一般很少通過交叉連接的方式進(jìn)行多表查詢。另外，如果在交叉連接時(shí)使用WHERE子句，MySQL會先生成兩個(gè)表的笛卡兒積，然后再選擇滿足WHERE條件的記錄。因此，表的數(shù)量較多時(shí)，交叉連接查詢的速度非常慢。任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢5.2.2內(nèi)連接查詢內(nèi)連接（INNERJOIN）主要通過設(shè)置連接條件的方式，移除查詢結(jié)果中某些數(shù)據(jù)行的交叉連接。也就是利用條件表達(dá)式來消除交叉連接的某些數(shù)據(jù)行。內(nèi)連接查詢的語法格式如下：SELECT查詢列表

FROM<表1>[別名1]INNERJOIN<表2>[別名2]ON<連接條件表達(dá)式>[WHERE<條件表達(dá)式>];或SELECT查詢列表

FROM<表1>[別名1],<表2>[別名2][,…]WHERE<連接條件表達(dá)式>[AND<條件表達(dá)式>];說明：（1）內(nèi)連接查詢語句中可以省略INNER關(guān)鍵字，只用JOIN關(guān)鍵字。（2）ON子句用來設(shè)置內(nèi)連接的連接條件。注意要指定其中的列來源于哪一張表。如果表名非常長的話，可以給表設(shè)置別名。（3）內(nèi)連接有兩種實(shí)現(xiàn)形式，但是INNERJOIN...ON是官方的標(biāo)準(zhǔn)寫法，而且WHERE子句在某些時(shí)候會影響查詢的性能。（4）當(dāng)進(jìn)行多個(gè)表的內(nèi)連接時(shí)，在FROM后連續(xù)使用INNERJOIN或JOIN即可。如通過INNERJOIN連接3個(gè)數(shù)據(jù)表的方法如下。SELECT*FROM(表1INNERJOIN表2ON表1.列名=表2.列名)INNERJOIN表3ON表1.列名=表3.列名任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢1．等值連接查詢與非等值連接查詢當(dāng)連接運(yùn)算符為“=”時(shí)，連接運(yùn)算稱為等值連接，其他情況則稱為非等值連接?！纠}5.23】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找每個(gè)學(xué)生以及選修的課程信息。執(zhí)行如下語句，結(jié)果如圖5-20所示。USEcjgl;SELECTxs.*,cj.*FROMxsINNERJOINcjONxs.學(xué)號=cj.學(xué)號;或SELECTxs.*,cj.*FROMxs,cjWHERExs.學(xué)號=cj.學(xué)號;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢2．自然連接查詢?nèi)粼诘戎颠B接中把目標(biāo)列中重復(fù)的值去掉，則該連接為自然連接?！纠}5.24】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找每個(gè)學(xué)生以及選修的課程信息。執(zhí)行如下語句，結(jié)果如圖5-21所示。USEcjgl;SELECTxs.*,cj.課程號,cj.成績FROMxsINNERJOINcjONxs.學(xué)號=cj.學(xué)號;【例題5.25】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查詢學(xué)生王元選修的課程。執(zhí)行如下語句，結(jié)果如圖5-22所示。USEcjgl;SELECTxs.學(xué)號,姓名,課程號FROMxs,cjWHERExs.學(xué)號=cj.學(xué)號AND姓名='王元';任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢

當(dāng)進(jìn)行3個(gè)以上表的內(nèi)連接查詢時(shí)，在FROM關(guān)鍵字后連續(xù)使用INNERJOIN或JOIN即可。通過INNERJOIN連接3個(gè)數(shù)據(jù)表的方法如下。SELECT*FROM(表1INNERJOIN表2ON表1.列名=表2.列名)INNERJOIN表3ON表1.列名=表3.列名【例題5.26】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找學(xué)號、姓名、選修的課程名及成績。執(zhí)行如下語句，結(jié)果如圖5-23所示。USEcjgl;SELECTxs.學(xué)號,姓名,課程名,成績FROM(xsJOINcjONxs.學(xué)號=cj.學(xué)號)JOINkcONcj.課程號=kc.課程號;或SELECTxs.學(xué)號,姓名,課程名,成績FROMxs,cj,kcWHERExs.學(xué)號=cj.學(xué)號ANDcj.課程號=kc.課程號;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢【例題5.27】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找選修了“C程序設(shè)計(jì)”課程且成績在80分以上的學(xué)生的學(xué)號、姓名、課程名及成績。執(zhí)行如下語句，結(jié)果如圖5-24

所示。USEcjgl;SELECTxs.學(xué)號,姓名,課程名,成績FROMxs,cj,kcWHERExs.學(xué)號=cj.學(xué)號ANDkc.課程號=cj.課程號AND課程名='C程序設(shè)計(jì)'AND成績>=80;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢5.2.3自身連接查詢連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以在一個(gè)表與其自身之間進(jìn)行，即將同一個(gè)表的不同行連接起來，這種連接稱為表的自身連接。自身連接是多表連接的一種特殊情況，可以看作一個(gè)表的兩個(gè)副本之間的連接。自身連接通常用于表中的數(shù)據(jù)有層次結(jié)構(gòu)的情形，如區(qū)域表、菜單表、商品分類表等。當(dāng)需要在同一個(gè)表內(nèi)進(jìn)行比較、查找部分重復(fù)的記錄或找出列的組合時(shí)，可以使用自身連接。例如，在人力資源管理數(shù)據(jù)庫HR的員工表employees中有經(jīng)理、員工兩種身份，要查詢某個(gè)員工屬于哪個(gè)經(jīng)理的部門，而有的員工本身就是經(jīng)理，這時(shí)候就要用到自身連接。自身連接查詢的語法格式如下。SELECT查詢列表FROM<表1>[別名1]JOIN<表1>[別名2]ON<連接條件表達(dá)式>[WHERE<條件表達(dá)式>];使用自身連接時(shí)，必須為表取兩個(gè)別名，使之在邏輯上成為兩個(gè)表。任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢【例題5.28】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找選修了兩門以上課程的學(xué)生學(xué)號。執(zhí)行如下語句，結(jié)果如圖5-25所示。USEcjgl;SELECTDISTINCTA.學(xué)號FROMcjASAJOINcjASBONA.學(xué)號=B.學(xué)號WhereA.課程號<>B.課程號;或SELECTDISTINCTA.學(xué)號FROMcjASAJOINcjASBONA.學(xué)號=B.學(xué)號ANDA.課程號<>B.課程號;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢5.2.4外連接查詢在一般的連接操作中，只有滿足連接條件的記錄才能作為結(jié)果輸出。但有時(shí)也需要使一個(gè)或兩個(gè)表中不滿足連接條件的記錄出現(xiàn)在結(jié)果中，這時(shí)就需要用到外連接。外連接查詢的語法格式如下。SELECT查詢列表FROM<表1>LEFT|RIGHT[OUTER]JOIN<表2>ON<表1.列1>=<表2.列2>;其中，OUTER關(guān)鍵字可以省略。外連接只能對兩個(gè)表進(jìn)行。外連接會先將連接的表分為基表和參考表，然后以基表為依據(jù)返回滿足和不滿足條件的記錄。外連接包括左外連接和右外連接。（1）左外連接（LEFTOUTERJOIN）是指結(jié)果表中除了包括滿足連接條件的行外，還包括左表的所有行，此時(shí)左表為基表，右表為參考表。（2）右外連接（RIGHTOUTERJOIN）是指結(jié)果表中除了包括滿足連接條件的行外，還包括右表的所有行，此時(shí)右表為基表，左表為參考表。MySQL不支持完全外連接。任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢【例題5.29】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找所有學(xué)生及他們選修的課程號信息，若學(xué)生未選修任何課程，也要輸出其信息。執(zhí)行如下語句，結(jié)果如圖5-26

所示。USEcjgl;SELECTxs.*,課程號FROMxsLEFTOUTERJOINcjONXS.學(xué)號=cj.學(xué)號;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢【例題5.30】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找選修課程的信息和所有開設(shè)的課程名。執(zhí)行如下語句，結(jié)果如圖5-27

所示。USEcjgl;SELECTcj.*,課程名FROMcjRIGHTJOINkcONcj.課程號=kc.課程號;任務(wù)2

實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的連接查詢5.3.1帶IN謂詞的子查詢在嵌套查詢中，子查詢的結(jié)果往往是一個(gè)集合。IN子查詢用于判斷一個(gè)給定值是否在子查詢結(jié)果集中，其語法格式如下。Expression[NOT]IN(subquery)其中，subquery是子查詢。【例題5.31】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找選修了課程號為206的課程的學(xué)生信息。執(zhí)行如下語句，結(jié)果如圖5-28所示。任務(wù)3實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的子查詢USEcjgl;SELECT*FROMxsWHERE學(xué)號IN

(SELECT學(xué)號FROMcjWHERE課程號='206');或SELECT*FROMxs,cjWHERExs.學(xué)號=cj.學(xué)號and課程號='206';IN和NOTIN子查詢只能返回一列數(shù)據(jù)。任務(wù)3實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的子查詢【例題5.32】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找未選修“C程序設(shè)計(jì)”課程的學(xué)生的信息。執(zhí)行如下語句，結(jié)果如圖5-29所示。USEcjgl;SELECT*FROMxsWHERE學(xué)號NOTIN

(SELECT學(xué)號

FROMcj

WHERE課程號IN

(SELECT課程號

FROMkc

WHERE課程名='C程序設(shè)計(jì)'

)

);任務(wù)3實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的子查詢5.3.2帶比較運(yùn)算符的子查詢帶比較運(yùn)算符的子查詢是指父查詢與子查詢之間用比較運(yùn)算符進(jìn)行連接，可以認(rèn)為它是IN子查詢的擴(kuò)展，當(dāng)子查詢的返回值只有一個(gè)時(shí)，可以使用比較運(yùn)算符將父查詢和子查詢連接起來。其語法格式如下。expression{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(subquery)其中，ALL的含義為全部。任務(wù)3實(shí)現(xiàn)學(xué)生成績管理數(shù)據(jù)庫的子查詢【例題5.33】在學(xué)生成績管理數(shù)據(jù)庫cjgl中，查找比軟件技術(shù)專業(yè)所有學(xué)生年齡都大的學(xué)生信息。執(zhí)行如下語句，結(jié)果如圖5-30所示。USEcjgl;SELECT*

FROMxsWHERE出生時(shí)間<ALL

(SELECT出生時(shí)間

FROMxs