第三章SQL编程

本章目标:

1.使用变量

2.输出语句

3.数据类型转换

4.逻辑控制语句

5.批处理

一.变量

1.什么是变量呢?

     变量是存储数据的容器

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

2.局部变量

局部变量的名称必须以标记@作为前缀

声明局部变量的语句如下：

declare @variable name(局部变量名称) DataType(数据类型)

局部变量赋值有两种方法：使用Set语句或Select语句

整型：

1 2 3

declare @num int set @num=1 print 'num的值是：'+convert(nvarchar(32),@num)

字符串类型：(char  varchar  nvarchar)

1 2 3

declare @name nvarchar(32) set @name='张三' print @name

小数类型(float decimal  numeric(18,0))

1 2 3

declare @num decimal(18,2) set @num=5.2 print @num

日期类型(datetime))

1 2 3

declare @date datetime set @date=GETDATE() print convert(nvarchar(32),@date,120)

 3.全局变量

全局变量都使用两个@符号为后缀

@@error（重点）	最后一个T-SQL错误的错误号
@@identity	最后一次插入的标识值
@@rowcount	受上一个SQL语句影响的行数
@@serviceName	该计算机上的SQL服务名称
@@Version	SQLServer的版本信息

4.SET语句和SELECT语句的区别

                        set语句和select的语句的区别

	Set	Select
同时对多个变量赋值	不支持	支持
表达式返回多个值时	出错	将返回的最后一个值赋给变量
表达式未返回值时	变量将赋值为NULL	变量保持原值

其中,set赋值语句一般用于赋给变量指定的数据常量,select赋值语句一般用于从表中查询数据,再赋给变量

use MySchool
--学号是23270李小龙姓名和年龄
select studentName as 姓名,datediff(YY,birthday,GETDATE())  as 年龄 from Student where StudentNo=23270

declare @age int 
select @age=datediff(YY,birthday,GETDATE()) 
from Student
where StudentNo=23270 
print @age
--比李小龙年龄小的人的姓名和年龄
select studentName as 姓名,datediff(YY,birthday,GETDATE())  as 年龄 from Student
where datediff(yy,birthday,Getdate())<@age

--比李小龙年龄大的人的姓名和年龄
select studentName as 姓名,datediff(YY,birthday,GETDATE())  as 年龄 from Student
where DATEDIFF(YY,birthday,GETDATE())>@age

--比李小龙年龄小一岁的人的信息
select * from Student
where DATEDIFF(YY,birthday,GETDATE())=@age-1

--比李小龙年龄大一岁的人的信息
select * from Student
where DATEDIFF(YY,birthday,GETDATE())=@age+1

二.输出语句

常用的输出语句有两种,即print语句和select语句.

语法:

print 局部变量或字符串

select 局部变量 as 自定义列名

其中,使用select语句输出数据是查询语句的特殊应用

用print语句输出的结果将在"消息"窗口中以文本方式显示,用select语句输出的结果将在结果窗口中以表格方式显示.

三.数据类型转换

cast()和convert()函数

cast(表达式 as  数据类型)

convert(数据类型[(长度)],表达式[,样式])

二者在本质上无任何区别 唯一不同之处是：在将日期时间类型的数据转换为字符串数据时，convert()函数可以通过第三个参数指定转换后字符数据的显示格式不同。

四.逻辑控制语句

在T-SQL中,常用的逻辑控制语句有以下几种

1.顺序结构控制语句

begin

   语句或语句块

end

begin-end语句的作用类似于C#语言的"{}",它经常在分支结构语句中出现,表示语句块的开始和结束.

2.if-else条件语句

if(条件)

  语句或语句块1

else

语句或语句块2

示例:

declare @age int 
select @age=datediff(YY,birthday,GETDATE()) 
from Student
where StudentNo=23270
if(@age>=18)
begin
print '黄荣18了'
end
else
begin
print '小于18'
end

练习

--if练习
--统计并显示2013-08-09 的oop考试平均分
--如果平均分在70以上，显示“考试成绩优秀”，并显示前三名学生的考试信息
--如果在70分以下，显示“考试成绩较差”，并显示后三名学生的考试信息
--01.定义一个变量，保存平均分
--如果在70分以下，显示“考试成绩较差”，并显示后三名学生的考试信息
--01.定义一个变量，保存平均分

declare @avg int
select @avg=AVG(StudentResult) from Result,Subject
where Result.SubjectID=Subject.SubjectID
and ExamDate>='2013-08-09'
and ExamDate>='2013-08-10'
and Subject.SubjectName='oop'
--02.判定：>=70  显示优秀，同时显示分数最高的三个人的分数
if(@avg>=70)
begin
print '优秀'
select top 3  * from Result,Subject
where Result.SubjectID=Subject.SubjectID
and ExamDate>='2013-08-09'
and ExamDate>='2013-08-10'
and Subject.SubjectName='oop'
order by StudentResult desc
end
--02.判定：<70  显示差，同时显示分数最低的三个人的分数
else 
begin
print '差'
select top 3  * from Result,Subject
where Result.SubjectID=Subject.Subjectid
and ExamDate>='2013-08-09'
and ExamDate<'2013-08-10'
and Subject.SubjectName='oop'
order by StudentResult asc
end

3.while循环语句

在while循环语句中可以使用continue和break语句来控制语句的执行

while(条件)

  begin

  语句和语句块

 [break | continue]

end

示例:

检查学生“oop”课最近一次考试是否有不及格（60分及格）的学生。

如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格

--求符合条件的人数
declare @subid int
select @subid=subjectid
from Subject
where SubjectName='oop'
--定义一个Datetime类型的变量，保存最近一次考试时间
declare @maxdate datetime
select @maxdate=MAX(Examdate)
from Result
where SubjectId=@subid
--print Convert(nvarchar(32),@maxdate,120)

declare @n int
select @n=COUNT(*) from Result
where SubjectId=@subid
and ExamDate=@maxdate
and StudentResult<70
--判定人数>0

--循环
while(@n>0)
begin
--有不及格的，提分+2  高于95，不提
update Result set StudentResult+=2
where SubjectId=@subid      
and ExamDate=@maxdate            --最近一次考试
and StudentResult<95

select @n=COUNT(*) from Result
where SubjectId=@subid
and ExamDate=@maxdate
and StudentResult<70
end

select * from Result
order by SubjectId

 4.case多分支语句

case-end语句计算一组条件表达式 ,并返回其中一个符合条件的结果

语法：

case

    when  条件1  then  结果1

    when  条件2  then  结果2

    [ else 其他结果]

end

ABCDE五级打分制显示学生oop课最近一次考试成绩（姓名和等级）

A级:   90分以上,B级：80－分,C级:   70－分,D级：60－分,E级：60分以下

select @maxdate=MAX(ExamDate) from Result,Subject
where Result.SubjectId=Subject.SubjectId
and SubjectName='oop'
select studentName,成绩=
case
when StudentResult>=90 then 'A'
when StudentResult>=80 then 'B'
when StudentResult>=70 then 'C'
when StudentResult>=60 then 'D'
else 'E'
end
from Result,Student

where Result.StudentNo=Student.StudentNoand ExamDate=@maxdate

五.批处理

1.go指令

go关键字标志着批处理的结束，它是一条或多条SQL语句的集合.

以一条命令的方式来处理一组命令的过程称为批处理.每个批处理之间都是独立的,当一个批处理出现错误时,并不会影响其它批处理中SQL代码的运行.

批处理的主要好处是能够简化数据库的管理

打印直角三角形

-----方法一
declare @num1 nvarchar(200)
declare @count int
declare @result nvarchar(200)
set @num1='*'
set @count=0
set @result=' '
while(@count<5)
begin
set @result+=@num1
print @result
set @count+=1
end
--方法二
declare @i int
declare @j int
declare @num2 nvarchar(100)
set @i=1
set @j=1
set @num2=''
while(@i<=5)
begin
while(@j<=@i)
begin
set @num2+='*'
set @j+=1
end
 print @num2
 set @i+=1
 end

行转列

--建表
create table tmp(rq varchar(10),shengfu nchar(1))
select * from tmp
--添加数据
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-10','胜')
insert into tmp values('2005-05-10','负')
insert into tmp values('2005-05-10','负')
select * from tmp
--行转列
select rq as 日期,sum
(case 
   when shengfu='胜' then 1 
   else 0 
end) as 胜,sum
(
  case
    when shengfu='负' then 1 
    else 0
  end
) as 负
from tmp
group by rq