交通信息系統(tǒng)第八章結(jié)構(gòu)化查詢語言SQL1

1第8章結(jié)構(gòu)化查詢語言SQL

主要內(nèi)容SQL概述SQL的數(shù)據(jù)定義SQL的數(shù)據(jù)查詢數(shù)據(jù)更新嵌入式SQLTransactSQL語言概述

2本章概述

結(jié)構(gòu)化查詢語言SQL（StructuredQueryLanguage）是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語言，對(duì)關(guān)系模型的發(fā)展和商用DBMS的研制起著重要的作用。SQL語言是介乎于關(guān)系代數(shù)和元組演算之間的一種語言。本章詳細(xì)介紹SQL的核心部分內(nèi)容：數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新和嵌入式SQL。34.1SQL概述

主要內(nèi)容SQL的產(chǎn)生與發(fā)展SQL的組成及特點(diǎn)44.1.1SQL的產(chǎn)生與發(fā)展

1972年，IBM公司開始研制實(shí)驗(yàn)型關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)SYSTEMR，其配備的查詢語言稱為SQUARE(SpecifyingQueriesAsRelationalExpression)語言，語言中使用了較多的數(shù)學(xué)符號(hào)。1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL(StructuredEnglishQueryLanguage)語言。后來SEQUEL簡(jiǎn)稱為SQL(StructuredQueryLanguage)，即“結(jié)構(gòu)式查詢語言”，SQL的發(fā)音仍“sequel”?，F(xiàn)在SQL已經(jīng)成為一個(gè)標(biāo)準(zhǔn)。

SQL有兩個(gè)標(biāo)準(zhǔn)：ANSISQL和1992年通過的修改版本SQL-92（簡(jiǎn)稱

SQL2）。還有一個(gè)新的標(biāo)準(zhǔn)SQL3，它擴(kuò)充了SQL2，引入了遞歸、觸發(fā)器和對(duì)象等概念和機(jī)制。SQL簡(jiǎn)介SQL語言是應(yīng)用于數(shù)據(jù)庫(kù)的語言，本身是不能獨(dú)立存在的。它是一種非過程性(non-procedural)語言，與一般的高級(jí)語言(如C、JAVA)不同。一般的高級(jí)語言在存取數(shù)據(jù)庫(kù)時(shí)，需要依照每一行程序的順序處理許多的動(dòng)作。但是使用SQL時(shí)，只需告訴數(shù)據(jù)庫(kù)需要什么數(shù)據(jù)，怎么顯示就可以了。具體的內(nèi)部操作則由數(shù)據(jù)庫(kù)系統(tǒng)來完成。64.1.2SQL的組成及特點(diǎn)

SQL語言從功能上可以分為四部分：數(shù)據(jù)查詢（DataQuery）、數(shù)據(jù)操縱（DataManipulation）、數(shù)據(jù)定義（DataDefinition）和數(shù)據(jù)控制（DataControl）。數(shù)據(jù)定義語言，即SQLDDL，用于定義SQL模式、基本表、視圖、索引等結(jié)構(gòu)。數(shù)據(jù)操縱語言，即SQLDML。數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩類。其中數(shù)據(jù)更新又分成插入、刪除和修改三種操作。數(shù)據(jù)控制語言，即SQLDCL，這一部分包括對(duì)基本表和視圖的授權(quán)、完整性規(guī)則的描述、事務(wù)控制等內(nèi)容。

SQL的核心部分相當(dāng)于關(guān)系代數(shù)，同時(shí)又具有關(guān)系代數(shù)所沒有的許多特點(diǎn)，如聚集、數(shù)據(jù)庫(kù)更新等。7特點(diǎn)：綜合統(tǒng)一高度非過程化SQL是一種第四代語言（4GL），用戶只需提出“干什么”，無需具體指明“怎么干”，像存取路徑選擇和具體處理操作等，均有系統(tǒng)自動(dòng)完成。統(tǒng)一的語法結(jié)構(gòu)SQL有兩種使用方式：一是聯(lián)機(jī)交互使用，另一種是嵌入到某種高級(jí)語言程序設(shè)計(jì)語言中去使用。語言簡(jiǎn)潔8SQL的核心動(dòng)詞

功能動(dòng)詞數(shù)據(jù)庫(kù)查詢SELECT數(shù)據(jù)定義CREATE，DROP數(shù)據(jù)操縱INSERT，UPDATE，DELECT數(shù)據(jù)控制GRANT，REVOKE9操作對(duì)象操作方式創(chuàng)建刪除修改數(shù)據(jù)庫(kù)CREATEDATABASEDROPDATABASE表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX視圖CREATEVIEWDROPVIEWSQL的數(shù)據(jù)定義功能包括數(shù)據(jù)庫(kù)定義、表定義、視圖和索引的定義。

10學(xué)號(hào)SNO姓名SNAME性別SEX年齡AGE所在系SDEPT200915121200915122200915123200915125李小勇劉晨王洪敏張力MFFM20191819CSCSMAIS學(xué)生表：S(SNO,SNAME,SEX,AGE,SDEPT)課程表：C(CNO,CNAME,CPNO,CCREDIT)學(xué)生選課表：SC(SNO,CNO,GRADE)學(xué)生表實(shí)例例4.1

定義一個(gè)教務(wù)管理數(shù)據(jù)庫(kù)JWGL，它包含3個(gè)表：本章數(shù)據(jù)庫(kù)示例（教務(wù)管理數(shù)據(jù)庫(kù)）11課程號(hào)CNO課程名CNAME先修課CPNO學(xué)分CCREDIT1234567數(shù)據(jù)庫(kù)數(shù)學(xué)信息系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理C語言516764243424課程表實(shí)例學(xué)生表選課表實(shí)例學(xué)號(hào)SNO課程號(hào)CNO成績(jī)GRADE200915121200915121200915121200915122200915122123239285889080124.3SQL的數(shù)據(jù)查詢

主要內(nèi)容SELECT語句格式單表查詢聚集函數(shù)數(shù)據(jù)分組多表查詢集合操作134.3.1SELECT語句格式1、SELECT—FROM—WHERE句型 在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式：

πA1,…,An(σF(R1×…×Rm))

這里R1、…、Rm為關(guān)系，F(xiàn)是公式，A1、…、An為屬性。 針對(duì)上述表達(dá)式，SQL為此設(shè)計(jì)了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm WHEREF

這個(gè)句型是從關(guān)系代數(shù)表達(dá)式演變來的，但WHERE子句中的條件表達(dá)式F要比關(guān)系代數(shù)中公式更靈活。14在WHERE子句的條件表達(dá)式F中可使用下列運(yùn)算符算術(shù)比較運(yùn)算符：<、<=、>、>=、<>、!=邏輯運(yùn)算符：AND、OR、NOT集合成員資格運(yùn)算符：IN、NOTIN謂詞：EXISTS、ALL、SOME、UNIQUE 聚合函數(shù)：AVG、MIN、MAX、SUM、COUNTF中運(yùn)算對(duì)象還可以是另一個(gè)SELECT語句，即SELECT句型可以嵌套2、SELECT語句完整的句法如下：

SELECT

目標(biāo)表的列名或列表達(dá)式序列

FROM

基本表名和（或）視圖序列

[WHERE

行條件表達(dá)式][GROUPBY

列名序列

[HAVING

組條件表達(dá)式]][ORDERBY

列名[ASC|DESC]，…]

主語句SELECT-FROM-WHERE的含義是：

根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。16整個(gè)語句的執(zhí)行過程如下：(1)讀取FROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。(2)選取滿足WHERE子句中給出的條件表達(dá)式的元組。(3)按GROUP子句中指定列的值分組，同時(shí)提取滿足HAVING子句中組條件表達(dá)式的那些組。(4)按SELECT子句中給出的列名或列表達(dá)式求值輸出。(5)ORDER子句對(duì)輸出的目標(biāo)表進(jìn)行排序，按附加說明ASC升序排列，或按DESC降序排列。17根據(jù)查詢條件不同，可分為：?jiǎn)伪聿樵冞B接查詢嵌套查詢集合查詢184.3.2單表查詢查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作。一、選擇表中的若干列二、選擇表中的若干元組三、對(duì)查詢結(jié)果排序四、使用聚合函數(shù)五、對(duì)查詢結(jié)果分組

191、查詢表中的若干列⑴查詢指定列

在很多情況下，用戶只對(duì)表中的一部分屬性列感興趣，這時(shí)可以通過在SELECT子句的<目標(biāo)列表達(dá)式>中指定要查詢的屬性列。 例4.21查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。

SELECTSNO,SNAMEFROMS;

執(zhí)行過程：從S表中取出一個(gè)元組，再取出該元組在屬性SNO和SNAME上的值，形成一個(gè)新的元組作為輸出。對(duì)S表中的所有元組做相同的處理，最后形成一個(gè)結(jié)果關(guān)系作為輸出。20⑵查詢?nèi)苛?/p>

將表中的所有屬性列都選出來，可以有兩種方法。一種方法就是在SELECT關(guān)鍵字后面列出所有列名；另一種方法是如果列的顯示順序與其在基表中的順序相同，也可以簡(jiǎn)單地將<目標(biāo)列表達(dá)式>指定為“*”。

例4.23查詢?nèi)w學(xué)生的詳細(xì)記錄。

SELECT*FROMS;

等價(jià)于：

SELECTSNO,SNAME,SEX,AGE,SDEPTFROMS;21(3)查詢經(jīng)過計(jì)算的值

SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是表中的屬性列，也可以是表達(dá)式。例4.24查詢?nèi)w學(xué)生的姓名及其出生年份。

SELECTSNAME,2009-AGEFROMS;

輸出結(jié)果：

Sname_________________

李小勇1989

劉晨1990

王名1991

張立1990222、選擇表中的若干元組

⑴消除取值重復(fù)的行

兩個(gè)本來并不完全相同的元組，投影到指定的某些列上后，可能變成相同的行了，可以用DISTINCT取消它們。例4.26查詢選修了課程的學(xué)生學(xué)號(hào)。

SELECTSNOFROMSC;該查詢結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，必須指定DISTINCT關(guān)鍵詞：

SELECTDISTINCTSNOFROMSC;23例(1)SELECTSnoFROMSC;

或(默認(rèn)ALL)SELECTALLSnoFROMSC;

結(jié)果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：

Sno-------950019500224

⑵查詢滿足條件的元組查詢滿足指定條件的元組可以通過WHERE子句實(shí)現(xiàn)。查詢條件謂詞比較確定范圍確定集合字符匹配空值多重條件（邏輯運(yùn)算）=，>，<，>=，<=，!=，<>，!>，!<，NOT+上述運(yùn)算符BETWEENAND，NOTBETWEENANDIN，NOTINLIKE，NOTLIKEISNULL，ISNOTNULLAND，OR，NOT

例4.27查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的名單。

SELECTSNAMEFROMSWHERESDEPT=’CS’;25范圍查詢謂詞BETWEEN…AND…和NOTBETWEEN…AND…可以用來查找屬性值在（或不在）指定范圍內(nèi)的元組，其中BETWEEN后是范圍的下限（即低值），AND后是范圍的上限（即高值）。例4.30

查詢年齡在20～23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。SELECTSNAME,SDEPT,AGEFROMSWHEREAGEBETWEEN20AND23;與BETWEEN…AND…相對(duì)的謂詞是NOTBETWEEN…AND…。例4.31

查詢年齡不在20～23歲之間的學(xué)生姓名、系別和年齡。SELECTSNAME,SDEPT,AGEFROMSWHEREAGENOTBETWEEN20AND23;26

例4.30可改寫為：

SELECTSNAME,SDEPT,AGEFROMSWHEREAGE>=20ANDAGE<=23；27確定集合謂詞IN可以用來查找屬性值屬于指定集合的元組。

例4.32

查詢計(jì)算機(jī)科學(xué)系（CS）、數(shù)學(xué)系（MA）和信息系（IS）學(xué)生的姓名和性別。

SELECTSNAME,SEXFROMSWHERESDEPTIN(’CS’,’MA’,’IS’);

與IN相對(duì)的謂詞是NOTIN，用于查找屬性值不屬于指定集合的元組。

例4.33

查詢既不是計(jì)算機(jī)科學(xué)系、數(shù)學(xué)系，也不是信息系的學(xué)生的姓名和性別。

SELECTSNAME,SEXFROMSWHERESDEPTNOTIN(’CS’,’MA’,’IS’);28字符匹配謂詞LIKE可以用來進(jìn)行字符串的匹配。其一般語法格式如下：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]其含義是查找指定的屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個(gè)完整的字符串，也可以含有通配符%和_。其中：%（百分號(hào)）代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串。例如a%b表示以a開頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿足該匹配串。_（下劃線）代表任意單個(gè)字符。例如a_b表示以a開頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串。29例4.34

查詢學(xué)號(hào)為200915121的學(xué)生的詳細(xì)情況。

SELECT*FROMSWHERESNOLIKE’200915121’;

等價(jià)于：

SELECT*FROMSWHERESNO=’200915121’;如果LIKE后面的匹配串中不含通配符，則可以用=（等于）運(yùn)算符取代LIKE謂詞，用!=或<>（不等于）運(yùn)算符取代NOTLIKE謂詞。例4.35

查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別。

SELECTSNAME,SNO,SEXFROMSWHERESNAMELIKE’劉%’;30

涉及空值的查詢例例4.41

某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSNO,CNOFROMSCWHEREGRADEISNULL;/*分?jǐn)?shù)GRADE是空值*/多重條件查詢可用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個(gè)查詢條件。AND的優(yōu)先級(jí)高于OR，但可以用括號(hào)改變優(yōu)先級(jí)。例4.43

查詢計(jì)算機(jī)科學(xué)系年齡在20歲以下的學(xué)生姓名。

SELECTSNAMEFROMSWHERESDEPT=’CS’ANDAGE<20;313、ORDERBY子句（對(duì)查詢結(jié)果排序）使用ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示32

例4.44

查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列。

SELECTSNO,GRADEFROMSCWHERECNO=’3’ORDERBYGRADEDESC;

SnoGrade--------------9501095024950079295003829501082950097595014619500255查詢結(jié)果33

聚合函數(shù)是涉及整個(gè)關(guān)系的另一類運(yùn)算操作,通過聚合函數(shù)，可以把某一列中的值形成單個(gè)值。

5類主要聚合函數(shù)：計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和SUM（[DISTINCT|ALL]<列名>） 計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）4.3.3聚合函數(shù)34求最大值MAX（[DISTINCT|ALL]<列名>） 求最小值

MIN（[DISTINCT|ALL]<列名>）

DISTINCT短語：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值35例4.47

查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTSNO)FROMSC;學(xué)生每選修一門課，在SC中都有一條相應(yīng)的記錄。一個(gè)學(xué)生要選修多門課程，為避免重復(fù)計(jì)算學(xué)生人數(shù)，必須在COUNT函數(shù)中用DISTINCT短語。例4.48

計(jì)算選修l號(hào)課程的學(xué)生平均成績(jī)。

SELECTAVG(GRADE)FROMSCWHERECNO=’1’;例4.49

查詢選修l號(hào)課程的學(xué)生最高分?jǐn)?shù)。

SELECTMAX(GRADE)FROMSCWHERCNO=’1’;36使用GROUPBY子句分組 細(xì)化集函數(shù)的作用對(duì)象未對(duì)查詢結(jié)果分組，聚合函數(shù)將作用于整個(gè)查詢結(jié)果對(duì)查詢結(jié)果分組后，聚合函數(shù)將分別作用于每個(gè)組4.3.4對(duì)查詢結(jié)果分組37這三個(gè)數(shù)取平均值第4-6分?jǐn)?shù)取平均值最后三個(gè)數(shù)取平均值有一個(gè)學(xué)員參加考試?yán)纾竺块T課的平均成績(jī)。38分組查詢—GROUPBYSELECT

Cours