數(shù)據(jù)庫(kù)應(yīng)用03 access中sql查詢語(yǔ)言

3.2.3多表自然連接查詢1.多表自然連接查詢的工作原理

多表查詢指FROM子句包括多個(gè)數(shù)據(jù)表。為簡(jiǎn)化問(wèn)題，先看兩個(gè)表的查詢

.兩表自然連接查詢工作原理【例3-33】

select*

from班級(jí)表,學(xué)生表;

結(jié)果非常龐大，有17個(gè)字段，180條記錄，原因：如果SELECT不帶WHERE子句時(shí)，首先從前一個(gè)表（此處為“班級(jí)表”）中取一條記錄然后與后面的表（此處為“學(xué)生表”）中的每條記錄進(jìn)行逐一連接再?gòu)牡谝粡埍砣〉诙l記錄，重復(fù)上述過(guò)程，直到第一張表的記錄取完結(jié)束結(jié)果存在組合爆炸的問(wèn)題，產(chǎn)生很多無(wú)用的垃圾數(shù)據(jù).兩表自然連接查詢工作原理2二個(gè)表之間自然連接（內(nèi)連）與主鍵、外鍵約束的關(guān)系：SELECT從兩表檢索結(jié)果時(shí)，要得到有效的數(shù)據(jù)必須帶WHERE子句，通常二個(gè)表之間必須有主外鍵的約束，即二個(gè)表中有共同的字段（或字段集），這個(gè)字段或字段集在一個(gè)表中為主鍵，在另一個(gè)表中為外鍵。如果二表是多對(duì)多的關(guān)系，也可以轉(zhuǎn)化為二個(gè)一對(duì)多的關(guān)系。.兩表自然連接查詢【例3-34】【例3-34】將學(xué)生表和班級(jí)表進(jìn)行自然連接，求結(jié)果集。

.將學(xué)生表和班級(jí)表進(jìn)行自然連接select*

from班級(jí)表innerjoin學(xué)生表

on班級(jí)表.班號(hào)=學(xué)生表.班號(hào);上述SELECT語(yǔ)句的等價(jià)SQL語(yǔ)句如下：

select*;

from班級(jí)表,學(xué)生表

where班級(jí)表.班號(hào)=學(xué)生表.班號(hào);.兩表自然連接查詢【例3-34】2例3-34中的子句“from班級(jí)表innerjoin學(xué)生表on班級(jí)表.班號(hào)=學(xué)生表.班號(hào)”或“from班級(jí)表,學(xué)生表where班級(jí)表.班號(hào)=學(xué)生表.班號(hào)”表示的是“班級(jí)表”和“學(xué)生表”進(jìn)行自然連接自然連接操作是數(shù)據(jù)庫(kù)SQL檢索語(yǔ)句中最常用的操作要求參與自然連接操作的兩個(gè)關(guān)系表間存在一對(duì)多的約束，即兩個(gè)關(guān)系表間存在外鍵約束，這樣的自然連接才有意義.兩表自然連接查詢與數(shù)據(jù)庫(kù)模式關(guān)系僅存在主外鍵約束的兩個(gè)表可以進(jìn)行自然連接

.兩表自然連接查詢規(guī)則一對(duì)多的自然連接的結(jié)果是一個(gè)關(guān)系表，關(guān)系表的結(jié)果為：橫向上（字段數(shù)）為兩表的字段相疊加；縱向上（記錄數(shù)）以多表的記錄為最終結(jié)果。以“學(xué)生表”與“成績(jī)表”的關(guān)聯(lián)為例，由于“成績(jī)表”中有60條記錄，使用SELECT中的子句“WHERE學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)”表示將兩表進(jìn)行自然連接操作，其結(jié)果中字段為13個(gè)（3個(gè)在班級(jí)表，11個(gè)在學(xué)生表），記錄為60條。

.兩表自然連接查詢【例3-35】【例3-35】求成績(jī)?cè)?5分以上學(xué)生的學(xué)號(hào)、姓名和成績(jī)。

.求成績(jī)?cè)?5分以上學(xué)生的學(xué)號(hào)、姓名和成績(jī)select學(xué)生表.學(xué)號(hào),姓名,成績(jī)

from學(xué)生表,成績(jī)表

where學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)

and成績(jī)>=85;.兩表自然連接查詢---別名例子為簡(jiǎn)化SELECT的書(shū)寫(xiě)，SQL中允許使用表的別名，上例可寫(xiě)成如下SELECT語(yǔ)句。selecta.學(xué)號(hào),姓名,成績(jī)

from學(xué)生表a,成績(jī)表b

wherea.學(xué)號(hào)=b.學(xué)號(hào)and成績(jī)>=85;a表示是數(shù)據(jù)表“學(xué)生表”的別名b是數(shù)據(jù)表“成績(jī)表”的別名

.多表自然連接查詢【例3-36】將兩表的自然連接推廣到多表【例3-36】將“學(xué)生表”、“成績(jī)表”和“課程表”進(jìn)行自然連接，求結(jié)果。

.多表自然連接查詢【例3-36】2select*

from(學(xué)生表innerjoin成績(jī)表

on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào))

innerjoin課程表

on課程表.課程號(hào)=成績(jī)表.課程號(hào);本例中可理解為先進(jìn)行“課程表”與“成績(jī)表”的自然連接，然后再用得到的關(guān)系與“學(xué)生表”進(jìn)行自然連接。例3-36的另一種書(shū)寫(xiě)方式為：

select*

from學(xué)生表,成績(jī)表,課程表

where學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)

and課程表.課程號(hào)=成績(jī)表.課程號(hào);.多表自然連接查詢一般方法必須首先知道數(shù)據(jù)庫(kù)模式，才能編寫(xiě)正確的SQL語(yǔ)句數(shù)據(jù)庫(kù)導(dǎo)航概念根據(jù)給出的已知條件，求需要的數(shù)據(jù)。已知條件是在一個(gè)表中的某個(gè)字段取值，所求數(shù)據(jù)是我們感興趣的字段。要從已知的數(shù)據(jù)表出發(fā)，通過(guò)表間的關(guān)聯(lián)到達(dá)目的表，最后根據(jù)題目要求篩選相關(guān)的字段和記錄。.多表的自然連接查詢【例3-37】【例3-37】求“楊小建”的成績(jī)表。根據(jù)給出的數(shù)據(jù)庫(kù)模式，我們知道：已知條件為“學(xué)生表”的“姓名”字段其內(nèi)容等于“楊小建”待求的是“課程表”中的“課程號(hào)、課程名”和“成績(jī)表”中的“成績(jī)”.多表的自然連接查詢【例3-37】select課程表.課程號(hào),課程名,成績(jī)

from(學(xué)生表innerjoin成績(jī)表

on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào))

innerjoin課程表

on課程表.課程號(hào)=成績(jī)表.課程號(hào)

where姓名=“楊小建”;或selectc.課程號(hào),課程名,成績(jī)

from學(xué)生表a

,成績(jī)表b,課程表c

wherea.學(xué)號(hào)=b.學(xué)號(hào)andc.課程號(hào)=b.課程號(hào)and姓名=“楊小建”;.多表的自然連接查詢【例3-38】【例3-38】求會(huì)計(jì)學(xué)081班的所有成績(jī)單。分析：已知條件為“班級(jí)表”中的“專業(yè)名稱”待求為“學(xué)生表”的“姓名”；“課程”表的“課程號(hào)、課程名”；“成績(jī)表”的“成績(jī)”

.多表的自然連接查詢【例3-38】2select學(xué)生表.學(xué)號(hào),姓名,課程表.課程號(hào),課程名,成績(jī),班級(jí)表.班號(hào)

from(班級(jí)表innerjoin學(xué)生表on班級(jí)表.班號(hào)=學(xué)生表.班號(hào))

innerjoin

(課程表innerjoin成績(jī)表on課程表.課程號(hào)=成績(jī)表.課程號(hào))

on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)

where班級(jí)表.班號(hào)=“會(huì)計(jì)學(xué)081”;select學(xué)生表.學(xué)號(hào),姓名,課程表.課程號(hào),課程名,成績(jī),班級(jí)表.班號(hào)

from班級(jí)表,學(xué)生表,課程表,成績(jī)表Where班級(jí)表.班號(hào)=學(xué)生表.班號(hào)and

學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)and

班級(jí)表.班號(hào)=“會(huì)計(jì)學(xué)081”;

.【例3-39】求“會(huì)計(jì)學(xué)081”的《高等數(shù)學(xué)III》成績(jī)單。.多表自然連接查詢【例3-39】【例3-39】求“會(huì)計(jì)學(xué)081”的《高等數(shù)學(xué)III》成績(jī)單。selectb.學(xué)號(hào),姓名,c.課程號(hào),課程名,成績(jī),a.班號(hào)from班級(jí)表a,學(xué)生表b,課程表c,成績(jī)表dwherea.班號(hào)=b.班號(hào)andc.課程號(hào)=d.課程號(hào)andb.學(xué)號(hào)=d.學(xué)號(hào)anda.班號(hào)="會(huì)計(jì)學(xué)081"and課程名="高等數(shù)學(xué)III";

.多表查詢GROUPBY【例3-40】【例3-40】求每個(gè)學(xué)生所修的總學(xué)分?jǐn)?shù)。

.求每個(gè)學(xué)生所修的總學(xué)分?jǐn)?shù)select姓名,sum(學(xué)分)as學(xué)分合計(jì)from學(xué)生表a,成績(jī)表b,課程表cwherea.學(xué)號(hào)=b.學(xué)號(hào)andc.課程號(hào)=b.課程號(hào)groupby姓名orderbysum(學(xué)分)desc;.多表查詢使用GROUPBY的例子【例3-41】求總學(xué)分在11分以上的學(xué)生姓名

.求總學(xué)分在11分以上的學(xué)生姓名select姓名,sum(學(xué)分)as學(xué)分合計(jì)from學(xué)生表a,成績(jī)表b,課程表cwherea.學(xué)號(hào)=b.學(xué)號(hào)andc.課程號(hào)=b.課程號(hào)groupby姓名havingsum(學(xué)分)>=11;不難看出，HAVING子句的功能是過(guò)濾GROUPBY子句的結(jié)果。.3.2.4參數(shù)查詢前面我們編寫(xiě)的查詢，運(yùn)行時(shí)直接返回結(jié)果，即所有的數(shù)據(jù)已經(jīng)編寫(xiě)在SQL語(yǔ)句中。所謂參數(shù)查詢是指在運(yùn)行該查詢時(shí)，系統(tǒng)會(huì)要求用戶輸入所需的參數(shù)值。.參數(shù)查詢【例3-42】【例3-42】用戶隨機(jī)輸入入學(xué)成績(jī)，使用參數(shù)查詢求大于該入學(xué)成績(jī)的學(xué)生學(xué)號(hào)、姓名和入學(xué)成績(jī)。

select學(xué)號(hào),姓名,入學(xué)成績(jī)

from學(xué)生表

where入學(xué)成績(jī)>=[請(qǐng)輸入入學(xué)成績(jī)];運(yùn)行該查詢時(shí)，首先彈出一個(gè)對(duì)話框，在輸入620，單擊確定后，得到所需的結(jié)果。下次運(yùn)行，根據(jù)用戶隨機(jī)輸入的值，返回不同結(jié)果。.3.2.4參數(shù)查詢【例3-43】【例3-43】用戶隨機(jī)輸入學(xué)生姓氏，輸出該姓氏的所有學(xué)生學(xué)號(hào)和姓名。

select學(xué)號(hào),姓名from學(xué)生表where姓名like[請(qǐng)輸入姓氏]&"*";

.參數(shù)查詢【例3-44】【例3-44】隨機(jī)輸入入學(xué)成績(jī)的下限和上限，輸出入學(xué)成績(jī)?cè)谙孪藓蜕舷拗g的所有學(xué)生學(xué)號(hào)，姓名和入學(xué)成績(jī)。

select學(xué)號(hào),姓名,入學(xué)成績(jī)from學(xué)生表where入學(xué)成績(jī)between[請(qǐng)輸入入學(xué)成績(jī)下限]and[請(qǐng)輸入入學(xué)成績(jī)上限];.3.2.5SQL子查詢與合并查詢1.SQL子查詢帶IN子查詢帶關(guān)系運(yùn)算符子查詢ALL限定詞ANY限定詞2.合并查詢.1.SQL子查詢子查詢是一個(gè)SELECT語(yǔ)句，它嵌套在一個(gè)SELECT語(yǔ)句（也可是INSERT語(yǔ)句、DELETE語(yǔ)句或UPDATE語(yǔ)句）的WHERE子句部分子查詢的SELECT查詢總是使用圓括號(hào)括起來(lái)。子查詢也可嵌套在另一子查詢中。有三種語(yǔ)法來(lái)創(chuàng)建子查詢：（1）WHERE表達(dá)式[NOT]IN(子查詢語(yǔ)句)（2）WHERE表達(dá)式關(guān)系運(yùn)算符[ANY|ALL](子查詢語(yǔ)句)（3）WHERE[NOT]EXISTS(子查詢語(yǔ)句)

.帶IN子查詢僅講解前兩種子查詢（1）帶IN子查詢

IN子查詢語(yǔ)法結(jié)構(gòu)為：

WHERE表達(dá)式[NOT]IN(子查詢語(yǔ)句)

.帶IN子查詢【例3-45】【例3-45】求“會(huì)計(jì)學(xué)”專業(yè)的所有的同學(xué)學(xué)號(hào)、姓名和班號(hào)。

可以用自然連接實(shí)現(xiàn)，也可以借助子查詢來(lái)完成。先查班級(jí)表中“會(huì)計(jì)學(xué)”專業(yè)對(duì)應(yīng)的班級(jí)號(hào)，再對(duì)學(xué)生表中查找相應(yīng)班級(jí)號(hào)的同學(xué)學(xué)號(hào)、姓名和班號(hào)。

.帶IN子查詢【例3-45】2select學(xué)號(hào),姓名,班號(hào)

from學(xué)生表

where班號(hào)in(select班號(hào)

from班級(jí)表

where專業(yè)="會(huì)計(jì)學(xué)");

.帶IN子查詢【例3-46】【例3-46】求沒(méi)有修“高等數(shù)學(xué)III”同學(xué)的學(xué)號(hào)和姓名。.【例3-46】求沒(méi)有修“高等數(shù)學(xué)III”同學(xué)的學(xué)號(hào)和姓名。select學(xué)號(hào),姓名from學(xué)生表where學(xué)號(hào)notin(select學(xué)號(hào)

from成績(jī)表,課程表

where成績(jī)表.課程號(hào)=課程表.課程號(hào)and課程名="高等數(shù)學(xué)III");.帶關(guān)系運(yùn)算符子查詢（2）帶關(guān)系運(yùn)算符子查詢語(yǔ)法結(jié)構(gòu)為：WHERE表達(dá)式關(guān)系運(yùn)算符[ANY|ALL](子查詢語(yǔ)句)當(dāng)子查詢返回單值時(shí)，符號(hào)ANY或ALL可以省略其他情況下，必須使用ANY或ALL修飾>ANY大于子查詢結(jié)果中的某個(gè)最小值<ANY小于子查詢結(jié)果中的某個(gè)最大值>=ANY大于等于子查詢結(jié)果中的某個(gè)最小值<=ANY小于等于子查詢結(jié)果中的某個(gè)最大值=ANYWhere表達(dá)式在子查詢結(jié)果中的某個(gè)值中<>ANY無(wú)意義>ALL大于子查詢結(jié)果中的某個(gè)最大值<ALL小于子查詢結(jié)果中的某個(gè)最小值>=ALL大于等于子查詢結(jié)果中的某個(gè)最大值<=ALL小于等于子查詢結(jié)果中的某個(gè)最小值=ALL無(wú)意義<>ALLWhere表達(dá)式不在子查詢結(jié)果中的某個(gè)值中.帶關(guān)系運(yùn)算符子查詢【例3-47】【例3-47】求入學(xué)成績(jī)高于平均入學(xué)成績(jī)的“計(jì)算機(jī)科學(xué)與技術(shù)081”同學(xué)學(xué)號(hào)和姓名。求解步驟分為兩步，求“計(jì)算機(jī)科學(xué)與技術(shù)081”平均入學(xué)成績(jī)，求“計(jì)算機(jī)科學(xué)與技術(shù)081”入學(xué)成績(jī)大于平均入學(xué)成績(jī)的學(xué)號(hào)和姓名。.帶關(guān)系運(yùn)算符子查詢【例3-47】2select學(xué)號(hào),姓名from學(xué)生表where班號(hào)="計(jì)算機(jī)科學(xué)與技術(shù)081"and入學(xué)成績(jī)>=(selectavg(入學(xué)成績(jī))from學(xué)生表

where班號(hào)="計(jì)算機(jī)科學(xué)與技術(shù)081");

.帶關(guān)系運(yùn)算符子查詢

ANY和ALL功能。給出高于“會(huì)計(jì)學(xué)091”班的同學(xué)入學(xué)成績(jī)的學(xué)生的學(xué)號(hào)、姓名、班號(hào)和入學(xué)成績(jī)Select學(xué)號(hào),姓名,班號(hào),入學(xué)成績(jī)From學(xué)生表Where入學(xué)成績(jī)>any(select入學(xué)成績(jī)

from學(xué)生表

where班號(hào)="會(huì)計(jì)學(xué)091");.Select學(xué)號(hào),姓名,班號(hào),入學(xué)成績(jī)From學(xué)生表Where入學(xué)成績(jī)>(selectmin(入學(xué)成績(jī))from學(xué)生表

where班號(hào)="會(huì)計(jì)學(xué)091");.ANY功能---例3-48【例3-48】求入學(xué)成績(jī)小于等于班號(hào)為“會(huì)計(jì)學(xué)091”學(xué)生的任一入學(xué)成績(jī)的學(xué)生學(xué)號(hào)、姓名和入學(xué)成績(jī)。select學(xué)號(hào),姓名,班號(hào),入學(xué)成績(jī)from學(xué)生表where入學(xué)成績(jī)<=any(select入學(xué)成績(jī)

from學(xué)生表

where班號(hào)="會(huì)計(jì)學(xué)091")

and班號(hào)<>"會(huì)計(jì)學(xué)091"orderby入學(xué)成績(jī)desc;

.ANY功能【例3-48】3等價(jià)于：select學(xué)號(hào),姓名,班號(hào),入學(xué)成績(jī)from學(xué)生表where入學(xué)成績(jī)<=(selectmax(入學(xué)成績(jī))from學(xué)生表

where班號(hào)="會(huì)計(jì)學(xué)091")and班號(hào)<>"會(huì)計(jì)學(xué)091"orderby入學(xué)成績(jī)desc;

.ALL功能【例3-49】【例3-49】求小于等于所有班號(hào)為“會(huì)計(jì)學(xué)091”學(xué)生入學(xué)成績(jī)的學(xué)生學(xué)號(hào)、姓名和入學(xué)成績(jī)。.ALL功能【例3-49】2select學(xué)號(hào),姓名,班號(hào),入學(xué)成績(jī)from學(xué)生表where入學(xué)成績(jī)<=all(select入學(xué)成績(jī)

from學(xué)生表

where班號(hào)="會(huì)計(jì)學(xué)091")and班號(hào)<>"會(huì)計(jì)學(xué)091"orderby入學(xué)成績(jī)desc;結(jié)果為小于等于“會(huì)計(jì)學(xué)091”班最小成績(jī)556分的所有非“會(huì)計(jì)學(xué)091”同學(xué)。思考：在子查詢中使用聚合函數(shù)完成上述功能。.2.SQL合并查詢SQL合并查詢就是將兩個(gè)SELECT語(yǔ)句的查詢結(jié)果通過(guò)并運(yùn)算（UNION）合并為一個(gè)查詢結(jié)果。SQL合并查詢要求兩個(gè)查詢的字段個(gè)數(shù)相同，且對(duì)應(yīng)字段的數(shù)據(jù)類型相同。

.SQL合并查詢【例3-50】【例3-50】使用SQL合并查詢運(yùn)算求“會(huì)計(jì)學(xué)081”班和“會(huì)計(jì)學(xué)091”的學(xué)號(hào)、姓名和班號(hào)。

雖然該查詢操作可以使用集合包含條件描述，但也可使用SQL合并查詢完成。.SQL合并查詢【例3-50】2select學(xué)號(hào),姓名,班號(hào)

from學(xué)生表

where班號(hào)=“會(huì)計(jì)學(xué)081”

union

select學(xué)號(hào),姓名,班號(hào)

from學(xué)生表

where班號(hào)=“會(huì)計(jì)學(xué)091”;.3.2.6交叉表查詢交叉表查詢是Access特有的SQL查詢語(yǔ)句。【例3-51】求給定課程選修總?cè)藬?shù),但要給出各班級(jí)的人數(shù)。transformcount(d.學(xué)號(hào))asAAselect課程名,count(d.學(xué)號(hào))as選課人數(shù)from班級(jí)表a,學(xué)生表b,課程表c,成績(jī)表dwherea.班號(hào)=b.班號(hào)andc.課程號(hào)=d.課程號(hào)andb.學(xué)號(hào)=d.學(xué)號(hào)groupby課程名pivota.班號(hào);

.交叉表查詢---語(yǔ)句.查詢向?qū)Ь帉?xiě)交叉查詢【例3-52】【例3-52】使用查詢向?qū)?，編?xiě)交叉查詢。（1）打開(kāi)Access“教學(xué)管理數(shù)據(jù)庫(kù)”文件。（2）編寫(xiě)一個(gè)稱為“查詢4張表”的查詢，語(yǔ)句如下：selecta.專業(yè),a.班號(hào),d.學(xué)號(hào),d.課程號(hào),c.課程名FROM班級(jí)表a,學(xué)生表b,課程表c,成績(jī)表dwherea.班號(hào)=b.班號(hào)andc.課程號(hào)=d.課程號(hào)andb.學(xué)號(hào)=d.學(xué)號(hào);.查詢向?qū)?xiě)交叉查詢【例3-52】2（3）啟動(dòng)查詢向?qū)?。在出現(xiàn)的“新建查詢”對(duì)話框中選擇“交叉表查詢向?qū)А边x項(xiàng)后，單擊“確定”按鈕。.查詢向?qū)?xiě)交叉查詢【例3-52】2.查詢向?qū)?xiě)交叉查詢【例3-52】3（4）選擇表或查詢包含交叉表查詢結(jié)果所需字段。.查詢向?qū)?xiě)交叉查詢【例3-52】4（5）選擇字段作為行標(biāo)題。.查詢向?qū)?xiě)交叉查詢【例3-52】5（6）選擇字段作為列標(biāo)題。.查詢向?qū)?xiě)交叉查詢【例3-52】6（7）選擇行列交叉點(diǎn)聚合計(jì)算方法。.查詢向?qū)?xiě)交叉查詢【例3-52】7（8）指定交叉查詢名稱。.查詢向?qū)?xiě)交叉查詢【例3-52】8（9）交叉查詢結(jié)果。.2.交叉查詢語(yǔ)法TRANSFORM聚合函數(shù)

select語(yǔ)句

PIVOTpivotfield[IN(value1[,value2[,...]])]TRANSFORM語(yǔ)句各部分含義如表3-12所示。transformcount(d.學(xué)號(hào))asAAselect課程名,count(d.學(xué)號(hào))as選課人數(shù)from班級(jí)表a,學(xué)生表b,課程表c,成績(jī)表dwherea.班號(hào)=b.班號(hào)andc.課程號(hào)=d.課程號(hào)andb.學(xué)號(hào)=d.學(xué)號(hào)groupby課程名pivota.班號(hào);.3.3數(shù)據(jù)操縱包括插入(INSERT）、刪除(DELETE）和更新(UPDATE）三種。插入、刪除和修改操作有可能導(dǎo)致數(shù)據(jù)違背數(shù)據(jù)庫(kù)完整性約束，操作時(shí)必須細(xì)心。例如本書(shū)教學(xué)管理數(shù)據(jù)庫(kù)中如果成績(jī)表中有某個(gè)同學(xué)的成績(jī)，學(xué)生表和成績(jī)