ORACLE之表

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

首先有t_fn_person和t_fn_dept表。

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

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

1 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');

 结果：

 

 

 

转载于:https://www.cnblogs.com/hoaprox/p/5318877.html