第3章 MySQL查詢和視圖

第3章MySQL查詢和視圖——MySQL數(shù)據(jù)查詢MySQL教程（第2版）MySQL數(shù)據(jù)庫查詢使用SELECT語句可以從一個或多個表中選取符合某種條件的特定的行和列，結(jié)果通常是生成一個臨時表。下面介紹SELECT語句，它是SQL的核心。SELECT語句的語法格式如下：SELECT [ALL|DISTINCT|DISTINCTROW]

列... [FROM表...] [WHERE條件] [GROUPBY{列名|表達(dá)式|position}[ASC|DESC],...] [HAVING條件] [ORDERBY{列名|表達(dá)式|position}[ASC|DESC],...]01選擇輸出列1.選擇指定的列2.定義列別名3.替換查詢結(jié)果中的數(shù)據(jù)4.計算列值5.消除結(jié)果集中的重復(fù)行6.聚合函數(shù)選擇輸出列1．選擇指定的列使用SELECT語句選擇一個表中的某些列，各列名之間要以逗號分隔，所有列用“*”表示。語法格式為如下：SELECT*|列名,列名,...FROM表名【例3.1】查詢xscj數(shù)據(jù)庫的xs表中各個學(xué)生的姓名、專業(yè)名和總學(xué)分。USExscj SELECT姓名,專業(yè)名,總學(xué)分 FROMxs;選擇輸出列2．定義列別名當(dāng)希望查詢結(jié)果中的列標(biāo)題顯示為自己選擇的列標(biāo)題，可以在列名之后使用AS子句。語法格式如下：SELECT...列名[AS列別名]【例3.2】查詢xs表中計算機專業(yè)學(xué)生的學(xué)號、姓名和總學(xué)分，將結(jié)果中各列的標(biāo)題分別指定為number、name和mark。SELECT學(xué)號ASnumber,姓名ASname,總學(xué)分ASmark FROMxs WHERE專業(yè)名='計算機';執(zhí)行結(jié)果如圖。選擇輸出列3．替換查詢結(jié)果中的數(shù)據(jù)語法格式如下：CASE WHEN條件1THEN表達(dá)式1 WHEN條件2THEN表達(dá)式2 ... ELSE表達(dá)式NEND【例3.3】查詢xs表中計算機專業(yè)各學(xué)生的學(xué)號、姓名和總學(xué)分，對總學(xué)分按如下規(guī)則進(jìn)行替換：若總學(xué)分為空值，替換為“尚未選課”；若總學(xué)分小于15，替換為“不及格”；若總學(xué)分為15～50，替換為“合格”；若總學(xué)分大于50，替換為“優(yōu)秀”；將總學(xué)分列的標(biāo)題更改為“等級”。選擇輸出列替換操作代碼如下：SELECT學(xué)號,姓名,CASE WHEN總學(xué)分ISNULLTHEN'尚未選課’ WHEN總學(xué)分<15THEN'不及格’ WHEN總學(xué)分>=15AND總學(xué)分<=50THEN'合格’ ELSE'優(yōu)秀'ENDAS等級FROMxsWHERE專業(yè)名='計算機';執(zhí)行結(jié)果如圖。選擇輸出列4．計算列值輸出列可使用表達(dá)式表示。語法格式如下：SELECT表達(dá)式...【例3.4】按120分制重新計算成績，顯示cj表中學(xué)號為221101學(xué)生的成績信息。SELECT學(xué)號,課程號,成績*1.20AS成績120 FROMcj WHERE學(xué)號='221101';執(zhí)行結(jié)果如圖。選擇輸出列5．消除結(jié)果集中的重復(fù)行對表只選擇其某些列時，輸出的結(jié)果可能會出現(xiàn)重復(fù)行。語法格式如下：SELECTDISTINCT|DISTINCTROW列名...【例3.5】對xs表只選擇專業(yè)名列和總學(xué)分列，消除結(jié)果集中的重復(fù)行。SELECTDISTINCT專業(yè)名,總學(xué)分 FROMxs;執(zhí)行結(jié)果如圖。選擇輸出列6．聚合函數(shù)SELECT的輸出列還可以包含所謂的聚合函數(shù)。聚合函數(shù)常常用于對一組值進(jìn)行計算，然后返回單個值。除COUNT()函數(shù)外，聚合函數(shù)都會忽略空值。表3.1列出了一些常用的聚合函數(shù)。函

數(shù)

名說

明COUNT()統(tǒng)計記錄數(shù)，返回int類型整數(shù)MAX()求最大值MIN()求最小值SUM()返回表達(dá)式中所有值的和AVG()求組中值的平均值STD()或STDDEV()返回給定表達(dá)式中所有值的標(biāo)準(zhǔn)差VARIANCE()返回給定表達(dá)式中所有值的方差GROUP_CONCAT()返回由屬于一組的列值連接組合而成的結(jié)果BIT_AND()邏輯或BIT_OR()邏輯與BIT_XOR()邏輯異或選擇輸出列（1）COUNT()函數(shù)COUNT()函數(shù)用于統(tǒng)計組中滿足條件的行數(shù)或總行數(shù)，返回SELECT語句檢索到的行中非NULL值的數(shù)目，若找不到匹配的行，則返回0。語法格式如下：COUNT({[ALL|DISTINCT]表達(dá)式}|*)其中，表達(dá)式的數(shù)據(jù)類型可以是除BLOB或TEXT之外的任何類型。ALL表示對所有值進(jìn)行運算，DISTINCT表示去除重復(fù)值，默認(rèn)值為ALL。使用COUNT(*)時將返回檢索行的總數(shù)目，不論其是否包含NULL值。【例3.6】求學(xué)生的總數(shù)。SELECTCOUNT(*)AS'學(xué)生總數(shù)’ FROMxs;執(zhí)行結(jié)果如圖。選擇輸出列【例3.7】統(tǒng)計備注不為空的學(xué)生數(shù)目。SELECTCOUNT(備注)AS'備注不為空的學(xué)生數(shù)目’ FROMxs;執(zhí)行結(jié)果如圖?！纠?.8】統(tǒng)計總學(xué)分在50分以上的人數(shù)。SELECTCOUNT(總學(xué)分)AS'總學(xué)分50分以上的人數(shù)’ FROMxs WHERE總學(xué)分>50;執(zhí)行結(jié)果如圖。選擇輸出列（2）MAX()函數(shù)和MIN()函數(shù)MAX()函數(shù)和MIN()函數(shù)分別用于求表達(dá)式中所有值項的最大值與最小值。語法格式如下：MAX/MIN([ALL|DISTINCT]表達(dá)式)【例3.9】求選修101課程的學(xué)生的最高分和最低分。SELECTMAX(成績),MIN(成績) FROMcj WHERE課程號='101';執(zhí)行結(jié)果如圖。選擇輸出列（3）SUM()函數(shù)和AVG()函數(shù)SUM()函數(shù)和AVG()函數(shù)分別用于求表達(dá)式中所有值項的總和與平均值。語法格式如下：SUM/AVG([ALL|DISTINCT]表達(dá)式)【例3.10】求學(xué)號為221101的學(xué)生所學(xué)課程的總成績。SELECTSUM(成績)AS'課程總成績’ FROMcj WHERE學(xué)號='221101';執(zhí)行結(jié)果如圖?！纠?.11】求選修101課程的學(xué)生的平均成績。SELECTAVG(成績)AS'課程101平均成績’ FROMcj WHERE課程號='101';執(zhí)行結(jié)果如圖。選擇輸出列（4）VARIANCE()函數(shù)和STDDEV()函數(shù)VARIANCE()函數(shù)和STDDEV()函數(shù)分別用于計算特定的表達(dá)式中的所有值的方差和標(biāo)準(zhǔn)差。語法格式如下：VARIANCE/STDDEV([ALL|DISTINCT]表達(dá)式)【例3.12】求選修101課程的成績的方差。SELECTVARIANCE(成績) FROMcj WHERE課程號='101';執(zhí)行結(jié)果如圖?！纠?.13】求選修101課程的成績的標(biāo)準(zhǔn)差。SELECTSTDDEV(成績) FROMcj WHERE課程號='101';執(zhí)行結(jié)果如圖。選擇輸出列（5）GROUP_CONCAT()函數(shù)MySQL支持一個特殊的聚合函數(shù)GROUP_CONCAT()。該函數(shù)返回來自一個組中指定列的所有非NULL值，這些值一個接著一個放置，中間用逗號隔開，并表示為一個長長的字符串。這個字符串的長度是有限制的，標(biāo)準(zhǔn)值是1024。語法格式如下：GROUP_CONCAT({[ALL|DISTINCT]表達(dá)式}|*)【例3.14】查詢選修了206課程的學(xué)生的學(xué)號。SELECTGROUP_CONCAT(學(xué)號) FROMcj WHERE課程號='206';執(zhí)行結(jié)果如圖。選擇輸出列（6）BIT_AND()函數(shù)、BIT_OR()函數(shù)和BIT_XOR()函數(shù)。存在與二進(jìn)制運算符&（與）、|（或）和^（異或）相對應(yīng)的聚合函數(shù)，分別是BIT_AND、BIT_OR、BIT_XOR。語法格式如下：BIT_AND|BIT_OR|BIT_XOR({[ALL|DISTINCT]表達(dá)式}|*)【例3.15】有一個表bits，其中有一列bin_value上有3個integer值，即1、3、7，獲取在該列上執(zhí)行BIT_OR的結(jié)果。SELECTBIN(BIT_OR(bin_value)) FROMbits;02數(shù)據(jù)來源1.引用一個表2.多表連接數(shù)據(jù)來源1．引用一個表用戶可以用如下兩種方式引用表。第一種方式是使用USE語句讓一個數(shù)據(jù)庫成為當(dāng)前數(shù)據(jù)庫，F(xiàn)ROM子句中指定表名應(yīng)該屬于當(dāng)前數(shù)據(jù)庫。第二種方式是在指定表名前帶上表所屬數(shù)據(jù)庫的名字。例如，假設(shè)當(dāng)前數(shù)據(jù)庫是db1，現(xiàn)在要顯示數(shù)據(jù)庫db2里的表tb的內(nèi)容，使用如下語句：SELECT*FROMdb2.tb;數(shù)據(jù)來源2．多表連接（1）全連接。將各個表用逗號分隔，就指定了一個全連接。使用FROM子句產(chǎn)生的中間結(jié)果是一個新表，新表是每個表的每行都與其他表中的每行交叉以產(chǎn)生的所有可能組合。這種連接方式會潛在地產(chǎn)生數(shù)量非常多的行，因為可能得到的行數(shù)為每個表行數(shù)之積！使用WHERE子句設(shè)定條件將結(jié)果集減小為易于管理的大小，這樣的連接即等值連接（連接條件中兩表對應(yīng)）?！纠?.16】查找所有學(xué)生選過的課程名和課程號。使用如下語句：SELECTDISTINCTkc.課程名,cj.課程號FROMkc,cjWHEREkc.課程號=cj.課程號;執(zhí)行結(jié)果如圖。數(shù)據(jù)來源（2）JOIN連接。語法格式如下：JOIN表ON連接條件使用JOIN關(guān)鍵字的連接主要分為如下3種。①內(nèi)連接。指定了INNER關(guān)鍵字的連接是內(nèi)連接?！纠?.17】查找所有學(xué)生選過的課程名和課程號?？梢允褂靡韵抡Z句：SELECTDISTINCT課程名,cj.課程號 FROMkcINNER JOINcjON(kc.課程號=cj.課程號);它的功能是合并兩個表，返回滿足條件的行。內(nèi)連接是系統(tǒng)默認(rèn)的，可以省略INNER關(guān)鍵字。數(shù)據(jù)來源【例3.18】查找選修了206課程且成績?yōu)?0分及以上的學(xué)生的姓名及成績。SELECT姓名,成績 FROMxsJOINcjONxs.學(xué)號=cj.學(xué)號 WHERE課程號='206'AND成績>=80;執(zhí)行結(jié)果如圖。數(shù)據(jù)來源【例3.19】查找選修了“計算機導(dǎo)論”課程且成績?yōu)?0分及以上學(xué)生的學(xué)號、姓名、課程名及成績。SELECTxs.學(xué)號,姓名,課程名,成績 FROMxsJOINcjONxs.學(xué)號=cj.學(xué)號 JOINkcONcj.課程號=kc.課程號 WHERE課程名='計算機導(dǎo)論'AND成績>=80;執(zhí)行結(jié)果如圖。數(shù)據(jù)來源【例3.20】查找課程不同、成績相同的學(xué)生的學(xué)號、課程號和成績。SELECTa.學(xué)號,a.課程號,b.課程號,a.成績 FROMcjASa JOINcjASbONa.成績=b.成績ANDa.學(xué)號=b.學(xué)號ANDa.課程號!=b.課程號;執(zhí)行結(jié)果如圖。【例3.21】查找kc表中所有學(xué)生選過的課程名。SELECT課程名 FROMkcINNERJOINcjUSING(課程號);數(shù)據(jù)來源②外連接。指定了OUTER關(guān)鍵字的連接為外連接，其中的OUTER關(guān)鍵字均可省略。外連接包括以下。左外連接（LEFTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括左表有的但右表中不匹配的行，對于這樣的行，右表被選擇的列輸出為NULL。右外連接（RIGHTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括右表有的但左表中不匹配的行，對于這樣的行，從左表被選擇的列設(shè)置為NULL。自然連接（NATURALJOIN）：自然連接包括自然左外連接（NATURALLEFTOUTERJOIN）和自然右外連接（NATURALRIGHTOUTERJOIN）?！纠?.22】查找所有學(xué)生情況及他們選修的課程號，若學(xué)生未選修任何課，也要顯示其情況。SELECTxs.*,課程號 FROMxsLEFTOUTERJOINcjONxs.學(xué)號=cj.學(xué)號;數(shù)據(jù)來源【例3.23】查找被選修了的課程的選修情況和所有開設(shè)的課程名。SELECTcj.*,課程名 FROMcjRIGHTJOINkcONcj.課程號=kc.課程號;結(jié)果顯示如圖。【例3.24】使用自然連接查詢所有學(xué)生選過的課程名和課程號。SELECT課程名,課程號FROMkc WHERE課程號IN (SELECTDISTINCT課程號FROMkcNATURALRIGHTOUTERJOINcj);數(shù)據(jù)來源③交叉連接。指定了CROSSJOIN關(guān)鍵字的連接是交叉連接。在不包含連接條件時，交叉連接結(jié)果表是由第一個表的每一行與第二個表的每一行拼接后形成的表，因此結(jié)果表的行數(shù)等于兩個表行數(shù)之積。在MySQL中，CROSSJOIN語法上與INNERJOIN等同，兩者可以互換。【例3.25】列出學(xué)生所有可能的選課情況。SELECT學(xué)號,姓名,課程號,課程名 FROMxsCROSSJOINkc;另外，STRAIGHT_JOIN連接用法和INNERJOIN連接基本相同。不同的是，STRAIGHT_JOIN后不可以使用USING子句替代ON條件。【例3.26】使用STRAIGHT_JOIN連接查找所有學(xué)生選過的課程名和課程號。SELECTDISTINCT課程名,cj.課程號 FROMkcSTRAIGHT_JOINcjON(kc.課程號=cj.課程號);03查詢條件1.比較運算2.模式匹配3.范圍比較4.空值比較5.子查詢查詢條件WHERE子句的基本格式為：WHERE條件條件格式如下：表達(dá)式<比較運算符>表達(dá)式 /*比較運算*/|邏輯表達(dá)式<邏輯運算符>邏輯表達(dá)式|表達(dá)式[NOT]LIKE表達(dá)式[ESCAPE'ESC字符’] /*LIKE運算符*/|表達(dá)式[NOT][REGEXP|RLIKE]表達(dá)式 /*REGEXP運算符*/|表達(dá)式[NOT]BETWEEN表達(dá)式AND表達(dá)式 /*指定范圍*/|表達(dá)式IS[NOT]NULL /*判斷是否為空值*/|表達(dá)式[NOT]IN(子查詢|表達(dá)式[,…n]) /*IN子句*/|表達(dá)式<比較運算符>{ALL|SOME|ANY}(子查詢) /*比較子查詢*/|EXIST(子查詢) /*EXIST子查詢*/使用WHERE子句會根據(jù)條件對FROM子句一行一行地進(jìn)行判斷，當(dāng)條件為TRUE的時候，這一行就被包含到WHERE子句的中間結(jié)果中。查詢條件1．比較運算比較運算用于比較兩個表達(dá)式值，當(dāng)兩個表達(dá)式值均不為空值（NULL）時，比較運算返回邏輯值TRUE（真）或FALSE（假）；而當(dāng)兩個表達(dá)式值中有一個為空值或都為空值時，將返回UNKNOWN。MySQL支持的比較運算符有=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）等。【例3.27】查詢xs表中學(xué)號為221101的學(xué)生的情況。SELECT姓名,學(xué)號,總學(xué)分 FROMxs WHERE學(xué)號='221101';執(zhí)行結(jié)果如圖。查詢條件【例3.28】查詢xs表中總學(xué)分大于50分的學(xué)生的情況。SELECT姓名,學(xué)號,出生日期,總學(xué)分 FROMxs WHERE總學(xué)分>50;執(zhí)行結(jié)果如圖。MySQL支持一個特殊的等于運算符“<=>”，當(dāng)兩個表達(dá)式值彼此相等或都等于空值時，它的返回值為TRUE，其中有一個空值或都是非空值但不相等時，返回值就是FALSE。其中沒有UNKNOWN的情況。查詢條件【例3.29】查詢xs表中備注為空的學(xué)生的情況。SELECT姓名,學(xué)號,出生日期,總學(xué)分 FROMxs WHERE備注<=>NULL;可以通過邏輯運算符（AND、OR、XOR和NOT）組成更為復(fù)雜的查詢條件。查詢xs表中專業(yè)為計算機、性別為女（0）的學(xué)生的情況。SELECT姓名,學(xué)號,性別,總學(xué)分 FROMxs WHERE專業(yè)名='計算機'AND性別=0;執(zhí)行結(jié)果如圖。查詢條件2．模式匹配（1）LIKE運算符。LIKE運算符用于指出一個字符串是否與指定的字符串相匹配，其運算對象可以是char、varchar、text、datetime等類型的數(shù)據(jù)，返回邏輯值TRUE或FALSE。語法格式如下：表達(dá)式[NOT]LIKE表達(dá)式[ESCAPE'esc字符']使用LIKE進(jìn)行模式匹配時，常使用特殊符號_和%，可進(jìn)行模糊查詢。“%”代表0個或多個字符，“_”代表單個字符。由于MySQL默認(rèn)不區(qū)分大小寫，要區(qū)分大小寫時需要更換字符集的校對規(guī)則?！纠?.30】查詢xs表中姓“王”學(xué)生的學(xué)號、姓名及性別。SELECT學(xué)號,姓名,性別 FROMxs WHERE姓名LIKE'王%';執(zhí)行結(jié)果如圖。查詢條件【例3.31】查詢xs表中學(xué)號倒數(shù)第二個數(shù)字為0的學(xué)生的學(xué)號、姓名及專業(yè)名。SELECT學(xué)號,姓名,專業(yè)名 FROMxs WHERE學(xué)號LIKE'%0_';執(zhí)行結(jié)果如圖。【例3.32】查詢xs表中名字包含下畫線學(xué)生的學(xué)號和姓名。SELECT學(xué)號,姓名 FROMxs WHERE學(xué)號LIKE'%#_%'ESCAPE'#';查詢條件（2）REGEXP運算符。REGEXP運算符用來執(zhí)行更復(fù)雜的字符串比較運算。REGEXP是正規(guī)表達(dá)式的縮寫，但它不是SQL標(biāo)準(zhǔn)的一部分。REGEXP運算符的一個同義詞是RLIKE。語法格式如下：表達(dá)式[NOT][REGEXP|RLIKE]表達(dá)式屬于REGEXP運算符的特殊字符如表。特殊字符含

義特殊字符含

義^匹配字符串的開始部分[abc]匹配方括號里出現(xiàn)的字符串a(chǎn)bc$匹配字符串的結(jié)束部分[a-z]匹配方括號里出現(xiàn)的a～z之間的1個字符.匹配任何一個字符（包括回車和新行）[^a-z]匹配方括號里出現(xiàn)的不在a～z之間的1個字符*匹配星號之前的0個或多個字符的任何序列|匹配符號左邊或右邊出現(xiàn)的字符串+匹配加號之前的1個或多個字符的任何序列[[..]]匹配方括號里出現(xiàn)的符號（如空格、換行、括號、句號、冒號、加號、連字符等）?匹配問號之前0個或多個字符[[:<:]和[[:>:]]匹配一個單詞的開始和結(jié)束{n}匹配括號前的內(nèi)容出現(xiàn)n次的序列[[::]匹配方括號里出現(xiàn)的字符中的任意一個字符()匹配括號里的內(nèi)容

查詢條件【例3.33】查詢姓李的學(xué)生的學(xué)號、姓名和專業(yè)名。SELECT學(xué)號,姓名,專業(yè)名 FROMxs WHERE姓名REGEXP'^李';執(zhí)行結(jié)果如圖?！纠?.34】查詢學(xué)號里包含4、5、6學(xué)生的學(xué)號、姓名和專業(yè)名。SELECT學(xué)號,姓名,專業(yè)名 FROMxs WHERE學(xué)號REGEXP'[4,5,6]';執(zhí)行結(jié)果如圖。查詢條件【例3.35】查詢學(xué)號以21開頭、以02結(jié)尾的學(xué)生學(xué)號、姓名和專業(yè)名。SELECT學(xué)號,姓名,專業(yè)名 FROMxs WHERE學(xué)號REGEXP'^21.*02$';執(zhí)行結(jié)果如圖。查詢條件3．范圍比較用于范圍比較的關(guān)鍵字有兩個：BETWEEN和IN。當(dāng)要查詢的條件是某個值的范圍時，可以使用BETWEEN關(guān)鍵字指出查詢范圍。語法格式如下：表達(dá)式[NOT]BETWEEN表達(dá)式1AND表達(dá)式2當(dāng)不使用NOT時，若表達(dá)式的值在表達(dá)式1的值與表達(dá)式2的值之間（包括這兩個值），則返回TRUE，否則返回FALSE；使用NOT時，返回值剛好相反。使用IN關(guān)鍵字可以指定一個值表，值表中列出所有可能的值，當(dāng)表達(dá)式的值與值表中的任一個值匹配時，即返回TRUE，否則返回FALSE。使用IN關(guān)鍵字指定值表的格式為：表達(dá)式IN(表達(dá)式[,…,n])查詢條件【例3.36】查詢xs表中不在2003年出生學(xué)生的情況。SELECT學(xué)號,姓名,專業(yè)名,出生日期 FROMxs WHERE出生日期NOTBETWEEN'2003-1-1'AND'2003-12-31';執(zhí)行結(jié)果如圖。查詢條件【例3.37】查詢xs表中專業(yè)名為“計算機”“通信工程”或“無線電”的學(xué)生的情況。SELECT* FROMxs WHERE專業(yè)名IN('計算機','通信工程','無線電');該語句與下句等價：SELECT* FROMxs WHERE專業(yè)名='計算機'OR專業(yè)名='通信工程'OR專業(yè)名='無線電';查詢條件4．空值比較當(dāng)需要判定一個表達(dá)式的值是否為空值時，使用ISNULL關(guān)鍵字。語法格式如下：表達(dá)式IS[NOT]NULL當(dāng)不使用NOT時，若表達(dá)式的值為空值，返回TRUE，否則返回FALSE；當(dāng)使用NOT時，結(jié)果剛好相反。【例3.38】查詢總學(xué)分尚不定學(xué)生的情況。SELECT* FROMxs WHERE總學(xué)分ISNULL;查詢條件5．子查詢（1）IN子查詢。IN子查詢用于進(jìn)行給定值是否在子查詢結(jié)果集中的判斷。語法格式如下：表達(dá)式[NOT]IN(子查詢)當(dāng)表達(dá)式的值與子查詢結(jié)果表中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。【例3.39】查找選修了課程號為206課程的學(xué)生的姓名、學(xué)號。SELECT姓名,學(xué)號 FROMxs WHERE學(xué)號IN (SELECT學(xué)號 fromcj WHERE課程號='206’ );執(zhí)行結(jié)果如圖。查詢條件【例3.40】查找未選修離散數(shù)學(xué)的學(xué)生的姓名、學(xué)號、專業(yè)名。SELECT姓名,學(xué)號,專業(yè)名 FROMxs WHERE學(xué)號NOTIN ( SELECT學(xué)號 FROMcj WHERE課程號IN (SELECT課程號 FROMkc WHERE課程名='離散數(shù)學(xué)’ ) );執(zhí)行結(jié)果如圖。查詢條件（2）比較子查詢。這種子查詢可以被認(rèn)為是IN子查詢的擴展，它使表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較運算。語法格式如下：表達(dá)式{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(子查詢)其中：ALL用于指定表達(dá)式的值要與子查詢結(jié)果集中的每個值都進(jìn)行比較，當(dāng)表達(dá)式的值與查詢結(jié)果集中的每個值都滿足比較的關(guān)系時，才返回TRUE，否則返回FALSE；SOME或ANY是同義詞，表示表達(dá)式的值只要與子查詢結(jié)果集中的某個值滿足比較的關(guān)系時，就返回TRUE，否則返回FALSE；如果子查詢的結(jié)果集只返回一行數(shù)據(jù)，可以通過比較運算符直接比較。查詢條件【例3.41】查找選修了離散數(shù)學(xué)學(xué)生的學(xué)號。SELECT學(xué)號 FROMcj WHERE課程號= ( SELECT課程號 FROMkc WHERE課程名='離散數(shù)學(xué)’ );執(zhí)行結(jié)果如圖。查詢條件【例3.42】查找xs表中比所有通信工程專業(yè)學(xué)生年齡都小的學(xué)生的學(xué)號、姓名、專業(yè)名、出生日期。SELECT學(xué)號,姓名,專業(yè)名,出生日期 FROMxs WHERE出生日期>ALL ( SELECT出生日期 FROMxs WHERE專業(yè)名='通信工程’ );執(zhí)行結(jié)果如圖。查詢條件【例3.43】查找cj表中課程號為206課程的成績不低于課程號為101課程的最低成績的學(xué)生的學(xué)號。SELECT學(xué)號 FROMcj WHERE課程號='206'AND成績>=ANY ( SELECT成績 FROMcj WHERE課程號='101’ );執(zhí)行結(jié)果如圖。查詢條件（3）EXISTS子查詢。EXISTS謂詞用于測試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則EXISTS返回TRUE，否則返回FALSE。EXISTS還可與NOT結(jié)合使用，即NOTEXISTS，其返回值與EXIST的剛好相反。語法格式如下：[NOT]EXISTS(子查詢)【例3.44】查找選修課程號為206學(xué)生的姓名。SELECT姓名 FROMxs WHEREEXISTS ( SELECT* FROMcj WHERE學(xué)號=xs.學(xué)號AND課程號='206’ );執(zhí)行結(jié)果如圖。查詢條件這類子查詢稱為相關(guān)子查詢，因為子查詢的條件依賴于外層查詢中的某些值。其處理過程：首先找外層SELECT中xs表的第一行，根據(jù)該行的學(xué)號列值處理內(nèi)層SELECT，若結(jié)果不為空，則WHERE條件就為真，就把該行的姓名值取出作為結(jié)果集的一行；然后找xs表的第2、3等行，重復(fù)上述處理過程直到xs表的所有行都查找完為止?！纠?.45】查找選修了全部課程學(xué)生的姓名。SELECT姓名 FROMxs WHERENOTEXISTS ( SELECT* FROMkc WHERENOTEXISTS ( SELECT* FROMcj WHERE學(xué)號=xs.學(xué)號AND課程號=kc.課程號 ) );查詢條件MySQL區(qū)分了4種類型的子查詢：①返回一個表的子查詢是表子查詢；②返回帶有一個或多個值的一行的子查詢是行子查詢；③返回一行或多行，但每行上只有一個值的是列子查詢；④只返回一個值的是標(biāo)量子查詢，從定義上講，每個標(biāo)量子查詢都是一個列子查詢和行子查詢。上面介紹的子查詢都屬于列子查詢。另外，子查詢還可以用在SELECT語句的其他子句中。子查詢可以用在FROM子句中，但必須為子查詢產(chǎn)生的中間表定義一個別名。【例3.46】從xs表中查找總學(xué)分大于50分的男學(xué)生的姓名和學(xué)號。SELECT姓名,學(xué)號,總學(xué)分 FROM(SELECT姓名,學(xué)號,性別,總學(xué)分 FROMxs WHERE總學(xué)分>50 )ASstudent WHERE性別='1';執(zhí)行結(jié)果如圖。查詢條件【例3.47】從xs表中查找所有女學(xué)生的姓名、學(xué)號，以及與221101號學(xué)生的年齡差距。SELECT學(xué)號,姓名,YEAR(出生日期)- YEAR((SELECT出生日期 FROMxs WHERE學(xué)號='221101’ ))AS年齡差距 FROMxs WHERE性別='0';執(zhí)行結(jié)果如圖。查詢條件【例3.48】查找與221101號學(xué)生性別相同、總學(xué)分相同學(xué)生的學(xué)號和姓名。SELECT學(xué)號,姓名 FROMxs WHERE(性別,總學(xué)分)=(SELECT性別,總學(xué)分 FROMxs WHERE學(xué)號='221101’ );執(zhí)行結(jié)果如圖。04分

組分

組語法格式如下：GROUPBY{列名|表達(dá)式|列順序}[ASC|DESC],...[WITHROLLUP]【例3.49】查詢各專業(yè)名及對應(yīng)的學(xué)生數(shù)。SELECT專業(yè)名,COUNT(*)AS'學(xué)生數(shù)’ FROMxs GROUPBY專業(yè)名;執(zhí)行結(jié)果如圖。分

組【例3.50】求被選修的各門課程的平均成績和選修該課程學(xué)生的人數(shù)。SELECT課程號,AVG(成績)AS'平均成績',COUNT(學(xué)號)AS'選修人數(shù)’ FROMcj GROUPBY課程號;執(zhí)行結(jié)果如圖。分

組【例3.51】查詢每個專業(yè)的男生人數(shù)、女生人數(shù)、總?cè)藬?shù)，以及學(xué)生總?cè)藬?shù)。SELECT專業(yè)名,性別,COUNT(*)AS'人數(shù)’ FROMxs GROUPBY專業(yè)名,性別 WITHROLLUP;執(zhí)行結(jié)果如圖。將上述語句與不帶ROLLUP操作符的GROUPBY子句的執(zhí)行情況進(jìn)行比較：SELECT專業(yè)名,性別,COUNT(*)AS'人數(shù)’ FROMxs GROUPBY專業(yè)名,性別;執(zhí)行結(jié)果如圖。分

組【例3.52】在xscj數(shù)據(jù)庫中產(chǎn)生一個結(jié)果集，包括每門課程各專業(yè)的平均成績、每門課程的總平均成績和所有課程的總平均成績。SELECT課程名,專業(yè)名,AVG(成績)AS'平均成績’ FROMcj,kc,xs WHEREcj.課程號=kc.課程號ANDcj.學(xué)號=xs.學(xué)號 GROUPBY課程名,專業(yè)名 WITHROLLUP;執(zhí)行結(jié)果如圖。05分組條件分組條件HAVING子句的語法格式如下：HAVING條件其中，條件的定義和WHERE子句中條件的類似，不過HAVING子句中的條件可以包含聚合函數(shù)，而WHERE子句中的則不可以。SQL標(biāo)準(zhǔn)要求HAVING子句必須引用GROUPBY子句中的列或用于聚合函數(shù)中的列。MySQL允許HAVING子句引用SELECT清單中的列和外部子查詢中的列?！纠?.53】查找平均成績?yōu)?5分及以上的學(xué)生的學(xué)號和平均成績。SELECT學(xué)號,AVG(成績)AS'平均成績’ FROMcj GROUPBY學(xué)號 HAVINGAVG(成績)>=85;執(zhí)行結(jié)果如圖。分組條件【例3.54】查找選修課程超過2門且成績都為80分及以上的學(xué)生的學(xué)號。SELECT學(xué)號 FROMcj WHERE成績>=80 GROUPBY學(xué)號 HAVINGCOUNT(*)>2;執(zhí)行結(jié)果如圖。分組條件【例3.55】查找通信工程專業(yè)平均成績?yōu)?5分及以上的學(xué)生的學(xué)號和平均成績。SELECT學(xué)號,AVG(成績)AS'平均成績’ FROMcj WHERE學(xué)號IN (SELECT學(xué)號 FROMxs WHERE專業(yè)名='通信工程’ ) GROUPBY學(xué)號 HAVINGAVG(成績)>=85;執(zhí)行結(jié)果如圖。06排

序排

序ORDERBY子句的語法格式如下：ORDERBY{列名|表達(dá)式|順序號}[ASC|DESC],...【例3.56】將通信工程專業(yè)的學(xué)生按出生日期先后排序。SELECT學(xué)號,姓名,專業(yè)名,出生日期 FROMxs WHERE專業(yè)名='通信工程' ORDERBY出生日期;執(zhí)行結(jié)果如圖。排

序【例3.57】將計算機專業(yè)學(xué)生的“計算機導(dǎo)論”課程成績按降序排列。SELECT姓名,課程名,成績 FROMxs,kc,cj WHERExs.學(xué)號=cj.學(xué)號 ANDcj.課程號=kc.課程號 AND課程名='計算機導(dǎo)論’ AND專業(yè)名='計算機’ ORDERBY成績DESC;執(zhí)行結(jié)果如圖。排

序【例3.58】將計算機專業(yè)學(xué)生按其平均成績排列。SELECT學(xué)號,姓名,專業(yè)名 FROMxs WHERE專業(yè)名='計算機’ ORDERBY(SELECTAVG(成績) FROMcj GROUPBYcj.學(xué)號 HAVINGxs.學(xué)號=cj.學(xué)號 );執(zhí)行結(jié)果如圖。07輸出行限制輸出行限制LIMIT子句主要用于限制被SELECT語句返回的行數(shù)。語法格式如下：LIMIT{[偏移量,]行數(shù)}【例3.59】查找xs表中學(xué)號靠前的5位學(xué)生的信息。SELECT學(xué)號,姓名,專業(yè)名,性別,出生日期,總學(xué)分 FROMxs ORDERBY學(xué)號 LIMIT5;執(zhí)行結(jié)果如圖。輸出行限制【例3.60】查找xs表中從第2位學(xué)生開始的3位學(xué)生的信息。SELECT學(xué)號,姓名,專業(yè)名,性別,出生日期,總學(xué)分 FROMxs ORDERBY學(xué)號 LIMIT1,3;執(zhí)行結(jié)果如圖。08聯(lián)合查詢聯(lián)合查詢用戶使用UNION語句，可以把來自許多SELECT語句的結(jié)果組合到一個結(jié)果集合中。語法格式如下：SELECT...UNION[ALL|DISTINCT]SELECT...[UNION[ALL|DISTINCT]SELECT...]使用UNION的時候，在第一個SELECT語句中被使用的列名稱將被用于結(jié)果的列名稱?！纠?.61】查找學(xué)號為221101和學(xué)號為211201的兩位學(xué)生的信息。SELECT學(xué)號,姓名,專業(yè)名,性別,出生日期,總學(xué)分 FROMxs WHERE學(xué)號='221101’ UNION SELECT學(xué)號,姓名,專業(yè)名,性別,出生日期,總學(xué)分 FROMxs WHERE學(xué)號='211201';執(zhí)行結(jié)果如圖。09行瀏覽查詢1．打開一個表2．瀏覽表中的行3．關(guān)閉打開的表行瀏覽查詢1．打開一個表用戶可以使用HANDLEROPEN語句打開一個表。語法格式如下：HANDLER表名OPEN[[AS]alias]2．瀏覽表中的行HANDLERREAD語句用于瀏覽一個已打開的表的數(shù)據(jù)行。語法格式如下：HANDLER表名READ{FIRST|NEXT} [WHERE條件][LIMIT...]由于沒有其他的聲明，在讀取一行數(shù)據(jù)的時候，行的順序是由MySQL決定的。如果要按某個順序來顯示，可以通過在HANDLERREAD語句中指定索引來實現(xiàn)。語法格式如下：HANDLER表名READ索引名{=|<=|>=|<|>}(值...) [WHERE條件][LIMIT...]HANDLER表名READ索引名{FIRST|NEXT|PREV|LAST} [WHERE條件][LIMIT...]行瀏覽查詢3．關(guān)閉打開的表讀取完行后必須使用HANDLERCLOSE語句來關(guān)閉表。語法格式如下：HANDLER表名CLOSE【例3.62】一行一行地瀏覽kc表中滿足條件的內(nèi)容，要求讀取學(xué)分大于4的第一行數(shù)據(jù)。首先打開表：USExscjHANDLERkcOPEN;讀取滿足條件的第一行：HANDLERkcREADFIRSTWHERE學(xué)分>4;執(zhí)行結(jié)果如圖。行瀏覽查詢讀取下一行：HANDLERkcREADNEXT;執(zhí)行結(jié)果如圖。關(guān)閉該表：HANDLERkcCLOSE;第3章MySQL查詢和視圖——MySQL視圖01視圖的概念視圖的概念使用視圖有下列優(yōu)點。（1）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時用戶所需要的數(shù)據(jù)分散在多個表中，定義視圖可將它們集中在一起，從而方便用戶的數(shù)據(jù)查詢和處理。（2）（3）（4）（5）屏蔽數(shù)據(jù)庫的復(fù)雜性。用戶不必了解復(fù)雜的數(shù)據(jù)庫中的表結(jié)構(gòu)，并且數(shù)據(jù)庫中表的更改也不影響用戶對數(shù)據(jù)庫的使用。簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次?？梢灾匦陆M織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。02創(chuàng)建視圖創(chuàng)建視圖視圖在數(shù)據(jù)庫中是作為一個對象來存儲的。用戶創(chuàng)建視圖前，要保證自己已被數(shù)據(jù)庫所有者授權(quán)可以使用CREATEVIEW語句，并且有權(quán)操作視圖所涉及的表或其他視圖，其語法格式如下：CREATE[ORREPLACE]VIEW視圖名[(列名...)]ASselect語句對SELECT語句有以下的限制：（1）定義視圖的用戶必須對所參照的表或視圖有查詢（即可執(zhí)行SELECT語句）權(quán)限；（2）不能包含F(xiàn)ROM子句中的子查詢；（3）在定義中引用的表或視圖必須存在；（4）若引用不是當(dāng)前數(shù)據(jù)庫的表或視圖時，要在表或視圖前加上數(shù)據(jù)庫的名稱；（5）在視圖定義中允許使用ORDERBY，但是，如果從特定視圖進(jìn)行了選擇，而該視圖使用了具有自己ORDERBY的語句，則視圖定義中的ORDERBY將被忽略；（6）對于SELECT語句中的其他選項或子句，若視圖中也包含了這些選項，則效果未定義。創(chuàng)建視圖【例3.63】假設(shè)當(dāng)前數(shù)據(jù)庫是mytest，創(chuàng)建xscj數(shù)據(jù)庫中的cs_kc視圖，包括計算機專業(yè)各學(xué)生的學(xué)號、選修的課程號及成績。要保證對該視圖的修改都符合專業(yè)名為“計算機”這個條件。CREATEORREPLACEVIEWxscj.cs_kc AS SELECTxs.學(xué)號,課程號,成績 FROMxscj.xs,xscj.cj WHERExs.學(xué)號=cj.學(xué)號ANDxs.專業(yè)名='計算機’ WITHCHECKOPTION;【例3.64】創(chuàng)建xscj數(shù)據(jù)庫中的計算機專業(yè)學(xué)生的平均成績視圖cs_kc_avg，包括學(xué)號（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）。USExscjCREATEVIEWcs_kc_avg(num,score_avg) AS SELECT學(xué)號,AVG(成績) FROMcs_kc GROUPBY學(xué)號;03查詢視圖查詢視圖【例3.65】在視圖cs_kc中查找計算機專業(yè)學(xué)生的學(xué)號和選修的課程號。SELECT學(xué)號,課程號 FROMcs_kc;【例3.66】查找平均成績?yōu)?0分及以上的學(xué)生的學(xué)號和平均成績。本例首先創(chuàng)建學(xué)生平均成績視圖xs_kc_avg，包括學(xué)號（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）。創(chuàng)建學(xué)生平均成績視圖xs_kc_avg：CREATEVIEWxs_kc_avg(num,score_avg) AS SELECT學(xué)號,AVG(成績) FROMcj GROUPBY學(xué)號;再對xs_kc_avg視圖進(jìn)行查詢。SELECT* FROMxs_kc_avg WHEREscore_avg>=80;執(zhí)行結(jié)果如圖。0