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

Myth丶恋晨 2022-05-30 03:53 218阅读 0赞

## 分组 ##

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