Oracle中建表及表操作
一、创建表
Oracle中的建表语句：
create table 表名(
字段名1 数据类型 列属性,
字段名2 数据类型 列属性,
......
)
如：创建表OA_DM.DM_GY_USER https://www.cnblogs.com/sjxbg/p/11171543.html
-- 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 [查询条件]
参考自：https://www.cnblogs.com/kkxwze/articles/10794544.html