T-SQL基础教程Day2
单表查询
2.1 SELECT语句的元素
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;
1 FROM 从Sales.Orders表查询行
2 WHERE 仅筛选客户ID等于71的订单
3 GROUP BY按雇员ID和订单年度对订单分组
4 HAVING 仅筛选出大于1个订单的组
5 SELECT 返回每组的雇员ID、订单年度和订单数量
6 ORDER BY 按雇员ID和订单年度排序输出行
2.1.1 FROM子句
FROM子句是逻辑化处理的第一个查询子句，此子句指定要查询的表名称和进行多表运算的表运算符。
FROM Sales.Orders
建议：要在代码中始终使用架构限定式的对象名称。
如果不显示制定架构名称，SQL Server必须基于其隐式名称解析规则来确定所归属的架构，这造成了一些不必要的额外支出，并且会导致SQL Server选择不同的对象，而不是所期望的对象。
2.1.2 WHERE子句
可以指定一个谓词或逻辑表达式来筛选由From阶段返回的行。
在谈到查询性能时，WHERE子句具有重要意义。
基于筛选表达式，SQL Server将评估访问请求数据要使用的索引。通过使用索引，相比全表扫描，SQL Server有时可以用更少的工作获得所需数据。
T-SQL使用三值谓词逻辑
“返回TRUE”并不等同于“不返回FALSE”，还有UNKNOWN部分。
2.1.3 GROUP BY子句
GROUP BY阶段允许把前面逻辑查询阶段返回的行排列到组中，组是根据GROUP BY子句中指定的元素而确定的。
如果查询涉及分组，那么GROUP BY阶段的所有后续阶段，包括HAVING、SELECT、ORDER BY都是对组的操作，而不是对单个行进行操作。
不参与到GROUP BY列表中的元素仅允许作为一个聚合函数的输入，如COUNT、SUM、AVG、MIN或MAX。
注意，除了COUNT（*）之外，所有聚合函数忽略NULL标记。
2.1.4 HAVING子句
可以指定一个谓词来筛选组，而不是筛选单个行。
只有HAVING子句中逻辑表达式计算结果为TRUE的组，由HAVING阶段返回到下一个逻辑查询处理阶段。逻辑表达式计算结果为FALSE或UNKNOWN的组会被筛选掉。
由于HAVING子句是在行分组后被处理，所以可以在逻辑表达式中引用集合函数。
2.1.5 SELECT子句
SELECT子句是用户指定要返回到查询结果表中的属性（列）的地方。用户可基于所查询表的属性，在SELECT列表中建立表达式。
SELECT子句是在FROM、WHERE、GROUP BY和HAVING子句之后处理的，这意味着SELECT子句中分配给表达式的别名，不会存在于之前的SELECT相关子句中。
例如以下语句是错误的
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear > 2006;
SELECT语句保持唯一性的方法，是加上DISTINCT子句，删除重复行。
2.1.6 ORDER BY子句
出于展示效果的考虑，ORDER BY子句允许你对输出行进行排序。
理解SQL的最重要一点是表中没有确定的顺序，因为表是被假定为表示一个集合（或是多重集合，如果有重复数据的话），并且集合是没有顺序的。这意味着在查询表时没有制定ORDER BY子句，查询将返回一个表结果，并且SQL Server可以按任意顺序自由返回输出行。
标准SQL中把具有ORDER BY子句的结果称为游标——一个具有确定行顺序的非关系型结果。
返回表结果或是游标的差异：在一些语言元素或运算符需要操作表结果，而不是游标。例如：表表达式，集合运算符等。
2.1.7 TOP和OFFSET-FETCH筛选
1 TOP筛选
TOP选项是一个专有的T-SQL功能，用于限制查询返回的行数或行的百分比。
SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
2 OFFSET-FETCH筛选
TOP选项不是标准SQL，且不支持跳过功能，OFFSET-FETCH是标准SQL，SQL Server2012时引入。
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
2.1.8 开窗函数速览
开窗函数的功能是：对于基本查询中的每一行，按行的窗口（组）进行运算，并计算一个标量结果值。行的窗口使用OVER子句定义。
例子：
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;
2.2谓词和运算符
谓词是计算为TRUE、FALSE或UNKNOWN的逻辑表达式。
可以使用逻辑运算符来连接谓词，如AND、NOT和OR。
T-SQL支持的谓词，例如IN、BETWEEN、LIKE等。
T-SQL支持的比较运算符包括=、>、<、>=、<=、<>、!=、!>、!<。
最后三个运算符不是标准的。
T-SQL支持的算术运算符：+、-、*、/、以及取模%
以下列出了运算符的优先级（从最高到最低）
1 （）
2 *、/、%
3 +（正号）-（符号）+（加号）+（串联）-（减号）
4 =、>、<、>=、<=、<>、!=、！>、!<
5 NOT
6 AND
7 BETWEEN、IN、LIKE、OR
8 =（赋值）
2.3 CASE表达式
CASE表达式是一个标量表达式，返回一个基于条件逻辑的值。
需要注意的是，CASE是表达式而不是语句，它不允许你控制活动流或做一些基于条件逻辑的操作。
不过，它的返回值缺失基于条件逻辑的。
CASE表达式具有“简单”和“搜索”两种格式。
简单格式允许在一个可能值列表中比较一个值或标量表达式，并返回第一个匹配值。如果无匹配值，则返回ELSE子句中的值或NULL。
CASE搜索格式更加灵活，允许你再WHEN子句中指定谓词或逻辑表达式，二不是限制于进行相等比较。
CASE表达式返回第一个WHEN逻辑表达式计算结果为TRUE的相关联THEN子句中的值。如果没有WHEN表达式计算结果为TRUE，则返回ELSE子句（如有）中的值或NULL。
2.4 NULL标记
SQL不同的语言元素对于UNKNOWN有不同的处理方式。
对于查询筛选而言，SQL的正确处理定义是“接受TRUE”，意味着FALSE和UNKNOWN都会被筛选掉。
对于CHECK约束而言，SQL的正确处理定义是“拒绝FALSE”，意味着TRUE和UNKNOWN会被接受。
UNKNOWN，当否定它时，仍然会得到UNKNOWN值。
比较两个NULL标记的表达式（NULL=NULL）计算为UNKNOWN。
SQL提供了谓词IS NULL和IS NOT NULL来解决比较问题。
对于分组和排序目的，两个NULL标记被视为相等。
即GROUP BY子句将所有NULL标记排列为一组。
ORDER BY子句也将所有NULL标记排序在一起。
2.5 同时操作
SQL支持一个称作同时操作（all at once operations）的概念，即出现在同一逻辑处理阶段的所有表达式在同一时间点进行逻辑计算。
select 1 as a, 2 as b into #allatonce
update #allatonce set a=b,b=a
select * from #allatonce
2.6.1 数据类型
SQL Server支持两种字符数据类型——常规和Unicode。
常规数据类型包括CHAR和VARCHAR，
Unicode数据类型包括NCHAR和NVARCHAR。
常规字符的每个字符使用一个字节，而Unicode数据的每个字符要求2个字节，并且需要一个代理项对时，要求4个字节。
（代理项（Surrogate），是一种仅在 UTF-16 中用来表示补充字符的方法。在 UTF-16 中，为补充字符分配两个 16 位的 Unicode 代码单元：
第一个代码单元，被称为高代理项代码单元或前导代码单元；
第二个代码单元，被称为低代理项代码单元或尾随代码单元。
这两个代码单元组合在一起，就被称为代理项对。）
在表示常规字符文本时，只需使用单引号，表示Unicode字符文本时，需要指定字符N（即National）作为前缀。
当使用MAX说明符来定义可变长度数据类型时，当大小在8000内时，可以内置存储在行内，超过时，作为大型对象（LOB）存储在行外部。
2.6.2 排序规则
排序规则是一个字符数据属性，其封装了多项内容，包括语言支持、排序顺序、区分大小写、区分重音等。
CI数据不区分大小写
AS数据区分重音
数据库的排序规则决定了数据库对象元数据的排序规则，包括对象和列名。如果区分大小写，则可以创建名为t1和T1的两个表。
2.6.3 运算符和函数
1 字符串连接（加号运算符和CONCAT函数）
2 SUBSTRING函数
3 LEFT和RIGHT函数
4 LEN和DATALENGTH函数
5 CHARINDEX函数
6 PATINDEX函数
7 REPLACE函数
8 REPLICATE函数
9 STUFF函数
10 UPPER和LOWER函数
11 RTRIM和LTRIM函数
12 FORMAT函数
13 COMPRESS和DECOMPRESS函数
14 STRING_SPLIT函数
15 LIKE谓词
2.7.2 日期和时间常量
SQL Server不同日期和时间的常量表示方法，相反，它允许用户指定可以被显式或隐式转换为日期和时间数据类型的不同类型常量。
使用字符串表示日期和时间值是最好的做法。
2.7.3 独立使用日期和时间
SQL Server 2008引入了独立的DATE和TIME数据类型。
2.7.5 日期和时间函数
1 当前日期和时间
2 CAST、CONVERT和PARSE函数，及其TRY_对应函数
3 SWITCHOFFSET函数
4 TODATETIMEOFFSET函数
5 DATEADD函数
6 DATEDIFF函数
7 DATEPART函数
8 YEAR、MONTH和DAY函数
9 DATENAME函数
10 ISDATE函数
11 FROMPARTS函数
12 EOMONTH函数
2.8.1 目录视图
目录视图为数据库中的对象提供了非常详细的信息。
如果想列出数据库中的表和架构，可以查询sys.tables视图。
SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name
FROM sys.tables;
要获取表中列的信息，可以查询sys.columns表。
SELECT
name AS column_name,
TYPE_NAME(system_type_id) AS column_type,
max_length,
collation_name,
is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');
2.8.2 信息结构视图
信息架构视图是一个视图集合，位于名为INFORMATION_SCHEMA的架构中，并以标准方式提供元数据信息。
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = N'BASE TABLE';
SELECT
COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'Sales'
AND TABLE_NAME = N'Orders';
2.8.3 系统存储过程和函数
系统存储过程和函数用来内部查询系统目录，有整理后的元数据信息。
EXEC sys.sp_tables;
EXEC sys.sp_help
@objname = N'Sales.Orders';
EXEC sys.sp_columns
@table_name = N'Orders',
@table_owner = N'Sales';
EXEC sys.sp_helpconstraint
@objname = N'Sales.Orders';
SELECT
SERVERPROPERTY('ProductLevel');
SELECT
DATABASEPROPERTYEX(N'TSQL2012', 'Collation');
SELECT
OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'), 'TableHasPrimaryKey');
SELECT
COLUMNPROPERTY(OBJECT_ID(N'Sales.Orders'), N'shipcountry', 'AllowsNull');
章节代码
---------------------------------------------------------------------
-- Microsoft SQL Server 2012 T-SQL Fundamentals
-- Chapter 02 - Single-Table Queries
-- ?Itzik Ben-Gan
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Elements of the SELECT Statement
---------------------------------------------------------------------
-- Listing 2-1: Sample Query
USE TSQL2012;
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;
---------------------------------------------------------------------
-- The FROM Clause
---------------------------------------------------------------------
SELECT orderid, custid, empid, orderdate, freight
FROM Sales.Orders;
---------------------------------------------------------------------
-- The WHERE Clause
---------------------------------------------------------------------
SELECT orderid, empid, orderdate, freight
FROM Sales.Orders
WHERE custid = 71;
---------------------------------------------------------------------
-- The GROUP BY Clause
---------------------------------------------------------------------
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
SELECT
empid,
YEAR(orderdate) AS orderyear,
SUM(freight) AS totalfreight,
COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
/*
SELECT empid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
*/
SELECT
empid,
YEAR(orderdate) AS orderyear,
COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate);
---------------------------------------------------------------------
-- The HAVING Clause
---------------------------------------------------------------------
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
---------------------------------------------------------------------
-- The SELECT Clause
---------------------------------------------------------------------
SELECT orderid orderdate
FROM Sales.Orders;
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
/*
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear > 2006;
*/
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE YEAR(orderdate) > 2006;
/*
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING numorders > 1;
*/
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
-- Listing 2-2: Query Returning Duplicate Rows
SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71;
-- Listing 2-3: Query With a DISTINCT Clause
SELECT DISTINCT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71;
SELECT *
FROM Sales.Shippers;
/*
SELECT orderid,
YEAR(orderdate) AS orderyear,
orderyear + 1 AS nextyear
FROM Sales.Orders;
*/
SELECT orderid,
YEAR(orderdate) AS orderyear,
YEAR(orderdate) + 1 AS nextyear
FROM Sales.Orders;
---------------------------------------------------------------------
-- The ORDER BY Clause
---------------------------------------------------------------------
-- Listing 2-4: Query Demonstrating the ORDER BY Clause
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;
SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;
/*
SELECT DISTINCT country
FROM HR.Employees
ORDER BY empid;
*/
---------------------------------------------------------------------
-- The TOP and OFFSET-FETCH Filters
---------------------------------------------------------------------
---------------------------------------------------------------------
-- The TOP Filter
---------------------------------------------------------------------
-- Listing 2-5: Query Demonstrating the TOP Option
SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
-- Listing 2-6: Query Demonstrating TOP with Unique ORDER BY List
SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;
SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
---------------------------------------------------------------------
-- The OFFSET-FETCH Filter
---------------------------------------------------------------------
-- OFFSET-FETCH
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
---------------------------------------------------------------------
-- A Quick Look at Window Functions
---------------------------------------------------------------------
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;
---------------------------------------------------------------------
-- Predicates and Operators
---------------------------------------------------------------------
-- Predicates: IN, BETWEEN, LIKE
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
-- Comparison operators: =, >, <, >=, <=, <>, !=, !>, !<
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101';
-- Logical operators: AND, OR, NOT
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101'
AND empid IN(1, 3, 5);
-- Arithmetic operators: +, -, *, /, %
SELECT orderid, productid, qty, unitprice, discount,
qty * unitprice * (1 - discount) AS val
FROM Sales.OrderDetails;
-- Operators Precedence
-- AND precedes OR
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE
custid = 1
AND empid IN(1, 3, 5)
OR custid = 85
AND empid IN(2, 4, 6);
-- Equivalent to
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE
( custid = 1
AND empid IN(1, 3, 5) )
OR
( custid = 85
AND empid IN(2, 4, 6) );
-- *, / precedes +, -
SELECT 10 + 2 * 3 -- 16
SELECT (10 + 2) * 3 -- 36
---------------------------------------------------------------------
-- CASE Expression
---------------------------------------------------------------------
-- Simple
SELECT productid, productname, categoryid,
CASE categoryid
WHEN 1 THEN 'Beverages'
WHEN 2 THEN 'Condiments'
WHEN 3 THEN 'Confections'
WHEN 4 THEN 'Dairy Products'
WHEN 5 THEN 'Grains/Cereals'
WHEN 6 THEN 'Meat/Poultry'
WHEN 7 THEN 'Produce'
WHEN 8 THEN 'Seafood'
ELSE 'Unknown Category'
END AS categoryname
FROM Production.Products;
-- Searched
SELECT orderid, custid, val,
CASE
WHEN val < 1000.00 THEN 'Less than 1000'
WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'
WHEN val > 3000.00 THEN 'More than 3000'
ELSE 'Unknown'
END AS valuecategory
FROM Sales.OrderValues;
---------------------------------------------------------------------
-- NULLs
---------------------------------------------------------------------
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = N'WA';
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA';
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = NULL;
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region IS NULL;
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA'
OR region IS NULL;
---------------------------------------------------------------------
-- All-At-Once Operations
---------------------------------------------------------------------
/*
SELECT
orderid,
YEAR(orderdate) AS orderyear,
orderyear + 1 AS nextyear
FROM Sales.Orders;
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 AND col2/col1 > 2;
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE
CASE
WHEN col1 = 0 THEN 'no' -- or 'yes' if row should be returned
WHEN col2/col1 > 2 THEN 'yes'
ELSE 'no'
END = 'yes';
*/
/*
SELECT col1, col2
FROM dbo.T1
WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1);
*/
select 1 as a, 2 as b into #allatonce
update #allatonce set a=b,b=a
select * from #allatonce
---------------------------------------------------------------------
-- Working with Character Data
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Collation
---------------------------------------------------------------------
SELECT name, description
FROM sys.fn_helpcollations();
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'davis';
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';
---------------------------------------------------------------------
-- Operators and Functions
---------------------------------------------------------------------
-- Concatenation
SELECT empid, firstname + N' ' + lastname AS fullname
FROM HR.Employees;
-- Listing 2-7: Query Demonstrating String Concatenation
SELECT custid, country, region, city,
country + N',' + region + N',' + city AS location
FROM Sales.Customers;
-- convert NULL to empty string
SELECT custid, country, region, city,
country + COALESCE( N',' + region, N'') + N',' + city AS location
FROM Sales.Customers;
-- using CONCAT (2012-only)
SELECT custid, country, region, city,
CONCAT(country, N',' + region, N',' + city) AS location
FROM Sales.Customers;
-- Functions
SELECT SUBSTRING('abcde', 1, 3); -- 'abc'
SELECT RIGHT('abcde', 3); -- 'cde'
SELECT LEN(N'abcde'); -- 5
SELECT DATALENGTH(N'abcde'); -- 10
SELECT CHARINDEX(' ','Itzik Ben-Gan'); -- 6
SELECT PATINDEX('%[0-9]%', 'abcd123efgh'); -- 5
SELECT REPLACE('1-a 2-b', '-', ':'); -- '1:a 2:b'
SELECT empid, lastname,
LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur
FROM HR.Employees;
SELECT REPLICATE('abc', 3); -- 'abcabcabc'
SELECT supplierid,
RIGHT(REPLICATE('0', 9) + CAST(supplierid AS VARCHAR(10)),
10) AS strsupplierid
FROM Production.Suppliers;
SELECT STUFF('xyz', 2, 1, 'abc'); -- 'xabcz'
SELECT UPPER('Itzik Ben-Gan'); -- 'ITZIK BEN-GAN'
SELECT LOWER('Itzik Ben-Gan'); -- 'itzik ben-gan'
SELECT RTRIM(LTRIM(' abc ')); -- 'abc'
SELECT FORMAT(1759, '0000000000'); -- '0000001759'
SELECT COMPRESS('abcd') as Comp
SELECT
CAST(
DECOMPRESS(COMPRESS(N'abcd'))
AS NVARCHAR(MAX));
SELECT
CAST(
COMPRESS(N'abcd')
AS NVARCHAR(MAX));
SELECT CAST(value AS INT) AS myvalue
FROM STRING_SPLIT('10248,10249,10250', ',') AS S;
---------------------------------------------------------------------
-- LIKE Predicate
---------------------------------------------------------------------
-- Last name starts with D
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
-- Second character in last name is e
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'_e%';
-- First character in last name is A, B or C
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[ABC]%';
-- First character in last name is A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[A-E]%';
-- First character in last name is not A through E
SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[^A-E]%';
---------------------------------------------------------------------
-- Working with Date and Time Data
---------------------------------------------------------------------
-- Literals
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = CAST('20070212' AS DATETIME);
SET LANGUAGE British;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE British;
SELECT CAST('20070212' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('20070212' AS DATETIME);
SELECT CONVERT(DATETIME, '02/12/2007', 101);
SELECT CONVERT(DATETIME, '02/12/2007', 103);
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US');
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');
-- Working with Date and Time Separately
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = '20070212';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070212'
AND orderdate < '20070213';
SELECT CAST('12:30:15.123' AS DATETIME);
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007;
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101';
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';
-- Functions
-- Current Date and Time
SELECT
GETDATE() AS [GETDATE],
CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],
GETUTCDATE() AS [GETUTCDATE],
SYSDATETIME() AS [SYSDATETIME],
SYSUTCDATETIME() AS [SYSUTCDATETIME],
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];
SELECT
CAST(SYSDATETIME() AS DATE) AS [current_date],
CAST(SYSDATETIME() AS TIME) AS [current_time];
-- The CAST, CONVERT and PARSE Functions and their TRY_ Counterparts
SELECT CAST('20090212' AS DATE);
SELECT CAST(SYSDATETIME() AS DATE);
SELECT CAST(SYSDATETIME() AS TIME);
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);
SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);
SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US');
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');
-- SWITCHOFFSET
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00');
-- TODATETIMEOFFSET
/*
UPDATE dbo.T1
SET dto = TODATETIMEOFFSET(dt, theoffset);
*/
-- DATEADD
SELECT DATEADD(year, 1, '20090212');
-- DATEDIFF
SELECT DATEDIFF(day, '20080212', '20090212');
SELECT
DATEADD(
day,
DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101');
SELECT
DATEADD(
month,
DATEDIFF(month, '20010101', CURRENT_TIMESTAMP), '20010101');
SELECT
DATEADD(
month,
DATEDIFF(month, '20091231', CURRENT_TIMESTAMP), '20091231');
-- DATEPART
SELECT DATEPART(month, '20090212');
-- DAY, MONTH, YEAR
SELECT
DAY('20090212') AS theday,
MONTH('20090212') AS themonth,
YEAR('20090212') AS theyear;
-- DATENAME
SELECT DATENAME(month, '20090212');
SELECT DATENAME(year, '20090212');
-- ISDATE
SELECT ISDATE('20090212');
SELECT ISDATE('20090230');
-- fromparts
SELECT
DATEFROMPARTS(2012, 02, 12),
DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7),
DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997),
DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7),
SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30),
TIMEFROMPARTS(13, 30, 5, 1, 7);
-- EOMONTH
SELECT EOMONTH(SYSDATETIME());
-- orders placed on last day of month
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);
---------------------------------------------------------------------
-- Querying Metadata
---------------------------------------------------------------------
-- Catalog Views
USE TSQL2012;
SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name
FROM sys.tables;
SELECT
name AS column_name,
TYPE_NAME(system_type_id) AS column_type,
max_length,
collation_name,
is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');
-- Information Schema Views
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = N'BASE TABLE';
SELECT
COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'Sales'
AND TABLE_NAME = N'Orders';
-- System Stored Procedures and Functions
EXEC sys.sp_tables;
EXEC sys.sp_help
@objname = N'Sales.Orders';
EXEC sys.sp_columns
@table_name = N'Orders',
@table_owner = N'Sales';
EXEC sys.sp_helpconstraint
@objname = N'Sales.Orders';
SELECT
SERVERPROPERTY('ProductLevel');
SELECT
DATABASEPROPERTYEX(N'TSQL2012', 'Collation');
SELECT
OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'), 'TableHasPrimaryKey');
SELECT
COLUMNPROPERTY(OBJECT_ID(N'Sales.Orders'), N'shipcountry', 'AllowsNull');
练习代码
---------------------------------------------------------------------
-- Microsoft SQL Server 2012 T-SQL Fundamentals
-- Chapter 02 - Single-Table Queries
-- Solutions
-- ?Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- Return orders placed on June 2007
-- Tables involved: TSQL2012 database, Sales.Orders table
-- Desired output:
orderid orderdate custid empid
----------- ----------------------- ----------- -----------
10555 2007-06-02 00:00:00.000 71 6
10556 2007-06-03 00:00:00.000 73 2
10557 2007-06-03 00:00:00.000 44 9
10558 2007-06-04 00:00:00.000 4 1
10559 2007-06-05 00:00:00.000 7 6
10560 2007-06-06 00:00:00.000 25 8
10561 2007-06-06 00:00:00.000 24 2
10562 2007-06-09 00:00:00.000 66 1
10563 2007-06-10 00:00:00.000 67 2
10564 2007-06-10 00:00:00.000 65 4
10565 2007-06-11 00:00:00.000 51 8
10566 2007-06-12 00:00:00.000 7 9
10567 2007-06-12 00:00:00.000 37 1
10568 2007-06-13 00:00:00.000 29 3
10569 2007-06-16 00:00:00.000 65 5
10570 2007-06-17 00:00:00.000 51 3
10571 2007-06-17 00:00:00.000 20 8
10572 2007-06-18 00:00:00.000 5 3
10573 2007-06-19 00:00:00.000 3 7
10574 2007-06-19 00:00:00.000 82 4
10575 2007-06-20 00:00:00.000 52 5
10576 2007-06-23 00:00:00.000 80 3
10577 2007-06-23 00:00:00.000 82 9
10578 2007-06-24 00:00:00.000 11 4
10579 2007-06-25 00:00:00.000 45 1
10580 2007-06-26 00:00:00.000 56 4
10581 2007-06-26 00:00:00.000 21 3
10582 2007-06-27 00:00:00.000 6 3
10583 2007-06-30 00:00:00.000 87 2
10584 2007-06-30 00:00:00.000 7 4
(30 row(s) affected)
-- Solution
USE TSQL2012;
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate >= '20070601'
AND orderdate < '20070701';
-- 2
-- Return orders placed on the last day of the month
-- Tables involved: Sales.Orders table
-- Desired output:
orderid orderdate custid empid
----------- ----------------------- ----------- -----------
10269 2006-07-31 00:00:00.000 89 5
10317 2006-09-30 00:00:00.000 48 6
10343 2006-10-31 00:00:00.000 44 4
10399 2006-12-31 00:00:00.000 83 8
10432 2007-01-31 00:00:00.000 75 3
10460 2007-02-28 00:00:00.000 24 8
10461 2007-02-28 00:00:00.000 46 1
10490 2007-03-31 00:00:00.000 35 7
10491 2007-03-31 00:00:00.000 28 8
10522 2007-04-30 00:00:00.000 44 4
10583 2007-06-30 00:00:00.000 87 2
10584 2007-06-30 00:00:00.000 7 4
10616 2007-07-31 00:00:00.000 32 1
10617 2007-07-31 00:00:00.000 32 4
10686 2007-09-30 00:00:00.000 59 2
10687 2007-09-30 00:00:00.000 37 9
10725 2007-10-31 00:00:00.000 21 4
10806 2007-12-31 00:00:00.000 84 3
10807 2007-12-31 00:00:00.000 27 4
10987 2008-03-31 00:00:00.000 19 8
10988 2008-03-31 00:00:00.000 65 3
10989 2008-03-31 00:00:00.000 61 2
11060 2008-04-30 00:00:00.000 27 2
11061 2008-04-30 00:00:00.000 32 4
11062 2008-04-30 00:00:00.000 66 4
11063 2008-04-30 00:00:00.000 37 3
(26 row(s) affected)
-- Solution
-- in SQL Server 2012
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);
-- pre-SQL Server 2012 (advanced)
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');
-- 3
-- Return employees with last name containing the letter 'a' twice or more
-- Tables involved: HR.Employees table
-- Desired output:
empid firstname lastname
----------- ---------- --------------------
9 Zoya Dolgopyatova
(1 row(s) affected)
-- Solution
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';
-- 4
-- Return orders with total value(qty*unitprice) greater than 10000
-- sorted by total value
-- Tables involved: Sales.OrderDetails table
-- Desired output:
orderid totalvalue
----------- ---------------------
10865 17250.00
11030 16321.90
10981 15810.00
10372 12281.20
10424 11493.20
10817 11490.70
10889 11380.00
10417 11283.20
10897 10835.24
10353 10741.60
10515 10588.50
10479 10495.60
10540 10191.70
10691 10164.80
(14 row(s) affected)
-- Solution
SELECT orderid, SUM(qty*unitprice) AS totalvalue
FROM Sales.OrderDetails
GROUP BY orderid
HAVING SUM(qty*unitprice) > 10000
ORDER BY totalvalue DESC;
-- 5
-- Return the three ship countries with the highest average freight in 2007
-- Tables involved: Sales.Orders table
-- Desired output:
shipcountry avgfreight
--------------- ---------------------
Austria 178.3642
Switzerland 117.1775
Sweden 105.16
(3 row(s) affected)
-- Solution
SELECT TOP (3) shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC;
-- in SQL Server 2012
SELECT shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;
-- 6
-- Calculate row numbers for orders
-- based on order date ordering (using order id as tiebreaker)
-- for each customer separately
-- Tables involved: Sales.Orders table
-- Desired output:
custid orderdate orderid rownum
----------- ----------------------- ----------- --------------------
1 2007-08-25 00:00:00.000 10643 1
1 2007-10-03 00:00:00.000 10692 2
1 2007-10-13 00:00:00.000 10702 3
1 2008-01-15 00:00:00.000 10835 4
1 2008-03-16 00:00:00.000 10952 5
1 2008-04-09 00:00:00.000 11011 6
2 2006-09-18 00:00:00.000 10308 1
2 2007-08-08 00:00:00.000 10625 2
2 2007-11-28 00:00:00.000 10759 3
2 2008-03-04 00:00:00.000 10926 4
...
(830 row(s) affected)
-- Solution
SELECT custid, orderdate, orderid,
ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;
-- 7
-- Figure out and return for each employee the gender based on the title of courtesy
-- Ms., Mrs. - Female, Mr. - Male, Dr. - Unknown
-- Tables involved: HR.Employees table
-- Desired output:
empid firstname lastname titleofcourtesy gender
----------- ---------- -------------------- ------------------------- -------
1 Sara Davis Ms. Female
2 Don Funk Dr. Unknown
3 Judy Lew Ms. Female
4 Yael Peled Mrs. Female
5 Sven Buck Mr. Male
6 Paul Suurs Mr. Male
7 Russell King Mr. Male
8 Maria Cameron Ms. Female
9 Zoya Dolgopyatova Ms. Female
(9 row(s) affected)
-- Solutions
SELECT empid, firstname, lastname, titleofcourtesy,
CASE titleofcourtesy
WHEN 'Ms.' THEN 'Female'
WHEN 'Mrs.' THEN 'Female'
WHEN 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
-- 8 (advanced, optional)
-- Return for each customer the customer ID and region
-- sort the rows in the output by region
-- having NULLs sort last (after non-NULL values)
-- Note that the default in T-SQL is that NULL sort first
-- Tables involved: Sales.Customers table
-- Desired output:
custid region
----------- ---------------
55 AK
10 BC
42 BC
45 CA
37 Co. Cork
33 DF
71 ID
38 Isle of Wight
46 Lara
78 MT
...
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
9 NULL
11 NULL
...
(91 row(s) affected)
-- Solution
SELECT custid, region
FROM Sales.Customers
ORDER BY
CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;
