表的建立修改及增刪改查-DML操作

1.表名和列的命令規則：

必須以字母開頭，長度不能超過30個字元。不能使用ORACLE保留字。只能使用A-Za-z0-9$#等。

表 TABLE包括：分割槽表，TABLE PARTITION       簇CLUSTER  索引表INDEX-ORGANIZED TABLE（IOTs)

2.支援的資料型別：

標量資料型別：

varchar2 最大值4000字元 變長字元型資料

char 最大值2000字元，定長字元型資料

date 日期型資料，取值範圍從公元前4712.1.1到9999.12.31    timestamp 精確到毫秒

number   數字型資料，

clob 用於在資料庫中儲存定長位元組的大資料物件，如WORD檔案。字元型大物件最大4G 

blob 儲存大的無結構二進位制物件，如照片或幻燈片。

bfile 用於在資料庫外的作業系統檔案中儲存大的無結構的二進位制物件，如電影

raw  在資料庫直接儲存二進位制資料。最大長度2000個位元組

LOGN LONG RAW，ORACLE 8  劈頭蓋臉資料庫中儲存二進位制影像文字等。

3.關於ROWID

在使用者向表中插入一行資料時，ORACLE會自動在這一行資料加上一個ROWID，每行都有一個唯一ROWID，ORACLE利用ROWID定位資料行。並不顯式儲存為一列的值，是訪問一個表中行的最快機制。

000000 FFF BBBBBB RRR

資料物件號 相應檔案號塊號 行號

資料物件號，被賦予每一個物件，在一個資料庫中是唯一的。相對檔案號，對同一個表空間中每一個檔案是唯一的。塊號，為相對檔案中包含資料行的塊的位置。行號，標識塊頭中行目錄位置。

SQL> select ename,rowid from scott.emp;

ENAME     ROWID

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

SMITH     AAAR3sAAEAAAACXAAA

ALLEN     AAAR3sAAEAAAACXAAB

WARD      AAAR3sAAEAAAACXAAC

JONES     AAAR3sAAEAAAACXAAD

SMITH行的資料物件號為AAAR3s,相對檔案號AAE，塊號AAAACX,行號AAA

#############################

ORACLE資料行結構

行頭 row header，儲存行中列數，遷移資訊，行鎖狀態。

行資料，一系列列長和列值組成

列長 columnlength,一般需要一個位元組，列長度超過250位元組，列長為3個位元組

列值 columnvalue,列的實際值緊接列長位元組後存放。

建立表時，應將不同表放在不同表空間中。

使用本地管理表空間以避免碎片，在表中使用若干標準EXTENT尺寸減少表空間碎片。

4.建表的幾種方式

create table語句建表    要指明表名，列名，列資料型別，列寬度，是否有預設值

用查詢語句做子句建表。此時新建表的內容將是所查詢表的相應列的內容。

create table t2 as select ename name,salsalary from emp;

create table t2 as select * from emp;

用假條件，只對立表結構，不要資料

create table t3(c1,c2,c3) as selectename,empno,sal from emp where 9=1;

手動建立一個表，有五個列，資料型別4位數字，20個變長，2位定長，生日資料型別為日期，獎金最多7位，有兩位小數，即最多9999.99

create table student(xh number(4),xmvarchar2(20),sex char(2),birthday date,sal number(7,2));

 

5.修改表

修改列的資料型別、字長

alter table t2 modify(name char(4));

修改表的名稱,表的OWNER或SYSDBA修改

rename t2 to t_2;

修改列名稱

alter table t3 rename column c1 to name;

表註釋和列註釋

修改一列：

alter table emp4 modify test varchar2(20);

刪除一列：

alter table emp4 drop column test;

給表中新增列並設定預設值時：alter table test add new number(9) default 22;
該語句會對所有記錄進行填值操作，對於現場tab_large中存在3000W條左右資料，該步驟需要執行數小時。
可以將該語句改為：
alter table test add newcol2 number(9);
alter table test modify newcol2 number(9) default 2;
該語句只會對新增的記錄進行填值操作，只需要數秒。如果需要對原來的值進行操作，可以在升級後進行update操作，這樣能夠節省大量的升級時間。

 

6.刪除表

未真將表刪除，改為系統命名錶，放在回收站,閃回時用到

drop table t2;

show recyclebin;

select * from user_recyclebin;

從回收站將刪除的表還原

flashback table t2 tobefore drop;

從回收站將刪除的表還原並改名

flashvack  table t2 to  before drop rename tt2;

從回收站刪除指定表

purge table t2

清空回收站

purge recyclebin;

直接刪除，不放在回收站

drop table t2  purge;

 

7.插入資料

ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd';

插入全部列的值

insert into student values('001','白不白','男','2012-12-23',99);

插入部分欄位

insert into student (xh,xm,sex)values('002','白白白','女');

插入空值NULL

insert into student (xh,xm,sex,birthday)values('003','白不','男',null);

使用函式插入資料

insert into t1(c2) values(sysdate);

日期轉換

insert into t29c1) values(to_date('20121223','yyyymmdd')

 

子查詢插入，相當於複製表資料，也可以在建立表時用子查詢建立

insert into d1 select * from dept;

insert ito emp select * from emp wheredeptno=30;

8.修改資料

修改資料：

update student set sex='女' where xh='001';

一次修改兩列資料

update student set sex='女',sal='19'where xh='001';

使用表示式修改

update student set sal=sal+1;

子查詢修改

update emp2 set dname=(select dname fromdept where dept.deptno=emp2.deptno);

9.刪除

刪除所有行

delete t1;

刪除符合條件行

delete emp2 where sal>2000;

--刪除表中的所有記錄，表結構還在，不寫日誌，無法找回刪除的記錄，速度快。DDL語句

truncate TABLE student;

truncate table <tab_name>
這樣預設會回收 Extent 的，應該會慢的。

如果是 truncate table <tab_name> resuse storage  ，這樣快

truncate表時如遇到主外來鍵約束引起的報錯，可以臨時關閉相應約束。

SQL> truncate table table_name;

        truncate table table_name * ERROR 位於第 1 行: ORA-02266: 表中的唯一/主鍵被啟用的外部關鍵字引用
SQL> alter table dorm_employee disableprimary key cascade; 表已更改。
SQL> truncate table dorm_employee; 表已截掉。
SQL> alter table dorm_employee enableprimary key; 表已更改。

10．還原點的設定和使用

提交COMMIT或ROLLBACK後，所有還原點失效。

設定還原點

Savepoint a;

進行修改操作

Savepoint b;

進行刪除操作

Savepoint c;

插入操作；

Rollback to a/b/c

可以回滾到設定還原點的狀態。

11.分頁的操作可以按rowid,RK分析函式。Rownum來分頁

下面最主要介紹第三種：按rownum來分

1. rownum 分頁

   SELECT * FROM emp;   

 2. 顯示rownum[oracle分配的]

SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;

 rn相當於Oracle分配的行的ID號

3.挑選出6—10條記錄

先查出1-10條記錄

SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <=10;

如果後面加上rownum>=6是不行的，

4. 然後查出6-10條記錄

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHEREROWNUM <= 10) WHERE rn >= 6;

5. 幾個查詢變化

a. 指定查詢列，只需要修改最裡層的子查詢

只查詢僱員的編號和工資

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROMemp) e WHERE ROWNUM <= 10) WHERE rn >= 6;

b. 排序查詢，只需要修改最裡層的子查詢

工資排序後查詢6-10條資料

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROMemp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;