第五章 查詢及視圖1.ppt

1、查詢及視圖(1) 第五章,回顧,數(shù)據(jù)庫(kù)設(shè)計(jì)的基本原則 數(shù)據(jù)庫(kù)中的表之間的關(guān)系 數(shù)據(jù)庫(kù)操作包括哪些方面 自由表可以加入數(shù)據(jù)庫(kù)中嗎？ 數(shù)據(jù)庫(kù)的數(shù)據(jù)完整性包含哪些方面？ 如何同時(shí)訪問多個(gè)表？,本章內(nèi)容,SQL SELECT命令的語(yǔ)法和SQL SELECT命令的使用和SQL語(yǔ)言的其他重要命令 查詢和查詢?cè)O(shè)計(jì)器。 視圖和視圖設(shè)計(jì)器以及視圖和查詢的關(guān)系和區(qū)別。 SQL語(yǔ)言是計(jì)算機(jī)等級(jí)考試（二級(jí)）的重點(diǎn)內(nèi)容。,本節(jié)主要內(nèi)容：,SQL 簡(jiǎn)介 SQL SELEST 命令格式 SQL SELEST 命令使用 SQL 的操作功能 SQL 的定義功能,5.1 結(jié)構(gòu)化查詢語(yǔ)言SQL,SQL是英文Structured Q

2、uery Language（結(jié)構(gòu)化查詢語(yǔ)言）的縮寫 SQL語(yǔ)言有數(shù)據(jù)查詢，數(shù)據(jù)操縱，數(shù)據(jù)定義和數(shù)據(jù)控制功能。 SQL是1986年10月由美國(guó)國(guó)家標(biāo)準(zhǔn)研究所ANSI（American National Standards Institute）提出。1987年6月國(guó)際標(biāo)準(zhǔn)化組織ISO（International Standards Organization）正式將SQL定為關(guān)系數(shù)據(jù)庫(kù)的國(guó)際標(biāo)準(zhǔn) SQL語(yǔ)言是目前最流行數(shù)據(jù)庫(kù)查詢語(yǔ)言。,結(jié)構(gòu)化查詢語(yǔ)言SQL,5.1.1 SQL 介紹 SQL功能及命令,結(jié)構(gòu)化查詢語(yǔ)言SQL,5.1.2 SQL SELECT 命令格式及使用 SELECT ; FROM

3、; WHERE ; GROUP BY HAVING ; ORDER BY ASC/DESC , ASC/DESC,SELECT是命令動(dòng)詞，其后的是一個(gè)或多個(gè)字段名，表示用戶要查詢的數(shù)據(jù)所在字段，若是多個(gè)字段名，其間用逗號(hào)隔開。,FROM短語(yǔ)中的是一個(gè)或多個(gè)表文件名，表示數(shù)據(jù)源，若是多個(gè)表，其間用逗號(hào)隔開。,WHERE短語(yǔ)中的是關(guān)系表達(dá)式或邏輯表達(dá)式，表示查詢數(shù)據(jù)必須符合的條件，或稱為約束條件。實(shí)際上是對(duì)表中記錄的篩選。,GROUP BY短語(yǔ)中的表示分組查詢的分組依據(jù)，HAVING短語(yǔ)中的是對(duì)分組的篩選。,ORDER BY短語(yǔ)中的、是對(duì)查詢結(jié)果排序的依據(jù)。ASC表示升序，DESC表示降序，如果這

4、兩個(gè)參數(shù)都不用，默認(rèn)為升序。,結(jié)構(gòu)化查詢語(yǔ)言SQL,SELECT命令使用實(shí)例 (1)簡(jiǎn)單查詢 例 5.1 從學(xué)生表中找出黨員學(xué)生的姓名及其所在系。 分析：要找的數(shù)據(jù)在學(xué)生表的字段“姓名”和“系名”中，篩選條件是該生必須是黨員，這一條件可以用表達(dá)式“政治面貌=黨員表示，故可以寫出如下命令： SELECT 姓名,系名 FROM 學(xué)生 WHERE 政治面貌=黨員,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.2 從成績(jī)表中找出2006年1月11號(hào)考試的全部信息。 SELECT * FROM 成績(jī) WHERE 考試日期=2006/01/11 *是通配符，代表成績(jī)表中所有的字段。,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.3 從學(xué)

5、生表中檢索該學(xué)校有哪些系。 SELECT 系名 FROM 學(xué)生 SELECT DISTINCT 系名 FROM 學(xué)生,作用是去掉系名的重復(fù),結(jié)構(gòu)化查詢語(yǔ)言SQL,例5.4 從課程表中查找課時(shí)為60且學(xué)分為3的課程。 SELECT 課程名稱,課時(shí),學(xué)分 FROM 課程； WHERE 課時(shí)=60 AND 學(xué)分=3,如果命令一行寫不下，可在第二行續(xù)寫，但第一行必須以分號(hào)結(jié)束,結(jié)構(gòu)化查詢語(yǔ)言SQL,(2)簡(jiǎn)單聯(lián)接查詢 簡(jiǎn)單聯(lián)接查詢是在多個(gè)表中進(jìn)行查詢，與在單個(gè)表中查詢相比較，它有自己的特點(diǎn)。 例 5.5 查看電子商務(wù)的考試成績(jī)。 分析：各科目的考試成績(jī)?cè)诔煽?jī)表中，課程名稱在課程表中，故此查詢需在成績(jī).

6、dbf和課程.dbf兩個(gè)表中進(jìn)行。 SELECT 學(xué)號(hào),課程名稱,成績(jī) FROM 成績(jī),課程 WHERE 課程名稱=“電子商務(wù)” AND 成績(jī).課程編號(hào)=課程.課程編號(hào) 注意： “成績(jī).課程編號(hào)=課程.課程編號(hào)” 是連接條件； “課程編號(hào)”在成績(jī)表和課程表中均有，因此在SELECT命令中使用它時(shí)必須在其前冠以表名，并以小數(shù)點(diǎn)相隔，指明它是哪一個(gè)表的“課程編號(hào)”。,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.6 列出高等數(shù)學(xué)不及格的學(xué)生的姓名及他們?cè)摽频目荚嚦煽?jī)。 分析：學(xué)生姓名在學(xué)生表中，考試的課程名在課程表中，考試的成績(jī)?cè)诔煽?jī)表中，因此該查詢的數(shù)據(jù)源為3個(gè)表。不及格用表達(dá)式“成績(jī)60”表示。 SELECT

7、 姓名,課程名稱,成績(jī) FROM 學(xué)生,成績(jī),課程 WHERE 課程名稱=高等數(shù)學(xué) AND 成績(jī)60 AND (學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào) AND 成績(jī).課程編號(hào)=課程.課程編號(hào)),結(jié)構(gòu)化查詢語(yǔ)言SQL,(3) 對(duì)查詢結(jié)果排序 如果要對(duì)查詢結(jié)果排序，可用SELECT命令的ORDER BY短語(yǔ)。 格式如下： ORDER BY ASC | DESC， ASC | DESC 其中ASC表示升序，DESC表示降序，如果沒有用ASC和DESC，默認(rèn)為升序，格式表明可以按多個(gè)字段排序。,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.7 查詢各門課的學(xué)時(shí)數(shù)及學(xué)分，結(jié)果按課程名排序。 分析：這些數(shù)據(jù)在“課程”表的“課程名稱”，“

8、課時(shí)”，“學(xué)分”三個(gè)字段中。 SELECT 課程名稱,課時(shí),學(xué)分； FROM 課程 ORDER BY ； 課程名稱,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.8 查詢每個(gè)同學(xué)的數(shù)學(xué)課成績(jī)，結(jié)果按成績(jī)升序排列。 分析：考試成績(jī)?cè)诔煽?jī)表中，課程名在課程表中，學(xué)生姓名在學(xué)生表中，故此查詢要在三個(gè)表中完成，如前所述在多個(gè)表查詢需在表中按同名字段建立聯(lián)接條件。 SELECT 姓名,課程名稱,成績(jī)； FROM 學(xué)生,成績(jī),課程 ； WHERE 課程名稱=“高等數(shù)學(xué)”； AND (學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào)； AND 成績(jī).課程編號(hào)=課程.課程編號(hào))； ORDER BY 成績(jī),結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.9 查詢學(xué)生的出

9、生日期及所在班級(jí)，查詢結(jié)果按班級(jí)的升序排列，同一班級(jí)按年齡升序排列，列順序?yàn)樾彰?、班?jí)、出生日期。 分析：要查數(shù)據(jù)均在學(xué)生表中，按年齡升序即按出生日期降序。 SELECT 姓名,班級(jí),出生日期 FROM 學(xué)生 ORDER BY 班級(jí),出生日期 DESC,結(jié)構(gòu)化查詢語(yǔ)言SQL,（4）計(jì)算查詢 SQL SELECT命令不但能對(duì)表進(jìn)行一般的數(shù)據(jù)查詢，還能通過計(jì)算方式在表中查找數(shù)據(jù)，當(dāng)然這只能是一些簡(jiǎn)單的計(jì)算，比如：查找最大值、最小值、平均值，對(duì)數(shù)值數(shù)據(jù)求和，以及統(tǒng)計(jì)記錄個(gè)數(shù)，這些計(jì)算查詢是通過函數(shù)實(shí)現(xiàn)的： COUNT( )計(jì)數(shù) SUM( )求和 MAX( )最大值 MIN()最小值 AVG( )平均

10、值,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.10 統(tǒng)計(jì)男生人數(shù)。 分析：統(tǒng)計(jì)記錄個(gè)數(shù)用COUNT( )函數(shù)，該查詢?cè)趯W(xué)生表中進(jìn)行。 SELECT COUNT(*) AS 男生人數(shù) FROM 學(xué)生 WHERE 性別=男“ 注意： 函數(shù)中的參數(shù)一定要有，本例中用*。實(shí)際上，用學(xué)生表中任意一個(gè)字段名均可，用*簡(jiǎn)單一些而已。 “AS 男生人數(shù)”短語(yǔ)生成查詢數(shù)據(jù)的列標(biāo)題。省去也可，查詢的列標(biāo)題為Cnt。 如果不用WHERE短語(yǔ)，則統(tǒng)計(jì)出表中所有學(xué)生的人數(shù)。,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.11 查看學(xué)生李林各科的成績(jī)的總和。 分析：對(duì)成績(jī)求和用SUM()函數(shù)，其參數(shù)必須是數(shù)值型。 SELECT 姓名,SUM(成績(jī))

11、 FROM 學(xué)生,成績(jī) WHERE 姓名=“李林“ AND 學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào) 思考： 1、如何將字段名“Sum_成績(jī)”改為總成績(jī) 2、如何顯示每個(gè)學(xué)生的總成績(jī) SELECT 姓名,SUM(成績(jī)) as 總成績(jī) FROM 學(xué)生,成績(jī) WHERE 學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào) group by 學(xué)生.學(xué)號(hào),結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.12 找出高等數(shù)學(xué)的最高分。 SELECT 課程名稱,MAX(成績(jī)) FROM 成績(jī),課程 WHERE 課程名稱=高等數(shù)學(xué) AND 成績(jī).課程編號(hào)=課程.課程編號(hào) 由于有限定條件：課程名稱=“高等數(shù)學(xué)”，故MAX(成績(jī))不是整個(gè)表的成績(jī)中的最大值，而是“高等數(shù)學(xué)”成

12、績(jī)中的最大值。,結(jié)構(gòu)化查詢語(yǔ)言SQL,（5）分組查詢 實(shí)際查詢中，經(jīng)常需要對(duì)記錄分成不同組，在組中進(jìn)行數(shù)據(jù)檢索，稱為分組查詢，分組查詢用GROUP BY短語(yǔ)實(shí)現(xiàn)，其格式如下： GROUP BYHAVING 是分組所依據(jù)的字段； HAVING短語(yǔ)是對(duì)分組的篩選。,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.13 求每個(gè)學(xué)生各門功課的平均成績(jī)，并按降序排列。 分析：學(xué)生的各科成績(jī)?cè)诔煽?jī)表中且以學(xué)號(hào)標(biāo)識(shí)，相同的學(xué)號(hào)對(duì)應(yīng)同一個(gè)學(xué)生，故應(yīng)以學(xué)號(hào)分組，每一組即對(duì)應(yīng)一個(gè)學(xué)生，求平均成績(jī)可用AVG()函數(shù)。 SELECT 姓名,AVG(成績(jī)) AS 平均成績(jī) FROM 學(xué)生,成績(jī) WHERE 學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào) GRO

13、UP BY 成績(jī).學(xué)號(hào) ORDER BY 平均成績(jī) DESC 注意：如果SELECT命令中使用了GROUP BY短語(yǔ)，則命令中出現(xiàn)的計(jì)算函數(shù)均是按分組的計(jì)算，而不是對(duì)整個(gè)表的計(jì)算，如本例中的AVG(成績(jī))是每一組的平均成績(jī)，而不是整個(gè)表的平均成績(jī)。,也可以用學(xué)生.學(xué)號(hào)，但不可用學(xué)號(hào)，因?yàn)閷W(xué)號(hào)不唯一,結(jié)構(gòu)化查詢語(yǔ)言SQL,例 5.14 求高等數(shù)學(xué)的最高分、最低分和平均成績(jī)。 SELECT 課程名稱,MAX(成績(jī)) AS 最高分,MIN(成績(jī)) AS 最低分,AVG(成績(jī)) AS 平均成績(jī) FROM 課程,成績(jī) WHERE 課程名稱=“高等數(shù)學(xué)”AND 成績(jī).課程編號(hào)=課程.課程編號(hào) GROUP

14、BY 成績(jī).課程編號(hào),結(jié)構(gòu)化查詢語(yǔ)言SQL,例5.15 找出不及格人數(shù)多于2人的課程（包括2人）各科成績(jī)?cè)诔煽?jī)表中，以課程編號(hào)標(biāo)識(shí)，故要按課程編號(hào)分組，在每一組中統(tǒng)計(jì)不及格人數(shù)，統(tǒng)計(jì)人數(shù)用COUNT()函數(shù)，課程名稱在課程表中，因此該查詢需在兩個(gè)表中進(jìn)行。 SELECT 課程名稱,COUNT(成績(jī)) AS 不及格人數(shù) FROM 課程,成績(jī) WHERE 成績(jī)=2,結(jié)構(gòu)化查詢語(yǔ)言SQL,（6）嵌套查詢 不做要求！ （7）兩個(gè)特殊運(yùn)算符 BETWEENAND和LIKE在SQL SELECT命令中表示某種運(yùn)算，它們使得命令更像自然語(yǔ)言。 BETWEENAND 表示 “在和之間” 例 5.20 找出課時(shí)在60 80之間的課程。 SELECT 課程名稱,課時(shí) FROM 課程 WHERE 課時(shí) BETWEEN 60 AND 80,結(jié)