《SQL必知必会》学习笔记之13-15章

多思、多想、多问

第13章 创建高级联结

13.1使用表别名

SELECT RTRIM(vend_name) + '('+ RTRIM(vend_country) +''
AS vend_title
FROM Vendors
ORDER BY vend_name

为什么要用表别名呢？
1、可以缩短SQL语句；
2、允许一条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';

查找购买了名为RGAN01商品的顾客ID
注意：Oracle中没有AS，如果需要使用只需要简单是 Customers C，而不是Customers AS C。

13.2使用不同类型的联结

自联结（self-join）、自然联结(natural join )和外联结（outer join）。

13.2.1.自联结

e.g:假如要给Jim Jones同一公司的所有顾客发送一封邮件。
这个查询首先要找出Jim Jones工作的公司，然后找过在该公司工作的顾客。
解决方法1：

SELECT cust_id,cust_name,cust_contact
FROM Customers 
WHERE cust_name=(SELECT cust_name
FROM Customers 
WHERE cust_contact = 'JiM Jones'
);

这是第一种方法，使用了子查询。内部的SELECT语句做了一个简单的检索然后返回Jim Jones工作公司的cust_name。该名字用作外部查询的WHERE子句中，以检索出为该公司工作的所有雇员。
下面是使用联结的相同查询

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'

这个查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次。虽然合法但是DBMS不知道你引用的是哪个Customers表。
解决此问题需要使用表别名。
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多，应该试一下两种方法，以确定哪种的性能更好。

13.2.2自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使得每一列只返回一次。
但是怎么完成这个功能呢？系统不完成这项功能，由自己完成。e.g:

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

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

13.2.3外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。
例如：
● 对每个顾客下的订单进行计数，甚至包括那些尚未下订单的顾客；
● 列出所有产品以及订购数量，包括没有人订购的产品
● 计算平均销售规模，包括那些至今未下订单的顾客。
下面SELECT语句给出了一个简单的内联结，它检索所有顾客及其订单：

SELECT Customers.cust_id,Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id=Orders.cust_id

外联结语法类似：

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

分析：与内联结不同的是，外联结还包括没有关联的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT或FULL（部分DBMS不支持全外联结）关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT JOIN指出的是OUTER JOIN左边的表）。

13.3使用带聚集函数的联结

检索所有顾客及每个顾客所下的订单数

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;

输出：没下订单的顾客将不会被输出
这条SELECT预计使用INNER JOIN将Customers和Orders表相互关联。GROUP BY子句将顾客分组数据，因此，函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回。
聚集函数也可以方便地与其他联结一起使用。

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT  OUTER JOIN Orders
ON Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;

输出：没下订单的顾客将会被输出

13.4使用联结和联结条件

联结的使用要点：
● 注意使用的联结类型，一般我们使用内联结，但使用外联结也有效。
● 关于确切的联结语法，应该具体查看相应的DBMS的语法。
● 保证使用正确的联结条件，否则会返回不正确的数据。
● 应该总是提供联结条件，否则会得出笛卡尔积。
● 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结，这会使故障排除更为简单。

14.组合查询
14.1组合查询
多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许执行多个查询（多个SELECT语句），并将结果作为一个查询结果集返回。这些组合通常称为并(union)或复合查询。
主要有两种情况需要组合查询：
在一个查询中从不同表中返回结构数据
对一个表执行多个查询，按一个查询返回数据。
14.2创建组合查询
14.2.1使用UNION
使用UNION很简单，只需要给每条SELECT语句，在各条语句之间放上关键字UNION。
例如，需要llinois、indiana和Michigan等美国几个州的所有顾客的报表，还想包括不管位于那个州的所有的FUN4ALL。

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

输出:所有符合这三个州的数据

SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name=='FUN4ALL';

输出：所有cust_name是FUN4ALL的数据
组合这两条语句，可以如下进行

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

输出:所有符合这三个州的数据并上所有cust_name是FUN4ALL的数据。
作为对比，还可以使用多条WHERE子句而不是UNION的相同查询：

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

看上去好像UNION更为复杂。但对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用UNION可能会使处理更简单。

第15章 插入数据

15.1 数据插入

这本书前14章都在将SELECT，说明SELECT是最常用的SQL语句。SQL还有一些其他的常用语句需要学习。这章主要介绍的是INSERT。
顾名思义，INSERT用来将行插入（或添加）到数据库表。插入有几种方式：

• 插入完整的行
• 插入行的一部分
• 插入某些查询的结果

15.1.1插入完整的行

e.g:

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

这段代码相当于将一个新的顾客插入到Customers表中。必须按照在表定义的先后次序填充，空值记做NULL。
但是，上述写法不太安全，因为上述SQL代码高度依赖于表中列的定义次序，而且表结构变动后不能保证各列保持完全相同的次序。
为了更安全的使用INSERT，可以用如下更加繁琐的办法来写：

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

可以看到，因为提供列名，即使表结构改变也不会影响插入语句。

15.1.2插入部分行

省略一些没有值的列

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',
    'USA',

15.1.3插入检索出的数据

假如想把另一表中的顾客列合并到Customers表中。不需要每次读取一行再将它用INSERT插入，可以如下进行：

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

其实，SELECT的列名和INSERT的列名不一定需要匹配，是根据列的位置进行的匹配。即第一列相互之间对应，第二列相互之间对应……
INSERT一般只插入一行，要插入多行要么使用多个INSERT语句要么使用INSERT SELECT，它会将SELECT选中的所有行都一并插入。

15.2 从一个表复制到另一个表

INSERT SELECT将数据添加到一个已经存在的表，SELECT INTO将数据复制到一个新表。
下面例子说明
SELECT INTO

SELECT *
INTO CustCopy
FROM Customers;

使用SELECT INTO的一些注意事项：

• 任何SELECT选项和字句都可以使用，包括WHERE和GROUP BY；
• 可利用联结表从多个表插入数据；
• 不管从多少个表中检索数据，数据都只能插入到一个表中。