联结表
联结表的目的是为了用一条select语句检索多张表的数据。
联结的关键在于主键和外键。主键唯一标识一行数据，外键则是另一张表的主键。（包含外键的表称为从表）
products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联。（products是从表，包含外键；vendors是主表）
mysql> select vend_name,prod_name,prod_price -> from vendors,products -> where vendors.vend_id = products.vend_id -> order by vend_name,prod_name; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Detonator | 13.00 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | +-------------+----------------+------------+ 14 rows in set (0.01 sec)
两张表：vendors,products
不同的列：**vend_name **和 prod_name,prod_price
完全限定列名：vendors.vend_id = products.vend_id 区分两张表的相同字段vend_id
where条件过滤
WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。不使用where条件过滤最终结果会采用笛卡尔积，其数据并不是我们想要的结果。
笛卡尔积
集合的乘积
对于最终的结果应该是3*3 =9 种组合
mysql> select count(*) from vendors,products; +----------+ | count(*) | +----------+ | 84 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from vendors; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from products; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)
结果计算：84=6*14
内连接
目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。
不同写法，一样的效果：
mysql> select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | +-------------+----------------+------------+ 14 rows in set (0.00 sec) mysql> select vend_name,prod_name,prod_price from vendors , products where vendors.vend_id = products.vend_id; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | +-------------+----------------+------------+ 14 rows in set (0.00 sec)
mysql>等值连接：2个表会先进行笛卡尔乘积运算，生成一个新表格，占据在电脑内存里，当表的数据量很大时，很耗内存，这种方法效率比较低，尽量不用。
内连接：2个表根据共同ID进行逐条匹配，不会出现笛卡尔乘积的现象，效率比较高，优先使用这种方法。
多表联结
SQL对一条SELECT语句中可以联结的表的数目没有限制。所以，继续往后加就行。🍁
mysql> 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; +----------------+-------------+------------+----------+ | prod_name | vend_name | prod_price | quantity | +----------------+-------------+------------+----------+ | .5 ton anvil | Anvils R Us | 5.99 | 10 | | 1 ton anvil | Anvils R Us | 9.99 | 3 | | TNT (5 sticks) | ACME | 10.00 | 5 | | Bird seed | ACME | 10.00 | 1 | +----------------+-------------+------------+----------+ 4 rows in set (0.01 sec)
子查询例子：
假如需要列出订购物品TNT2的所有客户：
- 检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'tnt2' - 检索具有前一步骤列出的订单编号的所有客户的ID。
select cust_id from orders where order_num in 。。。 - 检索前一步骤返回的所有客户ID的客户信息。
select cust_name ,cust_contact from customers where cust_id in。。。。。
mysql> 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')); +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.01 sec)
改写：
mysql> select cust_name,cust_contact -> from customers,orders,orderitems -> where customers.cust_id = orders.cust_id -> and orderitems.order_num = orders.order_num -> and prod_id = 'tnt2'; +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.00 sec)
似乎多表联结更加无脑
找出相关表，无脑跟在from后面，无脑联结
where后面跟上联结条件（匹配相关外键与主键）以及过滤条件
高级联结
别名使用
mysql> select cust_name,cust_contact -> from customers as cus ,orders as o,orderitems as oi -> where cus.cust_id = o.cust_id -> and oi.order_num = o.order_num -> and prod_id = 'tnt2'; +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.00 sec) # 当然，as可省略 mysql> select cust_name,cust_contact -> from customers cus ,orders o,orderitems oi -> where cus.cust_id = o.cust_id -> and oi.order_num = o.order_num -> and prod_id = 'tnt2'; +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.00 sec)
📣表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。
自联结
也就是自己和自己做关联
假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
等值连接：
- 联结两张products表，起名为p1 p2
- 组合过滤条件，
p2.vend_id = p1.vend_id联结两张表，p2.prod_id = 'dtntr'过滤数据
mysql> select p1.prod_id ,p1.prod_name -> from products p1,products p2 -> where p2.prod_id = 'dtntr' -> and p2.vend_id = p1.vend_id; +---------+----------------+ | prod_id | prod_name | +---------+----------------+ | DTNTR | Detonator | | FB | Bird seed | | FC | Carrots | | SAFE | Safe | | SLING | Sling | | TNT1 | TNT (1 stick) | | TNT2 | TNT (5 sticks) | +---------+----------------+ 7 rows in set (0.00 sec)
子查询:
- 查询id为DTNTR物品的供应商的vend_id
select vend_id from products where prod_id ='dtntr' - 检索该供应商的所有物品
select prod_id,prod_name from products where vend_id =
mysql> 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) | +---------+----------------+ 7 rows in set (0.01 sec)
自然联结
自然连接在等值连接中去除重复的属性列。（无重复列）
迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
外部联结
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
内部联结展示已关联的元组，外部联结可展示没有关联的元组。
检索所有客户及其订单：
mysql> select cus.cust_id ,o.order_num from customers cus inner join orders o on cus.cust_id = o.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10001 | 20010 | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 6 rows in set (0.00 sec)
检索所有客户，包括那些没有订单的客户：
mysql> select cus.cust_id ,o.order_num from customers cus left join orders o on cus.cust_id = o.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10001 | 20010 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 7 rows in set (0.00 sec) mysql>
外部联结分为左外联结left (outer) join和右外联结right (outer) join。
它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。
mysql> select cus.cust_id ,o.order_num from orders o right outer join customers cus on cus.cust_id = o.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10001 | 20010 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 7 rows in set (0.00 sec)
聚集函数
聚集函数也可以方便地与其他联结一起使用
mysql> select cus.cust_name,cus.cust_id,count(o.order_num) num_ord -> from customers cus left join orders o -> on cus.cust_id = o.cust_id -> group by cus.cust_id; +----------------+---------+---------+ | cust_name | cust_id | num_ord | +----------------+---------+---------+ | Coyote Inc. | 10001 | 3 | | Mouse House | 10002 | 0 | | Wascals | 10003 | 1 | | Yosemite Place | 10004 | 1 | | E Fudd | 10005 | 1 | +----------------+---------+---------+ 5 rows in set (0.00 sec)
组合查询
MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
- 在单个查询中从不同的表返回类似结构的数据；
- 对单个表执行多个查询，按单个查询返回数据。
组合查询和多个WHERE条件
任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。
union
UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。
假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。
不使用union：
mysql> select vend_id,prod_id ,prod_price -> from products -> where prod_price <=5 -> or vend_id in (1001,1002); +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1003 | FC | 2.50 | | 1002 | FU1 | 3.42 | | 1002 | OL1 | 8.99 | | 1003 | SLING | 4.49 | | 1003 | TNT1 | 2.50 | +---------+---------+------------+ 8 rows in set (0.00 sec)
使用union：
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); +---------+---------+------------+ | 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 | +---------+---------+------------+ 8 rows in set (0.00 sec)
在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。
