T-SQL基础查询——单表查询

1，查询的顺序

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
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

解释如下：

1. Queries the rows from the Sales.Orders table
2. Filters only orders where the customer ID is equal to 71
3. Groups the orders by employee ID and order year
4. Filters only groups (employee ID and order year) having more than one order
5. Selects (returns) for each group the employee ID, order year, and number of orders
6. Orders (sorts) the rows in the output by employee ID and order year

2，列名的别名

 有三种定义别名的方法，推荐AS的写法。

1）<expression> AS <alias>

2） <alias> = <expression> (“alias equals expression”)

3）and <expression> <alias> (“ex-pression space alias”)

由于SELECT语句执行顺序靠后，列的别名不能用于Where语句，下面的语句错误

SELECT orderid, YEAR(orderdate) AS orderyear 
FROM Sales.Orders 
WHERE orderyear > 2006;

列的别名同样不能用于SELECT语句之间作为前后引用，下面的语句错误

SELECT orderid, 
YEAR(orderdate) AS orderyear, 
orderyear + 1 AS nextyear 
FROM Sales.Orders;

3，ORDER BY 语句

1）ORDER BY的字段可以不出现在SELECT语句中

2）SELECT语句中有DISTINCT的话，ORDER BY的字段必须在SELECT中，下面的语句错误

SELECT DISTINCT country 
FROM HR.Employees 
ORDER BY empid;

4，TOP 语句

1）TOP可以指定PERCENT，返回所有记录的百分比

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

2）TOP后加入WITH TIES选项的话，会返回具有相同ORDER顺序的所有记录，而不仅限于top数。

SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid 
FROM Sales.Orders 
ORDER BY orderdate DESC;
This query returns the following output.
orderid orderdate custid empid 

----------- ---------------------------- ----------- ----------- 
11077 2008-05-06 00:00:00.000 65 1 
11076 2008-05-06 00:00:00.000 9 4 
11075 2008-05-06 00:00:00.000 68 8 
11074 2008-05-06 00:00:00.000 73 7 
11073 2008-05-05 00:00:00.000 58 2 
11072 2008-05-05 00:00:00.000 20 4 
11071 2008-05-05 00:00:00.000 46 1 
11070 2008-05-05 00:00:00.000 44 2

5，OFFSET FETCH 语句

这个语句在SQLSERVER2012中新添加，可以跳过某些行取需要的数据，并且这是标准SQL的一部分，这对于分页来说是极大的福音。

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

6，运算符的优先级

1. ( ) (Parentheses)
2. * (Multiplication), / (Division), % (Modulo)
3. + (Positive), – (Negative), + (Addition), + (Concatenation), – (Subtraction)
4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5. NOT
6. AND
7. BETWEEN, IN, LIKE, OR
8. = (Assignment)

7，Case When语句

1）简单的Case表达式（simple CASE expression）

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;

2）检索的CASE表达式（searched CASE expression）

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;

简单的Case表达式可以用第二种Case表达式替换，反过来就不行。

另外，Case表达式可以用下面的函数替代。

ISNULL：SQL SERVER 特有的函数

select isnull(id, null) from test

COALESCE：COALESCE是标准SQL的一部分，可以计算多个值，返回第一个不为Null的值，功能比ISNULL强大，推荐使用。

select coalesce(id, name,null) from test

 8，Null标记

1）SQL中采用3值逻辑，TRUE, FALSE, or UNKNOWN。

条件表达式会被计算成这3个值的一种，而Null会被计算成UNKNOWN。UNKNOWN的值再参与计算结果依然是UNKNOWN。对于Where语句，只会返回值为True的记录，False和UNKNOWN都不会返回，也就是NULL数据默认不返回。

2）如果要处理NULL的数据，条件中要用 IS NULL，IS NOT NULL来判断，这样计算的结果才是TRUE或FALSE。

9，同时操作（all-at-once operations）

同一语句块的字段，条件是同时计算的，没有先后顺序。

试图在同一语句块，进行前后引用，或是有顺序依赖的，都将失败。

下面的例子会失败，select字段间不能依赖，where中的col2/col1依然会被计算，col1为0会出异常，sql中没有短路运算。

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;

case可以顺序执行，可以通过case来改写上面的第二个例子

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';

或者是，改写除法为乘法，避免0除错误

SELECT col1, col2 
FROM dbo.T1 
WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1);

 备注：内容主要来自《SqlServer2012基础查询》（英文版）的学习笔记，部分内容保留英文摘要。