《sql必知必会》------day4

第 10 课 分组数据

SELECT COUNT(*) AS num_prods FROM Products WHERE vend_id = 'DLL01';

    这是返回供应商 DLL01 提供的产品数目，若要统计每个供应商提供的产品数目，这需要分组

SELECT vend_id, COUNT(*)  AS  num_prods  FROM Products  
GROUP BY vend_id;

过滤分组

WHERE 过滤指定的是行而不是分组， HAVING 过滤分组

SELECT cust_id, COUNT(*) AS orders FROM Orders
GROUP BY cust_id HAVING COUNT(*) >= 2;
#两个以上订单

WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤

SELECT vend_id, COUNT(*) AS num_prods FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

第 11 课 使用子查询

嵌套查询

SELECT cust_id FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems
                                        WHERE prod_id = 'RGAN01');
检索包含物品 RGAN01 的所有订单的订单编号的所有顾客的ID

字段查询

SELECT cust_name,cust_state,
              (SELECT COUNT(*)
                  FROM Orders
                  WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
从 Customers 表中检索顾客列表
对于检索出的每个顾客，统计其在 Orders 表中的订单数目

第 12 课 联结表

     将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。联结表可以认为是多表联合查询。
创建联结：

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;  #联结条件（即笛卡儿积的筛选条件）

      内联结（inner join）：基于两个表之间的相等测试，也称等值联结（equijoin）。INNER JOIN…ON…

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

    联结多个表:SQL 不限制一条 SELECT 语句中可以联结的表的数目,DBMS 在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源

SELECT cust_name, cust_contact
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, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

第 13 课 创建高级联结

使用表别名

       SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。与列别名不一样，表别名不返回到客户端。为了缩短 SQL 语句及在一条 SELECT 语句中多次使用相同的表。

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 = 'RGAN01';
#Oracle 中没有 AS。要在 Oracle 中使用别名，简单地指定列名即可（因此，应该是 Customers C)

自联结

       自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。许多 DBMS 处理联结远比处理子查询快得多。
       要给与 Jim Jones 同一公司的所有顾客发送一封信件。首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客。

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 *），而对其他表的列使用明确的子集来完成

SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
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 = 'RGAN01';

外联结

       有时候需要包含没有关联行的那些行，如对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
结果：
  cust_id     order_num
----------    ---------
1000000001     	20005
1000000001     	20009
1000000002 		NULL
1000000003	  	20006
1000000004     	20007
1000000005      20008

       在使用 OUTER   JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行。
       SQLite 支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。
       总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。调整 FROM 或 WHERE子句中表的顺序，左外联结可以转换为右外联结。
       全外联结（full outer join）：检索两个表中的所有行并关联那些可以关联的行。Access、 MariaDB、 MySQL、 Open Office Base 和 SQLite 不支持 FULL   OUTER   JOIN 语法。

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

第 14 课 UNION 操作

       组合查询通常称为并（union）或复合查询（compound query），通常用于以下两种情况：1在一个查询中从不同的表返回结构数据.2对一个表执行多个查询，按一个查询返回数据

SELECT cust_name, cust_contact, cust_email
FROM Customers WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers WHERE cust_name = 'Fun4All';

       UNION 中的每个查询必须包含相同的列、表达式或聚集函数。UNION 从查询结果集中自动去除了重复的行，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION（这时不能用WHERE替代）。
       在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。虽然ORDERBY 子句似乎只是最后一条 SELECT 语句的组成部分，但实际上 DBMS 将用它来排序所有 SELECT 语句返回的所有结果。

《sql必知必会》------day6
《sql必知必会》------day5
《sql必知必会》------day4
《sql必知必会》------day3
《sql必知必会》------day2
《sql必知必会》------day1