交通信息系統(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ù)庫的標準語言，對關(guān)系模型的發(fā)展和商用DBMS的研制起著重要的作用。SQL語言是介乎于關(guān)系代數(shù)和元組演算之間的一種語言。本章詳細介紹SQL的核心部分內(nèi)容：數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新和嵌入式SQL。34.1SQL概述

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

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

SQL有兩個標準：ANSISQL和1992年通過的修改版本SQL-92（簡稱

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

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，這一部分包括對基本表和視圖的授權(quán)、完整性規(guī)則的描述、事務(wù)控制等內(nèi)容。

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

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

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

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

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

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

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

SELECTA1，…，An FROMR1，…，Rm WHEREF

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

SELECT

目標表的列名或列表達式序列

FROM

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

[WHERE

行條件表達式][GROUPBY

列名序列

[HAVING

組條件表達式]][ORDERBY

列名[ASC|DESC]，…]

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

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

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

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

SELECTSNO,SNAMEFROMS;

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

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

例4.23查詢?nèi)w學生的詳細記錄。

SELECT*FROMS;

等價于：

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

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

SELECTSNAME,2009-AGEFROMS;

輸出結(jié)果：

Sname_________________

李小勇1989

劉晨1990

王名1991

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

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

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

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

SELECTDISTINCTSNOFROMSC;23例(1)SELECTSnoFROMSC;

或(默認ALL)SELECTALLSnoFROMSC;

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

結(jié)果：

Sno-------950019500224

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

例4.27查詢計算機科學系全體學生的名單。

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

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

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

例4.30可改寫為：

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

例4.32

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

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

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

例4.33

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

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

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

查詢學號為200915121的學生的詳細情況。

SELECT*FROMSWHERESNOLIKE’200915121’;

等價于：

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

查詢所有姓劉的學生的姓名、學號和性別。

SELECTSNAME,SNO,SEXFROMSWHERESNAMELIKE’劉%’;30

涉及空值的查詢例例4.41

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

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

查詢計算機科學系年齡在20歲以下的學生姓名。

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

例4.44

查詢選修了3號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)的降序排列。

SELECTSNO,GRADEFROMSCWHERECNO=’3’ORDERBYGRADEDESC;

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

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

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

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

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

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

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

計算選修l號課程的學生平均成績。

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

查詢選修l號課程的學生最高分數(shù)。

SELECTMAX(GRADE)FROMSCWHERCNO=’1’;36使用GROUPBY子句分組 細化集函數(shù)的作用對象未對查詢結(jié)果分組，聚合函數(shù)將作用于整個查詢結(jié)果對查詢結(jié)果分組后，聚合函數(shù)將分別作用于每個組4.3.4對查詢結(jié)果分組37這三個數(shù)取平均值第4-6分數(shù)取平均值最后三個數(shù)取平均值有一個學員參加考試例如，求每門課的平均成績。38分組查詢—GROUPBYSELECT

Cours