1、 第二講 創(chuàng)建和管理表Oracle數(shù)據(jù)庫(kù)管理系統(tǒng).課程目的創(chuàng)建表了解數(shù)據(jù)類(lèi)型修正表的定義 簡(jiǎn)單的增、刪、改、查操作4123第二講內(nèi)容Oracle體系引見(jiàn)(物理、邏輯、效力器)Oracle的數(shù)據(jù)類(lèi)型？數(shù)據(jù)表的創(chuàng)建、修正數(shù)據(jù)的修正和刪除簡(jiǎn)單查詢(xún).4ORACLE 11g體系構(gòu)造概述 在Oracle數(shù)據(jù)庫(kù)管理系統(tǒng)中有3個(gè)重要的概念需求了解，那就是實(shí)例Instance、數(shù)據(jù)庫(kù)Database和數(shù)據(jù)庫(kù)效力器Database Server。其中，實(shí)例是指一組Oracle后臺(tái)進(jìn)程以及在效力器中分配的共享內(nèi)存區(qū)域；數(shù)據(jù)庫(kù)是由基于磁盤(pán)的數(shù)據(jù)文件、控制文件、日志文件、參數(shù)文件和歸檔日志文件等組成的物理文件集合；數(shù)據(jù)

2、庫(kù)效力器是指管理數(shù)據(jù)庫(kù)的各種軟件工具比如，sqlplus、OEM等和實(shí)例及數(shù)據(jù)庫(kù)三個(gè)部分。從實(shí)例與數(shù)據(jù)庫(kù)之間的辯證關(guān)系來(lái)講，實(shí)例用于管理和控制數(shù)據(jù)庫(kù)；而數(shù)據(jù)庫(kù)為實(shí)例提供數(shù)據(jù)。一個(gè)數(shù)據(jù)庫(kù)可以被多個(gè)實(shí)例裝載和翻開(kāi)；而一個(gè)實(shí)例在其生存期內(nèi)只能裝載和翻開(kāi)一個(gè)數(shù)據(jù)庫(kù)。邏輯存儲(chǔ)構(gòu)造本講大綱：1、數(shù)據(jù)塊Data Blocks3、段Segment2、數(shù)據(jù)區(qū)Extent4、表空間TableSpace數(shù)據(jù)塊Data Blocks 數(shù)據(jù)塊是Oracle邏輯存儲(chǔ)構(gòu)造中的最小的邏輯單位，也是執(zhí)行數(shù)據(jù)庫(kù)輸入輸出操作的最小存儲(chǔ)單位。Oracle數(shù)據(jù)存放在“Oracle數(shù)據(jù)塊中，而不是“操作系統(tǒng)塊中。通常Oracle數(shù)據(jù)塊是

3、操作系統(tǒng)塊的整數(shù)倍，假設(shè)操作系統(tǒng)快的大小為2048B，并且Oracle數(shù)據(jù)塊的大小為8192B，那么表示Oracle數(shù)據(jù)塊由4個(gè)操作系統(tǒng)塊構(gòu)成。Oracle數(shù)據(jù)塊有一定的規(guī)范大小，其大小被寫(xiě)入到初始化參數(shù)DB_BlOCK_SIZE中。另外，Oracle支持在同一個(gè)數(shù)據(jù)庫(kù)中運(yùn)用多種大小的快，與規(guī)范塊大小不同的塊就是非規(guī)范塊。 數(shù)據(jù)區(qū)也可稱(chēng)作數(shù)據(jù)擴(kuò)展區(qū)是由一組延續(xù)的Oracle數(shù)據(jù)塊所構(gòu)成的Oracle存儲(chǔ)構(gòu)造，一個(gè)或多個(gè)數(shù)據(jù)塊組成一個(gè)數(shù)據(jù)區(qū)，一個(gè)或多個(gè)數(shù)據(jù)區(qū)再組成一個(gè)段Segment。當(dāng)一個(gè)段中的一切空間被運(yùn)用完后，Oracle系統(tǒng)將自動(dòng)為該段分配一個(gè)新的數(shù)據(jù)區(qū)，這也正符合Extent這個(gè)單詞所

4、具有的“擴(kuò)展的含義，可見(jiàn)數(shù)據(jù)區(qū)是Oracle存儲(chǔ)分配的最小單位，Oracle就以數(shù)據(jù)區(qū)為單位進(jìn)展存儲(chǔ)控件的擴(kuò)展。 運(yùn)用數(shù)據(jù)區(qū)的目的是用來(lái)保管特定數(shù)據(jù)類(lèi)型的數(shù)據(jù)，也是表中數(shù)據(jù)增長(zhǎng)的根本單位。在Oracle數(shù)據(jù)庫(kù)中，分配存儲(chǔ)空間就是以數(shù)據(jù)區(qū)為單位的。一個(gè)Oracle對(duì)象包含至少一個(gè)數(shù)據(jù)區(qū)。設(shè)置一個(gè)表或索引的存儲(chǔ)參數(shù)包含設(shè)置它的數(shù)據(jù)區(qū)大小。 數(shù)據(jù)區(qū)Extent段Segment 數(shù)據(jù)區(qū)也可稱(chēng)作數(shù)據(jù)擴(kuò)展區(qū)是由一組延續(xù)的Oracle數(shù)據(jù)塊所構(gòu)成的Oracle存儲(chǔ)構(gòu)造，一個(gè)或多個(gè)數(shù)據(jù)塊組成一個(gè)數(shù)據(jù)區(qū)，一個(gè)或多個(gè)數(shù)據(jù)區(qū)再組成一個(gè)段Segment。當(dāng)一個(gè)段中的一切空間被運(yùn)用完后，Oracle系統(tǒng)將自動(dòng)為該段分配

5、一個(gè)新的數(shù)據(jù)區(qū)，這也正符合Extent這個(gè)單詞所具有的“擴(kuò)展的含義，可見(jiàn)數(shù)據(jù)區(qū)是Oracle存儲(chǔ)分配的最小單位，Oracle就以數(shù)據(jù)區(qū)為單位進(jìn)展存儲(chǔ)控件的擴(kuò)展。 運(yùn)用數(shù)據(jù)區(qū)的目的是用來(lái)保管特定數(shù)據(jù)類(lèi)型的數(shù)據(jù)，也是表中數(shù)據(jù)增長(zhǎng)的根本單位。在Oracle數(shù)據(jù)庫(kù)中，分配存儲(chǔ)空間就是以數(shù)據(jù)區(qū)為單位的。一個(gè)Oracle對(duì)象包含至少一個(gè)數(shù)據(jù)區(qū)。設(shè)置一個(gè)表或索引的存儲(chǔ)參數(shù)包含設(shè)置它的數(shù)據(jù)區(qū)大小。表空間TableSpace Oracle運(yùn)用表空間將相關(guān)的邏輯構(gòu)造比如段、數(shù)據(jù)區(qū)等組合在一同，表空間是數(shù)據(jù)庫(kù)的最大邏輯劃分區(qū)域，通常用來(lái)存放數(shù)據(jù)表、索引、回滾段等數(shù)據(jù)對(duì)象即Segment，任何數(shù)據(jù)對(duì)象在創(chuàng)建時(shí)都必需被

6、指定存儲(chǔ)在某個(gè)表空間中。表空間屬邏輯存儲(chǔ)構(gòu)造與數(shù)據(jù)文件屬物理存儲(chǔ)構(gòu)造相對(duì)應(yīng)，一個(gè)表空間由一個(gè)或多個(gè)數(shù)據(jù)文件組成，一個(gè)數(shù)據(jù)文件只屬于一個(gè)表空間；Oracle數(shù)據(jù)的存儲(chǔ)空間在邏輯上表現(xiàn)為表空間，而在物理上表現(xiàn)為數(shù)據(jù)文件。舉個(gè)例子來(lái)說(shuō)，表空間相當(dāng)于操作系統(tǒng)中的文件夾，而數(shù)據(jù)文件就相當(dāng)于文件夾中的文件。每個(gè)數(shù)據(jù)庫(kù)至少有一個(gè)表空間即SYSTEM表空間，表空間的大小等于一切從屬于它的數(shù)據(jù)文件大小的總和。物理存儲(chǔ)構(gòu)造本講大綱：1、數(shù)據(jù)文件3、日志文件4、 效力器參數(shù)文件5、密碼文件、警告文件和跟蹤文件2、控制文件數(shù)據(jù)文件 數(shù)據(jù)文件是用于保管用戶(hù)運(yùn)用程序數(shù)據(jù)和Oracle系統(tǒng)內(nèi)部數(shù)據(jù)的文件，這些文件在操作系統(tǒng)

7、中就是普通的操作系統(tǒng)文件，Oracle在創(chuàng)建表空間的同時(shí)會(huì)創(chuàng)建數(shù)據(jù)文件。Oracle數(shù)據(jù)庫(kù)在邏輯上由表空間組成，每個(gè)表空間可以包含一個(gè)或多個(gè)數(shù)據(jù)文件，一個(gè)數(shù)據(jù)文件只能隸屬于一個(gè)表空間。 控制文件是一個(gè)二進(jìn)制文件，它記錄了數(shù)據(jù)庫(kù)的物理構(gòu)造，其中主要包含數(shù)據(jù)庫(kù)名、數(shù)據(jù)文件與日志文件的名字和位置、數(shù)據(jù)庫(kù)建立日期等信息。控制文件普通在Oracle系統(tǒng)安裝時(shí)或創(chuàng)建數(shù)據(jù)庫(kù)時(shí)自動(dòng)創(chuàng)建，控制文件所存放的途徑由效力器參數(shù)文件spfileorcl.ora的control_files參數(shù)值來(lái)指定。 由于控制文件存放有數(shù)據(jù)文件、日志文件等的相關(guān)信息，因此，Oracle實(shí)例在啟動(dòng)時(shí)必需訪問(wèn)控制文件。假設(shè)控制文件正常，實(shí)

8、例才干加載并翻開(kāi)數(shù)據(jù)庫(kù)；但假設(shè)控制文件中記錄了錯(cuò)誤的信息，或者實(shí)例無(wú)法找到一個(gè)可用的控制文件，那么實(shí)例無(wú)法正常啟動(dòng)?？刂莆募?日志文件的主要功能是記錄對(duì)數(shù)據(jù)所作的修正，對(duì)數(shù)據(jù)庫(kù)所作的修正幾乎都記錄在日志文件中。在出現(xiàn)問(wèn)題時(shí)，可以經(jīng)過(guò)日志文件得到原始數(shù)據(jù)，從而保證不喪失已有操作成果。Oracle的日志文件包括重做日志文件Redo Log File和歸檔日志文件Archive Log File，它們是Oracle系統(tǒng)的主要文件之一，尤其是重做日志文件，它是Oracle數(shù)據(jù)庫(kù)系統(tǒng)正常運(yùn)轉(zhuǎn)所不可或缺的。下面將引見(jiàn)這兩種日志文件。日志文件效力器參數(shù)文件 效力器參數(shù)文件SPFILEServer param

9、eter File是二進(jìn)制文件，用來(lái)記錄了Oracle數(shù)據(jù)庫(kù)的根本參數(shù)信息如，數(shù)據(jù)庫(kù)名、控制文件所在途徑、日志緩沖大小等。數(shù)據(jù)庫(kù)實(shí)例在啟動(dòng)之前，Oracle系統(tǒng)首先會(huì)讀取SPFILE參數(shù)文件中設(shè)置的這些參數(shù)，并根據(jù)這些初始化參數(shù)來(lái)配置和啟動(dòng)實(shí)例。比如，設(shè)置規(guī)范數(shù)據(jù)塊的大小即參數(shù)db_block_size的值、設(shè)置日志緩沖區(qū)的大小即參數(shù)log_buffer的值等等，所以SPFILE參數(shù)文件非常重要。效力器參數(shù)文件在安裝Oracle數(shù)據(jù)庫(kù)系統(tǒng)時(shí)由系統(tǒng)自動(dòng)創(chuàng)建，文件的稱(chēng)號(hào)為SPFILEsid.ora，sid為所創(chuàng)建的數(shù)據(jù)庫(kù)實(shí)例名。 與早期版本的初始化參數(shù)文件INITsid.ora不同的是，SPFIL

10、E中的參數(shù)由Oracle系統(tǒng)自動(dòng)維護(hù)，假設(shè)要對(duì)某些參數(shù)進(jìn)展修正，那么盡能夠不要直接對(duì)SPFILE進(jìn)展編輯，最好經(jīng)過(guò)企業(yè)管理器OEM或ALTER SYSTEM命令來(lái)修正，所修正正的參數(shù)會(huì)自動(dòng)寫(xiě)到SPFILE中。 Oracle系統(tǒng)運(yùn)轉(zhuǎn)時(shí)，除了必需的數(shù)據(jù)文件、控制文件、日志文件及效力器參數(shù)文件外，還需求一些輔助文件，如，密碼文件、警告文件和跟蹤文件，下面將對(duì)這些輔助文件進(jìn)展簡(jiǎn)單的引見(jiàn)。密碼文件、警告文件和跟蹤文件Oracle 11g效力器構(gòu)造本講大綱：1、系統(tǒng)全局區(qū)SGA2、程序全局區(qū)PGA3、前臺(tái)進(jìn)程4、后臺(tái)進(jìn)程系統(tǒng)全局區(qū)SGA 系統(tǒng)全局區(qū)System Global Area是一切用戶(hù)進(jìn)程共享的一

11、塊內(nèi)存區(qū)域，也就是說(shuō)，SGA中的數(shù)據(jù)資源可以被多個(gè)用戶(hù)進(jìn)程共同運(yùn)用。SGA主要由高速數(shù)據(jù)緩沖區(qū)、共享池、重做日志緩存區(qū)、java池和大型池等內(nèi)存構(gòu)造組成。SGA隨著數(shù)據(jù)庫(kù)實(shí)例的啟動(dòng)而加載到內(nèi)存中，當(dāng)數(shù)據(jù)庫(kù)實(shí)例封鎖時(shí)，SGA區(qū)域也就消逝了。程序全局區(qū)PGA 程序全局區(qū)Program Global Area也可稱(chēng)作用戶(hù)進(jìn)程全局區(qū)，它的內(nèi)存區(qū)在進(jìn)程私有區(qū)而不是共享區(qū)中。雖然PGA是一個(gè)全局區(qū)，可以把代碼、全局變量和數(shù)據(jù)構(gòu)造都可以存放在其中，但區(qū)域內(nèi)的資源并不像SGA一樣被一切的用戶(hù)進(jìn)程所共享，而是每個(gè)Oracle效力器進(jìn)程都只擁有屬于本人的那部分PGA資源。 在程序全局區(qū)PGA中，一個(gè)效力進(jìn)程只能訪

12、問(wèn)屬于它本人的那部分PGA資源區(qū)，各個(gè)效力進(jìn)程的PGA區(qū)的總和即為實(shí)例的PGA區(qū)的大小。通常PGA區(qū)由私有SQL區(qū)和會(huì)話區(qū)組成。前臺(tái)進(jìn)程1用戶(hù)進(jìn)程 用戶(hù)進(jìn)程是指那些可以產(chǎn)生或執(zhí)行SQL語(yǔ)句的運(yùn)用程序，無(wú)論是SQL*Plus，還是其它運(yùn)用程序，只需是能生成或執(zhí)行SQL語(yǔ)句，都被稱(chēng)作用戶(hù)進(jìn)程。2效力器進(jìn)程 效力進(jìn)程就是用于處置用戶(hù)會(huì)話過(guò)程中向數(shù)據(jù)庫(kù)實(shí)例發(fā)出的SQL語(yǔ)句或SQL*Plus命令，它可以分為公用效力器方式和共享效力器方式。后臺(tái)進(jìn)程 Oracle后臺(tái)進(jìn)程是一組運(yùn)轉(zhuǎn)于Oracle效力器端的后臺(tái)程序，是Oracle實(shí)例的重要組成部分。進(jìn)程包括DBWR、CKPT、LGWR、ARCH、SMON、P

13、MON、LCKN、RECO、DNNN、SNPN。其中SMON、PMON、DBWR、LGWR和CKPT這5個(gè)后臺(tái)進(jìn)程必需正常啟動(dòng)，否那么將導(dǎo)致數(shù)據(jù)庫(kù)實(shí)例解體。此外，還有很多輔助進(jìn)程，用于實(shí)現(xiàn)相關(guān)的輔助功能，假設(shè)這些輔助進(jìn)程發(fā)生問(wèn)題，只是某些功能遭到影響，普通不會(huì)導(dǎo)致數(shù)據(jù)庫(kù)實(shí)例解體。Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表 數(shù)據(jù)類(lèi)型 描畫(huà)VARCHAR2(size)可變長(zhǎng)字符數(shù)據(jù)CHAR(size) 定長(zhǎng)字符數(shù)據(jù)NUMBER(p,s) 可變長(zhǎng)數(shù)值數(shù)據(jù)DATE 日期型數(shù)據(jù)LONG 可變長(zhǎng)字符數(shù)據(jù)，最大可到達(dá)2GRAW 可變長(zhǎng)二進(jìn)制數(shù)據(jù)類(lèi)型，最大長(zhǎng)度4KB LONG RAW 可變長(zhǎng)二進(jìn)制數(shù)據(jù)，最大長(zhǎng)

14、度2GBCLOB字符數(shù)據(jù)，最大可到達(dá)4GBLOB二進(jìn)制數(shù)據(jù)，最大可到達(dá)4GBFILE存儲(chǔ)外部文件的二進(jìn)制數(shù)據(jù)，最大可到達(dá)4GROWID行地址.Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表字符類(lèi)型CHAR：有固定長(zhǎng)度和最大長(zhǎng)度的字符串。作為變量最大32767個(gè)字節(jié)，作為字段類(lèi)型最大為2000字節(jié)。 VARCHAR2數(shù)據(jù)類(lèi)型：可變長(zhǎng)度的字符串。作為變量最大32767個(gè)字節(jié)，作為字段類(lèi)型最大為4000字節(jié)。 Clob 大字符串類(lèi)型，長(zhǎng)度最大4GB。思索：一個(gè)空的VARCHAR2(2000)字段和一個(gè)空的VARCHAR2(2) 字段所占用的空間能否是一樣的？.Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表字

15、符類(lèi)型 比較規(guī)那么：對(duì)CHAR型數(shù)據(jù)，尾部的空格將被忽略掉，對(duì)于VARCHAR2型數(shù)據(jù)尾部帶空格的數(shù)據(jù)排序比沒(méi)有空格的要大些。比如：CHAR 型數(shù)據(jù)：YO=YO VARCHAR2 型數(shù)據(jù)：YOYO .Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表數(shù)值數(shù)據(jù)類(lèi)型NUMBER數(shù)據(jù)類(lèi)型：NUMBER(p,s)：p表示數(shù)據(jù)的總有效數(shù)字位數(shù)，s表示小數(shù)的位數(shù)。 留意：可以表示整數(shù)，也可以表示小數(shù)。number(5,2) 表示一個(gè)小數(shù)，范圍-999.99-999.99Number(5) 表示一個(gè)五位整數(shù)，范圍-99999-99999.Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表日期時(shí)間數(shù)據(jù)類(lèi)型DATE：用于存儲(chǔ)日

16、期的世紀(jì)、年、月、日、小時(shí)、分和秒。SYSDATE函數(shù)前往當(dāng)前日期和時(shí)間。TIMESTAMP：用于存儲(chǔ)日期的年、月、日以及時(shí)間的小時(shí)、分和秒。該數(shù)據(jù)類(lèi)型同時(shí)包含時(shí)區(qū)信息。SYSTIMESTAMP函數(shù)前往當(dāng)前日期、時(shí)間和時(shí)區(qū)。 其中秒的精度可以到ns。.Oracle的數(shù)據(jù)類(lèi)型第二講 創(chuàng)建和管理表LOB數(shù)據(jù)類(lèi)型“大對(duì)象數(shù)據(jù)類(lèi)型CLOBCharacter LOB：存儲(chǔ)大量字符數(shù)據(jù)，是內(nèi)部LOB。BLOBBinary LOB：存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖形、視頻剪輯和聲音剪輯等，是內(nèi)部LOB。BFILEBinary File：存儲(chǔ)二進(jìn)制文件，是外部LOB。.二、創(chuàng)建數(shù)據(jù)表必需以字母開(kāi)頭必需在 130 個(gè)

17、字符之間必需只能包含 AZ, az, 09, _, $, 和 #必需不能和用戶(hù)定義的其他對(duì)象重名必需 不能是Oracle 的保管字第二講 創(chuàng)建和管理表1、 Oracle數(shù)據(jù)表的命名規(guī)那么.二、創(chuàng)建數(shù)據(jù)表第二講 創(chuàng)建和管理表2.簡(jiǎn)單表的創(chuàng)建語(yǔ)法創(chuàng)建表CREATE TABLE schema. table_name ( column_name1 datatype, column_name2 datatype, column_name3 datatype).二、創(chuàng)建數(shù)據(jù)表第二講 創(chuàng)建和管理表2.簡(jiǎn)單表的創(chuàng)建語(yǔ)法例 創(chuàng)建stu表CREATE TABLE stu( sid varchar2(8) Prim

18、ary Key, name varchar2(20) Not Null, age number(3), addr varchar2(40);.二、創(chuàng)建數(shù)據(jù)表第二講 創(chuàng)建和管理表3.利用查詢(xún)創(chuàng)建表用查詢(xún)的結(jié)果集創(chuàng)建一張表拷貝一張表的全部信息，即建一張新表，這張表的內(nèi)容與另外存在的一張表的內(nèi)容完全一樣而表名不同。 CREATE TABLE newtablename AS SELECT * FROM existtablename;舉例：CREATE TABLE emp1 AS SELECT * FROM emp;.二、創(chuàng)建數(shù)據(jù)表第二講 創(chuàng)建和管理表？拷貝一張表，只拷貝已存在表的構(gòu)造，不拷貝表的內(nèi)容。

19、 CREATE TABLE newtablename AS SELECT * FROM existtablename WHERE 恒假條件; 舉例：CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;.三、修正表構(gòu)造第二講 創(chuàng)建和管理表修正表添加新列 ALTER TABLE table_name ADD( newcolumnname datatype);舉例：給emp2表添加新列 ALTER TABLE emp2 ADD(remark VARCHAR2(100);.三、修正表構(gòu)造第二講 創(chuàng)建和管理表更改現(xiàn)有列的數(shù)據(jù)類(lèi)型和字段寬度 ALTER TAB

20、LE tablename MODIFY( columnname datatype);舉例：修正remark列寬為200字節(jié) ALTER TABLE emp2 MODIFY(remark VARCHAR2(200);更改列名 ALTER TABLE tablename RENAME COLUMN oldcolumnname TO newcolumnname;舉例：更改emp2表中remark列名為email ALTER TABLE emp2 RENAME COLUMN remark TO ;.三、修正表構(gòu)造第二講 創(chuàng)建和管理表刪除表中現(xiàn)有列 ALTER TABLE tablename DROP

21、COLUMN columnname;舉例：刪除emp2表中的email列 ALTER TABLE emp2 DROP COLUMN ;添加或刪除完好性約束條件將某列定義為主鍵約束 ALTER TABLE emp2 ADD CONSTRAINT keyname PRIMARY KEY(columnname);舉例：將emp1表中的empno列設(shè)置為主鍵ALTER TABLE emp1 ADD CONSTRAINT PK_emp2 PRIMARY KEY(empno);.三、修正表構(gòu)造第二講 創(chuàng)建和管理表刪除主鍵或獨(dú)一鍵約束 ALTER TABLE tablename DROP CONSTRAINT keyname;舉例：刪除emp2表中的主鍵約束 ALTER TABLE emp2 DROP CONSTRAINT PK_emp2; .三、修正表構(gòu)造第二講 創(chuàng)建和管理表重命名表 RENAME oldtablename TO newtablename;舉例：將emp2表的表名修