第3章關系數據庫語言SQL課件

第3章關系數據庫語言SQL

第3章關系數據庫語言SQL

本章重要概念

（1）SQL數據庫的體系結構，SQL的組成。（2）SQL的數據定義：SQL模式、基本表和索引的創(chuàng)建和撤銷。（3）SQL的數據查詢；SELECT語句的句法，SELECT語句的三種形式及各種限定，基本表的聯接操作，SQL3中的遞歸查詢。（4）SQL的數據更新：插入、刪除和修改語句。（5）視圖的創(chuàng)建和撤銷，對視圖更新操作的限制。本章重要概念（1）SQL數據庫的體系結構，SQL的組成本章概述SQL是關系數據庫的標準語言，對關系模型的發(fā)展和商用DBMS的研制起著重要的作用。SQL語言是介乎于關系代數和元組演算之間的一種語言。本章詳細介紹SQL的核心部分內容：數據定義、數據查詢、數據更新和嵌入式SQL。

本章概述SQL是關系數據庫的標準語言，對關系模型的發(fā)展和商用關系數據庫語言SQL3.1SQL的體系結構

3.2SQL的數據定義

3.3SQL的數據查詢3.4數據更新3.5視圖的定義和對視圖的操作3.6嵌入式SQL

關系數據庫語言SQL3.1SQL的體系結構3.1SQL的體系結構

3.1.1SQL的產生與發(fā)展

3.1.2SQL數據庫的體系結構

3.1.3SQL的組成

返回3.1SQL的體系結構3.1.1SQL的產生與發(fā)展SQL的產生與發(fā)展

1970年，美國IBM研究中心的E.F.Codd連續(xù)發(fā)表多篇論文，提出關系模型。1972年，IBM公司開始研制實驗型關系數據庫管理系統(tǒng)SYSTEMR，配制的查詢語言稱為SQUARE(SpecifyingQueriesAsRelationalExpression)語言，在語言中使用了較多的數學符號。1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL(StructuredEnglishQUEryLanguage)語言。后來SEQUEL簡稱為SQL(StructuredQueryLanguage)，即“結構式查詢語言”，SQL的發(fā)音仍為“sequel”。現在SQL已經成為一個標準。SQL的產生與發(fā)展1970年，美國IBM研究中心的E.F.SQL數據庫的體系結構

用戶1用戶2用戶3用戶4視圖1視圖1基本表1基本表2基本表3基本表4存儲文件1存儲文件2存儲文件3存儲文件4圖3.1SQL數據庫的體系結構SQL用戶ViewBasetableStoredfileSQL數據庫的體系結構用戶1用戶2用戶3用戶4視圖1視圖1SQL的組成

核心SQL主要有四個部分: (1)數據定義語言，即SQLDDL，用于定義SQL模式、基本表、視圖、索引等結構。

(2)數據操縱語言，即SQLDML。數據操縱分成數據查詢和數據更新兩類。其中數據更新又分成插入、刪除和修改三種操作。

(3)嵌入式SQL語言的使用規(guī)定。這一部分內容涉及到SQL語句嵌入在宿主語言程序中的規(guī)則。

(4)數據控制語言，即SQLDCL，這一部分包括對基本表和視圖的授權、完整性規(guī)則的描述、事務控制等內容。返回SQL的組成核心SQL主要有四個部分:返回3.2SQL的數據定義

3.2.1SQL模式的創(chuàng)建和撤銷

3.2.2基本數據類型

3.2.3基本表的創(chuàng)建、修改和撤銷

3.2.4索引的創(chuàng)建和撤銷

返回3.2SQL的數據定義3.2.1SQL模式的創(chuàng)建和撤SQL模式的創(chuàng)建和撤銷

SQL模式的創(chuàng)建可用CREATESCHEMA語句定義，其基本句法如下：

CREATESCHEMA〈模式名〉AUTHORIZATION〈用戶名〉DROP語句的句法如下：

DROPSCHEMA〈模式名〉［CASCADE│RESTRICT］其方式有兩種：

CASCADE(級聯式)方式。

RESTRICT(約束式)方式。

SQL模式的創(chuàng)建和撤銷SQL模式的創(chuàng)建可用CREATESQLServer2008的schemaSchma也稱為架構，是形成單個命名空間的數據庫實體的集合。命名空間是一個集合，其中每個元素的名稱都是唯一的。以往SQLServer內的對象命名是“服務器.數據庫.用戶名.對象”，但新版的對象命名改為“服務器.數據庫.Schema.對象”。完全限定的對象名稱現在包含四部分：server.database.schema.objectSQLServer2005以后，架構獨立于創(chuàng)建它們的數據庫用戶而存在?？梢栽诓桓募軜嬅Q的情況下轉讓架構的所有權SQLServer2008的schemaSchma也稱為基本數據類型

SQL提供的主要數據類型(也稱為“域類型”)有：（1）數值型（2）字符串型（3）位串型（4）時間型返回基本數據類型SQL提供的主要數據類型(也稱為“域類型”)有AnIntroductiontoDatabaseSystemSQLServer支持的數據類型數據類型含義CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串INT整數型。精度為10，長度為4字節(jié)。SMALLINT短整數。精度為5，長度為2字節(jié)。NUMERIC(p[，s])精確整數型，由p位數字（不包括符號、小數點）組成，小數后面有s位數字,長度為5-17。REAL，FLOAT(n)浮點型。使用科學計數法表示數據。如：5.6432E20DATETIME,SMALLDATETIME日期型，包含年、月、日，長度分別為8字節(jié)，4字節(jié)，分別精確到毫秒和分鐘。bit位型。存儲0和1，長度為1字節(jié)。Binary,varbinary二進制型。存儲二進制位數據流。存儲字節(jié)個數0～8000text文本型，存儲大量的字符數據。請參見SQLServer實用教程表2.3（P38），或參見聯機幫助。AnIntroductiontoDatabaseSy基本表的創(chuàng)建、修改和撤銷

(1)

基本表的創(chuàng)建

例3.1

基本表SS（S#，SNAME，AGE，SEX）可用下列語句創(chuàng)建：

CREATETABLES (S# CHAR(4)NOTNULL，

SNAMECHAR(8)NOTNULL，

AGE CHAR(1)，

SEX CHAR(1), PRIMARYKEY(S#))；

基本表的創(chuàng)建、修改和撤銷(1)基本表的創(chuàng)建基本表的創(chuàng)建、修改和撤銷(2)基本表結構的修改增加新的列用“ALTER…ADD…”語句，其句法如下：

ALTERTABLE<基本表名>ADD<列名><類型>刪除原有的列用“ALTER…DROP…”語句，句法如下：

ALTERTABLE<基本表名>DROP<列名>［CASCADE│RESTRICT］此處CASCADE方式表示：在基本表中刪除某列時，所有引用到該列的視圖和約束也要一起自動地被刪除。而RESTRICT方式表示在沒有視圖或約束引用該屬性時，才能在基本表中刪除該列，否則拒絕刪除操作。

基本表的創(chuàng)建、修改和撤銷(2)基本表結構的修改基本表的創(chuàng)建、修改和撤銷(3)例3.2在基本表S中增加一個地址（ADDRESS）列，可用下列語句：

ALTERTABLESADDADDRESSVARCHAR（30）； 應注意，新增加的列不能定義為“NOTNULL”?；颈碓谠黾右涣泻螅性M在新增加的列上的值都被定義為空值（NULL）。例3.3在基本表S中刪除年齡（AGE）列，并且把引用該列的所有視圖和約束也一起刪除，可用下列語句：

ALTERTABLESDROPAGECASCADE；例3.4在基本表S中S#的長度修改為6，可用下列語句：

ALTERTABLESMODIFYS#CHAR（6）；

基本表的創(chuàng)建、修改和撤銷(3)例3.2在基本表S中增加一基本表的創(chuàng)建、修改和撤銷(4)基本表的撤銷撤銷語句的句法如下：

DROPTABLE<基本表名>［CASCADE│RESTRICT］此處的CASCADE、RESTRICT的語義同前面句法中的語義一樣。

返回基本表的創(chuàng)建、修改和撤銷(4)基本表的撤銷返回索引的創(chuàng)建和撤銷

索引的創(chuàng)建 創(chuàng)建索引可用“CREATEINDEX”語句實現。句法如下：

CREATE［UNIQUE］INDEX<索引名>ON<基本表名>（列名表）索引的撤銷 當索引不需要時，可以用“DROPINDEX”語句撤銷，其句法如下：

DROPINDEX<索引名>返回索引的創(chuàng)建和撤銷索引的創(chuàng)建返回3.3SQL的數據查詢

3.3.1

SELECT查詢語句

3.3.2

SELECT語句完整的句法

3.3.3

SELECT語句中的限定

3.3.4

基本表的聯接操作

3.3.5

SQL3中的遞歸查詢

返回3.3SQL的數據查詢3.3.1SELECT查詢語句SELECT查詢語句

(1)SELECT—FROM—WHERE句型 在關系代數中最常用的式子是下列表達式：

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

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

SELECTA1，…，An FROMR1，…，Rm WHEREF

這個句型是從關系代數表達式演變來的，但WHERE子句中的條件表達式F要比關系代數中公式更靈活。SELECT查詢語句(1)SELECT—FROM—WHESELECT語句完整的句法

(1)SELECT語句完整的句法如下：

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

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

[WHERE行條件表達式][GROUPBY列名序列

[HAVING組條件表達式]][ORDERBY列名[ASC|DESC]，…]SELECT語句完整的句法(1)SELECT語句完整的句法SELECT語句完整的句法(2)整個語句的執(zhí)行過程如下：（1）讀取FROM子句中基本表、視圖的數據，執(zhí)行笛卡爾積操作。（2）選取滿足WHERE子句中給出的條件表達式的元組。（3）按GROUP子句中指定列的值分組，同時提取滿足HAVING子句中組條件表達式的那些組。（4）按SELECT子句中給出的列名或列表達式求值輸出。（5）ORDER子句對輸出的目標表進行排序，按附加說明ASC升序排列，或按DESC降序排列。SELECT語句完整的句法(2)整個語句的執(zhí)行過程如下：SELECT查詢語句

(2)SELECT句型使用實例 例3.8教學數據中有三個基本表（關系）：

S（S#，SNAME，AGE，SEX）

SC（S#，C#，GRADE）

C（C#，CNAME，TEACHER） 下面用SELECT查詢語句表達每個查詢。 ①檢索學習課程號為C2的學生學號與成績。 ②檢索學習課程號為C2的學生學號與姓名。 ③檢索選修課程名為MATHS的學生學號與姓名。④檢索選修課程號為C2或C4的學生學號。⑤檢索不學Ｃ２課程的學生學號。（６）檢索至少選修課程號為C2和C4的學生學號。（７）檢索學習了全部課程的學生姓名。SELECT查詢語句(2)SELECT句型使用實例SELECT查詢語句

(4)

聚合函數

COUNT（*）計算元組的個數COUNT（列名）對一列中的值計算個數SUM（列名）求某一列值的總和（此列的值必須是數值型）AVG（列名）求某一列值的平均值（此列的值必須是數值型）MAX（列名）求某一列值的最大值MIN（列名）求某一列值的最小值返回SELECT查詢語句(4)

聚合函數返回SELECT語句完整的句法

(3)例3.11對教學數據庫的基本表S、SC、C中數據進行查詢和計算。①統(tǒng)計每一年齡選修課程的學生人數

SELECTAGE，COUNT（DISTINCTS.S#）

FROMS，SCWHERES.S#=SC.S#GROUPBYAGE；由于要統(tǒng)計每一個年齡的學生人數，因此要把滿足WHERE子句中條件的查詢結果按年齡分組，在每一組中的學生年齡相同。此時的SELECT子句應對每一組分開進行操作，在每一組中，年齡只有一個值，統(tǒng)計的人數是這一組中的學生人數。SELECT語句完整的句法(3)例3.11對教學數據庫SELECT語句完整的句法

(4)②求基本表S中男同學的每一年齡組（超過50人）有多少人？要求查詢結果按人數升序排列，人數相同按年齡降序排列。

SELECTAGE，COUNT（S#）

FROMS WHERESEX='M' GROUPBYAGE HAVINGCOUNT（*）>50 ORDERBY2，AGEDESC；返回SELECT語句完整的句法(4)②求基本表S中男同學的每一SELECT語句中的限定

(1)

SELECT子句中的規(guī)定

SELECT子句描述查詢輸出的表格結構，即輸出值的列名或表達式。其形式如下：

SELECT[ALL|DISTINCT]<列名或列表達式序列>|*

條件表達式中的算術比較操作條件表達式中可出現算術比較運算符（＜，＜＝，＞，＞＝，＝，!＝），也可以用“BETWEEN…AND…”比較運算符限定一個值的范圍。列和基本表的改名操作SELECT語句中的限定(1)SELECT子句中的規(guī)定SELECT語句中的限定

(2)字符串的匹配操作 條件表達式中字符串匹配操作符是“LIKE”。在表達式中可使用兩個通配符： 百分號（%）：與零個或多個字符組成的字符串匹配。 下劃線（_）：與單個字符匹配。集合的并、交、差操作當兩個子查詢結果的結構完全一致時，可以讓這兩個子查詢執(zhí)行并、交、差操作。并、交、差的運算符為UNION、INTERSECT和EXCEPT?？罩档谋容^操作

SQL中允許列值為空，空值用保留字NULL表示。

SELECT語句中的限定(2)字符串的匹配操作SELECT語句中的限定

(3)集合的比較操作

SQL提供SELECT語句的嵌套子查詢機制。子查詢是嵌套在另一個查詢中的SELECT語句。（1）集合成員資格的比較（2）集合成員的算術比較（3）空關系的測試（4）重復元組的測試導出表的使用

SQL2允許在FROM子句中使用子查詢。如果在FROM子句中使用了子查詢，那么要給子查詢的結果起個表名和相應的列名。

返回SELECT語句中的限定(3)集合的比較操作返回基本表的聯接操作

基本表的聯接操作基本表的聯接操作

(例)基本表的聯接操作(例)3.4數據更新

3.4.1數據插入

3.4.2

數據刪除

3.4.3數據修改

返回3.4數據更新3.4.1數據插入返回數據插入

（1）單元組的插入

INSERTINTO基本表名[（列名表）]VALUES（元組值）（2）多元組的插入

INSERTINTO基本表名[（列名表）] VALUES（元組值），（元組值），……，（元組值）（3）查詢結果的插入

INSERTINTO基本表名[(列名表)]＜SELECT查詢語句＞這個語句可把一個SELECT語句的查詢結果插到某個基本表中。（4）表的插入

INSERTINTO基本表名1[（列名表）]TABLE基本表名2

這個語句可把基本表2的值插入到基本表1中。數據插入（1）單元組的插入數據刪除

SQL的刪除操作是指從基本表中刪除元組，其句法如下：

DELETEFROM基本表名

[WHERE條件表達式]

其語義是從基本表中刪除滿足條件表達式的元組。刪除語句實際上是“SELECT*FROM基本表名[WHERE條件表達式]”和DELETE操作的結合，每找到一個元組，就把它刪去。應該注意，DELETE語句只能從一個基本表中刪除元組。WHERE子句中條件可以嵌套，也可以是來自幾個基本表的復合條件。數據刪除SQL的刪除操作是指從基本表中刪除元組，其句法如下數據修改

當需要修改基本表中元組的某些列值時，可以用UPDATE語句實現，其句法如下：

UPDATE基本表名

SET列名=值表達式[，列名=值表達式…]│ROW=（元組）

[WHERE條件表達式]

其語義是：修改基本表中滿足條件表達式的那些元組中的列值，需修改的列值在SET子句中指出。返回數據修改當需要修改基本表中元組的某些列值時，可以用UPDA3.5視圖的定義和對視圖的操作

3.5.1視圖的創(chuàng)建和撤銷

3.5.2

對視圖的更新操作

3.5.3視圖的優(yōu)點

返回3.5視圖的定義和對視圖的操作3.5.1視圖的創(chuàng)建和視圖的創(chuàng)建和撤銷

(1)視圖的創(chuàng)建創(chuàng)建視圖可用“CREATEVIEW”語句實現。其句法如下：

CREATEVIEW<視圖名>（列表名）

AS<SELECT查詢語句>

例3.27對于教學數據庫中基本表S、SC、C，用戶經常要用到S#、SNAME、CNAME和GRADE等列的數據，那么可用下列語句建立視圖：

CREATEVIEWSTUDENT_GRADE(S#，SNAME，CNAME，GRADE) ASSELECTS.S#，SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；視圖的創(chuàng)建和撤銷(1)視圖的創(chuàng)建視圖的創(chuàng)建和撤銷

(2)視圖的撤銷在視圖不需要時，可以用“DROPVIEW”語句把其從系統(tǒng)中撤銷。其句法如下：

DROPVIEW視圖名例3.28撤銷STUDENT_GRADE視圖，可用下列語句實現：

DROPVIEWSTUDENT_GRADE；

視圖的創(chuàng)建和撤銷(2)視圖的撤銷對視圖的更新操作

(1)定義3.1如果視圖是從單個基本表只使用選擇、投影操作導出的，并且包含了基本表的主鍵，那么這樣的視圖稱為“行列子集視圖”，并且可以被執(zhí)行更新操作。對視圖的更新操作(1)定義3.1如果視圖是從單個基本表對視圖的更新操作(2)例3.29下面討論對視圖更新的幾個例子。①設有一個視圖定義

CREATEVIEWSUDENT_GRADE ASSELECTS.S#,SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；這個視圖定義了學生選修的課程名和成績，是由三個基本表聯接而成的。 如果用戶要在視圖中執(zhí)行插入操作: INSERTINTOSTUDENT_GRADE VALUES('S24','WANG','MATHS',80)；若在基本表C中，課程名為MATHS的課程號有多個，則往基本表SC插入元組時，課程號C#就不定了。因此，對這個視圖的更新是不允許的。

對視圖的更新操作(2)例3.29下面討論對視圖更新的幾個例對視圖的更新操作(3)②設有一個視圖定義，包含每個學生選修課程（成績?yōu)榉强眨┑拈T數和平均成績：

CREATEVIEWS_GRADE（S#，C_NUM，AVG_GRADE）

ASSELECTS#，COUNT（C#），AVG（GRADE）

FROMSC WHEREGRADEISNOTNULL

視圖S_GRADE雖然是從單個基本表導出，但導出時使用了分組和聚集操作，因此也是不能更新的。譬如，在未更改基本表SC中值時，要在視圖S_GRADE中更改學生的平均成績，顯然是不切實際的。對視圖的更新操作(3)②設有一個視圖定義，包含每個學生選修對視圖的更新操作(4) ③如果定義了一個有關男學生的視圖：

CREATEVIEWS_MALE ASSELECTS#,SNAME,AGE FROMS WHERESEX='M'；這個視圖是從單個關系只使用選擇和投影導出的，并且包含鍵S#，因此是可更新的。例如，執(zhí)行插入操作：

INSERTINTOS_MALEVALUES（'S28','WU',18）；系統(tǒng)自動會把它轉變成下列語句：

INSERTINTOS VALUES('S28','WU',18,'M')；

返回對視圖的更新操作(4) ③如果定義了一個有關男學生的視圖：視圖的優(yōu)點

視圖是用戶一級的數據觀點，由于有了視圖，使數據庫系統(tǒng)具有下列優(yōu)點：（1）視圖提供了邏輯數據獨立性。

（2）簡化了用戶觀點。數據庫的全部結構是復雜的,并有多種聯系。

（3）數據的安全保護功能。

返回視圖的優(yōu)點視圖是用戶一級的數據觀點，由于有了視圖，使數據數據庫應用編程存儲過程ADO.net數據庫應用編程存儲過程小結

SQL是關系數據庫的標準語言，已廣泛應用在商用系統(tǒng)中。SQL的數據定義部分包括對SQL模式、基本表、視圖、索引的創(chuàng)建和撤銷。SQL的數據操縱分成數據查詢和數據更新兩部分。SQL的數據查詢是用SELECT語句實現，兼有關系代數和元組演算的特點。SQL的數據更新包括插入、刪除和修改等三種操作，在視圖中只有行列子集視圖是可以更新的。嵌入式SQL涉及到SQL語句的宿主語言程序中的使用規(guī)定，以解決兩種語言的不一致和相互聯系的問題。同時還介紹了動態(tài)SQL語句。

小結SQL是關系數據庫的標準語言，已廣泛應用在商用系統(tǒng)

本章的重點篇幅

（1）教材中P97的例3.8（SELECT語句）。（2）教材中P124的例3.31和的例3.32（嵌入式SQL）。

本章的重點篇幅（1）教材中P97的例3.8（SELEC重要內容分析（一）SELECT語句是SQL的核心內容，對于該語句考生應掌握下列內容。1．SELECT語句的來歷在關系代數中最常用的式子是下列表達式：

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

這里R1、…、Rm為關系，F是公式，A1、…、A