结构化查询语言.ppt

《结构化查询语言.ppt》由会员分享，可在线阅读，更多相关《结构化查询语言.ppt（64页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、结构化查询语言 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望引言SQL是什麼是什麼?當用戶發出一項當用戶發出一項查詢查詢，便可從數據庫檔，便可從數據庫檔內獲得若干資料。內獲得若干資料。1這項這項查詢查詢是根據用戶所提供的條件是根據用戶所提供的條件(condition)所作出的一項檢索。所作出的一項檢索。用戶只須列明查詢的用戶只須列明查詢的條件條件，而不須要實際，而不須要實際知道有關的檢索方法。知道有關的檢索方法。MySQL2引言SQL的概念的概念用戶先列出數據庫

2、檔及查詢的條件。用戶先列出數據庫檔及查詢的條件。1查詢所得的結果會以表格的形式顯示。查詢所得的結果會以表格的形式顯示。可查問統計數項。可查問統計數項。SQL 程序便會在這數據庫檔內檢查每筆程序便會在這數據庫檔內檢查每筆記錄是否符合這項條件，並把有關的資記錄是否符合這項條件，並把有關的資料顯示出來。這個過程稱為檢索。料顯示出來。這個過程稱為檢索。MySQL3基本結構2一般語法一般語法SELECT,ALL/DISTINCT,*,SELECT,ALL/DISTINCT,*,AS,FROM,WHEREAS,FROM,WHERE比較比較IN,BETWEEN,LIKE%_IN,BETWEEN,LIKE%_

3、群組群組GROUP BY,HAVING,GROUP BY,HAVING,COUNT(),SUM(),AVG(),MAX(),COUNT(),SUM(),AVG(),MAX(),MIN()MIN()顯示次序顯示次序ORDER BY,ASC/DESCORDER BY,ASC/DESC邏輯運算符邏輯運算符AND,OR,NOTAND,OR,NOT輸出輸出CREATE TABLE AS CREATE TABLE AS MySQL4欄名欄名類型類型欄寬欄寬內容內容id數字4學生編號name字符10學生名字dob日期8出生日期sex字符1性別：M/Fclass字符2班別hcode字符1社名：R,Y,B,Gd

4、code字符3地區碼remission 邏輯1學費減免mtest數字2數學測驗分數2Table:STUDENT實例：學生個人資料實例：學生個人資料MySQL5欄橫列2Table:STUDENT實例：學生個人資料實例：學生個人資料表格MySQL6一般語法ISELECT ALL/DISTINCTALL/DISTINCT expr1expr1 ASAS col1col1,expr2expr2 ASAS col2col2 FROM tablenametablename WHERE conditionconditionSELECT.FROM.WHERE.MySQL7一般語法ISQL 程序會從數據庫檔 t

5、ablename 選取符合條件的橫列(row)並以表格的格式顯示。表達式 expr1,expr2 可以是(1)字段(2)以函數和字段組成的表達式SELECT ALL/DISTINCTALL/DISTINCT expr1expr1 ASAS col1col1,expr2expr2 ASAS col2col2 FROM tablenametablename WHERE conditioncondition而 col1,col2 是表達式 expr1,expr2 在輸出結果的表格內的欄名。MySQL8一般語法I選項 DISTINCT 會把重覆出現的橫列刪去(即只顯示一次)，而選項 ALL 則會把所有

6、重覆的保留。條件 condition 可以是(1)等式或不等式(2)字符串的比較使用邏輯運算符 AND,OR,NOTSELECT ALL/DISTINCTALL/DISTINCT expr1expr1 ASAS col1col1,expr2expr2 ASAS col2col2 FROM tablenametablename WHERE conditionconditionMySQL9一般語法I例 1求出所有學生的資料。SELECT*FROM student結果結果MySQL10一般語法I例 2求出 1A 班學生的名字和社名。SELECT name,hcode,class FROM stude

7、nt WHERE class=1AClass1 1A A1 1A A1 1B B1 1A A1 1B B :Class1 1A A1 1A A1 1B B1 1A A1 1B B :class=1A逐一檢查逐一檢查MySQL11一般語法I結果結果選擇三欄選擇三欄 Class1 1A A1 1A A1 1B B1 1A A1 1B B :Class1 1A A1 1A A1 1A A1 1A A1 1A A :hcodenamePeterPeterMaryMaryJohnnyJohnnyLukeLukeBobbyBobby :R RY YG GG GB B :MySQL12一般語法I例 3求出紅

8、社社員的居住地區。SELECT DISTINCT dcode FROM student WHERE hcode=R結果結果MySQL131 1B B 女生的條件女生的條件:1)1)class=1Bclass=1B2)2)sex=Fsex=F3)3)符合以上兩項條件符合以上兩項條件(ANDAND運算符運算符)一般語法I例 4求出1B班女生的名字和年齡。MySQL14一般語法I例 4求出1B班女生的名字和年齡。SELECT name,(2010-year(dob)AS age FROM studentWHERE class=1B AND sex=F結果結果MySQL15一般語法I例例 5 5求出求

9、出1 1A A班班沒有學費減免的學生的名字和編號。沒有學費減免的學生的名字和編號。SELECT name,id,class FROM student WHERE class=1A AND remission!=1結果結果MySQL16比較IIexprexpr IN(IN(value1value1,value2value2,value3value3)exprexpr BETWEEN BETWEEN value1value1 AND AND value2value2exprexpr LIKE%_ LIKE%_MySQL17比較II例例 6 6求出所有出生於求出所有出生於星期三星期三或或星期六星期六

10、的學生。的學生。SELECT name,class,dayofweek(dob)AS bdate FROM studentWHERE bdate IN(4,7)結果結果MySQL18比較II例 7求出所有不是在一月、三月、六月或九月出生的學生。SELECT name,class,dob FROM student WHERE MONTH(dob)NOT IN(1,3,6,9)結果結果MySQL19比較II例 8求出1A班的學生名字，其數學測驗分數界乎於80至90分之間。SELECT name,mtest FROM student WHERE class=1A ANDmtest BETWEEN 8

11、0 AND 90結果結果MySQL20比較II例 9求出所有學生其名字是以T為起首。SELECT name,class FROM student WHERE name LIKE T%結果結果MySQL21比較II例10求出所有紅社社員其名字的第二個字母是a。SELECT name,class,hcode FROM student WHERE name LIKE _a%AND hcode=R結果結果MySQL22群組IIISELECT.FROM.WHERE SELECT.FROM.WHERE conditioncondition GROUP BY GROUP BY groupexprgroupe

12、xpr HAVING HAVING requirementrequirement 群組函數：群組函數：COUNT(),SUM(),AVG(),MAX(),MIN()COUNT(),SUM(),AVG(),MAX(),MIN()GROUP BY groupexpr 列出群組組成所依照的表達式。一般都是數據庫檔的一欄。WHERE condition 列出個別橫列所須符合的條件，而 HAVING requirement 則列出個別群組須符合的條件。MySQL23COUNT()COUNT()1ACOUNT()COUNT()1BCOUNT()COUNT()1C1A1B1CStudentStudentcl

13、ass1 1A A1 1A A1 1A A1 1B B1 1B B1 1B B1 1B B1 1B B1 1B B1 1C C1 1C C1 1C C數算數算1A班班的人數的人數數算數算1B班班的人數的人數數算數算1C班班的人數的人數群組group byIII例11求每班的人數。MySQL24群組IIISELECT class,COUNT(*)FROM student GROUP BY class例例1111求出求出每班每班的人數。的人數。結果結果MySQL25AVG()AVG()AVG()1A1B1CStudentStudentclass1 1A A1 1A A1 1A A1 1B B1 1

14、B B1 1B B1 1B B1 1B B1 1B B1 1C C1 1C C1 1C C求求1A班班的平均分的平均分求求1B班班的平均分的平均分求求1C班班的平均分的平均分例例1212求求每班每班的數學測驗的數學測驗平均平均分。分。群組IIIMySQL26群組III例12求出每班的數學測驗平均分。SELECT class,AVG(mtest)FROM studentGROUP BY class結果結果MySQL27群組IIISELECT dcode,COUNT(*)FROM student WHERE sex=F GROUP BY dcode結果結果例13求出每一居住地區的女生數目。MySQ

15、L28群組III例14求出每一區中一學生數學測驗的最高分及最低分。SELECT MAX(mtest),MIN(mtest),dcode FROM student WHERE class LIKE 1_GROUP BY dcode結果結果MySQL29群組III例15列出每班男生數學測驗的平均分，但男生人數不及三人的班則不計算在內。個別條件個別條件男生：男生：sex=M群組條件群組條件最少三人：最少三人：COUNT(*)=3MySQL30群組III例15列出每班男生數學測驗的平均分，但男生人數不及三人的班則不計算在內。SELECT AVG(mtest),class FROM student WH

16、ERE sex=M GROUP BY class HAVING COUNT(*)=3結果結果個別條件個別條件群組條件群組條件MySQL31顯示次序IVSELECT.FROM.WHERE.SELECT.FROM.WHERE.GROUP BY.GROUP BY.ORDER BY ORDER BY colnamecolname ASC/DESC ASC/DESCMySQL32顯示次序IVSELECT name,id FROM student WHERE sex=M AND class=1A ORDER BY nameORDER BYdcode 結果結果例16列出1A班男生的名字，並按名字序顯示。My

17、SQL33顯示次序IVSELECT name,id,class,dcode FROM student WHERE class=2A ORDER BY dcode例17列出2A班的學生資料，並按居住地區序顯示。結果結果MySQL34顯示次序IVSELECT COUNT(*)AS cnt,dcode FROM student GROUP BY dcode ORDER BY cnt DESC例18 求出每區居住學生人數，並按降冪顯示。結果結果MySQL35顯示次序IVSELECT name,class,hcode FROM student WHERE sex=M ORDER BY hcode,cla

18、ss例19列出每社的男社員名字並按班別序顯示。(即社和班的兩層次序)MySQL36顯示次序IV結果結果按 classBlueHouseGreenHouse:按hcodeMySQL37輸出V例20按學生名字的降冪，列出學生的所有資料，並把結果貯存成數據庫檔 NAME。CREATE TABLE name ASSELECT*FROM student ORDER BY name DESC結果結果MySQL38輸出V例21按社員的班別、性別及名字的次序，把紅社社員的資料列印出來。SELECT class,name,sex FROM student WHERE hcode=R ORDER BY class

19、,sex DESC,name結果結果MySQL39數據庫聯合、相交及差分3A 和和 B 的聯合的聯合(A B)檢取屬於檢取屬於 A A 或或 B B 的所有橫列。的所有橫列。ABunionMySQL403A 和和 B 的相交的相交(A B)數據庫聯合、相交及差分檢取檢取 A A 和和 B B 所共通的橫列。所共通的橫列。ABintersectionMySQL413A 和和 B 的相交的相交(AB)數據庫聯合、相交及差分檢取只屬於 A 而不屬於 B 的橫列。(即從 A 把 B 的部分排出)ABdifferenceMySQL423考慮學校的考慮學校的橋牌會橋牌會和和棋藝會棋藝會的會員，的會員，他們

20、的資料分別貯存於他們的資料分別貯存於同一結構同一結構的的數據庫檔內：數據庫檔內：欄名欄名類型類型欄寬欄寬內容內容id數字4學生編號name字符10學生名字sex字符1性別：M/Fclass字符2班別Tables:BRIDGE /CHESS實例：橋牌會和棋藝會實例：橋牌會和棋藝會MySQL433數據庫聯合、相交及差分MySQL443CREATE TABLE party ASSELECT*FROM bridge UNION SELECT*FROM chess ORDER BY class,name例22本校計劃舉行一次棋橋活動。列出兩會會員的名單。(即兩會的聯合)SELECT.FROM.WHERE

21、.SELECT.FROM.WHERE.UNION UNION SELECT.FROM.WHERE.SELECT.FROM.WHERE.結果結果數據庫聯合、相交及差分聯合聯合MySQL453SELECT*FROM bridge WHERE id IN(SELECT id FROM chess)例23列印兩會的共同會員。(即兩會的相交)SELECT.FROM SELECT.FROM table1table1 WHERE WHERE colcol IN (SELECT IN (SELECT colcol FROM FROM table2table2)結果結果數據庫聯合、相交及差分相交相交MySQL4

22、63CREATE TABLE diff ASSELECT*FROM bridge WHERE id NOT IN(SELECT id FROM chess)例24求出只參加了橋牌會的名單。(即兩會之差分)SELECT.FROM SELECT.FROM table1table1 WHERE WHERE colcol NOT IN (SELECT NOT IN (SELECT colcol FROM FROM table2table2)結果結果數據庫聯合、相交及差分差分差分MySQL47多個數據庫4 當所查詢的資料貯放於兩個數據庫檔時，就須使用接合接合(join)。接合的作用是把一個數據庫檔內的一

23、個橫列與另一個數據庫檔內的橫列連連合合起來，從而把所有不同的組合列出來。MySQL48在接合中加上一項接合條件，要求兩檔在接合中加上一項接合條件，要求兩檔的共通欄的共通欄(common column)的值是相同，的值是相同，這稱為這稱為自然接合自然接合。4自然接合這目的是要把這兩檔的這目的是要把這兩檔的相關資料相關資料連合起連合起來，變成一個來，變成一個合一的合一的大表格，再從這表大表格，再從這表格中執行查詢工作。格中執行查詢工作。MySQL49接合接合4多個數據庫T1PeterMary980198029803JohnidnameT2 98019802MongKokYaumeteiidaddr

24、9801Peter9801MongKok9801Peter9802Yaumetei9802Mary9801MongKok9802Mary9802Yaumetei98039803JohnJohn98019802MongKokYaumeteiT3T1.idT1.name T2.idT2.addrMySQL504多個數據庫9801Peter9801 MongKok9801Peter9802 Yaumetei9802Mary9801 MongKok9802Mary9802 Yaumetei98039803JohnJohn98019802MongKokYaumeteiT3T1.idT1.name T2.

25、idT2.addr自然接合自然接合9801Peter MongKok9802Mary YaumeteiidT1.name T2.addrT4相同的相同的 idMySQL514學校規定每個學生都須要學習一件樂器。學校規定每個學生都須要學習一件樂器。tablestables：STUDENTSTUDENT&MUSICMUSIC共同欄：共同欄：學生編號學生編號學生編號學生編號 idid欄名欄名類型類型欄寬欄寬內容內容id數字4學生編號type字符10樂器名稱MUSIC實例：樂器班實例：樂器班實例：樂器班實例：樂器班MySQL524MusicMusicidid98019801typetypeStuden

26、tStudent98019801ididnamename classclass98019801接合的結果接合的結果接合的結果接合的結果ididnamenameclassclasstypetype相同的相同的相同的相同的 idid自然自然自然自然 接合接合接合接合例25列出所有學生的名字及所學習的樂器名稱。自然接合MySQL53SELECT s.class,s.name,s.id,m.type FROM student s,music m WHERE s.id=m.id ORDER BY class,name4結果結果例25列出所有學生的名字及所學習的樂器名稱。自然接合MySQL54例26求出每

27、班學習鋼琴的學生數目。4三部分三部分：(1)自然接合自然接合：以以 id 來接合來接合(2)條件：條件：m.type=Piano(3)群組：群組：GROUP BY class自然接合MySQL554MusicMusicStudentStudent接合結果接合結果接合結果接合結果自然自然接合接合條件條件m.type=Piano例26自然接合Group By class群組群組MySQL56例26求每班學習鋼琴的學生數目SELECT s.class,COUNT(*)FROM student s,music m WHERE s.id=m.id AND m.type=Piano GROUP BY cl

28、ass ORDER BY class4結果結果自然接合MySQL57外接合外接合是自然接合自然接合再加上沒有配合沒有配合的部分。4外接合(1)先求出自然接合方法：(2)再求出沒有配合(3)把兩部分聯合起來MySQL58例27列出尚未選擇樂器的學生名字。(即沒有相配)4MusicMusicididtypetypeStudentStudent98039803ididnamename classclass外接合沒有配合沒有配合沒有配合沒有配合MySQL59例27列出尚未選擇樂器的學生名字。(即沒有相配)SELECT class,name,id FROM student WHERE id NOT IN

29、(SELECT id FROM music)ORDER BY class,name4結果結果外接合MySQL60例28列出一份名單，去查核所有學生學習的樂器。名單須包括尚未參加樂器班的學生名字。(即外接合)4外接合MySQL614自然接合自然接合自然接合自然接合沒有相配沒有相配外接合外接合例28外接合MySQL62SELECT s.class,s.name,s.id,m.type FROM student s,music m WHERE s.id=m.id 4UNION SELECT class,name,id,FROM student WHERE id NOT IN(SELECT id FROM music)ORDER BY 1,2例28外接合沒有配合沒有配合自然接合自然接合MySQL63外接合外接合4空白空白沒有相配沒有相配自然接合自然接合外接合MySQL64