oracle连接多个表.ppt

连接多个表 OracleSQL和PL SQL编程 目标 创建一个笛卡尔连接使用WHERE子句创建相等连接使用JOIN关键字创建相等连接使用WHERE子句创建不等连接使用JOIN ON方法创建不等连接创建自我连接区分内部连接和外部连接使用WHERE子句创建外部连接使用OUTER关键字创建外部连接使用集合运算符结合多个查询的结果连接三个或更多表 笛卡尔连接 又称为 笛卡尔乘积 或 交叉连接 第一表中的每一个数据与第二个表中的每一个记录配对如果第一个表中有m条记录 第二个表中有n条记录 结果是m n条记录 笛卡尔连接 传统方法 查找每本书的出版社名称 SELECTtitle nameFROMbooks publisher 笛卡尔连接 JOIN方法 Oracle中 可以使用CROSS关键字与JOIN关键字一起使用用来创建笛卡尔连接 SELECTtitle nameFROMbooksCROSSJOINpublisher 相等连接 实际工作中 最常用的连接类型是对一个公共列中存储了相同数据的两个 或多个 表创建的 这种连接被称为 相等连接 也可以称为 同等连接 内部连接 或 简单连接 公共列是两个或更多表中存在相同数据的列 比如 boooks和publisher表都包含一个Pubid的公共列 这个公共列标识为每一个出版社指定的标识代码 相等连接 传统方法 传统方法是使用WHERE子句 等号用来指定对于在结果中连接和返回的行 每一个表中的Pubid列和内容必须完全相等 SELECTtitle nameFROMbooks publisherWHEREbooks pubid publisher pubid 注意 WHERE子句中的pubid列名称前面包括了对应得表名称 相等连接 传统方法 需要输出这个pubid时 必须在SELECT子句中指明哪个表的 以便引起多义性错误 也就是说连接查询的两个表中有相同列明的时候 要指明具体是哪个表的列要显示 SELECTtitle books pubid nameFROMpublisher booksWHEREpublisher pubid books pubidANDpublisher pubid 4 相等连接 传统方法 查询成本低于15 00美元或者出版商id为1的任何图书书名 出版社编号 出版社名称 书的成本价 SELECTtitle p pubid name costFROMpublisherp booksbWHEREp pubid b pubidAND cost 15ORb pubid 1 用到了表别名 作用是为表临时指定一个不同的名称 原因 表名过长不便于使用 可以使用表别名 还可以减少需要的内存 要记住一个规则 如果在FROM子句中指定了一个表别名 那么只要在这个SQL语句中引用这个表 就必须使用这个别名 相等连接 JOIN方法 可以使用三种JOIN关键字的方法来创建相等连接 NATURALJOIN JOIN USING和JOIN ONNATURALJOIN关键字将在包括一个同等命名和定义的字段的两个表之间自动创建连接使用USING子句可以根据在两个表中具有相同名称和定义的列创建连接当USING子句中连接的表没有包括一个同等命名和定义的字段时 必须添加一个WHERE子句或者向JOIN关键字添加ON子句 以便制定这两个表之间的关系 相等连接 JOIN方法 列出books表中每一本书的书名以及对应的出版社ID和出版社名称 SELECTtitle pubid nameFROMpublisherNATURALJOINbooks 注意 不需要指定两个表包含哪一个或哪些公共列 但暗示了至少存在一个具有相同名称的公共列 相等连接 JOIN方法 JOIN USING方式JOIN ON方式 SELECTtitle pubid nameFROMbooksJOINpublisherUSING pubid SELECTtitle nameFROMbooksbJOINpublisherpONb pubid p pubid 如果不查询公共列时 SELECTtitle b pubid nameFROMbooksbJOINpublisherpONb pubid p pubid 如果查询公共列 要指出具体出自哪个表 相等连接 JOIN方法 随JOIN关键字使用USING和ON子句 其间存在的两个主要区别只有当连接的表包括一个具有相同名称的公共列式才能使用USING子句 这不是ON子句所必须的条件是在ON子句中指定的 这在USING中不允许 USING子句只能包含公共列的名称 练习1 创建一个列表 显示每本书的书名以及出版社办公室中你再次定购每本书时需要与之联系的人的姓名和电话号码 不等连接 在不能使用等号连接相关列的时候 也就是表中不存在可以连接的相同行的时候 我们可以使用 不等连接 不等连接允许你将一个范围的最小值存储在记录的一列 这个范围的最大值存储在另一列 不等连接 传统方法 books表和promotion表之间的行不包含相等的值 所以你需要使用不等连接来确定客户将在促销活动中得到什么礼品 SELECT FROMpromotion SELECTtitle giftFROMbooks promotionWHEREretailBETWEENminretailANDmaxretail 不等连接 JOIN方法 SELECTtitle giftFROMbooksJOINpromotionONretailBETWEENminretailANDmaxretail 练习2 订购图书SHORTESTPOEMS的客户将收到什么样的礼品 自我连接 一个表中的数据引用了同一个表中存储的其他数据 自我连接 传统方法 customers表referred列存储了介绍该新客户的个人的客户号 SELECTr firstname r lastname c lastnamereferredFROMcustomersc customersrWHEREc referred r customer 自我连接 JOIN方法 用JOIN ON方法 SELECTr firstname r lastname c lastnamereferredFROMcustomerscJOINcustomersrONc referred r customer 外部连接 在执行相等连接 不等连接 和自我连接时 只有当查询的每一个表都包括一个对应的纪录时才会返回一行 这些连接都可归为 内部连接 因为只有当每一个表中都存在匹配数据时 记录才会在结果中列出 事实上是JOIN关键字默认的INNER关键字 INNERJOIN当需要在连接查询的结果中包括存在于一个表中但是在另一个表中没有对应记录时 就需要外部连接 使用关键字OUTERJOIN 也可以在带有 外部连接运算符 外部连接 传统方法 下面是customers和orders表匹配的记录 SELECTlastname firstname order FROMcustomersc ordersoWHEREc customer o customer ORDERBYc customer 不匹配会怎样 外部连接 传统方法 使用外部运算符 的外部连接 SELECTlastname firstname order FROMcustomersc ordersoWHEREc customer o customer ORDERBYc customer 结果中发现包含了NULL值 也就是说如果一个客户在customers表中 但最近没有下达订单 那么orders表中缺少对应的行 或者是不完整的表 也就是说这个表缺少数据 因此 将外部连接运算符 放在了引用不完整的orders表的where子句中的连接条件部分之后 外部连接 传统方法 注意规则外部连接运算符只能用于连接条件中的一个表 换句话说 传统方式时 不能同时在两个表中创建NULL行包括外部连接运算符的条件不能使用IN或OR运算符 因为这表明如果一行与其他表中的一行匹配或者满足其他某个指定的条件 那么它就显示在结果中 外部连接 JOIN方法 可以随JOIN关键字包括LEFT RIGHT 或FULL来指定关联类型 SELECTlastname firstname order FROMcustomerscLEFTOUTERJOINordersoONc customer o customer ORDERBYc customer SELECTlastname firstname order FROMcustomerscRIGHTOUTERJOINordersoONc customer o customer ORDERBYc customer 集合运算符 用来结合两个 或更多 SELECT语句的结oracle中的集合预算符 UNION UNIONALL INTERSECT和MINUSUNION 返回两个查询的结果 如果存在重复记录 则将删除它们UNIONALL 在结果中包含重复的记录INTERSECT 只列出两个查询都返回的记录MINUS 将从输出中删除第二个查询的结果 集合运算符 需要customers表中最近下了订单的所有客户的列表 SELECTcustomer FROMcustomersUNIONSELECTcustomer FROMorders SELECTcustomer FROMcustomersUNIONALLSELECTcustomer FROMorders SELECTcustomer FROMcustomersINTERSECTSELECTcustomer FROMorders SELECTcustomer FROMcustomersMINUSSELECTcustomer FROMorders 连接三个或更多表连接三个或更多表 传统方法 SELECTtitle firstname lastnameFROMcustomers orders orderitems booksWHEREcustomers customer orders customer ANDorders order orderitems order ANDorderitems isbn books isbnORDERBYtitle 连接三个或更多表连接三个或更多表 JOIN方法 JOINONNATURALJOIN SELECTtitle firstname lastnameFROMcustomersJOINordersONcustomers customer orders customer JOINorderitemsONorders order orderitems order JOINbooksONorderitems isbn books isbnORDERBYtitle SELECTtitle firstname lastnameFROMcustomersNATURALJOINordersNATURALJOINorderitemsNATURALJOINbooksORDERBYtitle 练习3 生成居住在佛罗里达州并且订购了计算机