作者: 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') >= 60
PRINT 'pass'
ELSE
PRINT '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 = '存在记录'
ELSE
SET @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 sex
WHEN '男' 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)
BEGIN
IF (@i % 3 = 0)
BEGIN
SET @s = @s + @i
SET @nums = @nums + 1
END
SET @i = @i + 1
END
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)
BEGIN
SET @s = @s + @i
SET @i = @i + 1
GOTO BEG
END
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
BEGIN
DECLARE @i INT
DECLARE @sign INT
SET @sign = 1
SET @i = 2
WHILE @i <= SQRT(@n)
BEGIN
IF @n % @i = 0
BEGIN
SET @sign = 0
BREAK
END
SET @i = @i + 1
END
RETURN @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
BEGIN
INSERT INTO @T_score
SELECT CN, Score
FROM SC, C
WHERE SC.CNo = C.CNo and SC.SNo = @student_id and Score < 60
RETURN
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'