數(shù)據(jù)庫系統(tǒng)原理及應用-第三章-關系DB的標準語言SQLPPT課件.ppt

1、 第三章 關系DB的標準語言SQLSQL的數(shù)據(jù)定義：基本表,視圖,索引SQL的數(shù)據(jù)查詢（重點）SQL的數(shù)據(jù)更新內容：第一節(jié)SQL（Structured Query Language）基本簡介 SQL起源于1974年，作為關系DB的標準語言，用于： ORACLE，SQL/DB，DB2等中。標準版本：SQL89SQL2（92年）SQL3（正在修改）。一、SQL的特點集DB定義，操縱，控制功能于一體。高度非過程化。面向集合的操作方式。提供兩種用方式：交互式和嵌入式。語言簡潔，易學易用。二、SQL結構 基本表（Base Table） 關系模式支持DB的三級模式的結構： 視圖（View） 子模式 存儲文

2、件（Stored File）存儲模式其中：元組稱為行（row），屬性稱為列（column）。（1）SQL中的基本表對應DB中的一個關系，一行對應一個元組，一列對 應一個屬性值域。（2）SQL中的視圖由基本表或視圖導出；基本表是實際存儲在DB中的表, 視圖是虛表，它的數(shù)據(jù)仍在導出的基本表中，它的定義存在數(shù)據(jù)字典中。（3）一個基本表可以跨一個或多個存儲文件，一個存儲文件也可以存放一 個或多個基本表；每一個存儲文件對應外存上一個物理文件。（4）用戶可用SQL語句對視圖和基本表作查詢等操作。在用戶來看視圖與 基本表都是關系。（5）SQL的語句既可交互使用，也可嵌套到宿主語言的程序中使用。 結構要點：三

3、、SQL的構成數(shù)據(jù)定義（SQL DDL）：用于定義基本表、視圖、索引等。 數(shù)據(jù)操縱（SQL DML）：用于對DB的查詢和更新等操作。 數(shù)據(jù)控制：包括對基本表和視圖的授權， 完整性規(guī)則的 描述、事務控制等。嵌入式SQL的使用規(guī)定。4個部分構成：第二節(jié) SQL的數(shù)據(jù)定義包括：定義和撤消基本表（table）視圖（view）索引（index）完整性約束一、基本表的創(chuàng)建、修改和撤消1SQL提供的主要數(shù)據(jù)類型（1）數(shù)值型 （2）字符串型 INTEGER（或INT）長整型 CHAR（n）長度為n的定長字符串 SMALLINT 短整型 VARCHAR（n）具有最大長度n REAL 浮點型（取決于機器） 可變字

4、符串 FLOAT（n） 浮點型精度為n位 NCMERIC（P，d） 定點數(shù)（p位數(shù)字， 小數(shù)點后d位）（3）位串型 （4）時間型 BIT（N）長度為N的二進制的往事。DATE 日期：YYYYMMDD TIME 時間：時：分：秒HH：MM：SS算術操作僅限于數(shù)值型的數(shù)據(jù)。2基本表的創(chuàng)建，修改和撤消（1）創(chuàng)建基本表 句型：Create Table基本表名（列名 類型 列完整性約束，） 主關鍵字子（PRIMARY KEY） 其中：完整性約束定義 有三種子句： 檢查子句 （CHECK） 外關鍵字子句（FOREIGN KEY）例：教學DB中有三個關系模式： S（sno，sname，sex，age，dep

5、t） Sc（sno，cno，grade） C（cno，cname，credit） 定義其基本表。Create table s（sno CHAR（5）NOT NULL UNIQUE， sname CHAR（8）， sex CHAR（2）， age INT， dept CHAR（6） primary KEY（sno）)；Create table sc (sno CHAR (5),cno CHAR(4)，grade SMALLINT， PRIMARY KEY（sno，cno）， FOREIGN KEY（cno）REFERENCES C（cno）， FOREIGN KEY（sno）REFERENCES

6、 S（sno）， CHECK (（grade IS NULL）OR （grade BETWEEN 0 AND 100）) )；其基本表定義如下：表C定義略（2）基本表結構的修改增加新的屬性：ALTER TABLE 基本表名 ADD 列名 類型 例：ALTER TABLE S ADD PHON CHAR（8）刪除原有的屬性:ALTER TABLE 基本表名 DROP CASCADE|RESTRICT 其中：CASCADE： 所有引用該列的視圖和約束也要一起被刪除。 RESTRICT：只有視圖和約束沒有引用該屬性時，才能刪除， 否則拒絕。 例：ALTER TABLE S DROP age CASC

7、ADE；（3）基本表的撤消 句型：DROP TABLE 基本表名； 例：DROP TABLE S；三、視圖的創(chuàng)建與撤消 視圖：外模式（子模式）：由基本表或其它已建視圖構造出的表。（1）視圖的創(chuàng)建 句型：Create View 視圖名（列名表）AS SELECT 查詢子句 例：若用戶經常用到sno，sname，cname和grade信息，則建立視圖供查詢 Create View Sg（sno，sname，cname，grade） As select ssno，sname，cname，grade from s，sc，c where ssno=scsno And sc .cno=c. cno；（2）

8、視圖撤消句型：DROP View 視圖名四、索引的創(chuàng)建及撤消 建立索引的目的，加快對關系的查找。（浪費外存空間） 在基本表上可建立一個或多個索引，索引存在存儲文件中稱索引文件。句型：Create UNIQUE INDEX 索引名 ON 基本表名（列名）例：Create index SI ON S（sno）按sno升序排列 Create Unique index SI ON S（sno）；sno中值對應唯一的記錄值 Create Unique index SCI ON SC（ sno ASC，cno DESC）索引定義的結構存在數(shù)據(jù)字典庫中，學號按升序排列，CNO值按降序排列。索引的撤消：DRO

9、Pindex 索引名例：DROP index SI；第三節(jié) SQL的數(shù)據(jù)查詢數(shù)據(jù)查詢是DB中最重要，最基本的操作。查詢操作是更新操作的基礎。查詢功能的強弱直接影響DB使用性能。一、select查詢語句的句型 select目標表的屬性名或目標列表達式 from表名或視圖名 from子句 where行條件表達式 行條件子句 group by列名1having條件表達式 列條件子句 order by列名2asc|desc 排序子句執(zhí)行過程根據(jù)where子句的條件表達式，從from子句指定的基本表或視圖中找出滿足條件的元組，再按select子句中的目標列表達式，選出元組中的屬性值形成結果表。如果有gr

10、oup子句，將結果按列名1的值進行分組，列值相等的元組分為一組，每一組會產生結果表中的一條記錄。having子句將滿足條件的組給出輸出。order子句對輸出的目標表按asc升序或desc降序排列。select基本句型 select A1，A2，An （屬性名Ai） from R1，R2，Rn（關系名或視圖名Ri）A1,A2,An (F (R1R2Rn) where F （邏輯表達式F）其中：F可使用以下運算符算術比較符：，=，=，或！=邏輯符：AND，OR，NOT集合運算符：union（并），intersect（交），except（差），IN（屬于）謂詞：exists（存在量詞），all，an

11、y(some)，unique（唯一），LIKEF，中還可以是select子句（嵌套）使用的函數(shù)：avg（列名）：求平均值 min（列名）：求最小值 max（列名）：求最大值 sum（列名）：求和 count（列名）：求列中值的個數(shù) count（*）：求元組個數(shù)二、SELSCT語句的使用 單表查詢 多表關聯(lián)查詢（嵌套查詢） 集合查詢1、單表查詢例：教學DB三個基本表： s（sno，sname，sex，age，dept）：學號，姓名，性別，年齡，系名 c（cno，cname，pno，credit） ：課號，課名，先修課名，學分 sc（sno，cno，grade） ：學號，課號，成績列上查詢例1：查

12、詢學生的學號，姓名和系名 select sno，sname，dept from s；例2：查詢全部學生的詳細情況 select * from s；例3：查詢全部學生的姓名及出生年份 select sname，2003-age from s；結果：sname 2003-age 李一 1981 劉二 1982 王三 1983例4：查詢全部學生的姓名，出生年份和系名要求：指定別名改變查詢結果的列標題select sname NAME，2003-age BIRTHDAY，ISLOWER（dept） DEPARTMENTfrom s；結果：NAME BIRTHDAY DEPARTMENT李一 1981

13、cs劉二 1982 is王三 1983 ma行上查詢例1：查詢所有選修課程的學生的學號。Select distinct sno from SC; 例2：查找成績不及格的學生的學號及課號。select distinct sno ，cnofrom scwhere grade=90 AND grade3；（結果分組查詢）Sno Cno dept 1 1 IS 1 2 IS 1 3 IS 1 4 IS 2 1 IS 2 2 IS 2 3 IS 2 4 IS2、關聯(lián)查詢多個表聯(lián)接查詢等值與非等值的連接查詢a等值連接查詢： b笛卡爾積的連接 c自然連接Select s,sc select s,sc sel

14、ect sno,sname,sex,dept,cno,gradefrom s,sc from s,sc from s,sc where ssno=sc sno where s sno=sc snoSnosnamesexagedept200012000220003王一劉二李三男女男191820CSISMA設：SnoCno grade2000120001200012000220002123238085908090SSCa、等值連接結果：SSnoSnamesexagedeptSCSnoCnograde2000120001200012000220002王一王一王一劉二劉二男男男女女1919191818

15、CSCSCSISIS2000120001200012000220002123238085908090b、笛卡爾積連接結果產生：35=15個元組，產生無意義的元組，因此， 該連接很少使用。c、自然連接的結果，只是去掉重復的sno. sno sname sex dept cno grade 自身連接例：查找至少選擇選修課程號為“1”號和為“2”號的學生的學號。select xsno from SC x, SC ywhere xsno=ysno AND xcno=1 AND ycno=2;結果：xsno 20001外連接 為外連接符（或+）：它可將一些為空值列連接到結果表中。將中C改寫為外連接sel

16、ect sno, sname, sex, dept, cno,gradefrom s, scwhere ssno=scsno();注：*出現(xiàn)連接符的右邊稱左外連接 *出現(xiàn)連接符的左邊稱右外連接snosnamesexdeptcnograde200012000120001200022000220003王一王一王一劉二劉二李三男 男男女女男CSCSCSISISMA123238085908090結果：嵌套結構的查詢例：查詢選修課程號為“2”的學生姓名與學號a. Select ssno, ssname （涉及表s和sc連接查找）： from s,sc s(sno, sname, sex, age, de

17、pt) where ssno=scsno AND cno=2; sc(sno, cno, grade)b. 寫法：Select sno, sname （嵌套查詢結構） from s where sno IN(select sno from sc where cno=2）;注：嵌套結構層次分明，具有結構化程序設計特點 嵌套結構比不嵌套結構查詢效率高（因先作選擇） 子查詢中不能出現(xiàn)order by子句，即不能針對子查詢排序。c.寫法（使用存在量詞的嵌套查詢） select sno, sname from s where EXISTS (select from SC where SCSno=SSno

18、 AND Cno=2);例：查找選修了課程名為“信息系統(tǒng)”的學生學號與姓名 select sno, sname from s where sno IN (select sno from sc where cno IN (select cno from c where(cname=信息系統(tǒng));涉及：三個表：s(sno, sname, sex, age, dept) sc(sno, cno, grade) c(cno, cname, pno, credit)例：查找其他系中比IS系任何學生年齡小的學生名單 select sname, age from s where ageALL (select

19、age from s where dept=IS) AND deptIS Order by age DESC;例：查詢選修了全部課程的學生姓名Select snamefrom swhere NOT EXISTS(select from c where NOT EXISTS (select from sc where sc.sno=ssno AND sc. cno= ccno);(1)涉及s, sc, c三個表(2)SQL無全程量詞運算符，因此將題意轉化為等價的存在量詞的形式，查詢這樣的學生姓名，沒有一門課是他不選的。 (x)p(x(p)例：查找至少有一門課的成績超過學號為20002的一門課成績

20、的學生學號 select distinct sno from sc where gradesome (select grade from sc where sno=20002)3.集合查詢 例：查找計算機系的學生及年齡不大于19歲的學生 實際求計算機系的所有學生與年齡不大于19歲其它系的學生的并集 select from s where dept=cs UNION Select from s where age=19;第四節(jié) SQL的數(shù)據(jù)更新數(shù)據(jù)插入數(shù)據(jù)刪除數(shù)據(jù)修改更新包括：一、數(shù)據(jù)插入元組值的插入查詢結果的插入包括：1、元組值的插入 一般句型：INSERT INTO 基本表名（列名表) VA

21、LUES (元組值) 一次 僅插入一個元組 或 INSERT INTO 基本表名（列名表） （TABLE（元組值），（元組值），） 一次插入多個元組 如：INSERT INTO SC （TABLE（20005，1，85）， （20005，2，90）， （20005，3，95）2、查詢結果的插入 一般句型：INSERT INTO 基本表名（列名表） SELECT 查詢語句把當前表中滿足條件的元組送到已存在的表中。 如：從基本表SC中，把平均成績80的男學生的學號和平均成績存入另一個 已知的基本表SG（sno, sg1）中： INSERT INTO SG (sno, sg1) SELECT sno

22、, AVG(grade) FROM SC WHERE sno IN (SELECT sno FROM S WHERE SEX=男) GROUP BY sno HAVING AVG (grade) 80;二、數(shù)據(jù)的刪除 一般句型：DELETE FROM 基本表名 WHERE 條件表達式 從一個基本表中刪除滿足條件的元組例：刪除SC中1號課程中小于該課程平均成績的成績元組 DELETE FROM SC WHERE cno=1AND grade(SELECT AVG (grade) FROM SC WHERE cno=1);三、數(shù)據(jù)的修改 修改基本表中元組的某些列值 一般句型：UPDATE 基本表名

23、 SET 列名=值表達式，列名=值表達式， WHERE 條件表達式例：將女同學的成績提高10% UPDATE SC SET grade=grade11 WHERE sno IN (select sno from s where sex=女） 四、視圖的更新操作1、視圖的概念視圖從一個或多個基本表（或已定義的視圖）導出的表，提供給用戶查詢DB中數(shù)據(jù)使用。視圖是一個虛表，它的框架定義的屬性存于數(shù)據(jù)字典庫中，其數(shù)據(jù)仍在原來基本表中。視圖的查詢與基本表的查詢使用select語句。視圖的作用：（1）視圖能簡化用戶的操作。 通過視圖可以從一個基本表或多個基本表中抽取用戶感興趣的數(shù)據(jù)，方便 用戶處理。（2）

24、視圖對重構DB提供了一定程度的邏輯獨立性。例：將表s(sno, sname, sex, age, dept)垂直劃分為兩個表： sx(sno, sname, dept) sy(sno, sex, age)若建立一個視圖s: Create view s(sno, sname, sex, age, dept) as select sxsno, sxsname, sysex, syage, sxdept from sx, sy where sxsno=sysno;這樣盡管DB邏輯結構改變了，但應用程序不必修改，因為新建立的視圖定義了用戶原來的關系，使用戶的外模式保持不變。(3) 視圖能對保密數(shù)據(jù)提供

25、保護。2、視圖的更新操作視圖是不實際存儲數(shù)據(jù)的虛表，因此：對視圖的更新，最終要轉換為對基本表的更新。必須對視圖的更新操作作一些限制（查詢不限制）。視圖更新的三條規(guī)則：（1）不允許對從多個基本表作連接操作導出的視圖作更新操作。（2）不允許對使用了分組（group by）和函數(shù)（如：MIN等）操作導出的視圖作更新。（3）如果視圖從單個基本表，使用選擇、投影操作導出的，并包含了基本表的主關鍵字或某個候選關鍵字，那么這樣的視圖稱為“行列子集視圖”，可作更新操作。例：從s表中定義一個有關男同學的視圖 Create view S1( sno,snam,age) as select sno,sname,ag

26、e from S where sex=男;該視圖從單基本表導出，且包含關鍵字sno,可以更新： 執(zhí)行插入操作：INSERT INTO S1 VALUES（20006，王五，20）；例：定義視圖 Create view SSG(sno, cnum, Avgg) as select sno,count(sno),Avg(grade) from sc where grade IS NOT NULL Group by sno;SSG由分組和函數(shù)操作導出，據(jù)（2）規(guī)則不能更新。 第五節(jié) 嵌入式SQL的使用交互式SQL：用戶在終端上以命令形式使用嵌入式SQL：在高級語言源程序中嵌入SQL語句SQL語言使用

27、形式：一、嵌入式SQL的使用規(guī)定擴充宿主語言的編譯程序功能，使之能處理SQL語句采用預處理方式處理SQL語句嵌入式SQL兩種處理方式：宿主語言語句+SQL語句預處理方式SQL語句的過程：預備處理程序宿主語言語句+函數(shù)調用宿主語言編譯程序宿主語言目標程序SQL函數(shù)定義庫SQL與宿主語言的接口SQL與宿主語言的接口是共享變量。共享變量由宿主語言程序定義，再用SQL的DECLARE語句說明，隨后SQL語句可引用這些變量。SQL2規(guī)定，SQLSTATE是一個特殊的共享變量，起著解釋SQL語句執(zhí)行狀況的作用（如報錯）。嵌入式SQL的使用規(guī)定（1）在SQL語句前使用前綴標識“EXEC SQL”“END-E

28、XEC”（后綴標識）括起來，以示區(qū)別宿主語言語句。（注：END-EXEC后綴標識在C和PASCAL語言中用“；”號表示）。（2）允許嵌入的SQL語句引用宿主語言的程序變量（稱為共享變量），但有兩條規(guī)定：a、引用時，變量前必須加冒號“：”作前綴標識，以示與數(shù)據(jù)庫中變量有區(qū)別。b、這些變量由宿主語言的程序定義，并用SQL的DECLARE語句說明。如：C語言程序中使用如下形式定義共享變量： EXEC SQL BEGIN DECLARE SECTION； Char Sno5,name9； Char SQLSTATE6； EXEC SQL END DECLARE SECTION；（3）SQL的集合處理方

29、式與宿主語言單記錄處理方式的協(xié)調 由于SQL語句處理的是記錄集合，而宿主語言語句一次只能處理一個記錄，因此需要用游標（cursor）機制，把集合操作轉換成單記錄處理方式。與游標有關的SQL語句有下列四個：a、游標定義語句：游標是與某一查詢結果相聯(lián)系的符號名。 定義形式：EXEC SQL DECLARE 游標名CURSOR FOR SELECT語句 END-EXECb、游標打開語句（OPEN）：執(zhí)行游標定義中的SELECT語句，使游標（實際是指針）指向查詢結果的第一行之前。 語句型：EXEC SQL OPEN 游標名END-EXECc、游標推進語句（FETCH）：游標推進一行，將游標所指的當前行

30、的值送共享變量。 句型：EXEC SQL FETCH FROM游標名INTO變量表 END-EXEC 注：FETCH常置于宿主語言程序的循環(huán)語句中。d、游標關閉語句（CLOSE） 句型：EXEC SQL CLOSE游標名END-EXEC 注：在游標打開后，可修改和刪除游標所指的元組。二、嵌入式SQL的使用技術涉及游標機制的使用技術不涉及游標機制的使用技術方式：1、不涉及游標的SQL DML語句INSERT，DELETE和UPDATE語句加上前綴標識“EXEC SQL”可嵌入使用SELECT語句查找一個元組時，直接將該元組轉送共享變量中。如：EXEC SQL SELECT sname,age,s

31、ex/在s中尋找學生姓名、年齡和性別/ INTO :sn，:sa，:ss FROM S WHERE S#=:give；2、涉及游標的SQL DML語句a、SELECT語句使用 當查找多個元組時，則用游標機制將多個元組一次一個從DB中讀出并處理。具體過程：用游標定義語句定義一個游標與某一個SELECT語句對應。用游標打開語句（OPEN）打開，使游標指向滿足條件的元組之前。每執(zhí)行一次FECTH，游標指向下一個滿足條件的元組，并將其值送共享變量。 繼續(xù)循環(huán)。關閉語句（CLOSE）關閉游標。例：在SC基本表中查找某學生（學號由give給出）的學習成績信息 （S,C,GRADE），下面是該查詢的C語言函

32、數(shù)定義：# define NOM ! (strcmp (SQLSTATE, “02000”)void sel( ) EXEC SQL BEGIN DECLARE SECTION; Char sno5,cno6,give5; Int g; Char SQLSTATE6; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE SCX CURSOR FOR SELECT S, C, grade FROM SC WHERE S=:give; EXEC SQL OPEN SCX; While(1) EXEC SQL FETCH FROM SCX INTO :sno, :cno, :g; If (NOM) break; Printf (“%s, %s,%d”,sno,cno,g); EXEC SQL CLOSE SCX;b、游標指