纯属笔记记录------大部分内容来自公众号SQL数据库开发
数据库:SQL Servers
目录
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部分将会被自动忽略。