OCP SQL 学习笔记
看执行计划：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(子查询)
<all(子查询)
=all(子查询) --无意义，没有等于all，就是查询全部
6.exist / not exist:是效率最高的去重方法
7. in / not in:如果子查询里有任何一条为NULL，主query里查出来的也全都是null，下面的方法可以解决这个问题
实例：select emp.last_name
from employee emp
where emp.employee_id in (select emp.employee_id
from employees emp
where emp.employee is not null
and emp.department_id = 123)
总结：相关子查询比外连接的效率高，相关子查询指外部查询的列在子查询的where条件中用到
第八章 集合
1.UNION / UNION ALL：查出全部，UNION会去掉重复记录，但先要排序再工作，效率较低。UNION ALL会把重复记录也查出来，不排序，但效率较高
2.INTERSECT:把重复记录查出来，尽量少用，效率较低。先排序A，再排序B，在结合起来找相同的记录,可以用UNION ALL/exist代替，后者效率较高
3.MINUS:二者之间不同的地方查出来，相当于A表减B表
4.order by:默认用查出的第一列升序ASC排序
总结:union/intersect/miuns对NULL值的处理
实例： select 1,2,null from dual
union
select 1,2,null form dual
union/intersect/miuns：会合并成同一行，把NULL值作为相同的值进行处理
第九章熟练操作数据
1.insert/update/delete
2.merge 适用于批处理，有效防止
语法 merge int tb_name
using (table|view|sub_query)
on (join condition)
when matched then
update set ---无需加列名
col1 = col1_value
col2 = col2_value
when not matched then ---10g以后这段，如果无需insert可以不加
insert (column_list) values(column_values)
3.delete和trancate，前者是DML语句后者是DLL语句，delete可以rollback,trancate删完以后释放空间
4.save point: 断点保存，可以部分回滚
5.commit/rollback:set autocommite off的情况下，用于事务，保证数据的一致性
注：DDL/DCL会自动提交，或者退出sqlplus没有提交的情况下，也会自动提交。如果系统意外中断，会自动回滚
6.for update:先锁定查出来的记录，用来update/delete，但是其他用户不可以对这些数据进行任何其他update/delete操作,必须rollback/commit才能释放
语法：SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;
另一种情况：SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE nowait
ORDER BY employee_id;
注：加上nowait，当别的用户也使用这些记录时，报错通知用户此数据正在被占用，节省等待时间
第九章 表管理
1.约束
语法 alter table t add constraint t_pri primary key(id)[on delete cascade]
on delete cascade:当删除子表的某列时，主表里这个字段也相应删除，但会产生级联情况，用起来危险
2.create table XXX as (sub_query)
所有sub_query里的not null约束，会自动继承到新表里，主键/唯一/外键约束都不会继承
3.11g新功能，可以使整个table ready only/ready write
4. 表drop掉以后，如何找回
flashback table "垃圾箱里对应的名字" to before drop
不想以后找回，永久drop:drop table table_name perge;
2.索引
最多24层，层层消耗一样，称为balance
