asp.sql.txt

以上源码都能完成相应功能！是基础编程者学习着的好资料！

	--转到Master数据库
set nocount on
Use Master
Go

--检查数据库是否存在，存在则删除
if exists (select * from sysdatabases where name='BOOK_B')
begin
  raiserror('Dropping existing BOOK_B database ....',0,1)
  Drop database BOOK_B
end
Go


--创建数据库
raiserror('Creating BOOK_B database....',0,1)
  create database BOOK_B
Go

--转到数据库

Use  BOOK_B
--创建图书资料表
Go

create table books 
(
  autoNo  int IDENTITY(10000,1),
  b_ID         char(20),
  b_name       char(20),
  b_type       char(20),
  b_price      char(20),
  b_publisher  char(20),
  b_author     char(20),
  b_addDate    char(20),
  status       char(8),
  
)
--创建读者信息表
Go
create table readers
(
  autoNo  int IDENTITY(10000,1),
  r_name    char(30),
  r_stuNo   char(20),
  r_cardID  Char(30),
  r_identificationNo      char(18),
  r_majdep  char(12),
  r_addDate char(20),
  r_sex     char(8),
  r_age     int,
)

--创建借阅详细表
Go
create table bw_Details
(
  autoNo  int IDENTITY(10000,1),
  r_cardID  char(30),
  r_name    char(30),
  r_stuNo   char(20),
  b_name    char(20),
  b_ID      char(20),
  b_type    char(20),
  bwDate    char(20),
  bkData    char(20),
  bwCount   int,
)
--创建罚款表
Go 
create table fine
(
  f_No        int IDENTITY(001,1),
  r_name      char(30),
  r_cardID    Char(30),
  b_ID        char(20),
  b_name      char(20),
  f_price     int,
  expiredays  int,
  amount      int,
)

--创建图书管理员信息表
Go
create table libusers
(
  autoNo  int IDENTITY(10000,1),
  lib_user     char(20),
  lib_password char(20),
  lib_phone    char(10),
  registerTime  char(10),
  
)




insert into  books values('B0001','ASP教程','sofeware','80RMB','ChinaOil','lili','2005-1-1','in')
insert into  books values('B0002','JSP项目化演练','sofeware','90RMB','ChinaIndustry','mumu','2005-4-8','in')
insert into  books values('B0003','马克思主义基本原理','literature','48RMB','South','haha','2005-2-1','in')
insert into  books values('B0004','商标竞争','Ecomic','26RMB','North','dodo','2005-1-1','out')
insert into  books values('B0005','高阶英语','English','56RMB','West','sisi','2005-3-10','out')


insert into   fine values('Zhangsan','R001','B0002','JSP项目化演练','2','5','1')
insert into   fine values('Lisi','R002','B0003','马克思主义基本原理' ,'2','','0')
insert into   fine values('Wangwu','R003','B0004','商标竞争','2','8','16')
insert into   fine values('Qiutian','R004','B0005','高阶英语','2','21','42')
insert into   fine values('Gaolin','R005','B0001','ASP教程','2','36','72')

insert into  readers values('Zhangsan','RS06050301','R001','430621198526842471','软件06','2006-09-09','女','19')
insert into  readers values('Lisi','RS0775802','R002','930268119562284714','石油工程07','2007-09-08','男','21')
insert into  readers values('Wangwu','RS06050808','R003','651561515110255611','软件06','2006-09-09','男','20')
insert into  readers values('Qiutian','RS05475235','R004','538976425522333487','石油工程05','2005-12-08','男','22')
insert into  readers values('Gaolin','RS06589755','R005','456835664564572554','机械06','2006-09-10','女','20')

insert into  bw_Details values('R001','Zhangsan','RS06050301','JSP项目化演练','B0002','sofeware','2008-4-1','2008-5-1','1')
insert into  bw_Details values('R002','lisi','RS0775802','马克思主义基本原理','B0003','literature','2008-5-5','2008-6-5','1')
insert into  bw_Details values('R003','Wangwu','RS06050808','商标竞争','B0004','Ecomic','2008-6-1','2008-7-1','1')
insert into  bw_Details values('R004','Qiantian','RS05475235','高阶英语','B0005','English','2008-5-15','2008-6-15','1')
insert into  bw_Details values('R005','Gaolin','RS06589755','ASP教程','B0001','sofeware','2008-5-18','2008-6-18','1')