[Oracle]分享：我的oracle9i学习笔记（8）

文章作者 100test 发表时间 2007:03:14 13:54:41
来源 100Test.Com百考试题网

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

0select col_name as col_alias from table_name .

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

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

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

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

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

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

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

0select (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返回空)*/

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

0select 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函数

0select 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:

0select 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.

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

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

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

MERGE INTO table_name table1