1、Oracle數(shù)據(jù)庫技術(shù)基礎(chǔ)1數(shù)據(jù)庫技術(shù)基礎(chǔ)數(shù)據(jù)庫應(yīng)用系統(tǒng)的構(gòu)成硬件服務(wù)器：專用計算機，配置高端CPU、內(nèi)存條、磁盤客戶端：PC機，手機軟件操作系統(tǒng)：Unix，Linux，WindowsDBMS：Oracle，DB2，SQL Server，Access客戶端人員系統(tǒng)分析人員，數(shù)據(jù)庫設(shè)計人員，應(yīng)用程序開發(fā)人員數(shù)據(jù)庫管理員終端用戶 1-3Oracle數(shù)據(jù)庫系統(tǒng)原理-第1章 數(shù)據(jù)庫技術(shù)基礎(chǔ)2關(guān)系模型理論的發(fā)展及主要產(chǎn)品數(shù)據(jù)處理的歷史1940s科學(xué)與工程計算只能處理數(shù)字沒有大容量存儲器1950s字符發(fā)生器高速磁帶機磁盤存儲器文件管理系統(tǒng)1960s數(shù)據(jù)庫技術(shù) 2-5Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系

2、模型理論的發(fā)展及主要產(chǎn)品數(shù)據(jù)模型的三個要素數(shù)據(jù)結(jié)構(gòu)對所處理的數(shù)據(jù)經(jīng)過抽象后，呈現(xiàn)給用戶的邏輯形式數(shù)據(jù)操作對數(shù)據(jù)模型中的對象可執(zhí)行操作的集合約束條件完整性規(guī)則的集合 2-6Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品數(shù)據(jù)模型概念的提出E. F. Codd，Data Models in Database Management，1980 2-7Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品網(wǎng)狀模型和層次模型的提出網(wǎng)狀模型1964，通用電氣公司(GE，即General Electric)Charles BachmanIDS(Integrated Data Stor

3、e)Bachman因?qū)W(wǎng)狀模型的貢獻，獲得1973年度圖靈獎層次模型1961，阿波羅登月計劃1968，IBM，ICS，用于管理數(shù)目龐大的零部件1969，ICS更名為IMS/360 2-8Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Charles Bachman1924.12.11，生于Manhattan, Kansas1944.31946.2，任防空炮兵(澳大利亞，菲律賓)19461948， Michigan State College(1964年，改為MSU)，BS，機械工程19491950，University of Pennsylvania，MS，機械工程，MBA19

4、60，加入GE1963，開發(fā)出IDS1973，獲得Turing Award 2-9Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Charles Bachman 2-10Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品層次模型和網(wǎng)狀模型的貢獻和缺陷貢獻IDS是第一個具備通用功能的數(shù)據(jù)庫產(chǎn)品，開創(chuàng)了數(shù)據(jù)處理的數(shù)據(jù)庫時代。提出了數(shù)據(jù)庫的三層模式、DML與DDL語言等概念。IMS第一個提出了程序與數(shù)據(jù)分離的思想，使得數(shù)據(jù)獨立性有了很大提高。缺陷數(shù)據(jù)結(jié)構(gòu)復(fù)雜數(shù)據(jù)獨立性存在一定問題 2-11Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品數(shù)據(jù)獨立性數(shù)據(jù)的物

5、理獨立性存儲數(shù)據(jù)的文件位置發(fā)生改變，應(yīng)用程序不需要修改。數(shù)據(jù)的邏輯獨立性增加屬性，擴充功能，應(yīng)用程序不需要修改。 2-12Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Codd的建議用一種簡單的結(jié)構(gòu)存儲數(shù)據(jù)。用一種更高層次的、面向集合的語言訪問數(shù)據(jù)。不需要說明數(shù)據(jù)的物理存儲方式。 2-13Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系模型理論的提出1970年，Codd，A Relational Model of Data for Large Shared Data Banks因為對關(guān)系模型理論的貢獻，Codd獲得1981年度圖靈獎 2-14Oracle數(shù)

6、據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品E.F. Codd1923.8.19，生于英格蘭在Oxford學(xué)習(xí)數(shù)學(xué)和化學(xué)二次大戰(zhàn)中，Royal Air Force飛行員1948，去紐約加入IBM1965, CS PhD, Univ. of Michigan, Ann Arbor1970，提出關(guān)系數(shù)據(jù)模型理論1981，獲得Turing Award 2003.4.18，在佛羅里達家中因心臟病去世 2-15Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品IBMS的System R項目19731979由System R演化而來的產(chǎn)品1979，Oracle 2.01981，SQL/

7、Data1983，DB2James Gray，項目成員，因?qū)?shù)據(jù)庫系統(tǒng)和事務(wù)處理的貢獻，獲得1998年度圖靈 2-16Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品UCB的Ingres項目19731985, Michael Stonebraker與Eugene Wong由Ingres演化而來的產(chǎn)品1984，Ingres主要開發(fā)者Robert Epstein創(chuàng)立Sybase。1987.5，發(fā)布Sybase系統(tǒng)。1993，微軟購買Sybase源代碼的許可，即SQL Server。1996.8， PostgreSQL 2-17Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及

8、主要產(chǎn)品Michael Stonebraker(1943-)1965，Princeton，BS1967， MS， University of Michigan 1971，PhD，University of Michigan19712001，UCB任教2001，MIT任教2015.3，因為對現(xiàn)代數(shù)據(jù)庫系統(tǒng)的實踐和基礎(chǔ)理論貢獻，獲得2014年度圖靈獎 2-18Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Michael Stonebraker學(xué)生：Robert Epstein (founder and former VP of Engineering ofSybase)Diane

9、 Greene (co-founder and former CEO ofVMWare )Paula Hawthorn (founder of Britton-Lee, formerly VP of Engineering ofInformix) 2-19Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系定義關(guān)系是元組的集合元組是由若干列構(gòu)成的行 2-20Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系模型的三要素數(shù)據(jù)結(jié)構(gòu)：關(guān)系，即表操作方式：關(guān)系代數(shù)選擇映射并差連接約束實體完整性約束引用完整性約束 2-21Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論

10、的發(fā)展及主要產(chǎn)品幾個術(shù)語的不同叫法 2-22Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系和表的區(qū)別關(guān)系是集合，是數(shù)學(xué)概念，行之間無順序，列之間也無順序。表中的行一般會為了方便查看，以某個列排序，列也會按照習(xí)慣排序。 2-23Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系模型的特點嚴格的理論基礎(chǔ)簡單的邏輯結(jié)構(gòu)面向集合的操作語言, 與此相對的是面向過程語言 2-24Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品面向過程的數(shù)據(jù)處理方式也稱為imperative language以C語言讀取文件為例(寫入文件代碼參考本頁備注)fp=fop

11、en(e:law.dat,rb);fread(s,sizeof(struct stu),1,fp); printf(輸入要查詢的號碼：n); fflush(stdin); scanf(%d,&sn); while(!feof(fp)if(sn=s-sno) printf(%d,%s,%d,%dn, s-sno,s-sname,s-age,s-wgt);fclose(fp); 2-25Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品面向集合語言也稱為declarative languageselect * from emp where deptno=10 2-26Oracle數(shù)據(jù)

12、庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品關(guān)系型數(shù)據(jù)庫產(chǎn)品分類根據(jù)應(yīng)用的規(guī)模使用方式分為PC數(shù)據(jù)庫大型數(shù)據(jù)庫根據(jù)是否開源分為開源數(shù)據(jù)非開源數(shù)據(jù)庫 2-27Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品PC數(shù)據(jù)庫的特點本質(zhì)上是文件管理系統(tǒng)不支持的高級功能C/S結(jié)構(gòu)并發(fā)操作事務(wù)處理實時備份恢復(fù)用戶和權(quán)限管理價低、易用、少維護一般用于小數(shù)據(jù)量、單用戶系統(tǒng) 2-28Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品非開源關(guān)系型數(shù)據(jù)庫的典型產(chǎn)品PC數(shù)據(jù)庫AccessFoxPro大型數(shù)據(jù)庫Oracle：中國各電信公司計費系統(tǒng)SQL ServerDB2Sybase：鐵路

13、售票系統(tǒng)，農(nóng)行，廣州銀行Informix 2-29Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品開源關(guān)系型數(shù)據(jù)庫的典型產(chǎn)品PC數(shù)據(jù)庫SQLite：嵌入式數(shù)據(jù)庫，可以置于手機內(nèi)中型數(shù)據(jù)庫MySQL：用于論壇，微博等非關(guān)鍵領(lǐng)域PostgreSQL 2-30Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Oracle公司的早期創(chuàng)立者 2-31Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Ed Oates, Bruce Scott, Bob Miner, Larry Ellison公司成立一周年Larry Ellison1944.8.17，生于紐約，

14、成長于芝加哥University of Illinois at Urbana-Champaign ，University of Chicago (均中途退學(xué))1977，founded SDL with two partners and an investment of $2000; $1200 was his.1979，開發(fā)出Oracle數(shù)據(jù)庫2014， Forbes，3rd-wealthiest man in US，5th-wealthiest person in the world, $56.2 billion. 2-32Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品Bo

15、b Miner1941.12.23，生于伊利諾伊州1963年，畢業(yè)于UIUC，數(shù)學(xué)一直擔(dān)任Oracle公司主管技術(shù)的副總裁1994.11.11，在加州舊金山因肺癌去世2014，F(xiàn)orbes，美國最富有家庭，170位。 2-33Oracle數(shù)據(jù)庫系統(tǒng)原理-第2章 關(guān)系模型理論的發(fā)展及主要產(chǎn)品3ER模型數(shù)據(jù)庫設(shè)計在需求分析的基礎(chǔ)上順序進行的三個步驟概念設(shè)計邏輯設(shè)計物理設(shè)計 3-35Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型需求分析的主要任務(wù)通過對客戶的調(diào)查，得到數(shù)據(jù)庫應(yīng)用的下面內(nèi)容要保存的數(shù)據(jù)要完成的功能 3-36Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型數(shù)據(jù)庫設(shè)計的任務(wù)概念設(shè)計由需求分析結(jié)果

16、抽象出應(yīng)用的實體及聯(lián)系，得到ER圖。邏輯設(shè)計把ER圖轉(zhuǎn)化為表的結(jié)構(gòu)。物理設(shè)計在具體的DBMS上把表實現(xiàn)，并包括創(chuàng)建合適的索引。 3-37Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型ER圖的概念ER表示Entity Relationship，即實體聯(lián)系。是一種可以應(yīng)用于關(guān)系型數(shù)據(jù)庫(但不限于關(guān)系型)概念設(shè)計的建模方法。最早由陳品山(Peter Pin-Shan Chen)在1976年提出，是數(shù)據(jù)庫概念設(shè)計的主要方法。 3-38Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型陳品山1947，生于臺中市1968，畢業(yè)于臺灣大學(xué)，BS，EE1973，Harvard，PhD1976，發(fā)表The Entity

17、Relationship Model Toward A Unified View of Data曾在IBM，Honeywell，DEC短暫工作19741978，Assistant Professor，MIT19781983，Associate Professor，UCLA19832011，Professor，Louisiana State University當(dāng)前，CMU 3-39Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型ER模型的目的甲乙方以一種非技術(shù)的模型，交流對數(shù)據(jù)的理解，保證數(shù)據(jù)庫設(shè)計反映了企業(yè)對數(shù)據(jù)的使用方式。以直觀的形式開始數(shù)據(jù)庫設(shè)計過程。 3-40Oracle數(shù)據(jù)庫系統(tǒng)原理-第

18、3章 ER模型實體-聯(lián)系的概念實體是現(xiàn)實世界中可區(qū)別于其他對象的一個“事件”或一個“物體”。現(xiàn)實世界是由一系列的實體以及這些實體間的聯(lián)系構(gòu)成的。 3-41Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型ER圖的畫法陳方法Crows FeetUML 3-42Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型UML畫法用矩形表示實體把矩形分為上下兩部分，上半部分是實體名稱，下半部分填入實體的屬性存在聯(lián)系的兩個實體用直線連接起來，把聯(lián)系名稱放在直線上面聯(lián)系一般是有方向的，在其名稱左側(cè)或右側(cè)放置一個箭頭符號表示聯(lián)系的方向 3-43Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型ER圖示例 3-44Oracle數(shù)據(jù)庫

19、系統(tǒng)原理-第3章 ER模型聯(lián)系的映射約束映射約束是指一個實體集中的實體通過聯(lián)系可以同另一個實體集中相聯(lián)系的實體數(shù)量。 3-45Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型映射約束的種類一對一一對多多對多 3-46Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型ER圖轉(zhuǎn)化為表實體名稱即表名實體屬性即列名根據(jù)聯(lián)系的映射約束情況把聯(lián)系轉(zhuǎn)化為表 3-47Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型一對一聯(lián)系轉(zhuǎn)化為表把兩個實體及聯(lián)系的屬性合并為一個表，兩個實體的主鍵都可以選作新表的主鍵。 3-48Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型一對多聯(lián)系轉(zhuǎn)化為表把處在“一”的一方的實體的主鍵及聯(lián)系的屬性合并到“

20、多”的一方。也可以把聯(lián)系轉(zhuǎn)化為一個獨立的表，這個新表由兩個實體的主鍵及聯(lián)系的屬性構(gòu)成，多的一端的主鍵作為這個新表的主鍵。 3-49Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型多對多聯(lián)系把聯(lián)系轉(zhuǎn)化為一個獨立的表這個表由聯(lián)系的屬性及兩個實體的主鍵構(gòu)成這兩個實體的主鍵作為新表的外鍵各自指向由兩個實體轉(zhuǎn)化而成的兩個表的主鍵 3-50Oracle數(shù)據(jù)庫系統(tǒng)原理-第3章 ER模型4規(guī)范化理論范式即Normal Form，數(shù)據(jù)規(guī)范形式目的是減少數(shù)據(jù)冗余和避免數(shù)據(jù)異常 4-52Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論emp和dept表合并產(chǎn)生的問題空間浪費產(chǎn)生異常 4-53Oracle數(shù)據(jù)庫系統(tǒng)原理-第

21、4章 規(guī)范化理論Insert異常新添加員工的dept部分的細節(jié)要跟其他已有的記錄完全一致，否則會造成表中的數(shù)據(jù)彼此不一致。若一個新部門還沒有員工，則此部門的信息不能添加到表中去，此表中就查不到這些部門的信息。 4-54Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論Deletion異常如果一個部門的員工都辭職或調(diào)到其他部門，其對應(yīng)員工的記錄都會被刪除，這個部門的信息在表中也就不存在了。 4-55Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論Update異常若一個部門有多個員工，而其部門名稱發(fā)生改變，則必須更新這個部門的所有記錄的部門名稱字段，如果遺漏一個，會造成表中數(shù)據(jù)的彼此不一致。 4-56O

22、racle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論第一范式如果一個表沒有多值字段，則我們說這個表是滿足第一范式的(1NF)下面表中的author列有兩個值，不滿足1NF 4-57Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERAUTHOR7-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社薩師煊，王珊如何轉(zhuǎn)化為第一范式把多值列移出，與原表主鍵一起構(gòu)成一個新表 4-58Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論ISBNTITLEPUBDATERETAILCATEGORYPUBLISHER7

23、-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社ISBNAUTHOR7-04-007494-X薩師煊7-04-007494-X王珊第二范式如果一個表為第一范式，而且非主鍵字段完全依賴于主鍵，則此表稱為滿足第二范式。下面表的主鍵由ISBN和author列構(gòu)成，title等列只依賴于ISBN，不滿足第二范式。 4-59Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERCONTACTAUTHOR7-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社李軍薩師煊7

24、-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社李軍王珊如何轉(zhuǎn)化為第二范式把部分依賴的非主鍵字段移出，與原表所依賴的部分主鍵構(gòu)成一個新表 4-60Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERCONTACT7-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社李軍ISBNAUTHOR7-04-007494-X薩師煊7-04-007494-X王珊第三范式如果一個表滿足第二方式，并且沒有非主鍵字段傳遞依賴于主鍵字段，則我們稱這個表滿足第三范式出版社的聯(lián)系

25、人字段CONTACT顯然依賴于PUBLISHER字段，而PUBLISHER字段的值又依賴于ISBN，我們稱CONTACT字段傳遞依賴于ISBN 4-61Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論如何轉(zhuǎn)化為第三范式把存在傳遞依賴的字段從原表移出，與其依賴的字段構(gòu)成一個新表 4-62Oracle數(shù)據(jù)庫系統(tǒng)原理-第4章 規(guī)范化理論ISBNTITLEPUBDATERETAILCATEGORYPUBLISHER7-04-007494-X數(shù)據(jù)庫系統(tǒng)概論2002-1-125.10計算機高等教育出版社PUBLISHERCONTACT高等教育出版社李軍7配置和使用SQL*PlusSQL*PLus是什么Ora

26、cle提供的、用于執(zhí)行SQL命令的工具字符界面，只能輸入命令前身是Oracle 4.0的UFI1985，Oracle 5.0，改稱為SQL*Plus 7-64Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*PlusOracle提供的GUI客戶端工具SQL Developer2006-3出品可用于多種常見數(shù)據(jù)庫產(chǎn)品Oracle Enterprise Manager2001，Oracle9i加入使用B/S結(jié)構(gòu) 7-65Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus第三方GUI客戶端工具PL/SQL Developer1998， Allround Automations公司(荷

27、蘭)主要用于開發(fā)PL/SQL程序toadTool for Oracle Application Developers1995，Jim McDaniel1998-10，被Quest公司收購2012-09，Quest公司被Dell公司收購支持常見數(shù)據(jù)庫產(chǎn)品 7-66Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*PlusSQL*Plus執(zhí)行的命令種類SQL statementsPL/SQL blocksSQL*Plus internal commandsenvironment control commands such as SETenvironment monitoring command

28、s such as SHOWCommentsExternal commands prefixed by the ! char 7-67Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus開始使用SQL*Plus啟動/關(guān)閉數(shù)據(jù)庫服務(wù)C:net start oracleservicelaw12C:net stop oracleservicelaw12連接到數(shù)據(jù)庫C:sqlplus scott/tiger執(zhí)行SQL命令(行尾加分號，或換行后輸入/)SQL select * from dept; SQL select * from dep 2 / 7-68Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章

29、配置和使用SQL*Plus可用數(shù)據(jù)庫用戶系統(tǒng)用戶sys：權(quán)限最高，具備sysdba權(quán)限，可以啟動關(guān)閉數(shù)據(jù)庫，口令在建庫時指定system：在數(shù)據(jù)庫內(nèi)權(quán)限最高，具備dba角色，口令在建庫時指定測試用戶scott/tiger：用于學(xué)習(xí)和測試數(shù)據(jù)庫功能，可以刪除，可以執(zhí)行？RDBMSADMIN scott.sql創(chuàng)建(或utlsampl.sql)，執(zhí)行此腳本，會同時創(chuàng)建模擬人力資源管理的四張表：emp，dept，salgrade，bonus 7-69Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus獲得SQL*Plus幫助信息獲得SQL*Plus幫助SQL help獲得SQL*Plus幫

30、助主題SQL help index獲得指定主題的幫助SQL help show 7-70Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus常用SQL*Plus命令查看當(dāng)前用戶SQL show user查看當(dāng)前用戶下的表SQL select * from tab;修改執(zhí)行過的SQL命令SQL edit 查看指定表的結(jié)構(gòu)SQL describe dept執(zhí)行腳本文件SQL start c:test.sql 7-71Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus設(shè)置SQL*Plus環(huán)境設(shè)置每行的最大字符數(shù)SQL set linesize 300設(shè)置每頁的最大行數(shù)SQL s

31、et pagesize 300設(shè)置指定列的寬度SQL column filename format a50環(huán)境設(shè)置永久化把環(huán)境設(shè)置命令存入glogin.sql文件 7-72Oracle數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus給用戶解鎖和設(shè)置口令給scott用戶解鎖SQL connect system/oracle已連接。SQL alter user scott account unlock;修改scott用戶的口令為tigerSQL connect system/oracle已連接。SQL alter user scott identified by tiger; 7-73Oracl

32、e數(shù)據(jù)庫系統(tǒng)原理-第7章 配置和使用SQL*Plus8SQL查詢語句SQL歷史1970，Codd提出關(guān)系模型1973，IBM啟動System R項目Donald Chamberlin和Ray Boyce負責(zé)操作工具1974，DML與DDL成形稱為SEQUEL (Structured English Query Language)1980，重命名為SQLSEQUEL已被英國飛機公司Hawker Siddeley用作商標(biāo).SQL, Structured Query Language，結(jié)構(gòu)化查詢語言 8-75Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句Chamberlin(1944-)1966

33、, BS, Engineering, Harvey Mudd College1967, MS, EE, Stanford University1971, PhD, EE, Stanford University 8-76Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句SQL語言基本類型DMLselect，Update，Insert，deleteDDLcreate，drop，alter，truncate tableDCLgrant，revoke 8-77Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句Oracle對SQL語言的分類DDL語句DML語句事務(wù)控制語句會話控制語句系統(tǒng)控制語句嵌入式

34、SQL語句 8-78Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句select語句基本結(jié)構(gòu)select 指定要查詢的列名from 指定要查詢的表名where 指定查詢條件 group by 指定要分組列having 指定過濾分組的條件order by 指定排序列 8-79Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句查詢示例 8-80Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句三種常用數(shù)據(jù)類型數(shù)值number(p,s)：p為有效數(shù)字位數(shù)，s為精度字符串日期時間date：包括年、月、日、小時、分鐘、秒 8-81Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句字符集ASCII碼即A

35、merican Standard Code for Information Interchange。由早期電報編碼發(fā)展而來，使用7位編碼。主要用于處理英文字母。包括33個不可打印的控制字符和95個包括空格在內(nèi)的可打印字符。1963，ASCII碼標(biāo)準(zhǔn)由ASA(American Standards Association，即后來的ANSI)發(fā)布。 8-82Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句EBCDIC編碼即Extended Binary Coded Decimal Interchange Code使用8位編碼設(shè)計于1963至1964年主要用于IBM大型機System/360。 8-

36、83Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句不同語言的字符集不同國家和地區(qū)編制了適合存儲其語言的字符集。中文一般使用ZHS16GBK字符集。unicode字符集試圖把世界上使用所有字符統(tǒng)一到一個字符集中。 8-84Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句unicode編碼方式UTF-8字符編碼單位一個字節(jié)。UTF-8編碼方式中前128個字符為ASCII字符，且其編碼與ASCII碼相同。UTF-16字符編碼單位為兩個字節(jié)。 8-85Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句Oracle支持四種unicode字符集AL32UTF8用于ASCII平臺。一個字符使用13個字

37、節(jié)，增補字符使用4個字節(jié)。UTF8用于ASCII平臺。一個字符使用13個字節(jié)，增補字符使用6個字節(jié)。UTFE用于EBCDIC平臺。一個字符使用14個字節(jié)，增補字符使用8個字節(jié)。AL16UTF16使用雙字節(jié)編碼，增補字符使用4個字節(jié)。 8-86Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句數(shù)據(jù)庫字符集用于存儲下面數(shù)據(jù)CHAR、VARCHAR2、CLOB及 LONG的列值。表名、列名以及PL/SQL程序變量的標(biāo)識符。SQL語句和PL/SQL程序代碼。中文簡體Windows系統(tǒng)，默認ZHS16GBK字符集。推薦使用Unicode編碼方式優(yōu)先選用AL32UTF8。某些關(guān)鍵字只能使用單字節(jié)字符，AL

38、16UTF16不能用作數(shù)據(jù)庫字符集。 8-87Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句國家字符集為什么要使用國家字符集數(shù)據(jù)庫字符集未使用Unicode，數(shù)據(jù)庫中存在不屬于數(shù)據(jù)庫字符集中的字符，可以用國家字符集以Unicode編碼方式存儲這些特殊字符。如何使用國家字符集存儲字符把列的類型指定為nchar(n)或nvarchar(n)。如何設(shè)置字符集數(shù)據(jù)庫和國家字符集在建庫時指定。Oracle國家字符集支持的編碼方式AL16UTF16(默認)，UTF8 8-88Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句驗證數(shù)據(jù)庫字符集和國家字符集SQL select property_name

39、, property_value 2 from database_properties 3 where property_name like %CHARACTERSET 4 /PROPERTY_NAME PROPERTY_VALUE NLS_CHARACTERSET ZHS16GBKNLS_NCHAR_CHARACTERSET AL16UTF16 8-89Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句字符串類型-char(n)和varchar2(n)char(n)定長，若實際長度小于n，則用空格填滿n的最大值為2000單位默認為字節(jié)，也可以用char(n char)或char(n byte

40、)指定單位varchar2(n)可變長度，所占空間與實際長度相同n的最大值為4000單位默認為字節(jié)，也可以使用varchar2(n char)或varchar2(n byte)指定單位 8-90Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句字符串類型-nchar(n)與nvarchar2(n)nchar(n)n的最大值與國家字符集有關(guān)單位為字符占用空間不能超過2000字節(jié)nvarchar2(n)n的最大值與國家字符集有關(guān)單位為字符占用空間不超過4000字節(jié) 8-91Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句簡單查詢只指定表名select * from emp;指定列名select

41、ename,sal from emp使用列別名select ename as emp_name from emp附加條件select ename from emp where sal2000排序select ename from emp order by sal 8-92Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句查詢條件使用的關(guān)系運算符運算符功能=，或!=，=，betweenand大小比較in，exists子查詢中是否存在指定值like字符串模糊比較is null，is not null空值判斷and，or，not或!邏輯運算符 8-93Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語

42、句null作為條件正確語法where comm is nullwhere comm is not null錯誤語法where comm = nullwhere comm != null 8-94Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句字符串常量和字符串條件字符串常量用單引號括住-abc字符串區(qū)分大小寫字符串條件where ename=SMITH字符串模糊查詢where ename like %Awhere ename like _A% 8-95Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句查詢條件包含特殊字符包含通配符where ename like % escape 包含單引號

43、附加單引號，如：where dname like %qtext，如：where dname=qDrs，除了外，、()、! 也可以作為定界符使用 8-96Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句字符串函數(shù)|，lower/upper，lengthsubstringsubstr(job,1,5)lpad/rpadlpad(ename,10,#), rpad(ename,10,#)replacereplace(job,SALES,MARKETING)trimtrim( abc ) 8-97Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句處理日期時間數(shù)據(jù)sysdate函數(shù)獲取當(dāng)前日期時間。t

44、o_date函數(shù)或date關(guān)鍵字把字符串常量轉(zhuǎn)換為日期型常量。to_char函數(shù)把日期型列值轉(zhuǎn)換為指定格式的字符串。extract函數(shù)抽取日期型字段中的指定部分(年、月、日)。 8-98Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句日期時間數(shù)據(jù)處理示例獲取當(dāng)前日期select sysdate from dual;字符串轉(zhuǎn)化為日期值select ename from emp where hiredate=to_date(1981-12-21,yyyy-mm-dd)日期值轉(zhuǎn)化為字符串select ename, to_char(hiredate,yyyy/mm/dd) from emp抽取指定部

45、分select ename from emp where extract(month from hiredate)=5 8-99Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句null值的處理nvlnvl函數(shù)有兩個參數(shù)，若第一個參數(shù)不為空，則返回第一個參數(shù)的值，若第一個參數(shù)為null，則返回第二個參數(shù)的值。select ename,sal,comm,sal+nvl(comm,0) from emp;nvl2包括三個參數(shù)，第一個參數(shù)為存在空值的字段名稱，第二個參數(shù)為字段值不為空時所取的常量值，第三個參數(shù)為字段值為空時所取的常量值。select ename,sal,comm,sal+nvl2(c

46、omm,comm,100) from emp; 8-100Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句分類匯總常用匯總函數(shù)：max，min，count，sum，avgwhere子句：限定參與匯總的行g(shù)roup by子句：指定分組列having子句：過濾分組結(jié)果order by子句：指定排序列 8-101Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句簡單匯總練習(xí)查詢emp表的最高工資查詢emp表的最低工資查詢emp表的記錄個數(shù)查詢emp表中部門號20中的記錄個數(shù)查詢emp表中不重復(fù)的部門編號查詢emp表中20號部門的最高工資 8-102Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句

47、使用group by執(zhí)行分類匯總練習(xí)查詢emp表中每個部門的sal總和。查詢emp表中，每個部門工資超過2000的那些員工的工資總和。 8-103Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句使用having子句過濾分組匯總的結(jié)果查詢工資總額超過7000的部門編號及其工資總額，并以部門編號從小到大排序 8-104Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句各子句的處理順序執(zhí)行where子句，過濾符合條件的記錄。執(zhí)行g(shù)roup by子句，對以上結(jié)果以分組匯總。執(zhí)行having子句，對分組后的查詢結(jié)果進行過濾。執(zhí)行order by子句，對查詢結(jié)果以指定列排序。執(zhí)行select子句，顯示查

48、詢結(jié)果。 8-105Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句分組匯總的注意事項where子句置于group by之前。不能在where子句中使用分組函數(shù)。having子句一般會使用分組函數(shù)，若不需使用分組函數(shù)，則推薦使用where子句。在語法順序上，having子句可放置于group by子句之前或之后，推薦放置于group by子句之后。若未被分組函數(shù)作用，select、having和order by子句中的列只能選用group by子句中的列。 8-106Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句非相關(guān)子查詢查詢emp表中獲得最高工資的員工名稱。查詢emp表中獲得工資比平

49、均工資低的員工名稱。 8-107Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句相關(guān)子查詢查詢每個員工工資與其所在部門的平均工資之差。查詢每個部門的最高工資，要求在查詢結(jié)果中列出部門名稱。 8-108Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句不同位置的子查詢where子句select子句from子句 8-109Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句in與not in查詢有員工的部門名稱select dname from deptwhere deptno in(select distinct deptno from emp)查詢無員工的部門名稱select dname fr

50、om deptwhere deptno not in(select distinct deptno from emp) 8-110Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句若deptno存在空值時，使用not inwhere deptno not in (select deptno from emp)where deptno not in(null,10,20,30)where deptno!=null and deptno!=10 and deptno!=20 and deptno!=30where false and deptno!=10 and deptno!=20 and de

51、ptno!=30where false結(jié)論：查詢結(jié)果為空 8-111Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句deptno存在空值時，使用in的情形where deptno in (select deptno from emp) where deptno in(null,10,20,30)where deptno=null or deptno=10 or deptno=20 or deptno=30結(jié)論：查詢結(jié)果不為空 8-112Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句exists與not exists查詢有員工的部門名稱select dname from deptwhere

52、 exists(select deptno from emp where deptno=dept.deptno)查詢無員工的部門名稱select dname from deptwhere not exists(select deptno from emp where deptno=dept.deptno) 8-113Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句多表連接查詢當(dāng)查詢的數(shù)據(jù)存在于多個表時，要用到表的連接。實際工作中，會頻繁使用表連接查詢。表連接一般分為交叉連接內(nèi)連接外連接 8-114Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句交叉連接-即笛卡爾積SQL select em

53、p.ename, emp.deptno, dept.* 2 from emp, dept 3 /ENAME DEPTNO DEPTNO DNAME LOC SMITH 20 10 ACCOUNTING NEW YORKALLEN 30 10 ACCOUNTING NEW YORKWARD 30 10 ACCOUNTING NEW YORKJONES 20 10 ACCOUNTING NEW YORKMARTIN 30 10 ACCOUNTING NEW YORKBLAKE 30 10 ACCOUNTING NEW YORKCLARK 10 10 ACCOUNTING NEW YORKFORD

54、20 40 OPERATIONS BOSTONMILLER 10 40 OPERATIONS BOSTON已選擇56行。 8-115Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句交叉連接的意義表S和T分別有rS、rT行，cS、cT列交叉連接的結(jié)果為Q表Q表的列由S和T表的列合并得到，共cS+cT個列。Q表的行由S和T表的行兩兩拼接而成，共rSrT行。 8-116Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句內(nèi)連接對交叉連接附加連接條件過濾出交叉連接結(jié)果中有意義的行SQL select emp.ename, emp.deptno, dept.* 2 from emp, dept 3 wh

55、ere emp.deptno=dept.deptno 4 /ENAME DEPTNO DEPTNO DNAME LOC SMITH 20 20 RESEARCH DALLASALLEN 30 30 SALES CHICAGOWARD 30 30 SALES CHICAGO已選擇14行。 8-117Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句使用表別名SQL select e.ename, e.deptno, d.* 2 from emp e, dept d 3 where emp.deptno=dept.deptno 4 / 8-118Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句內(nèi)

56、連接練習(xí)查詢每個員工的名稱及其部門名稱。查詢每個員工的名稱及其所在地址。查詢每個員工的名稱及其工資級別。查詢每個員工的名稱及其經(jīng)理名稱。查詢工資超過其經(jīng)理的員工的名稱。 8-119Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句兩種連接標(biāo)準(zhǔn)：SQL-86與SQL-92SQL-86如前所述交叉連接-SQL-92語法形式select * from emp cross join dept;內(nèi)連接-SQL-92語法形式select e.ename,d.dname from emp e join dept don e.deptno=d.deptno 8-120Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL

57、查詢語句外連接左外連接除了把符合內(nèi)連接的數(shù)據(jù)顯示出來以外，也把left outer join左側(cè)表中不滿足連接條件的數(shù)據(jù)顯示出來。右外連接除了把符合內(nèi)連接的數(shù)據(jù)顯示出來，也把right outer join右側(cè)表中不滿足連接條件的數(shù)據(jù)顯示出來。全外連接除了把符合內(nèi)連接的數(shù)據(jù)顯示出來，也把full outer join兩側(cè)的表中不滿足條件的數(shù)據(jù)顯示出來。 8-121Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句外連接語法左外連接select e.ename,d.dnamefrom emp e left outer join dept d on e.deptno=d.deptno右外連接sel

58、ect e.ename,d.dnamefrom emp e right outer join dept d on e.deptno=d.deptno全外連接select e.ename,d.dnamefrom emp e full outer join dept d on e.deptno=d.deptno 8-122Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句外連接的簡單應(yīng)用查詢有員工的部門名稱查詢無員工的部門名稱 8-123Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句構(gòu)造復(fù)雜查詢得出平均工資最低的部門的名稱及其平均工資所處的級別第1步：查詢emp表的每個部門的平均工資。第2步：

59、得到以上三個部門的最低平均工資值。第3步：把上述查詢作為子查詢放入having子句，然后把此having子句添加到第1步查詢命令的末尾，得到其平均工資最低的行。第4步：把以上語句作為子查詢放入from子句，與salgrade表、dept表做交叉連接。第5步：分析查詢結(jié)果，構(gòu)造連接條件，過濾出有意義的行，第一個連接條件是前兩個表的deptno相等。第二個連接條件是最低平均工資值應(yīng)該在salgrade表的losal和hisal之間。第6步：在select子句中剔除不必要的列，得出查詢結(jié)果。 8-124Oracle數(shù)據(jù)庫系統(tǒng)原理-第8章 SQL查詢語句9數(shù)據(jù)修改語句DML語句示例deleteSQL

60、delete from emp where deptno=10;UpdateSQL update emp set sal=2000,comm=1200 where deptno=20;InsertSQL insert into dept(deptno,dname) values(50,HR);SQL insert into t 2 select deptno,dname from dept 3 where deptno create table t1 2 ( 3 a int primary key, 4 b int 5 ) 6 /列級約束，指定名稱SQL create table t2 2 (