分组数据

1
2
3
SELECT vend_id COUNT(*) AS num_prods FROM products GROUP BY vend_id;

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

Tips

  • GROUP BY 子句可以包含任意数目的列
  • 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总
  • GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式。不能使用别名。
  • 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，他们将分为一组

子查询

1
2
3
4
5
6
7
SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

-- 利用子查询进行过滤
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

-- 作为计算字段使用子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

Tips

  • 在 WHERE 子句中使用子查询，应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列

联结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 内部联结
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

-- 表别名
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 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 customers.cust_id, orders.order_num FROM customers LEFT 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 LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

Tips

  • 使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指右边的表包括所有行，包括没有关联行的行）

组合查询

1
2
3
4
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001, 1002);

Tips

  • UNION 必须由两条或以上的 SELECT 语句组成
  • UNION 中每个查询必须包含相同的列、表达式或聚集函数，列数据类型必须兼容
  • 默认去除重复行，使用 UNION ALL 可以返回所有行
  • ORDER BY 写在最后一条 SELECT 语句后

参考书籍: 《MySQL必知必会》