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 CASE2     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 。

 

    7,自定义排序

     默认的排序方式在实际生产中难免有失灵活。

    比如在查询汽车品牌表时,如果通过常规 order by 通过品牌名称排序无非得到按字母数字正序或倒叙的结果,如果现在的要求是:1:Lamborghini,2:McLaren,3:Ferrari,4:Bugatti,5:Astom-Martin 这样的自定义顺序呢?

    在 SQL Server 中我们通过 case 语句来实现自定义排序。

 1 select * from car
 2 order by case car_brand
 3                 when 'Lamborghini' then 1
 4                 when 'McLaren' then 2
 5                 when 'Ferrai' then 3
 6                 when 'Bugatti' then 4
 7                 when 'Astom-Martin' 5
 8                 else 6
 9                 end
10 -- 如果还有没指定的值,将按默认顺序排在这5个品牌之后

 

 

  四  编程对象

 

    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 RETURNS INT
 4 AS
 5 BEGIN
 6     RETURN DATEDIFF(DAY,@Birthday,@Today)/365
 7 END
 8 GO
 9 --根据提供的生日和现在的日期,计算年龄
10 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 函数名 这样的语法,那么这个函数基本和表具有一样的功能。

 

  五  其他

 

    1,游标

    游标本质上是一个包含多条记录的结果集,保存在内存中。在 T-SQL 中,声明一个游标类型的变量,你就可以对读入的结果集进行管理,对每条记录进行迭代了。所以,游标实际上是一种能从包含多条数据记录的结果集中,每次提取一条记录的机制。

    游标基本用法:

 1 --1:创建游标
 2 DECLARE Cur_Name CURSOR
 3 FOR
 4     SELECT Col1,Col2 FROM Tbale_name
 5 DECLARE @Var_Name1,@Var_Name2
 6 --2:打开游标
 7 OPEN Cur_Name
 8 --3:检索与操作数据
 9 FETCH NEXT FROM Cur_Name INTO @Var _Name1,@Var_Name2
10 WHILE @@Fetch_Status=0
11 BEGIN
12     PRINT @Var _Name1+':'+@Var_Name2
13     FETCH NEXT FROM Cur_Name INTO @Var _Name1,@Var_Name2
14 END
15 --4:关闭游标
16 CLOSE Cur_Name
17 --5:释放游标
18 DEALLOCATE Cur_Name

    使用游标必要的五步:创建,打开,检索,关闭,释放。缺一不可。

    创建游标时,游标名和关键字 CURSOR 之间可以有可参数列表:INSENSITIVE,SCROLL,INSENSITIVE 表示生成一个结果集的副本,而不是使用数据库的元数据。SCROLL 指定在数据检索时,所有的提取选项(FIRST(第一条记录)、LAST(最后一条记录)、PRIOR(上一条记录)、NEXT(下一条记录)、RELATIVE(相对当前移动 n 行的记录)、ABSOLUTE(第 n 记录))均可用。一般到了使用游标的时候,都是需要对每一条数据进行不同的操作了,所以提取选项多数情况都是使用 NEXT。

    在 SELECT 查询语句之后,还可以通过 FOR 关键字指定哪些列可更新或者只读,语法规则:[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

    游标的主要作用在于,允许程序对由查询语句 SELECT 返回的行集合中的每一行执行相同或不同的操作,而不是对整个行集合执行同一个操作,并且它还提供对基于游标位置而对表中数据进行删除或更新的能力。

    使用游标虽然可以带来一定程度的灵活性,但它是以牺牲性能和效率为代价的,所以游标一般作为最后的开发选项,毕竟,即使不对数据做任何特殊操作,也需要必要的五个步骤。

    

    2,触发器

    触发器实际上是一类特殊的存储过程,它能被 DDL 和 DML 触发,并自动运行,不能被用户显式的触发。DML触发器中,insert 和 delete 一般对行进行操作,所以又被称为行级触发器,update 对列进行操作,所以又被称为列级触发器。

    如果我们为表或视图的某些操作设置触发器后,那么,当我们在数据库中针对这些表或视图执行这些操作时,触发器内的程序代码将被执行。

    基本语法(DML触发器):

1 CREATE TRIGGER 触发器名称
2 ON 表或视图
3 触发器类型(FOR|AFTER|INSTEAD OF4 操作类型(INSERTUPDATEDELETE5 AS
6     语句块

    基本语法(DDL触发器):

1 CREATE TRIGGER 触发器名称
2 ONALL SERVER|DATEBASE)
3 触发器类型(FOR|AFTER)
4 操作类型(CREATEALTERDROP...5 AS
6     语句块

    触发器类型 FOR 和 AFTER 都是指在操作成功后执行,区别是 AFTER 类型只支持表,而不支持视图。

    INSTEAD OF 触发器使用触发器内部的操作替代指定的操作类型,真正起作用的是触发器里面的动作!

    在触发器中,可以使用两个虚拟表:inserted和deleted,inserted用来存放插入或更新后的新纪录,deleted用来存放删除或更新前的旧记录。

 1 create trigger tgr_update
 2 on tablename
 3 for update
 4 as
 5     declare @old varchar(20), @new varchar(20);
 6     if update(col)
 7     --特定列更新触发
 8         begin
 9         --更新前的数据
10         select @old = col from deleted;
11         --更新后的数据
12         select @new = col from inserted;    
13         end

    

    3,其他的

    A:MERGE

    MERGE 命令的作用是根据与源表的联接结果在目标表上运行插入,更新或删除操作。比如,通过对比另一个表的差异,在一个表中插入、更新或删除数据,以保证两个表中的数据同步。

    一个简单的示例:

 1 MERGE INTO Target
 2  --这里是目标表,它将要被源表Merge
 3 USING Source
 4 --这里是源表
 5 ON 匹配条件
 6     WHEN MATCHED
 7 --匹配条件为真的记录,执行下面的then
 8         THEN UPDATE --执行更新操作 
 9     WHEN NOT MATCHED BY TARGET
10 --目标表中不存在,而源表中存在数据,执行下面的then
11         THEN INSERT--执行插入操作
12     WHEN NOT MATCHED BY SOURCE
13 --当目标表中存在,而源表中不存在数据执,执行下面then
14         THEN DELETE--执行删除操作

    MERGE 命令在许多时候都可以替代游标批量操作数据,下一次你在考虑使用游标时,不妨先想一想,能否用 MERGE 代替呢!

    B:临时表和表变量

    临时表与永久表相似,只是它的创建是在 Tempdb 系统库中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。

    临时表分为本地和全局两种,本地临时表的名称都是以“#”为前缀,只有在本地当前的用户连接中才是可见的,当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀,创建后对任何用户都是可见的,当所有引用该表的用户断开连接时被删除。

 1 --创建临时表
 2 --方式一
 3 CREATE TABLE #表名()
 4 CREATE TABLE ##表名()
 5 --方式二
 6 SELECT INTO #表名 FROM TABLE
 7 SELECT INTO ##表名 FROM TABLE
 8 --查询和删除
 9 SELECT * FROM #表名
10 DROP * FROM #表名

    临时表还可以和普通表一样添加索引,主键、外键和约束。

    表变量本质上还是变量,所以依然使用 DECLARE 关键字声明,既然是变量,那么也分为本地及全局的两种,本地表变量的名称都是以“@”为前缀,只有在本地当前的用户连接中才可以访问。全局表变量的名称都是以“@@”为前缀,和其他的系统全局变量一样。

1 DECLARE @News TABLE()

    对于表变量,可以正常执行 DML。表变量一般存储在内存中,并且不能添加索引和约束。

    很多时候表变量和临时表的功能是重合的,如何选取完全取决于应用对内存的压力,如果数据量比较大,可以使用临时表,否则推荐使用表变量。

    C:CTE

    共用表表达式 CTE ,你可以把它理解为一个临时的查询结果集,你可以在接下来的 SELECT、INSERT、UPDATE等语句中重复引用它。微软对其的用处评价颇高,说来惭愧,我在实际工作中,主要是为了在复杂子查询中减少代码冗余而使用它。

1 WITH CTEName (col1,col2)
2 AS
3 (
4     SELECT col1,col2 FROM TableName
5 )

    注意,定义的 CTE 只能在紧接着的语句中使用。

    D:EXISTS()

    EXISTS 用于检测是否有符合条件的数据存在,它是一种探测机制,它执行时不关注有多少符合条件的记录,只关注是否存在这样的记录。所以,多数时候可以使用 EXISTS 子查询替换 IN 以提高性能。与之相对是 NOT EXISTS(),用于判断是否不存在。

1 SELECT * FROM TableA A
2 WHERE EXISTS
3 (SELECT * FROM TableB B WHERE A.ID = B.ID )
4 --查询A表中那些ID和B表相同的数据

 

  

posted @ 2020-04-28 21:11  任风来去匆匆  阅读(1201)  评论(5编辑  收藏  举报