SQL必知必会第五版学习笔记：第11节-第15节

---

第11节 使用子查询

子查询

1. SQL 允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

SELECT cust_id 
FROM Orders 
WHERE order_num IN (SELECT order_num 
                    FROM OrderItems 
                    WHERE prod_id = 'RGAN01');

2. 作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

作为计算字段使用子查询

1.完全限定列名：指定表名和列名。

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;

挑战题

1. 使用子查询，返回购买价格为 10 美元或以上产品的顾客列表。你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用 Order 表检索这些匹配订单的顾客 ID（cust_id）。

SELECT cust_id
FROM Orders
WHERE order_num IN (
        SELECT order_num
        FROM OrderItems
        WHERE item_price >= 10
    );

结果：

2. 你想知道订购 BR01 产品的日期。编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 BR01 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date）。按订购日期对结果进行排序。

SELECT cust_id
FROM Orders
WHERE order_num IN (
        SELECT order_num
        FROM OrderItems
        WHERE prod_id = 'BR01'
    )
ORDER BY order_date;

结果：

3. 现在我们让它更具挑战性。在上一个挑战题，返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email）。提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

SELECT cust_email
FROM Customers
WHERE cust_id IN (
        SELECT cust_id
        FROM Orders
        WHERE order_num IN (
                SELECT
                    order_num
                FROM
                    OrderItems
                WHERE
                    prod_id = 'BR01'
            )
    );

结果：

4. 我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回 total_ordered 以便返回每个顾客的订单总数。将结果按金额从大到小排序。提示：你之前已经使用 SUM()计算订单总数。

SELECT cust_id, (
        SELECT
            SUM(quantity * item_price)
        FROM OrderItems
        WHERE
            Orders.order_num = OrderItems.order_num
    ) AS total_ordered
FROM Orders
ORDER BY total_ordered DESC;

结果：

5. 再来。编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_ name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。

SELECT prod_name, (
        SELECT SUM(quantity)
        FROM OrderItems
        WHERE
            OrderItems.prod_id = Products.prod_id
    ) AS quant_sold
FROM Products

结果：

第12节 联结表

联结

1.关系表要：把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联。

• 关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
• 可伸缩（scale）：能够适应不断增加的工作量而不失败。

创建联结

1. 目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。

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

挑战题

1. 编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行排序。实际上是尝试两次，一次使用简单的等联结语法， 一次使用 INNER JOIN。

SELECT cust_name, order_num
FROM Customers, Orders
WHERE
    Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

SELECT cust_name, order_num
FROM Customers
    INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

结果：

2. . 我们来让上一题变得更有用些。除了返回顾客名称和订单号，添加第三列 OrderTotal，其中包含每个订单的总价。有两种方法可以执行此操作：使用 OrderItems 表的子查询来创建 OrderTotal 列，或者将 OrderItems 表与现有表联结并使用聚合函数。提示：请注意需要使用完全限定列名的地方。

SELECT
    cust_name,
    Orders.order_num,
    sum(quantity * item_price) as ordertotal
FROM
    Customers,
    Orders,
    OrderItems
WHERE
    Customers.cust_id = Orders.cust_id
    AND Orders.order_num = OrderItems.order_num
GROUP BY
    cust_name,
    Orders.order_num
ORDER BY cust_name, order_num;

结果：

3. 我们重新看一下第 11 课的挑战题 2。编写 SQL 语句，检索订购产品 BR01 的日期，这一次使用联结和简单的等联结语法。输出应该与第 11 课的输出相同。

SELECT cust_id, order_date
FROM Orders, OrderItems
WHERE
    prod_id = 'BR01'
    AND Orders.order_num = OrderItems.order_num;

SELECT cust_id, order_date
FROM Orders
    INNER JOIN OrderItems ON prod_id = 'BR01' AND Orders.order_num = OrderItems.order_num;

结果：

4. 很有趣，我们再试一次。重新创建为第 11 课挑战题 3 编写的 SQL 语句，这次使用 ANSI 的 INNER JOIN 语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它，需要两个 INNER JOIN 语句，每个语句的格式类似于本课讲到的 INNER JOIN 示例，而且不要忘记 WHERE 子句可以通过 prod_id 进行过滤。

SELECT cust_email
FROM Customers
    INNER JOIN Orders ON Orders.cust_id = Customers.cust_id
    INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
WHERE prod_id = 'BR01';

结果：

5. 再让事情变得更加有趣些，我们将混合使用联结、聚合函数和分组。准备好了吗？回到第 10 课，当时的挑战是要求查找值等于或大于 1000 的所有订单号。这些结果很有用，但更有用的是订单数量至少达到这个数的顾客名称。因此，编写 SQL 语句，使用联结从 Customers 表返回顾客名称（cust_name），并从 OrderItems 表返回所有订单的总价。

提示：要联结这些表，还需要包括 Orders 表（因为 Customers 表与 OrderItems 表不直接相关，Customers 表与 Orders 表相关，而 Orders 表与 OrderItems 表相关）。不要忘记 GROUP BY 和 HAVING，并按顾客名称对结果进行排序。你可以使用简单的等联结或 ANSI 的 INNER JOIN 语法。或者，如果你很勇敢，请尝试使用两种方式编写。

SELECT
    cust_name,
    SUM(quantity * item_price) AS total_price
FROM
    Orders,
    Customers,
    OrderItems
WHERE
    Customers.cust_id = Orders.cust_id
    AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING
    SUM(item_price * quantity) >= 1000
ORDER BY cust_name;

SELECT
    cust_name,
    SUM(item_price * quantity) AS total_price
FROM Customers
    INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
    INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING
    SUM(item_price * quantity) >= 1000
ORDER BY cust_name;

结果：

第13节 创建高级联结

使用表别名

1.给表名起别名的优点：

• 缩短 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';

2. 表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

使用不同类型的联结

1. 除内联结或等值联结外还有自联结（self-join）、自然联结（natural join）和外联结 （outer join）。
2. 自联结

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';

• 自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。

3. 自然联结

• 自然联结排除多次出现的列，使每一列只返回一次

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';

4. 外联结

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

• 与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。
• 在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表 （RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。
• 全外联结（full outer join）：检索两个表中 的所有行并关联那些可以关联的行。

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

挑战题

1. 使用 INNER JOIN 编写 SQL语句，以检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num）。

SELECT C.cust_name, O.order_num
FROM Customers AS C
LEFT JOIN Orders AS O ON C.cust_id = O.cust_id
UNION
SELECT C.cust_name, O.order_num
FROM Customers AS C
RIGHT JOIN Orders AS O ON C.cust_id = O.cust_id;

结果：

2. 修改刚刚创建的 SQL 语句，仅列出所有顾客，即使他们没有下过订单。

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

结果：

3. 使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按商品名称排序。

SELECT prod_name, order_num
FROM Products
RIGHT OUTER JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
ORDER BY prod_name;

结果：

4. 修改上一题中创建的 SQL 语句，使其返回每一项产品的总订单数 （不是订单号）。

SELECT prod_name, SUM(quantity) AS total
FROM Products
RIGHT OUTER JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
GROUP BY prod_name
ORDER BY prod_name;

结果：

5. 编写 SQL语句，列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量。注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

SELECT Vendors.vend_id, COUNT(prod_id)
FROM Vendors
LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id;

结果：

第14节 组合查询

组合查询

1.使用组合查询的情况：

• 在一个查询中从不同的表返回结构数据；

• 对一个表执行多个查询，按一个查询返回数据。

创建组合查询

1.UNION

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';

2.UNION规则：

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。

• UNION 中的每个查询必须包含相同的列、表达式或聚集函数。

• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型。

3. 如果结合 UNION 使用的 SELECT 语句遇到不同的列名，如一条语句是 SELECT prod_name，而另一条语句是 SELECT productname，查询结果返回的第一个名字

4. 使用 UNION 时，重复的行会被自动取消；如果想返回所有的匹配行，可使用 UNION ALL。

5. 在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。

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' 
ORDER BY cust_name, cust_contact;

挑战题

1. 编写 SQL 语句，将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 ID（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 ID 以 BNBG 开头的产品。按产品 ID 对结果进行排序。

SELECT prod_id,quantity
FROM OrderItems
WHERE quantity=100
UNION
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE 'BNBG%';

结果：

2. 重写刚刚创建的 SQL 语句，仅使用单个 SELECT 语句。

SELECT prod_id,quantity
FROM OrderItems
WHERE quantity=100 OR prod_id LIKE 'BNBG%';

结果：

3. 我知道这有点荒谬，但这节课中的一个注释提到过。编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行排序。

SELECT prod_name
FROM Products
UNION
SELECT cust_name
FROM Customers
ORDER BY prod_name;

结果：

4. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name;

答案：应删去第四行

第15节 插入数据

数据插入

1.插入完整的行

INSERT INTO Customers 
VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

• 第二个例子VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。

• 因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定 按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条 INSERT 语句仍然能正确工作。

• 不能插入同一条记录两次，因为cust_id 是主键，DBMS 不允许插入相同 cust_id 值的新行。

2.插入部分行

• 下述例子中，没有给 cust_contact 和 cust_email 这两列提供值。这表示没必要在 INSERT 语句中包含它们。因此，这里的 INSERT 语句省略了这两列及其对应的值。

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
VALUES(1000000006, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111',

• 省略列：如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列 必须满足以下某个条件。

  • 该列定义为允许 NULL 值（无值或空值）。
  • 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

• 如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值，DBMS 就会产生错误消息，相应的行不能成功插入。

3.插入检索出的数据

• 下述例子从一个名为 CustNew 的表中读出数据并插入到 Customers 表。

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT cust_id, cust_contact, cust_email,cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country 
FROM CustNew;

• 因为DBMS 一点儿也不关心 SELECT 返回的列名，SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

从一个表复制到另一个表

1. 与 INSERT SELECT 将数据添加到一个已经存在的表不同，CREATE SELECT 将数据复制到一个新表。

CREATE TABLE CustCopy AS SELECT * FROM Customers;

挑战题

1. 使用 INSERT 和指定的列，将你自己添加到 Customers 表中。明确列出要添加哪几列，且仅需列出你需要的列。

INSERT INTO Customers(cust_id,
 cust_name,
cust_address,
 cust_city,
cust_state,
 cust_zip,
 cust_country,
 cust_contact,
 cust_email)
VALUES(1000000010,
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA',
 NULL,
 NULL);

2.备份 Orders 表和 OrderItems 表。

select * into orderscopy from orders
select * into orderitemscopy from orderitems