sql数据库开发-10：多表查询2

 

项目案例：

    列出客户姓名以及开通的远程登录业务的数量。

 

from 后面跟子查询

   语法：

select t1.colname1,t1.colname2 from tabname1 t1 join
     (select colname1,AVG(colname2) colname2
          from tabname group by colname1) t2
     on t1.colname1 = t2.colname1
    and  t1.colname2 > t2.colname2;

 

项目案例：

  列出客户姓名以及开通的远程登录业务的数量，用from 后面跟子查询的方式实现。

  列出客户姓名以及他的推荐人。

 

 

 

自连接

    不同表有描述同一属性的列，用 ON tabname1.colname1 = tabname2.colname2 表达，那么同一张表的不同列的取值有相同含义，记录之间的关系又该如何表达 ？

    同一张表的行(记录) 之间的匹配关系可以用同一张表的列之间的条件表达式描述。

    通过给表起别名同一张表的列之间的关系转换成不同表的列之间的条件表达式。

 

 

项目案例:

    哪些客户是推荐人

 

 

外连接
 
    语法:

     select tabname1.colname1,tabname2.colname2 from tabname1
      [left|right|full] join tabname2  on tabname1.colname1 = tabname2.colname2;   

 

 

外连接原理(一)

  t1表和t2表做外连接，连接条件为 ：
  from t1 left outer join t2 on t1.c1 = t2.c2, t1表必须做驱动表，t2表做匹配表，
   记录的匹配过程如下:

   1.从t1表中读取 第一条记录r1,若它的c1值为1

   2.根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录开始，若该记录(r1) 的c2列的值等于1，我们就说这两条记录能够匹配上，那么t1的r1和t2的r1组合起来，作为结果集里的一条记录，否则检测t2表中的下一列记录。

   3. 按照方法2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中，若扫描完后，t1   的r1记录在t2表中找不到任何匹配的记录，t2表中模拟一条null记录与t1表中的r1组合起来，放入结果集中。

   4.从t1表中读取第二条记录，依次重复步骤2和3，产生最终的结果集

 

外连接原理(二)
 
     t1表和t2表做外连接，连接条件为
    from t1 left outer join t2 on t1.c1 = t2.c2,t1表必须做驱动表，t2表做匹配表:

  1.外连接的结果集 = 内连接的结果集+t1表中匹配不上的记录和一条null记录(按t2表的结构)的组合

  2.外连接的核心可以将t1中匹配不上的记录(按on条件在t2中找不到对应的匹配记录)找回来,即t1中的记录一个都不少的出现在结果集中

  3.外连接结果集的记录数不一定是驱动表的记录数

 

外连接原理 （三）

  t1表和t2表做外连接，连接条件为
  from t1 right outer join t2 on t1.c1 = t2.c2, t2表必须做驱动表，t1表做匹配表：

 1.外连接的结果集 = 内连接的结果集 + t2表中匹配不上的记录和一条null记录(按t1表的结构)的组合。

 2.外连接和核心可以将t2中匹配不上的记录(按on条件在t1中找不到对应的匹配记录)找回来,即t2中的记录一个都不少的出现在结果集中。

 3.外连接结果集的记录数不一定是驱动表的记录数。

 

外连接原理(四)

   t1表和t2表做外连接，连接条件为
 
   from t1 full outer join t2 on t1.c1 = t2.c2,t2表必须做驱动表，t1表做匹配表:

 1.外连接的结果集 = 内连接的结果集 + t1表中匹配不上的记录和一条null记录(按t2表的结构)
的组合+t2表中匹配不上的记录和一条null记录(按t1表的结构) 的组合。

 
 2.外连接结果集的记录数不一定是t1表和t2表的记录数之和。

 

项目案例:

   列出客户姓名以及他的推荐人

   列出客户姓名以及所开通的远程登录业务的信息（没有申请远程登录业务的客户也要出现在结果集中）

   列出客户姓名以及开通的远程登录业务的数量(结果集中包含所有的客户)

   哪些客户不是推荐人

 

 

 

 

外连接语句的执行顺序

     先将两张表进行外连接操作(join ,on) ,再对外连接的结果集用WHERE子句进行过滤，最后用select语句生成最终结果集 。

  select tabname1.colname1,tabname2.colname from tabname1
     [LEFT | RIGHT | FULL]  join tabname2 on
    tabname1.colname1 = tabname2.colname2
      where filter condition;   

 

 

UNIX服务器信息表结构

desc host

 
name         NULL?     Type
--------------------------------------
      -------

ID       NOT NULL    VARCHRA2(15)
NAME                 VARCHAR2(20)
LOCATION             VARCHAR2(30)

 

 

 

UNIX服务器信息表数据

select * from host;

     ID                    NAME          LOCATION
 192.168.0.26           sunv210           beijing
 192.168.0.20           sun-server        beijing
 192.168.0.23           sun280            beijing
 192.168.0.200          ultra10           beijing

 

项目案例:

   哪些UNIX服务器上没有os账号weixb

 

外连接语句的执行顺序

    ON和WHERE后面都可以跟多个条件表达式，表达式之间用AND连接 。

    先根据ON和AND对要连接的表进行过滤，将过滤后的结果集进行外连接操作(JOIN , ON),再对外连接的结果集用WHERE子句进行过滤，最后用select语句生成最终结果集。

    select tabname1.colname1,tabname2.colname2 from tabname1
      [LEFT | RIGHT | FULL] JOIN tabname2
     on tabname1.colname1 = tabname2.colname2
     and filter condition
     where filter condition;

 

 

年龄分段信息表结构

desc age_segment

Name                  BULL?               Type
----------------------------------------------------
ID               NOT NULL             NUMBER(1)
name             NOT NULL            VARCHAR2(20)
LOWAGE           NOT NULL            NUMBER(2)
HIAGE            NOT NULL            NUMBER(2)

 

年龄分段信息表数据

   select * from age_segment;

  ID     NAME         LOWAGE    HIAGE
  0   少年叛逆期       11        14
  1   少年成长期       15        17
  2   青年青春期       18        28
  3   青年成熟期       29        40
  4   中年壮实期       41        48
  5   中年稳健期       49        55
  6   中年调整期       56        65
  7   老年初老期       66        72

 

非等值连接
 
       不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。

   select tabname1.colname1,tabname2.colname2
     from tabnane1 join tabname2 on tabname1.colname1
    between tabname2.colname2 and tabname3.colname3
   and tabname1.colname2 = val1;

项目案例：

     显示客户的年龄段

     显示客户huangrong 的年龄段
 
     显示各个年龄段不用的客户数(没有客户的年龄段不用出现在结果集中)

     显示各个年龄段的客户数(没有客户的年龄段的客户数为0)

 

 

各类连接的应用场合

    根据结果集生成的规则不同，各类连接的应用场合如下:

 
  交叉连接 （cross join）
     --   笛卡尔积

  内连接 （inner join）
     --   解决匹配问题

  外连接 (outer join)

     --   解决不匹配问题
     --   表的所有记录出现在结果集

 

 

表连接总结

    从结果集的生成规则和连接的表达形式，得出如下结论:

      交叉连接 (cross join)

      内连接  (inner join)

          --   等值连接
          --   非等值连接
          --   自连接

      外连接 (outer join )
 
          --  等值连接
          --  非等值连接
          --  自连接