天猫官方网站,夸克建站系统官网,网站建设可行分析性报告,wordpress 订阅插件1.查询借阅了计算机类或者文学类图书的读者的借书证号
USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类 OR Class 文学类2.查询同时借阅了计算机类和文学类图书的读者的借书证号
USE T…1.查询借阅了计算机类或者文学类图书的读者的借书证号
USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类 OR Class 文学类2.查询同时借阅了计算机类和文学类图书的读者的借书证号
USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类
INTERSECT
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 文学类USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类 AND Class 文学类但不知道为什么我用and连接的话就查询不到 因为一本书不能既是计算机类又是文学类所以and不行可以用子查询
USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类 AND Reader.Lno IN (SELECT Lend.LnoFROM Book,Lend,ReaderWHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Book.Class 文学类
)3.查询只借阅了计算机类图书而没有借阅文学类图书的读者借书证号、ISBN和借书日期
USE TSGL
GO
SELECT DISTINCT Reader.Lno,Lend.ISBN,Lend.Bordate
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 计算机类
EXCEPT
SELECT DISTINCT Reader.Lno,Lend.ISBN,Lend.Bordate
FROM Book,Lend,Reader
WHERE Book.ISBNLend.ISBN AND Lend.LnoReader.Lno AND Class 文学类4.查询没有被借阅的图书的ISBN和书名
USE TSGL
GO
SELECT Book.ISBN,Book.Bname
FROM Book
WHERE ISBN in
(SELECT ISBN
FROM BookEXCEPT
SELECT DISTINCT ISBN
FROM Lend)5.查询没有借阅计算机类图书的读者借书证号、ISBN和借书日期
USE TSGL
GO
SELECT Book.ISBN
FROM Lend,Book
WHERE Book.ISBNLend.ISBN AND Book.ISBN NOT IN (SELECT Book.ISBN
FROM Book,Lend
WHERE Book.ISBNLend.ISBN AND Class 计算机类)上面这个不太对 改良版
USE TSGL
GO
SELECT Lend.Lno,Lend.ISBN,Lend.Bordate
FROM Lend
WHERE Lend.ISBN NOT IN (SELECT Book.ISBN
FROM Book
WHERE Class 计算机类)我又不知道为什么下面这个为什么查询不出来
USE TSGL
GO
SELECT Book.ISBN
FROM Lend,Book
WHERE NOT EXISTS (SELECT Book.ISBN
FROM Book,Lend
WHERE Book.ISBNLend.ISBN AND Class 计算机类)并且子查询是没有问题的 因为要考虑这两个是如何做的not exict是说有相同的ISBN存在返回false不存在返回True 改良后这个查询结果和前面是一样的但是多了一个NULL值行又不知道为什么
USE TSGL
GO
SELECT DISTINCT Lend.Lno,Lend.ISBN,Lend.Bordate
FROM Lend
WHERE NOT EXISTS (SELECT *FROM BookWHERE Lend.ISBN Book.ISBN AND Book.Class 计算机类
)6.查询借阅了没有与借书证号为201207035102的读者所借全部图书相同的读者的借书证号
USE TSGL
GO
SELECT Reader.Lno
FROM Reader,Lend
WHERE Reader.LnoLend.Lno AND Lend.ISBN NOT IN (SELECT Lend.ISBN
FROM Reader,Lend
WHERE Reader.LnoLend.Lno AND Reader.Lno 201207035102)
