USE [master]
GO
/****** Object: Database [CollegeCard_DB] Script Date: 2021/4/22 15:05:22 ******/
CREATE DATABASE [CollegeCard_DB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'一卡通', FILENAME = N'D:\一卡通.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'一卡通_log', FILENAME = N'd:\一卡通_log.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CollegeCard_DB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CollegeCard_DB].[dbo].[sp_fulltext_database] @action = 'enable'
end
USE [CollegeCard_DB]
GO
/****** Object: Table [dbo].[Books] Script Date: 2021/11/25 14:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Books](
[BoID] [varchar](12) NOT NULL,
[BoTitle] [varchar](42) NULL,
[BoAuthor] [varchar](20) NULL,
[BoPublisher] [varchar](28) NULL,
[BoPublicationDate] [date] NULL,
[BoPrice] [decimal](18, 2) NULL,
[BoStatus] [int] NULL,
CONSTRAINT [PK_Books_BoID] PRIMARY KEY CLUSTERED
(
[BoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BorrowInfo] Script Date: 2021/11/25 14:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BorrowInfo](
[BIID] [int] IDENTITY(20000,1) NOT NULL,
[BI_CID] [varchar](12) NOT NULL,
[BI_BoID] [varchar](12) NOT NULL,
[BILendDate] [datetime] NULL,
[BIReturnDate] [datetime] NULL,
CONSTRAINT [PK_BorrowInfo_BIID] PRIMARY KEY CLUSTERED
(
[BIID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Cards] Script Date: 2021/11/25 14:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cards](
[CID] [varchar](12) NOT NULL,
[C_UID] [varchar](12) NULL,
[CBalance] [decimal](18, 2) NULL,
[CDate] [datetime] NULL,
[CStatus] [int] NULL,
CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Users] Script Date: 2021/11/25 14:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[UID] [varchar](12) NOT NULL,
[U_UTID] [int] NULL,
[UName] [varchar](30) NULL,
[UTel] [varchar](11) NULL,
[UIDNo] [varchar](18) NULL,
CONSTRAINT [PK_用户信息表] PRIMARY KEY CLUSTERED
(
[UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[UserTypes] Script Date: 2021/11/25 14:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTypes](
[UTID] [int] IDENTITY(1,1) NOT NULL,
[UTName] [varchar](12) NULL,
[BorrowMaxNumber] [int] NULL,
[BorrowMaxDays] [int] NULL,
CONSTRAINT [PK_UserTypes] PRIMARY KEY CLUSTERED
(
[UTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CA10.10', N'数据库设计与实现', N'安风波', N'白云', CAST(N'2012-06-18' AS Date), CAST(32.30 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CA10.11', N'数据库设计与实现', N'安风波', N'白云', CAST(N'2012-06-18' AS Date), CAST(32.30 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CF12.245', N'计算机文化基础', N'刘凌云', N'青山', CAST(N'2013-02-11' AS Date), CAST(21.80 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CF23.55', N'数据结构(C语言版)', N'马志刚', N'青山', CAST(N'2010-08-26' AS Date), CAST(24.50 AS Decimal(18, 2)), 0)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CF33.33', N'数据结构实用教程', N'王晓云', N'蓝天', CAST(N'2011-01-15' AS Date), CAST(22.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG10.22', N'Java程序设计', N'张大海', N'碧水', CAST(N'2012-02-22' AS Date), CAST(20.80 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG11.11', N'程序逻辑与C语言', N'张大海', N'蓝天', CAST(N'2011-07-28' AS Date), CAST(26.30 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG11.12', N'程序逻辑与C语言', N'张大海', N'蓝天', CAST(N'2011-07-28' AS Date), CAST(26.30 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG12.01', N'C语言程序设计', N'梁晓峰', N'青山', CAST(N'2013-05-08' AS Date), CAST(22.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG12.02', N'C语言程序设计', N'梁晓峰', N'青山', CAST(N'2013-05-08' AS Date), CAST(22.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CG22.12', N'Visual Basic实用教程', N'梁晓峰', N'碧水', CAST(N'2010-06-10' AS Date), CAST(28.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CJ12.10', N'Java Web程序设计', N'赵文华', N'碧水', CAST(N'2011-04-19' AS Date), CAST(35.80 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CJ12.11', N'Java Web程序设计', N'赵文华', N'碧水', CAST(N'2011-04-19' AS Date), CAST(35.80 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CL23.10', N'ASP.Net动态网页程序设计', N'周洪涛', N'蓝天', CAST(N'2010-03-12' AS Date), CAST(30.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Books] ([BoID], [BoTitle], [BoAuthor], [BoPublisher], [BoPublicationDate], [BoPrice], [BoStatus]) VALUES (N'CL23.11', N'ASP.Net动态网页程序设计', N'周洪涛', N'蓝天', CAST(N'2010-03-12' AS Date), CAST(30.00 AS Decimal(18, 2)), 0)
SET IDENTITY_INSERT [dbo].[BorrowInfo] ON
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20000, N'100001', N'CJ12.10', CAST(N'2013-08-05 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20001, N'100001', N'CL23.10', CAST(N'2013-11-08 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20002, N'100002', N'CA10.11', CAST(N'2013-10-10 00:00:00.000' AS DateTime), CAST(N'2013-11-21 00:00:00.000' AS DateTime))
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20003, N'100002', N'CJ12.11', CAST(N'2013-12-18 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20004, N'100003', N'CG10.22', CAST(N'2013-09-12 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20005, N'100003', N'CG11.12', CAST(N'2013-11-01 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20006, N'100004', N'CF33.33', CAST(N'2013-10-16 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20007, N'100004', N'CG11.11', CAST(N'2013-10-16 00:00:00.000' AS DateTime), CAST(N'2013-11-01 00:00:00.000' AS DateTime))
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20008, N'100005', N'CA10.10', CAST(N'2013-12-10 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20009, N'100005', N'CG11.11', CAST(N'2013-12-10 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20010, N'100005', N'CG12.01', CAST(N'2013-11-22 00:00:00.000' AS DateTime), NULL)
INSERT [dbo].[BorrowInfo] ([BIID], [BI_CID], [BI_BoID], [BILendDate], [BIReturnDate]) VALUES (20011, N'100001', N'CF12.245', CAST(N'2014-01-13 17:30:47.780' AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[BorrowInfo] OFF
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100001', N'199808100330', CAST(986.78 AS Decimal(18, 2)), CAST(N'2003-11-20 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100002', N'201106200526', CAST(620.62 AS Decimal(18, 2)), CAST(N'2011-06-18 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100003', N'201309120016', CAST(120.35 AS Decimal(18, 2)), CAST(N'2013-09-12 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100004', N'201309081120', CAST(80.20 AS Decimal(18, 2)), CAST(N'2013-09-12 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100005', N'201009120038', CAST(78.39 AS Decimal(18, 2)), CAST(N'2010-09-02 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Cards] ([CID], [C_UID], [CBalance], [CDate], [CStatus]) VALUES (N'100006', N'201401160001', CAST(2000.00 AS Decimal(18, 2)), CAST(N'2014-01-18 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'199808100330', 1, N'王立群', N'13000318212', N'330719197012254611')
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'201009120038', 2, N'李亚茜', N'15886258808', N'330725199211104321')
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'201106200526', 3, N'刘超', N'15886826370', N'330723199901155114')
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'201309081120', 3, N'张刚', N'13711266109', N'370321199908180036')
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'201309120016', 3, N'王晓奇', N'13999062203', N'130306200006180021')
INSERT [dbo].[Users] ([UID], [U_UTID], [UName], [UTel], [UIDNo]) VALUES (N'201401160001', 1, N'马来', N'15869267329', N'610305197096090018')
SET IDENTITY_INSERT [dbo].[UserTypes] ON
INSERT [dbo].[UserTypes] ([UTID], [UTName], [BorrowMaxNumber], [BorrowMaxDays]) VALUES (1, N'教师', 50, 720)
INSERT [dbo].[UserTypes] ([UTID], [UTName], [BorrowMaxNumber], [BorrowMaxDays]) VALUES (2, N'研究生', 20, 180)
INSERT [dbo].[UserTypes] ([UTID], [UTName], [BorrowMaxNumber], [BorrowMaxDays]) VALUES (3, N'学生', 10, 90)
INSERT [dbo].[UserTypes] ([UTID], [UTName], [BorrowMaxNumber], [BorrowMaxDays]) VALUES (4, N'临时人员', 5, 45)
SET IDENTITY_INSERT [dbo].[UserTypes] OFF
ALTER TABLE [dbo].[BorrowInfo] WITH CHECK ADD CONSTRAINT [FK_BorrowInfo_Books_BI_BoID] FOREIGN KEY([BI_BoID])
REFERENCES [dbo].[Books] ([BoID])
GO
ALTER TABLE [dbo].[BorrowInfo] CHECK CONSTRAINT [FK_BorrowInfo_Books_BI_BoID]
GO
ALTER TABLE [dbo].[BorrowInfo] WITH CHECK ADD CONSTRAINT [FK_BorrowInfo_Cards] FOREIGN KEY([BI_CID])
REFERENCES [dbo].[Cards] ([CID])
GO
ALTER TABLE [dbo].[BorrowInfo] CHECK CONSTRAINT [FK_BorrowInfo_Cards]
GO
ALTER TABLE [dbo].[Cards] WITH CHECK ADD CONSTRAINT [FK_Cards_Users_C_UID] FOREIGN KEY([C_UID])
REFERENCES [dbo].[Users] ([UID])
GO
ALTER TABLE [dbo].[Cards] CHECK CONSTRAINT [FK_Cards_Users_C_UID]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_UserTypes_UID] FOREIGN KEY([U_UTID])
REFERENCES [dbo].[UserTypes] ([UTID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_UserTypes_UID]
GO
ALTER TABLE [dbo].[BorrowInfo] WITH CHECK ADD CONSTRAINT [CK_BorrowInfo_BIReturnDate] CHECK (([BIReturnDate]>=[BILendDate]))
GO
ALTER TABLE [dbo].[BorrowInfo] CHECK CONSTRAINT [CK_BorrowInfo_BIReturnDate]
GO
ALTER TABLE [dbo].[Cards] WITH CHECK ADD CONSTRAINT [CK_T_Cards1] CHECK (([CBalance]>=(0)))
GO
ALTER TABLE [dbo].[Cards] CHECK CONSTRAINT [CK_T_Cards1]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'作者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Books', @level2type=N'COLUMN',@level2name=N'BoAuthor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0-正常,1-挂起,2-挂失,3-注销' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Cards', @level2type=N'COLUMN',@level2name=N'CStatus'
GO
Find the titles of books which have never been borrowed in the library
查找从未在图书馆借阅过的书的书名