PL/SQL學習筆記(五)

　　創建和管理表

　　對象

　　表（TABLE）基本的存儲單位由行和列組成

　　表名和列名（使用規則）
必須是字母開頭
必須是的字符長度
只能包括AZaz_$#
在同一個用戶下不能頭重名的對象
不能是ORACLE的保留字

　　創建需求
必須有
 CREATE TABLE 權限
 足夠的存儲空間
語法
CREATE TABLE [SCHEMA（方案）]TABLE
（COL DATATYPE [DEFAULT默認值][]）

　　當前用戶所有的表
select table_name from user_tables;

　　當前用戶所有的對象
desc user_objects

　　當前用戶對象的別名
select * from cat;

　　字段類型
 VARCHAR(size)   變長字符串類型
 CHAR(size) 定長字符串類型
 NUMBER(ps)p位整數s位小數  
 DATE 
 DATETIME 秒級最多可以到小數點後的位
 TIMESTAMP 帶有小數秒的日期
  TIMESTAMP WITH TIME ZONE 帶時區的類型
  TIMESTAMP WITH LOCAL TIME ZONE 帶時區的並會進行時區轉換的類型（同一時間在不        同地區看到的時間）

　　INTERVAL YEAR TO MONTH 按年和月的間隔存儲的類型
  INTERVAL TEAR() TO MONTH
    
 INTERVAL DAY TO SECOND 按天小時分和秒的間隔存儲的類型
  INTERVAL 
 LONG 變長的長字符串類型
 CLOB 字符類型 GIGABYTES 
 RAW  二進制類型與CHAR對應
 LONG RAW 二進制類型與LONG對應
 BLOB
 BFILE 以文件的形式存儲在操作系統中
 ROWID 表中行的唯一地址（行地址）

　　方案一個用戶所有對象的命名集合

　　如果想訪問其他用戶或方案的表要加上用戶或方案作為前綴

　　必須指明
 表名稱
 列名列類型和長度
用戶表
 被用戶創建和維護的一些表
 包括了用戶自己的信息
數據字典表
 被ORACLE數據庫創建和維護的一些表
 包括了數據庫的信息

　　CTAS（子查詢建表）

　　CREATE TABLE table_name
[(colcoltype)]
as subquery(子查詢);

　　創建的表的列的數目匹配子查詢的列的數目
使用子查詢的列的名字和默認值定義表
注
*被創建表的字段名要遵循如果沒有字段別名和子查詢中沒設置別名的話使用子查詢中的列名
   如果有別名使用別名如果有字段列表（[(colcoltype)]）在被創建的表中使用字段列表
*有字段列表與子查詢的列要匹配
*當沒有字段列表的時候而在子查詢中有表達式的時候一定要在表達式後要加上別名 
*只會把屬性當中的非空屬性復制過來其他的比如約束條件關聯都不會復制過來

　　使用ALTER TABLE 語句可以

　　*在表中增加一個新列

　　語法ALTER TABLE table add (col datatype [default]);
新增加的字段一定是放在表的最後

　　*修改表字段的類型和長度

　　ALTER TABLE table modify (col datatype [default]);
對默認值的修改只會影響到新插入的行
如果字段下有值的話類型的修改成功率很小（要修改數據類型要修改的列必須為空即沒有數據項）
CHAR類型不能修改長度

　　*刪除表字段

　　ALTER TABLE table DROP COLUMN (COLUMN_NAME_LIST);

　　I版可以修改列名 

　　*SET UNUSED 設置字段為不可用

　　原理清楚掉字典信息（撤消存儲空間）不可恢復
可以使用 SET UNUSED 選項標記一列或者多列不可用
使用DROP SET UNUSED 選項刪除被被標記為不可用的列

　　語法
ALTER TABLE table SET UNUSED (COLlist多個) 或者  ALTER TABLE table SET UNUSED COLUMN col單個;
 
ALTER TABLE table DROP UNUSED COLUMNS;

　　刪除表

　　刪除關聯drop table table_name cascade;

　　改對象名
 RENAME 對象原名 TO 要改的對象名
注
    必須是對象的所有者才能進行改名的操作

　　截取
不能回滾
刪除表中所有數據
釋放存儲空間
語法 
TRUNCATE TABLE 表名稱

　　DELETE 也可以刪除所有行但
可以回滾
不釋放存儲空間

　　給表加注釋COMMENT
comment on table table_name is 注釋內容;

　　約束條件
如果經常用到約束條件的話最好自己命名
當定義約束的時候可以將定義的語句作為CREATE TABLE 中的參數的一部分來完成

　　表級別約束定義
CONSTRAINT 約束名 約束條件（字段名）

　　約束在表上強制了規則
如果有參照的花約束防止表的刪除
ORACLE支持的約束條件
NOT NULL 非空
         特點唯一一個只能在列級定義的約束條件                                                                           
UNIQUE 唯一
  允許有空值（空值不做比較）
 特點當創建約束的時候系統會自動創建對應其的索引
PRIMARY KEY 主鍵
   特點當創建約束的時候系統會自動創建對應其的索引
  在一個表中只允許一個主鍵  
FOREIGN KEY 外鍵 
 外鍵參照的一定是主表的主鍵或唯一鍵
 保證子表外鍵字段的值一定是主表中的被參照字段值的真子集
 當主表字段被參照的時候其值不允許被直接刪除
CONSTRAINT 約束名 FOREIGN KEY （外鍵字段名） REFERENCES 主表名（主表字段名）;

　　如果在字段列表中定義外鍵就可以不寫 FOREIGN KEY 關鍵字
 如下格式CONSTRAINT 約束名 REFERENCES 主表名（主表字段名）;

　　ON DELETE CASCADE  當主表的行被刪除的時候要刪除子表中參照主表的行
ALTER TABLE TABLE_NAME DROP (PK) CASCADE CONSTRAINTS;把作為主鍵的字段也同時刪除了

　　ON DELETE SET NULL 當主表的行被刪除的時候轉換子表中的參照值為空

　　CHECK

　　定義一個每行都必須滿足的條件
CREATE TABLE table_name
(
 
salary number()
CONSTRAINT 約束名 CHECK(SALARY>)
   
);

　　約束的使用

　　約束的命名給約束命名或者ORACLE服務器將使用SYS_Cn的格式為約束命名

　　創建時期

　　在創建表的同時或者在建表之後

　　定義級別

　　可以在表級定義或列級定義

　　在數據字典中可以查看約束

　　使用ALTER TABLE 語句

　　*添加或者刪除約束條件但是不能修改約束條件
 就算列名上已經有約束條件還可以繼續添加約束條件的
添加 ALTER TABLE table_name ADD [CONSTRAINT ] 約束名 約束條件(column);
刪除 ALTER TABLE table_name drop constraint 約束名; 
 ALTER TABLE table_name PRIMARY KEY CASCADE;刪除主鍵的時候不用約束名
*啟動或禁用約束條件
 ALTER TABLE table_name Disable constraint 約束名; 禁用
 ALTER TABLE table_name ENABLE constraint 約束名; 啟用
            
*通過MODIFY添加 NOT NULL 約束條件（因為NOT NULL為列級約束只能用MODIFY添加）
ALTER TABLE table_name MODIFY(col type NOT NULL);

　　查看約束條件
//desc user_constraints
OWNER 擁有者
CONSTRAINT_NAME 約束名稱
CONSTRAINT_TYPE 約束類型
 
SEARCH_CONDITION  check的條件
 

　　select constraint_name constraint_typesearch_conditionstatus
from user_constraint where table_name=b;

　　
視圖 （VIEW） 一個或多個表的數據集的邏輯表示（虛表不存儲數據）
視圖不能提高查詢的性能
分類
 簡單
  數目一個
  函數不包含
  分組數據不包含
  可以做DML操作
 復雜
  數目一個或多個
  函數包含
  分組數據包含
  不一定能做DML操作

　　視圖也可以用DESC描述
 
創建視圖

　　CREATE [or replace(修改視圖)] [force/noforce] VIEW view_name(col coltype )
as
subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY[CONSTRAINT constraint]];

　　USER_VIEWS 關於視圖的字典

　　修改視圖

　　CREATE OR REPLACE 原視圖名 （字段列表）
AS 子查詢

　　包含
GROUP BY
DISTINCT
ROWNUM
不能對視圖進行刪除操作

　　包含
GROUP BY
DISTINCT
ROWNUM
通過表達式定義的列
不能對視圖進行修改操作

　　包含
GROUP BY
DISTINCT
ROWNUM
通過表達式定義的列
在視圖中沒有包含基表中的 NOT NULL 列
不能對視圖進行插入操作

　　使用視圖的原因

　　為了限制對數據的訪問
為了使復雜的查詢變得簡單
提供了數據的獨立性
提供了對相同數據的不同顯示

　　使用WITH CHECK OPTION 子句創建視圖

　　創建視圖時通過 WITH CHECK OPTION 子句確保執行的DML語句不會引起數據不出現在視圖上
在對視圖做DML操作的時候一定要符合WHERE子句中的條件
CREATE OR REPLACE VIEW empvu as select * from employees
where check option constraint [empvu_ck];

　　WITH READ ONLY
不可以進行DML操作

　　刪除視圖

　　DROP VIEW view_name;

　　行內視圖
是一個在SQL語句中使用的帶有別名的子查詢該子查詢放在FROM之後
  

　　TOPN:
select [col_list]rownum rank(排名)
from (select [col_list] from table_name order by topn_col)
where rownum<=n;

　　序列（SEPUENCE）產生的順序數字單向遞增或單向遞減且步長相同

　　索引（INDEX）用於提高查詢性能

　　同義詞（SYNONYM）對象的別名
create public synonym e for hremployees;

　　user_synonyms;

　　創建同義詞要有權限訪問的時候也需要權限

From:http://tw.wingwit.com/Article/program/Oracle/201311/16640.html