SQL 图书借阅系统例题练习

在学校机房，为了完成坑爹的技能抽查练习的SqlServer 做准备，其中大一时候学习的创建视图、创建储存过程，现在可以回顾一下。

下面是CCE老师发的题目

数据

/*
@Describe:试题B_13
@Date:2014/10/23
@Student:GongBiao
*/

USE master
GO

-- 3.1 创建数据库BookDB
CREATE DATABASE BookDB;
USE BookDB
GO
-- 3.2 创建数据表T_card、T_book、T_borrow

CREATE TABLE T_book(
	Book_no VARCHAR(20) PRIMARY KEY,
	Book_name VARCHAR(100) NOT NULL,
	Author VARCHAR(50) NOT NULL,
	Price DECIMAL(10, 2) NOT NULL,
	Qty INT NOT NULL,
	Loan_qty INT NOT NULL
);


CREATE TABLE T_card(
	Card_no VARCHAR(20) PRIMARY KEY,
	Card_name VARCHAR(30) NOT NULL,
	Adress VARCHAR(30) NOT NULL,
	Mobile VARCHAR(11) NOT NULL
);

CREATE TABLE T_Borrow(
	Borrow_id BIGINT PRIMARY KEY,
	Book_no VARCHAR(20) NOT NULL,
	Card_no VARCHAR(20) NOT NULL,
	Borrow_date DATETIME NOT NULL,
	Return_date DATETIME NOT NULL
	
);



-- 3.3 创建数据表的关系
ALTER TABLE T_Borrow
ADD CONSTRAINT FK_T_BORROW_REFRENCES_T_BOOK
FOREIGN KEY (Book_no) REFERENCES T_Book(Book_no);

ALTER TABLE T_Borrow
ADD CONSTRAINT FK_T_BORROW_REFERENC_T_CARD
FOREIGN KEY (Card_no) REFERENCES T_Card(Card_no);


-- 3.4 数据操作

SELECT * FROM T_book;
SELECT * FROM T_card;
SELECT * FROM T_Borrow;

-- 插入Access 数据库技术与应用书
INSERT INTO T_book
VALUES('9787302245339', 'Access 数据库技术与应用', '陈世红', 27.20, 50, 13);
INSERT INTO T_book
VALUES('8999992311112', 'Linux Shell 强哥', '李强强', 45.88, 99, 15);
INSERT INTO T_book
VALUES('7923434321234', '艺术与生活', '牛小燕', 77, 30, 10);
INSERT INTO T_book
VALUES('7923434321233', '大话时光机', '彪哥哥', 77, 5, 3);
INSERT INTO T_book
VALUES('7923434321237', '大话时飞行器', '彪哥哥', 77, 3, 3);

-- 插入借书卡表信息
INSERT INTO T_card
VALUES('1225073421', '李霸天', '青年公寓306', '8878498');
INSERT INTO T_card
VALUES('1225073423', '王小强', '青年公寓303', '7788741');
INSERT INTO T_card
VALUES('1225073418', '谢龙', '儿区垃圾堆203', '4987814');

-- 插入借书记录
INSERT INTO T_Borrow
VALUES(1, '9787302245339', '1225073423', '2010-10-31', GETDATE());
INSERT INTO T_Borrow
VALUES(2, '9787302245339', '1225073418', '2010-10-31', '2011-9-14');
INSERT INTO T_Borrow
VALUES(3, '8999992311112', '1225073421', '2010-10-31', NULL);
-- 查询出日期为2010-10-31 以后借出的图书信息

SELECT Book_name, Price FROM T_Borrow, T_book WHERE T_Borrow.Book_no=T_book.Book_no;

-- 查询出还没有还书的借书人姓名

SELECT T_card.Card_name FROM T_Borrow, T_card WHERE Return_date IS NULL AND T_Borrow.Card_no=T_card.Card_no;

-- 查询出库存数量小于5 册的图书信息
SELECT * FROM T_book WHERE Qty < 5;

-- 编写视图查询借书人的姓名，手机号和地址

CREATE VIEW view_1
AS 
SELECT b.Card_name 姓名,b.Mobile 手机号码,b.Adress 地址
FROM T_borrow a,T_card b
WHERE a.Card_no=b.Card_no;

SELECT * FROM view_1;

CREATE VIEW view_borrow_stu
AS 
SELECT F.Card_name FROM T_Borrow T, T_card F
WHERE Return_date IS NULL AND T.Card_no=F.Card_no;

-- 使用视图 view_borrow_stu;
SELECT * FROM view_borrow_stu;



-- 编写存储过程，查询指定图书名称的借阅次数

 CREATE PROCEDURE procedure_1
@Book_name VARCHAR(100)
AS
BEGIN 
SELECT Qty
FROM T_book
WHERE Book_name=@Book_name
END
--执行
DECLARE @name VARCHAR(100)
SET @name='Access 数据库技术与应用'
EXEC procedure_1 @name

-- 编写存储过程,查询指定图书名称的借阅次数

CREATE PROCEDURE procedure_2
@Book_name VARCHAR(100)
AS BEGIN
SELECT * FROM T_book
WHERE Book_name = @Book_name
END;

-- 执行测试
DECLARE @Book_name VARCHAR(100)
SET @Book_name='大话时飞行器'
EXEC procedure_2 @Book_name;



SELECT * FROM T_book;
SELECT * FROM T_card;
SELECT * FROM T_Borrow;

-- 常用系统存在过程有
EXEC sp_databases;
sp_tables;
EXEC sp_columns T_card;

-- 查询所胡的存储过程
SELECT * FROM sys.objects WHERE type = 'p';
SELECT * FROM sys.objects WHERE type_desc LIKE '%pro%' AND name LIKE 'sp%';