第二章 单表查询 T-SQL语言基础(2)

单表查询(2)

2.2 谓词和运算符

T-SQL有几种不同的语言元素可以指定逻辑表达式,例如,查询过滤器(WHERE和HAVING),CHECK约束,等等.

在逻辑表达式中可以使用各种谓词(取值为TRUE,FALSE,或UNKNOWN的表达式)和运算符.

 

T-SQL支持的谓词包括IN,BETWEEN,以及LIKE等.

-- Predicates: IN, BETWEEN, LIKE
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);
--IN这个谓词用于检查一个值(或标量表达式)是否与一组元素的至少一个相等.

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;
--BETWEEN用于检查一个值是否在指定的范围内,包括两个指定的边界值

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';
--LIKE这个谓词用于检查一个字符串值是否与指定的模式匹配
--N'D%',N用于表示字符串是Unicode数据类型(NCHAR或NVARCHAR),与之相对应的是常规的字符数据类型(CHAR或VARCHAR)

 

T-SQL支持的比较运算符: =,>,<,>=,<=,<>,!=,!<,!>,其中最后3个运算符不是标准运算符.因为非标准运算符都可以用相应的标准运算符来替代,尽量避免使用非标准运算符.

-- Comparison operators: =, >, <, >=, <=, <>, !=, !>, !< 
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101';
--查询返回2008年1月1日之后生成的所有订单

 

如果要把多个逻辑表达式组合起来,可以使用逻辑运算符OR和AND.如果想对布尔型表达式取反,可以使用NOT运算符.

-- Logical operators: AND, OR, NOT
SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20080101'
  AND empid IN(1, 3, 5);
--查询返回2008年1月1日之后,由ID为1,3,5的雇员处理过的所有订单

 

T-SQL支持的算术运算符:+,-,*,/,以及%(取模)运算符,它返回一个整数除法运算的余数.

-- Arithmetic operators: +, -, *, /, %
SELECT orderid, productid, qty, unitprice, discount,
  qty * unitprice * (1 - discount) AS val
FROM Sales.OrderDetails;

 

注:涉及两个运算对象的标量表达式的数据类型取决于数据类型的优先级,由优先级较高的运算对象决定.

如果两个运算对象具有相同的数据类型,表达式的结果也为相同的数据类型.

如果两个运算对象具有不同的类型,则具有较低类型优先级的运算对象就自动转换成更高级的类型

 

T-SQL运算符优先级规则:

1.()

2.*,/,%

3.+,-,

4.=,>,<,>=,<=,<>,!=,!<,!>

5.NOT

6.AND

7.BETWEEN,IN,LIKE,OR

8. =

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

 

2.3 CASE表达式

CASE表达式是一个标量表达式,它基于条件逻辑来返回一个值.

(在SELECT阶段,对返回的某列的行元素进行逻辑判断,根据判断输出不同的结果,少量数据?)

 

CASE表达式有两种格式:简单表达式和搜索表达式.

---------------------------------------------------------------------
-- CASE Expression
-- CASE ....WHEN......ELSE...END AS...

---------------------------------------------------------------------

-- Simple
--CASE简单格式将一个值(或一个标量表达式)与一组可能的取值进行比较,并返回第一个匹配的结果.如果列表中没有值等于测试值,CASE表达式就返回其ELSE子句(如果存在)中列出的值.如果CASE表达式中没有ELSE子句,则默认将其视为ELSE NULL.
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;

SELECT orderid, custid, val,
  CASE NTILE(3) OVER(ORDER BY val)
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    ELSE 'Unknown'
  END AS titledesc
FROM Sales.OrderValues
ORDER BY val;

-- Searched
--CASE搜索表达式,可以在WHEN子句中指定谓词或逻辑表达式,而不限于只进行相等性比较.
--CASE搜索表达式返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值.如果没有任何WHEN表达式结果为TRUE,CASE表达式就返回ELSE子句中出现的值(如果没有指定ELSE子句,则返回NULL).
SELECT orderid, custid, val,
  CASE 
    WHEN val < 1000.00                   THEN 'Less then 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;

 

 2.4 NULL值   UNKNOWN

SQL支持用NULL符合来表示缺少的值,它使用的是三值谓词逻辑,这意味着谓词的计算结果可以是TRUE,FALSE或UNKNOWN(未知).

**如果逻辑表达式只涉及已经存在的值,那么最终的计算结果要么为TRUE,那么为FALSE;但是当逻辑表达式涉及缺少的值时,其计算结果就是UNKNOWN.

 

**在不同的语言元素中,SQL对UNKNOWN的处理也有所不同.SQL对于查询过滤条件处理的正确定义是:"接受TRUE"就意味着要过滤掉FALSE和UNKNOWN.反正,SQL对CHECK约束处理的正确定义是:"拒绝FALSE"就意味着接受TRUE和UNKNOWN.

**UNKNOWN当它取反时,仍然是UNKNOWN;对两个NULL值进行比较的,结果是UNKNOWN;因为NULL值代表的是一个缺少的值或不可知的值,所以实际上无法判断一个不可知的值是否等于另一个.

**SQL提供了两个谓词 IS NULL 和 IS NOT NULL,用它们来取代 =NULL和 <>NULL.

---------------------------------------------------------------------
-- NULLs
---------------------------------------------------------------------
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = N'WA';
--不会返回NULL的值,只返回region = N'WA' 为TRUE的值,过滤region不为'WA'和UNKNOWN
--注意:WHERE查询器,只接受为"TRUE"!

SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA';
--不会返回NULL的值,只返回 region不是N'WA' 为TRUE的值,过滤region是'WA'和UNKNOWN

SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = NULL;
--返回空

SELECT custid, country, region, city
FROM Sales.Customers
WHERE region IS NULL;
--返回 region 为NULL的值

SELECT custid, country, region, city
FROM Sales.Customers
WHERE region <> N'WA'
   OR region IS NULL;
--返回 region 列不等于WA的所有行(既包括region列有值,但不为WA的那些行;也包括region列缺少值的那些行)
--这里"and" 是没有用的!

 

 ****注意NULL值!

在用于比较和排序目的的不同语言元素中,SQL处理NULL的方式也有所不同.

例如:当进行分组和排序时,认为两个NULL值是相等的.也就是说,GROUP BY子句会在每个组中重新组织所有的NULL值,就像有具体值的列一样;ORDER BY子句也会对所有NULL值进行排序.至于NULL值应该排在有效值之前还是之后?? T-SQL是把NULL值排在了有效值之前.

 

ANSI SQL有两个UNIQUE约束:一种将多个NULL值视为相等的,另一个则将多个NULL值视为不同的.SQL Server只实现了前者.

 

2.5 同时操作(ALL-AT-ONCE OPERATION)

SQL支持一种所谓的同时操作的概念,其含义是认为在同一逻辑查询处理阶段中出现的所有表达式都是同时进行计算的.