Oracle簡介

Oracle數(shù)據(jù)庫是Oracle（甲骨文）公司的核心產品系忙，適合于大型項目的開發(fā)诵盼；銀行、電信银还、電商风宁、金融等各領域都大量使用Oracle數(shù)據(jù)庫。

Oracle數(shù)據(jù)庫是一種對象關系型數(shù)據(jù)庫蛹疯，在關系型數(shù)據(jù)庫的基礎上戒财，引入了一些面向對象的特性。

Oracle基礎概念（實例捺弦、表空間固翰、用戶、表之間關系）

數(shù)據(jù)庫

數(shù)據(jù)庫是數(shù)據(jù)集合羹呵。Oracle是一種數(shù)據(jù)庫管理系統(tǒng)骂际，是一種關系型的數(shù)據(jù)庫管理系統(tǒng)。

實例

一個Oracle實例（Oracle Instance）有一系列的后臺進程和內存結構組成冈欢。一個數(shù)據(jù)庫可以有n個實例歉铝。

用戶

用戶是在實例下建立的。不同實例可以建相同名字的用戶凑耻。

Oracle數(shù)據(jù)庫建好后太示，要想在數(shù)據(jù)庫里建表，必須先為數(shù)據(jù)庫建立用戶香浩，并為用戶指定表空間类缤。

表空間

表空間是一個用來管理數(shù)據(jù)存儲邏輯概念弧呐，表空間只是和數(shù)據(jù)文件（ORA或者DBF文件）發(fā)生關系扑毡，數(shù)據(jù)文件是物理的，一個表空間可以包含多個數(shù)據(jù)文件姆坚，而一個數(shù)據(jù)文件只能隸屬一個表空間囱晴。

數(shù)據(jù)文件（dbf膏蚓、ora）

數(shù)據(jù)文件是數(shù)據(jù)庫的物理存儲單位。數(shù)據(jù)庫的數(shù)據(jù)是存儲在表空間中的畸写，真正是在某一個或者多個數(shù)據(jù)文件中驮瞧。而一個表空間可以由一個或多個數(shù)據(jù)文件組成，一個數(shù)據(jù)文件只能屬于一個表空間枯芬。一旦數(shù)據(jù)文件被加入到某個表空間后论笔，就不能刪除這個文件采郎，如果要刪除某個數(shù)據(jù)文件，只能刪除其所屬于的表空間才行狂魔。

理解：表的數(shù)據(jù)尉剩，是有用戶放入某一個表空間的，而這個表空間會隨機把這些表數(shù)據(jù)放到一個或者多個數(shù)據(jù)文件中毅臊。

由于oracle的數(shù)據(jù)庫不是普通的概念理茎，oracle是由用戶和表空間對數(shù)據(jù)進行管理和存放的。但是表不是由表空間去查詢的管嬉，而是由用戶去查的皂林。因為不同用戶可以在同一個表空間建立同一個名字的表！這里區(qū)分就是用戶了蚯撩！

Oracle關系圖

Oracle數(shù)據(jù)庫可以創(chuàng)建多個實例础倍，每個實例可以創(chuàng)建多個表空間，每個表空間下可以創(chuàng)建多個用戶和數(shù)據(jù)庫文件胎挎，用戶可以創(chuàng)建多個表沟启。

解釋：一個表空間（數(shù)據(jù)庫）包含一個或多個數(shù)據(jù)文件，數(shù)據(jù)文件通常為*.dbf格式犹菇，一個數(shù)據(jù)庫的數(shù)據(jù)文件包含全部數(shù)據(jù)庫數(shù)據(jù)（如表德迹、索引等）。

一個用戶可以使用一個或多個表空間揭芍，一個表空間也可以供多個用戶使用胳搞。用戶和表空間沒有隸屬關系，表空間是一個用來管理數(shù)據(jù)存儲的邏輯概念称杨，表空間只是和數(shù)據(jù)文件發(fā)生關系肌毅，數(shù)據(jù)文件是物理的，一個表空間可以包含多個數(shù)據(jù)文件姑原，而一個數(shù)據(jù)文件只能隸屬一個表空間悬而。

總結：解釋數(shù)據(jù)庫、表空間锭汛、數(shù)據(jù)文件笨奠、表、數(shù)據(jù)的最好辦法就是想象一個裝滿東西的柜子店乐。數(shù)據(jù)庫其實就是柜子艰躺，柜中的抽屜是表空間呻袭，抽屜中的文件夾是數(shù)據(jù)文件眨八，文件夾中的紙是表，寫在紙上的信息就是數(shù)據(jù)左电。

SQLPlus常用語句

? 顯示當前用戶名

SQL>show user;

? 查看當前用戶的角色

SQL>select * from user_role_privs;

? 查看當前用戶的系統(tǒng)權限和表級權限

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

? 查看用戶所有表

SQL>select * from all_tab_comments -- 查詢所有用戶的表,視圖等廉侧。

SQL>select * from user_tab_comments -- 查詢本用戶的表,視圖等页响。

SQL>select * from all_col_comments? --查詢所有用戶的表的列名和注釋。

SQL>select * from user_col_comments -- 查詢本用戶的表的列名和注釋段誊。

SQL>select * from all_tab_columns --查詢所有用戶的表的列名等信息闰蚕。

SQL>select * from user_tab_columns --查詢本用戶的表的列名等信息。

? 關閉數(shù)據(jù)庫

SQL>shutdown immediate;

? 啟動數(shù)據(jù)庫

SQL>startup open;

? 創(chuàng)建表空間（數(shù)據(jù)庫）

SQL>create tablespace //表空間名稱

datafile 'E:\路徑\文件名.dbf' //表空間數(shù)據(jù)文件路徑

size 100M //表空間初始大小

? 刪除表空間

SQL>drop tablespace 數(shù)據(jù)庫名;

? 修改表空間：添加文件

SQL>alter tablespace 數(shù)據(jù)庫连舍；

add datafile'E:/Oracle/文件名.dbf' //添加文件

size 10M; 文件大小

? 修改表空間：刪除文件

SQL>alter tablespace //數(shù)據(jù)庫

drop datafile'E:/Oracle/文件名.dbf'; //刪除文件

? 顯示當前連接用戶

SQL>show user

? 查看系統(tǒng)擁有哪些用戶

SQL>select * from all_users;

? 連接到新用戶

SQL>conn

? 查看oracle的版本信息

SQL>select * from v$version;

? 查詢當前用戶下所有對象

SQL>select * from tab;

? 查詢表結構

SQL>desc 表名;

? 回滾

SQL>roll;

SQL>rollback;

? 提交

SQL>commit;

? 退出

SQL>exit;

SQL>quit;

? 設置顯示效果

SQL>set linesize 300;

SQL>set pagesize 300;

SQL>col 列名 for a列寬;

Oracle數(shù)據(jù)類型

Oracle創(chuàng)建用戶没陡、角色、授權索赏、建表

oracle用戶的概念對于Oracle數(shù)據(jù)庫至關重要盼玄，在現(xiàn)實環(huán)境當中一個服務器一般只會安裝一個Oracle實例，一個Oracle用戶代表著一個用戶群潜腻，他們通過該用戶登錄數(shù)據(jù)庫埃儿，進行數(shù)據(jù)庫對象的創(chuàng)建、查詢等開發(fā)融涣。

每一個用戶對應著該用戶下的N多對象童番，因此，在實際項目開發(fā)過程中威鹿，不同的項目組使用不同的Oracle用戶進行開發(fā)剃斧，不相互干擾。也可以理解為一個Oracle用戶既是一個業(yè)務模塊忽你，這些用戶群構成一個完整的業(yè)務系統(tǒng)悯衬，不同模塊間的關聯(lián)可以通過Oracle用戶的權限來控制，來獲取其它業(yè)務模塊的數(shù)據(jù)和操作其它業(yè)務模塊的某些對象檀夹。

用戶權限

Oracle數(shù)據(jù)庫用戶權限分為：系統(tǒng)權限和對象權限兩種筋粗。

系統(tǒng)權限：比如：create session可以和數(shù)據(jù)庫進行連接權限、create table炸渡、create view 等具有創(chuàng)建數(shù)據(jù)庫對象權限娜亿。

對象權限：比如：對表中數(shù)據(jù)進行增刪改查操作，擁有數(shù)據(jù)庫對象權限的用戶可以對所擁有的對象進行相應的操作蚌堵。

一买决、創(chuàng)建新用戶

create user student--用戶名

? identified by "123456"--密碼

? default tablespace USERS--表空間名

? temporary tablespace temp --臨時表空間名

? profile DEFAULT --數(shù)據(jù)文件（默認數(shù)據(jù)文件）

? account unlock; -- 賬戶是否解鎖（lock:鎖定、unlock解鎖）

更改用戶：

alter user STUDENT

? identified by 123456? --修改密碼

? account lock;--修改用戶處于鎖定狀態(tài)或者解鎖狀態(tài) （LOCK|UNLOCK ）

二吼畏、刪除用戶

語法：

? ? drop user 用戶名;

例子：

? ? drop user test;

若用戶擁有對象督赤，則不能直接刪除，否則將返回一個錯誤值泻蚊。

指定關鍵字cascade,可刪除用戶所有的對象躲舌，然后再刪除用戶。

語法：

? ? drop user 用戶名 cascade;

例子：

? ? drop user test cascade;

三性雄、授權角色

角色是一組權限的集合没卸，將角色賦給一個用戶羹奉，這個用戶就擁有了這個角色中的所有權限。

三種標準角色：

1.connect(連接角色)

connect角色是Oracle用戶的基本角色约计，connect權限代表著用戶可以和Oracle服務器進行連接诀拭，建立session（會話）。

2.resource(資源角色)

resouce角色是開發(fā)過程中常用的角色煤蚌。 RESOURCE給用戶提供了可以創(chuàng)建自己的對象耕挨，包括：表、視圖尉桩、序列俗孝、過程、觸發(fā)器魄健、索引赋铝、包、類型等沽瘦。

3.dba(數(shù)據(jù)庫管理員角色)

DBA角色是管理數(shù)據(jù)庫管理員該有的角色革骨。它擁護系統(tǒng)了所有權限，和給其他用戶授權的權限析恋。SYSTEM用戶就具有DBA權限良哲。

提示：

系統(tǒng)權限只能通過DBA用戶授權，對象權限有擁有該對象權限的對象授權（不一定是本身對象）助隧！用戶不能自己給自己授權筑凫！

授權命令：

--GRANT 對象權限 on 對象 TO 用戶? ?

grant select, insert, update, delete on JSQUSER to STUDENT;

--GRANT 系統(tǒng)權限 to 用戶

grant select any table to STUDENT;

--GRANT 角色 TO 用戶

grant connect to STUDENT;--授權connect角色

grant resource to STUDENT;--授予resource角色

撤銷權限：

-- Revoke 對象權限 on 對象 from 用戶

revoke select, insert, update, delete on JSQUSER from STUDENT;

-- Revoke 系統(tǒng)權限? from 用戶

revoke SELECT ANY TABLE from STUDENT;

-- Revoke 角色（role） from 用戶

revoke RESOURCE from STUDENT;

四、創(chuàng)建/授權/刪除角色

創(chuàng)建角色：

語法：

? ? create role 角色名;

例子：

? ? create role testRole;

授權角色：

語法：

? ? grant select on class to 角色名;

列子：

? grant select on class to testRole;

注：現(xiàn)在并村，擁有testRole角色的所有用戶都具有對class表的select查詢權限

刪除角色：

語法：

? ? drop role 角色名;

例子：

? ? drop role testRole;

注：與testRole角色相關的權限將從數(shù)據(jù)庫全部刪除

Oracle約束

在Oracle中巍实，數(shù)據(jù)完整性可以使用約束、觸發(fā)器哩牍、應用程序（過程棚潦、函數(shù)）三種方法來實現(xiàn)，在這三種方法中膝昆，因為約束易于維護丸边，并且具有最好的性能，所以作為維護數(shù)據(jù)完整性的首選荚孵。

添加約束語法：

ALTER? TABLE? 表名? ADD CONSTRAINT? 約束名? 約束類型? 約束描述

約束命名規(guī)范:

　非空約束 　　　　NN_表名_列名

　唯一約束 　　　　UK_表名_列名

　主鍵約束 　　　　PK_表名_列名

　外鍵約束 　　　　FK_表名_列名

　檢查約束 　　　　CK_表名_列名

　默認約束 　　　　DF_表名_列名

主鍵約束（ Primary key, 簡稱 PK）

該約束的定義為：不能重復妹窖，不能為null。

SQL> alter table 表名 add constraint 約束名 primary key(字段名);

非空約束( not null , 簡稱 NN )

約束該列不能為空值收叶。

SQL> alter table 表名 modify 字段名 not null;

唯一約束( Unique , 簡稱 UK )

約束該列數(shù)據(jù)不能重復骄呼，不能相同。

SQL> alter table 表名 add constraint 約束名 unique(字段名);

檢查約束( Check , 簡稱 CK )

檢查自定義條件是否為真，為真就可以插入谒麦，更新俄讹。

SQL> alter table 表名 add constraint 約束名 check(字段名 in('約束的值','約束的值')));

例子：

SQL> alter table emp add constraint CK_stuSex check(sex in('男','女'));

SQL>alter table 表名 add constraint 約束名 check(字段 between 0 and 100);

--出生日期在1980年1月1日之后

SQL>ALTER TABLE student ADD CONSTRAINT CK_student_borndate CHECK (borndate > TO_date(‘1980-01-01‘,‘yyyy-MM-dd‘) );

外鍵約束( Foreign key, 簡稱 FK )

? ? 外鍵約束定義在具有父子關系的子表中哆致，外鍵約束使得子表中的列對應父表的主鍵列绕德，用以維護數(shù)據(jù)庫的完整性。

外鍵約束注意以下幾點：

1摊阀、 外鍵約束的子表中的列和對應父表中的列數(shù)據(jù)類型必須相同耻蛇，列名可以不同

2、 對應的父表列必須存在主鍵約束（PRIMARY KEY）或唯一約束（UNIQUE）

3胞此、 外鍵約束列允許NULL值臣咖，對應的行就成了孤行了

4、 外鍵約束的表中的列數(shù)據(jù)必須包含在父表主鍵字段的數(shù)據(jù)內漱牵，否則會報錯：ORA-02298: 無法驗證- 未找到父項關鍵字

? ? 其實很多時候不使用外鍵夺蛇，很多人認為會讓刪除操作比較麻煩，比如要刪除父表中的某條數(shù)據(jù)酣胀，但某個子表中又有對該條數(shù)據(jù)的引用刁赦，這時就會導致刪除失敗。我們有兩種方式來優(yōu)化這種場景：

　 第一種方式：簡單粗暴闻镶，刪除的時候甚脉，級聯(lián)刪除掉子表中的所有匹配行，在創(chuàng)建外鍵時铆农，通過 on delete cascade 子句指定該外鍵列可級聯(lián)刪除：

SQL> alter table 表名 add constraint 約束名 foreign key(字段名) references 父表名 (父表字段) on delete cascade;

第二種方式：刪除父表中的對應行牺氨，會將對應子表中的所有匹配行的外鍵約束列置為NULL，通過 on delete set null 子句實施：

SQL> alter table 表名 add constraint 約束名 foreign key(字段名) references 父表名 (父表字段) on delete set null;

默認約束( Default Key,簡稱 DF )

約束用于向列中插入默認值墩剖。

SQL> alter table 表名 modify （字段 類型 default 默認值）

例子：

? ? alter table Student Modify Address varchar(50) default '地址不詳';

? ? alter table Student Modify JoinDate Date default sysdate;

Oracle注釋

添加表級注釋

SQL> COMMENT ON TABLE 表名 IS '注釋內容';

添加列級注釋

SQL> COMMENT ON COLUMN 表名.字段名 IS '注釋內容';

查看表級注釋

SQL> SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='表名';

查看列級注釋

SQL> SELECT * FROM USER_COL_COMMENTS WHERE COLUMN_NAME='字段名' AND COMMENTS IS NOT NULL;

刪除注釋（即猴凹，添加空注釋）

SQL> COMMENT ON TABLE 表名 IS '';

SQL> COMMENT ON COLUMN 表名.字段名 IS '';

Oracle序列

序列(SEQUENCE)是序列號生成器，可以為表中的行自動生成序列號岭皂，產生一組等間隔的數(shù)值(類型為數(shù)字)精堕。不占用磁盤空間，占用內存蒲障。

其主要用途是生成表的主鍵值歹篓，可以在插入語句中引用，也可以通過查詢檢查當前值揉阎，或使序列增至下一個值庄撮。

創(chuàng)建序列

創(chuàng)建序列需要CREATE SEQUENCE系統(tǒng)權限。

序列的創(chuàng)建語法如下：

　　CREATE SEQUENCE 序列名

　　[INCREMENT BY n]

　　[START WITH n]

　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]

　　[{CYCLE|NOCYCLE}]

　　[{CACHE n| NOCACHE}];

其中：

1)? INCREMENT BY用于定義序列的步長毙籽，如果省略洞斯，則默認為1，如果出現(xiàn)負值，則代表Oracle序列的值是按照此步長遞減的烙如。

2)? START WITH 定義序列的初始值(即產生的第一個值)么抗，默認為1。

3)? MAXVALUE 定義序列生成器能產生的最大值亚铁，選項NOMAXVALUE是默認選項蝇刀，代表沒有最大值定義。

4)? MINVALUE定義序列生成器能產生的最小值徘溢。選項NOMAXVALUE是默認選項吞琐，代表沒有最小值定義。

5)? CYCLE和NOCYCLE 表示當序列生成器的值達到限制值后是否循環(huán)然爆。CYCLE代表循環(huán)站粟，NOCYCLE代表不循環(huán)。如果循環(huán)曾雕，則當遞增序列達到最大值時奴烙，循環(huán)到最小值;對于遞減序列達到最小值時，循環(huán)到最大值剖张。

如果不循環(huán)切诀，達到限制值后，繼續(xù)產生新值就會發(fā)生錯誤修械。

6)? CACHE(緩沖)定義存放序列的內存塊的大小趾牧，默認為20。NOCACHE表示不對序列進行內存緩沖肯污。對序列進行內存緩沖翘单，可以改善序列的性能。

8)? CURRVAL 中存放序列的當前值,NEXTVAL 應在 CURRVAL 之前指定 蹦渣，二者應同時有效哄芜。

例子：

SQL> create sequence t1_seq increment by 1 start with 1;

使用序列

調用NEXTVAL將生成序列中的下一個序列號，調用時要指出序列名柬唯，即用以下方式調用: 序列名.NEXTVAL

CURRVAL用于產生序列的當前值认臊，無論調用多少次都不會產生序列的下一個值。如果序列還沒有通過調用NEXTVAL產生過序列的下一個值锄奢，先引用CURRVAL沒有意義失晴。調用CURRVAL的方法同上，要指出序列名拘央，即用以下方式調用:序列名.CURRVAL

SQL> create table t1(id number,qq number,ww number);

SQL> insert into t1 values(t1_seq.nextval,1,1);

查看序列

SQL> select * from t1;

? ? ? ? ID? ? ? ? QQ? ? ? ? WW

----------? ? ----------? ? ----------

? ? ? ? 1? ? ? ? ? 1? ? ? ? ? 1

? ? ? ? 2? ? ? ? ? 1? ? ? ? ? 1

? ? ? ? 3? ? ? ? ? 1? ? ? ? ? 1

? ? ? ? 4? ? ? ? ? 1? ? ? ? ? 1

? ? ? ? 5? ? ? ? ? 1? ? ? ? ? 1

SQL> select t1_seq.currval from dual;

? CURRVAL

----------

? ? ? ? 5

SQL> select t1_seq.nextval from dual;

? NEXTVAL

----------

? ? ? ? 6

SQL> select t1_seq.nextval from dual;

? NEXTVAL

----------

? ? ? ? 7