第五章關系數據庫標準語言——SQL第五章關系數據庫標準語言——SQL本章教學目標、重點和難點

本章教學目標：使學生了解SQL的功能和語言特點；熟練掌握用SQL進行表的定義、數據查詢和數據更新，掌握SQL的數據控制方法。2.教學重點：SQL的數據定義、數據查詢、數據維護和數據控制表示方法3.教學難點：用SQL實現嵌套查詢、組合查詢、使用分組和SQL函數查詢。本章教學目標、重點和難點本章教學目標：使學生了解SQL的功本章內容SQL的功能及特點數據定義語句數據查詢語句數據更新語句嵌入式SQL數據控制機制和語句本章內容SQL的功能及特點SQL的功能及特點19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了關系DBMS原型，并實現該語言美國批準SQL作為美國標準，隨后ISO也通過這一標準公布了SQL-92標準公布了SQL-99，也稱作SQL3SQL的功能及特點19741975-19791986-198SQL的功能及特點數據定義數據控制數據操縱SQL語句分類SQL的功能及特點數據定義數據控制數據操縱SQL語句分類SQL的功能及特點SQL語句分類數據定義數據操縱數據控制定義關系數據庫的模式、外模式和內模式，以實現對基本表、視圖以及索引文件的定義、修改和刪除等操作。包括數據查詢和數據更新兩種數據操作語句：數據查詢指對數據庫中的數據查詢、統計、分組、排序操作；數據更新指數據的插入、刪除、修改等數據維護操作。通過對數據庫用戶的授權和收權命令來實現有關數據的存取控制，以保證數據庫的安全性。SQL的功能及特點SQL語句分類數據定義數據操縱數據控制定義SQL的功能及特點SQL的特點1）SQL具有自主式語言和嵌入式語言兩種使用方式；2）SQL具有語言簡潔、易學易用的特點；SQL功能命令動詞數據定義（數據模式定義、刪除、修改）Create、Drop、Alter數據操縱（數據查詢和維護）Select、Insert、Update、Delete數據控制（數據存取控制權和收權）Grant、RemoveSQL的功能及特點SQL的特點SQL功能命令動詞數據定義（數SQL的功能及特點SQL的特點3）SQL支持三級模式結構存儲文件1存儲文件2基本表1基本表2基本表3基本表4視圖1視圖2SQL外模式模式內模式SQL的功能及特點SQL的特點存儲文件1存儲文件2基本表1基數據定義語句操作對象創建語句刪除語句修改語句基本表CreatetableDroptableAltertable索引CreateindexDropindex視圖CreateviewDropview數據庫CreatedatabaseDropdatabaseAlterdatabase1.語句格式約定符號尖括號<>中括號[]大括號{}或分隔符|[,…n]實際語義任選項必選項，必選其中一項前面的項可重復多次數據定義語句操作對象創建語句刪除語句修改語句基本表Creat數據定義語句2.一般語法規定SQL中的數據項（包括列項、表和視圖）分隔符為“,”，其字符串常數的定界符用單引號“’”表示3.SQL特殊語法規定SQL的關鍵詞一般使用大寫字母表示；語句的結束符為“;”語句一般應采用格式化書寫方式數據定義語句2.一般語法規定數據定義語句基本表的定義和維護1.基本表的定義定義基本表語句的一般格式為：CREATETABLE[〈庫名〉]〈表名〉(〈列名〉〈數據類型〉[〈列級完整性約束條件〉][，〈列名〉〈數據類型〉[〈列級完整性約束條件〉]][，…n]

[，〈表級完整性約束條件〉][，…n])；數據定義語句基本表的定義和維護定義基本表語句的一般格式為：數據定義語句建立基本表：學生(學號，姓名，年齡，性別，所在系)；

課程(課程號，課程名，先行課)；選課(學號，課程號，成績).CREATETABLE課程(課程號CHAR(5)PRIMARYKEY，

課程名CHAR(20)，

先行課CHAR(5)

)；數據定義語句建立基本表：CREATETABLE課程(課數據定義語句（1）SQL支持的數據類型類型表示類型說明數值型數據SMALLINT半字長二進制證書，15bit數據INTEGER或INT全字長（4字長）整數，31bit數據DECIMAL(p[,q])十進制數，共p位，其中小數點后q位，0《q《p，q=0時可省略不寫FLOAT雙字長浮點數字符型數據CHARTER(n)或CHAR(n)長度為n的定長圖形字符串VARCHAR(n)最大長度為n的變長字符串特殊數據類型GRAPHIC(n)長度為n的定長圖形字符串VARGRAPHIC(n)最大長度為n的變長圖形字符串日期時間型DATE日期型，格式為YYYY-MM-DDTIME時間型，格式為HH.MM.SSTIMESTAMP日期加時間數據定義語句（1）SQL支持的數據類型類型表示類型說明數值型數據定義語句（2）列級完整性的約束條件1）NOTNULL或NULL約束NOTNULL約束不允許字段值為空而NULL約束允許字段值為空。2）UNIQUE約束UNIQUE約束是惟一性約束，即不允許列中出現重復的屬性值。3）DEFAULT約束DEFAULT為默認值約束。DEFAULT〈約束名〉〈默認值〉FOR〈列名〉4）CHECK約束CHECK為檢查約束。

CONSTRAINT〈約束名〉CHECK(〈約束條件表達式〉)數據定義語句（2）列級完整性的約束條件數據定義語句建立基本表：學生(學號，姓名，年齡，性別，所在系)；

課程(課程號，課程名，先行課)；選課(學號，課程號，成績).CREATETABLE學生(學號CHAR(5)NOTNULLUNIQUE，

姓名CHAR(8)NOTNULL，

年齡SMALLINT，

性別CHAR(2)，

所在系CHAR(20)，

DEFAULTC120FOR年齡，

CONSTRAINTC2CHECK(性別IN('男'，'女')))；

數據定義語句建立基本表：CREATETABLE學生(學數據定義語句（3）表級完整性的約束條件1）UNIQUE約束UNIQUE約束是惟一性約束，當要求列祖的值不能有重復值時，使用UNIQUE約束定義。2）PRIMARYKEY約束定義主碼，保證惟一性和非空性。CONTRAINT〈約束名〉PRIMARYKEY[CLUSTERED](〈列組〉)3）FOREIGNKEY約束用于定義參照完整性。

CONTRAINT〈約束名〉FOREIGNKEY(〈外碼〉)

REFERENCES〈被參照表名〉(〈與外碼對應的主碼名〉)數據定義語句（3）表級完整性的約束條件數據定義語句建立基本表：學生(學號，姓名，年齡，性別，所在系)；

課程(課程號，課程名，先行課)；選課(學號，課程號，成績).CREATETABLE選課(學號CHAR(5)，

課程號CHAR(5)，

成績SMALLINT，

CONSTRAINTC3CHECK(成績BETWEEN0AND100)，

CONSTRAINTC4PRIMARYKEY(學號，課程號)，

CONSTRAINTC5FOREIGNKEY(學號)REFERENCES學生(學號)，

CONSTRAINTC6FOREIGNKEY(課程號)REFERENCES課程(課程號))；數據定義語句建立基本表：CREATETABLE選課(學數據定義語句2.修改基本表ALTERTABLE〈表名〉

[ADD(〈新列名〉〈數據類型〉[完整性約束][，…n])]

[DROP〈完整性約束名〉]

[MODIFY(〈列名〉〈數據類型〉[，…n])]；使用ADD子句增加新列

【例5-2】向課程表中增加“學時”字段。

ALTERTABLE課程ADD學時SMALLINT；使用MODIFY子句修改列的原定義：MODIFY子句主要用于加寬原列的寬度使用DROP子句刪除指定的完整性約束條件

【例5-3】刪除學生表中對年齡的默認值的定義。

ALTERTABLE學生DROPC1；數據定義語句2.修改基本表ALTERTABLE〈表名〉

數據定義語句3.刪除基本表刪除基本表的一般格式為：DROPTABLE〈表名〉；數據定義語句3.刪除基本表刪除基本表的一般格式為：DROP數據定義語句索引的定義和維護1.索引的作用使用索引可以明顯地加快數據查詢的速度；先將索引文件讀入內存，根據索引項找到元組的地址，然后再根據地址將元組數據直接讀入計算機。由于索引文件中只含有索引項和元組地址，所以文件小，一般可一次讀入內存索引文件中的索引項是經過排序的，可以很快地找到索引項值和元組地址數據定義語句索引的定義和維護使用索引可以明顯地加快數據查詢的數據定義語句索引的定義和維護1.索引的作用使用索引可以明顯地加快數據查詢的速度；

使用索引可保證數據的惟一性；使用索引可以加快連接速度。2.建立索引的原則索引的建立和維護由DBA和DBMS完成；大表應當建索引，小表則不必建索引；對于一個基本表，不要建立過多的索引；根據查詢要求建索引。數據定義語句索引的定義和維護使用索引可以明顯地加快數據查詢的數據定義語句3.建立索引的格式建立格式為：

CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；<表名>是要建索引的基本表的名字；每個<列名>后面還可以用<次序>指定索引值的排列次序，次序可選ASC（升序）或DESC（降序），默認值為ASC；UNIQUE表示該索引的每一個索引值只對應唯一的數據記錄；CLUSTER表示要建立的索引是聚簇索引，使基本表中數據的物理順序與索引項的排列順序一致。數據定義語句3.建立索引的格式建立格式為：

CREATE[數據定義語句3.建立索引的格式【例5-4】為學生_課程數據庫中的學生、課程和選課三個表建立索引。其中，學生表按學號升序建立索引；課程表按課程號升序建惟一索引；選課表按學號升序和課程號降序建惟一索引。

CREATEUNIQUEINDEX學號ON學生(學號)；

CREATEUNIQUEINDEX課程號ON課程(課程號)；

CREATEUNIQUEINDEX選課號ON選課(學號ASC，課程號DESC)；數據定義語句3.建立索引的格式【例5-4】為學生_課程數數據定義語句4.刪除索引刪除索引格式為：DROPINDEX〈索引名〉；例：刪除學生表的學生姓名索引DROPINDEX學生姓名數據定義語句4.刪除索引刪除索引格式為：DROPI數據定義語句視圖的定義和維護1.視圖的優點視圖是根據子模式設計的關系，由一個或幾個基本表導出的虛表。視圖能夠簡化用戶的操作；視圖機制可以減少用戶對數據庫中數據結構的調整操作，使用戶將注意力集中在所關心的數據上。2）視圖機制可以使用戶以不同的方式看待同一數據；當多用戶共享同一個數據庫的數據時，通過視圖機制可以實現各個用戶對數據的不同使用要求。3）視圖可以對機密的數據提供安全保護數據定義語句視圖的定義和維護視圖能夠簡化用戶的操作；數據定義語句1.視圖的優點4）視圖對數據庫的重構提供了一定程度的邏輯獨立性；在關系數據庫中，數據庫的重構往往是不可避免的。但由于視圖機制，將為應用程序提供了原來使用的關系，保持外模式。原表：學生（學號，姓名，性別，年齡，所在系）分成：SX（學號，姓名，年齡）SY（學號，性別，所在系）CREATEVIEW學生(學號，姓名，性別，年齡，所在系）ASSELECTSX.學號，SX.姓名，SX.性別，SY.年齡，SY.所在系FROMSX,SYWHERESX.學號=SY.學號；數據定義語句1.視圖的優點4）視圖對數據庫的重構提供了一定程數據定義語句2.視圖定義的格式一般格式為：CREATEVIEW〈視圖名〉[(列名組)]AS〈子查詢〉

[WITHCHECKOPTION]；（1）WITHCHECKOPTION選項表示在對視圖進行UPDATE、INSERT和DELETE操作時，要保證操作的數據滿足視圖定義中的謂詞條件。該謂詞條件是視圖子查詢中的WHERE子句的條件。數據定義語句2.視圖定義的格式一般格式為：（1）WITHC數據定義語句2.視圖定義的格式一般格式為：CREATEVIEW〈視圖名〉[(列名組)]AS〈子查詢〉

[WITHCHECKOPTION]；（2）組成視圖的屬性列名全部省略或者全部指定若省略了視圖的各個屬性列名，則由子查詢中的SELECT子句的目標列代替必須明確指定列名的情況①某個目標列是集函數或列表達式；②子查詢中使用多個表（或視圖），并且目標列中含有相同的屬性名；③需要在視圖中改用新的、更合適的列名。數據定義語句2.視圖定義的格式一般格式為：（2）組成視圖的屬數據定義語句2.視圖定義的格式【例5-6】建立計算機系學生的視圖。

CREATEVIEW計算機系學生

ASSELECT學號，姓名，年齡

FROM學生

WHERE所在系=‘計算機系’；數據定義語句2.視圖定義的格式【例5-6】建立計算機系學生的數據定義語句2.視圖定義的格式【例5-7】由學生、課程和選課三個表，定義一個計算機系的學生成績視圖，其屬性包括學號、姓名、課程名和成績CREATEVIEW學生成績(學號，姓名，課程名，成績)ASSELECT學生.學號，學生.姓名，課程.課程名，選課.成績

FORM學生，課程，選課

WHERE學生.學號=選課.學號AND程.課程名=選課.課程號

AND學生.所在系=‘計算機系’；數據定義語句2.視圖定義的格式【例5-7】由學生、課程和選課數據定義語句2.視圖定義的格式【例5-8】將學生的學號、總成績、平均成績定義成一個視圖。CREATEVIEW學生成績統計(學號，總成績，平均成績)ASSELECT學號，SUM（成績），AVG（成績）

FORM選課

GROUPBY學號；3.視圖的刪除DROPVIEW<視圖名>數據定義語句2.視圖定義的格式【例5-8】將學生的學號、總成數據定義語句4.視圖的查詢和維護視圖可以和基本表一樣被查詢，其使用方法與基本表相同，但利用視圖進行數據增、刪、改操作，會受到一定的限制。一般的數據庫系統不支持對下列幾種情況的視圖進行數據更新操作。1）由兩個以上基本表導出的視圖2）視圖的字段來自字段表達式函數3）視圖中有分組子句或使用了DISTINCT短語4）視圖定義中有嵌套查詢，且內層查詢中涉及了與外層一樣的導出該視圖的基本表5）在一個不允許更新的視圖上定義的視圖數據定義語句4.視圖的查詢和維護視圖可以和基本表一樣被查詢，數據查詢語句數據查詢的基本語法1.SELECT語句的語法SELECT〈目標列組〉FROM〈數據源〉[WHERE〈元組選擇條件〉][GROUPBY〈分列組〉[HAVING〈組選擇條件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；數據查詢語句數據查詢的基本語法SELECT〈目標列組〉數據查詢語句1.SELECT語句的語法SELECT子句：指明目標列（字段、表達式、函數表達式、常量）。基本表中相同的列名表示為：〈表名〉.〈列名〉FROM子句：指明數據源。表間用“，”分割。數據源不在當前數據庫中，使用“〈數據庫名〉.〈表名〉”表示。一表多用，用別名標識。定義表別名：〈表名〉.〈別名〉WHERE子句：元組選擇條件。數據查詢語句1.SELECT語句的語法SELECT子句：指明數據查詢語句1.SELECT語句的語法學生課程庫結構為：學生(學號，姓名，年齡，所在系)；

課程(課程號，課程名，先行課)；

選課(學號，課程號，成績).【例5-9】求數學系學生的學號和姓名。

SELECT學號，姓名

FROM學生

WHERE所在系=‘數學系’；【例5-10】求選修了課程的學生學號。

SELECTDISTINCT學號

FROM選課；數據查詢語句1.SELECT語句的語法學生課程庫結構為：學生數據查詢語句1.SELECT語句的語法(4)GROUPBY子句：結果集分組。當目標列中有統計函數，則統計為分組統計，否則為對整個結果集統計。子句后帶上HAVING子句表達組選擇條件（帶函數的表達式）。(5)ORDERBY子句：排序。當排序要求為ASC時升序排序；排序要求為DESC時降序排列。【例5-11】求選修C1課程的學生學號和成績，并要求對查詢結果按成績的降序排列，如果成績相同則按學號的升序排列。

SELECT學號，成績

FROM選課

WHERE課程號=‘C1’

ORDERBY成績DESC，學號ASC；數據查詢語句1.SELECT語句的語法(4)GROUPB數據查詢語句2.SELECT語句的操作符(1)算術操作符

+（加號）、－（減號）、*（乘號）和/（除號）。(2)比較操作符

=（等于）、>（大于）、<（小于）、<=（小于等于）、>=（大于等于）、!=（不等于）、<>（小于大于）、!>（不大于）和!<（不小于），共9種操作符。

數據查詢語句2.SELECT語句的操作符(1)算術操作符

數據查詢語句語義操作符使用格式或示例在[不在]其中[NOT]IN〈字段〉IN（〈數據表|子查詢〉）任何一個ANY〈字段〉〈比較符〉ANY（數據表|子查詢）例：〈字段〉>ANY（數據表|子查詢）全部(每個)ALL〈字段〉〈比較符〉ALL（數據表|子查詢）例：〈字段〉>ALL（數據表|子查詢）[不]存在EXISTSEXISTS(〈子查詢〉)在[不在]范圍[NOT]BETWEEN…AND…〈字段〉[NOT]BETWEEN小值AND大值是[不是]空值IS[NOT]NULL〈字段〉IS[NOT]NULL模式比較[NOT]LIKE〈字段〉[NOT]LIKE〈字符常數〉其中，“_”單字符通配符和“%”多字符通配符與運算AND〈條件1〉AND〈條件2〉或運算OR〈條件1〉OR〈條件2〉非運算NOTNOT〈條件〉數據查詢語句語義操作符使用格式或示例在[不在]其中[NOT]數據查詢語句(4)組合查詢操作符SQL的組合查詢操作符是針對傳統關系運算的操作符，包括UNION（并查詢）、MINUS（差查詢）和INTERSECT（交查詢）其格式為：<查詢1><組合操作符><查詢2>1）UNION：并查詢，并在結果集中去掉重復行。2)MINUS：差查詢操作。

3)INTERSECT：交查詢操作。

4)*：取全部字段。格式為：*或〈表名〉.*

5)ALL：全部。保留重復值（有統計函數時要求計算重復值）。

格式為：ALL〈字段〉或

ALL〈字段組〉

6)DISTINCT：去掉重復值。在結果集中去掉重復值，或在統計函數中不計重復值。

格式為：DISTINCT〈字段〉或

DISTINCT〈字段組〉數據查詢語句(4)組合查詢操作符數據查詢語句【例5-12】求選修課程C1且成績在80～90之間的學生學號和成績，并將成績乘以系數0.8輸出。

SELECT學號，成績*0.8

FROM選課

WHERE課程號=‘C1’AND

成績BETWEEN80AND90；【例5-13】求數學系或計算機系姓張的學生的信息。

SELECT*

FROM學生

WHERE所在系IN(‘數學系’，‘計算機系’)AND姓名LIKE‘張%’；1.簡單查詢實例數據查詢語句【例5-12】求選修課程C1且成績在80～90之數據查詢語句【例5-14】求缺少了成績的學生的學號和課程號。

SELECT學號，課程號

FROM選課

WHERE成績ISNULL；數據查詢語句【例5-14】求缺少了成績的學生的學號和課程號。數據查詢語句2.連接查詢實例連接查詢中的連接條件通過WHERE子句表達，連接條件和元組選擇條件之間用AND（與）操作符銜接(1)等值連接和非等值連接

[〈表名1〉.]〈列名1〉〈比較運算符〉[〈表名2〉.]〈列名2〉

比較運算符：=、>、<、>=、<=和!=；列名稱為連接字段。【例5-15】查詢每個學生的情況以及他(她)所選修的課程。

SELECT學生.*，選課.*

FROM學生，選課

WHERE學生.學號=選課.學號；數據查詢語句2.連接查詢實例(1)等值連接和非等值連接

數據查詢語句【例5-16】求學生的學號、姓名、選修的課程名及成績。

SELECT學生.學號，姓名，課程名，成績

FROM學生，課程，選課

WHERE學生.學號=選課.學號

AND課程.課程號=選課.課程號；【例5-17】求選修C1課程且成績為90分以上的學生學號、姓名及成績。

SELECT學生.學號，姓名，成績

FROM學生，選課

WHERE學生.學號=選課.學號

AND課程號=‘C1’AND成績>90；數據查詢語句【例5-16】求學生的學號、姓名、選修的課程名及數據查詢語句(2)自身連接操作

例如，課程表中的先行課是在上學期應開設的，先行課的先行課，即間接先行課應提前一學年開設。如果求查詢某門課的間接先行課或全部課程的間接先行課，就需要對課程表進行自身連接。課程號課程名先行課C1計算機引論—C2PASCAL語言C1C3數據結構C2C4數據庫C3C5軟件工程C4課程的先行關系鏈為：C5→C4→C3→C2→C1，課程的間接關系鏈為：C5→C3→C1。數據查詢語句(2)自身連接操作

例如，課程表中的先行課是在數據查詢語句(2)自身連接操作【例5-18】查詢每一門課的間接先行課。

SELECTA.課程號，A.課程名，B.先行課

FROM課程A，課程B

WHEREA.先行課=B.課程號

課程號課程名先行課

課程號課程名先行課C1計算機引論

C1計算機引論

C2Pascal語言C1C2Pascal語言C1C3數據結構C2C3數據結構C2C4數據庫C3C4數據庫C3C5軟件工程C4C5軟件工程C4數據查詢語句(2)自身連接操作【例5-18】查詢每一門課的數據查詢語句(2)自身連接操作【例5-18】查詢每一門課的間接先行課。

SELECTA.課程號，A.課程名，B.先行課

FROM課程A，課程B

WHEREA.先行課=B.課程號

A.課程號A.課程名B.先行課C2Pascal語言

C3數據結構C1C4數據庫C2C5軟件工程C3數據查詢語句(2)自身連接操作【例5-18】查詢每一門課的數據查詢語句(3)外部連接操作左外部連接操作是在結果集中保留連接表達式左表中的非匹配記錄；右外部連接操作是在結果集中保留連接表達式右表中的非匹配記錄。外部連接符號為“*=”，右外部連接符號為“=*”。外部連接中不匹配的分量用NULL表示。職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

數據查詢語句(3)外部連接操作左外部連接操作是在結數據查詢語句(3)外部連接操作職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產科5661012王敏女2212計劃科5781014張立男2113一車間467內連接的結果集數據查詢語句(3)外部連接操作職工號姓名性別年齡所在部門部數據查詢語句(3)外部連接操作左連接的結果集職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產科5661011劉晨女19

1012王敏女2212計劃科5781014張立男2113一車間467數據查詢語句(3)外部連接操作左連接的結果集職工號姓名性別數據查詢語句(3)外部連接操作右連接的結果集職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產科5661012王敏女2212計劃科5781014張立男2113一車間467科研所數據查詢語句(3)外部連接操作右連接的結果集職工號姓名性別數據查詢語句(3)外部連接操作【例5-19】用SQL表達職工和部門之間的內連接、左外部連接和右外部連接的語句內連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=部門.部門號；左外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門*=部門.部門號；右外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=*部門.部門號；數據查詢語句(3)外部連接操作【例5-19】用SQL表達職數據查詢語句3.嵌套查詢在SQL語言中，一個SELECT…FROM…WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。(1)使用IN操作符的嵌套查詢

【例5-20】求選修了高等數學的學生學號和姓名。

SELECT學號，姓名

FROM學生

WHERE學號IN(SELECT學號

FROM選課

WHERE課程號IN(SELECT課程號

FROM課程

WHERE課程名=‘高等數學’))；數據查詢語句3.嵌套查詢在SQL語言中，一個SELECT…F數據查詢語句3.嵌套查詢在SQL語言中，一個SELECT…FROM…WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。(1)使用IN操作符的嵌套查詢

【例5-20】求選修了高等數學的學生學號和姓名。

該題也可以使用下面的連接查詢表達。

SELECT學生.學號，姓名

FROM學生，課程，選課

WHERE學生.學號=課程.學號AND課程.課程號=選課.課程號

AND課程.課程名=‘高等數學’；數據查詢語句3.嵌套查詢在SQL語言中，一個SELECT…F數據查詢語句3.嵌套查詢(2)使用比較符的嵌套查詢【例5-21】求C1課程的成績高于張三的學生學號和成績。

SELECT學號，成績

FROM選課

WHERE課程號=‘C1’AND成績>(SELEC成績

FROM選課

WHERE課程號=‘C1’AND學號=

(SELECT學號

FROM學生

WHERE姓名='張三'))；數據查詢語句3.嵌套查詢(2)使用比較符的嵌套查詢數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢其格式為：<字段><比較符>[ANY|ALL]<子查詢>操作符語意>ANY大于子查詢結果中的某個值，即表示大于查詢結果中最小值>ALL大于子查詢結果中的所有值，即表示大于查詢結果中最大值<ANY小于子查詢結果中的某個值，即表示小于查詢結果中最大值<ALL小于子查詢結果中的所有值，即表示小于查詢結果中最小值>=ANY大于等于子查詢結果中的某個值，即表示大于等于結果集中最小值>=ALL大于等于子查詢結果中的所有值，即表示大于等于結果集中最大值數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢操作符數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢其格式為：<字段><比較符>[ANY|ALL]<子查詢>操作符語意<=ANY小于等于子查詢結果中的某個值，即表示小于等于結果集中最大值<=ALL小于等于子查詢結果中的所有值，即表示小于等于結果集中最小值=ANY等于子查詢結果中的某個值，即相當于IN=ALL等于子查詢結果中的所有值(通常沒有實際意義)!=(或<>)ANY不等于子查詢結果中的某個值，!=(或<>)ALL不等于子查詢結果中的任何一個值，即相當于NOTIN數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢操作符數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢其格式為：<字段><比較符>[ANY|ALL]<子查詢>【例5-22】求其他系中比計算機系某一學生年齡小的學生。

SELECT*

FROM學生

WHERE年齡<ANY(SELECT年齡

FROM學生

WHERE所在系=‘計算機系’)AND所在系<>‘計算機系’；數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢【例5數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢其格式為：<字段><比較符>[ANY|ALL]<子查詢>【例5-23】求其他系中比計算機系學生年齡都小的學生。

SELECT*

FROM學生

WHERE年齡<ALL(SELECT年齡

FROM學生

WHERE所在系='計算機系')AND所在系<>'計算機系'；數據查詢語句(3)使用ANY或ALL操作符的嵌套查詢【例5數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-24】求選修了C2課程的學生姓名。

SELECT姓名

FROM學生

WHEREEXISTS(SELECT*

FROM選課

WHERE學生.學號=學號AND課程號='C2')；Select姓名From學生，選課Where學生.學號=選課.學號and課程號=‘c2’數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-25】求沒有選修C2課程的學生姓名。

SELECT姓名

FROM學生

WHERENOTEXISTS(SELECT*

FROM選課

WHERE學生.學號=學號AND課程號=‘C2’)；不能寫成以下：Select姓名From學生，選課Where學生.學號=選課.學號and課程號<>‘c2’數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-26】查詢選修了全部課程的學生的姓名。

SELECT姓名

FROM學生

WHERENOTEXISTS(SELECT*

FROM課程

WHERENOTEXISTS

(SELECT*

FROM選課

WHERE學生.學號=學號AND

課程.課程號=課程號))；由于SQL沒有全稱量詞的操作符，該題轉換為查詢這樣的學生：沒有一門課他不選修。數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-27】求至少選修了學號為“S2”的學生所選修的全部課程的學生學號和姓名。

SELECT學號，姓名

FROM學生

WHERENOTEXISTS(SELECT*FROM選課選課1WHERE選課1.學號='S2'ANDNOTEXISTS(SELECT*FROM選課選課2WHERE學生.學號=選課2.學號

AND選課2.課程號=選課1.課程號)；數據查詢語句(4)使用EXISTS操作符的嵌套查詢【例5-數據查詢語句4.組合查詢Union并操作，Intersect交操作，MINUS差操作【例5-28】求選修了C1課程或選修了C2課程的學生學號。

SELECT學號

FROM選課

WHERE課程號=‘C1’

UNION

SELECT學號

FROM選課

WHERE課程號=‘C2’；Select學號From選課Where課程號=‘C1’or課程號=‘C2’數據查詢語句4.組合查詢【例5-28】求選修了C1課程或選修數據查詢語句4.組合查詢【例5-29】求選修C1課程，并且也選修C2課程的學生學號。

SELECT學號

FROM選課

WHERE課程號='C1'

INTERSECT

SELECT學號

FROM選課

WHERE課程號='C2'；數據查詢語句4.組合查詢【例5-29】求選修C1課程，并且也數據查詢語句4.組合查詢【例5-30】求選修了C1課程但沒有選修C2課程的學生學號。SELECT學號

FROM選課

WHERE課程號='C1'

MINUS

SELECT學號

FROM選課

WHERE課程號='C2'；數據查詢語句4.組合查詢【例5-30】求選修了C1課程但沒有數據查詢語句4.組合查詢【例5-30】求選修了C1課程但沒有選修C2課程的學生學號。本例也可以用下面的EXISTS嵌套查詢表示。

SELECT學號

FROM選課選課1

WHERE課程號='C1'ANDNOTEXISTS

(SELECT學號

FROM選課選課2

WHERE選課1.學號=選課2.學號AND選課2.課程號='C2')；數據查詢語句4.組合查詢【例5-30】求選修了C1課程但沒有數據查詢語句5.使用分組和SQL函數查詢函數功能AVG(〈數值表達式〉)求與字段相關的數值表達式的平均值SUM(〈數值表達式〉)求與字段相關的數值表達式的和值MIN(〈字段表達式〉)求字段表達式的最小值MAX(〈字段表達式〉)求字段表達式的最大值COUNT(*|〈字段〉)求記錄行數(*)，或求不是NULL的字段的行數數據查詢語句5.使用分組和SQL函數查詢函數功能AVG(〈數數據查詢語句5.使用分組和SQL函數查詢【例5-31】求學生的總人數。

SELECTCOUNT(*)FROM學生；【例5-32】求選修了課程的學生人數。

SELECTCOUNT(DISTINCT學號)

FROM選課；數據查詢語句5.使用分組和SQL函數查詢【例5-31】求學生數據查詢語句5.使用分組和SQL函數查詢【例5-33】求課程和選修該課程的人數。

SELECT課程號，COUNT(學號)

FROM選課

GROUPBY課程號；【例5-34】求選修課超過3門課的學生學號。

SELECT學號

FROM選課

GROUPBY學號HAVINGCOUNT(*)>3；數據查詢語句5.使用分組和SQL函數查詢【例5-33】求課程數據更新語句SQL的數據插入功能1.使用常量插入單個元組格式為：

INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉…)]

VALUES(〈常量1〉[，〈常量2〉]…)；數據更新語句SQL的數據插入功能格式為：

INSER數據更新語句SQL的數據插入功能1.使用常量插入單個元組【例5-35】將一個新學生記錄(學號：‘98010’，姓名：‘張三’，年齡：20，所在系：‘計算機系’)插入到學生表中。

INSERT

INTO學生

VALUES(‘98010’，‘張三’，20，‘計算機系’)；【例5-36】插入一條選課記錄(學號：‘98011’，課程號：‘C10’，成績不詳)。

INSERT

INTO選課(學號，課程號)

VALUES('98011'，'C10')；數據更新語句SQL的數據插入功能【例5-35】將一個新學生記數據更新語句2.在表中插入子查詢的結果集INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉]…)]

〈子查詢〉；【例5-37】求每個系學生的平均年齡，并把結果存入數據庫中。

CREATETABLE系平均年齡(系名稱CHAR(20)，

平均年齡SMALLINT)；

INSERT

INTO系平均年齡

SELECT所在系，AVG(ALL年齡)

FROM學生

GROUPBY所在系；數據更新語句2.在表中插入子查詢的結果集【例5-37】求每個數據更新語句3.SQL的數據修改功能UPDATE〈表名〉

SET〈列名〉=〈表達式〉[，〈列名〉=〈表達式〉][，…n]

[WHERE〈條件〉]；【例5-38】將學生表中全部學生的年齡加上2歲。

UPDATE學生

SET年齡=年齡+2；

【例5-39】將選課表中的數據庫課程的成績乘以1.2。

UPDATE選課

SET成績=成績*1.2

WHERE課程號=(SELECT課程號

FROM課程

WHERE課程名='數據庫')；數據更新語句3.SQL的數據修改功能【例5-38】將學生表中數據更新語句3.SQL的數據刪除功能DELETE

FROM〈表名〉

[WHERE〈條件〉]；【例5-40】刪除藝術系的學生記錄及選課記錄。

DELETE

FROM選課

WHERE學號IN(SELECT學號

FROM學生

WHERE所在系='藝術系')；

DELETE

FROM學生

WHERE所在系='藝術系'；數據更新語句3.SQL的數據刪除功能【例5-40】刪除藝術系嵌入式SQL嵌入式SQL的特點1.嵌入式SQL應注意的問題

1)SQL和主語言的配合問題。

2)合理選擇主語言。

2.SQL嵌入主語言時必須解決的三個問題

(1)如何區別SQL和主語言

在所有的SQL語句前面加上前綴EXECSQL；SQL語句的結束標志隨主語言的不同而不同嵌入式SQL嵌入式SQL的特點1.嵌入式SQL應注意的問題嵌入式SQL嵌入式SQL的特點2.SQL嵌入主語言時必須解決的三個問題

(2)使數據庫的工作單元與程序工作單元之間能夠通信

1)主語言通過主變量向SQL語句提供參數。

2)SQL語句的當前工作狀態和運行環境數據要返饋給應用程序。

(3)使用游標解決SQL一次一集合的操作與主語言一次一記錄操作的矛盾

游標是系統為用戶開設的一個數據緩沖區，存放SQL語句的執行結果。嵌入式SQL嵌入式SQL的特點2.SQL嵌入主語言時必須解嵌入式SQL不用游標的SQL語句1.幾種不需要使用游標的SQL語句

(1)用于說明主變量的說明性語句

SQL的說明性語句主要有兩條：

EXECSQLBEGINDECLARESECTION；

EXECSQLENDDECLARESECTION；

(2)數據定義和數據控制語句

(3)查詢結果為單記錄的查詢語句

(4)數據的插入語句和某些數據刪除、修改語句

獨立的數據刪除和修改語句不需要使用游標；與查詢語句配合，刪除或修改查詢到的當前記錄的操作，與游標有關。

嵌入式SQL不用游標的SQL語句1.幾種不需要使用游標的S嵌入式SQL2.不用游標的查詢語句

EXECSQLSELECT[ALL|DISTINCT]〈目標列表達式〉[，…n]

INTO〈主變量〉[〈指示變量〉][，…n]

FROM〈表名或視圖名〉[，…n]

[WHERE〈條件表達式〉]；

1)在語句開始前要加EXECSQL前綴。

2)該查詢語句中又擴充了INTO子句。

3)在WHERE子句的條件表達式中可以使用主變量。嵌入式SQL2.不用游標的查詢語句

EXECSQLSE嵌入式SQL2.不用游標的查詢語句

EXECSQLSELECT[ALL|DISTINCT]〈目標列表達式〉[，…n]

INTO〈主變量〉[〈指示變量〉][，…n]

FROM〈表名或視圖名〉[，…n]

[WHERE〈條件表達式〉]；

4)由于查詢的結果集中只有一條記錄，該語句中不必有排序和分組子句。

5)INTO子句中的主變量后面跟有指示變量時如果查詢結果列值為NULL，指示變量為負值，結果列不向該主變量賦值；如果傳遞正常，指示變量的值為0；如果主變量寬度不夠，則指示變量的值為數據截斷前的寬度。

6)如果查詢結果并不是單條記錄，則程序出錯。嵌入式SQL2.不用游標的查詢語句

EXECSQLSE嵌入式SQL2.不用游標的查詢語句

EXECSQLSELECT[ALL|DISTINCT]〈目標列表達式〉[，…n]

INTO〈主變量〉[〈指示變量〉][，…n]

FROM〈表名或視圖名〉[，…n]

[WHERE〈條件表達式〉]；

【例5-41】查詢學號為主變量givesno、課號為主變量givecno的值的學生選課記錄。

EXECSQLSELECT學號，課程號，成績

INTO:Sno，:Cno，:grade:gradeid

FROM選課

WHERE學號=:givesnoAND課程號=:givecno；嵌入式SQL2.不用游標的查詢語句

EXECSQLSE嵌入式SQL2.不用游標的數據維護語句

(1)不用游標的數據刪除語句

【例5-42】刪除學號由主變量Sno決定的學生記錄。

EXECSQLDELETE

FROM學生

WHERE學號=:Sno；嵌入式SQL2.不用游標的數據維護語句

(1)不用游標嵌入式SQL2.不用游標的數據維護語句

(2)不用游標的數據維護語句

【例5-43】將計算機系所有學生的年齡都加上主變量Raise。

EXECSQLUPDATE學生

SET年齡=年齡+:Raise

WHERE所在系=‘計算機系’；【例5-44】將計算機系學生的年齡置空。

Raiseid=-1EXECSQLUPDATE學生

SET年齡=年齡+:Raise:Raiseid；嵌入式SQL2.不用游標的數據維護語句

(2)不用游標嵌入式SQL2.不用游標的數據維護語句

(3)不用游標的數據插入語句

【例5-45】將學號為主變量Sno、課程號為Cno的選課記錄，插到庫中。

EXECSQLINSERTINTO選課

VALUES(:Sno，:Cno)；嵌入式SQL2.不用游標的數據維護語句

(3)不用游標嵌入式SQL使用游標的SQL游標機制用于解決SQL查詢結果為集合而主語言處理方式為記錄方式的矛盾。在處理中，必須使用游標的SQL語句有兩種：一種是查詢結果為多條記錄的SELECT語句，另一種是使用游標的DELETE語句和UPDATE語句。嵌入式SQL使用游標的SQL游標機制用于解決SQL查嵌入式SQL1.游標的4個命令(1)定義游標命令

EXECSQLDECLARE〈游標名〉CURSOR

FOR〈子查詢〉

[FORUPDATEOF〈字段名1〉[，…n]]；

刪除和修改數據的語句中，WHERE為：

WHERECURRENTOF〈游標名〉

【例5-46】定義按主變量DEPT查詢系里學生的游標。

EXECSQLDECLARESXCURSORFOR

SELECT*

FROM學生

WHERE所在系=:DEPT；嵌入式SQL1.游標的4個命令嵌入式SQL(2)打開游標命令

EXECSQLOPEN〈游標名〉；

執行對應的查詢語句，并將游標指向結果集的第一條記錄前。打開的游標處于活動狀態，可以被推進。【例5-47】打開SX游標。

DEPT='計算機系'

EXECSQLOPENSX；嵌入式SQL(2)打開游標命令

EXECSQLOPE嵌入式SQL(3)推進游標命令

EXECSQLFETCH〈游標名〉INTO〈主變量組〉；

將游標下移一行，讀出當前的記錄，將當前記錄的各數據項值放到INTO后的主變量組中。【例5-48】將打開的指向系的游標向前推進。

EXECSQLFETCHSX

INTO:Sno，:Sname，:Sage，:Sdept；(4)關閉游標命令

EXECSQLCLOSE〈游標名〉；嵌入式SQL(3)推進游標命令

EXECSQL數據控制機制和語句數據控制是系統通過對數據庫用戶的使用權限加以限制而保證數據安全的重要措施。授權拒絕訪問收權數據控制機制和語句數據控制是系統通過對數據庫用戶的使用權限加數據控制機制和語句數據控制機制授權定義存權處理查權操作具有授權資格的用戶，如數據庫管理員DBA或建表戶DBO，通過數據控制語言DCL，將授權決定告知數據庫管理系統。數據庫管理系統DBMS把授權的結果編譯后存入數據字典中。當用戶提出操作請求時，系統要在數據字典中查找該用戶的數據操作權限，當用戶擁有該操作權時才能執行其操作，否則系統將拒絕其操作。數據控制機制和語句數據控制機制授權定義存權處理查權操作具有授數據控制機制和語句1.授權語句

系統授權的授權語句格式為：

GRANT〈系統特權組〉To〈用戶組〉|PUBLIC

[WITHGRANTOPTION]；

1)PUBLIC指數據庫的所有用戶。

2)WITHGRANTOPTION:獲得權限的用戶可以把該權限再授予別的用戶。

對象特權的授權格式為：

GRANTALLPRIVILIGES|〈對象特權組〉ON〈對象名〉

TO〈用戶組〉|PUBLIC

[WITHGRANTOPTION]；

1)ALLPRIVILIGES指所有的對象特權。

2)對象名指操作的對象標識，它包括表名、視圖名和過程名等。數據控制機制和語句1.授權語句

系統授權的授權語句格式為：數據控制機制和語句【例5-51】把修改學生學號和查詢學生表的權力授予用戶王平。

GRANTUPDATE(學號)，SELECTON學生TO王平；【例5-52】把建立數據庫和備份數據庫的權力賦給用戶王平。

GRANTCREATEDATABASE，

BACKUPDATABASETO王平；數據控制機制和語句【例5-51】把修改學生學號和查詢學生表的數據控制機制和語句2.收權語句

REVOKE語句的一般格式為：

REVOKE〈權限組〉|ALLPRIVILIGES[ON〈對象名〉]

FROM〈用戶名組〉|PUBLIC；

其中：ON子句用于指定被收回特權的對象；ALLPRIVILIGES指收回所有特權；PUBLIC指所有用戶。

【例5-53】將用戶王平的可以在學生表中修改學生學號的權利收回。

REVOKEUPDATE(學號)ON學生FROM王平；數據控制機制和語句2.收權語句

REVOKE語句的一般格數據控制機制和語句3.拒絕訪問語句

DENYALL[PRIVILIGES]|〈權限組〉[ON〈對象名〉]TO〈用戶組〉|PUBLIC；

其中：ON子句用于說明對象特權的對象名；對象名指的是表名、視圖名、視圖和表的字段名或者過程名。數據控制機制和語句3.拒絕訪問語句

DENYALL[第五章關系數據庫標準語言——SQL第五章關系數據庫標準語言——SQL本章教學目標、重點和難點

本章教學目標：使學生了解SQL的功能和語言特點；熟練掌握用SQL進行表的定義、數據查詢和數據更新，掌握SQL的數據控制方法。2.教學重點：SQL的數據定義、數據查詢、數據維護和數據控制表示方法3.教學難點：用SQL實現嵌套查詢、組合查詢、使用分組和SQL函數查詢。本章教學目標、重點和難點本章教學目標：使學生了解SQL的功本章內容SQL的功能及特點數據定義語句數據查詢語句數據更新語句嵌入式SQL數據控制機制和語句本章內容SQL的功能及特點SQL的功能及特點19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了關系DBMS原型，并實現該語言美國批準SQL作為美國標準，隨后ISO也通過這一標準公布了SQL-92標準公布了SQL-99，也稱作SQL3SQL的功能及特點19741975-19791986-198SQL的功能及特點數據定義數據控制數據操縱SQL語句分類SQL的功能及特點數據定義數據控制數據操縱SQL語句分類SQL的功能及特點SQL語句分類數據定義數據操縱數據控制定義關系數據庫的模式、外模式和內模式，以實現對基本表、視圖以及索引文件的定義、修改和刪除等操作。包括數據查詢和數據更新兩種數據操作語句：數據查詢指對數據庫中的數據查詢、統計、分組、排序操作；數據更新指數據的插入、刪除、修改等數據維護操作。通過對數據庫用戶的授權和收權命令來實現有關數據的存取控制，以保證數據庫的安全性。SQL的功能及特點SQL語句分類數據定義數據操縱數據控制定義SQL的功能及特點SQL的特點1）SQL具有自主式語言和嵌入式語言兩種使用方式；2）SQL具有語言簡潔、易學易用的特點；SQL功能命令動詞數據定義（數據模式定義、刪除、修改）Create、Drop、Alter數據操縱（數據查詢和維護）Select、Insert、Update、Delete數據控制（數據存取控制權和收權）Grant、RemoveSQL的功能及特點SQL的特點SQL功能命令動詞數據定義（數SQL的功能及特點SQL的特點3）SQL支持三級模式結構存儲文件1存儲文件2基本表1基本表2基本表3基本表4視圖1視圖2SQL外模式模式內模式SQL的功能及特點SQL的特點存儲文件1存儲文件2基本表1基數據定義語句操作對象創建語句刪除語句修改語句基本表CreatetableDroptableAltertable索引CreateindexDropindex視圖CreateviewDropview數據庫CreatedatabaseDropdatabaseAlterdatabase1.語句格式約定符號尖括號<>中括號[]大括號{}或分隔符|[,…n]實際語義任選項必選項，必選其中一項前面的項可重復多次數據定義語句操作對象創建語句刪除語句修改語句基本表Creat數據定義語句2.一般語法規定SQL中的數據項（包括列項、表和視圖）分隔符為“,”，其字符串常數的定界符用單引號“’”表示3.SQL特殊語法規定SQL的關鍵詞一般使用大寫字母表示；語句的結束符為“;”語句一般應采用格式化書寫方式數據定義語句2.一般語法規定數據定義語句基本表的定義和維護1.基本表的定義定義基本表語句的一般格式為：CREATETABLE[〈庫名〉]〈表名〉(〈列名〉〈數據類型〉[〈列級完整性約束條件〉][，〈列名〉〈數據類型〉[〈列級完整性約束條件〉]][，…n]

[，〈表級完整性約束條件〉][，…n])；數據定義語句基本表的定義和維護定義基本表語句的一般格式為：數據定義語句建立基本表：學生(學號，姓名，年齡，性別，所在系)；

課程(課程號，課程名，先行課)；選課(學號，課程號，成績).CREATETABLE課程(課程號CHAR(5)