Transact-SQL语句概述讲解
transact- sql概述
了解
sql 结构化查询语言 transact-sql语言是sql 的增强版本
种类
数据
定义 操纵 控制 常用
事务管理 流程控制 附加的语言元素
常量与变量
常量
常量指具体的值，不需要声明
变量
分类
全局 局部
局部变量声明
格式
declare
{@local_variablep[as]data_type
|@cursor_variable_namecursor
}[,…n]
给声明的局部变量赋值
set@local_variable=expression select@local_variable=expression[,…n]
运算符
算数运算符
| 运算符 | 说明 |
|---|---|
| + | 加法运算 |
| — | 减法运算 |
| * | 乘法运算 |
| / | 除法运算，若两个表达式都是整数，则结果取整数值，小数值将略去 |
| % | 取模运算，返回两数相除后的余数 |
位运算符
| 运算符 | 说明 |
|---|---|
| & | 位与逻辑运算 |
| l | 位或逻辑运算 |
| ^ | 位异或逻辑运算 |
比较运算符
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| > | 大于 |
| < | 小于 |
= |
大于或等于 |
| <= | 小于或等于 |
| <> | 不等于 |
| != | 不等于 |
| !> | 不大于 |
| !< | 不小于 |
逻辑运算符
| 运算符 | 说明 |
|---|---|
| all | 如果一组的比较都为true，则结果为true |
| and | 都为true，则为true，否则为false |
| any | 任何一个为true，则为true |
| between | 操作数在某个范围，则为true |
| exists | 若子查询中包含某些行，则为true |
| in | 若操作数中等于表达式中的一个，则为true |
| like | 操作数与某种模式相匹配，则为true |
| not | 对任何其他布尔运算符的结果值取反 |
| or | 任何一个为true，结果为true |
| some | 在一组比较中，有些比较为true，结果为true |
其他运算符
赋值运算符 连接运算符 一元运算符
|
| 运算符 | 描述 |
|---|---|
| + | 数值为正 |
| - | 数值为负 |
| ~ | 返回数字的逻辑非 |
表达式
表达式是一个或者多个值、运算符和 sql 函数的组合。每个表达式都有值，通过求值可以得到。
注释
目的
养成好的开发习惯 对代码进行说明诊断
内容
程序名称 作者名称 变量说明 代码更改日期 算法描述
添加
双联字符（单行注释）——“–” 正斜杠星号（多行注释）——“/…/”
流程控制语句
begin…end 语句块
语法格式
begin
{
sql_statement|statement_block
}
end
选择结构
if……else 语句
单条件使用
语法格式
if boolean_expression
{sql_statement| statement_block}
else
{sql_statement| statement_block}
嵌套使用
case语句
多个条件进行判断
语法格式
case input_expression when where_expression then result_expression [...n] [else else_result_expression] end
循环
while语句
语法格式
while boolean_expression
{sql_statement|statement_block}
[break]
{sql_statement|statement_block}
[continue]
{sql_statement|statement_block}
goto语句
作用
减少代码量
错误处理语句
try…catch语句
代码格式
begin try
{sql_statement|statement_block}
end try
begin catch
{sql_statement|statement_block}
end catch
waitfor延迟语句
语法格式
waitfor
{
delay time
|time time
}
函数
数学函数
| 函数 | 描述 |
|---|---|
| abs | 返回数值表达式的绝对值 |
| exp | 返回指定表达式以e为底的指数 |
| ceiling | 返回小于或等于数值表达式的最小整数 |
| floor | 返回小于或等于数值表达式的最大整数 |
| ln | 返回数值表达式的自然对数 |
| log | 返回数值表达式以10为底的对数 |
| power | 返回对数值表达式进行幂运算的结果 |
| round | 返回舍入到指定长度或精度的数值表达式 |
| sigh | 返回数值表达式的“+”“—”“0” |
| souare | 返回数值表达式的平方 |
| sqrt | 返回数值表达式的平方根 |
字符串函数
| 字符串函数 | 描述 |
|---|---|
| acsii | 返回指定ascii代码的字符 |
| char | 返回指定ascii代码的字符 |
| len | 返回指定字符串表达式的字符数，不包含尾随的空格 |
| left | 返回字符串中从左边开始指定个数的字符 |
| lower | 将大写字符数据转换为小写 |
| ltrim | 删除前导空格字符串 |
| replicate | 复制函数，以指定的次数重复字符表达式 |
| replace | 替换函数 |
| right | 返回字符串中从右边开始指定个数的字符 |
| rtrim | 删除尾随空格函数 |
| space | 空格函数，返回由重复的空格组成的字符串 |
| str | 数字向字符转换函数 |
| substring | 子串函数，返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分 |
| upper | 大写函数 |
聚合函数
| 常用聚合函数 | 作用 |
|---|---|
| avg | 平均数 |
| count | 结果的记录数 |
| max | 最大值 |
| min | 最小值 |
| sum | 列的数值的和 |
日期和时间函数
| 日期函数 | 描述 |
|---|---|
| dateadd | 返回给指定日期加上一个时间间隔后的新达特time值 |
| datadiff | 返回跨两个指定日期的日期边界数和时间边界数 |
| datename | 返回标识指定日期的指定日期部分的字符串 |
| datepart | 返回标识指定日期的指定日期部分的整数 |
| day | 返回一个整数，标识指定日期的天datepart部分 |
| getdate | 以datetime值得sql service标准内部格式返回当前日期还有时间 |
| getutcdate | 返回表示当前的utc时间 |
| month | 返回标识指定日期的“月”部分的整数 |
| year | 返回表示指定日期的年份的整数 |
标量值函数
创建语法格式
creat function function_name
（[{@parameter_name scalar_parameter_data_type[=default]}[,…n]]）
returens scalar return_data_typr
[with encryption]
[as]
begin
function_body
return scalar_expression
end
表值函数
语法格式
create function function_name
([{@parameter_name scalar_ parameter_data_type[= default]，...n]])
returns table
[with encryption]
[as]
return (select_statement)
系统与元数据函数
convert 数据类型转换 current_user 返回当前用户的名称 isdate 判断是否为有效日期 isnull 用一个指定替换值替换任意空值 isnumeric 判断输入是否为数值
游标
概述
数据访问机制 用户访问单独的数据行并操作
组成
游标结果集（由定义游标的select语句返回的行的集合） 游标位置（执行结果集中的某一行的指针）
特点
返回完整的结果集 允许定位在结果集的特定行 从结果集的当前位置检索若干行 支持对结果集中当前位置的行进行数据修改 可以为其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持 提供脚本、存储过程和触发器中使用的访问结果集中数据的t-sql语句
声明
语法格式
declare cursor_name[insensitive][scroll]cursor
forselect_statement
[for{read only|update[of column_name[,...n]]}]
操作游标
打开游标
open{ {[global]cursor_name}|cursor_variable_name}|cursor_name
检索游标
fetch
[[next|prior|first|last|absolute{n|@navr}|relative{n|@nvar}]from]
{{[global]cursor_name}|@cursor_variable_name}
[into@variable_name[,...n]]
关闭游标
close{{[global]cursor_name}|cursor_variable_name}
释放游标
deallocate{{[global]cursor_name}|cursor_variable_name}
判断游标提取状态
@fetch_status
- 取值
- 0——fetch语句失败
- -1——失败或者行不在结果集中
- -2——提取的行不存在
事务
作用
对语句进行封装语句
begin teansaction
commit transaction
rollback transaction
save transaction
事务模式
自动提交事务
每条单独的语句都是一个事务
显式事务
每个事务以begin transaction语句显式开始，以commit或rollback语句显式结束
隐式事务
在前一个事务完成时新事务隐式启动，以commit或rollback语句显式完成
批处理级事务
只应用多个活动结果集（mars）。在mars会话中启动的transact_sql显式或隐式事务变为批处理级事务
xact_about选项
作用
指定当sql语句错误是，sql service是否自动回滚到当前事务
语法格式
set xact_about{on|off}
当set xact_about为on，执行语句时产生错误，则事务终止并回滚 当set xact_about为off，回滚产生错误的语句，事务继续处理 设置是在执行或者运行时设置
嵌套事务
锁
概述
事务向系统提出需求，对被操作数据加锁 可以锁定行、页、表、盘区、数据库 确保在用户并发访问数据库的时候不受干扰
分类
共享（s）
读操作 多个事务科*一个共享单位的数据 任何事务都不能修改加s锁的数据 加s锁的数据读取完毕后，s锁立即被释放 独占（x）
写操作 仅允许一个事务*此共享数据 其他事务等到x锁被释放后才可以对该数据访问 x锁到事务结束后才被释放 更新（u）
用来预定要对此页施加x锁，允许其他事务读，但是不允许再施加u锁或者x锁 当被读取数据也将要被更新时，则升级为x锁 u锁一直到事务结束时才可以被释放
死锁
概述
指大于或等于两个的进程在执行时，因争夺资源造成的互相等待的现象 无外力作用，都将无法推进下去
产生死锁条件
互斥条件 请求与保持条件 不剥夺条件 循环等待条件
上述条件之一不满足，就不会产生死锁
解除与预防
按同一顺序访问对象 避免事务中的用户交互 保持事务简短并在一个批处理中 使用低隔离级别 使用绑定连接
建议
频繁使用表用集簇化索引 避免一次性影响大量记录的t-sql语句 让update和delete语句使用索引 使用嵌套事务是，避免提交和回退冲突 对一些数据不需要几十读取更新值得表在写sql的时候在表的后台加上(nolock)