12.运算符

运算符

运算符分为7种分别是

算数运算符:  + - * / %
逻辑运算符:  and or like between in exists not all any
赋值运算符:  =
字符串运算符: +
比较运算符:  = > < <= >= <>
位运算符:  | & ^
复合运算符:  +=  -=  /=  %= *=

示例：

--已知长方形的长和宽，求长方形的周长和面积
declare @x int = 10;
declare @y int = 5;
declare @perimeter int;
declare @area int;
set @perimeter = 2*(@x+@y);
set @area = @x*@y;
--第一种
print '圆周长:' + convert(varchar(10),@perimeter);
print '圆面积:' + convert(varchar(10),@area);
--第二种
print '圆周长:' + cast(@perimeter as varchar(10));
print '圆面积:' + cast(@area as varchar(10));

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

--查询出姓名种含有‘刘’的账户信息以及银行卡信息
select*from AccountInfo 
inner join BankCard 
on AccountInfo.AccountId = BankCard.AccountId 
where RealName like '%刘%'; 

--查询余额在2000-5000之间的银行卡信息
select*from BankCard 
where CardMoney between 2000 and 5000;

--查询出银行卡状态为冻结或者注销的银行卡信息
select*from BankCard 
where CardState in (3,4);

--关羽身份证：4206398768631，关羽到银行来开户
--查询身份证在账户表是否存在，不存在则进行开户开卡，存在则不开户直接开卡
declare @AccountId int;
if exists(select*from AccountInfo where AccountCode='4206398768631')
 begin
	set @AccountId = (select AccountId from AccountInfo
	where AccountCode = '4206398768631' )
	insert into BankCard(CardId,AccountId,CardPwd,CardMoney,CardState)
	values('6237442395726163',@AccountId,'12345',0,1);
 end
else 
 begin
	insert into AccountInfo(AccountCode,RealName,AccountPhone)
	values('4206398768631','关羽','12287497603');
	set @AccountId = @@IDENTITY;
	insert into BankCard(CardId,AccountId,CardPwd,CardMoney,CardState)
	values('6237442395726163',@AccountId,'12345',0,1);
 end
--扩展：一个人最多只能有3张卡	
declare @AccountId int;
declare @CardCount int;
if exists(select*from AccountInfo where AccountCode='4206398768631')
 begin
	set @AccountId = (select AccountId from AccountInfo
	where AccountCode = '4206398768631' )
	set @CardCount = 
	(select (count(*)) from BankCard where AccountId = @AccountId);
	if @CardCount<=2
	 begin
		insert into BankCard(CardId,AccountId,CardPwd,CardMoney,CardState)
		values('6237442395726163',@AccountId,'12345',0,1);
	 end
	else 
	 begin
		print '您名下的银行卡太多了，最多有3张卡'
	 end
 end
else 
 begin
	insert into AccountInfo(AccountCode,RealName,AccountPhone)
	values('4206398768631','关羽','12287497603');
	set @AccountId = @@IDENTITY;
	insert into BankCard(CardId,AccountId,CardPwd,CardMoney,CardState)
	values('6237442395726163',@AccountId,'12345',0,1);
 end

--查询银行卡账户卡余额，是不是所有账户余额都超过3000
if 3000 > all(select CardMoney from BankCard)
 begin
  print '<'
 end
else 
 begin
 print '>'
 end

--查询银行卡账户余额，是否含有账户余额超过30000000的信息
if 30000000 < any(select CardMoney from BankCard)
 begin
  print '>'
 end
else 
 begin
 print '<'
 end