T-SQL编程

　　　　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 命令不区分大小写，但建议使用大写。语句不强制使用 ； 结束。

1 select * from tablename --可以运行
2 select * from tablename;--也能运行

　　　　注意缩进和对象命名规范。正确的缩进能保证你的代码易于阅读和维护。t-sql 允许使用字母、数字、下划线、@、#、$符号来创建你自己的标识符（如变量、表名、视图名等），但首字母不能是数字和 $ 符号，建议遵循大驼峰命名法则。

1 create table mytable
2 (
3     m_name varcahr(20) not null,
4     ......
5 )

　　　　t-sql 同时支持双引号和单引号，那么如何区分呢？双引号一般用来标识 t-sql 对象，比如表、视图，过程等，还有一种表示对象的方式是 [] ，而单引号用来表示字符串数据。

1 select "m".*  from  "mytable" as "m"
2 　　where "m".name='张三';
3 select "m".*  from  [mytable];

　　　　t-sql 使用 -- 双中横线表示单行注释，/* */ 表示多行注释。

1 /* 
2 这是多行注释
3 这是多行注释
4  */
5 --这是单行注释

　　　　　　

　　　　2，运算符

　　　　数学运算符：+ 加、- 减、* 乘、/ 除、% 余。数学运算符返回值时数学计算值。= 既可以是等号也可以是赋值。+ 既可以作为数学加号也可以作为连接运算符。

　　　　比较运算符：> 大于、< 小于、>= 大于或等于、<= 小于或等于、<> 不等于、!= 不等于、!< 不小于、!> 不大于，比较运算返回的是布尔值。

　　　　逻辑运算符：and、or、not、between、like、in。

　　　　　　

　　　　3，变量

　　　　变量分为局部变量和全局变量。局部变量用户可以自定义，而全局变量由系统管理，用户可以使用，但不能更改。　

　　　　局部变量使用 declare 关键字声明，以 @ 符号标识。使用 select 或 set 关键之赋值。

1 declare @age int
2 declare @name varcahr(20)
3 set @age=20
4 select @name='张三'
5 print @name+@age

　　　　set 一次只能为一个变量赋值，select 一次可以为多个变量赋值。在 t-sql 中，有两种方式输出内容，print 和 select。print 一次只能输出一个值，通常用于向 api 返回值，而 select 可以以数据集的形式返回多行记录。

　　　　全局变量以 @@ 符号标识，常用的全局变量如下：

1 select @@version as '版本';--返回当前数据库的版本信息
2 select @@error as '错误id';--返回上一次t-sql的错误id，如果正常执行了查询，error为0，出错时error一定大于0
3 select @@identity as '标示符';--返回最后一次的标识符,如先执行了
4 select @@connections as '连接次数';--返回自上次sql启动以来连接或试图连接的次数
5 select @@total_errors as '错误总数';--返回至启动以来的错误总数
6 select @@total_read as '读取总数';--返回自启动以来的读取总数
7 select @@total_write as '写入总数';--返回自启动以来的写入总数
8  ......

 

　　　　4，流程控制

　　　　begin...end：该语句用来标记一个语句块，通常和其他流程控制语句一起使用。

　　　　if...else：条件判断语句。如果 if 关键字后面的条件表达式计算结果为真，则执行语句块1，否者执行语句块2。if 和 else 之间还可以存在 else if 组合关键字，表示逻辑上的其他情况。

1 if 条件
2     begin
3     语句块1
4     end
5 else
6     begin
7     语句块2
8     end    

　　　　如果语句块只有一条语句，那么begin...end 可以省略。

　　　　while：循环。当 while 关键字后面的循坏条件为真时，执行下面的语句块，需要注意的是，语句块中应该有能够左右循坏条件的语句存在，否则这将变成一个死循环。

1 while 循环条件
2 begin
3     语句块
4 end
5 --如果只有一条语句，也可以省略begin...end

　　　　break 和 continue：退出循环。该关键字一般和循环配合使用，break 用于结束整个循环，不管循环条件是否为真。continue 用于跳过本次循环需要执行的代码快，直接开始执行下一次需要执行的代码块（前提是循环条件还为真）。

　　　　waitfor：延时执行。

 1 begin
 2     waitfor time '22:00'
 3     语句块
 4 end
 5 --指定执行语句的具体时间
 6 begin
 7     waitfor delay '01:00:00'
 8     语句块
 9 end
10 --指定执行语句的延迟时间量

　　　　case：基于列的计算返回指定的值。case 执行的逻辑和 if else 语句类似，当 when 关键字后面的表达式结果为真时，用 then 后面的新值替换列中原来的值。

1 case 列
2     when 表达式 then 新的值或表达式
3     when 表达式 then 新的值或表达式
4     ......
5     else 其他未指定匹配值或表达式的新值 
6 end

 

　　　　三　　函数

 

　　　　1，聚合函数

　　　　聚合函数主要包括：sum() 求和，avg() 求平均值，min() 求最小值，max() 求最大值，count() 计数。

　　　　以上聚合函数和标准 sql 中的聚合函数功能一样，使用方式也一样，这里不再介绍，如有疑问可以参照我的《sql入门》。

　　　　　　

　　　　2，日期函数

　　　　t-sql 提供了功能强大的操作日期类型值的相关函数，通过这些函数你可以轻松实现比如，解析日期类型值的日期与时间部分，比较与操纵日期/时间值等。

　　　　a：getdate() 和 getutcdate()　　　　　

1 select getdate() as '标准时间'
2 select getutcdate() as 'utc时间'

　　　　getutcdate() 通过本地服务器上的时区来求出 utc 时间，一般使用前一个函数较多。

　　　　b：datepart() 和 datename()

　　　　这两个函数的作用类似，都是用于返回日期中指定的部分，不通电在于：datepart() 返回值类型为 int，而datename() 为 nvarchar。

 1 declare @mydate;
 2 set @mydate=getdate();
 3 print datepart(year,@mydate);--int
 4 print datename(year,@mydate);--nvarcahr
 5 print datepart(month,@mydate);--int
 6 print datename(month,@mydate);--nvarcahr
 7 print datepart(day,@mydate);--int
 8 print datename(day,@mydate);--nvarcahr
 9 print datepart(dayofyear,@mydate);--int
10 print datename(dayofyear,@mydate);--nvarcahr
11 --可选的参数还有 week,weekday,hour,minute,second,millisecond等，请自行测试返回值

　　　　c：year()，month()，day()

　　　　以 int 类型值返回指定日期的年，月，日。

1 select year(mydate)as year,month(mydate)as month,day(mydate)as day

　　　　d：dateadd() 和 datediff()

　　　　dateadd() 用于计算给定时间间隔后的日期，datediff() 用于计算两个日期之间指定单位的时间差。

1 declare @mydate datetime;
2 declare @dateafter7days datetime;
3 set @mydate=getdate();
4 set @dateafter7days=dateadd(day,7,@mydate) ;--计算距今7天之后的日期
5 print @dateafter7days;
6 print datediff(day,@mydate,@dateafter7days) ;--7，差7天
7 --第一个参数都是时间单位，可选的有：year,month,day,week,hour,minute,second等，dateadd()的第二个参数是一个数字，可以为负，以为之前的日期

 

　　　　3，数学函数

　　　　t-sql 数学专门用于数学计算，常用的数学函数列表请移步《t-sql 数学函数》，这里不举例说明。

　　　　　　

　　　　4，字符函数

 1 select ascii('abc');--计算字符串第一个字符的ascii值
 2 select char(65);--把给定ascii编码转换成字符
 3 
 4 select lower('abc');--全转换为小写
 5 select upper('abc');--全转换为大写
 6 
 7 select ltrim('   aaa');--去掉左边的空格
 8 select rtrim('aaa   ');--去掉右边的空格
 9 
10 select len('  abc  ');--计算字符个数，不包含后面的空格
11 select left('abcdefg',3);--从左边返回指定个数的字符
12 select right('abcdefg',3);--从右边返回指定个数的字符
13 select substring('abcdefg',3,2);--从第三个字符开始返回2个字符
14 
15 select charindex('a', 'ccbbaa'); --返回第一个参数字符串在指定字符串中的位置
16 select replace('aabbcc','a','d'); --用第三个参数替换第一个参数中的第二个参数所指定字符串

 

　　　　5，类型转换函数

　　　　t-sql 中的显示类型转换通过 convert() 和 cast() 实现。

1 select convert(int,' 123 ');
2 select cast(' 123 ' as int);
3 --把字符串转换成数字，可以有空格，但不能有其他字符
4 
5 select convert(date,'2020-01-01');
6 select cast('2020-01-01' as date);
7 --把字符串表示的日期转换为日期格式

　　　　convert() 另一个很重要的应用是：以不同的格式显示日期。这是你需要传入第三个参数，该参数用来表示格式日期：

1 select convert(varchar(19),getdate());--04 27 2020 11:10am
2 select convert(varchar(10),getdate(),110); --04-27-2020
3 select convert(varchar(11),getdate(),106);--27 04 2020
4 select convert(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	张五	四川

1 select row_number() over(order by id) as num,* from person; --多了一列 num，值从1 - 4

1 select row_number() over(partition by prov order by 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，视图

　　　　设计视图的唯一目的就是简化代码，解决代码重用问题。

　　　　视图是一个逻辑表，它和真正的表在使用上完全一致，但他不是一个真正的表，视图的本质是一个复杂的查询语句。查询视图会返回数据，但这些数据并不是存储在视图中。而是在具体的真实的表中。

　　　　语法：

 1 create view view_name
 2 as
 3     语句
 4 --创建视图
 5 alter view view_name
 6 as
 7     语句
 8 --修改视图
 9 drop view view_name 
10 --删除视图

　　　　创建视图时可以在视图名后使用（）来为视图的列指定新的名称，但必须全部提供，不能只指定一部分列的新名称。

　　　　虽然视图也可以用来对原始数据进行操作，但不建议这样做，因为使用视图简化查询才是它的本职工作。

　　　　使用视图的一个注意点：不能直接使用 order by，如果需要排序，则必须配合 top 关键字一起使用。

　　　　

　　　　2，存储过程

　　　　存储过程不仅可以实现返回查询数据集的功能，而且功能比视图更进一步，它还提供了很多编程功能，比如：带参数的视图，返回标量值，维护记录，处理业务逻辑等等。

　　　　一个存储过程实现了一个特定的功能，并且别sql server 编译好后存储在数据库中，下一次执行不需要重新编译，提高程序执行效率。

　　　　先来看基本语法：

 1 create procedure proc_name
 2 as
 3     语句
 4 --创建存储过程
 5 alter procedure proc_name
 6 as
 7     语句
 8 --修改存储过程
 9 drop procdure proc_name
10 --删除存储过程
11 execute proc_name
12 --执行存储过程

　　　　a：带参数的存储过程

1 create procedure proc_name
2 @var_name
3 as
4     select *  from table_name
5         where some_col = @var_name 
6 --在存储过程中，使用变量过滤数据，变量不需要使用 declare 关键字
7 execute proc_name something;
8 execute proc_name @var_name=something;
9 --两种传参的方式，多个参数使用逗号隔开

　　　　b：返回值

　　　　从存储过程返回值有两种方式，output 修饰符和 return 关键字。

　　　　先来看 output 方式：

 1 create procedure p_test
 2 @out int output
 3 as
 4     set @out=1;
 5 go
 6 declare @num int;
 7 execute p_test @num output;
 8 select @num;
 9 go
10 --go 表示一批 t-sql 语句结束，go 之后的 t-sql 语句属于另一个批处理的范围，go 不是 t-sql 命令，它只是一个能被 sql server 管理器识别的命令

　　　　使用这种方式，在创建存储过程时，需要定义一个带有 output 修饰符的参数，用于存储即将被返回的值。在存储过程的外部，也需要定义一个变量，用来接收返回的值。并且在执行存储过程时，需要把接收值的变量传递到存储过程中去，且必须指明修饰符 output，否则，存储过程虽然能正常执行，但不会返回任何数据。

　　　　另一种 return 方式：

1 alter procedure p_test
2 as
3     return 1;
4 go
5 declare @num int;
6 execute @num = p_test;
7 select @num;
8 go

　　　　这种方式使用 return 关键字显示的指定需要返回的值，但与 output 不同的是，它只能返回 int类型的值。这种方式使用起来更简单，不许要定义额外的变量，只需要在外部定义一个接收数据的变量，并在执行时赋值，即可拿到存储过程的返回值。

　　　　return 还有一个功能：结束存储过程的执行。即执行完 return 语句之后，后面的任何语句都不会再被执行了，存储过程的执行到此结束。

　　　　

　　　　3，自定义函数

　　　　和存储过程很相似，用户自定义函数也是一组有序的t-sql语句，用户自定义函数被预先优化和编译并且作为一个单元进行调用。它和存储过程的主要区别在于返回结果的方式。

　　　　用户自定义函数可以传入参数，单传出参数被返回值概念替代了。用户自定义函数的返回值可以是普通的标量值，也可以是表。

1 create function fun_name
2 ( 参数列表 )
3 returns 数据类型
4 as
5 begin
6     语句块
7 end

　　　　使用 create function 创建用户自定义函数，函数名后面用（）定义传入的参数，然后使用 returns 定义函数返回值的数据类型，用户自定义函数的语句块必须包含在 begin...end中，并且在自定义函数内部，不能调用非确定性的函数，比如 getdate()。这是因为如果在内部出现非确定性函数，可能导致自定义函数在参数相同的情况下而返回值不同。请看下面的例子：

1 create function getage
2 (@birthday date,@today date)
3 as
4 begin
5     return datediff(day,@birthday,@today)/365
6 end
7 --根据提供的生日和现在的日期，计算年龄
8 select dbo.getage('2000-01-01',getdate());

　　　　通常情况下，sql server 把没指定所有者或模式的函数调用当做一个系统内置函数，所以在使用用户自定义函数时，请至少要指定所有者或模式名。

　　　　如果要使用用户自定义函数返回表类型的值，你需要这样做：

 1 create function myfun(@var 数据类型)
 2 returns table
 3 as
 4 begin
 5     return
 6     (
 7         select * from tablename where 使用@var 的条件表达式;
 8     )
 9 end
10 
11 select * from myfun(参数值)；

　　　　使用 select * from 函数名这样的语法，那么这个函数基本和表具有一样的功能。

 

　　　　关于 t-sql 的内容就整理了这么多，希望对大家有用，如果发现有什么的不对的地方，欢迎留言。