fuill 2022-11-27 18:50 采纳率: 68.2%
浏览 22
已结题

查找从未在图书馆借阅过的书的书名

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
查找从未在图书馆借阅过的书的书名

  • 写回答

1条回答 默认 最新

  • Nicehanxy 2022-12-03 14:00
    关注

    左连接查询，查询所有借出时间为空的书名

    
```sql
SELECT A.BoTitle
      ,B.BILendDate
FROM Books A
left join BorrowInfo B
on A.BoID=B.BI_BoID
where B.BILendDate is null

    ```

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

问题事件

  • 系统已结题 12月14日
  • 已采纳回答 12月6日
  • 创建了问题 11月27日

悬赏问题

  • ¥16 mybatis的代理对象无法通过@Autowired装填
  • ¥15 可见光定位matlab仿真
  • ¥15 arduino 四自由度机械臂
  • ¥15 wordpress 产品图片 GIF 没法显示
  • ¥15 求三国群英传pl国战时间的修改方法
  • ¥15 matlab代码代写，需写出详细代码，代价私
  • ¥15 ROS系统搭建请教（跨境电商用途）
  • ¥15 AIC3204的示例代码有吗，想用AIC3204测量血氧，找不到相关的代码。
  • ¥20 CST怎么把天线放在座椅环境中并仿真
  • ¥15 任务A：大数据平台搭建（容器环境）怎么做呢？