Oracle主鍵約束的創(chuàng)建,添加和刪除

1、Oracle主鍵約束的創(chuàng)建，添加和刪除2010-06-27 10:40:16| 分類：learning | 字號(hào)大中小訂閱oracle的主鍵約束添加刪除1、創(chuàng)建表的同時(shí)創(chuàng)建主鍵約束一、無(wú)命名create table acco unts (acco unts_nu mber nu mber primary key,acco un ts_bala nee nu mber);二、有命名create table acco unts (acco unts_nu mber nu mber primary key,acco un ts_bala nee nu mber,con stra int yy pri

2、mary key(acco unts_nu mber)；2、刪除表中已有的主鍵約束一、無(wú)命名SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='accou nts：找出主鍵名ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;二、有命名ALTER TABLE ACCOUNTS DROP CONTRAINT yy;3、向表中添加主鍵約束ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);orac

3、le中not null約束是我們用到的最多的約束之一了。我們可以在創(chuàng)建表時(shí)讓系統(tǒng)自動(dòng)指定not null約束的名字來(lái)創(chuàng)建，也可以手動(dòng)的的指定 not null約束的名字來(lái)創(chuàng)建，也可以在 表創(chuàng)建好后手動(dòng)的修改表已達(dá)到增加not null約束的目的。下面是一個(gè)例子create table test_ not_n ull(id nu mber not n ull,n ame varchar2(30),tel varchar2(20) con stra int test_ not_n ull_tel not n ull);在這里我們創(chuàng)建了一個(gè)表，并分別給 id和tel兩列創(chuàng)建了 not null的約束

4、，其中id的約束 是系統(tǒng)自動(dòng)命名的，而 tel的約束使我們手動(dòng)命名的。對(duì)于地二個(gè)列name，oracle系統(tǒng)不會(huì)自動(dòng)增加not null的約束。我們可以在這個(gè)表里面插入一行數(shù)據(jù)，例如：insert into test_not_null(id,tel) values(123,' 88888888 ');我們也還可以使用修改表的方式來(lái)增加not null約束。例如剛才我們沒有在test_not_null上建立空值約束，我們可以現(xiàn)在通過(guò)修改的方式來(lái)建立alter table test_ not_nullmodify (n ame not nu II);如果你執(zhí)行了剛才我們的插入數(shù)據(jù)的

5、操作，你執(zhí)行這句話的時(shí)候可能會(huì)有以下錯(cuò)誤：ORA-02296:無(wú)法啟用(SYS.)-找到空值這是因?yàn)槲覀儎偛旁诓迦霐?shù)據(jù)時(shí)已經(jīng)給name這一列增加了一個(gè)空值，現(xiàn)在又要在這一列上增加not null約束，顯然是不行的。如果實(shí)際問(wèn)題中我們真的遇到這種需求，例如對(duì)于某個(gè)字段我們認(rèn)為原來(lái)可以為空，并且再該列插入了很多空值，后來(lái)我們發(fā)現(xiàn)實(shí)際上是不應(yīng)該為空的，可能會(huì)需先將原來(lái)的空值全部更新到一個(gè)新的有統(tǒng)一意義的值，然后在進(jìn)行空值約束的加入操作。例如我們可以這樣做：update test_ not_n ullset name = 不知道'where n ame is n ull;現(xiàn)在我們?cè)僭黾恿械膎o

6、t null約束是沒有問(wèn)題了。alter table test_ not_nullmodify (n ame not nu II);表修改1創(chuàng)建表：a. 創(chuàng)建xs表中計(jì)算機(jī)專業(yè)學(xué)生的備份Create table xs_jsj as select * from xs where zym='計(jì)算機(jī) '；b. 完整的例子：Create table test (xm char(20) not null,zy varchar(30) default ( 計(jì)算機(jī)')；'-Create tablecreate table DEPT(DEPTNO NUMBER(2) not n

7、 ull,DNAME V ARCHAR2(14),LOC VARCHAR2(13)tablespace USERSpctfree 10in itra ns 1maxtra ns 255storage(in itial 64Kmi nexte nts 1maxexte nts un limited)；-Create/Recreate primary, unique and foreig n key con stra intsalter table DEPTadd con strai nt PK_DEPT primary key (DEPTNO)using in dextablespace USE

8、RSpctfree 10in itra ns 2maxtra ns 255 storage(in itial 64Kmi nexte nts 1maxexte nts un limited)；c. 規(guī)則Alter table命令有許多選項(xiàng)，一個(gè)記住語(yǔ)法的方法是Oracle執(zhí)行這個(gè)操作需要的信息：1) 、你不得不告訴 Oracle你準(zhǔn)備alter什么表：Alter table table_ name2) 、然后，你準(zhǔn)備做什么？Addi ng 個(gè)約束ALTER TABLE table_ name ADD CONSTRAINT3) 、強(qiáng)烈建議但不要求為約束定義個(gè)名字。約束名不需要放在引號(hào)里，但 會(huì)以

9、大寫字母形式存儲(chǔ)在數(shù)據(jù)字典里，ALTER TABLE temp ADD CONSTRAINT pk_temp4) 、表示約束類型將是 Primary Key， Unique , Foreign Key，或 Check 約束ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY5) 、跟在約束類型后面有幾種特殊選項(xiàng)，Primary Key和Unique約束，需要指定約束的列；Check約束需要指定約束規(guī)則。ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID);6) 、Primary Key

10、和Unique約束應(yīng)當(dāng)指定索引的表空間名，使用USINGINDEX TABLESPACE 子句.ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE stude nt_in dex;創(chuàng)建表及索引：create table DEPT(DEPTNO NUMBER(2) not nu II,DNAME V ARCHAR2(14),LOC VARCHAR2(13)tablespace USERSpctfree 10in itra ns 1maxtra ns 255 storage (in itia

11、l 64Kmi nexte nts 1maxexte nts un limited)；con stra int emp_pk primarycreate table employees (emp no nu mber(5), dept no nu mber(3) not n ull,key(emp no ,dept no)/ / 有名的主鍵using in dex tablespace in dex storage (in itial 64Kn ext 64K)alter table DEPTadd con strai nt PK_DEPT primary key (DEPTNO)using

12、in dex tablespace USERS pctfree 10in itra ns 2maxtra ns 255 storage (in itial 64K mi nexte nts 1 maxexte nts un limited);2 .修改表.Alter table table_ name增加新列修改已有列和屬性修改存儲(chǔ)特征刪除約束條件Add colu mn_n ame type default expressi onModify datatype default expressi onStorage storage_clauseDrop drop_clausea. 改變表所在的表

13、空間altertable n ame movetablespacen ewtablespacenu m<imgsrc="<imgsrc="<img例：在表xs中添加兩列.jsj,djsmAlter table xs add(jxj src="">">">ber(1),djsm varchar(40) defau lt 獎(jiǎng)金 1000 '； 在表xs中修改名為djsm的列的默認(rèn)值A(chǔ)lter talbe xs modify(djsm default獎(jiǎng)金 80') 在表中刪除名為jxj和d

14、jxm的列.Alter table xs drop colu mn jxj;Alter table xs drop colu mn djsm; 修改表xs_kc的存儲(chǔ)特征Alter talbe xs pctfree 20 maxtra ns 200 為表xs_jsj加上主鍵Alter table xs_jsj add (constraint“ pk_jsj ” primary key(xh); 增加列語(yǔ)法：ALTER TABLE schema. table_name ADD column_definitiontype例：ALTER TABLE orders ADD order_date DAT

15、E;更改列語(yǔ)法：ALTER TABLE schema. table_name MODIFY column_namen ew_attributes;例：ALTER TABLE orders MODITY (quantity number(10,3),statusvarchar2(15); .更改表名：alter table t ren ame to s; .更改列名alter table t ren ame colu mn n to s;3約束 Oracle數(shù)據(jù)庫(kù)的完整性約束規(guī)則唯一性約束(Unique constraint)唯一性約束可以保護(hù)表中多個(gè)數(shù)據(jù)列，保證在保護(hù)的數(shù)據(jù)列中任何兩行的數(shù)據(jù)都

16、不相同。唯一性約束與表一起創(chuàng)建，在唯一性約束創(chuàng)建后，可以使用ALTER TABLE語(yǔ)句修改。語(yǔ)法：column_ name data_type CONSTRAINT con strai nt_n ame UNIQUE如果唯一性約束保護(hù)多個(gè)數(shù)據(jù)列，那么唯一性約束要作為表約束增加。語(yǔ)法如下：CONSTRAINT con strai nt_n ame (colum n) UNIQUE USING INDEX TABLESPACE (tablespace_ name) STORAGE (stored clause)唯一性約束由一個(gè) B-tree索引增強(qiáng)，所以可以在 USING子串中為索引使用特殊特征，

17、 比如表空間或存儲(chǔ)參數(shù)。CREATE TABLE語(yǔ)句在創(chuàng)建唯一性約束的同時(shí)也給目標(biāo)數(shù)據(jù)列建 立了一個(gè)唯一的索引。F國(guó)內(nèi)最早的網(wǎng)管網(wǎng)站CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN uni que_auto UNIQUE (policy_id,vi n) bitsCN_comUSING INDEX TABLESPACE index STORAG

18、E (INITIAL 1M NEXT 10M PCTINCREASE 0)用戶可以禁用未以性約束，但他仍然存在，禁用唯一性約束使用 ALTER TABLE 語(yǔ)句ALTER TABLE in sured_autos DISABLE CONSTRAIN uni que_ name;刪除唯一性約束，使用 ALTER TABLE.DROP CONSTRAIN 語(yǔ)句 ALTER TABLE insured_autos DROP CONSTRAIN unique_name;注意用戶不能刪除在有外部鍵指向的表的唯一性約束。這種情況下用戶必須首先禁用或刪除外部鍵(foreign key)。 中國(guó)網(wǎng)管聯(lián)盟刪除或

19、禁用唯一性約束通常同時(shí)刪除相關(guān)聯(lián)的唯一索引，因而降低了數(shù)據(jù)庫(kù)性能。經(jīng)常刪除或禁用唯一性約束有可能導(dǎo)致丟失索引帶來(lái)的性能錯(cuò)誤。要避免這樣錯(cuò)誤，可以采取下面的步驟：1、在唯一性約束保護(hù)的數(shù)據(jù)列上創(chuàng)建非唯一性索引。2、添加唯一性約束主鍵(Primary Key)約束 中國(guó)網(wǎng)管聯(lián)盟表有唯一的主鍵約束。表的主鍵可以保護(hù)一個(gè)或多個(gè)列，主鍵約束可與NOT NULL約束共同作用于每一數(shù)據(jù)列。NOT NULL約束和唯一性約束的組合將保證主鍵唯一地標(biāo)識(shí)每一行。 像唯一性約束一樣，主鍵由B-tree索引增強(qiáng)。 網(wǎng)管求職創(chuàng)建主鍵約束使用CREATE TABLE語(yǔ)句與表一起創(chuàng)建， 如果表已經(jīng)創(chuàng)建了， 可以使用ALTER

20、 TABLE 語(yǔ)句。CREATE TABLE policies (policy_id NUMBERCONSTRAINT pk_policies PRIMARY KEY, holder_ name VARCHAR2(40),gen derVARCHAR2(1), marital_status V ARCHAR2(1), date_of_birth DATE );與唯一性約束一樣，如果主鍵約束保護(hù)多個(gè)數(shù)據(jù) 列，那么必須作為一個(gè)表約束創(chuàng)建。中國(guó)網(wǎng)管聯(lián)盟CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40),coverage_beg

21、i n DATE, coverage_term NUMBER, CONSTRAINTpk_insured_autos PRIMARY KEY (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0);禁用或刪除主鍵必須與ALTER TABLE語(yǔ)句一起使用ALTER TABLE policies DROP PRIMARY KEY;ALTER TABLE policies DISABLE PRIMARY KEY;1、創(chuàng)建約束CREATE TABLE stude nts(stude

22、 nt_id VARCHAR2(10) NOT NULL, stude nt_n ame VARCHAR2(30) NOT NULL, college_major VARCHAR2(15) NOT NULL, statusVARCHAR2(20) NOT NULL,stateVARCHAR2(2),lice nse_ noVARCHAR2(30) TABLESPACEstude nt_data;2、創(chuàng)建主鍵ALTER TABLE stude ntsADD CONSTRAINTpk_stude nts PRIMARY KEY(stude nt_id)USING INDEX TABLESPACE

23、stude nt_in dex;3、創(chuàng)建Unique約束ALTER TABLE stude ntsADD CONSTRAINT uk_stude nts_lice nseUNIQUE (state, lice nse_no) USING INDEX TABLESPACE stude nt_in dex;4、創(chuàng)建Check約束ALTER TABLE stude ntsADD CONSTRAINT ck_stude nts_st_licCHECK (state IS NULL AND lice nse_no IS NULL)OR(state IS NOT NULL AND license_no i

24、s NOT NULL);添加check約束alter table emp add con stra int con check(dept_salary>0);con為約束名，dept_salary為字段名5、創(chuàng)建外鍵約束ALTER TABLE stude ntsADD CONSTRAINT fk_stude nts_stateFOREIGN KEY (state) REFERENCES state_lookup (state);6、約束.Alter table table_ name add con stra nts BID primary key (book no);ALERT TAB

25、LE table_ name MODIFY( colu mn1 PRIMARY KEY);1、創(chuàng)建表的同時(shí)創(chuàng)建主鍵約束(1) 無(wú)命名 create table stude nt (stude ntid int primary key not null,stude ntn ame varchar(8),age in t);(2) 有命名 create table students (studentid int ,studentname varchar(8),age int,constraint yy primary key(stude ntid);2、刪除表中已有的主鍵約束(1) 有命名 alt

26、er table stude nts drop con stra int yy;(2) 無(wú)命名 可用 SELECT * from user_cons_columns where .;查找表中主鍵名稱得stude nt表中的主鍵名為SYS_C002715alter table stude nt drop con strai nt SYS_C002715;(3 )使約束失效：alter table tbl_employee disable con stra int fk_emp;刪除約束：alter table tbl_departme nt drop con stra int pk_dept;查

27、詢約束：select CONSTRAINT_NAME from user_co nstrai nts where table_ name='TBL_EMPLOYEE'select CONSTRAINT_NAME,COLUMN_NAMEfromuser_co ns_colu mnswheretable_ name='TBL_EMPLOYEE'3. 刪除表.Drop table table_ name;4、操作表數(shù)據(jù)插入表記錄： .a. in sert into table_ name col1,col2 values (val1,val2);例：Insrt int

28、o xs(xh,xm,) values ( 09' ,to_date( 19860210' , ' yyyymmdd );insertintodeptovalues('100','xieyu nchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)b. 從一個(gè)表中向另一個(gè)表中插入數(shù)據(jù)In sert into table1(col1,col2,col3) select (col1,col2,col3)from otherta

29、blec. 使用子查詢插入數(shù)據(jù)in sert into employee (emp no,en ame,sal,dept no)select emp no,en ame,sal,depto from emp;d. IN SERTINTOEMP(ENAME,HIREDATE)VALUE( ' AA' ,TO_DTE( '19802-09' , ' YYMMI- DD ) .刪除表數(shù)據(jù)：Delete from table_ name where con diti on; .修改表記錄Update table_ name set colu mn_n ame=e

30、xpressi on,where con diti on.基于一張表修改另一張表的數(shù)據(jù)UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO FROM EMPWHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788) .刪除所有記錄但保留表結(jié)構(gòu).Trun cate table table_ name; .查詢數(shù)據(jù)查詢表結(jié)構(gòu)：DESC table_nameSelect en ame,sal,12*sal+100 from emp注:select coun t(d in sti nct(dept no)

31、 from empa.查詢大于平均的：select emp no from emp a,(select avg(sal) as sal_sal from emp) bwhere a.sal>b.sal_sal;如果列中有空值時(shí)，則結(jié)果也為空(關(guān)于 null值的處理(p 4 7).女口： select ename,name,12*sal+comm 如果 comm 的值為 null，結(jié)果也為 null;如上所示,comm為null時(shí).則12*sal+comm也為null;解決方法是用 nvl方法替換.在兩個(gè)表中查詢：以下兩種方式都是一樣的.a.select t_pho ne_operati

32、on. operati on_n amefromt_pho ne_operatio n ,t_pho ne_operati on _chargewhere'159.'t_pho ne_operatio n. operatio n_id=t_pho ne_operati on _charge.operati on_id and t_pho ne_operatio n_charge.ph one_num=b.select operatio n_n ame from t_ph on e_operati on where operati on_id in (select operati

33、 on_idfromt_pho ne_operati on _charge wherephone_num='159.' 使用日期格式顯示日期：select en ame,to_char(hiredate,m'-yydy ) from empselect ename where hiredate>to_date( 1999-12-31'' , ' yyym-dd');使用別名的三種方式：a. select en ame as n ame,sal salary,from empb. select en ame使用連接操作符：Select

34、 en ame | job as用連接字符：Select en ame |'限制重復(fù)的行：Select disti net dept no from emp注意大小寫：Select en ame,job,dept no from emp where job= 使用between .anc運(yùn)算符Select en ame,sal from emp where sal betwee n 1000 and 1500; 使用in運(yùn)算符Select emp no,en ame,sal,mgr from emp where mgr in(23,231,2345); Like運(yùn)算符：(模糊查詢)%代

35、表至多任意字符代表一個(gè)任意字符女口： select ename from emp where ename like顯示第三個(gè)字符為大寫 A的所有信息ENAME ,SAL FROM EMP WHERESELECTname ” , sal*12 ” annual salary ”“ employees ” from emp| ' is a ' | '' |job as'CLERK“ employee details ”'s%'；ENAME LIKE'_A%'a為轉(zhuǎn)義字符)顯示雇員名包含SELECT"_"的雇

36、員信息(其中ESCAPE后的字符ENAME,SAL WHERE ENAME LIKE '%a_% ESCAPE 'a'值的處理(p4 7):)Null運(yùn)算符(關(guān)于null測(cè)試一個(gè)值是否為空：Select ename,mgr from emp where mgr is null ( 注意是 is null ,不是=null)函數(shù)處理NULL值函數(shù)用于將NULL轉(zhuǎn)換為實(shí)際值，其語(yǔ)法為NVL(exp1,exp2).如果是使用NVLNVLexp1是null,則返回exp2，如果exp1不為null,則返回exp1,參數(shù)exp1和exp2是任意相同的數(shù)據(jù)類型如：SELECT EN

37、AME, SAL,SAL+NVL(COM,0) AS " 月收入 ” from EMP;使用NVL2函數(shù)處理NULL值語(yǔ)法為NVL(exp1,exp2,exp3),如果exp1不是null,則返回exp2,如果exp1是 null,則返回 exp3;用WHERE語(yǔ)句限制日期示例一 符合默認(rèn)日期格式select * from emp where hiredate>'01-1 月-82'示例2:不符合默認(rèn)日期格式(需要用TO_DATE函數(shù)轉(zhuǎn)換)SELECT*FROMEMPWHEREHIREDATE>TO_DATE('1994-9-24',

38、9;yyyy-mm-dd');邏輯操作符：And or notOrder by 子句a. 使用order by子句對(duì)查詢數(shù)據(jù)排序asc 升序，默認(rèn)desc降序：重復(fù)總結(jié)：./DISTINCT 不SELECTDISTINCT *,COL UMNALIAS,FROMtable nameWHEREcon diti onORDER BYcolu mn ,expr,alias ASC|DESC依據(jù)多列進(jìn)行排序:Select en ame,dept no ,sal from emp order by dept no ,sal desc分組查詢：常用分組函數(shù)：MAX , MIN ,AVG ,SUM,

39、COUNT,V ARIANCE(取方差),STDDEV (取標(biāo)準(zhǔn)差)當(dāng)使用分組函數(shù)時(shí)，分組函數(shù)只能出現(xiàn)在選擇列表，order by，和 having子句中，而不能出現(xiàn)在where和group by子句中. 當(dāng)執(zhí)行SELECT語(yǔ)句時(shí)，如果選擇列表同時(shí)包含列，表達(dá)式，和分組函數(shù)，那么這 些列和表達(dá)式必須出現(xiàn)在 GROUP BY子句中.select dept no ,avg(sal),co un t(*) from emp group by dept no;having avg(sal)<2000;如果在select語(yǔ)句中同時(shí)包含 group by , having ,以及order by子句

40、，則必須將 order by 子句放在最后.當(dāng)限制分組結(jié)果時(shí)，必須要使用having，而不能在 WHERE子句中使用分函數(shù)限制分組顯示結(jié)果.子查詢：子查詢的作用： 在insert或create table語(yǔ)句中使用子查詢，可以將表數(shù)據(jù)插入到目標(biāo)表中 通過(guò)update子句中使用子查詢，可以修改一列事多列數(shù)據(jù) 通過(guò)在where ,having,start with子句中使用子查詢，可以提供條件值單行子查詢：select en ame ,sal,dept no from emp where dept no=(select dept no from emp where en ame=' scott多行子查詢：select en ame ,job,sal,dept no from