OCP SQL 学习笔记
看执行计划：set autot traceonly,退出看set autot off第6章 表连接
自然表连接：
1.nature join: 可省去where条件，会自动匹配列名相同的列，如有多列相同，不建议用此法
语法：select employee_name, department_id
fromemployees nature join departments
2.using: 只能匹配1列，与nature join互斥，不能连用.可用where，用连接字段再判断时不能加前缀
语法：select employee_name, department_id
fromemployees 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(子查询)
页:
[1]