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%';