第6章 Oracle_DML_高级查询

Java程序员Oracle就业课程Lesson6高级查询主讲：杨昊Q：952063095掌握集合运算掌握子查询掌握子查询所能够解决的各类问题掌握多表连接查询使用自连接让一个表连接它本身掌握子查询解决程序中的各种业务逻辑目标I、集合运算集合运算符ABAAABBBUNION/UNIONALLINTERSECTMINUSEMPLOYEES:提供关于当前所有雇员的详细信息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语法介绍CrossjoinJoinOn()Leftouterjoinon()Rightouterjoin()Fullouterjoinon()CROSSJOIN产生了一个笛卡尔积（Cartesianproduct），就象是在连接两个表格时忘记加入一个WHERE子句一样例：SELECTlast_name,dept_idFROMemp,dept;例：SELECTename,locFROMempCROSSJOINdept;CROSSJOINON子句被用于当在两个表中的字段名不一致时来连接表例：SELECTdepartment_name,cityFROMdepartment,locationWHEREdepartment.location_id=location.loc_id;SELECTdepartment_name,cityFROMdepartmentdJOINlocationlON(d.location_id=l.id);ON子句在LEFTOUTERJOIN中，会返回所有左边表中的行，甚至在被连接的表中没有可对应的列名的情况下也如此。例：SELECTlast_name,dept_idFROMempe,deptdWHEREe.department_id=d.department_id(+);SELECTlast_name,dept_idFROMempLEFTOUTERJOINDeptONe.dept_id=d.dept_id;LEFTOUTERJOIN在RIGHTOUTERJOIN中返回的是表中所有右边的行，甚至在被连接的表中没有对应的情况下也如此。例：SELECTlast_name,d.dept_idFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;SELECTlast_name,d.dept_idFROMemployeeseRIGHTOUTERJOINdepartmentsdON(e.department_id=d.department_id);RIGHTOUTERJOINFULLOUTERJOIN=LEFTOUTJOIN+RIGHTOUTJOIN例：SELECTlast_name,d.dept_idFROMemployeese，departmentsdWHEREe.department_id=d.department_id（+）UNIONALLSELECTlast_name,d.dept_idFROMemployeese，departmentsdWHEREe.department_id（+）=d.department_idSELECTlast_name,d.dept_idFROMemployeeseFULLOUTERJOINdepartmentsdON(e.department_id=d.department_id);FULLOUTERJOIN附加条件