SQL Server2012 T-SQL基础教程--读书笔记(1-4章)
示例数据库:点我
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语句的查询顺序:
-
FROM
-
WHERE
-
GROUP BY
-
HAVING
-
SELECT
-
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 谓词
-
%
通配符,代表任意字符 -
_
通配符,表示单个字符 -
[ ]
通配符,代表单个字符必须是指定[ ]
内的字符之一 -
[-]
通配符,代表单个字符必须在指定的范围内 -
[^ ]
通配符,代表指定的字符不在指定的字符列表或范围内。 -
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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/6cd75ae63c546ba5b301325fc1bea91a.png)
--查询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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/69bfba2e4c68f2bc0e8e477abcc03923.png)
2.8.2 信息架构视图
信息架构视图是一个视图合集,其位于 INFORMATION_SCHEMA
的架构中,并以标准方式提供元数据信息。
--查询所有的基本表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
![1460383700927.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/8713353105003130b85c0d842d919798.png)
--查询 Sales.Orders 所有的column
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Sales' AND TABLE_NAME = 'orders'
![1460383929710.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/87a4363291f6e8f2955abcbb667cf19f.png)
2.8.3 系统存储过程和函数
--返回当前数据库中能够查询到的对象列表(表、视图等)
EXEC sys.sp_tables
![1460383952232.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/ab344e2d7265b2fb952dd6f59e81e821.png)
--返回orders表的详细信息
EXEC sys.sp_help 'Sales.Orders'
![1460384002912.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/123bbc33b9c2296b19e80fbf61d155aa.png)
--返回orders表的column
EXEC sys.sp_columns @table_name = 'orders', @schema_name = 'Sales'
![1460383680829.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/1ef3b5e7e6df12b9f41a79ba00c1e579.png)
--返回orders表的约束信息,参数必须包含schema
EXEC sys.sp_helpconstraint 'Sales.orders'
![1460384107103.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/6668384c10b58976617019654e7b3124.png)
--返回指定数据为所请求的属性信息
SELECT DATABASEPROPERTYEX(N'TSQL2012','Collation')
![1460384124796.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/bfc43de4a0b54e4bc6a25b7a05422d52.png)
--返回指定对象名称的所请求属性信息
SELECT OBJECTPROPERTY(OBJECT_ID('Sales.Orders'),'TableHasPrimaryKey')
![1460384170971.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/2d6b883940148531899902ab7b14c513.png)
--返回指定column所请求的信息
SELECT COLUMNPROPERTY(OBJECT_ID('Sales.Orders'),'orderdate','AllowsNull')
![1460384190281.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/1b3650c30fe8c21984993a6612952e3b.png)
练习
--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
-
在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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/91f1e872c319345effaa818e2111a256.png)
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 其他外联接知识
-
包含缺失值
-
从外联接的非保留侧筛选属性
当需要检查涉及外部联接的代码查找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'
-
在多联接查询中使用外联接
在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
-
-
外部联接使用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'
-
1460550080821.jpg -
1460550162466.jpg -
1460550215348.jpg -
1460550241333.jpg -
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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/8d971d003e2b7334068abf42f0aa2234.png)
--返回当前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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/d054963f565e37b85848afceebaa6ba5.png)
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 enter description here](https://i-blog.csdnimg.cn/blog_migrate/74916edd8a438e447d2357777a215e85.png)
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的子查询使用谓词时,外部查询总是返回空集合。
解决方案:
-
列不允许NULL标记,将其定义为NOT NULL是什么必要的。
-
编写所有查询时,应考虑三值逻辑(TRUE,FALSE和UNKNNOW)的所有可能的三种真值。
-
考虑使用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
返回顶部