1、模塊三：創建和管理數據表主講：馬建霞TELQ：48189407使用 Oracle 數據庫的開發流程服務器端安裝 Oracle 服務器軟件創建數據庫(安裝時自動創建)配置監聽器(安裝時自動配置)啟動Oracle實例(自動啟動服務)安裝 Oracle 客戶端軟件配置網絡服務名以新用戶登錄 Oracle提交 SQL 查詢創建新用戶并授權創建用戶表空間客戶端目 標 任 務：任務一：創建與管理表空間任務二：創建與管理用戶任務三：創建數據表任務四：管理數據表任務一：創建與管理表空間表空間（tablespace）是Oracle數據庫中最大的邏輯結構，它是數據庫的邏輯劃分，Oracl

2、e數據庫就是由一個或多個表空間組成的，一個表空間由一個或多個數據文件組成，但一個數據文件只能屬于一個表空間。所有的數據庫對象都存放在指定的表空間中。但主要存放的是表， 所以稱作表空間，默認情況下系統會自己創建一個System的表空間。 數據庫表空間1表空間2數據文件1數據文件2段1段2塊 塊 塊 擴展區1數據庫的幾個概念與寫字樓中的概念的對應關系：寫字樓中的概念數據庫中的概念概念虛、實概念邏輯、物理大樓實的數據庫物理的公司虛的表空間邏輯的占用的房間實的數據文件物理的子任務1-1：創建表空間ORACLE可以創建的表空間大致有三種類型:(1)TEMPORARY: 臨時表空間,用于臨時數據的存放;創

3、建臨時表空間的語法如下: CREATE TEMPORARY TABLESPACE SAMPLE.(2)UNDO : 還原表空間. 用于存入重做日志文件.創建還原表空間的語法如下: CREATE UNDO TABLESPACE SAMPLE.(3)用戶表空間: 最重要,也是用于存放用戶數據的表空間可以直接寫成: CREATE TABLESPACE SAMPLETEMPORARY 和 UNDO 表空間是ORACLE 管理的特殊的表空間.只用于存放系統相關數據.子任務1-1：創建表空間（續） 利用CREATE TABLESPACE命令創建和管理表空間 ,語法格式：CREATE UNDO|TEMPOR

4、ARY TABLESPACE tablespace_nameDATAFILE | TEMPFILE path/filename SIZE integer KM REUSE AUTOEXTEND OFFON NEXT integer KM MAXSIZE UNLIMITEDinteger KM MINIMUM EXTENT integer K | M DEFAULT STORAGE storage_clause ONLINEOFFLINE LOGGINGNOLOGGING PERMANENTTEMPORARY EXTENT MANAGEMENT DICTIONARYLOCAL AUTOALLOC

5、ATEUNIFORM SIZE integer KM ；子任務1-1：練習1、創建大小為50M的表空間student，禁止自動擴展數據文件。2、創建一個臨時表空間temp,文件的存放路徑自定，空間的初始大小為10M，指定允許分配給數據文件的最大磁盤空間為200M。 注意：可以用下面的語句查看表空間的信息Select * from dba_tablespaces;子任務1-1：練習下面兩段創建表空間語句：1、CREATE TEMPORARY TABLESPACE temp2 TEMPFILE F:oracletemp01.ORA SIZE 32M REUSE AUTOEXTEND ON NEXT

6、 640K ;2、CREATE TABLESPACE data LOGGING DATAFILE F:oracledata.dbf SIZE 50M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M DEFAULT STORAGE ( INITIAL 16K NEXT 32K MINEXTENTS 1 );請分別說出兩條語名的含義。3、CREATE TABLESPACE IMAGEDATALOGGINGDATAFILE E:ORACLEORADATADATA_01.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MA

7、XSIZE 3900M,E:ORACLEORADATAXLDATA_02.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MAXSIZE 3900M,E:ORACLEORADATAXLDATA_03.DBF SIZE 2000M REUSEAUTOEXTEND ON NEXT 51200K MAXSIZE 3900M,E:ORACLEORADATAXLDATA_04.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MAXSIZE 3900MEXTENT MANAGEMENT LOCAL SEGMENT SP

8、ACE MANAGEMENT AUTO子任務1-2：修改表空間語法格式：ALTER TABLESPACE tablespace_name ADD DATAFILETEMPFILE path/filename SIZE integer KM REUSE AUTOEXTEND OFFON NEXT integer KM MAXSIZE UNLIMITEDinteger KM RENAME DATAFILE pathfilename,n TO pathre_filename,n DEFAULT STORAGE storage_clause ONLINEOFFLINE NORMALTEMPORARYI

9、MMEDIATE LOGGINGNOLOGGING READ ONLYWRITE PERMANENT TEMPORARY 子任務1-2：修改表空間（續）練習1、增加表空間的容量任務要求： 通過ALTER TABLESPACE命令把一個新的數據文件添加到student表空間(文件名為student1，路徑自定)，并指定AUTOEXTEND ON和 MAXSIZE 300M。注意： 盡管可以設置MAXSIZE UNLIMITED，但應總是規定一個文件的最大尺寸值。子任務1-2：修改表空間（續）2、修改原有文件的大小增加表空間的容量任務要求： 修改Student表空間，設置每次以2M的大小自動擴展表

10、空間。注意：修改表空間的容量需要用Alter database命令。3、修改表空間的屬性 任務要求： 修改Student表空間的屬性，指定其將來的表、索引等不需要進行日志處理 。提示：用alter tablespace 命令可能直接修改表空間的屬性。 子任務1-2：修改表空間（續） 4、修改表空間的名稱任務要求： 將student表空間的名稱改為student_jrg.再改回為Student.語法格式：ALTER TABLESPACE old_name RENAME TO new_name 5、設置數據庫的默認表空間 通常情況下，數據庫的默認表空間為SYSTEM或USERS表空間。提示：可以用

11、以下的語句查看用戶的默認表空間 Select username,default_tablespace from dba_users;任務要求： 將Student表空間設置為數據庫的默認表空間。語法格式：ALTER DATABASE DEFAULT TABLESAPCE tablespace_name子任務1-3：刪除表空間1、刪除表空間test，但不刪除其文件drop tablespace test;2、. 刪除表空間data同時刪除表空間的 內容(了解)drop tablespace data including contents;3、刪除表空間data及其包含的內容以及數據文件drop t

12、ablespace data including contents and datafiles; 使用OEM創建表空間：以sys用戶，sysdba連接身份登錄OEM，出現”數據庫“頁，單擊“存儲”，點擊“表空間”，如下圖：使用OEM創建表空間：在客戶端的OEM中可以查看表空間信息，如下圖：任務二：創建與管理用戶Oracle 默認用戶只有用合法的用戶帳號才能訪問Oracle數據庫Oracle 有幾個默認的數據庫用戶 Oracle 默認用戶SYSSYSTEMSCOTT數據庫中所有數據字典表和視圖都存儲在 SYS 模式中。SYS用戶主要用來維護系統信息和管理實例。SYSTEM 是默認的系統管理員，該

13、用戶擁有Oracle管理工具使用的內部表和視圖。通常通過SYSTEM用戶管理數據庫用戶、權限和存儲等SCOTT用戶是Oracle 數據庫的一個示范帳戶，在數據庫安裝時創建子任務2-1：創建新用戶要連接到Oracle數據庫，就需要創建一個用戶帳戶每個用戶都有一個默認表空間和一個臨時表空間CREATE USER命令用于創建新用戶,語法格式如下：CREATE USER username IDENTIFIED BY passwordOR IDENTIFIED BY EXETERNALLYDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACE temptabl

14、espaceQUOTA integer KM UNLIMITED ON tablespace,QUOTA integer KM UNLIMITED ON tablespacePROFILES profile_namePASSWORD EXPIREACCOUNT LOCK or ACCOUNT UNLOCK子任務2-1：練習 1、創建用戶byxy,密碼為baiyun.2、創建一個以自己的名字命名（用拼英命名，如果有英文名的可用英文名）的用戶，密碼自定，設置默認表空間為student，臨時表空間為temp。 請用記事本寫出相應的代碼，并在SQL*Plus中運行。子任務2-1：練習（續） 3、在SQ

15、L*Plus中創建一個user1的用戶，設置密碼為123456，看能否正常運行？4、在SQL*Plus中依次執行如下的語句：create user myuser identified by welcomedefault tablespace studenttemporary tablespace temp password expire;創建完成后，以myuser用戶身份連接到數據庫，會出現什么情況？說明什么？子任務2-2：給用戶授予權限權限指的是執行特定命令或訪問數據庫對象的權利Oracle中存在兩種權限 系統權限（SYSTEM PRIVILEGE）：允許用戶在數據庫中執行指定的行為，一般可

16、以理解成比較通用的一類權限。 對象權限（OBJECT PRIVILEGE）：允許用戶訪問和操作一個指定的對象，該對象是一個確切存儲在數據庫中的命名對象。系統權限oracle包含100多種系統權限，其主要作用：執行系統端的操作，比如CREATE SESSION是登陸的權限，CREATE TABLESPACE創建表空間的權限管理某類對象，比如CREATE TABLE是用戶建表的權限管理任何對象，比如CREATE ANY TABLE，ANY關鍵字表明該權限“權力”比較大，可以管理任何用戶下的表，所以一般只有DBA來使用該權限，普通用戶是不應該擁有該類權限的。表的系統權限CREATE TABLE（建表

17、）CREATE ANY TABLE（在任何用戶下建表）ALTER ANY TABLE（修改任何用戶的表的定義）DROP ANY TABLE（刪除任何用戶的表）SELECT ANY TABLE（從任何用戶的表中查詢數據）UPDATE ANY TABLE（更改任何用戶表的數據）DELETE ANY TABLE（刪除任何用戶的表的記錄）。索引及會話系統權限索引：CREATE ANY INDEX（在任何用戶下創建索引）ALTER ANY INDEX（修改任何用戶的索引定義）DROP ANY INDEX（刪除任何用戶的索引）會話：（SESSION）CREATE SESSION（創建會話，登陸權限）ALT

18、ER SESSION（修改會話）表空間系統權限表空間CREATE TABLESPACE（創建表空間）ALTER TABLESPACE（修改表空間）DROP TABLESPACE（刪除表空間）UNLIMITED TABLESPACE（不限制任何表空間的配額）注意： 表空間的所有權限都不應該分配給普通用戶。系統特權系統特權權限SYSDBA和SYSOPERSYSOPER的權限：啟動停止數據庫，恢復數據庫等SYSDBA的權限：所有SYSOPER功能的管理權限；創建數據庫等權限。注意： 以系統特權權限登陸的用戶一般都是特權用戶，或稱為超級用戶。以SYSDBA身份登陸的用戶在ORACLE中是權限最大的用戶

19、，可以執行數據庫的所有操作。這些特權權限是不應該隨便賦予給普通用戶的。對象權限對象權限 是在指定的表、視圖、序列或過程上執行指定動作的權限或權利。每個對象都有一個特殊的可授予的權限集。對象權限的種類不是很多，但數量較大，因為具體對象的數量很多。對象權限的分類權限分類對象類型表(Table)視圖(View)序列(Sequence)存儲(Procedure)SELECT(選擇)INSERT(插入)UPDATE(更改)DELETE(刪除)ALTER(修改)INDEX(索引)REFERENCE(引用)EXECUTE(執行)子任務2-2：給用戶授予權限（續）GRANT 命令可用于為用戶分配權限或角色 G

20、RANT CONNECT TO 用戶名; CONNECT角色允許用戶連接至數據庫，并創建數據庫對象GRANT RESOURCE TO用戶名; RESOURCE角色允許用戶使用數據庫中的存儲空間GRANT CREATE SEQUENCE TO用戶名; 此系統權限允許用戶在當前模式中創建序列，此權限包含在CONNECT角色中子任務2-2：給用戶授予權限（續）授予用戶 MARTIN 操作TEST表對象的權限 GRANT SELECT ON TEST TO MARTIN; 允許用戶查詢 TEST 表的記錄GRANT UPDATE ON TEST TO MARTIN; 允許用戶更新 TEST 表中的記錄

21、GRANT ALL ON TEST TO MARTIN; 允許用戶插入、刪除、更新和查詢 TEST 表中的記錄子任務2-2：練習1、給用戶“majianxia”（自己創建的用戶名）設置權限，允許該用戶連接（connect或者create session）至數據庫，并創建數據庫對象(例如：create table,create view)。再試著以majianxia （自己創建的用戶名）連接到數據庫。區分connect 權限和create session權限。2、給用戶“majianxia”設置權限，允許該用戶插入、刪除、更新和查詢Scott.emp表中的記錄.子任務2-3：管理用戶1、修改用戶

22、密碼語法DBA可以修改任何普通用戶的密碼，而不需要知道用戶的舊密碼。在sqlplus下執行password命令來修改登陸用戶自己的密碼，提示會輸入舊密碼和新密碼。ALTER USER user IDENTIFIED BY 新密碼；子任務2-3：管理用戶（續）2、用戶狀態：OPEN、EXPIRED、LOCKED。OPEN表正常狀態，為用戶帳號初始創建后狀態。EXPIRED表示密碼過期，用戶下次登陸的時候需要修改密碼；LOCKED表示該帳戶已被鎖定，不能執行任何Oracle相關操作（即使擁有相關的權限）。狀態管理語句：ALTER USER user PASSWORD EXPIRE;-密碼過期ALT

23、ER USER user ACCOUNT LOCKUNLOCK;-帳戶鎖定/解鎖子任務2-3：管理用戶（續）3、刪除用戶語法CASCADE表示系統先自動刪除該用戶下的所有對象，然后再刪除該用戶的定義。已經登陸的用戶是不允許被刪除的。DROP USER user CASCADE回收對象權限回收對象權限語法：對象的權限會級聯回收。權限的查詢DBA_SYS_PRIVS：查詢所有的系統權限的授權情況。SESSION_PRIVS：能夠查詢出當前會話已經激活的所有系統權限。DBA_TAB_PRIVS：查詢出表的對象權限的授權情況。REVOKE 對象權限種類 ON 對象名 FROM user子任務2-3：練

24、習1、修改“user1”用戶的密碼為“byxy2011”；并用連接的方式驗證密碼修改是否成功；2、以majianxia用戶的身份連接到數據庫，再刪除“user1”用戶，看看會出現什么結果？分析原因并解決問題，并刪除user1用戶。3、回收user1用戶的權限。了解角色：角色（ROLE）的目的就是為了簡化權限的管理。 權限用戶單獨授予權限使用角色授予權限role子任務二綜合練習：1.建立新用戶user_neu 2. 給用戶user_neu授權,使其能夠登陸到數據庫，能夠查詢scott下的emp表;3.查詢用戶user_neu的權限;4.回收用戶user_neu的登陸權限;5.回收用戶user_n

25、eu的所有對象權限;6.建立角色role_neu ;7.給角色role_neu授權,使其能夠登陸到數據庫（可選） ;8.賦角色role_neu給用戶user_neu （可選） ;9.刪除角色role_neu （可選） ;10.刪除用戶user_neu （可選） ;子任務二綜合練習：1.建立新用戶user_neu create user user_neu identified by mjx default tablespace users;2. 給用戶user_neu授權,使其能夠登陸到數據庫，能夠查詢scott下的emp表;grant connect to user_neu;grant sel

26、ect on scott.emp to user_neu;3.查詢用戶user_neu的權限;select * from dba_sys_privs where grantee=MJX;Select * from session_privs;select * from dba_tab_privs where grantee=USER_NEU;4.回收用戶user_neu的登陸權限;revoke create session from user_neu;5.回收用戶user_neu的所有對象權限;revoke all on scott.emp from user_neu;6.建立角色role_n

27、eu ;Create role user_neu;7.給角色role_neu授權,使其能夠登陸到數據庫（可選） ; grant connect to role_neu;8.賦角色role_neu給用戶user_neu （可選） ; grant role_neu to user_neu;9.刪除角色role_neu （可選） ; drop role role_neu;10.刪除用戶user_neu （可選） ;Drop user user_neu;任務三：創建數據表分成兩個子任務來完成： 子任務3-1：創建數據表 子任務2：創建臨時表 子任務3-1：創建數據表任務要求：在數據庫中創建如下的tea

28、cher數據表，請用記事本寫出SQL代碼，并在SQL*Plus以你自己的名字命名的用戶登錄并執行。 表1：教師檔案表（teacher）教師編號姓名性別工作時間政治面貌學歷職稱系別1張三男1969-11-10群眾大學本科副教授經濟2郭新女1979-6-25黨員大學本科講師計算機任務三：創建數據表1、方案： 所謂方案，就是一系列數據庫對象的集合，是數據庫中存儲數據的一個邏輯表示或描述。Oracle 10g數據庫中并不是所有的數據庫對象都是方案對象，方案對象有表、索引、觸發器、數據庫鏈接、PL/SQL包、序列、同義詞、視圖、存儲過程、存儲函數等，非方案對象有表空間、用戶、角色、概要文件等。 在Ora

29、cle 10g數據庫中，每個用戶都擁有自己的方案，創建了一個用戶，就創建了一個同名的方案，方案與數據庫用戶是對應的。但在其他關系型數據庫中兩者卻沒有這種對應關系，所以方案和用戶是兩個完全不同的概念，要注意加以區分。在默認情況下，一個用戶所創建的所有數據庫對象均存儲在自己的方案中。任務三：創建數據表（續）2、表結構 表是oracle數據庫數據存儲的基本單元，通過行和列來組織數據。 字段：字段名、字段數據類型、字段長度、約束、默認值等。任務三：創建數據表（續）3、基本的數據類型（1）char(n)：定長字符串，n=1 to 2000字節，即最大長度為2K，如果不指定長度，缺省為1個字節長（一個漢字

30、為2字節）；新建一個測試表test_char.,只有一個char類型的列。長度為10 SQL create table test_char(colA char(10); （2）varchar2(n)：可變長的字符串， n=1 to 4000字節。 如果數據長度沒有達到最大值n，Oracle 會根據數據大小自動調節字段長度，如果你的數據前后有空格，Oracle 會自動將其刪去。 varchar2(n)是最常用的數據類型。 創建一個表,只有一列，類型為varchar2,長度為10 SQL create table test_varchar( col varchar2(10); 任務三：創建數據表（

31、續）3、基本的數據類型（續）（3）number(m,n) ：m=1 to 38 、n=-84 to 127， m是所有有效數字的位數，n是小數點以后的位數。如：number(5,2)，但在一行數據中的這個字段輸入575.316，則真正保存到字段中的數值是575.32。 （4）date ：該數據類型用于定義日期時間數據，長度為7B。（5）raw(n)：n=1 to 2000可變長二進制數據，在具體定義字段的時候必須指明最大長度n，Oracle 用這種格式來保存較小的圖形文件或帶格式的文本文件，如Miceosoft Word文檔。 raw是一種較老的數據類型，將來會逐漸被blob、clob、ncl

32、ob等大的對象數據類型所取代。任務三：創建數據表（續）3、基本的數據類型（續）（6）存儲大對象的數據類型：blob ：用于存儲非結構化數據，比如二進制圖像。clob：單字節字符數據，用于存儲大型的固定寬度字符數據。nclob ：用于存儲大型的，固定寬度字符集數據bfile ：用于存儲操作系統文件中的非結構化數據。大對象數據類型的列不能出現在where,group by或order by子句中。 不能在SQL*Plus等環境中查詢、顯示大對象類型的數據，也不能通過insert語句插入大對象類型的數據 常見的數據類型具體見下頁表。任務三：創建數據表（續）創建數據表的語法格式：CREATE TABL

33、E schema. table_name(column_name datatype DEFAULT expression column_constraint,n) PCTFREE integer PCTUSED integerINITRANS integerMAXTRANS integer TABLESPACE tablespace_name STORGE storage_clause CLUSTER cluster_name(cluster_column,n) LOGGING |NOLOGGING AS subquery 任務三：創建數據表（續）創建表時的命名規則和注意事項1)表名和字段名的

34、命名規則：必須以字母開頭，可以含符號A-Z,a-z,0-9,_,$,#2)大小寫不區分； 3)不用SQL里的保留字, 一定要用時可用雙引號把字符串括起來；4)用和實體或屬性相關的英文符號長度有一定的限制。子任務3-1：練習 1、要求：在數據庫中創建如下的teacher數據表，請用記事本寫出SQL代碼，并在SQL*Plus以你自己的名字命名的用戶登錄并執行。字段名稱字段類型字段大小允許空值備注teacherno8位數字必填教師編號teachername必填姓名teachersex性別worktime工作時間political政治面貌education學歷professional職稱departm

35、entno系別telephone聯系電話子任務3-1：練習（續） 2、閱讀下面的SQL語句，指出該Create Table命令創建的表的特性。create table ITEM( typeid varchar2(14) not null, type varchar22(10)tablespace USERS / ITEM這個表是在USERS表空間中創建的 pctfree 10 /用于控制空閑空間比例，更新操作比較多時，此參數應設為較高的值 initrans 1 / 指定針對同一個塊所允許的最小并發事務數目 maxtrans 255 storage ( initial 64K minextent

36、s 1 maxextents unlimited );子任務3-2：創建臨時表當緩存中間數據時候，需要創建一個臨時表，其實oracle本身在這方面就已經考慮很全了，除非有些高級應用的時候才考慮自己創建臨時表。Oracle的臨時表創建之后基本不占用表空間，如果你沒有指定臨時表（包括臨時表的索引）存放的表空的時候，你插入到臨時表的數據是存放在ORACLE系統的臨時表空間中（TEMP）。 建立臨時表語法:1、ON COMMIT DELETE ROWS 定義了建立事務級臨時表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(COUMNS )AS SELECT FR

37、OM TABLE.ON COMMIT DELETE ROWS;當前session發出commit/rollback命令，則該事務周期發生的所有數據自動被Oracle刪除(Oracle truncate table)。但不影響任何其他session的數據。建立臨時表語法:2、ON COMMIT PRESERVE ROWS 定義了創建會話級臨時表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(COUMNS )AS SELECT FROM TABLEON COMMIT PRESERVE ROWS;當前session結束(用戶正常退出 / 用戶不正常退出 / O

38、racle實例崩潰)，Oracle對這個會話的中發生的數據進行刪除(Oracle truncate table)。但不影響任何其他session的數據。子任務3-2：練習完成Word文檔中的練習。任務四：管理數據表子任務4-1：修改數據表的結構子任務4-2：重命名數據表子任務4-3：刪除數據表子任務4-4：截斷數據表子任務4-1：修改數據表的結構添加列語法：修改列語法：刪除列語法：ALTER TABLE tableDROP (columnname ,columnname); ALTER TABLE table MODIFY(columnname datatypeDEFAULT expr , columnname datatype.);ALTER