Oracle 常见问题整理，来自CSDN帖子

1、DDL, DML和DCL 的不同

(1)

DDL is Data Definition Language statements. Some examples: CREATE,ALTER,DROP,RUNCATE (emove all records from a table, including all spaces allocated for the records are removed),COMMENT(dd comments to the data dictionary ),GRANT,EVOKE(ithdraw access privileges given with the GRANT command )

(2)

DML is Data Manipulation Language statements. Some examples: SELECT,INSERT,UPDATE,DELETE(deletes all records from a table, the space for the records remain), CALL(call a PL/SQL or Java subprogram), EXPLAIN PLAN (explain access path to data ),LOCK TABLE (control concurrency )

(3)

DCL is Data Control Language statements. Some examples: COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like what rollback segment to use 

2.去除表中重复行。

（1） 

DELETE FROM table_name A WHERE ROWID > (       SELECT min(rowid) FROM table_name B        WHERE A.key_values = B.key_values);

（2）

create table table2 as select distinct * from table1;     drop table1;     rename table2 to table1;

（3) 

Delete from mytable where rowid not in(       select max(rowid) from mytable       group by column_name );

（4）

delete from mytable t1      where  exists (select 'x' from my_table t2                   where t2.key_value1 = t1.key_value1                     and t2.key_value2 = t1.key_value2                     ...                     and t2.rowid > t1.rowid);

3.得到一个表所有的索引信息

select * from ml_tindex('mytable');

4.generate primary key values for a table

CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;

UPDATE table_name SET seqno = sequence_name.NEXTVAL;

</pre><p></p></blockquote><p>5.对一列的值作范围内的统计</p><p></p><p><blockquote style="margin: 0 0 0 40px; border: none; padding: 0px;"><p><pre name="code" class="sql"> select jgbh,               sum(decode(greatest(age,59), least(age,100), 1, 0)) "年龄60-100",               sum(decode(greatest(age,30), least(age, 59), 1, 0)) "年龄30-59",               sum(decode(greatest(age, 0), least(age, 29), 1, 0)) "年龄0-29"        from   pepole        group  by jgbh;