MySQ随笔3

七、子查询、派生表和通用表达式
mysql 子查询
MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。另外，MySQL子查询可以嵌套在另一个子查询中。

MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。

以下查询返回在位于美国(USA)的办公室工作的员工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

在这个例子中：子查询返回位于美国的办公室的所有办公室代码。外部查询选择在办公室代码在子查询返回的结果集中的办公室中工作的员工的姓氏和名字。

可以使用比较运算符，例如=，>，<等将子查询返回的单个值与WHERE子句中的表达式进行比较。

例如，以下查询返回最大付款额的客户。
```
SELECT 
    customerNumber, checkNumber, amount
FROM
    payments where amount =(select max(amount) from payments);
```
除等式运算符之外，还可以使用大于(>)，小于(<)等的其他比较运算符。

例如，可以使用子查询找到其付款大于平均付款的客户。首先，使用子查询来计算使用AVG聚合函数的平均付款。然后，在外部查询中，查询大于子查询返回的平均付款的付款。参考以下查询语句的写法 -

SELECT 
    customerNumber, checkNumber, amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);

例如，可以使用带有NOT IN运算符的子查询来查找没有下过任何订单的客户，如下所示：

SELECT 
    customerName
FROM
    customers where customerNumber not in (select customerNumber from orders);

FROM子句中的MySQL子查询

在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。该表称为派生表或物化子查询。

以下子查询将查找订单表中的最大，最小和平均数：

select max(items),min(items),floor(avg(items))
from
(select ordernumber,count(ordernumber) items 
from orderdetails group by ordernumber)
lineitems;
+------------+------------+-------------------+
| max(items) | min(items) | floor(avg(items)) |
+------------+------------+-------------------+
|         18 |          1 | 9                 |
+------------+------------+-------------------+
1 row in set (0.08 sec)

在以下查询中，我们查询选择购买价格高于每个产品线中的产品的平均购买价格的产品

SELECT 
    productname,
    buyprice
FROM
    products p1
WHERE
    buyprice > (select avg(buyprice) from products 
where  productline =p1.productline);

5。MySQL子查询与EXISTS和NOT EXISTS

当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询返回一个布尔值为TRUE或FALSE的值。以下查询说明了与EXISTS运算符一起使用的子查询：

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

以下查询选择总额大于60000的销售订单。

SELECT 
    orderNumber, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

如上面所示，返回3行数据，这意味着有3个销售订单的总额大于60000。

可以使用上面的查询作为相关子查询，通过使用EXISTS运算符来查找至少有一个总额大于60000的销售订单的客户信息：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

执行上面查询，得到以下结果 -

+----------------+-------------------------+
| customerNumber | customerName            |
+----------------+-------------------------+
|            148 | Dragon Souveniers, Ltd. |
|            259 | Toms Spezialitten, Ltd  |
|            298 | Vida Sport, Ltd         |
+----------------+-------------------------+
3 rows in set

SELECT 
    customerNumber, 
    customerName
FROM
    customers
c inner join ( SELECT customerNumber,
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000) o  using(customerNumber);
+----------------+-------------------------+
| customerNumber | customerName            |
+----------------+-------------------------+
|            148 | Dragon Souveniers, Ltd. |
|            298 | Vida Sport, Ltd         |
|            259 | Toms Spezialitten, Ltd  |
+----------------+-------------------------+
3 rows in set (0.07 sec)

mysql 派生表介绍
派生表是从SELECT语句返回的虚拟表。派生表类似于临时表，但是在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。

术语:*派生表*和子查询通常可互换使用。当SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。

以下说明了使用派生表的查询：

SELECT 
    column_list
FROM
    (SELECT 
        column_list
    FROM
        table_1) derived_table_name;
WHERE derived_table_name.c1 > 0;

从orders表和orderdetails表中获得2013年销售收入最高的前5名产品：

select  productCode,round(sum(quantityOrdered * priceEach)) sales from orderdetails
        INNER JOIN
    orders USING (orderNumber) 
where year(shippedDate) =2013 
group by productcode
order by sales desc limit 5;
+-------------+--------+
| productCode | sales  |
+-------------+--------+
| S18_3232    | 103480 |
| S10_1949    | 67985  |
| S12_1108    | 59852  |
| S12_3891    | 57403  |
| S12_1099    | 56462  |
+-------------+--------+
5 rows in set (0.08 sec)

您可以使用此查询的结果作为派生表，并将其与products表相关联，products表的结构如下所示：

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2013
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2013
INNER JOIN
    products USING (productCode);

3.更复杂的mysql 派生表示例：

假设必须将2013年的客户分为3组：铂金，白金和白银。此外，需要了解每个组中的客户数量，具体情况如下：
- 订单总额大于100000的为铂金客户；
- 订单总额为10000至100000的为黄金客户
- 订单总额为小于10000的为银牌客户
- 要构建此查询，首先，您需要使用CASE表达式和GROUP BY子句将每个客户放入相应的分组中，如下所示：

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales, (case when SUM(quantityOrdered * priceEach) < 10000 then "selver" when when SUM(quantityOrdered * priceEach) between 10000 and 100000 then "gold"
    when WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2013
GROUP BY customerNumber 
ORDER BY sales DESC;

以下是查询的输出：

+----------------+--------+---------------+
| customerNumber | sales  | customerGroup |
+----------------+--------+---------------+
|            141 | 189840 | Platinum      |
|            124 | 167783 | Platinum      |
|            148 | 150123 | Platinum      |
|            151 | 117635 | Platinum      |
|            320 | 93565  | Gold          |
|            278 | 89876  | Gold          |
|            161 | 89419  | Gold          |
| ************此处省略了一大波数据 *********|
|            219 | 4466   | Silver        |
|            323 | 2880   | Silver        |
|            381 | 2756   | Silver        |
+----------------+--------+---------------+

然后，可以使用此查询作为派生表，并按如下所示进行分组：

 1 select customerGroup,  COUNT(cg.customerGroup) AS groupCount from(SELECT 
 2     customerNumber,
 3     ROUND(SUM(quantityOrdered * priceEach)) sales, 
 4         (case 
 5         when SUM(quantityOrdered * priceEach) < 10000 then "selver"    when SUM(quantityOrdered * priceEach) between 10000 and 100000 then "gold"
 6     WHEN SUM(quantityOrdered * priceEach) > 100000 THEN "Platinum"
 7     END) customerGroup FROM
 8     orderdetails
 9         INNER JOIN
10     orders USING (orderNumber)
11 WHERE
12     YEAR(shippedDate) = 2013
13 GROUP BY customerNumber 
14 ORDER BY sales DESC) cg 
15 GROUP BY cg.customerGroup;

执行上面查询语句，得到以下结果 -

+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold          |         61 |
| Platinum      |          4 |
| Silver        |          8 |
+---------------+------------+
3 rows in set

八、使用set 操作符
MySQL 交集 intersect 运算符
intersect 运算符简介
INTERSECT运算符是一个集合运算符，它只返回两个查询或更多查询的交集。

以下说明INTERSECT运算符的语法。

(SELECT column_list 
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);

下图说明了INTERSECT运算符。
不幸的是，MySQL不支持INTERSECT操作符。但是我们可以模拟INTERSECT操作符。
模拟示例：
下语句使用DISTINCT运算符和INNER JOIN子句来返回两个表中的相交集合：

SELECT DISTINCT 
   id 
FROM t1
   INNER JOIN t2 USING(id);
SQL
执行上面查询语句，得到以下结果 -

+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set

上面语句是怎么工作的？

INNER JOIN子句从左表和右表返回所有符合条件的行记录。
DISTINCT运算符删除重复行。

Made by Guangqing Xu