Oracle中tablesinfo_Oracle中建表及表操作

一、创建表

Oracle中的建表语句：

create table 表名(

字段名1 数据类型 列属性,

字段名2 数据类型 列属性,

......

)

-- Create table

create table OA_DM.DM_GY_USER

(

user_id CHAR(32),

username VARCHAR2(100) not null,

loginname VARCHAR2(50) not null,

password VARCHAR2(255) not null,

email VARCHAR2(100),

active CHAR(1) not null,

delete_time DATE,

register_time DATE,

emp_num VARCHAR2(50),

certificate VARCHAR2(200),

sex CHAR(1),

has_image CHAR(1),

telephone VARCHAR2(50),

cellphone_bak VARCHAR2(50),

cert_no VARCHAR2(50),

address VARCHAR2(1000),

postcode VARCHAR2(10),

bz VARCHAR2(2000),

position VARCHAR2(200),

fax VARCHAR2(50),

ldap_unid VARCHAR2(100),

xsxh NUMBER(8),

lx VARCHAR2(200),

cellphone VARCHAR2(50)

)

tablespace TS_OA_DMCS_DATA

pctfree 10

--PCTFREE：默认是10，表示当数据块的可用空间低于10%后，当一个block剩余空间低于10%，就不可以被insert了，只能被用于update；即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

--PCTUSED：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

--假设你一个块可以存放100个数据，而且PCTFREE 是10，PCTUSED是40，则：不断的向块中插入数据，如果当存放到90个时，就不能存放新的数据，这是受pctfree来控制，预留的空间是给UPDATE用的。

--当你删除一个数据后，再想插入个新数据行不行？不行，必须是删除41个，即低于40个以后才能插入新的数据的，这是受pctused来控制的

initrans 1

--每个block都有一个块首部。这个块首部中有一个事务表(Interested Transaction List)。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。这个事务表的初始大小由对象的INITRANS 设置指定

--(Interested Transaction List)事物槽列表是Oracle数据块内部的一个组成部分，，它是由一系列的ITS(Interested Transaction Slot,事物槽)组成,其初始的ITL Slot数量由INITRANS决定的，如果有足够的剩余空间，oracle也会根据需要动态的分配这些slot，直到受到空间限制或者达到MAXTRANS,注意10g以后MAXTRANS被废弃，默认为255。

--事物槽列表用来来记录该块所有发生的事务，一个itl可以看作是一个记录，在一个时间，可以记录一个事务(包括提交或者未提交事务)。当然，如果这个事务已经提交，那么这个itl的位置就可以被反复使用了，因为itl类似记录，所以，有的时候也叫itl槽位。

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

)

--数据库的逻辑结构如下：数据库是由一系列表空间(tablespace)组成，表空间由若干段(segment)组成，段由若干区(extent)组成，区由若干块(block)组成

--当在表空间中创建表时，系统先分配一个初始空间，这个空间大小由initial这个参数决定，此处为64KB,minextents 表示建好表后至少要分配几个区，这里是1个，maxextents 表示表空间最多能分配几个区，这里是无限制

nologging;

-- Add comments to the table

comment on table OA_DM.DM_GY_USER

is '用户表';

-- Add comments to the columns

comment on column OA_DM.DM_GY_USER.user_id

is '用户id';

comment on column OA_DM.DM_GY_USER.username

is '姓名';

comment on column OA_DM.DM_GY_USER.loginname

is '登录名';

comment on column OA_DM.DM_GY_USER.password

is '密码';

comment on column OA_DM.DM_GY_USER.email

is '邮箱';

comment on column OA_DM.DM_GY_USER.active

is '状态||1启动0禁用2注销';

comment on column OA_DM.DM_GY_USER.delete_time

is '删除时间';

comment on column OA_DM.DM_GY_USER.register_time

is '注册事件';

comment on column OA_DM.DM_GY_USER.emp_num

is '员工编号';

comment on column OA_DM.DM_GY_USER.certificate

is '登陆验证方式';

comment on column OA_DM.DM_GY_USER.sex

is '性别||0:男 1：女';

comment on column OA_DM.DM_GY_USER.has_image

is '是否有图片';

comment on column OA_DM.DM_GY_USER.telephone

is '固定电话号码';

comment on column OA_DM.DM_GY_USER.cellphone_bak

is '手机号码';

comment on column OA_DM.DM_GY_USER.cert_no

is '身份证号码';

comment on column OA_DM.DM_GY_USER.address

is '工作所在地';

comment on column OA_DM.DM_GY_USER.postcode

is '邮政编码';

comment on column OA_DM.DM_GY_USER.bz

is '备注';

comment on column OA_DM.DM_GY_USER.position

is '职位';

comment on column OA_DM.DM_GY_USER.fax

is '传真';

comment on column OA_DM.DM_GY_USER.ldap_unid

is 'LDAP_UNID';

comment on column OA_DM.DM_GY_USER.xsxh

is '显示序号';

comment on column OA_DM.DM_GY_USER.lx

is '类型';

-- Create/Recreate indexes

create index OA_DM.IDX_USER_LGOIN_PWD_ACTIVE on OA_DM.DM_GY_USER (LOGINNAME, PASSWORD, ACTIVE)

tablespace TS_OA_DMCS_IDX

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

)

nologging;

-- Create/Recreate primary, unique and foreign key constraints

alter table OA_DM.DM_GY_USER

add constraint PK_DM_GY_USER primary key (USER_ID)

novalidate

using index

tablespace TS_OA_DMCS_IDX

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 8M

next 16M

minextents 1

maxextents unlimited

);

alter index OA_DM.PK_DM_GY_USER nologging;

--oracle日志模式分为(logging，force logging，nologging)

--默认情况是logging，就是会记录到redo日志中，

--force logging是强制记录日志，

--nologging是尽量减少日志。

--FORCE LOGGING可以在数据库级别、表空间级别进行设定、

--LOGGING与NOLOGGING可以在表级别设定。

--force logging和nologging是只记录到redo日志中，归档不归档是另外的设置，但是如果用nologging了，那么显然就算归档的话，归档日志就少了，但是可能不能用于介质回复了，因为有些根本没有记录。

二、表操作

建测试表

create table dept(

deptno number(3) primary key,

dname varchar2(10),

loc varchar2(13)

);

create table employee_info(

empno number(3),

deptno number(3),

ename varchar2(10),

sex char(1),

phone number(11),

address varchar2(50),

introduce varchar2(100)

);

1、重名令

(1)重命名表:rename dept to dt;

rename dt to dept;

(2)重命名列:alter table dept rename column loc to location;

alter table dept rename column location to loc;

2、添加约束

(1)primary key

alter table employee_info add constraint pk_emp_info primary key(empno);

(2)foreign key

alter table employee_info add constraint fk_emp_info foreign key(deptno)

references dept(deptno);

(3)unique

alter table employee_info add constraint uq_emp_info unique(phone);

(4)check

alter table employee_info add constraint ck_emp_info check (sex in ('F','M'));

(5)not null

alter table employee_info modify phone constraint not_null_emp_info not null;

(6)default

alter table employee_info modify sex char(2) default 'M';

3、禁用约束

alter table employee_info disable constraint uq_emp_info;

4、启用约束

alter table employee_info enable constraint uq_emp_info;

5、延迟约束

alter table employee_info drop constraint fk_emp_info;

alter table employee_info add constraint fk_emp_info foreign key(deptno)

references dept(deptno) deferrable initially deferred;

6、添加列

alter table employee_info add id varchar2(18);

alter table employee_info add hiredate date default sysdate not null;

7、删除列

alter table employee_info drop column introduce;

8、修改列

(1)修改列的长度

alter table dept modify loc varchar2(50);

(2)修改列的精度

alter table employee_info modify empno number(2);

(3)修改列的数据类型

alter table employee_info modify sex char(2);

(4)修改默认值

alter table employee_info modify hiredate default sysdate+1;

9、添加注释

(1)向表中添加注释

comment on table employee_info is 'information of employees';

(2)向列添加注释

comment on column employee_info.ename is 'the name of employees';

comment on column dept.dname is 'the name of department';

10、清除表中所有数据

truncate table employee_info; (DELETE FROM table_name或DELETE * FROM table_name)

11、删除表

drop table employee_info;

注：查看表的约束信息：user_constraints视图查看约束、user_cons_columns视图查看有关列的约束信息、user_tab_comments视图查看对表的注释 、user_col_comments视图查看对表列的注释，desc tablename查看表结构。

三、补充

1、建表时指定约束

Oracle 支持下面五类完整性约束:

NOT NULL 非空

UNIQUE Key 唯一键

PRIMARY KEY 主键

FOREIGN KEY 外键

CHECK 自定义检查约束

如：

CREATE TABLE s_dept (

id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY,

name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL,

region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id),

CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id)

);

-- 在列属性后面添加约束，指定约束的名字

CREATE TABLE s_emp (

id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY,

last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL,

first_name VARCHAR2(25),

userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE,

start_date DATE DEFAULT SYSDATE,

comments VARCHAR2(25),

manager_id NUMBER(7),

title VARCHAR2(25),

dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),

salary NUMBER(11,2),

commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20))

);

-- 在列属性后面添加约束，不指定约束的名字

CREATE TABLE s_emp (

id NUMBER(7) PRIMARY KEY,

last_name VARCHAR2(25) NOT NULL,

first_name VARCHAR2(25),

userid VARCHAR2(8) NOT NULL UNIQUE,

start_date DATE DEFAULT SYSDATE,

comments VARCHAR2(25),

manager_id NUMBER(7),

title VARCHAR2(25),

dept_id NUMBER(7) REFERENCES s_dept(id), --dept_id NUMBER(7) FOREIGN KEY REFERENCES s_dept(id)

salary NUMBER(11,2),

commission_pct NUMBER(4,2) CHECK (commission_pct IN(10,12.5,15,17.5,20))

);

-- 在列定义后添加约束

CREATE TABLE s_emp (

id NUMBER(7),

last_name VARCHAR2(25) NOT NULL,

first_name VARCHAR2(25),

userid VARCHAR2(8) NOT NULL UNIQUE,

start_date DATE DEFAULT SYSDATE,

comments VARCHAR2(25),

manager_id NUMBER(7),

title VARCHAR2(25),

dept_id NUMBER(7),

salary NUMBER(11,2),

commission_pct NUMBER(4,2) CHECK (commission_pct IN(10,12.5,15,17.5,20)),

CONSTRAINT s_emp_id_pk PRIMARY KEY(id),

constraint s_emp_dept_id_fk foreign key (dept_id) references s_dept(id)

);

2、利用现有表创建表

方式一：create table 新表 as select *[或者具体列名] from 旧表 [查询条件]

方式二：insert into 表1 select *[或者具体列名] 表2 [查询条件]