1.技术内幕：T-SQL语言基础

经典T-SQL书籍笔记，值得推荐！

技术内幕：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.可编程对象