SQL SERVER数据库基础（8）子查询，事务，索引，视图，游标

转载

LDX1998 2021-08-30 21:59:34

一、子查询

（1）关羽的银行卡号为"6225547858741263"，查询出余额比关羽多的银行卡信息，显示卡号，身份证，姓名，余额。

--方案一
declare @balance money
select @balance=(select CardMoney from BankCard where CardNo='6225547858741263')
select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardMoney>@balance

--方案二
select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardMoney>(select CardMoney from BankCard where CardNo='6225547858741263')

（2）从所有账户信息中查询出余额最高的交易明细(存钱取钱信息)。

--如果有多个银行卡余额相等并且最高，此方案只能求出其中一个人的明细
select*from CardExchange where CardNo=
(select top 1 CardNo from BankCard order by CardMoney desc)

select*from CardExchange where CardNo in
(select CardNo from BankCard where CardMoney=
(select max(CardMoney)from BankCard))

（3）查询有取款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardNo in(select CardNo from CardExchange where MoneyOutBank>0)

（4）查询出没有存款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardNo not in(select CardNo from CardExchange where MoneyInBank>0)

（5）关羽的银行卡号为"6225547858741263",查询当天是否有收到转账。

if exists(select *from CardTransfer where CardNoIn='6225547858741263'
and convert(varchar(22),getdate(),23)=convert(varchar(22),TransferTime,23))
  begin
	print'有收到转帐'
  end
else
  begin
	print'没有收到转帐'
  end

（6）查询出交易次数（存款取款操作）最多的银行卡账户信息，显示：卡号，身份证，姓名，余额，交易次数。

select top 1 BankCard.CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 ,
Temp.myCount 交易次数 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
inner join (select CardNo,count(*) myCount from CardExchange group by CardNo)Temp
on BankCard.CardNo=Temp.CardNo 
order by myCount desc

--方案二(如果有多个人交易次数相同，都是交易次数最多，则使用以下方案)
select  BankCard.CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,交易次数 
from AccountInfo
inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
inner join
(select CardNo,COUNT(*) 交易次数 from CardExchange group by CardNo) Temp 
on BankCard.CardNo = Temp.CardNo
where 交易次数 = (select max(交易次数) from
(select CardNo,COUNT(*) 交易次数 from CardExchange group by CardNo) Temp )

（7）查询出没有转账交易记录的银行卡账户信息，显示卡号，身份证，姓名，余额。

select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from BankCard 
left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where BankCard.CardNo not in (select CardNoIn from CardTransfer)
and BankCard.CardNo not in (select CardNoOut from CardTransfer)

（8）分页。

--数据结构和数据如下：
create table Student
(
    StuId int primary key identity(1,2), --自动编号
    StuName varchar(20),
    StuSex varchar(4)
)
insert into Student(StuName,StuSex) values('刘备','男')
insert into Student(StuName,StuSex) values('关羽','男')
insert into Student(StuName,StuSex) values('张飞','男')
insert into Student(StuName,StuSex) values('赵云','男')
insert into Student(StuName,StuSex) values('马超','男')
insert into Student(StuName,StuSex) values('黄忠','男')
insert into Student(StuName,StuSex) values('魏延','男')
insert into Student(StuName,StuSex) values('简雍','男')
insert into Student(StuName,StuSex) values('诸葛亮','男')
insert into Student(StuName,StuSex) values('徐庶','男')
insert into Student(StuName,StuSex) values('周仓','男')
insert into Student(StuName,StuSex) values('关平','男')
insert into Student(StuName,StuSex) values('张苞','男')
insert into Student(StuName,StuSex) values('曹操','男')
insert into Student(StuName,StuSex) values('曹仁','男')
insert into Student(StuName,StuSex) values('曹丕','男')
insert into Student(StuName,StuSex) values('曹植','男')
insert into Student(StuName,StuSex) values('曹彰','男')
insert into Student(StuName,StuSex) values('典韦','男')
insert into Student(StuName,StuSex) values('许褚','男')
insert into Student(StuName,StuSex) values('夏侯敦','男')
insert into Student(StuName,StuSex) values('郭嘉','男')
insert into Student(StuName,StuSex) values('荀彧','男')
insert into Student(StuName,StuSex) values('贾诩','男')
insert into Student(StuName,StuSex) values('孙权','男')
insert into Student(StuName,StuSex) values('孙坚','男')
insert into Student(StuName,StuSex) values('孙策','男')
insert into Student(StuName,StuSex) values('太史慈','男')
insert into Student(StuName,StuSex) values('大乔','女')
insert into Student(StuName,StuSex) values('小乔','女')
select*from Student

--方案一：top方式分页
declare @PageSize int=5
declare @PageIndex int=3
select top(@PageSize)*from Student
where StuId not in(select top(@PageSize *(@PageIndex-1))StuId from Student)

--方案二：row_number分页
declare @PageSize int=5
declare @PageIndex int=3
 select*from
(select ROW_NUMBER() over(order by StuId) RowId,*from Student)Temp
where RowId between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize

二、事务

人员信息如下：（第二列是身份证号，第三列是银行卡卡号）

刘备 420107198905064135 6225125478544587
关羽 420107199507104133 6225547858741263
张飞 420107199602034138 6225547854125656

（1）假设刘备取款6000，(添加check约束，设置账户余额必须>=0)，要求：使用事务实现，修改余额和添加取款记录两步操作使用事务

alter table BankCard add constraint ck_money check(CardMoney>=0)
begin transaction
declare @myError int=0
update BankCard set CardMoney=CardMoney-6000 where CardNo='6225125478544587'
set @myError =@myError+@@ERROR
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',0,6000,GETDATE())
set @myError =@myError+@@ERROR
if @myError=0
  begin
    commit transaction
	print '取款成功'
  end
else
  begin
    rollback transaction
	print '取款失败'
  end
select*from BankCard
select*from CardExchange

（2）假设刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)；分析步骤有三步（1）张飞添加1000元，（2）刘备扣除1000元，（3）生成转账记录；使用事务解决此问题。

begin transaction
declare @myErr int=0
update BankCard set CardMoney=CardMoney+1000 where CardNo='6225547854125656'
set @myErr =@myErr+@@ERROR
update BankCard set CardMoney=CardMoney-1000 where CardNo='6225125478544587'
set @myErr =@myErr+@@ERROR
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225547854125656','6225125478544587',1000,GETDATE())
set @myErr =@myErr+@@ERROR
if @myErr=0
  begin
    commit transaction
	print '转账成功'
  end
else
  begin
    rollback transaction
	print '转账失败'
  end
select*from BankCard
select*from CardTransfer

三、索引

索引：提高检索查询效率。

SQL SERVER索引类型： 按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“非聚集索引（非聚类索引，非簇集索引）”；

聚集索引：根据数据行的键值在表或视图中的排序存储这些数据行，每个表只有一个聚集索引。聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序(类似字典中的拼音索引)(物理存储顺序)。
非聚集索引：具有独立于数据行的结构，包含非聚集索引键值，且每个键值项都有指向包含该键值的数据行的指针。（类似字典中的偏旁部首索引）（逻辑存储顺序）。

SQL SERVER索引其他分类：

按数据唯一性区分：“唯一索引”，“非唯一索引”；按键列个数区分：“单列索引”，“多列索引”。

创建索引的方式：

通过显式的CREATE INDEX命令
在创建约束时作为隐含的对象主键约束(聚集索引) 唯一约束(唯一索引)

创建索引语法：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])

索引基本示例语法：

create unique nonclustered index index_code on AccountInfo(AccountCode)

==备注==：索引信息存储在系统视图sys.indexes中。

按照指定索引进行查询

select * from AccountInfo with(index=indexAccount) where AccountCode='6225125478544587'

删除索引

drop index index_code on AccountInfo

四、视图

视图：可以理解成虚拟表。

（1）编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。

create view View_Account_Card
as
select  CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额
from AccountInfo
inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
go

（2）如果要进行相应信息的查询，不需要编写复杂的SQL语句，直接使用视图，如下：

select*from View_Account_Card

五、游标

游标：定位到结果集中某一行。

游标分类：（1）静态游标(Static)：在操作游标的时候，数据发生变化，游标中数据不变（2）动态游标(Dynamic)：在操作游标的时候，数据发生变化，游标中数据改变，默认值。（3）键集驱动游标(KeySet)：在操作游标的时候，被标识的列发生改变，游标中数据改变，其他列改变，游标中数据不变。

假设有如下表结构和数据：

create table Member
(
    MemberId int primary key identity(1,1),
    MemberAccount nvarchar(20) unique check(len(MemberAccount) between 6 and 12),
    MemberPwd nvarchar(20),
    MemberNickname nvarchar(20),
    MemberPhone nvarchar(20)
)

insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('liubei','123456','刘备','4659874564')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('guanyu','123456','关羽','42354234124')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangfei','123456','张飞','41253445')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangyun','123456','赵云','75675676547')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('machao','123456','马超','532523523')

创建游标：

--创建游标(Scroll代表滚动游标，不加Scroll则是只进的，只能支持fetch next)
declare mycur cursor scroll
for select MemberAccount from Member

打开游标：

open mycur

提取数据：

fetch first from mycur  --第一行
fetch last from mycur  --最后一行
fetch absolute 2 from mycur  --提取第二行
fetch relative 2 from mycur  --当前行下移2行
fetch next from mycur  --上移一行
fetch prior from mycur  --下移一行

提取数据给变量以供它用（取出第3行用户名，查询该用户详细信息）：

declare @acc varchar(20)
fetch absolute 3 from mycur into @acc
select*from Member where MemberAccount=@acc

利用游标提取所有的账户信息：

--@@FETCH_STATUS：0，提取成功；-1，失败；-2，不存在
declare @acc varchar(20)
fetch absolute 1 from mycur into @acc
while @@FETCH_STATUS=0
  begin
    print'提取成功：'+@acc
	fetch next from mycur into @acc
  end

利用游标修改和删除数据：

fetch absolute 2 from mycur 
update Member set MemberPwd='654321' where current of mycur

fetch absolute 2 from mycur
delete from Member where current of mycur

关闭游标：

close mycur

删除游标：

deallocate mycur

创建游标指向某行多列数据,并循环显示数据：

declare mycur cursor scroll
for select MemberAccount,MemberPwd,MemberNickname from Member
open mycur
declare @acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)
fetch absolute 1 from mycur into @acc,@pwd,@nickname
while @@FETCH_STATUS=0
  begin
    print'用户名：'+@acc+',密码：'+@pwd+',昵称：'+@nickname
	fetch next from mycur into @acc,@pwd,@nickname
  end
close mycur 
deallocate mycur