SQLServer单表查询

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.查询顺序
a.FROM
b.WHRER
c.GROUP BY
d.HAVING
e.SELECT
I.表达式
II.DISTINCT
f.ORDER BY
I.TOP/OFFSET-FETCH
2.FROM子句
逻辑化处理的第一个查询子句，此子句指定要查询的表的名称和进行多表运算的表运算符。
3.WHRER子句
指定一个谓词或逻辑表达式来筛选由FROM阶段返回的行
4.GROUP BY子句
用户把前面逻辑查询处理阶段返回的行排列到组中，组是根据你在GROUP BY子句中指定的元素而确定的。一样的行当做一组输出，显示为一行
GROUP BY阶段对于WHERE阶段返回的数据会为雇员ID与订单年度值的每个唯一组合生成一个组
如果查询涉及到组，那么GROUP BY阶段的所有后续阶段，包括HAVING,SELECT和ORDER BY都是对组的操作，而不是对单个行进行操作。这意味着在GROUP BY阶段后续阶段处理的子句中，
指定的所有表达式必须确保每组返回一个标量（单个值）。
不参与到GROUP BY列表中的元素仅允许作为一个聚合函数的输入，如COUNT,SUM,AVG,MIN,MAX.
5.HAVING子句
在HAVING子句中，可以指定一个谓词来筛选组，而不是删选单个行（行删选发生在WHERE阶段）。只有HAVING子句中逻辑表达式计算结果为TRUE的组，由HAVING阶段返回到下一个逻辑
查询处理阶段。
由于HAVING子句是在行分组后被处理，所以可以在逻辑表达式中引用集合函数。
6.SELECT子句
AS指定别名 可以用=  也可以用空格，推荐用AS
在SELECT子句内，不允许使用在同一SELECT子句中创建的列别名。
7.因为执行有先后顺序，所以一下语句是错误的
SELECT orderid,YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear>2006;
会提示Invalid column name 'orderyear'，解决方式就是重复YEAR(orderdate),但是SQL Server能够识别查询中重复使用的相同表达式，此表达式只需被计算一次。
8.消除重复行
SELECT distinct empid,YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71;
9.ORDER BY
升序ASC(默认)
降序DESC  
允许在ORDER BY子句中使用列的序号位置指定拍序列，此序号位置是在SELECT列表中的顺序。
ORDER BY empid, orderyear
ORDER BY 1,2 不推荐，这是认为一种很糟糕的编程做法。
T-SQL允许ORDER BY子句中指定未出现在SELECT子句中的元素，但是如果指定了DISTINCT，则ORDER BY列表中只能是出现在SELECT列表中的元素：原因是在指定了DISTINCT后，单个
结果行很可能会代表多个源行，因此ORDER BY子句中会有多个可能值可用，这就导致不清晰。

10.WHERE和HAVING是基于谓词的筛选，下面是基于行数和排序的筛选
a.TOP筛选
用于限制查询行数或行的百分比。它依赖于两个元素作为规范的一部分，一个是要返回的数目或百分比，另一个是排序。
例如：在orders表中返回最近的5个订单，你应该在SELECT子句中指定TOP(5),并在ORDER BY子句中指定orderdate DESC.
SELECT TOP(5) orderid,orderdate, custid, empid 
FROM Sales.Orders
ORDER BY orderdate DESC;
为TOP选项指定PERCENT关键字，在这种情况下，SQL Server基于限定行数的百分比计算要返回的行数，向上舍入
例如：查询近1%的订单
SELECT TOP(1) PERCENT orderid,orderdate, custid, empid 
FROM Sales.Orders
ORDER BY orderdate DESC;
注意；查询出的结果可能顺序不一样，因为没有在orderdate列上定义主键或唯一约束，SQL Server确定行的顺序是基于那个行先被物理访问到，所以要保证搜索出结果顺序一
致性应该给主键排序。
b.OFFSET-FETCH筛选
TOP有两个缺陷，不是标准SQL，且不支持跳过功能。
SQL Server2012中OFFSET-EFTCH筛选被视为ORDER BY子句的一部分，通常用于实现按顺序显示效果。OFFSET子句指定要跳过的行数，FETCH子句指定在跳过的行数后要筛选的行
数。
SELECT  orderid,orderdate, custid, empid 
FROM Sales.Orders
ORDER BY orderdate,orderid 
OFFSET 50 ROW FETCH NEXT 25 ROWS ONLY;
注意：OFFSET-FETCH的查询必须具有ORDER BY子句。FETCH子句不支持没有OFFSET子句，如果不想跳过任何行，用0，不过没有FETCH的OFFSET是允许的，这种情况是跳过指定行
数，并返回查询结果的所有剩余行。ROW和ROWS是一样的，NEXT和FIRST是一样的，可以互换。不过OFFSET-FETCH不支持PERCENT.
11.开窗函数
ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum 
根据custid分组，在分组内部按照val排序，再为每组排序后每条custid记录返回一个值从1开始。
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid 
order by val desc) AS rownum
FROM Sales.OrderValues;
12.谓词和运算符
T-SQL具有可以在其中指定谓词的语言元素，例如，WHERE和HAVING查询筛选、CHECK约束等。谓词是计算TRUE、FALSE或UNKNOWN的逻辑表达式。你可以使用谓词运算符来连接谓词，
如AND和OR,也可以引用其他类型运算符，如在表达式中使用比较运算符。
a.IN谓词可以检查一个值或是一个标量表达式是否至少等于集合中的一个元素。例如：查询将返回订单ID等于10248,10249或10250的订单
SELECT orderid,empid,orderdate
FROM Sales.Orders
WHERE orderid in(10248,10249,10250);
b.BETWEEN谓词可以检查一个值是否在指定的范围内，其包括两个指定的边界值。例如：查询返回所有包含在10300-10310范围内的订单
SELECT orderid,empid,orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310; 
c.LIKE谓词可以检查一个字符串值是否满足指定的模式。例如：查询返回姓氏以D开始的雇员
SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
注意字符串'D%'前缀字母N的使用，它代表Nation,用来表示字符串是一个Unicode数据类型（NCHAR或NVARCHAR），而不是一个常规字符数据类型（CHAR或VARCHAR）
d.T-SQL支持的比较运算符有=,>,<,>=,<=,<>,!=,!>和!<。其中，后三个运算符是不标准的，建议避免使用非标准运算符。
例如：查询返回2008年1月1日以后的所有订单
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate>='20080101';
e.如果需要连接逻辑表达式，可以使用逻辑运算符AND和OR，如果希望否定一个表达式，可以使用一个NOT运算符。
f.T-SQL支持四个明显的的算术运算符：+、-、*、/以及%取模运算符。
g.运算符的优先级
1.()
2.*,/,%
3.+(正号),-(负号),+(加号),+(串联),-(减号)
4.=,>,<,>=,<=,<>,!=,!>和!<
5.NOT
6.AND
7.BETWEEN,IN,LIKE,OR
8.=(赋值)
12.CASE表达式
CASE表达式具有简单和搜索两种格式。
CASE表达式是一个标量表达式，返回一个基于条件逻辑的值，需要注意的是CASE是表达式而不是语句，因为CASE是一个标量表达式，因此可以在任何允许使用
标量表达式的地方使用它，例如SELECT、WHERE、HAVING和ORDER BY子句中，以及在CHECK约束中。
CASE表达式具有“简单”和“搜索”两种格式。简单格式允许在一个可能值列表中比较一个值或是标量表达式，并返回第一个匹配值。如果列表中没有值等于
测试值，CASE表达式返回ELSE子句（如果存在）中的值。如果没有ELSE子句，默认ELSE NULL。
例如：在SELECT子句中使用了一个CASE表达式用于生产categoryid列值得说明。
SELECT productid, productname,categoryid,
CASE categoryid
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
WHEN 4 THEN 'D'
WHEN 5 THEN 'E'
WHEN 6 THEN 'F'
WHEN 7 THEN 'G'
WHEN 8 THEN 'H'
ELSE 'UNKNOWN'
END AS categoryname
FROM Production.products;
CASE简单格式在CASE关键字后具有单个测试值或表达式，与WHEN子句中的可能值列表进行比较。CASE搜索格式更加灵活，因为它允许你在WHEN子句中指定谓词
或逻辑表达式，而不是限制于进行相等比较。
例如：下面的查询根据值是否小于1000，在1000和3000之间，或是大于3000，生成一个值得类别说明。
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 THEN 'More than 3000'
ELSE 'Unkonwn'
END AS valuecategory
FROM Sales.OrderValues; 
某些函数可以CASE的缩写形式：
COALESCE(,,,...)中间可以加很多参数，只返回第一个为非NULL的，否则返回NULL,标准SQL.
ISNULL( , )只能加两个参数，返回第一个非NULL的参数，否则返回NULL，非标准。
IIF(expression，ex1, ex2), 如果expression为true,返回ex1，否则返回ex2，非标准。
CHOOSE(index, ex1, ex2,ex3...) 返回列表中指定索引的表达式。index=3,返回ex3，非标准。
13.NULL
SQL支持用于表示缺失值得NULL标记。并使用三值逻辑，即谓词可以计算为：TRUE,FALSE或UNKNOWN。
SQL不同的语言元素对于UNKNOWN有不同的处理方式，对于查询而言，SQL正确处理定义是“接受TRUE”意味着FALSE和UNKNOWN会被筛选掉。
对于CHECK约束而言，SQL的正确处理方式定义是“拒绝FALSE”，意味着TRUE和UNKNOWN会被接受。
14.数据类型
SQL支持两种数据字符类型—常规和Unicode,常规数据类型包括CHAR和VARCHAR，Unicode数据类型包括NCHAR和NVARCHAR。
两种数据类型的文本表示方式不同：在表示常规字符文本时，只需使用单引号，在表示Unicode字符文本时，需要指定字符N（即:National）作为前缀。