《數(shù)據(jù)庫(kù)原理及應(yīng)用2》課件第5章

第5章結(jié)構(gòu)化查詢(xún)語(yǔ)言SQL5.1SQL簡(jiǎn)介5.2SELECT查詢(xún)語(yǔ)句5.3基于單表查詢(xún)5.4基于多表的聯(lián)接查詢(xún)5.5子查詢(xún)5.6在查詢(xún)的基礎(chǔ)上創(chuàng)建新表5.7本章小結(jié)

5.1SQL簡(jiǎn)介

5.1.1SQL概述

20世紀(jì)70年代初，E.F.Codd在計(jì)算機(jī)學(xué)會(huì)(AssociationofComputerMachinery，ACM)期刊CommunicationsoftheACM(ACM通訊)發(fā)表了題為“ARelationalModelofDataforLargeSharedDataBanks”(大型共享數(shù)據(jù)庫(kù)的數(shù)據(jù)關(guān)系模型)的論文，該論文提出的關(guān)系數(shù)據(jù)庫(kù)模型成為今天最為權(quán)威的關(guān)系型數(shù)據(jù)庫(kù)管理模型。5.1.2SQL分類(lèi)

SQL是目前使用最廣泛的數(shù)據(jù)庫(kù)語(yǔ)言，就象SQL的名字一樣，我們可以通過(guò)容易理解的查詢(xún)語(yǔ)言，來(lái)和數(shù)據(jù)庫(kù)打交道，從數(shù)據(jù)庫(kù)中得到我們想要的數(shù)據(jù)。對(duì)于SQL語(yǔ)言，由下列四個(gè)組成部分：

(1)數(shù)據(jù)定義語(yǔ)言DDL(DataDefinitionLanguage)

(2)數(shù)據(jù)處理語(yǔ)言DML(DataManipulationLanguage)

(3)數(shù)據(jù)控制語(yǔ)言DCL(DataControlLanguage)

(4)數(shù)據(jù)庫(kù)事務(wù)(DatabaseTransactions)

5.2SELECT查詢(xún)語(yǔ)句

在SQL中，使用SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)的查詢(xún)，其應(yīng)用靈活，功能強(qiáng)大。

1．基本格式

SELECT[ALL|DISTICT]<字段表達(dá)式1>[，<字段表達(dá)式2>[，…]]

FROM<表名1>[，<表名2>[，…]]

[WHERE<篩選條件表達(dá)式>]

[GROUPBY<分組表達(dá)式>[HAVING<分組條件表達(dá)式>]]

[ORDERBY<字段>[ASC|DESC]]

2．語(yǔ)句說(shuō)明

(1)?SELECT語(yǔ)句的基本格式是由SELECT子句、FROM子句和WHERE子句組成的查詢(xún)塊。

(2)整個(gè)SELECT語(yǔ)句的含義是：根據(jù)WHERE子句的篩選條件表達(dá)式，從FROM子句指定的表中找出滿足條件的記錄，再按SELECT語(yǔ)句中指定的字段次序，用篩選出記錄中的字段值構(gòu)造一個(gè)顯示結(jié)果表。

(3)如果有GROUP子句，則將結(jié)果按<分組表達(dá)式>的值進(jìn)行分組，該值相等的記錄為一個(gè)組。

(4)如果GROUP子句帶HAVING短語(yǔ)，則只有滿足指定條件的組才會(huì)顯示輸出。

5.3基于單表查詢(xún)

5.3.1查詢(xún)表中指定的字段

一般情況下，用戶只對(duì)表中的一部分字段感興趣，通過(guò)SELECT語(yǔ)句，就可以“過(guò)濾”掉某些字段的數(shù)據(jù)，而只顯示用戶需要的數(shù)據(jù)。

【例1】顯示學(xué)生表中學(xué)生的學(xué)號(hào)、姓名和性別。

select學(xué)號(hào)，姓名，性別，總學(xué)分from學(xué)生表

執(zhí)行結(jié)果如圖5-1所示。圖5-1執(zhí)行結(jié)果5.3.2通配符“*”的使用

在SELECT語(yǔ)句中，可以使用通配符“*”顯示所有字段。

【例2】列出學(xué)生表中所有的字段。

select*from學(xué)生表

執(zhí)行結(jié)果如圖5-2所示。圖5-2執(zhí)行結(jié)果5.3.3使用單引號(hào)加入字符串

在SELECT語(yǔ)句中，可以在一個(gè)字段的前面加上一個(gè)單引號(hào)字符串，對(duì)后面的字段起說(shuō)明的作用。

【例3】顯示學(xué)生表中學(xué)生的姓名和出生年月日。

select姓名,‘出生年月日’,出生日期from學(xué)生表

執(zhí)行結(jié)果如圖5-3所示。圖5-3執(zhí)行結(jié)果5.3.4使用別名

在顯示結(jié)果時(shí)，可以指定以別名代替原來(lái)的字段名稱(chēng)，共有3種方法：

(1)采用“字段名稱(chēng)AS別名”的格式；

(2)采用“字段名稱(chēng)別名”的格式；

(3)采用“別名=字段名稱(chēng)”的格式。

【例4】顯示學(xué)生表中的姓名，并在標(biāo)題中顯示“名單”，而不是“姓名”。下面3條語(yǔ)句執(zhí)行結(jié)果相同。

select姓名as名單from學(xué)生表

select姓名名單from學(xué)生表

select‘名單’=姓名from學(xué)生表

執(zhí)行結(jié)果如圖5-4所示。圖5-4執(zhí)行結(jié)果5.3.5顯示表達(dá)式的值

在SELECT語(yǔ)句后面可以是字段表達(dá)式，字段表達(dá)式不僅可以是算術(shù)表達(dá)式，還可以是字符串常量和函數(shù)等。

【例5】顯示學(xué)生表中所有學(xué)生姓名和年齡。

select姓名，year(getdate())-year(出生日期)as年齡from學(xué)生表

執(zhí)行結(jié)果如圖5-5所示。圖5-5執(zhí)行結(jié)果5.3.6使用DISTINCT短語(yǔ)消除重復(fù)的記錄

DISTINCT短語(yǔ)能夠從結(jié)果表中去掉重復(fù)的記錄。

【例6】查詢(xún)學(xué)生表中的姓名，去掉重復(fù)的名字。

selectdistinct姓名名單from學(xué)生表

執(zhí)行結(jié)果如圖5-6所示。圖5-6執(zhí)行結(jié)果5.3.7使用WHERE子句查詢(xún)特定的記錄

SQL是一種集合處理語(yǔ)言，所以數(shù)據(jù)修改及數(shù)據(jù)檢索語(yǔ)句會(huì)對(duì)表中的所有記錄(行)起作用，除非使用WHERE子句來(lái)限定查詢(xún)的范圍。

1．算術(shù)運(yùn)算符

(1)?+：加法運(yùn)算符或正號(hào)。

(2)?-：減號(hào)運(yùn)算或負(fù)號(hào)。

(3)?*：乘法運(yùn)算符。

(4)?/：除法運(yùn)算符。

(5)?%：取模運(yùn)算符，即返回兩個(gè)整數(shù)相除后的余數(shù)。

2．位運(yùn)算符

位運(yùn)算符可以對(duì)整數(shù)或二進(jìn)制數(shù)據(jù)進(jìn)行按位的與(&)、或(|)、異或(^)和求反(～)等邏輯運(yùn)算。

在使用與(&)、或(|)、異或(^)運(yùn)算時(shí)需要兩個(gè)操作數(shù)的參與。求反(～)運(yùn)算只需要一個(gè)操作數(shù)參加，但注意它只能對(duì)int、smallint、tinyint或bit等類(lèi)型的操作數(shù)進(jìn)行運(yùn)算。

(1)?&(與)：對(duì)參數(shù)進(jìn)行按位與(AND)運(yùn)算。

(2)?|(或)：對(duì)參數(shù)進(jìn)行按位或(OR)運(yùn)算。

(3)?～(反)：對(duì)參數(shù)進(jìn)行按位反(NOT)運(yùn)算。

(4)?^(異或)：對(duì)參數(shù)進(jìn)行按位異或運(yùn)算。

3．比較運(yùn)算符

比較運(yùn)算符可以用來(lái)比較兩個(gè)表達(dá)式的大小。

(1)?>?(大于)：

(2)?>=?(大于)等于：

(3)?<?(小于)：

(4)?<=?(小于等于)：

(5)?<>?(不等于)：

(6)?！=?(不等于)：

(7)?=?(等于)：

(8)?！>?(不大于)：

(9)?！<?(不小于)：比較運(yùn)算得到的返回值是true(1)、false(0)和undefined三種：

(1)如果比較表達(dá)式的條件成立，則返回值為true。

(2)如果比較表達(dá)式的條件不成立，則返回值為false。

(3)如果用戶打開(kāi)ANSI_NULLS選項(xiàng)，當(dāng)比較表達(dá)式的操作數(shù)中任何一個(gè)為null時(shí)，則返回值為undefined。

4．邏輯運(yùn)算符

邏輯運(yùn)算符的返回值為true、false、unknown三種，它用來(lái)測(cè)試表達(dá)式條件是否為真。

(1)?AND：

(2)?OR：

(3)?NOT：謂詞定義了一種用于表格中行的邏輯條件，Transact-SQL語(yǔ)言支持以下謂詞。

(1)?[NOT]BETWEEN：

(2)?[NOT]LIKE：

(3)?[NOT]IN：

(4)ALL、SOME、ANY：

5．連接運(yùn)算符和賦值運(yùn)算符

(1)連接運(yùn)算符：SQLServer利用“+”號(hào)實(shí)現(xiàn)字符串之間的連接。要注意的是，連接運(yùn)算符是在字符串?dāng)?shù)據(jù)類(lèi)型之間使用的，而并非數(shù)字類(lèi)型表達(dá)式的加法算術(shù)運(yùn)算。

(2)賦值運(yùn)算符：利用“=”號(hào)實(shí)現(xiàn)將表達(dá)式的值賦予一個(gè)變量或?yàn)槟沉兄刀x列標(biāo)題。

6．關(guān)系運(yùn)算符的使用

【例7】顯示性別為女的學(xué)生的信息。

select*from學(xué)生表where性別=‘女’

執(zhí)行結(jié)果如圖5-7所示。圖5-7執(zhí)行結(jié)果

7．邏輯運(yùn)算符的使用

【例8】顯示性別為男并且備注為團(tuán)員的學(xué)生的信息。

select*from學(xué)生表where性別=‘男’and備注=‘團(tuán)員’

執(zhí)行結(jié)果如圖5-8所示。圖5-8執(zhí)行結(jié)果

8．特殊運(yùn)算符的使用

使用BETWEEN…AND的作用是定義表達(dá)式在兩數(shù)之間，格式為：

表達(dá)式[NOT]BETWEEN表達(dá)式1AND表達(dá)式2

【例9】從學(xué)生表里查詢(xún)年齡在20到23歲之間的學(xué)生信息(包括20和23歲)。

select*from學(xué)生表whereyear(getdate())-year(出生日期)

between20and23

執(zhí)行結(jié)果如圖5-9所示。注：此例中g(shù)etdate()=2013-5-2。圖5-9執(zhí)行結(jié)果

【例10】顯示所有備注內(nèi)容為空的學(xué)生信息。

select*from學(xué)生表where備注isnull

【例11】列出2013002和2013004學(xué)生的學(xué)號(hào)和姓名。

select學(xué)號(hào),姓名from學(xué)生表where學(xué)號(hào)in

(‘2013002’,‘2013004’)

執(zhí)行結(jié)果如圖5-10所示。圖5-10執(zhí)行結(jié)果

【例12】列出所有姓“劉”的學(xué)生信息。

本例主要學(xué)習(xí)LIKE與通配符“_”、“%”的使用及模式匹配表達(dá)式書(shū)寫(xiě)格式。LIKE關(guān)鍵字用于指出一個(gè)字符串是否與指定的字符串匹配，其運(yùn)算對(duì)象可以是char、text、datetime和smalldatetime等類(lèi)型的數(shù)據(jù)，返回邏輯值true和false。LIKE關(guān)鍵字表達(dá)式的格式為：

字符表達(dá)式1[NOT]LIKE字符表達(dá)式2

select*from學(xué)生表where姓名like‘劉_’

select*from學(xué)生表where姓名like'[張,劉]%'5.3.8使用ORDERBY子句對(duì)查詢(xún)結(jié)果排序

在SELECT語(yǔ)句中，使用“ORDERBY”子句可以對(duì)查詢(xún)結(jié)果進(jìn)行升序或降序的排序，ASC表示升序，為默認(rèn)值，DESC表示降序。排序時(shí)空值(NULL)被認(rèn)為最小值。

【例13】顯示學(xué)生表中所有學(xué)生的信息，并按姓名降序排序。

select*from學(xué)生表orderby姓名desc

執(zhí)行結(jié)果如圖5-11所示。圖5-11執(zhí)行結(jié)果5.3.9SQL的聚合函數(shù)

常用的幾個(gè)聚合函數(shù)如表5-1所示。

1．COUNT函數(shù)

【例14】統(tǒng)計(jì)學(xué)生表中的人數(shù)。

selectcount(*)人數(shù)from學(xué)生表

2．MAX和MIN函數(shù)

【例15】查找學(xué)生表中年齡最大和最小的學(xué)生出生日期。

selectmax(出生日期)年齡最大值，min(出生日期)年齡最小值from學(xué)生表

執(zhí)行結(jié)果如圖5-12所示。圖5-12執(zhí)行結(jié)果

3．AVG和SUM函數(shù)

【例16】顯示成績(jī)表中成績(jī)的平均值和總和。

selectavg(成績(jī))平均成績(jī)，sum(成績(jī))總和from成績(jī)表

執(zhí)行結(jié)果如圖5-13所示。圖5-13執(zhí)行結(jié)果5.3.10使用GROUPBY子句對(duì)查詢(xún)結(jié)果進(jìn)行分組

利用SQL的GROUPBY子句，能夠快速而簡(jiǎn)便地將查詢(xún)結(jié)果表按照指定的字段進(jìn)行分組，值相等的記錄分為一組。

GROUPBY子句一般和SQL的聚合函數(shù)一起使用。

【例17】統(tǒng)計(jì)成績(jī)表中每人的平均成績(jī)。

select學(xué)號(hào)，avg(成績(jī))as平均成績(jī)from成績(jī)表groupby學(xué)號(hào)

執(zhí)行結(jié)果如圖5-14所示。

圖5-14執(zhí)行結(jié)果

【例18】統(tǒng)計(jì)男女生的人數(shù)。

select性別，count(*)as總分from學(xué)生表groupby性別

執(zhí)行結(jié)果如圖5-15所示。

圖5-15執(zhí)行結(jié)果5.3.11使用HAVING子句篩選結(jié)果表

在實(shí)際使用中，往往還要對(duì)分組后的結(jié)果按某種條件再進(jìn)行篩選，而只輸出滿足用戶指定條件的記錄。在SQL中，HAVING子句能完成此功能。

【例19】將例17得到的結(jié)果中平均值大于75分的篩選出來(lái)。

select學(xué)號(hào)，avg(成績(jī))as平均成績(jī)from成績(jī)表groupby學(xué)號(hào)havingavg(成績(jī))>75

執(zhí)行結(jié)果如圖5-16所示。圖5-16執(zhí)行結(jié)果

5.4基于多表的聯(lián)接查詢(xún)

通過(guò)聯(lián)接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。聯(lián)接條件表示了SQLServer2000應(yīng)如何使用一個(gè)表中的數(shù)據(jù)來(lái)選擇另一個(gè)表中的行。

【例20】下面使用聯(lián)接查詢(xún)學(xué)生的成績(jī)。

select學(xué)生表.姓名,成績(jī)表.成績(jī)

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

執(zhí)行結(jié)果如圖5-17所示。圖5-17執(zhí)行結(jié)果

1．內(nèi)聯(lián)接

內(nèi)聯(lián)接按照on所指定的連接條件合并兩個(gè)表，返回滿足條件的行。在SQL-92標(biāo)準(zhǔn)中，內(nèi)聯(lián)接可在FROM或WHERE子句中指定。這是WHERE子句中唯一一種SQL-92支持的聯(lián)接類(lèi)型。WHERE子句中指定的內(nèi)聯(lián)接稱(chēng)為舊式內(nèi)聯(lián)接。

2．外聯(lián)接

當(dāng)且僅當(dāng)至少有一個(gè)同屬于兩表的行符合聯(lián)接條件時(shí)，內(nèi)聯(lián)接才返回行。內(nèi)聯(lián)接消除與另一個(gè)表中的任何行不匹配的行；而外聯(lián)接會(huì)返回FROM子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何WHERE或HAVING搜索條件，將檢索通過(guò)“左向外聯(lián)接”引用的左表的所有行，以及通過(guò)“右向外聯(lián)接”引用的右表的所有行。完整外部聯(lián)接中兩個(gè)表的所有行都將返回。

SQLServer2000對(duì)在FROM子句中指定的外聯(lián)接使用以下關(guān)鍵字：

(1)?leftouterjoin或leftjoin(左向外聯(lián)接)

(2)?rightouterjoin或rightjoin(右向外聯(lián)接)

(3)?fullouterjoin或fulljoin(完整外部聯(lián)接)

1)左向外聯(lián)接

左向外聯(lián)接簡(jiǎn)稱(chēng)為左聯(lián)接，其結(jié)果包括第一個(gè)命名表(“左”表，出現(xiàn)在JOIN子句的最左邊)中的所有行。不包括右表中的不匹配行。

【例21】下面的SQL語(yǔ)句首先在課程表中插入一個(gè)記錄，然后采用左聯(lián)接顯示所有課程的成績(jī)，最后刪除這個(gè)新插入的記錄。圖5-18執(zhí)行結(jié)果

2)右向外聯(lián)接

右向外聯(lián)接簡(jiǎn)稱(chēng)為右聯(lián)接。其結(jié)果中包括第二個(gè)命名表(“右”表，出現(xiàn)在JOIN子句的最右邊)中的所有行。不包括左表中的不匹配行。

【例22】將上面的例子中的左聯(lián)接改為右聯(lián)接。

insert課程表values(‘20008’,‘軟件工程’,90,4)

go

select課程表.課程名稱(chēng),成績(jī)表.成績(jī)

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

go

delete課程表where課程號(hào)=‘20008’

執(zhí)行結(jié)果如圖5-19所示。圖5-19執(zhí)行結(jié)果

3)完整外部聯(lián)接

若要在聯(lián)接結(jié)果中包括不匹配的行以便保留不匹配信息，可以使用完整外部聯(lián)接。SQLServer2000提供完整外部聯(lián)接運(yùn)算符FULLOUTERJOIN，不管另一個(gè)表是否有匹配的值，此運(yùn)算符都包括兩個(gè)表中的所有行。

【例23】將上面的例子改為完整外部聯(lián)接。

insert課程表values(‘20008’,‘軟件工程’,90,4)

go

select課程表.課程名稱(chēng),成績(jī)表.成績(jī)

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

go

delete課程表where課程號(hào)=‘20008’

執(zhí)行結(jié)果如圖5-20所示。圖5-20執(zhí)行結(jié)果

3．交叉聯(lián)接

在這類(lèi)聯(lián)接的結(jié)果集內(nèi)，兩個(gè)表中每?jī)蓚€(gè)可能成對(duì)的行占一行。交叉聯(lián)接不使用WHERE子句。在數(shù)學(xué)上，就是表的笛卡兒積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于笛卡爾積結(jié)果集的大小。

【例24】下面的SQL語(yǔ)句使用交叉聯(lián)接產(chǎn)生所有可能的組合。

select學(xué)生表.*，成績(jī)表.*

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

執(zhí)行結(jié)果如圖5-21所示。圖5-21執(zhí)行結(jié)果

5.5子查詢(xún)

子查詢(xún)是一個(gè)SELECT查詢(xún)，它嵌套在SELECT、INSERT、UPDATE、DELETE語(yǔ)句或其他子查詢(xún)中。子查詢(xún)也稱(chēng)為內(nèi)部查詢(xún)或內(nèi)部選擇，而包含子查詢(xún)的語(yǔ)句也稱(chēng)為外部查詢(xún)或外部選擇。

【例25】下面的SQL語(yǔ)句使用子查詢(xún)來(lái)查詢(xún)陳艷的成績(jī)。

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

(select學(xué)號(hào)from學(xué)生表where姓名=‘陳艷’)

使用下面的聯(lián)接方式也能完成此功能：

select成績(jī)

from學(xué)生表innerjoin成績(jī)表on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)where姓名=‘陳艷’

執(zhí)行結(jié)果如圖5-22所示。圖5-22執(zhí)行結(jié)果

1．子查詢(xún)規(guī)則

在SQLServer2000中，由于子查詢(xún)也是使用SELECT語(yǔ)句組成，所以在SELECT語(yǔ)句使用中應(yīng)注意的問(wèn)題，在這里也適用。

2．子查詢(xún)類(lèi)型

可以在許多地方指定子查詢(xún)，例如：

(1)使用別名時(shí)。

(2)使用IN或NOTIN時(shí)。

(3)在UPDATE、DELETE和INSERT語(yǔ)句中。

(4)使用比較運(yùn)算符時(shí)。

(5)使用ANY、SOME或ALL時(shí)。

(6)使用EXISTS或NOTEXISTS時(shí)。

(7)在有表達(dá)式的地方。

1)使用IN或NOTIN

通過(guò)IN(或NOTIN)引入的子查詢(xún)結(jié)果是一列零值或更多值。子查詢(xún)返回結(jié)果之后，外部查詢(xún)將利用這些結(jié)果。

【例26】下面的SQL語(yǔ)句查詢(xún)選修“20003”課程號(hào)的學(xué)生信息。

select*from學(xué)生表where學(xué)號(hào)in

(select學(xué)號(hào)from成績(jī)表where課程號(hào)=‘20003’)

執(zhí)行結(jié)果如圖5-23所示。圖5-23執(zhí)行結(jié)果

【例27】下面的SQL語(yǔ)句查詢(xún)沒(méi)有選修“20003”課程號(hào)的學(xué)生信息。

select*from學(xué)生表where學(xué)號(hào)notin

(select學(xué)號(hào)from成績(jī)表where課程號(hào)=‘20003’)

執(zhí)行結(jié)果如圖5-24所示。圖5-24執(zhí)行結(jié)果

2)?UPDATE、DELETE和INSERT語(yǔ)句中的子查詢(xún)

子查詢(xún)可以嵌套在UPDATE、DELETE和INSERT語(yǔ)句以及SELECT語(yǔ)句中。

【例28】以下語(yǔ)句先在課程表中插入一個(gè)記錄，然后通過(guò)刪除沒(méi)有被選修的課程從而刪除該插入的記錄。

insert課程表values(‘20005’,‘離散數(shù)學(xué)’,51,2)

go

delete課程表where課程號(hào)notin

(select課程號(hào)from成績(jī)表)

go

select*from課程表

執(zhí)行結(jié)果如圖5-25所示。圖5-25執(zhí)行結(jié)果

3)比較運(yùn)算符的子查詢(xún)

子查詢(xún)可由一個(gè)比較運(yùn)算符(=、<>、>、>=、<、!>,!<或<=)引入。與使用IN引入的子查詢(xún)一樣，由未修改的比較運(yùn)算符(后面不跟IN、ANY或ALL等的比較運(yùn)算符)引入的子查詢(xún)必須返回單個(gè)值而不是值列表。如果這樣的子查詢(xún)返回多個(gè)值，SQLServer將顯示錯(cuò)誤信息。

【例29】下面的SQL語(yǔ)句查找成績(jī)高于平均分的成績(jī)記錄。

select學(xué)號(hào),課程號(hào),成績(jī)from成績(jī)表where成績(jī)>

(select