常用sql语句记录--sqlserver

1.使用SQL语句创建满足要求的数据表；
建立图书类型表（BookClass）、图书表（Book）和借阅信息表（Borrow），表结构：

表BookClass包含类型编号：BCid，
类型名称：BCname这两列，其中BCid是主键；

表Book包含四个字段：书号：Bid，书名：Bname，类型编号：BCid，价格：
BPrice，其中Bid为主键，BCid为外键；

表Borrow包含四个字段：Bid，书号：RName读者姓名（假设读者姓名都不重复）

，BDate借书时间，RDate还书时间，（Bid，Rname，BDate）为主键，Bid为参照Book表的外键；

======================创建==============================

sql语句：

create table BookClass(
    BCid varchar(20) primary key,   //这里主键
    BCname varchar(20)
)

create table Book(
    Bid varchar(20) primary key,    //这里主键
    BName varchar(20) ,
    BCid varchar(20)FOREIGN KEY References BookClass,    //外键写法
    BPrice float
)
create table Borrow(
    Bid varchar(20) FOREIGN KEY References Book,  //外键写法
    RName varchar(20) ,
    BDate date ,
    RDate date,
    primary key(Bid,RName,BDate)     //多个主键写法
   )

========================创建==============================

书号  书名       价格     类型编号    类型名称   
B1    水浒传        15.5        C01        小说      
B2    西游记        25.0        C01        小说      
B3    大学物理   19.0         C02        自然科学  
B4    摄影基础    12         C03        艺术类    
B8    黑格尔哲学  30.0        C04        哲学类    

借阅信息：
书号      读者姓名     借书时间     还书时间
B1         张三        2016-03-01    2016-05-10
B1         李四        2016-05-14
B2         王五        2016-04-05    2016-05-01
B2         张三        2016-05-05    
B8         刘军        2016-05-14

2.根据表插入相应数据

=========================插入===========================

insert into BookClass(BCid,BCname) values('C01','小说')
insert into BookClass(BCid,BCname) values('C02','自然科学')
insert into BookClass(BCid,BCname) values('C03','艺术类')
insert into BookClass(BCid,BCname) values('C04','哲学类')

insert into Book(Bid,BName,BCid,BPrice) values('B1','水浒传','C01',15.5)
insert into Book(Bid,BName,BCid,BPrice) values('B2','西游记','C01',25.0)
insert into Book(Bid,BName,BCid,BPrice) values('B3','大学物理','C02',19.0)
insert into Book(Bid,BName,BCid,BPrice) values('B4','摄影基础','C03',12.0)
insert into Book(Bid,BName,BCid,BPrice) values('B8','黑格尔哲学','C04',30.0)

insert into Borrow(Bid,RName,BDate,RDate) values('B1','张三','2016-03-01','2016-05-10')
insert into Borrow(Bid,RName,BDate,RDate) values('B1','李四','2016-05-14','')
insert into Borrow(Bid,RName,BDate,RDate) values('B2','王五','2016-04-05','2016-05-01')
insert into Borrow(Bid,RName,BDate,RDate) values('B2','张三','2016-05-05','')
insert into Borrow(Bid,RName,BDate,RDate) values('B8','刘军','2016-05-14','')

=====================插入=================================

3.根据上面三个表的内容，分别写出完成下面查询要求的SQL语句；
（1）找出姓名为李四的读者所借阅的所有图书，列出书号，书名和图书类型，借书时间；将此创建为视图，命名为vi_1;

================比较好的解决方案===================

select t1.*,t2.*,t3.* from BookClass  t1 join Book t2 on  t1.BCid = t2.BCid join Borrow t3 on t3.Bid = t2.Bid where BName = '李四'//三个表连接

解析：关于三个表的连接

join on 自然连接，

left join on 左连接 ，以左边的表为基准连接右边的表。

full join on 全连接，没有对应的数据也会显示出来 但是会以null显示。

================比较好的解决方案===================

（2）找出被借阅次数最多的图书的借阅信息，列出书号，书名，读者姓名，借阅时间；将此创建为视图，命名为vi_2；

================比较好的解决方案===================
select Borrow.Bid,Book.BName,Borrow.RName,Borrow.BDate from Book join Borrow on Book.Bid = Borrow.Bid and Borrow.Bid in
(select top 1 Bid from Borrow group by Bid order by COUNT(Bid) desc)

解析：

select top 1 Bid from Borrow group by Bid order by COUNT(Bid) desc //这句的意思是找出 借阅次数最多的图书的bid

然后根据选出的bid用表连接查询出相关数据

================比较好的解决方案===================

（3）找出借书次数为前3名的读者的借书信息，列出读者信息，图书编号，书名，借书时间，还书时间；将此创建为视图，命名为vi_3；



================比较好的解决方案===================
select Book.Bid,Book.BName,Borrow.BDate,Borrow.RDate from Book join Borrow on Book.Bid = Borrow.Bid and Borrow.RName in
(select top 3 Borrow.RName from Borrow group by Borrow.RName order by COUNT(Borrow.RName) desc)

解析：为了找出3名读者，我们先要根据borrow表 找出相应读者的排名，再根据名字查找相关借书信息

================比较好的解决方案===================
=====新方案=========================================
select * from Borrow;

select t3.RName,t2.Bid,t2.Bname,t3.BDate,t3.RDate from book t2 join Borrow t3
on t3.Bid=t2.Bid

go
Create view vi_m
as
select rname ,DENSE_RANK() over ( order by COUNT(*) Desc)as 名次 from borrow group by Rname

select * from Borrow

select t3.RName,t2.Bid,t2.Bname,t3.BDate,t3.RDate from book t2 join Borrow t3
on t3.Bid=t2.Bid where RName in
(select rname from vi_m where 名次<=3);


select t3.RName,t2.Bid,t2.Bname,t3.BDate,t3.RDate from book t2 join Borrow t3
on t3.Bid=t2.Bid where RName in (
select rname from (
select rname ,DENSE_RANK() over ( order by COUNT(*) Desc)
 as 名次
from borrow
group by Rname) as b where 名次<=3 )

解析：这是老师给出的方案，以后再研究

===新方案===============================================

（4）查询出哪些图书一直没有被借阅过，列出图书编号，图书名称，类型名称；将此创建为视图，命名为vi_4；
================比较好的解决方案===================
select Book.Bid,Book.BName,BookClass.BCname from Book join BookClass on Book.BCid = BookClass.BCid and Book.Bid not in (select Bid from Borrow)
================比较好的解决方案===================

（5）查询出每类图书中借阅次数最多的图书信息，列出图书编号、图书名称、类型名称；将此创建为视图，命名为vi_5；
select Book.Bid,Book.BName,BookClass.BCname from Book join BookClass on Book.BCid = BookClass.BCid
select BookClass.BCid from BookClass  left join Book on BookClass.BCid = Book.BCid left join Borrow on Borrow.Bid = Book.Bid
group by BookClass.BCid

select BName,COUNT(*) from BookClass full join Book on BookClass.BCid = Book.BCid full join Borrow on Borrow.Bid = Book.Bid
where BookClass.BCid in (select BCid from BookClass) group by BName  //查询出所有借阅次数最多的书
select BName,COUNT(*) from BookClass full join Book on BookClass.BCid = Book.BCid full join Borrow on Borrow.Bid = Book.Bid
where BookClass.BCid = 'C01' group by BName //查询出co1这个类别借阅次数最多的图书

select Borrow.Bid,(COUNT(*)) as 借阅次数 from Borrow join Book on Borrow.Bid = Book.Bid join BookClass on BookClass.BCid = Book.BCid   group by Borrow.Bid
//查询出借阅次数最多的图书
================比较好的解决方案===================

select t2.Bid,t2.Bname,t1a.BCname,t1a.BCid from
BookClass t1a join Book t2 on t1a.BCid=t2.BCid
where t2.Bid in (
select top 1 with ties t2.Bid from
BookClass t1 join Book t2 on t1.BCid=t2.BCid
join Borrow t3 on t2.Bid=t3.Bid where t1.BCid=t1a.BCid
group by t2.Bid order by COUNT(*) Desc);

================比较好的解决方案===================

（6）查询出Borrow表中借阅天数最长的借书信息，还没有归还的不算在内，列出书号，读者姓名，借书日期，还书日期；将此创建为视图，命名为vi_6；

//datediff
select * from Borrow where RDate = null

//算上没有还回来的书
select *,DATEDIFF(DD,BDate,ISNULL(Rdate,GETDATE())) from Borrow

解析：这里用到两个函数

datediff（数据类型，被减的日期，减的日期） 返回值是 减的日期-被减的日期= 想要的数据类型

isnull（检验的数据，默认数据）检车检验的数据是否为空，如果是空则返回默认数据，否则返回原来的数据

--4.创建存储过程：设计一个存储过程，可以根据图书的编号，和设定的价格，把图书表中该图书的价格改为设定的值，如果给
--定的图书编号的图书不存在，应该提示错误信息。创建该存储过程，命名为usp_1；并调用该存储过程，把B1号图书的价格改为30；

update Book set BPrice = 30 where Bid = 'b1'

create proc usp_1(@Bid varchar(20),@BPrice float)
as
begin
update Book set BPrice = @BPrice where Bid = @Bid
end
exec usp_1 'b1',30  //执行存储过程

--5.修改表BookClass；
--在BookClass表中增加字段总量Bnum列，并将图书信息表中相应类型的图书的总数量填写到Bnum列；
alter table BookClass add Bnum int // 增加列


update BookClass set Bnum=(
select COUNT(*) from Book where BCid=BookClass.bcid);

--6.删除数据；
--（1）删除Borrow表中李四已经归还了的借阅信息，将此创建为存储过程，命名为usp_2；
select * from Borrow where RName='张三' and RDate is not null;
delete from Borrow where RName='张三' and RDate is not null;


--7.创建新表，将所有已经归还的借阅信息放入到新表Borrow_his中。
--将此语句创建为存储过程，命名为usp_3；
--*/

select * into borrow_his from Borrow where RDate is not null;

select * from Borrow_his;

go
insert into Borrow_his  select * from Borrow where RDate is not null;