oracle 课堂笔记2

cissdy

--添加列
ALTER TABLE employee
ADD address VARCHAR2(20)
ALTER TABLE employee
ADD  job  varchar2(20)
--删除列
ALTER TABLE employee
DROP COLUMN address
--删除多列
ALTER TABLE employee
DROP (job,address)
--将列置为不可用
ALTER TABLE employee
SET UNUSED (job,address)
--删除所有不可的列
ALTER TABLE employee
DROP UNUSED COLUMN
--调整表的特性
ALTER TABLE employee
PCTFREE 30
PCTUSED 60
--
ALTER TABLE employee MOVE
STORAGE
(
 INITIAL 50K
 NEXT 128K
 PCTINCREASE 10
 MAXEXTENTS UNLIMITED
 )
 --手动分配空间
 ALTER TABLE employee
 ALLOCATE EXTENT
 --指定大小
 ALTER TABLE employee
 ALLOCATE EXTENT(SIZE 100k)
 --指定数据文件
 ALTER TABLE employee
 ALLOCATE EXTENT(SIZE 1M
 DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\users01.dbf')
 --回收所有没有使用的表空间
 ALTER TABLE employee
 DEALLOCATE  UNUSED;
 --
 ALTER TABLE employee
 DEALLOCATE UNUSED KEEP 100k;
 --删除已经存在的表
 DROP TABLE employee
-- 删除表及其规则
DROP TABLE employee CASCADE CONSTRAINT
--完整性约束
--创建主键
CREATE TABLE employee
(
 emp_id number(5) ,
 name VARCHAR2(8) CONSTRAINT nl_emp NOT NULL,
 age number(3),
 address varchar2(40),
 phone  varchar2(20) CONSTRAINT uq_phone UNIQUE,
 dep_id number(3),
 CONSTRAINT pk_employee PRIMARY KEY(emp_id)
)
--
ALTER TABLE employee
ADD phone VARCHAR2(20)
--
ALTER TABLE employee
DROP CONSTRAINT pk_employee
--
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY(emp_id);
--
ALTER TABLE employee
MODIFY name CONSTRAINT nul_name NOT NULL
--
ALTER TABLE employee
ADD CONSTRAINT uq_phone UNIQUE(phone);
--外键约束
CREATE TABLE employee
(
 emp_id number(5) ,
 name VARCHAR2(8) CONSTRAINT nl_emp NOT NULL,
 age number(3),
 address varchar2(40),
 phone  varchar2(20) CONSTRAINT uq_phone UNIQUE,
 dep_id number(3),
 CONSTRAINT pk_employee PRIMARY KEY(emp_id),
 CONSTRAINT fk_dep_id FOREIGN KEY(dep_id)
 REFERENCES department(dep_id)
)
--
ALTER TABLE employee
DROP CONSTRAINT fk_dep_id
--
ALTER TABLE employee
ADD CONSTRAINT fk_dep_id
FOREIGN KEY(dep_id) REFERENCES department(dep_id)
--ON DELETE SET NULL --
ON DELETE CASCADE
--ON DELETE NO ACTION
ALTER TABLE employee
ADD CONSTRAINT chk_age CHECK(age>=16 and age<=60)

try{parent.JS.modules[window.name].content.setHeight();}catch(e){}