1、第三部分 關系數據庫標準語言SQL一、 SQL概述1.1 SQL的概念：SQL(Structured Query Language)是結構化查詢語言的簡稱，它引領著關系數據庫的發展方向，是未來關系數據庫發展的標準。1.2 SQL的特點：u 一體化u 高度非過程化u 非常簡潔u 直接以命令方式交互使用1.3 SQL的主要命令：u 數據查詢：SELECTu 數據定義：CREATE、DROP、ALTERu 數據操縱：INSERT、UPDATE、DELETE二、 使用命令建立SQL查詢2.1查詢的概念：查詢是從指定的表或視圖中提取滿足條件的記錄，然后按照想得到的輸出類型定向輸出查詢結果。2.2 SQL

2、查詢語句格式：u SELECT : 要查詢的數據（字段）u FROM ：數據來源(要查詢的數據來自哪個或哪些表)u WHERE ：查詢需要滿足的條件u GROUP BY ：對查詢的數據進行分組u ORDER BY ：對查詢結果進行排序2.3 SQL簡單查詢：1. 在當前數據庫當前表中查詢某一數據基本格式：SELECT Select_Item FROM Table 其中：Select_Item ：字段名列表 Table ：數據表名稱例題2.1：從職工關系中檢索所有工資值。實現語句：SELECT 工資 FROM 職工2. 在指定數據庫指定表中查詢某一數據基本格式：SELECT Alias.Sele

3、ct_Item FROM DatabaseName!Table例題2.2：從訂貨管理數據庫中的職工數據表中檢索所有工資值。實現語句：SELECT 職工.工資 FROM 訂貨管理數據庫!職工3. 在當前數據庫當前表中查詢所有數據基本格式：SELECT * FROM Table例題2.3：從當前數據庫中當前數據表中檢索所有數據。實現語句：SELECT * FROM 職工4. 在當前數據庫當前表中查詢所有數據（不允許內容重復）基本格式：SELECT ALL | DISTINCT Select_Item FROM Table例題2.4：從當前數據庫中當前數據表中檢索所有不重復的工資數據。實現語句：SE

4、LECT DISTINCT 工資 FROM 職工5. 在當前數據庫當前表中進行條件查詢基本格式：SELECT Select_Item FROM Table WHERE JoinCondition例題2.5：在當前數據庫當前表中，查詢工資大于1210元職工的倉庫號Select 倉庫號 from 職工 where 工資>1210實現語句：SELECT 倉庫號 FROM 職工 WHERE 工資>1210例題2.6：查詢在倉庫“WH1”或“WH2”工作，并且工資少于1250元的職工工號實現語句：SELECT 職工號 FROM 職工 WHERE 工資<1250 AND (倉庫號=”WH

5、1” OR 倉庫號=”WH2”)完整格式：SELECT ALL|DISTINCT Alias.Select_Item |* FROM DatabaseName!Table WHERE JoinCondition2.4 SQL聯接查詢：聯接查詢的含義：聯接查詢是一種基于多個關系的查詢，即將多個數據表中數據構成一個查詢集合，并在其中查詢出符合要求的記錄。例題2.7：找出工資多于1230元的職工號和他們所在的城市。實現語句：SELECT 職工號,城市 FROM 職工,倉庫 WHERE 工資>1230 AND (職工.倉庫號=倉庫.倉庫號)特別注意：u 職工.倉庫號=倉庫.倉庫號 是連接條件u

6、如果在FROM之后有兩個關系，則這兩個關系之間肯定有一種聯系，否則無法構成檢索表達式例題2.8：找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市。實現語句：SELECT 職工號,城市 FROM 職工,倉庫 WHERE 面積>400 AND (職工.倉庫號=倉庫.倉庫號)2.5 SQL嵌套查詢：例題2.9：哪些城市至少有一個倉庫的職工工資為1250元？實現語句：SELECT 城市 FROM 倉庫 WHERE 倉庫號 in (SELECT 倉庫號 FROM 職工 WHERE 工資=1250)例題2.10：查詢所有職工的工資都多于1210元的倉庫信息。實現語句：SELECT *

7、 FROM 倉庫 WHERE 倉庫號 NOT IN (SELECT 倉庫號 FROM 職工 WHERE 工資<=1210)例題2.11：找出和職工E4掙同樣工資的所有職工。實現語句：SELECT 職工號 FROM 職工 WHERE 工資=(SELECT 工資 FROM 職工 WHERE 職工號=”E4”)2.6 SQL幾個特殊運算符：u BETWEEN AND ：和之間u LIKE：字符串匹配運算符例題2.12：檢索出工資在1220元到1240元范圍內的職工信息。實現語句：SELECT * FROM 職工 WHERE 工資 BETWEEN 1220 AND 1240例題2.13：從供應商

8、關系中檢索出全部公司的信息，不要工廠或其他供應商的信息。實現語句：SELECT * FROM 供應商 WHERE 供應商名 LIKE “%公司”通配符：u % ：表示任意多個字符（含0個）u _ ：表示一個字符2.7 SQL排序：基本格式：ORDER BY Order_Iten ASC|DESC ,Order_Iten ASC|DESC例題2.14：按職工的工資值升序檢索出全部職工信息。實現語句：SELECT * FROM 職工 ORDER BY 工資 注意：默認為升序例題2.15：先按倉庫號排序，再按工資排序并輸出全部職工信息。實現語句：SELECT * FROM 職工 ORDER BY 倉

9、庫號,工資 2.8 SQL計算查詢：1.用于檢索的函數：COUNT()計數 SUM()求和 AVG()計算平均值 MAX()求最大值 MIN()求最小值2.例子：例題2.16：找出供應商所在地的數目。實現語句：SELECT COUNT(DISTINCT 地址) FROM 供應商例題2.17：求出供應商數據表中記錄數目。實現語句：SELECT COUNT(*) FROM 供應商例題2.18：求支付的工資總數。實現語句：SELECT SUM(工資) FROM 職工例題2.19：求北京和上海的倉庫職工的工資總和。實現語句：SELECT SUM(工資) FROM 職工 WHERE 倉庫號 IN (SE

10、LECT 倉庫號 FROM 倉庫 WHERE 城市=”北京” OR 城市=”上海”)例題2.20：求所有職工的工資都多于1210元的倉庫的平均面積。實現語句：SELECT AVG(面積) FROM 倉庫 WHERE 倉庫號 NOT IN (SELECT 倉庫號 FROM 職工 WHERE 工資<=1210)例題2.21：求在WH2倉庫工作的職工的最高工資值。實現語句：SELECT MAX(工資) FROM 職工 WHERE 倉庫號=”WH2”2.9 SQL分組與計算查詢：基本格式：GROUP BY GroupColumn ,GroupColumn 例題2.22：求每個倉庫的職工的平均工資

11、。實現語句：SELECT 倉庫號,AVG(工資) FROM 職工 GROUP BY 倉庫號2.10 SQL的幾個特殊選項：1. 將查詢結果存放到數組中：語句格式：INTO ARRAY ArrayName例題：將查詢到的職工放到數組TEMP中。 SELECT * FROM 職工 INTO ARRAY temp2. 將查詢結果存放到臨時文件中語句格式：INTO CURSOR CursorName臨時文件是一只讀的DBF文件，可以像一般的DBF文件一樣使用，但只讀。當關閉該文件時該文件會自動刪除。例題：將查詢到的職工放到DBF文件TEMP中。 SELECT * FROM 職工 INTO CURSOR

12、 temp3. 將查詢結果存放到永久表中語句格式：INTO DBF|TABLE TableName 例題：將查詢到的職工放到DBF文件TEMP中。 SELECT * FROM 職工 INTO DBF temp4. 將查詢結果存放到文本文件中語句格式：TO FILE FileName例題：將查詢到的職工放到文本文件TEMP中。 SELECT * FROM 職工 TO FILE temp5. 將查詢結果直接輸出到打印機中語句格式：TO PRINT PROMPT例題：SELECT * FROM 職工 TO PRINT三、 利用查詢設計器查詢3.1 查詢設計器：1. 建立查詢的方法：u 使用“項目管理

13、器”建立查詢；u 使用“新建”對話框建立查詢；u 使用命令CREATE QUERY命令建立查詢；u 通過直接編輯.qpr文件建立。2. 查詢設計器：u 字段：選擇需要顯示的字段；u 聯接：編輯聯接條件；u 篩選：指定查詢條件；u 排序依據：用于指定排序字段；u 分組依據：用于分組；u 雜項：指定是否要重復記錄及列在前面的記錄，與DISTINCT及TOP對應。3.2 查詢設計器的局限性：利用查詢設計器不能夠設計出所有的查詢，如：嵌套查詢、查詢去向到數組等。3.3 運行查詢：1.查詢運行方法：u 使用“項目管理器”運行；u 使用命令運行：格式：DO QueryFile注意：使用命令運行查詢時，必須

14、給出查詢文件的擴展名qpr，如：DO abc.qpr如果不給出擴展名(DO abc)，VFP將把命令解釋為 DO abc.prg，即執行程序文件。2查詢去向：u 瀏覽：在“瀏覽(BROWSE)”窗口顯示；u 臨時表：命名的臨時文件；u 表：命名表；u 圖形：統計圖；u 屏幕：屏幕直接輸出（類似LIST）；u 報表：報表文件.frxu 標簽：標簽文件.lbx四、 數據表記錄的SQL操作：4.1 插入：標準格式：INSERT INTO dbf_name(fname1,fname2,) VALUES(eExoression1,eExoression2,)VFP特殊格式：INSERT INTO dbf

15、_name FROM ARRAY ArrayName|FROM MEMVAR例題4.1：往訂購單關系中插入元組（”E7”,”S4”,”OR01”,2001/05/25）。實現語句：INSERT INTO 訂購單 VALUE(”E7”,”S4”,”OR01”,2001-05-25)例題4.2：往訂購單關系中插入元組（”E7”,”OR01”）。實現語句：INSERT INTO 訂購單(職工號,訂購單號) VALUE(”E7”,”OR01”)注意：u 用INSERT命令在數據表中插入數據時，該數據表事先可以不必打開；u 當數據表設置了“主索引”或“候選索引”時，不能用以前的INSERT與APPEND

16、來添加記錄，只能使用INSERT INTO添加。(實際有出入)4.2 更新：格式：UPDATE TableName SET Column_Name1=eExoression1,Column_Name2=eExoression2 WHERE Condition例題3.4：給WH1倉庫的職工提高10%的工資。實現語句：UPDATE 職工SET 工資=工資+工資*0.1 WHERE 倉庫號=”WH1”等價語句：replace工資 with工資*1.1 for倉庫號=”WH1”4.3 刪除：格式：DELETE FROM TableName WHERE Condition例題3.5：刪除倉庫關系中倉庫號

17、值是WH2的元組。實現語句：DELETE FROM 倉庫 WHERE 倉庫號=”WH2”注意：在VFP中，SQL DELETE命令同樣是邏輯刪除，如果要物理刪除記錄同樣需要使用PACK命令。五、 數據表結構的SQL操作：5.1 表定義：1.建立數據表簡單操作基本格式：CREATE TABLE|DBF TableName Name LongTableName | FREE(FieldName1 FieldType(nFieldWidth,nPrecision)其中：TableName：新建數據表的表名稱FieldName ：字段名FieldType ：用字母表示的字段類型NFieldWidth：

18、字段總寬度NPrecision：小數位數FREE：建立自由表例題5.1：用SQL命令建立“訂貨管理1”數據庫，然后建立“倉庫1”數據表。實現語句：CREATE DATABASE 訂貨管理1CREATE TABLE 倉庫1 (倉庫號 C(5),城市 C(10),面積 I)2.數據表字段有效性設置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) CHECK lExpression ERROR cMessageText1 )例題5.2：用SQL

19、命令建立“訂貨管理1”數據庫，然后建立“倉庫1”數據表，并定義面積字段的有效性規則為面積必須大于0。實現語句：CREATE DATABASE 訂貨管理1 CREATE TABLE 倉庫1 (倉庫號 C(5),城市 C(10),面積 I ；CHECK(面積>0) ERROR “面積必須大于0”)3.數據表字段默認值設置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) DEFAULT eExpression )例題5.3：建立數據表“倉

20、庫1”，并將“倉庫1”數據表的“面積”字段的默認值設置為200。實現語句：CREATE TABLE 倉庫1(倉庫號 C(5),城市 C(10),面積 I DEFAULT 200)4.數據表字段主關鍵字或候選索引設置方法基本格式：CREATE TABLE|DBF TableName1 Name LongTableName FREE(FieldName1 FieldType(nFieldWidth,nPrecision) PRIMARY KEY | UNIQUE ) 注意：UNIQUE在此處是候選索引例題5.4：建立數據表“倉庫1”，并將“倉庫號”字段設置為主關鍵字。實現語句：CREATE TAB

21、LE 倉庫1(倉庫號 C(5) PRIMARY KEY,城市 C(10),面積 I)5.2 表刪除：基本格式：DROP TABLE | DBF TableName 例題5.5：刪除數據表“倉庫1”。實現語句：DROP TABLE 倉庫15.3 表結構修改：1.添加新字段、修改已有字段并設置字段級相關規則：基本格式：ALTER TABLE TableName ADD | ALTER COLUMN FieldName FieldType(nFieldWidth,nPrecision)NULL | NOT NULL&&字段是否允許為空CHECK lExpression ERROR c

22、MessageText&&設置字段的有效性規則DEFAULT eExpression&&設置字段的有效性規則PRIMARY KEY | UNIQUE&&設置字段索引類型例題5.6：為訂購單1表增加一個貨幣類型的總金額字段。實現語句：ALTER TABLE 訂購單1 ADD 總金額 Y CHECK 總金額>0 ERROR “總金額應該大于0”例題5.7：將訂購單1表的訂購單號字段的寬度由原來的5改為6。實現語句：ALTER TABLE 訂購單1 ALTER 訂購單號 C(6)2.定義、修改、刪除字段級有效性規則和默認值定義：基本格式：ALTE

23、R TABLE TableName ALTER COLUMN FieldName NULL | NOT NULL&&字段是否允許為空ALTER TABLE TableName ALTER COLUMN FieldName SET CHECK lExpression ERROR cMessageText&&設置字段的有效性規則ALTER TABLE TableName ALTER COLUMN FieldName DROP CHECK&&刪除字段的有效性規則ALTER TABLE TableName ALTER COLUMN FieldName S

24、ET DEFAULT eExpression&&設置字段的默認值ALTER TABLE TableName ALTER COLUMN FieldName DROP DEFAULT&&刪除字段的默認值例題5.8：修改(定義)總金額字段的有效性規則。實現語句：ALTER TABLE 訂購單1 ALTER 總金額 SET CHECK 總金額>100 ERROR “總金額應該大于100”例題5.9：刪除總金額字段的有效性規則。實現語句：ALTER TABLE 訂購單1 ALTER 總金額 DROP CHECK3.刪除字段、修改字段名，定義、修改、刪除表一級的有效性

25、規則：基本格式：ALTER TABLE TableName DROP COLUMN FieldName&&刪除字段ALTER TABLE TableName RENAME COLUMN FieldName1 TO FieldName2&&修改字段名ALTER TABLE TableName SET CHECK lExpression ERROR cMessageText&&設置表的有效性規則ALTER TABLE TableName DROP CHECK&&刪除表的有效性規則例題5.10：將訂購單1表的總金額字段名改為金額。實現語句：ALTER TABLE 訂購單1 RENAME 總金額 TO金額例題5.11：刪除訂購單1金額字段。實現語句：ALTER TABLE 訂購單1 DROP 金額六、 視圖：6.1 視圖的命令操作：1.視圖的定義格式：格式：CREATE VIEW ViewName AS SelectStatement其中：SelectStatemen