导图社区 1.技术内幕:T-SQL语言基础
经典T-SQL书籍笔记,值得推荐!
编辑于2020-02-29 22:17:28技术内幕:T-SQL语言基础
1.T-SQL查询和编程基础
2.单表查询
1.Select语句的元素
1.From子句
2.Where子句
3.group by子句
4.having子句
5.select子句
6.order by子句
7.top选项
8.over子句
--返回所有行的总价格和当前客户的总价格。 select orderid,custid,val, sum(val) over() as totalvalue, sum(val) over(partition by custid) as custtotalvalue from Sales.OrderValues
--row_number(行号),rank(排名),dense_rank(密集排名) select orderid,custid,val, ROW_NUMBER() over(order by val) as rownum, rank() over(order by val) as rank, DENSE_RANK() over(order by val) as dense_rank, ntile(10) over(order by val) as ntile from Sales.OrderValues order by val;
/*************************** 2-6:为每个顾客单独根据订单日期的顺序(用orderID作为附加属性)来计算其订单的行号。 涉及的表:Sales.Orders表。 ***************************/ select custid,orderdate,orderid ,dense_rank() over(partition by custid order by orderdate) as rownum from Sales.Orders order by custid,orderdate;
2.谓词和运算符
3.Case表达式
4.NULL值
5.同时操作(ALL-At-Once Operation)
6.处理字符数据
1.数据类型
普通字符(256)、仅限英语: CHAR/VARCHAR
Unicode字符(2~16=65536)、包含其它语言: NCHAR/NVARCHAR
2.排序规则
分割标识符
3.运算符和函数
+
coalesce函数(将NULL替换为空字符串)
substring函数(提取子串)
left和right函数
len函数(返回字符数)
datalength函数(返回字节数)
charindex函数(某子串第一次出现的位置)
patindex函数(某模式第一次出现)
replace函数(替换)
replicate函数(复制)
stuff函数(替换)
upper和lower函数
rtrim和ltrim(删除空格)
7.处理日期和时间数据
1.数据类型
2.字符串文字
3.单独使用日期和时间
单独使用日期:时间全为0
单独使用时间:日期为:19000101
4.过滤日期范围
尽量不对日期字段做处理,而是用相应范围
5.日期和时间函数
返回当前日期和时间
其他函数
cast和convert函数(转化类型)
switchoffset函数(根据时区调整时间)
todatetimeoffset函数(设置时区偏移量)
dateadd函数(增加指定的数量)
datediff函数(差值)
/******************************* --返回每个月的最后一天生成的订单 --涉及的表:Sales.Orders表 *******************************/ select orderid,orderdate,custid,empid,datediff(month,'1999-12-31',orderdate ),dateadd(month,datediff(month,'1999-12-31',orderdate ),'1999-12-31') from Sales.Orders where cast(orderdate as datetime) = dateadd(month,datediff(month,'1999-12-31',orderdate ),'1999-12-31');
datepart函数(指定部分的整数)
8.查询元数据
查询SQL Server系统目录
1.目录视图
2.信息架构视图
3.系统存储过程和函数
3.联接查询
1.交叉联接
自交叉联接:生成数字表: select D3.digit*100 + D2.digit*10 + D1.digit + 1 AS n from digits AS D1 CROSS JOIN Digits AS D2 CROSS JOIN dIGITS as D3 ORDER BY n;
2.内联接
inner join
3.特殊的联接
组合联接:联接条件涉及联接两边的多个列的查询
FROM Table as T1 JOIN Table2 as T2 on T1.col1=T2.col1 and T1.col2=T2.col2
不等联接
多表联接
4.外联接
新建并填充辅助表Nums
set nocount on; if OBJECT_ID('dbo.Nums','U') is not null drop table dbo.Nums; create table dbo.Nums(n int not null primary key); declare @i as int =1; begin tran while @i<=100000 begin insert into dbo.Nums values(@i); set @i=@i+1; end commit tran set nocount off;
select dateadd(day,n-1,'20060101') as orderdate from dbo.Nums where n <= datediff(day,'20060101','20081231') + 1 order by orderdate;
4.子查询
1.独立子查询
2.相关子查询
3.高级子
1.返回前一个或后一个记录查询
select orderid,orderdate,empid,custid, (select max(o2.orderid) from sales.orders as o2 where o2.orderid < o1.orderid) as prevorderid from sales.orders as o1;
select orderid,orderdate,empid,custid, (select min(o2.orderid) from sales.orders as o2 where o2.orderid > o1.orderid) as nextorderid from sales.orders as o1;
2.连续聚合
select orderyear,qty, (select sum(o2.qty) from sales.ordertotalsbyyear as o2 where o2.orderyear <= o1.orderyear) as runqty from sales.OrderTotalsByYear as o1 order by orderyear;
5.表表达式
1.派生表
2.公用表表达式(CTE(with))
3.视图
encryption选项:文本混淆
4.内联表值函数
5.APPLY运算符
6.集合运算
1.UNION(并集)集合运算
1.UNION ALL集合运算
2.UNION DISTINCT集合运算
2.INTERSECT(交集)集合运算
1.intersect distinct集合运算
2.intersect all集合运算(无此方法,用其他方法实现)
select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from HR.Employees INTERSECT select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from Sales.Customers;
/********************************* 如何用Intersect All运算来返回雇员和客户地址中所有重复的行 *******************************/ with INTERSECT_ALL AS ( select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from HR.Employees INTERSECT select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from Sales.Customers ) select country,region,city from INTERSECT_ALL;
3.EXCEPT(差集)集合运算
1.EXCEPT DISTINCT集合运算
select country,region,city from HR.Employees except select country,region,city from Sales.Customers;
2.EXCEPT ALL集合运算
with except_all as ( select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from HR.Employees except select ROW_NUMBER() over(partition by country,region,city order by (select 0)) as rownum, country,region,city from Sales.Customers ) select country,region,city from except_all;
4.集合运算的优先级
intersect运算比union和except运算的优先级高,而union和except的优先级相等。要控制集合运算的计算顺序,可以使用圆括号。
5.避开不支持的逻辑查询处理
7.透视、逆透视及分组集
1.透视转换pivot
三个逻辑处理阶段
1.分组阶段:处理相关分组或行元素
2.扩展(spreading)阶段:处理相关的扩展或列元素
3.聚合阶段:处理相关的聚合元素和聚合函数
标准SQL
--使用标准SQL进行透视转换:返回每个雇员(按行)和客户(按列)的总订货量。 select empid, sum(case when custid = 'A' then qty end) as A, sum(case when custid = 'B' then qty end) as B, sum(case when custid = 'C' then qty end) as C, sum(case when custid = 'D' then qty end) as D from dbo.Orders group by empid;
T-SQL PIVOT
--使用T-SQL PIVOT运算符进行透视转换:返回每个雇员(按行)和客户(按列)的总订货量。 select empid,A,B,C,D FROM ( SELECT empid,custid,qty from dbo.Orders ) as d pivot (sum(qty) for custid in(A,B,C,D)) as p;
--使用T-SQL PIVOT运算符进行透视转换:返回每个雇员(按列)和客户(按行)的总订货量。 --注意:因为empid为数字(非常规标识符),须要对他们进行分割,所以语句中使用了方括号。 select custid,[1],[2],[3] FROM ( SELECT empid,custid,qty from dbo.Orders ) as d pivot (sum(qty) for empid in([1],[2],[3])) as p;
2.逆透视转换unpivot
三个逻辑处理阶段
1.生成副本
2.提取元素
3.删除不相关的交叉
1.使用标准SQL进行逆透视转换:
--1.使用标准SQL进行逆透视转换: select * from ( select empid,custid, case custid when 'A' THEN A WHEN 'B' THEN B WHEN 'C' THEN C WHEN 'D' THEN D END AS qty from dbo.EmpCustOrders cross join(values('A'),('B'),('C'),('D')) AS Custs(custid) ) as d where qty is not null;
2.使用T-SQL的UNPIVOT运算符进行逆透视转换
--2.使用T-SQL的UNPIVOT运算符进行逆透视转换 select empid,custid,qty from dbo.EmpCustOrders unpivot(qty for custid in(A,B,C,D)) AS u;
3.分组集
1.grouping sets
用途:主要用于生成报表和数据仓库处理
select empid,custid,sum(qty) as sumqty from dbo.Orders group by grouping sets ((empid,custid),(empid),(custid),());
2.cube
在CUBE从属子句的圆括号中,只需列出由逗号分隔开的元素成员,就可以得到基于输入成员而定义的所有可能的分组集
select empid,custid,sum(qty) as sumqty from dbo.Orders group by CUBE(empid,custid);
3.rollup
rollup认为输入成员之间存在一定的层次关系,从而生成这种层次关系有意义的所有分组集。(a,b,c)->(a>b>c)。即rollup(a,b,c)相当于grouping sets((a,b,c),(a,b),(a),())
4.grouping和grouping_id
grouping
grouping函数接受一个列名,如果该列是当前分组集的成员,就返回0,否则返回1。
select GROUPING(empid) as grpemp, GROUPING(custid) as grpcust, empid,custid,sum(qty) as sumqty from dbo.Orders group by cube(empid,custid);
grouping_id
--grouping_id函数 select GROUPING_ID(empid,custid) as groupingset, empid,custid,sum(qty) as sumqty from dbo.Orders group by cube(empid,custid);
8.数据修改
9.事务和并发
10.可编程对象