第4章 關(guān)系數(shù)據(jù)查詢語言

第4章關(guān)系數(shù)據(jù)查詢語言關(guān)系的查詢操作：關(guān)系的更新操作：選擇(Select)投影(Project)連接(Join)除(Divide)并(Union)交(Intersection)差(Difference)廣義笛卡兒積(ExtendedCartesianproduct)插入（Insert）刪除（Delete）修改（Update）關(guān)系模型三要素：數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)的約束條件關(guān)系操作4.1關(guān)系代數(shù)關(guān)系數(shù)據(jù)語言

4.1關(guān)系代數(shù)4.1關(guān)系代數(shù)

4.1.1傳統(tǒng)的集合運算1、并（Union）關(guān)系的集合表示：RSR∪S

●

●

R∩SR－S2、差（Except）3、交（Intersect）并、交、差舉例R={('數(shù)據(jù)庫系統(tǒng)原理'),('嵌入式系統(tǒng)開發(fā)'),('面向?qū)ο蟪绦蛟O(shè)計'),('網(wǎng)絡(luò)與信息安全')}S={('計算機三維建模'),('面向?qū)ο蟪绦蛟O(shè)計'),('數(shù)字圖象處理技術(shù)'),('數(shù)字媒體技術(shù)')}例4-1某校軟件學院有軟件工程和數(shù)字媒體技術(shù)兩個專業(yè)，設(shè)關(guān)系R為前者在本學期所開設(shè)課程的集合，S為后者在本學期所開設(shè)課程的集合：關(guān)系運算意義結(jié)果R∪SR∩SR－S{('數(shù)據(jù)庫系統(tǒng)原理'),('嵌入式系統(tǒng)開發(fā)'),

('面向?qū)ο蟪绦蛟O(shè)計'),('網(wǎng)絡(luò)與信息安全'),

('計算機三維建模'),('數(shù)字圖象處理技術(shù)'),

('數(shù)字媒體技術(shù)')

}本學期，兩個專業(yè)共開設(shè)的課程本學期，兩個專業(yè)均開設(shè)的課程本學期，只為軟件工程專業(yè)開設(shè)的課程{('面向?qū)ο蟪绦蛟O(shè)計')}{('數(shù)據(jù)庫系統(tǒng)原理'),('嵌入式系統(tǒng)開發(fā)'),

('網(wǎng)絡(luò)與信息安全')}4.1.1傳統(tǒng)的集合運算4、廣義笛卡爾積RSR×S4.1.2專門的關(guān)系運算

1、選擇（Selection）運算例4-3要求從[課程]關(guān)系中提取所有選修課的所有信息。δ(課程)

【課程】關(guān)系結(jié)果2、聯(lián)接（Join）例4-4將R關(guān)系與S關(guān)系做一個等值聯(lián)接運算，聯(lián)接條件為：R.課程編號

=S.課程編號。RSR

S

3、投影（Projection）例4-5從課程關(guān)系中，提取課程名稱與學時數(shù)。

(R)

R關(guān)系運算的綜合舉例例4-6根據(jù)如圖4-7所示的關(guān)系R和如圖4-8所示的關(guān)系S，求如下的對關(guān)系進行運算的表達式的結(jié)果。

數(shù)據(jù)源INTERNETSQL請求結(jié)果集結(jié)果集SQL請求服務(wù)器客戶端SQL查詢是SQL的核心，是對存儲在SQLServer中數(shù)據(jù)的一種用SELECT語句表達的查詢請求，其功能是從指定的SQL數(shù)據(jù)源中提取滿足用戶要求的數(shù)據(jù)以結(jié)果集的形式返回給用戶。4.2SQL查詢基礎(chǔ)1、數(shù)據(jù)源的組成SQL數(shù)據(jù)源由一個或多個表源構(gòu)成，表源可以是以下三種之一：基表：

本地服務(wù)器中的數(shù)據(jù)表。視圖：

本地服務(wù)器中的視圖。鏈接表：

遠程服務(wù)器中的表或視圖。數(shù)據(jù)源2、結(jié)果集的構(gòu)成結(jié)果集相關(guān)的五個主要屬性：結(jié)果集包含哪些列以及這些列的屬性：列的名稱、數(shù)據(jù)類型、大?。涣兄袛?shù)據(jù)值的來源。數(shù)據(jù)源中所包含的表源，以及表源之間的所有邏輯關(guān)系。表源中的行所必須達到的條件，凡不符合條件的行會被忽略對表源中的行或者列中的數(shù)據(jù)如何完成數(shù)學統(tǒng)計（如計數(shù)、求和、求平均值等）。結(jié)果集中行的排列順序。結(jié)果集由行和列組成，是對用SELECT語句提取數(shù)據(jù)源中的數(shù)據(jù)的表格排列，與SQL數(shù)據(jù)表的結(jié)構(gòu)相同，因此，結(jié)果集還可以作為其它查詢的數(shù)據(jù)源。3、SELECT語句的語法框架SELECT[ALL|DISTINCT]

[TOP(expression)[PERCENT][WITHTIES]]

select_list[INTOnew_table]FROMdata_source[WHEREline_search_condition][GROUPBYgroup_by_list][HAVINGgroup_search_condition][ORDERBYorder_list[ASC|DESC]]可在多個查詢之間使用UNION、INTERSECT和EXCEPT運算符（相當于關(guān)系的并、交、差等集合操作），以便將各個查詢的結(jié)果集歸并到一個結(jié)果集中。4、單個SELECT語句的執(zhí)行順序FROM子句：確定數(shù)據(jù)源中的表源。JOIN謂詞運算符和ON子句（常包含在FROM子句中）：建立表源之間的聯(lián)接。WHERE子句：行篩選。GROUPBY子句：行分組。聚合函數(shù)：組統(tǒng)計。HAVING子句：組篩選。選擇列表<select_list>：提取列以建立結(jié)果集。DISTINCT或者ALL謂詞運算符：消除或者保留重復(fù)行ORDER子句：結(jié)果集中行的排序。TOP子句（需要ORDER子句的配合）：行截取。INTO子句：以結(jié)果集的結(jié)構(gòu)和內(nèi)容為準，建立新表。數(shù)據(jù)源4.3 單表查詢特點：查找范圍狹窄簡單、易學課程編號課程名稱學時數(shù)學分數(shù)課程性質(zhì)課程介紹學院編號C001數(shù)據(jù)庫技術(shù)與應(yīng)用322必修數(shù)據(jù)庫技術(shù)藥劑學課程是生物制藥專業(yè)的專業(yè)課程與應(yīng)用是一門非計算機專業(yè)的必修課程6C002生物化學885.5必修生物化學課程是生物技術(shù)等相關(guān)專業(yè)的專業(yè)基礎(chǔ)課1C003藥劑學483必修1C004理論力學483必修理論力學是工程類專業(yè)的基礎(chǔ)課程2C005會計學563.5必修會計學是金融類專業(yè)的基礎(chǔ)課程3C006社會工作概論644必修本課程是管理類專業(yè)的基礎(chǔ)課程4C007英美文化概論322必修本課程是語言類專業(yè)的基礎(chǔ)課程5C008C++程序設(shè)計基礎(chǔ)644必修NULL6C009Python322選修NULL6C010中國古典文學鑒賞181選修本課程是公選課程，適合于所有喜歡中國古典文學的同學4C011古典哲學322選修本課程是公選課程，適合于所有喜歡世界歷史、哲學的同學44.3.1 基本查詢SELECT[ALL|DISTINCT]<select_list>FROMtable_name|view_name<select_list>::={[table_name.]*|[view_name.]*|column_name[AScolumn_alias]|expressionAScolumn_alias|{column_alias=expression}}[,...n]例4-7從[學院]表中提取各個學院的所有信息。SELECT

*

FROM

學院

;例4-8從[學院]表中提取所有學院的名稱、電話組成一個學院電話表。語句1：SELECT學院名稱,學院電話FROM學院;語句2：SELECT學院名稱,RIGHT(LTRIM(RTRIM(學院電話)),8)AS教務(wù)辦電話

FROM學院例4-9查詢所有教師所屬的職稱有哪些？語句1：SELECT職稱FROM教師;語句2：去掉重復(fù)的行SELECTDISTINCT職稱FROM教師;4.3.2 條件查詢條件子句：

WHERE<line_search_condition><line_search_condition>：行篩選條件行篩選條件可為：關(guān)系表達式、邏輯表達式

關(guān)系運算符為：=（等于）!=或<>（不等于）>（大于）>=（大于等于）<（小于）<=（小于等于）等。邏輯運算符為：Not非（求反）And與Or或運算符說明ALL滿足子查詢中所有值的記錄，用法：<字段><比較符>ALL(<子查詢>)ANY滿足子查詢中任意一個值的記錄。用法：<字段><比較符>ANY(<子查詢>)BETWEEN字段的內(nèi)容在指定范圍內(nèi)。用法：<字段>BETWEEN<范圍始值>AND<范圍終值>EXISTS測試子查詢中查詢結(jié)果是否為空。若為空，則返回假（FALSE）。用法：EXISTS(<子查詢>)IN字段內(nèi)容是結(jié)果集合或者子查詢中的內(nèi)容。用法：<字段>IN<結(jié)果集合>或者<字段>IN(<子查詢>)LIKE對字符型數(shù)據(jù)進行字符串比較，提供兩種通配符，即下劃線“_”和百分號“%”，下劃線表示1個字符，百分號表示0個或多個字符。用法：<字段>LIKE<字符表達式>SOME滿足集合中的某一個值，功能與用法等同于ANY。用法：<字段><比較符>SOME(<子查詢>)ISIS[NOT]NULLWHERE子句中的條件運算符（謂詞）LIKE用法：

<字符型字段名>LIKE<字符表達式>在<字符表達式>中可以使用下面的通配符通配符：

_

表示某個占位符上的一個任意字符，

%

表示可以是任意多個任意字符，

[]

表示取[]中的任意一個字符，如：[abcdef]也可[a-f]

[^]

表示不取[]中的字符，例4-10列出學分在3分以下（不包含3分）所有選修課程的名稱和它們的學分數(shù)和學時數(shù)。SELECT課程名稱,學分數(shù),學時數(shù)

FROM課程

WHERE學分數(shù)<3AND課程性質(zhì)='選修';例4-11查詢學時數(shù)在40和60之間的所有課程的名稱和學時數(shù)。SELECT課程名稱,學時數(shù)FROM課程

WHERE學時數(shù)BETWEEN40AND60WHERE

學時數(shù)

>=

40AND學時數(shù)<=60例4-12查詢四川籍（特指省份，不包括直轄市）男學生的所有信息。SELECT*

FROM學生

WHERE籍貫LIKE'四川%'AND性別='男';WHERELEFT(籍貫,2)='四川'AND性別='男'例4-13查詢鄭濤、郭豪、蘇永紅、蔣波四位同學的學號、姓名、籍貫、專業(yè)班級和學院編號。SELECT學號,姓名,籍貫,專業(yè)班級,學院編號

FROM學生

WHERE姓名IN('鄭濤','郭豪','蘇永紅','蔣波');條件表示這樣的意思：WHERE姓名='鄭濤'OR姓名='郭豪'OR姓名='蘇永紅'OR姓名='蔣波'例4-14查找還未設(shè)置密碼的教師的所有信息。SELECT*

FROM教師

WHERE密碼ISNULL空值的判斷不能使用等于比較運算符：

密碼=NULL×4.3.3生成表查詢語法：

INTOnew_table參數(shù)：new_table：以SELECT語句執(zhí)行后的結(jié)果集為內(nèi)容，創(chuàng)建新的數(shù)據(jù)表或臨時表。注意：當選擇列表select_list中包括計算列時，新表中的相應(yīng)列不再是計算列，其類型由其值的格式來確定。例4-15將工程力學1701班所有學生的學號、姓名、出生日期等信息永久保存到當前數(shù)據(jù)庫的新數(shù)據(jù)表[工程力學1701]中。注意：生成表查詢并不顯示查詢結(jié)果。但可用如下SELECT語句顯示新表SupperFilm的內(nèi)容：SELECT*

FROMSupperFilm;SELECT學號,姓名,出生日期

INTO[工程力學1701]

FROM學生

WHERE專業(yè)班級='工程力學1701';4.3.4 聚合查詢什么是聚合查詢？所謂聚合查詢就是按照分組列（在GROUP子句中指定）值的個數(shù)n，將數(shù)據(jù)源中指定的行（滿足WHERE條件的行）分成n個組（缺省GROUP的情況下，分成一個組），并且可對每一個組做進一步的組篩選（由HAVING子句實現(xiàn)），再針對每一組返回一個統(tǒng)計性的摘要行（該摘要行中的統(tǒng)計數(shù)據(jù)由SELECT子句中的聚合函數(shù)提供）。聚合查詢有下列三種實現(xiàn)方式：僅由聚合函數(shù)實現(xiàn)聚合查詢由聚合函數(shù)和GROUP子句共同實現(xiàn)由聚合函數(shù)、GROUP子句和HAVING子句共同實現(xiàn)1.僅由聚合函數(shù)實現(xiàn)聚合查詢計數(shù)函數(shù)：COUNT(*) COUNT([[ALL|DISTINCT]expression])求和函數(shù)：SUM([ALL|DISTINCT]expression)求平均值函數(shù)：AVG([ALL|DISTINCT]expression)求最小值函數(shù)：MIN(expression)求最大值函數(shù)：MAX(expression)聚合函數(shù)只能在以下位置作為表達式使用：SELECT語句的選擇列表select_list中。HAVING子句的組篩選器中。例4-16統(tǒng)計全校一共開設(shè)了多少門課程。

SELECTCOUNT(*)as課程門數(shù)

FROM課程;2.由聚合函數(shù)和GROUP子句共同實現(xiàn)語法：GROUPBY<group_by_list><group_by_list>::=

<group_by_expression>[,...n]參數(shù)：group_by_list：分組列表，可由多個分組列組成。group_by_expression：分組列（或者稱分組依據(jù)），可以是單獨的表列或視圖列，也可以是關(guān)于表列或視圖列的非聚合表達式。其意義是根據(jù)其值的個數(shù)將數(shù)據(jù)源中行分成幾個組。例4-17統(tǒng)計2017-2018學年第一學期每一個課堂的平均成績，顯示每一個課堂的編號和平均成績。SELECT課堂編號,AVG(成績)AS平均成績

FROM選課成績

WHERE課堂編號LIKE'2017-2018-1%'GROUPBY課堂編號;結(jié)果集

有幾行？例4-18根據(jù)院系統(tǒng)計男女生的人數(shù)，顯示每一個學院的編號、性別以及人數(shù)。SELECT學院編號,性別,COUNT(*)AS人數(shù)

FROM學生

GROUPBY學院編號,性別;3.由聚合函數(shù)、GROUP子句和HAVING子句共同實現(xiàn)例4-19查詢各種職稱的教師人數(shù)。SELECT職稱,COUNT(*)AS人數(shù)

FROM教師

GROUPBY職稱HAVING<group_search_condition>group_search_condition為組篩選條件如果想去掉無職稱人的統(tǒng)計結(jié)果，則可使用如下兩種方式：SELECT職稱,COUNT(*)AS人數(shù)FROM教師GROUPBY職稱

HAVING職稱ISNOTNULL;SELECT職稱,COUNT(*)AS人數(shù)

FROM教師

WHERE職稱ISNOTNULL

GROUPBY職稱;SELECT職稱,COUNT(*)AS人數(shù)FROM教師GROUPBY職稱HAVINGCOUNT(*)>2;

如果我們限定人數(shù)，則可使用下面的語句屏蔽人數(shù)小于等于2的組（注意：在HAVING子句中不可使用列別名）：生成合計作為附加的匯總列出現(xiàn)在結(jié)果集的最后。當與BY一起使用時，COMPUTE子句在結(jié)果集內(nèi)生成控制中斷和小計。[COMPUTE

{聚合函數(shù)}[,...n][BYexpression[,...n]]]BYexpression表示按expression字段分組，使用BY子句必須先按此字段排序4．使用COMPUTE和COMPUTEBY子句匯總統(tǒng)計學生表中的學生人數(shù)并顯示詳細數(shù)據(jù)SELECT*FROM學生computecount(學號)按班級分別統(tǒng)計學生表中各班的學生人數(shù)并顯示詳細數(shù)據(jù)SELECT*FROM學生orderby專業(yè)班級computecount(學號)by專業(yè)班級4.3.5 結(jié)果集的數(shù)據(jù)排序1、ORDER子句語法：ORDERBYorder_by_listorder_by_list::= {order_by_expression[ASC|DESC]}[,...n]參數(shù)：order_by_list：排序列表，可包含多個排序列。order_by_expression：排序列?？梢允且粋€表列、視圖列或列別名，也可以是一個表示該名稱或別名在選擇列表select_list中所處位置的非負整數(shù)。[ASC|DESC]：可缺省。ASC代表升序（默認），DESC代表降序。例4-20從[教師]表中提取每一位教師的姓名和職稱，要求按職稱的字典序排列顯示。SELECT姓名,職稱

FROM教師

ORDERBY職稱;

如果要求按照職稱的意義進行排序，即按照教授、副教授、講師、助教、無職稱的順序排列?CASE表達式的妙用之一SELECT姓名,職稱

FROM教師

ORDERBYCASE

WHEN職稱='教授'THEN1

WHEN職稱='副教授'THEN2

WHEN職稱='講師'THEN3

WHEN職稱='助教'THEN4

ELSE5

END;CASE

WHEN條件1THEN表達式1WHEN條件2THEN表達式2…

ELSE表達式nEND2、TOP子句[TOP(expression)[PERCENT][WITHTIES]]參數(shù)：TOP(expression)：指示只能從結(jié)果集返回由expression指定數(shù)目的第一組行。TOP(expression)PERCENT：指示只能從查詢結(jié)果集返回由expression指定的百分比數(shù)目（相對于結(jié)果集的大小）的第一組行。WITHTIES指定從結(jié)果集中再返回一些額外的行，這些額外的返回行與TOP限制后從結(jié)果集返回的最后一行，在ORDER子句指定的排序列上的值是相同的。例4-21提取編號為“2017-2018-2-B009”的中，成績最高的前三名學生的學號，要求顯示學號和成績。SELECTTOP(3)

學號,成績

FROM選課成績

WHERE課堂編號='2017-2018-2-B009'

ORDERBY成績DESC;SELECTTOP(3)WITHTIES學號,成績

FROM選課成績

WHERE課堂編號='2017-2018-2-B009'

ORDERBY成績DESC;如果要顯示并列第三的4.4 多表查詢學院學院編號學院名稱學院地址學院電話課程課程編號課程名稱學時數(shù)學分數(shù)課程性質(zhì)課程介紹學院編號數(shù)據(jù)查詢要求：查詢生命學院共開設(shè)了那些課程，要求顯示課程的名稱?！吧鼘W院”“課程名稱”課程名稱生物化學藥劑學結(jié)論：本次查詢至少需要從[學院]、[課程]二個相關(guān)數(shù)據(jù)表中提取結(jié)果數(shù)據(jù)4.4.1 聯(lián)接概述1、聯(lián)接的目的建立數(shù)據(jù)行的導(dǎo)航路徑[學院]表[課程]表2、聯(lián)接展示

[學院]表[課程]表3、聯(lián)接條件ON<join_condition>{AND<join_condition>}[…n]<join_condition>::=<column_exp>OP<column_exp>OP：=、<、>、<=、>=、<>、!=、!<、!>聯(lián)接條件最常見的寫法是將兩個表的公共列的名字用比較運算符連接起來（注意：在列名前必須加上表名限制，以限定列的所屬）。公共列的兩種常見形態(tài)：公共列在兩個表中均為主鍵。公共列是一個表的主鍵，但在另一個表中是外鍵。例如：

ON學院.學院編號

=課程.學院編號4、聯(lián)接類型內(nèi)鏈接INNERJOIN顯示符合條件的記錄，此為默認值等值連接用“=”非等值連接用>,>=,<,<=自然連接去掉重復(fù)字段外連接（用“=”）

LEFT（OUTER）JOIN為左（外）連接，用于顯示符合條件的數(shù)據(jù)行以及左邊表中不符合條件的數(shù)據(jù)行，此時右邊數(shù)據(jù)行會以NULL來顯示RIGHT（OUTER）JOIN右（外）連接，用于顯示符合條件的數(shù)據(jù)行以及右邊表中不符合條件的數(shù)據(jù)行。此時左邊數(shù)據(jù)行會以NULL來顯示FULL（OUTER）JOIN顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行。此時缺乏數(shù)據(jù)的數(shù)據(jù)行會以NULL來顯示交叉連接CROSSJOIN（無條件）將一個表的每一個記錄和另一表的每個記錄匹配成新的數(shù)據(jù)行5、聯(lián)接規(guī)范（內(nèi)部聯(lián)接）(1) 在FROM子句中指定聯(lián)接規(guī)范，語法如下：FROM<data_source><data_source>::=<table_source>|{<table_source>[INNERJOIN<table_source>ON<join_condition>][…n]}<table_source>::={table_name|view_name}[[AS]table_alias]例4-22在FROM子句中指定[成績]表和[學生]表的聯(lián)接規(guī)范（采用內(nèi)部聯(lián)接）：FROM

成績

INNER

JOIN學生

ON成績.學生編號

=學生.學生編號(2)在WHERE子句中指定聯(lián)接規(guī)范FROM<data_source>WHERE<join_condition>[AND<line_search_condistion>]<data_source>::=<table_source>[,…n]<table_source>::={table_name|view_name}[AStable_alias]例4-23在WHERE子句中指定[成績]表和[學生]表的聯(lián)接規(guī)范（注意：聯(lián)接類型默認為內(nèi)部聯(lián)接）：FROM成績,學生WHERE

成績.學生編號

=

學生.學生編號4.4.2內(nèi)部聯(lián)接(innerjoin)內(nèi)部聯(lián)接可分為：

等值聯(lián)接、非等值聯(lián)接、自然聯(lián)接等值聯(lián)接的聯(lián)接條件中使用“=”非等值聯(lián)接的聯(lián)接條件中使用“>”、“>=”、“<”、“<=”自然聯(lián)接的聯(lián)接條件中使用“=”

和等值聯(lián)接不同的是要去掉重復(fù)屬性。例4-26查詢教師歐陽淑芳所上的所有課堂，要求按照開課年份和開課學期的升序，顯示課堂名稱、開課年份、開課學期。SELECT

課堂.課堂名稱,

課堂.開課年份,

課堂.開課學期

FROM

教師

INNER

JOIN

課堂

ON

教師.教師編號

=

課堂.教師編號

WHERE

教師.姓名

=

'歐陽淑芳'

ORDER

BY

課堂.開課年份,

課堂.開課學期

;SELECT

課堂.課堂名稱,

課堂.開課年份,

課堂.開課學期

FROM

教師,

課堂

WHERE教師.教師編號

=

課堂.教師編號AND

教師.姓名

=

'歐陽淑芳'

ORDER

BY

課堂.開課年份,

課堂.開課學期

;例4-27查詢王志強老師講授的所有學生的名單，顯示學號、姓名、專業(yè)班級。SELECT

學生.學號,

學生.姓名,

成績,

專業(yè)班級

FROM

教師

INNER

JOIN

課堂

ON

教師.教師編號

=

課堂.教師編號

INNER

JOIN

選課成績

ON

課堂.課堂編號

=

選課成績.課堂編號

INNER

JOIN

學生

ON

選課成績.學號

=

學生.學號

WHERE

教師.姓名

=

'王志強'

;如果將聯(lián)接條件寫在WHERE子句中?例4-28列出最受歡迎（特指選修人數(shù)）的前三門課程，要求按選修人數(shù)的降序排列課程的名稱和選修人數(shù)。SELECT

TOP3課程名稱,

選修人數(shù)

FROM

課程

INNER

JOIN(SELECT

課程.課程編號,

COUNT(*)

AS

選修人數(shù)FROM

課程

INNER

JOIN

課堂

ON

課程.課程編號

=

課堂.課程編號

INNER

JOIN

選課成績

ON

課堂.課堂編號

=

選課成績.課堂編號GROUP

BY

課程.課程編號

)ASAON

課程.課程編號

=

A.課程編號

ORDER

BY

選修人數(shù)

DESC;例4-29統(tǒng)計計算機學院每一位教師本學期的教學工作量（指學時數(shù)），要求顯示教師編號和學時數(shù)，并按學時數(shù)的降序排列。SELECT教師.教師編號,SUM(課程.學時數(shù))AS學時數(shù)

FROM教師INNERJOIN課堂ON教師.教師編號=課堂.教師編號

INNERJOIN課程ON課堂.課程編號=課程.課程編號

innerjoin學院on教師.學院編號=學院.學院編號

WHERE課堂.開課年份='2017-2018'AND課堂.開課學期='一'and學院名稱='計算機學院'GROUPBY教師.教師編號

ORDERBY學時數(shù)DESC4.4.3外聯(lián)接(outerjoin)外聯(lián)接外聯(lián)接的聯(lián)接條件是用“=”實現(xiàn)的外聯(lián)接結(jié)果集中除了有滿足條件的元組還有不滿足條件的元組。外聯(lián)接分為：左(外)聯(lián)接、右(外)聯(lián)接、全聯(lián)接聯(lián)接規(guī)范為：From表1left/right/fullouterjoin表2on聯(lián)接條件交叉鏈接(crossjoin)交叉聯(lián)接即沒有條件的聯(lián)接。其結(jié)果集為笛卡爾全集。聯(lián)接規(guī)范為：From表1crossjoin表24.4.4結(jié)果集的歸并處理使用謂詞運算符（Union(集合并)、Except(集合差)、Intersect(集合交)）可以將多個結(jié)果處理成成一個結(jié)果集。語法：<SELECT_Statement>{UNION[ALL]|EXCEPT|INTERSECT}<SELECT_statement>位置：INTO的位置:只能放在第一個select語句中ORDERBY子句的位置:只能放在最后一個select語句中UNION：并運算例4-31從[學生]表中提取湖北省和其他省的人數(shù)。SELECT

'湖北'

AS

省份,

count(*)

AS

人數(shù)

FROM

學生

WHERE

籍貫

LIKE

'湖北%'UNIONSELECT

'其他'

AS

省份,

count(*)

AS

人數(shù)

FROM

學生

WHERE

籍貫

NOT

LIKE

'湖北%'

;INTERSECT：交運算例4-32查詢土木工程、工程力學兩個專業(yè)的學生在2017-2018學年均選修過的必修課程，要求顯示課程編號、課程名稱，并按課程編號的升序排列，并將查詢結(jié)果保存到臨時表Temp3中。SELECTdistinct課程.課程編號,課程.課程名稱

INTOTemp3FROM課程INNERJOIN

課堂ON課程.課程編號=課堂.課程編號

WHERE課堂.開課年份='2017-2018'AND課程.課程性質(zhì)='必修'AND課堂.班級列表LIKE'%土木工程%'INTERSECTSELECTdistinct課程.課程編號,課程.課程名稱

FROM課程INNERJOIN

課堂ON課程.課程編號=課堂.課程編號

WHERE課堂.開課年份='2017-2018'AND課程.課程性質(zhì)='必修'AND課堂.班級列表LIKE'%工程力學%'ORDERBY課程.課程編號;Except：差運算例4-33給出如表4.6所示的十二星座查詢表，查詢什么星座的人沒有出現(xiàn)在[教師]表中。SELECT

*

FROM(

VALUES('白羊座'),

('金牛座'),

('雙子座'),

('巨蟹座'),

('獅子座'),

('處女座'),

('天秤座'),

('天蝎座'),

('射手座'),

('摩羯座'),

('水瓶座'),

('雙魚座')

)

AS

星座表(

星座

)

EXCEPT

01月21日太陽進入水瓶座02月20日太陽進入雙魚座03月21日太陽進入白羊座04月20日太陽進入金牛座05月21日太陽進入雙子座06月21日太陽進入巨蟹座07月23日太陽進入獅子座08月23日太陽進入處女座09月23日太陽進入天秤座10月23日太陽進入天蝎座11月22日太陽進入射手座12月22日太陽進入摩羯座SELECT

DISTINCT

CASE

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/1/21'

AND

STR(YEAR(出生日期),4)

+

'/2/19'

THEN

'水瓶座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/2/20'

AND

STR(YEAR(出生日期),4)

+

'/3/20'

THEN

'雙魚座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/3/21'

AND

STR(YEAR(出生日期),4)

+

'/4/19'

THEN

'白羊座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/4/20'

AND

STR(YEAR(出生日期),4)

+

'/5/20'

THEN

'金牛座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/5/21'

AND

STR(YEAR(出生日期),4)

+

'/6/20'

THEN

'雙子座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/6/21'

AND

STR(YEAR(出生日期),4)

+

'/7/22'

THEN

'巨蟹座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/7/23'

AND

STR(YEAR(出生日期),4)

+

'/8/22'

THEN

'獅子座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/8/23'

AND

STR(YEAR(出生日期),4)

+

'/9/22'

THEN

'處女座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/9/23'

AND

STR(YEAR(出生日期),4)

+

'/10/22'

THEN

'天秤座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/10/23'

AND

STR(YEAR(出生日期),4)

+

'/11/21'

THEN

'天蝎座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/11/22'

AND

STR(YEAR(出生日期),4)

+

'/12/21'

THEN

'射手座'

ELSE

'摩羯座'

END

AS

星座

FROM

教師

;4.5 子查詢什么是子查詢？

子查詢也可稱為嵌套查詢，SQLServer允許在查詢中再嵌套查詢。最外面的查詢?yōu)楦覆樵?，里面的查詢?yōu)樽硬樵?，父查詢是要做的主要工作，子查詢是為父查詢提供條件或數(shù)據(jù)源等工作的。嵌套查詢一般的查詢方法是由里向外進行處理，即每個子查詢在上一級查詢處理之前處理，子查詢中所存取的表可以是父查詢沒有存取的表，子查詢選出的記錄不顯示。SELECT

MAX(選課成績.成績)

AS

最高分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號WHERE

課程.課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'SELECT

COUNT(*)

AS

人數(shù)

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程.課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'

AND

選課成績.成績

=

(“數(shù)據(jù)庫技術(shù)與應(yīng)用”課程的最高分

)

例如：查詢自“數(shù)據(jù)庫技術(shù)與應(yīng)用”課程開設(shè)以來獲得最高分的人數(shù)有多少。4.5子查詢的出現(xiàn)位置可在外部查詢的許多位置指定子查詢：在WHERE子句的行篩選條件line_search_condition或者HAVING子句的組篩選條件group_search_condition中：使用比較運算符（注：必須是單值子查詢）使用ANY、SOME或ALL修飾的比較運算符使用IN或NOTIN運算符使用EXISTS或NOTEXISTS運算符在FROM子句中：必須使用表別名。在SELECT子句select_list中：代替表達式或表達式的一部分（注：必須是單值子查詢）。4.5.1 單值子查詢單值子查詢：即子查詢得到的結(jié)果為一個值！例如：查詢生命學院都開設(shè)了那些課程。(SELECT

學院編號

FROM

學院

WHERE

學院名稱

=

'生命學院')SELECT

課程.*

FROM

課程

WHERE

學院編號

='生命學院的學院編號'1、單值子查詢出現(xiàn)在行篩選條件中例4-34假設(shè)生物科學1702班的吳冰同學的姓名是唯一的，查詢與吳冰同學的所有同鄉(xiāng)（指相同省份）的姓名和專業(yè)班級。SELECT

姓名,

專業(yè)班級

FROM

學生

WHERE

LEFT(籍貫,3)

=

(SELECT

LEFT(籍貫,3)

AS

省份

FROM

學生

WHERE

姓名

=

'吳冰'

AND

專業(yè)班級

=

'生物科學1702'

)AND

姓名

<>

'吳冰';2、單值子查詢出現(xiàn)在組篩選條件中例4-35在所有的“數(shù)據(jù)庫技術(shù)及應(yīng)用”課堂中，查詢比“數(shù)據(jù)庫-交通工程1701-2”課堂的平均分還高的其它課堂的編號和平均分。SELECT

課堂.課堂編號,

AVG(選課成績.成績)

AS

平均分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程.課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'

GROUP

BY

課堂.課堂編號

HAVING

AVG(選課成績.成績)

>

(

SELECT

AVG(選課成績.成績)

AS

平均分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程.課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'AND

課堂.課堂名稱

=

'數(shù)據(jù)庫-交通工程1701-2'

);3、單值子查詢的結(jié)果作為查詢的結(jié)果集中一列例4-36在所有的“數(shù)據(jù)庫技術(shù)及應(yīng)用”課堂中，顯示每一個課堂的平均分和所有課堂總平均分的比對情況，要求顯示每一個課堂的編號和平均分、所有課堂的總平均分（作為比對值）。第一步：先做一個生成表查詢，因為后續(xù)的兩步，均要用到第一步的結(jié)果。求各個課堂的平均分，并將結(jié)果送到臨時表“#各課堂平均分”表中保存SELECT

課堂.課堂編號,AVG(選課成績.成績)

AS

平均分

INTO

#各課堂的平均分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程.課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'

GROUP

BY

課堂.課堂編號第二步：在第一步的執(zhí)行結(jié)果上，求所有課堂的總平均分：SELECT

AVG(平均分)

AS

總平均分

FROM

#各課堂的平均分

第三步：在第一步和第二步的執(zhí)行結(jié)果上，查詢每一個課堂的平均分和所有課堂的總平均分的差異比較。SELECT

*,

(第二步的SELECT語句)

AS

總平均分

FROM

#各課堂的平均分最后，將第一步（取消臨時表）和第二步的語句帶入第三步合成：SELECT

*,(SELECT

AVG(平均分)

AS

總平均分

FROM(SELECT

課堂.課堂編號,

AVG(成績)

AS

平均分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'

GROUP

BY

課堂.課堂編號

)

AS

各課堂的平均分

)

AS

總平均分

FROM(SELECT

課堂.課堂編號,

AVG(成績)

AS

平均分

FROM

選課成績

INNER

JOIN

課堂

ON

選課成績.課堂編號

=

課堂.課堂編號

INNER

JOIN

課程

ON

課堂.課程編號

=

課程.課程編號

WHERE

課程名稱

=

'數(shù)據(jù)庫技術(shù)與應(yīng)用'

GROUP

BY

課堂.課堂編號

)

AS

各課堂的平均分4.5.2 多值子查詢所謂多值子查詢是指結(jié)果集中有多行多列多個數(shù)據(jù)的子查詢。可使用ANY、SOME、ALL修飾的比較運算符或者IN、EXISTS運算符，可出現(xiàn)：在WHERE子句的行篩選條件line_searc