第3章 MySQL查詢和視圖

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

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

數

名說

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

義特殊字符含

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

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

組分

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

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

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

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

序排

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

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

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