TSQL编程
T-SQL(Transact-SQL)是一种 SQL 扩展语言,由微软实现,运行在 Ms SQL Server 平台上。T-SQL 主要用来和SQL Server 交流,而查询语句则主要用来告诉服务器该做什么。T-SQL 是标准 SQL 语言的扩展,自然也继承了其基本功能:DDL、DML,DCL,DQL。另外,T-SQL 扩展了标准 SQL 不具备的编程特性,比如:运算符、文本字符串处理、流程控制、存储过程、API,自定义函数等。
T-SQL(Transact-SQL)是一种 SQL 扩展语言,由微软实现,运行在 Ms SQL Server 平台上。T-SQL 主要用来和SQL Server 交流,而查询语句则主要用来告诉服务器该做什么。T-SQL 是标准 SQL 语言的扩展,自然也继承了其基本功能:DDL、DML,DCL,DQL。另外,T-SQL 扩展了标准 SQL 不具备的编程特性,比如:运算符、文本字符串处理、流程控制、存储过程、API,自定义函数等。
一 T-SQL是什么
T-SQL 并没有被作为一种编程语言设计。虽然 T-SQL 经过多年的发展,已经加入了不少编程语言的特性,但其仍然缺少真正的编程语言所具备的能力和灵活性。
T-SQL 被设计的目的是实现数据的检索和操纵,虽然其具有一定的编程能力,但其性能不能被很好的得到保证,当你希望把 T-SQL 当做编程语言使用时,你会不可避免的遇到性能问题,所以,在使用它时,请在心里牢记,T-SQL 是操纵数据集的,这才是它发挥真正作用的地方。
二 编程特性
1,语法约束
T-SQL 命令不区分大小写,但建议使用大写。语句不强制使用 ; 结束。
1SELECT*FROM TableName --可以运行2SELECT*FROM TableName;--也能运行
注意缩进和对象命名规范。正确的缩进能保证你的代码易于阅读和维护。T-SQL 允许使用字母、数字、下划线、@、#、$符号来创建你自己的标识符(如变量、表名、视图名等),但首字母不能是数字和 $ 符号,建议遵循大驼峰命名法则。
1CREATETABLE MyTable2(3 M_Name VARCAHR(20) NOTNULL,4 ......5 )
T-SQL 同时支持双引号和单引号,那么如何区分呢?双引号一般用来标识 T-SQL 对象,比如表、视图,过程等,还有一种表示对象的方式是 [] ,而单引号用来表示字符串数据。
1SELECT "M".*FROM "MyTable" AS "M"2 WHERE "M".Name="张三";3SELECT "M".*FROM[MyTable];
T-SQL 使用 -- 双中横线表示单行注释,/* */ 表示多行注释。
1/*2这是多行注释
3这是多行注释
4*/
5--这是单行注释
2,运算符
数学运算符:+ 加、- 减、* 乘、/ 除、% 余。数学运算符返回值时数学计算值。= 既可以是等号也可以是赋值。+ 既可以作为数学加号也可以作为连接运算符。
比较运算符:> 大于、< 小于、>= 大于或等于、<= 小于或等于、<> 不等于、!= 不等于、!< 不小于、!> 不大于,比较运算返回的是布尔值。
逻辑运算符:AND、OR、NOT、BETWEEN、LIKE、IN。
3,变量
变量分为局部变量和全局变量。局部变量用户可以自定义,而全局变量由系统管理,用户可以使用,但不能更改。
局部变量使用 DECLARE 关键字声明,以 @ 符号标识。使用 SELECT 或 SET 关键之赋值。
1DECLARE@AgeINT2DECLARE@Name VARCAHR(20)
3SET@Age=20
4SELECT@Name="张三"
5PRINT@Name+@Age
SET 一次只能为一个变量赋值,SELECT 一次可以为多个变量赋值。在 T-SQL 中,有两种方式输出内容,PRINT 和 SELECT。PRINT 一次只能输出一个值,通常用于向 API 返回值,而 SELECT 可以以数据集的形式返回多行记录。
全局变量以 @@ 符号标识,常用的全局变量如下:
1SELECT@@versionAS"版本";--返回当前数据库的版本信息2SELECT@@errorAS"错误ID";--返回上一次T-SQL的错误ID,如果正常执行了查询,error为0,出错时error一定大于0
3SELECT@@identityAS"标示符";--返回最后一次的标识符,如先执行了
4SELECT@@connectionsAS"连接次数";--返回自上次SQL启动以来连接或试图连接的次数
5SELECT@@total_errorsAS"错误总数";--返回至启动以来的错误总数
6SELECT@@total_readAS"读取总数";--返回自启动以来的读取总数
7SELECT@@total_writeAS"写入总数";--返回自启动以来的写入总数
8 ......
4,流程控制
BEGIN...END:该语句用来标记一个语句块,通常和其他流程控制语句一起使用。
IF...ELSE:条件判断语句。如果 IF 关键字后面的条件表达式计算结果为真,则执行语句块1,否者执行语句块2。IF 和 ELSE 之间还可以存在 ELSE IF 组合关键字,表示逻辑上的其他情况。
1IF 条件2BEGIN3 语句块1
4END
5ELSE
6BEGIN
7 语句块2
8END
如果语句块只有一条语句,那么BEGIN...END 可以省略。
WHILE:循环。当 WHILE 关键字后面的循坏条件为真时,执行下面的语句块,需要注意的是,语句块中应该有能够左右循坏条件的语句存在,否则这将变成一个死循环。
1WHILE 循环条件2BEGIN3 语句块
4END
5--如果只有一条语句,也可以省略BEGIN...END
BREAK 和 CONTINUE:退出循环。该关键字一般和循环配合使用,BREAK 用于结束整个循环,不管循环条件是否为真。CONTINUE 用于跳过本次循环需要执行的代码快,直接开始执行下一次需要执行的代码块(前提是循环条件还为真)。
WAITFOR:延时执行。
1BEGIN2WAITFOR TIME "22:00"
3 语句块
4END
5--指定执行语句的具体时间
6BEGIN
7WAITFOR DELAY "01:00:00"
8 语句块
9END
10--指定执行语句的延迟时间量
CASE:基于列的计算返回指定的值。CASE 执行的逻辑和 IF ELSE 语句类似,当 WHEN 关键字后面的表达式结果为真时,用 THEN 后面的新值替换列中原来的值。
1CASE 列2WHEN 表达式 THEN 新的值或表达式
3WHEN 表达式 THEN 新的值或表达式4 ......5ELSE 其他未指定匹配值或表达式的新值 6END
三 函数
1,聚合函数
聚合函数主要包括:SUM() 求和,AVG() 求平均值,MIN() 求最小值,MAX() 求最大值,COUNT() 计数。
以上聚合函数和标准 SQL 中的聚合函数功能一样,使用方式也一样,这里不再介绍,如有疑问可以参照我的《SQL入门》。
2,日期函数
T-SQL 提供了功能强大的操作日期类型值的相关函数,通过这些函数你可以轻松实现比如,解析日期类型值的日期与时间部分,比较与操纵日期/时间值等。
A:GETDATE() 和 GETUTCDATE()
1SELECTGETDATE() AS"标准时间"2SELECT GETUTCDATE() AS"UTC时间"
GETUTCDATE() 通过本地服务器上的时区来求出 UTC 时间,一般使用前一个函数较多。
B:DATEPART() 和 DATENAME()
这两个函数的作用类似,都是用于返回日期中指定的部分,不通电在于:DATEPART() 返回值类型为 INT,而DATENAME() 为 NVARCHAR。
1DECLARE@MyDate; 2SET@MyDate=GETDATE(); 3PRINTDATEPART(year,@mydate);--int4PRINTDATENAME(year,@mydate);--nvarcahr
5PRINTDATEPART(month,@mydate);--int
6PRINTDATENAME(month,@mydate);--nvarcahr
7PRINTDATEPART(day,@mydate);--int
8PRINTDATENAME(day,@mydate);--nvarcahr
9PRINTDATEPART(dayofyear,@mydate);--int
10PRINTDATENAME(dayofyear,@mydate);--nvarcahr
11--可选的参数还有 week,weekday,hour,minute,second,millisecond等,请自行测试返回值
C:YEAR(),MONTH(),DAY()
以 INT 类型值返回指定日期的年,月,日。
1SELECTYEAR(MyDate)ASYEAR,MONTH(MyDate)ASMONTH,DAY(MyDate)ASDAY
D:DATEADD() 和 DATEDIFF()
DATEADD() 用于计算给定时间间隔后的日期,DATEDIFF() 用于计算两个日期之间指定单位的时间差。
1DECLARE@MyDateDATETIME;2DECLARE@DateAfter7DaysDATETIME;3SET@MyDate=GETDATE();4SET@DateAfter7Days=DATEADD(day,7,@MyDate) ;--计算距今7天之后的日期5PRINT@DateAfter7Days;
6PRINTDATEDIFF(day,@MyDate,@DateAfter7Days) ;--7,差7天
7--第一个参数都是时间单位,可选的有:year,month,day,week,hour,minute,second等,DATEADD()的第二个参数是一个数字,可以为负,以为之前的日期
3,数学函数
T-SQL 数学专门用于数学计算,常用的数学函数列表请移步《T-SQL 数学函数》,这里不举例说明。
4,字符函数
1SELECTASCII("ABC");--计算字符串第一个字符的ASCII值2SELECTCHAR(65);--把给定ASCII编码转换成字符
3
4SELECTLOWER("AbC");--全转换为小写
5SELECTUPPER("aBc");--全转换为大写
6
7SELECTLTRIM(" AAA");--去掉左边的空格
8SELECTRTRIM("AAA ");--去掉右边的空格
9
10SELECTLEN(" ABC ");--计算字符个数,不包含后面的空格
11SELECTLEFT("ABCDEFG",3);--从左边返回指定个数的字符
12SELECTRIGHT("ABCDEFG",3);--从右边返回指定个数的字符
13SELECTSUBSTRING("ABCDEFG",3,2);--从第三个字符开始返回2个字符
14
15SELECTCHARINDEX("A", "CCBBAA"); --返回第一个参数字符串在指定字符串中的位置
16SELECTREPLACE("AABBCC","A","D"); --用第三个参数替换第一个参数中的第二个参数所指定字符串
5,类型转换函数
T-SQL 中的显示类型转换通过 CONVERT() 和 CAST() 实现。
1SELECTCONVERT(INT," 123 ");2SELECTCAST(" 123 "ASINT);3--把字符串转换成数字,可以有空格,但不能有其他字符4
5SELECTCONVERT(DATE,"2020-01-01");
6SELECTCAST("2020-01-01"AS DATE);
7--把字符串表示的日期转换为日期格式
CONVERT() 另一个很重要的应用是:以不同的格式显示日期。这是你需要传入第三个参数,该参数用来表示格式日期:
1SELECTCONVERT(VARCHAR(19),GETDATE());--04 27 2020 11:10AM2SELECTCONVERT(VARCHAR(10),GETDATE(),110); --04-27-2020
3SELECTCONVERT(VARCHAR(11),GETDATE(),106);--27 04 2020
4SELECTCONVERT(VARCHAR(24),GETDATE(),113);--27 04 2020 11:11:07:857
6,排序
ROW_NUMBER() OVER():通过在 OVER 中使用 ORDER BY 字句,对指定列排序,并生成一个标识该行的唯一序号(从1开始)。比如有如学生生源信息表 Person:
id
name
prov
1001
张一
四川
1002
张二
湖北
1003
张三
上海
1004
张四
北京
1005
张五
四川
1SELECT ROW_NUMBER() OVER(ORDERBY ID) AS NUM,*FROM Person; --多了一列 NUM,值从1 - 4
1SELECT ROW_NUMBER() OVER(PARTITION BY PROV ORDERBY ID) AS NUM,*FROM Person;2-- 先通过 prov 分组学生信息,然后再通过 id 对学生在组内排序,生成 num 列,张一的 num 为1,张五的 num 为 2,其他的均为1
RANK() OVER():排序和 ROW_NUMBER() OVER() 相似,区别是它不能分组排序,并且它的排序结果可能会出现相同的序号,且整体可能会不连续。
如果某些行的的值相同,那么 RANK() OVER() 会为这些行给出相同的序号,并且,下一行的排序并不会和上一个相邻。
比如:前两个学生的成绩都是 100 分,那么他们应该是并列第一名,排序都是 1,第三名学生的成绩是 99,应该是第二名,但 RANK() OVER() 的排序会是 3 。
四 编程对象
1,视图
设计视图的唯一目的就是简化代码,解决代码重用问题。
视图是一个逻辑表,它和真正的表在使用上完全一致,但他不是一个真正的表,视图的本质是一个复杂的查询语句。查询视图会返回数据,但这些数据并不是存储在视图中。而是在具体的真实的表中。
语法:
1CREATEVIEW VIEW_NAME 2AS3语句
4--创建视图
5ALTERVIEW VIEW_NAME
6AS
7语句
8--修改视图
9DROPVIEW VIEW_NAME
10--删除视图
创建视图时可以在视图名后使用()来为视图的列指定新的名称,但必须全部提供,不能只指定一部分列的新名称。
虽然视图也可以用来对原始数据进行操作,但不建议这样做,因为使用视图简化查询才是它的本职工作。
使用视图的一个注意点:不能直接使用 order by,如果需要排序,则必须配合 TOP 关键字一起使用。
2,存储过程
存储过程不仅可以实现返回查询数据集的功能,而且功能比视图更进一步,它还提供了很多编程功能,比如:带参数的视图,返回标量值,维护记录,处理业务逻辑等等。
一个存储过程实现了一个特定的功能,并且别SQL Server 编译好后存储在数据库中,下一次执行不需要重新编译,提高程序执行效率。
先来看基本语法:
1CREATEPROCEDURE PROC_NAME 2AS3语句
4--创建存储过程
5ALTERPROCEDURE PROC_NAME
6AS
7语句
8--修改存储过程
9DROP PROCDURE PROC_NAME
10--删除存储过程
11EXECUTE PROC_NAME
12--执行存储过程
A:带参数的存储过程
1CREATEPROCEDURE Proc_name2@Var_name3AS
4SELECT*FROM Table_name
5WHERE Some_col =@Var_name
6--在存储过程中,使用变量过滤数据,变量不需要使用 declare 关键字
7EXECUTE Proc_name Something;
8EXECUTE Proc_name @Var_name=Something;
9--两种传参的方式,多个参数使用逗号隔开
B:返回值
从存储过程返回值有两种方式,OUTPUT 修饰符和 RETURN 关键字。
先来看 OUTPUT 方式:
1CREATEPROCEDURE P_TEST 2@OUTINT OUTPUT 3AS4SET@OUT=1;
5GO
6DECLARE@NUMINT;
7EXECUTE P_TEST @NUM OUTPUT;
8SELECT@NUM;
9GO
10--GO 表示一批 T-SQL 语句结束,GO 之后的 T-SQL 语句属于另一个批处理的范围,GO 不是 T-SQL 命令,它只是一个能被 SQL Server 管理器识别的命令
使用这种方式,在创建存储过程时,需要定义一个带有 OUTPUT 修饰符的参数,用于存储即将被返回的值。在存储过程的外部,也需要定义一个变量,用来接收返回的值。并且在执行存储过程时,需要把接收值的变量传递到存储过程中去,且必须指明修饰符 OUTPUT,否则,存储过程虽然能正常执行,但不会返回任何数据。
另一种 RETURN 方式:
1ALTERPROCEDURE P_TEST2AS3RETURN1;
4GO
5DECLARE@NUMINT;
6EXECUTE@NUM= P_TEST;
7SELECT@NUM;
8GO
这种方式使用 RETURN 关键字显示的指定需要返回的值,但与 OUTPUT 不同的是,它只能返回 INT类型的值。这种方式使用起来更简单,不许要定义额外的变量,只需要在外部定义一个接收数据的变量,并在执行时赋值,即可拿到存储过程的返回值。
RETURN 还有一个功能:结束存储过程的执行。即执行完 RETURN 语句之后,后面的任何语句都不会再被执行了,存储过程的执行到此结束。
3,自定义函数
和存储过程很相似,用户自定义函数也是一组有序的T-SQL语句,用户自定义函数被预先优化和编译并且作为一个单元进行调用。它和存储过程的主要区别在于返回结果的方式。
用户自定义函数可以传入参数,单传出参数被返回值概念替代了。用户自定义函数的返回值可以是普通的标量值,也可以是表。
1CREATEFUNCTION FUN_NAME2( 参数列表 )3RETURNS 数据类型4AS5BEGIN
6 语句块
7END
使用 CREATE FUNCTION 创建用户自定义函数,函数名后面用()定义传入的参数,然后使用 RETURNS 定义函数返回值的数据类型,用户自定义函数的语句块必须包含在 BEGIN...END中,并且在自定义函数内部,不能调用非确定性的函数,比如 GETDATE()。这是因为如果在内部出现非确定性函数,可能导致自定义函数在参数相同的情况下而返回值不同。请看下面的例子:
1CREATEFUNCTION GetAge2 (@Birthday DATE,@Today DATE)3AS4BEGIN
5RETURNDATEDIFF(DAY,@Birthday,@Today)/365
6END
7--根据提供的生日和现在的日期,计算年龄
8SELECT DBO.GetAge("2000-01-01",GETDATE());
通常情况下,SQL SERVER 把没指定所有者或模式的函数调用当做一个系统内置函数,所以在使用用户自定义函数时,请至少要指定所有者或模式名。
如果要使用用户自定义函数返回表类型的值,你需要这样做:
1CREATEFUNCTION MYFUN(@VAR 数据类型) 2RETURNSTABLE3AS
4BEGIN
5RETURN
6 (
7SELECT*FROM TABLENAME WHERE 使用@VAR 的条件表达式;
8 )
9END
10
11SELECT*FROM MYFUN(参数值);
使用 SELECT * FROM 函数名这样的语法,那么这个函数基本和表具有一样的功能。
关于 T-SQL 的内容就整理了这么多,希望对大家有用,如果发现有什么的不对的地方,欢迎留言。
以上是 TSQL编程 的全部内容, 来源链接: utcz.com/z/533355.html