《SQL必知必会》—— 检索2

子查询

-- 列出订购RGAN01物品的所有顾客
SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01';      -- 1. 检索包含RGAN01物品的所有订单编号
SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);   -- 2. 检索订单编号的所有顾客ID
SELECT cust_name FROM customers WHERE cust_id IN (1000000004, 1000000005);      -- 3. 检索所有顾客ID的顾客信息
-- 等价于
SELECT cust_name FROM customers WHERE cust_id IN (
        SELECT cust_id FROM orders WHERE order_num IN (
                SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01'
        )
);
-- 等价于(联结表)
SELECT cust_name 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num
    AND prod_id = 'RGAN01';

-- 显示customers表中每个顾客的订单总数
SELECT cust_name, 
        cust_state, 
        (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers;

联结表

SELECT vend_name, prod_name, prod_price 
FROM vendors, products WHERE vendors.vend_id = products.vend_id;        -- 等值联结

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 products, vendors, orderitems
WHERE products.vend_id = vendors.vend_id AND products.prod_id = orderitems.prod_id
    AND order_num = 20007;  

-- 选择与'Jim Jones'同一cust_name的所有cust_contact
SELECT cust_id, cust_name, cust_contact
FROM customers
WHERE cust_name = (
    SELECT cust_name FROM customers WHERE cust_contact = 'Jim Jones'
);
-- 等价于(自联结)
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers AS c1, customers AS c2
WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

-- 检索有订单的所有顾客(内联结)
SELECT customers.cust_id, order_num
FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;
-- 检索包含没有订单的所有顾客(左外联结)
SELECT customers.cust_id, order_num
FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
-- 外联结：包含了那些在相关表中没有关联行的行
-- LEFT OUTER JOIN 指定包含所有行的表在左边
-- RIGHT OUTER JOIN 指定包含所有行的表在右边

-- 检索所有顾客及每个顾客所下的订单(带聚集函数的联结)
SELECT customers.cust_id, cust_name, COUNT(order_num) AS num_ord
FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id; 

组合查询

SELECT cust_name, cust_contact, cust_email 
FROM customers WHERE cust_state IN ('IL', 'IN', 'MI')
UNION                   -- UNION ALL包含重复行
SELECT cust_name, cust_contact, cust_email
FROM customers WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;       -- 对返回的所有结果排序
-- 等价于
SELECT cust_name, cust_contact, cust_email 
FROM customers WHERE cust_state IN ('IL', 'IN', 'MI') OR cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;