多表查询和子查询

第四部分：多表查询和子查询l多表查询：在作数据查询操作时，经常出现所要的数据不在一个表中，这就需要多表查询， 在关系型数据库中将相关的信息保存在不同的表中，避免了数据的冗余的发生，通常在关系 型数据库中，表与表之间都是有关系的，建立关系的字段称为关系字段，通常是主键表中的 主键和外键表中的外键。多表查询通常是将两个表或多个表中关系字段的值相等的记录连接 成一条新记录出现在结果集中。 1、笛卡尔集： 在多表查询中出现的笛卡尔集现象：笛卡尔集就是：如果有两个表，一个表中的一条记录和 另外一个表中的每一条记录都会组成一条新的记录出现在结果集中这样结果集的总记录数就 是m×n条记录。在多表查询中出现这样的现象是我们不愿意看到的。出现笛卡尔集的原因是 多表查询中没有连接条件。 2、Oracle中多表连接主要有以下几种： （1）内连接：分为等值连接和不等值连接、自然连接。 （2）外连接：分为左连接和右连接。 （3）自连接：一个表中的一条记录和本表中其他的记录相连接。 3、等值连接：称为简单连接或内连接，是连接查询中最常用的一种连接方式。 （1）语法如下： Select table1.column_name,table2.column_name, From table1 inner join table2 On table1.column_name=table2.column_name innerjoin Where condition第四部分：多表查询和子查询等值连接等值连接 sales titles 外键主键第四部分：多表查询和子查询（2）例如：查询被销售过的图书的书号，书的类型，价格销售量和销售日期 Select titles.title_id,type_,price,ord_date From titles join sales on titles.title_id=sales.title_id; 注意： 在进行多表连接查询时，如果多个表中具有同名字段，应该标明同名字段的所 属表，可以用“表名.”加以限定。也可以采用给表起别名的形式加以限定，这样 可以简化编程，提高效率。如： Select t.title_id,t.type_,t.price,s.ord_date From titles t join sales s on t.title_id=s.title_id; （3）独立练习： 1）显示所有员工的姓名，部门号和部门名称 。 2）查询所有有奖金的员工的员工号，员工名，部门号和部门名信息。 3）查询被销售过的图书的书号、书名、出版社名，作者名，销售日期和销售总量 。 注意：在内连接中可以不使用join 语句：如： Select t.title_id,t.type_,t.price,s.ord_date From titles t,sales s where t.title_id=s.title_id; 将以上3了练习改写成没有join on连接的形式第四部分：多表查询和子查询4、不等值连接：在两个表中并不存在关键字段，但是根据业务的需要 也可以进行连接查询： 如查询员工的姓名、工资及工资级别。 select ename,sal,grade from emp,salgrade where sal between losal and hisal; 5、自然连接： NATURAL JOIN 子句，会以两个表中具有相同名字的列做为 条件创建等值连接，而不用指定关键字段的对等值，在表中查询所有满足等 值条件的数据。 注意：如果只是列名相同而数据类型不同或者在查询中出现列的限定词，则 会产生错误。 如： select title_id,type_,s.qty,ord_date from titles natural join sales where price 20;第四部分：多表查询和子查询6、自连接：自连接时内连接的一种特例，一个表中的记录进行连接的情况，如查询和bu1032号图书类型相同的书的书号书名 价格，包括bu1032号图书的信息 select distinct t1.title_id,t1.title,t1.price,t2.title_id,t2.title,t2.price from titles t1,titles t2 where t1.price=t2.price and t1.title_id='BU1032' 观察结果集中的数据：并进行分析，看看结果集中的数据是否都满足业务需要，如果不需要该怎样改写。 7、外连接：外连接分为两种，一是左连接，二是右连接；外连接时需要确定两个表中那个表是主表。 （1）外连接的语法格式：用“”表示缺乏表，即不包含匹配值的表称为“缺乏表”。 SELECT table1.column, table2.column FROMtable1, table2 WHERE table1.column(+) = table2.column;（右连接） SELECTtable1.column, table2.column FROMtable1, table2 WHERE table1.column = table2.column(+);（左连接）或： SELECT table1.column, table2.column FROMtable1 left outerjoin table2 ontable1.column = table2.column;（左连接） SELECT table1.column, table2.column FROMtable1 rightouter join table2 ontable1.column = table2.column;（左连接）第四部分：多表查询和子查询（2）实例： 1）查询所有图书的销售情况（两种语法形式都要用到） A、select t.title_id,t.type_,s.qty,s.ord_date from titles t , sales s where t.title_id=s.title_id(+); B、。 2）查询所有以销售过的图书的书号书名销售日期和销售量 A、select t.title_id,t.type_,s.qty,s.ord_date from titles t , sales s where t.title_id (+) =s.title_id; B、。第四部分：多表查询和子查询8、补充： 联合查询：可以用关键字union将多个select结果集合在一起，union将来自不同的select命令所产生的数 据混合在一起，并且将重复的记录删除剩下一条，如果不想删除重复记录可使用union all命令。也可以 将查询获得的数据储存在一个新表中。 例如：查询书号为7067和7066号图书销售店的销售信息，合并分别一个结果集。 select * from sales where stor_id=7066 union select * from sales where stor_id=7067 ; 9、补充：ANSI SQL 1999连接查询的语法如下： SELECT table1.column, table2.column FROM table1 CROSS JOIN table2 |-交叉连接生成笛卡尔集 NATURAL JOIN table2 |-自然连接 JOIN table2 USING (column_name) |-使用using语句 JOIN table2 ON(table1.column_name = table2.column_name) | LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name);第四部分：多表查询和子查询（1 1）CROSS JOINCROSS JOINl使用CROSS JOIN 子句使连接的表产生叉积。l叉积和笛卡尔积是相同的。 SELECT last_name, department_name FROM employees CROSS JOIN departments ;第四部分：多表查询和子查询（2 2）使用）使用 USINGUSING 子句创建连接子句创建连接l在NATURAL JOIN 子句创建等值连接时，如果有多个名字相同，数 据类型不同的列，那么可以使用 USING 子句指定等值连接中需要用 到的列。l使用 USING 可以在有多个列满足条件时进行选择。l不要给选中的列使用表名前缀或别名。lNATURAL JOIN 和 USING 子句经常同时使用。SELECT e.employee_id, e.last_name, department_id FROM employees e JOIN departments d USING (department_id) ;第四部分：多表查询和子查询10、连接查询的小练习（内连接） （1）查询1994年销售的图书相关信息，包括书名、价格、销售日期、销售数量信息 （2）查询所有员工信息，包括员工姓名、工作描述、工资、雇用日期,所在出版社名称及其地址信息 （3）查询书号是 BU1032符号图书的书名、价格及作者姓名 （4）查询PS3333号图书的书名、价格、作者、出版社名称的信息 （5）查询在NEW YORK工作的员工的姓名，工资，奖金部门名称，工资级别信息第四部分：多表查询和子查询l子查询：通过以上章节的学习，我们掌握了基本查询语句，条件查询，单行函 数，分组函数及分组查询，以及多表 查询等功能，基本上能满足工作的需要， 但是有些需求要通过复杂查询来实现，这就用到比较复杂的，有些不好掌握的 查询子查询。子查询就是出现在基本查询结构中的select语句称为子 查询, 子查询必须用小括号括起来，括号内的查询称为子查询或内部查询，外部的查 询称为主查询，执行的顺序是先执行子查询，然后再执行主查询。 1、子查询的语法结构： Select 查询列 From 表名 Where 列名 操作符 （select 查询列from 表名 ）;说明：这个子查询的结果可以作为外部查询的条件。 例如： （1）查询和SCOTT同在一个部门的所有员工的信息，包括工号、姓名、工资、奖 金以及部门号、部门名称。第四部分：多表查询和子查询Select e.empno,e.ename,e.sal,e.comm,e.deptno,d.dname From emp e,dept d Where e.deptno=d.deptno and e.deptno=(select deptno from empwhere ename='SCOTT'); （2）查询价格小于平均价格的图书信息和销售信息，包括书号、书名、类型、价格、销售日 期和销售量的信息。 select t.title_id,t.title,t.type_,t.price,s.ord_date,s.qty from titles t,sales s where price、=、。 （1）出现在where字句中的子查询：例如，查询和SCOTT同在一个部门并且工资大 于SCOTT工资的员工信息包括姓名、部门号，工资及奖金信息。（独立完成）略。 （2）出现在having字句中的子查询：例如：需要对公司公司的人数进行统计，需要 找到员工人数高于公司各个部门平均人数的部门信息 select deptno,count(empno) from emp group by deptno having count(empno)(select avg(count(empno)from empgroup b