gaozhi第4章關(guān)系型數(shù)據(jù).ppt

1、第4章 關(guān)系型數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,內(nèi)容提要,4.1 SQL概述 4.2 查詢功能 4.3 操作功能 4.4 定義功能 4.5 數(shù)據(jù)控制 4.6 小型案例實訓(xùn) 4.7 本章小結(jié) 4.8 習(xí)題,4.1 SQL概述,SQL具有以下特點: SQL是一種一體化的語言，它包含數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制等方面的功能，可以完成數(shù)據(jù)庫活動中的全部工作。 SQL是一種高度非過程化的語言，它沒有必要一步步地告訴計算機“如何”去做，只需清楚用戶要“做什么”，SQL就可以將要求交給系統(tǒng)，自動完成全部 工作。 SQL非常簡潔,靈活易用。 SQL可以直接以同一語法結(jié)構(gòu)使用不同語言，以命令方式交互使用，也可以嵌

2、入到程序設(shè)計語言中以程序方式 使用。 面向集合的操作方式。,表4-1 SQL命令動詞,4.2 查 詢 功 能,4.2.1 創(chuàng)建SELECT命令 可以在以下3個區(qū)域中創(chuàng)建SELECT命令。 在命令窗口中。 在VFP程序中。 在查詢設(shè)計器中。,界面,連接到SQLServer對話框,查詢分析器工作界面,SQL語言的組成,SQL語言集數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制功能于一體。 數(shù)據(jù)定義是定義SQL模式、基本表、視圖、索引等結(jié)構(gòu)； 數(shù)據(jù)操縱是數(shù)據(jù)插入、刪除和修改等3種操作； 數(shù)據(jù)查詢包括單表查詢、多表查詢、嵌套查詢和連接查詢等； 數(shù)據(jù)控制是對基本表和視圖的安全表授權(quán)、完整性規(guī)則的設(shè)定、事務(wù)并發(fā)控

3、制等內(nèi)容。,數(shù)據(jù)定義語言,SQL對應(yīng)關(guān)系數(shù)據(jù)庫模式的三級模式結(jié)構(gòu)，模式、外模式和內(nèi)模式的基本對象有基本表、視圖和索引。 SQL數(shù)據(jù)定義功能包括定義基本表、定義視圖和定義索引。 定義基本表（CREATE TABLE語句） CREATE TABLE （ 列級完整性約束條件 ， 列級完整性約束條件 ， ）,SQL支持的數(shù)據(jù)類型,定義表的各個屬性，需要指明其數(shù)據(jù)類型及長度。 SQL支持以下數(shù)據(jù)類型： SMALLINT ：半字長二進制整數(shù) INTEGER：全字長二進制整數(shù) DECIMAL：壓縮十進制數(shù) FLOAT：雙字長浮點數(shù) CHAR（n）：長度為n的定長字符串 VARCHAR（n）：最大長度為n的變

4、長字符串,例子： 定義STUDENT（學(xué)生）基本表,CREATE TABLE STUDENT （SNO CHAR（7）NOT NULL， SNAME VARCHAR（8） NOT NULL ， SEX CHAR（2）NOT NULL， PRIMARY KEY（SNO）,修改基本表（ALTER TABLE語句）,修改基本表（ALTER TABLE語句） ALTER TABLE ADD DROP MODIFY ； 其中ALTER TABLE語句指定修改哪個基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件，MODIFY子句用于修改原有的列定義，包括列名和數(shù)

5、據(jù)類型。,刪除基本表（DROP TABLE語句）,DROP TABLE,索引的建立和刪除,建立索引節(jié)省查詢操作的時間，回憶查詢速度的有效手段，使用CREATE INDEX語句。 CREATE UNIQUECLUSTER INDEX ON （ , ）； 其中， 是要建立索引的基本表的名稱，索引可以建立在該表的一列或多列上，各列名之間用逗號分隔。列名后面可用指定索引值的排列次序，缺省值為ASC（升序）； UNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄； CLUSTER表示要建立的索引是聚簇索引。,索引的建立和刪除,例：按照學(xué)號升序建立唯一索引（次序升序，省略） CREATE UNIQUE

6、 INDEX STUSNO ON STUDENT （SNO）； 刪除索引，數(shù)據(jù)增加刪改頻繁，系統(tǒng)會花費許多時間來維護索引，可以刪除一些不必要的索引，使用DROP INDEX語句。 格式 DROP INDEX 例： DROP INDEX Stusname； 刪除索引，系統(tǒng)同時從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。,視圖-外模式,視圖是從一個或幾個基本表導(dǎo)出的表，是虛表（不實際存儲在數(shù)據(jù)庫中） 格式： CREATE VIEW, AS 子查詢 WITH CHECK OPTION,4.2.2 簡單查詢,簡單查詢是針對單個表的查詢，由SELECT和FROM短語構(gòu)成無條件查詢或由SELECT、FROM和WHE

7、RE短語構(gòu)成條件查詢。其基本格式是： SELECT select_Item FROM DatabaseName 如果要去掉查詢結(jié)果中的重復(fù)值，只需要指定DISTINCT短語： SELECT DISTINCT select_Item FROM DatabaseName 此外還可以用WHERE短語指定查詢條件，查詢條件可以是任意復(fù)雜的邏輯表達式： SELECT select_Item FROM DatabaseName WHERE FilterCondition 例4.1 從zg關(guān)系中檢索所有的工資值。 SELECT 工資 FROM zg 結(jié)果顯示如圖4-2所示。,圖4-2 檢索所有的工資值,例4

8、.2 檢索工資多于1220元的職工號。 SELECT 職工號 FROM zg WHERE 工資1220 結(jié)果顯示如圖4-3所示。,4.2.3 簡單的連接查詢,簡單的連接查詢是一類基于多個表的查詢，與嵌套查詢的區(qū)別是要查詢的結(jié)果可以出自多個表，而嵌套查詢的結(jié)果是基于一個表。 一個數(shù)據(jù)庫中的多個表之間一般都存在某種內(nèi)在聯(lián)系，它們共同提供有用的信息。前面的查詢都是針對一個表進行的。如果一個查詢同時涉及兩個以上的表，則稱為連接查詢。其基本格式是： SELECT select_Item 1,select_Item 2 FROM DatabaseName 1,DatabaseName 2; WHERE J

9、oinCondition 當(dāng)FROM之后的多個關(guān)系中含有相同的屬性名時，必須用關(guān)系前綴指明屬性所屬的關(guān)系，“.”前面的是關(guān)系名，后面是屬性名。 例4.4 找出工作在面積大于340的倉庫的職工號和職工所在的城市。 SELECT 職工號,城市FROM ck,zg; WHERE(面積340)AND (ck.倉庫號=zg.倉庫號) 結(jié)果顯示如圖4-5所示。,4.2.4 嵌套查詢,嵌套查詢所要求的結(jié)果出自一個關(guān)系，但相關(guān)的條件卻涉及多個關(guān)系。例如，當(dāng)檢索關(guān)系X中的元組時，它的條件依賴于相關(guān)的關(guān)系Y中的元組的屬性值，這時使用SQL的嵌套查詢功能將非常方便。其格式為： SELECT select_Item

10、1 FROM DatabaseName 1 WHERE FilterCondition 1 IN; SELECT select_Item 2 FROM DatabaseName 2 WHERE FilterCondition 2) 例4.5 哪些城市至少有一個倉庫的職工工資為1220元？ 這個查詢可以使用如下的嵌套查詢： SELECT 城市FROM ck WHERE 倉庫號IN; (SELECT倉庫號FROM zg WHERE工資=1220) 結(jié)果顯示如圖4-6所示。,4.2.5 幾個特殊運算符,SQL SELECT有如下幾個特殊運算符： BETWEENAND：在“和之間”，并且包括兩個邊界值

11、。 LIKE：字符串匹配運算符，通配符“%”表示0個或多個字符，另外還有一個通配符“_”(下劃線)表示一個字符。 !=：在SQL中“不等于”用“!=”表示，另外還可以用否定運算符NOT寫出等價的命令。 例4.7 查詢工資在12201270元之間的職工所有信息。 SELECT *FROM zg WHERE 工資BETWEEN 1220 AND1270,4.2.6 排序,SQL SELECT命令可以將查詢結(jié)果排序，實現(xiàn)該排序的短語是ORDER BY。具體格式如下： ORDER BY Order_Item ASC | DESC,Order_Item ASC |DESC 根據(jù)列的數(shù)據(jù)對查詢結(jié)果進行排序

12、，每個Order_Item都必須對應(yīng)查詢結(jié)果中的一列。 可以按升序(ASC)或降序(DESC)排序，可以按一列或多列排序。 例4.9 按zg表中的工資值升序查找出所有職工信息。 SELECT*FROM zg ORDER BY工資,4.2.7 簡單的計算查詢,事實上，SQL不僅具有一般的檢索能力，而且還有計算方式的檢索；SQL不僅可以查詢表中數(shù)據(jù)，而且還可以計算表中的數(shù)據(jù)。用于計算檢索的函數(shù)有如下幾個。 COUNT：計算列中選定項的數(shù)目，計算查詢輸出的行數(shù)。 SUM：計算列中數(shù)值的和。 AVG：計算列中數(shù)值的平均值。 MAX：確定列中的最大值。 MIN：確定列中的最小值。 這些函數(shù)可以在SELE

13、CT短語中對查詢結(jié)果進行計算。 例4.10 查詢gys表中所在地的數(shù)目。 SELECT COUNT(DISTINCT 地址) FROM gys,4.2.9 利用空值查詢,由于SQL支持NULL，所以可以利用空值進行查詢。NULL值具有以下特點。 等價于沒有任何值。 與0、空格和空字符串不同。 排序時具有最大的優(yōu)先權(quán)。 可以用于計算和大多數(shù)的函數(shù)中。 NULL值不改變變量或字段的數(shù)據(jù)類型。 NULL值會影響命令、函數(shù)、表達式的執(zhí)行。 例4.16 查詢已經(jīng)確定供應(yīng)商號的dgd信息。 SELECT* FROM dgd WHERE 供應(yīng)商號 IS NOT NULL,4.2.15 Visual FoxP

14、ro中SQL SELECT的幾個特殊選項,在符合查詢條件的所有記錄中，選擇指定數(shù)量或百分比的記錄，只顯示前幾項記錄。 (1) 使用TOP nExpr PERCENT 短語非常有用，其中nExpr是數(shù)字表達式，當(dāng)不使用PERCENT時，nExpr是132767間的整數(shù)，說明顯示前幾條記錄；當(dāng)使用PERCENT時，nExpr是0.0199.99間的實數(shù)，說明顯示結(jié)果中前百分之幾的記錄。需要注意的是，TOP短語要與ORDER BY短語同時使用才有效。 (2) 將查詢結(jié)果存放到數(shù)組中 可以使用INTO ARRAY ArrayName短語將查詢結(jié)果存放到數(shù)組中，ArrayName可以是任意的數(shù)組變量名。

15、一般將存放查詢結(jié)果的數(shù)組作為二維數(shù)組來使用，每行一條記錄，每列對應(yīng)于查詢結(jié)果的一列。查詢結(jié)果存放在數(shù)組中，可以非常方便地在程序中使用。 (3) 將查詢結(jié)果存放在臨時文件夾中 使用短語INTO CURSOR CursorName可以將查詢結(jié)果存放到臨時數(shù)據(jù)庫文件中，其中CursorName是臨時文件名，該短語產(chǎn)生的臨時文件是一個只讀的.dbf文件，當(dāng)查詢結(jié)束后該臨時文件是當(dāng)前文件，可以像一般的.dbf文件一樣使用(當(dāng)然是只讀)，當(dāng)關(guān)閉文件時該文件將自動刪除。,一般利用INTO CURSOR短語存放一些臨時結(jié)果，比如一些復(fù)雜的匯總可能需要分段完成，需要根據(jù)幾個中間結(jié)果再匯總等，這時利用該短語存放中

16、間結(jié)果就非常合適。使用完后這些臨時文件會自動刪除。 (4) 將查詢結(jié)果存放到永久表中 使用短語INTO DBF | TABLE TableName可以將查詢結(jié)果存放到永久表中(.dbf文件)。 (5) 將查詢結(jié)果存放到文本文件中 使用短語TO FILE FileName ADDITIVE 可以將查詢結(jié)果存放到文本文件中，其中FileName給出了文本文件名(默認是.TXT)，如果使用ADDITIVE則結(jié)果將追加在原文件的尾部，否則將覆蓋原有文件。 (6) 將查詢結(jié)果直接輸出到打印機 使用短語TO PRINTER PROMPT 可以直接將查詢結(jié)果輸出到打印機，如果使用了PROMPT選型，在開始打

17、印之前會彈出打印機設(shè)置對話框。,4.3 操 作 功 能,4.3.1 數(shù)據(jù)插入 Visual FoxPro支持兩種SQL插入命令的格式，可以說第一種格式是標(biāo)準(zhǔn)格式，第二種格式是Visual FoxPro的特殊格式。 第一種格式是： INSERT INTO dbf_name (fname1 , fname2, ) VALUES ( eExpression1 , eExpression2,) 命令說明： INSERT INTO dbf_name指定要追加記錄的表名，dbf_name中可以包含路徑，也可以是一個名稱表達式。 也可以用fname1,fname2,來指定新記錄的值所對應(yīng)的字段名。 VALU

18、ES(eExpression1,eExpression2,)指定新插入記錄的字段值。 第二種格式是： INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR 命令說明： FROM ARRAY ArrayName指定一個數(shù)組，數(shù)組中的數(shù)據(jù)將被插入到新記錄中。 FROM MEMVAR把內(nèi)存變量的內(nèi)容插入到與其同名的字段中。插入新記錄后，指針指向新記錄。,4.3.2 數(shù)據(jù)更新,SQL的數(shù)據(jù)更新命令UPDATE用于更新表中的記錄。其格式如下： UPDATE TableName SET Column_Name1=eExpression1 , Col

19、umn_Name2=eExpression2 WHERE Condition 命令說明： TableName指定要更新記錄的表名。 SET Column_Name1=eExpression1 ,Column_Name1=eExpression2 指定要更新的列及這些列的新值。 WHERE Condition指定要更新記錄的條件。 UPDATE命令的功能是修改指定表中滿足WHERE子句條件的元組。其中SET子句用于指定修改方法，即用的值取代相應(yīng)的屬性列值。如果省略WHERE子句，則表示要修改表中的所有元組。 例如，將“選課”表中學(xué)生號為003號的學(xué)生成績增加15分，應(yīng)使用如下命令： UPDATE

20、 選課 SET 成績=成績+15 WHERE 學(xué)生號=003“,4.3.3 數(shù)據(jù)刪除,SQL從表中刪除DELETE數(shù)據(jù)的命令格式如下： DELETE FROM TableName WHERE Condition 這里FROM指定從哪個表中刪除數(shù)據(jù)，WHERE指定被刪除的記錄所滿足的條件，如果不使用WHERE子句，則刪除該表中的全部記錄。 使用DELETE命令刪除的記錄并不是物理刪除，只是在記錄上打上一個刪除標(biāo)記。如果要真正地從數(shù)據(jù)庫中把相應(yīng)的記錄清除掉，還必須用PACK命令。打上刪除標(biāo)記的記錄可以通過RECALL命令恢復(fù)。SQL中刪除記錄的格式如下： DELETE FROM TableName

21、 WHERE Condition 命令說明： TableName指定要給其中的記錄加刪除標(biāo)記的表。 WHERE Condition指定給某些記錄做刪除標(biāo)記的條件。,4.4 定 義 功 能,4.4.1 表的定義 在Visual FoxPro中可通過SQL語句的CREATE TABLE命令來建立表。 命令格式： CREATE TABLE | DBF TableName1 NAME LongTableName FREE (FieldName1 FieldType (nFieldWidth,nPrecision)NULL | NOT NULL CHECK lExpression1ERROR cMess

22、ageText1 DEFAULT eExpression1 PRIMARY KEY | UNIQUE REFERENCES TableName2 TAG TagName1 NOCPTRANS ,FieldName2 ,PRIMARY KEY eExpression2 TAG TagName2 |,UNIQUE eExpression3 TAG TagName3 ,FOREIGN KEY eExpression4 TAG TagName4 NODUP REFERENCES TableName3 TAG TagName5 ,CHECK lExpression2 ERROR cMessageText

23、2) | FROM ARRAY ArrayName,4.4.2 表的刪除,Visual FoxPro可以通過SQL的DROP TABLE命令來刪除表。 命令格式： DROP TABLE TableName 命令說明： 執(zhí)行DROP TABLE命令應(yīng)在當(dāng)前數(shù)據(jù)庫下，否則將從磁盤上刪除表文件，但該表在數(shù)據(jù)庫文件中的信息并沒有刪除，以后會出現(xiàn)錯誤提示。若要刪除數(shù)據(jù)庫中的表，最好先打開該數(shù)據(jù)庫，在當(dāng)前數(shù)據(jù)庫中進行操作。 TableName指定要移出并刪除的表的名稱。DROP TABLE直接從磁盤上刪除table_name所對應(yīng)的.dbf文件。 如果table_name是數(shù)據(jù)庫中的表并且相應(yīng)的數(shù)據(jù)庫是當(dāng)

24、前數(shù)據(jù)庫，則從數(shù)據(jù)庫中刪除了表；否則雖然從磁盤上刪除了.dbf文件，但是在數(shù)據(jù)庫中(記錄在.dbc文件中)的信息卻沒有刪除，此后會出現(xiàn)錯誤提示。所以要輸出數(shù)據(jù)庫中的表時，最好在數(shù)據(jù)庫中進行操作，即數(shù)據(jù)庫是當(dāng)前打開的數(shù)據(jù)庫。,4.4.3 表結(jié)構(gòu)的修改,在Visual FoxPro中可通過SQL的ALTER TABLE命令來修改表。 格式1： ALTER TABLE TableName1 ADD | ALTER COLUMN FieldName1 FieldType ( nFieldWidth , nPrecision ) NULL | NOT NULL CHECK lExpression1 ERROR cMessageText1 DEFAULT eExpression1 PRIMARY KEY |