workshop.sql

该车间信息管理系统

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

create sequence hibernate_sequence;

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,'aaa','klarck');
insert into lxf_workshop_manager values(2,'klarck','klarck',1);
insert into lxf_workshop_employee values(2,2,'klarck','aaa',TO_DATE('17-10-2007','DD-MM-YYYY'),0,0);
insert into lxf_workshop_employee values(3,2,'briup','aaa',TO_DATE('11-8-2008','DD-MM-YYYY'),0,0);
insert into lxf_workshop_employee values(5,2,'briup1','aaa',TO_DATE('07-5-2008','DD-MM-YYYY'),0,0);
insert into lxf_workshop_product values(2,1,'briup','A6589',20,TO_DATE('17-10-2008','DD-MM-YYYY'));
commit;

select * from lxf_workshop_employee;
select * from lxf_workshop_room;
select * from lxf_workshop_product;
select * from lxf_workshop_manager;

delete from lxf_workshop_employee where name='李四';
delete from lxf_workshop_manager;
delete from lxf_workshop_room;

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

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