当前位置: 代码迷 >> 综合 >> Transact-SQL变量基本语法
  详细解决方案

Transact-SQL变量基本语法

热度:23   发布时间:2024-02-24 16:56:12.0

作者: 18届 KDB

日期: 2020-10-2

Transact-SQL变量基本语法

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

Transact-SQL变量

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

常用全局变量

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

  1. 算数运算符
运算符 含义
+
-
*
/
% 求余数
  1. 比较运算符
运算符 含义
= 等于
> 大于
< 小于
>= 大于或等于
<= 小于或等于
<> 不等于
!= 不等于(非SQL-92标准)
!< 不小于(非SQL-92标准)
!> 不大于(非SQL-92标准)
  1. 逻辑运算符
运算符 含义
ALL 如果一组比较中都为TRUE,运算结果就为TRUE
AND 如果两个表达式都为TRUE,运算结果就为TRUE
ANY 如果一组的比较中任何一个为TRUE,运算结果就为TRUE
BETWEEN 如果操作数在每个范围之内,运算结果就为TRUE
EXISTS 如果子查询包含一些行,运算结果就为TRUE
IN 如果操作数等于表达式列表中的一个,运算结果就为TRUE
LIKE 如果操作数与一种模式匹配,运算结果为TRUE
NOT 对逻辑值取反
OR 如果两个两个布尔值表达式中的一个为TRUE,运算结果就为TRUE
SOME 如果一系列操作数中,有些值为TRUE,运算结果为TRUE
  1. 换位运算符
运算符 含义
& 按位与
| 按位或
^ 按位异或

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语句
  1. BEGIN…END语句
-- BEGIN...END语句经常在条件语句和循环语句中使用
BEGIN<命令行或程序块>
END
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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函数

  1. 统计函数
  • STDEV函数:STDEV函数返回表达式中所有数据的标准差
  • STDEVP函数:STDEVP函数返回值表达式中所有数据的总体标准差
  1. 算数函数
  • 三角函数:SIN、COS、TAN、COT…
  • 角度弧度转换:DEGREES(把弧度转换为角度)、RADIANS(把角度转换为弧度)
  • 指数函数:EXP(表达式)
  • 对数函数:LOG(表达式)、LOG10(表达式)
  • 平方根函数:SQRT(表达式)
  • 近似取整函数:CEILING(表达式)向上取整、FLOOR(表达式)向下取整、ROUND(表达式, n)四舍五入取整,精度为n
  • 符号函数:ABS(表达式)返回表达式的绝对值、SIGN(表达式)测试表达式的正负号,返回0,1,-1
  • 其他函数:PI()返回圆周率、PAND()0-1之间随机浮点数
  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):返回字符串中某个指定的子串出现的开始位置
  1. 日期函数
  • 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自定义函数

  1. 创建标量值函数:标量值函数的函数体由一条或多条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
  1. 创建内联表值函数
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
  1. 多语句表值函数
    与内敛表值函数不同的是,多语句表值函数在返回语句之前还有其他的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

存储过程

  1. 创建存储过程
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
  1. 查看存储过程
    例: 查看数据库Teach中存储过程MyProc的源代码
USE Teach
GO
EXEC Sp_Helptext MyProc
  1. 删除存储过程
DROP PROCEDURE {procedure} [,...n]
  1. 执行存储过程
    例: 执行数据库Teach中一定义不带参数的存储过程MyProc
USE Teach
GO
EXEC MyProc
  1. 修改存储过程
ALTER PROCEDURE procedure_name [; number]

触发器

  • DML触发器:在执行数据操作语言(DML)事件时被激活而自动执行的触发器
  • DDL触发器:在执行数据定义语言(DDL)事件时被激活而自动执行的触发器
  • 登录触发器:由登录(LOGON)事件而激活的触发器
  1. 创建触发器
  • 创建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
  1. 查看触发器
EXEC Sp_Helptrigger 'table' [.'type']EXEC Sp_HelpText 'trigger_name'
  1. 修改触发器
  • 修改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 [;]
  1. 触发器无效\有效
-- 无效
DISABLE TRIGGER{[schema.] trigger_name [,...n]
| ALL}
ON object_name--有效
ENABLE TRIGGER{[schema.] trigger_name [,...n]
| ALL}
ON object_name
  1. 删除触发器
DROP TRIGGER trigger_name [,...n] [;]

备份与还原

  1. 创建备份
sp_addumpdevice [@devtype =] 'device_type',
[@logicalname = ]'logical_name',
[@physicalname = ]'physical_name'

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

USE Teach
GO 
EXEC Sp_Addumpdevice
'disk','pubss','C:\backdev\backdevpubs.bak'
  1. 删除备份
sp_dropdevice [@logicalname = ] 'device' [, [@delfile = ] 'delfile']

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

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