-SQL結(jié)構(gòu)化查詢語言

1、第5章SQL結(jié)構(gòu)化查詢語言5.1 SQL語言簡介SQL（StructuredQueryLanguage結(jié)構(gòu)化查詢語言）語言的核心是數(shù)據(jù)查詢，在標(biāo)準(zhǔn)化過程中不斷成為通用的數(shù)據(jù)庫語言。5.1.1 SQL語言特點(diǎn)（1）高度集成化：SQL語言在數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制方面都有規(guī)范的格式?？梢元?dú)立完成數(shù)據(jù)庫管理的各項(xiàng)工作。（2）非過程化語言：它不要求用戶指定對數(shù)據(jù)的存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式，只需告訴要做什么，系統(tǒng)就可以自動(dòng)完成。（3）集合操作：SQL語言的的操作對象和操作結(jié)果都是記錄集合，這種集合特性允許一條SQL語句的輸出作為另一條SQL語句的輸入，SQL語句可以嵌套

2、。（4）簡單易學(xué)：SQL語言語句不多，語言接近英語自然語言。在多數(shù)情況下，在其他語言中需要一大段程序?qū)崿F(xiàn)的功能只需要一個(gè)SQL語句就可以達(dá)到目的。（5）用法靈活：SQL語言可以在數(shù)據(jù)庫管理中直接應(yīng)用，可以嵌入其它高級程序設(shè)計(jì)語言中，完成對數(shù)據(jù)庫的操作。5.1.2 SQL語句組成SQL語言包含4個(gè)部分：數(shù)據(jù)定義語句（DDL）:CREATE（創(chuàng)建）、DROP（撤掉）、ALTER（更改）。數(shù)據(jù)操彳語句（DML）:INSERT（插入）、UPDATE（更新）、DELETE（刪除）。數(shù)據(jù)查詢語句（DQL）:SELECT（選擇）。數(shù)據(jù)控制語句（DCL）:GRANT（授予）、REVOKE（吊銷）、COMMIT

3、（提交）、ROLLBACK回滾）本章主要講ACCESS2003中所支持的SQL語句。5.1.3 SQL語句在ACCES新的使用方法使用SQL語句的步驟如下：打開數(shù)據(jù)庫窗口，進(jìn)入查詢設(shè)計(jì)器。關(guān)閉顯示表窗口，執(zhí)行菜單"查詢|SQL特定查詢|數(shù)據(jù)定義”命令。在打開的查詢設(shè)計(jì)編輯窗口中，輸入SQL語句。單擊工具欄上的執(zhí)行按鈕，可以直接執(zhí)行查詢。保存查詢，單擊工具欄上的保存按鈕，在彈出的對話框中命名查詢。5.2 SQL數(shù)據(jù)查詢語句(1) .1SELECT語句語法完整的SQL查詢語句常用的語法格式如下：SELECTALL|DISTINCT|TOP<n><*|表名.*|表名.字段

4、1AS列標(biāo)題1,表名.字段2AS列標(biāo)題2,>INTO<新表名>FROM裱列或者表之間的聯(lián)接關(guān)系>WHERE番詢條件>GROUPBY<分組項(xiàng)>HAVING<分組篩選條件>ORDERBY排序項(xiàng)>ASC|DESC,其中：(1)語句中的符號“口”表示可選項(xiàng)，“|”表示在多項(xiàng)選一，“<>”表示必選項(xiàng)。(2) ALL|DISTINCT|TOP<n>限定查詢的記錄范圍，默認(rèn)ALL,DISTINCT是無重復(fù)，TOP是前n個(gè)。(3) *代表全部字段，多個(gè)表時(shí)要有表名前綴。(4) AS可用于計(jì)算字段的列標(biāo)題，也可以為原字段另起列標(biāo)

5、題。(5) FROM為要查詢的數(shù)據(jù)源，數(shù)據(jù)源可以是表也可以是查詢，多個(gè)表時(shí)須有聯(lián)接方式。(6) WHER豉置查詢的條件。(7) GROUPBY設(shè)置分組依據(jù)，HAVING對分組進(jìn)行篩選。(8) ORDERBY設(shè)置排序依據(jù)。(9) INTO設(shè)置查詢?nèi)ハ虮?，若無，則顯示查詢數(shù)據(jù)表。SELEC延詢語句可以劃分為幾個(gè)組成部分：查詢項(xiàng)、查詢?nèi)ハ颉?shù)據(jù)源、查詢條件、查詢分組和查詢排序。5.2.2SQL常用聚合函數(shù)在SQL查詢中，查詢項(xiàng)為分組統(tǒng)計(jì)的需要使用聚合函數(shù)，常用的聚合函數(shù)如表。函數(shù)功能Avg(expr)數(shù)值平均值Sum(expr)數(shù)值求和Count(expr)統(tǒng)計(jì)記錄數(shù)First(expr)、Last

6、(expr)記錄集中第一個(gè)、最后-個(gè)記錄指定字段的數(shù)值Min(expr)、Max(expr)記錄集中指定列最小、最大值(10) 3查詢中的查詢項(xiàng)查詢項(xiàng)也稱為列標(biāo)題，一般是數(shù)據(jù)源中的字段名、也可以是計(jì)算統(tǒng)計(jì)表達(dá)式。每個(gè)查詢項(xiàng)可以通過as起別名，成為查詢結(jié)果的列標(biāo)題?！纠?.1查詢教師表的全部信息。分析：教師表的全部信息包含所有字段和所有的記錄兩個(gè)含義，所有字段的數(shù)據(jù)項(xiàng)可以用“*”代替，所有記錄則不需設(shè)置條件。Select*from教師表【例5.2從“學(xué)生成績查詢”中，查詢學(xué)號、姓名、課程名、考試成績。分析：該查詢的數(shù)據(jù)源為查詢，查詢項(xiàng)逐個(gè)列出，查詢項(xiàng)之間用英文逗號分割。Select學(xué)號，姓名，課

7、程名，考試成績from學(xué)生成績查詢【例5.3查詢學(xué)生的總評成績，查詢項(xiàng)有學(xué)號、課程號、平時(shí)成績、考試成績和總評。分析：查詢要求除了成績表中的所有字段，添加了總評計(jì)算項(xiàng)，總評的計(jì)算表達(dá)式通過AS給出列標(biāo)題。Select*,平時(shí)成績*0.2+考試成績*0.8as總評from成績表【例5.4查詢教師的代課信息，查詢項(xiàng)有課程號、課程名、教師代碼、教師姓名和職稱。分析：查詢項(xiàng)來源兩個(gè)表，當(dāng)兩個(gè)表中有相同的字段名時(shí)，必須通過前綴指明那個(gè)表，表名與字段名之間用英文句點(diǎn)標(biāo)示（語法中兩個(gè)表之間的聯(lián)接表達(dá)，當(dāng)沒有其他條件時(shí)，可以在where子句中用關(guān)聯(lián)字段相等的方法）。Select課程號，課程名，教師表.教師代碼

8、，姓名，職稱from課程表，教師表where課程表.教師彳碼=教師表.教師代碼(11) 4查詢中的條件查詢條件是在where子句中表示的，條件表達(dá)式的結(jié)果為邏輯值?！纠?.5查詢學(xué)生表中有貸款學(xué)生的學(xué)號、姓名、系部。分析：“貸款否”字段本身為邏輯值，條件表達(dá)式可以直接用字段名（如果是沒有貸款的學(xué)生，則在“貸款否”前，加上NOT）。Select學(xué)號，姓名，系部，貸款否from學(xué)生表where貸款否【例5.6從“學(xué)生成績查詢”中查詢考試成績不及格的學(xué)生信息。分析：條件表達(dá)式是一個(gè)簡單的關(guān)系運(yùn)算。Select*from學(xué)生成績查詢where考試成績60【例5.7查詢課程表中，含有“技術(shù)”二字的課程名

9、。分析：查詢條件需要進(jìn)行部分匹配，可以使用Like關(guān)系運(yùn)算符和統(tǒng)配符。Select課程名from課程表where課程名like"*技術(shù)*”(12) 4查詢中的多表聯(lián)接多個(gè)表之間的聯(lián)接是在from子句中實(shí)現(xiàn)的，聯(lián)接方式有左聯(lián)接、右聯(lián)接和內(nèi)聯(lián)接。Access默認(rèn)為內(nèi)聯(lián)接，而且是有效的聯(lián)接?！纠?.8】查詢“公共英語”課程學(xué)生的學(xué)號、課程名、平時(shí)成績和考試成績。分析：查詢需要課程表和成績表兩個(gè)表中的字段，兩個(gè)表之間為內(nèi)聯(lián)接，查詢條件為“公共英語”課程。Select學(xué)號，課程名，平時(shí)成績，考試成績from課程表innerjoin成績表on課程表.課程號=成績表.課程號where課程名=“公共

10、英語”【例5.9】查詢“網(wǎng)頁制作技術(shù)”這門課程的成績信息，提供學(xué)號、姓名、課程名、平時(shí)成績、考試成績和總評。分析：查詢需要學(xué)生表、課程表和成績表中的字段，需建立3個(gè)表之間的內(nèi)聯(lián)接關(guān)系。Select學(xué)生表.學(xué)號，姓名，課程名，平時(shí)成績，考試成績，平時(shí)成績*0.2+考試成績*0.8as總評from學(xué)生表innerjoin（成績表innerjoin課程表on成績表.課程號=課程表.課程號）on學(xué)生表.學(xué)號=成績表.學(xué)號where課程名="網(wǎng)頁制作技術(shù)”(13) 4查詢中的統(tǒng)計(jì)和排序1 .查詢統(tǒng)計(jì)查詢統(tǒng)計(jì)時(shí)通常使用GROUBY子句進(jìn)行分組，使用SQL聚合函數(shù)進(jìn)行統(tǒng)計(jì)，對查詢統(tǒng)計(jì)進(jìn)行篩選使用H

11、AVING?句，HAVING必須與GROUPB譜已合，不能單獨(dú)使用。分組統(tǒng)計(jì)查詢中查詢項(xiàng)只需分組字段和統(tǒng)計(jì)的字段?！纠?.10】查詢各學(xué)期學(xué)生平均考試成績。分析：根據(jù)題意，查詢分組有開課學(xué)期和課程名稱，統(tǒng)計(jì)為對“考試成績”求平均，沒有篩選要求。SELECT學(xué)號，開課學(xué)期，avg（考試成績）as學(xué)期平均考試分from課程表innerjoin成績表on課程表.課程號=成績表.課程號groupby學(xué)號，開課學(xué)期【例5.11】對上例5.10的查詢結(jié)果，篩選平均分大于80的記錄。分析：在上例查詢的基礎(chǔ)上，添加分組篩選即可。SELECT學(xué)號，開課學(xué)期，avg（考試成績）AS學(xué)期平均考試分FROM課程表IN

12、NERJOIN成績表ON課程表.課程號=成績表.課程號GROUPBY學(xué)號，開課學(xué)期HAVINGavg（考試成績）80【例5.12】統(tǒng)計(jì)每個(gè)學(xué)生選修的課程門數(shù)。分析：根據(jù)題意，查詢數(shù)據(jù)源為成績表，分組字段為學(xué)號，選修課程門數(shù)也就是該學(xué)號對應(yīng)的記錄數(shù)，記錄數(shù)統(tǒng)計(jì)方法是count（）聚合函數(shù)。SELECT學(xué)號,count（*）as選修門數(shù)FROM成績表GROUPBY學(xué)號2 .查詢排序查詢排序使用ORDEFBY子句實(shí)現(xiàn)。排序通常與限定詞TOPn連用，默認(rèn)升序。使用DESC1示降序，ASC表示升序。【例5.13查詢“ACCES做據(jù)庫程序設(shè)計(jì)”課程總評成績及格以上的記錄，按總評升序顯示。分析：根據(jù)題意，選

13、擇已經(jīng)建立的查詢“學(xué)生成績總評查詢”作為數(shù)據(jù)源；查詢要求的課程名和總評及格為邏輯“與”關(guān)系；顯示按總評升序排列。Select*from學(xué)生成績總評查詢where課程名="ACCES做據(jù)庫程序設(shè)計(jì)”and總評60orderby總評【例5.14】輸入學(xué)生的學(xué)號，查詢該學(xué)生所有選修課程的考試成績，并按照考試成績由大到小選取前3門課程的記錄。分析：查詢的數(shù)據(jù)源為成績表；查詢要求的輸入學(xué)號為可變參數(shù)；對查詢結(jié)果的3條記錄限制用TOP查詢結(jié)果按考試成績降序排列。Selecttop3*from成績表where學(xué)號=請輸入學(xué)號orderby考試成績desc3 .2.5聯(lián)合查詢和嵌套查詢1 .聯(lián)合查詢

14、在SQL查詢中，可以把兩個(gè)SELECT吾句通過聯(lián)合（UNION）運(yùn)算進(jìn)行合并，稱為聯(lián)合查詢。聯(lián)合查詢的查詢項(xiàng)名稱必須相同，數(shù)據(jù)類型一致，可以是不同的數(shù)據(jù)源?！纠?.15】查詢成績表中考試成績=90和60的記錄。分析：該查詢可以通過條件的邏輯運(yùn)算實(shí)現(xiàn)，也可以通過聯(lián)合查詢實(shí)現(xiàn)。Select*from成績表where考試成績>=90unionselect*from成績表where考試成績<602 .嵌套查詢嵌套查詢是在一個(gè)SELEC俏詢的WHER子句中嵌入了另一個(gè)SELEC詹詢。嵌套查詢通常用于查詢條件字段不在查詢的數(shù)據(jù)源中。【例5.16】查詢法律系學(xué)生在成績表中的記錄。分析：查詢要求的

15、信息僅限于成績表中，系部信息在學(xué)生表中，它們之間通過學(xué)號關(guān)聯(lián)，通過對學(xué)號的進(jìn)一步嵌套選擇來實(shí)現(xiàn)查詢的目標(biāo)。select*from成績表where學(xué)號in(select學(xué)號from學(xué)生表where系部="法律")5.2.6查詢?nèi)ハ蛟赟QL中，執(zhí)行查詢默認(rèn)去向是數(shù)據(jù)表的形式顯示結(jié)果，查詢隨著關(guān)閉查詢顯示窗口而結(jié)束。若需要將查詢結(jié)果保留存檔，或繼續(xù)在其它應(yīng)用中使用，可以通過INTO子句使查詢?nèi)ハ驗(yàn)閿?shù)據(jù)表。【例5.17】將第一學(xué)期學(xué)生的學(xué)號、姓名、性別、開課學(xué)期、課程名稱、平時(shí)成績、考試成績和總評查詢結(jié)果保存到“第一學(xué)期成績表”中。分析：根據(jù)題目要求，查詢?nèi)ハ驗(yàn)楸恚O(shè)計(jì)查詢時(shí)，在查

16、詢項(xiàng)后設(shè)置INTO子句。SELECT學(xué)生表.學(xué)號，姓名，性別，開課學(xué)期，課程名，平時(shí)成績，考試成績，平時(shí)成績*0.2+考試成績*0.8AS總評INTO第一學(xué)期成績表FROM學(xué)生表INNERJOIN(課程表INNERJOIN成績表ON課程表.課程號=成績表.課程號)ON學(xué)生表.學(xué)號=成績表.學(xué)號WHEREFF課學(xué)期=15.3SQL數(shù)據(jù)定義SQL語言的重要功能之一就是實(shí)現(xiàn)數(shù)據(jù)的定義。數(shù)據(jù)定義語句主要有定義數(shù)據(jù)表、定義索引和修改表結(jié)構(gòu)等語句。數(shù)據(jù)定義語句的編輯執(zhí)行也是在查詢對象中進(jìn)行的，操作方法與查詢語句相同。5.3.1 定義數(shù)據(jù)表SQL定義表的基本語法格式是：CREATETABLE裱名>(&

17、lt;字段1><數(shù)據(jù)類型1>(n)NOTNULLPRIMARYKEY|UNIQUE,<字段2><數(shù)據(jù)類型2>(n)NOTNULLPRIMARYKEY|UNIQUE,)其中：(1)數(shù)據(jù)類型的表示方法是：文本(text)、字節(jié)(byte)、長整型(integer)、單精度(single)、雙精度(float)、貨幣(currency)、備注(memo、日期時(shí)間(date)、邏輯(logical)、OLE對象(OLEobject)等。(2) PRIMARYKEY定義字段為主鍵，UNIQUE定義字段為唯一索引?！纠?.18在圖書管理數(shù)據(jù)庫中，定義圖書信息的表“

18、Books”。CREATETABLEBooks(圖書編號text(8)primarykey,書名text(16)notnull,作者text(4),出版社text(12),出版日期date,價(jià)格currency,內(nèi)容簡介memo)【例5.19在圖書管理數(shù)據(jù)庫中，定義讀者信息的表“ReadersCREATETABLEReaders(借書證號text(8)primarykey,姓名text(4)notnull,性別text(1),工作部門text(10),身份證號text(18),出生日期date,照片OLEobject)【例5.20在圖書管理數(shù)據(jù)庫中，定義借閱信息的表“BorrowsCREATE

19、TABLEBorrows(圖書編號text(8)referencesBooks(圖書編號)，借書證號text(8)referencesReaders(借書證號),借閱日期date,歸還日期date,primarykey(圖書編號，借書證號)圖書管理的三個(gè)表從關(guān)系上，Books和Readers為父表，Borrows為子表，應(yīng)先建立父表，后建立子表，子表中用references指定與主表之間的參照關(guān)系。5.3.2 定義表索引SQL定義索引的語法格式是：CREATEUNIQUEINDEX索引名稱ON表名(索引字段1DESC|ASC,索引字段2ASC|DESC,)WITHPRIMARYKEY建立索引可

20、以是單字段索引或多字段索引，索引的類型可以是：唯一索引(UNIQUE限定)，主鍵(WITHPRIMARYKEY限定)、普通索引?！纠?.21給Books表的“書名”建立一個(gè)降序排列的普通索引，索引的名稱為“書名”oCREATEINDEX書名ONBooks(書名DESC)5.3.3 修改表1 .添加字段ALTERTABLE表名ADD字段名數(shù)據(jù)類型(n)【例5.22給圖書管理數(shù)據(jù)庫的Readers表添加住址字段和電話字段。ALTERTABLEReadersADD住址text(20)ALTERTABLEReadersADD電話integer2 .刪除字段ALTERTABLE表名DROP字段名【例5.23把Readers表中的住址字段刪除。ALTERTABLEReadersDROP住址3 .修改字段數(shù)據(jù)類型ALTERTABLE表名ALTER字段名數(shù)據(jù)類型(n)【例5.24把Readers表中的電話字段數(shù)據(jù)類型改為文本型，11個(gè)字符大小。ALTERTABLEReadersALTER電話text(11)4 .刪除索引DROPINDEX索引名ON表名5 .刪除表DROPTABLE裱名>5.4SQL數(shù)據(jù)操縱數(shù)據(jù)操縱是對數(shù)據(jù)表記錄的操彳數(shù)據(jù)操縱的語句有INSERT（插入記錄），UPDATE更新字段數(shù)據(jù)），DELETE（刪除記錄）o數(shù)據(jù)操縱在Access的查詢對象中使用，操作方