數據庫對象
常見：表：存放數據的基本數據庫對象，由行(記錄)和列(字段)組成
約束條件：執行數據校驗，保證數據完整性的系列規則
視圖：表中數據的邏輯顯示
索引：根據表中指定的字段建立起來的順序，用于提高查詢性能
序列：一組有規律的整數值
同義詞：對象的別名
命名：必須以字母開頭。可以包含字母、數據、下劃線、$、#
同一方案(用戶)下的對象不能重名。不能使用Oracle的保留字
補充：Oracle數據庫中的表分為用戶定義的表和數據字典表
用戶定義的表：用戶自己創建并維護的一組表，包含了用戶所需的信息
數據字典表：由Oracle數據庫自動創建并維護的一組表，包含數據庫信息
創建表
概述：創建表需要兩個前提條件，即具備創建表的權限和有可用的存儲空間
創建表時必須指定表名、字段名、字段類型。create table為DDL語句，一經執行不可撤銷
語法：create table [schema.]table(column datatype [default expr][,...]);--缺省是將表創建在當前方案(用戶)下
舉例：create table scott.test1(name varchar2(20),hiredata date default sysdate,salary number(8,2) default 0);
向test1插入新記錄的時候，若沒有指定hiredate值，缺省就會取當前的系統時間。同樣也設置了salsry的缺省值為0
如果插入記錄的時候也沒有指定eid的值，那么eid的缺省值是null。注意數值型的缺省值不是0，而是null
補充：還可以使用子查詢創建表。這是創建表的另一種方式，但不是很常用。即創建表的同時將子查詢的結果直接插入其中
新建表與子查詢結果的字段列表必須匹配。新建表的字段列表可以缺省，這時字段名就跟子查詢的結果的字段名相同
語法：create table [schema.]table(column[,...]) as subquery;--括號中不可以定義字段類型
舉例：create table myemp2 as select empno,ename,sal*12 from emp;--非法。表達式不可以充當字段，需要指定別名
create table myemp2 as select empno,ename,sal*12 annsal from emp;--合法
create table myemp(編號,姓名,年薪) as select empno,ename,sal*12 from emp;--指定新建表的字段名
修改表結構
概述：使用alter table語句修改表的結構。包括添加、修改、刪除字段。alter語句為DDL語句，一經執行不可撤銷
添加：在alter table語句中使用add子句添加新字段。新字段只能被加到整個表的最后
alter table table add(column datatype [default expr] [column datatype]...);
alter table test1 add(grade number(3),phone varchar2(20) default '無');
修改：在alter table語句中使用modify子句修改現有字段。包括數據類型、大小和默認值。但不可以修改字段名
alter table table modify(column datatype [default expr] [column datatype]...);
alter table test1 modify(grade number(2),phone varchar2(15) default '010-12345678');
修改的缺省值設置，只對此后新插入的記錄有效。修改操作會受到當前表中已有數據的影響
當已有記錄的相應字段只包含空值時，類型和大小都可以修改。如果該字段已包含數值，則修改可能失敗
刪除：在alter table語句中使用drop子句刪除字段。從每行中刪除該字段占據的長度和數據，釋放在數據塊中占用的存儲空間
alter table table drop(column[,column]...);
alter table test1 drop(grade,phone);
清空表數據
概述：使用truncate table可以清空表中數據。清除表中所有記錄，釋放表的存儲空間，它是DDL語句，一經執行不可撤銷
它與DML中的delete語句有很大的差別。delete可以進行條件性的刪除，也可以定義到事務中，對其進行回滾或撤銷
語法：truncate table table;--它清除的并不是表格本身，表的結構還是存在的，只不過變成了一個空表
刪除表
概述：使用drop table語句刪除表，它是DDL語句，一經執行不可撤銷
表中所有數據將被刪除，此前未完成的事務將被提交，所有相關的索引被刪除
語法：drop table table;
重命名表
概述：使用rename語句可以改變現有表的名稱，它是DDL語句，一經執行不可撤銷
也可修改其它數據庫對象(視圖、序列、同義詞等)的名稱。執行重命名操作的必須是對象的所有者
語法：rename old_name to new_name;
舉例：rename test1 to test88;
數據字典
概述：數據字典是Oracle數據庫的核心，用于描述數據庫及其所有對象。數據字典由一系列只讀的表和視圖組成
這些表和視圖屬SYS用戶擁有，由Oracle Server負責維護，用戶可以通過select語句進行訪問
內容：數據庫的物理和邏輯結構。對象的定義和空間分配。完整性約束條件。用戶。角色。權限。審計記錄
視圖：數據字典中的視圖都是只讀的，主要可以分為如下三類
dba(所有方案包含的對象信息)、all(用戶可以訪問的對象信息)、user(用戶方案的對象信息)
舉例：select table_name from user_tables;--查看當前用戶擁有的所有表的名字
select table_name from all_tables;--查看當前用戶可以訪問的所有表的名字
select distinct object_type from user_objects;--查看當前用戶擁有的所有對象的類型
select distinct object_type from all_objects;--返回當前用戶可以查看的所有的對象的類型
select table_name frome dba_tables;--查看所有用戶擁有的所有表的名字
select * from user_constraints;--查看當前方案(用戶)下所有的約束的信息
select * from user_constraints where table_name='student';--查看當前方案(用戶)下的student表中的約束信息
約束(Constraint)
概述：約束是在表上強制執行的數據校驗規則，用于保護數據的完整性
具體包括五種，即not null(非空)、unique key(唯一鍵)、primary key(主鍵)、foreign key(外鍵)、check(檢查)
分類：域完整性約束：not null、check。實體完整性約束：unique、primary key。參照完整性約束：foreign key
說明：約束也是一種數據庫對象。如果創建約束時，用戶沒有指定它的名字，那么系統會自動的為其命名
在Oracle使用SYS_Cn格式命名約束，也可以由用戶命名。也可以通過數據字典視圖查看約束
可以在建表的同時添加約束，也可以在建表后單獨添加約束。可以在表級或列級定義約束
通常并不太建議在建表之后再添加約束或者建表之后再修改表的結構
查看：查詢用戶字典視圖user_constraints可得到當前用戶的所有約束。即select * from user_constraints;
查詢用戶字典視圖user_cons_columns可獲知約束建立在哪些字段上。即select * from user_cons_columns;
創建：create table [schema.]table(column datatype[default expr][column_constraint],...[table_constraint]);
alter table table add [constraint constraint_name] constraint_type(column);--這是建表后添加約束
比如alter table stu add constraint stu_sid_pk primary key(sid);--原stu表中有sid和name兩個字段
等價alter table stu add primary key(sid);--只不過此時的約束名就會由系統自動設定了
特例：建表后添加約束時，非空約束必須使用modify子句添加。實際上相當于重新定義了某個字段
如alter table stu modify(name not null);或alter table stu modify(name char(8) default 'N/A' not null);
也可以修改多個字段，如alter table stu modify(sid not null,name default 'Stone' not null);
刪除：語法為alter table table drop constraint constraint_name;
alter table table drop primary key;--刪除主鍵的另一種方式。只有主鍵才可以這樣刪除
因為一個表中只可以定義一個主鍵，所以不會有二義性。而其它的約束，都可能定義多個
續一：刪除約束時，若存在與該約束相關聯的其它約束，則刪除操作會失敗。可用cascade子句將其它關聯約束一并刪除
語法為alter table table drop constraint constraint_name cascade;
這個時候一共刪除了兩個約束，一個是主表中的主鍵，一個是子表中的外鍵
續二：刪除表中字段時，若該字段處于多字段聯合約束條件(聯合主鍵、聯合唯一鍵、存在參照當前字段的外鍵)中時
則刪除會失敗。此時可使用cascade constraints子句將與該字段相關的約束一并刪除
語法為alter table table drop(column[,column]...) cascade constraints;
禁用：在alter table中，可使用disable constraint子句禁用已有約束。也可用cascade選項將相關聯的約束也一并刪除
語法為alter table table disable constraint constraint_name [cascade];
禁用跟刪除不同，它還可以啟用。而且約束的具體內容或約束的定義等，還是存在的，只是臨時不起作用了
啟用：在alter table中，可使用enable constraint子句啟用已被禁用的約束
語法為alter table table enable constraint constraint_name;
啟用約束后，就無法再使用cascade選項一并啟用相關聯的其它約束。若仍想使用其它約束，則只能重建其它約束
非空約束(not null)
特點：只能在字段級定義。確保字段值不能為空。一個表中可以定義多個非空約束
舉例：create table strudent(sid number(3) not null,name varchar2(20),birth date constraint nn not null);
說明：constraint nn not null也是非空約束，只不過birth的約束名字是由用戶命名的，而sid的約束是由系統命名的
constraint是一個保留字，說明這里要添加一個約束，nn是約束的名字，后面的not null是約束的類型
按照慣例，約束名習慣命名為“表名_字段名_約束的類型簡稱”這里就應該是student_birth_nn
唯一性約束(unique)
特點：既可以在字段級定義，也可以在表級定義。用于確保所在的字段(或字段組合)不出現重復值
唯一性約束的字段允許出現空值。Oracle會自動為唯一性約束創建對應的唯一性索引。一個表中可以定義多個唯一鍵
舉例：create table student(sid number(3) unique,name varchar2(20));--字段級定義
create table student(sid number(3),name varchar2(20),constraint strudent_sid_un unique(sid));--表級約束
說明：對一個字段進行唯一性約束時，這兩種效果相同的。若需要對多個字段定義唯一性約束的話，則只能定義成表級約束
如create table fenshu(a number(3),b varchar2(20),c number(4),constraint fenshu_a_b_un unique(a,b));
也就是說a和b兩個字段的值組合起來不能出現重復。可以把它想象成是一張學生分數表，a和b相當于學號和科目
主鍵約束(primary key)
特點：主鍵既可以在字段級定義，也可以在表級定義。主鍵用于唯一標識表中的某一行記錄，功能上相當于非空且唯一
一個表中只允許一個主鍵，主鍵可以是單個字段或多字段的組合。Oracle會自動為主鍵字段創建對應的唯一性索引
舉例：create table student(sid number(3) primary key, name varchar2(20));
create table student(sid number(3),name varchr2(20), constraint student_sid_pk primary key(sid));
聯合：由多個字段組合而成的主鍵也稱聯合主鍵。聯合主鍵中每一個字段都不能為空
聯合主鍵字段組合的值不能出現重復。聯合主鍵只能定義為表級約束
create table fenshu(a number(3),b varchar2(20),c number(3),constraint fenshu_a_b_pk primary_key(a,b));
同樣是學生分數表。其實學號和科目組合起來更應該設成主鍵，而不是唯一鍵，因為它們的值也不應該為空
外鍵約束(foreign key)
特點：外鍵用于確保相關的兩個字段之間的參照關系，以實現參照完整性約束。外鍵參照的必須是主表的主鍵或者唯一鍵
外鍵約束通常構建于來自不同表的兩個字段之間。子表外鍵列的值必須在主表參照列值的范圍內，或者為空
主表的主鍵或唯一鍵被子表參照時，主表相應記錄不允許被刪除。
參照：所謂的參照完整性約束，比如說員工信息表和工資表，二者是通過員工編號建立連接的
這時工資表中的員工編號是受限制的，即必須是出現在員工信息表中的，這種關系稱之為參照關系
舉例：create table info(id number(3) primary key,name varchar2(20),job varchar2(20),birth date);
create table sal(a number(3) references info(id),b number(8,2));--將外鍵約束定義成字段級約束
create table sal(a number(3),b number(8,2),constraint sal_a_fk foreign key(a) references info(id));
說明：子表sal中的a字段被定義為外鍵，它參照的是主表info中的id字段。這里是把外鍵約束定義成了表級約束
執行完這兩行語句之后，兩個表中均沒有數據。若再執行insert into sal(a,b) values(1,2);則會出現錯誤
因為系統并沒有在子表所參照的主表中的某一條記錄上找到id為1的字段的值
此時可以在剛才的插入語句之前先執行insert into info values(1,'Tom','Adv',sysdate);就不會出現錯誤了
如果此時再執行insert into sal(null,8);則該記錄插入成功。外鍵畢竟不同于主鍵，這也不算違背參照規則
接著再執行一次insert into sal(1,8)則記錄插入成功。這種插入null和重復記錄的方式都是不合理的
為了實現數據的合理化，此時也可以進行其它的限制。即字段a在作為外鍵的同時，還可以進行主鍵的限制
即create table sal(a number(3) primary key references info(id),b number(8,2));
或create table sal(a number(3) primary key,b number(8,2),constraint sal_a_fk foreign key(a) references info(id));
檢查約束(check)
特點：只能在字段級定義。它定義每一行(的指定字段)都必須滿足的條件。以條件表達式的形式給出數據需要符合的條件
條件中不允許出現currval,nextval,level,rownum等偽列或sysdate,uid,user,userenv等函數或對其它字段值的引用
currval即序列當前的值。nextval即序列生成器的下一個序列值。level即在層次化查詢的過程中標明查詢的層數
sysdate即當前系統時間。uid即當前用戶的id號。user即當前用戶名。userenv用來查詢客戶端的環境，包括語言或用戶名等等
舉例：create table test1(name varchar2(20) check(length(name)>=6),age number(3) check(age>=0 and age<=120));
create table test1(name varchar2(20) check(name is not null),age number(3));--這就等價于非空約束了
視圖(View)
概述：視圖由一個或多個表(或視圖)中提取數據而成。視圖是一種虛擬表。視圖一經創建就可以當作表來使用
使用視圖可以簡化復雜的數據查詢。能夠提高運行效率。可以屏蔽數據庫表結構，實現數據邏輯獨立性
還可以限制數據庫訪問。也可以在相同數據上提供不同的視圖，便于數據共享。視圖也可以有主鍵等等
可以通過在create view語句中嵌入子查詢的方式創建視圖
創建：create [or replace] view [schema.]view [(alias[,aliasx]...)] as subquery;
舉例：create or replace view v1(編號,姓名,工資) as select empno,ename,sal from emp where deptno=20;
建議在創建視圖時加上or replace，即如果存在重名視圖，則替換掉重名視圖
而且不允許在括號中列出字段類型，因為具體的字段類型由子查詢的結果決定
強調：可用force選項強制創建視圖。即無論預期中的字段或基表是否存在，都要強制創建視圖
也就是先給出一個視圖的定義。但此時不能對這個連基表都不存在的視圖進行查詢，原因不言自明
語法為create [or replace] [force|noforce] view [schema.]view [(alias[,aliasx]...)] as subquery;
比如create or replace force view v2 as select empno,ename,job,sal from emp2 where deptno=20;
假定此時不存在emp2表，當然這些字段存不存在就無從談起了，如果不使用force選項，肯定無法創建視圖
缺省為不強制(即noforce)創建視圖。實際上不建議強制創建視圖
查詢：和查詢表數據一樣，可以使用select * from v1;來查詢視圖中的數據
它的實現細節是這樣的，當數據庫服務器接收到應用程序或SQLPlus等客戶端軟件發送過來的查詢視圖的指令的時候
首先會在當前方案下尋找名字叫v1的視圖定義，即定義視圖的create語句，也就是create or replace view v1...
找到v1的定義之后，便執行定義中的子查詢，再把查詢的結果返回給客戶端
這意味著每次引用或者訪問視圖的時候，都會執行一次子查詢，都會查一下底層的物理表
所以物理表中的任何數據更新，都會立即在視圖的查詢中體現出來
續一：可以用desc v1查看視圖結構。可以用drop view v1刪除視圖
所謂的臨時表是在程序運行的過程中，根據需要而臨時創建的一張表。通常是利用create table創建臨時表
臨時表只是臨時用到它，用完了便刪除。類似于臨時文件，但臨時表所保存的是真正的數據，這跟虛擬表不同
續二：創建視圖的時候，也可以不指定字段名。缺省視圖的字段屬性都與子查詢結果中的字段屬性相同
若子查詢中使用了別名，視圖則采用別名作為它的字段名。而且子查詢中的表達式或函數不允許作為視圖的字段名
續三：實際上視圖的定義和表的定義一樣，都應該在應用程序開發的時候，在數據庫設計階段就確定下來它們數目個功能
即創建一定數量的表，以保存最底層基礎的數據。并設定好哪些數據需要以視圖的方式交由具體的程序模塊去使用
復雜：也可以創建復雜視圖，也就是說子查詢還可以復雜一些
如果某業務經常需要查詢統計信息，那么只需要創建一個這樣的視圖就可以了，而不必每次都執行子查詢以獲得數據
比如create or replace v_emp(工號,姓名,職位,年薪,工齡(月),部門編號,部門名稱,)
as select empno, ename, job, sal*12, month_between(sysdate,hiredate), emp.deptno, dname
from emp, dept where emp.deptno=dept.deptno;
更新：在可更新視圖上進行DML操縱，可以修改基表中的數據。語法與在表上操作相同，分別為insert、update、delete語句
可更新視圖的定義中不能使用分組函數、group by子句、distinct關鍵字、rownum偽列，而且字段的定義不能為表達式
由兩個以上基表中導出的視圖不可更新。基表中非空的列在視圖定義中未包括，則不可在視圖上進行insert操作
這種操作很容易出錯，實際上在真實的開發中很少這么做，也不建議通過視圖去更新底層數據
只讀：在創建視圖時，可使用with read only選項將之設置為只讀
語法為create [or replace] view [schema.]view [(alias[,aliasx]...)] as subquery [with read only];
臨時：嵌入到SQL語句中的子查詢都是臨時視圖。比如說用子查詢建表或TopN分析等等所涉及到的子查詢都屬于臨時視圖
臨時視圖不是數據庫對象，其定義不會長久保存在數據庫中，本次運行后即被清除。它類似于Java語言中創建的匿名類
索引(Index)
概述：是一種用于提升查詢效率的數據庫對象。索引信息與表獨立存放。它可以通過快速定位數據的方法，減少磁盤I/O操作
索引分為兩類，即唯一性索引和非唯一索引。Oracle數據庫自動使用和維護索引
創建：自動創建：在定義主鍵或唯一鍵約束時，系統會自動在相應的字段上創建唯一性索引
手動創建：用戶可以在其它列上創建非唯一的索引，以加速查詢
語法為create index [schema.]index on table(column[,column]...);
比如說create index myindex on emp(ename);--查詢表時，若以ename作為查詢條件的話，就很容易定位到某行記錄了
刪除：使用drop index語句刪除索引，如drop index myindex;操作者必須是索引的所有者，或擁有drop該index的權限
刪除表時，相關的索引(和約束)將被自動刪除，但視圖和序列將保留
原則：適合于創建索引：字段取值的分布范圍很廣。字段中包含大量空值。字段經常出現在where子句或連接條件中
表經常被訪問，或表數據量很大時，且通常每次訪問的數據量小于記錄總量的2%～4%
不適合創建索引：表很小。字段不經常出現在where子句中。每次訪問的數據量大于記錄總數的2%～4%
表經常更新。被索引的字段作為表達式的一部分被引用
查看：查詢用戶字典視圖user_indexes可得到用戶的所有索引。如select * from user_indexes;
查詢用戶字典視圖user_ind_columns可獲知索引建立在哪些字段上。如select * from user_ind_columns;
函數：基于表達式的索引被統稱為基于函數的索引。索引表達式由表中的字段、常量、SQL函數和自定義函數構建而成
創建函數索引語法為create index [schema.]index on table(function(column));
創建函數索引create index myindex on emp(lower(ename));
使用函數索引select * from emp where lower(ename)='king';
在使用時，條件中的字段需要與創建索引時指定的相同，索引才會起作用。即lower(ename)需要前后一致
序列(Sequence)
概述：是由系統自動生成的，不重復的整數值。序列是一種數據庫對象，可以被多個用戶共享。序列可以代替應用程序編號
序列的最典型的用途是作為主鍵值，它對于每一行必須是唯一的。可以對序列值進行緩沖存儲，以提高訪問效率
但在應用程序中生成編號的話，會產生性能瓶頸的。也就是說比較麻煩，要確保它的唯一性，還要它的保證效率
所謂的緩沖存儲是指，在使用序列之前，先生成指定的多個序列的取值作為備用，這樣能提高訪問效率
從實際應用的角度來講，序列的重要性遠遠超過視圖和索引，跟約束條件以及表的重要性相當
查看：查詢數據字典視圖user_sequences可獲得用戶序列信息
創建：create sequence [schema.]sequence [increment by n][start with n]
[{maxvalue n|nomaxvalue}][{minvalue n}|nominvalue][{cycle|nocycle}][{cache n|nocache}][{order|noorder}];
舉例：create sequence mysequence2;
create sequence mysequence1 increment by 1 start with 1 nomaxvalue nocycle;
釋一：increment指明當前序列號遞增的值，缺省為1。start with指定當前序列號的起始值，缺省為1
maxvlaue設定序列號的最大值。缺省為nomaxvalue，即沒有最大值。同理minvalue就是設定序列號的最小值
cycle設置在序列達到最大值之后，是否重新從1開始循環，缺省為nocycle，即不循環
釋二：實際上nomaxvalue在實現的過程中，是有最大值的，大約是10的27次方。同理nominvalue是10的負27次方
而且在使用序列的時候，一般不應該讓它循環。避免出現預期以外的數據的重復
釋三：cache設置是否進行緩沖存儲。它的缺省值不是nocache，而是cache 20
也就是說cache會先生成20個序列號備用，當序列號被用掉了一個之后，它就會再補充上一個
這種通過備用的方式，能夠使程序運行效率會高一點。但是緩存序列號有可能會有丟失，倒不是出錯，只是不連續而已
釋四：order保證生成的序列號一定是按照請求的順序。其實對于單進程或者單線程的程序(即非并發性的訪問)來說沒有什么差別
若在并發(也叫并行)工作模式下，多個請求同時請求同一個序列號的時候，分配給它們序列號就有可能是不保證順序的
只是順序不保證，但值還是唯一的，不會重復。實際上它對我們來說影響不大。比如有兩個請求先后同時到達
如果用order來保證的話，那么先到達的請求所請求到的序列號就會是101，后到達的就是102
但如果nooder就可能出現先到達的請求得到的反而是102，后到達的可能是101，因為中間有一個交替的過程
缺省為noorder，即不保證序列號是按照請求順序生成的
使用：select mysequence1.nextval from dual;--其實它會新生成一個序列號，返回一個整數值
select mysequence1.currval from dual;--取出當前的序列號。這種直接的查詢，意義不大
insert into test1 values(mysequence1.nextval,'Tom');--這才是真正的使用序列
nextval偽列用于從指定的序列數值中取出下一個值。currval偽列引用的是指定序列的“當前值”
使用緩存(cache n)可提高訪問效率。使用nocache和order設置會降低運行效率
當在回滾、系統異常、多個表同時使用同一序列等情況下，可能會使得序列不連續
修改：alter sequence [schema.]sequence [increment by n][{maxvalue n|nomaxvalue}]
[{minvalue n}|nominvalue][{cycle|nocycle}][{cache n|nocache}][{order|noorder}];
操作者必須是序列的所有者，或者擁有alter該序列的權限。只有未來再生成的序列數受影響
修改時會進行一些驗證，比如新的maxvalue如果小于當前的序列值就會報錯。序列的初始值不可更改
為了避免跟現有的序列號發生重復，所以序列的初始值不可更改
刪除：使用drop sequence語句刪除序列。操作者須是序列的所有者，或擁有drop該sequence的權限
同義詞
概述：相當于對象的別名，使用同義詞可以方便訪問其它用戶的對象，也能夠縮短對象名字的長度
創建：create [public] synonym synonym fro object;
create synonym gt1 for emp;
使用：select * from gt1;
刪除：drop synonym gt1;
說明：若不加public，那么定義的同義詞則只能在當前方案(用戶)的環境中可用，其它用戶則無法使用該同義詞
而加了public后，其它的方案(用戶)便也可以使用這個同義詞了
但必須是具有相應權限的用戶(如DBA)才有資格將同義詞定義為public類型
更多信息請查看IT技術專欄
版權所有:易賢網
公眾號
事業單位
當前位置：
公考類
招聘類
各類考試