Oracle從入門到精通-經(jīng)典.ppt

1、第一章,Oracle 入門,2,課程地位,.Net ,創(chuàng)建表空間的語法是：,22,Oracle 邏輯組件 3-2,create tablespace test_data datafile D:oracleoradataOracle9iuser_data.dbf size 50m autoextend on next 50m maxsize 20480m extent management local;,23,Oracle 邏輯組件 3-3,段,段是構(gòu)成表空間的邏輯存儲(chǔ)結(jié)構(gòu)，段由一組區(qū)組成。 按照段所存儲(chǔ)數(shù)據(jù)的特征，將段分為四種類型，即數(shù)據(jù)段、索引段、回退段和臨時(shí)段。,區(qū),區(qū)為段分配空間，它由連

2、續(xù)的數(shù)據(jù)塊組成。 當(dāng)段中的所有空間已完全使用時(shí)，系統(tǒng)自動(dòng)為該段分配一個(gè)新區(qū)。 區(qū)不能跨數(shù)據(jù)文件存在，只能存在于一個(gè)數(shù)據(jù)文件中。,數(shù)據(jù)塊,數(shù)據(jù)塊是Oracle服務(wù)器所能分配、讀取或?qū)懭氲淖钚〈鎯?chǔ)單元。 Oracle服務(wù)器以數(shù)據(jù)塊為單位管理數(shù)據(jù)文件的存儲(chǔ)空間。,模式,模式是對(duì)用戶所創(chuàng)建的數(shù)據(jù)庫對(duì)象的總稱。 模式對(duì)象包括表、視圖、索引、同義詞、序列、過程和程序包等。,24,Oracle 邏輯組件 3-4,25,Oracle 網(wǎng)絡(luò)配置 2-1,Oracle產(chǎn)品安裝完成后，服務(wù)器和客戶端都需要進(jìn)行網(wǎng)絡(luò)配置才能實(shí)現(xiàn)網(wǎng)絡(luò)連接。 服務(wù)器端配置監(jiān)聽器，客戶端配置網(wǎng)絡(luò)服務(wù)名。,26,Oracle 網(wǎng)絡(luò)配置 2-2

3、,服務(wù)器端監(jiān)聽器配置信息包括監(jiān)聽協(xié)議、地址及其他相關(guān)信息。 配置信息保存在名為listener.ora的文件中。在安裝服務(wù)器軟件時(shí)自動(dòng)配置一個(gè)監(jiān)聽器 客戶端的網(wǎng)絡(luò)服務(wù)名配置信息包括服務(wù)器地址、監(jiān)聽端口號(hào)和數(shù)據(jù)庫SID等，與服務(wù)器的監(jiān)聽器建立連接。配置信息保存在名為tnsnames.ora的文件中 Oracle中的 Net Configuration Assistant和Net Manager工具都能用來配置監(jiān)聽器和網(wǎng)絡(luò)服務(wù)名,27,Oracle 查詢工具,Oracle 查詢工具,SQL*Plus,iSQL*Plus,PL/SQL,Oracle 提供的工具非常容易使用。Oracle 的查詢工具包

4、括：,SQL*Plus是Oracle最常用的工具之一，用于接受和執(zhí)行SQL命令以及PL/SQL塊。,iSQL*Plus可以執(zhí)行能用SQL*Plus完成的所有任務(wù)。該工具的優(yōu)勢在于能通過瀏覽器訪問它。,PL/SQL 是 SQL 的擴(kuò)展。PL/SQL 結(jié)合了SQL語言的數(shù)據(jù)操縱能力和過程語言的流程控制能力,28,Oracle 企業(yè)管理器,Oracle 企業(yè)管理器為管理員提供了圖形化的用戶界面，并集成了管理數(shù)據(jù)庫的工具,29,Oracle 默認(rèn)用戶,只有用合法的用戶帳號(hào)才能訪問Oracle數(shù)據(jù)庫 Oracle 有幾個(gè)默認(rèn)的數(shù)據(jù)庫用戶,Oracle 默認(rèn)用戶,SYS,SYSTEM,SCOTT,數(shù)據(jù)庫中

5、所有數(shù)據(jù)字典表和視圖都存儲(chǔ)在 SYS 模式中。SYS用戶主要用來維護(hù)系統(tǒng)信息和管理實(shí)例。,SYSTEM 是默認(rèn)的系統(tǒng)管理員，該用戶擁有Oracle管理工具使用的內(nèi)部表和視圖。通常通過SYSTEM用戶管理數(shù)據(jù)庫用戶、權(quán)限和存儲(chǔ)等,SCOTT用戶是Oracle 數(shù)據(jù)庫的一個(gè)示范帳戶，在數(shù)據(jù)庫安裝時(shí)創(chuàng)建,30,創(chuàng)建新用戶,要連接到Oracle數(shù)據(jù)庫，就需要?jiǎng)?chuàng)建一個(gè)用戶帳戶 每個(gè)用戶都有一個(gè)默認(rèn)表空間和一個(gè)臨時(shí)表空間 CREATE USER命令用于創(chuàng)建新用戶,CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespac

6、e TEMPORARY TABLESPACE tablespace;,CREATE USER 命令的語法是：,CREATE USER MARTIN IDENTIFIED BY martinpwd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;,創(chuàng)建一個(gè)名稱為 martin 的用戶，其密碼為 martinpwd,31,授予權(quán)限 3-1,權(quán)限指的是執(zhí)行特定命令或訪問數(shù)據(jù)庫對(duì)象的權(quán)利 權(quán)限有兩種類型，系統(tǒng)權(quán)限和對(duì)象權(quán)限 系統(tǒng)權(quán)限允許用戶執(zhí)行某些數(shù)據(jù)庫操作，如創(chuàng)建表就是一個(gè)系統(tǒng)權(quán)限 對(duì)象權(quán)限允許用戶對(duì)數(shù)據(jù)庫對(duì)象（如表、視圖、序列等）執(zhí)行特定操作

7、角色是一組相關(guān)權(quán)限的組合，可以將權(quán)限授予角色，再把角色授予用戶，以簡化權(quán)限管理。,32,授予權(quán)限 3-2,GRANT 命令可用于為用戶分配權(quán)限或角色,GRANT CONNECT TO MARTIN;,CONNECT角色允許用戶連接至數(shù)據(jù)庫， 并創(chuàng)建數(shù)據(jù)庫對(duì)象,GRANT RESOURCE TO MARTIN;,RESOURCE角色允許用戶使用 數(shù)據(jù)庫中的存儲(chǔ)空間,GRANT CREATE SEQUENCE TO MARTIN;,此系統(tǒng)權(quán)限允許用戶在當(dāng)前模式中創(chuàng)建 序列，此權(quán)限包含在CONNECT角色中,33,授予權(quán)限 3-3,授予用戶 MARTIN 操作TEST表對(duì)象的權(quán)限,GRANT SEL

8、ECT ON TEST TO MARTIN;,允許用戶查詢 TEST 表的記錄,GRANT UPDATE ON TEST TO MARTIN;,允許用戶更新 TEST 表中的記錄,GRANT ALL ON TEST TO MARTIN;,允許用戶插入、刪除、更新和查詢 TEST 表中的記錄,34,更改和刪除用戶,ALTER USER 命令可用于更改口令,ALTER USER MARTIN IDENTIFIED BY martinpass;,修改 MARTIN 用戶的密碼,DROP USER 命令用于刪除用戶,DROP USER MARTIN CASCADE;,刪除 MARTIN 用戶模式,35

9、,用戶管理,Connect scott/tiger Connect System/sys as sysdba 斷開鏈接 disconnect 解鎖： alter user scott account unlock; 顯示當(dāng)前用戶： show user; 查看當(dāng)前用戶下所有對(duì)象： select * from tab; 跨用戶訪問： select * from scott.emp;,36,用戶管理,創(chuàng)建用戶： create user zs identified by zs; drop user zs; drop user zs cascade; 創(chuàng)建session權(quán)限： Grant 權(quán)限 to 用

10、戶 grant create session to zs; Oracle提供了n種角色： Connect Resource DBA Grant connect,resource to zs; 但是仍然不能創(chuàng)建表，是因?yàn)檫€沒有表空間的權(quán)限。（10G已經(jīng)有了?。?37,用戶管理,提示密碼失效： ALTER USER test PASSWORD expire ; 對(duì)象授權(quán)： GRANT 權(quán)限（select、update、insert、delete） ON schema.table TO 用戶 |- GRANT select ON scott.emp TO test ; |- Grant all on

11、 scott.emp to test; -將表相關(guān)的所有權(quán)限付給test |- Grant update(ename) on emp to test; 可以控制到列(還有insert),38,用戶管理,權(quán)限回收 REVOKE 權(quán)限 ON schema.table FROM 用戶 |- REVOKE select ON scott.emp FROM test ; 查看權(quán)限 select * from user_sys_privs; 權(quán)限傳遞 Grant create session to test with admin option;(可以就可以實(shí)現(xiàn)權(quán)限傳遞) Q：如果權(quán)限sys-test-te

12、st1 ，這時(shí)斷掉test的權(quán)限， test1還會(huì)有權(quán)限嗎？ A:在oracle9i是，答案是還會(huì)有。,39,用戶管理,角色 角色就是一堆權(quán)限的集合 Create role myrole; Grant create table to myrole; Drop role myrole; 刪除角色 grant unlimited tablespace to user;,40,用戶管理,grant connect,resource to user;然后，用戶包括的權(quán)限:CONNECT角色： -是授予最終用戶的典型權(quán)利，最基本的ALTER SESSION -修改會(huì)話CREATE CLUSTER -建立

13、聚簇CREATE DATABASE LINK -建立數(shù)據(jù)庫鏈接CREATE SEQUENCE -建立序列CREATE SESSION -建立會(huì)話CREATE SYNONYM -建立同義詞CREATE VIEW -建立視圖 RESOURCE角色： -是授予開發(fā)人員的CREATE CLUSTER -建立聚簇CREATE PROCEDURE -建立過程CREATE SEQUENCE -建立序列CREATE TABLE -建表CREATE TRIGGER -建立觸發(fā)器CREATE TYPE -建立類型,41,用戶管理,從dba_sys_privs里可以查到:SQL select grantee,pri

14、vilege from dba_sys_privs where grantee=RESOURCE order by privilege,42,序列,create sequence myseq start with 1 increment by 2 order cache 20 select myseq.nextval from dual select myseq.currval from dual 必須現(xiàn)有nextval才有currval.,43,序列,create sequence myseq1 start with 1 increment by 1 cycle maxvalue 3 noc

15、ache 這樣到3之后就會(huì)從1重新開始 alter sequence myseq increment by 5 不能改變當(dāng)前值，但是可以改變?cè)隽俊?44,同義詞,很方便的操作不同用戶下的對(duì)象 能使2個(gè)不同的應(yīng)用程序使用不同的名字指向同一張表 使用不同的用戶指向同一張表。,45,同義詞,create synonym userinfo for zs.userinfo(這樣創(chuàng)建的同義詞是私有的，只有創(chuàng)建者才能用) drop synonym userinfo 如果在本用戶中已有名字，再創(chuàng)建同義詞，會(huì)報(bào)錯(cuò) 名稱已由現(xiàn)有對(duì)象使用！,46,同義詞,create public synonym userinfo

16、 for zs.userinfo（這樣創(chuàng)建的同義詞才是公有的） drop public synonym userinfo 如果在本用戶中已經(jīng)有名字，再創(chuàng)建同義詞，會(huì)遵循就近一致的原則。,47,視圖,視圖以經(jīng)過定制的方式顯示來自一個(gè)或多個(gè)表的數(shù)據(jù) 視圖可以視為“虛擬表”或“存儲(chǔ)的查詢” 創(chuàng)建視圖所依據(jù)的表稱為“基表” 視圖的優(yōu)點(diǎn)有： 提供了另外一種級(jí)別的表安全性 隱藏的數(shù)據(jù)的復(fù)雜性 簡化的用戶的SQL命令 隔離基表結(jié)構(gòu)的改變 通過重命名列，從另一個(gè)角度提供數(shù)據(jù),48,視圖,create or replace view schema.view alias,alias. as subquery; d

17、esc myview; drop view myview;,49,視圖,創(chuàng)建試圖 create or replace view myuser as select * from userinfo order by id desc,50,視圖,創(chuàng)建試圖帶輸出效果 create or replace view myuser(編號(hào),名稱,成績) as select * from userinfo order by id desc,51,視圖,創(chuàng)建試圖帶輸出效果 create or replace view myuser(編號(hào),名稱,成績) as select id,username,score+20

18、from userinfo order by id desc,52,視圖,更新試圖： 可以更新試圖 可更新試圖中不能包含分組，group by子句，distinct,rownum等。 insert into myuser(編號(hào),名稱,成績) values (103,ddd,198),53,視圖,with read only 只讀試圖： create or replace view myuser(編號(hào),名稱,成績) as select id,username,score from userinfo order by id desc with read only,54,聯(lián)接視圖 2-1,Stud_

19、details,Sub_details,CREATE VIEW Stud_sub_view AS SELECT Studno, Studname, Submrks, Subname FROM Stud_details, Sub_Details WHERE Stud_details.Subno=Sub_details.Subno;,Stud_sub_view,聯(lián)接視圖,55,視圖中可以使用單行函數(shù)、分組函數(shù)和表達(dá)式 使用DROP VIEW語句刪除視圖,視圖中的函數(shù),CREATE VIEW item_view AS SELECT itemcode, LOWER(itemdesc) item_des

20、c FROM itemfile;,SQL DROP VIEW toys_view;,56,約束的主要功能是保證表中的數(shù)據(jù)合法性，按照約束的分類，一共有五種約束， 非空約束、 唯一約束、 主鍵約束、 外鍵約束、 檢查約束、,約束,57,非空約束簡稱NK create table a(id number,name varchar(2) not null);,約束,58,唯一約束 unique UK 唯一約束指的是每一個(gè)列上的數(shù)據(jù)是不允許重復(fù)的，例如email 下面建表 create table a( id number, name varchar2(100) not null, email var

21、char2(50) unique );,約束,59,主鍵約束Primary Key PK 主鍵約束=非空約束+唯一約束， 在之前設(shè)置唯一約束的時(shí)候發(fā)現(xiàn)可以為null值，而如果使用了主鍵約束之后則不能為空，而主鍵一般作為數(shù)據(jù)的唯一的一個(gè)標(biāo)記出現(xiàn)，例如 人員ID 范例建立主鍵約束 create table a( id number primary key, name varchar2(50) not null );,約束,60,檢查約束 check CK 檢查約束指的是為表中的數(shù)據(jù)增加一些過來?xiàng)l件 設(shè)置年齡的時(shí)候范圍是：0-200; 設(shè)置性別的時(shí)候應(yīng)該是：男、女、中； 范例設(shè)置檢查約束 creat

22、e table a( id number , name varchar2(50) not null, sex varchar2(10) not null, age number(3), constraint PK_id primary key(id), constraint CK_sex check(sex in(男,女,中), constraint CK_age check(age between 0 and 150) );,約束,61,主外鍵約束 之前的四種約束都是在單張表中進(jìn)行的，而主外鍵是在兩張表中進(jìn)行的，這兩張表是存在父子關(guān)系的，也就是子表中的某個(gè)字段的取值范圍由父表所決定。例如現(xiàn)在

23、要求表示出一種關(guān)系，每一個(gè)人有多本書，應(yīng)該定義兩種數(shù)據(jù)表 member（主） book（子）,約束,62,create table member(mid number ,name varchar2(50) not null,constraint PK_mid primary key(mid);create table book (bid number ,title varchar2(50) not null,mid number,constraint PK_bid primary key(bid),constraint fk_mid foreign key(mid) references me

24、mber(mid);,約束,63,刪除父表之前，必須刪除字表 oracle之中專門提供一個(gè)強(qiáng)制性刪除表的操作，在刪除的時(shí)候加上一句 cascade constraint drop table member cascade constraint ; 或者 drop table member cascade constraint purge;,約束,64,索引 3-1,索引是與表相關(guān)的一個(gè)可選結(jié)構(gòu) 用以提高 SQL 語句執(zhí)行的性能 減少磁盤I/O 使用 CREATE INDEX 語句創(chuàng)建索引 在邏輯上和物理上都獨(dú)立于表的數(shù)據(jù) Oracle 自動(dòng)維護(hù)索引,65,索引 3-2,B*樹索引 位圖索引,6

25、6,索引 3-2,索引有各種類型，除了標(biāo)準(zhǔn)索引外，還有一些特殊類型的索引：,索引的類型,基于函數(shù)的索引,反向鍵索引,位圖索引,唯一索引,組合索引,67,索引 3-3,SQL CREATE UNIUQE | BITMAP INDEX . ON . ( | ASC | DESC, | ASC | DESC,.)TABLESPACE STORAGE LOGGING | NOLOGGINGCOMPUTE STATISTICSNOCOMPRESS | COMPRESSNOSORT | REVERSEPARTITION | GLOBAL PARTITION,68,索引 3-3,相關(guān)說明 1） UNIQUE

26、 | BITMAP：指定UNIQUE為唯一值索引，BITMAP為位圖索引，省略為B-Tree索引。2） | ASC | DESC：可以對(duì)多列進(jìn)行聯(lián)合索引，當(dāng)為expression時(shí)即“基于函數(shù)的索引”3）TABLESPACE：指定存放索引的表空間(索引和原表不在一個(gè)表空間時(shí)效率更高)4）STORAGE：可進(jìn)一步設(shè)置表空間的存儲(chǔ)參數(shù)5）LOGGING | NOLOGGING：是否對(duì)索引產(chǎn)生重做日志(對(duì)大表盡量使用NOLOGGING來減少占用空間并提高效率)6）COMPUTE STATISTICS：創(chuàng)建新索引時(shí)收集統(tǒng)計(jì)信息7）NOCOMPRESS | COMPRESS：是否使用“鍵壓縮”(使用鍵壓

27、縮可以刪除一個(gè)鍵列中出現(xiàn)的重復(fù)值)8）NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲(chǔ)索引值9）PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對(duì)創(chuàng)建的索引進(jìn)行分區(qū),69,索引 3-3,SQL CREATE INDEX item_index ON itemfile (itemcode) TABLESPACE index_tbs;,創(chuàng)建標(biāo)準(zhǔn)索引,重建索引,SQL ALTER INDEX item_index REBUILD;,刪除索引,SQL DROP INDEX item_index;,70,唯一索引,SQL C

28、REATE UNIQUE INDEX item_index ON itemfile (itemcode);,唯一索引確保在定義索引的列中沒有重復(fù)值 Oracle 自動(dòng)在表的主鍵列上創(chuàng)建唯一索引 使用CREATE UNIQUE INDEX語句創(chuàng)建唯一索引,71,組合索引,SQL CREATE INDEX comp_index ON itemfile(p_category, itemrate);,組合索引是在表的多個(gè)列上創(chuàng)建的索引 索引中列的順序是任意的 如果 SQL 語句的 WHERE 子句中引用了組合索引的所有列或大多數(shù)列，則可以提高檢索速度,72,反向鍵索引反轉(zhuǎn)索引列鍵值的每個(gè)字節(jié) 通常建立

29、在值是連續(xù)增長的列上，使數(shù)據(jù)均勻地分布在整個(gè)索引上 創(chuàng)建索引時(shí)使用REVERSE關(guān)鍵字,反向鍵索引,SQL CREATE INDEX rev_index ON itemfile (itemcode) REVERSE;,SQL ALTER INDEX rev_index REBUID NOREVERSE;,73,位圖索引,SQL CREATE BITMAP INDEX bit_index ON order_master (orderno);,位圖索引適合創(chuàng)建在低基數(shù)列上 位圖索引不直接存儲(chǔ)ROWID，而是存儲(chǔ)字節(jié)位到ROWID的映射 減少響應(yīng)時(shí)間 節(jié)省空間占用,74,索引中的分區(qū),可以將索引存儲(chǔ)

30、在不同的分區(qū)中 與分區(qū)有關(guān)的索引有三種類型： 局部分區(qū)索引 在分區(qū)表上創(chuàng)建的索引，在每個(gè)表分區(qū)上創(chuàng)建獨(dú)立的索引，索引的分區(qū)范圍與表一致 全局分區(qū)索引 在分區(qū)表或非分區(qū)表上創(chuàng)建的索引，索引單獨(dú)指定分區(qū)的范圍，與表的分區(qū)范圍或是否分區(qū)無關(guān) 全局非分區(qū)索引 在分區(qū)表上創(chuàng)建的全局普通索引，索引沒有被分區(qū),75,獲取索引的信息,SQL SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;,與索引有關(guān)的數(shù)據(jù)字典視圖有： USER_INDEXES 用戶創(chuàng)建的索引的信息 USER_IND_PARTITIONS 用戶創(chuàng)建的分區(qū)索引的信息 USER_IND_COLUMNS 與索引相關(guān)的表列的信息,76,Oracle 9i 的安裝和卸載,通過運(yùn)行Oracle Universal Installer來安裝和卸載Oracle數(shù)據(jù)庫,選擇 Oracle 9i Database .0 來安裝數(shù)據(jù)庫服務(wù)器。,選擇 Oracle 9i Client .0 來安裝 Oracle 客戶端,啟動(dòng)Universal Installer，選擇“卸裝產(chǎn)品”，選中要卸載的組件,77,使用 Oracle 數(shù)據(jù)庫的開發(fā)流程,服務(wù)器