第6章 Oracle_DML_高级查询.ppt

JavaOracle就业课程 Lesson6Q 952063095 掌握集合运算掌握子查询掌握子查询所能够解决的各类问题掌握多表连接查询使用自连接让一个表连接它本身掌握子查询解决程序中的各种业务逻辑 目标 I 集合运算 集合运算符 A B A A A B B B UNION UNIONALL INTERSECT MINUS EMPLOYEES 提供关于当前所有雇员的详细信息JOB HISTORY 当一个雇员转换工作的时候 记录前面工作开始和结束的日期 以及工作的标识号和部门 本课中使用的表 本课中使用的表 UNION运算符 UNION运算符从两个查询中返回消除重复之后的结果 A B UNION运算符的使用 显示当前和先前的所有雇员的的工作细节 每个雇员只显示一次 UNIONALL运算符 A B UNIONALL返回两个查询的结果 其中包括重复的 UNIONALL运算符的使用 显示当前和先前的所有雇员的部门 INTERSECT运算符 A B INTERSECT运算符的使用 显示在开始公司的任期之前 拥有工作资格的雇员的IDs和工作IDs MINUS运算符 A B MINUS运算符 显示那些一次都没有转换工作的雇员IDs II 子查询 使用子查询解决问题 谁的工资比Abel高 主查询 哪个雇员的工资比Abel高 Abel的工资是多少 子查询 子查询的语法 子查询 内部查询 在主查询之前执行一次子查询的结果被用于主查询 外部查询 使用子查询 单行子查询 只返回一行结果使用单行比较运算符 执行单行子查询 在子查询中使用组函数 在子查询中使用HAVING子句 Oracle首先执行子查询Oracle返回结果到主查询的HAVING子句 这个语句的错误是什么 这个语句将返回行吗 多行子查询 返回多行使用多行比较运算符 在多行子查询中使用ANY运算符 在多行子查询中使用ALL运算符 子查询中的空值 相关子查询 子查询参考父查询表中的一列 使用相关子查询 找到那些收入超过他们部门平均薪酬的所有雇员 每次从外部查询来的行被处理的时候 内部查询就被求值 使用相关子查询 显示那些至少已经更换了两次工作的雇员 EXISTS运算符检测子查询的结果集中行的存在如果一个子查询的行值被找到 内部查询中的搜索将不会继续 条件将会标记为TRUE如果一个子查询的行值没有被找到 条件将会标记为FALSE 内部查询中的搜索将会继续 使用EXISTS运算符 使用EXISTS运算符 找到那些至少有一个人向他们报告的雇员 使用NOTEXISTS运算符 找到没有任何职员的部门 III 多表连接 从多张表中获取数据 EMPLOYEES DEPARTMENTS 笛卡尔连接等值连接非等值连接外部连接自连接 连接类型 产生一个笛卡连接 EMPLOYEES 20行 DEPARTMENTS 8行 笛卡尔积 20 x8 160行 笛卡尔积在下列情况下产生 忽略了一个连接条件 一个连接条件失效 第一张表的所有行和第二张表的所有行连接为了避免笛卡尔积的产生 通常包含一个有效连接条件的WHERE子句 笛卡尔积 使用Oracle语法连接表 使用一个连接去查询多张表的数据 在WHERE子句中设定连接条件当多张表的列重名时 以表名作为列名的前缀 等值连接 外键 主键 EMPLOYEES DEPARTMENTS 使用等值连接检索记录 用表前缀在多张表中限定列名用表前缀提高系统性能用列别名区分不同表中同名的列 限定使用二义性列名 使用表别名 用表别名简化查询用表名前缀提高性能 两张表以上的连接 EMPLOYEES DEPARTMENTS LOCATIONS 连接n张表至少需要n 1个连接条件 例如 连接3张表至少要2个连接条件 非等值连接 EMPLOYEES JOB GRADES EMPLOYEES表中的工资应介于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 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 RIGHTOUTERJOIN FULLOUTERJOIN LEFTOUTJOIN RIGHTOUTJOIN例 SELECTlast name d dept idFROMemployeese departmentsdWHEREe department id d department id UNIONALLSELECTlast name d dept idFROMemployeese department