第五章 查詢及視圖1.ppt

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

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

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

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

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

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

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

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

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

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

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

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

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

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