數據庫對象
常見表存放數據的基本數據庫對象由行(記錄)和列(字段)組成
約束條件執行數據校驗保證數據完整性的系列規則
視圖表中數據的邏輯顯示
索引根據表中指定的字段建立起來的順序用於提高查詢性能
序列一組有規律的整數值
同義詞對象的別名
命名必須以字母開頭可以包含字母數據下劃線$#
同一方案(用戶)下的對象不能重名不能使用Oracle的保留字
補充Oracle數據庫中的表分為用戶定義的表和數據字典表
用戶定義的表用戶自己創建並維護的一組表包含了用戶所需的信息
數據字典表由Oracle數據庫自動創建並維護的一組表包含數據庫信息
創建表
概述創建表需要兩個前提條件即具備創建表的權限和有可用的存儲空間
創建表時必須指定表名字段名字段類型create table為DDL語句一經執行不可撤銷
語法create table [schema]table(column datatype [default expr][]);缺省是將表創建在當前方案(用戶)下
舉例create table scotttest(name varchar()hiredata date default sysdatesalary number() default );
向test插入新記錄的時候若沒有指定hiredate值缺省就會取當前的系統時間同樣也設置了salsry的缺省值為
如果插入記錄的時候也沒有指定eid的值那麼eid的缺省值是null注意數值型的缺省值不是而是null
補充還可以使用子查詢創建表這是創建表的另一種方式但不是很常用即創建表的同時將子查詢的結果直接插入其中
新建表與子查詢結果的字段列表必須匹配新建表的字段列表可以缺省這時字段名就跟子查詢的結果的字段名相同
語法create table [schema]table(column[]) as subquery;括號中不可以定義字段類型
舉例create table myemp as select empnoenamesal* from emp;非法表達式不可以充當字段需要指定別名
create table myemp as select empnoenamesal* annsal from emp;合法
create table myemp(編號姓名年薪) as select empnoenamesal* from emp;指定新建表的字段名
修改表結構
概述使用alter table語句修改表的結構包括添加修改刪除字段alter語句為DDL語句一經執行不可撤銷
添加在alter table語句中使用add子句添加新字段新字段只能被加到整個表的最後
alter table table add(column datatype [default expr] [column datatype]);
alter table test add(grade number()phone varchar() default 無);
修改在alter table語句中使用modify子句修改現有字段包括數據類型大小和默認值但不可以修改字段名
alter table table modify(column datatype [default expr] [column datatype]);
alter table test modify(grade number()phone varchar() default );
修改的缺省值設置只對此後新插入的記錄有效修改操作會受到當前表中已有數據的影響
當已有記錄的相應字段只包含空值時類型和大小都可以修改如果該字段已包含數值則修改可能失敗
刪除在alter table語句中使用drop子句刪除字段從每行中刪除該字段占據的長度和數據釋放在數據塊中占用的存儲空間
alter table table drop(column[column]);
alter table test drop(gradephone);
清空表數據
概述使用truncate table可以清空表中數據清除表中所有記錄釋放表的存儲空間它是DDL語句一經執行不可撤銷
它與DML中的delete語句有很大的差別delete可以進行條件性的刪除也可以定義到事務中對其進行回滾或撤銷
語法truncate table table;它清除的並不是表格本身表的結構還是存在的只不過變成了一個空表
刪除表
概述使用drop table語句刪除表它是DDL語句一經執行不可撤銷
表中所有數據將被刪除此前未完成的事務將被提交所有相關的索引被刪除
語法drop table table;
重命名表
概述使用rename語句可以改變現有表的名稱它是DDL語句一經執行不可撤銷
也可修改其它數據庫對象(視圖序列同義詞等)的名稱執行重命名操作的必須是對象的所有者
語法rename old_name to new_name;
舉例rename test to test;
數據字典
概述數據字典是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 nullcheck實體完整性約束uniqueprimary 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() default N/A not null);
也可以修改多個字段如alter table stu modify(sid not nullname 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() not nullname varchar()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() uniquename varchar());字段級定義
create table student(sid number()name varchar()constraint strudent_sid_un unique(sid));表級約束
說明對一個字段進行唯一性約束時這兩種效果相同的若需要對多個字段定義唯一性約束的話則只能定義成表級約束
如create table fenshu(a number()b varchar()c number()constraint fenshu_a_b_un unique(ab));
也就是說a和b兩個字段的值組合起來不能出現重復可以把它想象成是一張學生分數表a和b相當於學號和科目
主鍵約束(primary key)
特點主鍵既可以在字段級定義也可以在表級定義主鍵用於唯一標識表中的某一行記錄功能上相當於非空且唯一
一個表中只允許一個主鍵主鍵可以是單個字段或多字段的組合Oracle會自動為主鍵字段創建對應的唯一性索引
舉例create table student(sid number() primary key name varchar());
create table student(sid number()name varchr() constraint student_sid_pk primary key(sid));
聯合由多個字段組合而成的主鍵也稱聯合主鍵聯合主鍵中每一個字段都不能為空
聯合主鍵字段組合的值不能出現重復聯合主鍵只能定義為表級約束
create table fenshu(a number()b varchar()c number()constraint fenshu_a_b_pk primary_key(ab));
同樣是學生分數表其實學號和科目組合起來更應該設成主鍵而不是唯一鍵因為它們的值也不應該為空
外鍵約束(foreign key)
特點外鍵用於確保相關的兩個字段之間的參照關系以實現參照完整性約束外鍵參照的必須是主表的主鍵或者唯一鍵
外鍵約束通常構建於來自不同表的兩個字段之間子表外鍵列的值必須在主表參照列值的范圍內或者為空
主表的主鍵或唯一鍵被子表參照時主表相應記錄不允許被刪除
參照所謂的參照完整性約束比如說員工信息表和工資表二者是通過員工編號建立連接的
這時工資表中的員工編號是受限制的即必須是出現在員工信息表中的這種關系稱之為參照關系
舉例create table info(id number() primary keyname varchar()job varchar()birth date);
create table sal(a number() references info(id)b number());將外鍵約束定義成字段級約束
create table sal(a number()b number()constraint sal_a_fk foreign key(a) references info(id));
說明子表sal中的a字段被定義為外鍵它參照的是主表info中的id字段這裡是把外鍵約束定義成了表級約束
執行完這兩行語句之後兩個表中均沒有數據若再執行insert into sal(ab) values();則會出現錯誤
因為系統並沒有在子表所參照的主表中的某一條記錄上找到id為的字段的值
此時可以在剛才的插入語句之前先執行insert into info values(TomAdvsysdate);就不會出現錯誤了
如果此時再執行insert into sal(null);則該記錄插入成功外鍵畢竟不同於主鍵這也不算違背參照規則
接著再執行一次insert into sal()則記錄插入成功這種插入null和重復記錄的方式都是不合理的
為了實現數據的合理化此時也可以進行其它的限制即字段a在作為外鍵的同時還可以進行主鍵的限制
即create table sal(a number() primary key references info(id)b number());
或create table sal(a number() primary keyb number()constraint sal_a_fk foreign key(a) references info(id));
檢查約束(check)
特點只能在字段級定義它定義每一行(的指定字段)都必須滿足的條件以條件表達式的形式給出數據需要符合的條件
條件中不允許出現currvalnextvallevelrownum等偽列或sysdateuiduseruserenv等函數或對其它字段值的引用
currval即序列當前的值nextval即序列生成器的下一個序列值level即在層次化查詢的過程中標明查詢的層數
sysdate即當前系統時間uid即當前用戶的id號user即當前用戶名userenv用來查詢客戶端的環境包括語言或用戶名等等
舉例create table test(name varchar() check(length(name)>=)age number() check(age>= and age<=));
create table test(name varchar() check(name is not null)age number());這就等價於非空約束了
視圖(View)
概述視圖由一個或多個表(或視圖)中提取數據而成視圖是一種虛擬表視圖一經創建就可以當作表來使用
使用視圖可以簡化復雜的數據查詢能夠提高運行效率可以屏蔽數據庫表結構實現數據邏輯獨立性
還可以限制數據庫訪問也可以在相同數據上提供不同的視圖便於數據共享視圖也可以有主鍵等等
可以通過在create view語句中嵌入子查詢的方式創建視圖
創建create [or replace] view [schema]view [(alias[aliasx])] as subquery;
舉例create or replace view v(編號姓名工資) as select empnoenamesal from emp where deptno=;
建議在創建視圖時加上or replace即如果存在重名視圖則替換掉重名視圖
而且不允許在括號中列出字段類型因為具體的字段類型由子查詢的結果決定
強調可用force選項強制創建視圖即無論預期中的字段或基表是否存在都要強制創建視圖
也就是先給出一個視圖的定義但此時不能對這個連基表都不存在的視圖進行查詢原因不言自明
語法為create [or replace] [force|noforce] view [schema]view [(alias[aliasx])] as subquery;
比如create or replace force view v as select empnoenamejobsal from emp where deptno=;
假定此時不存在emp表當然這些字段存不存在就無從談起了如果不使用force選項肯定無法創建視圖
缺省為不強制(即noforce)創建視圖實際上不建議強制創建視圖
查詢和查詢表數據一樣可以使用select * from v;來查詢視圖中的數據
它的實現細節是這樣的當數據庫服務器接收到應用程序或SQLPlus等客戶端軟件發送過來的查詢視圖的指令的時候
首先會在當前方案下尋找名字叫v的視圖定義即定義視圖的create語句也就是create or replace view v
找到v的定義之後便執行定義中的子查詢再把查詢的結果返回給客戶端
這意味著每次引用或者訪問視圖的時候都會執行一次子查詢都會查一下底層的物理表
所以物理表中的任何數據更新都會立即在視圖的查詢中體現出來
續一可以用desc v查看視圖結構可以用drop view v刪除視圖
所謂的臨時表是在程序運行的過程中根據需要而臨時創建的一張表通常是利用create table創建臨時表
臨時表只是臨時用到它用完了便刪除類似於臨時文件但臨時表所保存的是真正的數據這跟虛擬表不同
續二創建視圖的時候也可以不指定字段名缺省視圖的字段屬性都與子查詢結果中的字段屬性相同
若子查詢中使用了別名視圖則采用別名作為它的字段名而且子查詢中的表達式或函數不允許作為視圖的字段名
續三實際上視圖的定義和表的定義一樣都應該在應用程序開發的時候在數據庫設計階段就確定下來它們數目個功能
即創建一定數量的表以保存最底層基礎的數據並設定好哪些數據需要以視圖的方式交由具體的程序模塊去使用
復雜也可以創建復雜視圖也就是說子查詢還可以復雜一些
如果某業務經常需要查詢統計信息那麼只需要創建一個這樣的視圖就可以了而不必每次都執行子查詢以獲得數據
比如create or replace v_emp(工號姓名職位年薪工齡(月)部門編號部門名稱)
as select empno ename job sal* month_between(sysdatehiredate) empdeptno dname
from emp dept where empdeptno=deptdeptno;
更新在可更新視圖上進行DML操縱可以修改基表中的數據語法與在表上操作相同分別為insertupdatedelete語句
可更新視圖的定義中不能使用分組函數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子句或連接條件中
表經常被訪問或表數據量很大時且通常每次訪問的數據量小於記錄總量的%～%
不適合創建索引表很小字段不經常出現在where子句中每次訪問的數據量大於記錄總數的%～%
表經常更新被索引的字段作為表達式的一部分被引用
查看查詢用戶字典視圖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 mysequence;
create sequence mysequence increment by start with nomaxvalue nocycle;
釋一increment指明當前序列號遞增的值缺省為start with指定當前序列號的起始值缺省為
maxvlaue設定序列號的最大值缺省為nomaxvalue即沒有最大值同理minvalue就是設定序列號的最小值
cycle設置在序列達到最大值之後是否重新從開始循環缺省為nocycle即不循環
釋二實際上nomaxvalue在實現的過程中是有最大值的大約是的次方同理nominvalue是的負次方
而且在使用序列的時候一般不應該讓它循環避免出現預期以外的數據的重復
釋三cache設置是否進行緩沖存儲它的缺省值不是nocache而是cache
也就是說cache會先生成個序列號備用當序列號被用掉了一個之後它就會再補充上一個
這種通過備用的方式能夠使程序運行效率會高一點但是緩存序列號有可能會有丟失倒不是出錯只是不連續而已
釋四order保證生成的序列號一定是按照請求的順序其實對於單進程或者單線程的程序(即非並發性的訪問)來說沒有什麼差別
若在並發(也叫並行)工作模式下多個請求同時請求同一個序列號的時候分配給它們序列號就有可能是不保證順序的
只是順序不保證但值還是唯一的不會重復實際上它對我們來說影響不大比如有兩個請求先後同時到達
如果用order來保證的話那麼先到達的請求所請求到的序列號就會是後到達的就是
但如果nooder就可能出現先到達的請求得到的反而是後到達的可能是因為中間有一個交替的過程
缺省為noorder即不保證序列號是按照請求順序生成的
使用select mysequencenextval from dual;其實它會新生成一個序列號返回一個整數值
select mysequencecurrval from dual;取出當前的序列號這種直接的查詢意義不大
insert into test values(mysequencenextvalTom);這才是真正的使用序列
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 gt for emp;
使用select * from gt;
刪除drop synonym gt;
說明若不加public那麼定義的同義詞則只能在當前方案(用戶)的環境中可用其它用戶則無法使用該同義詞
而加了public後其它的方案(用戶)便也可以使用這個同義詞了
但必須是具有相應權限的用戶(如DBA)才有資格將同義詞定義為public類型
From:http://tw.wingwit.com/Article/program/Oracle/201311/19081.html
