OCP SQL 学习笔记

542505989

　　看执行计划：set autot traceonly,退出看set autot off
　　第6章 表连接
　　自然表连接：
　　1.nature join: 可省去where条件，会自动匹配列名相同的列，如有多列相同，不建议用此法
　　语法：select employee_name, department_id
　　from  employees nature join departments
　　2.using: 只能匹配1列，与nature join互斥，不能连用.可用where，用连接字段再判断时不能加前缀
　　语法：select employee_name, department_id
　　from  employees join departments
　　using (department_id)
　　3.on: 条件判断时如有别名，需加前缀
　　通用语法：select last_name, department_id
　　from employees e join departments d
　　on (e.department_id = d.department_id)
　　oracle语法：select last_name, department_id
　　from employees e, departments d
　　where (e.department_id = d.department_id)
　　自连接：
　　实例：SELECT worker.last_name emp, manager.last_name mgr
　　FROM   employees worker JOIN employees manager
　　ON    (worker.manager_id = manager.employee_id)
　　
　　非等价连接：
　　实例-给员工工资分级  select e.last_name, e.salary, j.grade_level
　　from   employees e join job_grades j
　　on     e.salary
　　between j.lowest_sal and j.highest_sal
　　
　　外连接：
　　1.inner join
　　2.left join
　　3.left outer join
　　通用语法：SELECT e.last_name, e.department_id, d.department_name
　　FROM   employees e left outer join departments d
　　ON   (e.department_id = d.department_id)
　　oracle语法：SELECT e.last_name, e.department_id, d.department_name
　　FROM   employees e ,departments d
　　ON   (e.department_id = d.department_id(+))
　　
　　3.right join
　　4.right outer join
　　通用语法：SELECT e.last_name, d.department_id, d.department_name
　　FROM   employees e right outer join departments d
　　ON    (e.department_id = d.department_id)
　　oracle语法：SELECT e.last_name, d.department_id, d.department_name
　　FROM   employees e, departments d
　　ON    (e.department_id(+) = d.department_id)
　　5.full join
　　6.full outer join
　　通用语法：SELECT e.last_name, d.department_id, d.department_name
　　FROM   employees e full outer join departments d
　　ON   (e.department_id = d.department_id)
　　oracle语法：SELECT e.last_name, d.department_id, d.department_name
　　FROM   employees e，departments d
　　WEHRE (e.department_id(+) = d.department_id)
　　AND (e.department is null)
　　注：能用UNION ALL的时候尽量不用UNION，UNION需要排序，消耗CPU，UNION ALL的效率比UNION高
　　
　　嵌套子查询：
　　1.in
　　通用实例：select e.empno, e.deptno
　　from emp e
　　where e.deptno in (select d.deptno
　　from dept d
　　where d.loc = ‘CHICAGO’)
　　更高效率的写法：select e.empno, e.deptno
　　from emp e， dept d
　　where (e.deptno = d.deptno) and  (d.loc = ‘CHICAGO’)   
　　总结：外连接比子查询效率高
　　
　　
　　笛卡尔集
　　多表连接，没有连接条件，或者连接条件少了，都会产生笛卡尔集
　　1.cross join
　　通用语法：SELECT last_name, department_name
　　FROM   employees
　　cross join departments
　　
　　第七章 子查询
　　注：单行子查询要用单行操作符，多行子查询要用多行操作符
　　用命令查看详细错误：
　　a.host
　　b.oerr 错误编号
　　
　　1.实例：查出department_id为null的员工信息
　　select * from employees
　　where sys_op_map_nonnull (department_id)=(select sys_op_map_nonnull(department_id)
　　from emplyees
　　where department_id = 178)
　　注：sys_op_map_nonnull 是内部函数，帮助文档里找不到，会把NULL值自动转换为‘FF’，即可找到department_id为null的employee
　　
　　2.实例: 各个部门有多少个员工
　　select dname， (select count(*)
　　from emp
　　where emp.deptno = dept.deptno) cnt
　　from dept
　　
　　3.标量查询实例
　　select XX
　　from dept
　　where XX = f()   --查询某个函数的返回值,有多少条就计算多少次，效率较低
　　效率高的方法：
　　select XX
　　from dept
　　where XX = (select f() from dual) --函数只查询一次，计入缓存，效率较高
　　4.any
　　> any(子查询) --大于任何一个值
　　< any(子查询) --小于任何一个值
　　= any(子查询) --相当于in
　　5.all
　　>all(子查询)