第四章關系數(shù)據(jù)庫標準語言SQL

1、第四章 關系數(shù)據(jù)庫標準語言SQL第一節(jié) 查詢功能一.最短格式:SELECT FROM 注:查詢項:要查詢的字段,表達式例:查詢職工表中的所有信息SELE * FROM 職工注: *代表所有字段例:查詢倉庫表中倉庫號,面積信息SELE 倉庫號,面積 FROM 倉庫例:查詢職工表中不重復的倉庫號信息SELE DIST 倉庫號 FROM 職工注: DISTINCT代表在查詢結(jié)果中去掉重復值,并進行升序排序例:查詢職工表所有職工的職工號,月工資(工資)和年工資(工資*12)SELE 職工號,工資 AS 月工資,工資*12 AS 年工資 FROM 職工二.條件短語 WHERE 注:條件表達式是能返回一個

2、邏輯值的式子,該短語相當于VFP命令中的FOR 短語,讓查詢命令僅對符合條件的記錄進行操作例:查詢職工表中工資大于1210元的職工信息SELE * FROM 職工 WHERE 工資1210例:查詢在北京和上海的倉庫信息 SELE * FROM 倉庫 WHERE 城市 IN(北京,上海)注: IN (表達式列表)當字段的值與表達式列表中的任何一項相等時返回.T.例:查詢不在北京和上海的倉庫信息 SELE * FROM 倉庫 WHERE 城市 NOT IN (北京,上海)注: NOT IN (表達式列表)當字段的值與表達式列表中的任何一項都不相等時返回.T.例:查詢職工表中工資在1230與1250

3、之間的職工信息 SELE * FROM 職工 WHERE 工資 BETWEEN 1230 AND 1250注: BETWEEN AND 當字段的值在數(shù)據(jù)1與數(shù)據(jù)2之間時返回.T.例:查詢訂購單表中所有供應商號為NULL值的信息SELE * FROM 訂購單 WHERE 供應商號 IS NULL注: IS NULL當字段的值為NULL值時返回.T.例:查詢訂購單表中所有訂購日期不為NULL值的信息 SELE * FROM 訂購單 WHERE 訂購日期 IS NOT NULL注: IS NOT NULL當字段的值不為NULL值時返回.T.例:查詢供應商表中供應商名里包含廠字的信息 SELE * F

4、ROM 供應商 WHERE 供應商名 LIKE %廠%注: 字段名 LIKE 當字符型字段的值符合通配符表達式時返回.T.在SQL中,%代表多個任意字符, _代表單個字符三.排序短語 ORDER BY DESCASC, DESCASC例:按面積降序查詢倉庫表的所有信息 SELE * FROM 倉庫 ORDER BY 面積 DESC例:按工資降序,工資相同按職工號升序查詢職工表的所有信息 SELE * FROM 職工 ORDER BY 工資 DESC,職工號例:按倉庫號降序查詢職工表的所有信息 SELE * FROM 職工 ORDER BY 1 DESC注: ORDER BY 或GROUP BY

5、 短語后跟的是數(shù)字而不是字段名則代表按查詢結(jié)果的第幾列進行排序或分組四.計算查詢1.SUM 求和 2.AVG 求平均 3.MAX 求最大值4.MIN 求最小值 5.COUNT計數(shù)SELE (表達式) FROM 注:根據(jù)使用的計算函數(shù),查詢計算結(jié)果.一般在查詢項中只包括計算項,查詢結(jié)果如果沒有分組短語則只有一條記錄例:查詢職工表中的職工人數(shù) SELE COUNT(*) AS 職工人數(shù) FROM 職工注:COUNT函數(shù)是用來統(tǒng)計記錄個數(shù)的,因此,括號內(nèi)如果沒有DISTINCT選項則無論是使用哪一個字段名都是同樣的結(jié)果,所以一般使用*例:統(tǒng)計職工表中的倉庫個數(shù) SELE COUNT(DIST 倉庫號

6、) AS 倉庫個數(shù) FROM 職工例:查詢職工表中職工的總工資和平均工資以及年總工資和年平均工資 SELE SUM(工資) AS 總工資,AVG(工資) AS 平均工資,SUM(工資*12) AS 年總工資,AVG(工資*12) AS 年平均工資 FROM 職工例:查詢職工表中職工的最高工資和最低工資 SELE MAX(工資) AS 最高工資,MIN(工資) AS 最低工資 FROM 職工五.分組計算查詢GROUP BY , HAVING 例:查詢各倉庫職工的總工資和平均工資 SELE 倉庫號,SUM(工資) AS 總工資,AVG(工資) AS 平均工資 FROM 職工 GROUP BY 倉庫

7、號例:查詢訂單數(shù)不小于2的所有職工的訂單數(shù),平均金額信息SELE 職工號,COUNT(*) AS 訂單數(shù),AVG(總金額) AS 平均金額 FROM 訂購單 GROUP BY 職工號 HAVI COUNT(*)=2六.聯(lián)接查詢SELE FROM , WHERE AND 例:查詢在北京工作的職工的職工號,工資及所在倉庫的倉庫號,面積 SELE 職工號,工資,倉庫.倉庫號,面積 FROM 倉庫,職工 WHERE 倉庫.倉庫號=職工.倉庫號 AND 城市=北京例:查詢由在北京工作的職工所開的訂購單的訂購單號,訂購日期,職工號,供應商號,并將結(jié)果按訂購單號升序排列 SELE 訂購單號,訂購日期,職工.

8、職工號,供應商號 FROM 倉庫,職工,訂購單 WHERE 倉庫.倉庫號=職工.倉庫號 AND 職工.職工號=訂購單.職工號 AND 城市=北京 ORDER BY 訂購單號七.嵌套查詢 SELE FROM WHERE IN|=|=|=|NOT IN 注:子查詢是另一個查詢,它只有一個查詢項,它的查詢結(jié)果是括號外查詢的條件的一部分例:查詢在北京和上海工作的職工的職工號,工資信息SELE 職工號,工資 FROM 職工,倉庫 WHERE 職工.倉庫號=倉庫.倉庫號 AND 城市 IN (北京,上海)*以上方法是用連接完成的* SELE 職工號,工資 FROM 職工 WHERE 倉庫號 IN (SEL

9、E 倉庫號 FROM 倉庫 WHERE 城市 IN (北京,上海)*此類查詢(查詢項在一個表,查詢條件在另一表)的一般格式SELE FROM WHERE IN (SELE FROM WHERE )注:在VFP中,SQL語言的嵌套只能有一層例:查詢沒有職工工作的倉庫信息SELE * FROM 倉庫 WHERE 倉庫號 NOT IN (SELE 倉庫號 FROM 職工)例:查詢工資高于所有職工平均工資的職工信息(大于等于)SELE * FROM 職工 WHERE 工資=(SELE AVG(工資) FROM 職工)八.超連接查詢SELE FROM INNER|LEFT|RIGHT|FULL JOIN

10、 ON INNER JOIN 等價于 JOIN 為內(nèi)部連接,與普通連接相同LEFT JOIN 左連接,即除滿足條件的記錄出現(xiàn)在查詢結(jié)果中外,第一個表中不滿足連接條件的記錄也出現(xiàn)在查詢中RIGH JOIN 右連接,即除滿足條件的記錄出現(xiàn)在查詢結(jié)果中外,第二個表中不滿足連接條件的記錄也出現(xiàn)在查詢中FULL JOIN 完全連接,即兩個表中記錄不管是否滿足條件都在查詢結(jié)果中出現(xiàn)注:不滿足條件的記錄對應部分顯示為.NULL.例:INSERT INTO 職工 VALUE(WH5,E8,1240)注: 在職工表的尾部加入一條新記錄*普通連接SELE * FROM 倉庫,職工 WHERE 倉庫.倉庫號=職工.

11、倉庫號*超連接SELE * FROM 倉庫 INNER JOIN 職工 ON倉庫.倉庫號=職工.倉庫號將INNER JOIN 改為LEFT JOIN 、RIGHT JOIN、FULL JOIN分別練習。例:查詢由工資高于1230元的職工所開的訂購單信息SELE 訂購單.* FROM 訂購單 INNER JOIN 職工 ON 訂購單.職工號=職工.職工號 WHERE 工資=1230九.顯示部分結(jié)果SELE TOP PERCENT FROM ORDER BY 例29:顯示面積最大的2個倉庫的信息SELE TOP 2 * FROM 倉庫 ORDER BY 面積 DESC例30:顯示工資最低的50%職

12、工的職工號,工資信息SELE TOP 50 PERCENT 職工號,工資 FROM 職工 ORDER BY 工資十查詢?nèi)ハ?.將查詢結(jié)果保存在數(shù)組INTO ARRAY *DISP MEMO LIKE AAA2.將查詢結(jié)果保存在臨時文件(臨時表)INTO CURSOR 顯示方法:BROWSE3.將查詢結(jié)果保存在DBF表文件INTO TABLE|DBF 例33:將倉庫表復制到CKBAK.DBF中 SELE * FROM 倉庫 INTO TABLE CKBAK例34:查詢工資最高的三個職工的職工號,倉庫號信息,并將結(jié)果保存在表文件JG.DBF中SELE TOP 3 職工號,倉庫號 FROM 職工 O

13、RDER BY 工資 DESC INTO TABLE JG注:此時JG.DBF自動處于打開狀態(tài),且為當前表4.將查詢結(jié)果保存在文本文件TO FILE ADDITIVE顯示方法：MODIFY FILE JG5.將查詢結(jié)果通過打印機進行輸出TO PRINTER PROMPT 第二節(jié) 操作功能一.插入INSERT INTO (字段名列表) VALUES(數(shù)據(jù)列表)例:在倉庫表中插入一條記錄 WH5 蘭州 700 INSERT INTO 倉庫 VALUE(WH5,蘭州,700)注:當為指定表的所有字段都給數(shù)據(jù)時,不用指定字段,但數(shù)據(jù)的類型和個數(shù)必須與表結(jié)構(gòu)相符二.更新UPDATE SET = WHER

14、E 例:為所有北京職工工資上調(diào)20%UPDA 職工 SET 工資=工資*1.2 WHERE 倉庫號 IN (SELE 倉庫號 FROM 倉庫 WHERE 城市=北京)三.刪除DELETE FROM WHERE 注:本命令同樣為邏輯刪除,如果要物理刪除記錄需要繼續(xù)使用PACK命令例:刪除面積為750的倉庫DELE FROM 倉庫 WHERE 面積=750第三節(jié) 定義功能一.表的定義CREATE TABLE|DBF FREE (,)【字段描述】 字段名 類型(,) NULL|NOT NULL PRIMARY KEY|UNIQUE CHECK ERROR DEFAULT 【表聯(lián)系描述】 FOREIG

15、N KEY TAG REFERENCES TAG 例:創(chuàng)建數(shù)據(jù)庫訂貨管理1,并創(chuàng)建數(shù)據(jù)庫表 倉庫1,職工1,訂購單1,供應商1以及表與表之間的聯(lián)系 MODI DATA 訂貨管理1 CREATE TABLE 倉庫1(倉庫號 C(5) PRIM KEY,城市 C(10),面積 I CHECK 面積=0 ERROR 面積不能小于0 DEFAULT 400) CREATE TABLE 職工1(倉庫號 C(5),職工號 C(5) PRIM KEY,工資 I,FORE KEY 倉庫號 TAG 倉庫號 REFE 倉庫1) CREATE TABLE 供應商1(供應商號 C(5) PRIM KEY,供應商名 C

16、(20),地址 C(5) CREATE TABLE 訂購單1(職工號 C(5),供應商號 C(5) NULL,訂購單號 C(5) PRIM KEY,訂購日期 D NULL,FORE KEY 職工號 TAG 職工號 REFE 職工1,FORE KEY 供應商號 TAG 供應商號 REFE 供應商1)二.表的修改格式1:ALTER TABLE ALTER|ADD COLUMN 功能:修改(ALTER)或添加(ADD)指定的字段*在表創(chuàng)建命令完成的基礎上完成如下例題:例:為訂購單1表添加一個新字段 總金額 N (7)ALTER TABLE 訂購單1 ADD 總金額 N(7)例:將訂購單1表中的總金額

17、字段改為整型ALTER TABLE 訂購單1 ALTER 總金額 I 格式2:ALTER TABLE ALTER COLUMN NULL NOT NULLSET CHECK ERROR SET DEFAULT DROP CHECKDROP DEFAULT功能:為指定的字段添加有效規(guī)則或默認值,也可用來刪除有效規(guī)則或默認值例:為訂購單1表的總金額字段設置有效規(guī)則為總金額不能小于0ALTER TABLE 訂購單1 ALTER 總金額 SET CHECK 總金額=0 ERROR 總金額不能小于0!例:刪除訂購單1表總金額字段的有效規(guī)則ALTER TABLE 訂購單1 ALTER 總金額 DROP C

18、HECK格式3:ALTER TABLE DROP COLUMN DROP PRIMARY KEYDROP UNIQUE TAG ADD PRIMARY KEY TAG ADD UNIQUE TAG RENAME COLUMN TO 功能:刪除字段,主索引,候選索引或增加主索引,候選索引或給指定的字段改名例:將訂購單1表的總金額字段改名為金額ALTER TABLE 訂購單1 RENAME 總金額 TO 金額例:刪除訂購單1中的金額字段ALTER TABLE 訂購單1 DROP 金額例:將訂購單1中的訂購日期字段創(chuàng)建為候選索引,索引名RQALTER TABLE 訂購單1 ADD UNIQUE 訂購日期 TAG RQ例:刪除訂購單1中的候選索引RQALTER TABLE 訂購單1 DROP U