oracle第二講創(chuàng)建及管理表

第二講創(chuàng)建和管理表Oracle數(shù)據(jù)庫管理系統(tǒng)課程目標創(chuàng)建表了解數(shù)據(jù)類型修改表的定義簡單的增、刪、改、查操作4123第二講內容Oracle體系介紹(物理、邏輯、服務器)Oracle的數(shù)據(jù)類型？數(shù)據(jù)表的創(chuàng)建、修改數(shù)據(jù)的修改和刪除簡單查詢4ORACLE11g體系結構概述在Oracle數(shù)據(jù)庫管理系統(tǒng)中有3個重要的概念需要理解，那就是實例（Instance）、數(shù)據(jù)庫（Database）和數(shù)據(jù)庫服務器（DatabaseServer）。其中，實例是指一組Oracle后臺進程以及在服務器中分配的共享內存區(qū)域；數(shù)據(jù)庫是由基于磁盤的數(shù)據(jù)文件、控制文件、日志文件、參數(shù)文件和歸檔日志文件等組成的物理文件集合；數(shù)據(jù)庫服務器是指管理數(shù)據(jù)庫的各種軟件工具（比如，sqlplus、OEM等）和實例及數(shù)據(jù)庫三個部分。從實例與數(shù)據(jù)庫之間的辯證關系來講，實例用于管理和控制數(shù)據(jù)庫；而數(shù)據(jù)庫為實例提供數(shù)據(jù)。一個數(shù)據(jù)庫可以被多個實例裝載和打開；而一個實例在其生存期內只能裝載和打開一個數(shù)據(jù)庫。邏輯存儲結構本講大綱：1、數(shù)據(jù)塊（DataBlocks）3、段（Segment）2、數(shù)據(jù)區(qū)（Extent）4、表空間（TableSpace）數(shù)據(jù)塊（DataBlocks）數(shù)據(jù)塊是Oracle邏輯存儲結構中的最小的邏輯單位，也是執(zhí)行數(shù)據(jù)庫輸入輸出操作的最小存儲單位。Oracle數(shù)據(jù)存放在“Oracle數(shù)據(jù)塊”中，而不是“操作系統(tǒng)塊”中。通常Oracle數(shù)據(jù)塊是操作系統(tǒng)塊的整數(shù)倍，如果操作系統(tǒng)快的大小為2048B，并且Oracle數(shù)據(jù)塊的大小為8192B，則表示Oracle數(shù)據(jù)塊由4個操作系統(tǒng)塊構成。Oracle數(shù)據(jù)塊有一定的標準大小，其大小被寫入到初始化參數(shù)DB_BlOCK_SIZE中。另外，Oracle支持在同一個數(shù)據(jù)庫中使用多種大小的快，與標準塊大小不同的塊就是非標準塊。數(shù)據(jù)區(qū)（也可稱作數(shù)據(jù)擴展區(qū)）是由一組連續(xù)的Oracle數(shù)據(jù)塊所構成的Oracle存儲結構，一個或多個數(shù)據(jù)塊組成一個數(shù)據(jù)區(qū)，一個或多個數(shù)據(jù)區(qū)再組成一個段（Segment）。當一個段中的所有空間被使用完后，Oracle系統(tǒng)將自動為該段分配一個新的數(shù)據(jù)區(qū)，這也正符合Extent這個單詞所具有的“擴展”的含義，可見數(shù)據(jù)區(qū)是Oracle存儲分配的最小單位，Oracle就以數(shù)據(jù)區(qū)為單位進行存儲控件的擴展。使用數(shù)據(jù)區(qū)的目的是用來保存特定數(shù)據(jù)類型的數(shù)據(jù)，也是表中數(shù)據(jù)增長的基本單位。在Oracle數(shù)據(jù)庫中，分配存儲空間就是以數(shù)據(jù)區(qū)為單位的。一個Oracle對象包含至少一個數(shù)據(jù)區(qū)。設置一個表或索引的存儲參數(shù)包含設置它的數(shù)據(jù)區(qū)大小。數(shù)據(jù)區(qū)（Extent）段（Segment）數(shù)據(jù)區(qū)（也可稱作數(shù)據(jù)擴展區(qū)）是由一組連續(xù)的Oracle數(shù)據(jù)塊所構成的Oracle存儲結構，一個或多個數(shù)據(jù)塊組成一個數(shù)據(jù)區(qū)，一個或多個數(shù)據(jù)區(qū)再組成一個段（Segment）。當一個段中的所有空間被使用完后，Oracle系統(tǒng)將自動為該段分配一個新的數(shù)據(jù)區(qū)，這也正符合Extent這個單詞所具有的“擴展”的含義，可見數(shù)據(jù)區(qū)是Oracle存儲分配的最小單位，Oracle就以數(shù)據(jù)區(qū)為單位進行存儲控件的擴展。使用數(shù)據(jù)區(qū)的目的是用來保存特定數(shù)據(jù)類型的數(shù)據(jù)，也是表中數(shù)據(jù)增長的基本單位。在Oracle數(shù)據(jù)庫中，分配存儲空間就是以數(shù)據(jù)區(qū)為單位的。一個Oracle對象包含至少一個數(shù)據(jù)區(qū)。設置一個表或索引的存儲參數(shù)包含設置它的數(shù)據(jù)區(qū)大小。表空間（TableSpace）

Oracle使用表空間將相關的邏輯結構（比如段、數(shù)據(jù)區(qū)等）組合在一起，表空間是數(shù)據(jù)庫的最大邏輯劃分區(qū)域，通常用來存放數(shù)據(jù)表、索引、回滾段等數(shù)據(jù)對象（即Segment），任何數(shù)據(jù)對象在創(chuàng)建時都必須被指定存儲在某個表空間中。表空間（屬邏輯存儲結構）與數(shù)據(jù)文件（屬物理存儲結構）相對應，一個表空間由一個或多個數(shù)據(jù)文件組成，一個數(shù)據(jù)文件只屬于一個表空間；Oracle數(shù)據(jù)的存儲空間在邏輯上表現(xiàn)為表空間，而在物理上表現(xiàn)為數(shù)據(jù)文件。舉個例子來說，表空間相當于操作系統(tǒng)中的文件夾，而數(shù)據(jù)文件就相當于文件夾中的文件。每個數(shù)據(jù)庫至少有一個表空間（即SYSTEM表空間），表空間的大小等于所有從屬于它的數(shù)據(jù)文件大小的總和。物理存儲結構本講大綱：1、數(shù)據(jù)文件3、日志文件4、服務器參數(shù)文件5、密碼文件、警告文件和跟蹤文件2、控制文件數(shù)據(jù)據(jù)文文件件數(shù)據(jù)據(jù)文文件件是是用用于于保保存存用用戶戶應應用用程程序序數(shù)數(shù)據(jù)據(jù)和和Oracle系系統(tǒng)統(tǒng)內內部部數(shù)數(shù)據(jù)據(jù)的的文文件件，，這這些些文文件件在在操操作作系系統(tǒng)統(tǒng)中中就就是是普普通通的的操操作作系系統(tǒng)統(tǒng)文文件件，，Oracle在在創(chuàng)創(chuàng)建建表表空空間間的的同同時時會會創(chuàng)創(chuàng)建建數(shù)數(shù)據(jù)據(jù)文文件件。。Oracle數(shù)數(shù)據(jù)據(jù)庫庫在在邏邏輯輯上上由由表表空空間間組組成成，，每每個個表表空空間間可可以以包包含含一一個個或或多多個個數(shù)數(shù)據(jù)據(jù)文文件件，，一一個個數(shù)數(shù)據(jù)據(jù)文文件件只只能能隸隸屬屬于于一一個個表表空空間間。。控制制文文件件是是一一個個二二進進制制文文件件，，它它記記錄錄了了數(shù)數(shù)據(jù)據(jù)庫庫的的物物理理結結構構，，其其中中主主要要包包含含數(shù)數(shù)據(jù)據(jù)庫庫名名、、數(shù)數(shù)據(jù)據(jù)文文件件與與日日志志文文件件的的名名字字和和位位置置、、數(shù)數(shù)據(jù)據(jù)庫庫建建立立日日期期等等信信息息。?？乜刂浦莆奈募灰话惆阍谠贠racle系系統(tǒng)統(tǒng)安安裝裝時時或或創(chuàng)創(chuàng)建建數(shù)數(shù)據(jù)據(jù)庫庫時時自自動動創(chuàng)創(chuàng)建建，，控控制制文文件件所所存存放放的的路路徑徑由由服服務務器器參參數(shù)數(shù)文文件件spfileorcl.ora的的control_files參參數(shù)數(shù)值值來來指指定定。。由于于控控制制文文件件存存放放有有數(shù)數(shù)據(jù)據(jù)文文件件、、日日志志文文件件等等的的相相關關信信息息，，因因此此，，Oracle實實例例在在啟啟動動時時必必須須訪訪問問控控制制文文件件。。如如果果控控制制文文件件正正常常，，實實例例才才能能加加載載并并打打開開數(shù)數(shù)據(jù)據(jù)庫庫；；但但若若控控制制文文件件中中記記錄錄了了錯錯誤誤的的信信息息，，或或者者實實例例無無法法找找到到一一個個可可用用的的控控制制文文件件，，則則實實例例無無法法正正常常啟啟動動。?？刂浦莆奈募罩局疚奈募牡闹髦饕δ苣苁鞘怯浻涗涗泴?shù)數(shù)據(jù)據(jù)所所作作的的修修改改，，對對數(shù)數(shù)據(jù)據(jù)庫庫所所作作的的修修改改幾幾乎乎都都記記錄錄在在日日志志文文件件中中。。在在出出現(xiàn)現(xiàn)問問題題時時，，可可以以通通過過日日志志文文件件得得到到原原始始數(shù)數(shù)據(jù)據(jù)，，從從而而保保障障不不丟丟失失已已有有操操作作成成果果。。Oracle的日日志志文文件件包包括括重重做做日日志志文文件件（（RedoLogFile）和和歸歸檔檔日日志志文文件件（（ArchiveLogFile），，它它們們是是Oracle系統(tǒng)統(tǒng)的的主主要要文文件件之之一一，，尤尤其其是是重重做做日日志志文文件件，，它它是是Oracle數(shù)據(jù)據(jù)庫庫系系統(tǒng)統(tǒng)正正常常運運行行所所不不可可或或缺缺的的。。下下面面將將介介紹紹這這兩兩種種日日志志文文件件。。日志志文文件件服務務器器參參數(shù)數(shù)文文件件服務務器器參參數(shù)數(shù)文文件件SPFILE（（ServerparameterFile））是是二二進進制制文文件件，，用用來來記記錄錄了了Oracle數(shù)數(shù)據(jù)據(jù)庫庫的的基基本本參參數(shù)數(shù)信信息息（（如如，，數(shù)數(shù)據(jù)據(jù)庫庫名名、、控控制制文文件件所所在在路路徑徑、、日日志志緩緩沖沖大大小小等等））。。數(shù)數(shù)據(jù)據(jù)庫庫實實例例在在啟啟動動之之前前，，Oracle系系統(tǒng)統(tǒng)首首先先會會讀讀取取SPFILE參參數(shù)數(shù)文文件件中中設設置置的的這這些些參參數(shù)數(shù)，，并并根根據(jù)據(jù)這這些些初初始始化化參參數(shù)數(shù)來來配配置置和和啟啟動動實實例例。。比比如如，，設設置置標標準準數(shù)數(shù)據(jù)據(jù)塊塊的的大大小小（（即即參參數(shù)數(shù)db_block_size的的值值））、、設設置置日日志志緩緩沖沖區(qū)區(qū)的的大大小?。ǎ醇磪?shù)數(shù)log_buffer的的值值））等等等等，，所所以以SPFILE參參數(shù)數(shù)文文件件非非常常重重要要。。服服務務器器參參數(shù)數(shù)文文件件在在安安裝裝Oracle數(shù)數(shù)據(jù)據(jù)庫庫系系統(tǒng)統(tǒng)時時由由系系統(tǒng)統(tǒng)自自動動創(chuàng)創(chuàng)建建，，文文件件的的名名稱稱為為SPFILEsid.ora，，sid為為所所創(chuàng)創(chuàng)建建的的數(shù)數(shù)據(jù)據(jù)庫庫實實例例名名。。與早期版版本的初初始化參參數(shù)文件件INITsid.ora不不同的是是，SPFILE中的的參數(shù)由由Oracle系統(tǒng)自自動維護護，如果果要對某某些參數(shù)數(shù)進行修修改，則則盡可能能不要直直接對SPFILE進進行編輯輯，最好好通過企企業(yè)管理理器（OEM））或ALTERSYSTEM命令令來修改改，所修修改過的的參數(shù)會會自動寫寫到SPFILE中。。Oracle系系統(tǒng)運行行時，除除了必須須的數(shù)據(jù)據(jù)文件、、控制文文件、日日志文件件及服務務器參數(shù)數(shù)文件外外，還需需要一些些輔助文文件，如如，密碼碼文件、、警告文文件和跟跟蹤文件件，下面面將對這這些輔助助文件進進行簡單單的介紹紹。密碼文件件、警告告文件和和跟蹤文文件Oracle11g服務器結結構本講大綱綱：1、系統(tǒng)全全局區(qū)（（SGA）2、程序全局局區(qū)（PGA））3、前臺進程程4、后臺進程程系統(tǒng)全局局區(qū)（SGA）系統(tǒng)全局局區(qū)（SystemGlobalArea））是所有有用戶進進程共享享的一塊塊內存區(qū)區(qū)域，也也就是說說，SGA中的的數(shù)據(jù)資資源可以以被多個個用戶進進程共同同使用。。SGA主要由由高速數(shù)數(shù)據(jù)緩沖沖區(qū)、共共享池、、重做日日志緩存存區(qū)、java池和大大型池等等內存結結構組成成。SGA隨著著數(shù)據(jù)庫庫實例的的啟動而而加載到到內存中中，當數(shù)數(shù)據(jù)庫實實例關閉閉時，SGA區(qū)區(qū)域也就就消失了了。程序全局局區(qū)（PGA）程序全局局區(qū)（ProgramGlobalArea）也可稱稱作用戶戶進程全全局區(qū)，，它的內內存區(qū)在在進程私私有區(qū)而而不是共共享區(qū)中中。雖然然PGA是一個全全局區(qū)，，可以把把代碼、、全局變變量和數(shù)數(shù)據(jù)結構構都可以以存放在在其中，，但區(qū)域域內的資資源并不不像SGA一樣被所所有的用用戶進程程所共享享，而是是每個Oracle服務器進進程都只只擁有屬屬于自己己的那部部分PGA資源。在程序全全局區(qū)（（PGA）中，一一個服務務進程只只能訪問問屬于它它自己的的那部分分PGA資源區(qū)，，各個服服務進程程的PGA區(qū)的總和和即為實實例的PGA區(qū)的大小小。通常常PGA區(qū)由私有有SQL區(qū)和會話話區(qū)組成成。前臺進程程1．用戶進進程用戶進程程是指那那些能夠夠產(chǎn)生或或執(zhí)行SQL語句的應應用程序序，無論論是SQL*Plus，還是其其它應用用程序，，只要是是能生成成或執(zhí)行行SQL語句，都都被稱作作用戶進進程。2．服務器器進程服務進程程就是用用于處理理用戶會會話過程程中向數(shù)數(shù)據(jù)庫實實例發(fā)出出的SQL語句或SQL*Plus命令，它它可以分分為專用用服務器器模式和和共享服服務器模模式。后臺進程程Oracle后臺進程程是一組組運行于于Oracle服務器端端的后臺臺程序，，是Oracle實例的重重要組成成部分。。進程包包括DBWR、CKPT、LGWR、ARCH、SMON、PMON、LCKN、RECO、DNNN、SNPN。其中SMON、PMON、DBWR、LGWR和CKPT這5個后臺進進程必須須正常啟啟動，否否則將導導致數(shù)據(jù)據(jù)庫實例例崩潰。。此外，，還有很很多輔助助進程，，用于實實現(xiàn)相關關的輔助助功能，，如果這這些輔助助進程發(fā)發(fā)生問題題，只是是某些功功能受到到影響，，一般不不會導致致數(shù)據(jù)庫庫實例崩崩潰。Oracle的數(shù)據(jù)類類型第二講創(chuàng)創(chuàng)建建和管理理表數(shù)據(jù)類型型描述VARCHAR2(size)可變長字字符數(shù)據(jù)據(jù)CHAR(size)定長字符符數(shù)據(jù)NUMBER(p,s)可變長數(shù)數(shù)值數(shù)據(jù)據(jù)DATE日期型數(shù)數(shù)據(jù)LONG可變長字字符數(shù)據(jù)據(jù)，最大大可達到到2GRAW可變長二二進制數(shù)數(shù)據(jù)類型型，最大大長度4KBLONGRAW可變長長二進進制數(shù)數(shù)據(jù)，，最大大長度度2GBCLOB字符數(shù)數(shù)據(jù)，，最大大可達達到4GBLOB二進制制數(shù)據(jù)據(jù)，最最大可可達到到4GBFILE存儲外外部文文件的的二進制制數(shù)據(jù)據(jù)，最最大可可達到到4GROWID行地址址Oracle的數(shù)據(jù)據(jù)類型型第二講講創(chuàng)創(chuàng)建建和管管理表表字符類類型CHAR：有固固定長長度和和最大大長度度的字字符串串。作為變變量最最大32767個字節(jié)節(jié)，作作為字字段類類型最最大為為2000字節(jié)。。VARCHAR2數(shù)據(jù)類類型：：可變變長度度的字字符串串。作為變變量最最大32767個字節(jié)節(jié)，作作為字字段類類型最最大為為4000字節(jié)。。Clob大字符符串類類型，，長度度最大大4GB。思考：一個個空的的VARCHAR2(2000)字段和和一個個空的的VARCHAR2(2)字段所所占用用的空空間是是否是是一樣樣的？？Oracle的數(shù)據(jù)據(jù)類型型第二講講創(chuàng)創(chuàng)建建和管管理表表字符類類型比較規(guī)規(guī)則：：對CHAR型數(shù)據(jù)據(jù)，尾尾部的的空格格將被被忽略略掉，，對于于VARCHAR2型數(shù)據(jù)據(jù)尾部部帶空空格的的數(shù)據(jù)據(jù)排序序比沒沒有空空格的的要大大些。。比如如：CHAR型數(shù)據(jù)據(jù)：‘YO’=‘YO’’VARCHAR2型數(shù)據(jù)據(jù)：‘YO’<‘YO’’Oracle的數(shù)據(jù)據(jù)類型型第二講講創(chuàng)創(chuàng)建建和管管理表表數(shù)值數(shù)數(shù)據(jù)類類型NUMBER數(shù)據(jù)類類型：：NUMBER(p,s)：p表示數(shù)數(shù)據(jù)的的總有有效數(shù)數(shù)字位位數(shù)，，s表示小小數(shù)的的位數(shù)數(shù)。注意：：可以以表示示整數(shù)數(shù)，也也可以以表示示小數(shù)數(shù)。number(5,2)表示一一個小小數(shù)，，范圍圍-999.99---999.99Number(5)表示一一個五五位整整數(shù)，，范圍圍-99999---99999Oracle的數(shù)據(jù)據(jù)類型型第二講講創(chuàng)創(chuàng)建建和管管理表表日期時時間數(shù)數(shù)據(jù)類類型DATE：用于存存儲日日期的的世紀紀、年年、月月、日日、小小時、、分和和秒。。SYSDATE函數(shù)返返回當當前日日期和和時間間。TIMESTAMP：用于存存儲日日期的的年、、月、、日以以及時時間的的小時時、分分和秒秒。該該數(shù)據(jù)據(jù)類型型同時時包含含時區(qū)區(qū)信息息。SYSTIMESTAMP函數(shù)返返回當當前日日期、、時間間和時時區(qū)。。其中秒秒的精精度可可以到到ns。Oracle的數(shù)據(jù)據(jù)類型型第二講講創(chuàng)創(chuàng)建建和管管理表表LOB數(shù)據(jù)類類型（“大大對象象”數(shù)數(shù)據(jù)類類型））CLOB（CharacterLOB）：存儲儲大量量字符符數(shù)據(jù)據(jù)，是是內部部LOB。BLOB（BinaryLOB）：存儲較較大的的二進進制對對象，，如圖圖形、、視頻頻剪輯輯和聲聲音剪剪輯等等，是是內部部LOB。BFILE（BinaryFile）：存儲二二進制制文件件，是是外部部LOB。二、創(chuàng)創(chuàng)建數(shù)數(shù)據(jù)表表必須以以字母母開頭頭必須在在1–30個字符符之間間必須只只能包包含A–Z,a––z,0–9,_,$,和#必須不不能和和用戶戶定義義的其其他對對象重重名必須不不能能是Oracle的保留字第二講創(chuàng)創(chuàng)建和管管理表1、Oracle數(shù)據(jù)表的命命名規(guī)則二、創(chuàng)建數(shù)數(shù)據(jù)表第二講創(chuàng)創(chuàng)建和管管理表2.簡單表的創(chuàng)創(chuàng)建語法創(chuàng)建表CREATETABLE[schema.]table_name(column_name1datatype,column_name2datatype,column_name3datatype)二、創(chuàng)建數(shù)數(shù)據(jù)表第二講創(chuàng)創(chuàng)建和管管理表2.簡單表的創(chuàng)創(chuàng)建語法例創(chuàng)建stu表CREATETABLEstu(sidvarchar2(8)PrimaryKey,namevarchar2(20)NotNull,agenumber(3),addrvarchar2(40));二、創(chuàng)建數(shù)數(shù)據(jù)表第二講創(chuàng)創(chuàng)建和管管理表3.利用查詢創(chuàng)創(chuàng)建表用查詢的結結果集創(chuàng)建建一張表拷貝一張表表的全部信信息，即建建一張新表表，這張表表的內容與與另外存在在的一張表表的內容完完全相同而而表名不同同。CREATETABLEnewtablenameASSELECT*FROMexisttablename;舉例：CREATETABLEemp1ASSELECT*FROMemp;二、創(chuàng)建數(shù)數(shù)據(jù)表第二講創(chuàng)創(chuàng)建和管管理表？拷貝一張表表，只拷貝貝已存在表表的結構，，不拷貝表表的內容。。CREATETABLEnewtablenameASSELECT*FROMexisttablenameWHERE恒假條件;舉例：CREATETABLEemp2ASSELECT*FROMempWHERE1=2;三、修改表表結構第二講創(chuàng)創(chuàng)建和管管理表修改表添加新列ALTERTABLEtable_nameADD(newcolumnnamedatatype);舉例：給emp2表增加新列列ALTERTABLEemp2ADD(remarkVARCHAR2(100));三、修改表表結構第二講創(chuàng)創(chuàng)建和管管理表更改現(xiàn)有列列的數(shù)據(jù)類類型和字段段寬度ALTERTABLEtablenameMODIFY(columnnamedatatype);舉例：修改改remark列寬為200字節(jié)ALTERTABLEemp2MODIFY(remarkVARCHAR2(200));更改列名ALTERTABLEtablenameRENAMECOLUMNoldcolumnnameTOnewcolumnname;舉例：更改改emp2表中remark列名為emailALTERTABLEemp2RENAMECOLUMNremarkTOemail;三、修改表表結構第二講創(chuàng)創(chuàng)建和管管理表刪除表中現(xiàn)現(xiàn)有列ALTERTABLEtablenameDROPCOLUMNcolumnname;舉例：刪除除emp2表中的email列ALTERTABLEemp2DROPCOLUMNemail;添加或刪除除完整性約約束條件將某列定義義為主鍵約約束ALTERTABLEemp2ADDCONSTRAINTkeynamePRIMARYKEY(columnname);舉例：將emp1表中的empno列設置為主主鍵ALTERTABLEemp1ADDCONSTRAINTPK_emp2PRIMARYKEY(empno);三、修改表表結構第二講創(chuàng)創(chuàng)建和管管理表刪除主鍵或或唯一鍵約約束ALTERTABLEtablenameDROPC