SQL高级篇（一）

纯属笔记记录------大部分内容来自公众号SQL数据库开发

数据库：SQL Servers

目录

1、临时表

2、变量

局部变量赋值

全局变量

3、CASE

---

1、临时表

临时表的定义：临时表与实体表相似，只是在使用过程中，临时表是存储在系统数据库tempdb中。当我们不再使用临时表的时候，临时表会自动删除。

临时表分类：临时表分为本地临时表和全局临时表，它们在名称、可见性以及可用性上有区别。

临时表的特性：

   1）本地临时表就是用户在创建表的时候添加了“#”前缀的表，其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限，其他连接不能访问该表。

   2）不同的数据库连接中，创建的本地临时表虽然“名字”相同，但这些表之间相互并不存在任何关系；在SQLServer中，通过特别的命名机制保证本地临时表在数据库连接上的独立性。

   3）全局临时表是用户在创建表的时候添加“##”前缀的表，其特点是所有数据库连接均可使用该全局临时表，当所有引用该临时表的数据库连接断开后自动删除。

   4）全局临时表相比本地临时表，命名上就需要注意。与本地临时表不同的是，全局临时表不能重复。

   5）临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了物理表空间，由数据库系统自动进行维护，因此节省了物理表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此提高了系统效率。

   6）临时表在事务完毕或会话完毕数据库会自动清空，不必己得用完后删除数据。

本地临时表

本地临时表的名称以单个数字符号“#”打头；它们仅对当前的用户连接（也就是创建本地临时表的connection）是可见的；当用户从SQL Server实例断开连接时被删除。

全局临时表

全局临时表的名称以两个数字符号“##”打头，创建后对任何数据库连接都是可见的，当所有引用该表的数据库连接从SQL Server断开时被开除。

临时表的用途

临时表的优化一般使用在子查询较多的情况下，也称为嵌套查询。我们写如下子查询：

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN
(SELECT SalesOrderDetailID FROM sales.SalesOrderDetail
WHERE UniPrice IN
(SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>)
)

用临时表重新来看下执行情况如何，将第一二层的查询结果插入到#temp中，然后从临时表中查询结果。

SELECT SalesOrderDetalID INTO #temp FROM sales.SalesOrderDetail
WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN
(SELECT SalesOrderDetailID FROM #temp)

相比前面的代码，用临时表的查询方式，成倍减少了逻辑读取次数。在对查询的性能进行调节时，如果逻辑读值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果逻辑度指增加，则表示调节措施降低了查询的性能。在其它条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。

2、变量

变量的定义：SQL Server中的变量就是一个参数，可以对这个参数进行赋值。

变量的分类：变量分为局部变量和全局变量，局部变量用@来标识，全局变量用@@来标识（常用的全局变量一般都是已经定义好的）。

声明变量：变量在使用前必须先声明才能够使用。

申明局部变量语法：DECLARE@变量名 数据类型；

例如：DECLARE @A INT；声明了一个整数型的变量@A。

局部变量赋值

声明完了变量就可以给变量赋值了，变量赋值有两种方式SET或SELECT。

SET 变量名=值
SELECT 变量名1=值1,变量名2=值2

SET仅能给一个变量赋值，SELECT可以给多个变量赋值。

变量常用场景：

变量一般用作参数去给字段赋值，即将变量的值反过来赋值给字段。

实例：Customers

客户ID	姓名	地址	城市	邮编	省份
1	张三	北京路27号	上海	200000	上海市
2	李四	南京路12号	杭州	310000	浙江省
3	王五	花城大道17号	广州	510000	广东省
4	马六	江夏路19号	武汉	430000	湖北省
5	赵七	西二旗12号	北京	100000	北京市
6	宋一	花城大道21号	广州	51000	广东省
7	刘二	长安街121号	北京	100000	北京市

DECLARE @ID INT
DECLARE @NAME VARCHAR(50)
DECLARE @ADDRESS VARCHAR(50)

SET @ID=1

SELECT @NAME=姓名,@ADDRESS=地址 FROM Customers WHERE 客户ID=@ID

SELECT @NAME,@ADDRESS

如果查询其他ID的姓名和地址，只需要更改一下@ID的值即可。

使用变量的好处：

1）使用简单：当查询里用一个字段需要修改的地方较多的时候，我们只需要修改这个字段对应的变量内容，那么所有的字段对应的值都会一起跟着修改。

例如：查询学生对应的不同老师的信息

DECLARE @ID INT
SET @ID=1
SELECT * FROM TEST WHERE Teacher=@ID AND Student='张三'
UNION ALL
SELECT * FROM TEST WHERE Teacher=@ID AND Student='李四'
UNION ALL
SELECT * FROM TEST WHERE Teacher=@ID AND Student='王五'
UNION ALL
SELECT * FROM TEST WHERE Teacher=@ID AND Student='马六'
UNION ALL
SELECT * FROM TEST WHERE Teacher=@ID AND Student='赵七'

上述只需要修改@ID的值，下面的子查询的ID都会变更。这样的方式避免了重复Teacher字段匹配工作。

2）可以提高查询效率

当我们使用查询的使用，数据库在执行这个查询语句的时候，如果不使用变量来修改值，实际上是两个查询。

例如：

SELECT * FROM TEST WHERE Student='张三'
SELECT * FROM TEST WHERE Student='李四'

执行这两个查询，数据库会制定两个执行计划，而制定执行计划是需要消耗系统资源的。

可以改成：

DELCARE @NAME VARCHAR(20)
SET @NAME='张三'
SELECT * FROM TEST WHERE Student=@NAME

当我们修改@NAME的值为'李四'的时候，数据库还是会使用之前的执行计划，这样节省了空间。

全局变量

全局变量使用@@来表示，一般都是系统预定义的一些全局变量。常用的全局变量有

@@ERROR——最后一个SQL错误的错误号

@@IDENTITY——最后一次插入的标识值

@@LANGUAGE——当前使用的语言名称

@@MAX_CONNECTIONS——可以创建的同时连接的最大数目

@@ROWCOUNT——受上一个SQL语句影响的行数

@@SERVERNAME——本地服务器的名称

@@TRANSCOUNT——当前连接打开的事物数

@@VERSION——SQL Server的版本信息

例如：PRINT @VERSION

输出SQL服务器的版本信息

这些信息均存储在全局变量中，当发生改变时，全局变量的值也会跟着改变。

3、CASE

CASE函数的类型：CASE具有两种格式，简单CASE函数和CASE搜索函数。这两种方式，大部分情况下可以实现相同的功能。

简单CASE函数：

CASE column
WHEN <condition> THEN value
WHEN <condition> THEN value
...
ELSE value END

示例：

CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END

CASE搜索函数

CASE 
WHEN <condition> [,<condition>] THEN value
WHEN <condition> [,<condition>] THEN value
...
ELSE value END

注意：CASE只返回第一个符合条件的值，剩下的CASE部分将会被自动忽略。