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
--調(diào)整表的特性
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)
 --指定數(shù)據(jù)文件
 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;
 --刪除已經(jīng)存在的表
 DROP TABLE employee
-- 刪除表及其規(guī)則
DROP TABLE employee CASCADE CONSTRAINT
--完整性約束
--創(chuàng)建主鍵
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){}