服务器之家

服务器之家 > 正文

SQL Server 总结复习(一)

时间:2019-12-23 16:27     来源/作者:MSSQL教程网

1. TVP, 表变量,临时表,CTE 的区别 
TVP和临时表都是可以索引的,总是存在tempdb中,会增加系统数据库开销,而表变量和CTE只有在内存溢出时才会被写入tempdb中。对于数据量大,并且反复使用,反复进行查询关联的,建议使用临时表或TVP,数据量小,使用表变量或CTE比较合适 
2. sql_variant 万能类型 
可以存放所有数据类型,相当于C#中的object数据类型 
3. datetime, datetime2, datetimeoffset 
datetime 时间有效期较小,在1753-1-1 之前就不能使用了,精度为毫秒级别,而datetime2 数据范围相当于C#中的datetime ,精度达到了秒后面小数点后7位,datetimeoffset则是考虑是时区的日期类型 
4. MERGE的用法 
语法很简单就不说了,主要是处理两张表某些字段对比后的操作,需注意 when not matched (by target) 与 when not matched by source的区别,前者是是针对对比后目标表不存在的记录,可以选择insert操作,而后者则是针对对比后目标表多出来的记录,可以选择delete或update操作 
5. rowversion 类型 
代替以前的timestamp,时间戳,8字节二进制值,常用来进行解决并发操作的问题 
6. Sysdatetime() 
返回datetime2类型,精度比datetime高 
7. with cube , with rollup , grouping sets 运算符 
都可与group by 后连用,with cube 表示汇总所有级别的组合,with rollup 则是按级别汇总,从下面的代码可以详细看出区别。注意,汇总行,null可以看成所有值 
而grouping sets运算符,则仅返回每个分组顶级汇总行,在查询汇总行中 可使用grouping(字段名) = 1来判断,该运算符可和rollup, cube连用,表示按照grouping by sets和按照rollup/cube处理的结果集union all 
示例代码如下: 

复制代码代码如下:


With cube, With rollup 
--示例代码 
declare @t table(goodsname VARCHAR(max) ,sku1name VARCHAR(max) , sku2name VARCHAR(max), qty INT) 
insert @t select '凡客TX','红色','S',1 
insert @t select '凡客TX','黑色','S',2 
insert @t select '凡客TX','白色','L',3 
insert @t select '京东村山','白色','L',4 
insert @t select '京东村山','红色','S',5 
insert @t select '京东村山','黑色','L',6 
insert @t select '亚马逊拖鞋','白色','L',7 
insert @t select '亚马逊拖鞋','红色','S',8 
SELECT * FROM @t 
select goodsname,sku1name,sku2name,sum(qty) sumqty 
from @t 
group by goodsname,sku1name,sku2name with rollup 
ORDER BY goodsname,sku1name,sku2name 
select goodsname,sku1name,sku2name,sum(qty) sumqty 
from @t 
group by goodsname,sku1name,sku2name with cube 
ORDER BY goodsname,sku1name,sku2name 
----------------------- 
declare @t table(goodsname VARCHAR(max) ,sku1name VARCHAR(max) , sku2name VARCHAR(max), qty INT) 
insert @t select '凡客TX','红色','S',1 
insert @t select '凡客TX','黑色','S',2 
insert @t select '凡客TX','白色','L',3 
insert @t select '京东村山','白色','L',4 
insert @t select '京东村山','红色','S',5 
insert @t select '京东村山','黑色','L',6 
insert @t select '亚马逊拖鞋','白色','L',7 
insert @t select '亚马逊拖鞋','红色','S',8 
--GROUPING SETS 运算符 
SELECT goodsname,sku1name,sku2name, SUM(qty) FROM @t GROUP BY GROUPING SETS(goodsname,sku1name,sku2name) 
SELECT goodsname, sku1name, sku2name ,SUM(qty) FROM @t 
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name,sku2name) 
ORDER BY goodsname,sku1name,sku2name 
SELECT goodsname, sku1name, sku2name ,SUM(qty) FROM @t 
GROUP BY ROLLUP(goodsname,sku1name,sku2name) 
ORDER BY goodsname,sku1name,sku2name 
SELECT CASE WHEN GROUPING(goodsname) = 1 THEN '[ALL]' ELSE goodsname END goodsname, 
CASE WHEN GROUPING(sku1name) = 1 THEN '[ALL]' ELSE sku1name END sku1name, 
CASE WHEN GROUPING(sku2name) = 1 THEN '[ALL]' ELSE sku2name END sku2name ,SUM(qty) FROM @t 
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name,sku2name) 
ORDER BY goodsname,sku1name,sku2name 


8. 一些快捷的语法 例如 Declare @id int = 0 
虽然有时很快捷,但DBA不建议这样使用,Declare @id = select top 1 id from 表名,建议声明和查表赋值分开 
9. 公用表达式 CTE 
特点:可嵌套使用,代替联接表中的子查询,结构层次更加清晰,也可用来递归查询,另外通过巧妙的常量列控制递归层次 
示例代码如下: 

复制代码代码如下:


--公用表达式CTE Common table expression 
--用CTE实现递归算法 
CREATE TABLE EMPLOYEETREE( 
EMPLOYEE INT PRIMARY KEY, 
employeename nvarchar(50), 
reportsto int 

insert into EMPLOYEETREE values(1,'Richard',null) 
insert into EMPLOYEETREE values(2,'Stephen',1) 
insert into EMPLOYEETREE values(3,'Clemens',2) 
insert into EMPLOYEETREE values(4,'Malek',2) 
insert into EMPLOYEETREE values(5,'Goksin',4) 
insert into EMPLOYEETREE values(6,'Kimberly',1) 
insert into EMPLOYEETREE values(7,'Ramesh',5) 
---------------------- 
--确定哪些员工向Stephen报告的递归查询 
with employeeTemp as 

select EMPLOYEE, employeename, reportsto from EMPLOYEETREE where EMPLOYEE = 2 
union all 
select a.EMPLOYEE, a.employeename, a.reportsto from EMPLOYEETREE as a 
inner join employeeTemp as b on a.reportsto = b.EMPLOYEE 

select * from employeeTemp where EMPLOYEE <> 2 --option(maxrecursion 2) 
--不报错设置级联关联递归 
with employeeTemp as 

select EMPLOYEE, employeename, reportsto,0 as sublevel from EMPLOYEETREE where EMPLOYEE = 2 
union all 
select a.EMPLOYEE, a.employeename, a.reportsto,sublevel+1 from EMPLOYEETREE as a 
inner join employeeTemp as b on a.reportsto = b.EMPLOYEE 

select * from employeeTemp where EMPLOYEE <> 2 and sublevel <=2 --option(maxrecursion 2) 


10. pivot 与 unpivot 
前者用在行转列,注意:必须用聚合函数与PIVOT一起使用,计算聚会时将不考虑出现在值列中的任何空值;一般情况下,可以用列上的子查询来替换pivot语句,但是这样做效率不高 
后者用在列转行,注意:如果某些列中有null值,将会被过滤掉,不产生新行;语法上For前指定的新列,对应原表指定列名中的值,For后指定的新列对应原表指定列名中的标题的值 
两者都有的共性:语法上最后必须要有别名;IN里面指定的列类型必须是一致的。 
示例代码如下: 

复制代码代码如下:


pivot与unpivot 
--关于PIVOT的操作 
CREATE TABLE #test 

NAME VARCHAR(max), 
SCORE INT 

INSERT INTO #test VALUES ('张三','97') 
INSERT INTO #test VALUES ('李四','28') 
INSERT INTO #test VALUES ('王五','33') 
INSERT INTO #test VALUES ('神人','78') 
--NAME SCORE 
--张三 97 
--李四 28 
--王五 33 
--神人 78 
--行转列 
SELECT --'成绩单' AS SCORENAME , 
[张三], [李四], [王五] 
FROM #test 
PIVOT (AVG(SCORE) FOR NAME IN ([张三], [李四], [王五])) b 
----------------------------------------- 
CREATE TABLE VendorEmployee( 
VendorId INT, 
Emp1Order INT, 
Emp2Order INT, 
Emp3Order INT, 
Emp4Order INT, 
Emp5Order INT, 

GO 
INSERT INTO VendorEmployee VALUES(1,4,3,5,4,4) 
INSERT INTO VendorEmployee VALUES(2,4,1,5,5,5) 
INSERT INTO VendorEmployee VALUES(3,4,3,5,4,4) 
INSERT INTO VendorEmployee VALUES(4,4,2,5,4,4) 
INSERT INTO VendorEmployee VALUES(5,5,1,5,5,5) 
SELECT * FROM VendorEmployee 
---------------- 
--列转行 
SELECT * FROM ( 
SELECT VendorId,[Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order] FROM VendorEmployee) AS unpiv 
UNPIVOT (orders FOR elyid IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order])) AS child 
ORDER BY elyid 
SELECT * FROM VendorEmployee 
UNPIVOT (orders FOR elyid IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order])) AS child 
ORDER BY elyid 
SELECT * FROM VendorEmployee UNPIVOT ( ORDERS FOR [操作员名字] IN ([Emp1Order],[Emp2Order],[Emp3Order],[Emp4Order],[Emp5Order])) 

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部