分组
使用GROUP BY
对数据进行分组
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
GROUB BY
子句可以包含任意数目的列。因此可以对分组进行嵌套GROUB BY
子句列出的每一列都必须是检索列或有效的表达式,不能使用别名- 大多数SQL不允许
GROUB BY
带有长度可变的数据类型(如文本或备注型字段) - 除了聚集计算语句外,SELECT语句的每一列都必须在
GROUB BY
子句给出 - 如果分组列中有NULL值,则NULL将作为一个分组返回。如果有多行NULL值,它们将分为一组
GROUP BY
子句必须在WHERE
子句之后,ORDER BY
之前
过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >2;
HAVING
和WHERE
类似,唯一的区别是WHERE
过滤行,HAVING
过滤分组WHERE
在分组前过滤,HAVING
在分组后进行过滤,WHERE
排除的行不包括在分组中- 在分组后,应使用
ORDER BY
进行排序
子查询
在WHERE
子句中使用子查询
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = '123');
- 子查询内部没有分号,可多层嵌套
- 作为子查询的
SELECT
语句只能查询单个列,返回多个列将引起错误
作为计算字段使用子查询
SELECT cust_name,cust_state,(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers;
- 用一个句号分隔表名和列名,避免混淆列名
联结表
在数据查询时联结(join)表是利用SQL的SELECT能执行的最重要的操作
联结是一种机制,用来在一条SELECT语句中关联表,使用特殊的语法,可以联结多个表返回一组输出。
使用WHERE
子句联结(内联结)
SELECT vend_name, prod_name, prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id;
- 若不联结两表,则返回的结果为笛卡尔积,即第一个表中的每一行与第二个表中每一行两两配对
使用INNER JOIN
子句联结(内联结)
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
- 此种联结方式与
WHERE
联结相同 - SQL不限制一条
SELECT
语句中可以联结表的数目,可以使用AND
关键词联结多个表 - 联结表越多,性能消耗越大
- 可用联结的方式代替子查询
表别名
SELECT vend_name, prod_name, prod_price
FROM vendors AS V,products AS P
WHERE V.vend_id = P.vend_id;
- ORACLE不支持
AS
关键字,直接指定即可,如Customers C
- 表别名只在查询时使用,不返回客户端
自联结
在一条SELECT
语句中不止一次引用相同的表
SELECT cust_id,cust_name,cust_contact
FROM customers AS c1,customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'jim jones';
外联结
用于包含那些没有关联行的行,例如对每个顾客的订单进行计数,包括那些从未下过订单的顾客
SELECT customers.id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
- 外联结使用
OUTER JOIN
关键字而不是WHERE
- 使用外联结时,需使用
LEFT
或RIGHT
,用于指定包括所有行的表(即使没有关联行的行),LEFT
代表OUTER JOIN
左边的表,而RIGHT
代表OUTER JOIN
右边的表 - 有些数据库软件还支持
FULL OUTER JOIN
,名为全外联,包含两个表的不关联行
带聚集函数的联结
SELECT customer_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;