MS SQL高级——逻辑语句与子查询+定义参数

逻辑语句与子查询

变量的分类

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

• 局部变量必须以标记@作为前缀，如@age。局部变量的使用也是先声明（使用declare），再赋值。

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

• 全局变量必须以标记@@作为前缀，如@@version。全局变量由系统定义和维护，我们只能读取，不能修改全局变量值。

局部变量定义与赋值（定义参数）

局部变量的定义语法

DECLARE @变量名 数据类型

赋值方法

• SET @变量名=值
• SELECT @变量名=值

使用Select赋值确保筛选出的记录只有1条

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

局部变量的使用

代码编写：

 示例：

use StudentManageDB
go
--声明学号变量
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比较

 

示例：

declare @stuAddress nvarchar(100) , @stuName nvarchar(100)
--set @stuAddress='天津', @stuName='张三'                            --不允许这样赋值
select @stuaddress='天津', @stuName='王小虎'             --允许

    --set @stuAddress = (select StudentAddress from Students)  --不允许
    select @stuAddress = StudentAddress from Students    --得到最后一个
    set @stuAddress = (select StudentAddress from Students where 1<0) --NULL值
    select @stuAddress =  StudentAddress from Students where 1<0        --保持原值

全局变量示例

全局变量都使用两个@标志作为前缀

变量	含义
@@ERROR	最后一个T-SAQL错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言的名称
@@MAX_CONNECTIONS	可以创建的同时连接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器的名称
@@TRANSCOUNT	当前连接打开的事务数
@@VERSIONSQL	Server的版本信息

PRINT  '服务器的名称: ' + @@SERVERNAME  
PRINT 'SQL Server的版本' + @@VERSION 
  
SELECT  @@SERVERNAME  AS  '服务器名称'
SELECT  @@VERSION  AS  'SQL Server的版本' 

use StudentManageDB
go
--插入学员信息
insert into Students (StudentName,Gender,Age,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
values('王小欣','男',28,'1988-08-07',120223198808071111,'022-22222222','天津市南开区',10)


--获取最后一条SQL语句的执行错误号
print @@error

T-SQL数据类型转换

常用的两种转换方法

• CONVERT（数据类型，表达式，样式）第三个参数可以省略，它一般用于日期类型数据转换为字符类型，或浮点类型数据转换为字符类型，不同的样式使转换后字符数据的显示格式不同
• CAST（表达式AS数据类型）

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

use StudentManageDB
go
--定义变量并查询
declare @sumScore int
select @sumScore=(CSharp+SQLServerDB) from ScoreList
where StudentId=100003
--输出
--print '学号=100003总成绩：'+@sumScore

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

两种不同类型的转换比较

use StudentManageDB
go
--使用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

T-SQL中的其他函数

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

use StudentManageDB
go
--定义变量
declare @birthday datetime,@days int,@age int
--查询出生日期
select @birthday=Birthday from Students where StudentId=100002
--计算出生天数
set @days=datediff(dayofyear,@birthday,getdate())
--计算年龄
set @age=floor(@days/365)
--输出信息
print '100002学员年龄：'+convert(varchar(20),@age)

--直接查询
select FLOOR(DATEDIFF(dy, Birthday, GETDATE())/365) 年龄
from Students where StudentId=100002

逻辑控制语句

分支结构

• IF-ELSE语句
• CASE-END语句

循环结构

• WHILE语句

IF-ELSE语句示例

use StudentManageDB
go
--查询成绩
declare @cAvg int
select @cAvg=avg(CSharp) from ScoreList 
inner join Students on ScoreList.StudentId=Students.StudentId where ClassId=1
print 'C#平均成绩：'+convert(varchar(20),@cAvg)
--判断成绩
if(@cAvg>=80)  
    print '软件一班成绩优秀！'  
else
    print '软件一班成绩一般！'

WHILE语句示例

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

use StudentManageDB
go
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 

CASE-END语句示例

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

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

use StudentManageDB
go
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

 EXISTS子查询

回顾：如何用SQL语句检测数据库或数据表是否已经创建？

语法规范

IF EXISTS（子查询）
    语句

用法总结果

• 子查询的结果非空，即记录条数1条以上，则EXISTS（子查询）将返回真（true），否则返回假（false）。
• EXISTS也可以作为WHERE语句的子查询，但一般都能用IN子查询替换。

END