SQL学习日记（7）

目录

创建高级联结

使用表别名

使用不同类型的联结

1.自联结

2.自然联结

3.外联结

全外联结

使用联结和联结条件

组合查询

使用union操作符创建组合查询

---

创建高级联结

使用表别名

好处：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’;

分析：在此列中，使用c作为customers的别名等。表别名不仅用于where语句，还可以用于select列表、order by子句以及其他语句部分。

注意：1.oracle中不支持as关键字，要在oracle中使用别名，简单地指定列名即可，如：customers c

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

使用不同类型的联结

前文曾提过内联结或等值联结，其他联结：自联结、自然联结、外联结

1.自联结

要给Jim Jones同一公司的所有顾客发送一封邮件。首先，找出Jim Jones工作的公司，然后找出在该公司工作的顾客。

方法1（使用子查询）：

Select cust_id,cust_name,cust_contact

From customers

Where cus_name=(select cust_name

                From customers

                Where cust_contact=’jim jones’);

方法二（使用自查询）：

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_cantact=’Jim Jones’;

此查询中的两个表实际上是相同的表，因此在from子句中customers出现了两次。虽然这样是完全合法的，但是对customers表的引用具有歧义性。需要使用表别名指定DBMS想要的哪一列。where首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据。

2.自然联结

标准的联结返回全部数据，相同的列出现多次。自然联结排除多次出现，使每一列只返回一次。

Select c.*,o.order_num,o.ordeer_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’;

自然联结要求你选择那些需要唯一的列，一般通过对一个表使用通配符*，而对其他表的列使用明确的子集来完成。

3.外联结

许多联结将一个表中的行与另一个表中的行相关联你，但有时候需要包含没有关联行的那些行，如对每个顾客下的订单进行计数，包含那些至今尚未下订单的顾客。这种联结称为外联结。

使用内联结检索所有有订单的顾客及其订单：

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 outer join orders

On customers.cust_id=orders.cust_id

与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用outer join语法时，必须使用right、left关键字指定包括其所有行的表，此列即指customers表。

全外联结

检索两个表中的所有行并关联那些可以关联的行。但是access\mysql\sqlite\open office base\mariadb不支持这种语法。

使用带聚集函数的联结

前文所述的聚集函数用来汇总数据，都是从一个表中汇总数据，但是这些函数还可以与联结一起使用

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

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

使用联结和联结条件

1. 一般使用内联结，外联结也有效
2. 关于确切的联结语法，应查看相应的文档
3. 保证使用正确的联结条件，否则返回不正确的数据
4. 应该总是提供联结条件，否则会得出笛卡尔积
5. 在一个联结中可以包含多个表，甚至对每个联结采用不同的联结类型

组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条select语句，但是SQL也允许执行多个select语句，并将结果作为一个查询结果集返回，即复合查询

使用情况：1.在一个查询中从不同的表返回结构数据2.对一个表执行多个查询，按一个查询返回数据

组合相同表的两个查询所完成的工作与具有多个where子句条件的一个查询所完成的工作相同。

使用union操作符创建组合查询

假如需要illinois、indiana、michigan美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的fun4all：

方法1：

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：

Select cust_name,cust_contact,cust_email

From customers

Where cust_state in (‘il’,’in’,’mi’)

      Or cust_name=’fun4all’;

注意：对于较复杂的过滤条件，或者从多个表中检索数据的情形，使用union可能会使处理更简单

union规则

1. union必须由两条或以上的select语句组成，语句之间用union分隔
2. union中的每个查询必须包含相同的列、表达式、聚集函数
3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型
4. union从查询结果集中自动去除重复的行。如若想返回所有的匹配行，可以使用union all而不是union
5. 对组合查询结果排序，只能使用一条order by子句，且必须位于最后一条select语句之后