assetdb.sql

1、管理员登录 2、固定资产信息管理 3、人员信息管理 4、资产的领用 5、资产的归还 6、资产信息浏览和查询

create table employee (
  empno int primary key,
  ename varchar(40) not null,
  position varchar(40),
  remarks varchar(255));
  
insert into employee values(1001,'Jack','工程师','老员工');
insert into employee values(1002,'Tom','工程师','新员工');
insert into employee values(1003,'Jerry','技术员','老员工');
insert into employee values(1004,'Mark','技术员','新员工');
insert into employee values(1005,'Lisa','工程师','优秀员工');
  
create table category (
  cateid int,
  catename varchar(40),
  subcateid int,
  subcatename varchar(40),
  constraint cate primary key (cateid,subcateid));
  
insert into category values(1,'办公设备',1,'计算器');
insert into category values(1,'办公设备',2,'打印机');
insert into category values(1,'办公设备',3,'传真机');
insert into category values(2,'交通工具',4,'别克商务车');
insert into category values(2,'交通工具',5,'宝马车');
insert into category values(2,'交通工具',6,'奔驰商务车');
  
create table operator (
  name varchar(10),
  pwd  varchar(20));
  
insert into operator values('admin','123456');
insert into operator values('admin1','123456');
insert into operator values('admin2','123456');
insert into operator values('admin3','123456');

create table asset (
  assetid int primary key,
  asset_name varchar(40) not null,
  cateid int not null,
  subcateid int not null,
  model varchar(40),
  price numeric(7,2) not null,
  purchase_date varchar(12) not null,
  status char(1) not null,
  useby varchar(10),
  remarks varchar(255));
  
insert into asset values(10001,'计算器E101',1,1,'',80.00,'2005-12-30','0','','');
insert into asset values(10002,'计算器E102',1,1,'',60.00,'2005-12-30','0','','');
insert into asset values(10003,'别克商务车X01',2,4,'',50000.00,'2005-12-30','0','','');  
insert into asset values(10004,'打印机XX-1',1,2,'',400.00,'2005-12-30','0','',''); 
insert into asset values(10005,'奔驰商务车F01',2,6,'',80000.00,'2005-12-30','0','','');


create table lend (
  assetid int primary key,
  empno int ,
  usedate varchar(12),
  retdate varchar(12),
  isreturn varchar(4),
  lender varchar(10),
  remarks varchar(255));
  
 
insert into lend values(10003,1001,'2007-12-30','2010-12-30','否','admin',''); 
insert into lend values(10001,1003,'2006-12-30','207-12-30','是','admin','');