数据库T-SQL语句的详细讲解

                
1 . 变量
在Transact-SQL 中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT 或SET 命令来设定变量的值。其语法如下：
SELECT @局部变量= 变量值
SET @局部变量量= 变量值
【例】声明一个长度为 8 个字符的变量id，并赋值。
declare @id char（8）
select @id =‘10010001’


                
2．全局变量
全局变量是SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。
全局变量通常存储一些SQL Server 的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL 命令执行后的状态值。
全局变量不是由用户的程序定义的，它们是在服务器级定义的，只能使用预先说明及定义的全局变量。
引用全局变量时必须以“@@”开头。
局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。

                
3．注释符
在Transact-SQL 中可使用两类注释符：
1．ANSI 标准的注释符“- -”用于单行注释。
2．与C 语言相同的程序注释符号，即“/*……*/”，/* 用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。

                
2 . 　流程控制命令
Transact-SQL 语言使用的流程控制命令主要有以下几种控制命令。
2.1　BEGIN…END
其语法如下：
BEGIN
<命令行或程序块块>
END
BEGIN…END 用来设定一个程序块，将在BEGIN…END 内的所有程序视为一个单元执行。
BEGIN…END 经常在条件语句（如IF…ELSE）中使用。
在BEGIN…END 中可嵌套另外的BEGIN…END 来定义另一程序块。

                
2.2　IF … ELSE
其语法如下：
IF <条件表达式式>
<命令行或程序块块>
[ELSE [条件表达式式]
<命令行或程序块块>]
其中:
<条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。
ELSE 子句是可选的，最简单的IF 语句没有ELSE 子句部分。
IF…ELSE 用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。
如果不使用程序块，IF 或ELSE 只能执行一条命令。
IF ELSE 可以进行嵌套，在Transact-SQL 中最多可嵌套32 级。

                
【例】从SC数据表中求出学号为S1同学的平均成绩，如果此平均成绩大于或等于60分，则输出“pass”信息。
if (select avg(score) from sc where sno='S1' group by sno)>=60
   begin
      print 'pass'
  end
                
2.3　CASE
CASE 命令有两种语句格式：
格式1：
CASE <运算式>
WHEN <运算式> THEN <运算式>
…
WHEN <运算式> THEN <运算式>
[ELSE <运算式>]
END
该语句的执行过程是：
将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，
如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。
ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。

                
【例】从学生表S中，选取SNO，SEX，如果SEX为“男”则输出“M”，如果为“女”输出“F”。
SELECT SNO,
     SEX=
     CASE sex
     WHEN '男' THEN 'M'
     WHEN '女' THEN 'F'
     END
FROM S
               
格式2：CASE
   WHEN <条件表达式> THEN <运算式>
   …
   WHEN <条件表达式> THEN <运算式>
   [ELSE <运算式>]
END
该语句的执行过程是：
首先测试WHEN后的表达式的值
如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值
如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值
如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。
注：CASE 命令可以嵌套到SQL 命令中。


                
【例】从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。
SELECT SNO,CNO,
      SCORE=
      CASE 
      WHEN SCORE IS NULL THEN '未考'
      WHEN SCORE<60 THEN '不及格'
      WHEN SCORE>=60 AND SCORE<70 THEN '及格'
      WHEN SCORE>=70 AND SCORE<90 THEN '良好'
      WHEN SCORE>=90 THEN '优秀'
      END
FROM SC
                
2.4　WHILE…CONTINUE…BREAK
其语法如下：
WHILE <条件表达式>
BEGIN
  <命令行或程序块>
  [BREAK]
  [CONTINUE]
  [命令行或程序块]
END
WHILE 命令在设定的条件成立时，会重复执行命令行或程序块。
CONTINUE 命令可以让程序跳过CONTINUE 命令之后的语句，回到WHILE 循环的第一行，继续进行下一次循环。
BREAK 命令则让程序完全跳出循环，结束WHILE 命令的执行。
WHILE 语句也可以嵌套。

                
如：以下程序计算1－100之间所有能被3整除的数的个数及总和。
DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT
SET @S=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
     BEGIN
        IF (@I%3=0)
           BEGIN
              SET @S=@S+@I
              SET @NUMS=@NUMS+1
           END
        SET @I=@I+1
     END
PRINT @S
PRINT @NUMS
                
2.5　WAITFOR
其语法如下：
WAITFOR {DELAY <‘时间’> | TIME <‘时间’>
| ERROREXIT | PROCESSEXIT | MIRROREXIT}
WAITFOR 命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。
其中‘时间’必须为DATETIME 类型的数据，但不能包括日期。
各关键字含义如下：
1．DELAY：用来设定等待的时间，最多可达24 小时
2．TIME：用来设定等待结束的时间点
3．ERROREXIT：直到处理非正常中断
4．PROCESSEXIT：直到处理正常或非正常中断
5．MIRROREXI： 直到镜像设备失败

                
【例】等待1 小时2 分零3 秒后才执行SELECT 语句。

waitfor delay ‘01:02:03’
Select  *   from    employee
                
2.6　GOTO
语法如下：
GOTO 标识符
GOTO 命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。
作为跳转目标的标识符可为数字与字符的组合。但必须以“：”结尾。
在GOTO 命令行，标识符后不必跟“：”

                
如：求1＋2＋3＋…＋10的总和。
DECLARE @S SMALLINT,@I SMALLINT
SET @I=1
SET @S=0
BEG:
IF (@I<=10)
   BEGIN
       SET @S=@S+@I
       SET @I=@I+1
       GOTO BEG
   END
PRINT @S
                
2.7　RETURN
语法如下：
RETURN （[整数值]）
RETURN 命令用于结束当前程序的执行，返回到上一个调用它的程序或其它程序。
在括号内可指定一个返回值。
如果没有指定返回值，SQL Server 系统会根据程序执行的结果返回一个内定值，如：

0 程序执行成功
-1 找不到对象
-2 数据类型错误
-3 死锁
-4 违反权限原则
-5 语法错误
-6 用户造成的一般错误
-7 资源错误如磁盘空间不足
-8 非致命的内部错误
-9 已达到系统的极限
-10 -11 致命的内部不一致性错误
-12 表或指针破坏
-13 数据库破坏
-14 硬件错误
如果运行过程产生了多个错误，SQL Server 系统将返回绝对值最大的数值；
如果此时用户定义了返回值，则以返回用户定义的值。RETURN 语句不能返回NULL值。

                
2．8    SELECT
SELECT 命令可用于给变量赋值其语法如下：
SELECT {@local_variable = expression } [，...n]
SELECT 命令可以一次给多个变量赋值。
当表达式expression 为列名时，SELECT 命令可利用其查询功能一次返回多个值，变量中保存的是其返回的最后一个值。
如果SELECT命令没有返回值，则变量值仍为其原来的值。
当表达式expression 是一个子查询时，如果子查询没有返回值，则变量被设为NULL。

                
12．SET
SET 命令有两种用法：
（1）用于给局部变量赋值
在用DECLARE 命令声明之后，所有的变量都被赋予初值NULL。
需要用SET 命令来给变量赋值，但与SELECT 命令不同的是SET 命令一次只能给一个变量赋值。
不过由于SET 命令功能更强且更严密，因此，SQL Server 推荐使用SET 命令来给变量赋值。

                
（2）用于设定用户执行SQL 命令时，SQL Server 的处理选项设定。
有以下几种设定方式：
SET：选项ON
SET：选项OFF
SET：选项值
                
15．USE
语法如下：
USE {databasename}
USE 命令用于改变当前使用数据库为指定的数据库。
用户必须是目标数据库的用户成员或目标数据库建有GUEST 用户账号时，使用USE 命令才能成功切换到目标数据库。

                
3.存储过程和触发器
在大型数据库系统中，存储过程和触发器具有很重要的作用。
无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。
就本质而言，触发器也是一种存储过程。
存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。
SQL Server 2005 不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。

                
3.1  存储过程概述
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。
用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
在SQL Server 的系列版本中存储过程分为两类：
系统提供的存储过程
用户自定义存储过程

                
系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。
通过系统存储过程，SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。
尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。
而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。
用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。

                
3.2   创建存储过程
在SQL Server 2000 中创建一个存储过程有两种方法：
一种是使用Transaction-SQL命令Create Procedure，
另一种是使用图形化管理工具Enterprise Manager。
用T-SQL 创建存储过程是一种较为快速的方法，
对初学者使用Enterprise Manager 更易理解，更为简单。
当创建存储过程时，需确定存储过程的三个组成部分：
1．所有的输入参数以及传给调用者的输出参数
2．被执行的针对数据库的操作语句包括调用其它存储过程的语句
3．返回给调用者的状态值以指明调用是成功还是失败

                
2.1 用CREATE PROCEDURE 命令创建存储过程
在创建存储过程之前应该考虑到以下几个方面：
1．在一个批处理中Create Procedure 语句不能与其它SQL 语句合并在一起。
2．数据库所有者具有默认的创建存储过程的权限它可把该权限传递给其它的用户。
3．存储过程作为数据库对象其命名必须符合命名规则。
4．只能在当前数据库中创建属于当前数据库的存储过程。





                
创建存储过程的语法规则
CREATE  PROCEDURE 存储过程名
     @参数名  类型[=default] [OUTPUT][,…n]
     AS
    SQL语句[,…n]
如：在teach数据库中，创建一个名称为myproc的存储过程，该存储过程的功能是从数据表s中查询所有男同学的信息。
GO
CREATE PROCEDURE myproc
AS
  SELECT * FROM s WHERE sex = '男'
GO


定义具有参数的存储过程。
如：在teach数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向数据表s中插入一条记录，新记录的值由参数提供。
USE teach
GO
CREATE PROCEDURE InsertRecord
(
@sno char(6),
@sn char(20),
@age numeric(5),
@sex char(2),
@dept char(10)
)
AS
INSERT INTO s VALUES(@sno,@sn,@sex,@age,@dept)
GO

                
定义具有参数默认值的存储过程。
如：在teach数据库中，创建一个名称为InsertRecordDefa的存储过程，该存储过程的功能是向数据表s中插入一条记录，新记录的值由参数提供，如果未提供系别dept的值时，由参数的默认值代替。
USE teach
GO
CREATE PROCEDURE InsertRecordDefa
(
@sno char(6),
@sn char(20),
@age numeric(5),
@sex char(2),
@dept char(10)='无'
)
AS
INSERT INTO s VALUES(@sno,@sn, @sex , @age, @dept)
GO

                
定义能够返回值的存储过程。
如：在teach数据库中，创建一个名称为Query_Study的存储过程，该存储过程的功能是从数据表s中根据学号查询某一同学的姓名和系别。
USE teach
GO
CREATE PROCEDURE Query_Study
(
@sno char(6),
@sn char(20) OUTPUT,
@dept char(10) OUTPUT
)
AS
SELECT @sn=sn, @dept=dept
FROM s
WHERE sno=@sno
GO

                
2.2 使用Enterprise Manager 创建存储过程
1．启动Enterprise Manager，登录到要使用的服务器。
2．在Enterprise Manager的左窗格中，展开要创建存储过程的数据库文件夹，单击“存储过程”文件夹，此时在右窗格中显示该数据库的所有存储过程。
3．用右键单击“存储过程”文件夹，在弹出的快捷菜单中选择“新建存储过程”,此时打开“新建存储过程”对话框。
4．在“文本”编辑框中输入存储过程的正文内容。
5．单击“检查语法”按钮，检查语法是否正确。
6．单击“确定”，保存。
7．在右窗格中，右击该存储过程，在弹出菜单中选择“所有任务”, 选择“管理权限”设置权限。 



                
3.2　重新命名存储过程

通过Enterprise Manager 也可修改存储过程的名字，其操作过程与Windows下修改文件名字的操作类似。
即首先选中需修改名字的存储过程
然后右击鼠标，在弹出菜单中选取“重命名”选项
最后输入新存储过程的名字。

                
3.3　删除存储过程
删除存储过程使用drop 命令，drop 命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。其语法规则为：

DROP PROCEDURE {procedure}} [,…n]
如：将存储过程mynewproc从数据库中删除。
     drop procedure mynewproc
                
3.4　执行存储过程
执行已创建的存储过程使用EXECUTE 命令
1.执行teach库中的存储过程myproc(无参调用)。
EXECUTE myproc
2.执行teach库中存储过程InsertRecord (带参调用) 。
EXECUTE InsertRecord @sno = ‘S1’, @sn = ‘王大利’, @sex = ‘男’, @age = 18,@dept= ‘计算机系’

                
3.执行teach库中的存储过程InsertRecordDefa (含默认值调用) 。
EXECUTE InsertRecordDefa @sno = 'S10',@sn = '高平', @sex = '女', @age = 18
4.执行teach库中的存储过程Query_Study (含有输出参数) 。
DECLARE @sn char(20)
DECLARE @dept char(10)
EXECUTE Query_Study 'S10',@sn OUTPUT,@dept OUTPUT
SELECT '姓名' =@sn, '系别'=@dept

                
　触发器概述
上面介绍了一般意义的存储过程，即用户自定义的存储过程和系统存储过程。
接下来将介绍一种特殊的存储过程，即触发器。
触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。
当对某一表进行诸如UPDATE、NSERT、DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句。从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。
除此之外，触发器还有其它许多不同的功能。

                
创建触发器
1　用CREATE TRIGGER 命令创建触发器
  CREATE TRIGGER 触发器名 ON 表名
        FOR INSERT（UPDATE、DELETE）
    AS
      SQL语句
其中：insert 、update 、delete为当前表所执行的操作（这些操作可根据需要进行设定），只有匹配了指定的操作，AS 后面的语句才会执行，也就是激活了触发器。
  在触发器中也可以象存储过程中一样定义变量。

                
　
如：下面创建一个触发器，当向表s中插入一条记录时，自动显示表s中的记录。
CREATE TRIGGER Change_Display
ON s
FOR INSERT,UPDATE,DELETE
AS
SELECT * FROM s
该触发器建立完毕后，当执行如下操作时将会显示数据表s中的全部记录。
EXECUTE InsertRecordDefa @sno = 'S11', @sn = '张建峰', @age = 17, @sex = '男'

                
2　用管理工具Enterprise Manger 创建触发器
1．启动Enterprise Manager 登录到要使用的服务器。
2．在Enterprise Manager的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。
3．在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现 “触发器属性”对话框。

                
2　用管理工具Enterprise Manger 创建触发器
4．在“名称”下拉框中选择“<新建>”，在“文本”编辑框中输入触发器的文本命令。
5．单击“检查语法”按钮，检查语句是否正确。
6．单击“应用”按钮，在“名称”下拉列框中会有新创建的触发器名字。
7．单击“确定”按钮，关闭窗口创建成功。
                
　管理触发器
1  使用Enterprise Manager显示触发器信息
1．启动Enterprise Manager 登录到要使用的服务器。
2．在Enterprise Manager的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。
3．在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，出现 “触发器属性”对话框。
4．在“名称”下拉框中选择所要查看的触发器的名称，在“文本”编辑框中显示出该触发器的文本命令。
                
3　修改、删除触发器
通过Enterprise Manager 和系统过程或T_SQL 命令，可以修改触发器的名字和正文。
1．使用sp_rename 命令修改触发器的名字
其语法格式为：
sp_rename oldname , newname
oldname为触发器原来的名称 ，newname为触发器的新名称。
2．通过Enterprise Manager 修改触发器正文
通过Enterprise Manager 修改触发器正文的操作步骤与查看触发器信息一样。
修改完触发器后要使用“检查语法”选项对语句进行检查。
3．通过Alert trigger 命令修改触发器正文

                
4．删除触发器
用户在使用完触发器后可以将其删除。
只有触发器属主才有权删除触发器。
删除已创建的触发器有三种方法：
（1）用系统命令DROP TRIGGER 删除指定的触发器，其语法形式如下：
DROP TRIGGER 触发器名字
（2）删除触发器所在的表时，SQL Server 将自动删除与该表相关的触发器。
（3）按前介绍的方法进入“触发器属性”对话框，在该对话框中选择要删除的触发器，然后单击“删除”按钮即可。

                
CREATE PROCEDURE disRecord
AS
declare @sno char(6),@sn char(20),@sex char(2),@age smallint
declare sp cursor for
select s_no,s_name,s_sex ,year(getdate())-   year(s_birthday) from student where s_sex='女'
open sp
fetch sp into @sno ,@sn ,@sex ,@age
while @@fetch_status=0
begin
    print @sno+@sn +@sex +convert(char,@age)
    fetch sp into @sno ,@sn ,@sex
end
close sp
deallocate sp


另外关于go和as的意思区别：GO 用于在 SSMS 和 SQLCMD 中将其之前的 T-SQL 语句作为一个批处理提交给 SQL Server 实例。GO 不是 T-SQL 语句，只是由这些特定客户端指定的提交批处理的方式。批处理（Batch）是 SQL SERVER 客户端作为一个单元发送给服务的一个或多个 T-SQL 语句的集合－客户端将此集合一次性的提交给实例，而服务会将其编译为一个执行计划。启用 ANSI_NULLS，所有与空值的比较运算结果为 UNKNOWN；否则空值与空值的比较结果为 TRUE。启用 QUOTED_IDENTIFIER 表示使用双引号（ ""） 作为分隔符（当标示符不符合 SQL SERVER 的命名规则时可以使用 "" 或 [] 作为分隔符）。AS 其实是关键字，在存储过程中可以理解为将其下（到 GO）的 T-SQL 语句，定义为存储过程。