数据库建立.sql

一个图书馆的VB做的mis系统

create database lib
go

use lib
go

create table users(
    users_id int identity(1,1) primary key,
    u_name varchar (6) default('无'),
    u_sex varchar (2) default('男'),
    u_dep varchar (10) default('无'),
    u_phone varchar (10) default('无'),
    u_address varchar (20) default('无'),
    u_reg_date datetime default('2006-6-1'),
    u_text text default('无'),
    u_oper varchar(6) default('无')

)


create table del_users(   
    du_id int identity(1,1)  primary key,
    du_uid int default('无'),
    du_name varchar (6) default('无'),
    du_sex varchar (2) default('男'),
    du_dep varchar (10) default('无'),
    du_phone varchar (10) default('无'),
    du_address varchar (20) default('无'),
    du_reg_date datetime default('2006-6-1'),
    du_text text default('无'),
    du_oper_add varchar(6) default('无'),
    du_oper_d varchar(6) default('无'),
    del_u_date datetime default('2006-6-1')
)



create table admin(
     admin_id  int identity(1,1) primary key,
     a_name varchar (6) default('无'),
     a_pwd int default('000'),
     a_type varchar (5)  default('普通管理员'),
     a_oper varchar(6) default('无')

)



create table del_admin
(
  da_id  int identity(1,1) primary key,
  da_aid int,
  da_name varchar (6) default('无'),
  da_pwd int default('000'),
  da_oper_add varchar(6)  default('无'),
  da_oper_d varchar(6)  default('无'),
  del_a_date datetime  default('2006-6-1')

)


create table books(
    books_id int identity(1,1) primary key,
    b_name varchar (20) default('无'),
    b_author varchar (10) default('无'),
    b_type varchar (10) default('无'),
    b_pub varchar (20) default('无'),
    b_isbn varchar (20) default('无'),
    b_have varchar(2) default('是'),
    b_p_date datetime default('2006-6-1'),
    b_reg_date datetime default('2006-6-1'),
    b_address varchar(20) default('无'),
    b_price money default(100),
    b_text text default('无'),
    b_oper varchar(6) default('无')
)



create table del_books
(
    dk_id int identity(1,1) primary key,
    dk_bid int default('000'),
    dk_name varchar (20) default('无'),
    dk_author varchar (10) default('无'),
    dk_type varchar (10) default('无'),
    dk_pub varchar (20) default('无'),
    dk_isbn varchar (20) default('无'),
    dk_have varchar(2) default('是'),
    dk_p_date datetime default('2006-6-1'),
    dk_reg_date datetime default('2006-6-1'),
    dk_address varchar(20) default('无'),
    dk_price money default(100),
    dk_text text default('无'),
    dk_oper_add varchar(6) default('无'),
    dk_oper_d varchar(6) default('无'),
    del_b_date datetime default('2006-6-1')

)


create table borrow(
    borrow_id int identity(1,1) primary key,
    users_id int foreign key references users(users_id)  default('000'),
    books_id int foreign key references books(books_id) default('000'),
    br_u_name varchar (6) default('无'),
    br_b_name varchar (20) default('无'),
    br_isbn varchar (20) default('无'),
    br_b_date datetime default('2006-6-1'),
    br_r_date datetime default('2006-6-1'),
    br_n_date datetime default('2006-6-1'),
    br_text text,
    br_price money default(100),
    br_oper varchar(10)
)


create table return_b(
    return_id int identity(1,1) primary key,
    users_id int default('000'),
    books_id int default('000'),
    re_u_name varchar (6) default('无'),
    re_b_name varchar (20) default('无'),
    re_isbn varchar (20) default('无'),
    re_r_date datetime default('2006-6-1'),
    re_price money  default(100),
    re_pay_for char(10) default('无'),
    re_oper varchar(6)
)













go

select * from users
select * from books
select * from borrow
select * from admin

select * from del_users
select * from del_books
select * from del_admin