本文共 5520 字，大约阅读时间需要 18 分钟。

第十四章 - 使用子查询

子查询

在学习SQL时，查询（query）是最基础的操作。简单查询是从单个数据库表中检索数据的基本操作。然而，SQL还允许嵌套在其他查询中的查询，这被称为子查询（subquery）。

子查询是一种强大的工具，可以帮助我们在复杂的数据检索任务中分解问题。通过使用子查询，我们可以将复杂的逻辑分解为多个简单的查询，并将这些查询的结果结合起来。

利用子查询进行过滤

在实际应用中，我们经常需要根据某些条件过滤数据。例如，假设我们有以下数据库表：

• orders：存储订单信息，包含订单号、客户ID和订单日期。
• orderitems：存储订单中的具体物品信息，包含物品ID和订单号。
• customers：存储客户信息，包含客户ID、姓名和联系方式。

我们需要列出订购物品“TNT2”的所有客户。具体步骤如下：

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);

为了实现上述步骤，可以将子查询嵌套在WHERE子句中：

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

子查询的原则

• 列必须匹配：在WHERE子句中使用子查询时，子查询返回的列必须与外部查询的列匹配。通常，子查询会返回单个列用于单个列匹配，但也可以返回多个列。
• 子查询和性能：使用子查询并不总是最有效的方法。对于复杂的数据检索任务，可能需要优化查询性能。

子查询作为计算字段

子查询还可以用来创建计算字段。例如，我们可以为每个客户显示其订单总数。

SELECT cust_name, cust_state FROM customers;

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

此时，orders字段是由圆括号中的子查询计算得出。子查询会为每个客户执行一次，返回订单总数。

相关子查询

当子查询涉及外部查询的字段时，称为相关子查询（correlated subquery）。在这种情况下，子查询中的字段必须与外部查询中的字段明确相关联。

例如：

SELECT cust_name, cust_contact FROM customers WHERE cust_id = (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

如果不明确指定表和字段，可能会导致错误。因此，必须使用明确的字段引用方式：

SELECT cust_name, cust_contact FROM customers WHERE cust_id = orders.cust_id AND orders.order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

第十五章 - 联结表

关系表

在数据库设计中，关系表的核心思想是将相同的数据存储在不同的表中，以减少数据冗余。例如：

• vendors 表：存储供应商信息。
• products 表：存储产品信息。

vendors表中的vend_id是products表的外键。

为什么使用联结

分解数据为多个表可以更有效地存储和处理数据。然而，如何用单一的SELECT语句从多个表中检索数据？这就是联结的作用。

联结是一种机制，用于在一个SELECT语句中关联多个表，返回多个表中的数据。

创建联结

最简单的联结是等值联结（equijoin），它基于两个表之间的相等测试。例如：

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

此时，vendors和products表通过vend_id字段进行联结。

WHERE子句的重要性

在联结多个表时，WHERE子句用于指定联结条件。没有WHERE子句，vendors和products表将执行笛卡尔积（cartesian product），返回所有可能的行组合。

内部联结

内部联结（equijoin）是最常用的联结类型。它要求两个表中的对应字段值相等。可以使用INNER JOIN语法明确指定联结类型：

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

联结多个表

可以使用多个JOIN关键字将多个表联结在一起。例如：

SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

此时，orderitems表通过prod_id与products表关联，而products表通过vend_id与vendors表关联。

高级联结

表别名

为了简化SELECT语句，可以为表起别名。例如：

SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';

不同类型的联结

除了等值联结，还有其他三种类型的联结：

自联结示例：

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

自然联结示例：

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

外部联结示例：

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

使用聚集函数与联结

聚集函数可以与联结一起使用。例如，统计每个客户的订单数量：

SELECT customers.cust_id, customers.cust_name, COUNT(orders.order_num) AS num_orders FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

第十七章 组合查询

什么时候使用组合查询？

组合查询（Compound Query）是将多个SELECT语句合并为单个结果集的技术。常见的使用场景包括：

使用UNION操作符

UNION操作符用于组合多个SELECT语句。例如：

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

此时，UNION关键字将两个SELECT语句的结果合并为单个结果集。

UNION的规则

包含或取消重复的行

UNION默认会去除重复的行。如果需要保留所有行，可以使用UNION ALL：

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

对组合查询结果排序

SELECT语句的输出可以使用ORDER BY子句排序。例如：

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002) ORDER BY vend_id, prod_price;

第十八章 - 全文本搜索

全文本搜索的优势

传统的LIKE和正则表达式匹配有以下限制：

全文本搜索可以解决这些问题。

启用全文本搜索支持

在创建表时，可以启用全文本搜索：

CREATE TABLE productnotes(note_id int NOT NULL AUTO_INCREMENT, prod_id char NOT NULL, note_date datetime NOT NULL, note_text text NULL, PRIMARY KEY(note_id), FULLTEXT(note_text)) ENGINE=MyISAM;

进行全文本搜索

使用Match()和Against()函数：

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

查询扩展

WITH QUERY EXPANSION可以扩展搜索范围。例如：

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

布尔方式

布尔方式提供更高级的搜索控制：

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

布尔操作符包括：

全文本搜索的使用说明

通过使用全文本搜索，可以高效地检索文本数据，支持复杂的搜索逻辑。

转载地址：http://hyhwz.baihongyu.com/