运算符

这世上所有温柔的事情，都让我想起你。 --zhu
运算符

T-SQL中使用的运算符分为7种
算数运算符：加（+）减（-）乘（*）除（/）模（%）
逻辑运算符：AND, OR, LIKE, BETWEEN, IN, EXISTS, NOT, ALL, ANY
赋值运算符：=
字符串运算符：+
比较运算符：=, >, <, >=, <=, <>
位运算符：|, &, ^
复合运算符：+=, -=, /=, %=, *=

运算符示例
（1）已知长方形的长和宽，求长方形的周长和面积

declare @c int = 5
declare @k int = 10
declare @zc int
declare @mj int
set @zc =(@c+@k)*2
set @mj =@c*@k
print '周长为：'+Convert(varchar(20),@zc)
print '面积为：'+Convert(varchar(20),@mj)

(2)查询银行卡状态为冻结，并且余额超过10万的银行卡信息
select * from BankCard where CardState = 3 and CardMoney > 100000

(3)查询出银行卡状态为冻结或者余额等于0的银行卡信息
select * from BankCard where CardState = 3 or 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）关羽身份证：420101010101014133，关羽到银行开户。查询身份证在账号表是否存在，不存在则进行开户开卡，存在则不开户直接开卡。

declare @AccountId int 
if EXISTS(select * from AccountInfo  where AccountCode = '420101010101014133' --存在此人)
begin
 select @AccountId=
 (select AccountId from AccountInfo where AccountCode = '420101010101014133')
  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('420101010101014133','18219990099','关羽'，getdate())
   set @AccountId = @@IDENTITY
   insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
   values('6225547858741264',@AccountId,'123456',0,1)
 end

--扩展：上面需求添加一个限制即一个人最多只能开3张银行卡

declare @AccountId int --账户编号
declare @CardCount int --卡数量
if EXISTS(select * from AccountInfo  where AccountCode = '420101010101014133' --存在此人)
 begin
  select @AccountId=
  (select AccountId from AccountInfo where AccountCode = '420101010101014133')
   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 '最多只能三张银行卡'
     end
 end

else --不存在此人
 begin
   insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
   values('420101010101014133','18219990099','关羽'，getdate())
   set @AccountId = @@IDENTITY
   insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
   values('6225547858741264',@AccountId,'123456',0,1)
 end

（8）查询银行卡账号余额，是不是所有账户余额都超过了3000

if 3000 < All(select CardMoney from BankCard)
  begin
    print '所有账户余额都超过了3000'
  end
else
  begin
    print '不是所有账户余额都超过了3000'
  end

（9）查询银行卡账户余额，是否含有账户余额超过300000000的信息

if 300000000 < Any(select CardMoney from BankCard)
  begin
    print '有账户余额都超过了300000000'
  end
else
  begin
    print '没有账户余额都超过了300000000'
  end