第6节 用通配符进行过滤

LIKE 操作符

1.通配符（wildcard）：用来匹配值的一部分的特殊字符。

2.搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。

3.搜索可以是区分大小写的。

4. 为在搜索子句中使用通配符，必须使用 LIKE 操作符。

5.百分号（%）通配符：在搜索串中，%表示任何字符出现任意次数。

• 此例子使用了搜索模式'Fish%'。在执行这条子句时，将检索任意以Fish 起头的词。

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

• 搜索模式'%bean bag%'表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么字符。

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';

• 下面的例子找出以F起头、以y结尾的所有产品。

SELECT prod_name
FROM Products 
WHERE prod_name LIKE 'F%y';

• 'F%y%'还匹配 y 之后的字符（或空格）

6.下划线（_）通配符：下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

• 下述WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '__ inch teddy bear';

• 与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少。

7.方括号（[ ]）通配符：方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

• 下述语句用于找出所有名字以 J 或 M 起头的联系人

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

• [JM]匹配方括号中任意一个字符，它也只能匹配单个字符。

• 此通配符可以用前缀字符^ （脱字号）来否定。

挑战题

1. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品。

SELECT prod_name
FROM Products
WHERE prod_name LIKE '%toy%';

结果：

2. 反过来再来一次。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序。

SELECT prod_name,prod_desc
FROM Products
WHERE NOT prod_name LIKE '%toy%';

结果：

3. 编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个LIKE 比较。

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

结果：

4. 来个比较棘手的。我没有特别向你展示这个语法，而是想看看你根据目前已学的知识是否可以找到答案。编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';

结果：

第7节 创建计算字段

计算字段

1.字段（field）：基本上与列（column）的意思相同，经常互换使用，不过数据库列一 般称为列，而字段这个术语通常在计算字段这种场合下使用。

2. 只有数据库知道 SELECT 语句中哪些列是实际的表列， 哪些列是计算字段。

拼接字段

1.拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。

SELECT Concat(vend_name, ' (', vend_country, ')') 
FROM Vendors 
ORDER BY vend_name;

2.TRIM 函数：大多数 DBMS 都支持 RTRIM()（它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及 TRIM()（去掉字符 串左右两边的空格）。

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') 
FROM Vendors 
ORDER BY vend_name;

3.别名（alias）：一个字段或值的替换名

• 别名用 AS 关键字赋予

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title 
FROM Vendors 
ORDER BY vend_name;

• 别名有时也称为导出列（derived column）。

执行算术计算

SELECT 
    prod_id, 
    quantity, 
    item_price, 
    quantity*item_price AS expanded_price 
FROM OrderItems 
WHERE order_num = 20008;

挑战题

1. 别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。编写 SQL 语句，从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，将 vend_name 重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address 重命名为 vaddress。按供应商名称对结果进行排序（可以使用原始名称或新的名称）。

SELECT
    vend_id,
    vend_name AS vname,
    vend_address AS vaddress,
    vend_city AS vcity
FROM Vendors
ORDER BY vend_name;

结果：

2. 我们的示例商店正在进行打折促销，所有产品均降价 10%。编写 SQL 语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。 sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）。

SELECT 
    prod_id,
    prod_price,
    prod_price*0.9 AS sale_price 
FROM Products;

结果：

第8节 使用函数处理数据

函数

1.可移植（portable）：所编写的代码可以在多个系统上运行。

• 为了代码的可移植，许多 SQL 程序员不赞成使用特定于实现的功能。

使用函数

1. 大多数 SQL 实现支持以下类型的函数：

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。

• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。

• 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。

• 返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数。

2.文本处理函数

函 数	说 明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SUBSTR()或SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

• 下述语句使用SOUNDEX()函数进行搜索，它匹配所有发音类似于 Michael Green 的联系名

SELECT cust_name, cust_contact
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

3.日期和时间处理函数

• 下述使用名为 YEAR()的函数从日期中提取年份

SELECT order_num 
FROM Orders 
WHERE YEAR(order_date) = 2020;

• 下述使用名为 MONTH()的函数从日期中提取月份

SELECT order_num 
FROM Orders 
WHERE MONTH(order_date) = 1;

4.数值处理函数

函 数	函 数
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率 π 的值
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

挑战题

1. 我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。编写 SQL 语句，返回顾客 ID （cust_id）、顾客名称（customer_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。例如，我的登录名是 BEOAK（Ben Forta，居住在 Oak Park）。提示：需要使用 函数、拼接和别名。

SELECT
    cust_id,
    cust_name,
    Concat(
        UPPER(LEFT(cust_contact, 2)),
        UPPER(LEFT(cust_city, 3)),
        ' (',
        cust_name,
        '，居住在',
        cust_address,
        ' )'
    ) AS user_login
FROM Customers;

结果：

2. 编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期排序。你应该能够根据目前已学的知识来解决此问题，但也可以开卷查阅 DBMS 文档。

SELECT order_num, order_date
FROM Orders
WHERE (
        YEAR(order_date) = 2020
        AND MONTH(order_date) = 1
    )
    OR YEAR(order_date) < 2020;

结果：

第9节 汇总数据

聚集函数

1.聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和
2.COUNT()函数有两种使用方式：

• 使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值；COUNT()函数会忽略指定列的值为 NULL 的行。

• 使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。

3. 在用于文本数据时，MAX()返回按该列排序后的最后一行，MIN()返回该列排序后最前面的行。

4. SUM()、MIN()和MAX()函数忽略列值为 NULL 的行。

聚集不同值

1.以上 5 个聚集函数都可以如下使用：

• 对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。
• 只包含不同的值，指定 DISTINCT 参数。

2. DISTINCT 不能用于 COUNT(*)：如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用 于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表 达式。

挑战题

1. 编写 SQL 语句，确定已售出产品的总数（使用 OrderItems 中的quantity 列）。

SELECT SUM(quantity) AS sum
FROM OrderItems;

结果：

2. 修改刚刚创建的语句，确定已售出产品项（prod_item）BR01 的 总数。

SELECT SUM(quantity) AS sum
FROM OrderItems
WHERE prod_id = 'BR01';

结果：

3. 编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品 的价格（prod_price）。将计算所得的字段命名为 max_price。

SELECT MAX(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10;

结果：

第10节 分组数据

创建分组

1.分组是使用 SELECT 语句的 GROUP BY 子句建立的。

2.GROUP BY 子句：指示 DBMS 分组数据，然后对每个组而不是整 个结果集进行聚集。

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id;

3.GROUP BY 子句的规定：

• GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套， 更细致地进行数据分组。

• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进 行汇总。

• GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。

• 大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型。

• 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句 中给出。

• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。 如果列中有多行 NULL 值，它们将分为一组。

• GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

4.通过相对位置指定列：有的 SQL 实现允许根据 SELECT 列表中的位置指定 GROUP BY 的列。例如，GROUP BY 2, 1 可表示按选择的第二个列分组，然后再按第一个列分组。

过滤分组

1. WHERE 过滤行，而 HAVING 过滤分组。

2. 下面的例子，它列出具有两个以上产品且其价格 大于等于 4 的供应商

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

挑战题

1. OrderItems 表包含每个订单的每个产品。编写 SQL 语句，返回每个 订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行排序。

SELECT order_num,COUNT(*) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;

结果：

2. 编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个 供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行排序。

SELECT prod_id,MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY prod_id;

结果：

3. 确定最佳顾客非常重要，请编写 SQL 语句，返回至少含 100 项的所有 订单的订单号（OrderItems 表中的 order_num）。

SELECT order_num,SUM(quantity) AS sum_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity)>=100;

结果：

4. 确定最佳顾客的另一种方式是看他们花了多少钱。编写 SQL 语句，返回总价至少为 1000 的所有订单的订单号（OrderItems 表中的 order_num）。提示：需要计算总和（item_price 乘以 quantity）。按订单号对结果进行排序。

SELECT order_num,SUM(quantity*item_price) AS sum_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=1000
ORDER BY sum_price;

结果：

5. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

答案：第三句应该改为GROUP BY order_num