2.《SQLSERVER2012之T-SQL教程》T-SQL单表查询（二）

2019独角兽企业重金招聘Python工程师标准>>>

表结构与数据：https://github.com/XuePeng87/TSQLV4

谓词和运算符

1) 谓词

    T-SQL支持的谓词例子包括IN、BETWEEN和LIKE。

    IN可以检查一个值或是一个标量表达式是否至少等于集合中的一个元素。例如下面查询，将返回10248、10249或10250的订单：

SELECT orderid, empid, orderdate 
FROM Sales.Orders 
WHERE orderid IN (10248, 10249, 10250);

    BETWEEN谓词可以检查一个值是否在制定范围内，其包括两个指定的边界值。例如下面查询，将返回包括在10300~10310范围内的订单：

SELECT orderid, empid, orderdate 
FROM Sales.Orders 
WHERE orderid BETWEEN 10300 AND 10310;

    LIKE谓词可以检查一个字符串值是否满足指定的模式。例如，下面的查询返回姓氏以字母D开始的雇员：

SELECT empid, firstname, lastname 
FROM HR.Employees 
WHERE lastname LIKE N'D%';

    注意字符串"D%"前缀字母N的使用，他代表National（国家），用来表示字符串是一个Unicode数据类型数据类型（NCHAR或NVARCHAR），而不是一个常规字符数据类型（CHAR或VARCHAR）。由于lastname属性的数据类型为NVARCHAR(40)，所以字母N作为字符串前缀。

2) 运算符

    T-SQL支持的比较运算符包括：=、>、<、>=、<=、<>、!=、!>和!<。后三个不是标准的。

    如果需要连接逻辑表达式，可以使用逻辑运算符：OR和AND。

    T-SQL支持4个明显的算数运算符：+、-、*、/，以及%运算符。

    注意，在T-SQL中，涉及两个操作数的标量表达式的数据类型，是按两个数据类型优先级中的较高优先级确定的。如果两个操作数的数据类型相同，表达式结果是相同的数据类型。例如：两个INT相除将生成一个整数，5/2=2，而不是2.5了。如果希望计算正数的结果是一个浮点类型，则需要强制转换操作数为适当的类型：

SELECT CAST(5 AS NUMERIC(12, 2)) / CAST (2 AS NUMERIC(12, 2));

    下面描述了运算符的优先级（从高到低）：

1. "()"，圆括号，小括号；
2. *、/、%；
3. +（正号）、-（符号）、+（加号）、+（串联）、-（减号）；
4. =、>、<、>=、<=、<>、!=、!>、!<（比较运算符）；
5. NOT；
6. AND；
7. BETWEEN、IN、LIKE、OR；
8. =（赋值）；

CASE表达式

    CASE表达式是一个标量表达式，返回一个基于条件逻辑的值。需要注意的是，CASE是表达式而不是语句，它不允许你控制活动流或是做一些基于条件逻辑的操作。不过，它的返回值是基于条件逻辑的。由于CASE是一个标量表达式，因此可以在任何允许使用标量表达式的地方使用它。例如，在SELECT、WEHERE、HAVING、ORDER BY中，以及在CHECK约束中。

    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 'Unkonwn Categroy'
	END AS categoryname 
FROM Production.Products;

    简单格式在CASE关键字后具有单个测试值或表达式，与WHEN子句中的可能值列表进行比较。

    在看一个搜索格式的例子：

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;

    搜索格式更加灵活，允许在WHEN中制定谓词或逻辑表达式，而不是限制与进行相等比较。
    T-SQL支持的某些函数，可以看作是CASE表达式的缩写形式，如ISNULL、COALESCE、IIF和CHOOSE。这4和函数中只有COALESCE是标准的。此外，IIF和CHOOSE仅在SQL Server2012中可以使用。

    ISNULL函数可以接收两个参数作为输入，并返回第一个非NULL的参数值，如果两个参数值均为NULL，则返回NULL。例如，ISNULL(col1,'')，如果col1值不是NULL的话，那么返回col1，如果col1是NULL的话，则返回空字符串。

    COALESCE函数与其类似，只是它支持两个或更多参数，并返回第一个非NULL值，如果所有参数均为NULL的话，则返回NULL。

    IIF和CHOOSE函数，是为了支持更容易从Microsoft Access迁移。函数IIF(<logical_exp>,<exp1>,<exp2>)，如果Logical_exp为TRUE，则返回exp1，否则返回exp2。

    函数CHOOSE(<index>,<exp1>,<exp2>,...,<expn>)，返回列表中指定索引的表达式。

NULL标记

    T-SQL支持用于表示缺失值的NULL标记，并使用三值逻辑。TRUE、FALSE和UNKNOWN，T-SQL遵循这方面的标准。

    逻辑表达式仅涉及已有或是现值，其计算结果为TRUE或FALSE，但当逻辑表达式涉及NULL时，其计算结果为UNKNOWN。例如salary>0，当salary等于1000时，结果为TRUE。当salary等于-1000时，结果为FALSE。当salary是NULL时，结果为UNKNOWN。

    先来看看，SQL是怎么处理计算结果为TRUE和FALSE的表达式。例如salary>0出现在查询筛选中（WHERE或HAVING）,将返回表达式计算为TRUE的行或组，那些结果为FALSE的会被筛选掉。同样，如果谓词salary>0出现在表的CHECK约束中，所有行的表达式计算为TRUE的INSERT或UPDATE语句会被接收，而那些计算结果为FALSE的会被拒绝。

    接下来看看表达式计算结果为UNKNOWN的处理方式。对于查询筛选而言，SQL的正确处理定义是接收TRUE，意味着FALSE和UNKNOWN会被筛选掉。相反，对于CHECK约束而言，SQL的正确处理定义是“拒绝FALSE”，所以TRUE和UNKNOWN会被接受。

    UNKNOWN一个棘手的问题是，当否定它时，仍然会得到UNKNOWN值。例如，给出的谓语NOT(salary>0)，当salary(工资)为NULL时，salary>0的结果为UNKNOWN，并且NOT UNKNOWN仍是UNKNOWN。比较两个NULL标记(NULL=NULL)结果也是UNKNOWN。因此SQL提供了IS NULL和IS NOT NULL来比较NULL。看几个例子：

SELECT custid, country, region, city 
FROM Sales.Customers 
WHERE region = N'WA';

    执行上面的SQL既不会返回region不是WA的行，也不会返回region为NULL的行。

SELECT custid, country, region, city 
FROM Sales.Customers 
WHERE region <> N'WA';

    执行上面的SQL既不会返回region属性等于WA的行，也不会返回region为NULL的行。

SELECT custid, country, region, city 
FROM Sales.Customers 
WHERE region = NULL;

    执行上面的SQL什么都不会返回，因为region为NULL的行与NULL比较会得到UNKNOWN，而查询筛选只支持TRUE。

SELECT custid, country, region, city 
FROM Sales.Customers 
WHERE region IS NULL;

    执行上面的SQL会返回region为NULL的行。

SELECT custid, country, region, city 
FROM Sales.Customers 
WHERE region <> N'WA' OR region IS NULL;

    执行上面的SQL会返回region不是WA的和region是NULL的行。

    注意，对于分组和排序，两个NULL表示被视为相等。即GROUP BY将所有NULL分成一组，ORDER BY也将所有NULL排序在一起，标准SQL将NULL标记在现值之前排序，还是之后排序留给了产品实施，T-SQL是在现值之前对NULL标记排序。

    为了强制执行UNIQUE约束，标准SQL将NULL标记视为彼此不同。相反地，T-SQL在UNIQUE约束中认为NULL标记是相等的。

同时操作

    SQL支持一个称作同时操作的概念，意思是出现在同意逻辑处理阶段的所有表达式在同一时间点进行逻辑计算，例如：

SELECT col1, col2 
FROM dbo.T1 
WHERE col1 <> 0 AND col2/col1 > 2

    这是关于同时操作的一个例子，希望返回col2/col1>2的所有行。因此有可能表中行col1是等于0的，不可以将0做除数，所以在col2/col1 > 2之前判断col1<>0，如果col1为0那么表达式会短路。

    遗憾的是，SQL Server不支持短路，它基于标准SQL的同事操作概念，SQL Server可以按任意顺序自由处理WHERE子句中的表达式。所以如果先执行了col2/col1>2这个表达式，而col1为0，那么就会导致执行失败。

    有几种方法可以避免同时操作导致执行失败。例如，CASE表达式中的WHEN子句的计算顺序是有保证的，可以按如下方式修改：

SELECT col1, col2 
FROM dbo.T1 
WHERE 
	CASE
		WHEN col1 = 0 THEN 'no'
		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);

 

转载于:https://my.oschina.net/u/2981366/blog/775196