第二章.SELECT语句的元素

USE TSQL2012;

SELECT 
	empid,
	YEAR(orderdate) AS orderyear,	--获取时间点钟的年
	COUNT(*) AS numorders			--通过GROUP BY分组每组的行数量	
FROM Sales.Orders
WHERE custid = 71					--仅筛选 客户ID = 71的订单			
GROUP BY empid,YEAR(orderdate)		--按照 雇员ID 与 订单年度 进行分组
HAVING COUNT(*) > 1					--仅筛选出大于1的订单组
ORDER BY empid,orderyear;			--按照 雇员ID 和 订单 输出行

GROUP BY 后的 HAVING和ORDER BY操作都是对于组的操作,而不是对单行操作。

不参加到GROUP BY列表中的元素仅允许作为一个聚合函数的输入，如COUNT、SUM、AVG、MIN或MAX。

SELECT 
	custid,
	empid,
	YEAR(orderdate)
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid,YEAR(orderdate)

像以上情况 未参与到GROUP BY的列表中的属性例如custid会被系统当成一个聚合函数操作，从而产生以下错误。

选择列表中的列 ‘Sales.Orders.custid’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

聚合函数

除COUNT(*)之外所有聚合操作都忽略NULL标记。

例如：一个名为qty的列，具有5行，数值为。

30、10、NULL、10、10

表达式COUNT(*)将返回5、而COUNT(qty)将返回4。

指定DISTINCT关键字可以去重复值。

COUNT(DISTINCT qty)将返回2。

SUM(DISTINCT qty)将返回40，SUM(qty)将返回60。

AVG(DISTINCT qty)将返回20，AVG(qty)将返回20。

HAVING子句

以下是第一段代码执行结果

empid orderyear numorders

​ 1 2007 2
​ 1 2008 3
​ 2 2008 2
​ 3 2007 2
​ 4 2008 3
​ 5 2007 3
​ 6 2007 3
​ 7 2008 2
​ 8 2007 4

---

以下是去掉HAVING子句的执行结果

empid orderyear numorders

​ 1 2006 1
​ 1 2007 2
​ 1 2008 3
​ 2 2006 1
​ 2 2007 1
​ 2 2008 2
​ 3 2007 2
​ 4 2007 1
​ 4 2008 3
​ 5 2007 3
​ 6 2007 3
​ 6 2008 1
​ 7 2007 1
​ 7 2008 2
​ 8 2007 4
​ 9 2006 1

比较发现表2中numorders列所有为1的组，都消失在表1中。

HAVING子句是在分组之后进行处理，所以可以在逻辑表达式中引用集合函数。

执行顺序

----FROM

----WHERE

----GROUP BY

----HAVING

----SELECT

----------表达式

----------DISTINCT

----ORDER BY

-----------TOP/OFFSET-FETCH

ORDER BY

默认升序、ASC升序、DESC降序

T-SQL允许在ORDER BY中出现SELECT中未出现的的元素，这意味着你无需按照输出排序。

但是，如果指定了DISTINCT，则ORDER BY只能出现是出现在SELECT列表中的元素。此限制背后的原因是，在指定DISTINCT后，单个结果行可能代表多个源行，因此ORDER BY子句中可能会有多个值可用。

TOP和OFFSET-FETCH筛选

--选取最顶部5行 即 时间最近的5个订单
SELECT TOP(5)
	*
FROM Sales.Orders
ORDER BY orderdate DESC
--在TOP后面加 PERCENT 代表前0.5% 
--会向上舍入即若计算出的百分比是5.32行会变成6行
SELECT TOP(0.5) PERCENT
	*
FROM Sales.Orders
ORDER BY orderdate DESC
--在TOP后面加上WITH TIES会要求系统除返回前5行外，还会返回与第5行(orderdate)值相同的行
SELECT TOP(5) WITH TIES
	*
FROM Sales.Orders
ORDER BY orderdate DESC

TOP有两个缺陷，不是标准SQL和不支持跳过功能，标准SQL中有类似的筛选称为OFFSET-FETCH

SELECT 
	*
FROM Sales.Orders
ORDER BY orderdate DESC
OFFSET 50 ROWS FETCH NEXT 8 ROWS ONLY;
--跳过前50行后，去接下来的8行

OFFSET FETCH查询的前提是必须具有ORDER BY子句。

如果没OFFSET的支持不能进行FETCH操作，不跳过只能是 OFFSET 0 ROWS。

SQL中ROWS与ROW、NEXT与FIRST 可以相互替换

从支持跳过来看 OFFSET FETCH比TOP更加灵活，但是OFFSET FETCH没有PERCENT 和WITH TIES选项。

在实现时，OFFSET FETCH与TOP都能可用时尽量用OFFSET FETCH

谓词和运算符

 SELECT
	orderid,
	empid,
	orderdate
 FROM Sales.Orders
 WHERE orderid IN(10248,10249,10250)
 --获取orderid在()这个集合里面的行
 
 SELECT
	orderid,
	empid,
	orderdate
 FROM Sales.Orders
 WHERE orderid BETWEEN 10300 AND 10310;
 --获取orderid在10300到10310中间的行
 
 SELECT
	empid,
	firstname,
	lastname
 FROM HR.Employees
 WHERE lastname LIKE N'D%';
 --获取lastname属性第一个字母为D的行
 --前缀字母N的使用，代表National，用来表示字符串是一个Unicode数据类型(NCHAR或NVARCHAR)

其它运算符还有=、>、<、>=、<=、<>(!=)、!>和!<

SELECT
	orderid,
	empid,
	orderdate
 FROM Sales.Orders
 WHERE orderdate >= '20080101'
	AND empid IN (1,3,5);
--2008年之后 和 雇员编号是 1或3或5 的订单

算数运算符 +、-、*、/、%

SELECT
	orderid,
	productid,
	qty,
	unitprice,
	discount,
	qty * unitprice * (1 - discount) AS VAL 
FROM Sales.OrderDetails

比较优先级

1、（）

2、*、/、%

3、+、-

4、==、>、<、>=、<=、<>(!=)、!>、!<

5、NOT

6、AND

7、BETWEEN、IN、NOT、OR

8、=

CASE表达式

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;
--当 categoryID = 1 时 categoryname 列显示 Beverages

SELECT
	orderid,
	custid,
	val,
	CASE
		WHEN val < 1000.0					THEN 'lESS THAN 1000'
		WHEN val BETWEEN 1000.0 AND 3000.0	THEN 'BETWEEN 1000 AND 3000'
		WHEN val > 3000.0					THEN 'MORE THAN 3000'
	END AS VALUECATEGORY
FROM Sales.OrderValues

NULL标记

SELECT
	custid,
	country,
	region,
	city
FROM Sales.Customers
WHERE region IS NULL;
--region = null 会返回 一个unknown结果

运算符和函数

SELECT
	empid,
	firstname + N' '+ lastname AS fullname 
FROM HR.Employees;

通过连接fisrstname、空格、lastname生成fullname结果列。

--从字符串中提取一个子串
SELECT SUBSTRING('ASDFSADF',2,4);
--结果是：SDFS - 从第2个位置提取其后4个字符

--从左提取一个子串
SELECT LEFT('HELLOWORLD',3);
--结果是：HEl - 从左开始提取3个字符

--从右提取一个子串
SELECT RIGHT('HELLOWORLD',3);
--结果是：RLD - 从右开始提取3个字符

--返回字符长度
SELECT LEN('HELLOWORLD ');
SELECT DATALENGTH('HELLOWORLD ');
--结果是:10、11  - 区别是LEN会去掉尾部空格不计，而DATALENGTH并不会

--返回子串在字符串中第一次出现的位置
SELECT CHARINDEX('L','HELLOWORLD');
--结果是：3

--返回模式在字符串中第一次出现的位置
SELECT PATINDEX('%[0-9]%','HELLO343WORLD');
--结果是：6

--使用某一字符串 替代 另一字符串中的 子串
SELECT REPLACE('1-S 3-ER','-',':');
--结果是：1:S 3:ER

--让某字符串重复几次
SELECT REPLICATE('ABC',3);
--结果是：ABCABCABC

--在字符串中的某个位置去除其后几个字符并插入字符串
SELECT STUFF('HELLOWORLD',3,2,'123');
--结果是：HE123OWORLD - 去除HELLOWORLD的第3个位置加他后面 共两个字符 再 插入 123

--转大小写
SELECT UPPER('abc');
SELECT LOWER('ABC');
--结果是：ABC、abc

--去除左右空格
SELECT RTRIM(LTRIM('   ABC   '));
--结果是：ABC - RTRIM去除右边空格 LTRIM去除左边空格

--将输入值转化为字符串
SELECT FORMAT(1759,'0000000');
--结果：0001759 

LIKE谓词

SELECT 
	empid,
	lastname
FROM HR.Employees
WHERE lastname LIKE N'D%'
--匹配以D开头的字符串

SELECT
	empid,
	lastname
FROM HR.Employees
WHERE lastname LIKE '_e%'
--匹配第二个字母是e

SELECT
	empid,
	lastname
FROM HR.Employees
WHERE lastname LIKE N'[ABC]%'
--第一个字母是ABC其中之一的


SELECT
	empid,
	lastname
FROM HR.Employees
WHERE lastname LIKE N'[A-E]%'
--第一个字母是A-E其中的一个

日期函数

SELECT
	orderid,
	custid,
	empid,
	orderdate
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
--查询2007年2月的订单

查询元数据

目录视图

SELECT
	SCHEMA_NAME(schema_id) AS table_schema_name,
	name AS table_name
FROM sys.tables;
--列出数据库中表的架构名称 和 表的名称

SELECT
	name AS columns_name,
	TYPE_NAME(system_type_id) AS columns_type,
	max_length,
	collation_name,
	is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');
--返回Sales.Orders表的：列名、数据类型、最大长度、排列规则名称、是否为空

信息架构视图

信息架构视图是一个视图集合，其名位于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';
--返回Sales.Orders表的：列名、数据类型、最大长度、排列规则名称、是否为空

系统存储过程和函数

EXEC sys.sp_tables
--返回一个当前数据库中能查询到的对象列表

EXEC sys.sp_help
	@objname = N'Sales.Orders';
--返回Orders表的详情信息

EXEC sys.sp_columns
	@table_name = N'Orders',
	@table_owner = N'Sales';
--返回Orders表中的列信息

EXEC sys.sp_helpconstraint
	@objname = N'Sales.Orders';
--返回Orders中的约束信息

SELECT
	SERVERPROPERTY('ProductLevel');
--返回当前实例的产品级别

SELECT
	DATABASEPROPERTYEX(N'TSQL2012','Collation');
--返回TSQL2012数据库的排序规则

SELECT
	OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'),'TableHasPrimaryKey');
--判断Orders是否具有主键

###练习

--2.10.1
SELECT
	orderid,
	orderdate,
	custid,
	empid
FROM Sales.Orders
WHERE YEAR(orderdate) = '2007'
	AND MONTH(orderdate) = '6'
	
--2.10.2
SELECT
	orderid,
	orderdate,
	custid,
	empid
FROM Sales.Orders
WHERE DAY(orderdate + 1) = '1'

--2.10.3
SELECT
	empid,
	firstname,
	lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';

--2.10.4
SELECT
	orderid,
	SUM(qty * unitprice) AS totalvalue	
FROM Sales.OrderDetails
GROUP BY orderid
HAVING SUM(qty * unitprice) > 10000
ORDER BY totalvalue DESC

--2.10.5
SELECT TOP(3)
	shipcountry,
	AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE YEAR(orderdate) = '2007'
GROUP BY shipcountry
ORDER BY avgfreight DESC

--2.10.6*
SELECT
	custid,
	orderdate,
	orderid,
	ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate,orderid) AS rownum
FROM Sales.Orders
ORDER BY custid,rownum
--1、按照 custid 分组，并且按照组内按照orderdate、orderid升序排序
--2、组外按照 custid、rownum排序

--row_number() over()分组排序功能：
--row_number() over(order by salary desc) - 降序排序查询
--row_number()over(partition by id order by salary desc) - 按照id分组降序

--2.10.7
SELECT
	empid,
	firstname,
	lastname,
	titleofcourtesy,
	CASE titleofcourtesy
		WHEN 'MS.'	THEN 'Female'
		WHEN 'MRS.'	THEN 'Male'
		ELSE 'Unknown'
	END AS gender
FROM HR.Employees

--2.10.8*
SELECT
	custid,
	region	
FROM Sales.Customers
ORDER BY 
	CASE WHEN region IS NULL THEN 1 ELSE 0 END,region
--先根据‘CASE WHEN region IS NULL THEN 1 ELSE 0 END’排序，再根据region