數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告2013第九次課

同學(xué)們好！第九次課任課教師：湯俊

5.1查詢Query概述數(shù)據(jù)庫(kù)三大功能：數(shù)據(jù)定義、數(shù)據(jù)操作、數(shù)據(jù)控制定義功能包括對(duì)象的創(chuàng)建、修改、刪除操作功能包括對(duì)于數(shù)據(jù)庫(kù)中數(shù)據(jù)的增加、刪除、更新和查找控制實(shí)現(xiàn)完整性、安全性、效率以及并發(fā)管理等實(shí)現(xiàn)數(shù)據(jù)庫(kù)的功能需要使用數(shù)據(jù)庫(kù)語(yǔ)言。關(guān)系數(shù)據(jù)庫(kù)中使用的語(yǔ)言是結(jié)構(gòu)化查詢語(yǔ)言（StructureQueryLanguage），即SQL第5章查詢SQL(StructureQueryLanguage)是數(shù)據(jù)庫(kù)世界里的英語(yǔ)、普通話、國(guó)際歌SQL使用包括獨(dú)立使用以及嵌入高級(jí)語(yǔ)言使用(如論壇、淘寶鏈接)查詢作用表實(shí)現(xiàn)數(shù)據(jù)組織存儲(chǔ)，查詢實(shí)現(xiàn)數(shù)據(jù)庫(kù)操作、重新組織，查詢以表為基礎(chǔ)。查詢主要有兩種作用：（1）通過查詢，可以實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的各種操作。（2）可以將設(shè)計(jì)好的查詢命名保存，就得到查詢對(duì)象。查詢對(duì)象的外在視圖與表類似。查詢可以對(duì)數(shù)據(jù)庫(kù)重新組織，查詢對(duì)象可以將數(shù)據(jù)庫(kù)以不同的形式呈現(xiàn)在不同的用戶眼中。（例）查詢的意義查詢對(duì)象在邏輯上相當(dāng)于是一個(gè)表，與表有一樣的結(jié)構(gòu)。查詢也可以作為其他對(duì)象的數(shù)據(jù)源。(示范：查詢結(jié)果)查詢對(duì)象保存查詢定義，不保存查詢結(jié)果，不會(huì)浪費(fèi)存儲(chǔ)空間，查詢對(duì)象為“虛表”。在查詢對(duì)象界面選中查詢對(duì)象“打開”，就可以運(yùn)行查詢查看結(jié)果，避免了每次重新設(shè)計(jì)查詢的操作。同時(shí)，結(jié)果是一個(gè)動(dòng)態(tài)數(shù)據(jù)集，自動(dòng)與源表保持同步。(示范：查詢SQL視圖)查詢對(duì)象基于SQL，隱藏了數(shù)據(jù)庫(kù)復(fù)雜性(From子句)，可以為不同的應(yīng)用設(shè)計(jì)不同的查詢，因此查詢是用戶眼中的數(shù)據(jù)庫(kù)。查詢?cè)黾恿藨?yīng)用的靈活性，實(shí)現(xiàn)了數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)中外模式的功能。對(duì)查詢對(duì)象的操作要轉(zhuǎn)換為對(duì)表的操作。由于查詢的多樣性，因此，不是所有對(duì)查詢對(duì)象的操作都可以完成，一般來說，對(duì)查詢對(duì)象的SELECT操作都可以實(shí)現(xiàn)，但對(duì)查詢對(duì)象的INSERT、UPDATE、DELETE操作有很多限制。查詢?cè)黾恿藬?shù)據(jù)應(yīng)用的安全性。查詢操作Access中實(shí)現(xiàn)查詢或查詢對(duì)象的基本操作方法有兩種：（1）通過查詢?cè)O(shè)計(jì)視圖交互式設(shè)置（包括向?qū)В唬?）通過輸入SQL命令(適合筆試)在Access中這兩種操作的界面是集成在一起并可靈活切換5.2SQLSQL是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，集數(shù)據(jù)定義、數(shù)據(jù)操作和數(shù)據(jù)控制功能于一身SQL提出于1974年。IBM公司的SystemR中率先實(shí)現(xiàn)SQL。1986年10月美國(guó)國(guó)家標(biāo)準(zhǔn)局（ANSI）批準(zhǔn)將SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)，并發(fā)布了ANSISQL標(biāo)準(zhǔn)文本（SQL-86）。1987年，國(guó)際標(biāo)準(zhǔn)化組織（ISO）通過了這一標(biāo)準(zhǔn)。1989年發(fā)布SQL-89，1992年發(fā)布SQL-92（也稱SQL2）。1997年發(fā)布SQL3。各數(shù)據(jù)庫(kù)廠家推出的關(guān)系型DBMS都支持SQL，其基本內(nèi)容、命令和格式是一致的。掌握SQL對(duì)使用關(guān)系數(shù)據(jù)庫(kù)非常重要。SQL特點(diǎn)SQL定義和操作功能使用的命令動(dòng)詞只有：CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT查詢視圖SQL的基本工作方式是命令行方式。使用“SQL視圖”?！癝QL視圖”是一個(gè)文本編輯器窗口，用戶在這個(gè)窗口中可以完成：輸入、編輯SQL語(yǔ)句。運(yùn)行SQL語(yǔ)句并查看查詢結(jié)果。保存SQL語(yǔ)句為查詢對(duì)象。該工具一次只能編輯處理一條SQL語(yǔ)句。在“SQL視圖”和“設(shè)計(jì)視圖”之間轉(zhuǎn)換界面。SQL視圖(命令行)在這個(gè)窗口只能使用SQL命令語(yǔ)句。包括定義命令：CREATE、ALTER、DROP；查詢命令：SELECT；更新命令：INSERT、UPDATE、DELETE。SQL語(yǔ)句都以“；”作為結(jié)束標(biāo)志。在“SQL視圖”窗口中輸入命令后，可單擊工具欄中的運(yùn)行按鈕，或者選擇“查詢”菜單中“運(yùn)行”命令，SQL視圖界面就會(huì)變成查詢結(jié)果的顯示界面。單擊工具欄“存儲(chǔ)”按鈕，或“文件”菜單中“另存為”菜單項(xiàng)，命名保存查詢對(duì)象運(yùn)算與表達(dá)式運(yùn)算符運(yùn)算對(duì)象運(yùn)算符運(yùn)算與函數(shù)f(x)運(yùn)算對(duì)象常量輸入?yún)?shù)(變量x)字段算術(shù)運(yùn)算符+-*/^5^(1/2)MOD取余12mod5=2文本文本&(+)文本函數(shù)ASCCHRLTRIMTRIMLEFTRIGHTMID日期

+<日期>+n加天數(shù)<日期1>+<日期2>1899-12-31為1-<日期1>-<日期2>相隔天數(shù)比較結(jié)果為是否型(0/-1)<<=>>==<>BetweenandLikeP133列出所有不姓劉的所有學(xué)生；SELECTsnameFROMstudentWHEREsnameNOTLIKE'劉*';列出姓“沈”且全名為3個(gè)漢字的學(xué)生；SELECTsnameFROMstudentWHEREsnameLIKE'沈??'INEXISTS邏輯與AND或OR非NOT常用函數(shù)考前會(huì)列一個(gè)復(fù)習(xí)重點(diǎn)LEFTRIGHTMIDLENDATETIMEDAYNOWSTRVAL參數(shù)SELECTx-1;參數(shù)就是變量參數(shù)用方括號(hào)括起來表達(dá)式示例SELECT-3+5*20/4and“ABC”<“123”or-08-08#<date();SELECT查詢SQL查詢只有一條SELECT語(yǔ)句，功能強(qiáng)大，語(yǔ)法復(fù)雜。

SELECT[ALL|DISTINCT][TOP<數(shù)值>[PERCENT]]*|[[<別名.>]<輸出列>[AS<列名>]…][INTO<保存表名>]

FROM<表名>[INNER|LEFT|RIGHTJOIN<表名>][ON<連接條件>…]][WHERE<條件>[AND|OR<條件>...]][GROUPBY<分組項(xiàng)>[,...][HAVING<過濾條件>]][UNIONSELECT語(yǔ)句][ORDERBY<排序列>[ASC|DESC][,...]]說明命令中只有SELECT<輸出列>和FROM<數(shù)據(jù)源>是必選項(xiàng)。其他子句可選。SELECT子句輸出列：列名、*、函數(shù)（SUM，AVG，MAX，MIN，COUNT、FIRST、LAST、STDEV、VAR）、常量、表達(dá)式、DISTINCT、TOPn[PERCENT]、表名前綴、AS<新名>換名。INTO子句：INTO<新表>。保存查詢結(jié)果到某個(gè)表。FROM子句：?jiǎn)蝹€(gè)表；兩個(gè)表：內(nèi)連接：左表INNERJOIN右表ON條件外連接：左表LEFT|RIGHT[OUTER]JOIN右表ON條件笛卡爾積：左表，右表多個(gè)表在兩個(gè)表基礎(chǔ)上擴(kuò)展。用“AS”子句為表?yè)Q名。重名字段加表名前綴。WHERE子句：WHERE<邏輯表達(dá)式>SELECT其他子句“邏輯表達(dá)式”可以使用所有的比較運(yùn)算符、邏輯運(yùn)算符。還可以使用子查詢，使用與子查詢進(jìn)行整體比較的ALL、SOME、IN、EXISTS運(yùn)算。GROUP與HAVING子句：分組統(tǒng)計(jì)及結(jié)果過濾輸出。GROUPBY分組字段[，…][HAVING<過濾條件>]分組統(tǒng)計(jì)時(shí)輸出列應(yīng)該由分組字段和統(tǒng)計(jì)集函數(shù)構(gòu)成。HAVING子句必須與GROUP子句聯(lián)用，只對(duì)統(tǒng)計(jì)的結(jié)果進(jìn)行篩選。可以使用集函數(shù)。OREDER子句：OREDERBY排序項(xiàng)[，…]ASC或缺省表示升序，DESC表示降序。如此復(fù)雜如何記憶考前背范例教材以圖書銷售為范例上機(jī)做練習(xí)考試漢譯英英譯漢單表查詢基本SELECTFROM加DISTINCTTOPnORDERBY加WHERE加count(*)MAXMINAVG〖例5-9〗查詢員工表中所有員工姓名、性別、職務(wù)和薪金；查詢輸出所有字段。命令1：SELECT員工.姓名,員工.性別,員工.職務(wù),員工.薪金FROM員工;命令2：SELECT員工.*FROM員工;如何使用可視化方式(查詢生成器)自動(dòng)生成SQL語(yǔ)句〖例5-10〗查詢員工表，輸出“職務(wù)”和“薪金”。命令：SELECT職務(wù),薪金FROM員工;

輸出時(shí)有重復(fù)行，去掉重復(fù)行加DISTINCTSELECTDISTINCT職務(wù),薪金FROM員工;SELECTDISTINCT職務(wù)FROM員工;該命令的語(yǔ)義是什么呢（英譯漢）？〖例5-11〗查詢員工表，輸出“薪金”最高的3名員工姓名、職務(wù)及薪金要實(shí)現(xiàn)該功能，要在命令中增加按“薪金”排順序并取前幾名的功能。命令：SELECTTOP3姓名,職務(wù),薪金FROM員工ORDERBY薪金DESC;〖例5-12〗查詢員工表，統(tǒng)計(jì)輸出職工人數(shù)、最高薪金、最低薪金、平均薪金。統(tǒng)計(jì)人數(shù)，需要對(duì)員工表的行數(shù)進(jìn)行統(tǒng)計(jì)計(jì)數(shù)；其他幾項(xiàng)都要對(duì)于薪金字段進(jìn)行計(jì)算統(tǒng)計(jì)。在SQL中提供了相應(yīng)的集函數(shù)來完整這些功能。集函數(shù)及功能見表5-6。命令：SELECTcount(*),max(薪金),min(薪金),avg(薪金)FROM員工;AS輸出列重命名本例命令可改為：SELECTcount(*)AS人數(shù),max(薪金)AS最高薪金,min(薪金)AS最低薪金,avg(薪金)AS平均薪金FROM員工;〖例5-13〗查詢所有清華大學(xué)出版社（編號(hào)1010）出版的計(jì)算機(jī)類的圖書信息。命令：SELECT*FROM圖書;WHERE編號(hào)="1010"and圖書類別="計(jì)算機(jī)"〖例5-14〗WHERE子句中使用不同條件的查詢示例。命令1:SELECT姓名,性別,生日,職務(wù)FROM員工WHERE姓名LIKE“張?”and生日LIKE“198*”;含義是查詢80年代出生的張姓單名的員工有關(guān)數(shù)據(jù)命令2:SELECT*FROM員工WHERE職務(wù)IN("總經(jīng)理","經(jīng)理","副經(jīng)理")and薪金LIKE"2*“ORDERBY生日;含義是查詢“經(jīng)理”級(jí)、薪金為2開頭的員工數(shù)據(jù)并按生日升序輸出。貨幣或數(shù)字型字段也可以進(jìn)行匹配運(yùn)算。單表命令小結(jié)在命令中包括了<輸出列>、<數(shù)據(jù)源>、<條件>、<排序字段>等。<輸出列>直接位于SELECT命令后面，可以指定字段輸出，“*”代表所有字段；TOP子句指定保留前面若干行；DISTINCT子句用來排除重復(fù)行，與之相反的功能是保留所有行；使用COUNT()、MAX()、MIN()、SUM()、AVG()集函數(shù)可以進(jìn)行匯總統(tǒng)計(jì)；使用AS子句可對(duì)輸出列重新命名。<數(shù)據(jù)源>位于FROM子句后，目前為止都是單個(gè)表。<條件>位于WHERE子句后，定義對(duì)數(shù)據(jù)源的篩選條件，滿足條件的才輸出。<條件>是由多種比較運(yùn)算和邏輯運(yùn)算組成的邏輯表達(dá)式。<排序字段>指定輸出時(shí)用來排序的依據(jù)列，ASC或缺省表示升序，DESC表示降序。多表查詢?cè)黾拥闹饕Z(yǔ)法是在FROM子句中，基本語(yǔ)法〖子句語(yǔ)法〗FROM<左表>JOIN<右表>ON<連接條件