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;