一、子查询

在 SQL 的 select 语句中可以创建 子查询(subquery),即嵌套在其他查询中的查询。子查询总是从内向外处理。

相关子查询:涉及外部查询的子查询。

1.进行过滤

在 where 子句中使用子查询(一般与 in 操作符连用),应保证 select 语句与 where 子句中的列相匹配。

例,需要列出订购物品 TNT2 的所有客户:

1
2
3
4
5
select cust_id,cust_name from customers where cust_id in (
select cust_id from orders where order_num in (
select order_num from orderitems where prod_id='TNT2'
)
);

SQL 语句中对于能嵌套的子查询的数目没有限制,但过多的子查询会导致性能低下,而且子查询也不是多表数据检索的最有效的方法。

2.作为计算字段

可以使用子查询创建计算字段。例,显示 customers 表中每个客户的订单总数:

1
2
3
mysql> select cust_id,cust_name,(select count(*) from orders
-> where orders.cust_id=customers.cust_id) as cust_orders
-> from customers;

二、联结表

良好的关系表的设计要保证把信息分解成多个表,一类数据一个表,各表之间通过某些值(即关系,relational)互相关联。外键(foreign key)为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。

分解数据为多个表能更有效地存储,更方便地处理,并具有更大的可伸缩性。但这些好处是有代价的。当数据存储在多个表中时,使用联结(join,也称为连接)机制在一条 select 查询语句中关联表、查询数据。

1.创建联结

联结的创建很简单,规定要联结的表以及它们如何关联即可。如:

1
2
3
4
mysql> select vend_name,prod_name,prod_price
-> from vendors,products
-> where vendors.vend_id = products.vend_id
-> order by vend_name,prod_name;

这里利用了 where 子句建立正确的联结关系。

在 select 语句中联结多表时,相应的关系是在运行中构造的。在联结两个表时,第一个表的每一行会与第二个表的每一行配对(笛卡尔积);where 子句作为过滤条件,它只包含那些匹配给定条件(联结条件)的行。

2.内部联结(等值联结)

这种基于两个表之间的相等测试的联结称为内部联结(也称为等值联结)。对于内部联结,在 from 子句中以 inner join 指定两个表之间的关系,联结条件用 on 子句替代 where 子句。

例如,下面的 select 语句返回与前面例子相同的数据:

1
2
3
mysql> select vend_name,prod_name,prod_price
-> from vendors inner join products
-> on vendors.vend_id = products.vend_id order by vend_name,prod_name;

在这两种语法中,应首选 inner join 语法。这样会使联结语法更明确,性能有所提升。

3.自联结

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。使用表别名来区分相同表的不同表对象。

例如,要求查询生产 ID 为 DTNTR 的物品的供应商,然后找出这个供应商生产的其他物品:

1
2
mysql> select p1.prod_id,p1.prod_name from products p1,products p2
-> where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

4.外部联结

联结包含了那些在相关表中没有关联行的行(即在一个表中该行有数据,而其它相关表的关联行没有数据,这些行也进行联结输出),这种类型的联结称为外部联结

外部联结在 select 语句中,使用关键字 outer join 替代 where 子句来指定联结的类型。在使用 outer join 语法时,必须使用 right 或 left 关键字指定包含其所有行的表。(right 指的是 outer join 右边的表,left 指 outer join 左边的表。)

例如,对每个客户下了多少订单进行计数,包括那些至今尚未下订单的客户:

内部联结:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select customers.cust_id,orders.order_num
-> from orders inner join customers
-> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)

左外联结(从左边的表选择所有的行):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select customers.cust_id,orders.order_num
-> from customers left outer join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)

上面的左外联结中,行 customers.cust_id = 10002 其关联行 orders.order_num 没有数据,也被联结输出了;而在上面的内部联结中则没有联结输出。

右外联结(从右边的表选择所有的行):

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select customers.cust_id,orders.order_num
-> from customers right outer join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)

从右边表的行对应过来,自然就少了 customers.cust_id = 10002 的那一行。

三、组合查询

MySQL 允许执行多个查询(多条 select 语句),并将结果作为单个查询结果集返回。这些组合查询通常称为 并(union) 或复合查询。

关于 union 关键字的使用:

  • 必须由两条及以上的 select 语句组成,语句之间用关键字 union 分隔。
  • union 中的每个查询必须包含相同的列、表达式或聚集函数(次序也应相同)。
  • union 默认从查询结果集中去除了重复的行;使用 union all 可返回所有的行。
  • order by 子句只能用于最后的输出结果,即应出现在最后一天 select 语句之后。

例如,查询价格小于等于 5 的所有物品的一个列表,以及供应商 1001 和 1002 生产的所有物品(不考虑价格列表):

1
2
3
4
5
6
mysql> 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;

四、全文本搜索

在 MySQL 中,可以使用 like 关键字和正则表达式的方式搜索、匹配文本。相比之下,利用全文本搜索可以利用索引机制提升性能、更明确地控制匹配规则、匹配结果更智能化。

MySQL 的 MyISAM 引擎支持全文本搜索,对于需要启用全文本搜索的表,需使用 full text 指定索引列。在定义之后,MySQL 自动维护该索引,在数据更新时索引随之自动更新(需要一定的开销)。

在索引之后,使用两个函数 match() 和 against() 执行全文本搜索。其中 match() 指定被搜索的列(必须与 full text 中的定义相同),against() 指定要使用的搜索表达式。

例如,查询列 note_text 中包含词 rabbit 的行:

1
2
mysql> select note_text from productnotes
-> where match(note_text) against('rabbit');

全文本搜索还有很多的功能,如查询扩展、布尔文本搜索等。


(完)