数据库技术与应用（达梦）课件 任务3.2 查询电力调度管理系统多表数据

《数据库原理与应用》任务3.1查询电力调度管理系统多表数据项目3数据查询与管理主讲人：刘培林第9讲

定制化查询你的数据（2）在任务3.1中，详细分析了T_CONSUME数据表的数据，本任务将T_CONSUME表与T_USERINFO表、T_COMMUNITY表、T_AREA表连接，返回用电的完整信息，包括区域名称、街道名称、用户姓名和用电消费情况。要求用交叉连接、内连接和自然连接3种连接方式分别实现。素质目标能力目标知识目标1）了解交叉连接的概念，掌握有过滤条件交叉连接的定义与含义。2）掌握内连接的定义与用法，了解自连接的含义。3）了解自然连接的概念与含义，理解自然连接与内连接的区别与联系。4）掌握外连接的定义与用法。1）能够正确理解各连接查询的含义，根据应用要求准确选择查询的连接方式。2）能够熟练使用内连接、外连接进行连接查询。从事物的整体出发，基于事务的关联关系和层次结构性分析问题，树立全局意识，培养协同素质。3.2.1交叉连接如果一个查询包含至少2个表，则称为连接查询。连接查询中<FROM子句>中使用的是<连接表>。1无过滤条件无过滤条件交叉连接对连接的两张表的记录做笛卡尔集，返回两张表所有可能的组合。也即将两张表不加限制地连接在一起，没有约束条件，也称为非限制性连接。语法格式如下。SELECT[[<模式名>.]<基表名>|<视图名>.]*|<值表达式>[[AS]<列别名>],….FROM[<模式名>.]<基表名|视图名>CROSSJOIN|,[<模式名>.]<基表名|视图名>【例3-40】将T_AREA表与T_COMMUNITY表交叉连接。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1CROSSJOINEPDMS.T_COMMUNITYT2;代码也可以简写如下。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1,EPDMS.T_COMMUNITYT2;T_AREA表有6条记录，T_COMMUNITY表有14条记录，运行结果为6*14=84条记录，也即将所有的区域和街道组合了起来，意味着每个区都有所有的街道。2有过滤条件对连接的两张表的记录做笛卡尔乘积，根据<WHERE子句>条件进行过滤，产生最终结果输出。【例3-41】将T_AREA表与T_COMMUNITY表交叉连接的结果用AREA_ID列进行等值过滤，返回街道及其所属地区的信息。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1CROSSJOINEPDMS.T_COMMUNITYT2WHERE

T1.AREA_ID=T2.AREA_ID;代码也可以简写如下。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1,EPDMS.T_COMMUNITYT2WHERET1.AREA_ID=T2.AREA_ID;本例中的查询数据来自T_AREA和T_COMMUNITY两个表，因此，在FROM子句中给出了两个表的表名，为了使用简化，为表起了别名。在WHERE子句中给出连接条件，由于参加连接的列名相同，为了避免混淆，在列名前添加了表名前缀。本例的查询结果是T_AREA表和T_COMMUNITY表在AREA_ID列上做等值连接产生的，条件“T1.AREA_ID=T2.AREA_ID”也称为连接条件或连接谓词。当连接运算符为“=”号时，称为等值连接，使用其它运算符则称非等值连接。使用交叉连接词，还应注意以下几点：1）连接谓词中的列类型必须是可比较的，但不一定要相同，只要可以隐式转换即可；2）不要求连接谓词中的列同名；3）连接谓词中的比较操作符可以是>、>=、<、<=、=、<>；4）WHERE子句中可同时包含连接条件和其它非连接条件。3.2.2内连接根据连接条件，结果集仅包含满足全部连接条件的记录，称这样的连接为内连接。1基本用法内连接的语法格式如下。SELECT值表达式列表FROM表|视图[INNER]JOIN表1|视图1ON条件其中，<JOIN子句>指定连接的表，<ON子句>指定连接的条件表达式。1）建议在列名前面加上表或视图名进行限定，如果没有歧义，也可以省略。2）可以对多个表进行连接查询，但是需要注意连接的顺序。【例3-42】将T_AREA表与T_COMMUNITY表连接，返回街道名称、街道编码和区域名称。SELECTT1.AREA_NAME,T2.COMMUNITY_ID,T2.COMMUNITY_NAMEFROMEPDMS.T_AREAT1INNERJOINEPDMS.T_COMMUNITYT2ONT1.AREA_ID=T2.AREA_ID;上面代码可以省略部分表名，书写如下。SELECTAREA_NAME,COMMUNITY_ID,COMMUNITY_NAMEFROMEPDMS.T_AREAT1INNERJOINEPDMS.T_COMMUNITYT2ONT1.AREA_ID=T2.AREA_ID;【例3-43】将T_USERINFO表与T_COMMUNITY表和T_AREA表连接，返回用户的区域名称、街道名称和用户基本信息。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,USER_PASSWORDFROMEPDMS.T_USERINFOT1INNERJOINEPDMS.T_COMMUNITYT2ONT2.COMMUNITY_ID=T1.COMMUNITY_IDINNERJOINEPDMS.T_AREAT3ONT3.AREA_ID=T2.AREA_ID;【例3-44】为例3-43增加条件，查询编号为“1000000001”的用户的信息。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,USER_PASSWORDFROMEPDMS.T_USERINFOT1INNERJOINEPDMS.T_COMMUNITYT2ONT2.COMMUNITY_ID=T1.COMMUNITY_IDINNERJOINEPDMS.T_AREAT3ONT3.AREA_ID=T2.AREA_IDWHERET1.USER_ID='1000000001';内连接的INNER关键字可以省略，例3-44代码可以省略INNER关键字书写如下。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,USER_PASSWORDFROMEPDMS.T_USERINFOT1JOINEPDMS.T_COMMUNITYT2ONT2.COMMUNITY_ID=T1.COMMUNITY_IDJOINEPDMS.T_AREAT3ONT3.AREA_ID=T2.AREA_IDWHERET1.USER_ID='1000000001';内连接也可以用等值连接实现，例3-44代码可以书写如下。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,USER_PASSWORDFROMEPDMS.T_USERINFOT1,EPDMS.T_COMMUNITYT2,EPDMS.T_AREAT3WHERET2.COMMUNITY_ID=T1.COMMUNITY_ID

ANDT3.AREA_ID=T2.AREA_ID

ANDT1.USER_ID='1000000001';2自连接数据表与自身进行的连接称为自连接。自连接查询至少要对一张表起别名，否则服务器无法识别要处理的是哪张表，自连接也可以看作是一张表的两个副本之间进行的连接。【例3-45】已知职工档案表（T_EMPLOYEE）的字段为：EMPLOYEE_ID（职工编号）、EMPLOYEE_NAME（职工姓名）和LEADER_ID（领导编号，取值为职工编号）。假定职工档案表的数据下表所示，使用自连接查询职工编码、职工姓名和职工所在部门领导的名字。EMPLOYEE_IDEMPLOYEE_NAMELEADER_ID1000000001王明10000000011000000002李明10000000011000000003蔡明1000000001自连接代码如下。SELECTT1.EMPLOYEE_ID,T1.EMPLOYEE_NAME,T2.EMPLOYEE_NAMEAS部门负责人FROMEPDMS.T_EMPLOYEET1JOINEPDMS.T_EMPLOYEET2ONT1.LEADER_ID=T2.EMPLOYEE_ID;运行结果如下表所示，部门负责人显示为名字。EMPLOYEE_IDEMPLOYEE_NAME部门负责人1000000001王明王明1000000002李明王明1000000003蔡明王明3.2.3自然连接1NATURALJOIN把两张连接表中的同名列作为连接条件，进行等值连接，称这样的连接为自然连接。自然连接具有以下特点。1）连接表中存在的同名列；2）如果有多个同名列，则会产生多个等值连接条件；3）如果连接表中的同名列类型不匹配，则报错处理。【例3-46】将T_AREA表与T_COMMUNITY表自然连接，返回区域名称、街道编码和街道名称。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1NATURALJOINEPDMS.T_COMMUNITYT2;这里T_AREA表与T_COMMUNITY表的同名列是AREA_ID，自然连接使用同名列，同名列不显示，运行结果如图所示。例3-46用内连接书写如下。SELECTT1.AREA_NAME,T2.COMMUNITY_ID,T2.COMMUNITY_NAMEFROMEPDMS.T_AREAT1JOINEPDMS.T_COMMUNITYT2ONT1.AREA_ID=T2.AREA_ID2JOIN…USING这是自然连接的另一种写法，<JOIN子句>指定连接的两张表，<USING子句>指明连接的列，要求<USING子句>中的列存在于两张连接表中。【例3-47】用JOIN…USING实现例3-46。SELECTT1.*,T2.*FROMEPDMS.T_AREAT1JOINEPDMS.T_COMMUNITYT2USING(AREA_ID);3.2.4外连接外连接返回一张表的所有记录，对于另一张表无法匹配的字段用NULL值填充返回。外连接中的表分为左表和右表，根据表所在外连接中的位置来确定，位于左侧的表，称为左表；位于右侧的表，称为右表。支持三种方式的外连接：左外连接、右外连接、全外连接。1）左外连接：返回左表所有记录；2）右外连接：返回右表所有记录；3）全外连接：返回两张表所有记录。处理过程为分别对两张表进行左外连接和右外连接，最后合并结果集。【例3-48】连接T_ADMININFO表与T_CONSUME表，查询每个管理员的用电消费情况，要求必须列出所有的管理员。分析：题目中要求列出所有的管理员，也就是说即使管理员没有用电消费，也需要列出管理员的名字，所以对于T_ADMININFO表将不作限制地列出所有的管理员名字。另一方面对于T_CONSUME表，则需要将其与T_ADMININFO表进行连接限制，仅列出符合连接条件的记录。基于上述分析，需要使用外连接，将T_ADMININFO表放在左边，进行左外连接。SELECTADMIN_NAME,CONSUME_NUMFROMEPDMS.T_ADMININFO--左表--LEFTOUTER

JOINEPDMS.T_CONSUME--右表--ONEPDMS.T_ADMININFO.ADMIN_ID=EPDMS.T_CONSUME.USER_ID;也可以将T_ADMININFO表放在右边，改用右外连接实现，代码如下。SELECTADMIN_NAME,CONSUME_NUMFROMEPDMS.T_CONSUME--左表--RIGHTOUTERJOINEPDMS.T_ADMININFO--右表--ONEPDMS.T_ADMININFO.ADMIN_ID=EPDMS.T_CONSUME.USER_ID;运行结果如图所示，列出了所有的管理员，哪怕管理员没有用电消费。【例3-49】将例3-48修改为全外连接，分析查询结果。SELECTADMIN_NAME,CONSUME_NUMFROMEPDMS.T_ADMININFOFULLOUTERJOINEPDMS.T_CONSUMEONEPDMS.T_ADMININFO.ADMIN_ID=EPDMS.T_CONSUME.USER_ID;运行结果如图所示，列出了所有的管理员和所有的用电消费记录，共363条记录。T_ADMININFO11条记录，T_CONSUME表352条记录，11+352=363。外连接的OUTER关键字可以省略。[任务实施]

1技术分析1）本任务涉及T_AREA、T_COMMUNITY、T_USERINFO和T_CONSUME共4张表，是典型的连接查询。2）4张表相互有同名字段，具有主外键约束关系，可以用交叉连接、内连接、自然连接进行连接查询。[任务实施]

2实现步骤1）内连接是最常用连接查询，参考例3-43，用内连接实现的代码如下。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,T1.CONSUME_NUMFROMEPDMS.T_CONSUMET1INNERJOINEPDMS.T_USERINFOT2ONT2.USER_ID=T1.USER_IDINNERJOINEPDMS.T_COMMUNITYT3ONT3.COMMUNITY_ID=T2.COMMUNITY_IDINNERJOINEPDMS.T_AREAT4ONT4.AREA_ID=T3.AREA_ID;[任务实施]

2）将内连接修改为有过滤条件交叉连接的代码如下。SELECTAREA_NAME,COMMUNITY_NAME,USER_NAME,T1.CONSUME_NUMFROMEPDMS.T_CONSUMET1,EPDMS.T_USERINFOT2,EPDMS.T_COMMUNITYT3,EPDMS.T_AREAT4WHERET2.US