表关联查询

表关联查询

1.表别名

  当表的名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，以替代表原来的名称。

• 在为表取别名时，要保证不能与数据库中的其他表的名称冲突。

• 对单表做简单的别名查询通常是无意义的。一般是对一个表要当作多个表来操作，或者是对多个表进行操作时，才设置表别名。

• 当为表取别名后，列名前面最好都加上表的别名，做以区分。

SELECT p.*,v.vend_name 
FROM products p ,vendors v  
WHERE p.vend_id=v.vend_id;
1
2
3

  使用表别名查询，可以使 SQL 变得简洁而更易书写和阅读，尤其在 SQL 比较复杂的情况下。除了使用别名来简化 SQL 外，有些时候例如一个表做自身关联时，必须要使用别名来当作两个表进行关联操作。

  
  

2.内连接

两表内联

案例

  假设现在我想查询给我们供货的供应商的名称，以及商品名称和商品价格。此时我们发现，要查询的字段不在同一张表里。供应商名称在vendors表里，而商品名称和商品价格在products表里，这时可以使用内联查询，将两张表进行关联之后进行查询。

# 这里没有使用表别名
SELECT vend_name, prod_name, prod_price
FROM products,
     vendors
WHERE vendors.vend_id = products.vend_id;

# 这种方式叫等值连接。当然也可以用表别名，来达到同样效果
SELECT v.vend_name, p.prod_name, p.prod_price
FROM products p,
     vendors v
WHERE p.vend_id = v.vend_id;
# 这种连接方式也被称为内部连接，可以用以下语法来明确指定连接类型 -- inner可以省略
SELECT vend_name, prod_name, prod_price
FROM products  INNER JOIN vendors ON vendors.vend_id = products.vend_id;

# 这两种方式都叫内连接，一种是隐式内联，一种是显示内联 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

  这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

  

笛卡尔积

  在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。我们必须自己通过SQL做这件事情。

  在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

  笛卡尔积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT v.vend_name, p.prod_name, p.prod_price
FROM products p,  vendors v ORDER BY vend_name,prod_name;
1
2

  从上面的输出中可以看到，相应的笛卡尔积不是我们所想要的。这里返回的数据用每个供应商匹配了每个产品，它包括了供应商不正确的产品。实际上有的供应商根本就没有产品。所以两表联查时，一定要注意where条件。

  
  

多表内联(and)

  SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。

案例

  假设现在要查询订单编号为20005的产品名称，产品价格、产品数量、供应商名称。该如何查询呢？

# 首先，先确认要查询的字段来自哪几张表？经分析可以得出，可以从orderitems、products、venders中获取数据
# 其次，再确定三张表的关联关系
# 然后，确定过滤条件
-- 隐式连接
SELECT prod_name, vend_name, prod_price, quantity
FROM vendors,
     products,
     orderitems
WHERE vendors.vend_id = products.vend_id
  AND products.prod_id = orderitems.prod_id
  AND order_num = 20005;
-- 显示连接
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems o
         JOIN products p ON o.prod_id = p.prod_id
         JOIN vendors v ON p.vend_id = v.vend_id
WHERE o.order_num = 20005;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

  
  

3.外连接

左外连接（left outer join，outer可省略）

左表全部出现在结果集中，若右表无对应记录，则相应字段为NULL

举例说明：

客户表：

订单表：

select first_name, last_name, order_date, order_amount
from customers c
left join orders o 
on c.customer_id = o.customer_id
1
2
3
4

  结果：右表（order）只选取customer_id在左表出现过的结果（符合条件的order_date, order_amount，所以最后两行中date和amount都有NULL值）

  

  

右外连接（right outer join，outer可省略）

右表全部出现在结果集中，若左表无对应记录，则相应字段为NULL

举例说明：

select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
1
2
3
4

  结果：左表（customer）只选取customer_id在右表出现过的结果（符合条件的first_name和last_name，所以最后两行中first_name和last_name都有NULL值）

  

  

4.自连接

  假设你发现商品id为60001的商品存在质量缺陷，现在需要查找，60001供应商所提供的所有商品的名称和商品id。请问如何编写SQL语句？

# 1.使用现有的知识储备，子查询
SELECT vend_id FROM products WHERE prod_id=60001;
SELECT prod_id,prod_name FROM products WHERE vend_id=1001;

SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id=60001);

# 2.采用自连接
SELECT p1.prod_id, p1.prod_name, p1.vend_id
FROM products p1,
     products p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 60001;
1
2
3
4
5
6
7
8
9
10
11
12