MySQL联结表

1. 联结表

创建联结，有where子句
SQL> select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;

创建联结，没有where子句，返回的结果并不匹配
SQL> select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;

内部联结
SQL> select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

联结多个表(非常耗费资源)
SQL> 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;

子查询与联结查询
SQL> 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'));

1. 外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表直接的关系。
2. 笛卡儿积：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
3. 内部联结：inner join ... on ...
    基于两个表之间的相等测试，叫做等值联结。也叫做内部联结。

2. 高级联结

使用表别名
SQL> select concat(rtrim(vend_name), '（', rtrim(vend_country), '）') as vend_title from vendors order by vend_name;
SQL> select cust_name, cust_contact from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'TNT2';

使用不同类型的联结

子查询自联结
SQL> select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');
自联结
SQL> 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';

自然联结
SQL> select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'FB';

外部联结
SQL> select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;
SQL> select customers.cust_id, orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id;

使用带聚集函数的联结
SQL> select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord from customers inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

1. 使用表别名 orders as o
2. 自然联结：排除多次出现，使每个列只返回一次。
3. 外部联结 left/right outer join ... on ...
4. 使用联结和联结条件
    注意所使用的联结类型，一般我们使用内部联结，是使用外部联结也是有效的；
    保证使用正确的联结条件，否则将返回不正确的数据；
    应该总是提供联结条件，否则会得出笛卡儿积；
    在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单；