SQL是什麼.doc

SQL筆記1.引言SQL是什麼?*SQL (Structured Query Language)代表結構化查詢語言。*當用戶發出一項查詢，便可從數據庫檔內獲得若干資料。這項查詢是根據用戶所提供的條件 (condition) 所作出的一項檢索。而 SQL則是一個可讓用戶把條件列明的查詢語言。這樣，用戶只須列明查詢的條件，而不須要實際知道有關的檢索方法。*一般的數據庫管理軟件系統 (DBMS) 都包含 SQL 功能。SQL的概念*通過 SQL 指令，用戶先列出數據庫檔及查詢的條件， SQL 程序便會在這數據庫檔內檢查每筆記錄是否符合這項條件，並把有關的資料顯示出來。這個過程稱為檢索。 (見例 2)*用戶除了直接查問各筆記錄的資料外，也可查問統計數項，例如最大值、最小值、總和及平均值。*查詢所得的結果會以表格的形式顯示，用戶亦可指示 SQL 程序把結果貯存成為數據庫檔。在 FoxPro 如何使用 SQL*使用 SQL，必須先把有關數據庫檔開啟。*用戶可使用指令視窗 (Command Window) 直接把指令輸入，亦可使用對話方塊把指令輸入。*若用戶選用字符串的完全配對時，便須輸入 SET ANSI ON。2.SQL檢索指令的基本結構一般語法SELECT, ALL / DISTINCT, *,AS, FROM, WHERE比較IN, BETWEEN, LIKE % _群組GROUP BY, HAVING,COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )顯示次序ORDER BY, ASC / DESC邏輯運算符AND, OR, NOT輸出INTO TABLE / CURSORTO FILE ADDITIVE, TO PRINTER, TO SCREEN聯合UNION簡稱：expr =表達式 expression,groupexpr=群組表達式 group expressioncol=欄 column,comcol=共同欄 common columncolname =欄名 column namenullval =空值 null value欄橫列表格字段記錄數據庫檔實例：學生個人資料例子:考慮以下貯存學生數據的數據庫檔 STUDENT. DBF：(i) hcode 表示學生的社名(即紅黃藍綠四社)。R = Red, Y = Yellow,B = Blue,G = Green(ii) dcode 表示學生的居住地區碼。eg. TST = 尖沙咀(Tsim Sha Tsui),MKK = 旺角(Mong Kok)(iii) remission 表示學生是否享有學費減免：.T. =享有學費減免,.F. = 沒有學費減免(iv) mtest 貯存學生數學測驗的分數，滿分為100。欄名類型欄寬內容id數字4學生編號name字符10學生名字dob日期8出生日期sex字符1性別： M / Fclass字符2班別hcode字符1社名： R, Y, B, Gdcode字符3地區碼remission邏輯1學費減免mtest數字2數學測驗分數I一般語法SELECT . FROM . WHERE .SELECT ALL / DISTINCT expr1 AS col1, expr2 AS col2 ;FROM tablename WHERE conditionSQL 程序會從數據庫檔 tablename 選取符合條件的橫列 (row) 並以表格的格式顯示。表達式 expr1, expr2 可以是 (1) 字段，或 (2) 以函數和字段組成的表達式。而 col1, col2 是表達式 expr1, expr2 在輸出結果的表格內的欄名。選項 DISTINCT 會把重覆出現的橫列刪去(即只顯示一次)，而選項 ALL 則會把所有重覆的保留。條件 condition 可以是 (1) 等式或不等式，或 (2) 字符串的比較，並使用邏輯運算符 AND, OR, NOT。在使用SQL之前，開啟數據庫檔：USE student例 1求出所有學生的資料。SELECT * FROM student注意:1)這個指令並不提出任何條件，所以把 WHERE 部分省去。2)在 SELECT 部分中使用 * 來表示揀選來源表格的所有欄。3)查詢所得的結果將貯存於一個暫時的表格內。結果idnamedobsexclassmtesthcodedcoderemission9801Peter06/04/86M1A70RSSP.F.9802Mary01/10/86F1A92YHHM.F.9803Johnny03/16/86M1A91GSSP.T.9804Wendy07/09/86F1B84BYMT.F.9805Tobe10/17/86M1B88RYMT.F.:例 2求出 1A 班學生的名字和社名。SELECT name, hcode, class FROM student ;WHERE class=1A注意:這例使用條件 class=1A 揀選 1A 班學生：SQL 程序會從來源表格中的每一橫列逐一地檢查是否符合這條件。然後 SQL 程序會根據 SELECT 的選項而保留這些橫列內的三欄，即 name, hcode 及 class。最後程序會把所得的結果貯存於一個暫時的表格內。Class1A1A1A1B1B :Class1A1A1A1B1B :class=1A逐一檢查結果namehcodeclassPeterR1AMaryY1AJohnnyG1ALukeG1ABobbyB1AAaronR1A:例 3求出紅社社員的居住地區。(hcode=R)SELECT DISTINCT dcode FROM student ;WHERE hcode=R注意:若兩個或以上的學生居住於同一地區，使用選項 DISTINCT 便可把重覆的結果省去。結果dcodeHHMKWCMKKSSPTSTYMT例 4求出 1B 班女生的名字和年齡。(準至一位小數)SELECT name, ROUND(DATE( )-dob)/365,1) AS age FROM student ;WHERE class=1B AND sex=F注意:1)1B 班女生的條件包括兩部分：class=1B 和 sex=F。而這項條件必須同時符合，所以要使用邏輯運算符 AND。2)在這數據庫檔裏並沒有一欄直接貯存年齡，所以我們須要使用學生的出生日期 dob 來計算。首先，DATE( )-dob 表示該生的日歲，再除 365 就變成年歲。再用選項 AS age 去說明該欄的名稱。結果nameageWendy12.1Kitty11.5Janet12.4Sandy12.3Mimi12.2例 5求出 1A 班沒有學費減免的學生的名字和編號。SELECT name, id, class FROM student ;WHERE class=1A AND NOT remission注意:1)這裏包括兩項條件：學生必須是 1A 班，而且沒有享有學費減免。 所以在 WHERE 部分中加上運算符 AND。2)因 remission 是一個邏輯字段，所以可直接在邏輯表達式中使用 。在 remission 之前加上 NOT 便把意思相反。結果nameidclassPeter98011AMary98021ALuke98101ABobby98111AAaron98121ARon98131AGigi98241A:II比較expr IN ( value1, value2, value3)expr BETWEEN value1 AND value2expr LIKE %_在 WHERE 部分中，可使用以上的比較條款：1)若 expr 的值是相等於 value1, value2, value3 其中的一個時，條款 expr IN ( value1, value2, value3) 便會送回邏輯值 .T.。而 expr 可以是數值或字符串。2)若expr 是界乎於 value1 和 value2 之間，條款expr BETWEEN value1 AND value2 便會送回邏輯值 .T.。3)若字符串 expr 符合 %_ 的樣式，條款 expr LIKE %_ 便會送回邏輯值 .T.。在樣式中， % 代表任何長度的字符串，而 _ 則代表任何的單一字符。例 6求出所有出生於星期三或星期六的學生。SELECT name, class, CDOW(dob) AS bdate FROM student ;WHERE DOW(dob) IN (4,7)注意:學生若在星期三星期六出生， DOW(dob) 使會送回 數值 4 或 7，所以用 IN (4,7) 來檢查。結果nameclassbdatePeter1AWednesdayWendy1BWednesdayKevin1CSaturdayLuke1AWednesdayAaron1ASaturday:例 7求出所有不是在一月、三月、六月或九月出生的學生。SELECT name, class, dob FROM student ;WHERE MONTH(dob) NOT IN (1,3,6,9)注意:我們不想 MONTH(dob) = 1, 3, 6 或 9， 所以使用 NOT IN (1,3,6,9) 來檢查。結果nameclassdobWendy1B07/09/86Tobe1B10/17/86Eric1C05/05/87Patty1C08/13/87Kevin1C11/21/87Bobby1A02/16/86Aaron1A08/02/86:例 8求出1A 班的學生名字，其數學測驗分數界乎於 80 至 90 分之間。SELECT name, mtest FROM student ;WHERE class=1A AND mtest BETWEEN 80 AND 90注意:1)這裏用了兩個條件：第一個是 class=1A，第二個是測驗分數界乎於 80 至 90 分之間。這兩個條件必須同時成立，所以須用 AND。2)測驗分數界乎於 80 至 90 分之間可用 mtest BETWEEN 80 AND 90 來表示。結果namemtestLuke86Aaron83Gigi84例 9求出所有學生其名字是以 T 為起首。SELECT name, class FROM student ;WHERE name LIKE T%注意:這裏所用的樣式是 T% ，這表示第一個字符必須是 T 而其後可以是任何的字符串。結果nameclassTobe1BTeddy1BTim2A例10求出所有紅社社員其名字的第二個字母是a。SELECT name, class, hcode FROM student ;WHERE name LIKE _a% AND hcode=R注意:這裏所用的樣式是 _a% ，而其中的 _ 符號代表任何單一字符，亦即第一個字符是任意的。第二個字符則必須是 a，而其後的 % 代表任何的字符串。結果nameclasshcodeAaron1ARJanet1BRPaula2ARIII群組SELECT . FROM . WHERE condition ;GROUP BY groupexpr HAVING requirement群組函數： COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )GROUP BY groupexpr 列出群組組成所依照的表達式。一般都是數據庫檔的一欄。WHERE condition 列出個別橫列所須符合的條件，而 HAVING requirement 則列出個別群組須符合的條件。可使用以群組函數來計算統計數項：COUNT( ):記錄出現的目數SUM( ):總和AVG( ):平均值MAX( ):最大值MIN( ):最小值例11求出每一班的人數。SELECT class, COUNT(*) FROM student GROUP BY class注意:1)使用 GROUP BY class， SQL 程序便會先把表格裏的橫列按照 class 排列起來。2)然後 SQL 程序會把相連的橫列依照 class 來群組。3)最後使用群組函數 COUNT(*) 去算數每組的數目。COUNT( )1ACOUNT( )1BCOUNT( )1C以 Class 為群組Studentclass1A1A1A1B1B1B1B1B1B1C1C1C數算1A班的人數數算1C班的人數數算1B班的人數結果classcnt1A101B91C92A82B82C6例12求出每一班的數學測驗平均分。SELECT class, AVG(mtest) FROM student GROUP BY class注意:SQL 程序會先依照 class 來群組，然後在每一組中計算該組的 mtest 平均值。結果classavg_mtest1A85.901B70.331C37.892A89.382B53.132C32.67例13求出每一居住地區的女生數目。SELECT dcode, COUNT(*) FROM student ;WHERE sex=F GROUP BY dcode注意:查詢的條件為 sex=F。而 SQL 程序會先把符合這條件的橫列選出，然後把這些橫列依照 dcode 為群組。結果dcodecntHHM6KWC1MKK1SSP5TST4YMT8例14求出每一區中一學生數學測驗的最高分及最低分。SELECT MAX(mtest), MIN(mtest), dcode FROM student ;WHERE class LIKE 1_ GROUP BY dcode結果max_mtestmin_mtestdcode9236HHM9119MKK9131SSP9236TST7575TSW8838YMT注意:這例使用條件 class LIKE 1_ 來揀選每區的中一學生。例15列出每一班男生數學測驗的平均分，但男生人數不及三人的班則不計算在內。SELECT AVG(mtest), class FROM student ;WHERE sex=M GROUP BY class HAVING COUNT(*) = 3注意:SQL 程序會先把符合個別條件 sex=M 的橫列揀選出來，然後依照 class 來群組並計算每組的 mtest 平均值。最後 SQL 程序會檢查群組條件 COUNT(*) = 3。(這例中 2C 班男生人數不及三人，所以並沒有在結果裏顯示出來。)結果avg_mtestclass86.001A77.751B35.601C86.502A56.502BIV顯示次序SELECT . FROM . WHERE . GROUP BY . ;ORDER BY colname ASC / DESCORDER BY colname 控制結果的顯示序。而 colname 代表結果表格的一欄。而ASC = 升冪， DESC = 降冪。例16列出 1A 班男生的名字，並按名字序顯示。SELECT name, id FROM student ;WHERE sex=M AND class=1A ORDER BY name結果nameidnameiddcodeORDER BYPeter9801Aaron9812Johnny9803Bobby9811Luke9810Johnny9803Bobby9811Luke9810Aaron9812Peter9801Ron9813Ron9813例17列出 2A 班的學生資料，並按居住地區序顯示。SELECT name, id, class, dcode FROM student ;WHERE class=2A ORDER BY dcode結果nameidclassdcodeJimmy97122AHHMTim97132AHHMSamual97142ASHTRosa97032ASSPHelen97022ATSTJoseph97152ATSWPaula97012AYMTSusan97042AYMT例18求出每區居住學生的人數，並按降冪顯示。SELECT COUNT(*) AS cnt, dcode FROM student ;GROUP BY dcode ORDER BY cnt DESC結果cntdocode11YMT10HHM10SSP9MKK5TST2TSW1KWC1MMK1SHT例19列出每社的男社員名字並按班別序顯示。(即社和班的兩層次序)SELECT name, hcode, class FROM student ;WHERE sex=M ORDER BY hcode, class注意:這些橫列先按 hcode 排列(即第一層排列)；而相同的 hcode，再按 class 次序顯示。結果namehcodeclass按 class而排Blue HouseGreen House:按 hcode而排BobbyB1ATeddyB1BJosephB2AZionB2BLeslieB2CJohnnyG1ALukeG1AKevinG1CGeorgeG1C : : : : : :V輸出INTO TABLE tablename把查詢所得的結果貯存成數據庫檔。INTO CURSOR temp把查詢所得的結果暫時貯存於電腦的工作記憶裏。TO FILE filename ADDITIVE把查詢所得的結果貯存成文字檔。(additive = 附加)TO PRINTER輸出到列印機。TO SCREEN輸出到螢幕。例20按學生名字的降冪，列出學生的所有資料，並把結果貯存成數據庫檔NAME.DBF。SELECT * FROM student ;ORDER BY name DESC INTO TABLE name.dbf注意:1)INTO TABLE name.dbf 要求 SQL 程序把結果貯存成檔案。2) 這指令相等於數據庫指令 SORT。3) SQL 程序會把結果貯存成一個新的數據庫檔 name.dbf。結果idnamedobsexclassmtesthcodedcoderemission9707Zion07/29/85M2B51BMKK.F.9709Yvonne08/24/85F2C10RTST.F.9804Wendy07/09/86F1B84BYMT.F.9819Vincent03/15/85M1C29YMKK.F.9805Tobe10/17/86M1B88RYMT.F.9713Tim06/19/85M2A91RHHM.T.9816Teddy01/30/86M1B64BSSP.F.name.dbf:例21按社員的班別、性別及名字的次序，把紅社社員的資料列印出來。SELECT class, name, sex FROM student ;WHERE hcode=R ;ORDER BY class, sex DESC, name TO PRINTER注意:1)這指令要求程序先找出綠社社員，然後把這些橫列以class, sex 和 name 的次序排列。2)SQL 程序先把查詢的結果暫時貯存於工作記憶裏，然後把這結果輸出到打印機。結果classnamesex1AAaronM打印機1APeterM1ARonM1BTobeM1BJanetF1BKittyF硬副本1BMimiF:3.數據庫聯合、相交及差分考慮兩個結構相同的數據庫檔 A 和 B。A 和 B 的聯合(AB) union檢取屬於 A 或 B 的所有橫列。A 和 B 的相交(AB) intersection檢取 A 和 B 所共有的橫列。A 和 B 的差分(AB)difference檢取只屬於 A 而不屬於 B 的橫列。(即從 A 把 B 的部分排出)ABABABAB 聯合AB 相交AB 差分SELECT . FROM . WHERE . ;UNION ;聯合SELECT . FROM . WHERE .SELECT . FROM table1 ;相交WHERE col IN ( SELECT col FROM table2 )SELECT . FROM table1 ;差分WHERE col NOT IN ( SELECT col FROM table2 )實例：橋牌會和棋藝會考慮學校橋牌會和棋藝會的會員，他們的資料分別貯存於同一結構的數據庫檔 BRIDGE.DBF 和 CHESS.DBF 內：欄名類型欄寬內容id數字4學生編號name字符10學生名字sex字符1性別： M / Fclass字符2班別Bridge AChess Bidnamesexclassidnamesexclass19812AaronM1A19802MaryF1A29801PeterM1A29801PeterM1A39814KennyM1B39815EddyM1B49806KittyF1B49814KennyM1B59818EdmondM1C59817GeorgeM1C:在使用SQL之前，開啟這兩個數據庫檔：SELECT AUSE bridgeSELECT BUSE chess例22本校計劃舉行一次 棋橋活動，所有棋藝會和橋牌會的會員必須出席。試以班別和名字的次序，列出兩會會員的名單。(即兩會的聯合)SELECT * FROM bridge ;UNION ;SELECT * FROM chess ;ORDER BY class, name INTO TABLE party注意:所要求的是兩會的所有會員，這便是兩會的聯合。Party結果idnamesexclass19812AaronM1A29802MaryF1A39801PeterM1A49815EddyM1B59814KennyM1B69806KittyF1B79818EdmondM1C89817GeorgeM1C:例23列印兩會的共同會員。(即兩會的相交)SELECT * FROM bridge ;WHERE id IN ( SELECT id FROM chess ) ;TO PRINTER注意:這例須找出兩會所共通的會員。SQL 程序須檢查橋牌會的每一個會員是否也屬於棋藝會。若是屬於的話，這人便符合這個要求了。Common結果idnamesexclass19801PeterM1A29814KennyM1B:例24求出只參加了橋牌會的會員名單。(即兩會之差分)SELECT * FROM bridge ;WHERE id NOT IN ( SELECT id FROM chess ) ;INTO TABLE diff注意:1)這例須從橋牌會中揀選那些並不屬於棋藝會的人。所以 SQL 程序須使用 FROM bridge，即從逐一檢查橋牌會的會員是否屬於棋藝會，若不屬於的話，這人便符合這個要求了。2)差分不是對稱的：若想找出只參加了棋藝會的會員名單，其結果則會完全不同。Diff結果Idnamesexclass19812AaronM1A29806KittyF1B39818EdmondM1C:4.多個數據庫當所查詢的資料貯放於兩個數據庫檔時，就須使用接合 (join)。 接合的作用是把一個數據庫檔內的一個橫列與另一個數據庫檔內的橫列連合起來，從而把所有不同的組合列出來。 (數學: Cartesian Product)自然接合*在接合中加上一項接合條件，要求兩檔的共通欄(common column)的值是相同，這稱為自然接合。這目的是要把這兩檔的相關資料連合起來，變成一個合一的大表格，再從這表格中執行查詢工作。考慮以下兩個數據庫檔 T1 和 T2：T1PeterMary980198029803JohnidnameT2MongKokYaumatei98019802Peter9801Peter9801Mary9802Mary98029803John9803JohnMongKok9801MongKok9801MongKok9801Yaumatei9802Yaumatei9802Yaumatei9802T2.idT2.addrT1.idT1.nameT3接合addrid相同的 id自然接合Peter9801MongKokMary9802YaumateiT2.addridT1.nameT4T3Peter9801Peter9801Mary9802Mary98029803John9803JohnMongKok9801MongKok9801MongKok9801Yaumatei9802Yaumatei9802Yaumatei9802T2.idT2.addrT1.idT1.nameSELECT col, a.col1, b.col2, expr1, expr2 ;FROM table1 a, table2 b ;WHERE col = col在自然接合中，這兩個數據庫檔須要有一個欄是相同的，這一欄稱為共通欄 。SQL 程序會先把這兩檔的所有組合列出，然後從中揀選共通欄的值是相同的橫列。a 和 b 分別是 table1 和 table2的代號，用以指明各欄所屬的檔。表達式 expr1, expr2 可以使用 table1 和 table2內的各欄。實例：樂器班學校規定每個學生都須要學習一件樂器。現在使用數據庫檔 MUSIC.DBF 貯存學生所學的樂器名稱 (而學生的其他資料則貯存於 STUDENT.DBF 內)欄名類型欄寬內容id數字4學生編號type字符10樂器名稱例25列出所有學生的名字及所學習的樂器名稱。SELECT s.class, , s.id, m.type FROM student s, music m ;WHERE s.id=m.id ORDER BY class, name注意:1)這裏用了s 代表 student.dbf 而 m 代表 music.dbf。2)接合條件是 s.id=m.id，表示兩檔的橫列須要依照 id 而接合。相同的 idMusicid9801typeStudent9801idnameclass9801接合結果idnameclasstype接合結果classnameidtype1AAaron9812Piano1ABobby9811Flute1AGigi9824Recorder1AJill9820Piano1AJohnny9803Violin1ALuke9810Piano1AMary9802Flute:例26求出每班學習鋼琴的學生數目。SELECT s.class, COUNT(*) FROM student s, music m ;WHERE s.id=m.id AND m.type=Piano ;GROUP BY class ORDER BY classclass注意:1)裏先首先用了接合條件 s.id=m.id 把兩檔自然接合起來。2)SQL 程序然後從接合的結果(表格形式)中再揀選去符合條件 m.type=Piano 的橫列。3)最後 SQL 程序會依照 class 而群組，並數算每班的數目。Student接合結果接合條件m.type= Piano群組Group By classMusic結果classcnt1A41B21C1外接合*外接合是自然接合再加上沒有配合的部分。(1)先求出自然接合(2)再求出沒有配合(3)把兩部分聯合起來方法：SELECT mon, a.column1, b.column2, expr1, expr2 ;FROM table1 a, table2 b ;WHERE col = col ;UNION ;SELECT comcol, col1, nullval, nullval, nullval ;FROM table1 ;WHERE col NOT