workshop.sql~

该车间信息管理系统

drop table lxf_workshop_manager;
drop table lxf_workshop_employee;
drop table lxf_workshop_product;
drop table lxf_workshop_room;

create table lxf_workshop_room (
id 	number(5)	primary key,
roomname	varchar2(30) not null,
manager		varchar2(30) not null
);

create table lxf_workshop_manager (
id	number(5)	primary key,
username	varchar2(30) not null,
password	varchar2(30) not null,
roomid	number(5),
foreign key(roomid) references lxf_workshop_room(id)
);

create table lxf_workshop_employee (
id	number(5)	primary key,
roomid	number(5),
name varchar2(30) not null,
gender varchar2(4) not null,
begintime	date not null,
dutystatus number default 0,
isonline number default 0,
foreign key(roomid) references lxf_workshop_room(id)
);

create table lxf_workshop_product (
id	number(5) primary key,
roomid number(5),
productname	varchar2(30) not null,
type	varchar2(30)	not null,
quantity	number(30)	not null,
finishtime	date not null,
foreign key(roomid) references lxf_workshop_room(id)
);

insert into lxf_workshop_room values(hibernate_sequence.nextval,'第一车间','klarck');
insert into lxf_workshop_manager values(1,'klarck','klarck',1);
insert into lxf_workshop_employee values(1,1,'klarck','男',TO_DATE('17-10-2007','DD-MM-YYYY'),0,0);
insert into lxf_workshop_employee values(2,1,'张三','男',TO_DATE('11-8-2008','DD-MM-YYYY'),0,0);
insert into lxf_workshop_employee values(5,1,'王五','女',TO_DATE('07-5-2008','DD-MM-YYYY'),0,0);
insert into lxf_workshop_product values(1,1,'引擎','A6589',20,TO_DATE('17-10-2008','DD-MM-YYYY'));
commit;

select * from klarck_workshop_employee;
select * from klarck_workshop_room;
select * from klarck_workshop_product;
select * from klarck_workshop_manager;
 
create sequence hibernate_sequence;

delete from klarck_workshop_employee where name='李四';
delete from klarck_workshop_manager;
delete from klarck_workshop_room;

insert into klarck_workshop_room values(hibernate_sequence.nextval,'第二车间','klarck');
insert into klarck_workshop_room values(hibernate_sequence.nextval,'第三车间','klarck');
insert into klarck_workshop_room values(hibernate_sequence.nextval,'第四车间','klarck');

insert into klarck_workshop_product values(hibernate_sequence.nextval,46,'车窗','B489',20,TO_DATE('07-12-2008','DD-MM-YYYY'));
insert into klarck_workshop_product values(hibernate_sequence.nextval,47,'车窗','B489',20,TO_DATE('07-12-2008','DD-MM-YYYY'));
insert into klarck_workshop_product values(hibernate_sequence.nextval,48,'车窗','B489',20,TO_DATE('07-12-2008','DD-MM-YYYY'));