MySql-增删改查之查（三）

子查询（subquery）

子查询，即嵌套在其他查询中的查询。

#首先在orderitems表中查询商品（prod_id）为TNT2的订单号(order_num)，再从orders表
#中找出相应order_num的客户号（cust_id），最后从customers表中找出相应cust_id的
#cust_name...

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id     # 子查询一般与IN操作符结合使用，但也可以用
                                     # 于等于=和不等于<>等；
                  FROM orders
                  WHERE order_num IN(SELECT order_num
                                     FROM orderitems
                                     WHERE prod_id = 'TNT2'));

作为计算字段使用子查询

#统计一个客户的订单数量
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001; 

#统计所有客户的订单数量
SELECT cust_name, 
       (SELECT COUNT(*)
       FROM orders
       WHERE orders.cust_id = customers.cust_id) AS orders   # 完全限定列名
FROM customers
ORDER BY cust_name;

联结表（WHERE、JOIN…ON)

使用外键定义关系的多个表，在SELECT中可以使用联结返回一组多个表的数据。

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE venord.vend_id = products.vend_id  #用WHERE子句进行联结
ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON venord.vend_id = products.vend_id  #用JOIN..ON进行联结
ORDER BY vend_name, prod_name;

联结类型有自联结、内部联结、外部联结（左联结、右联结）和自然联结（排除多次出现，使每个列只返回一次）。

# 自联结
# 找出产品id为DTNTR的供应商的所有产品
SELECT prod_id, pros_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE pord_id = 'DTNTR');
# 右联结
# 检索所有客户，包括那些没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM custmors LEFTER OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

组合查询（UNION)

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION                                # 自动去除重复的行，
SELECT vend_id, prod_id, prod_price  # 如要返回所有行使用'UNION ALL'
FROM products
WHERE vend_id IN (1001,1002);  

CASE WHEN