Web数据库应用课件

Web資料庫應用

2.1關係資料庫

關係資料庫系統是支持關係模型的資料庫系統。關係模型由關係數據結構、關係操作集合和關係完整性約束三部分組成。

關係資料庫是表的集合，資料庫中的表，數學家稱為關係。關係資料庫中還定義了關係操作和完整性約束，關係操作就是對表中的數據的處理、查詢，完整性約束就是表中的數據要滿足的條件，這些條件由資料庫系統自動維護，產生違反這些約束條件的對數據的操作會被拒絕執行。

2.1.1關係數據結構

在關係模型中，無論是實體還是實體之間的聯繫均由單一的結構即關係（表）來表示。關係模型是建立在集合代數的基礎上的。

1關係的定義在用戶觀點下，關係模型中數據的邏輯結構是一張二維表。二維表的每一列稱為屬性。每個屬性有一個取值範圍，稱為值域。每個屬性對應一個值域。下麵是二維表的一個實例。

學號姓名性別1張三男2李四男2關係中的基本名詞(1)關鍵字能唯一標識一行記錄或元組的一個或一組屬性。(2)候選關鍵字（Candidatekey）和主關鍵字（Primarykey）能作為關鍵字的屬性可能有很多，這些屬性稱為候選關鍵字。為數據管理方便，若一個關係有多個候選關鍵字時，指定其中一個為主關鍵字。(3)主屬性（PrimaryAttribute）和非主屬性（Non-keyAttribute）候選關鍵字中的屬性為主屬性。不包含在任何候選關鍵字中的屬性為非主屬性。3資料庫中關係的類型關係可以有三種類型：基本關係（通常稱為基本表或基表）查詢表和視圖表。

(1)基本表基本表是關係資料庫中實際存在的表，它是實際存儲數據的邏輯表。

(2)查詢表它是從基本表中查詢得到的，一般是臨時表。

(3)視圖表視圖表是由基本表或其他視圖表導出的表，它是為數據處理方便以及數據安全要求而設計的虛表，它一般是不存儲數據的，而只是存儲提取數據的條件，它的數據依賴於基本表。

4基本關係的性質基本關係具有如下六條性質：(1)列是同性質的，即同一列中的分量是同一類型的數據。(2)關係中的列又稱為屬性，並賦予屬性名。不同列的屬性名不同。不同列可以取相同的數據類型。(3)列的次序可以任意交換。(4)任意兩個元組不能完全相同。(5)行的次序可以任意交換。(6)列具有原子性，即每一列值是不可分的資料項目5關係模式關係模式是對關係的描述，可以理解為表的結構定義。關係實際上就是關係模式在某一時刻的狀態或內容。也就是說，關係模式是型，關係是它的值。

6關係資料庫在關係模型中，實體以及實體間的聯繫都是用關係來表示。在一個給定應用中，所有實體及實體之間聯繫的集合構成一個關係資料庫。關係資料庫也有型和值之分。

2.1.2關係操作

關係模型給出了關係操作的能力，但不對具體的RDBMS給出具體的語法要求。關係操作語言靈活方便，表達能力和功能強大。關係模型的數據操作採用集合操作方式，即操作對象和結果都是集合。關係模型中常用的關係操作包括數據查詢、數據維護和數據控制三大功能。數據查詢指數據檢索、統計、排序等功能；數據維護指數據的增加、刪除、修改等數據的更新功能；數據控制是為了保證數據的安全性和完整性而採用的數據存儲控制及併發控制等功能。

2.1.3關係的完整性

1實體完整性現實世界中的實體是可區分的，即它們具有某種唯一性標識。相應地，關係模型中以主鍵（主關鍵字）作為唯一性標識。主鍵中的屬性即主屬性不能取空值。所謂空值就是“不知道”或“無意義”的值。2參照完整性現實世界中的實體之間往往存在某種聯繫，在關係模型中實體及實體間的聯繫都是用關係來描述的。這樣就自然存在著關係與關係間的引用。

3.用戶定義的完整性

用戶定義的完整性是針對某一具體關係資料庫的約束條件，它反映某一具體應用所涉及的數據必須滿足的語義要求。例如：某個屬性的取值不能為空，學生考試的成績取值範圍在0到100之間。

2.2SQL語言

SQL全稱是“StructuredQueryLanguage”，即結構化查詢語言，它目前是關係資料庫的標準語言，它是在1974年由Boyce和Chamberlin提出的。經過不斷修改、擴充和完善，SQL語言最終發展成為關係資料庫的標準語言。由於它功能豐富、語言簡潔、易學易用，所以深受用戶和電腦工業界的歡迎，被各電腦公司和軟體公司廣泛採用。

SQL標準的影響超出了資料庫領域，SQL成為國際標準後，它在資料庫以外的其他領域也得到了重視和採用。在未來一段相當長的時間，SQL將是資料庫語言領域中的一個主流語言。

2.2.1SQL概述及特點

1.SQL概述

SQL是一種結構化查詢語言，其功能並不象名稱所顯示的那樣僅僅是查詢，而是一個通用的、功能強大的關係資料庫語言。SQL從主要功能上可以分為4個部分：數據定義、數據查詢、數據操縱和數據控制。它是一個綜合的、通用的關係資料庫查詢語言。它的特點如下：

(1)功能一體化

(2)高度非過程化

(3)以同一種語法結構提供兩種使用方式

(4)語言簡潔，易學易用

2.SQL語言的基本概念

SQL支持三級模式結構，外模式對應於視圖和部分基本表，模式對應於基本表，內模式對應於存儲檔。基本表是獨立存在的表，一個關係就是一個表。基本表是按數據全局邏輯模式建立的。全體基本表構成了資料庫的模式。一個SQL模式（Schema）是表和約束的集合。視圖是從基本表或其他視圖中導出的表，它本身不獨立存儲在資料庫中，也就是說資料庫中只存放視圖的定義而不存放視圖的數據，這些數據仍存放在導出視圖的基本表中。因此，視圖是一個虛表。2.2.2SQL數據定義

SQL的數據定義功能包括定義表、定義視圖、定義索引和定義資料庫。SQL的數據定義語句見下表。操作對象創建語句刪除語句修改語句基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEXALTERINDEX視圖CREATEVIEWDROPVIEWALTERVIEW資料庫CREATEDATABASEDROPDATABASEALTERDATABASE

1.基本表的定義和維護

SQL基本表的定義和維護功能包括表的定義、修改和刪除。(1)定義基本表（CREATETABLE）

[例2-2]建立一個“學生”表，它由學號、姓名、性別、年齡、所在系五個屬性組成，年齡是整型數據，其他為字元型。

CREATETABLE學生（學號CHAR（5）,

姓名CHAR（20）,

年齡INT,

性別CHAR（2）,

所在系CHAR（20）,

）

(2)修改基本表（ALTERTABLE）

[例2-6]刪除“學生”表中“入學時間”列。

ALTERTABLE學生

DROPCOLUMN入學時間；

(3)

刪除基本表

[例2-7]刪除學生表

DROPTABLE

學生

2.索引的定義和維護當你閱讀一本厚書時，如果你想快速找到特定的內容，你就需要查看目錄，找到你所關心的內容在書中的準確位置——頁碼，然後直接翻到這個位置。索引對於表，就相當於書的目錄。索引可以提高數據查詢速度。可以對頻繁查詢的表增加索引。只有按有索引的列查詢，索引才有作用。

(1)建立索引

（CREATEINDEX）

[例2-8]為學生表建立索引，按學號昇冪建唯一索引。

CREATEUNIQUEINDEXStusnoON學生

（學號）(2)刪除索引（DROPINDEX）

[例2-9]DROPINDEX學生.Stusno

2.2.3SQL數據查詢

資料庫查詢是資料庫的核心操作，查詢就是從資料庫中挑選、提取所需要的數據。SQL提供了SELECT語句進行資料庫的查詢，該語句使用方式靈活、功能強大。

SELECT語句的語法格式為：SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]...FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<行選擇條件>][GROUPBY<列名1>[HAVING<組選擇條件>]][ORDERBY<列名2>[ASC|DESC]]；

1.簡單查詢簡單查詢是指從一個表中進行查詢，也叫單表查詢。如選擇一個表中的某些列、某些行。(1)查詢指定列[例2-10]查詢全體學生的姓名與學號。學生表中有很多列：學號，姓名，年齡，性別，所在系，而下列語句只選擇學號和姓名兩列，即查詢的結果是一個只包含兩列的新關係。

SELECT學號，姓名

FROM學生；(2)查詢全部列[例2-11]查詢學生表的全部列：

SELECT*FROM學生也可以寫為：

SELECT學號，姓名，年齡，性別，所在系(3)查詢經過計算的值

SELECT子句可以包含運算式，運算對象可以是常量或屬性。[例2-12]查詢5年之後學生的年齡。假如資料庫中學生的年齡是今年的年齡。

SELECT學號，姓名，年齡+5FROM學生(4)消除取值重複的行一個表中兩個本來不完全相同的行，選擇部分列後，可能完全相同了。[例2-13]查詢選修了課程的學號。

SELECT學號

FROM選課如果要去掉重複行，必須指定短語DISTINCT：

SELECTDISTINCT學號

FROM選課取消了重複行，結果就是選了課的學生學號。(5)查詢滿足條件的行

WHERE子句通過條件運算式描述表中行的選擇條件。DBMS系統根據選擇條件掃描表中的每一行，把不符合條件的行篩選掉。WHERE子句常用的查詢條件，如下表所示：查詢條件常用謂詞比較=，>，<，>=，<=，<>，!>，!<；NOT+上述比較運算符確定範圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字元匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR①比較[例2-14]查詢資訊系學生的名單

SELECT姓名

FROM學生

WHERE所在系='資訊系'[例2-15]查詢所有20歲以下的學生姓名和年齡

SELECT姓名，年齡

FROM學生

WHERE年齡<20[例2-16]查詢有成績不及格的學生的學號

SELECTDISTINCT學號

FROM選課

WHERE成績<60②

確定範圍[例2-17]查詢年齡在20到23歲（包括20和23）之間的學生姓名、年齡、所在系。

SELECT姓名，年齡，所在系

FROM學生

WHERE年齡BETWEEN20AND23③

確定集合[例2-18]查詢數學系或化學系學生的姓名、性別、所在系。

SELECT姓名，性別，所在系

FROM學生

WHERE所在系

IN（'數學系'，'化學系'）④字串的匹配。[例2-19]查詢姓劉的學生的姓名、性別、所在系。

SELECT姓名，性別，所在系

FROM學生

WHERE姓名LIKE'劉%'⑤

涉及空值的查詢[例2-22]查詢選修了課程而沒參加考試的學生名單及課程號。

SELECT學號，課程號

FROM選課

WHERE成績ISNULL⑥

多重條件查詢如果查詢條件多於兩個，則要使用邏輯運算符AND和OR連接多個條件，AND的優先順序高於OR，但可使用括弧改變運算順序。[例2-23]查詢電腦系姓趙的學生。

SELECT姓名

FROM學生

WHERE所在系='電腦系'AND姓名

LIKE'趙%'(6)對查詢結果排序如果沒有指定查詢結果的顯示順序，DBMS通常按其數據錄入的先後順序（物理存儲順序）輸出查詢結果。用戶也可以用ORDERBY子句指定按照一個或多個屬性列的昇冪（ASC）或降序（DESC）重新排列查詢結果，其中昇冪ASC為缺省值。[例2-24]查詢選修了3號課程的學生的學號及其成績，查詢結果按成績的降序排列。

SELECT學號，成績

FROM選課

WHERE課程號＝'3'ORDERBY成績DESC(7)使用集合函數為了進一步方便用戶，增強檢索功能，SQL提供了許多集函數，常用的集函數見下表。函數名意義COUNT（[DISTINCT|ALL]*）統計記錄個數COUNT（[DISTINCT|ALL]列名）統計一列中值的個數SUM（[DISTINCT|ALL]列名）計算一列值的總和（此列必須是數值型）AVG（[DISTINCT|ALL]列名）計算一列值的平均值（此列必須是數值型）MAX（[DISTINCT|ALL]列名）求一列值中的最大值MIN（[DISTINCT|ALL]列名）求一列值中的最大值[例2-25]查詢學生總人數。

SELECTCOUNT（*）

FROM學生[例2-26]查詢選修了課程的學生人數。

SELECTCOUNT（DISTINCT學號）

FROM選課學生每選修一門課，在選課中都有一條相應的記錄，而一個學生一般都要選修多門課，為避免重複計算學生人數，必須在COUNT函數中用DISTINCT限定詞。(8)對查詢結果分組

GROUPBY子句可以將查詢結果表的各行按一列或多列的值分組，值相等的為一組。對查詢結果分組的目的是為了細化集函數的作用對象。如果未對查詢結果分組，集函數將作用於整個查詢結果，即整個查詢結果只有一個函數值，如上面的例題，否則，集函數將作用於每一個組，即每一組都有一個函數值。[例2-28]查詢各個課程號與相應的選課人數。

SELECT課程號，COUNT（學號）

FROM選課

GROUPBY課程號該SELECT語句對課程表按課程號的取值進行分組，所有具有相同課程號的行為一組，然後對每一組作用集函數COUNT以求得該組的學生人數。2.連接查詢一個資料庫中的多個表之間一般都存在某種內在聯繫，它們共同提供有用的資訊。前面的查詢都是針對一個表進行的。若一個查詢同時涉及兩個以上的表，則稱之為連接查詢。連接查詢實際上是關係資料庫中最主要的查詢，主要包括等值連接查詢、非等值連接查詢、自身連接查詢、外連接查詢和複合條件連接查詢。

[例2-31]查詢每個學生及其選修課程的情況。學生選課情況存放在選課表中，所以本查詢實際上同時涉及學生表和選課表兩個表中的數據。這兩個表之間的聯繫是通過兩個表都具有的公共屬性學號實現的。要查詢學生及其選修課程的情況．就必須將這兩個表中學號相同的元組連接起來。這是一個等值連接。完成本查詢的SQL語句為：

SELECT學生.*，選課.*FROM學生，選課

WHERE學生.學號＝選課.學號3.嵌套查詢在SQL語言中，一個SELECT—FROM—WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。在嵌套查詢中，上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層查詢塊又稱為內層查詢或子查詢。SQL語言允許多層嵌套查詢。即一個子查詢中還可以嵌套其他子查詢。

嵌套查詢使得可以用一系列簡單查詢構成複雜的查詢，從而明顯地增強了SQL的查詢能力。(1)帶有IN謂詞的子查詢帶有IN渭詞的子查詢是指父查詢與子查詢之間用IN進行連接，判斷某個屬性列值是否在子查詢的結果中。由於在嵌套查詢中，子查詢的結果往往是一個集合，所以謂詞IN是嵌套查詢中最經常使用的謂詞。[例2-32]查詢選修了“數學”課的學生姓名。學生名和課程名分別在“學生”和“課程”表中，因此不能通過簡單的查詢一個表而得到結果。實際上連接學生和課程的是“選課”表，因此這個查詢涉及到三個表：學生、課程、選課。查詢步驟如下：

在課程表中查詢“數學”課的課程號。

在選課表中找到選修了上一步得到的課程號的課程的學生學號。

在學生表中找到學號包含在上一步所得到的學號集合中的學生姓名。

SELECT學號，姓名

FROM學生

WHERE學號IN（SELECT學號

FROM 選課

WHERE課程號IN（SELECT課程號

FROM課程

WHERE課程='數學'））

(2)帶有比較運算符的子查詢帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符進行連接。當用戶能確切知道內層查詢返回的是單值時，可以用＞、＜、＝、＞＝、＜＝、2＝或＜＞等比較運算符。[例2-33]查詢與“劉剛”同一個系的學生。這個查詢分兩步：

首先在學生表找到“劉剛”所在的系。

然後在學生表中查詢所在系與上一不的查詢結果相等的學生名單。SELECT姓名FROM學生WHERE所在系=（SELECT所在系

FROM學生

WHERE姓名='劉剛'）

2.2.4SQL數據操縱

在對數據庫進行操作時，除了經常查詢資料庫外，還要更新數據。SQL中數據更新包括插入數據（INSERT）、修改數據（UPDATE）和刪除數據（DELETE）三條語句。插入語句（INSERTINTO

）[例2-34]將一個新學生記錄（學號：95005；姓名：陳俊；性別：男；所在系：電腦；年齡：18歲）插入學生表中。

INSERTINTO學生

VALUES（"95020"，"陳俊"，"男"，18，"電腦系"）；

2.刪除語句（DELETE）[例2-35]刪除學號為95001的學生記錄。

DELETEFROM學生

WHERE學號='95001'；3.修改語句（UPDATE

）[例2-36]將學號為95001的學生年齡改為22歲。

UPDATE學生

SET年齡=22WHERE學號='95001'2.2.5SQL數據控制

這裏所討論的數據控制主要是指SQL語言的安全性控制功能，通過對數據庫用戶的使用權限加以限制而保證數據安全的重要措施。SQL語言的的數據控制語句包括授權（Grant）、收權或刪除許可權（Revoke）、拒絕訪問（Deny）三種，許可權的設置對象可以是資料庫用戶或用戶組。授權

[例2-37]將查詢成績的許可權授予所有人。

GRANTSELECTONTABLE成績TOPUBLIC2.收權語句資料庫管理員DBA、資料庫擁有者DBO或資料庫對象的擁有者DBOO可以通過REVOKE語句將其他用戶的數據操作許可權收回。

[例2-38]將用戶wp修改學生學號的許可權收回。

REVOKEUPDATE（學號）

ON學生

FROMwp

2.3關係數據理論

針對一個具體的問題，應該如何構造一個適合於它的數據模式。這是資料庫設計問題，確切地講是資料庫邏輯設計問題。關係模型有嚴格的數學理論基礎，基於這種理論上的優勢，關係模型可以設計的更加科學，關係資料庫中的問題可以更好地解決。關係資料庫規範化理論就是資料庫邏輯設計工具。關係資料庫的規範化理論就是解決如何設計出合理的關係資料庫模式，也就是一個資料庫中要定義哪些表，每個表有哪些屬性，表之間有哪些聯繫，而儘量地減少數據冗餘，消除插入、刪除等存在的問題。2.3.1關係模式可能存在的問題

例如，要設計一個教學管理資料庫，希望在該資料庫中保存學生的學號、姓名、性別、年齡、系名、系主任和該學生選修的課程及成績資訊。若據此設計一個關係保存所有的數據，關係模式為：教學（學號，姓名，性別，年齡，系名，系主任，課程名，成績）這個關係模式存在以下的問題：

數據冗餘大

插入異常

刪除異常。

更新異常

由於上述的問題，它是一個不合理的設計。一個合理的設計應該儘量減少以上的問題。只有找到問題存在的原因，才能找到解決辦法。那麼出現上述問題的原因是什麼呢？這是因為這個關係模式中的數據之間的依賴關係所造成的。如果把這個單一的關係模式，分解成如下三個關係模式：學生（學號，姓名，性別，年齡，所在系）；系（系名，系主任）；選課（學號，課程名，成績）

2.3.2函數依賴及規範化

為了使資料庫模式的設計更合理，人們研究了規範化理論。而函數依賴理論是關係規範化的理論基礎。函數依賴反映了同一關係中屬性間一一對應的約束。

1.關係模式的簡化表示法一般把關系模式看做一個三元組：

R（U，F）其中，R是關係名，U是一組屬性，F是屬性組U上的一組數據依賴關係的集合。當且僅當U上的一個關係r滿足F時，r稱為關係模式R（U，F）的一個關係。

2.函數依賴

函數依賴是數據依賴的一種，函數依賴反映了同一關係中屬性間的一一對應約束。它是現實世界屬性間相互聯繫的抽象，是數據內在的性質，是語義的體現。它是資料庫規範化理論的基礎。函數依賴普遍存在於現實生活中。例如在描述學生的關係中，可能的屬性有學號，姓名，所在系等。由於一個學號只對應於一個學生，一個學生只能屬於一個系，因此學號確定之後，該學生的姓名和所在系也唯一的確定了。類似於引數確定了，函數值也唯一確定了。起決定性的屬性相當於引數。函數依賴記為：學號→姓名，學號→所在系。

現在討論一個具體的關係模式。例如，要設計一個教學管理資料庫，希望該資料庫中包括的資訊有學生學號、姓名、系名、系主任姓名，以及學生學習的課程和成績。於是得到一組屬性：

U={學號，姓名，系名，系主任，課程名，成績}

根據經驗得知：

一個學號對應一個學生，即一個學號對應一個姓名，但可以有多個學生重名；

一個系有若干學生，但一個學生只屬於一個系；

一個系只有一名（正職）負責人；

一個學生可選修多門課程，每門課程有若干學生選修；

學生學習每一門課程有一個成績；於是得到屬性組上的一組函數依賴：

F={學號→姓名，學號→系名，系名→系主任，（學號，課程名）→成績}

定義：設R（U）是屬性集U上的關係模式。X，Y是U的子集。若對於R（U）的任意一個可能的關係r（一個滿足約束條件的數據組成的表），r中不可能存在兩個元組在X上的屬性值相等，而在Y上的屬性值不等，則稱X函數確定Y或Y函數依賴於X，記作X→Y。

定義：在R（U）中,，如果X→Y，Y→Z，且（Y

X），YX，則稱Z對X傳遞函數依賴。例如，在教學關係模式中，學號→系名，系名→系主任，所以系主任對學號傳遞依賴。

3.範式

關係資料庫中的關係是要滿足一定要求的。滿足不同程度要求的為不同的範式。範式是滿足一定函數依賴的關係模式的集合。有多種範式：第一範式、第二範式、第三範式、BCN範式等。滿足最低要求的叫第一範式，簡稱1NF。在滿足第一範式的條件下，滿足進一步條件的為第二範式，簡稱2NF。其餘依此類推。顯然各種範式之間存在關係：

1NF

2NF

3NF

BCNF

4NF

5NF

一個低一級的範式模式，通過模式分解可以轉換為若干個高一級範式的模式集合，這個過程叫規範化。

(1)第一範式（1NF）定義：如果一個關係模式R的所有屬性都是簡單屬性，即每個屬性都是不可再分的基本資料項目，則稱R屬於第一範式，記作R∈1NF。例如，教學模式中的所有屬性都是不可再分的簡單屬性，即：教學（學號，姓名，系名，系主任，課程名，成績）∈

1NF(2)第二範式（2NF）定義：若一個關係模式R∈lNF，且每一個非主屬性完全函數依賴於關鍵字，則R∈2NF。

將教學模式分為以下兩個模式：學生_系（學號，姓名，系，系主任）；選課（學號，課程名，成績）在“學生_系”關係中，“學號”是關鍵字，其他非主屬性都完全依賴於關鍵字，“選課”關係中，（學號，課程名）是關鍵字，非主屬性也完全依賴於關鍵字，因此兩個關係中非主屬都完全函數依賴於關鍵字，服從2NF，即： 學生_系∈2NF，選課∈2NF。

(3)第三範式（3NF）定義：若關係模式R是第二範式，且沒有一個非主屬性傳遞函數依賴關鍵字，則R∈3NF。R∈3NF，則非主屬性對主屬性既不部分函數依賴於關鍵字，