Oracle 學習筆記（2）

 約束 constraint
----語法格式
---主鍵 primary key
CONSTRAINT PK_Table_name PRIMARY KEY(column_name1,column_name2,...)
---外鍵 foreign key
CONSTRAINT FK_Table_name_column FOREIGN KEY(column_name1,column_name2,...) REFERENCES table_name(column_name1,column_name2,...)
---唯一性 unique
CONSTRAINT UN_Table_name_column Unique(column_name1,column_name2,...)
---check 約束
CONSTRAINT  CK_Table_name_column CHECK (條件)
--例如
CONSTRAINT CK_EMP_M_SEX CHECK(emp_sex='1' OR emp_sex='0')
---其中 主鍵,外鍵,唯一性 約束可以定義在多列上,check 只能定義在 一列上,一列可以定義多個 CHECK
---如何查看有關約束的信息
SELECT owner,constraint_name,constraint_type,table_name FROM User_Constraints
---查看約束定義在哪個表的哪個列上
SELECT owner,constraint_name,table_name,column_name FROM User_Cons_Columns
---約束的維護
---語法格式:
---1,刪除約束 drop
ALTER TABLE table_name DROP  PRIMARY KEY
ALTER TABLE table_name DROP  CONSTRAINT  constraint_name [可以是主鍵/外鍵/CHECK/UNIQUE] [CASCADE]  
---CASCADE 的作用是可以關閉有完整性關系的約束,如,當要刪除某個主鍵時,提示有外鍵指向的錯誤時,可在 放棄
---主鍵約束的語句后 加 CASCADE
--2,禁用/啟用約束 disable/enable
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name [可以是主鍵/外鍵/CHECK/UNIQUE] [CASCADE]
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name  [可以是主鍵/外鍵/CHECK/UNIQUE] [CASCADE]
---修改主鍵/外鍵
---如果主鍵/外鍵已經存在﹐只能先刪除﹐再增加
---例子:
alter table sysprog drop primary key pk_sysprog;----------刪除主鍵
alter table sysprog add constraint PK_SYSPROG primary key("SYS_NO","PROG_SEQ");---增加主鍵

alter table SYSPROG add constraint FK_SYSPROG_SYSNAME foreign key (SYS_NO)  --------增加外鍵
references SYSNAME (SYS_NO);
---外鍵約束注意事項：

----定義外鍵時，外鍵字段的數據類型和長度要與參考的主表的字段的數據類型和長度相同
----delete
----只能先刪除 從表(子表),再刪除主表(父表)
----insert
--只有子表的外鍵存在于父表,才能插入
----update
--同時要考慮到主表和從表

---------CHECK 約束

-----------------------------------------------------------------------------------------------------------------------------

修改表(alter table tableName )
   有四中類型﹕
   增加列alter table tableName add  (column1  dataType  [isNull,UNIQUE]﹐column2  dataType  [isNull,unque],…..)
   刪除列alter table tableName drop(column1,column2….) 或 alter table tableName drop column column1 //(方法2一次只能刪除一列)
   修改列alter table tableName modify (column1 dataType(n)   [isNull  UNIQUE],column2 dataType(n) [isNull unque],….)
   修改列的類型﹐長度﹐是否為空等
         ALTER TABLE ableName add  (column1 datatype [DEFAULT 'value'] [NOT NULL] ) --增加有默認值的列,且不為空   
修改表會對系統的效率帶來很大的沖擊﹐一個折衷的方案是在 alter table 語句中使用 set unused 子句
例如﹕將某列設置成不可用的(unused):
    alter table table_name set unused(column_name1,column_name2..)或
    alter table table_name set unused column column_name //一次只能設置一列
    刪除不可用的列﹕alter table table_name drop unused columns;
--------------截斷表﹕當一個表中的數據已經不再使用時可使用 truncate table ,它有如下特性
                     1﹐刪除表中的所有數據行﹐但是保留表的結構
                     2﹐如果沒有備份﹐鎖刪除的數據是無法恢復的
                     3﹐釋放表所占用的磁盤空間
                     4﹐它并不觸發表的刪除觸發器
             它與 drop table 語句的特性 的區別﹕
                     1﹐它刪除表中所有的數據行和表的結構
                     2﹐它刪除表的所有索引
                     3﹐如果沒有備份﹐所刪除的表無法恢復
                     4﹐它提交所有挂起的事物
                     5﹐所有基于該表的視圖和別名依然保留但是已經無效

-----------------------------------------------------------------------------------------------------------------------------

交易控制命令: rollback,commit,autocommit,savepoint
-----隱示提交:即使沒有直接下commit命令,有些操作,如 quit ,exit,以及數據庫定義語言(DDL)的命令也會使提交命令發生

轉義符:用 escape 定義轉義字符﹐一般定義轉義字符為 / ﹐但是也可以定義為其它字符﹐如 ~,/ 等

update/insert 與 select 的配合
UPDATE emp_m SET emp_age =(SELECT emp_age + 10 FROM emp_m WHERE emp_no = '0001')
INSERT INTO emp_m_copy SELECT * FROM emp_m

 

刪除重複記錄，只保留第一條．
delete from dumpy_part a
where a.rowid <>(select min(rowid) from dumpy_part b where a.id = b.id )

 

外連接 替代 not in ,可以提高查詢效率
SELECT emp_no FROM emp_m WHERE emp_no NOT IN (SELECT emp_no FROM emp_d)
--等價于(但是效率較高)
SELECT emp_m.emp_no,emp_d.emp_no from emp_m,emp_d where emp_m.emp_no=emp_d.emp_no(+) AND emp_job_no IS NULL;

 

單行子查詢包括﹕where單行子查詢,having單行子查詢﹐from單行子查詢
---------where單行子查詢
select * from emp_m
where emp_no =(select emp_no from emp_d where emp_job_no='c123');
---------having單行子查詢
select avg(emp_salary),min(emp_salary),max(emp_salary)
from emp_d
having avg(emp_salary)> min(emp_salary)

---------from 子句中的單行查詢子查詢
select emp_name from emp_m a,(select emp_no,emp_job_no,emp_salary from emp_d) x
where a.emp_no=x.emp_no

--------------多行子查詢﹐包括﹕in 多行子查詢﹐all 多行子查詢﹐any 多行子查詢
-----------------in 多行子查詢
select * from emp_m
where emp_no in (select emp_no from emp_d)
-----------------any 多行子查詢
select * from emp_m
where emp_age < any (select avg(emp_age) from emp_m group by emp_no)
---
select * from emp_m
where emp_age = any (select avg(emp_age) from emp_m group by emp_no)
等價于
select * from emp_m
where emp_age in (select avg(emp_age) from emp_m group by emp_no)
-------------any 多行子查詢
select * from emp_m
where emp_age > all (select avg(emp_age) from emp_m)

-----------------------------------多列子查詢﹕與單列子查詢不一楊﹐多列子查詢要返回多列﹐多列子查詢
-----------------------------------又分為成對比較子查詢和非成對比較子查詢
---------成對子比較多列查詢
----哪些員工的工資在本部門最高﹖
select emp_no,emp_dept_no,emp_salary from emp_d
where (emp_dept_no,emp_salary) in (select emp_dept_no,max(emp_salary) from emp_d group by emp_dept_no)
--------非成對比較多列子查詢
----哪些員工的工資與某一職位的最高工資相同
select emp_no,emp_dept_no,emp_salary from emp_d
where emp_salary in (select max(emp_salary) from emp_d group by emp_dept_no)
and emp_dept_no in (select distinct emp_dept_no from emp_d)