随便玩玩之PostgreSQL（第三章）表连接查询

随便玩玩之PostgreSQL（第三章）表连接查询

随便玩玩之PostgreSQL

 未经授权不得转载

第三章 表连接查询

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和自连接等。

 3.1 准备数据

先创建学生基本信息表。

CREATE TABLE jbxx(
    id    int,
    xm    varchar(20),
    xb    varchar(1),
    sfzh  varchar(18),
    bj    varchar(2),
    zz    varchar(50),
    lxdh  varchar(11)
);

再插入一些数据。

INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (1,'李四','男','11010119990101103X','二班','北京市东城区','13900100001');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (3,'张三','男','120101199901014419','二班','天津市和平区','13900100002');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (5,'赵六','男','110101199906104171','三班','北京市东城区','13900100003');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (7,'刘一','男','110102199910178874','一班','北京市西城区','13900100004');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (8,'吴九','男','110224199807080450','一班','北京市大兴县','13900100005');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (9,'郑十','男','120221199909120292','一班','天津市宁河县','13900100006');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (2,'王五','女','130101199901081445','二班','河北省石家庄市','13900100007');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (4,'孙七','女','110101199905152083','三班','北京市东城区','13900100008');
INSERT INTO jbxx(id, xm, xb, sfzh, bj, zz, lxdh)   VALUES (6,'周八','女','110102199906106763','三班','北京市西城区','13900100009');

查看数据。

SELECT * FROM jbxx;

结果如图。

3.2内连接查询

内连接使用inner join操作符，内连接查询的查询结果集中仅包含满足条件的行。

内连接又称为相等连接或自然连接，根据连接的表内共有的列匹配表中的行。

如：查询其中考试语文成绩大于70的学生的基本信息。

SELECT cj.kc,
       cj.xm,
       cj.yw,
       jbxx.* 
    FROM cj,jbxx 
    WHERE jbxx.xm=cj.xm 
        AND kc ='期中考试' 
        AND yw >70;

结果如图：

如果不限定其中考试和语文成绩，则会列出成绩表（cj）所有信息和基本信息表（jbxx）所有信息。

SELECT *  FROM cj,jbxx  WHERE jbxx.xm=cj.xm;

其中cj表有每个人有两个姓名，jbxx表每个人只有一个姓名，jbxx的表内信息出现两次。这不是出错，关系数据库本来就是这样的机制。

如果不指定连接字段（限制条件），则会得到18X9=162条记录，有很多错误数据（冗余），这叫笛卡尔积。实际应用中，笛卡尔积没有什么实际用处。

内连接加不加操作符都一样，前提是要指定连接字段。

SELECT *  
   FROM cj inner join jbxx 
   ON jbxx.xm=cj.xm
   WHERE  kc ='期中考试' AND yw >70;

结果如图。

ON jbxx.xm=cj.xm等同于WHERE jbxx.xm=cj.xm 。加INNER JOIN的叫显式连接，不加的角隐式连接。

 3.3外连接

外连接包括左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。

3.3.1 左外连接。

如：查询成绩表中所有人的基本信息。

SELECT * 
    FROM cj LEFT OUTER JOIN jbxx 
        ON cj.xm=jbxx.xm;

结果如图。

3.3.2右外连接

如：查询基本信息中所有人的成绩。

SELECT * 
    FROM cj RIGHT OUTER JOIN jbxx 
        ON cj.xm=jbxx.xm;

结果如图。

3.3.3全连接

如：查询cj表和jbxx表中，不管左边有或右边有，全部列出来。

SELECT * 
    FROM cj FULL OUTER JOIN jbxx 
        ON cj.xm=jbxx.xm;

结果如图。

注：这几个连接查询的结果数据都一样，因为基本信息表的学生在成绩表中都有。稍后将从新整理这一章内容。

3.3.4自连接

自连接是一种特殊的连接，连接的表同源。简单地说就是一张表起两个名字，连接这两张表再查询。

如：

SELECT a.xm,b.xm
FROM jbxx a inner join jbxx b
ON a.xm=b.xm

结果如图。

 

3.4合并查询结果。

合并查询使用UNION关键字，可以将多条SELECT语句的结果合并成一个结果。合并时，两个表的列数和数据类型必须相同。

合并时可以在UNION后加ALL关键字，也可以不加。不加的结果为删除查询结果的重复行，加ALL后的结果为列出所有查询的结果，包括重复行。

如：查询期中考试成绩和一班学生的成绩。

SELECT * FROM CJ WHERE kc='期中考试'
UNION
SELECT * FROM CJ WHERE bj='一班';

结果如图。

结果包括期中考试的成绩和一班的期中期末考试成绩。结果已经去掉一班的期中成绩和期中考试中一般的成绩，重复的只保留一个。加上ALL操作符可以保留重复数据。

SELECT * FROM CJ WHERE kc='期中考试'
UNION ALL
SELECT * FROM CJ WHERE bj='一班';

结果如图。

另外的例子。查询成绩表和基本信息表中出现的名字总和。

SELECT id,bj,xm FROM cj
UNION ALL
SELECT id,bj,xm FROM jbxx;

结果如图。

结果没有去掉重复，为18+9=27行记录。

3.5为表和字段取别名