图书管理系统.rar

create database librarysys go use librarysys go create table users ( [借阅证号] int not null, [证件号码] int not null, [用户真实姓名] nvarchar(50) not null, [用户年龄] int , [用户性别] nvarchar(2), [所在单位] nvarchar(400), [是否可用] int not null ) go insert into users values('1','1','zbh','20','男' ,'microsoft',1) go create table books ( [图书编号] int identity (1,1) primary key not null, [图书种类] nvarchar(50) , [图书名称] nvarchar(50) not null, [出版社] nvarchar(100) , [作者] nvarchar(30) , [单价] money, [书架号] int not null, [图书总数] int not null, [库存图书数目] int not null, [是否可外借] int not null ) go create table onlyread ( [图书编号] int identity (1,1) primary key not null, [图书种类] nvarchar(50) , [图书名称] nvarchar(50) not null, [出版社] nvarchar(100) , [作者] nvarchar(30) , [单价] money, [书架号] int not null, [图书总数] int not null, [库存图书数目] int not null, [是否可外借] int not null ) go insert into books values('IT杂志','.NET高级面向对象参考','清华大学出版社','金冠亮',100,0,'10','10',1) insert into books values('编程奥秘','JAVA','北京大学出版社','潭豪情',500,0,'60','40',1) insert into books values('汇编语言','VB','厦门大学出版社','张三',300,0,'30','90',1) insert into books values('C语言编程奥秘','C','北京大学出版社','潭浩强',600,0,'120','130',1) insert into books values('PNP编程奥秘','PNP','北京大学出版社','王五',234,0,'89','350',1) insert into books values('Oracle编程奥秘','Oracle','浙江大学出版社','李市',357,0,'80','20',1) insert into books values('SQL编程奥秘','SQL','湖南农大出版社','ZBH',500,0,'60','40',1) insert into books values('VB编程奥秘','VB','北京大学出版社','潭情',500,0,'60','40',1) insert into books values('VS编程奥秘','VS','哈佛大学出版社','ZGJ',540,0,'450','44',1) insert into onlyread values('IT杂志','.NET高级面向对象参考','清华大学出版社','金冠亮',100,0,'10','10',0) insert into onlyread values('编程奥秘','JAVA','北京大学出版社','潭豪情',500,0,'60','40',0) insert into onlyread values('汇编语言','VB','厦门大学出版社','张三',300,0,'30','90',0) insert into onlyread values('C语言编程奥秘','C','北京大学出版社','潭浩强',600,0,'120','130',0) insert into onlyread values('PNP编程奥秘','PNP','北京大学出版社','王五',234,0,'89','350',0) insert into onlyread values('Oracle编程奥秘','Oracle','浙江大学出版社','李市',357,0,'80','20',0) insert into onlyread values('SQL编程奥秘','SQL','湖南农大出版社','ZBH',500,0,'60','40',0) insert into onlyread values('VB编程奥秘','VB','北京大学出版社','潭情',500,0,'60','40',0) insert into onlyread values('VS编程奥秘','VS','哈佛大学出版社','ZGJ',540,0,'450','44',0) insert into historybooks values('文史名著','红楼梦','厦门大学','曹雪芹',60,0,'12','100',0) insert into historybooks values('名著','三国演义','湖南大学','CGZ',60,0,'12','100',1) insert into historybooks values('名著','水浒传','湖南出版社','SNA',30,0,'132','1400',1) insert into historybooks values('名著','西游记','人民出版社','CGZ',40,0,'312','1400',1) go create table borrowInfo ( [信息编号] int identity (1,1) primary key not null, [图书编号] int not null , [借阅证号] int not null, [借阅日期] nvarchar(100) not null, [归还日期] nvarchar(100) not null ) --select 信息编号,证件号码,用户真实姓名,所在单位,借阅日期,归还日期 FROM borrowInfo cross join users where 图书编号=1 --select 信息编号,图书名称,图书种类,借阅日期,归还日期,出版社,作者,图书总数,单价,库存图书数目 FROM borrowInfo cross join books where 借阅证号=1 --select 信息编号,图书名称,图书种类,证件号码,用户真实姓名,用户年龄,所在单位,借阅日期,归还日期,库存图书数目 FROM borrowInfo as d LEFT OUTER JOIN books on d.图书编号 = books.图书编号 cross join users --where users.借阅证号 = borrowInfo.借阅证号 -- on borrowInfo.借阅证号 = users.借阅证号 --SELECT au_lname, au_fname FROM authorsWHERE au_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'popular_comp')) go create table historybooks ( [图书编号] int identity (1,1) primary key not null, [图书种类] nvarchar(50) , [图书名称] nvarchar(50) not null, [出版社] nvarchar(100) , [作者] nvarchar(30) , [单价] money, [书架号] int not null, [图书总数] int not null, [库存图书数目] int not null, [是否可外借] int not null ) go insert into historybooks values('文史名著','红楼梦','厦门大学','曹雪芹',60,0,'12','100',1) insert into historybooks values('名著','三国演义','湖南大学','CGZ',60,0,'12','100',1) insert into historybooks values('名著','水浒传','湖南出版社','SNA',30,0,'132','1400',1) insert into historybooks values('名著','西游记','人民出版社','CGZ',40,0,'312','1400',1) go create table lostInfo ( [借阅证号] int identity(1,1) primary key not null, [图书编号] int not null, [图书名称] varchar(100), [丢失日期] datetime not null, [备注] nvarchar(500) ) go create table lostInfonotBeLent ( [编号] int identity(1,1) primary key not null, [图书编号] int not null, [图书名称] varchar(100), [数量] varchar(500), [价格] money, [丢失日期] datetime not null, [备注] nvarchar(500) ) go create table administer ( 信息编号 int identity(1,1) not null, 用户名 varchar(50), 密码 varchar(30) ) go insert into administer values('zbh','123') insert into administer values('administer','123456') go create table kindsInfo ( 图书类别 varchar(100) not null, 图书编号 int, 说明 varchar(1000) ) CREATE TABLE [dbo].[numbers] ( [借阅证号] [int] identity(1,1) primary key NOT NULL , [用户真实姓名] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL , [用户年龄] [int] NOT NULL , [用户性别] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL , [会员级别] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [最多可借图书] [int] NOT NULL ) ON [PRIMARY] GO insert into numbers values('zbh',19,'男','A',10) insert into numbers values('zh',19,'女','B',6) insert into numbers values('zyz',18,'女','c',5) insert into numbers values('zl',16,'女','B',5) insert into numbers values('zh',19,'男','B',5) insert into numbers values('rt',19,'女','B',9) insert into numbers values('zh',19,'女','B',8) insert into numbers values('bh',19,'女','B',7) select *From numbers --alter table lostInfo drop column 借阅日期 --alter table lostInfo drop column 图书名称 --alter table books drop column 图书种类 ='文史名著' go insert into lostInfo values('5','C#','4/17/2008','不小心，就丢了') insert into lostInfo values('7','C#','4/17/2008','不小心，就丢了') insert into lostInfo values('8','C#','4/17/2008','不小心，就丢了') insert into lostInfo values('9','C#','4/17/2008','不小心，就丢了') insert into lostInfo values('34','C#','4/17/2008','不小心，就丢了') insert into lostInfo values('45','C#','4/17/2008','不明') insert into lostInfonotBeLent values('5','IT','3',100,'4/17/2008','被窃，就丢了') insert into lostInfonotBeLent values('7','C#','3',100,'4/17/2008','被学生顺手拿了') insert into lostInfonotBeLent values('8','JAVA','3',100,'4/17/2008','不明') insert into lostInfonotBeLent values('9','C#','3',100,'4/17/2008','不详') insert into lostInfonotBeLent values('34','SQL','3',100,'4/17/2008','不详') insert into lostInfonotBeLent values('45','VB','3',100,'4/17/2008','不详') select *from administer select *from books select *from users select *from historybooks select *from lostInfo --delete books where 图书种类 ='文史名著' --select 是否可用 from users where 借阅证号码 =1 select *From borrowInfo --select *from borrowInfo where 归还日期 =0 and 借阅证号=1 select *from [dbo].[numbers] select *from lostInfonotBeLent