Oracle连接多个表学习课程

1、笛卡尔连接 又称为“笛卡尔乘积”或“交叉连接”，第一表中的每一个数据与第二个表中的每一个记录配对 如果第一个表中有m条记录，第二个表中有n条记录，结果是m*n条记录第1页/共32页第一页，编辑于星期六：十一点 二十三分。笛卡尔连接传统方法 查找每本书的出版社名称SELECT title,nameFROM books,publisher;第2页/共32页第二页，编辑于星期六：十一点 二十三分。笛卡尔连接JOIN方法 Oracle 中，可以使用CROSS关键字与JOIN关键字一起使用用来创建笛卡尔连接SELECT title,nameFROM books CROSS JOIN publisher;

2、第3页/共32页第三页，编辑于星期六：十一点 二十三分。相等连接实际工作中，最常用的连接类型是对一个公共列公共列中存储了相同数据的两个（或多个）表创建的，这种连接被称为“相等连接相等连接”，也可以称为“同等连接同等连接”，“内部连接内部连接”或或“简单连接简单连接”。公共列是两个或更多表中存在相同数据的列公共列是两个或更多表中存在相同数据的列，比如， boooks和publisher表都包含一个Pubid的公共列，这个公共列标识为每一个出版社指定的标识代码。第4页/共32页第四页，编辑于星期六：十一点 二十三分。相等连接传统方法 传统方法是使用WHERE子句，等号用来指定对于在结果中连接和返回

3、的行，每一个表中的Pubid列和内容必须完全相等。SELECT title,nameFROM books,publisherWHERE books.pubid = publisher.pubid;注意：WHERE子句中的pubid列名称前面包括了对应得表名称。第5页/共32页第五页，编辑于星期六：十一点 二十三分。相等连接传统方法 需要输出这个pubid时，必须在SELECT子句中指明哪个表的，以便引起多义性错误，也就是说连接查询的两个表中有相同列明的时候，要指明具体是哪个表的列要显示SELECT title,books.pubid,nameFROM publisher,booksWHERE

4、publisher.pubid = books.pubid AND publisher.pubid=4;第6页/共32页第六页，编辑于星期六：十一点 二十三分。相等连接传统方法 查询成本低于15.00美元或者出版商id为1的任何图书书名，出版社编号，出版社名称，书的成本价SELECT title,p.pubid,name,costFROM publisher p,books bWHERE p.pubid=b.pubid AND (cost15 OR b.pubid=1);用到了表别名表别名，作用是为表临时指定一个不同的名称，原因：表名过长不便于使用，可以使用表别名，还可以减少需要的内存要记住一

5、个规则：要记住一个规则：如果在FROM子句中指定了一个表别名，那么只要在这个SQL语句中引用这个表，就必须使用这个别名。第7页/共32页第七页，编辑于星期六：十一点 二十三分。相等连接JOIN方法 可以使用三种JOIN关键字的方法来创建相等连接：NATURAL JOIN,JOINUSING和JOINON1.NATURAL JOIN关键字将在包括一个同等命名和定义的字段的两个表之间自动创建连接2.使用USING子句可以根据在两个表中具有相同名称和定义的列创建连接3.当USING子句中连接的表没有包括一个同等命名和定义的字段时，必须添加一个WHERE子句或者向JOIN关键字添加ON子句，以便制定这

6、两个表之间的关系。第8页/共32页第八页，编辑于星期六：十一点 二十三分。相等连接JOIN方法 列出books表中每一本书的书名以及对应的出版社ID和出版社名称SELECT title,pubid,nameFROM publisher NATURAL JOIN books;注意：注意：不需要指定两个表包含哪一个或哪些公共列，但暗示了至少存在一个具有相同名称的公共列第9页/共32页第九页，编辑于星期六：十一点 二十三分。相等连接JOIN方法 JOINUSING方式 JOINON方式SELECT title,pubid,nameFROM books JOIN publisherUSING (pub

7、id);SELECT title ,nameFROM books b JOIN publisher pON b.pubid = p.pubid;如果不查询公共列时SELECT title,b.pubid,nameFROM books b JOIN publisher pON b.pubid = p.pubid;如果查询公共列，要指出具体出自哪个表第10页/共32页第十页，编辑于星期六：十一点 二十三分。相等连接JOIN方法 随JOIN关键字使用USING和ON子句，其间存在的两个主要区别1.只有当连接的表包括一个具有相同名称的公共列式才能使用USING子句，这不是ON子句所必须的2.条件是在O

8、N子句中指定的，这在USING中不允许，USING子句只能包含公共列的名称第11页/共32页第十一页，编辑于星期六：十一点 二十三分。练习1 创建一个列表，显示每本书的书名以及出版社办公室中你再次定购每本书时需要与之联系的人的姓名和电话号码。 第12页/共32页第十二页，编辑于星期六：十一点 二十三分。不等连接 在不能使用等号连接相关列的时候，也就是表中不存在可以连接的相同行的时候，我们可以使用“不等连接” 不等连接允许你将一个范围的最小值存储在记录的一列，这个范围的最大值存储在另一列第13页/共32页第十三页，编辑于星期六：十一点 二十三分。不等连接传统方法 books表和promotion

9、表之间的行不包含相等的值，所以你需要使用不等连接来确定客户将在促销活动中得到什么礼品。SELECT * FROM promotion;SELECT title,giftFROM books,promotionWHERE retailB E T W E E N m i n r e t a i l A N D maxretail;第14页/共32页第十四页，编辑于星期六：十一点 二十三分。不等连接JOIN方法SELECT title,giftFROM books JOIN promotionON retail BETWEEN minretail AND maxretail;第15页/共32页第十五

10、页，编辑于星期六：十一点 二十三分。练习2 订购图书SHORTEST POEMS 的客户将收到什么样的礼品？ 第16页/共32页第十六页，编辑于星期六：十一点 二十三分。自我连接 一个表中的数据引用了同一个表中存储的其他数据第17页/共32页第十七页，编辑于星期六：十一点 二十三分。自我连接传统方法 customers 表referred列存储了介绍该新客户的个人的客户号SELECT r.firstname,r.lastname,c.lastname referredFROM customers c,customers rWHERE c.referred = r.customer#;第18页/

11、共32页第十八页，编辑于星期六：十一点 二十三分。自我连接JOIN方法 用JOINON方法SELECT r.firstname,r.lastname,c.lastname referredFROM customers c JOIN customers rON c.referred = r.customer#;第19页/共32页第十九页，编辑于星期六：十一点 二十三分。外部连接 在执行相等连接，不等连接，和自我连接时，只有当查询的每一个表都包括一个对应的纪录时才会返回一行，这些连接都可归为“内部连接”，因为只有当每一个表中都存在匹配数据时，记录才会在结果中列出，事实上是JOIN关键字默认的INN

12、ER关键字，INNER JOIN 当需要在连接查询的结果中包括存在于一个表中但是在另一个表中没有对应记录时，就需要外部连接，使用关键字OUTER JOIN。也可以在带有“外部连接运算符+”第20页/共32页第二十页，编辑于星期六：十一点 二十三分。外部连接传统方法 下面是customers 和orders表匹配的记录SELECT lastname,firstname,order#FROM customers c,orders oWHERE c.customer# = o.customer#ORDER BY c.customer#;不匹配会怎样？不匹配会怎样？第21页/共32页第二十一页，编辑于

13、星期六：十一点 二十三分。外部连接传统方法 使用外部运算符（+）的外部连接SELECT lastname,firstname,order#FROM customers c,orders oWHERE c.customer# = o.customer#(+)ORDER BY c.customer#;结果中发现包含了NULL值，也就是说如果一个客户在customers表中，但最近没有下达订单，那么orders表中缺少对应的行或者是不完整的表（也就是说这个表缺少数据）。因此，将外部连接运算符（+）放在了引用不完整的orders表的where子句中的连接条件部分之后。第22页/共32页第二十二页，编辑

14、于星期六：十一点 二十三分。外部连接传统方法 注意规则1. 外部连接运算符只能用于连接条件中的一个表，换句话说，传统方式时，不能同时在两个表中创建NULL行2. 包括外部连接运算符的条件不能使用IN或OR运算符，因为这表明如果一行与其他表中的一行匹配或者满足其他某个指定的条件，那么它就显示在结果中。第23页/共32页第二十三页，编辑于星期六：十一点 二十三分。外部连接JOIN方法 可以随JOIN关键字包括LEFT，RIGHT，或FULL来指定关联类型SELECT lastname,firstname,order#FROM customers c LEFT OUTER JOIN orders o

15、ON c.customer#=o.customer#ORDER BY c.customer#;SELECT lastname,firstname,order#FROM customers c RIGHT OUTER JOIN orders oON c.customer#=o.customer#ORDER BY c.customer#;第24页/共32页第二十四页，编辑于星期六：十一点 二十三分。集合运算符 用来结合两个（或更多）SELECT语句的结 oracle中的集合预算符：UNION，UNION ALL，INTERSECT和MINUSUNION：返回两个查询的结果，如果存在重复记录，则将删

16、除它们UNION ALL：在结果中包含重复的记录INTERSECT：只列出两个查询都返回的记录MINUS：将从输出中删除第二个查询的结果第25页/共32页第二十五页，编辑于星期六：十一点 二十三分。集合运算符 需要customers表中最近下了订单的所有客户的列表SELECT customer# FROM customersUNIONSELECT customer# FROM orders;SELECT customer# FROM customersUNION ALLSELECT customer# FROM orders;SELECT customer# FROM customersINT

17、ERSECTSELECT customer# FROM orders;SELECT customer# FROM customersMINUSSELECT customer# FROM orders;第26页/共32页第二十六页，编辑于星期六：十一点 二十三分。连接三个或更多表连接三个或更多表传统方法SELECT title,firstname,lastnameFROM customers,orders,orderitems,booksWHERE customers.customer# = orders. customer#AND orders.order# = orderitems.orde

18、r#AND orderitems.isbn = books.isbnORDER BY title;第27页/共32页第二十七页，编辑于星期六：十一点 二十三分。连接三个或更多表连接三个或更多表JOIN方法 JOIN ON NATURAL JOINSELECT title,firstname,lastnameFROM customers JOIN orders ON customers.customer# = orders.customer#JOIN orderitems ON orders.order# = orderitems.order#JOIN books ON orderitems.isbn = books.isbnORDER BY title;SELECT title,firstname,lastnameFROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books ORDER BY title;第28页/共32页第二十八页，编辑于星期六：十一点 二十三分。练习3 生成居住在佛罗里达州并且订购了计算机图书的所有客户的列表.第29页/共32页第二十九页，编辑于星期六：十一点 二十三分。总结 为什么要进行多表连接 两个表之间的笛卡尔连接 相等连接。传统