第7章-查詢(xún)和視圖

第7章

查詢(xún)和視圖第7章查詢(xún)和視圖ppt課件（全）本章學(xué)習(xí)目標(biāo)：掌握T-SQL作為數(shù)據(jù)定義語(yǔ)言的語(yǔ)法與應(yīng)用掌握WHERE、ORDERBY、GROUPBY、HAVING子句的使用掌握基本的多表查詢(xún)掌握內(nèi)連接、外連接、交叉連接和聯(lián)合查詢(xún)的使用掌握多行和單值子查詢(xún)的使用掌握嵌套子查詢(xún)的使用了解表的視圖第7章查詢(xún)和視圖ppt課件（全）7.1連接、選擇和投影SQLServer2008是一種關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，在關(guān)系數(shù)據(jù)庫(kù)中，必須提供一種對(duì)二維表進(jìn)行運(yùn)算的機(jī)制。這種機(jī)制除了包括傳統(tǒng)的集合運(yùn)算中的并、交、差、廣義笛卡爾積以外，還包括專(zhuān)門(mén)的關(guān)系運(yùn)算中的選擇、投影和連接。第7章查詢(xún)和視圖ppt課件（全）7.1.1選擇(Selection)選擇是單目運(yùn)算，它是按照一定的條件，從關(guān)系R中選擇出滿(mǎn)足條件的行為作為結(jié)果返回。選擇運(yùn)算的操作對(duì)象是一張二維表，其運(yùn)算結(jié)果也是一章二維表。選擇運(yùn)算的記號(hào)為σF(R)，其中σ是選擇運(yùn)算符，下標(biāo)F是一個(gè)條件表達(dá)式，R是被操作的表。 選擇運(yùn)算符的含義：在關(guān)系R中選擇滿(mǎn)足給定條件的諸元組σF(R)={t|tR∧F(t)='真'}F：選擇條件，是一個(gè)邏輯表達(dá)式，基本形式為：[(]X1θY1[)][φ[(]X2θY2[)]]…θ：比較運(yùn)算符（＞，≥，＜，≤，＝或<>）第7章查詢(xún)和視圖ppt課件（全） 例【7-1】設(shè)有一個(gè)學(xué)生-課程數(shù)據(jù)庫(kù)，包括學(xué)生關(guān)系Student，查詢(xún)信息系（IS系）全體學(xué)生。 σSdept='IS'(Student) 或σ5='IS'(Student)第7章查詢(xún)和視圖ppt課件（全）學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage系別Sdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19IS第7章查詢(xún)和視圖ppt課件（全）7.1.2投影(Projection)投影也是單目運(yùn)算，該運(yùn)算從表中選出指定的屬性值組成一個(gè)新表，記為：ΠA(R)。其中A是屬性名（即列名）表，R是表名。投影運(yùn)算符的含義從R中選擇出若干屬性列組成新的關(guān)系πA(R)={t[A]|tR} A：R中的屬性列投影操作主要是從列的角度進(jìn)行運(yùn)算，但投影之后不僅取消了原關(guān)系中的某些列，而且還可能取消某些元組（避免重復(fù)行）。第7章查詢(xún)和視圖ppt課件（全）例【7-2】查詢(xún)學(xué)生的姓名和所在系 即求Student關(guān)系上學(xué)生姓名和所在系兩個(gè)屬性上的投影πSname，Sdept(Student) 或π2，5(Student)結(jié)果如表7-2所示：第7章查詢(xún)和視圖ppt課件（全）SnameSdept李勇CS劉晨IS王敏MA張立IS第7章查詢(xún)和視圖ppt課件（全）7.1.3連接(JOIN)把兩個(gè)表中的行按著給定的條件拼接而形成的新表。連接也稱(chēng)為θ連接連接運(yùn)算的含義：從兩個(gè)關(guān)系的笛卡爾積中選取屬性間滿(mǎn)足一定條件的元組A和B：分別為R和S上度數(shù)相等且可比的屬性組θ：比較運(yùn)算符

連接運(yùn)算從R和S的廣義笛卡爾積R×S中選?。≧關(guān)系）在A屬性組上的值與（S關(guān)系）在B屬性組上值滿(mǎn)足比較關(guān)系的元組。θ為“＝”的連接運(yùn)算稱(chēng)為等值連接第7章查詢(xún)和視圖ppt課件（全）7.2數(shù)據(jù)查詢(xún)語(yǔ)句(SELECT語(yǔ)句)查詢(xún)功能是T-SQL的核心，通過(guò)T-SQL的查詢(xún)可以從表或視圖中迅速、方便地檢索數(shù)據(jù)。T-SQL的查詢(xún)最基本的方式是SELECT語(yǔ)句，其功能十分強(qiáng)大。它能夠以任意順序、從任意數(shù)目的列中查詢(xún)數(shù)據(jù)，并在查詢(xún)過(guò)程中進(jìn)行計(jì)算，甚至能包含來(lái)自其他表的數(shù)據(jù)。我們選取stuinfo數(shù)據(jù)庫(kù)作為示例數(shù)據(jù)庫(kù)，以下例句均省略打開(kāi)數(shù)據(jù)庫(kù)的USEstuinfo語(yǔ)句。stuinfo數(shù)據(jù)庫(kù)有三張數(shù)據(jù)表：student、course和sc。第7章查詢(xún)和視圖ppt課件（全）SELECT語(yǔ)句的完整語(yǔ)法格式為：SELECT<列名選項(xiàng)>FROM<表名>|<視圖名稱(chēng)>[WHERE<查詢(xún)條件>|<聯(lián)接條件>][GROUPBY<分組表達(dá)式>[HAVING<分組統(tǒng)計(jì)表達(dá)式>]][ORDERBY<排序表達(dá)式>[ASC|DESC]]其中的SELECT和FROM語(yǔ)句為必選子句，而WHERE、ORDERBY和GROUPBY子句為可選子句，要根據(jù)查詢(xún)的需要去選用。SELECT語(yǔ)法中各參數(shù)進(jìn)行說(shuō)明：SELECT子句：用來(lái)指定由查詢(xún)返回的列，并且各列在SELECT子句中的順序決定了它們?cè)诮Y(jié)果表中的順序；FROM子句：用來(lái)指定數(shù)據(jù)來(lái)源的表；WHERE子句：用來(lái)限定返回行的搜索條件；GROUPBY子句：用來(lái)指定查詢(xún)結(jié)果的分組條件；ORDERBY子句：用來(lái)指定結(jié)果的排序方式。SELECT語(yǔ)句可以寫(xiě)在一行中。但對(duì)于復(fù)雜的查詢(xún)，SELECT語(yǔ)句隨著查詢(xún)子句的增加不斷增長(zhǎng)，一行很難寫(xiě)下，此時(shí)可以采用分行的寫(xiě)法，即每個(gè)子句分別在不同的行中。需要注意，子句與子句之間不能使用符號(hào)分隔。第7章查詢(xún)和視圖ppt課件（全）7.2.1SELECT語(yǔ)句對(duì)列的查詢(xún)對(duì)列的查詢(xún)實(shí)質(zhì)上是對(duì)關(guān)系的“投影”操作。在很多情況下，用戶(hù)只對(duì)表中的一部分列感興趣，可以使用SELECT子句來(lái)指明要查詢(xún)的列，并可根據(jù)需要改變輸出列顯示的先后順序。T-SQL中對(duì)列的查詢(xún)是通過(guò)對(duì)SELECT子句中的列名選項(xiàng)進(jìn)行設(shè)置完成的，具體格式為：SELECT[ALL|DISTINCT][TOPn[PERCENT]]{*|表的名稱(chēng).*|視圖名稱(chēng).*/*選擇表或視圖中的全部列*/|列的名稱(chēng)|列的表達(dá)式[[AS]列的別名] /*選擇指定的列*/}[，…n]第7章查詢(xún)和視圖ppt課件（全）1.查詢(xún)一個(gè)表中的全部列

選擇表的全部列時(shí)，可以使用星號(hào)“*”來(lái)表示所有的列?！纠?-9】檢索students表、course表和sc表中的所有紀(jì)錄。T-SQL語(yǔ)句為：

USEStuInfoSELECT*FROMstudentSELECT*FROMcourseSELECT*FROMsc也可以將三個(gè)表的操作合并為一條語(yǔ)句，使結(jié)果在一個(gè)表中顯示：

SELECTstudent.*,course.*,sc.*FROMstudent,course,sc第7章查詢(xún)和視圖ppt課件（全）2.查詢(xún)一個(gè)表中部分列

若查詢(xún)數(shù)據(jù)時(shí)要選擇一個(gè)表中的部分列信息，則在SELECT后給出需要的列即可，各列名之間逗號(hào)分隔?！纠?-10】檢索student表中學(xué)生的部分信息，包括學(xué)號(hào)、學(xué)生姓名和所屬院系。T-SQL語(yǔ)句為：

SELECTsno,sname,sdeptFROMstudent第7章查詢(xún)和視圖ppt課件（全）3.為列設(shè)置別名通常情況下，當(dāng)從一個(gè)表中取出列值時(shí)，該值與列的名稱(chēng)是聯(lián)系在一起的。如上例中從student表中取出學(xué)號(hào)與學(xué)生姓名，取出的值就與sno和sname有聯(lián)系。當(dāng)希望查詢(xún)結(jié)果中的列使用新的名字來(lái)取代原來(lái)的列名稱(chēng)時(shí)，可以使用以下方法：列名之后使用AS關(guān)鍵字來(lái)更改查詢(xún)結(jié)果中的列標(biāo)題名。如snoAS學(xué)號(hào)；直接在列名后使用列的別名，列的別名課帶雙引號(hào)、單引號(hào)或不帶引號(hào)?！纠?-11】檢索student表中學(xué)生的Sno、Sname、Sage和Sdept，結(jié)果中各列的標(biāo)題分別指定為學(xué)號(hào)、學(xué)生姓名、年齡和所屬院系。T-SQL語(yǔ)句為：

SELECTsnoas學(xué)生學(xué)號(hào),sname學(xué)生姓名,sage'年齡',sdept"所屬院系" FROMstudent第7章查詢(xún)和視圖ppt課件（全）4.計(jì)算列值使用SELECT語(yǔ)句對(duì)列進(jìn)行查詢(xún)時(shí)，SELECT后可以跟列的表達(dá)式。也就是說(shuō)，使用SELECT語(yǔ)句不僅可以查詢(xún)?cè)瓉?lái)表中已有的列，還可以通過(guò)計(jì)算得到新的列?！纠?-12】查詢(xún)sc表中的學(xué)生成績(jī)，并且顯示折算后的分?jǐn)?shù)。（折算方法：原始分?jǐn)?shù)*1.2）T-SQL語(yǔ)句為：

SELECTsno,gradeAS原始分?jǐn)?shù),grade*1.2AS折算后分?jǐn)?shù)FROMsc第7章查詢(xún)和視圖ppt課件（全）5.消除結(jié)果中的重復(fù)項(xiàng)在一張完整的關(guān)系數(shù)據(jù)庫(kù)表中不可能出現(xiàn)兩個(gè)完全相同的紀(jì)錄，但由于我們?cè)诓樵?xún)時(shí)經(jīng)常只涉及表的部分字段，這樣，就有可能出現(xiàn)重復(fù)行，可以使用DISTINCT短語(yǔ)可以避免這種情況。關(guān)鍵字DISTINCT的含義是對(duì)結(jié)果中的重復(fù)行只選擇一個(gè)，以保證行的唯一性。【例7-13】從student表中查詢(xún)所有的院系信息，并去掉重復(fù)信息。T-SQL語(yǔ)句為：

SELECTDISTINCTsdeptFROMstudent可以看到，student表一共只有4個(gè)專(zhuān)業(yè)。與DISTINCT相反，當(dāng)使用關(guān)鍵字ALL時(shí)，將保留結(jié)果中的所有行。在省略DISTINCT和ALL的情況下，SELECT語(yǔ)句默認(rèn)為ALL。第7章查詢(xún)和視圖ppt課件（全）6.限制結(jié)果返回的行數(shù)若SELECT語(yǔ)句返回的結(jié)果行數(shù)非常多，而用戶(hù)只需要返回滿(mǎn)足條件的前幾條紀(jì)錄，可以使用TOPn[PERCENT]可選子句。其中n是一個(gè)正整數(shù)，表示返回查詢(xún)結(jié)果的前n行。若使用PERCENT關(guān)鍵字，則表示返回結(jié)果的前n%行?！纠?-14】查詢(xún)student表中前10個(gè)學(xué)號(hào)。T-SQL語(yǔ)句為：

SELECTTOP10*FROMstudent執(zhí)行結(jié)果如圖7-8所示，只返回了10個(gè)學(xué)生的學(xué)號(hào)。第7章查詢(xún)和視圖ppt課件（全）7.2.2SELECT語(yǔ)句中的條件查詢(xún)條件查詢(xún)是用的最多的一種查詢(xún)方式，通過(guò)在WHERE子句中設(shè)置查詢(xún)條件可以挑選符合需要的數(shù)據(jù)、修改某一記錄、刪除某一記錄。條件查詢(xún)本質(zhì)是對(duì)表中的數(shù)據(jù)進(jìn)行篩選，即關(guān)系運(yùn)算中的“選擇”操作。在SELECT語(yǔ)句中，WHERE子句必須緊跟在FROM子句后，其基本格式為：WHERE<查詢(xún)條件>第7章查詢(xún)和視圖ppt課件（全）1.使用比較運(yùn)算符我們使用上一章介紹的比較運(yùn)算符來(lái)比較表達(dá)式值的大小，包括：=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、!=(不等于)、<>(不等于)、!<(不小于)、!>(不大于)。運(yùn)算結(jié)果為T(mén)RUE或者FALSE【例7-15】在student表中查詢(xún)信息系（IM）的學(xué)生。T-SQL語(yǔ)句為：

SELECT*FROMstudentWHEREsdept=N'IM'顯示的全為信息系的同學(xué)。第7章查詢(xún)和視圖ppt課件（全）2.使用邏輯運(yùn)算符邏輯運(yùn)算符包括AND、OR和NOT，用于連接WHERE子句中的多個(gè)查詢(xún)條件。當(dāng)一條語(yǔ)句中同時(shí)含有多個(gè)邏輯運(yùn)算符時(shí)，取值的優(yōu)先順序?yàn)椋篘OT、AND和OR?！纠?-16】在student表中查詢(xún)年齡在小于18或者大于22，并且籍貫是河南的學(xué)生信息。T-SQL語(yǔ)句為：

SELECT*FROMstudent WHERE(sage<18orsage>22)andsnativeplace=N'河南'顯示的滿(mǎn)足條件的有三名同學(xué)。第7章查詢(xún)和視圖ppt課件（全）3.使用LIKE模式匹配在查找記錄時(shí)，若不是很適合使用算術(shù)運(yùn)算符和邏輯運(yùn)算符，則可能要用到更高級(jí)的技術(shù)。LIKE是模式匹配運(yùn)算符，用于指出一個(gè)字符串是否與指定的字符串相匹配。使用LIKE進(jìn)行匹配時(shí)，可以使用通配符，即可以使用模糊查詢(xún)。T-SQL中使用的通配符有“%”、“_”、“[]”和“[^]”。通配符用在要查找的字符串的旁邊。它們可以一起使用，使用其中的一種并不排斥使用其他的通配符?！?”用于字符串的末尾或開(kāi)始處，代表0個(gè)或任意多個(gè)字符。如要查找姓名中有“a”的教師，可以使用“%a%”，這樣會(huì)查找出姓名中任何位置包含字母“a”的記錄；“_”代表單個(gè)字符。使用“_a”，將返回任何名字為兩個(gè)字符且第二個(gè)字符是“a”的姓名記錄；“[]”允許在指定值的集合或范圍中查找單個(gè)字符。如要搜索名字中包含介于a-f之間的單個(gè)字符的記錄，可以使用LIKE“%[a-f]%”；“[^]”與“[]”相反，用于指定不屬于范圍內(nèi)的字符。如[^abcdef]表示不屬于abcdef集合中的字符。第7章查詢(xún)和視圖ppt課件（全）【例7-17】在students表中查詢(xún)姓“趙”的學(xué)生信息。T-SQL語(yǔ)句為： SELECT*FROMstudentWHEREsnamelikeN'趙%'第7章查詢(xún)和視圖ppt課件（全）4.確定范圍T-SQL中與范圍有關(guān)的關(guān)鍵字有兩個(gè)：BETWEEN和IN。當(dāng)要查詢(xún)的條件是某個(gè)值的范圍時(shí)，使用BETWEEN…AND…來(lái)指出查詢(xún)范圍。其中，AND的左端給出查詢(xún)范圍的下限，AND的右端給出查詢(xún)范圍的上限。【例7-18】在sc表中，查詢(xún)成績(jī)?cè)?0到80分的學(xué)生情況T-SQL語(yǔ)句為： SELECT*FROMscWHEREgradebetween60and80關(guān)鍵字IN用來(lái)表示查詢(xún)范圍屬于指定的集合。集合中列出所有可能的值，當(dāng)表中的值與集合中的任意一個(gè)值匹配時(shí)，即滿(mǎn)足條件?！纠?-19】在student表中查詢(xún)IM系和CAST系同學(xué)的情況。T-SQL語(yǔ)句為： SELECT*FROMstudentWHEREsdeptIN(N'CSAT',N'IM')該語(yǔ)句等價(jià)于語(yǔ)句： SELECT*FROMstudentWHERESdept=N'CSAT'orSdept=N'IM'執(zhí)行結(jié)果如圖7-13所示第7章查詢(xún)和視圖ppt課件（全）5.涉及空值NULL的查詢(xún)值為“空”并非沒(méi)有值，而是一個(gè)特殊的符號(hào)“NULL”。一個(gè)字段是否允許為空，需要在建立表的結(jié)構(gòu)時(shí)設(shè)置。當(dāng)要判斷一個(gè)表達(dá)式的值是否為空值時(shí)，使用ISNULL關(guān)鍵字【例7-20】查詢(xún)?nèi)鄙賳慰瞥煽?jī)的學(xué)生的信息。T-SQL語(yǔ)句為： SELECT*FROMscWHEREgradeISNULL第7章查詢(xún)和視圖ppt課件（全）7.2.3ORDERBY子句的使用

利用ORDERBY子句可以對(duì)查詢(xún)的結(jié)果按照指定字段進(jìn)行排序。ORDERBY子句格式為：ORDERBY排序表達(dá)式[ASC|DESC]其中ASC代表升序，DESC表示降序，默認(rèn)時(shí)為升序排列。對(duì)數(shù)據(jù)類(lèi)型為T(mén)EXT、NTEXT和IMAGE的字段不能使用ORDERBY進(jìn)行排序。【例7-21】查詢(xún)student表中全體女學(xué)生的情況，要求結(jié)果按照年齡降序排列。T-SQL語(yǔ)句為：SELECT*FROMstudentWHEREssex=N'女'ORDERBYsageDESC第7章查詢(xún)和視圖ppt課件（全）7.2.4GROUPBY子句的使用在T-SQL中經(jīng)常使用聚合函數(shù)和GROUPBY子句來(lái)實(shí)現(xiàn)統(tǒng)計(jì)計(jì)算。1.聚合函數(shù)聚合函數(shù)用于處理單個(gè)列中所選的全部值，并生成一個(gè)結(jié)果值。常用的聚合函數(shù)有：第7章查詢(xún)和視圖ppt課件（全）【例7-22】統(tǒng)計(jì)查詢(xún)student表中學(xué)生的總?cè)藬?shù)。T-SQL語(yǔ)句為：

SELECTCOUNT(*)FROMstudent【例7-23】查詢(xún)選修01課程學(xué)生的最高分，最低分和平均分T-SQL語(yǔ)句為：

SELECTMAX(grade)as'01課程最高分',MIN(grade)as'01課程最低分',AVG(grade)as'01課程平均分' FROMscWHEREcno='01'第7章查詢(xún)和視圖ppt課件（全）2.GROUPBY子句GROUPBY子句用于對(duì)表或視圖中的數(shù)據(jù)按字段分組，還可以利用HAVING短語(yǔ)按照一定的條件對(duì)分組后的數(shù)據(jù)進(jìn)行篩選。GROUPBY子句格式為：GROUPBY[ALL]分組表達(dá)式[HAVING查詢(xún)條件]需要注意的是，當(dāng)使用HAVING短語(yǔ)指定篩選條件時(shí)，HAVING短語(yǔ)必須與GROUPBY配合使用。【例7-24】求student表中各個(gè)專(zhuān)業(yè)的學(xué)生人數(shù)。T-SQL語(yǔ)句為：

SELECTsdept,COUNT(*)as'學(xué)生人數(shù)'FROMstudent GROUPBYsdept【例7-25】查詢(xún)sc表中選修了兩門(mén)課并且成績(jī)均不及格的學(xué)生的學(xué)號(hào)分析：我們將SC表中的成績(jī)不及格的學(xué)生按照學(xué)號(hào)分組，對(duì)各個(gè)分組進(jìn)行篩選，找出紀(jì)錄數(shù)大于2的學(xué)生學(xué)號(hào)，進(jìn)行結(jié)果輸出。T-SQL語(yǔ)句為：

SELECTsnoFROMscWHEREgrade<60 GROUPBYsnoHAVINGCOUNT(*)>2第7章查詢(xún)和視圖ppt課件（全）7.2.4表的連接(JOIN)以上我們介紹的都是單表查詢(xún)，在實(shí)際應(yīng)用中，經(jīng)常需要把兩個(gè)或者多個(gè)表按照給定的條件進(jìn)行連接而形成新的表。多表連接使用FROM子句指定多個(gè)表，連接條件指定各列之間（每個(gè)表至少一列）進(jìn)行連接的關(guān)系。連接條件中的列必須具有一致的數(shù)據(jù)類(lèi)型。在T-SQL中，連接查詢(xún)有兩大類(lèi)實(shí)現(xiàn)形式，一類(lèi)是使用等值連接形式，另一類(lèi)是使用關(guān)鍵字JOIN連接形式。第7章查詢(xún)和視圖ppt課件（全）1.等值連接等值連接的連接條件是在WHERE子句中給出的，只有滿(mǎn)足連接條件的行才會(huì)出現(xiàn)在查詢(xún)結(jié)果中。這種形式也被稱(chēng)為連接謂詞表示形式，是SQL語(yǔ)言早期的連接形式。等值連接的連接條件格式：表名1.字段名1=表名2.字段名2【例7-26】從student表和sc表中，查詢(xún)所有不及格的學(xué)生的學(xué)號(hào)、學(xué)生姓名、所屬院系、所選的課程號(hào)和成績(jī)。T-SQL語(yǔ)句為：

SELECTstudent.sno,sname,sdept,cno,grade FROMstudent,sc WHEREstudent.sno=sc.snoandgrade<60第7章查詢(xún)和視圖ppt課件（全）說(shuō)明：本例中，WHERE子句既有查詢(xún)條件（Grade<60），又有連接條件（student.Sno=sc.Sno）；連接條件中的兩個(gè)字段稱(chēng)為連接字段，它們必須是具有一致的數(shù)據(jù)類(lèi)型。如本例中連接字段分別為student表的sno字段和sc表中的sno字段；在單表查詢(xún)中，所有的字段都來(lái)自于同一張表，故在SELECT語(yǔ)句中不需要特別說(shuō)明。但是在多表查詢(xún)中，有的字段（如sno字段）在幾個(gè)表中都出現(xiàn)了，引用時(shí)就必須說(shuō)明其來(lái)自那個(gè)表，否則就可能引起混亂，造成語(yǔ)法錯(cuò)誤；連接條件中使用的比較符可以是<、<=、=、>、>=、!=、<>、!<和!>。當(dāng)比較符為“=”時(shí)，就是等值連接。第7章查詢(xún)和視圖ppt課件（全）2.JOIN關(guān)鍵字連接多個(gè)表T-SQL擴(kuò)展了連接的形式，引入了JOIN…ON關(guān)鍵字連接形式，從而使表的連接運(yùn)算能力得到了增強(qiáng)。JOIN…ON關(guān)鍵字放在FROM子句中，命令格式如下：FROM<表名1>[INNER]|{|LEFT|RIGHT|FULL}[OUTER]]JOIN<表名2>ON<連接條件>這種連接形式通過(guò)FROM給出連接類(lèi)型，用JOIN表示連接，用ON短語(yǔ)給出連接條件。JOIN提供了多種類(lèi)型的連接方法：內(nèi)連接、外連接和交叉連接。它們之間的區(qū)別在于：從相互關(guān)聯(lián)的不同表中選擇用于連接的行時(shí)所采用的方法不同。第7章查詢(xún)和視圖ppt課件（全）內(nèi)連接INNER查詢(xún)

內(nèi)連接是最常見(jiàn)的一種連接，也被稱(chēng)為普通連接或自然連接，它是系統(tǒng)默認(rèn)形式，在實(shí)際使用中可以省略INNER關(guān)鍵字。例7-26也可以改寫(xiě)成如下形式實(shí)現(xiàn)：SELECTstudent.sno,sname,sdept,cno,gradeFROMstudentJOINscONstudent.sno=sc.snoWHEREgrade<60使用JOIN…ON連接詞替換了上例中的WHERE子句的連接條件。內(nèi)連接與等值連接效果相同相同，僅當(dāng)兩個(gè)表中都至少有一行符合連接條件時(shí)，內(nèi)連接才返回行。第7章查詢(xún)和視圖ppt課件（全）外連接OUTER查詢(xún)外連接是指連接關(guān)鍵字JOIN后面表中指定列連接在前一表中指定列的左邊或者右邊，如果兩表中指定列沒(méi)有匹配行，則返回空值。外連接的結(jié)果不但包含滿(mǎn)足連接條件的行，還包含相應(yīng)表中的所有行。外連接有三種形式，其中的OUTER可以省略： （1）左外連接（LEFTOUTERJOIN或LEFTJOIN）：包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部滿(mǎn)足條件的行。 （2）右外連接（RIGHTOUTERJOIN或RIGHTJOIN）：包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部滿(mǎn)足條件的行。 （3）全外連接（FULLOUTERJOIN或FULLJOIN）：包含左、右兩個(gè)表的全部行，不管另外一邊的表中是否存在與它們匹配的行，其實(shí)全外連接將返回兩個(gè)表的所有行。在現(xiàn)實(shí)生活中，參照完整性約束可以減少對(duì)于全外連接的使用，一般情況下左外連接就足夠了。但當(dāng)在數(shù)據(jù)庫(kù)中沒(méi)有利用清晰、規(guī)范的約束來(lái)防范錯(cuò)誤數(shù)據(jù)情況下，全外連接就變得非常有用了，你可以使用它來(lái)清理數(shù)據(jù)庫(kù)中的數(shù)據(jù)。第7章查詢(xún)和視圖ppt課件（全）【例7-27】分別用左外連接和右外連接查詢(xún)student表和sc表中的學(xué)生的Sno、Cno、Sname和Grade。比較查詢(xún)結(jié)果的區(qū)別并分析。左外連接T-SQL語(yǔ)句為：

SELECTstudent.sno,cno,sname,grade FROMstudentLEFTJOINscONsc.sno=student.sno右外連接T-SQL語(yǔ)句為：

SELECTstudent.sno,cno,sname,grade FROMstudentRIGHTJOINscONsc.sno=student.sno第7章查詢(xún)和視圖ppt課件（全）交叉連接(CROSSJOIN)交叉連接即兩個(gè)表的笛卡爾積，返回結(jié)果是由第一個(gè)表的每行與第二個(gè)表的所有行組合后形成的表，因此，數(shù)據(jù)行數(shù)等于第一個(gè)表中符合查詢(xún)條件的數(shù)據(jù)行數(shù)乘以第二個(gè)表中符合查詢(xún)條件的數(shù)據(jù)行數(shù)。交叉連接關(guān)鍵字CROSSJOIN后不跟ON短語(yǔ)引出的連接條件?！纠?-28】交叉連接student和sc兩表，查看新表的行數(shù)。T-SQL語(yǔ)句為：

SELECT*FROMstudent SELECT*FROMsc SELECTstudent.*,sc.*FROMstudentCROSSJOINsc執(zhí)行結(jié)果如圖7-20所示，可以看到，student表有239行，sc表有929行，因此，兩表相乘有222031行。第7章查詢(xún)和視圖ppt課件（全）7.2.5子查詢(xún)?cè)赟ELECT查詢(xún)語(yǔ)句中，子查詢(xún)也稱(chēng)為嵌套查詢(xún)，是一個(gè)嵌套在SELECT中的查詢(xún)語(yǔ)句。處于內(nèi)層的查詢(xún)稱(chēng)為子查詢(xún)，處于外層的查詢(xún)稱(chēng)為父查詢(xún)。任何允許使用表達(dá)式的地方都可以使用子查詢(xún)。T-SQL語(yǔ)句支持子查詢(xún)，正是SQL結(jié)構(gòu)化的具體體現(xiàn)。子查詢(xún)SELECT語(yǔ)句必須放在括號(hào)中，子查詢(xún)只返回一行數(shù)據(jù)，并且返回的數(shù)據(jù)常常也只有一列。可以用子查詢(xún)來(lái)檢查或者設(shè)置變量和列的值，或者用子查詢(xún)來(lái)測(cè)試數(shù)據(jù)行是否存在于WHERE子句中。需要注意的是ORDERBY子句只能對(duì)最終查詢(xún)結(jié)果排序，即在子查詢(xún)中的SELECT語(yǔ)句中不能使用ORDERBY子句。第7章查詢(xún)和視圖ppt課件（全）1.使用IN關(guān)鍵字的子查詢(xún)由于子查詢(xún)的結(jié)果是紀(jì)錄的集合，故常使用謂詞IN來(lái)實(shí)現(xiàn)。IN謂詞用于判斷一個(gè)給定值是否在子查詢(xún)結(jié)果集中。當(dāng)父查詢(xún)表達(dá)式與子查詢(xún)的結(jié)果集中的某個(gè)值相等時(shí)，返回TURE，否則返回FALSE。同時(shí)，可以在IN關(guān)鍵字之前使用NOT，表示表達(dá)式的值不在查詢(xún)結(jié)果集中?！纠?-29】查詢(xún)至少有一門(mén)課程不及格的學(xué)生的信息。T-SQL語(yǔ)句為：

SELECT*FROMstudentWHEREsnoIN (SELECTsnoFROMscWHEREgrade<60)在執(zhí)行包含子查詢(xún)的SELECT語(yǔ)句時(shí)，系統(tǒng)先執(zhí)行子查詢(xún)，產(chǎn)生一個(gè)結(jié)果表。在本例中，系統(tǒng)先執(zhí)行子查詢(xún)，得到所有不及格學(xué)生的sno，再執(zhí)行父查詢(xún)，若student表中某行的sno值等于子查詢(xún)結(jié)果表中的任一值，則該行就被選擇。第7章查詢(xún)和視圖ppt課件（全）2.使用比較運(yùn)算符的子查詢(xún)使用帶有比較運(yùn)算符的子查詢(xún)，是當(dāng)用戶(hù)能確切知道子查詢(xún)返回的是單值時(shí)，可以在父查詢(xún)WHERE子句中，使用比較運(yùn)算符進(jìn)行比較查詢(xún)。這種查詢(xún)可以認(rèn)為是IN子查詢(xún)的擴(kuò)展?！纠?-30】從sc表中查詢(xún)王小華同學(xué)的考試成績(jī)信息，顯示sc表所有字段T-SQL語(yǔ)句為：

SELECT*FROMscWHEREsno= (SELECTsnoFROMstudentWHEREsname=N'王小華')我們通過(guò)查詢(xún)語(yǔ)句： SELECT*FROMstudentWHEREsname=N'王小華'能夠驗(yàn)證，查詢(xún)結(jié)果顯示的是王小華同學(xué)的考試成績(jī)信息。第7章查詢(xún)和視圖ppt課件（全）3.帶有ANY、SOME或ALL關(guān)鍵字的子查詢(xún)

使用ANY、SOME或ALL關(guān)鍵字對(duì)子查詢(xún)進(jìn)行限制。ALL代表所有值，ALL指定的表達(dá)式要與子查詢(xún)結(jié)果集中的每個(gè)值都進(jìn)行比較，當(dāng)表達(dá)式與每個(gè)值都滿(mǎn)足比較的關(guān)系是，才返回TRUE，否則返回FALSE。SOME或ANY代表某些或者某個(gè)值，表達(dá)式只要與子查詢(xún)結(jié)果集中的某個(gè)值滿(mǎn)足比較的關(guān)系時(shí)，就返回TRUE，否則返回FALSE。【例7-31】查詢(xún)考試成績(jī)比王小華同學(xué)高的學(xué)生信息。在上題的基礎(chǔ)上，我們進(jìn)一步進(jìn)行查詢(xún)嵌套：如果使用ANY，則查詢(xún)結(jié)果是只要比王小華同學(xué)任一門(mén)成績(jī)高的學(xué)生信息；使用ALL，則查詢(xún)結(jié)果是比王小華同學(xué)所有的成績(jī)都要高的學(xué)生信息。T-SQL語(yǔ)句為：

SELECT*FROMscWHEREgrade>ALL (SELECTgradeFROMscWHEREsno= (SELECTsnoFROMstudentWHEREsname=N'王小華')) go SELECT*FROMscWHEREgrade>ANY (SELECTgradeFROMscWHEREsno= (SELECTsnoFROMstudentWHEREsname=N'王小華')) go第7章查詢(xún)和視圖ppt課件（全）4.使用EXISTS的子查詢(xún)EXISTS稱(chēng)為存在量詞，WHERE子句中使用EXISTS表示當(dāng)子查詢(xún)的結(jié)果非空時(shí)，條件為T(mén)RUE，反之則為FALSE。EXISTS前面也可以加NOT，表示檢測(cè)條件為“不存在”。EXISTS語(yǔ)句與IN非常類(lèi)似，它們都根據(jù)來(lái)自子查詢(xún)的數(shù)據(jù)子集測(cè)試列的值。不同之處在于，EXISTS使用聯(lián)接將列的值與子查詢(xún)中的列聯(lián)接起來(lái)，而IN不需要聯(lián)接，它直接根據(jù)一組以逗號(hào)分隔的值進(jìn)行比較?！纠?-32】查詢(xún)沒(méi)有選修01課程的學(xué)生的信息T-SQL語(yǔ)句為：

SELECT*FROMstudentWHERENOTEXISTS (SELECT*FROMscWHEREsno=student.snoANDcno='01')第7章查詢(xún)和視圖ppt課件（全）7.2.6SELECT語(yǔ)句的其他子句

除了上面介紹的SELECT語(yǔ)句的主要子句外，SELECT還有2個(gè)常用子句：INTO子句UNION子句使用它們可以實(shí)現(xiàn)更為完備的功能。第7章查詢(xún)和視圖ppt課件（全）1.INTO子句

SELECT語(yǔ)句使用INTO子句來(lái)表明查詢(xún)結(jié)果的去向。我們?nèi)绻枰獙⒉樵?xún)得到的結(jié)果存入新的數(shù)據(jù)表中，這時(shí)就需要使用語(yǔ)句：INTO<新表名>，來(lái)創(chuàng)建新表，存儲(chǔ)紀(jì)錄?！纠?-33】將student表中所有年齡為19歲的學(xué)生信息存入新表age19中。T-SQL語(yǔ)句為：

SELECT*INTOage19FROMstudentWHEREsage=19 GO SELECT*FROMage19第7章查詢(xún)和視圖ppt課件（全）2.UNION子句T-SQL支持集合的并（UNION）運(yùn)算，執(zhí)行聯(lián)合查詢(xún)。需要注意的是，參與并運(yùn)算操作的兩個(gè)查詢(xún)語(yǔ)句，其結(jié)果應(yīng)具有相同的字段個(gè)數(shù)，以及相同的對(duì)應(yīng)字段的數(shù)據(jù)類(lèi)型。默認(rèn)情況下，UNION將從結(jié)果集中刪除重復(fù)的行。如果使用ALL關(guān)鍵字，那么結(jié)果中將包含所有行而不刪除重復(fù)的行?！纠?-34】查詢(xún)CSAT專(zhuān)業(yè)的女學(xué)生和IM專(zhuān)業(yè)的男學(xué)生信息T-SQL語(yǔ)句為：

SELECT*FROMstudentwheresdept=N'CSAT'andssex=N'女' UNION SELECT*FROMstudentwheresdept=N'IM'andssex=N'男'第7章查詢(xún)和視圖ppt課件（全）7.3視圖7.3.1視圖概述視圖是一種數(shù)據(jù)庫(kù)對(duì)象，它是從一個(gè)或多個(gè)表或視圖中導(dǎo)出的虛表，即它可以從一個(gè)或多個(gè)表中的一個(gè)或多個(gè)列中提取數(shù)據(jù)，并按照表的組成行和列來(lái)顯示這些信息。視圖與真實(shí)的表也有很多類(lèi)似的地方。例如，視圖也是由若干個(gè)字段(列)和一些記錄組成的，在某些條件滿(mǎn)足時(shí)，還可以通過(guò)視圖來(lái)插入、更改和刪除數(shù)據(jù)。第7章查詢(xún)和視圖ppt課件（全）使用視圖有很多優(yōu)點(diǎn)，這些優(yōu)點(diǎn)主要如下：(1)視圖是作為一個(gè)數(shù)據(jù)庫(kù)對(duì)象存在數(shù)據(jù)庫(kù)中的，便于管理和維護(hù)，而且視圖象表一樣還可以用在查詢(xún)語(yǔ)句中，從而簡(jiǎn)化了檢索數(shù)據(jù)的操作。(2)可以定制允許用戶(hù)查看哪些數(shù)據(jù)，讓用戶(hù)通過(guò)視圖來(lái)訪問(wèn)表中的特定字段和記錄，而不對(duì)用戶(hù)授予直接訪問(wèn)數(shù)據(jù)庫(kù)表的權(quán)限。(3)可以針對(duì)不同的用戶(hù)定義不同的視圖，在用戶(hù)視圖上不包括機(jī)密數(shù)據(jù)字段，從而自動(dòng)提供對(duì)機(jī)密數(shù)據(jù)的保護(hù)。(4)可以使用視圖將數(shù)據(jù)導(dǎo)出到其他的應(yīng)用程序。(5)允許用戶(hù)以不同的方式查看數(shù)據(jù)，即使在用戶(hù)同時(shí)使用相同的數(shù)據(jù)也可如此。第7章查詢(xún)和視圖ppt課件（全）7.3.2視圖的創(chuàng)建 1.使用SQLServerManagementStudio創(chuàng)建視圖第7章查詢(xún)和視圖ppt課件（全） 2.使用Transact-SQL語(yǔ)句中的CREATEVIEW命令創(chuàng)建視圖使用CREATEVIEW語(yǔ)句創(chuàng)建視圖的基本語(yǔ)法形式如下：CREATEVIEW[schema_name.]view_name[(column[,···,n])][WITH[ENCRYPTION][SCHEMABINDING][VIEW_METADATA]]ASselect_statement[;][WITHCHECKOPTION]第7章查詢(xún)和視圖ppt課件（全）例【7-30】：選擇表s和sc中的部分字段和(name,age,sex,cno,score)記錄來(lái)創(chuàng)建一個(gè)視圖，限制表s中的記錄是計(jì)算機(jī)系的記錄集合，視圖定義為view_s。T-SQL語(yǔ)句如下：CREATEVIEWview_sASSELECT,s.age,s.sex,o,sc.scoreFROMs,scWHEREs.sno=sc.snoANDs.dept=‘計(jì)算機(jī)’；第7章查詢(xún)和視圖ppt課件（全）7.3.3視圖的修改、查看和重命名1.視圖的修改(1)使用SSMS圖形化界面修改視圖在SSMS圖形化界面中，右擊要修改的視圖，從彈出的快捷菜單中選擇“修改”命令，出現(xiàn)原視圖。該視圖與創(chuàng)建視圖時(shí)相同，可以按照創(chuàng)建視圖的方法修改視圖。(2)使用T-SQL語(yǔ)句修改視圖首先必須擁有使用視圖的權(quán)限，然后才能使用ALTERVIEW語(yǔ)句第7章查詢(xún)和視圖ppt課件（全）例【7-31】：修改視圖V_employees(number,name,age)，在該視圖中增加一個(gè)新的字段employees.salary，并且定義一個(gè)新的字段名稱(chēng)e_salary。T-SQL語(yǔ)句如下：ALTERVIEWdbo.V_employees(number,name,age,e_salary)ASSELECTnumber,name,age,salaryFROMemployees；第7章查詢(xún)和視圖ppt課件（全） 2.查看視圖信息(1)使用SSMS圖形化界面查看視圖在SSMS中，右擊某個(gè)視圖的名稱(chēng)，從彈出的快捷菜單中選擇“選擇前1000行”或“編輯前200行”命令，在SQLServer圖形化界面中就會(huì)顯示該視圖輸出的相應(yīng)數(shù)據(jù)第7章查詢(xún)和視圖ppt課件（全）(2)捕獲視圖信息在sys.Views視圖中，每個(gè)視圖的對(duì)象在該sys