數(shù)據(jù)庫基礎(chǔ)PPT第5章 數(shù)據(jù)庫的查詢和視圖

第5章數(shù)據(jù)庫的查詢和視圖5.1簡(jiǎn)單select查詢5.2select語句的統(tǒng)計(jì)功能5.3select語句中的多表連接5.4子查詢5.5數(shù)據(jù)庫視圖Page129十月2023通過學(xué)習(xí)本章，讀者應(yīng)掌握以下內(nèi)容：掌握各種查詢方法的語法格式和使用，包括單表?xiàng)l件查詢、單表多條件查詢、多表多條件查詢、嵌套查詢，并能對(duì)查詢結(jié)果進(jìn)行排序、分組和匯總操作。掌握視圖的建立、修改、使用和刪除操作；并能通過視圖查詢數(shù)據(jù)，修改數(shù)據(jù)，更新數(shù)據(jù)和刪除數(shù)據(jù)。Page229十月20235.1簡(jiǎn)單SELECT語句5.1.1SELECT語句概述查詢是SQL語言中最主要、最核心的部分。查詢語言用來對(duì)已經(jīng)存在于數(shù)據(jù)庫的數(shù)據(jù)按照特定組合、條件表達(dá)式或者一定次序進(jìn)行檢索。數(shù)據(jù)查詢命令是SQL最常用的命令。Page329十月20235.1.2完整的SELECT語句的基本語法格式

SELECT語句的完整語法格式如下：

SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_list [INTOnew_table_name] FROM<table_name/view_name> [WHEREsearch_condition] [GROUPBYgroup_by_expression] [HAVINGsearch_condition] [ORDERBYorder_expression[ASC|DESC]][,…n]

其中各子句說明如下：(1)ALL。表示輸出所有記錄，包括重復(fù)記錄；distinct表示去掉重復(fù)的記錄。(2)TOPn。查詢結(jié)果只顯示表中前n條記錄，TOPnPERCENT關(guān)鍵字，則查詢結(jié)果只顯示前面n%條記錄。(3)select_list。所要查詢的選項(xiàng)的集合，多個(gè)選項(xiàng)之間用逗號(hào)分開。(4)FROMtable_name/view_name。結(jié)果集數(shù)據(jù)來源于哪些表或視圖，F(xiàn)ROM子句還可包含連接的定義。(5)INTOnew_table_name。用于指定使用結(jié)果集來創(chuàng)建一個(gè)新表，new_table_name是新表名。(6)WHEREsearch_condition。是一個(gè)條件篩選，只有符合條件的行才向結(jié)果集提供數(shù)據(jù)，不符合條件的行中的數(shù)據(jù)不會(huì)被使用。(7)GROUPBYgroup_by_expression。根據(jù)group_by_expression列中的值將查詢結(jié)果進(jìn)行分組。(8)HAVINGsearch_condition。應(yīng)用于結(jié)果集的附加篩選。邏輯上講，HAVING子句從中間結(jié)果集對(duì)行進(jìn)行篩選，這些中間結(jié)果集是用SELECT語句中的FROM、WHERE或GROUPBY子句創(chuàng)建的。HAVING子句通常與GROUPBY子句一起使用。(9)ORDERBYorder_expression[ASC|DESC]。定義結(jié)果集中的行排列的順序。order_expression指定組成排序列表的結(jié)果集的列。ASC指定行按升序排序，DESC指定行按降序排序。SELECT語句可以完成以下工作：投影。用來選擇表中的列。選擇。用來選擇表中的行。連接。將兩個(gè)關(guān)系拼接成一個(gè)關(guān)系。5.1.3基本的SELECT語句

SELECT語句的基本形式如下：

SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_list FROM<table_name/view_name> [WHEREsearch_condition]【例5.1】在“學(xué)生信息”表中查詢學(xué)生的學(xué)號(hào)及姓名。

USEjxglGOSELECTstu_id,stu_name FROM學(xué)生信息GO2．查詢表中全部列（*）將表中所有屬性都選出來，可以有兩種方法。一種方法是在SELECT命令后面列出所有列名。如果列的顯示順序與其在基表中的順序相同，也可以簡(jiǎn)單地將<字段列表>簡(jiǎn)寫為“*”?！纠?.2】查詢“課程”表的所有信息。

USEjxglGOSELECT* FROM課程GOPage829十月20233．設(shè)置字段別名列表達(dá)式[as]別名或：別名=列表達(dá)式【例5.3】查詢jxgl數(shù)據(jù)庫的“課程”表，列出表中的所有記錄，每個(gè)記錄名稱依次為課程編號(hào)，課程名稱，課程學(xué)分及學(xué)時(shí)數(shù)。

USEjxglGOSELECTcourse_idAS課程編號(hào),course_nameAS課程名稱,course_creditAS課程學(xué)分,course_hourAS學(xué)時(shí)數(shù)

FROM課程

Page929十月20234．查詢經(jīng)過計(jì)算的值SELECT子句的<字段列表>不僅可以是表中的屬性列，也可以是表達(dá)式，包括字符串常量、函數(shù)等。其語法格式為：計(jì)算字段名=表達(dá)式【例5.4】查詢“學(xué)生信息”表中所有學(xué)生的學(xué)號(hào)、姓名及年齡。USEjxglGOSELECTstu_idas學(xué)號(hào),stu_nameas姓名,年齡=DATEDIFF(YY,stu_birth,GETDATE())FROM學(xué)生信息GO5．返回全部記錄（ALL）要返回所有記錄可在SELECT后使用ALL，ALL是默認(rèn)設(shè)置，因此也可以省略?！纠?.5】查詢“學(xué)生信息”表中所有學(xué)生的系別代碼。

USEjxglGOSELECTdept_id FROM學(xué)生信息GOPage1129十月20236．過濾重復(fù)記錄（DISTINCT）在例5.5的執(zhí)行結(jié)果集中顯示重復(fù)行。如果讓重復(fù)行只顯示一次，需在SELECT子句中用DISTINCT指定在結(jié)果集中只能顯示唯一行?！纠?.6】查詢“學(xué)生信息”表中的學(xué)生所在系別有哪些(重復(fù)專業(yè)只顯示一次)。USEjxglGOSELECTDISTINCTdept_id FROM學(xué)生信息GOPage1229十月20237．僅返回前面若干條記錄其語法格式如下：SELECT[TOPn︱TOPnPERCENT]列名l[，．．．n]FROM表名其中：TOPn表示返回最前面的n行，n表示返回的行數(shù)。TOPnPERCENT表示返回的最前面的n％行。【例5.7】查詢“學(xué)生信息”表中前5條記錄。

USEjxglGOSELECTTOP5* FROM學(xué)生信息GOPage1329十月2023【例5.8】查詢xs表中前面10%行記錄。

USEjxglGOSELECTTOP10PERCENT* FROM學(xué)生信息GOPage1429十月20235.1.4INTO子句

使用INTO子句允許用戶定義一個(gè)新表，并且把SELECT子句的數(shù)據(jù)插入到新表中，其語法格式如下。SELECT<字段列表> INTO新表名

FROM<表名列表> WHERE查詢條件使用INTO子句插入數(shù)據(jù)時(shí)，應(yīng)注意以下幾點(diǎn)：（1）新表不能存在，否則會(huì)產(chǎn)生錯(cuò)誤信息。（2）新表中的列和行是基于查詢結(jié)果集的。（3）使用該子句必須在目的數(shù)據(jù)庫中具有CREATETABLE權(quán)限。（4）如果新表名稱的開頭為“#”，則生成的是臨時(shí)表。注意：使用INTO子句，通過在WHERE子句中FALSE條件，可以創(chuàng)建一個(gè)和源表結(jié)構(gòu)相同的空表。Page1529十月2023【例5.9】創(chuàng)建一個(gè)和“學(xué)生信息”表結(jié)構(gòu)相同的xs_new表。

USEjxglGOSELECT*INTOxs_new FROM學(xué)生信息 WHERE6>8設(shè)置“WHERE6>8”這樣一個(gè)明顯為邏輯否的條件的目的是為了只保留“學(xué)生信息”表的結(jié)構(gòu)，而不返回任何記錄。Page1629十月2023【例5.10】查詢所有女生的信息并將結(jié)果保存在名為“女生表”的數(shù)據(jù)表中。

USEjxglGOSELECT*INTO女生表FROM學(xué)生信息WHEREstu_sex='女'

【例5.11】查詢所有男生的信息并將結(jié)果存入臨時(shí)表中

USEjxglSELECT* INTO#男生表 FROM學(xué)生信息 WHEREstu_sex='男'Page1729十月20235.1.5使用WHERE子句

其語法格式如下：SELECT列名1[,…列名n]FROM表名WHERE條件表達(dá)式使用WHERE子句可以限制查詢的記錄范圍。在使用時(shí)，WHERE子句必須緊跟在FROM子句后面。WHERE子句中的條件是—個(gè)邏輯表達(dá)式，其中可以包含的運(yùn)算符見表6-1。Page1829十月2023Page1929十月2023運(yùn)算符和連接謂詞用

途=，<>，>，>=，<，<=，!=比較大小AND、OR、NOT設(shè)置多重條件BETWEEN…AND…確定范圍IN、NOTIN、ANY︱SOME、ALL確定集合LIKE字符匹配，用于模糊查詢IS[NOT]NULL測(cè)試空值1．比較表達(dá)式作查詢條件比較表達(dá)式是邏輯表達(dá)式的一種，使用比較表達(dá)式作為查詢條件的一般表達(dá)形式是：表達(dá)式比較運(yùn)算符表達(dá)式其中：表達(dá)式為：常量、變量和列表達(dá)式的任意有效組合。比較運(yùn)算符包括：=（等于）、<（小于）、>（大于）、<>（不等于）、!>（不大于）、!<（不小于）、>=（大于等于）、<=（小于等于）、!=（不等于）。Page2029十月2023【例5.12】查詢年齡在20歲以下的學(xué)生。USEjxglGOSELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE()) FROM學(xué)生信息 WHEREDATEDIFF(YEAR,stu_birth,GETDATE())<20GOPage2129十月20232．邏輯表達(dá)式作查詢條件使用邏輯表達(dá)式作為查詢條件的一般表達(dá)形式是：表達(dá)式1AND|OR表達(dá)式2，或NOT表達(dá)式【例5.13】查詢年齡為20歲且性別為“女”的學(xué)生。

USEjxglGOSELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE()) FROM學(xué)生信息 WHEREDATEDIFF(YEAR,stu_birth,GETDATE())=20ANDstu_sex='女'Page2229十月20233．使用（NOT）BETWEEN…AND關(guān)鍵字其語法格式為：表達(dá)式[NOT]BETWEEN表達(dá)式lAND表達(dá)式2謂詞可以用來查找屬性值在（或不在）指定范圍內(nèi)的元組，其中BETWEEN后是范圍的下限（即低值），AND后是范圍的上限（即高值）。使用BETWEEN限制查詢數(shù)據(jù)范圍時(shí)同時(shí)包括了邊界值，而使用NOTBETWEEN進(jìn)行查詢時(shí)沒有包括邊界值。【例5.14】查詢年齡在19～20歲之間的女學(xué)生的學(xué)號(hào)、姓名和年齡。SELECTstu_name,stu_sex,age=DATEDIFF(YEAR,stu_birth,GETDATE()) FROM學(xué)生信息WHEREDATEDIFF(YEAR,stu_birth,GETDATE())BETWEEN19AND20ANDstu_sex='女'

4．使用IN關(guān)鍵字同BETWEEN關(guān)鍵字一樣，IN的引入也是為了更方便地限制檢索數(shù)據(jù)的范圍，靈活使用IN關(guān)鍵字，可以用簡(jiǎn)潔的語句實(shí)現(xiàn)結(jié)構(gòu)復(fù)雜的查詢。語法格式為：表達(dá)式[NOT]IN（表達(dá)式1,表達(dá)式2[,…表達(dá)式n]）如果“表達(dá)式”的值是謂詞IN后面括號(hào)中列出的表達(dá)式1，表達(dá)式2,…表達(dá)式n的值之一，則條件為真?！纠?.15】查詢選修了100101或200101課程的學(xué)生的學(xué)號(hào)。

SELECTDISTINCTstu_idFROM成績(jī)WHEREcourse_idIN('100101','200101')Page2429十月20235．使用LIKE關(guān)鍵字語法格式：表達(dá)式[NOT]LIKE<匹配串>其含義是查找指定的屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個(gè)完整的字符串，也可以含有通配符。SQLServer提供了以下4種通配符供用戶靈活實(shí)現(xiàn)復(fù)雜的查詢條件。％（百分號(hào)）：表示從0到n個(gè)任意字符。_（下劃線）：表示單個(gè)的任意字符。[]（封閉方括號(hào)）：表示方括號(hào)里列出的任意一個(gè)字符。[^]：任意一個(gè)沒有在方括號(hào)里列出的字符。需要注意的是，以上所有通配符都只有在LIKE子句中才有意義，否則通配符會(huì)被當(dāng)作普通字符處理。Page2529十月2023【例5.16】查詢“王”姓學(xué)生的學(xué)號(hào)及姓名。

USEjxglGOSELECTstu_id,stu_name FROM學(xué)生信息 WHEREstu_nameLIKE'王%'GO注意：通配符和字符串必須括在單引號(hào)中。要查找通配符本身時(shí)，需將它們用方括號(hào)括起來。例如：LIKE'[[]'表示要匹配“[”。Page2629十月20236．涉及空值的查詢對(duì)于空值（NULL）要用IS進(jìn)行連接，不能用“=”代替?！纠?.17】查詢選修了課程卻沒有成績(jī)的學(xué)生的學(xué)號(hào)。

USEjxglGOSELECT* FROM成績(jī) WHEREscoreISNULLGOPage2729十月20235.1.6ORDERBY子句

對(duì)查詢的結(jié)果進(jìn)行排序，通過使用ORDERBY子句實(shí)現(xiàn)。語法格式如下：ORDERBY表達(dá)式1[ASC︱DESC][,…n]]其中，表達(dá)式給出排序依據(jù)，即按照表達(dá)式的值升序（ASC）或降序（DESC）排列查詢結(jié)果。在默認(rèn)的情況下，ORDERBY按升序進(jìn)行排列，即默認(rèn)使用的是ASC關(guān)鍵字。不能按ntext、text或image類型的列排序，因此ntext、text或image類型的列不允許出現(xiàn)在ORDERBY子句中。Page2829十月2023

【例5.18】按年齡從小到大的順序顯示女學(xué)生的姓名、性別及出生時(shí)間。

USEjxglGOSELECTstu_name,stu_sex,stu_birth FROM學(xué)生信息 WHEREstu_sex='女' ORDERBYstu_birthDESCGOPage2929十月20235.2SELECT語句的統(tǒng)計(jì)功能為了進(jìn)一步方便用戶，增強(qiáng)檢索功能，SELECT語句中的統(tǒng)計(jì)功能可以對(duì)查詢結(jié)果集進(jìn)行求和、求平均值、求最大最小值等操作。統(tǒng)計(jì)的方法是通過集合函數(shù)和GROUPBY子句來實(shí)現(xiàn)。5.2.1集合函數(shù)匯總查詢是把存儲(chǔ)在數(shù)據(jù)庫中的數(shù)據(jù)作為一個(gè)整體，對(duì)查詢結(jié)果得到的數(shù)據(jù)集合進(jìn)行匯總或求平均值等各種運(yùn)算。SQLServer提供了一系列統(tǒng)計(jì)函數(shù)，用于實(shí)現(xiàn)匯總查詢。常用的統(tǒng)計(jì)函數(shù)見表5-2。Page3029十月2023Page3129十月2023函數(shù)名功

能SUM()對(duì)數(shù)值型列或計(jì)算列求總和AVG()對(duì)數(shù)值型列或計(jì)算列求平均值MIN()返回一個(gè)數(shù)值列或數(shù)值表達(dá)式的最小值MAX()返回一個(gè)數(shù)值列或數(shù)值表達(dá)式的最大值COUNT()返回滿足SELECT語句中指定條件的記錄的個(gè)數(shù)COUNT(*)返回找到的行數(shù)【例5.19】查詢學(xué)生總?cè)藬?shù)。

圖5.19求學(xué)生總?cè)藬?shù)的

查詢結(jié)果USEjxgl

SELECT學(xué)生總?cè)藬?shù)=COUNT(*) FROM學(xué)生信息GOPage3229十月2023【例5.20】查詢選修“100101”課程的學(xué)生人數(shù)。

USEjxglGO

SELECT選課人數(shù)=COUNT(DISTINCTstu_id) FROM成績(jī) WHEREcourse_id='100101'GO

查詢結(jié)果如圖5.20所示?！纠?.21】查詢選修“100101”課程的學(xué)生的最高分?jǐn)?shù)。

USEjxglGOSELECTMAX(score)AS課程最高分 FROM成績(jī) WHEREcourse_id='100101'GO5.2.2使用GROUPBY子句

其語法格式如下。GROUPBY列名[HAVING條件表達(dá)式]HAVING條件表達(dá)式選項(xiàng)是對(duì)生成的組進(jìn)行篩選。Page3429十月2023【例5.22】在“學(xué)生信息”表中分系統(tǒng)計(jì)出男生和女生的平均年齡及人數(shù)，結(jié)果按性別排序。

USEjxglGOSELECTdept_id,stu_sex,AVG(DATEDIFF(YEAR,stu_birth,GETDATE()))AS平均年齡,COUNT(*)AS人數(shù) FROM學(xué)生信息 GROUPBYdept_id,stu_sex ORDERBYstu_sexGOPage3529十月2023【例5.23】查詢“成績(jī)”表中平均成績(jī)大于等于80分的學(xué)生的學(xué)號(hào)、平均分，并按分?jǐn)?shù)由高到低排序

USEjxglGOSELECTstu_idAS學(xué)號(hào),AVG(score)AS平均成績(jī) FROM成績(jī) GROUPBYstu_id HAVINGAVG(score)>=80 ORDERBYAVG(score)DESCGOPage3629十月2023注意：WHERE子句是對(duì)表中的記錄進(jìn)行篩選，而HAVING子句是對(duì)組內(nèi)的記錄進(jìn)行篩選，在HAVING子句中可以使用集合函數(shù)，并且其統(tǒng)計(jì)運(yùn)算的集合是組內(nèi)的所有列值，而WHERE子句中不能使用集合函數(shù)。Page3729十月20235.3SELECT語句中的多表連接

在實(shí)際工作中，我們所查詢的內(nèi)容往往是涉及多張表中的內(nèi)容。連接查詢的目的是通過加載連接字段條件將多個(gè)表連接起來，以便從多個(gè)表中檢索用戶所需要的數(shù)據(jù)。在SQLServer中連接查詢類型分為交叉連接、內(nèi)連接、外連接、自連接。連接查詢就是關(guān)系運(yùn)算的連接運(yùn)算，它是從多個(gè)數(shù)據(jù)源間（FROM）查詢滿足一定條件的記錄。Page3829十月20235.3.1交叉連接

交叉連接也稱非限制連接，它是將兩個(gè)表不加任何約束地組合起來。也就是將第一個(gè)表的所有行分別與第二個(gè)表的每一行形成一條新的記錄，連接后該結(jié)果集的行數(shù)等于兩個(gè)表的行數(shù)積，列數(shù)等于兩個(gè)表列數(shù)的和。在數(shù)學(xué)上，就是兩個(gè)表的笛卡兒積，在實(shí)際應(yīng)用中一般是沒有意義的，但在數(shù)據(jù)庫的數(shù)學(xué)模型上有重要的作用。其語法結(jié)構(gòu)如下

SELECT列名列表FROM表名1CROSSJOIN表名2

或SELECT列名列表FROM表名1,表名25.3.2內(nèi)連接

內(nèi)連接有以下兩種語法格式。

SELECT列名列表FROM表名1[INNER]JOIN表名2ON表名1.列名=表名2.列名

或

SELECT列名列表FROM表名1,表名2WHERE表名1.列名<比較運(yùn)算符>表名2.列名等值連接：在連接條件中使用等于（=）運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列。不等值連接：在連接條件使用除等于以外的其他比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<、<=、!>、!<、<>?！纠?.25】分別用等值連接和自然連接方法連接“學(xué)生信息”表和“系部”表。

等值連接方法代碼如下：USEjxglGOSELECT*FROM學(xué)生信息A,系部BWHEREA.dept_id=B.dept_id自然連接方法代碼如下：SELECT* FROM學(xué)生信息AINNERJOIN系部BONA.dept_id=B.dept_idGO5.3.3外連接

1．左外連接主表在連接符的左邊，通過左向外連接引用左表的所有行SELECT列名列表FROM表名1ASALEFT[OUTER]JOIN表名2ASBONA.列名=B.列名【例5.26】用左外連接方法連接“系部”表與“學(xué)生信息”表。

USEjxglGOSELECT* FROM系部ALEFTJOIN學(xué)生信息BONA.dept_id=B.dept_idGO2．右外連接右外連接是結(jié)果表中包括的所有行和左表中滿足連接條件的行。SELECT列名列表 FROM表名1ASARIGHT[OUTER]JOIN表名2ASBONA.列名=B.列名

【例5.27】用右外連接方法連接“學(xué)生信息”表與“系部”表。。USEjxglGOSELECT* FROM學(xué)生信息ARIGHTJOIN系部BONA.dept_id=B.dept_idGO3．全外連接全外連接結(jié)果集中除返回左表和右表內(nèi)部連接的記錄以外，還在查詢結(jié)果中返回兩個(gè)表中不符合條件的記錄，并在左表或右表的相應(yīng)列中填上NULL，BIT類型以0值填充。其語法格式如下。

SELECT列名列表 FROM表名1ASAFULL[OUTER]JOIN表名2ASBONA.列名=B.列名5.3.4自連接

不僅可以在不同的表上進(jìn)行連接操作，也可以在同一張表內(nèi)進(jìn)行自身連接，即將同一個(gè)表的不同行連接起來?！纠?.28】在“學(xué)生信息”表中查詢和“朱凡”在同一個(gè)系的所有男同學(xué)的信息。

USEjxglGOSELECTB.* FROM學(xué)生信息A,學(xué)生信息B WHEREA.stu_name='朱凡'ANDB.dept_id=A.dept_idANDB.stu_sex='男'ANDB.stu_name<>'朱凡'GO5.3.5合并查詢

合并查詢也稱聯(lián)合查詢，是將兩個(gè)或兩個(gè)以上的查詢結(jié)果合并，形成一個(gè)具有綜合信息的查詢結(jié)果。使用UNION語句可以把兩個(gè)或兩個(gè)以上的查詢結(jié)果集合并為一個(gè)結(jié)果集。其語法格式如下：

查詢語句1UNION[ALL]查詢語句2【例5.29】由“學(xué)生信息”表合并查詢女生和男生，顯示學(xué)號(hào)、姓名和性別。

SELECTstu_idAS學(xué)號(hào),stu_nameAS姓名,stu_sexAS性別FROM學(xué)生信息WHEREstu_sex='女'UNIONSELECTstu_idAS學(xué)號(hào),stu_nameAS姓名,stu_sexAS性別FROM學(xué)生信息WHEREstu_sex='男'5.4子查詢?cè)赟QL語言中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為子查詢。子查詢總是寫在圓括號(hào)中，可以用在使用表達(dá)式的任何地方。上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內(nèi)查詢或子查詢。SQL語言允許多層嵌套查詢。即一個(gè)子查詢中還可以嵌套其他子查詢。注意：子查詢的SELECT語句中不能使用ORDERBY子句，ORDERBY子句只能對(duì)最終查詢結(jié)果排序。Page4829十月20231．嵌套查詢概述嵌套子查詢的執(zhí)行不依賴于外部嵌套。其一般的求解方法是由里向外處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。有了嵌套查詢，可以有多個(gè)簡(jiǎn)單的查詢構(gòu)造復(fù)雜查詢。（嵌套不能超過32層），提高了SQL語言的表達(dá)能力，以這樣的方式來構(gòu)造查詢程序，層次清晰，易于實(shí)現(xiàn)。2．子查詢分類(1)無關(guān)子查詢(2)相關(guān)子查詢5.4.1嵌套子查詢5.4.1嵌套子查詢3．比較測(cè)試中的子查詢比較測(cè)試中的子查詢是指父查詢與子查詢之間用比較運(yùn)算符進(jìn)行連接。但是用戶必須確切地知道子查詢返回的是一個(gè)單值，否則數(shù)據(jù)庫服務(wù)器將報(bào)錯(cuò)。返回的單個(gè)值被外部查詢的比較操作（如：=、!=、<、<=、>、>=）使用，該值可以是子查詢中使用集合函數(shù)得到的值。Page5029十月2023【例5.30】查詢選修了“操作系統(tǒng)原理”課程的學(xué)生的學(xué)號(hào)及姓名。

USEjxglGOSELECT學(xué)生信息.stu_id,stu_name FROM學(xué)生信息,成績(jī) WHERE學(xué)生信息.stu_id=成績(jī).stu_idAND成績(jī).course_id= (SELECTcourse_id FROM課程 WHEREcourse_name='操作系統(tǒng)原理')GOPage5129十月2023【例5.31】在“學(xué)生信息”表中查詢和“朱凡”在同一專業(yè)的所有男同學(xué)的信息。

USEjxglGOSELECT* FROM學(xué)生信息 WHEREstu_sex='男'ANDdept_id= (SELECTdept_id FROM學(xué)生信息 WHEREstu_name='朱凡') ANDstu_name<>'朱凡'GOPage5229十月20234．集合成員測(cè)試中的子查詢集合成員測(cè)試中的子查詢是指父查詢與子查詢之間用IN或NOTIN進(jìn)行連接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中，通常子查詢的結(jié)果是一個(gè)集合。IN表示屬于，即外部查詢中用于判斷的表達(dá)式的值與子查詢返回的值列表中的一個(gè)值相等；NOTIN表示不屬于?！纠?.32】查詢成績(jī)大于90分的學(xué)生的學(xué)號(hào)及姓名。

USEjxglGOSELECTDISTINCT學(xué)生信息.stu_id,stu_name FROM學(xué)生信息 WHERE學(xué)生信息.stu_idIN(SELECTstu_idFROM成績(jī)WHEREscore>90)GOPage5329十月20235．批量比較測(cè)試中的子查詢1）使用ANY關(guān)鍵字的比較測(cè)試通過比較運(yùn)算符將一個(gè)表達(dá)式的值或列值與子查詢返回的一列值中的每一個(gè)進(jìn)行比較，只要有一次比較的結(jié)果為TRUE，則ANY測(cè)試返回TRUE。2）使用ALL關(guān)鍵字的比較測(cè)試通過比較運(yùn)算符將一個(gè)表達(dá)式的值或列值與子查詢返回的一列值中的每一個(gè)進(jìn)行比較，只要有一次比較的結(jié)果為FALSE，則ALL測(cè)試返回FALSE。ANY和ALL都用于一個(gè)值與一組值的比較，以“>”為例，ANY表示大于一組值中的任意一個(gè)，ALL表示大于一組值中的每一個(gè)。比如，>ANY（1,2,3）表示大于1；而>ALL（1,2,3）表示大于3。Page5429十月2023【例5.33】查詢所有同學(xué)中年齡最大的學(xué)生的姓名和性別。

USEjxglGOSELECTstu_name,stu_sex FROM學(xué)生信息 WHEREstu_birth<=ALL (SELECTstu_birthFROM學(xué)生信息)GOPage5529十月20235.4.2相關(guān)子查詢（EXISTS）所謂相關(guān)子查詢，是指在子查詢中，子查詢的查詢條件中引用了外層查詢表中的字段值。相關(guān)子查詢的結(jié)果集取決于外部查詢當(dāng)前的數(shù)據(jù)行，這一點(diǎn)是與嵌套子查詢不同。相關(guān)子查詢的執(zhí)行過程如下。（1）子查詢?yōu)橥獠坎樵兊拿恳恍袌?zhí)行一次，外部查詢將子查詢引用的列的值傳給子查詢。（2）如果子查詢的任何行與其匹配，則外部查詢就返回結(jié)果行。（3）再回到第一步（1），直到處理完外部表的每一行。Page5629十月2023【例5.34】查詢選修了數(shù)據(jù)庫原理課程的學(xué)生的學(xué)號(hào)及姓名

USEjxglGOSELECTstu_id,stu_name FROM學(xué)生信息 WHEREEXISTS (SELECT* FROM成績(jī) WHERE學(xué)生信息.stu_id=成績(jī).stu_idANDcourse_id=(SELECTcourse_idFROM課程WHEREcourse_name='數(shù)據(jù)庫原理'))GOPage5729十月2023【例5.35】查詢沒有選修課程的學(xué)生的學(xué)號(hào)和姓名。USEjxglGOSELECTstu_id,stu_nameFROM學(xué)生信息WHERENOTEXISTS(SELECT*FROM成績(jī)WHERE學(xué)生信息.stu_id=成績(jī).stu_id)GOPage5829十月20235.4.3使用子查詢向表中添加多條記錄使用INSERT…SELECT語句可以一次向表中添加多條記錄。其語法格式如下：INSERT表名[(字段列表)]SELECT字段列表FROM表名WHERE條件表達(dá)式Page5929十月2023【例5.36】通過子查詢語句將男生表的記錄一次添加到xs_new表中。

--查看原表中的內(nèi)容SELECT*FROM男生表GO--向其他表中插入數(shù)據(jù)INSERTxs_newSELECT*FROM男生表

GO--插入后查看表中內(nèi)容SELECT*FROMxs_newGOPage6029十月20235.5數(shù)據(jù)庫的視圖

5.5.1視圖的概述1．視圖的概念視圖是通過定義查詢語句SELETE建立的虛擬表。在視圖中被查詢的表稱為基表。與普通的數(shù)據(jù)庫表一樣，視圖由一組數(shù)據(jù)列、數(shù)據(jù)行構(gòu)成。視圖是從一個(gè)或幾個(gè)表導(dǎo)出來的表，它實(shí)際上是一個(gè)查詢結(jié)果，視圖的名字和視圖對(duì)表的查詢存儲(chǔ)在數(shù)據(jù)字典中。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時(shí)，從視圖中查詢出來的數(shù)據(jù)也隨之改變。由于視圖返回的結(jié)果集與數(shù)據(jù)表有相同的形式，因此可以像數(shù)據(jù)表一樣使用。在授權(quán)許可的情況下，用戶還可以通過視圖來插入、更改和刪除數(shù)據(jù)。通過視圖進(jìn)行查詢沒有任何限制，但對(duì)視圖的更新操作（增、刪、改）即是對(duì)視圖的基表的操作，因此有一定的聚限制條件。2．視圖的種類在SQLServer2019據(jù)庫中，視圖主要分為三種，根據(jù)實(shí)現(xiàn)的使用，標(biāo)準(zhǔn)視圖是最為常用的視圖。(1)標(biāo)準(zhǔn)視圖。標(biāo)準(zhǔn)視圖組合了一個(gè)或多個(gè)表中的數(shù)據(jù)，其重點(diǎn)放在特定數(shù)據(jù)上及簡(jiǎn)化數(shù)據(jù)操作。(2)索引視圖。一般的視圖是虛擬的，并不是實(shí)現(xiàn)保存在磁盤上的表，索引視圖是被物理化了的視圖，它已經(jīng)過計(jì)算并記錄在磁盤上。(3)分區(qū)視圖。分區(qū)視較長(zhǎng)是由在一臺(tái)或多臺(tái)服務(wù)器間水平連接一組成員表中的分區(qū)數(shù)據(jù)形成的視圖。3．視圖的作用(1)簡(jiǎn)化操作?？梢园呀?jīng)常使用的多表查詢操作定義成視圖，從而用戶不用每次都要寫復(fù)雜的查詢語句，直接使用視圖來方便地完成查詢。(2)導(dǎo)入導(dǎo)出數(shù)據(jù)。用戶可以使用復(fù)制程序把數(shù)據(jù)通過視圖導(dǎo)出。(3)數(shù)據(jù)定制與保密。重新定制數(shù)據(jù)，使得數(shù)據(jù)便于共享；合并分割數(shù)據(jù)，有利于數(shù)據(jù)輸出到應(yīng)用程序中。視圖機(jī)制能使不同的用戶以不同的方式看待同一數(shù)據(jù)。增加了安全性。(4)保證數(shù)據(jù)的邏輯獨(dú)立性。簡(jiǎn)化查詢操作，屏蔽了數(shù)據(jù)庫的復(fù)雜性。4．視圖的約束(1)可以對(duì)其他視圖創(chuàng)建視圖，SQLServer允許嵌套視圖，但嵌套不得超過32層。(2)定義視圖的查詢不能包含COMPUTE子句、COMPUTEBY子句或INTO關(guān)鍵字。(3)定義視圖的查詢不能包含ORDERBY子句，除非在SELECT語句的選擇列表中使用TOP子句。(4)定義視圖的查詢不能包含指定查詢提示的OPTION子句，也不能包含TABLESAMPLE子句。(5)不能為視圖定義全文索引。(6)不能創(chuàng)建臨時(shí)視圖，也不能對(duì)臨時(shí)表創(chuàng)建視圖。(7)不能刪除參與到使用SCHEMABINDING子句創(chuàng)建的視圖中的視圖、表或函數(shù)，除非該視圖已被刪除或更改而再具有架構(gòu)綁定。5.5.2視圖的創(chuàng)建

1．使用對(duì)象資源管理器創(chuàng)建視圖2．使用T-SQL語句創(chuàng)建視圖創(chuàng)建視圖的基本語法格式如下。

CREATEVIEW[database_name.][owner_name.]view_name[(column[,…n])][WITH{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}]]ASSelect_statement[WITHCHECKOPTION]

語法中的各參數(shù)說明如下：view_name。用于指定新建視圖的名稱。column。用于指定視圖中的字段名稱。ENCRYPTION。表示將新建視圖加密。(4)SCHEMABINDING。表示在select_statement語句中如果包含表、視圖或者用戶自定義函數(shù)，則表名、視圖名或者函數(shù)名前必須包含所有者前綴。(5)VIEW_METADATA。表示如果某一查詢中引用該視圖且要求返回瀏覽模式的元數(shù)據(jù)時(shí)，那么SQLServer將向DBLIB和OLEDBAPLS返回視圖的元數(shù)據(jù)信息。(6)Select_statement。用于創(chuàng)建視圖的SELECT語句。(7)WITHCHECKOPTION。用于強(qiáng)制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由Select_statement設(shè)置的準(zhǔn)則。創(chuàng)建視圖時(shí)應(yīng)該注意以下情況：(1)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖，在視圖中最多只能引用1024個(gè)列，視圖中記錄的數(shù)目由其基表中的記錄數(shù)決定。(2)如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。(3)如果視圖中某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者來自多個(gè)表的列名相同，則必須為列定義名稱。(4)不能在視圖上創(chuàng)建索引，不能在規(guī)則、默認(rèn)、觸發(fā)器的定義中引用視圖。(5)視圖的名稱必須遵循標(biāo)識(shí)符規(guī)則，且對(duì)每個(gè)用戶必須是唯一的。此外，該名稱不得與該用戶擁有的任何表的名稱相同。【例5.37】在jxgl數(shù)據(jù)庫中由“學(xué)生信息”、“課程”、“成績(jī)”三個(gè)表創(chuàng)建視圖“學(xué)生成績(jī)視圖”，包含的列有學(xué)號(hào)、姓名、性別、課程號(hào)、課程名和成績(jī)。代碼如下。CREATEVIEW學(xué)生成績(jī)視圖ASSELECT學(xué)生信息.stu_idAS學(xué)號(hào),stu_nameAS姓名,stu_sexAS性別,課程.course_idAS課程號(hào),course_nameAS課程名稱,scoreAS成績(jī)FROM成績(jī)INNERJOIN課程ON成績(jī).course_id=課程.course_idINNERJOIN學(xué)生信息ON成績(jī).stu_id=學(xué)生信息.stu_id【例5.38】建立計(jì)算機(jī)系學(xué)生視圖，包括學(xué)生的學(xué)號(hào)、姓名、性別、出生時(shí)間、系別，并使用WITHENCRYPTION選項(xiàng)，將視圖進(jìn)行加密。代碼如下。CREATEVIEW計(jì)算機(jī)系學(xué)生WITHENCRYPTIONASSELECTstu_idAS學(xué)號(hào),stu_nameAS姓名,stu_sexAS性別,stu_birthAS出生時(shí)間,dept_nameAS系別FROM學(xué)生信息INNERJOIN系部ON學(xué)生信息.dept_id=系部.dept_idWHEREdept_name='計(jì)算機(jī)系'5.5.3修改和查看視圖1．使用對(duì)象資源管理器修改視圖2．使用T-SQL語句修改視圖視圖建立后，可以使用ALTERVIEW語句修改視圖定義。其語法格式如下。

ALTERVIEW[database_name.][owner_name.]view_name[(column[,…n])][WITH{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}]]ASSelect_statement[WITHCHECKOPTION]【例5.39】修改學(xué)生成績(jī)視圖，使其顯示成績(jī)?cè)?0分以上的學(xué)生的成績(jī)信息。代碼如下：ALTERVIEW學(xué)生成績(jī)視圖ASSELECT學(xué)生信息.stu_idAS學(xué)號(hào),stu_nameAS姓名,stu_sexAS性別,課程.course_idAS課程號(hào),course_nameAS課程名稱,scoreAS成績(jī)FROM成績(jī)INNERJOIN課程ON成績(jī).course_id=課程.course_idINNERJOIN學(xué)生信息ON成績(jī).stu_id=