SQL Server2012 T-SQL基础教程--读书笔记（1-4章）

SQL Server2012 T-SQL基础教程--读书笔记（1-4章）

SqlServer

T-SQL

示例数据库：点我

---

• Chapter 01 T-SQL 查询和编程背景
  • 1.3 创建表和定义数据的完整性
    • 1.3.1 创建表
    • 1.3.2 定义数据的完整性
      • 1. 主键约束
      • 2. 唯一约束
      • 3. 外键束约
      • 4. CHECK约束
      • 5. 默认约束
• Chapter 02 单表查询
  • 2.1 SELECT 语句元素
    • 2.1.7 TOP和OFFSET-FETCH
      • 1. TOP筛选
      • 2. OFFSET-FETCH
    • 2.1.8 开窗函数速览
  • 2.2 谓词和运算符
  • 2.3 CASE表达式
  • 2.5 同时操作
  • 2.6 运算符和函数##
    • 2.6.1 字符串连接（+和CONCAT函数）
    • 2.6.2 SUBSTRING 函数
    • 2.6.3 LEFT 和 RIGHT函数
    • 2.6.4 LEN 和 DATALENGTH 函数
    • 2.6.5 CHARINDEX 函数
    • 2.6.6 PATINDEX 函数
    • 2.6.7 REPLACE 函数
    • 2.6.8 REPLICATE 函数
    • 2.6.9 STUFF 函数
    • 2.6.10 UPPER 和 LOWER 函数
    • 2.6.11 RTRIM 和 LRITM 函数
    • 2.6.12 FORMAT 函数
    • 2.6.13 LIKE 谓词
  • 2.7 时间和日期
    • 2.7.1 当前时间
    • 2.7.2 CAST、CONVERT和PARSE函数，及其TRY_对应函数
    • 2.7.3 SWITCHOFFSET 函数
    • 2.7.4 TODATETIMEOFFSET 函数
    • 2.7.5 DATEADD 函数
    • 2.7.6 DATEDIFF 函数
    • 2.7.7 DATEPART 、YEAR、MONTH、DAY和DATENAME 函数
    • 2.7.7 ISDATE 函数
    • 2.7.8 FORMPARTS 函数
    • 2.7.9 EOMONTH 函数
  • 2.8 查询元数据
    • 2.8.1 目录视图
    • 2.8.2 信息架构视图
    • 2.8.3 系统存储过程和函数
  • 1460384190281.jpg
  • 练习
• CHAPTER 03 联接
  • 3.1交叉联接
    • 3.1.1 ANSI SQL-92 和 89 语法
    • 3.1.2 自交叉联接
    • 3.1.3 生成数字表
  • 3.2 内部联接
    • 3.2.1 ANSI SQL-92、89 语法和安全性
  • 3.3 更多联接示例
    • 3.3.1 复合联接
    • 3.3.2 不等联接
    • 3.3.3 多联接查询
  • 3.4 外部联接
    • 3.4.1 外联接基础知识
    • 3.4.2 其他外联接知识
  • 练习
• CHAPTER 04 子查询
  • 4.1 自包含子查询
    • 4.1.1 自包含标量子查询示例
    • 4.1.2 自包含多值子查询示例
  • 4.2 相关子查询
    • 4.2.1 EXISTS 谓词
  • 4.3 额外子查询知识
    • 4.3.1 返回前一个或下一个值
    • 4.3.2 使用运行聚合
    • 4.3.3 不当子查询处理
      • 4.3.3.1 NULL故障
      • 4.3.2 子查询列中的替换错误
  • 练习

---

Chapter 01 T-SQL 查询和编程背景

1.3 创建表和定义数据的完整性

1.3.1 创建表

USE TSQL2012;
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
  empid     INT         NOT NULL,
  firstname VARCHAR(30) NOT NULL,
  lastname  VARCHAR(30) NOT NULL,
  hiredate  DATE        NOT NULL,
  mgrid     INT         NULL,
  ssn       VARCHAR(20) NOT NULL,
  salary    MONEY       NOT NULL
);

在创建对象脚本中包含USE语句是十分重要，能确保在指定的数据库中创建对象。

1.3.2 定义数据的完整性

1. 主键约束

主键约束强制行的唯一性，在约束的属性中不允许使用NULL标记。约束的属性的值必须是唯一的，每个表只能有一个主键。为了强制逻辑主键约束的一唯一性，SQL Server会在后台创建一个唯一索引。唯一索引是SQL Server为了强制唯一性所使用的一种物理机制。

ALTER TABLE dbo.Employees
  ADD CONSTRAINT PK_Employees
  PRIMARY KEY(empid)

2. 唯一约束

唯一约束强制行的唯一性，允许你在自己的数据库中实现关系模型的备用键概念。与主键不同，可以在同一个表内定义多个唯一约束，此外，唯一约束不限制列必须定义为NOT NULL。根据SQL标准，具有唯一约束的列应该允许重复的NULL值。但是，SQL Server则不允许重复的NULL标记

ALTER TABLE dbo.Employees
  ADD CONSTRAINT UNQ_Employees_ssn
  UNIQUE(ssn);

3. 外键束约

外键用于强制引用的完整性，此约束定义了引用表中的一个或多个属性指向被引用表（父表）中候选键（主键或唯一约束），引用表和被引用表可以是同一个。外键的目的是限制在外键列中允许的值要存在于那些被引用列中。

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
  DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
( orderid   INT         NOT NULL,
  empid     INT         NOT NULL,
  custid    VARCHAR(10) NOT NULL,
  orderts   DATETIME2   NOT NULL,
  qty       INT         NOT NULL,
  CONSTRAINT PK_Orders
    PRIMARY KEY(orderid)
);

ALTER TABLE dbo.Orders
  ADD CONSTRAINT FK_Orders_Employees
  FOREIGN KEY(empid)
  REFERENCES dbo.Employees(empid)

如果Orders表中的订单引用了Employees中的某一个雇员行，当尝试从Employees中删除这一雇员行时，RDBMS会拒绝删除并抛出错误。

4. CHECK约束

CHECK允许定义一个谓词，确保进入到表中的行或是被修改的行必须满足些约束。

ALTER TABLE dbo.Employees
  ADD CONSTRAINT CHK_Employees_salary
  CHECK(salary > 0);

5. 默认约束

如果没有在插入时指定一个显式值时，将会使用该默认值。

ALTER TABLE dbo.Employees
  ADD CONSTRAINT DFT_Orders_orders
  DEFAULT(SYSDATETIME()) FOR orders;

---

Chapter 02 单表查询

---

2.1 SELECT 语句元素

SELECT语句的查询顺序：

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

2.1.7 TOP和OFFSET-FETCH

1. TOP筛选

指定10个最近的订单：

SELECT TOP 10 orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

可以为TOP选项指定PERCENT关键字，向上舍入。

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC

Orders中总共有830行，830 * 1%向上舍入则是9行。
我们可以发现，在前4行的数据中，orderdate是相相同的，在这种没有指定决胜属性（tiebreaker）的情况下，对具有相同值的orderdate排序是没有意义的。SQL Server返回的结果是不确定的，即哪行先被物理访问到就先返回哪行。
如果希望查询结果是确定的，则需要ORDER BY列出的数据是唯一的，即要添加一个决胜发展（tiebreaker）。如可在ORDER BY加入orderid作为tiebreaker

SELECT TOP(5)  orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;

或者使用WITH TIES作为关联行，来实现tiebreaker

SELECT TOP(5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;

注意，即使指定了TOP 5，但是输出却是8行。SQL Server首先返回基于orderdate DESC排序的TOP 5行，然后是返回与检索到的5行中最后一行orderdate值相同的其他所有行。

2. OFFSET-FETCH

TOP不是标准SQL，并且不支持跳过功能。标准的SQL定义的TOP类似筛选称为为OFFSET-FETCH，支持跳过功能。SQL SERVER 2012中的OFFSET-FETCH被视为ORDER BY子句的一部分，通常用于实现按顺序
显示效果。OFFSET子句指定要跳过的行数，FETCH子句指定在跳过的行数后要筛选的行数。

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

基于排序好的结果，OFFSET子句跳过50行，由FETCH子句往后取25行。
注意，使用OFFSET-FETCH的查询必须具有ORDER BY子句。此外FETCH子句不支持没有OFFSET子句。不过，没有FETCH的OFFSET是允许的，这种情况就是跳过多少行，并返回剩余的所有行。而且ROW和ROWS是可以互换的。

2.1.8 开窗函数速览

开窗函数的功能是：对于基本的每一行，按行的窗口（组）进行运算，并计算一个标量（单个）结果值，行的窗口使用OVER子句定义。OVER子句可以使用PARTITION BY子子句约束窗口中的行，并且可以使用ORDER BY子子名为计算结果定义排序。

SELECT orderid, custid, val
    , ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid,val;

ROW_NUMBER函数对于查询结果按custid进行分区，并在各个分区内按照指定的val进行排序，分配了唯一、递增、连续的整数。
注意，ROW_NUMBER函数必须在每个分区内生成唯一值 。这意味着即使排序值不增加，行号也会增加。
SELECT列表中的表达式是在DISTINCT子句之前计算的，总之，sql语句的执行顺序为：

• FROM

• WHERE

• GROUP BY

• HAVING

• SELECT

  • 表达式

  • DISTINCT

• ORDER BY

  • TOP/OFFSET-FETCH

---

2.2 谓词和运算符

---

2.3 CASE表达式

CASE是一个标题表达式，返回一个基于条件置逻辑的值。注意，CASE是表达式而不是语句。所以，它不支持你控制活动流或是做一些基于条件逻辑的损伤。可以在诸如：SELECT、WHERE、HAVING和ORDER BY 子句中以及在CHECK中使用。CASE具有简单和复杂格式，ELSE子句默认值为NULL
简单格式：

SELECT productid,productname, categoryid
  ,CASE categoryid
    WHEN 1 THEN 'Beverages'
    WHEN 2 THEN 'Condiments'
    WHEN 3 THEN 'Confections'
    WHEN 4 THEN 'Dairy Products'
    ELSE 'Others'
  END AS categoryname
FROM Production.Products
ORDER BY categoryid

复杂格式：

SELECT orderid, custid, val,
  CASE
    WHEN val < 1000 THEN 'Less than 1000'
    WHEN val > 1000 THEN 'More than 1000'
    ELSE 'Others'
  END AS category
FROM Sales.OrderValues

T-SQL 中某些函数可以看作是CASE的表达式缩写，如：ISNULL、COALESCE、IIF和CHOOSE，其中只有COALESCE*是标准的，后两个仅在2012版本中可以使用

---

2.5 同时操作

SELECT col1, col2 FROM dbo.test WHERE col1 <> 0 AND col2/col > 2

这是一个col2/col1大于2的查询语句，很有可能假定SQL Server 会从左到右计算此表达式。从而使得col=0时会出现短路，从而不继续进行col2/col1的计算。但是，SQL Server 支持短路，这是基于SQL标准的同时操作概念。SQL Server通常基于成本估计来进行表达式的计算顺序。所以此语句有可能会查询失败。对于此种特定情况，该语句可改写为

SELECT col1, col2 FROM dbo.test WHERE （col1<0 AND col2> *col1） OR (col1<0 AND col2<2*col1)

---

2.6 运算符和函数##

2.6.1 字符串连接（+和CONCAT函数）

使用*+运算符作为字符连接时，如果连接符中有数字类型需要使用CONVERT*，CAST将数字类型转换为字符类型，否则可能会因字符类型不能转换为数字类型而报错，这是因为SQL Server会隐式的将其他字符类型转换为数字类型。

SELECT CONCAT(1,'/',5,'a') --query success: 1/5a
SELECT 1+'a' --execute fail

2.6.2 SUBSTRING 函数

SUBSTRING(string, start, length) 在sql中，string字符串下标从1开始算起，而不是0。

SELECT SUBSTRING('ABCD',1,2) --AB

---

2.6.3 LEFT 和 RIGHT函数

LEFT(string, n),RIGHT(string, n)，n代表从LEFT或RIGHT提取的字符数

SELECT LEFT('ABCD',2) --AB RIGHT('ABCD',2) --CD

2.6.4 LEN 和 DATALENGTH 函数

LENG(string) 返回字符数（或者长度)，并且会删除字符串最后的空格.
DATALENG(string) 返回的是字节数

SELECT LEN（N'ABCD') --4   DATALENGTH(N'ABCD') --8

2.6.5 CHARINDEX 函数

CHARINDEX(subString, string[,start_pos]) 返回字符串subString从start_pos开始在string中第一次出现的位置。如果找不到指定的字符串，返回0

SELECT CHARINDEX('cd','CDABCDEFG',2) --5

2.6.6 PATINDEX 函数

PATINDEX(pattern,string) pattern与LIKE 模式相同

SELECT PATINDEX('%[0-9]%','abcde53fg') --6

2.6.7 REPLACE 函数

REPLACE(string, subString1, subString2) 使用subString2取代string中所有的subString1

SELECT REPLACE('abcde53fg53','53','11') --abcde11fg11

2.6.8 REPLICATE 函数

REPLICATE(string, n) 将string复制n次

SELECT REPLICATE('abc',3) --abcabcabc

2.6.9 STUFF 函数

STUFF(string, pos, del_length, insertString) 从pos位置开始，删除指定del_length长度的字符串，并从pos处插入insertString字符串

SELECT STUFF('abcdefg',3,4,1234) --ab1234g

2.6.10 UPPER 和 LOWER 函数

返回大小写或小写字符串

2.6.11 RTRIM 和 LRITM 函数

从右边或左边删句末或句首的空格

2.6.12 FORMAT 函数

FORMAT(input, format_string, culture) 按照.NET格式和一个可选的区域参数，将input格式化成一个字符串 从pos位置开始，删除指定del_length长度的字符串，并从pos处插入insertString字符串

SELECT FORMAT(153,'0000') --0153

2.6.13 LIKE 谓词

1. % 通配符，代表任意字符

2. _ 通配符，表示单个字符

3. [ ] 通配符，代表单个字符必须是指定[ ]内的字符之一

4. [-] 通配符，代表单个字符必须在指定的范围内

5. [^ ] 通配符，代表指定的字符不在指定的字符列表或范围内。

6. ESCAPE 字符，要查找上面定义的通配符时，需要使用转义字符。如要查找_：col LIKE '%!_%'ESCAPE'!' 或 col LIKE '%[_]%'

---

2.7 时间和日期

在筛选列上应用操作时，数据库不能以有效方式使用索引，所以如果要筛选日期范围时可使用如col >= 20160101 AND col < 20080101来代替YEAR(col) = 2007

2.7.1 当前时间

SELECT
    GETDATE()           --2016-01-28 23:37:29.447
    ,CURRENT_TIMESTAMP  --2016-01-28 23:37:29.447
    ,GETUTCDATE()       --2016-01-28 15:37:29.447
    ,SYSDATETIME()      --2016-01-28 23:37:29.4810323
    ,SYSUTCDATETIME()   --2016-01-28 15:37:29.4810323
    ,SYSDATETIMEOFFSET()--2016-01-28 23:37:29.4810323 +08:00

2.7.2 CAST、CONVERT和PARSE函数，及其TRY_对应函数

输入值如果转换成功，则返回目标值，否则就会查询失败。其对应的 TRY_ 函数执行的是相同的操作，只是如果查询失败返回的是 NULL 。
语法:
CAST(val AS datatype)
CONVERT(dataType, val [,sytle_number])
PARSE(val AS dataType [USING culture])

SELECT PARSE('02/12/2016' AS DATETIME USING 'en-us'),PARSE('02/12/2016' AS DATETIME USING 'en-gb')

2.7.3 SWITCHOFFSET 函数

SEITCHOFFSET(datatimeoffset_val, time_zone) 该函数将输入的datatimeoffset_val值调整为指定的时区

--将时区调整为-05:00
SELECT SYSDATETIMEOFFSET(), SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00')

2.7.4 TODATETIMEOFFSET 函数

TODATETIMEOFFSET(date_and_time_value,time_zone) 设置date_and_time_value为time_zone时区，通常用于迁移非已知偏移量数据到已知偏移数据。

--Sunday, January 31, 2016 8:42:00 PM +08:00
SELECT TODATETIMEOFFSET(CONVERT(DATETIME,'20160131 20:42'),'+08:00')

2.7.5 DATEADD 函数

DATEADD(part,n,dt_val) 指定日期的某个部分part增加n到dt_val中

• part:YEAR MONTH QUARTER DAY DAYOFYEAR WEEK WEEKDAY HOUR MINUTE SECOND MILLISECOND MICROSECOND NANOSECOND，也可以使用缩写形式，如yy代替year等

• n:可以是正、负数

--4/30/2016 9:00:52 PM  1/31/2016 9:00:52 PM
SELECT DATEADD(QUARTER,1,SYSDATETIME()), SYSDATETIME()

2.7.6 DATEDIFF 函数

DATEDIFF(part, dt_val1, dt_val2) 返回 dt_val1 到 dt_val2 之间指定日期部分 part 的间隔

2.7.7 DATEPART 、YEAR、MONTH、DAY和DATENAME 函数

DATEPART(part, dt_val) 返回 dt_val 指定日期部分 part 的值。可以使用 YEAR()，MONTH()，DAY() 代替
DATETIME(part, dt_val) 此函数和DATEPART 相似，但是它返回的是对应日期部分的名称，而不是数字。如:

--January
SELECT DATENAME(MONTH, SYSDATETIME())

2.7.7 ISDATE 函数

ISDATE(string) 判断 string 是否为日期格式可以转换为日期格式数据，可以则返回1，否则返回0。

---

2.7.8 FORMPARTS 函数

• DATEFORMPARTS(year, month, day)

• DATETIME2FORMPARTS(year, month, day,hour,minute,seconds,fractions,precisions)

• DATETIMEFORMPARTS(year, month, day,hour,minute,seconds.millseconds)

• DATETIMEOFFSETFORMPARTS(year,month,day,hour,minute,seconds,fractions,hour_offset,minute_offset,precision)

• SMALLDATETIMEFORMPARTS(year, month, day,hour,minute)

• TIMEFORMPARTS(hour,minute,seconds,fractions,precisions)

这是SQL Server 2012中引入的，它们接受代表日期和时间各个部分的整数值，并根据这些值构建一个所请求类型的值。

SELECT DATEFROMPARTS(2016,01,31) --2016-01-31
    ,DATETIMEFROMPARTS(2016,01,31,22,30,00,00) --2016-01-31 22:30:00.000
    ,DATETIMEOFFSETFROMPARTS(2016,01,31,22,30,00,1,8,4,3)
    --2016-01-31 22:30:00.001 +08:04

2.7.9 EOMONTH 函数

EOMONTH（input[,months_to_add]）
这是SQL Server 2012引入的函数。它接受一个日期和时间值输入，并返回相应的每个月的最后一天，作为 DATE 数据类型。第2个可选参数指示要增加多少个月。

SELECT EOMONTH(DATEADD(mm,1,SYSDATETIME())) --2016-02-29

---

2.8 查询元数据

2.8.1 目录视图

--查询所有Schema.Table
SELECT SCHEMA_NAME(SCHEMA_ID) AS tableSchemaName, name AS tableName FROM sys.tables

1460382672102.jpg

--查询Sales.Orders表的相关column
SELECT name AS colName, TYPE_NAME(system_type_id) AS colType, max_length AS length FROM sys.columns WHERE object_id = OBJECT_ID('Sales.Orders')

1460383561004.jpg

2.8.2 信息架构视图

信息架构视图是一个视图合集，其位于 INFORMATION_SCHEMA 的架构中，并以标准方式提供元数据信息。

--查询所有的基本表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

1460383700927.jpg

--查询 Sales.Orders 所有的column
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Sales' AND TABLE_NAME = 'orders'

1460383929710.jpg

2.8.3 系统存储过程和函数

--返回当前数据库中能够查询到的对象列表（表、视图等）
EXEC sys.sp_tables

1460383952232.jpg

--返回orders表的详细信息
EXEC sys.sp_help 'Sales.Orders'

1460384002912.jpg

--返回orders表的column
EXEC sys.sp_columns @table_name = 'orders', @schema_name = 'Sales'

1460383680829.jpg

--返回orders表的约束信息，参数必须包含schema
EXEC sys.sp_helpconstraint 'Sales.orders'

1460384107103.jpg

--返回指定数据为所请求的属性信息
SELECT DATABASEPROPERTYEX(N'TSQL2012','Collation')

1460384124796.jpg

--返回指定对象名称的所请求属性信息
SELECT OBJECTPROPERTY(OBJECT_ID('Sales.Orders'),'TableHasPrimaryKey')

1460384170971.jpg

--返回指定column所请求的信息
SELECT COLUMNPROPERTY(OBJECT_ID('Sales.Orders'),'orderdate','AllowsNull')

1460384190281.jpg

练习

--1.查询2007年6月份订单(30 rows)
SELECT * FROM Sales.orders WHERE orderdate >= '20070601' AND orderdate < '20070701' ORDER BY orderdate

--2.查询每月最后一天的订单(26 rows)
SELECT * FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate)
ORDER BY orderdate 

--3.查询HR.Employees表中姓氏包含‘a’两次以上的雇员(1 rows)
SELECT * FROM HR.Employees WHERE lastname LIKE '%a%a%'

--4.查询Sales.OrderDetails，返回总价（qty*unitprice）大于10000的订单
SELECT orderid, SUM(unitprice * qty) AS total
FROM Sales.OrderDetails
GROUP BY orderid
HAVING SUM(unitprice*qty) > 10000

--5.查询Sales.Orders，返回2007年中平均运费最高的3个国家
SELECT TOP 3 shipcountry, AVG(freight) AS avgFreight1,SUM(freight)/COUNT(*) AS avgFreight2
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry ORDER BY 3 DESC

--6.查询Sales.Orders，分别对每个客户的订单按订单日期排序(orderid作为决胜属性),计算订单编号
SELECT  custid,orderid, orderdate, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY orderdate,orderid ) rownum  FROM Sales.orders

--7.查询HR.Employees，推测每个雇员性别。"Ms."和"Mrs.":Female,"Mr.":male，其他:Unknown
SELECT *,
    CASE
        WHEN titleofcourtesy='Mrtitleofcourtesy' THEN 'Male'
        WHEN titleofcourtesy in('Mrs.','Ms.') THEN 'Female'
    ELSE 'Unknown' END AS gender
FROM HR.Employees 

--8.查询Sales.Customers，返回客户的ID和地区，按地区排序，具有null的最后输出(null值默认是先输出)
SELECT custid, region FROM Sales.Customers
ORDER BY
    CASE WHEN region IS NULL THEN 1 ELSE 0 END, region

1. 在SQL SERVER 中 NULL 默认为无穷小，所以排序时默认排在前面。

---

CHAPTER 03 联接

联接的3个基本类型是交叉联接、内部联接和外部联接。交叉联接仅处理阶段——笛卡尔乘积，内部联接应用两个阶段——笛卡尔乘积和筛选，外部联接应用三个阶段——笛卡尔乘积、筛选和添加外部行。

3.1交叉联接

3.1.1 ANSI SQL-92 和 89 语法

两种语法没有逻辑或性能上的差异，但推荐使用92的SQL语法。

--92
SELECT custid, empid FROM Sales.Customers CROSS JOIN HR.Employees ORDER BY 1,2
--89
SELECT custid, empid FROM Sales.Customers ,HR.Employees ORDER BY 1,2

3.1.2 自交叉联接

可以对一个表的多个实例进行联接，支持联接的基本类型，这功能就是自联接。

-- 9*9 得到81行纪录
SELECT * FROM HR.Employees e1 CROSS JOIN HR.Employees e2

3.1.3 生成数字表

自联接生成整数数列（1，2，3....）结果集非常方便。

--总共10*10*10行，生成从0-999的数
SELECT num INTO #tmp_digits
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS digits(num)

SELECT d3.num * 100 + d1.num * 10 +  d2.num FROM #tmp_digits d1 CROSS JOIN #tmp_digits d2 CROSS JOIN #tmp_digits d3 ORDER BY 1

---

3.2 内部联接

内部联接是默认联接，所以INNER是可选的。

3.2.1 ANSI SQL-92、89 语法和安全性

--ANSI 92
SELECT  e.empid , o.orderid
FROM Sales.Orders o
    INNER JOIN HR.Employees e ON o.empid = e.empid
    ORDER BY 1,2
--ANSI 89
SELECT e.empid , o.orderid FROM Sales.Orders o, HR.Employees  e
WHERE o.empid = e.empid
ORDER BY 1,2

92语法具有在联接安全性方面比较好，比如忘记写on后面的联接条件时，SQL语句会运行异常，以便您对SQL进行修改。然而对于89语法来说，如果忘记在WHERE后添加条件，此时SQL也可以进行有效查询，返回了错误的结果集，并且由于查询不会失败，SQL可能会运行一段时间。而且，其他维护人员也不会知道些SQL语句是交叉联接还是内联接。所以，建议使用92语法的SQL。

3.3 更多联接示例

3.3.1 复合联接

复合联接就是在ON之后涉及多个属性的简单联接。

SELECT * FROM table1 AS t1 INNER JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2

3.3.2 不等联接

联接条件除了=号之外还有其他任何运算符的联接即为不等联接

--除自己以外的行就是9 * 9 - 9
SELECT e1.empid, e2.empid FROM HR.Employees e1 JOIN HR.Employees e2 ON e1.empid <> e2.empid ORDER BY 1,2

1460471949536.jpg

3.3.3 多联接查询

一般来说，当FROM子句中出现多个表运算符时，表运算符会从左到右进行逻辑处理。也就是说，第一个运算符生成的结果集将会被视为第二个运算符的左侧驱动表，以此类推。

SELECT c.custid, c.companyname, o.orderid, od.productid, od.qty
FROM Sales.Customers c
  JOIN Sales.Orders o         ON c.custid = o.custid
  JOIN Sales.OrderDetails od  ON o.orderid = od.orderid

---

3.4 外部联接

与其它联接相比，外部联接更难把握，因为你需要清楚知道哪个表的数据应该保留还是去掉。

3.4.1 外联接基础知识

外联接的语法是LEFT OUTER JOIN RIGHT OUTER JOIN 和 FULL OUTER JOIN， OUTER关键字可选，LEFT保留左侧表的行，RIGHT则相反，FULL则是两侧的行都需要保留。

SELECT c.custid, c.companyname, O.orderid
FROM Sales.Customers c
  LEFT OUTER JOIN Sales.Orders O ON c.custid = O.custid
ORDER BY 3

Customers表中有两位客户(22、75)是没有orderid的，在Orders返回的orderid属性中是NULL。逻辑上，这两个客户会被联接的第二阶段(基于ON的筛选)过滤掉，但是在第三个阶段将其作为外部行添加回了结果集。而在内联接中，这两行是不会返回的。这两行是在保留左侧表中所有行时添加的。
所以在外联接的的结果中，保留的行有两种：内部行和外部行。内部行是基于ON与另一侧匹配的行，外部行则是未匹配的行。
外部联接的一个混乱问题是：是在ON子句还是WHERE子句进行筛选。ON子句并不确定行是否会最终显示在输出的结果集中，只是判断行是否与另一侧是否匹配。所以，当需要表达的谓词不是最终结果时，在ON子句中进行指定谓词。当需要在外部行生成后再筛选，则应当使用WHERE子句。WHERE子句是在FROM子句之后进行处理的，即外部行生成后。

3.4.2 其他外联接知识

1. 包含缺失值

2. 从外联接的非保留侧筛选属性
   当需要检查涉及外部联接的代码查找bug时，要查的项目之一就是WHERE子句，查看是否引用了非保留侧的属性。这是因为联接非保留侧的属性在输出行中是NULL标记，通常NULL的operator value 形式的表达式会生成UNKNOWN值。WHERE子句通常会过滤掉UNKNOWN值，这样的谓词会导致所有外部行被过滤掉，实际上就抵消了外联接 。逻辑上就是实现了一个内联接。

SELECT c.custid, c.companyname,o.orderid,o.orderdate FROM Sales.Customers c LEFT JOIN Sales.Orders o ON c.custid = o.custid WHERE o.orderdate > '20070101'

1. 在多联接查询中使用外联接
   在2.5中“同时操作”的概念不适用于 FROM 阶段中表运算符的处理，表运算符是从左到右进行逻辑计算的，重新排列在外联接中的顺序可能会生成不同的输出结果。
   下面这个bug是上面的bug的变异，第一个联接返回的是客户及其订单，以及没有订单的客户（即外部行，orderid为NULL标记）。第二个 o.orderid = od.orderid 作为内联接的联接条件，生成的结果将会是左右两侧表都有相应 orderid 记录的结果集。因此，第一个联接（外联接）得到的外部行将不会作为结果输出。所以，外联接后跟一个内联接或是右外联接查询都会使得外部行被删除。当然这是假设联接条件是来自左侧的NULL标记和右侧的任意值进行比较。
   解决方法：

   • 第二个联接使用LEFT JOIN

   • 内联接先进行查询，再使用一个RIGHT JOIN

   • 使用()将内联接括起来，使得内联接变成一个独立的逻辑阶段

   SELECT c.custid,o.orderid,od.qty FROM Sales.Customers c
       LEFT JOIN Sales.Orders o ON c.custid = o.custid
       INNER JOIN Sales.OrderDetails od ON o.orderid = od.orderid
   

2. 外部联接使用COUNT聚合
   通常，不应该将外部行作为计数目标。进行COUN计算时不要使用 COUNT(*) 来进行计数，应当使用COUNT(col)代替。

---

练习

--1.为每个雇员行生成5个副本，表 HR.Employees 和 dbo.Nums
SELECT empid, firstname,lastname, n.n FROM HR.Employees e CROSS JOIN Nums n WHERE n < 6

--2. 编写一个查询，每个雇员返回一行，并且日期在20090612-20090616范围内，表HR.Employees, dbo.Nums
SELECT e.empid, x.orderDate
FROM HR.Employees e
    CROSS JOIN (SELECT DATEADD(DAY,n.n,'20090611') AS orderDate FROM Nums n WHERE n.n <= DATEDIFF(DAY, '20090612','20090616') + 1 ) x ORDER BY 1

--3. 返回美国客户，并为每个客户返回订单总数和总数量。表customers、orders、orderDetails
SELECT o.custid, COUNT(DISTINCT od.orderid) AS ordersCount, SUM(od.qty) AS totalQty
FROM Sales.Customers c
    INNER JOIN Sales.Orders o ON c.custid = o.custid INNER JOIN Sales.OrderDetails od ON o.orderid = od.orderid GROUP BY o.custid ORDER BY 1

--4. 返回客户及其订单，包括没有下订单的客户，customers、orders
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers c
    LEFT JOIN Sales.Orders o ON c.custid = o.custid

--5. 返回20070212下订单的客户以及他们的订单。此外，还返回20070212没有下订单的客户
SELECT c.custid, c.companyname, o.orderid, o.orderdate
FROM Sales.Customers c
    LEFT JOIN Sales.Orders o ON c.custid = o.custid AND o.orderdate = '20070212'

1. 

   1460550080821.jpg
2. 

   1460550162466.jpg
3. 

   1460550215348.jpg
4. 

   1460550241333.jpg
5. 

   1460550259556.jpg

---

CHAPTER 04 子查询

最外面查询的查询结果集返回给调用者，被称为外部查询。内部查询的查询结果被用于外部查询，称为内部查询。

---

4.1 自包含子查询

4.1.1 自包含标量子查询示例

--1. 将值赋给一个变量
DECLARE @maxId INT = (SELECT MAX(orderid) FROM Sales.Orders)
SELECT * FROM Sales.Orders WHERE orderid = @maxId

--2. 使用子查询代替变量。“=”号右边只允许返回一个单值，不可返回多个，否则报错
SELECT * FROM Sales.Orders WHERE orderid = (SELECT MAX(orderid) FROM Sales.Orders)

如果子查询没有返回值，则返回 NULL 。而与NULL的都会生成 NUKNOWN， 然而查询筛选是不返回筛选表达式试算为 UNKNOWN 的行。

4.1.2 自包含多值子查询示例

多值子查询是作为单个列，返回多个值的子查询，无论子查询是否是自包含的。可使用IN谓词进行多值子查询操作。
没有明确的经验方法表示子查询比联接要好，所以，在写SQL时先以直观的形式对指定任务写一个解决方案查询，如果性能不满意，可以尝试查询调教来进行调优，如：使用子联接代替子查询或是使用子查询代替联接。
在子查询中可以不使用 DISTINCT 子句进智能行去重从而提高性能，因为数据库引擎足够智能，会考虑删除重复项，不用显式地去告诉它这样做。

---

4.2 相关子查询

相关子查询引用的表属性位于外部查询中，这意味着，该子查询依赖于外部查询并且无法单独调用。

SELECT * FROM Sales.Orders AS o1
WHERE o1.orderid =
        (SELECT MAX(o2.orderid) FROM Sales.Orders AS o2 WHERE o2.custid = o1.custid )

4.2.1 EXISTS 谓词

使用 EXISTS 可作为一个子查询作为输入，如果子查询返回任意行，EXISTS 返回TRUE，否则返回FALSE，所以 EXISTS 是一个两值逻辑运算。

SELECT c.* FROM Sales.Customers c
WHERE c.country = 'Spain' AND EXISTS ( SELECT * FROM Sales.Orders o WHERE c.custid = o.custid )

EXISTS 相当于短路计算功能，即SQL Server 只要知道子查询返回了一行或者没有数据返回就足够了， 它不需要处理所有符合条件的行。
与其他大多数情况不同，在 EXISTS的上下文子查询中使用星号(*)并无不妥之处，EXISTS只关心匹配行的存在，并不关心SELECT列表中指定的列。数据库会自动忽略查询的 SELECT 列表， 所以并不会带来大的性能损耗。相比于SELECT 1，使用SELECT *更让人容易理解，更加直观。

4.3 额外子查询知识

4.3.1 返回前一个或下一个值

--返回当前orderid的前一个orderid
SELECT o1.custid,o1.orderdate,o1.orderid,
    (SELECT MAX(o2.orderid) FROM Sales.Orders o2 WHERE o2.orderid < o1.orderid ) AS preId
FROM Sales.Orders o1

1460550519873.jpg

--返回当前orderid的下一个orderid
SELECT o1.custid,o1.orderdate,o1.orderid,
    (SELECT MIN(o2.orderid) FROM Sales.Orders o2 WHERE o2.orderid > o1.orderid ) AS nextId
FROM Sales.Orders o1

1460550555897.jpg

PS:在SQL2012中引入了 LAG和LEAD两个新开窗函数，允许按照指定排序从“前一个”或“后一个”返回一个元素。

4.3.2 使用运行聚合

运行聚合是随着时间累积值的聚合。

SELECT orderyear,o1.qty,
    (SELECT SUM(o2.qty) FROM Sales.OrderTotalsByYear o2 WHERE o2.orderyear <= o1.orderyear)
FROM Sales.OrderTotalsByYear o1
ORDER BY 1

1460550595851.jpg

4.3.3 不当子查询处理

4.3.3.1 NULL故障

T-SQL 使用三值逻辑，下面将演示当子查询涉及NULL标记并且没有考虑三值逻辑情况下演变而来的问题。

--返回没有订单的客户
SELECT * FROM Sales.Customers WHERE custid NOT IN ( SELECT custid FROM Sales.Orders  )

当前示例似乎按照期望的方式工作，即返回两个没有下订单的客户。

--插入客户ID为**NULL**的新订单到Orders表中。
INSERT INTO Sales.Orders (custid, empid, orderdate, requireddate, shippeddate, shipperid,freight, shipname, shipaddress, shipcity, shipregion,shippostalcode, shipcountry)
VALUES(NULL, 1, '20090212', '20090212', '20090212', 1, 123.00, N'abc', N'abc', N'abc', N'abc', N'abc', N'abc');

--查询
SELECT * FROM Sales.Customers WHERE custid NOT IN ( SELECT custid FROM Sales.Orders  )

这一次查询返回的是一个空集。

--可以查到记录
SELECT TOP 1 'Have Value' FROM Sales.Customers WHERE 3 NOT IN (1,2)
--没有返回数据
SELECT TOP 1 'Have Value' WHERE 3 NOT IN (1,2,null)
--等价于
SELECT 'Have Value' WHERE 3 <> 1 AND 3 <> 2 AND 3 <> NULL

我们知道IN返回的是TRUE，NOT IN则为FASLE。 如果Orders表中存在有一条custid为NULL的记录，Customers表的custid与该记录进行比较时产生的是UNKNOWN，所以整个NOT IN 的返回结果是UNKNOWN。这意味着，这是一种不知道custid是否出现在集合中，也不知道其是否求出现在集合中的情况。总之，当对一个至少返回一个NULL的子查询使用谓词时，外部查询总是返回空集合。
解决方案：

1. 列不允许NULL标记，将其定义为NOT NULL是什么必要的。

2. 编写所有查询时，应考虑三值逻辑（TRUE,FALSE和UNKNNOW）的所有可能的三种真值。

3. 考虑使用EXISTS代替IN

4.3.2 子查询列中的替换错误

在子查询中可以使用别名来查询正确的列。
PS:如果用提示插件就不会出现这种低级错误了。

---

练习

--1.返回Orders表中可以查到的活动最后一天的所有订单。表：Sales.Orders
SELECT * FROM Sales.Orders o1
WHERE o1.orderdate = (SELECT MAX(orderdate) FROM Sales.Orders )

--2.返回订单数量最多的客户的所有订单，表：Sales.Orders。注意：可能有多个用户具有相同的订单，可使用 TOP WIT TIES 来作为关联行绑定，详情参见2.1.7
SELECT * FROM Sales.Orders
WHERE custid IN
    (SELECT TOP 1 WITH TIES custid FROM Sales.Orders GROUP BY custid ORDER BY COUNT(*) DESC) --可使用联接

--3.返回20080501或之后没有下订单的雇员，表：HR.Employees，Sales.Orders，关联条件empid
SELECT * FROM HR.Employees
WHERE empid NOT
    IN (SELECT o1.empid FROM Sales.Orders o1 WHERE o1.orderdate > '20080501') --可使用EXISTS

--4.返回有客户但是没有雇员的国家，表:Sales.Customers，HR.Employees
SELECT DISTINCT country FROM Sales.Customers
WHERE country NOT IN (SELECT country FROM HR.Employees) ORDER BY 1

--5.返回每个客户活动最后一天下的所有订单，表:Orders
SELECT o1.custid,o1.orderdate,o1.empid FROM Sales.Orders o1
WHERE o1.orderdate =
    (SELECT MAX(o2.orderdate) AS orderdate FROM Sales.Orders o2 WHERE o1.custid = o2.custid )
ORDER BY 1

--6.返回2007年下了订单但是2008年没有下订单的客户，表：Orders,Customers
SELECT * FROM Sales.Customers c
WHERE EXISTS
    (SELECT o1.custid FROM Sales.Orders o1 WHERE orderdate > '20070101' AND orderdate < '20080101' AND o1.custid = c.custid )
AND NOT EXISTS
    (SELECT o2.custid FROM Sales.Orders o2 WHERE orderdate > '20080101' AND orderdate < '20090101' AND o2.custid = c.custid)--EXCEPT

--7.返回订购产品12的客户，表:customers,orders,orderdetails
SELECT * FROM Sales.Customers c
WHERE EXISTS
    (SELECT * FROM Sales.Orders o
    WHERE EXISTS
        (SELECT * FROM Sales.OrderDetails od
            WHERE productid = 12 AND od.orderid = o.orderid)
    AND o.custid = c.custid )  

--8. 计算每个客户及其月度的采购总量，表CustOrders
SELECT *,
    (SELECT SUM(c2.qty)
        FROM Sales.CustOrders c2
        WHERE c2.ordermonth <= c1.ordermonth AND c2.custid = c1.custid)
FROM Sales.CustOrders c1
ORDER BY 1

---

返回顶部