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

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

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

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

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}]單表查詢是指僅涉及一個表的查詢。5.1.1選擇列最基本的SELECT語句僅有要返回的列和這些列的來源表，這種不使用WHERE子句的查詢稱為無條件查詢，也稱作投影查詢。1．查詢表中所有的列使用SELECT語句查詢表中所有的列時，不必逐一列出列名，可用“*”通配符代替所有列名，但此時只能按照數(shù)據(jù)表中列的原有順序進行排列。任務1

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

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

mysql>USEcjgl;

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

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

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

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

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

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

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

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

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

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

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

實現(xiàn)學生成績管理數(shù)據(jù)庫的單表查詢4．模糊查詢當不知道精確的值時，可以使用LIKE關(guān)鍵字進行部分匹配查詢，也稱模糊查詢。LIKE關(guā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作為實際的字符對待。任務1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

所示。USEcjgl;SELECTxs.*,課程號FROMxsLEFTOUTERJOINcjONXS.學號=cj.學號;任務2

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

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

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

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

(SELECT學號

FROMcj

WHERE課程號IN

(SELECT課程號

FROMkc

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

)

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

FROMxsWHERE出生時間<ALL

(SELECT出生時間

FROMxs