OCP 1Z0-007笔记（2）

1--1set unused设字段为“不使用”(unused的字段无法再启用)
alter table table1 set unused (column2,column3);
alter table table1 set unused  column column1;   --用这个语法不可以一
次1个以上字段
--2drop unused清除已经unused的字段
alter table table1 drop unused column;
--3add constraint(或modify)添加约束
alter table table1 modify column1 primary key;
alter table table2
add constraint fk1
foreign key(column4)
references table1(column1) on delete cascade;
alter table table2
add constraint fk1
foreign key(column4)
references table1(column1) on delete set null;
--????deferrable             not deferrable
--????initially deferred     initially immediate
--4drop constraint删除一个约束
alter table table2 drop constraint fk1;
alter table table1 drop constraint pk1;
alter table table1 drop primary key cascade;
--5disable constraint禁用一个约束
alter table table1 disable constraint pk1;
--6enable constraint重新启用一个约束
alter table table1 enable constraint pk1;
--7cascade constraints
alter table table1 drop column column1          cascade constraints;
alter table table1 drop        (column1,column2)cascade constraints;
--8在建表时建索引
create table table1
(column1 number primary key using index
           (create index i1 on table1(column1)),
column2 number,
column3 varchar2(2));
--9以函数为基础的索引
create index i1 on table1(upper(column1));
select * from table1 where upper(column1)='ABC';
--10drop index移除一个索引
drop index i1;
--11drop table ... purge删除表不放入回收站
drop table table1 purge;
--12flashback table闪回已删除的表
flashback table table1 to before drop;
--13外部表????????????????????????????
--14insert时的with check option选项
insert into
(
select employee_id,last_name,email,
       hire_date,job_id,salary
from employees
where department_id=50
with check option)--向结果中插入值时检查插入的值是否符合结果的要求
values
(
9998,'smith','jsmith',
to_date('07-01-99','dd-mm-yy'),
'st_clerk',5000)
--ORA-01402:view with check option where-clause violation
--ORA-01402:视图 with check option where 子句违规
--15default在insert和update中的应用
insert into table1
(column1,column2)
values
(23,default);
update table1
set column2=default
where column1=21;
--16多表插入语句(无条件insert)
insert  all
into table2 values(column1,column2)
into table3 values(column2,column3)
select column1,columnb2,column3
from table1
where column1=99;
--17多表插入语句(有条件all insert)
insert all
when c1>10 then into t2 values(c1,c2,c3)  --c1>10是指t1的c1
when c1<8  then into t3 values(c1,c2,c3)  --c1<10是指t1的c1
else
                into t4 values(c1,c2,c3)
select * from t1
--这将把t1中所c1>10的行插入t2
--          所c1<8 的行插入t3
--               其它行插入t4
--18多表插入语句(有条件 first insert)????????????????????????????
insert first
when c1>10 then into t2 values(c1,c2,c3)  --c1>10是指t1的c1
when c1<8  then into t3 values(c1,c2,c3)  --c1<10是指t1的c1
else
                into t4 values(c1,c2,c3)
select * from t1
--19pivoting insert????????????????????????????
INSERT ALL
  INTO sales_info VALUES (employee_id,week_id,sales_MON)
  INTO sales_info VALUES (employee_id,week_id,sales_TUE)
  INTO sales_info VALUES (employee_id,week_id,sales_WED)
  INTO sales_info VALUES (employee_id,week_id,sales_THUR)
  INTO sales_info VALUES (employee_id,week_id, sales_FRI)
  SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
         sales_WED, sales_THUR,sales_FRI
  FROM sales_source_data;
--20merge更新表
merge into t2 t --要更新的目标表
using t1 s --源表
on t.id=s.id --更新条件（即主键）
when matched --如果主键匹配，更新
then update set t.name=s.name
when not matched
then insert values(id,name);--这里的id,和name是指t2的(很神吧)
merge into t2
using t1
on( t2.c1=t1.c1)
when matched
then update set t2.c3='@@'
when not matched
then insert values(c1,999,'99')
--21VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE????????????????????