分享：我的oracle 9i学习笔记(11)

########### Basic SQL SELECT ################

select col_name as col_alias from table_name ;

select col_name from table_name where col1 like "_o%"; ----"_"匹配单个字符

/*使用字符函数(右边截取,字段中包含某个字符,左边填充某字符到固定位数,右边填充某字符到固定位数)*/

select substr(col1,-3,5),instr(col2,"g"),LPAD(col3,10,"$"),RPAD(col4,10,"%") from table_name;

/*使用数字函数(往右/左几位四舍五入,取整,取余)*/

select round(col1,-2),trunc(col2),mod(col3) from table_name ;

/*使用日期函数(计算两个日期间相差几个星期,两个日期间相隔几个月,在某个月份上加几个月,某个日期的下一个日期,

某日期所在月的最后的日期,对某个日期的月分四舍五入，对某个日期的月份进行取整)*/

select (sysdate-col1)/7 week,months_between(sysdate,col1),add_months(col1,2),next_day(sysdate,"FRIDAY"),last_day(sysdate),

round(sysdate,"MONTH"),trunc(sysdate,"MONTH") from table_name;

/*使用NULL函数(当expr1为空取expr2/当expr1为空取expr2,否则取expr3/当expr1=expr2返回空)*/

select nvl(expr1,expr2),nvl2(expr1,expr2,expr3),nullif(expr1,expr2) from table_name;

select column1,column2,column3, case column2 when "50" then column2*1.1

when "30" then column2*2.1

when "10" then column3/20

else column3

end as ttt

from table_name ; ------使用case函数

select table1.col1,table2.col2 from table1

[CROSS JOIN table2] | -----笛卡儿连接

[NATURAL JOIN table2] | -----用两个表中的同名列连接

[JOIN table2 USING (column_name)] | -----用两个表中的同名列中的某一列或几列连接

[JOIN table2

ON (table1.col1=table2.col2)] |

[LEFT|RIGHT|FULL OUTER JOIN table2 ------相当于(+)=,=(+)连接,全外连接

ON (table1.col1=table2.col2)]; ------SQL 1999中的JOIN语法;

example:

select col1,col2 from table1 t1

join table2 t2

on t1.col1=t2.col2 and t1.col3=t2.col1

join table3 t3

on t2.col1=t3.col3;

select * from table_name where col1 < any (select col2 from table_name2 where continue group by col3);

select * from table_name where col1 < all (select col2 from table_name2 where continue group by col3);

insert into (select col1,col2,col3 form table_name where col1>; 50 with check option) values (value1,value2,value3);

MERGE INTO table_name table1

USING table_name2 table2

ON (table1.col1=table2.col2)

WHEN MATCHED THEN

UPDATE SET

table1.col1=table2.col2,

table1.col2=table2.col3,

...

WHEN NOT MATCHED THEN

INSERT VALUES(table2.col1,table2.col2,table2.col3,...); -----合并语句