表其实是数据的‘容器’。oracle有几种类型的表:
- 普通表(ordinary table)又叫堆组织表。
- 聚簇表(clustered table)
- 分区表(partition table)
- 外部表(external table)
- 临时表 (temporary table)
- 索引组织表(index-Organized table IOT)
聚簇是表的一种特殊结构,一个聚簇有多个表组成,几个表共享相同的数据块。一个聚簇有一个或者多个公共的列,多个表共享这些列(聚簇关键字 Cluster Key)。
Oracle 把多个表的数据物理的存储在一起,以加速表的连接(join),这是聚簇的特点。
只有在创建聚簇后,才能在聚簇中创建表,在往聚簇表中插入数据之前必须在聚簇上创建聚簇索引。
在应用中满足下面的条件时才适合使用聚簇:
- 建立聚簇表的目的是为了查询而不是为了修改,繁修改的表不适合创建成聚簇表;
- 查询时,常常对聚簇中的多个表进行连接
创建聚簇表的步骤
- 1,创建聚簇。
- 2,把多个表加入到聚簇中(创建聚簇表)
1: select tablespace_name from dba_tablespaces;
2: create cluster tb_cluster (postcode int) tablespace userdb;
3: SQL> create table stu (
4: 2 id int primary key,
5: 3 name varchar2(20) not null,
6: 4 postcode int)
7: 5 cluster tb_cluster (postcode);
8: SQL> create table address_info (
9: 2 postcode int primary key,
10: 3 name varchar2(30),
11: 4 detail varchar2(30))
12: 5 cluster tb_cluster (postcode);
13:
14: 表已创建。
创建索引组织表(index-Organized Table)
1: create table stu(
2: name varchar2(20) PRIMARY KEY,
3: id NUMBER,
4: detail VARCHAR2(100))
5: ORGAINZATION INDEX
6: TABLESPACE users
7: PCTTHRESHOLD 30
8: INCLUDING detail
9: OVERFLOW TABLESPACE myspace;
索引组织表中一定要有主键。ORGAINZATION INDEX 是指定创建的表示索引组织表,pctthreshold 是指定溢出比例,如果超过溢出比例的限制,则溢出部分讲被存储到溢出区中。
including 指定列名,表示从这个列以后的所有列将存储在溢出区中。overflow tablespace 指定溢出表空间。
创建外部表
先创建本地目录F:\temt\data F:\temt\bad F:\temt\log.
首先用sys身份创建目录对象,授权给用户 item
SQL> CREATE OR REPLACE DIRECTORY dat_dir AS 'f:\temtb\data';
目录已创建。
已用时间: 00: 00: 00.09
SQL> CREATE OR REPLACE DIRECTORY log_dir AS 'F:\temtb\log';
目录已创建。
已用时间: 00: 00: 00.04
SQL> CREATE OR REPLACE DIRECTORY bad_dir AS 'F:\temtb\bad';
目录已创建。
已用时间: 00: 00: 00.07
SQL> GRANT READ ON DIRECTORY dat_dir to item;
授权成功。
已用时间: 00: 00: 00.12
SQL> GRANT READ,WRITE ON DIRECTORY log_dir TO item;
授权成功。
已用时间: 00: 00: 00.01
SQL> GRANT READ,WRITE ON DIRECTORY bad_dir TO item;
授权成功。
连接item用户:
conn item
create table fitness_member
(id integer,
name VARCHAR2(14),
city VARCHAR2(30),
age int)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER //指定访问的驱动是ORACLE_LOADER
DEFAULT DIRECTORY dat_dir // 指定数据的默认存放路径
ACCESS PARAMETERS
(
records delimited by newline //一行为一条记录
badfile bad_dir:'empxt%a_%p.bad' //指定坏文件的名字及路径
logfile log_dir:'empxt%a_%p.log'
fields terminated by ',' //指定字段之间是以逗号进行分割的
missing field VALUES are NULL
(id,name,city,age)
)
LOCATION ('temstu.txt') //指定存放数据的文件的名字
)
PARALLEL
REJECT LIMIT UNLIMITED; //表示允许无限制的行发生错误
SQL> select * from fitness_member;
ID NAME CITY AGE
---------- -------------- ------------------------------ ----------
50016 xiao1 shanghai 22
30021 xiao2 shanghai 22
30032 xiaoW beijing 23
20033 xiaoZ guangzhou 24
已选择4行。
注释:一般情况下 %a 数据库的ID;%p 进程ID;
创建临时表
临时表用于 临时存放中间数据.
- 创建事务型临时表
1: create global temporary table gtt2
2: (name varchar2(10),
3: id number,
4: birthday date)
5: on commit delete rows;
- 创建会话型临时表
1: create global temporary table gtt2
2: (name varchar2(10),
3: id number,
4: birthday date)
5: on commit preserve rows;
修改表的定义
添加length列 alter table t add ( length number(8,3));
重命名列 alter table t rename column length to new_length;
改变列的属性 alter table t modify (new_length not null | number (4,1) |encrypt using ‘3DES168’ |DECRYPT)…..非空、改变列的宽带、加密、解密
为 表手工分配一个新的分区 alter table t allocate extent (size 50K);
删除列 alter table t drop (length,address);
表管理技巧
将表移动到一个新的段 Segment
alter table t move
storage (
initial 20K
minextents 2
…
)
将表移动到其他表空间
alter table t move tablespace myspace2;
锁住表
lock table t
in exclusive mode
nowait; //行级锁
锁住指定行
select * from t where name =’**’ for update;
让一个列自动增长:
Oracle没有提供一种便捷的方法实现表中某列的自动增长,但是我们可以使用一种变通的方式实现表中某列的自动增长。
create table myorder 2 (order_NO NUMBER, 3 order_name VARCHAR2(20), 4 order_desc VARCHAR2(20)); create SEQUENCE seq_order_NO 2 START WITH 1 3 MAXVALUE 999999999999999999999999 4 MINVALUE 1 5 NOCYCLE 6 CACHE 100;
create or replace TRIGGER trigger_myorder 2 BEFORE INSERT ON myorder 3 FOR EACH ROW 4 BEGIN SELECT seq_order_NO.NEXTVAL INTO :NEW.order_NO from dual; 5 END; 6 /
创建触发器,在插入数据之前每一行都出发,使order_NO自动自动增加,.NEXTVAL 表示取序列(seq_order_NO)的下一个值
SQL> INSERT INTO myorder
2 (order_name,order_desc)
3 VALUES
4 ('光盘采购','采购公司需要的光盘');
SQL> insert into myorder
2 (order_name,order_desc)
3 VALUES
4 ('大米','要采购来自美国的大米');
已创建 1 行。
SQL> select * from myorder;
ORDER_NO ORDER_NAME ORDER_DESC
---------- -------------------- --------------------
1 光盘采购 采购公司需要的光盘
2 大米 要采购来自美国的大米
可以看到 order_NO一列的值在自动增长。