图书馆SQL查询2

--创建图书数据库
CREATE DATABASE LibWangdeqiang
GO

--创建图书类别表
USE LibWangdeqiang
CREATE TABLE t_booktype(
--图书类别编号
booktypeid VARCHAR(5) NOT NULL PRIMARY KEY,
--类别名称 --创建唯一约束
booktypename VARCHAR(30) CONSTRAINT AK_TransactionID UNIQUE(booktypename),




--上级类别
fathertype VARCHAR(5) FOREIGN KEY (booktypeid) REFERENCES t_booktype(booktypeid),
--参照booktypeid


)
GO
CREATE TABLE t_publisher(
--pubid 出版社编号
pubid VARCHAR(10) NOT NULL PRIMARY KEY,


--pubname  出版社名称
pubname NVARCHAR(30) NOT NULL UNIQUE,


--pubaddress 出版社地址
pubaddress NVARCHAR(100),


--pubphone 出版社电话
pubphone NVARCHAR(20),


--contactman 联系人
contactman NVARCHAR(20)
)
GO


--t_book（图书表）
CREATE TABLE t_book(
--bookid 图书编号
bookid VARCHAR(20) NOT NULL PRIMARY KEY,
--bookname 书名
bookname NVARCHAR(30),
--ISBN ISBN号 
ISBN NCHAR(20) UNIQUE,
--author 作者 
author NVARCHAR(50),
--price 价格 
price Decimal(10,2),
--pages 页数 INT
pages INT,
--bookshelf 所在书架
bookshelf NVARCHAR(20),
--currquantity 现库存量 
currquantity INT DEFAULT 0,
--sumquantity 总库存量 
sumquantity INT DEFAULT 0,
--numberoftimes 借出次数 
numberoftimes INT DEFAULT 0,
--booktypeid 图书类型 
booktypeid VARCHAR(5)FOREIGN KEY (booktypeid) REFERENCES t_booktype(booktypeid),
--pubname 出版社名称 
pubname NVARCHAR(30),
--pubid 出版社编号 
pubid VARCHAR(10)FOREIGN KEY (pubid) REFERENCES t_publisher(pubid)
)
GO
use LibWangdeqiang
Insert Into t_booktype values ('A1','马克思主义','A') 
Insert Into t_booktype values ('A2','列宁主义','A') 
Insert Into t_booktype values ('A1','***思想','A') 
Insert Into t_booktype values ('B1','哲学','B') 
Insert Into t_booktype values ('C1','社会科学总论','C') 
Insert Into t_booktype values ('D1','政治','C') 
Insert Into t_booktype values ('D2','法律','C') 
Insert Into t_booktype values ('E1','军事','E') 
Insert Into t_booktype values ('F1','经济','F') 
Insert Into t_booktype values ('G1','文化','G') 
Insert Into t_booktype values ('G2','科学','G') 
Insert Into t_booktype values ('G3','教育','G') 
Insert Into t_booktype values ('G4','体育','G')
Insert Into t_booktype values ('H1','语言','H') 
Insert Into t_booktype values ('H1','文字','H') 
Insert Into t_booktype values ('I1','文学','I') 
Insert Into t_booktype values ('J1','艺术','J') 
Insert Into t_booktype values ('K1','历史','K') 
Insert Into t_booktype values ('K2','地理','K')
Insert Into t_booktype values ('N1','自然科学总论','N') 
Insert Into t_booktype values ('N2','数理科学和化学','N') 
Insert Into t_booktype values ('Q1','生物科学','Q') 
Insert Into t_booktype values ('R1','医药','R') 
Insert Into t_booktype values ('R2','卫生','R') 
Insert Into t_booktype values ('S1','农业科学','S')
Insert Into t_booktype values ('T1','工业技术','T') 
Insert Into t_booktype values ('U1','交通运输','U') 
Insert Into t_booktype values ('V1','航空','V') 
Insert Into t_booktype values ('V2','航天','V') 
Insert Into t_booktype values ('X1','环境科学','X') 
Insert Into t_booktype values ('Z1','综合性图书','Z')


Insert Into t_publisher values ('01','人民出版社','中国北京市*城区隆福寺街','010-58758866','王红宇') 
Insert Into t_publisher values ('02','人民文学出版社','中国北京市*城区朝阳门内大街朝内大街166','010-65221920','李春凯') 
Insert Into t_publisher values ('03','科学出版社','中国北京市*城区*黄城根北街16号','010-64780850','王北亭') 
Insert Into t_publisher values ('04','高等教育出版社','中国北京市西城区北太平庄德胜门外大街辅路','010-58581507','张艺文') 
Insert Into t_publisher values ('05','商务印书馆','北京王府井大街36号','010-65253913','曹征') 
Insert Into t_publisher values ('121','电子工业出版社','北京市万寿路南口金家村288号华信大厦','010-88258888','服务部') 
Insert Into t_publisher values ('111','机械工业出版社','北京市西城区百万庄大街22号','010-88379639','代小姐') 
Insert Into t_publisher values ('5635','北京邮电大学出版社','中国北京市海淀区杏坛路22号','010-62281264','王丹丹') 


GO
--查询出版社
SELECT * FROM t_publisher
GO
use LibWangdeqiang
Insert Into t_book 
values ('TN929','Kotlin崛起:次世代Android开发',
'978-7-121-32494-9','张云波',99.0,544,'逸夫馆三层*区',1,2,1,'T1','电子工业出版社','121')
GO
Insert Into t_book 
values ('TP312JA','Java语言导学',
'978-7-111-57330-2','加拉尔多',99.0,543,'逸夫馆三层西区',1,2,1,'T1','机械工业出版社','111')
GO
Insert Into t_book 
values ('TN929.53','疯狂Android讲义',
'978-7-121-25958-6','李刚',108.0,765,'逸夫馆三层*区',1,2,1,'T1','电子工业出版社','121')
GO
Insert Into t_book 
values ('TN929.51','Android实例详解:项目实训开发',
'978-7-5635-4797-5','韩迪',76.0,597,'逸夫馆三层*区',1,3,2,'T1','北京邮电大学出版社','5635')
GO
--查询表 图书表
SELECT * FROM t_book
--删除


SELECT * FROM t_book


--(删除)对韩迪进行封杀 ,所有有关韩迪的书都进行删除
DELETE FROM t_book
WHERE author='韩迪'; 
GO
--(增加)对韩迪进行宣传 ,所有有关韩迪的书都进行添加


Insert Into t_book 
values ('TN929.51','Android实例详解:项目实训开发',
'978-7-5635-4797-5','韩迪',76.0,597,'逸夫馆三层*区',1,3,2,'T1','北京邮电大学出版社','5635')
GO


--(修改) 将韩迪的书改成王德强的书
UPDATE t_book
SET author = '王德强'
WHERE author = '韩迪'
SELECT * FROM t_book
GO


--(查找) 作者为王德强的书
SELECT * FROM t_book WHERE author='王德强'
GO

--(查找作者为王某的书)
SELECT * FROM t_book WHERE author LIKE '王%'
GO


--(查找作者为张某的书)
SELECT * FROM t_book WHERE author LIKE '张%'
GO
--查找所有出版社 的名字和地址
SELECT DISTINCT t_publisher.pubname,t_publisher.pubaddress FROM t_publisher
GO
--查找分类号为T的所有书籍
SELECT * FROM t_book WHERE booktypeid LIKE 'T%'
GO
--查找每个出版社的所有书籍的总价格,按照图书册数进行降序排列
SELECT t_book.pubname'的图书总价为','册数',count(*),SUM(t_book.price) FROM t_book GROUP BY t_book.pubname
ORDER BY count(*) desc
GO