图书管理系统的sqlserver数据库设计示例

CREATE DATABASE BMS
 
IF OBJECT_ID(N't_booktype',N'U') IS NOT NULL
	DROP TABLE t_booktype
CREATE TABLE t_booktype(
	typeno int primary key not null,
	typename varchar(30) not null
)
 
IF OBJECT_ID(N't_books',N'U') IS NOT NULL
	DROP TABLE t_books
CREATE TABLE t_books(
	bookno int primary key not null,
	bookname varchar(30) not null,
	typeno int not null
)
 
IF OBJECT_ID(N't_readertype',N'U') IS NOT NULL
	DROP TABLE t_readertype
CREATE TABLE t_readertype(
	readertypeno int primary key not null,
	readername varchar(30) not null,
	lendnumber int not null
)
 
IF OBJECT_ID(N't_readerinfo',N'U') IS NOT NULL
	DROP TABLE t_readerinfo
CREATE TABLE t_readerinfo(
	readerno int primary key not null,
	readername varchar(30) not null,
	readeraddress varchar(30) not null,
	readertypeno int not null
)
 
IF OBJECT_ID(N't_lendbook',N'U') IS NOT NULL
	DROP TABLE t_lendbook
CREATE TABLE t_lendbook(
	recordno int primary key not null,
	readerno int not null,
	bookno int not null,
	lendtime datetime ,
	returntime datetime
)
 
INSERT INTO t_booktype
VALUES('1005','悬疑类')
 
 
INSERT INTO t_books
VALUES('1025','盗墓笔记','1005')
 
 
INSERT INTO t_readertype
VALUES('0005','黄秋萍',20)
 
 
INSERT INTO t_readerinfo
VALUES('0005','黄秋萍','南昌市','0005')
 
INSERT INTO t_lendbook
VALUES('0005','0002','1013','2004-07-28','2004-11-16')
 
 
SELECT *
FROM t_books
 
UPDATE t_books
SET bookname='深入理解计算机系统'
WHERE bookno='1001'
 
UPDATE t_readertype
SET readername='吴娇'
WHERE readertypeno='0001'
 
SELECT *
FROM t_readerinfo
 
--查询图书名字
SELECT bookname
FROM t_books
 
--查询图书类别
SELECT typename
FROM t_booktype
 
--查询名字叫吴娇的借书记录
 
SELECT t_readerinfo.readername,t_lendbook.lendtime,t_lendbook.returntime
FROM t_readerinfo join t_lendbook on t_readerinfo.readerno=t_lendbook.readerno
WHERE t_readerinfo.readername='吴娇'