2022年图书管理系统[] 2.pdf

《2022年图书管理系统[] 2.pdf》由会员分享，可在线阅读，更多相关《2022年图书管理系统[] 2.pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-创建数据库BlueSkyDB USE master GO IF EXISTS(SELECT * FROM sys.databases WHERE name=BlueSkyDB) DROP DATABASE BlueSkyDB GO CREATE DATABASE BlueSkyDB ON PRIMARY (NAME=BSPrimaryFile1, FILENAME=C:PrimaryFile1Data.mdf, SIZE=4MB, MAXSIZE=50MB, FILEGROWTH=1MB), FILEGROUP UserGroup1 (NAME=BSGroup1File1, FILENAME

2、=C:Group1File1Data.ndf, SIZE=2MB, MAXSIZE=50MB, FILEGROWTH=1MB), (NAME=BSGroup1File2, FILENAME=C:Group1File2Data.ndf, SIZE=2MB, MAXSIZE=50MB, FILEGROWTH=1MB), FILEGROUP UserGroup2 (NAME=BSGroup2File1, FILENAME=C:Group2File1Data.ndf, SIZE=1MB, MAXSIZE=50MB, FILEGROWTH=10%), FILEGROUP UserGroup3 (NAME

3、=BSGroup3File1, FILENAME=C:Group3File1Data.ndf, SIZE=1MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON (NAME=BSLogFile1, FILENAME=C:LogFile1.ldf, SIZE=2MB, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 15 页 - - - - - - - - - MAXSIZE=50MB, FILEGROWTH=1MB) GO -在 BlueSky

4、DB 中将文件组UserGroup1 配置为默认文件组。USE master GO ALTER DATABASE BlueSkyDB MODIFY FILEGROUP UserGroup1 DEFAULT GO -创建一个数据类型，用于存放图书价格数据。USE BlueSkyDB GO CREATE TYPE PriceDecimal FROM decimal(6,2) GO -在数据库BlueSkyDB 中创建表Cagegories，用于存放图书的类别信息。USE BlueSkyDB GO CREATE TABLE Categories (categoryCode nchar(4) PRIM

5、ARY KEY , categoryName nvarchar(50) NOT NULL ) GO -在数据库BlueSkyDB 中创建表Books，用于存放图书的信息。USE BlueSkyDB GO CREATE TABLE Books (bookID int IDENTITY(1,1) PRIMARY KEY , title nvarchar(50) NOT NULL, ISBN nchar(13) NOT NULL, author nvarchar(50), unitPrice PriceDecimal, categoryCode nchar(4) ) GO -在数据库BlueSkyD

6、B 中创建表Customers，用于存放顾客的信息。USE BlueSkyDB GO CREATE TABLE Customers (customerID int IDENTITY PRIMARY KEY , customerName nvarchar(20) NOT NULL, emailAddressnvarchar(50) NOT NULL, password nvarchar(50) NOT NULL ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 15 页

7、- - - - - - - - - GO -在数据库BlueSkyDB 中创建表Orders，用于存放订单的信息。USE BlueSkyDB GO CREATE TABLE Orders (orderID int IDENTITY(1,1) PRIMARY KEY , orderDate datetime NOT NULL DEFAULT GETDATE(), shipDate datetime, customerID int NOT NULL ) GO -定义订单项目表OrderItems USE BlueSkyDB GO CREATE TABLE OrderItems (orderID i

8、nt NOT NULL, bookID int NOT NULL, quantity int NOT NULL DEFAULT 1, price PriceDecimal, total AS price*quantity, PRIMARY KEY(orderID,bookID) ) GO -定义评论表Comments USE BlueSkyDB GO CREATE TABLE Comments (commentID int IDENTITY PRIMARY KEY , userName nvarchar(50) NOT NULL, userEmail nvarchar(50) NOT NULL

9、, rating tinyint NOT NULL DEFAULT 5, comment nvarchar(200) NOT NULL, bookID int NOT NULL ) GO -创建表 ShoppingRecords 存放购物车的信息USE BlueSkyDB GO CREATE TABLE ShoppingRecords (recordID int IDENTITY PRIMARY KEY , cartID uniqueidentifier NOT NULL, quantity int NOT NULL, dateCreated datetime NOT NULL DEFAULT

10、 GETDATE(), bookID int NOT NULL 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 15 页 - - - - - - - - - ) GO -创建表 Books 与 Categories 之间的关系ALTER TABLE Books ADD CONSTRAINT Books_categoryID_fk FOREIGN KEY(categoryCode) REFERENCES Categories(categoryCode) GO -创建表 Or

11、ders 与 Customers 之间的关系ALTER TABLE Orders ADD CONSTRAINT Orders_customerID_fk FOREIGN KEY(customerID) REFERENCES Customers(customerID) GO -创建表 OrderItems 与 Orders 之间的关系ALTER TABLE OrderItems ADD CONSTRAINT OrderItems_orderID_fk FOREIGN KEY(orderID) REFERENCES Orders(orderID) GO -创建表 OrderItems 与 Book

12、s 之间的关系ALTER TABLE OrderItems ADD CONSTRAINT OrderItems_bookID_fk FOREIGN KEY(bookID) REFERENCES Books(bookID) GO -创建表 Comments 与 Books 之间的关系ALTER TABLE Comments ADD CONSTRAINT Comments_bookID_fk FOREIGN KEY(bookID) REFERENCES Books(bookID) GO -创建表 ShippingRecords 与 Books 之间的关系ALTER TABLE ShoppingRe

13、cords ADD CONSTRAINT ShoppingRecords_bookID_fk FOREIGN KEY(bookID) REFERENCES Books(bookID) GO -向 Categories 表导入数据USE BlueSkyDB GO INSERT INTO Categories V ALUES(CO01, 计算机 ) GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 15 页 - - - - - - - - - INSERT INTO Ca

14、tegories V ALUES(FI01, 小说 ) GO INSERT INTO Categories V ALUES(LA01, 语言 ) GO -向 Books 表导入数据USE BlueSkyDB GO INSERT INTO Books VALUES(SQL Server 2005 实现与维护 ,9787302163305,Solid Quality Learning,79.00,CO01) GO INSERT INTO Books VALUES(ASP.NET 2.0入门经典 ,9787115158284,Scott Mitchell,49.00,CO01) GO INSERT

15、INTO Books VALUES(英语阅读词汇双突破,7560922171,杨建荣 ,15.50,LA01) GO INSERT INTO Books VALUES(杜拉拉升职记 ,9787561339121,NULL,26.00,FI01) GO INSERT INTO Books VALUES(夏洛的网 ,7532733416,NULL,17.00,FI01) GO INSERT INTO Books VALUES(新编日语 ,9787810097987,周平 ,11.80,LA01) GO INSERT INTO Books VALUES(管理信息系统 ,7111156242,祝士明

16、,21.00,NULL) GO INSERT INTO Books VALUES(系统防真导论 ,730203821x,肖田元 ,23.50,NULL) GO INSERT INTO Books VALUES(Java编程思想 ,711116220 x,Bruce Eckel,95.00,CO01) GO INSERT INTO Books VALUES(C#与 ASP.NET 程序设计 ,7560519636,曹锰 ,29.00,CO01) GO -向 Customers 表导入数据USE BlueSkyDB GO INSERT INTO Customers V ALUES( 张志远 ,12

17、345678) GO INSERT INTO Customers V ALUES( 李涛 ,12345678) GO INSERT INTO Customers V ALUES(Scottfield,S,12345678) GO -向 Orders 表导入数据USE BlueSkyDB GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 15 页 - - - - - - - - - INSERT INTO Orders V ALUES(2008-12-14,2008-1

18、2-17,3) GO INSERT INTO Orders V ALUES(2008-12-15,2008-12-18,1) GO INSERT INTO Orders V ALUES(2009-2-12,2009-2-15,1) GO INSERT INTO Orders V ALUES(2009-3-1,2009-3-4,2) GO INSERT INTO Orders V ALUES(2009-3-15,NULL,3) GO INSERT INTO Orders V ALUES(2009-3-16,NULL,1) GO -向 OrderItems 表导入数据USE BlueSkyDB G

19、O INSERT INTO OrderItems VALUES(1,1,1,71.10) GO INSERT INTO OrderItems VALUES(1,2,1,36.90) GO INSERT INTO OrderItems VALUES(2,6,10,10.03) GO INSERT INTO OrderItems VALUES(3,9,1,85.50) GO INSERT INTO OrderItems VALUES(3,10,1,26.10) GO INSERT INTO OrderItems V ALUES(4,1,2,71.10) GO INSERT INTO OrderIt

20、ems VALUES(4,2,2,36.90) GO INSERT INTO OrderItems VALUES(4,9,2,85.50) GO INSERT INTO OrderItems VALUES(4,10,2,26.10) GO INSERT INTO OrderItems VALUES(5,6,10,10.03) GO INSERT INTO OrderItems VALUES(6,7,1,18.90) GO -向 Comments 表导入数据USE BlueSkyDB GO INSERT INTO Comments V ALUES( 张志远 ,5, 适合参加MCTS 的考生使用,

21、1) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 15 页 - - - - - - - - - GO INSERT INTO Comments V ALUES( 李涛 ,4,ASP.NET 的基础知识 ,2) GO -向 ShoppingRecords 表导入数据USE BlueSkyDB GO INSERT INTO ShoppingRecords V ALUES(NEWID(),1,2009-3-15,1) GO -例 6-1 显示所有图书的详细信息。USE Bl

22、ueSkyDB GO SELECT bookID, title, ISBN, author, unitPrice, categoryCode FROM Books GO -第 2种写法SELECT * FROM Books GO -例 6-2 显示所有图书的书号、书名和单价。SELECT bookID, title, unitPrice FROM Books GO -例 6-3 在 Books 表中查看有哪些类别代号。SELECT categoryCode FROM Books GO -正确写法SELECT DISTINCT categoryCode FROM Books GO -例 6-4

23、返回 Books 表中前百分之20 行的数据。SELECT TOP 20 PERCENT * FROM Books GO -例 6-5 以中文列名显示所有图书的书号、书名、单价以及打9 折以后的价格。SELECT 书号 =bookID, title 书名 , unitPrice AS 原价 , unitPrice*0.9 AS 折扣价FROM Books 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 15 页 - - - - - - - - - GO -例 6-6 显示

24、某个作者编写了某本书以及该书的单价。SELECT TOP 3 author+ 编写的+title AS 图书 , 单价是： , unitPrice AS 单价FROM Books GO -例 6-7 显示前 3 本图书的单价。SELECT TOP 3 title+ 单价是： +CONVERT(nchar(6), unitPrice) AS 图书单价FROM Books GO -例 6-8 显示单价高于40 元的图书的书号、书名和单价。SELECT bookID, title, unitPrice FROM Books WHERE unitPrice40 GO -例 6-9 显示类别代码是LA0

25、1 或者单价低于20 元的图书的书号、书名、单价和类别代码。SELECT bookID, title, unitPrice, categoryCode FROM Books WHERE categoryCode=LA01 OR unitPrice= 20 AND unitPrice 30 GO -第 2种写法SELECT COUNT(bookID) AS 图书册数FROM Books WHERE unitPrice 30 GO -例 6-21 在 Books 表中统计有多少个类别代号。SELECT COUNT(categoryCode) AS 类别数FROM Books GO -正确写法SEL

26、ECT COUNT(DISTINCT categoryCode) AS 类别数FROM Books GO -例 6-22 统计类别编号是CO01 的图书册数和单价总和。SELECT COUNT(bookID) AS 图书册数 , SUM(unitPrice) AS 单价总和FROM Books WHERE categoryCode=CO01 GO -例 6-23 统计所有图书的最高单价、最低单价和平均单价。SELECT MAX(unitPrice) AS 最高单价 , MIN(unitPrice) AS 最低单价 , AVG(unitPrice) AS 平均单价FROM Books GO -例

27、 6-24 统计每本图书的销售数量总和。SELECT SUM(quantity) AS 销售总册数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 15 页 - - - - - - - - - FROM OrderItems GROUP BY bookID GO -正确写法SELECT bookID, SUM(quantity) AS 销售总册数FROM OrderItems GROUP BY bookID GO -例 6-25 统计销售次数超过1 次的图书的书号和销售总

28、册数。SELECT bookID, SUM(quantity) AS 销售总册数FROM OrderItems GROUP BY bookID HAVING COUNT(orderID)3 GO -例 6-26 统计单笔销售数量达到或超过10 本的图书的书号和销售总册数。SELECT bookID, SUM(quantity) AS 销售总册数FROM OrderItems GROUP BY bookID HAVING MAX(quantity)=10 GO -例 6-27 显示所有图书的书号、书名、单价和类别代号，最后显示所有图书的总价。SELECT bookID,title,unitPri

29、ce,categoryCode FROM Books ORDER BY categoryCode COMPUTE SUM(unitPrice) GO -例 6-28 显示所有图书的书号、书名、单价和类别代号，并显示每类图书的总价。SELECT bookID,title,unitPrice,categoryCode FROM Books ORDER BY categoryCode COMPUTE SUM(unitPrice) BY categoryCode GO -例 6-29 连接 Books 表和 Categories 表，显示图书编号、书名、单价以及类别编号和类别名称。SELECT b.b

30、ookID, b.title, b.unitPrice, c.categoryCode, c.categoryName FROM Books b, Categories c GO -例 6-30 显示图书的编号、书名、单价以及类别编号和类别名称。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 15 页 - - - - - - - - - SELECT b.bookID, b.title, b.unitPrice, c.categoryCode, c.categoryNa

31、me FROM Books b, Categories c WHERE b.categoryCode=c.categoryCode GO -例 6-31 显示每张订单的订单号、订购日期以及所订购的图书的书号、书名。SELECT o.orderID, o.orderDate, b.bookID, b.title FROM Books b, OrderItems i, Orders o WHERE b.bookID=i.bookID AND o.orderID=i.orderID GO -/* 创建表 Grades CREATE TABLE Grades (gradeID int identity

32、 PRIMARY KEY, description nchar(5), minUnitPrice decimal(6,2), maxUnitPrice decimal(6,2) ) GO INSERT INTO Grades V ALUES( 便宜 ,0,20) GO INSERT INTO Grades V ALUES( 适中 ,21,50) GO INSERT INTO Grades V ALUES( 昂贵 ,51,100) GO INSERT INTO Grades V ALUES( 非常昂贵 ,101,1000) GO -*/ -例 6-32 显示每本图书的书号、书名、单价和价格描述。

33、SELECT b.bookID, b.title, b.unitPrice, g.description FROM Books b, Grades g WHERE b.unitPrice=g.minUnitPrice AND b.unitPrice( SELECT A VG (unitPrice) FROM Books) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 15 页 - - - - - - - - - GO -例 6-38 显示书名中包含字符串ASP 的图书

34、的销售订单号和销售数量。SELECT orderID, quantity FROM OrderItems WHERE bookID IN ( SELECT bookID FROM Books WHERE title LIKE %ASP%) GO -例 6-39 对于那些比类别代号为FI01 的任意一本图书的单价高的图书，显示其书号和书名。SELECT bookID, title FROM Books WHERE unitPriceANY ( SELECT unitPrice FROM Books WHERE categoryCode=FI01) GO -例 6-40 对于那些比类别代号是FI0

35、1 的图书的所有单价都高的图书，显示其书号和书名。SELECT bookID, title FROM Books WHERE unitPriceALL ( SELECT unitPrice FROM Books WHERE categoryCode=FI01) GO -例 6-41 显示所有图书的书号、书名、单价以及销售总量。SELECT bookID, title, unitPrice, (SELECT SUM(quantity) FROM OrderItems i WHERE i.bookID=b.bookID) AS 销售总量FROM Books b GO -例 6-42 显示 2009

36、 年以后购买过图书的顾客的编号和姓名。SELECT c.customerID, c.customerName FROM Customers c WHERE EXISTS ( SELECT * FROM Orders o WHERE o.orderDate=01/01/09 AND c.customerID=o.customerID) GO -例 6-43 创建一个与订单表结构相同的表OrderArchive ，将 2008 年的订单记录拷贝到该表中。USE BlueSkyDB 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师

37、精心整理 - - - - - - - 第 14 页，共 15 页 - - - - - - - - - GO CREATE TABLE OrderArchive (orderID int PRIMARY KEY , orderDate datetime, shipDate datetime, customerID int ) GO INSERT INTO OrderArchive SELECT * FROM Orders WHERE orderDate BETWEEN 2008-1-1 AND 2008-12-31 GO -例 6-44 列出 Customers 表和 Comments 表中所有

38、的姓名，保留重复的数据行。(SELECT customerName FROM Customers) UNION ALL (SELECT userName FROM Comments) GO -例 6-45 列出 Customers 表和 Comments 表中都存在的姓名，去除重复的数据行。(SELECT customerName FROM Customers) INTERSECT (SELECT userName FROM Comments) GO -例 6-46 列出 Customers 表中有而Comments 表中没有的姓名，去除重复的数据行。(SELECT customerName FROM Customers) EXCEPT (SELECT userName FROM Comments) GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 15 页 - - - - - - - - -