第6章 OracleDML高级查询

Java程序员Oracle就业课程Lesson6高级查询主讲：杨昊Q：952063095

掌握集合运算掌握子查询掌握子查询所可以处理的各类问题掌握多表衔接查询运用自衔接让一个表衔接它本身掌握子查询处理程序中的各种业务逻辑目标I、集合运算集合运算符ABAAABBBUNION/UNIONALLINTERSECTMINUSEMPLOYEES:提供关于当前一切雇员的详细信息JOB_HISTORY:当一个雇员转换任务的时候，记录前面任务开场和终了的日期，以及任务的标识号和部门本课中运用的表本课中运用的表：UNION运算符UNION运算符从两个查询中前往消除反复之后的结果ABUNION运算符的运用显示当前和先前的一切雇员的的任务细节。每个雇员只显示一次UNIONALL运算符ABUNIONALL前往两个查询的结果，其中包括反复的UNIONALL运算符的运用显示当前和先前的一切雇员的部门INTERSECT运算符ABINTERSECT运算符的运用显示在开场公司的任期之前，拥有任务资历的雇员的IDs和任务IDsMINUS运算符ABMINUS运算符显示那些一次都没有转换任务的雇员IDsII、子查询运用子查询处理问题谁的工资比Abel高？主查询哪个雇员的工资比Abel高？Abel的工资是多少？子查询？子查询的语法子查询〔内部查询〕在主查询之前执行一次子查询的结果被用于主查询〔外部查询〕运用子查询单行子查询只前往一行结果运用单行比较运算符执行单行子查询在子查询中运用组函数在子查询中运用HAVING子句Oracle首先执行子查询Oracle前往结果到主查询的HAVING子句这个语句的错误是什么？这个语句将前往行吗？运算符含义IN与列表中的任一成员相等ANY与子查询返回的每一个值比较ALL与子查询返回的所有值比较多行子查询前往多行运用多行比较运算符在多行子查询中运用ANY运算符在多行子查询中运用ALL运算符子查询中的空值相关子查询子查询参考父查询表中的一列运用相关子查询找到那些收入超越他们部门平均薪酬的一切雇员每次从外部查询来的行被处置的时候，内部查询就被求值运用相关子查询显示那些至少曾经改换了两次任务的雇员EXISTS运算符检测子查询的结果集中行的存在假设一个子查询的行值被找到：—内部查询中的搜索将不会继续—条件将会标志为TRUE假设一个子查询的行值没有被找到：—条件将会标志为FALSE—内部查询中的搜索将会继续运用EXISTS运算符运用EXISTS运算符找到那些至少有一个人向他们报告的雇员运用NOTEXISTS运算符找到没有任何职员的部门III、多表衔接从多张表中获取数据EMPLOYEESDEPARTMENTS笛卡尔衔接等值衔接非等值衔接外部衔接自衔接衔接类型产生一个笛卡衔接EMPLOYEES(20行)DEPARTMENTS(8行)笛卡尔积：20x8=160行笛卡尔积在以下情况下产生：—忽略了一个衔接条件—一个衔接条件失效—第一张表的一切行和第二张表的一切行衔接为了防止笛卡尔积的产生，通常包含一个有效衔接条件的WHERE子句。笛卡尔积运用Oracle语法衔接表运用一个衔接去查询多张表的数据在WHERE子句中设定衔接条件当多张表的列重名时，以表名作为列名的前缀。等值衔接外键主键EMPLOYEESDEPARTMENTS运用等值衔接检索记录用表前缀在多张表中限定列名用表前缀提高系统性能用列别名区分不同表中同名的列限定运用二义性列名运用表别名用表别名简化查询用表名前缀提高性能两张表以上的衔接EMPLOYEESDEPARTMENTSLOCATIONS衔接n张表至少需求n-1个衔接条件。例如：衔接3张表至少要2个衔接条件非等值衔接EMPLOYEESJOB_GRADESEMPLOYEES表中的工资应介于JOB_GRADES表的最低和最高工资之间用非等值衔接检索记录外部衔接在ID为190的部门中没有任何雇员外部衔接语法利用一个外部衔接查询那些无法满足衔接条件的行外部衔接运算符是一个加号〔+〕wheree.dept_id(+)=d.id;+号在左边,已右边为准,左边补齐+号在右边,已左边为准,右边补齐外部衔接语法+号问题运用外部衔接自衔接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID表的自衔接运用SQL99语法引见运用一个衔接查询多张表的数据运用SQL99语法引见CrossjoinJoinOn()Leftouterjoinon()Rightouterjoin()Fullouterjoinon() CROSSJOIN产生了一个笛卡尔积〔Cartesianproduct〕，就象是在衔接两个表格时忘记参与一个WHERE子句一样

例： SELECTlast_name,dept_idFROMemp,dept;

例： SELECTename,locFROMempCROSSJOINdept;CROSSJOIN

ON子句被用于当在两个表中的字段名不一致时来衔接表

例：

SELECTdepartment_name,city

FROMdepartment,location

WHEREdepartment.location_id=location.loc_id;

SELECTdepartment_name,city

FROMdepartmentdJOINlocationl

ON(d.location_id=l.id);ON子句 在LEFTOUTERJOIN中，会前往一切左边表中的行，甚至在被衔接的表中没有可对应的列名的情况下也如此。

例：

SELECTlast_name,dept_id

FROMempe,deptd

WHEREe.department_id=d.department_id(+);

SELECTlast_name,dept_id

FROMemp

LEFTOUTERJOINDeptONe.dept_id=d.dept_id;

LEFTOUTERJOIN 在RIGHTOUTERJOIN中前往的是表中一切右边的行，甚至在被衔接的表中没有对应的情况下也如此。例：

SELECTlast_name,d.dept_id

FROMemployeese,departmentsd

WHEREe.department_id(+)=d.department_id; SELECTlast_name,d.dept_id

FROMemployeese

RIGHTOUTERJOINdepartmentsd

ON(e.department_id=d.department_id);

RIGHTOUTERJOIN FULLOUTERJOIN=LEFTOUTJOIN+RIGHTOUTJOIN例：

SELECTlast_name,d.dept_id

FROMemployeese，departmentsd

WHEREe.department_id=d.department_id〔+〕 UNIONALL SELECTlast_name,d.dept_id

FROMemployeese，departmentsd

WHEREe.depar