MySQL子查询，联结表

MySQL子查询，联结表

子查询：

1. select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
2. 对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询：select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;

联结表：

1. select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name; 如果没有where将输出笛卡尔积（cartesian product），即没有联结条件的表关系返回的结果为笛卡儿积，简单的说就是每个供应商匹配每个产品，它包括了供应商不正确的产品。
2. 内部联结：select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
3. 联结多个表：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;
4. 使用表别名：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';
5. 自联结：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';
6. 外部联结：select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id; （在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字 指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表(customers表)中选择所有行。）
7. 要检索所有客户及每个客户所下的订单数：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;