`
jinyanliang
  • 浏览: 308028 次
  • 性别: Icon_minigender_1
  • 来自: 河南开封
社区版块
存档分类
最新评论

db2中的连接查询，内连接、外连接、交叉连接

DB2CC++C#SQL 
阅读更多
DB2 连接查询的学习：
[size=large]


首先创建三个表 student(id integer not null,name varchar(32),sex integer) ;

                            score(name varchar(32),score decimal);

                            grade(score double,grade varchar(2));

       学生表，分数表，等级表。




db2 => select * from student

ID          NAME                             SEX
----------- -------------------------------- -----------
          1 xiaoming                                   1
          2 vicky                                      1
          3 xiaohong                                   0
          4 xiaogang                                   1
          5 linlin                                     0
          6 xiaoyue                                    0
          7 manman                                     0
          8 penpen                                     1
          9 hanghang                                   1
         10 bingbing                                   0

  10 条记录已选择。






db2 => select * from score

NAME                             SCORE
-------------------------------- -------
xiaoming                           81.00
vicky                             100.00
xiaohong                           90.00
xiaoli                             90.00

  4 条记录已选择。



db2 => select * from grade

SCORE   GRADE
-------    -----
100.00  A
  90.00  B
  80.00  C
  60.00  D
   0.00   X

  5 条记录已选择。








连接查询开始



1.       查询出student 和 score 相同姓名的所有列。要求只列出完全相同的列。

内连接：

select s.id,s.name,s.sex,c.score from student s inner join score c on s.name = c.name

此时外表是基表 即 from后的student表是基表。

db2 => select s.id,s.name,s.sex,c.score from student s inner join score c on s.name = c.name

ID          NAME                             SEX         SCORE
----------- -------------------------------- ----------- -------
          1 xiaoming                                 1     81.00
          2 vicky                                      1     100.00
          3 xiaohong                                 0     90.00

  3 条记录已选择。





或者使用where 连接：

select s.id,s.name,s.sex,c.score from student s,score c where s.name = c.name



db2 => select s.id,s.name,s.sex,c.score from student s,score c where s.name = c.name

ID          NAME                             SEX         SCORE
----------- -------------------------------- ----------- -------
          1 xiaoming                                   1   81.00
          2 vicky                                      1  100.00
          3 xiaohong                                   0   90.00

  3 条记录已选择。





2.       外连接查询：

左连接：

select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name

首先看一下上面的sql。



db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          1 xiaoming                         xiaoming                           81.00
          2 vicky                            vicky                             100.00
          3 xiaohong                         xiaohong                           90.00
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -

  10 条记录已选择。



            

        左连接查询本质是以左边的表，即from后面的表为主要基表查询的。从上面的结果可以看到，左表中的数据全部都查了出来，右边的表中只有有符合 on 后条件的有对应的都列了出来，右表中没有对应左表的数据就补空。即null 。

        再看这一句

        select * from student s left join score c on s.name = c.name where s.id<5



db2 => select * from student s left join score c on s.name = c.name where s.id<5

ID          NAME                             SEX         NAME                             SCORE
----------- -------------------------------- ----------- -------------------------------- -------
          1 xiaoming                                   1 xiaoming                           81.00
          2 vicky                                      1 vicky                             100.00
          3 xiaohong                                   0 xiaohong                           90.00
          4 xiaogang                                   1 -                                      -

  4 条记录已选择。







和上面类似。Where条件句中规定了主表的条件。



还有，where条件句子也可以规定右表的条件。

如：select * from student s left join score c on s.name = c.name where c.score>80



db2 => select * from student s left join score c on s.name = c.name where c.score>80

ID          NAME                             SEX         NAME                             SCORE
----------- -------------------------------- ----------- -------------------------------- -------
          1 xiaoming                                1    xiaoming                           81.00
          2 vicky                                      1    vicky                             100.00
          3 xiaohong                               0    xiaohong                           90.00

  3 条记录已选择。





同样也会有3条符合的数据列出。但是遍历查询 score 表会发现，

db2 => select * from score

NAME                             SCORE
-------------------------------- -------
xiaoming                           81.00
vicky                             100.00
xiaohong                           90.00
xiaoli                             90.00

  4 条记录已选择。





name = xiaoli的条目没有列出。这就是因为基表 左表 中没有这一条主记录。



同样的更深刻一点，像这个sql语句：



select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where s.id > 3



db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where s.id > 3

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -

  7 条记录已选择。





更加证明只要基表 左表 中有符合where 条件的条目就会列出，右表中是否有对应的记录是无关紧要的。这在很多特殊应用的时候都是很有用的哦。

还有这个看看：

select * from student s left join score c on s.name = c.name where c.name = 'xiaoli'



db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where c.name = 'xiaoli'

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------

  0 条记录已选择。





select * from score where name = 'xiaoli'



db2 => select * from score where name = 'xiaoli'

NAME                             SCORE
-------------------------------- -------
xiaoli                             90.00

  1 条记录已选择。







为什么第一句没有查询出记录？还是那句话，左连接是以左表为基表的。基表中没有的记录，是根本查不到的。

那换成右连接就有了吗？我们来试试。



select * from student s right join score c on s.name = c.name where c.name = 'xiaoli'



db2 => select s.id,s.name,c.name,c.score from student s right join score c on s.name = c.name where c.name = 'xiaoli'

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          - -                                xiaoli                             90.00

  1 条记录已选择。







效果是立竿见影的。呵呵。融会贯通一下吧，右跟左是一样的，自己多试试就能掌握他们了。



还有一种叫全外连接，也叫交叉连接。

select s.id,s.name,c.name,c.score from student s full outer join score c on s.name = c.name





db2 => select s.id,s.name,c.name,c.score from student s full outer join score c on s.name = c.name

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          1 xiaoming                         xiaoming                           81.00
          2 vicky                            vicky                             100.00
          3 xiaohong                         xiaohong                           90.00
          - -                                xiaoli                             90.00
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -

  11 条记录已选择。



效果就等于是把左连接和右连接合到一块。不加where的时候，就是把两个表的条目都列出来，没有对应的条目，左右都可以 空 出来为null。

加个where试试：

本人的理解，

用左连接和右连接以及交叉连接的时候，你先不要带 where 条件句。试一下，查询出来的东西，就把他当作是一个视图。然后你再加where 条件的时候，其实就跟从这张视图中 查询数据是一样的。

就像这两句一样：

select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where

s.id is null



db2 => select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where s.id is null

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          - -                                xiaoli                             90.00

  1 条记录已选择。







select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where c.name is null



db2 => select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where c.name is null

ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -

  7 条记录已选择。



看看结果就明白了。



下面看看这个sql:





select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1)

执行一下上面的sql：







db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1)

ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          2 vicky                                      3 xiaohong                                   4 xiaogang
          3 xiaohong                                   4 xiaogang                                   5 linlin
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          5 linlin                                     6 xiaoyue                                    7 manman
          6 xiaoyue                                    7 manman                                     8 penpen
          7 manman                                     8 penpen                                     9 hanghang
          8 penpen                                     9 hanghang                                  10 bingbing
          9 hanghang                                  10 bingbing                                   - -
         10 bingbing                                   - -                                          - -

  10 条记录已选择。





这有什么用呢？

看看加一个where后的效果。

select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1) where mod(s1.id,3) = 1;

效果如下：



db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3
id-1) where mod(s1.id,3) = 1

ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          7 manman                                     8 penpen                                     9 hanghang
         10 bingbing                                   - -                                          - -

  4 条记录已选择。





这样就实现了把一张表的数据 以 横排三列的形式展现了出来。

这种形式，在很多时候还是颇为有用的。比如曾经遇到过用ireport做jasper的时候，就需要这么来展现。因为ireport的detail 区是只支持向下遍历的，用户如果需要这样的三列相连的话，用这个sql就非常有用了。

当然你也可以用这个，

select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1,student s2,student s3 where mod(s1.id,3)=1 and mod(s2.id,3)=2 and mod(s3.id,3)=0 and s1.id = s2.id-1 and s2.id = s3.id-1


db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1,student s2,student s3 where mod(s1.id,3)=1 and mod(s2.id,3)=2 and mod(s3.id,3)
=0 and s1.id = s2.id-1 and s2.id = s3.id-1

ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          7 manman                                     8 penpen                                     9 hanghang

  3 条记录已选择。




但是发现没有？最后的id=10 没有了。因为没有用外连接，所以是不会有空行的。[/size]
分享到：
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    数据库表连接

    交叉连接是指两个表中的每一行与其他表的所有行组合在一起的一种连接类型。在实际应用中，它并不常用，但在某些特定场景下，比如需要生成所有可能的组合时，则会用到。 **示例：** 1. **显式使用CROSS JOIN** ``...

    DB2 通用数据库 SQL入门

    - **JOIN操作**：DB2支持内连接、外连接（左连接、右连接和全连接）、交叉连接等多种JOIN操作，便于处理多表查询。 - **视图**：通过`CREATE VIEW`定义视图，可以简化复杂的查询，提供数据抽象。 - **索引**：...

    SQL快速入门(DB2)

    - **联接（JOIN）**：合并两个或更多表中的数据，例如内连接、外连接、交叉连接等。 - **视图（View）**：虚拟表，基于一个或多个实际表，提供简化数据访问的方式。 - **索引（Index）**：加速数据检索，提高查询...

    DB2_400数据库设计与编程

    - **SQL JOINs**：用于合并多个表的数据，有内连接、外连接、交叉连接等多种类型。 - **子查询**：嵌套在其他SQL语句中的查询，用于检索满足特定条件的数据。 - **存储过程和触发器**：存储过程是一组预编译的SQL...

    Struts2 + JasperReports4 + DB2 Demo

    2. **JDBC连接与数据查询**：使用DB2 JDBC驱动建立连接，编写SQL语句获取数据，使用ResultSet处理查询结果。 3. **JasperReports报表设计**：了解JRXML语法，设计报表布局，使用iReport或Jaspersoft Studio创建报表...

    黑马程序员JavaEE就业班同步笔记数据库相关：JDBC操作.pdf

    - 交叉连接。 - 内连接：inner join、隐式内连接。 + 外连接：left outer join、right outer join。 + 子查询。 JDBC * Java DataBase Connectivity，Java数据库的连接。 * JDBC是SUN公司统一提供的一套接口...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    db2 IBM DB2在企业级的应用最为广泛， 在全球的500家最大的企业中,几乎85%以上用DB2数据库服务器。收费 大型企业 Access 微软 Access是一种桌面数据库，只适合数据量少的应用，在处理少量 数据和单机访问的数据库时...

    SQL语法大全

    1. ASP与Access数据库连接： dim conn,mdbfile mdbfile=server.mappath("数据库名称.mdb") set conn=server.createobject("adodb.connection") conn.open "driver={microsoft access driver (*.mdb)};uid=admin;...

    wfsReporter.rar

    从描述中我们可以了解到，该工具具备三大核心功能：数据格式转换、多数据库连接支持以及自动生成交叉报表。 首先，让我们关注“数据格式转换”这一特性。在IT领域，数据格式转换是常见的需求，不同的系统和应用往往...

    程序员的SQL金典.rar

    　7.4 交叉连接 　7.5 自连接 　7.6 外部连接 　7.6.1 左外部连接 　7.6.2 右外部连接 　7.6.3 全外部连接 第8章 子查询 　8.1 子查询入门 　8.1.1 单值子查询 　8.1.2 列值子查询 　8.2 SELECT列表中的标量子查询 ...

    程序员的SQL金典

    本书全面介绍了内连接、外连接、交叉连接等各种连接方式的特点及应用场景，并提供了实用的例子。 - **SQL调优**：SQL调优是提高数据库性能的关键。本书通过具体的案例分析，教授如何优化SQL查询计划、使用索引等技巧...

    CP343_1的使用

    - 将PG/PC的网络地址设置为与硬件组态中的IP地址处于同一网段内（注意避免IP地址冲突）。 3. **网线选择：** - 直接连接计算机与模块时，可使用交叉线或平行线。 - 若通过交换机或路由器连接，则需使用平行线。 ...

    从两种SQL表连接写法来了解过去

    这种写法又称为"交叉连接"或"隐式连接"，它将`table1`与`table2`的所有行组合在一起，然后通过`WHERE`子句过滤出满足条件的行。 2. ANSI SQL 92标准写法： ```sql select A.c1, A.c2, B.c1, B.c2 from table1 A ...

    Web数据库设计原则 精品资料.pptx

    Web数据库的优点包括借用现成的浏览器软件，无需开发数据库前端，标准统一，开发过程简单，交叉平台支持等。Web数据库系统的基本模型包括客户端、Web服务器、数据库服务器和互联网。Web数据库的发展趋势包括非结构化...

    java部分面试题.pdf

    - 包括内连接(INNER JOIN)，外连接(LEFT JOIN, RIGHT JOIN, FULL JOIN)，交叉连接(CROSS JOIN)等，用于在不同表间进行数据关联。 5. **Tomcat和WebLogic配置连接池**： - 连接池管理数据库连接，提高效率，避免...

    dbvis_windows-x64_9_1_5

    DbVisualizer是一个完全基于JDBC的跨平台数据库管理工具，内置SQL语句编辑器（支持语法高亮），凡是具有JDBC数据库接口的数据库都可以管理，已经在Oracle, Sybase, DB2, MySQL, InstantDB, Cloudcape, HyperSonic ，...

    程序员的SQL金典6-8

    - **交叉连接** - 返回所有可能的记录组合。 - **自连接** - 表与自身连接。 - **外部连接** - 包括左外部连接、右外部连接和全外部连接。 - **左外部连接**：返回左表的所有记录以及右表匹配的记录。 - **右...

Magicbox
Global site tag (gtag.js) - Google Analytics