MySQL - 子查询与联结

MySQL 子查询与联结

子查询
联结表
高级联结
组合查询（UNION）
全文本搜索

子查询

子查询是嵌套在其他查询中的查询。

利用子查询进行过滤：把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。

-- 查找订购物品 TNT2 的所有客户
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 IN (
    SELECT cust_id FROM orders WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
    )
);

作为计算字段使用子查询：

-- 显示 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.cust_id 和 customers.cust_id 使用完全限定列名避免歧义。

联结表

关系表的设计保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值互相关联。联结是一种机制，用来在一条 SELECT 语句中关联表。

外键：某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

关系表的好处：供应商信息不重复、信息变动只更新一处、数据一致。

创建联结：在 FROM 子句列出多个表，在 WHERE 子句中指定联结条件。

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

笛卡儿积：没有联结条件的表关系返回的结果为笛卡儿积，检索出的行数是第一个表行数乘以第二个表行数。应该保证所有联结都有 WHERE 子句。

内部联结：等值联结（equijoin）可以使用 INNER JOIN 语法明确指定，联结条件用 ON 子句给出。

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

联结多个表：SELECT 语句对联结的表的数目没有限制。

-- 显示订单 20005 中的物品，涉及 3 个表
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;

联结的表越多，性能下降越厉害，不要联结不必要的表。

高级联结

使用表别名：缩短 SQL 语句、允许在单条 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 语句中两次引用同一张表，使用别名区分。

-- 找到生产 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 = 'TNT2';

自然联结：排除多次出现的列，使每列只返回一次。通常通过对表使用通配符（SELECT *）对其他表的列使用明确的子集来完成。

外部联结：包含关联表中没有关联行的行。LEFT OUTER JOIN 包含左表的所有行，RIGHT OUTER JOIN 包含右表的所有行。

-- 左外部联结：检索所有客户及其订单（包括没有订单的客户）
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结：

-- 检索所有客户及每个客户所下的订单数
SELECT customers.cust_name, customers.cust_id,
    COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询

使用 UNION 操作符将数条 SQL 查询组合成单个结果集。

-- 查找价格小于等于 5 的产品，以及供应商 1001 和 1002 生产的所有产品
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);

等价的 WHERE 子句方式：

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

UNION 规则：

每个 UNION 中的查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容
自动去除重复的行，使用 UNION ALL 保留所有行
只能使用一条 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;

全文本搜索

并非所有引擎都支持全文本搜索，MyISAM 支持，InnoDB 不支持。一般在创建表时启用全文本搜索，使用 FULLTEXT 索引。

CREATE TABLE productnotes (
    note_id int NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

进行全文本搜索：使用 Match() 和 Against() 函数。Match 指定被搜索的列，Against 指定搜索表达式。

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

传递给 Match 的值必须与 FULLTEXT 定义中的相同。全文本搜索不区分大小写，并且按文本匹配的良好程度排序（具有较高等级的行先返回）。

使用查询扩展：WITH QUERY EXPANSION 进行两次扫描，找出更多相关行。

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

布尔文本搜索：IN BOOLEAN MODE 提供更细粒度的控制，即使没有 FULLTEXT 索引也可使用。

-- 匹配包含 heavy 但不包含任意以 rope 开头的词的行
SELECT note_text FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

布尔操作符：

操作符	说明
`+`	包含，词必须存在
`-`	排除，词必须不存在
`>`	包含，且增加等级值
`<`	包含，且减少等级值
`()`	把词组成子表达式
`~`	取消一个词的排序值
`*`	词尾的通配符
`""`	定义一个短语

全文本搜索注意事项：

索引全文数据时，短词（3 个或以下字符）被忽略且不被索引
很多词出现频率很高（如 the、an），搜索这些词没有用处，MySQL 内建有一个非用词列表
50% 规则：如果一个词出现在 50% 以上的行中，则将它作为一个非用词
忽略词中的单引号
没有词分隔符（如中文）的语言不能适当返回全文本搜索结果

MySQL - 子查询与联结