關系數(shù)據(jù)庫標準語言SQL.ppt

1、第五章 關系數(shù)據(jù)庫標準語言SQL,5.1 SQL概述,SQL是1974年由Boyce Chamberlin提出。 1979年IBM公司首次在其關系數(shù)據(jù)庫系統(tǒng)System R上實現(xiàn)了SQL。 1986年10月由美國國家標準局（簡稱ANSI）的數(shù)據(jù)委員會X3H2批準了SQL作為關系數(shù)據(jù)庫語言的美國國家標準，同年公布了SQL標準文本（簡稱SQL86）。1987年國際標準化組織（簡稱ISO）也通過了這一標準。此后ANSI不斷修改和完善SQL標準，并于1989年公布了SQL89標準，1992年又公布了SQL92標準。SQL92標準將其內(nèi)容分為三個級別，即基本級、標準級和完全級。盡管如此，SQL-92標準

2、也不夠完備，且正在進行修改。,5.1.1 SQL的特點,1、一體化語言。 2、高度非過程化。 3、語言簡潔，易學易用。 4、統(tǒng)一的語法結(jié)構(gòu)對待不同的工作方式。,5.1.2 SQL的主要功能,SQL是一種介于關系代數(shù)與關系演算之間的結(jié)構(gòu)化查詢語言，其功能并不僅僅是查詢，還包括數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制。SQL語言是一個通用的、功能極強的關系數(shù)據(jù)庫語言。 1、數(shù)據(jù)定義功能 2、數(shù)據(jù)操縱功能 3、數(shù)據(jù)控制功能 VFP在SQL方面支持數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操縱功能，但在具體實現(xiàn)方面也存在一些差異。另外，由于VFP自身在安全控制方面的缺陷，所以它沒有提供數(shù)據(jù)控制功能。,5.2 SQL的數(shù)據(jù)定義功能,

3、標準SQL的數(shù)據(jù)定義功能非常廣泛，一般包括數(shù)據(jù)庫的定義、表的定義、視圖的定義、存儲過程的定義、規(guī)則的定義和索引的定義等若干部分。 5.2.1 表的定義及維護 1、表結(jié)構(gòu)的定義 語句格式：CREATE TABLE ( (,列級完整性的約束條件,n ，,n );,說 明： (1)SQL支持的數(shù)據(jù)類型 不同的數(shù)據(jù)庫系統(tǒng)支持的數(shù)據(jù)類型不完全相同。使用最多的是字符型數(shù)據(jù)和數(shù)值數(shù)據(jù)。 (2)省略為自由表，否則為數(shù)據(jù)庫中表。 (3)為新建表的表名。 (4)數(shù)據(jù)類型(字段寬度,小數(shù)位數(shù))指定了字段名、字段類型、字段寬度及小數(shù)位數(shù)。字段類型可以用一個字符表示。,(5)列級完整性約束條件 NOT NULL或NUL

4、L約束：NULL允許該字段值為空；NOT NULL不允許字段值為空。缺省值為NOT NULL。 UNIQUE約束：是唯一性約束。 DEFAULT約束：是缺省值約束。 格式為：DEFAULT FOR CHECK約束：是檢查約束。 格式為:CONSTRANT CHECK ERROR約束：輸入的值不符合CHECK子句的合法值時，顯示的錯誤信息。 格式為：ERROR字符型文本信息,(6)表級完整性約束條件 PRIMARY KEY約束：是實體完整性約束。用于定義主碼，它能保證主碼的唯一性和非空性。 PRIMARY KEY約束格式為：CONSTRAINT PRIMARY KEY（） FOREIGN KEY

5、約束：是外碼和參照表約束，它用于定義參照完整性。 FOREIGN KEY約束格式為：CONSTRANT FOREIGN KEY() REFERENCES()(),例如：用SQL命令建立下列三個基本表，基表結(jié)構(gòu)為： 學生（學號，姓名，年齡，性別，所在系）； 課程（課程號，課程名，先行課）； 選課（學號，課程號，成績）； CREATE TABLE 學生(學號 CHAR(5) NOT NULL UNIQUE, 姓名 CHAR(8) NOT NULL, 性別 SMALLINT, 所在系 CHAR(20),DEFAULT C1 20 FOR 年齡, CONSTRAINT C2 CHECK(性別 IN(男

6、, 女);,CREATE TABLE 課程(課程號 CHAR(5) PRIMARY KEY, 課程名 CHAR(20), 先行課 CHAR(5); CREATE TABLE 選課(學號 CHAR(5), 課程號 CHAR(5), 成績 SMALLINT, CONSTRAINT C3 CHECK(成績 BETEWWN 0 AND 100), CONSTRAINT C4 PRIMARY KEY(學號，課程號)， CONSTRAINT C5 FOREIGN KEY(學號) REFERENCES 學生(學號) CONSTRAINT C6 FOREIGN KEY(課程號) REFERENCES 課程(課

7、程號);,例題說明： （1）在學生表中定義的列組約束條件是：學號不能為空且不能出現(xiàn)重復值；姓名不能為空；年齡的缺省值為20；性別必須為“男”或“女”。 （2）在課程表中定義了課程號為主碼的表級約束條件。 （3）在選課表中定義了學號和課程號為主碼的約束；定義了成績值在0到100之間的約束；定義了學號為外碼，其參照表為學生表，該外碼對應學生表中的學號的約束；還定義了課程號為外碼，其參照表為課程表，該外碼對應課程表中的課程號的約束。,2、表結(jié)構(gòu)的修改,語句格式：ALTER TABLE ADD 完整性約束,n DROP MODIFY ,n; 語句功能：該語句可以添加（ADD）新字段、刪除（DROP）字

8、段或修改（MODIFY）原字段的定義。,播放演示,例如：向課程表中增加“學時”字段。 ALTER TABLE 課程 ADD 學時 SMALLINT; 例如：將選課表中課程號的寬度改為6。 ALTER TABLE 選課 MODIFY 課程號 CHAR(6); 例如：刪除學生表中對年齡的默認值的定義。 ALTER TABLE 學生 DROP C1;,3、刪除基本表,語句格式：DROP TABLE ; 例如：刪除學生表。 DROP TABLE 學生; 如果表是數(shù)據(jù)庫中的表并且相應的數(shù)據(jù)庫是當前數(shù)據(jù)庫，則從數(shù)據(jù)庫中刪除了表；否則雖然從磁盤上刪除了表文件，但是記錄在數(shù)據(jù)庫文件中的信息卻沒有刪除，此后會出

9、現(xiàn)錯誤提示。所以要刪除數(shù)據(jù)庫中的表時，最好應該使數(shù)據(jù)庫是當前數(shù)據(jù)庫。 基本表一旦被刪除，表中的數(shù)據(jù)及在此表基礎上建立的索引、視圖將自動地全部被刪除。,5.2.2 索引的定義及維護,1、建立索引 語句格式： CREATE UNIQUECLUSTER INDEX ON (,是要建索引的基本表的名稱。 （4）是要按其建立索引的列名。 （5）指定了索引值的排列順序，有ASC(升序)和DESC(降序)兩個值，缺省為ASC。,例如：為學生表按學號升序建立索引STUSNO。CREATE UNIQUE INDEX Stusno ON 學生(學號); 例如：為課程表按課程號升序建立唯一索引COUCNO。 CRE

10、ATE UNIQUE INDEX Coucno ON 課程(課程號); 例如：為選課表按學號升序和課程號降序建立唯一索引SCNO。 CREATE UNIQUE INDEX SCno ON 選課(學號 ASC，課程號 DESC);,5.2.3 視圖的定義及維護,1、視圖的定義 語句格式：CREATE VIEW視圖名,)AS查詢語句; 說 明： （1）組成視圖的屬性列名全部省略或者全部指定。若省略了視圖的各個屬性列名，則該視圖的屬性為查詢語句中指定的列名。 （2）查詢語句可以是任意的SELECT查詢語句，它說明并限制了視圖中的數(shù)據(jù)。 （3）必須明確指定組成視圖的各列名的情況。,例如：建立計算機系學

11、生的視圖。 CREATE VIEW COMPUTER AS SELECT 學號,姓名，年齡 FROM 學生 WHERE 所在系=“計算機”； （上例為從單個表派生出的視圖，視圖還可以由多個表導出。）,例如：由學生、課程和選課三個表，定義包括學號、姓名、課程名和成績屬性的數(shù)學系學生成績視圖。 CREATE VIEW 成績(學號,姓名,課程名,成績) AS SELECT 學生.學號,學生.姓名，課程.課程名,選課.成績 FROM 學生,課程,選課 WHERE 學生.學號=選課.學號 AND 課程.課程名=選課.課程號 AND 學生.所在系=“數(shù)學”；,建立視圖時，用一個查詢來建立視圖的SELECT

12、子句可以包含算術表達式或集函數(shù)，這些表達式或集函數(shù)與視圖的其他字段一樣對待，由于它們是計算得來的，并不存儲在表內(nèi)，所以稱為虛字段。 例如：定義一個視圖，它包含學號、總成績、平均成績?nèi)齻€屬性。 CREATE VIEW 成績統(tǒng)計(學號,總成績,平均成績) AS SELECT 學號,SUM(成績)，AVG(成績) FROM 選課；,2、視圖的刪除,語句格式：DROP VIEW視圖名 說 明：上述命令與VFP中刪除視圖的命令DELETE VIEW 視圖名等價。 視圖刪除后，視圖的定義將從數(shù)據(jù)字典中刪除，而由該視圖導出的其他視圖的定義卻仍存在數(shù)據(jù)字典中，但這些視圖已失效。為了防止用戶在使用時出錯，要用視

13、圖刪除語句把那些失效的視圖一一刪除。同樣，在某個基本表被刪除后，由該基本表導出的所有視圖定義雖然沒有被刪除，但均已無法使用了。,3、關于視圖的說明,在VFP中視圖是可更新的，但是這種更新是否反映在基本表中則取決于視圖更新屬性的設置。在VFP中視圖有它特殊的概念和用途，在關系數(shù)據(jù)庫中，視圖始終不真正含有數(shù)據(jù)，它總是原來表的一個窗口。所以，雖然視圖可以像表一樣進行各種查詢，但是插入、更新和刪除操作在視圖上卻有一定的限制。 在一般情況下，當一個視圖是由單個表導出時可以進行插入和更新操作，但不能進行刪除操作;當視圖是從多個表導出時，插入、更新和刪除操作都不允許進行。這種限制是很必要的，它可以避免一些潛

14、在問題的發(fā)生。,5.3 SQL的數(shù)據(jù)更新功能,數(shù)據(jù)更新是指對數(shù)據(jù)的操作功能，主要包括數(shù)據(jù)的插入、更新、刪除三個方面的操作。 5.3.1 數(shù)據(jù)插入 SQL的數(shù)據(jù)插入語句有兩種使用形式：一種是使用常量，一次插入一條記錄；另一種是插入子查詢的結(jié)果，一次插入多條記錄。 1、使用常量插入一條記錄 2、在表中插入子查詢的結(jié)果,1、使用常量插入一條記錄,語句格式： INSERT INTO (,) VALUES(常量1), 常量2，) 語句功能：將新記錄插入指定表中。 說 明： （1）新記錄的值為的值。 （2）如果INTO子句中有屬性列選項，則沒有出現(xiàn)在子句中的屬性將取空值。 （3）如果INTO子句中沒有指明

15、任何列名，則新插入的記錄必須在每個列上均有值。,例如：將一新生記錄(學號:20050110,姓名: 張亮,年齡: 20，所在系: 數(shù)學)插入到學生表中。 INSERT INTO 學生 VALUES(20050110, 張亮, 20, 數(shù)學)； 例如：在選課表中插入一條記錄(學號: 20030211,課程號: C10,成績不詳)。 INSERT INTO 選課 VALUES(20030211, C10)；,2、在表中插入子查詢的結(jié)果,語句格式：INSERT INTO (,)； 例如：求每個系學生的平均年齡，并把結(jié)果存入數(shù)據(jù)表中。 CREATE TABLE 系平均年齡(系名 CHAR(20),平均

16、年齡 SMALLINT)； INSERT INTO 系平均年齡 SELECT 所在系,AVG(ALL 年齡) FROM 學生 GROUP BY 所在系；,5.3.2 數(shù)據(jù)更新,語句格式：UPDATE表名 SET=,= WHERE ； 語句功能：將表中符合條件的記錄的某些列用表達式的值替代。 說 明： (1)指定要更新數(shù)據(jù)的記錄所在的表名及該表所在的數(shù)據(jù)庫名。 (2)SET=指定被更新的字段及該字段的新值。 (3)WHERE條件表達式指明將要更新符合條件表達式的記錄。并且一次可以更新多個字段;如果不使用WHERE子句，則更新全部記錄。,例如：將學生表中全部學生的年齡加上2歲。 UPDATE 學生

17、 SET 年齡=年齡+2 例如：將選課表中的VB課程的成績乘以1.4。 UPDATE 選課 SET 成績成績*1.4 WHERE 課程號=(SELECT 課程號 FROM 課程 WHERE 課程名= VB)；,5.3.3 數(shù)據(jù)刪除,語句格式：DELETE FROM表名 WHERE 條件表達式1； 說 明： (1)指定要刪除數(shù)據(jù)的表名。 (2)WHERE選項指明只對滿足條件的記錄刪除。如果不使用WHERE子句，則刪除該表中的全部記錄。 例如：刪除數(shù)學系學生的選課記錄。 DELETE FROM 選課 WHERE 學號IN (SELECT 學號 FROM 學生 WHERE 所在系=數(shù)學)；,播放演示

18、,5.4 SQL的數(shù)據(jù)查詢功能,5.4.1 SELECT語句介紹 1、SELECT語句格式 SELECTALL|DISTINCT FORM WHERE GROUP BY ,HAVING ORDER BYASC|DESC,ASC |DESC 語句功能：從指定的表中選擇滿足條件的記錄。,播放演示,SELECT-SQL命令的格式包括三個基本子句：SELECT子句、FROM子句、WHERE子句，還包括操作子句：ORDER子句、GROUP子句以及其他一些選項。 說 明： （1）SELECT子句用來指定查詢結(jié)果中的目標列，目標列組用一個*號來表示時，指所有的字段。 （2）ALL選項：表示選出的記錄中包括重

19、復記錄，這是缺省值；DISTINCT則表示選出的記錄中不包括重復記錄。 （3）FROM子句用于指明查詢的數(shù)據(jù)源。 定義表別名的格式為： 。,（4）WHERE子句通過條件表達式描述表中記錄的篩選條件，將不滿足條件的元組篩選掉。 （5）GROUP BY子句對記錄按分組列的值分組，分組可以使同組的元組集中在一起，常用于數(shù)據(jù)分組統(tǒng)計。 （6）GROUP BY子句后可帶上HAVING子句作為組篩選條件，組選擇條件為帶有函數(shù)的條件表達式，它決定著整個組記錄的取舍條件。 （7）ORDER BY子句指定查詢結(jié)查中記錄按排序列排序，默認升序。選項ASC表示升序，DESE表示降序。,2、SELECT 子句的操作符

20、,SELECT語句中使用的操作符包括算術操作符、比較操作符、邏輯操作符、組合查詢操作符。 （1）算術操作符 算術操作符在SQL語句中表達數(shù)學運算操作。 SQL的算術操作符有：+、-、*、/ 四種。 （2）比較操作符 比較操作符用于測試兩個數(shù)據(jù)的關系。 SQL的比較操作符有：=、=、!（不大于）、!（不小于）九種。 （3）邏輯操作符 P94表5-3列出了SQL的主要邏輯操作符的語義。,（4）組合查詢操作符 組合查詢操作符的使用格式為： UNION:并查詢操作符。操作結(jié)果將和的結(jié)果合并，即取和的元組，并在結(jié)果中去掉重復行。 MINUS:差查詢操作符。操作結(jié)果為取在中且中沒有的記錄。 INTERSE

21、CT:交查詢操作符。操作結(jié)果為取和中共有的記錄。 SELECT查詢命令的使用非常靈活，用它可以構(gòu)造各種各樣的查詢。,5.4.2 簡單查詢,簡單查詢是指在查詢過程中只涉及一個表的查詢語句，可有簡單的查詢條件。是最基本的查詢語句。 例如：求計算機系學生的學號和姓名。 SELECT 學號，姓名 FROM 學生 WHERE 所在系= 計算機； 例如：求選修了課程的學生學號，結(jié)果去掉重復值。 SELECT DISTINCT 學號 FROM 選課；,例如：求選修了課程C1的學生學號和成績，結(jié)果按成績降序排列，如果成績相同則按學號升序排列。 SELECT 學號,成績 FROM 選課 WHERE 課程號= C

22、1 ORDER BY 成績 DESC，學號 ASC； 例如：求物理系或數(shù)學系姓王的學生的信息。 SELECT * FROM 學生 WHERE 所在系 IN (物理, 數(shù)學) AND 姓名 LINK 王%；,例如：求缺少成績的學生的學號和課程號。 SELECT 學號,課程號 FROM 選課 WHERE 成績 IS NULL； 例如：求選修課程C1且成績在80-90之間的學生學號和成績，并將成績乘以系數(shù)0.8輸出。 SELECT 學號,成績*0.8 FROM 選課 WHERE 課程號=C1 AND 成績 BETWEEN 80 AND 90;,5.4.3 連接查詢,若查詢涉及到兩個以上的表，則稱之為

23、連接查詢。連接查詢是關系數(shù)據(jù)庫中最主要的查詢功能。 1、等值連接和非等值連接 連接查詢中，用來連接兩個表的條件稱為連接條件或連接謂詞。連接條件的一般格式為： . 連接謂詞中的列名稱為連接字段；連接條件中，連接字段類型必須是可比的，但連接字段不一定是同名的。,當連接運算符為“=”時，該連接操作稱為等值連接；否則，使用其他運算符的連接運算稱非等值連接。當?shù)戎颠B接中的連接字段相同，并且在SELECT子句中去除了重復字段時，則該連接操作為自然連接。 例如：查詢每個學生的情況以及他所選修的課程。 SELECT 學生.*，選課.* FROM 學生, 選課 WHERE 學生.學號=選課.學號；,例如：查詢選

24、修C1課程且成績?yōu)?0分以上的學生學號、姓名及成績。 SELECT 學生.學號,姓名,成績 FROM 學生，選課 WHERE 學生.學號=選課.學號 AND 課程號= C1 AND 成績90； 例如:查詢學生的學號、姓名、選修的課程名及成績。 SELECT 學生.學號,姓名,課程名,成績 FROM 學生,課程,選課 WHERE 學生.學號=選課.學號 AND 課程.課程號=選課.課程號；,2、自身連接,連接操作不只是在兩個表之間進行，一個表內(nèi)還可以進行自身連接操作。表自身的連接操作稱為自身連接。 例如：查詢每一門課的間接先行課（即先行課的先行課）。 SELECT A.課程號，A.課程名,B.先

25、行課 FROM 課程 A,課程 B WHERE A.先行課=B.課程號；,說 明： （1）同一查詢語句中，當一個表有兩個作用時，需要對表起別名，應用中使用表的別名。上例中的A和B分別是課程的別名，A和B分別作為獨立表使用。 （2）該題的連接條件是A表中的先行課與B表中的課程號等值連接，其結(jié)果集中“B.先行課”為“A.課程號”的間接先行課字段。由于A和B都是課程的別名，所以該例是自身連接操作。,5.4.4 嵌套查詢,將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。 1、使用IN操作符的嵌套查詢 當IN操作符后的數(shù)據(jù)集需要通過查詢得到時，就需要使用IN嵌

26、套查詢。 例如：查詢選修了高等數(shù)學的學生學號和姓名。 SELECT 學號，姓名 FROM 學生 WHERE 學號 IN (SELECT 學號 FROM 課程 WHERE 課程號= 高等數(shù)學)；,2、使用比較符的嵌套查詢,例如：查詢選修了課程C1的成績高于張亮的學生學號和成績。 SELECT 學號，成績 FROM 選課 WHERE 課程號C1 AND 成績 (SELECT 成績 FROM 選課 WHERE 課程號= C1 AND 學號 (SELECT 學號 FROM 學生 WHERE 姓名張亮)；,3、使用ANY或ALL操作符的嵌套查詢,使用ANY或ALL操作符時必須與比較符配合使用，其格式為：

27、 ANY|ALL 例如：查詢其他系中比數(shù)學系某一學生年齡小的學生（即查詢年齡小于數(shù)學系最大者的學生）。 SELECT * FROM 學生 WHERE 年齡 數(shù)學；,例如：查詢其他系中比數(shù)學系學生年齡都小的學生。 SELECT * FROM 學生 WHERE 年齡 數(shù)學；,4、使用EXISTS操作符的嵌套查詢,EXISTS代表存在量詞。EXISTS操作符后子查詢的結(jié)果集中如果不為空，則產(chǎn)生邏輯真值“TRUE”，否則產(chǎn)生假值“FALSE”。 例如：查詢選修了C2課程的學生姓名。 SELECT 姓名 FROM 學生 WHERE EXISTS(SELECT * FROM 選課 WHERE 學生.學號=

28、學號 AND 課程號=C2)；,例如：查詢沒有選修C2課程的學生姓名。 SELECT 姓名 FROM 學生 WHERE NOT EXISTS(SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號= C2)；,例如：查詢選修了全部課程的學生的姓名。 SELECT 姓名 FROM 學生 WHERE NOT EXISTS(SELECT * FROM 課程 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學生.學號=學號 AND 課程號=課程號)；,例如：查詢至少選修了學號為“S2”的學生所選修的全部課程的學生學號和姓名。 SELECT 學號，姓名