mysql内部联结_MySQL联结表

1. 联结表

创建联结，有where子句

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;

创建联结，没有where子句，返回的结果并不匹配

select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;

内部联结

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

联结多个表(非常耗费资源)

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;

子查询与联结查询

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. 高级联结

使用表别名

select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title from vendors order by vend_name;

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';

使用不同类型的联结

子查询自联结

select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');

自联结

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';

自然联结

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';

外部联结

select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

select customers.cust_id, orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id;

使用带聚集函数的联结

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. 使用联结和联结条件

注意所使用的联结类型，一般我们使用内部联结，是使用外部联结也是有效的；

保证使用正确的联结条件，否则将返回不正确的数据；

应该总是提供联结条件，否则会得出笛卡儿积；

在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单；