1.在T-SQL中用declare定义变量,
用@@标示全局变量,
用@标示局部变量,
--单行注释,
/* */多行注释
begin......end 表示一个代码块
2.
- (1)定义变量:declare @变量名 变量数据类型
- 例如:declare @count int, @coun2 int
- (2)变量赋值:set select
- 例如:set @count = 1 赋值只能一种
- select @count = 1 赋值一般配合查询
- select @name = stuname from student where stuno = 'S25301'
- (3)变量再加 go 关键字以前,存储过程结束以前,变量都是起作用的
- (4)常用的全局变量:@@error 返回执行上一个T-SQL语句的错误代码
- @@identity 返回上回插入的标示值
- @@Rowcount 返回受上一语句影响的行数
- (5)输出 print 字符串 变量 函数 字符串表达式
- 注意:print 以文字字符串输出
- select 一网格形式输出
3.
- (1)流程控制语句:
- 1. if-else
- if(判断条件)
- begin
- 语句1
- 语句2
- end
- else
- begin
- 语句1
- 语句2
- end
- if-else的例题:declare @pcid int , @pcuse int
- set @pcuse
- select @pcuse = pcinfo .pcuse from pcinfo where pcid= @pcid
- if(@pcuse = 0)
- print convert(varchar,@pcid ) + '台机器处于空闲'
- else
- print convert(varchar,@pcid) + '台机器处于忙碌'
- (2) case的简单格式
- 语法:
- case 测试表达式
- when 表达式 then 结果表达式1
- when 表达式 then 结果表达式2
- [....]
- else 结果表达式n
- end
- 例题:
- select pcid
- pcuse (对pcuse的分支,如下例题1)
- ,pcnote from pcinfo
- 例题1.
- select pcid
- case pcuse
- when 0 then '空闲'
- when 1 then '忙碌'
- end
- ,pcnote from pcinfo
- (3)case的搜索格式:
- 语法:
- case
- when 布尔表达式 then 结果表达式1
- when 表达式 then 结果表达式2
- [....]
- else 结果表达式n
- end
- 例题:select Sno as '学号',Sname as '姓名',
- case
- when Smark >=90 then '优秀'
- when Smark >=80 then '良好'
- when Smark >=70 then '中等'
- when Smark >=60 then '及格'
- when Smark >=0 then '不及格'
- end as '成绩等级'
- from Score
- go
- (4) while语句
- 语法:
- while(条件)
- begin
- 语句1
- [break]
- 语句2
- [contiune]
- end
- go
- 例题:--从办卡之日起已有一年的所有会员,赠送50元上网费
- update cardinfo set CardBalance = CardBlance + 50
- where datediff(year ,transacttime ,getdate())=1
- --从办卡之日起不足一年的所有会员,赠送10元上网费
- update cardinfo set CardBalance = CardBlance + 10
- where datediff(year ,transacttime ,getdate())<1
- --定义变量@count,表示会员卡余额少于20元的会员个数
- declare @count int
- while (1+1) --1=1永远成立
- begin
- --为@count赋值,查询会员卡余额少于20元的会员个数
- select @count = count(*) from cardinfo where CardBalance <20
- --若还有会员卡余额少于20的
- if(@count >0)
- --为每张不足20元的会员卡增加1 元余额
- update cardinfo set CardBalance = CardBalance + 1
- where CardBalance <20
- else
- break
- end
- go
4.关于子查询
- (1)常用子查询:
- a.使用比较运算符(=、<>、>、>=、!>、!<、或者<=)的子查询
- 例题:
- select * from recordinfo
- where datediff(month,endtime,getdate())=0
- and CardId = (select CardId from cardinfo where CardNumber='023-002')
- go
- b.in 和 not in 子查询(引入的子查询结果是包含0个值或多个值得列 表,子查询返回结果之后,外部查询将利用这些结果)
- 例题:
- --查询2009-6-16的上网记录
- select * from recordinfo
- where BeginTime <'2009-06-17 00:00:00'
- and EndTime >= '2009-06-16 00:00:00'
- go
- 使用in子查询
- 例题:select * from cardinfo
- where cardid in (select cardid from recordinfo where BeginTime <'2009-06-17 00:00:00'
- and EndTime >= '2009-06-16 00:00:00')
- go
- c. exists 和 not exists子查询
- exists 的语法: while [not] exists(子查询)
- 例题:
- select * from recordinfo
- where exists(
- select *from cardinfo
- where cardid = recordinfo.cardid and cardnumber = '002-003')
- go
- (2)子查询出现在where关键字之后
- 例题:
- select cardnumber ,cardpassword,cardbalance,transacttime,pcid,begintime,endtime,fee
- from recordinfo r inner join cardinfo c
- on c.cardid = r.cardid
- (3)子查询出现在from关键字之后
- 例题:
- select cardnumber as '会员号',sum (fee) as '消费'
- from (select cardnumber ,cardpassword,cardbalance,transacttime,pcid,begintime,endtime,fee
- from recordinfo r inner join cardinfo c
- on c.cadid = r.cardid ) as temp_table
- group by cardnumber
- go
- (4)子查询出现在select关键字之后
- 例题:
- --查找会员卡信息表余额的最大值
- select (select max(cardbalance) from cardinfo) as '最高余额'