24 数据库练习——简单练习

要求如下：

 

答案

--创建三张表
create table employees(
employeeID varchar2(6) primary key,
ename varchar2(10) not null,
birthDay  Date not null,
sex  varchar2(3) not null,
address varchar2(20),
Zip varchar2(6),
phoneNumber varchar2(12),
emailAddress varchar2(30),
departmentID varchar2(3) not null
)

drop table employees

create table departments(
departmentID varchar2(3) primary key,
departmentName varchar2(20) not null,
note varchar2(16)
)

create table salary(
employeeID varchar2(6) primary key,
income number(8,2) not null,
outcome number(8,2) not null
)
--创建约束
alter table employees 
add constraints fk_employees_departmentID foreign key(departmentID) 
references departments(departmentID)

alter table employees 
add constraints ck_employees_sex 
check(sex='男' or sex='女');

alter table departments 
add constraints uk_departments_departmentName
unique(departmentName)
--添加数据
insert into employees(employeeID,ename,birthDay,sex,departmentID) values(010001,'王平',to_date('1981-1-5','yyyy-mm-dd'),'女',1);
insert into employees(employeeID,ename,birthDay,sex,departmentID) values(010003,'韦言瓶',to_date('1979-11-5','yyyy-mm-dd'),'男',2);
insert into employees(employeeID,ename,birthDay,sex,departmentID) values(010021,'吴清空',to_date('1984-7-2','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-2-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(employeeID,income,outcome) values(010001,3000,100);
insert into salary(employeeID,income,outcome) values(010003,5000,50);
insert into salary(employeeID,income,outcome) values(010021,3000,50);
insert into salary(employeeID,income,outcome) values(010031,3000,50);
insert into salary(employeeID,income,outcome) values(010005,2500,50);
--修改表结构：新增字段
alter table employees add QQ number(15)

--修改值
 --将表salary中编号为10001的职工收入改为2890
 update salary set income=2890 where employeeID=10001
 --将表departments表中生产部的名称改为生产计划部
 update departments set departmentName= '生产计划部' where departmentName='生产部'
 --给每个职工增加收入100元
 update salary set income=income+100;

--查询
select * from employees;
select * from departments;
select * from salary;