第04次課SQL的基本操作（02）

1、四、 利用查詢設(shè)計器查詢4.1 查詢設(shè)計器窗口：查詢設(shè)計器是采用SQL查詢的模式，使用圖形界面的方式，快捷實現(xiàn)查詢的一種查詢形式，如右圖所示。查詢設(shè)計器所設(shè)計的查詢具有直觀、高效、簡便等特點，可以保存為查詢文件（.QPR），因此可以反復使用。查詢設(shè)計器構(gòu)成如下：u 字段：選擇需要顯示的字段，對應SELECT；u 聯(lián)接：編輯聯(lián)接條件，對應ON；u 篩選：指定查詢條件，對應WHERE；u 排序依據(jù)：用于指定排序字段，對應ORDER BY；u 分組依據(jù)：用于分組，對應GROUP BY；u 雜項：指定是否要重復記錄及列在前面的記錄，與DISTINCT及TOP對應。4.2 建立查詢：1利用“命令”創(chuàng)建查

2、詢：格式：CREATE QUERY QueryFile2利用“查詢向?qū)А苯⒉樵儯海ɑ静豢?，僅簡單介紹）3利用“查詢設(shè)計器”創(chuàng)建查詢（）：u “字段”標簽的使用方法：注意表達式的輸入方法等同命令：SELECT 學號, 姓名, (語文+英語+數(shù)學)/3 as 平均成績；u “聯(lián)接”標簽的使用方法：利用“查詢設(shè)計器”創(chuàng)建查詢，如果涉及多個表，則多個表之間需要建立聯(lián)接。查詢設(shè)計器中的聯(lián)接為“超級聯(lián)接”簡稱“超聯(lián)接”，超聯(lián)接的聯(lián)接分類及聯(lián)接規(guī)則如下圖所示：u “篩選”標簽的使用方法：u “排序”標簽的使用方法u “分組”標簽的設(shè)置方法u “雜項”標簽的設(shè)置方法u 查詢的“輸出去向”l 瀏覽：在瀏覽窗

3、口中輸出查詢結(jié)果（默認）；l 臨時表：表關(guān)閉后自動消失；l 表：永久表，上機考試的主要類型；l 圖形：統(tǒng)計圖，一般不考；l 屏幕：屏幕直接輸出（打印機、文本文件）；l 報表：報表文件（.frx）l 標簽：標簽文件（.lbx）4“查詢設(shè)計器”使用舉例（）：例題2.29 利用查詢設(shè)計器創(chuàng)建一個查詢，其功能是從xuesheng和chengji兩個表中找出1982年出生的漢族學生記錄。查詢結(jié)果包含學號、姓名、數(shù)學、英語和信息技術(shù)5個字段；各記錄按學號降序排列；查詢?nèi)ハ驗楸韙able1。最后將查詢保存為query1.qpr，并運行查詢。（練習系統(tǒng)第26套）4.3 運行查詢：格式：DO QueryFile

4、.QPR注意：使用命令運行查詢時，必須給出查詢文件的擴展名.QPR，如：DO abc.qpr五、 視圖：1. 用命令定義視圖：格式：CREATE VIEW ViewName AS SelectStatement其中：SelectStatement ：查詢語句，用于限定視圖中的數(shù)據(jù)2. 利用視圖設(shè)計器創(chuàng)建視圖：（）3“視圖”使用舉例：（）例題2.30 首先創(chuàng)建數(shù)據(jù)庫cj_m，并向其中添加xuesheng表和chengji表。然后在數(shù)據(jù)庫中創(chuàng)建視圖view1，其功能是利用該視圖只能查詢數(shù)學、英語和信息技術(shù)3門課程中至少有一門不及格的學生記錄；查詢結(jié)果包含學號、姓名、數(shù)學、英語和信息技術(shù)5個字段；各

5、記錄按學號降序排列。最后利用剛創(chuàng)建的視圖view1查詢視圖中的全部信息，并將結(jié)果存儲于表table2中（練習系統(tǒng)第26套）。4視圖的特點：u 可以從一個表或多個相關(guān)聯(lián)的表中提取有用信息創(chuàng)建視圖；u 只有在包含視圖的數(shù)據(jù)庫打開時，才能使用視圖；u 視圖不含真正的數(shù)據(jù)，僅是原表的一個窗口；u 在視圖中可以進行查詢操作，但視圖的插入、更新操作有一定的限制；5視圖分類：u 本地視圖：使用當前數(shù)據(jù)庫中的表建立的視圖。u 遠程視圖：使用非當前數(shù)據(jù)庫中的表建立的視圖。6. 視圖設(shè)計器與查詢設(shè)計器的區(qū)別：（）u 視圖建立后直接保存在數(shù)據(jù)庫中，在磁盤上沒有相應文件，查詢要形成.qpr文件；u 視圖可以更新，查詢

6、不能更新（除非重新查詢）；u 查詢有“查詢?nèi)ハ颉保晥D沒有。六、 SQL的表操作功能：6.1 插入（）：標準格式：INSERT INTO dbf_name(fname1,fname2,) VALUES(eExoression1,eExoression2,)VFP特殊格式：INSERT INTO dbf_name FROM ARRAY ArrayName例題2.31：往訂購單關(guān)系中插入元組（"E7","S4","OR01",2001/05/25）。實現(xiàn)語句：INSERT INTO 訂購單 VALUES("E7",&q

7、uot;S4","OR01",2001-05-25)例題2.32：往訂購單關(guān)系中插入元組（"E7", "OR01"）。實現(xiàn)語句：INSERT INTO 訂購單(職工號,訂購單號) VALUES("E7","OR01")注意：用INSERT命令在數(shù)據(jù)表中插入數(shù)據(jù)時，該數(shù)據(jù)表事先可以不必打開；6.2 更新（）：格式：UPDATE TableName SET Column_Name1=eExoression1,Column_Name2=eExoression2 WHERE Condition

8、例題2.33：給WH1倉庫的職工提高10%的工資。實現(xiàn)語句：UPDATE 職工SET 工資=工資*1.1 WHERE 倉庫號="WH1"等價語句：replace工資 with工資*1.1 for倉庫號="WH1"6.3 刪除（）：格式：DELETE FROM TableName WHERE Condition例題2.34：刪除倉庫關(guān)系中倉庫號值是WH2的元組。實現(xiàn)語句：DELETE FROM 倉庫 WHERE 倉庫號="WH2"注意：在VFP中，SQL DELETE命令同樣是邏輯刪除，如果要物理刪除記錄同樣需要使用PACK命令。七、

9、SQL的表定義功能：7.1 表定義：1.建立數(shù)據(jù)表簡單操作基本格式：CREATE TABLE|DBF TableName (FieldName1 FieldType(nFieldWidth,nPrecision)其中：TableName：新建數(shù)據(jù)表的表名稱FieldName ：字段名FieldType ：用字母表示的字段類型NFieldWidth：字段總寬度NPrecision：小數(shù)位數(shù)例題2.35：用SQL命令建立“訂貨管理”數(shù)據(jù)庫，然后建立“倉庫”數(shù)據(jù)表。實現(xiàn)語句：CREATE DATABASE 訂貨管理CREATE TABLE 倉庫(倉庫號 C(5),城市 C(10),面積 I)2.數(shù)據(jù)

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

11、默認值設(shè)置方法基本格式：CREATE TABLE|DBF TableName1 (FieldName1 FieldType(nFieldWidth,nPrecision) DEFAULT eExpression )例題2.37：建立數(shù)據(jù)表“倉庫”，并將“倉庫”數(shù)據(jù)表的“面積”字段的默認值設(shè)置為200。實現(xiàn)語句：CREATE TABLE 倉庫(倉庫號 C(5),城市 C(10),面積 I DEFAULT 200)4.數(shù)據(jù)表字段主關(guān)鍵字或候選索引設(shè)置方法基本格式：CREATE TABLE|DBF TableName1 (FieldName1 FieldType(nFieldWidth,nPreci

12、sion) PRIMARY KEY | UNIQUE ) 注意：UNIQUE在此處是候選索引例題2.38：建立數(shù)據(jù)表“倉庫1”，并將“倉庫號”字段設(shè)置為主關(guān)鍵字。實現(xiàn)語句：CREATE TABLE 倉庫1(倉庫號 C(5) PRIMARY KEY,城市 C(10),面積 I)5.數(shù)據(jù)表表間關(guān)系設(shè)置方法基本格式：CREATE TABLE|DBF TableName1 (FieldName1 FieldType(nFieldWidth,nPrecision) FOREIGN KEY eExpression TAG TagName REFERENCES TableName2 )例題2.39：建立數(shù)據(jù)

13、表“供應商”（供應商號 C(5),供應商名 C(20),地址 C(20)），其中“供應商號”設(shè)置為主關(guān)鍵字；然后建立數(shù)據(jù)表“訂購單”(職工號 C(5),供應商號 C(5),訂購單號 C(5),訂購日期 D)，將“訂購單號”設(shè)置為主關(guān)鍵字，并在“供應商”與“訂購單”之間建立以“供應商號”為關(guān)鍵字的關(guān)系。實現(xiàn)語句：CREATE TABLE 供應商(供應商號 C(5) PRIMARY KEY,供應商名 C(20),地址 C(20)CREATE TABLE 訂購單(職工號 C(5),供應商號 C(5),訂購單號 C(5),訂購日期 D,；FOREIGN KEY 供應商號 TAG 供應商號 REFERE

14、NCES 供應商)7.2 表刪除：基本格式：DROP TABLE|DBF TableName 例題2.40：刪除數(shù)據(jù)表“倉庫1”。實現(xiàn)語句：DROP TABLE 倉庫17.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 cMessageText&&設(shè)置字段的有效性規(guī)則DEF

15、AULT eExpression&&設(shè)置字段的有效性規(guī)則PRIMARY KEY | UNIQUE&&設(shè)置字段索引類型REFERENCES TableName TAG TagName&&修改表的關(guān)聯(lián)例題2.41：為"訂購單"表增加一個貨幣類型的總金額字段。實現(xiàn)語句：ALTER TABLE 訂購單 ADD 總金額 Y CHECK 總金額>0 ERROR "總金額應該大于0"例題2.42：將"訂購單"表的訂購單號字段的寬度由原來的5改為6。實現(xiàn)語句：ALTER TABLE 訂購單 ALT

16、ER 訂購單號 C(6)2.定義、修改、刪除字段級有效性規(guī)則和默認值定義：基本格式：ALTER TABLE TableName ALTER COLUMN FieldNameNULL | NOT NULL&&字段是否允許為空SET CHECK lExpression ERROR cMessageText&&設(shè)置字段的有效性規(guī)則DROP CHECK&&刪除字段的有效性規(guī)則SET DEFAULT eExpression&&設(shè)置字段的默認值DROP DEFAULT&&刪除字段的默認值例題2.43：修改(定義)總金額字段的有

17、效性規(guī)則。實現(xiàn)語句：ALTER TABLE 訂購單 ALTER 總金額 SET CHECK 總金額>100 ERROR "總金額應該大于100"例題2.44：刪除總金額字段的有效性規(guī)則。實現(xiàn)語句：ALTER TABLE 訂購單 ALTER 總金額 DROP CHECK3.刪除字段、修改字段名，定義、修改、刪除表一級的有效性規(guī)則：基本格式：ALTER TABLE TableName DROP COLUMN FieldName&&刪除字段RENAME COLUMN FieldName1 TO FieldName2&&修改字段名SET CHE

18、CK lExpression ERROR cMessageText&&設(shè)置表的有效性規(guī)則DROP CHECK&&刪除表的有效性規(guī)則ADD PRIMARY KEY eExpression TAG TagName FOR lExpression &&設(shè)置主索引DROP PRIMARY KEY&&刪除主索引ADD UNIQUE eExpression TAG TagName FOR lExpression &&候選索引DROP UNIQUE TAG TagName&&刪除索引ADD FOREIGN KEY eExpression TAG TagName FOR lExpression REFERENCES TableName TAG TagName &&建立表間關(guān)系DROP FOREIGN KEY TAG TagName&&a