常用管理SQL

更改列未使用

alter table testgroup set unused column n

删除表中所有未使用列

alter table testgroup drop unused columns

延时约束

Deferring constraints

级联约束

Cascading Constraints

在create statement中建立索引

CREATETABLE NEW_EMP(employee_idNUMBER(6)  

  PRIMARY KEY USINGINDEX

   (CREATE INDEX emp_id_idxON   NEW_EMP(employee_id)),

first_name  VARCHAR2(20),
last_name   VARCHAR2(25));

DROP TABLE dept80 PURGE;

FLASHBACKTABLE[schema.]table[,
[ schema.]table ]...

TO{ TIMESTAMP | SCN } expr

[{ ENABLE | DISABLE } TRIGGERS ];

一子查询为目标表

INSERTINTO (SELECT l.location_id, l.city, l.country_id

             FROM   locations l

             JOIN   countries c

             ON(l.country_id = c.country_id)

             JOIN regions USING(region_id)

             WHERE region_name = 'Europe')

VALUES(3300, 'Cardiff','UK');

with check option

insert into dept(departname)
values(default);

INSERT  ALL
   INTO sal_historyVALUES(EMPID,HIREDATE,SAL)
   INTO mgr_historyVALUES(EMPID,MGR,SAL)

   SELECT employee_id EMPID, hire_date HIREDATE,
          salary SAL, manager_id MGR

   FROM  employees
   WHERE employee_id > 200;

INSERT  ALL

 WHEN HIREDATE < '01-JAN-95' THEN

   INTO emp_history VALUES(EMPID,HIREDATE,SAL)

 WHEN COMM IS NOT NULL THEN

   INTO emp_sales VALUES(EMPID,COMM,SAL)

   SELECT employee_id EMPID, hire_date HIREDATE,

          salary SAL, commission_pct COMM

   FROM  employees

INSERT FIRST

WHEN salary < 5000 THEN

 INTO sal_lowVALUES (employee_id, last_name,salary)

WHEN salary between 5000 and 10000 THEN

 INTO sal_midVALUES (employee_id, last_name,salary)

ELSE

 INTO sal_highVALUES (employee_id, last_name,salary)

SELECT employee_id,last_name,salary

FROM employees

MERGE INTO table_nametable_alias

  USING (table|view|sub_query) alias

  ON (join condition)

  WHEN MATCHED THEN

    UPDATE SET

    col1 = col1_val,

    col2 = col2_val

 WHEN NOT MATCHED THEN

    INSERT (column_list)

    VALUES (column_values);