1、第四章 SQLSQL概述SQL數(shù)據(jù)定義功能SQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)修改功能SQL數(shù)據(jù)控制功能嵌入式SQLSQL概述（）歷史1974年，由Boyce和Chamber提出。1975-1979年，在System R上實現(xiàn)，由IBM的San Jose研究室研制，稱為Sequel，現(xiàn)在稱為SQL (Struceured Query Languang)。標(biāo)準(zhǔn)化有關(guān)組織ANSI(American Natural Standard Institute)ISO(International Organization for Standardization)有關(guān)標(biāo)準(zhǔn)SQL-86：“數(shù)據(jù)庫語言SQL”SQL概述（

2、）SQL-89：“具有完整性增強的數(shù)據(jù)庫語言SQL”，增加了對完整性約束的支持。SQL-92：“數(shù)據(jù)庫語言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強的完整性、安全性支持等。SQL-3：正在討論中的新的標(biāo)準(zhǔn)，將增加對面向?qū)ο竽Ｐ偷闹С帧Ｌ攸c一體化集DDL，DML，DCL于一體。單一的結(jié)構(gòu)-關(guān)系，帶來了數(shù)據(jù)操作符的統(tǒng)一。面向集合的操作方式一次一集合。SQL概述（）高度非過程化 用戶只需提出“做什么”，無須告訴“怎么做”，不必了解存取路徑。兩種使用方式，統(tǒng)一的語法結(jié)構(gòu) SQL既是自含式語言（用戶使用），又是嵌入式語言（程序員使用）。語言簡潔，易學(xué)易用SQ

3、L功能操作符數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE，DROP數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REVOKE示例關(guān)系DEPT(D# , DNAME , DEAN)S(S# , SNAME , SEX , AGE , D#)COURSE(C# , CN , PC# , CREDIT)SC(S# , C# , SCORE)PROF(P# , PNAME, AGE, D# , SAL)PC(P# , C#)SQL數(shù)據(jù)定義功能域定義基本表的定義索引的定義數(shù)據(jù)庫的建立與撤消SQL數(shù)據(jù)定義特點域定義（）域類型（SQL-92）char（n）：固定長度的字符串。varchar

4、（n）：可變長字符串。int：整數(shù)。smallint：小整數(shù)類型。numeric（p，d）：定點數(shù)，小數(shù)點左邊p位，右邊q位。real：浮點數(shù)。double precision：雙精度浮點數(shù)。date：日期（年、月、日）。time：時間（小時、分、秒）。interval：兩個date或time類型數(shù)據(jù)之間的差。域定義（）域定義格式 create domain 域名 數(shù)據(jù)類型示例create domain person-name char（20） 類似C語言中：typedef ADDRESS_LISTchar name10;char telephone20;char location20char

5、 email20;ADDRESS_LIST tom;基本表的定義（）基本表的定義（CREATE）格式 create table 表名（ 列名 數(shù)據(jù)類型 default 缺省值 not null ，列名 數(shù)據(jù)類型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（條件）基本表的定義（）示例 create domain person_name char(20)create table PROF( PNO char10, person_name PNAME

6、not null, SAL int, AGE int, DNO char10, primary key (PNO), foreign key (DNO) references DEPT(DNO), check (SAL 0)基本表的定義（）修改基本表定義（ALTER）格式：alter table 表名add 子句增加新列drop 子句刪除列modify 子句修改列定義示例alter table PROFadd LOCATION char30基本表的定義（）撤消基本表定義（drop）格式drop table 表名示例 drop table DEPT危險 撤消基本表后，基本表的定義、表中數(shù)據(jù)、索引

7、、以及由此表導(dǎo)出的視圖的定義都被刪除。索引的定義（）索引的定義格式 create unique/distinct cluster index 索引名 on 表名 (列名 asc/desc , 列名asc/desc) unique（distinct）：唯一性索引，不允許表中不同的行在索引列上取相同值。若已有相同值存在，則系統(tǒng)給出相關(guān)信息，不建此索引。系統(tǒng)并拒絕違背唯一性的插入、更新。 cluster：聚集索引，表中元組按索引項的值排序并物理地聚集在一起。一個基本表上只能建一個聚集索引。 asc/desc：索引表中索引值的排序次序，缺省為asc。 示例： create cluster index

8、s-index on S（S#） 索引的定義（）索引的刪除格式： drop index 索引名索引的有關(guān)說明可以動態(tài)地定義索引，即可以隨時建立和刪除索引。不允許用戶在數(shù)據(jù)操作中引用索引。索引如何使用完全由系統(tǒng)決定，這支持了數(shù)據(jù)的物理獨立性。應(yīng)該在使用頻率高的、經(jīng)常用于連接的列上建索引。一個表上可建多個索引。索引可以提高查詢效率，但索引過多耗費空間，且降低了插入、刪除、更新的效率。數(shù)據(jù)庫的建立與撤消 有的數(shù)據(jù)庫系統(tǒng)支持多庫。建立一個新數(shù)據(jù)庫create database 數(shù)據(jù)庫名撤消一個數(shù)據(jù)庫drop database 數(shù)據(jù)庫名指定當(dāng)前數(shù)據(jù)庫database 數(shù)據(jù)庫名指定當(dāng)前數(shù)據(jù)庫close d

9、atabase 數(shù)據(jù)庫名SQL數(shù)據(jù)定義特點 SQL中，任何時候都可以執(zhí)行一個數(shù)據(jù)定義語句，隨時修改數(shù)據(jù)庫結(jié)構(gòu)。而在非關(guān)系型的數(shù)據(jù)庫系統(tǒng)中，必須在數(shù)據(jù)庫的裝入和使用前全部完成數(shù)據(jù)庫的定義。若要修改已投入運行的數(shù)據(jù)庫，則需停下一切數(shù)據(jù)庫活動，把數(shù)據(jù)庫卸出，修改數(shù)據(jù)庫定義并重新編譯，再按修改過的數(shù)據(jù)庫結(jié)構(gòu)重新裝入數(shù)據(jù)。數(shù)據(jù)庫定義不斷增長（不必一開始就定義完整）。數(shù)據(jù)庫定義隨時修改（不必一開始就完全合理）?？蛇M(jìn)行增加索引、撤消索引的實驗，檢驗其對效率的影響。SQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)查詢基本結(jié)構(gòu)select子句重復(fù)元組的處理from子句where子句更名運算字符串操作元組顯示順序集合操作SQL數(shù)據(jù)查

10、詢功能分組和聚集函數(shù)空值嵌套子查詢派生關(guān)系視圖關(guān)系的連接SQL數(shù)據(jù)查詢基本結(jié)構(gòu)基本結(jié)構(gòu)select A1 , A2 , , Anfrom r1 , r2 , , rmwhere P A1 , A2 , , An(p(r1 r2 rm)示例給出所有老師的姓名。select PNAMEfrom PROFselect子句目標(biāo)列形式 可以為列名，* ，算術(shù)表達(dá)式，聚集函數(shù)?！?”：表示“所有的屬性”。給出所有老師的信息。select *from PROF帶， ， 的算術(shù)表達(dá)式 給出所有老師的姓名及稅后工資額。select PNAME，SAL 0.95from PROF重復(fù)元組的處理語法約束 缺省為保留

11、重復(fù)元組，也可用關(guān)鍵字all顯式指明。若要去掉重復(fù)元組，可用關(guān)鍵字distinct或unique指明。示例 找出所有選修課程的學(xué)生。 select distinct SNO from SCfrom子句（）說明 from子句列出查詢的對象表。當(dāng)目標(biāo)列取自多個表時，在不混淆的情況下可以不用顯式指明來自哪個關(guān)系。示例例：找出工資低于500的職工的姓名、工資、系別。 select PNAME , SAL , DNAME from PROF , DEPT where SAL P2.SAL 注：as可選。字符串操作（）命令格式like：找出滿足給定匹配條件的字符串。 格式：列名 not like “字符串

12、”匹配規(guī)則： “%” ：匹配零個或多個字符。 “”：匹配任意單個字符。 escape ：定義轉(zhuǎn)義字符，以去掉特殊字符的特定含義，使其被作為普通字符看待。如escape “”，是定義了 作為轉(zhuǎn)義字符，則可用%去匹配%，用去匹配，用 去匹配 。字符串操作（）示例列出姓名以“張”打頭的教師的所有信息。 select * from PROF where PNAME like “張%”列出名稱中含有4個字符以上，且倒數(shù)第3個字符是d，倒數(shù)第2個字符是_的系的所有信息。 select * from PROF where PNAME like “% d ”元組顯示順序命令order by 列名 asc |

13、desc示例按系名升序列出老師姓名，所在系名，同一系中老師按姓名降序排列。 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc集合操作（）命令集合并：union集合交：intersect集合差： except示例求選修了001或002號課程的學(xué)生號。（select SNO from SC where CNO = 001）union all（select SNO from SC where CNO = 002）集合操作（）求選修了001和002號而沒有選003號課程的學(xué)

14、生號。（select SNO from SC where CNO = 001 or CNO = 002 ）except（select SNO from SC where CNO = 003）提示 集合操作自動去除重復(fù)元組，如果要保留重復(fù)元組的話，必須用all關(guān)鍵詞指明。分組和聚集函數(shù)（）分組命令group by 列名 having 條件表達(dá)式 group by將表中的元組按指定列上的值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值。having則對分組進(jìn)行選擇，只將聚集函數(shù)作用到滿足條件的分組上。聚集函數(shù)平均值：avg最小值：min最大值：max總和：sum記數(shù)：count分組和聚集

15、函數(shù)（）示例列出各系的老師的最高、最低、平均工資。 select DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO列出及格的學(xué)生的平均成績。 select SNO，avg(SCORE) from SC group by SNO having min(SCORE) = 60分組和聚集函數(shù)（）? 求選修了課程的學(xué)生人數(shù)。 select count (SNO ) from SC select PNAME，max(SAL) from PROF select DNO，avg(SAL) from PROF group by DNO where AG

16、E 60空值（）空值測試is not null測試指定列的值是否為空值。示例找出年齡值為空的老師姓名。 select PNAME from PROF where AGE is null不可寫為where AGE = null空值（）注意事項除is not null之外，空值不滿足任何查找條件。如果null參與算術(shù)運算，則該算術(shù)表達(dá)式的值為null。如果null參與比較運算，則結(jié)果可視為false。在SQL-92中可看成unknown。如果null參與聚集運算，則除count(*)之外其它聚集函數(shù)都忽略null。 例：select sum(SAL) from PROF 例：select coun

17、t(*) from PROF嵌套子查詢集合成員資格集合之間的比較集合基數(shù)的測試測試集合是否為空測試集合是否存在重復(fù)元組集合成員資格（）in 子查詢表達(dá)式 not in （子查詢）判斷表達(dá)式的值是否在子查詢的結(jié)果中。示例選修了001號課程的學(xué)生的學(xué)號及姓名。 select SNO，SNAME from S where SNO in (select SNO from SC where CNO = 001）集合成員資格（）列出選修了001號和002號課程的學(xué)生的學(xué)號。 select SNO from SC where SC.CNO = 001 and SNO in （select SNO from

18、SC where CNO = 002）列出張軍和王紅同學(xué)的所有信息。 select * from S where SNAME in （“張軍”，“王紅”）集合之間的比較（）some/all子查詢表達(dá)式 比較運算符 some （子查詢） 表達(dá)式的值至少與子查詢結(jié)果中的一個值相比滿足比較運算符 。表達(dá)式 比較運算符 all （子查詢） 表達(dá)式的值與子查詢結(jié)果中的所有的值相比都滿足比較運算符。集合之間的比較（）示例找出平均成績最高的學(xué)生號。 select SNO from SC group by SNO having avg(SCORE) = all（select avg(SCORE) from S

19、C group by SNO）集合基數(shù)的測試（）測試集合是否為空not exists （子查詢）判斷子查詢的結(jié)果集合中是否有任何元組存在。列出選修了01號課程的學(xué)生的學(xué)號及姓名。 select SNO，SNAME from S where exists （select * from SC where CNO = 01 and SNO = S.SNO）集合基數(shù)的測試（）列出選修了001號和002號課程的學(xué)生的學(xué)號。 select SNO from SC SC1 where SC1.CNO = 001 and exists （select SNO from SC SC2 where SC2. CN

20、O = 002 and SC2.SNO = SC1.SNO）注：in后的子查詢與外層查詢無關(guān)，每個子查詢執(zhí)行一次，而exists后的子查詢與外層查詢有關(guān)，需要執(zhí)行多次，稱之為相關(guān)子查詢。列出至少選修了001號學(xué)生選修的所有課程的學(xué)生名。 select SNAME from S where not exists（select CNO from COURSE where exists（select * from SC where SC.CNO = COURSE.CNO and SC.SNO = 001） and not exists（select * from SC where SC.CNO =

21、COURSE.CNO and SC.SNO = S.SNO）任意課程，001號學(xué)生選之，所求學(xué)生選之。 不存在任何一門課程，001號學(xué)生選之，所求學(xué)生沒有選之。集合基數(shù)的測試（）測試集合是否存在重復(fù)元組unique (子查詢）如果子查詢結(jié)果中沒有重復(fù)元組，則返回true。示例找出所有只教授一門課程的老師姓名。 select PNAME from PROF where unique（select PNO from PC where PC.PNO = PROF.PNO)集合基數(shù)的測試（）找出至少選修了兩門課程的學(xué)生姓名。 select SNAME from S where not unique（s

22、elect SNO from SC where SC.SNO = S.SNO)思考 to TRUE or not to TRUE , that is the question. unique (a , b , null) , (a , b , null) ? 派生關(guān)系（）命令（子查詢） as 關(guān)系名（列名，列名，） SQL-92中，允許在from子句中使用子查詢表達(dá)式，這時可將該子查詢的結(jié)果命名為一個臨時關(guān)系加以引用。示例找出平均成績及格的學(xué)生。 先求出每個學(xué)生的平均成績，再從中找出及格的學(xué)生 select SNAME ， avg（SCORE） from S，SC where SC.SNO =

23、 S.SNO group by SC .SNO派生關(guān)系（）select SNAME ， AVG_SCOREfrom (select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO) as result(SNAME ， AVG_SCORE )where AVG_SCORE = 60派生關(guān)系 Vs 視圖？視圖（）定義視圖create view view_name(列名，列名 ) as （查詢表達(dá)式）with check option 視圖的屬性名缺省為子查詢結(jié)果中的屬性名，也可以顯式指明。with check o

24、ption指明當(dāng)對視圖進(jìn)行insert，update時，要檢查是否滿足視圖定義中的條件。撤消視圖drop view view_name視圖（）示例create view COMPUTER_PROFas (select PNO , PNAME , SAL from PROF，DEPT where PROF.PNO = DEPT.PNO and DEPT.DNAME = “計算機系”）create view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL )as ( select DNO, min(SAL), max(SAL), avg(SAL), sum(SAL)

25、 from PROF group by DNO )視圖（）給出計算機系工資超過800的老師姓名。select PNAMEfrom COMPUTER_PROFwhere SAL 800給出計算機系老師的最低、最高、平均工資以及工資總額。select LOW , HIGH , AVERAGE , TOTALfrom DEPTSAL , DEPTwhere DEPTSAL.DNO = DEPT.DNO and DEPT.DNAME =“計算機系”關(guān)系的連接（）基本分類 連接成分包括兩個輸入關(guān)系、連接條件、連接類型。連接條件：決定兩個關(guān)系中哪些元組相互匹配，以及連接結(jié)果中出現(xiàn)哪些屬性。連接類型：決定如

26、何處理與連接條件不匹配的元組。連接類型連接條件inner joinleft outer joinright outer joinfull outer joinnatureon using (A1, A2 , An)關(guān)系的連接（）自然連接：出現(xiàn)在結(jié)果關(guān)系中的兩個連接關(guān)系的元組在公共屬性上取值相等，且公共屬性只出現(xiàn)一次。on ：出現(xiàn)在結(jié)果關(guān)系中的兩個連接關(guān)系的元組在公共屬性上取值滿足謂詞條件P，且公共屬性出現(xiàn)兩次。using (A1, A2 , An)： (A1, A2 , An)是兩個連接關(guān)系的公共屬性的子集，元組在(A1, A2 , An)上取值相等，且(A1, A2 , An)只出現(xiàn)一次。內(nèi)

27、連接：舍棄不匹配的元組。左外連接：內(nèi)連接 + 左邊關(guān)系中失配的元組（缺少的右邊關(guān)系屬性值用null表示）。關(guān)系的連接（）右外連接：內(nèi)連接 + 右邊關(guān)系中失配的元組（缺少的左邊關(guān)系屬性值用null表示）。全外連接：內(nèi)連接 + 左邊關(guān)系中失配的元組（缺少的右邊關(guān)系屬性值用null表示）+ 右邊關(guān)系中失配的元組（缺少的左邊關(guān)系屬性值用null表示）。cross join：兩個關(guān)系的笛卡兒積。union join：左邊關(guān)系中失配的元組+ 右邊關(guān)系中失配的元組。對于外連接，連接條件是必須的；對于內(nèi)連接，連接條件是可選的，沒有連接條件等價于兩個關(guān)系的笛卡兒積。關(guān)系的連接（）列出老師的教工號、姓名、工資、所

28、教課程號。select PNO，PNAME，SAL，CNOfrom （PROF nature left outer join PC）R inner join S on R.C = S.CRS關(guān)系的連接（）R left outer join S on R.C = S.CR nature right outer join S關(guān)系的連接（）R full outer join S on R.C = S.CSQL的數(shù)據(jù)修改功能插入刪除修改視圖更新插入操作（）命令insert into 表名 （列名，列名values （值 ，值）插入一條指定好值的元組insert into 表名 （列名，列名（子查詢）插

29、入子查詢結(jié)果中的若干條元組示例insert into PROF values ( P123, “王明”, 35, D08, 498 )insert into PROF (PNO, PNAME, DNO) values ( P123, “王明”, D08 ) 思考：SAL取何值？如何防止插入帶有空值的元組？插入操作（）將平均成績大于90的學(xué)生加入到EXCELLENT中。insert into EXCELLENT ( SNO, GRADE)select SNO , avg(SCORE)from SCgroup by (SNO)having avg(SCORE) 90FORBIDDEN( INFOR

30、MIX) insert into PROFselect *from PROF若支持，則完成查詢后，再執(zhí)行修改操作不支持修改在子查詢中出現(xiàn)的表刪除操作（）命令delete from 表名 where 條件表達(dá)式 從表中刪除符合條件的元組，如果沒有where語句，則刪除所有元組。示例清除所有選課記錄delete from SC刪除王明老師所有的任課記錄。delete from PCwhere PNO in (select PNO from PROF where PNAME = “王明”) 刪除操作（）刪除低于平均工資的老師記錄。delete from PROFwhere SAL 2000 upda

31、te PROF set SAL = SAL * 0.95 where SAL 0：取到主變量的值發(fā)生了截斷，指示變 量的值是截斷前的字符串的實際長度。需要解決的幾個問題（）指示變量的用法：聲明與宿主變量的聲明方式一樣，在數(shù)據(jù)操縱語句中，在宿主變量和指示變量之間加( : )或關(guān)鍵字indicator。EXEC SQL BEGIN DECLARE SECTIONint prof_no;char prof_name30;int salary;short name_id;shortsal_id;EXEC SQL END DECLARE SECTIONEXEC SQL select PNAME , SA

32、L into :prof_name : name_id , :salary: sal_id from PROF where PNO = prof_no ; 需要解決的幾個問題（）SQL與主語言之間操作方式的協(xié)調(diào)SQL：一次一集合。C語言：一次一記錄。游標(biāo)：在查詢結(jié)果的記錄集合中移動的指針。若一個SQL語句返回單個元組，則不用游標(biāo)。若一個SQL語句返回多個元組，則使用游標(biāo)。不需要游標(biāo)的數(shù)據(jù)操作結(jié)果是一個元組的select語句 EXEC SQL select PNAME , SAL into :prof_name : name_id , :salary: sal_id from PROF where PNO = prof_no ;需要解決的幾個問題（）insert語句 EXEC SQL i