database

用vb开发的图书管理系统设计,论文加原代码.

-- 1-> ******Help informations ******
--------------------------------------------------------------------------------------------
-- sp_helpdb    ...................................List all databases  in your computer 
-- sp_helpdb  BooksManSys .........................List database's informations
-- sp_renamedb 'BooksManSys' , 'newname' ..........Rename BooksManSys 
-- drop database BooksManSys ......................delete BooksMansSys
-- drop table tableNames ..........................delete tableNames
-- sp_help  tVip..............................List  table tvip'information
--------------------------------------------------------------------------------------------

-- 2-> ****** Create database ******
--------------------------------------------------------------------------------------------
-- Modify
-- sp_help drop database Library
use master
go

if exists (select * from dbo.sysdatabases where name = 'Library') 
drop database Library
create database Library
	on 
	(name='Librarydat',
	 filename='d:\Librarions ******
--------------------------------------------------------------------------------------------
-- sp_helpdb    ...................................List all databases  in your computer 
-- sp_helpdb  BooksManSys .........................List database's informations
-- sp_renamedb 'BooksManSys' , 'newname' ..........Rename BooksManSys 
-- drop database BooksManSys ......................delete BooksMansSys
-- drop table tableNames ..........................delete tableNrators 
-- select * from tOperators
use Library
go
create table tOperators 
	(cOperatorID char(5), --PRIMARY KEY CHECK (cOperatorID like '[A-Z][1-9][0-9][0-9][0-9]')
			     --DEFAULT 'Y0OO1',
	 cOperatorName varchar(10),
	 cOperatorPassword varchar(6),
	 cOperatorGrade varchar(10),
	 cOperatorAddTime varchar(10) 
 	 )

--insert values 
insert  tOperators  values( 'M0001','Admin','1','管理员', '2000-01-01 ' )
insert  tOperators  values( 'O0002','Ha Ha','2','普通', '2000-02-01 ' )
insert  tOperators  values( 'O0003','大辫子','3','普通', '2000-03-01 ' )
insert  tOperators  values( 'O0004','叮当','4','普通', '2000-04-01 ' )
insert  tOperators  values( 'O0005','蝈蝈','5','普通', '2000-05-01 ' )
insert  tOperators  values( 'O0006','小燕子','6','普通', '2000-06-01 ' )
--Table 2:   tVip
-------------------------------------------
-- Modify
-- drop table tVip
-- select * from tVip
use Library
go
create table tVip
	(cVipID char(6) PRIMARY KEY ,
	 cVipName varchar (10) NOT NULL,
	 cVipSex char (2) ,
	 cVipID_card char (15) ,						
	 cVipPhone varchar(15),				
	 cVipAddress varchar (25),
	 cVipAddTime varchar (10) ,
	 cVipEndTime varchar (10)	 
	 )
insert tVip values('0001','刘俪','女','36029421685','029-88219498','西安市电子城210所','2004-01-01','2005-01-01')
insert tVip values('0002','阿厦丽','女','360296236555','0394-82677666','河南省周口市','2003-03-13','2004-03-13')
insert tVip values('0003','金正武','男','560298456755','0370-2314897','河南省洛阳市','2005-01-01','2006-01-01')
insert tVip values('0004','尚西户','男','860298623755','029-82381397','西安市交大商场','2002-05-01','2003-05-01')
insert tVip values('0005','赵平','女','8606292356755','0577-2314897','浙江省宁波市','2001-07-01','2002-07-01')
insert tvip values('0006','赵亚兄','女','860292356725','0577-2314897','湖南省长沙市','2001-07-01','2002-07-01')


--Table 3:   tBorrow
----------------------------------------------
-- Modify
-- drop table tBorrow
-- select * from  tBorrow
use Library
go
create table tBorrow
	(cBorrowID int PRIMARY KEY  IDENTITY(0001,1) ,
	 cVipID char(6)  ,--FOREIGN KEY  REFERENCES tVip(cVipId) NOT NULL  ,	
	 cBooksID char (7) ,-- FOREIGN KEY REFERENCES tBooks(cBooksID) NOT NULL ,	
	 cBorrowTime varchar(10) default getdate(),
	 cReturnTime varchar(10) 
	 )
insert  tBorrow values('0001','0001','2003-01-01',NULL)
insert  tBorrow values('0002','0002' ,'2003-02-01',NULL)
insert  tBorrow values('0003','0003' ,'2003-03-01',NULL)
insert  tBorrow values('0004','0004' ,'2003-04-01',NULL)
insert  tBorrow values('0005','0005' ,'2003-05-01',NULL)
insert  tBorrow values('0006','0006' ,'2003-06-01',NULL)

--Table 4:   tBooks
-----------------------------------------------
-- Modify
-- drop table tBooks
-- select * from  tBooks 
use Library
go
create table tBooks 
	(cBooksID char(7) PRIMARY KEY,
	 cBooksName varchar (30) NOT NULL,	 
	 cBooksISBN char (15),	
   	 cBooksAuthor char (10),
	 cBooksPublisher varchar (20),
	 cBooksType varchar (16),
	 smBooksPrice smallmoney ,
   	 iBooksStoreQuantity int default 1,
	 iBooksLeftQuantity int default 1,	
 	 )
insert tBooks values('0001','计算机基础','ISN001','路琳','机械工业出版社','计算机',23.50,1,1)
insert tBooks values('0002','高等数学','ISN001','张阿丽','西安理工大学出版社','数学',10.50,1,1)
insert tBooks values('0003','微机原理','ISN001','郭永达','西安交通大学出版社','计算机',16.00,1,1)
insert tBooks values('0004','数据库原理','ISN001','松砺','清华大学出版社','计算机',32.00,1,1)
insert tBooks values('0005','古代文学史','ISN001','苌蒙','高等教育出版社','计算机',18.6,1,1)
insert tBooks values('0006','一米阳光','ISN001','武辉','陕西人民出版社','文史',15.3,1,1)
select * from tvip
select * from tBooks
select * from tBorrow
select tBorrow.cBorrowID,tBorrow.cBorrowTime,tBorrow.cBooksStatus, tvip.cVipID,tvip.cVipName,tBooks.cBooksID,tBooks.cBooksName,tBooks.cBooksAuthor,tBooks.smBooksPrice,tBooks.cBooksPublisher from tVip ,tBooks,tBorrow where tvip.cVipId=tBorrow.cVipId AND tBorrow.cBooksId =tBooks.cBooksId