vf課件第4章查詢與視圖

1、第第4章章 查詢與視圖查詢與視圖上篇上篇 語言基礎(chǔ)語言基礎(chǔ)主要內(nèi)容：主要內(nèi)容：lSQL語言的特點和功能語言的特點和功能lSQL-SELECT命令的語法和功能命令的語法和功能l查詢設(shè)計器進(jìn)行查詢的過程查詢設(shè)計器進(jìn)行查詢的過程l視圖設(shè)計器進(jìn)行查詢的過程視圖設(shè)計器進(jìn)行查詢的過程本章要求：本章要求：l了解了解SQL語言的特點和功能語言的特點和功能l掌握掌握SELECT語句的語法和功能語句的語法和功能l了解查詢和視圖的異同了解查詢和視圖的異同l了解查詢設(shè)計器和視圖設(shè)計器進(jìn)行查詢的過程了解查詢設(shè)計器和視圖設(shè)計器進(jìn)行查詢的過程4.1 SQL語言概述語言概述4.1.1 SQL 語言的特點語言的特點lSQL(

2、(Structured Query Language) )結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫管理系統(tǒng)的一種標(biāo)準(zhǔn)查結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫管理系統(tǒng)的一種標(biāo)準(zhǔn)查詢語言，由于功能強大、命令簡潔，被廣泛應(yīng)用于各大詢語言，由于功能強大、命令簡潔，被廣泛應(yīng)用于各大數(shù)據(jù)庫類管理軟件。數(shù)據(jù)庫類管理軟件。l數(shù)據(jù)庫中的數(shù)據(jù)很多時侯都是為了查詢，因此數(shù)據(jù)查數(shù)據(jù)庫中的數(shù)據(jù)很多時侯都是為了查詢，因此數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。而在詢是數(shù)據(jù)庫的核心操作。而在SQL語言中，只有一條查語言中，只有一條查詢命令，即詢命令，即SELECT語句。語句。 SQL是一種高度非過程化的語言。是一種高度非過程化的語言。 SQL是一種一體化的

3、語言。是一種一體化的語言。 SQL語言簡潔，易學(xué)易用。語言簡潔，易學(xué)易用。 SQL是面向集合的操作方式。是面向集合的操作方式。 SQL以同一種語法結(jié)構(gòu)提供兩種使用方式。以同一種語法結(jié)構(gòu)提供兩種使用方式。4.1.1 SQL語言的特點語言的特點 數(shù)據(jù)定義數(shù)據(jù)定義DDL( (Data Definition Language) ) CREATE、ALTER、DROP 數(shù)據(jù)操縱數(shù)據(jù)操縱DML( (Data Manipulation Language) ) INSERT、DELETE、UPDATE 數(shù)據(jù)控制數(shù)據(jù)控制DCL( (Data Control Language) ) GRANT、REVOKE 數(shù)據(jù)

4、查詢數(shù)據(jù)查詢DQL( (Data Query Language) ) SELECT 4.1.2 SQL語言的功能語言的功能4.2 SELECT- SQL查詢查詢4.2.1 SELECT語句的語法語句的語法SELECT ALL | DISTINCT & 方式方式. AS & 顯示顯示FROM & 相關(guān)庫相關(guān)庫INTO | TO FILE & 查詢?nèi)ハ虿樵內(nèi)ハ騑HERE | & 關(guān)聯(lián)篩選關(guān)聯(lián)篩選GROUP BY , & 分組統(tǒng)計分組統(tǒng)計HAVING & 篩選篩選ORDER BYASC|DESC, ASC|DESC & 排序排序格式：格

5、式：SELECT ALL|DISTINCT AS 列名列名 FROM 說明：說明：l ALL：表示顯示全部查詢記錄，包括重復(fù)記錄。表示顯示全部查詢記錄，包括重復(fù)記錄。l DISTINCT：表示顯示無重復(fù)結(jié)果的記錄。表示顯示無重復(fù)結(jié)果的記錄。例例4.1 查詢所有學(xué)生信息。查詢所有學(xué)生信息。 SELECT * FROM 學(xué)生基本資料表學(xué)生基本資料表 說明：命令中的說明：命令中的*表示輸出所有的字段。表示輸出所有的字段。例例4.2 列出所有學(xué)生姓名，去掉重名。列出所有學(xué)生姓名，去掉重名。 SELECT DISTINCT 姓名姓名 FROM 學(xué)生基本資料表學(xué)生基本資料表4.2.2 單表查詢語句單表查詢

6、語句格式：格式：SELECT ALL|DISTINCT AS FROM WHERE 功能：功能：從一個表中查詢滿足條件的數(shù)據(jù)。從一個表中查詢滿足條件的數(shù)據(jù)。4.2.3 條件查詢條件查詢運算符運算符含義含義舉例舉例=、=、=、!=、比較大小比較大小 成績成績=80AND、OR、NOT多重條件多重條件 成績成績85 AND 成績成績85 多重條件查詢多重條件查詢例例4.5 查詢選修查詢選修004課程且成績大于課程且成績大于90分或成績小于分或成績小于85分分的學(xué)生成績信息。的學(xué)生成績信息。 SELECT * FROM 成績表成績表; WHERE 課程號課程號= 004 AND( (成績成績90 O

7、R 成績成績85) )例例4.6 查詢學(xué)生基本資料表中土木工程專業(yè)或會計學(xué)專業(yè)查詢學(xué)生基本資料表中土木工程專業(yè)或會計學(xué)專業(yè)學(xué)生的學(xué)號、姓名、性別和年齡信息。學(xué)生的學(xué)號、姓名、性別和年齡信息。 SELECT 學(xué)號學(xué)號, ,姓名姓名, ,性別性別, ,; YEAR( (DATE()()-YEAR( (出生日期出生日期) ) AS 年齡年齡; FROM 學(xué)生基本資料表學(xué)生基本資料表; WHERE 專業(yè)專業(yè)= 土木工程土木工程 OR 專業(yè)專業(yè)= 會計學(xué)會計學(xué) 指定范圍查詢指定范圍查詢格式：格式：NOT BETWEEN AND 說明：說明：的值必須小于的值必須小于的值。的值。例例4.7 查詢成績大于等于

8、查詢成績大于等于85分且成績小于等于分且成績小于等于90分的學(xué)生分的學(xué)生的學(xué)號和課程號信息。的學(xué)號和課程號信息。 SELECT 學(xué)號學(xué)號,課程號課程號 FROM 成績表成績表; WHERE 成績成績 BETWEEN 85 AND 90等價于等價于 SELECT 學(xué)號學(xué)號,課程號課程號 FROM 成績表成績表; WHERE 成績成績=85 AND 成績成績=90 確定集合查詢確定集合查詢說明：說明：IN操作查詢字段值屬于某指定集合的記錄，操作查詢字段值屬于某指定集合的記錄，NOTIN操作查詢字段值不屬于某指定集合的記錄。操作查詢字段值不屬于某指定集合的記錄。例例4.8 在成績表中檢索課程號是在成

9、績表中檢索課程號是001、002和和006的學(xué)生成的學(xué)生成績信息?？冃畔ⅰ?SELECT * FROM 成績表成績表; WHERE 課程號課程號 IN (001 , 002 , 006)等價于等價于 SELECT * FROM 成績表成績表; WHERE 課程號課程號= 001 OR 課程號課程號= 002 ; OR 課程號課程號= 006 部分匹配查詢部分匹配查詢格式：格式： NOT LIKE 說明：說明：字段類型必須是字符型，字符串常量的字符可包括如字段類型必須是字符型，字符串常量的字符可包括如下兩個符號：下兩個符號：%表示表示0個或多個字符，個或多個字符，_表示任意一個字符。表示任意一個

10、字符。注意：注意：在在VFP系統(tǒng)中，一個漢字用一個字符系統(tǒng)中，一個漢字用一個字符“_”表示。表示。例例4.9 在學(xué)生基本資料表中檢索姓張的學(xué)生信息。在學(xué)生基本資料表中檢索姓張的學(xué)生信息。 SELECT * FROM 學(xué)生基本資料表學(xué)生基本資料表; WHERE 姓名姓名 LIKE 張張% 等價于等價于 SELECT * FROM 學(xué)生基本資料表學(xué)生基本資料表; WHERE 張張 $ 姓名姓名思考：查詢姓名中第二個字是思考：查詢姓名中第二個字是“宇宇”的學(xué)生信息。的學(xué)生信息。 空值查詢空值查詢l 在在SELECT語句中，使用語句中，使用IS NULL或或IS NOT NULL來查來查詢某個字段的值

11、是否為空值。詢某個字段的值是否為空值。l IS不能用等號不能用等號 = 代替。代替。4.2.4 統(tǒng)計查詢統(tǒng)計查詢l SQL提供了計數(shù)、求和、求平均值、求最大值、求最提供了計數(shù)、求和、求平均值、求最大值、求最小值函數(shù)，以完成數(shù)據(jù)統(tǒng)計和匯總工作。小值函數(shù)，以完成數(shù)據(jù)統(tǒng)計和匯總工作。l 函數(shù)中如果使用函數(shù)中如果使用DISTINCT，計算時可取消指定列中，計算時可取消指定列中的重復(fù)值。的重復(fù)值。 函函 數(shù)數(shù)功功 能能 AVG( () ) 求求值的平均值值的平均值 COUNT( () 統(tǒng)計記錄的個數(shù)統(tǒng)計記錄的個數(shù) MIN( () ) 求求值的最小值值的最小值 MAX( () ) 求求值的最大值值的最大值

12、 SUM( () ) 求求值的和值的和例例4.10 計算成績表中所有成績的最高分、最低分和平均計算成績表中所有成績的最高分、最低分和平均分。分。 SELECT MAX( (成績成績) ) AS 最高分最高分,; MIN( (成績成績) ) AS 最低分最低分,AVG( (成績成績) ) AS 平均分平均分; FROM 成績表成績表例例4.11 統(tǒng)計選修了課程的學(xué)生人數(shù)。統(tǒng)計選修了課程的學(xué)生人數(shù)。 SELECT COUNT( (DISTINCT 學(xué)號學(xué)號) ) AS 人數(shù)人數(shù); FROM 成績表成績表4.2.4 統(tǒng)計查詢統(tǒng)計查詢格式：格式：SELECT ALL | DISTINCT FROM W

13、HERE GROUP BY , HAVING 功能：對查詢結(jié)果分組。功能：對查詢結(jié)果分組。說明：說明： 可以是字段名和可以是字段名和SQL函數(shù)表達(dá)式，也可以是函數(shù)表達(dá)式，也可以是列序號列序號( (最左邊為最左邊為1) )。 若分組后還要按照一定的條件進(jìn)行篩選，則使用若分組后還要按照一定的條件進(jìn)行篩選，則使用HAVING子句。子句。4.2.5 分組查詢分組查詢例例4.12 統(tǒng)計選修各門課程的學(xué)生人數(shù)。統(tǒng)計選修各門課程的學(xué)生人數(shù)。 SELECT 課程號課程號,COUNT( (學(xué)號學(xué)號) ) AS 人數(shù)人數(shù); FROM 成績表成績表 GROUP BY 課程號課程號例例4.13 查詢每個學(xué)生的平均成績

14、。查詢每個學(xué)生的平均成績。 SELECT 學(xué)號學(xué)號, AVG( (成績成績) ) AS 平均成績平均成績; FROM 成績表成績表 GROUP BY 學(xué)號學(xué)號例例4.14 求選修課程超過或等于求選修課程超過或等于2門的學(xué)生的學(xué)號。門的學(xué)生的學(xué)號。 SELECT 學(xué)號學(xué)號,COUNT( (課程號課程號) ) AS 課程門數(shù)課程門數(shù); FROM 成績表成績表; GROUP BY 學(xué)號學(xué)號 HAVING COUNT( (課程號課程號) )=24.2.5 分組查詢分組查詢4.2.6 排序查詢排序查詢格式：格式：SELECT ALL | DISTINCT FROM WHERE GROUP BY , HA

15、VING ORDER BY ASC|DESC, 功能：對查詢結(jié)果進(jìn)行排序。功能：對查詢結(jié)果進(jìn)行排序。說明：說明： 可以是字段名，也可以是數(shù)字。數(shù)字是可以是字段名，也可以是數(shù)字。數(shù)字是表的列序號，第表的列序號，第1列為列為1，以此類推。，以此類推。 ASC表示排序方式為升序，表示排序方式為升序，DESC表示排序方式為降序表示排序方式為降序,默認(rèn)為升序。默認(rèn)為升序。 在排序的基礎(chǔ)上可以使用在排序的基礎(chǔ)上可以使用TOP N PERCENT子句限制輸子句限制輸出的記錄行數(shù)，其中出的記錄行數(shù)，其中N是數(shù)值型表達(dá)式，取值范圍是數(shù)值型表達(dá)式，取值范圍132767，表示顯示前，表示顯示前N個記錄；含個記錄；含

16、PERCENT選項時，表示顯示前選項時，表示顯示前面百分之面百分之N個記錄，個記錄，N的取值范圍的取值范圍1100。例例4.15 將成績表的信息按學(xué)生學(xué)號升序，課程號降序排列。將成績表的信息按學(xué)生學(xué)號升序，課程號降序排列。 SELECT * FROM 成績表成績表; ORDER BY 學(xué)號學(xué)號,課程號課程號 DESC例例4.16 查詢成績表中選修查詢成績表中選修004課程成績最高的前三位同學(xué)。課程成績最高的前三位同學(xué)。 SELECT TOP 3 * FROM 成績表成績表; WHERE 課程號課程號=004 ORDER BY 成績成績 DESC4.2.6 排序查詢排序查詢4.2.7 內(nèi)連接查詢

17、內(nèi)連接查詢l在一個查詢語句中同時涉及到兩個或兩個以上的表時，這在一個查詢語句中同時涉及到兩個或兩個以上的表時，這種查詢稱為連接查詢（也稱為多表查詢）。在多表之間查詢種查詢稱為連接查詢（也稱為多表查詢）。在多表之間查詢必須處理表與表之間的連接關(guān)系。必須處理表與表之間的連接關(guān)系。l表的連接分為：表的連接分為：內(nèi)連接和外連接內(nèi)連接和外連接。l內(nèi)連接查詢格式有兩種：內(nèi)連接查詢格式有兩種：格式格式1：SELECT FROM , WHERE AND格式格式2：SELECT FROM INNER JOIN ON WHERE 例例4.17 查詢選修查詢選修004課程的學(xué)生姓名、課程號和成績信息課程的學(xué)生姓名、

18、課程號和成績信息分析：分析：姓名可以從姓名可以從“學(xué)生基本資料表學(xué)生基本資料表”中查得，課程號和中查得，課程號和成績只能從成績只能從“成績表成績表”中查得，因此本例涉及中查得，因此本例涉及“學(xué)生基本學(xué)生基本資料表資料表”和和“成績表成績表”兩張表，它們的公共字段是兩張表，它們的公共字段是“學(xué)號學(xué)號”。方法方法1：SELECT 姓名姓名,課程號課程號,成績成績; FROM 學(xué)生基本資料表學(xué)生基本資料表,成績表成績表; WHERE 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表.學(xué)號學(xué)號; AND 課程號課程號= 004 4.2.7 內(nèi)連接查詢內(nèi)連接查詢方法方法2：SELECT 姓名姓名,課

19、程號課程號,成績成績; FROM 學(xué)生基本資料表學(xué)生基本資料表 INNER JOIN 成績表成績表; ON 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表.學(xué)號學(xué)號; WHERE 課程號課程號= 004 4.2.7 內(nèi)連接查詢內(nèi)連接查詢例例4.18 查詢選修查詢選修C語言課程的學(xué)生學(xué)號、姓名、成績信息語言課程的學(xué)生學(xué)號、姓名、成績信息，并將結(jié)果按成績降序排列。，并將結(jié)果按成績降序排列。方法方法1：SELECT 成績表成績表.學(xué)號學(xué)號,姓名姓名,成績成績; FROM 學(xué)生基本資料表學(xué)生基本資料表,成績表成績表,課程表課程表; WHERE 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表

20、.學(xué)號學(xué)號; AND 成績表成績表.課程號課程號=課程表課程表.課程號課程號; AND 課程名課程名=C語言語言 ; ORDER BY 成績成績 DESC4.2.7 內(nèi)連接查詢內(nèi)連接查詢方法方法2：SELECT 成績表成績表.學(xué)號學(xué)號, 姓名姓名, 成績表成績表.成績成績;FROM 學(xué)生基本資料表學(xué)生基本資料表 INNER JOIN 成績表成績表;INNER JOIN 課程表課程表 ON 成績表成績表.課程號課程號=課程表課程表.課程號課程號; ON 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表.學(xué)號學(xué)號;WHERE 課程名課程名=C語言語言 ;ORDER BY 成績表成績表.成績成績

21、 DESC4.2.7 內(nèi)連接查詢內(nèi)連接查詢例例4.19 查詢選修了課程的學(xué)生姓名和平均成績信息，并查詢選修了課程的學(xué)生姓名和平均成績信息，并將結(jié)果按平均成績升序排列。將結(jié)果按平均成績升序排列。方法方法1：SELECT 姓名姓名,AVG(成績成績) AS 平均成績平均成績; FROM 學(xué)生基本資料表學(xué)生基本資料表,成績表成績表; WHERE 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表.學(xué)號學(xué)號; GROUP BY 姓名姓名 ORDER BY 平均成績平均成績方法方法2：SELECT 姓名姓名,AVG(成績成績) AS 平均成績平均成績; FROM 學(xué)生基本資料表學(xué)生基本資料表 INNE

22、R JOIN 成績表成績表; ON 學(xué)生基本資料表學(xué)生基本資料表.學(xué)號學(xué)號=成績表成績表.學(xué)號學(xué)號; GROUP BY 姓名姓名 ORDER BY 24.2.7 內(nèi)連接查詢內(nèi)連接查詢1. 查詢的設(shè)計步驟查詢的設(shè)計步驟 啟動啟動“查詢設(shè)計器查詢設(shè)計器”創(chuàng)建查詢。創(chuàng)建查詢。 在在“查詢設(shè)計器查詢設(shè)計器”窗口中添加表。窗口中添加表。 選擇查詢結(jié)果中需要的字段。選擇查詢結(jié)果中需要的字段。 設(shè)置查詢記錄的條件。設(shè)置查詢記錄的條件。 設(shè)置排序及分組條件來組織查詢結(jié)果。設(shè)置排序及分組條件來組織查詢結(jié)果。 選擇查詢輸出類別選擇查詢輸出類別,可以是報表、表文件、瀏覽窗口等。可以是報表、表文件、瀏覽窗口等。 運行

23、查詢。運行查詢。 保存查詢，保存查詢，查詢文件的擴展名為查詢文件的擴展名為.QPR。4.3 用查詢設(shè)計器建立查詢用查詢設(shè)計器建立查詢常用的啟動查詢設(shè)計器的方法有兩種：常用的啟動查詢設(shè)計器的方法有兩種： 選擇選擇“文件文件|新建新建”菜單項，或單擊常用工具欄上的菜單項，或單擊常用工具欄上的“新新建建”按鈕，打開按鈕，打開“新建新建”對話框，然后選擇對話框，然后選擇“查詢查詢”單單選按鈕選按鈕并單擊并單擊“新建文件新建文件”按鈕。按鈕。 用用CREATE QUERY命令打開查詢設(shè)計器建立查詢。命令打開查詢設(shè)計器建立查詢。例例4.20 查詢選修大學(xué)英語課程成績在查詢選修大學(xué)英語課程成績在8590分之

24、間的學(xué)分之間的學(xué)生，查詢結(jié)果包括姓名、性別、成績和年齡生，查詢結(jié)果包括姓名、性別、成績和年齡4個字段，并個字段，并按成績降序排列。按成績降序排列。2. 查詢設(shè)計器查詢設(shè)計器l 查詢的運行查詢的運行方法：方法： 在系統(tǒng)菜單中，選擇在系統(tǒng)菜單中，選擇“查詢查詢”| “運行查詢運行查詢”命令。命令。 在在“查詢設(shè)計器查詢設(shè)計器”窗口中單擊鼠標(biāo)右鍵，選擇快捷窗口中單擊鼠標(biāo)右鍵，選擇快捷菜單中的菜單中的“運行查詢運行查詢”命令。命令。 在命令窗口中鍵入在命令窗口中鍵入 DO l 查詢的修改查詢的修改方法：方法： 選擇選擇“文件文件”|“打開打開”命令，在命令，在“打開打開”對話框中，對話框中，選選擇所要

25、修改的查詢文件，單擊擇所要修改的查詢文件，單擊“確定確定”按鈕。按鈕。 在命令窗口中鍵入在命令窗口中鍵入 MODIFY QUERY 3. 查詢的運行與修改查詢的運行與修改1. 視圖的概念視圖的概念視圖是一個從基本表中導(dǎo)出的邏輯虛表，視圖沒有自己的數(shù)視圖是一個從基本表中導(dǎo)出的邏輯虛表，視圖沒有自己的數(shù)據(jù)實體。視圖中的數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此據(jù)實體。視圖中的數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個虛表。視圖不能獨立存在，視圖從屬于數(shù)據(jù)庫視圖是一個虛表。視圖不能獨立存在，視圖從屬于數(shù)據(jù)庫。2. 視圖與查詢的主要區(qū)別視圖與查詢的主要區(qū)別l 功能不同：功能不同：視圖可以更新字段內(nèi)容并返

26、回源表，而查詢文視圖可以更新字段內(nèi)容并返回源表，而查詢文件中的記錄數(shù)據(jù)不能被修改。件中的記錄數(shù)據(jù)不能被修改。l 從屬不同：從屬不同：視圖不是一個獨立的文件而從屬于某一個數(shù)據(jù)視圖不是一個獨立的文件而從屬于某一個數(shù)據(jù)庫。查詢是一個獨立的文件，它不從屬于某一個數(shù)據(jù)庫。庫。查詢是一個獨立的文件，它不從屬于某一個數(shù)據(jù)庫。4.4 用視圖設(shè)計器創(chuàng)建視圖用視圖設(shè)計器創(chuàng)建視圖l 輸出去向不同：輸出去向不同：視圖只能輸出到表中，而查詢可以選擇視圖只能輸出到表中，而查詢可以選擇多種去向，如表、圖表、報表、標(biāo)簽、窗口等形式。多種去向，如表、圖表、報表、標(biāo)簽、窗口等形式。l 使用方式不同：使用方式不同：視圖只有所屬的數(shù)據(jù)庫被打開時，才能視圖只有所屬的數(shù)據(jù)庫被打開時，才能使用。而查詢文件可在命令窗口中執(zhí)行。使用。而查詢文件可在命令窗口中執(zhí)行。l 訪問范圍不同：訪問范圍不同：視圖可以訪問本地數(shù)據(jù)源和遠(yuǎn)程數(shù)據(jù)源，視圖可以訪問本地數(shù)據(jù)源和遠(yuǎn)程數(shù)據(jù)源，查詢只能訪問本地數(shù)據(jù)源。查詢只能訪問本地數(shù)據(jù)源。2. 視圖與查詢的主要區(qū)別視圖與查詢的主要區(qū)別啟動啟動“視圖設(shè)計器