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;