表管理

主鍵 外鍵

主鍵:唯一辨別一條記錄，不能有重複的，不允許為空.

     用來保證資料完整性

     主鍵隻能有一個

外鍵:表的外鍵是另一表的主鍵, 外鍵可以有重複的, 可以是空值

     用來和其他表建立聯系用的

     一個表可以有多個外鍵

1.建立一張學生表

SQL> create table t_stu(  

     stuid number(10) primary key,  

     stuname varchar2(20) not null,  

     stusex varchar2(3) default 'nan' check(stusex in('nan','nv')));  

2.建立一張課程表

SQL> create table t_couse(  

    couseid number(10) primary key,  

    cousename varchar2(20) not null,  

    cousetype varchar2(4)); 

3.建立一張學生課程成績表（包括主外鍵）

SQL> create table t_score(  

    scoreid number(10) primary key,  

    stuid number(10) references t_stu(stuid),  

    couseid number(10),  

    constraint fk_couseid foreign key(couseid)  

    references t_couse(couseid)  

    on delete cascade);        

__________________________________________________________

建立表空間

cd /oracle/app/oracle/oradata/orcl/

[oracle@sq orcl]$ ls

SQL> create tablespace aa datafile '/oracle/app/oradata/TEST/aaa.dbf' size 50M;

[oracle@sq orcl]$ ls (目錄下多出一個aaa.dbf檔案)

SQL> create table a01 tablespace aaa as select * from sys.dba_objects;

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

驗證資料類型:

DUAL 表用途

常用沒有目标表的select語句中

SQL> select user from dual;(檢視目前連接配接使用者)

SQL> select sysdate from dual;(檢視目前系統時間)

SQL> select systimestamp from dual;(檢視目前系統時間)

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

(檢視目前系統時間)

SQL>  select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff') from dual;(檢視目前系統時間)

SQL> select 1+2 from dual;

==========================================================

查詢rowid

檢視t3表中的rowid

SQL> select rowid,t.* from t10 t;(rowid為表中的僞列 不能插入資料)

1.scott登陸

2.對象編号，所有的對象在資料庫裡面都有一個編号

SQL>  select dbms_rowid.rowid_object(rowid) from emp t;

結果中dbms_rowid.rowid_object = 51148

2.驗證 51148 是scott使用者下的 emp表

SQL> conn sys/123456 as sysdba

SQL> select * from dba_objects t where t.object_id=51148;

SQL> select * from dba_data_files;(檢視檔案号)

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

檢視t10表中 僞列rowid中 id,塊,資料檔案:

SQL> select dbms_rowid.rowid_row_number(rowid) ROWID#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_relative_fno(rowid) FILE# from t10;

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

2.檢視所在的資料檔案

select dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_relative_fno(rowid),t.*from emp t;

結果:dbms_rowid.rowid_relative_fno=4 (表示對應4号檔案)

sys登陸(對應的檔案)

SQL> select FILE_NAME,FILE_ID from dba_data_files;

3.查詢所在塊

select dbms_rowid.rowid_block_number(rowid),t.* from emp t;

dbms_rowid.rowid_block_number 對應的是所在的塊

4.檢視行号

select dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_row_number(rowid),t.* from emp t;

dbms_rowid.rowid_row_number(rowid) 對應的是行号

====================================================

建立表前的思考

1 将表存放在不同的表空間

通常單使用者模式下表對于表空間的分散存放并不能提升io性能

便于管理

有利于并發性能提升

2 使用本地管理表空間存儲表避免塊碎片

本地管理表空間自動進行碎片整理一直提升查詢性能

3 使用合适的資料類型

     不痛的資料類型性能不同（整型長高于字元串型）

4 使用分區表提升性能

      适合超過10G大小的表

1.建立普通表

create table t1(id int);

2.分區表

create tablespace ts01 logging datafile '/oracle/app/oradata/TEST/ts01.dbf' size 10m;

create tablespace ts02 logging datafile '/oracle/app/oradata/TEST/ts02.dbf' size 10m;

create tablespace ts03 logging datafile '/oracle/app/oradata/TEST/ts03.dbf' size 10m;

create tablespace ts04 logging datafile '/oracle/app/oradata/TEST/ts04.dbf' size 10m;

建立分區表(範圍分區)(2001之前放在ts01,2002年之前方式ts02)

SQL> create table test123(id number,createdate date)

    partition by range(createdate)

    (

    partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace ts01,

    partition p2 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace ts02,

    partition p3 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace ts03,

    partition pmax values less than (maxvalue) tablespace ts04

   );

'2004-01-01'

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

建立hash 分區表

通常應用在不能劃分範圍的表

SQL> create table test6 (id number,name varchar2(10))

    partition by hash(name)

    partitions 4

    store in (ts01,ts02,ts03,ts04);

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

建立清單分區

該分區的特點是某列的值隻有幾個，基于這樣的特點我們可以采用清單分區。

比如城市,省份

SQL> create table test122 (id number,name varchar2(10),city varchar2(10))

    partition by list(city)

    partition p1 values('bj','sh') tablespace ts01,

    partition p2 values('gz','sz') tablespace ts02,

    partition p3 values('cc','jl') tablespace ts03,

    partition p4 values(default) tablespace ts04

    );

檢視分區表

SQL> select * from user_tab_partitions

複合分區

SQL>create table test111 (id int,createdate date)

    partition by range(createdate) subpartition by hash(id)

    subpartitions 3 store in(ts01,ts02,ts03)  

    (partition p11 values less than(to_date('2001-01-01','yyyy-mm-dd')),

     partition p12 values less than(to_date('2002-01-01','yyyy-mm-dd')),

     partition p13 values less than(maxvalue)

);

先是根據交易日期進行範圍分區，然後根據ID将記錄散列地存儲在三個表空間中。

insert into test111 values(22,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(25,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(33,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(28,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(39,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(55,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(178,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(83,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(99,to_date('2000-12-12','yyyy-mm-dd'));

insert into test111 values(67,to_date('2000-12-12','yyyy-mm-dd'));

SQL>create tablespace ts05 logging datafile '/oracle/app/oradata/TEST/ts05.dbf' size 10m;

SQL>create tablespace ts06 logging datafile '/oracle/app/oradata/TEST/ts06.dbf' size 10m;

SQL>create table test2 (time DATE,province VARCHAR2(10))

partition by range(time) subpartition by list (province)

(

partition p1 values less than (to_date('2004-1-1','YYYY-MM-DD'))

subpartition sp1 values ('BJ') tablespace ts01,

  subpartition sp2 values ('SH') tablespace ts02,

  subpartition sp3 values ('SZ') tablespace ts03,

  subpartition other values(DEFAULT) tablespace ts04

  ) ,

partition p2 values less than (to_date('2005-1-1','YYYY-MM-DD')) 

subpartition sp12 values ('BJ') tablespace ts01,

  subpartition sp22 values ('SH') tablespace ts02,

  subpartition sp32 values ('SZ') tablespace ts03,

  subpartition other2 values(DEFAULT) tablespace ts04

  )

SQL> insert into test2 values(to_date('2003-12-12','yyyy-mm-dd'),'BJ');   

多插些資料,發現ts01變大了

===========================================

分區合并

範圍分區合并

SQL> alter table test123 merge partitions p1,p2 into partition p12;

分隔分區

SQL> alter table test123 split partition pmax at(to_date('2004-01-01','yyyy-mm-dd'))

     into (partition p5,partition pmax);

把原來的pmax分區 拆分成2個分區

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

建立新的分區

create tablespace ts05 logging datafile '/oracle/app/oradata/TEST/ts05.dbf' size 10m;

SQL> alter table test123 drop partition pmax;

SQL> alter table test123 add partition p15 values less 

  2  than(to_date('2005-01-01','yyyy-mm-dd'))tablespace ts05;

create tablespace ts06 logging datafile '/oracle/app/oradata/TEST/ts06.dbf' size 10m;

SQL> alter table test123 add partition p16 values less 

  2  than(maxvalue)tablespace ts06;

============================================

高水位線

SQL> create table t1 as select * from dba_objects;

SQL> insert into t1 select * from t1;

SQL> /

SQL> commit;

SQL> select segment_name,blocks from user_segments where segment_name='T1';

SQL> analyze table t1 compute statistics;

(analyze分區表,即産生統計資訊)

SQL> select table_name,blocks,empty_blocks,avg_space from tabs where table_name='T1';

**

 SQL> delete from t1 where OBJECT_ID>1000;

發現水位線沒變

SQL> create tablespace k1 logging datafile '/oracle/app/oracle/oradata/orcl/kkk1.dbf' size 64m autoextend on next 65m maxsize 1000m extent management local;

SQL> alter table t1 move tablespace k1;

SQL>  select table_name,blocks,empty_blocks,avg_space from tabs where table_name='T1';

發現水位線已經收縮

臨時表

1.建立會話級别臨時表

SQL> create global temporary table temp_tbl(col_a varchar2(30)) 

  2  on commit preserve rows;

插入資料

insert into temp_tbl values('test session table');

SQL> select * from temp_tbl;

退出會話再重新登陸

SQL> exit

[oracle@sq ~]$ sqlplus sys/123456 as sysdba

SQL> select * from temp_tbl;  (沒有資料了)

2.建立事務級别臨時表

SQL> create global temporary table temp_tbl1(col_a varchar2(20))

  2  on commit delete rows;

SQL> insert into temp_tbl1 values('transaction table');

送出

SQL> select * from temp_tbl1;

(沒有資料了)

===================================================

截斷表

删除表中的所有資料

删除速度快，不生成undo資料，隻生成很少的日志

删除表中所有資料并回收使用的空間

對應的索引資料也被清除

SQL> create table test19 as select * from dba_objects;

SQL> insert into test19 select * from dba_objects;

SQL> select count(*) from test19;

  COUNT(*)

----------

     99490

SQL> truncate table test19 reuse storage;

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='TEST19';

發現資料塊并沒有釋放,但降低了水位線

================================================

删除表

drop table t1;

delete from t1;

Truncate 清空表所有資料，生成最少量日志與undo資料

Delete 不帶where條件，清空表所有資料，生成大量日志undo資料

Drop   在删除資料的同時删除表結構

添加列

SQL> alter table t1 add (age1 number(3));

修改列屬性

SQL> alter table t1 modify (age int);

======================================

SQL> create table tg (rr date);

SQL> insert into tg values(to_date('2012-03-12 11:11:11','yyyy-mm-dd hh24:mi:ss'));

SQL> select * from tg;

     本文轉自陳繼松 51CTO部落格，原文連結：http://blog.51cto.com/chenjisong/1737385，如需轉載請自行聯系原作者