ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > > SQL SERVER 高级语法之T-SQL

SQL SERVER 高级语法之T-SQL

作者：u_9ff4c058a0e0 时间：2022-05-31 15:22:08 0 删除 编辑

一. 信息打印

1.1 打印的方式

  1. 直接打印消息：
    • 代码格式： 
      --print
复制代码
    • 实战演示： 在这里插入图片描述

      可以看到是打印在窗口中；

  2. 在表格中打印消息，可以设置多列，以及每一列的名字：
    • 代码格式： 
      --select
复制代码
    • 实战演示： 在这里插入图片描述

      结果是打印在表格内的；select还可以用于从表里查询数据；

二. 变量、Go语句和运算符

2.1 T-SQL中变量分为局部变量和全局变量

  • 局部变量：
    • 特征：
      • 以@作为前缀
      • 先声明，再赋值
    • 示例： 
      declare @str varchar(20)
set @str = '我爱数据库编程' --或者select @str = '我爱数据库编程'
print @str
复制代码

      sql中的声明是以declare 开头

    • 注意：set赋值和select赋值的区别？
      • set：赋给变量指定的值
      • select:一般用于从表中查询的数据，如果查询的记录有多条，将最后一条记录的值赋给变量，例如： 
        select @变量名 = 字段名 from 表名
复制代码

      在赋值过程中，如果是表中查询的数据，如果记录只有一条，使用set和select都可以，但是习惯上使用select;

  • 全局变量：
    • 特征：
      • 以@@作为前缀
      • 由系统进行定义和维护，只读
    • 说明： 
      --@@ERROR: 返回执行的上一个语句的错误号
--@@IDENTITY：返回最后插入的标识值
--@@MAX_CONNECTIONS:返回允许同时进行的最大用户连接数
--@@ROWCOUNT:返回受上一语句影响的行数
--@@SERVERNAME：返回运行SQL Server的本地服务器的名称
--@@SERVICENAME：返回SQL Server 正在其下运行的注册表项的名称
--@@TRANCOUNT:返回当前连接的活动事务数
--@@LOCK_TIMEOUT:返回当前会话的当前锁定超时设置（毫秒）
复制代码
    • 示例代码：
      • 为赵云此人进行开-户开卡操作，赵云身份证：420107199904054233: 在这里插入图片描述

        我们先执行了一个insert 操作，当保存了用户信息时，然后我们定义了一个AccountId的变量，它的加载方式是@@INENTITY，就是说获取的前面的insert的操作的主键id。拿到了此主键id，后续就进行了insert BankCard操作，就进行了开-户了。

      • 需要求出张飞的银行卡卡号和余额，张飞身份证：420107199602034138 
        -- 方案一： 连接查询
select CardNo 卡号,CardMoney 余额 from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where AccountCode = '420107199602034138'
-- 方案二: 使用变量
declare @AccountId int
select @AccountId = (select AccountId from AccountInfo where AccountCode = '420107199602034138')
select CardNo 卡号,CardMoney 余额 from BankCard where BankCard.AccountId = @AccountId
复制代码

        在方案二中的查询中，@AccountId 的值的传入是通过后面的select 语句得到的，它是将查询出来的结果传给了AccountId；

2.2 go语句

  • 概述：
    • 等待go语句前的代码执行完成后，再执行go后面的代码
    • 批处理语句的结束标志
  • 代码示例：
    • 等待前面代码执行完成：
      • 在这里插入图片描述

      图中的create database DBTEST1建表操作还没有创建完成时，如果直接`use DBTEST1'则会抛异常报错。所以在这样的业务场景下，我们可以使用go，让之前的代码执行完毕后，再进行执行后续代码。

    • 批处理结束的一个标志：
      • 在这里插入图片描述

      如果在go的上方，则声明的作用范围在上方，如果在两个go之间则就会在两个之间，它能够圈定一个作用范围，在go之后则无法再继续使用此变量了，因为go后面已经批处理结束了。

2.3 运算符

  • T-SQL中使用的运算符分为7种，如图所示： 在这里插入图片描述

2.4 案例实战

  • 实战示例：
    1. 已知长方形的长和宽，求长方形的周长和面积 
      declare @c int =10
declare @k int =5
declare @zc int
declare @mj int
set @zc =(@c + @k )*2
set @mj = @c*@k
print '圆周长:'+ Convert(varchar(10),@zc) 
print '圆面积' + Convert(varchar(10),@mj)
复制代码

      打印结果为： 圆周长：30 圆面积50 ；这里的Convert是将@zc和@mj转成字符串，它的类型为varchar(10) 还可以使用cast函数，能够达到同样的效果，如图所示：在这里插入图片描述 后续再讲他们之间的区别

    2. 查询银行卡状态为冻结，并且余额超过1000000的银行卡信息 
      select * from BankCard where CardState = 3 and CardMoney> 1000000
复制代码

      这里3是冻结状态

    3. 查询银行卡状态为冻结并且余额等于0的银行卡信息 
      select * from BankCard where CardState = 3 and CardMoney =0
复制代码
    4. 查询出姓名中含有“刘”的账户信息以及银行卡信息 
      select * from AccountInfo inner join BankCard on BankCard.AccountId = AccountInfo.AccountId  where RealName like '%刘%'
复制代码
    5. 查询出余额在2000-5000之间的银行卡信息 
      select * from BankCard where CardMoney between 2000 and 5000
复制代码
    6. 查询出银行卡状态为冻结或者注销的银行卡信息 
      select * from BankCard where CardState in(3,4)
复制代码
    7. 关羽身份证：420107199507104133,关羽到银行来开-户，查询身份证在账户表是否存在，不存在则进行开-户开卡，存在则不开-户直接开卡。 
      declare @AccountId int
if EXISTS(select * from AccountInfo where AccountCode = '420107199507104133')   -- 存在此人
	begin
		select @AccountId = (select AccountId from AccountInfo where AccountCode = '420107199507104133') insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState) values('6225547858741264',@AccountId,'123456',0,1)
	end
else -- 不存在
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime) 
		values('420107199507104133','13656565656','关羽',getdate())
		set @AccountId = @@IDENTITY
		insert into BankCard (CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225547858741264',@AccountId,'123456',0,1)
	end
复制代码
    8. 扩展步骤7： 上面需求添加一个限制，即一个人最多只能开三张银行卡： 
      declare @AccountId int  -- 账户编号
declare @CardCount int	-- 卡数量
if EXISTS(select * from AccountInfo where AccountCode = '420107199507104133')   -- 存在此人
	begin
		select @AccountId = (select AccountId from AccountInfo where AccountCode = '420107199507104133') 
		select @CardCount = (select count(*) from BankCard where AccountId = @AccountId)
		if @CardCount <=2
			begin
				insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState) values('6225547858741264',@AccountId,'123456',0,1)
			end
		else
			begin
				print '您名下的银行卡太多了，最多只能由3张银行卡'
			end
	end
else -- 不存在
	begin
		insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime) 
		values('420107199507104133','13656565656','关羽',getdate())
		set @AccountId = @@IDENTITY
		insert into BankCard (CardNo,AccountId,CardPwd,CardMoney,CardState)
		values('6225547858741264',@AccountId,'123456',0,1)
	end
复制代码
    9. 查询银行卡账户余额，是不是所有的账户余额都超过了3000 
      if 3000 < All(select CardMoney from BankCard)
	begin
		print '所有的银行卡余额都超过了3000'
	end
else
	begin
		print '不是所有的银行卡余额都超过了3000'
	end
复制代码
    10. 查询银行卡账户余额，是否含有账户余额超过30000000的信息 
      if 30000000 < Any(select CardMoney from BankCard)
	begin
		print '有银行卡余额超过了3000'
	end
else
	begin
		print '没有银行卡余额超过了3000'
	end
复制代码

三. 流程控制

3.1 选择分支结构

  • 样式为： 
    if xxx
	begin
		xxx
	end
else
	begin
		xxx
	end
复制代码
  • 案例演示：
    1. 某用户银行卡号为：6225547854125656,该用户执行取钱操作，取钱5000元，余额充足则进行取钱操作，并提示‘取钱成功’，否则提示‘余额不足’ 
      declare @banlance money
select @balance = (select CardMoney from BankCard where CardNo = '6225547854125656')
if @balance >= 5000
	begin
		update BankCard set CardMoney = CardMoney -5000
		insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
		values('6225547854125656',0,5000,GETDATE())
	end
else
	print '余额不足'
复制代码
    2. 查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字，“正常，挂失，冻结，注销”，并且根据银行卡余额显示银行卡等级，30万以下为“普通用户”，30万及以上为“VIP用户”，显示列分别为卡号，身份证，姓名，余额，用户等级，银行卡状态。 
      select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额,
case
	when CardMoney >= 300000 then 'VIP用户'
	else '普通用户'
end 用户等级
case CardState
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
复制代码
    3. 循环结构（while）
      • 循环打印1-10 
        declare @int =1
while @i <=10
begin 
	print @i
	set @i = @i +1
end
复制代码
      • 循环打印九九乘法表 
        declare @i int =1
while @i < 9
	begin
		declare @str varchar(1000) = ''
		declare @j int =1
		while @j <=@i
			begin
				set @str = @str + cast(@i as varchar(1)) + '*' + cast(@i as varchar(1)) + '=' + cast(@i*@j as varchar(2)) + char(9)
				set @j = @j+1
			end
		set @i = @i +1
	end
复制代码

特殊字符：char(9):制表符； char(10):换行符，print是自动换行

四. 子查询

4.1 关羽的银行卡号为“6225547858741263”，查询出余额比关于多的银行卡信息，显示卡号，身份证，姓名，余额。

  • 方案一： 
    declare @gyBalance money
select @gyBalance = (select CardMoney from BankCard where CardNo = '6225547858741263')
select CardNo 卡号, AccountCode 身份证, RealName 姓名, CardMoney 余额 from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId 
where CardMoney > @gyBalance
复制代码
  • 方案二： 
    select CardNo 卡号,AccountCode 身份证, RealName 姓名,CardMoney 余额 from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardMoney > (select CardMoney from BankCard where CardNo = '6225547858741263')
复制代码

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

  • 方案一： 
    select * from CardExchange where CardNo in (select CardNo from BankCard where CardMoney = (select MAX(CardMoney) from BankCard))
复制代码
  • 方案二： 
    select * from CardExchange where CardNo = (select top 1 CardNo from BankCard order by CardMoney desc)
复制代码

4.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.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 )
复制代码

4.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
复制代码

4.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 Temp.myCount desc
复制代码

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

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

五. 分页

5.1 方案一,top方式

declare @PageSize int = 5
declare @PageIndex int = 1
select top(@PageSize) * from Student where StuId not in (select top(@PageSize*(@PageIndex-1)) StuId from Student)
复制代码

当数据量大的时候，查询的效率并不高

5.2 方案二，使用rowNumber分页

declare @PageSize int = 4
declare @PageIndex int = 2
select * from (select ROW_NUMBER() over(order by StuId) RowId , * from Student) Temp
where RowId between(@PageIndex-1)* @PageSize+1 and @PageIndex * @PageSize
复制代码

六. 事务

6.1 假设刘备取款6000，（添加Check约束，设置账户余额必须>=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,5000,GETDATE())
set @myError = @myError + @@ERROR
if @myError =0
	begin
		commit transaction
		print '取款成功'
	end
else 
	begin
		rollback transaction
		print '取款失败'
	end
复制代码

@@ERROR 在异常时会返回一个错误int值，我们将这个错误值放入@myError，如果它的最终值大于0说明存在异常，则进行回滚，如果等于0 说明执行成功，则直接提交事务；

6.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('6225125478544587','6225547854125656',1000,GETDATE()) set @myerr = @myerr + @@ERROR
if @myerr = 0
	begin
		commit transaction
		print '转账成功'
	end
else 
	begin
		rollback transaction
		print '取款失败'
	end
复制代码

七. 索引

7.1 概述

  • 索引： 提高检索查询效率。
  • SQL SERVER 索引类型：
    • 按存储结构分：“聚集索引（又称聚类索引，簇集索引）”，“非聚集索引（非聚类索引，非簇集索引）”；
      • 聚集索引：根据数据行的键值在表或视图中的排序存储这些数据行，每个表只有一个聚集索引。聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序（类似字典中的拼音索引）（物理存储顺序）
      • 非聚集索引：具有独立于数据行的结构，包含非聚集索引键值，且每个键值想都指向包含改键值的数据行的指针。（类似字典中的偏旁部首索引）（逻辑存储顺序）。
    • 按照数据唯一性分：
      • 唯一索引
      • 非唯一索引
    • 按键列个数区分：
      • 单列索引
      • 多列索引
    • 创建索引的方式：
      1. 通过显式的CREATE INDEX 命令
      2. 在创建约束时作为隐含的对象
        1. 主键约束（聚集索引）
        2. 唯一约束（唯一索引）
    • 创建索引语法： 
      CREATE [UNIQUE] [CLUSTERED | NONCLUSTE] 
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])
复制代码
    • 索引基本示例语法： 
      -- exp:创建一个非聚集索引
create nonclustered index indexAccount on AccountInfo(AccountCode)
-- 删除一个索引
drop index indexAccount on AccountInfo
复制代码

7.2 索引代码示例

  1. 给AccountInfo表中的AccountCode字段添加索引 
    create unique nonclustered index index_code on AccountInfo(AccountCode)
复制代码
  2. 索引查看（sys.indexes） 
    select * from sys.indexes where name = 'index_code'
复制代码
  3. 删除索引 
    drop index index_code on AccountInfo
复制代码
  4. 显示指定索引进行查询 
    select * from AccountInfo with(index = index_code) where AccountCode = '420107199507104133'
复制代码

八. 视图

8.1 实战演示

  • 视图，可以理解为虚拟表
  • 代码演示：
    1. 编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额 
      create view CardAndAccount as select CardNo 卡号, AccountCode 身份证, RealName 姓名,CardMoney 余额 from BankCard left join AccountInfo on BankCard.AccountId= AccountInfo.AccountId
go
复制代码
    2. 如果要进行相应的信息查询，不需要编写复杂的SQL语句，直接使用视图，如下： 
      select * from CardAndAccount
复制代码
    3. 删除视图 
      drop view View_Account_Card
复制代码

九. 游标

9.1 概述

  • 游标：定位到结果集中某一行
  • 游标分类：
    1. 静态游标（Static）：在操作游标的时候，数据发生变化，游标中数据不变。
    2. 动态游标（Dynamic）:在操作游标的时候，数据发生变化，游标中数据改变，默认值。
    3. 键集驱动游标（KeySet）：在操作游标的时候，被标识的列发生改变，游标中数据改变，其他列改变，游标中数据不变。

9.2 实战代码示例

-- 创建游标（scroll：滚动游标，没有scroll,只进）
declare mycur cursor scroll for select MemberAccount from Member  # mycur是游标名称， cursor标识它是一个游标，scroll是类型表示它是一个滚动游标 MemberAccount表示游标的值为Member表中的MemberAccount字段。
-- 游标打开
open mycur

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

-- 提取游标数据存入变量，进行查询所有列数据
declare @acc varchar(20)
fetch absolute 2 from mycur into @acc		# 绝对游标第二行数据存入@acc变量，然后下方再进行查询
select * from Member where MemberAccount = @acc

-- 遍历游标
declare @acc varchar(20)
fetch absolute 1 from mycur into @acc
-- @@fetch_status: 0提取成功， -1 表示失败， -2 不存在
while @@fetch_status = 0
	begin
		print '提取成功' + @acc
		fetch next from mycur into @acc
	end
-- 上面的遍历是while进行持续判端，然后begin方法中一直下移游标，然后实现了遍历操作；


-- 利用游标进行数据的修改和删除
select * from Member 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   # 其中MemberAccount, MemberPwd,MemberNickName 均为Member表中字段，我们使用时根据实际来确定；

-- 上面定义好游标后，我们下面开始进行遍历操作：
declare @acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)

fetch absolute 1 from mycur into @acc, @pwd, @nickname
-- @@fetch_status: 0 提取成功 ， -1 失败 ， -2 不存在
while @@fetch_status = 0 
	begin 
		print '用户名:'+@acc + ',密码：'+@pwd +',昵称：'+@nickname fetch next from mycur into @acc, @pwd,@nickname
	end

-- 关闭游标
close mycur

-- 删除游标
deallocate mycur

复制代码

游标关闭后可以再打开，但是删除后如果需要再使用则必须重新建了。游标类似于指向数据的指针，所以灵活性非常高。

十. 函数

10.1 概述

  • 函数的分类：
    1. 系统函数
    2. 自定义函数
      1. 标量值函数（返回单个值）
      2. 表值函数（返回查询结果）

10.2 函数实战代码演示

  1. 编写一个函数求该银行的金额总和（没有参数，返回标量值）： 
    drop function GetSumMoney
create function GetSumMoney() returns money
as 
begin
	declare @sum money
	select @sum= (select SUM(CardMoney) from BankCard)
	return @sum
end

-- 函数调用
select dbo.GetSumMoney()
复制代码

    上面函数没有参数，下面介绍有参数函数的定义及使用

  2. 传入账户编号，返回账户真实姓名 
    create function GetRealNameById(@accid int) returns varcahr(30) 
as 
begin
	declare @name varchar(30)
	select @name = (select RealName from AccountInfo where AccountId = @accid) return @name
end

select dbo.GetRealNameById(2)
复制代码
  3. 传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含真实姓名，卡号，存钱金额，取钱金额，交易时间
    • 方案一：（复杂查询使用此方案，除了查询外还有其他逻辑方法） 
      create function GetRecordByTime(@start varcahr(30),@end varchar(30)) returns @result table (
	RealName varchar(20), --真实姓名
	CardNo varchar(30),  -- 银行卡号（与银行卡表形成主外键关系）
	MoneyInBank money,  -- 存钱金额
	MoneyOutBank money,   -- 取钱金额
	ExchangeTime smalldatetime  -- 交易时间
)
as
begin
	insert into @result
	select RealName 姓名,CardExchange.CardNo 卡号, MoneyInBank 存钱金额, MoneyOutBank 取钱金额,ExchangeTime 交易时间 from CardExchange
	inner join BankCard on CardExchange.CardNo = BankCard.CardNo 
	inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where ExchangeTime between @start+ ' 00:00:00' and @end + ' 23:59:59'
	return
end

-- 函数调用使用
select * from GetRecordByTime('2020-01-01', '2020-12-12')
复制代码
    • 方案二：（函数体内只能由 return + sql 查询结果） 
      drop function GetRecordByTime
create function GetRecordByTime(@start varcahr(30),@end varchar(30))
return table
as 
	return 
	select RealName 姓名,CardExchange.CardNo 卡号, MoneyInBank 存钱金额, MoneyOutBank 取钱金额,ExchangeTime 交易时间 from CardExchange
	inner join BankCard on CardExchange.CardNo = BankCard.CardNo 
	inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where ExchangeTime between @start+ ' 00:00:00' and @end + ' 23:59:59'
go

-- 函数调用使用
select * from GetRecordByTime('2020-01-01', '2020-12-12')
复制代码
  4. 查询银行卡信息，将银行卡状态1,2,3,4 分别转换成汉字“正常，挂失，冻结，注销”，根据银行卡余额显示银行卡等级30w以下为普通用户，30万以上为VIP用户，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。
    • 普通查询： 
      select CardNo 卡号,AccountCode 身份证,RealName 姓名, CardMoney 余额, 
case 
	when CardMoney < 300000 then '普通用户'
	else 'VIP用户'
end 用户等级,
case
	CardState
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end 卡状态
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
复制代码
    • 使用函数的方式查询： 
      -- 创建用户等级函数
create function GetGrade(@cardmoney money) returns varchar(30) 
as 
begin
	declare @result varchar(30)
	if @cardmoney >= 300000
		set @result = 'VIP用户'
	else 
		set @result = '普通用户'
	return @result
end

-- 求银行卡状态的函数：
create function GetState(@state int) returns varchar(30)
as
begin
	declare @result varchar(30)
	if @state =1
		set @result = '正常'
	else if @state = 2
		set @result = '挂失'
	else if @state = 3
		set @result = '冻结'
	else if @state = 4
		set @result = '注销'
	else
		set @result = '异常'
	return @result
end

-- 查询的时候直接使用函数，查询语句如下	
select CardNo 卡号, AccountCode 身份证, RealName 姓名, CardMoney 余额, dbo.GetGrade(CardMoney) 用户等级, dbo.GetState(CardState) 银行卡状态 from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
复制代码
  5. 编写函数，根据出生日期求年龄，年龄为实岁，例如：（1）生日为2000-5-5，当前为2018-5-4，年龄为17岁。 （2）生日为2000-5-5，当前为2018-5-6，年龄为18岁 
    -- 方案一，直接查询：
select *,year(GETDATE()) - year(empBirth) 年龄 from Emp
-- 方案二,使用函数查询：
create function GetAge(@birth smalldatetime) returns int
as 
begin
	declare @age int
	set @age = year(GETDATE()) -year(@birth)
	if month(getdate()) < month(@birth)
		set @age = @age -1
	if month(getdate()) = month(@birth) and day(getdate()) <day(@birth)
		set @age = @age -1
	return @age
end

select *,dbo.GetAge(empBirth) 年龄 from Emp
复制代码

    使用函数能够更加精确，也更加灵活，同时可以多处复用，我们可以在需要以上优势的地方使用它；

十一. 触发器

11.1 概述

  • 触发器概念：触发器是一种特殊类型的存储过程，它不同于我们下一节要介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。

  • 什么是触发器？触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server2005 中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。

  • 触发器分类：

    • after触发器（之后触发）
      1. insert触发器
      2. update触发器
      3. delete触发器
    • instead of 触发器（之前触发）

    两者区别在于after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发,且只能定义在表上。而instead of 触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。既可以在表上定义instead of 触发器，也可以在视图上定义。

  • 触发器的表：触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建两张表，不会存储在数据库中。而且两张表都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改动触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。 在这里插入图片描述

11.2 代码实战：

  1. 假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为“新部门” 
    create trigger tri_InsertPeople on People after insert
as
	if exists(select * from Department where DepartmentId = (select DepartmentId from inserted))
		begin
			insert into Department(DepartmentId, DepartmentName)
			values((select DepartmentId from inseted),'新部门')
		end
	go
	-- 测试触发器
	insert into People(DepartmentId, PeopleName, PeopleSex,PeoplePhone) values('003','赵云','男','13698547125')
	insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone) values('002','张飞','男','13698547125')
复制代码
  2. 触发器实现，删除一个部门的时候将部门下所有员工全部删除 
    create trigger tri_DeleteDept on Department after delete
as 
	delete from People where DepartmentId = (select DepartmentId from deleted)
go
select * from Department
select * from People
-- 测试触发器
delete from Department where DepartmentId = '006'
复制代码

    相当于实现了一个级联删除的操作；

  3. 创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除。 
    drop trigger tri_DeleteDept
create trigger tri_DeleteDept on Department after delete
as 
	if not exists(select * from People where DepartmentId = (select DepartmentId from deleted)) 
		delete from Department where DepartmentId = (select DepartmentId from deleted)
go

-- 测试触发器
delete from Department where DepartmentId = '001'
复制代码
  4. 修改一个部门编号之后,将该部门下所有员工的部门编号同步进行修改 
    create trigger tri_UpdateDept on Department after update
as
	update People set DepartmentId = (select DepartmentId from inserted)
	where DepartmentId = (select DepartmentId from deleted)
go

update Department set DepartmentId = '005' where DepartmentId = '001'
复制代码

注意：使用触发器需要注意一点，一个功能点根据A删除触发B删除、另外一个功能点根据B删除触发C删除、以此类推，本意操作A只想连带删除B，但可能导致其他的触发器也同时生效，导致全部均被删除；

十二. 存储过程

12.1 概述

  • 概述：
    • Transact-SQL的存储过程，非常类似于JAVA语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存，这样下次执行的时候直接使用缓存中的语句，这样就可以提高存储过程的性能。
  • 概念：
    1. 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
    2. 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
    3. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需要提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量，简单网络负担。
  • 优点：
    1. 存储过程允许标准组件式编程：
      • 存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大地提高了程序的可移植性。
    2. 存储过程能够实现较快的执行速度：
      • 如果某一个操作包含大量的T-SQL 语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，而批处理的T-SQL语句每次运行都要预编译和优化，所以速度就要慢一些。
    3. 存储过程减轻网络流量
      • 对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句，从而减轻了网络流量，降低了网络负载。
    4. 存储过程可被作为一种安全机制来充分利用：
      • 系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

    函数一般是在sql中引用，而存储过程是可以直接被外部调用，比如java或者C# 语言可以直接调用存储过程语句；

  • 常用系统存储过程有： 
    exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
复制代码

12.2 案例演示

  • 系统存储过程示例： 
    --表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
复制代码
  • 实战：用户自定义存储过程：
    1. 创建语法： 
      create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
 {@参数数据类型} [=默认值] [output],
 ...
]
as
SQL_statement
复制代码
    2. 没有输入参数，没有输出参数的存储过程； 
      -- 定义存储过程实现查询出账户余额最低的银行卡账户信息，显示银行卡号，姓名，账户余额
drop proc proc_MinMoneyCard
create proc proc_MinMoneyCard
as 
	select top 1 CardNo,RealName,CardMoney from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
	order by CardMoney
go
exec proc_MinMoneyCard
select * from BankCard
-- 方案二：(余额最低，多个人并列，都可以查出来)
create proc proc_MinMoneyCard
as 
	select CardNo, RealName,CardMoney from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardMoney = (select min(CardMoney) from BankCard)
go
exec proc_MinMoneyCard
复制代码
    3. 有输入参数，没有输出参数的存储过程 
      --模拟银行卡取钱操作，传入银行卡号，存钱金额，实现存钱操作
create proc proc_Cunqian
@CardNo varchar(30)
@money money
as 
	update BankCard set CardMoney = CardMoney + @money where CardNo = @CardNo
	insert into CardExchange(CardNo, MoneyInBank,MoneyOutBank,ExchangeTime)
	values(@CardNo,@money,0,getdate())
go
select * from BankCard
select * from CardExchange
exec proc_Cunqian '6225547858741263',1000
复制代码
    4. 有输入参数，没有输出参数，但是有返回值的存储过程（返回值必须整数） 
      -- 模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，
-- 取钱成功，返回1， 取钱失败返回-1
create proc proc_Quqian
	@CardNo varchar(30),
	@money money
as
	update BankCard set CardMoney = CardMoney - @money
	where CardNo = @CardNo
	if @@ERROR <> 0
		return -1
	insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values(@CardNo,0,@money,getdate())
	return 1
	go

	declare @returnValue int
	exec @returnValue = proc_Quqian '6225125478544587',100
	select @returnValue
	~~~ 	
复制代码
    5. 有输入参数，有输出参数的存储过程 
      -- 查询出某时间段的银行存取款信息以及存款总金额，取款总金额，
-- 传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。
create proc proc_selectExchange
	@start varchar(20),   --开始时间
	@end varchar(20),	--结束时间
	@sumIn money output, --存款总金额
	@sumOut money output  -- 取款总金额
as
	select @sumIn= (select sum (MoneyInBank) from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
	select @sumOut = (select sum(MoneyOutBank) from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
	select * from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59'
go
declare @sumIn money
declare @sumOut money
exec proc_selectExChange '2020-1-1','2020-12-11',@sumIn output,@sumOut output
select @sumIn
select @sumOut
复制代码
    6. 具有同时输入输出参数的存储过程 
      -- 密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8 ，自动升级成8位密码
select * from BankCard
select floor(rand()*10)

create proc procPwdUpgrade
	@CardNo nvarchar(20),  --卡号
	@pwd nvarchar(20) output --卡号
as
	if not exists(select * from BankCard where CardNo = @CardNo and CardPwd = @pwd)
		set @pwd = ''
	else
		begin
			if len(@pwd) < 8
			begin
				declare @len int = 8 - len(@pwd)
				declare @i int = 1
				while @i <= @len
					begin
						set @pwd = @pwd + cast(floor(rand() * 10) as varchar(1))
						set @i = @i+1
					end
				update BankCard set CardPwd = @pwd where CardNo = @CardNo
		end 
go
declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade '6225125478544587',@pwd output
select @pwd
复制代码

      @pwd nvarchar(20) output 说明它既可以当做输入也可以作为输出；

看完啦~ 博主码字不易，觉得有用可以收藏点赞，感谢您的观看，我们一起加油ヾ(◍°∇°◍)ﾉﾞ


作者：暗余
链接：https://juejin.cn/post/7008551261863673864

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69999831/viewspace-3150945/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： TDengine集成EMQX：通过规则引擎实现设备数据直接入库
下一篇： 分布式事务数据库HotDB 备份还原工具
请登录后发表评论 登录
全部评论
u_9ff4c058a0e0

注册时间：2022-02-11

  • 博文量
    1828
  • 访问量
    20598

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593