ORACLE之表

本文章中的表在以后的例子中会用到。

首先有t_fn_person和t_fn_dept表。

create table t_fn_person(map_id NUMBER(10) primary key not null,
                         person_code VARCHAR2(20) not null,
                         person_name VARCHAR2(20) not null,
                         sex VARCHAR2(10),
                         insert_time date,
                         update_time date,
                         position VARCHAR2(20),
                         Salary NUMBER(12, 2),
                         Dept number);
create table t_fn_dept(DEPT_ID NUMBER PRIMARY KEY NOT NULL,
                       DEPT_NO VARCHAR2(15) NOT NULL,
                       DLOCATION VARCHAR2(15));

建立t_fn_person和t_fn_dept的外键（可以不建）

alter table t_fn_person add constraint fk_fn_dept foreign key(dept) references t_fn_dept(dept_id);--建立外键

 更改列名一致及删除外键

alter table t_fn_person drop constraint   fk_fn_dept
alter table t_fn_person RENAME  COLUMN  Dept to DEPT_ID 

 

插入数据

insert into t_fn_person values(10001,'20001','Lily','F',sysdate,sysdate,'PM',2000,301);
insert into t_fn_person values(10002,'20002','Tom','M',sysdate,sysdate,'PM',3000,301);
insert into t_fn_person values(10003,'20003','Cat','F',sysdate,sysdate,'DM',1000,302);
insert into t_fn_person values(10004,'20004','Dog','M',sysdate,sysdate,'CM',4000,303);
insert into t_fn_person(map_id,person_code,person_name,sex,insert_time,update_time,position,Salary) values(10005,'20005','Xxx','M',sysdate,sysdate,'CM',3000);

insert into t_fn_dept values(301,'301','shanghai');
insert into t_fn_dept values(302,'302','wuhan');
insert into t_fn_dept values(303,'303','shenzhen');
insert into t_fn_dept values(304,'304','beijing');

 结果：