二級培訓(xùn)講義SQL查詢查詢設(shè)計(jì)器SQL數(shù)據(jù)表的操作視圖設(shè)計(jì)器

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

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

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

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

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

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

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

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

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

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

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

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

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

14、給出查詢文件的擴(kuò)展名qpr，如：DO abc.qpr如果不給出擴(kuò)展名(DO abc)，VFP將把命令解釋為 DO abc.prg，即執(zhí)行程序文件。2查詢?nèi)ハ颍簎 瀏覽：在“瀏覽(BROWSE)”窗口顯示；u 臨時(shí)表：命名的臨時(shí)文件；u 表：命名表；u 圖形：統(tǒng)計(jì)圖；u 屏幕：屏幕直接輸出（類似LIST）；u 報(bào)表：報(bào)表文件.frxu 標(biāo)簽：標(biāo)簽文件.lbx四、 數(shù)據(jù)表記錄的SQL操作：4.1 插入：標(biāo)準(zhǔn)格式：INSERT INTO dbf_name(fname1,fname2,) VALUES(eExoression1,eExoression2,)VFP特殊格式：INSERT INTO dbf

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

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

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

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

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

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

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

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

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

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

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