文章目录
第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