SQL server学习（三）T-SQL编程、逻辑控制语句和安全模式

314598340

T-SQL编程
　　T-SQL编程与C语言类似，只是语法稍有不同而已，总体思想还是没有变化的。多的就不说了，还是从变量开始。
　　变量也分为全局变量和局部变量，表示方式稍有不同。
　　局部变量：

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

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

全局变量：

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

　　全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

局部变量

声明局部变量语法为：declare @变量名 数据类型

　　

例：declare @age int　　declare @name nvarchar(
10)　　

　　赋值的语法为：
　　set @变量名 = 值 或 select @变量名 = 值
　　

例：　　
set @name
=＇张三＇　　
select @name
= stuname from stu where stuid=＇101＇　　

使用select方法赋值需要注意的是：要确保筛选出来的数据只有一条

来看一个例子：

　　

/*--查找一壶清酒的信息--*/　　
declare @name varchar(8)  --学员姓名
　　
set @name='一壶清酒'        --使用set赋值
　　
select * from stuInfo where stuName = @name
　　
/*--查找一壶清酒的左右同桌--*/
　　
declare @seat int  --座位号
　　
select  @seat=stuSeat from stuInfo  --使用select赋值
　　
where stuName=@name
　　
select * from stuInfo
　　
    where (stuSeat = @seat+1) or (stuSeat = @seat-1)
　　
go
　　


全局变量
　　声明全部变量语法为：declare @@变量名 数据类型
　　常用的全局变量如下表所示：

变量

含义

@@ERROR

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

@@IDENTITY

最后一次插入的标识值

@@LANGUAGE

当前使用的语言的名称

@@MAX_CONNECTIONS

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

@@ROWCOUNT      

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

@@SERVERNAME

本地服务器的名称

@@TRANSCOUNT   

当前连接打开的事务数

@@VERSION     

SQL Server的版本信息

　　再看一个例子：
　　

print  'SQL Server的版本'+@@VERSION　　

print  '服务器的名称: '+@@SERVERNAME　　

insert into stuInfo(stuName,stuNo,stuSex,stuAge)　　

      values('武松','s25328','男','23')　　

--如果大于0表示上一条语句执行有错误　　

print '当前错误号'+convert(varchar(5),@@ERROR)　　

  print '刚才报名的学员，座位号为:'　　
               +convert(varchar(5),@@IDENTITY )
　　
update stuinfo set stuAge=85
　　
        where stuName='一壶清酒'
　　
print '当前错误号'+convert(varchar(5),@@ERROR)
　　
go
　　

　　error指的是错误号，返回为0表正确，没有错误

逻辑控制语句
　　在c语言中，逻辑控制语句是if-else，switch，for，while，在sql编程中，也大相径庭，这里的逻辑控制语句分别是：if-else，while，case-end语句

if-else语句
　　语法格式为：
　　

if(条件)　　begin
　　语句1
　　语句2
　　end
　　

else　　begin
　　语句1
　　语句2
　　end
　　

　　注意：begin…end相当于c语言中的大括号，有多条语句才使用begin…end，相当于begin…end之间的语句就是一个语句块
　　看个实例更容易理解，例：
　　

declare @myavg float　　
select @myavg
=avg(writexam) from stumake　　
print ＇本班平均分＇
+ convert(varchar(5),@myavg)　　

if(@myavg>70)　　begin
　　print ＇前三的成绩为＇
　　select top
3 * from stumake order by writexam dese　　end
　　

else　　begin
　　print ＇后三名的成绩为＇
　　select top
3 * from stumake order by writexam asc　　end
　　

在使用T-SQL编程时，可以切换视图来查看结果，工具>选项>查询结果>常规>显示结果的默认方式>以文本格式显示结果，查看效果更好

while循环语句

语法格式为：

　　

while(条件)　　begin
　　语句1
　　语句2
break　　end
　　

　　注意：break是表示结束循环，与c语言中的辅助控制语句break，continue类似
　　看个实例：
　　

declare @n int　　
where(
1=1) --条件永远成立　　begin
　　select @n
=count(*) from stuMarks　　where writtenExam
<60    --统计不及格人数if (@n>0)　　update stuMarks     
--每人加2分　　set writtenExam
=writtenExam+2　　else
　　break    --退出循环
　　end
　　
print '加分后的成绩如下：'
　　
select * from stuMarks
　　


case-end多分支语句

语法格式为：

　　

case　　when 条件1 then 结果1
　　when 条件2 then 结果2
else 其他结果　　
end
　　

　　看个成绩等级实例就很清楚了，例：
　　

print  'ABCDE五级显示成绩如下：'　　
select stuNo,成绩
= case　　when writtenExam
<60 then 'E'　　when writtenExam between
60 and 69 then 'D'　　when writtenExam between
70 and 79 then 'C'　　when writtenExam between
80 and 89 then 'B'　　else  'A'
　　end
　　
from stuMarks
　　

注意：之前代码中出现过go，批处理标志是go，批处理是将一些逻辑相关的业务操作语句放置在同一批中，同时操作

再看一个实例：

根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。

90分以上：   不加分

80－89分：   加1分

70－79分：   加2分

60－69分：   加3分

60分以下：   加5分

　　

select * from stuMarks  --原始成绩　　

declare @labAvg int      --声明变量，用户临时存放平均分　　

while(1=1)                   --条件一直成立　　

       begin　　

            update stuMarks　　

           set labExam=case　　
             when labExam<60 then labExam+5     --设置加分条件
　　
             when labExam between 60 and 69 then labExam+3
　　
             when labExam between 70 and 79 then labExam+2
　　
             when labExam between 80 and 89 then labExam+1
　　
             else labExam
　　
           end
　　
      select @labAvg=avg(labExam) from stuMarks   --获取目前平均分，判断是否还据需加分
　　
      if @labAvg>=85
　　
            break               --如果已经大于或等于85就执行break，跳出循环
　　
        end
　　
select * from stuMarks  --加分后的成绩
　　


安全模式
　　在SQL server学习（一）的帖子中，有提到过安全模式（设置权限），但都是用手动操作，现在讲一种代码实现的方式

登录验证有两种方式：

SQL身份验证：适合于非windows平台的用户或Internet用户， 需要提供帐户和密码

Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证

　　登录帐户相应有两种：SQL 帐户和Windows帐户

创建登录

添加 Windows登录帐户

　　

exec sp_grantlogin 'HONG-PJ3108YT43\Administrator' –域名\用户名，域名为本机计算机名，用户名为计算机下的用户　　

添加 SQL登录帐户

　　

exec sp_addlogin  ‘yihuqingjiu', ‘123456’         --用户名,密码　　

exec表示调用存储过程，存储过程类似C语言的函数,在后面文章会讲到

创建数据库用户

创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：

　　

exec sp_grantdbaccess ‘登录帐户名’，’数据库用户名’　　

其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。

在数据库中添加两个用户

　　

exec sp_grantdbaccess 'jbtraining\ww123456', 'ww123DBUser'　　
exec sp_grantdbaccess
'yihuqingjiu', 'yiDBUser'　　


系统内置的数据库用户

dbo用户和guest用户

dbo用户：表示数据库的所有者（DB Owner），无法删除dbo用户，此用户始终会出现在每个数据库中

guest来宾用户：适用于没有数据库用户的登录账号访问，每个数据可有也可删除，来宾用户需要管理员授权，不然无法操作数据库

授权的语法
　　

grant 权限 [on  表名 ]  to  数据库用户　　

　　例：
　　

/*--为yiDBUser分配对表stuInfo的select, insert, update权限--*/　　
grant select, insert, update on  stuInfo  to  yiDBUser  
　　

/*--为ww123DBUser分配建表的权限--*/　　
grant  create  table  to  ww123DBUser
　　

　　这样操作比之前的方法可是要简单了很多
　　

本文仅代表作者观点，系作者@温一壶清酒发表。欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。文章出处：http://www.cnblogs.com/hong-fithing/