本文详细介绍了MySQL中的表联结查询，包括子查询、表联结、自联结和外部联结。讲解了如何利用JOIN和WHERE进行表间操作，以及如何避免笛卡尔积问题。此外，还提到了子查询的效率问题和UNION的使用规则。
子查询与表联结
SQL最强大的功能之一就是能在数据检索查询的执行中**联结（join）**表。
##子查询（嵌套SQL）
SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句句都是简单查询，即从单个数据库表中检索数据的单条语句。SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。
###利用子查询进行过滤
订单存储在两个表中。对于包含订单号、客户ID、订单⽇日期的每个订单，orders表存储一行。 各订单的物品存储在相关的orderitems表中。orders表不不存储客户信息。它只存储客户的ID。
实际的客户信息存储在customers表中。
现在，假如需要列列出订购物品TNT2的所有客户，应该怎样检索？
--（1） 查询包含物品TNT2的所有订单编号
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
-- (2) 查询对应订单编号的用户ID
select cust_id from orders where order_num in(20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
-- (3) 查询购买对应物品的用户信息
select cust_id,cust_name from customers where cust_id in(10001,10004);
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10004 | Yosemite Place |
+---------+----------------+
可以把其中的WHERE子句转换为子查询而不是硬编码这些SQL返回的数据：
-- 转换为嵌套SQL，子查询
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' )
);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
-- 为了执行上述SELECT语句句，MySQL实际上必须执行3条SELECT语句。
-- 最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。
-- 外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。
-- 最外层查询确实返回所需的数据。
什么是嵌套查询，子查询：
就是在一个sql当中，它的where条件来源于另外一个sql，
或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。
这里给出的代码有效并获得所需的结果。
但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。
###作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列列表。
select cust_id,cust_name from customers;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- 2.先获取一个用户在orders表中的订单数
select count(*) as orders_num from orders where cust_id = 10001;
+------------+
| orders_num |
+------------+
| 2 |
+------------+
为了对每个客户进行count()计算，应该将count()作为一个子查询：
-- 把count()作为一个子查询
select cust_id,cust_name,
(select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;
+---------+----------------+------------+
| cust_id | cust_name | orders_num |
+---------+----------------+------------+
| 10001 | Coyote Inc. | 2 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 1 |
| 10004 | Yosemite Place | 1 |
| 10005 | E Fudd | 1 |
+---------+----------------+------------+
关系表
SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。
在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。
-- 假如有⼀一个包含产品⽬目录的数据库表，其中每种类别的物品占⼀一⾏行行。
-- 对于每种物品要存储的信息包括产品描述和价格，以及⽣生产该产品的供应商信息。
产品表：
产品，描述，价格，供应商名称，供应商地址,供应商联系⽅方式
A6 ... ... 奥迪 ... ....
520li .. .... 宝马 ... ...
...
-- 现在，假如有由同⼀一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系⽅方法等）呢？
产品，描述，价格，供应商名称，供应商地址,供应商联系⽅方式
A6 ... ... 奥迪 ... ....
520li .. .... 宝马 ... ...
A8 .. ... 奥迪 ... ...
相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。
各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。
-- vendors表包含所有供应商信息
|vend_id | vend_name | vend_address| vend_city ....
-- products表只存储产品信息，它除了了存储供应商ID（vendors表的主键）外不不存储其他供应商信息。
prod_id | vend_id | prod_name | prod_price | prod_desc
vendors表的主键vend_id又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。 这样做的好处如下：
- 供应商信息不重复，从而不浪费时间和空间；
- 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
- 由于数据无重复，显然数据是一致的，这使得处理数据更简单
关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
关系表以及关系数据库设计的基础知识
-
表关系：和关系表都是指：表与表之间的关系
-
外键：
在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键
就是在一个表中的字段，代表着这个数据属于谁了解：
外键实现的方式，有两种：物理外键、逻辑外键- 物理外键：就是在创建表时，就指定这个表中的字段是一个外键，并且强关联某个表中的某个字段：需要在定义字段时，使用sql语句来实现。【不推荐】
- 逻辑外键：就是在表中创建一个普通的字段，没有强关联关系，需要通过程序逻辑来实现。【一般都使用这个】
-
一对一：
在一个表中的数据，对应着另外一张表中的一个数据，只能有一个。
【注意！一对一是双向的！】
例：员工表： id，姓名、性别、年龄、籍贯、联系方式、学历、工龄... 将这一个表拆分成两个表： 员工表： id，姓名、联系方式、工龄、 12 张三 1010 3 13 李四 1020 2 详情表： yid 性别、籍贯、学历... 12 男 山东 本科 13 男 山西 本科上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。 一个员工有着一个对应的详情信息，存储在详情表中， 在详情表中的数据，也只属于某一个员工。 -
一对多\多对一：
在一个表中的一条数据对应着另外一个表中的多条数据；
在一个表中的多条数据，对应着另外一张表中一个数据。商品分类 id 分类名 1 手机 2 电脑 商品 id 所属分类id，商品名 1 1 小米手机 2 1 华为手机 新闻分类 id 分类名 1 体育 2 国际 新闻 id title 分类id 1 国足加油 1 2 特朗普加油 2 3 特朗普被网民称为特没谱 2 -
多对多：
例：
例如一本书，有多个标签，同时每一个标签下又对应多本书 books 图书 id name author 1 <跟川哥学编程> 川哥 2 <跟川哥学数据分析> 川哥 3 <川哥讲法律故事> 川哥 tags 标签 id name 1 编程 2 计算机 3 互联网 4 法律 5 文学 从图书角度看，一本书有多个标签 1 <跟川哥学编程> 川哥 ， 编程、计算机、互联网 2 <跟川哥学数据分析> 川哥 ， 互联网、计算机 3 <川哥讲法律故事> 川哥 ， 法律 换一个角度，从标签这个角度看，一个标签包含多个图书 计算机， <跟川哥学编程>， <跟川哥学数据分析>
表联结：where与join
就是一种查询的机制，用来在一个select语句中关联多个表进行查询，称为联结。
-
使用where进行表联结
例：
-- 需要查询出所有商品以及对应的供应商信息
-- 供应商名称，商品名称，商品价格
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Sling | 4.49 |
| ACME | Carrots | 2.50 |
| ACME | Safe | 50.00 |
| ACME | Bird seed | 10.00 |
| ACME | Detonator | 13.00 |
| ACME | TNT (5 sticks) | 10.00 |
| ACME | TNT (1 stick) | 2.50 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Jet Set | JetPack 2000 | 55.00 |
| Jet Set | JetPack 1000 | 35.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
【在from连接两个表的时候，实际上是把A表里的数据和B表里的数据每一行都进行匹配，where条件作为筛选\过滤】
假如没有where条件：
- 那么第一个表中的每一行数据会与第二个表中的每一行数据进行匹配，不管逻辑是否可以匹配
- 这种结果称为 笛卡尔积，第一个表的行数乘以第二个表中的行数。
所以千万不要忘记where条件！！！！
-
使用join进行表联结
除了使用where进行表的联结查询外，还可以使用另外一种联结方式，join
select vend_name,prod_name,prod_price
from vendors
join products on vendors.vend_id = products.vend_id;
-- 这个sql就是使用了 join 的语法，进行了两个表的联结，在 on 后面定义了联结的条件。
-- 还可以写成
select vend_name,prod_name,prod_price
from vendors
inner join products on vendors.vend_id = products.vend_id;
联结多个表
案例： 查询出订单号为20005的订单中购买的商品及对应的产品供应商信息
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;
-- 改写为 join 的语法
select prod_name,vend_name,prod_price,quantity
from orderitems
inner join products on orderitems.prod_id = products.prod_id
inner join vendors on products.vend_id = vendors.vend_id
where order_num = 20005;
###使用表别名 as
别名除了了用于列名和计算字段外，SQL还允许给表名起别名。
-- 例
select id from student as p1,student as p2,wosuibianxiede as p3 where...
这样做有两个主要理由：
- 缩短SQL语句；
- 允许在单条SELECT语句中多次使用相同的表
应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机
自联结
当前表与自己做联结（join）
假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
-- 使用子查询(嵌套查询)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 使用自联结查询
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';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 用join写
select p1.prod_id,p1.prod_name
from products as p1
join products as p2 on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';
-- 此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不不知道你引⽤用的是products表中的哪个实例。
-- 为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名。
用自联结而不用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使⽤用的子查询语句。
子查询（嵌套查询） 是目前可明确知道的 sql中运行效率最低的一种方式，尽可能不使用嵌套语句。
外部联结：以其中某个表为基准
【left join、right join】
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
例如，可能需要使用联结来完成以下工作：
- 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
- 列出所有产品以及订购数量，包括没有人订购的产品；
- 计算平均销售规模，包括那些至今尚未下订单的客户
在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
-- 内部联结。它检索所有客户及其订单：
select customers.cust_id,orders.order_num from customers
inner 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)
--外部联结语法类似。检索所有客户，包括那些没有订单的客户
select customers.cust_id,orders.order_num from customers
left 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)
聚集函数也可以方便地与其他联结一起使⽤用。
如果要检索所有客户及每个客户所下的订单数，下面使用了了COUNT()函数的代码可完成此工作，包含那些没有任何下订单的客户。
select customers.cust_name,customers.cust_id,count(orders.order_num) as
num_ord from customers
left join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
练习：
-- 列出所有产品以及订购数量，包括没有人订购的产品
select products.prod_id,products.prod_name,sum(orderitems.quantity) as nums
from products
left join orderitems on products.prod_id = orderitems.prod_id
group by products.prod_id;
【注意这里用sum，不是用count！】
##组合查询UNION
MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。
规则：
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
- UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
-- 假如需要价格小于等于5的所有物品的一个列列表，而且还想包括供应商1001和1002⽣产的所有物品。
-- 先查询第一个结果
select vend_id,prod_id,prod_price from products where prod_price <= 5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
-- 再查询第二个结果
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
--使用union将两个sql一并执行
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);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
-- 这条语句由前面的两条SELECT语句组成，语句中用UNION关键字分隔。
-- UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集
-- 同样也可以使用where的多条件来实现
select vend_id,prod_id,prod_price from products where prod_price <= 5 or
vend_id in (1001,1002);
-- 在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。
-- 但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。
- UNION从查询结果集中自动去除了重复的行，这是UNION的默认行为，但是如果需要，可以改变它。如果想返回所有匹配行，可使用UNION ALL而不是UNION
- 对组合查询结果排序
- SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使⽤用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。
- 对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。
扫一扫
2974
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
