Head First SQL 第九章

查询中的查询。P379

如果想把甲的查询结果作为乙的查询输入，就该子查询出场。

子查询有助于避免数据重复，让查询更加动态灵活。

Greg不想把招聘服务信息存在my_contacts表中，一是并非所有表里的人都对此感兴趣。二是想要只让特定人访问这些敏感信息。

比如添加了 追踪理想职务和理想薪资的表，还有追踪联络人当前职务和当前薪资的表，还有一个职务空缺信息的表。

这些表和my_contacts 表是有一对一关系的，用自然联接就可以了。P381

比如

要找网站开发员，

年薪 9.5w - 10.5w

经验5年以上。

该怎么设计数据库的查询呢？

==========================================

首先选出 job_listings表中所有的职位空缺：

SELECT title FROM job_listings
GROUP BY title ORDER BY title;

还记得 GROUP BY 吗？不记得回头看看去。

现在使用关键字 IN ，检查他的联络人清单中是否存贮了符合职务需求的人才。

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title IN ('Cook', 'Hairdresser', 'Waiter', 'Web Designer', 'Web Developer');

IN： 如果列表中的  jc.title 属于括号里面的职缺之一，IN 就会返回该行。

但是现在我们还是执行了两段查询，有什么办法只用一段查询呢？

==============================================================

想用一个查询完成两个查询的工作，我们要在查询中添加 子查询。

前一页的第二个查询从 my_contacts 和 job_current 中取出职务符合所需职缺的人的信息，这里的查询叫 外层查询。

它里面有个内层查询。

也就是说，('Cook', 'Hairdresser'...)之类的可以用一个内层查询代替。

SELECT title FROM job_listings;

现在，合二为一：

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

这样搞定~

======================================================================

子查询就是查询中的查询。P388

还可以举例：

SELECT some_column, another_column
FROM table
WHERE column = (SELECT coumn FROM table);

======================================================================

再举个例子，查询邮政编码的。

首先子查询接受来自 zip_code 的标量值。然后再 WHERE 子句中寻找这个值。

SELECT last_name, first_name
FROM my_contacts
WHERE zip_code = (
SELECT zip_code
FROM zip_code
WHERE city = 'Memphis' AND state = 'TN');

相同的查询也可以用联接完成，但是可能子查询简单一些：

SELECT last_name, first_name
FROM my_contacts mc
NATURE JOIN zip_code zc
WHERE zc.city = 'Memphis'
AND zc.state = 'TN';

运用别名会更简单。

比如上面的 zc

=====================================================

子查询难的地方不是结构，而是要知道什么时候需要子查询。

我们可以用MAX(salary)举例：

SELECT MAX(salary) FROM job_current;

继续分解查询，第一个部分：

SELECT mc.first_name, mc.last_name
FROM my_contacts AS mc;

最后找出串起两个查询的方式：

SELECT mc.first_name, mc.last_name, jc.salary
FROM my_contacts AS mc
NATURE  JOIN job_current AS jc;

接下来加上 WHERE子句来连接两段查询：

SELECT mc.first_name, mc.last_name, jc.salary
FROM my_contacts AS mc
NATURE  JOIN job_current AS jc
WHERE jc.salary =
(SELECT MAX()jc.salary) FROM job_current jc;

 

=================================================================

当然也可以用自然内联接和 LIMIT 命令完成。

==================================================================

子查询也可以作为 SELECT 选取的列，比如：

SELECT mc.first_name, mc.last_name,
(SELECT state
FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM my_contacts mc;

这里子查询就是查找 mc 的每一行，取出每一行的州名。

子查询只能返回一个值，每返回一个值，整个查询也返回一行，以下为结果：

=========================================================

目前我们看到的都是非关联子查询。软件先处理内层查询，再用于外层查询的 WHERE。

内层查询不依赖于外层查询的值，这称为非关联子查询。

有多个值的非关联子查询： IN， NOT IN

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

这里 IN 根据子查询返回的整个结果集来评估 jc.title 每一行的值。

这个命令是协助找出符合职缺列表需求的职务。这个查询接受整个 title 集并评估 当前job 表中的每一行，寻找潜在的匹配记录。

使用 NOT IN 可以找出不符合职缺列表的职务。

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title NOT IN (SELECT title FROM job_listings);

非关联子查询使用 IN 或者 NOT IN 来检查子查询返回的值是否为集合的成员之一。
=======================================================================

关联子查询：

关联子查询是指内层查询的解析需要依赖外层查询的结果。

例如：

SELECT mc.first_name, mc.last_name
FROM my_contacts AS mc
WHERE
3 = (
SELECT COUNT(*) FROM contact_interest
WHERE contact_id = mc.contact_id
);

mc的别名建立在外层查询中，内层的子查询也引用了别名mc，于是外层查询要先执行，我们才知道 mc.contacts_id 的值。

==================================================================

搭配 NOT EXISTS 的关联子查询：

SELECT mc.first_name firstname, mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc
WHERE mc.contact_id = jc.contact_id);

NOT EXISTS 负责从 mc 表中找出姓名与电子邮件地址，他们都没列在 job_current 中。

===========================================================================

EXISTS 与 NOT EXISTS

SELECT mc.first_name firstname, mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE  EXISTS
(SELECT * FROM contact_interest ci WHERE mc.contact_id = ci.contact_id);

找出曾出现在 contact_interest 表中的人

============================================================================

这些工具还可以用在 INSERT,UPDATE,DELETE语句中~