第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排序数据