SQL FAQ 个人总结(结合oracle FAQ)

·                     The method that remove the duplicate rows from a table

1、delete from table_name

   where row_id>(

                 select min(row_id) from table_name B

                 where a.key_value=b.key_value);

2、create table table_name2

          as select distinct * from table_name1

   drop table_name1

   rename table_name2 to table_name1;

 

Note:Beacuse the table has been dropped from the table,the indexs、triggers、constrants etc also has been removed,we must establish them again.

3、delete table table_name1 tbl1

   where exists (select 'X' from table_name2 tbl2

                where tbl2.key_value1=tbl1.key_value2

                    tbl2.key_value2=tbl1.key_value1

                    tbl2.row_id>tbl1.row_id);

 

·                     How to use SYSDATE?

1、add "day/hour/minute/second" format value to a column

   select sysdate,sysdate+1 tomorow,sysdate+1/24 H_before,sysdate+1/1440 min_before,sysdate+30/86400 from table1;

 

2、Using "sysdate+7" To descripe one week before;

3、The midnight using "trunc(sysdate+1)";

4、Using "trunc(sysdate+1)+8/24" to descripe 8:00 AM the next day;

 

·                     Get the Nth column value only.

1、select * from table t1

 where N=(

          select count(row_id) from table t2

          where t1.key_value=t2.key_value

          t1.row_id>t2.row_id

          );

2、select * from (

    select rowMN rn from table1 where rn<101

    )

   where rn=100;

 

Note:The second SQL statement is better than the first,also far better than using the MINUS operation,the first must scan all the records,but the second not,only 101 records.

if the table consists of many records,the first SQL statement will expend large memory.

 

 

·                     Get rows N to M from a table.(N<101,M<101)

 

1、select * from (

         select col1,rownum RN from table1

         where RN>101)

   where rownum between N to M;

 

2、select rownum,col2 from table

   group by rownum,having rownum between N to M;

 

3、select rownum RN,col2 from (

                    select rownum,col2 from

                    (select * from table1 order by col3) a

                   where rownum<=M )

   where rownum>=N;

 

·                     Get the every Nth rows from a table

 

select * from table

group by rownum having mod(rownum,N)=0;

 

·                     Get the topN of the rows from a table

 

select * from

      (select * from table1 order by col1 desc) A

where A.rownum>N;

 

Note:The SQL statement can be used to oracles from oracle8i;

 

·                     Rename a column of the table

 

1、alter table table1 rename oldcolumnname to new columnname;

2、create table table2(list of the new columns) as (

                     select * from table1)

drop table table1;

rename table table2 to table1;

 

3、alter table table1 add(newcolumnname datatype);

update table1 set newcolumnname=oldcolumnname;

alter table table1 drop column oldcolumnname;

 

 

·                     Change the password of your Oracle.

 

alter user username identified by newpassword.

 

·                     The function of DECODE in Oracle SQL

 

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

 

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

 

Note:DECODE's function is same of comparetion expression,

    it search the result_X which match the expression,if YES,returns result_X,if NOT, returns default,if default left out,returns nothing.