MySQL教程（第4版） 課件 第4章 查詢和視圖

第4章

查詢和視圖——關(guān)系運算基礎關(guān)系運算基礎關(guān)系運算的特點：運算的對象和結(jié)果都是表。例如，學生表、課程表和成績表所示。學號姓名專業(yè)名性別出生日期總學分備注221101王林計算機12004-02-1015

221102程明計算機12005-02-0115

221103王燕計算機02003-10-0615參加校女子足球隊221201劉華通信工程12004-06-1013輔修計算機專業(yè)221202王林通信工程12004-01-2913

221204馬琳琳通信工程02003-02-1015

學生表關(guān)系運算基礎課程表課程號課程名開課學期學時學分101計算機導論1805102程序設計與語言2684206離散數(shù)學4684208數(shù)據(jù)結(jié)構(gòu)5684209操作系統(tǒng)6684210計算機原理5855212數(shù)據(jù)庫原理7684301計算機網(wǎng)絡7513302軟件工程7513成績表學號課程號成績2211011018022110110278221101206762211021027822110220652221103101622211031025022110320681221201101802212021015501選擇（Selection）運算選擇（Selection）運算選擇運算的記號為

F(R)。其中，

是選擇運算符，下標F是一個條件表達式，R是被操作的表?！纠咳粢趯W生表中找出性別為女的行形成一個新表，則運算式為

F(學生)。其中，F(xiàn)：性別=“女”。該選擇運算的結(jié)果如表。學號姓名專業(yè)名性別出生日期總學分備注221103王燕計算機02003-10-0615參加校女子足球隊221204馬琳琳通信工程02003-02-1015

02投影（Projection）運算投影（Projection）運算投影運算也是單目運算，該運算從表中選出指定的屬性值組成一個新表，記為

A(R)。其中，A是屬性名（列名）表，R是表名。【例】若在學生表中對學號、姓名和總學分投影，運算式如下：

學號，姓名，總學分(學生)該運算得到如表所示的新表。學號姓名總學分221101王林15221102程明15221103王燕15221201劉華13221202王林13221204馬琳琳1503連接（Join）運算等值連接自然連接連接（Join）運算1．等值連接兩個表連接的常用條件是兩個表的某些列值相等，這樣的連接稱為等值連接，記為R?FS。其中，R、S是被操作的表，F(xiàn)是條件。【例】若表R和表S分別為成績表和學生表（

學號，姓名，總學分(學生情況)結(jié)果），則R?FS如表。其中，F(xiàn)為“成績表.學號=學生表.學號”。成績表.學號課程號成績學生表.學號姓名總學分22110110180221101王林1522110110278221101王林1522110120676221101王林1522110210278221102程明1522110220652221102程明1522110310162221103王燕1522110310250221103王燕1522110320681221103王燕1522120110180221201劉華1322120210155221202王林13連接（Join）運算2．自然連接自然連接運算記為R?S。其中，R和S是參與運算的兩個表。【例】若R表和S表分別為成績表和課程表所示，則R?S如表。學號課程號成績課程名開課學期學時學分22110110180計算機導論180522110110278程序設計與語言268422110120676離散數(shù)學468422110210278程序設計與語言268422110220652離散數(shù)學468422110310162計算機導論180522110310250程序設計與語言268422110320681離散數(shù)學468422120110180計算機導論180522120210155計算機導論1805第4章

查詢和視圖——數(shù)據(jù)庫表查詢數(shù)據(jù)庫表查詢SELECT語句格式如下：SELECT [ALL|DISTINCT|DISTINCTROW]表達式,...] [FROM源表,...] /*FROM子句*/ [WHERE條件] /*WHERE子句*/ [GROUPBY{列名|表達式|輸出項號}[ASC|DESC],...[WITHROLLUP]] /*GROUPBY子句*/ [HAVING條件] /*HAVING子句*/ [ORDERBY{列名|表達式|輸出項號}[ASC|DESC],...] /*ORDERBY子句*/ [LIMIT[偏移,]行數(shù) /*LIMIT子句*/ [UNION[ALL|DISTINCT]SELECT語句] /*UNION子句*/ [INTO變量名,...] [INTOOUTFILE'文件名'[CHARACTERSET字符集名]數(shù)據(jù)庫表查詢說明：（1）最簡單的SELECT語句是“SELECT表達式”，顯示表達式的值。表達式可以是常量、列、函數(shù)以及由運算符連接起來的式子。例如，如果變量@x值為2，下列語句顯示3：SELECT@x+1;（2）ALL|DISTINCT|DISTINCTROW：ALL（默認）返回所有的匹配行；DISTINCT和DISTINCTROW是同義詞，用于消除結(jié)果集中的重復行。（3）INTOOUTFILE'文件名'：將查詢結(jié)果導出到指定文件中，這個文件被創(chuàng)建在服務器主機中。（4）所有被使用的子句必須嚴格按順序編寫。例如，一個HAVING子句必須位于GROUPBY子句之后，并位于ORDERBY子句之前。（5）格式?jīng)]有包含MySQL相對于標準SQL的擴展選項（以SQL_為開頭），一般情況下不用。01選擇輸出項選擇指定的列替換查詢結(jié)果中的數(shù)據(jù)計算列值消除結(jié)果集中的重復行聚合函數(shù)選擇輸出項1．選擇指定的列SELECT*|列名[AS]別名|表達式[AS]別名，...說明：（1）選擇表中的部分列，各列名之間要以逗號分隔，“*”表示所有列。（2）用AS子句可更改查詢結(jié)果的列標題，當自定義的列標題中含有空格時，必須使用引號將標題括起來。不允許在WHERE子句中使用列別名。【例】按專業(yè)查詢學生表中各個學生的學號、姓名和總學分。USExscj;SELECT*FROMxsWHERE專業(yè)='計算機'; SELECT學號ASID,姓名ASNAME,總學分AS'Totalcredit' FROMxs WHERE專業(yè)='通信工程'; 選擇輸出項2．替換查詢結(jié)果中的數(shù)據(jù)語句如下：CASE WHEN條件1THEN表達式1 WHEN條件2THEN表達式2 …… ELSE表達式nEND其中：將符合條件i的值用表達式i值替換?！纠堪殉煽儽戆俜种瞥煽兎殖傻燃?。USExscj;SELECT學號,成績, CASE WHEN成績>=90THEN'優(yōu)秀' WHEN成績>=80AND成績<90THEN'良好' WHEN成績>=70AND成績<80THEN'中等' WHEN成績>=60AND成績<70THEN'及格' ELSE'不及格' ENDAS等級 FROMcj WHERE課程號='206';選擇輸出項查詢結(jié)果如圖。選擇輸出項3．計算列值在結(jié)果中可以輸出對包含列在內(nèi)的表達式計算后的值，即可使用表達式作為輸出項?！纠坎樵?022年入學學生的年齡。SELECT學號,姓名,出生日期,year(now())-year(出生日期)+1AS年齡 FROMxs WHEREleft(學號,2)='22';查詢結(jié)果如圖。選擇輸出項4．消除結(jié)果集中的重復行對表查詢輸出部分列時，可能會出現(xiàn)重復行。可以使用DISTINCT或DISTINCTROW關(guān)鍵字消除結(jié)果集中的重復行?！纠坎樵兂煽儽硪延姓n程號。SELECTDISTINCT課程號FROMcj;查詢結(jié)果如圖。選擇輸出項5．聚合函數(shù)聚合函數(shù)常用于對一組值進行計算，然后返回單個值。除COUNT()函數(shù)外，聚合函數(shù)都會忽略空值。聚合函數(shù)通常與GROUPBY子句一起使用。表列出了一些常用的聚合函數(shù)。函數(shù)名說明COUNT求組中項數(shù)，返回int類型整數(shù)MAX求最大值MIN求最小值SUM返回表達式中所有值的和AVG求組中值的平均值STD或STDDEV返回給定表達式中所有值的標準差VARIANCE返回給定表達式中所有值的方差GROUP_CONCAT返回由屬于一組的列值連接組合而成的結(jié)果BIT_AND邏輯或BIT_OR邏輯與BIT_XOR邏輯異或選擇輸出項說明：（1）COUNT()函數(shù)：用于統(tǒng)計組中滿足條件的行數(shù)或總行數(shù)，統(tǒng)計的是行中非NULL值的數(shù)目，格式如下。COUNT({[ALL|DISTINCT]表達式}|*)其中，表達式的數(shù)據(jù)類型是除BLOB或TEXT之外的任何類型；ALL（默認）表示對所有值進行統(tǒng)計，DISTINCT表示去除重復值。（2）MAX()函數(shù)和MIN()函數(shù)：分別用于求所有項表達式的最大值與最小值，忽略NULL值，格式如下。MAX/MIN([ALL|DISTINCT]表達式)其中，表達式的數(shù)據(jù)類型可以是數(shù)字、字符和時間日期類型。（3）SUM()函數(shù)和AVG()函數(shù)：分別用于求所有項表達式值的總和與平均值，格式如下。SUM/AVG([ALL|DISTINCT]表達式)其中，表達式的數(shù)據(jù)類型只能是數(shù)值型數(shù)據(jù)。選擇輸出項【例】求選修101號課程的學生的人數(shù)、最高分、最低分和平均成績。SELECTCOUNT(成績),MAX(成績),MIN(成績),AVG(成績) FROMcj WHERE課程號='101';查詢結(jié)果如圖。選擇輸出項（4）VARIANCE()、STDDEV()和STD()函數(shù)：分別用于求所有項表達式值的方差和標準差，格式如下。VARIANCE/STDDEV([ALL|DISTINCT]表達式)方差的計算步驟：①計算相關(guān)列的平均值；②求列中的每一個值和平均值的差；③計算差值的平方的總和；④用總和除以（列中的）值的個數(shù)得到結(jié)果。STDDEV()或STD()函數(shù)用于計算標準差。標準差等于方差的平方根。所以它和SQRT()或VARIANCE()這兩個表達式是等效的。【例】求選修101號課程等效學生成績的標準差和方差。SELECTSTDDEV(成績),VARIANCE(成績) FROMcj WHERE課程號='101';查詢結(jié)果如圖。選擇輸出項（5）GROUP_CONCAT()函數(shù)：它返回來自一個組指定表達式的所有非NULL值，這些值一個接著一個放置，中間用逗號隔開，并表示為一個長長的字符串，這個字符串的長度是有限制的，標準值是1024，格式如下。GROUP_CONCAT({[ALL|DISTINCT]表達式}|*)【例】求選修了206號課程的學生的學號。SELECTGROUP_CONCAT(學號) FROMcj WHERE課程號='206';查詢結(jié)果如圖。選擇輸出項（6）BIT_AND()函數(shù)、BIT_OR()函數(shù)和BIT_XOR()函數(shù)：它們與二進制運算符&（與）、|（或）和^（異或）相對應格式如下。BIT_AND|BIT_OR|BIT_XOR({[ALL|DISTINCT]表達式}|*)【例】查詢（xsk）學生表共同的愛好和所有的愛好。USExscj；SELECT*FROMxsk; #（a）SELECTBIT_OR(愛好),BIT_AND(愛好)FROMxsk; #（b）查詢結(jié)果如圖。

02限制輸出行限制輸出行LIMIT子句用于限制輸出起始位置和輸出行數(shù)，格式為。LIMIT[偏移,]行數(shù)說明：限制返回查詢結(jié)果集不超過指定行數(shù)。包含偏移，從偏移位置開始，否則從第1行開始?！纠坎檎覍W生表中學號最靠前的5位學生的信息。SELECT學號,姓名,專業(yè),性別,出生日期,總學分 FROMxs ORDERBY學號 LIMIT5; #（a）SELECT學號,姓名,專業(yè),性別,出生日期,總學分 FROMxs ORDERBY學號 LIMIT5,5; #（b）限制輸出行查詢結(jié)果如圖。

03指定查詢數(shù)據(jù)源全連接JOIN連接指定查詢數(shù)據(jù)源SELECT指定查詢對象（數(shù)據(jù)源）：FROM源表,...其中，源表：表名[AS別名]|視圖名[AS別名]|表連接

說明：（1）SELECT指定查詢數(shù)據(jù)源為數(shù)據(jù)庫的表、視圖或多個表組成的連接。（2）別名為表指定的另一個（更直觀）名字。如果指定了表別名，則這條SELECT語句中的其他子句都必須使用表別名來代替原表名。（3）直接指定表名屬于當前數(shù)據(jù)庫，否則表名前需要加數(shù)據(jù)庫名作為前綴。例如：USExscj;SELECT*FROMtest.memoryb;當然，在SELECT關(guān)鍵字后指定列名的時候也可以在列名前帶上所屬數(shù)據(jù)庫和表的名字。指定查詢數(shù)據(jù)源1.全連接將各個表用逗號分隔，就指定了一個全連接。FROM子句產(chǎn)生的中間結(jié)果是一個新表，表的每行都與其他表中的每行交叉以產(chǎn)生所有可能的組合，列包含了所有表中出現(xiàn)的列，也就是笛卡兒積?！纠坎檎宜袑W生選過的課程信息。SELECTDISTINCTkc.課程名,cj.課程號,學時,學分 FROMkc,cj WHEREkc.課程號=cj.課程號;查詢結(jié)果如圖。指定查詢數(shù)據(jù)源2.JOIN連接JOIN連接子句如下：表[INNER|CROSS]JOIN表[連接條件]|表STRAIGHT_JOIN表|表STRAIGHT_JOIN表ON連接條件|表{LEFT|RIGHT}[OUTER]JOIN表連接條件|表NATURAL[{LEFT|RIGHT}[OUTER]]JOIN表其中，連接條件：ON連接表達式|USING(列表)指定查詢數(shù)據(jù)源使用JOIN關(guān)鍵字的連接主要分為以下3種。（1）內(nèi)連接。指定INNER關(guān)鍵字的連接為內(nèi)連接，它相當于全連接用ON實現(xiàn)WHERE等值功能?！纠坎檎宜袑W生選過的課程名和課程號。SELECTDISTINCT課程名,cj.課程號 FROMKCINNERJOINcj ON(kc.課程號=cj.課程號);

內(nèi)連接是系統(tǒng)默認的，可以省略INNER關(guān)鍵字。使用內(nèi)連接后，F(xiàn)ROM子句中的ON條件主要用來連接表，其他并不屬于連接表的條件可以使用WHERE子句來指定?！纠坎檎疫x修了206號課程且成績80分以上的學生的姓名及成績。SELECT姓名,成績 FROMxsJOINcjONxs.學號=cj.學號 WHERE課程號='206'AND成績>=80;查詢結(jié)果如圖。指定查詢數(shù)據(jù)源內(nèi)連接還可用于多個表的連接。【例】查找選修了計算機基礎課程且成績不少于80分的學生的學號、姓名、課程名及成績。SELECTxs.學號,姓名,課程名,成績 FROMcjJOINxsONcj.學號=xs.學號 JOINkcONcj.課程號=kc.課程號 WHERE課程名='計算機導論'AND成績>=80;查詢結(jié)果如圖。指定查詢數(shù)據(jù)源作為特例，可以將一個表與它自身進行連接，稱為自連接。若要在一個表中查找具有相同列值的行，則可以使用自連接。使用自連接時需為表指定兩個別名，且對所有列的引用均要用別名限定?！纠坎檎蚁嗤煽兊膶W生的學號、課程號和成績。SELECTa.學號,a.課程號,b.課程號,a.成績 FROMcjasaJOINcjasb ONa.成績=b.成績ANDa.課程號!=b.課程號 WHEREa.成績>=85;查詢結(jié)果如圖。指定查詢數(shù)據(jù)源【例】查詢課程表中所有學生選過的課程號和課程名。SELECTDISTINCT課程號,課程名 FROMkcINNERJOINcj USING(課程號);查詢結(jié)果如圖。指定查詢數(shù)據(jù)源【例】查詢前5個成績記錄學生的學號、課程名和成績。SELECT學號,課程名,成績 FROMkcINNERJOINcj USING(課程號) LIMIT5; USING(課程號);查詢結(jié)果如圖。指定查詢數(shù)據(jù)源（2）外連接。指定了OUTER關(guān)鍵字的連接為外連接。外連接有以下幾種形式。左外連接（LEFTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括左表有但右表中不匹配的行，對于這樣的行，從右表被選擇的列設置為NULL。右外連接（RIGHTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括右表有但左表中不匹配的行，對于這樣的行，從左表被選擇的列設置為NULL。自然連接（NATURALJOIN）：自然連接又分自然左外連接（NATURALLEFTOUTERJOIN）和自然右外連接（NATURALRIGHTOUTERJOIN）。NATURALJOIN的語義與使用了ON條件的INNERJOIN相同。指定查詢數(shù)據(jù)源【例】查找所有學生情況及他們選修的課程號，若學生未選修任何課程，也要包括其情況。SELECTxs.*,課程號,成績FROMxsLEFTOUTERJOINcjONxs.學號=cj.學號;【例】查找被選修了的課程的選修情況和所有開設的課程名。SELECT課程名,cj.* FROMcjRIGHTJOINkcONcj.課程號=kc.課程號 LIMIT40,10;查詢結(jié)果如圖。指定查詢數(shù)據(jù)源【例】使用自然連接查詢成績表中的課程號和課程名。SELECT課程名,課程號 FROMkc WHERE課程號IN (SELECTDISTINCT課程號FROMkcNATURALRIGHTOUTERJOINcj);查詢結(jié)果如圖。指定查詢數(shù)據(jù)源（3）交叉連接。指定了CROSSJOIN關(guān)鍵字的連接為交叉連接，功能與INNERJOIN等同?！纠坎樵儗W生表所有可能的選課情況。SELECTxs.學號,姓名,課程號 FROMxsCROSSJOINcj LIMIT40,10;查詢結(jié)果如圖。另外，STRAIGHT_JOIN連接的用法和INNERJOIN連接基本相同。不同的是，STRAIGHT_JOIN后不可使用USING子句替代ON條件?！纠坎樵兂煽儽戆恼n程。SELECTDISTINCT課程名,cj.課程號 FROMkcSTRAIGHT_JOINcj ON(kc.課程號=cj.課程號);04查

詢

條

件比較運算模式匹配范圍比較空值比較子查詢查詢條件WHERE子句設置查詢條件。WHERE條件其中，條件：<判定條件[邏輯運算符<判定條件>]判定條件:表達式{=|<|<=|>|>=|<=>|<>|!=}表達式 /*比較運算*/|匹配列[NOT]LIKE匹配表達式[ESCAPE'轉(zhuǎn)義字符'] /*LIKE運算符*/|匹配列[NOT][REGEXP|RLIKE]匹配表達式 /*REGEXP運算符*/|表達式[NOT]BETWEEN表達式AND表達式 /*指定范圍*/|表達式IS[NOT]NULL /*是否是空值判斷*/|表達式[NOT]IN(SELECT語句|表達式[,…n]) /*IN子句*/|表達式{=|<|<=|>|>=|<=>|<>|!=}{ALL|SOME|ANY}(SELECT語句) /*比較子查詢*/|EXIST(SELECT語句) /*EXIST子查詢*/查詢條件1．比較運算比較運算符用于比較兩個表達式的值，MySQL支持的比較運算符有=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）。表達式{=|<|<=|>|>=|<=>|<>|!=}表達式其中，表達式是除TEXT和BLOB外的類型的表達式。當兩個表達式值均不為空值（NULL）時，除了“<=>”運算符，其他比較運算返回邏輯值TRUE（真）或FALSE（假）；而當兩個表達式值中有一個為空值或都為空值時，將返回UNKNOWN?！纠坎樵兂煽儽碇袑W號為221101的學生的成績。SELECT學號,課程號,成績 FROMcj WHERE學號='221101';查詢結(jié)果如圖。查詢條件【例】查詢學生表中總學分大于50分的學生的情況。SELECT姓名,學號,出生日期,總學分 FROMxs WHERE總學分>50;查詢結(jié)果如圖。查詢條件【例】查詢學生表中包含備注的學生的情況。SELECT姓名,學號,專業(yè),備注 FROMxs WHERE備注!='';查詢結(jié)果如圖。說明：（1）因為“備注”列為text類型，所以包含內(nèi)容肯定不為''（注意中間沒有空格）。（2）因為text屬于字符串，可以用操作字符串函數(shù)，所以條件：“LENGTH(備注)>0”與上述效果相同。其中，LENGTH()為獲得字符串長度。查詢條件【例】查詢學生表中專業(yè)為計算機、性別為女的學生的情況。SELECT姓名,學號,總學分 FROMxs WHERE專業(yè)='計算機'AND性別=0;查詢結(jié)果如圖。查詢條件2．模式匹配（1）LIKE運算符。用于指出一個字符串是否與指定的字符串相匹配，其運算對象可以是char、varchar、text、datetime等類型的數(shù)據(jù)，返回邏輯值TRUE或FALSE。匹配列[NOT]LIKE匹配表達式[ESCAPE'轉(zhuǎn)義字符']說明：用匹配列與匹配表達式匹配。用LIKE進行模式匹配時，常使用特殊符號“_”和“%”，可進行模糊查詢。轉(zhuǎn)義字符：沒有默認值，且必須為單個字符。當要匹配的字符串中含有與特殊符號（“_”和“%”）相同的字符時，應通過該字符前的轉(zhuǎn)義字符指明其為模式串中的一個匹配字符。查詢條件【例】查詢學生表中姓“王”的學生的學號、姓名及性別。SELECT學號,姓名,性別 FROMxs WHERE姓名LIKE'王%';查詢結(jié)果如圖。【例】查詢學生表中大三（學號第二個數(shù)字為0）的學生的學號、姓名及專業(yè)。SELECT學號,姓名,專業(yè) FROMxs WHERE學號LIKE'_0%';查詢結(jié)果如圖。查詢條件（2）REGEXP運算符。REGEXP是正則表達式的縮寫，和LIKE運算符一樣，REGEXP運算符有多種功能，但它不是SQL標準的一部分。REGEXP運算符的一個同義詞是RLIKE。列名[NOT][REGEXP|RLIKE]匹配表達式REGEXP運算符則有更多具有特殊含義的符號，參見表。特殊字符含義特殊字符含義^匹配字符串的開始部分[abc]匹配方括號里出現(xiàn)的字符串a(chǎn)bc$匹配字符串的結(jié)束部分[a-z]匹配方括號里出現(xiàn)的在a～z范圍內(nèi)的1個字符.匹配任意一個字符（包括回車和新行）[^a-z]匹配方括號里出現(xiàn)的不在a～z范圍內(nèi)的1個字符*匹配星號之前的0個或多個字符的任意序列|匹配符號左邊或右邊出現(xiàn)的字符串+匹配加號之前的1個或多個字符的任意序列[[..]]匹配方括號里出現(xiàn)的符號（如空格、換行、括號、句號、冒號、加號、連字符等）?匹配問號之前的0個或多個字符[[:<:]]和[[:>:]]匹配一個單詞的開始和結(jié)束{n}匹配括號前的內(nèi)容出現(xiàn)n次的序列[[::]]匹配方括號里出現(xiàn)的字符中的任意一個()匹配括號里的內(nèi)容

查詢條件【例】查詢學生表姓“李”的學生的學號、姓名和專業(yè)。SELECT學號,姓名,專業(yè) FROMxs WHERE姓名REGEXP'^李';查詢結(jié)果如圖?！纠坎樵儗W生表學號里包含數(shù)字4、5、6的學生的學號、姓名和專業(yè)。SELECT學號,姓名,專業(yè) FROMxs WHERE學號REGEXP'[4,5,6]';查詢結(jié)果如圖。查詢條件【例】查詢學生表學號以22開頭、以01結(jié)尾的學生的學號、姓名和專業(yè)。SELECT學號,姓名,專業(yè) FROMxs WHERE學號REGEXP'^22.*01$';查詢結(jié)果如圖。查詢條件3．范圍比較用于范圍比較的關(guān)鍵字有兩個：BETWEEN和IN。（1）BETWEEN指定查詢范圍。表達式[NOT]BETWEEN表達式1AND表達式2當不使用NOT時，若表達式的值在表達式1與表達式2之間（包括這兩個值），則返回TRUE，否則返回FALSE；使用NOT時，返回值剛好相反。查詢條件（2）IN關(guān)鍵字可以指定一個值表，值表中列出所有可能的值，當與值表中的任一個匹配時都返回TRUE，否則返回FALSE。表達式IN(表達式[,…n])【例】查詢學生表中2004年出生的學生的情況。SELECT學號,姓名,專業(yè),出生日期 FROMxs WHERE出生日期BETWEEN'2004-1-1'AND'2004-12-31';查詢結(jié)果如圖。查詢條件【例】查詢學生表中計算機、通信工程或電氣工程專業(yè)的學生的情況。SELECT* FROMxs WHERE專業(yè)IN('計算機','通信工程','電氣工程');該語句與下句等價：SELECT* FROMxs WHERE專業(yè)='計算機'OR專業(yè)='通信工程'OR專業(yè)='電氣工程';查詢條件4．空值比較當需要判定一個表達式的值是否為空值時，使用ISNULL關(guān)鍵字。表達式IS[NOT]NULL當不使用NOT時，若表達式值為空值，則返回TRUE，否則返回FALSE；當使用NOT時，結(jié)果剛好相反?！纠坎樵儗W生表備注為空的學生的情況。SELECT* FROMxs WHERE備注ISNULL;查詢條件5．子查詢子查詢除了可以用在SELECT語句中外，還可以用在INSERT、UPDATE及DELETE語句中，通常與IN、比較運算符及EXIST謂詞結(jié)合使用。（1）IN子查詢。IN子查詢用于進行一個給定值是否在子查詢結(jié)果集中的判斷。表達式[NOT]IN(SELECT語句)當表達式與子查詢SELECT語句的結(jié)果表中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE；若使用了NOT，則返回的值剛好相反。查詢條件在執(zhí)行包含子查詢的SELECT語句時，系統(tǒng)先執(zhí)行子查詢，產(chǎn)生一個結(jié)果表，再執(zhí)行外查詢。【例】查找xscj數(shù)據(jù)庫中選修了206號課程的學生的姓名、學號。SELECT姓名,學號 FROMxs WHERE學號IN (SELECT學號 FROMcj WHERE課程號='206' );說明：先執(zhí)行下列子查詢：SELECT學號 FROMcj WHERE課程號='206';得到一個只含有學號列的表，cj中的每個課程號為206的行在結(jié)果表中都有一行。查詢條件【例】查找未選修離散數(shù)學課程的學生的姓名、學號、專業(yè)。SELECT姓名,學號,專業(yè) FROMxs WHERE學號NOTIN (SELECT學號 FROMcj WHERE課程號IN (SELECT課程號 FROMkc WHERE課程名='離散數(shù)學' ) );查詢條件（2）比較子查詢將表達式的值與子查詢的結(jié)果進行比較運算。表達式{<|<=|=|>|>=|<>|!=}{ALL|SOME|ANY}(select語句)如果子查詢的結(jié)果集只返回一行數(shù)據(jù)，可以通過比較運算符直接比較。ALL指定表達式要與子查詢結(jié)果集中的每個值都進行比較，當表達式與每個值都滿足比較的關(guān)系時才返回TRUE，否則返回FALSE?！纠坎檎疫x修了離散數(shù)學課程的學生的學號。SELECTcount(學號) FROMcj WHERE課程號= (SELECT課程號 FROMkc WHERE課程名='離散數(shù)學' );查詢結(jié)果如圖。查詢條件【例】查找學生表中比所有計算機專業(yè)的學生出生日期都大的學生的學號、姓名、專業(yè)、出生日期。SELECT學號,姓名,專業(yè),出生日期 FROMxs WHERE出生日期>ALL (SELECT出生日期 FROMxs WHERE專業(yè)='計算機' );查詢結(jié)果如圖。查詢條件【例】查找課程號為206的成績不低于課程號為102的最低成績的學生的學號。SELECT學號 FROMcj WHERE課程號='206'AND成績>=ANY (SELECT成績 FROMcj WHERE課程號='102' );查詢條件（3）EXISTS子查詢用于測試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則返回TRUE，否則返回FALSE。NOTEXISTS返回值與EXIST剛好相反。[NOT]EXISTS(SELECT語句)【例】查找選修206號課程的學生的姓名。SELECT姓名 FROMxs WHEREEXISTS (SELECT* FROMcj WHERE學號=xs.學號AND課程號='206' );查詢條件【例】查找選修了全部課程的學生的姓名。SELECT姓名 FROMxs WHERENOTEXISTS (SELECT* FROMkc WHERENOTEXISTS (SELECT* FROMcj WHERE學號=xs.學號AND課程號=kc.課程號 ) );說明：由于沒有人選修全部課程，所以結(jié)果為空。查詢條件（4）子查詢的4種類型如下：返回一個表的子查詢是表子查詢；返回帶有一個或多個值的一行的子查詢是行子查詢；返回一行或多行，但每行上只有一個值的是列子查詢；返回一個值的是標量子查詢。每個標量子查詢都是一個列子查詢和行子查詢。上面介紹的子查詢都屬于列子查詢。另外，子查詢還可以用在SELECT語句的其他子句中。表子查詢可以用在FROM子句中，但必須為子查詢產(chǎn)生的中間表定義一個別名?！纠繌膞s表中查找總學分大于50分的男學生的姓名、學號和總學分。SELECT姓名,學號,總學分 FROM(SELECT姓名,學號,性別,總學分 FROMxs WHERE總學分>50 )ASstudent WHERE性別;查詢結(jié)果如圖。查詢條件【例】從學生表中查找所有女學生的姓名、學號，以及與221101號學生的年齡差距。SELECT學號,姓名,YEAR(出生日期)-YEAR((SELECT出生日期 FROMxs WHERE學號='221101'))AS年齡差距FROMxsWHERE性別=FALSE;查詢結(jié)果如圖。查詢條件【例】查找與學號為221101的學生性別相同、總學分相同的學生的學號和姓名。SELECT學號,姓名 FROMxs WHERE(性別,總學分)=(SELECT性別,總學分 FROMxs WHERE學號='221101' );查詢結(jié)果如圖。05查

詢

分

組查詢分組GROUPBY子句用于對查詢結(jié)果集分組。GROUPBY{列名|表達式|輸出項號}[ASC|DESC],...[WITHROLLUP]說明：可以按照列名、表達式、輸出項號進行分組，分組后輸出順序指定ASC為升序或DESC為降序。表達式由列、運算符和函數(shù)（聚合函數(shù)）組成。【例】求xscj數(shù)據(jù)庫中各專業(yè)的學生數(shù)。SELECT專業(yè),COUNT(*)AS'學生數(shù)' FROMxs GROUPBY專業(yè);查詢結(jié)果如圖。查詢分組【例】求被選修的各門課程的平均成績和選修該課程的人數(shù)。SELECT課程號,AVG(成績)AS'平均成績',COUNT(學號)AS'選修人數(shù)' FROMcj GROUPBY課程號;查詢結(jié)果如圖。查詢分組【例】查詢每個專業(yè)的男生人數(shù)、女生人數(shù)、總?cè)藬?shù)，以及學生總?cè)藬?shù)。SELECT專業(yè),性別,count(*)AS'人數(shù)' FROMxs GROUPBY專業(yè),性別 WITHROLLUP;查詢結(jié)果如圖。查詢分組產(chǎn)生的規(guī)則如下：按列的逆序依次進行匯總。如本例根據(jù)專業(yè)（2個專業(yè)）和性別（1和0）組合將xs表分為4組，使用ROLLUP操作符后，先對性別列產(chǎn)生了匯總行（針對專業(yè)相同的行），然后對專業(yè)與性別均不同的值產(chǎn)生了匯總行。所產(chǎn)生的匯總行中對應具有不同列值的列值將置為NULL?？梢詫⑸鲜稣Z句與不帶ROLLUP操作符的GROUPBY子句的執(zhí)行情況進行比較：SELECT專業(yè),性別,COUNT(*)AS'人數(shù)' FROMxs GROUPBY專業(yè),性別;查詢結(jié)果如圖。查詢分組【例】查詢每門課程各專業(yè)的平均成績、每門課程的總平均成績和所有課程的總平均成績。SELECT課程名,專業(yè),AVG(成績)AS'平均成績' FROMcj,kc,xs WHEREcj.課程號=kc.課程號ANDcj.學號=xs.學號 GROUPBY課程名,專業(yè) WITHROLLUP;查詢結(jié)果如圖。06分組后過濾分組后過濾使用HAVING子句的目的與WHERE子句不同，WHERE子句從FROM指定的表選擇行，而HAVING子句則用來在GROUPBY子句選擇分組后選擇輸出的行。HAVING子句中的條件可以包含聚合函數(shù)，而WHERE子句則不可以。HAVING條件SQL標準要求HAVING必須引用GROUPBY子句中的列或用于聚合函數(shù)中的列。【例】查詢平均成績在85分以上的學生的學號和平均成績。SELECT學號,AVG(成績)AS平均成績 FROMcj GROUPBY學號 HAVING平均成績>=85;查詢結(jié)果如圖。分組后過濾說明：（1）如果不包含分組GROUP子句和HAVING子句：SELECT學號,AVG(成績)AS平均成績 FROMcj;則查詢結(jié)果顯示一個記錄為所有人的平均成績，如圖。（2）如果不包含分組GROUP子句，則查詢結(jié)果所有人的平均成績不大于等于85，沒有顯示記錄。（3）如果不包含分組GROUP子句，而輸出項不包括聚合函數(shù)，則HAVING與WHERE子句功能相當。SELECT學號,成績 FROMcj HAVING成績>=85;查詢結(jié)果為所有大于等于85的學生課程成績，如圖。分組后過濾【例】查找選修課程超過2門且成績都在70分以上的學生的學號。SELECT學號 FROMcj WHERE成績>=70 GROUPBY學號 HAVINGCOUNT(*)>2;查詢結(jié)果如圖。分組后過濾【例】查找通信工程專業(yè)平均成績在85分以上的學生的學號和平均成績。SELECT學號,AVG(成績)AS'平均成績' FROMcj WHERE學號IN (SELECT學號 FROMxs WHERE專業(yè)='通信工程' ) GROUPBY學號 HAVINGAVG(成績)>=85;查詢結(jié)果如圖。07輸

出

排

序輸出排序ORDERBY子句指定結(jié)果行按指定列、表達式或者輸出項號順序排列，否則無法預料結(jié)果中行的順序格式如下。ORDERBY{列名|表達式|輸出項號}[ASC|DESC],...說明：輸出項號表示按SELECT結(jié)果集中位置上的項排序。【例】將計算機專業(yè)學生的計算機導論課程成績按降序排列。SELECTxs.學號,姓名,成績 FROMxs,kc,cj WHERExs.學號=cj.學號 ANDcj.課程號=kc.課程號 AND課程名='計算機導論' AND專業(yè)='計算機' ORDERBY成績DESC;查詢結(jié)果如圖。輸出排序【例】將出生日期2003年后的學生按專業(yè)名拼音排序，相同專業(yè)按照出生日期從小到大排序。SELECT學號,姓名,專業(yè),出生日期 FROMxsb WHERE出生日期>='2004-1-1' ORDERBY專業(yè)DESC,出生日期查詢結(jié)果如圖。輸出排序【例】將通信工程專業(yè)學生的情況按平均成績升序排列。SELECT學號,姓名,總學分 FROMxs WHERE專業(yè)='通信工程' ORDERBY(SELECTAVG(成績) FROMcj GROUPbycj.學號 HAVINGxs.學號=cj.學號);查詢結(jié)果如圖。08聯(lián)

合

查

詢聯(lián)合查詢使用UNION子句，可以把來自許多SELECT語句的結(jié)果組合到一個結(jié)果集中。SELECT語句UNIONSELECT語句...說明：使用UNION的時候，在第一個SELECT語句中被使用的列名稱被用于結(jié)果中的列名稱。【例】聯(lián)合計算機專業(yè)和通信工程專業(yè)2022學生信息。（1）為了測試聯(lián)合，將學生表（xs）分成計算機專業(yè)和通信工程專業(yè)表，模擬實際獨立結(jié)構(gòu)相同的表。USExscj;DROPTABLEIFEXISTSxs_jsj,xs_txgc;CREATETABLExs_jsjSELECT*FROMxsWHERE專業(yè)='計算機';CREATETABLExs_txgcSELECT*FROMxsWHERE專業(yè)='通信工程';聯(lián)合查詢（2）聯(lián)合計算機專業(yè)和通信工程專業(yè)2022學生信息。SELECT* FROMxs_jsj WHERE學號LIKE'22%' UNION SELECT* FROMxs_txgc WHERE學號LIKE'22%';查詢結(jié)果如圖。聯(lián)合查詢下列命令與上述功能相同：SELECT* FROM (SELECT* FROMxs_jsj UNION SELECT* FROMxs_txgc )ASmyxs WHERE學號LIKE'22%';09輸出到變量或文件輸出到變量或文件使用INTO子句，可以把來自許多SELECT語句的結(jié)果輸出到變量或者文件中。INTO變量名,...INTOOUTFILE'文件名'[CHARACTERSET字符集名]說明：默認情況下，查詢結(jié)果在屏幕上顯示。指定INTO變量名項，可將查詢結(jié)果存放到內(nèi)存變量中。指定OUTFILE項，將查詢結(jié)果存放到指定文件中。10非基本數(shù)據(jù)類型查詢枚舉類型列查詢條件集合類型列查詢條件JSON類型列查詢條件空間類型列查詢條件非基本數(shù)據(jù)類型查詢先顯示rsk表結(jié)構(gòu)：USExscj;DESCRIBExsk; #（a）SELECT*FROMxsk; #（b）顯示如圖。非基本數(shù)據(jù)類型查詢1．枚舉類型列查詢條件例如，“畢業(yè)去向”枚舉定義enum('直接就業(yè)','考研','考公務員','出國留學','創(chuàng)業(yè)')，該列可以取的值和對應的索引編號如表。成員索引編號NULLNULL“0'直接就業(yè)'1'考研'2'考公務員'3'出國留學'4'創(chuàng)業(yè)'5非基本數(shù)據(jù)類型查詢（1）精確查詢枚舉類型列查詢條件可以用成員序號，也可以用字符串。【例】在xsk表中查詢畢業(yè)去向為直接就業(yè)或者創(chuàng)業(yè)。USExscj;SELECT學號,畢業(yè)去向FROMxsk WHERE畢業(yè)去向='直接就業(yè)'OR畢業(yè)去向=5;顯示如圖。（2）模糊查詢可以將枚舉類型的列存放的內(nèi)容認為是枚舉字符串。SELECT學號,畢業(yè)去向FROMxsk WHERE畢業(yè)去向LIKE'%業(yè)';顯示如圖。非基本數(shù)據(jù)類型查詢2．集合類型列查詢條件例如：“愛好”集合定義set('書法','繪畫','音樂','運動')，對應“愛好”列存儲內(nèi)容如表。成員二進制值十進制值'書法'11'繪畫'102'音樂'1004'運動'10008非基本數(shù)據(jù)類型查詢1）精確查詢集合類型列查詢條件可以采用將成員的二進制位序號表示成十進制，也可以采用字符串，多個成員順序必須完全相同，用逗號（,）分隔?！纠吭趚sk表中查詢愛好為書法或者繪畫，查詢同時愛好書法和繪畫。（1）查詢愛好為書法或者繪畫：USExscj;SELECT學號,愛好FROMxsk WHERE愛好='書法'OR愛好=2;顯示如圖（a）。非基本數(shù)據(jù)類型查詢（2）查詢同時愛好書法和繪畫。USExscj;SELECT學號,愛好FROMxsk WHERE愛好='書法,繪畫';或者SELECT學號,愛好FROMxsk WHERE愛好=3;顯示如圖（b）。非基本數(shù)據(jù)類型查詢2）模糊查詢可以將集合類型的列存放的內(nèi)容認為是集合字符串，之間用逗號（,）分隔。SELECT學號,愛好FROMxskWHERE愛好LIKE'%書法%';顯示如圖（c）。3）按內(nèi)容查詢USExscj;SELECT學號,愛好FROMxsk WHEREFIND_IN_SET('繪畫',愛好);或者SELECT學號,愛好FROMxsk WHEREFIND_IN_SET('繪畫',愛好)>0;查詢結(jié)果如圖（d）。非基本數(shù)據(jù)類型查詢3．JSON類型列查詢條件JSON類型列使用“列名->>路徑”來指定JSON的某一路徑“鍵”。也可以使用JSON函數(shù)進行查詢。JSON函數(shù)很多，請參考有關(guān)文檔。【例】在xsk表中按家庭地址查詢。USExscj;SELECT學號,家庭地址->>'$."市"'AS城市,家庭地址->>'$."街道"'AS具體位置FROMxsk WHERE家庭地址->>'$."街道"'LIKE'%學府%'; #（a）SELECT學號,家庭地址->>'$."區(qū)縣"'AS區(qū)縣,家庭地址->>'$."街道"'AS具體位置FROMxsk WHEREJSON_CONTAINS(家庭地址,'"南京"','$."市"'); #（b）查詢結(jié)果如圖。

非基本數(shù)據(jù)類型查詢4．空間類型列查詢條件空間類型列數(shù)據(jù)一般不能直接使用表達式作為查詢條件，而是需要通過空間數(shù)據(jù)處理系統(tǒng)函數(shù)進行處理?！纠坎樵兡暇┫蓤蛲哆f站與學生家庭地理位置的距離。SET@g1=ST_GeomFromText('POINT(118.8832.11)'); #南京仙堯投遞站位置USExscj;SELECT學號,家庭地址->>'$."市"'AS城市,家庭地址->>'$."街道"'AS地點,TRUNCATE(ST_Distance(地理位置,@g1)*111195/1000,2)ASkm FROMxsk;運行查詢結(jié)果如圖。11分

區(qū)

查

詢查詢數(shù)據(jù)庫表分區(qū)情況直接按分區(qū)查詢分區(qū)查詢1．查詢數(shù)據(jù)庫表分區(qū)情況在MySQL系統(tǒng)數(shù)據(jù)庫information_schema中的PARTITIONS表存放MySQL實例中所有數(shù)據(jù)庫表的分區(qū)信息，對該表查詢就可了解你關(guān)注的數(shù)據(jù)庫表的分區(qū)情況。【例】查詢xscj數(shù)據(jù)庫xsb表分區(qū)情況。SELECT PARTITION_NAME分區(qū)名稱, PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分區(qū)類型, PARTITION_EXPRESSION表達式, PARTITION_DESCRIPTION描述, CREATE_TIME創(chuàng)建時間, TABLE_ROWSAS記錄數(shù) FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='xsb';查詢結(jié)果如圖。分區(qū)查詢?nèi)舨樵儣l件換成如下形式運行，查詢結(jié)果如圖。WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='kcb';若查詢條件換成如下形式運行，查詢結(jié)果如圖。WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='cjb';分區(qū)查詢2．直接按分區(qū)查詢?nèi)绻樵兊臈l件為分區(qū)列，則可以直接通過分區(qū)查詢?！纠坎樵儀scj數(shù)據(jù)庫xsb表的2021年入學的女生。USExscj;SELECT* FROMxsb PARTITION(p1) WHERE!性別;SELECT* FROMxsb WHERE學號LIKE'21%'AND!性別;查詢結(jié)果如圖。分區(qū)查詢【例】查詢第四學年開設課程名以“計算機”打頭的課程。USExscj;SELECT* FROMkcb PARTITION(四學年課)WHERE課程名LIKE'計算機%';查詢結(jié)果如圖。分區(qū)查詢【例】查詢指定學號和指定課程號的學生成績。USExscj;SET@xh='211101';SET@kch='101';SELECT*FROMcjb WHERE學號=@xhAND課程號=@kch;查詢結(jié)果如圖。第4章

查詢和視圖——視

圖視

圖使用視圖具有下列優(yōu)點。（1）為用戶集中數(shù)據(jù)。定義視圖可以將分散在多個表中數(shù)據(jù)集中在一起，用戶對多表數(shù)據(jù)查詢和處理就可以在一個視圖中完成。（2）創(chuàng)建視圖可以向最終用戶隱藏復雜的表連接，使用視圖的用戶并不知道基本數(shù)據(jù)的來源。（3）簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。（4）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫中的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次。（5）可以重新組織數(shù)據(jù)以便輸出到其他應用程序中。01創(chuàng)

建

視

圖創(chuàng)建視圖視圖在數(shù)據(jù)庫中是作為一個對象來存儲的。用戶創(chuàng)建視圖前，要保證自己已被數(shù)據(jù)庫所有者授權(quán)使用CREATEVIEW語句，并且有權(quán)操作視圖所涉及的表或其他視圖。CREATE[ORREPLACE]VIEW視圖名[(視圖列名,...)] [選項] ASSELECT語句 [WITH[CASCADED|LOCAL]CHECKOPTION]視圖名：視圖的命名必須遵循標識符命名規(guī)則，不能與表和其他視圖同名。ORREPLACE：能夠替換已有的同名視圖。它