SQL学习笔记4 分组 子查询 联结

分组

使用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;