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