7.6 SQL Server条件查询

SQL Server条件查询

目录

• SQL Server条件查询
  • SQL Server WHERE
    • 简介
    • SQL Server WHERE示例
      • A)等值查询(=)
      • B)查找满足两个条件的行(AND)
      • C)使用比较运算符查找(>,>=,<,<=,!=)
      • D)查找满足两个条件之一的行(OR)
      • E)查找值介于两个值之间的行(BETWEEN)
      • F)查询列具有在值列表中的值的行(IN)
      • G)查找值包含字符串的行(LIKE)
  • SQL Server NULL
    • 简介
    • IS NULL
  • SQL Server AND
    • AND简介
    • AND运算符示例
  • SQL Server OR
    • OR简介
  • SQL Server IN
    • 简介
    • 示例
  • SQL Server between
    • BETWEEN示例
      • A)BETWEEN与数字类型
      • B)BETWEEN与日期类型
  • SQL Server LIKE
    • 简介
    • LIKE示例
      • ％（百分比）通配符示例
      • _（下划线）通配符示例
      • [字符列表]通配符示例
      • [字符-字符]通配符示例
      • [^字符列表或范围]通配符示例
    • NOT LIKE示例
    • LIKE与转义字符示例
  • SQL Server别名
    • 列别名
    • 表别名

SQL Server WHERE

简介

使用SELECT语句对表查询数据时，一般来说不需要返回所有的数据行

条件查询是通过where子句进行检索的查询方式：

select 字段名1, 字段名2, …, 字段名n
from 数据表名
where 查询条件

在WHERE子句中指定查询条件，只有满足查询条件的记录才会被返回。

查询条件是一个逻辑表达式或多个逻辑表达式的组合。

注意：SQL中，逻辑表达式的值可以是TRUE,FALSE,UNKNOWN(未知)，查询只返回表达式值为TRUE的记录。

SQL Server WHERE示例

有如下产品表:

A)等值查询(=)

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    category_id = 1
ORDER BY
    list_price DESC;

B)查找满足两个条件的行(AND)

使用AND组合条件

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    category_id = 1 AND model_year = 2018
ORDER BY
    list_price DESC;

C)使用比较运算符查找(>,>=,<,<=,!=)

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    list_price > 300 AND model_year = 2018
ORDER BY
    list_price DESC;

D)查找满足两个条件之一的行(OR)

使用OR组合条件

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    list_price > 3000 OR model_year = 2018
ORDER BY
    list_price DESC;

E)查找值介于两个值之间的行(BETWEEN)

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    list_price BETWEEN 1899.00 AND 1999.99
ORDER BY
    list_price DESC;

F)查询列具有在值列表中的值的行(IN)

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    list_price IN (299.99, 369.99, 489.99)
ORDER BY
    list_price DESC;

G)查找值包含字符串的行(LIKE)

SELECT
    product_id,
    product_name,
    category_id,
    model_year,
    list_price
FROM
    production.products
WHERE
    list_price > 3000 OR model_year = 2018
ORDER BY
    list_price DESC;

SQL Server NULL

简介

在数据库世界中，NULL用于指示不存在任何数据值。例如，当记录客户信息时，电子邮件可能是未知的，在数据库中将其记录为NULL。

通常，逻辑表达式的结果为TRUE或FALSE。然而，当逻辑表达式中包含NULL时，结果是UNKNOWN。因此，逻辑表达式可能返回三值逻辑之一：TRUE、FALSE和UNKNOWN

以下比较结果为UNKNOWN：

NULL = 0
NULL <> 0
NULL > 0
NULL = NULL

NULL不等于任何值，甚至不等于它本身。这意味着NULL不等于NULL，因为每个NULL可能不同。

IS NULL

有如下客户表：

以下语句查找没有在客户表中记录电话号码的客户,即phone列为null得客户：

SELECT
    customer_id,
    first_name,
    last_name,
    phone
FROM
    sales.customers
WHERE
    phone = NULL
ORDER BY
    first_name,
    last_name;

查询返回了空结果集。

WHERE子句返回条件表达式计算为TRUE的行。但是，以下表达式的计算结果为UNKNOWN:

phone = NULL;

所以返回了空结果集。

要测试值是否为空，使用IS NULL运算符。

SELECT
    customer_id,
    first_name,
    last_name,
    phone
FROM
    sales.customers
WHERE
    phone IS NULL
ORDER BY
    first_name,
    last_name;`

查询返回了没有电话信息的客户。

如果要检查一个值不为NULL,使用IS NOT NULL。

以下查询返回具有电话信息的客户：

SELECT
    customer_id,
    first_name,
    last_name,
    phone
FROM
    sales.customers
WHERE
    phone IS NOT NULL
ORDER BY
    first_name,
    last_name;

SQL Server AND

AND简介

AND是一个逻辑运算符，允许您组合两个布尔表达式。只有当两个表达式的值都为TRUE时，它才会返回TRUE:

boolean_expression AND boolean_expression   

boolean_expression是任何有效的布尔表达式，其计算结果为TRUE、FALSE和UNKNOWN。

下面是TRUE,FALSE,UNKNOWN通过AND不同组合的结果：

TRUE AND TRUE == TRUE
TRUE AND FALSE == FALSE
FALSE AND FALSE == FALSE
TRUE AND UNKNOWN == UNKNOWN
FALSE AND UNKNOWN == FALSE
UNKNOWN AND UNKNOWN == UNKNOWN

在表达式中使用多个逻辑运算符时，SQL Server始终首先计算AND运算符。但是，可以使用括号更改求值顺序。

AND运算符示例

有如下产品表：

A)使用单个AND
查找类别id为1且标价大于400的产品：

SELECT
    *
FROM
    production.products
WHERE
    category_id = 1
AND list_price > 400
ORDER BY
    list_price DESC;

B)使用多个AND
查找满足以下所有条件的产品：类别id为1，标价大于400，品牌id为1：

SELECT
    *
FROM
    production.products
WHERE
    category_id = 1
AND list_price > 400
AND brand_id = 1
ORDER BY
    list_price DESC;

C)使用AND和其他操作符

SELECT
    *
FROM
    production.products
WHERE
    brand_id = 1
OR brand_id = 2
AND list_price > 1000
ORDER BY
    brand_id DESC;

此例子中，使用了AND和OR操作符，SQL Server先处理AND操作符。然后处理OR操作符.

如果想先执行OR操作符得话：

SELECT
    *
FROM
    production.products
WHERE
    (brand_id = 1 OR brand_id = 2)
AND list_price > 1000
ORDER BY
    brand_id;

SQL Server OR

OR简介

SQL Server OR是一个逻辑运算符，允许您组合两个布尔表达式。当任一条件的计算结果为TRUE时，它将返回TRUE。

语法：

boolean_expression OR boolean_expression  

下面是TRUE,FALSE,UNKNOWN通过AND不同组合的结果：

TRUE OR TRUE == TRUE
TRUE OR FALSE == TRUE
FALSE OR FALSE == FALSE
TRUE OR UNKNOWN == TRUE
FALSE OR UNKNOWN == UNKNOWN
UNKNOWN OR UNKNOWN == UNKNOWN

OR示例：
A）查找标价低于200或高于6000的产品：

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price < 200
OR list_price > 6000
ORDER BY
    list_price;

查找品牌id为1、2或4的产品：

SELECT
    product_name,
    brand_id
FROM
    production.products
WHERE
    brand_id = 1
OR brand_id = 2
OR brand_id = 4
ORDER BY
    brand_id DESC;

可以用IN运算符替换多个OR运算符，如以下查询所示：

SELECT
    product_name,
    brand_id
FROM
    production.products
WHERE
    brand_id IN (1, 2, 3)
ORDER BY
    brand_id DESC;

SQL Server IN

简介

IN运算符是一个逻辑运算符，允许您测试指定值是否与列表中的任何值匹配。

语法：

column | expression IN ( v1, v2, v3, ...)

• 首先，指定列或表达式。
• 其次，指定值列表。所有值的类型必须与列或表达式的类型相同。

如果列或表达式中的值等于列表中的任何值，则IN运算符的结果为TRUE。

IN运算符等价于多个OR运算符，因此，下面是等价的：

column IN (v1, v2, v3)

column = v1 OR column = v2 OR column = v3

要否定IN运算符，使用NOT IN运算符，如下所示：

column | expression NOT IN ( v1, v2, v3, ...)

除了值列表之外，还可以使用子查询，该子查询返回值列表供IN运算符使用，如下所示：

column | expression IN (subquery)

在这种语法中，子查询是一个SELECT语句，返回单个列的值列表。

请注意，如果列表包含NULL，则IN或NOT IN的结果将是UNKNOWN:

示例

有如下产品表：

A）SQL Server IN与值列表示例
查找标价为以下值之一的产品：89.99、109.99和159.99：

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price IN (89.99, 109.99, 159.99)
ORDER BY
    list_price;

上述查询相当于以下使用OR运算符的查询：

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price = 89.99 OR list_price = 109.99 OR list_price = 159.99
ORDER BY
    list_price;

要查找其标价不是上述价格之一的产品，请使用NOT IN运算符，如以下查询所示：

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price NOT IN (89.99, 109.99, 159.99)
ORDER BY
    list_price;

B）SQL Server IN与子查询示例

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    product_id IN (
        SELECT
            product_id
        FROM
            production.stocks
        WHERE
            store_id = 1 AND quantity >= 30
    )
ORDER BY
    product_name;

• 首先，子查询返回产品id列表。
• 其次，外部查询检索产品id与子查询返回的任何值匹配的产品。

SQL Server between

BETWEEN简介

逻辑运算符BETWEEN，允许指定要查询的范围：

column | expression BETWEEN start_expression AND end_expression

查询返回指定列column或表达式expression得值大于等于start_expression并且小于等于end_expression得记录。

也可以使用>=和<=进行替换:

column | expression <= end_expression AND column | expression >= start_expression 

要否定BETWEEN运算符的结果，使用NOT BETWEEN操作符，如下所示：

column | expression NOT BETWEEN start_expression AND end_expresion

同理可以使用<和>替换：

column | expression < start_expression AND column | expression > end_expression

注意，如果对BETWEEN或NOT BETWEEN的任何输入为NULL，则结果是UNKNOWN。

BETWEEN示例

A)BETWEEN与数字类型

有如下产品表：

1）查找标价在149.99和199.99之间的产品：

SELECT
    product_id,
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price BETWEEN 149.99 AND 199.99
ORDER BY
    list_price;

2）查询标价不在149.99和199.99之间的产品，请使用NOT BETWEEN运算符，如下所示：

SELECT
    product_id,
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price NOT BETWEEN 149.99 AND 199.99
ORDER BY
    list_price;

B)BETWEEN与日期类型

有如下订单表：

以下查询将查找客户在2017年1月15日至17日之间下的订单：

SELECT
    order_id,
    customer_id,
    order_date,
    order_status
FROM
    sales.orders
WHERE
    order_date BETWEEN '20170115' AND '20170117'
ORDER BY
    order_date;

请注意，要指定字符串格式的日期，请使用YYYYMMDD格式，其中YYYY是4位数字的年份，例如2017年，MM是2位数字的月份，例如01，DD是两位数字的天，例如15。

SQL Server LIKE

简介

SQL Server 逻辑运算符LILE用于确定字符串是否与指定模式匹配。
模式可以包括常规字符和通配符。在SELECT、UPDATE和DELETE语句的WHERE子句中使用LIKE运算符来基于模式匹配筛选行。

语法：

column | expression LIKE pattern [ESCAPE escape_character]

Pattern

模式是要在列或表达式中搜索的字符序列。它可以包括以下有效通配符：

• 百分比通配符（%）：零个或多个字符的任何字符串。
• 下划线通配符（_）：任何单个字符。
• [字符列表]通配符：指定集合中的任何单个字符。
• [字符-字符]：指定范围内的任何单个字符。
• [^]：不在列表或范围内的任何单个字符。

通配符使LIKE运算符比相等（=）和不相等（！=）的字符串更灵活。

Escape character(转义字符)

转义字符指定LIKE运算符将通配符视为常规字符。转义字符没有默认值，只能计算为一个字符。

如果列或表达式与指定的模式匹配，则LIKE运算符返回TRUE。

要否定LIKE运算符的结果，请按如下方式使用NOT运算符：

column | expression NOT LIKE pattern [ESCAPE escape_character]

LIKE示例

有如下客户表：

％（百分比）通配符示例

以下示例查找姓氏以字母z开头的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE 'z%'
ORDER BY
    first_name;

以下示例返回姓氏以字符串er结尾的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '%er'
ORDER BY
    first_name;

以下语句检索姓氏以字母t开头，以字母s结尾的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE 't%s'
ORDER BY
    first_name;

_（下划线）通配符示例

下划线表示单个字符。例如，以下语句返回第二个字符为字母u的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '_u%'
ORDER BY
    first_name; 

通配符 _u%:

• 第一个下划线字符（_）匹配任何单个字符。
• 第二个字母u与字母u完全匹配
• 第三个字符%匹配任何字符序列

[字符列表]通配符示例

带有字符列表的方括号，例如[ABC]表示一个字符，该字符必须是列表中指定的字符之一。

例如，以下查询返回姓氏中第一个字符为Y或Z的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[YZ]%'
ORDER BY
    last_name;

[字符-字符]通配符示例

带有字符范围的方括号，例如[A-C]，表示必须在指定范围内的单个字符。

例如，以下查询查找姓氏中的第一个字符是A到C范围内的字母的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[A-C]%'
ORDER BY
    first_name;

[^字符列表或范围]通配符示例

带有插入符号（^）的方括号后跟一个范围，例如[^A-C]或[^ABC]，表示不在指定范围或字符列表中的单个字符。

例如，以下查询返回姓氏中的第一个字符不是A到X范围内的字母的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '[^A-X]%'
ORDER BY
    last_name;

NOT LIKE示例

以下示例使用NOT LIKE运算符查找名字中第一个字符不是字母A的客户：

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    first_name NOT LIKE 'A%'
ORDER BY
    first_name;

LIKE与转义字符示例

首先，为演示创建一个新（反馈）表：

CREATE TABLE sales.feedbacks (
   feedback_id INT IDENTITY(1, 1) PRIMARY KEY, 
    comment     VARCHAR(255) NOT NULL
);

第二，在反馈表中插入一些行：

INSERT INTO sales.feedbacks(comment)
VALUES('Can you give me 30% discount?'),
      ('May I get me 30USD off?'),
      ('Is this having 20% discount today?');

第三，查询一下：

SELECT * FROM sales.feedbacks;

如果您想在“评论”列中搜索30%，您可能会得到如下查询：

SELECT 
   feedback_id,
   comment
FROM 
   sales.feedbacks
WHERE 
   comment LIKE '%30%';

查询返回包含30%和30USD的评论，这不是我们期望的。

要解决此问题，您需要使用ESCAPE子句：

SELECT 
   feedback_id, 
   comment
FROM 
   sales.feedbacks
WHERE 
   comment LIKE '%30!%%' ESCAPE '!';

在该查询中，ESCAPE子句指定字符！是转义字符。它指示LIKE将%字符视为文字字符串，而不是通配符。注意，如果没有ESCAPE子句，查询将返回空结果集。

SQL Server别名

列别名

使用SELECT语句查询表中的数据时，SQL Server将列名用作输出的列标题。请参见以下示例：

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;

如输出所示，first_name和last_name列名分别用于列标题。

要获取客户的全名，可以使用+运算符连接名字、空格和姓氏，如以下查询所示：

SELECT
    first_name + ' ' + last_name
FROM
    sales.customers
ORDER BY
    first_name;

SQL Server将全名列返回为No column name(无列名)，这在本例中没有意义。

要在查询执行期间为列或表达式指定临时名称，可以使用别名。

column_name | expression  AS column_alias

在此语法中，使用AS关键字分隔列名或表达式和别名。

AS关键字是可选的，因此可以按如下方式为列分配别名：

column_name | expression column_alias

回到上面的示例，您可以使用列别名重写查询：

SELECT
    first_name + ' ' + last_name AS full_name
FROM
    sales.customers
ORDER BY
    first_name;

注意，如果列别名包含空格，则需要将其括在引号中，如以下示例所示

SELECT
    first_name + ' ' + last_name AS 'Full Name'
FROM
    sales.customers
ORDER BY
    first_name;

以下示例显示了如何为列分配别名：

SELECT
    category_name 'Product Category'
FROM
    production.categories;

为列指定别名后，可以在ORDER BY子句中使用列名或列别名，如以下示例所示：

SELECT
    category_name 'Product Category'
FROM
    production.categories
ORDER BY
    category_name;  


SELECT
    category_name 'Product Category'
FROM
    production.categories
ORDER BY
    'Product Category';

注意，ORDER BY子句是最后一个要处理的子句，因此列别名在ORDER BY时是已知的。

表别名

与列别名类似，可以使用或不使用AS关键字分配表别名：

table_name AS table_alias
table_name table_alias

请参见以下示例：

SELECT
    sales.customers.customer_id,
    first_name,
    last_name,
    order_id
FROM
    sales.customers
INNER JOIN sales.orders ON sales.orders.customer_id = sales.customers.customer_id;

在本例中，customers和orders表都有一个名为customer_id的列，因此，需要使用以下语法引用该列：

table_name.column_name

比如：

sales.custoners.customer_id
sales.orders.customer_id

如果直接写字段名的话就会报错。

上面的查询可读性很差。但是，可以通过使用表别名来提高可读性，如下所示：

SELECT
    c.customer_id,
    first_name,
    last_name,
    order_id
FROM
    sales.customers c
INNER JOIN sales.orders o ON o.customer_id = c.customer_id;

这个例子中C是客户表的别名，o是订单表的别名。

为表分配别名后，必须使用别名引用表列。否则，SQL Server将报错。