SQLServer-高级篇（sqlserver ag）

局部变量：（仅在过程中使用）

• 局部变量必须以标记@作为前缀，如@age

• 局部变量的使用也是先声明，（使用declare），再赋值

全局变量：（任何时候均可以使用）

• 局部变量必须以标记@@作为前缀，如@@version

• 全局变量有系统定义和维护，我们只能读取

declare @变量名 数据类型

declare @StuName varchar(20)

declare @StuId int

赋值

set @变量名 = 值

或

select @变量名 = 值

set @StuName = '王小虎'

select @StuName = StudentName form Students

where StudentId = '1001'

使用 select 赋值确保筛选出的记录只有一条

问题：编写T-SQL 查询李铭及其学号相邻的学员？

第一步：找到姓名等于李铭的学员

第二部：将李铭的学号加1或减一，然后在查询

代码实现：

--声明学号变量

declare @stuid int,@stuname varchar(20)

--查询李敏的信息

set @stuname='李敏'

select StudentId,StudentName,Gender,StudentIdNo from Students

where StudentName = @stuname

--查询李敏的学号

select @stuid = StudentId from Students where StudentName = @stuname

--查询与李敏学号相邻的学员

select StudentId,StudentName,Gender,StudentIdNo from Students

where StudentId=(@stuid+1) or StudentId=(@stuid-1)

使用场景 set select

同时对多个变量赋值 不支持 支持

表达式返回多个值时 出错 将返回 的最后一个值赋给变量

表达式未返回值时 变量被赋NULL值 变量保持原值

declare @stuAddress nvarchar(100),@stuName nvachar(100)

set @stuAddress='天津',@stuName='张三' --不允许

select @stuaddress = '天津',@stuName='小孩' --允许

set @stuAddress = (select StudentAddress from Students) --不允许

select @stuAddress = StudentAddress from Students --得到最后一个

set @stuAddress = (select StudentAddres from Students where 1<0) --NULL值

select @stuAddress = StudentAddress from STudents where 1<0 --保持原值

全局变量

变量 含义

@@error 最后一个T-SQL错误的错误号

@@identity 最后一次插入的标识值

@@language 当前使用的语言的名称

@@max_connections 可以创建的同时连接的最大数目

@@rowcount 受上一个SQL语句影响的行数

@@servername 本地服务器的名称

@@transcount 当前连接打开的事务数

@@version SQL Server的版本信息

问题：查询数据库服务器版本和SQLServer的版本？

print '服务器的名称' + @@servername

print 'SQL Server 的版本' + @@version 使用print在屏幕上输出信息

print后面必须跟字符串

select @@ servername as '服务器的名称'

select @@version as 'SQL Server 的版本'

T-SQL 类型转换函数

问题：查询学号等于 10003 的学员总成绩？

use StudentManager

go

--定义变量并查询

declare @sumScore int

select @sumScore=(CSharp+SQLServerDB) from ScoreList

where StudentId = 100003

--输出

print '学号=100003 总成绩：' + @sumScore

提示错误：在将varchar值 '学员=100003总成绩“‘转换成数据类型 int 时失败

字符串和数值不能直接相加

解决方案：使用 convert 函数实现强制转换

print '学号=100003总成绩：'+convert(varchar(20),@sumScore)

T-SQL 类型转换函数：CAST

CAST（表达式AS数据类型）

T-SQL类型转换函数：CONVERT

CONVERT（数据类型，表达式，样式） 样式： 102 103 。。。107

第三个参数可以省略，一般用于日期类型数据类型转为为字符串，或浮点类型数据转换为字符类型

--使用 CAST 转换

select StudentName + '的出生日期是:' + CAST(Birthday as varchar(50)) AS '学生信息'

from Students where StudentId = 100005

--使用CONVERT转换

select StudentName + '的出生日期是:' + CONVERT(varchar(50),Birthday,102) AS '学生信息'

from Students where StudentId = 100005

CONVERT() 与 CAST()的不同点是：可以指定转换的样式

输出结果：

何涛的出生日期是 04 04 1994 12:00AM

何涛的出生日期是 1994 04 04

实例练习

问题：查询学号等于 10002 的学员年龄？

第一步：查询 100002 学员的出生日期

第二步：获取出生年天数

第三步：计算年龄

--定义变量

declare @birthday datetime,@daysint,@age int

--查询出生日期

select @birthday = Birthday from Students where Students where StudentId = 100002

--计算出生天数

set @days = datediff(dayofyear,@birthday,getdate())

--计算年龄

set @age = floor(@days/365)

--输出信息

print '100002学员年龄：' + convert(vachar(20),@age)

datediff 函数计算两个日期差 单位：天

floor 函数返回一个小于或等于当前值的最大整数

--直接查询

select floor(datediff(dy,Birthday,GETDATE()/365) 年龄

from Students where StudentId = 100002

IF-ELSE语句

SQL中的IF-ELSE语句

IF（条件）

BEGIN

语句1

语句2

..........

END

ELSE

BEGIN

语句1

语句2

........

END

• ELSE是可选部分

• 如果有多条语句，才需要BEGIN-END语句块

问题：查询软件1班的C#考试平均分，如果平均分大于80，则表示成绩优秀，反之表示成绩一般

--查询成绩

declare @cAvg int

select @cAvg = avg(CSharp) from ScoreList

inner join Students on ScoreList.StudentId = StudentId

where ClassId = 1

print 'C#平均成绩：'+vonvert(varchar(20),@cAvg)

--判断成绩

if(@cAvg>=80)

print '软件一班成绩优秀'

else

print '软件一班成绩一般'

WHILE循环语句

SQL中的WHILE语句

WHILE（条件）

BEGIN

语句1

语句2

........

BREAK

END

BREAK 表示跳出循环，如果有多条语句，才需要BEGIN-END语句块

问题：将所有C#成绩不及格的学员加分到60

方法一：

print '加分之前的C#成绩：'

select StudentId,CSharp from ScoreList

declare @CSharp int,@StuId int

while(1=1)--隐患

begin

select top 1 @CSharp=CSharp,@StuId=StudentId

from ScoreList where CSharp<60

if(@CSharp<60)

update ScoreList set CSharp=CSharp+1

where StudentId = @StuId

if((select count(*) from ScoreList where CSharp<60)=0)

break

end

print '加分之后的C#成绩：'

select StudentId,CSharp from ScoreList

方法二：

while((select COUNT(*) from ScoreList where CSharp < 60)>0)

begin

select top 1 @csharp = CSharp,@stuId = StudentId from ScoreList

where CSharp < 60

update ScoreList Set CSharp = CSharp +1 where StudentId = @stuId

end

select StudentId,CSharp from ScoreList

CASE-END多分支语句

CASE

WHEN 条件1 THEN 结果1

WHEN 条件2 THEN 结果2

........

ELSE 其他结果

END

ELSE 表示 CASE 中所有 WHEN 条件均不为 TRUE 时返回的结果，如果省略 ELSE 且 WHEN 条件都为 FALSE 时，CASE 语句返回 NULL

问题：对学员成绩进行评比，90以上为A，80-89为B，70-79为C，60-69：为D，60以下：不及格

select 学员=StudentId,

总评 = CASE

when (CSharp+SQLServerDB)/2 >= 90 then 'A'

when (CSharp+SQLServerDB)/2 between 80 and 89 then 'B'

when (CSharp+SQLServerDB)/2 between 70 and 79 then 'C'

when (CSharp+SQLServerDB)/2 between 60 and 69 then 'D'

else '不及格'

end

from ScoreList

批处理命令

批处理：

语句1

语句2

......

GO

• 批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQL Server执行

• SQL Server 将每批SQL语句编译成一个执行单元，此单元称为执行计划

批处理语句示例

--创建成绩表

if exits(select * from sysobjects where name='ScoreList')

drop table ScoreList

go

create table ScoreList

(

id int identity(1,1) primary key,

StudentId int not null,

CSharp int null,

SQLServerDB int null,

UpdateTime smalldatetime not null

)

go

GO是批处理的标识，表示SQL Server将这些SQL语句编译为一个执行单元，提高执行效率一般是将一些逻辑相关的业务操作语句放置在同一批中，这完全有业务需求和代码编写者觉得

GO是SQLServer的批处理命令，只有代码编辑器才能识别并处理，编辑其他营业程序就不能使用该命令。由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影响其他批处理中SQL代码的运行

欲知后事如何，请听下回分解........