SQL-多表查询

来自必知必会的实践

子查询(嵌套查询)

嵌套在其他查询中的查询

-- 查询订购了物品RGAN01的所有顾客，订单细节表中有产品号和订单号，连接到订单表中的订单号和顾客号，连接到顾客表中的信息
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'))

– 内查询的结果作为外查询的条件，由内向外剥，先执行最内层查询，再依次向外。代码的编写顺序也是这样。
– note：只能是单列。作为子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。
– 由于性能的限制，不能嵌套太多。子查询的性能不高
– 用联结同样实现

SELECT cust_name,cust_contact
FROM Customers,OrderItems,Orders
WHERE OrderItems.order_num=Orders.order_num
      AND Orders.cust_id=Customers.cust_id
	  AND prod_id='RGAN01';

作为计算字段使用子查询

-- 查询顾客表中每个顾客的订单总数，对每个顾客增加一个属性：订单数
SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)     -- 该子查询对检索出的每个顾客执行一次，这里一共有5个顾客，故执行了5次
	    FROM Orders
		WHERE Customers.cust_id=Orders.cust_id) AS orders   -- 完全限定列名，同时指明表名和列名，当两个表的列名一样时为避免混淆，使用     
FROM Customers
ORDER BY cust_name;

– note:如果操作多个表，就要使用完全限定列名来避免歧义
– 子查询不是最高效的

多表连接-JOIN，最重要

– 为什么需要联结？
– 由于存储数据的不冗余，需要建立不同的表分别维护各自的信息，这样也易于修改，只需改动一处即可
– 既然分开建表分开维护各自信息，在需要组合它们的时候就需要联结这些表
– 比如产品表和供应商表，一个供应商可以生产多个产品，但每个产品都跟一个供应商的所有信息显然冗余，也不易维护，修改一个供应商的信息需要同时修改它的所有产品信息。所以产品与供应商要分开建表。
– 关系数据库的设计就是依据范式把信息分解成相对独立的多个表，一类数据一个表，通过某些共同的值产生关联，尽可能减少冗余
– 关系数据库的优点：可伸缩性好。即能适应不断增加的工作量而不失败

创建联结
联结是一种机制，用来在一条SELECT语句中关联表。

-- 查询所有供应商和它们生产的产品。
SELECT vend_name,prod_name,prod_price    -- 返回两个不同表中的数据
FROM Vendors,Products                  -- 指定要联结的表，需要完全限定列名
WHERE Vendors.vend_id = Products.vend_id;  -- 和关联它们的方式。    -- WHERE子句是正确匹配的关键，否则就笛卡尔积匹配了

– 笛卡尔积：没有联结条件的表返回的就是笛卡尔积，是一个表的每一行与另一个表的每一行配对，最终行数=两个表的行数之积
– 返回笛卡尔积的联结也称为叉联结（cross join）
– 该例是一个等值联结，也称为内连接（inner join），上面语句是简单格式，等价于下面语句（标准格式）

SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products       -- 指明联结类型是内连接
ON Vendors.vend_id = Products.vend_id;  -- 以ON子句给出条件，与WHERE的条件相同

联结多个表
SQL不限制可以联结的表的数目（DBMS可能有限制）。
创建联结的规则也相同：列出所有表，定义表之间的关系。

-- 查询订单20007中的产品名，供应商，产品单价和数量
SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE OrderItems.prod_id=Products.prod_id        -- 一般先写联结，最后写筛选的条件。因为联结之后是一张大表，最后从大表里筛 
	AND Products.vend_id=Vendors.vend_id         -- n个表使用n-1个联结就可以，不用两两连接
	AND order_num=20007;

性能考虑：联结的表越多，性能下降越厉害，因此不要联结不必要的表

高级联结

使用表别名
– 除了对列和计算字段起别名，还可以对表起别名,更简洁
上面的例子可以改为

SELECT cust_name,cust_contact
FROM Customers AS C,OrderItems AS OI,Orders AS O  -- 给表起别名
WHERE OI.order_num=O.order_num           -- 使用别名
      AND O.cust_id=C.cust_id
	  AND prod_id='RGAN01';
-- 注意：表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端

使用不同类型的联结

• 内连接（用等号是等值联结，一般都是等值联结）
• 自连接（self-join）,
• 自然联结（natural join）,
• 外连接（outer join）
  – 使用联结比子查询快得多
  – 自连接？

自然连接
– 标准联结（内连接）包含全部列，包括重复的列。自然联结也是这些结果，只不过我们在显示的时候不打印显示重复的列。本质上是一样的。
– 在内连接的基础上对列去重，删去重复列。是由我们完成的。我们人工选择唯一的列。人工去重。
– 一般显示一个表的所有列，部分显示其他表的不重的列

SELECT C.*,O.order_num,O.order_date
FROM Customers AS C,OrderItems AS OI,Orders AS O  -- 给表起别名
WHERE OI.order_num=O.order_num           -- 使用别名
      AND O.cust_id=C.cust_id
	  AND prod_id='RGAN01';

实际上，我们只选择我们需要的列显示就可以，这样选择出来的基本没有重复

外连接
如果两个表行数不一样，内连接是舍弃长的那些，只返回两个表都有的结果(都有记录能连起来的)。外连接是按长的返回，某个表缺的记录填空值。
– 左外连接列出左边表中的所有元组（即包含左边表的所有记录，右边不够的填空），右外连接反之。必须指定是LEFT OUTER JOIN 还是RIGHT OUTER JOIN，互换表的顺序就行了
– 例如：统计每个顾客的订单数，包括尚未下订单的顾客，这时顾客表是全的，订单表没有全部顾客，联结时需要按顾客表返回，订单表上没有的顾客填空

-- 查询所有顾客的订单编号,顾客表是全的，按顾客表来
SELECT Customers.cust_id,order_num          -- 完全限定列名来去重
FROM Customers LEFT OUTER JOIN Orders        -- 左外连接，按左边的顾客表的所有行
ON Customers.cust_id=Orders.cust_id;

还有一种：全外连接，FULL OUTER JOIN ,包含两个表中的所有行，不够就填空

带聚集函数的联结
对多个表的总数据聚集

-- 查询所有顾客及其订单数-左外连接
SELECT Customers.cust_id,COUNT(order_num) AS num_ord           -- COUNT(*)的最小值一定是1，因为把空值当做一个记录计数了，这里不能用COUNT(*),计的不是顾客数，是订单数
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;                -- 按每个顾客分组，然后聚集

conclude：常用内连接，外连接，需要时人工去重，其实只要完全限定列名就可以了。

组合查询-UNION

或复合查询，本质是对多个查询到的小结果集求并集并自动去重
– UNION操作符，组合多条SELECT语句为一个结果集
– 在各条SELECT语句之间加入UNION即可

-- 查询1：位于某三个州的所有顾客，
-- 查询2：所有的名为Fun4ALL的顾客，将它们的结果作为一个结果返回
SELECT cust_name,cust_contact,cust_email              -- 查询1
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name,cust_contact,cust_email              -- 查询2
FROM Customers
WHERE cust_name ='Fun4ALL';

在相同表上的多个查询用WHERE完全能做，没必要分开用UNION,等价于,不同表上就不一样了，性能理论上与WHERE一样，实际未知

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

规则：n个select语句使用n-1个UNION
– UNION的各个select语句必须显示的是一样的列，否则就不能显示为一个结果集了，要返回啥列都返回啥列，不同表同样列的数据类型必须兼容

是否对行去重
– UNION默认自动对行去重，（与WHERE效果一样），如果不想去重，使用UNION ALL
– 如果确实需要每个条件的匹配行全部出现，包括重复，则必须使用UNION ALL,不能用WHERE

对组合查询结果排序
– UNION只能使用一条ORDER BY,放在最后一条SELECT之后。
–因为它返回的就是一个总的大结果集，所以用一个ORDER BY就够了，并且永远放在最后一句，因为排序总是最后一步。查询出了数据最后排个序。