Oracle二维表操作训练

—创建Employees
create table Employees(
employeeID varchar2(6) primary key,
ename varchar2(10) not null,
Birthday date not null,
sex char(4) not null,
address varchar2(20) ,
zip varchar2(6),
Phonenumber varchar2(12),
Emailaddress varchar2(30),
departmentID varchar2(3) not null
);
—创建Departments
create table Departments(
departmentID varchar2(3) primary key,
departmentName varchar2(30) not null,
note varchar2(16)

);
—创建Salary
create table Salary(
employeeID varchar2(6) primary key,
income number(8,2) not null,
outcome number(8,2) not null

);

–插入测试数据
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010001,‘王平’,to_date(‘1981-01-05’,‘yyyy-mm-dd’),‘女’,‘1’);
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010003,‘韦严平’,to_date(‘1979-11-05’,‘yyyy-mm-dd’),‘女’,‘2’);
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010021,‘吴庆红’,to_date(‘1984-07-02’,‘yyyy-mm-dd’),‘女’,‘3’);
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010031,‘李方’,to_date(‘1980-8-4’,‘yyyy-mm-dd’),‘女’,‘4’);
insert into Employees(Employeeid,ename,Birthday,sex,Departmentid) values(010005,‘李明’,to_date(‘1985-02-10’,‘yyyy-mm-dd’),‘女’,‘5’);

insert into Departments(departmentID,departmentName)values(1,‘办公室’);
insert into Departments(departmentID,departmentName)values(2,‘人力资源’);
insert into Departments(departmentID,departmentName)values(3,‘销售部’);
insert into Departments(departmentID,departmentName)values(4,‘财务部’);
insert into Departments(departmentID,departmentName)values(5,‘生产部’);

insert into Salary values(010001,3000,100);
insert into Salary values(010003,3000,50);
insert into Salary values(010021,5000,50);
insert into Salary values(010031,3000,50);
insert into Salary values(010005,2500,50);

---添加外键约束 

alter table Employees add constraints fk_e_d_departtmentID foreign key(departmentID) references Departments(departmentID)
alter table Employees add constraints ck_sex check(sex=‘男’ or sex=‘女’)
alter table Departments add constraints un_departmentName unique(departmentName)
—增加字段
alter table Employees add qq number(15)

update salary set income=2890 where employeeID=010001
update Departments set departmentName=‘生产计划部’ where departmentName=‘生产部’
update salary set income=income+100