Transact-SQL变量基本语法_综合

作者： 18届 KDB

日期： 2020-10-2

Transact-SQL变量基本语法

Transact-SQL变量
Transact-SQL注释
Transact-SQL运算符
Transact-SQL批处理
Transact-SQL常用命令
Transact-SQL函数
Transact-SQL自定义函数
存储过程
触发器
备份与还原

Transact-SQL变量

全局变量

-- 全局变量使用两个@标记为前缀，使用SELECT查询语句检索任意全局变量
SELECT @@VERSION AS SQL_SERVER_VERSION

常用全局变量

全部变量名	含义
@@connections	服务器启动以来已经创建的连接数
@@ERROR	最后一个T-SQL错误的错误号
@@IDENTITY	最后一个插入的标识
@@SERVERNAME	本机服务器名称
@@ROWCOUNT	上一个执行的SQL语句影响行数

局部变量

DECLARE @变量名 变量类型 [, @变量名 变量类型 ......]
SELECT @变量名 = 变量值
-- 或 SET @变量名 = 变量值

例：声明一个长度为8字符变量id，并赋值为10010001

DECLARE @id char(8) SET @id = '10010001'

例：从表S中查询学号为’S7’的学生的学号和姓名，并将查询的学号和姓名分别赋值给@sno和@sn

DECLARE @sno VARCHAR(10), @sn VARCHAR(10) 
SELECT @sno = SNO, @sn = SN FROM S WHERE Sno = 'S7'

Transact-SQL注释

-- 常用于单行注释/* 用于多行注释 */

Transact-SQL运算符

算数运算符

运算符	含义
+	加
-	减
*	乘
/	除
%	求余数

比较运算符

运算符	含义
=	等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于
<>	不等于
!=	不等于(非SQL-92标准)
!<	不小于(非SQL-92标准)
!>	不大于(非SQL-92标准)

逻辑运算符

运算符	含义
ALL	如果一组比较中都为TRUE，运算结果就为TRUE
AND	如果两个表达式都为TRUE，运算结果就为TRUE
ANY	如果一组的比较中任何一个为TRUE，运算结果就为TRUE
BETWEEN	如果操作数在每个范围之内，运算结果就为TRUE
EXISTS	如果子查询包含一些行，运算结果就为TRUE
IN	如果操作数等于表达式列表中的一个，运算结果就为TRUE
LIKE	如果操作数与一种模式匹配，运算结果为TRUE
NOT	对逻辑值取反
OR	如果两个两个布尔值表达式中的一个为TRUE，运算结果就为TRUE
SOME	如果一系列操作数中，有些值为TRUE，运算结果为TRUE

换位运算符

运算符	含义
&	按位与
\|	按位或
^	按位异或

Transact-SQL批处理

批处理是使用GO语句标记批处理的技术。多个批处理可以用多个GO分开，其中两个GO之间的SQL语句就是一个批处理单元

USE STUDENT
SELECT * FROM STUDENT
UPDATE STUDENT SET AGE = 23 
WHERE SN = '1001'
GO

流程控制语句

BEGIN…END语句
CASE语句
WHILE语句
WAITFOR语句
GOTO语句
RETURN语句

BEGIN…END语句

-- BEGIN...END语句经常在条件语句和循环语句中使用
BEGIN<命令行或程序块>
END

IF…ELSE语句

IF <条件表达式><命令行或程序块>
[ELSE<命令行或程序块>]

例：从数据库Teach中的SC数据表中求出学号S1同学的平均成绩，如果此成绩大于或等于60分，则输出’pass’，否则输出’fail’信息

USE Teach
GO
IF (SELECT AVG(SCORE) FROM SC WHERE SNO = 'S1') >= 60PRINT 'pass'
ELSEPRINT 'fail'
GO

IF [NOT] EXISTS 语句

IF [NOT] EXISTS (SELECT 子查询)<命令行或程序块>
[ELSE<命令行或程序块>]

例：从数据库Teach中的S表中读取学号S1同学记录，如果存在，则输出’存在记录’，否则输出’不存在记录’

USE Teach
GO 
DECLARE @message VARCHAR(255)
IF EXISTS (SELECT * FROM S WHERE SNo = 'S1')SET @message = '存在记录'
ELSESET @message = '不存在记录'
PRINT @message
GO

CASE语句

CASE <表达式>WHEN <表达式> THEN <表达式>...WHEN <表达式> THEN <表达式>[ELSE <表达式>]
END

例：从数据库Teach中的S表中选取SNo和Sex，如果Sex字段为’男’，则输出’M’；如果为’女’，则输出’F’

USE Teach
GO
SELECT SNo, Sex = CASE sexWHEN '男' THEN 'M'WHEN '女' THEN 'F'
END
FROMS
GO

WHILE…CONTINUE…BREAK语句

WHILE <条件表达式>
BEGIN<命令行或程序块>[BREAK][CONTINUE][命令行或程序块]
END

例：计算输出1-100之间能够被3整除的数的总和及个数

DECLARE @s SMALLINT, @i SMALLINT, @nums SMALLINT
SET @s = 0
SET @i = 1
SET @nums = 0
WHILE (@i <= 100)BEGINIF (@i % 3 = 0)BEGINSET @s = @s + @iSET @nums = @nums + 1ENDSET @i = @i + 1END
PRINT @s
PRINT @nums

WAITFOR语句

WAITFOR {DELAY <'时间'> | TIME <'时间'> | ERROREXIT | PROCESSEXIT | MIRROEEXIT}

DELAY：用来设定等待的时间，最多可达24小时
TIME：用来设定等待结束的时间点
ERROREXIT：直到处理非正常中断
PROCESSEXIT：直到处理正常或非正常中断
MIRROREXIT：直到镜像设备失败
例：等待1小时2分零3秒后才执行SELECT语句

WAITFOR DELAY '01:02:03'
SELECT * FROM S

GOTO语句

DECLARE @s SMALLINT, @i SMALLINT
SET @i = 1
SET @s = 0
BEG:
IF (@i <= 10)BEGINSET @s = @s + @iSET @i = @i + 1GOTO BEGEND
PRINT @s

RETURN语句

RETURN ([整数值])

RETURN语句不能返回NULL

返回值	含义
0	程序执行成功
-1	找不到对象
-2	数据类型错误
-3	死锁错误
-4	违反权限规则
-5	语法错误
-6	用户造成的一般
-7	资源错误
-8	非致命的内部错误
-9	达到系统配置参数极限

Transact-SQL常用命令

BACKUP：用于将数据库内容或其事物处理日志备份到存储介质(软盘、硬盘、磁带等)
DBCC：用户验证数据库完整性、查找错误、分析系统使用情况等

DECLARE {
   {@local_variable data_type}
|{@cursor_variable_name CURSOR}
|{table_type_definition}
} [,...n]

EXECUTE：用来执行存储过程
KILL：用于中止某一过程的执行
PRINT：用于向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量

PRINT 'any ASCII text' | @local_variable | @@FUNCION | string_expreeion

PAISERROR：用于在SQL Server系统返回错误信息时，同时返回用户制定的信息
SELECT：用于给变量赋值
SET：有两种用法。用于给局部变量赋值，用于用户执行SQL命令时，SQL Server初理选项的内容设定。
SHUTDOWN：用于停止SQL Server的执行

SHUTDOWN [WITH NOWAIT]

USE：用于改变当前使用的数据库为指定数据库

Transact-SQL函数

统计函数

STDEV函数：STDEV函数返回表达式中所有数据的标准差
STDEVP函数：STDEVP函数返回值表达式中所有数据的总体标准差

算数函数

三角函数：SIN、COS、TAN、COT…
角度弧度转换：DEGREES(把弧度转换为角度)、RADIANS(把角度转换为弧度)
指数函数：EXP(表达式)
对数函数：LOG(表达式)、LOG10(表达式)
平方根函数：SQRT(表达式)
近似取整函数：CEILING(表达式)向上取整、FLOOR(表达式)向下取整、ROUND(表达式, n)四舍五入取整，精度为n
符号函数：ABS(表达式)返回表达式的绝对值、SIGN(表达式)测试表达式的正负号，返回0，1，-1
其他函数：PI()返回圆周率、PAND()0-1之间随机浮点数

字符串函数

ASCII(character_expreesion)：返回字符表达式最左端字符的ASCII码值
CHAR(integer_expression)：用于将ASCII码转换为字符
LOWER(character_expreesion)：用于把字符串全部转换为小写
UPPER(character_expreesion)：用于把字符串全部转换为大写
STR(float_expression[, length[, ]])：用于把数值型数据转换为字符型数据
LTRIM(character_expreesion)：用于把字符串头部的空格去掉
RTRIM(character_expreesion)：用于把字符串尾部的空格去掉
LEFT(character_expreesion, integer_expression)：返回从字符串最左边起到第integer_expression个字符部分
RIGHT(character_expreesion, integer_expression)：返回从字符串最右边起到第integer_expression个字符部分
CHARINDEX(substring_expression, expression)：返回字符串中某个指定的子串出现的开始位置

日期函数

DAY(<date_expression>)：DAY函数返回date_expression中的日期值
MONTH(<date_expression>)：MONTH函数返回date_expression中的月份值
YEAR(<date_expression>)：YEAR函数返回date_expression中的年份值
DATEADD()：DATEADD函数返回指定日期date加上指定的额外日期间隔number产生的日期
DATEDIFF()：DATEDIFF函数返回两个指定日期在datepart方面的不同之处，即date2超过date1的差距值，其结果值时是一个带有正负号的整数值
DATENAME(, )：DATENAME函数以字符串的形式返回日期的指定部分，此部分由datepart来指定

Transact-SQL自定义函数

创建标量值函数：标量值函数的函数体由一条或多条SQL语句组成，这些语句以BEGIN开始，以END结束

CREATE FUNCTION function_name
([{@parameter_name [AS] parameter_data_type [=default] [READONLY]}[,...N]]
)
RETURNS return_data_type
[WITH ENCRYPTION]
[AS]
BEGIN
funtion_body
RETURN scalar_expression
END

例：自定义一个标量函数Fun1，判断一个整数是否为素数，如果为素数，则函数返回1，否则返回0，待判断的数通过参数传给函数

CREATE FUNCTION dbo.Fun1(@n AS INT)
RETURNS INT
AS
BEGINDECLARE @i INTDECLARE @sign INTSET @sign = 1SET @i = 2WHILE @i <= SQRT(@n)BEGINIF @n % @i = 0BEGINSET @sign = 0BREAKENDSET @i = @i + 1ENDRETURN @sign
END

创建内联表值函数

CREATE FUNCTION function_name
([{@parameter_name [AS] parameter_data_type [=default][Readonly]}[,...n]]
)
RETURNS Table
[With Encryption]
[As]
RETURN (select_statement)

例：

CREATE FUNTION dbo.Fun2()
RETURN TABLE
AS
RETURN SELECT SNo, SN FROM S

多语句表值函数
与内敛表值函数不同的是，多语句表值函数在返回语句之前还有其他的Transact-SQL语句

CREATE FUNCTION function_name
([{@parameter_name [AS] parameter_data_type [=default] [Readonly]}[,...n]]
)
RETURNS Table
[With Encryption]
[As]
RETURN @return_variable Table <table_type_definition>
[With Encryption]
[As]
BEGIN
funcion_body
RETUEN 
END

例：

CREATE FUNCTION Score_Table
(@student_id CHAR(6))
RETURNS @T_score TABLE
(Cname VARCHAR(20),Grade INT)
AS 
BEGININSERT INTO @T_scoreSELECT CN, ScoreFROM SC, CWHERE SC.CNo = C.CNo and SC.SNo = @student_id and Score < 60RETURN
END

存储过程

创建存储过程

CREATE PROCEDURE procedure_name [; number]
[{@parameter data_type}[VARYING][=default][OUTPUT]][,...N][WITH{RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS Sql_statement [,...n]

例：在Teach数据库中，创建一个名称为MyProc的不带参数的存储过程，该过程的功能是从数据表S中查询所有男同学的信息

USE Teach
GO
CREATE PROCEDURE MyProc AS
SELECT * FROM S WHERE Sex = '男'

例：定义具有参数的存储过程。在Teach数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向S数据表中插入一条记录，新纪录的值由参数提供

USE Teach
GO
CREATE PROCEDURE InsertRecord
(   @SNo VARCHAR(6)@Sn NVARCHAR(10)@Sex NCHAR(1)@Age INT@Dept NVARCHAR(20))
AS
INSERT INTO S VALUES (@SNo, @Sn, @Sex, @Age, @Dept)

例：定义具有参数默认值的存储过程。在Teach数据库中，创建个名称为Insert RecordDefa的存储过程，该存储过程的功能是向S数据表中插入一条记录，新纪录的值由参数提供，如果未提供系别Dept的值时，由参数的默认值代替

USE Teach
GO
CREATE PROCEDURE InsertRecordDefa
(   @SNo VARCHAR(6)@Sn NVARCHAR(10)@Sex NCHAR(1)@Age INT@Dept NVARCHAR(20) = '无')
AS 
INSERT INTO S VALUES (@SNo, @Sn, @Sex, @Age, @Dept)

例：定义能够返回值的存储过程。在Teach数据库中，创建一个名称为QueryTeach的存储过程。该存储过程的功能是从数据表S中根据学号查询某一同学的姓名和系别，查询的结果由参数@Sn和@Dept返回

USE Teach
GO
CREATE ATE PROCEDURE QueryTeach
(   @SNo VARCHAR(6),@Sn NVARCHAR(10) OUTPUT@Dept NVARCHAR(20) OUTPUT)
AS
SELECT @Sn = SN, @Dept = Dept
FROM S WHERE SNo = @SNo

查看存储过程
例：查看数据库Teach中存储过程MyProc的源代码

USE Teach
GO
EXEC Sp_Helptext MyProc

删除存储过程

DROP PROCEDURE {procedure} [,...n]

执行存储过程
例：执行数据库Teach中一定义不带参数的存储过程MyProc

USE Teach
GO
EXEC MyProc

修改存储过程

ALTER PROCEDURE procedure_name [; number]

触发器

DML触发器：在执行数据操作语言(DML)事件时被激活而自动执行的触发器
DDL触发器：在执行数据定义语言(DDL)事件时被激活而自动执行的触发器
登录触发器：由登录(LOGON)事件而激活的触发器

创建触发器

创建DML触发器

CREATE TRIGGER trigger_name
ON {table | view}
[WITH ENERYPTION]
{FOR | AFTER | INSTEAD OF}
{[INSERT] [,] [UPDATE] [DELETE]}
AS SQL_Statement [;]

例：设计一个触发器，在学生表中删除某个学生时，在选课表SC中该学生的选课记录也全部删除

USE Teach
GO
CREATE TRIGGER del_S
ON S
AFTER DELETE
AS
DELETE FROM SC WHERE SC.SNo 
IN (SELECT SNo FROM DELECTED)
GO

创建DDL触发器

CREATE TRIGGER trigger_name
ON {ALL SERVER | DATABASE}
[WITH ENCRYPTION]
{FOR | AFTER} {event_type | event_group} [,...n]
AS SQL_Statement [;]

例：创建一个DDL触发器safety，禁止修改和删除数据库中的任何表

USE TEACH
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, FLTER_TABLE
AS PRINT'不能修改或删除数据库表！'
ROLLBACK
GO

查看触发器

EXEC Sp_Helptrigger 'table' [.'type']EXEC Sp_HelpText 'trigger_name'

修改触发器

修改DML触发器

ALTER TRIGGER schema_name.trigger_name
ON (table | view)
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF}
{[DELECT] [,] [INSERT] [,] [UPDATE]}
AS SQL_Statement [;]

修改DDL触发器

ALTER TRIGGER trigger_name
ON{ALL SERVER | DATABASE}
[WITH ENCRYPTION]
{FOR | AFTER} {event_type | event_group} [,...n]
AS SQL_Statement [;]

触发器无效\有效

-- 无效
DISABLE TRIGGER{[schema.] trigger_name [,...n]
| ALL}
ON object_name--有效
ENABLE TRIGGER{[schema.] trigger_name [,...n]
| ALL}
ON object_name

删除触发器

DROP TRIGGER trigger_name [,...n] [;]

备份与还原

创建备份

sp_addumpdevice [@devtype =] 'device_type',
[@logicalname = ]'logical_name',
[@physicalname = ]'physical_name'

例：为数据库Teach创建一个磁盘备份设备

USE Teach
GO 
EXEC Sp_Addumpdevice
'disk','pubss','C:\backdev\backdevpubs.bak'

删除备份

sp_dropdevice [@logicalname = ] 'device' [, [@delfile = ] 'delfile']

例：删除数据库Teach中创建的备份设备pubss

USE Teach
GO
EXEC sp_dropdevice 'pubss','C:\backdev\backdevpubs.bak'