SQL必知必会（第3版）学习笔记【6-10章】

第6章 用通配符进行过滤

• LIKE操作符
• 通配符：wildcard，用来匹配值的一部分的特殊字符
• 通配符搜索只能用于文本字段（串）

百分号（%）通配符

• %，表示任何字符出现任意次数（包括0次）

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

下划线（_）通配符

• （_)只匹配单个字符

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

• 注意数据后面可能存在的用来补全的空格，会影响匹配

方括号（[]）通配符

• []用来指定一个字符集，它必须匹配指定位置的一个字符
• Access支持，其他的看文档

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

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

使用通配符的技巧

• 不要过分使用通配符
• 一般不要把通配符置于搜索模式的开始处，因为搜索起来会最慢

---

第7章 创建计算字段

计算字段

• 存储在数据库表中的数据一般不是应用程序所需要的格式
• 需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出之后再重新格式化。（因为通常DBMS的设计可以快速有效完成上述处理）
• 计算字段是运行时在SELECT语句内创建的
• 例如：有单价字段和数量字段，计算总价=单价*数量

拼接字段

• 拼接：将值联结到一起构成单个值
• 可用（+）或（||）操作符

SELECT vend_name + '(' + vend_country + ')'
FROM Vendors 
ORDER BY vend_name;

• MySQL中使用CONCAT()函数

• RTRIM()函数用来去除填充为列宽的空格（右边的所有空格）

• LTRIM()去除左边的空格
• TRIM()去除串左右两边的空格

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' 
FROM Vendors 
ORDER BY vend_name;

使用别名

• 为了引用没有名字的值
• 别名（alias）：一个字段或值的替换名
• 用AS关键字

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title 
FROM Vendors 
ORDER BY vend_name;

• 创建一个包含指定计算的名为vend_title的计算字段
• 常用于将多个单词的列名重命名为一个单词的名字
• 别名也称为【导出列】

执行算数计算

• +
• -
• *
• /

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

---

第8章 使用数据处理函数

• 函数：SQL支持利用函数来处理数据

使用函数

• 处理文本串的文本函数
• 数值数据的算数操作
• 处理日期和时间值并从这些值中提取特定成分的日期和时间函数
• 返回DBMS正使用的特殊信息的系统函数（如用户登录信息）

文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors 
ORDER BY vend_name;

• LEFT()：返回串左边的字符（或使用子字符串函数）
• RIGHT()：返回串右边的字符（或使用子字符串函数）
• LENGTH()：返回串的长度，也使用DATALENGTH()或LEN()
• LOWER():转换为小写（Access使用LCASE()）
• UPPER()：转换为大写（Access使用UCASE()）
• LTRIM()：去掉串左边的空格
• RTRIM()：去掉串右边的空格
• SOUNDEX()：返回串的SOUNDEX值（文本串转换为描述其语音表示的算法，例如匹配近似发音的字符串）

日期和时间处理函数

SELECT order_num 
FROM Orders 
WHERE DATEPART('yyyy', order_date) = 2004;

• 不同DBMS的日期-时间处理函数可能不同

数值处理函数

• ABS()：绝对值
• COS()：余弦
• EXP()：指数
• PI()：圆周率
• SIN()：正弦
• SQRT()：平方根
• TAN()：正切

---

第9章 汇总数据

聚集函数

• 运行在行组上，计算和返回单个值的函数
• 其他说法：聚合函数、合计函数、统计函数

我们经常需要汇总数据，而不用把它们实际检索出来。
需要对数据汇总而不是实际数据本身。
- 确定表中行数（或者满足某个条件或包含某个特定值的行数）
- 获得表中行组的和
- 找出表列（或所有行或某些特定的行）的最大、最小、平均值

即：实际想要的是汇总信息，而不是实际数据本身。

• AVG()：返回某列的平均值
• COUNT()：返回某列的行数
• MAX()：返回某列的最大值
• MIN()：返回某列的最小值
• SUM()：返回某列值之和

AVG()函数

SELECT AVG(prod_price) AS avg_price
FROM Products;

• AVG()函数忽略列值为NULL的行

COUNT()函数

• COUNT(*)：所有行
• COUNT(column)：忽略NULL值

SELECT COUNT(*) AS num_cust 
FROM Customers;

MAX()函数

SELECT MAX(prod_price) AS max_price 
FROM Products;

• MAX()函数忽略列值为NULL的行

MIN()函数

• 与MAX()类似

SUM()函数

SELECT SUM(quantity) AS items_ordered 
FROM OrderItems 
WHERE order_num = 20005;

• SUM()函数忽略列值为NULL的行

聚合不同值

以上5个聚合函数都可以如下使用：
- 对所有的行执行计算，指定ALL参数或不给参数（默认ALL）
- 只包含不同的值，指定DISTINCT参数

Access不支持DISTINCT

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01';

组合聚合函数

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min 
FROM Products;

---

第10章 分组数据

• GROUP BY子句
• HAVING子句

数据分组

• 分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

创建分组

• 分组是在SELECT语句的GROUP BY子句中建立的

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

• GROUP BY子句指示DBMS分组数据，然后对每个组分别进行聚集
• 规定：
  
  • GROUP BY子句可以包含任意数目的列，因而能对分组进行嵌套，为数据分组提供更细致的控制；
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）；
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）；
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出;
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组；
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组

• HAVING子句（类似于WHERE，差别是WHERE过滤行，HAVING过滤分组）

SELECT cust_id, COUNT(*) AS orders 
FROM Orders 
GROUP BY cust_id
HAVING COUNT(*) >= 2;

WHERE和HAVING同时使用

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

分组和排序

• ORDER BY子句是保证数据正确排序的唯一方法
• 不要依赖于GROUP BY排序数据

SELECT子句顺序