oracle9i学习笔记

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,...); -----合并语句

##################### CREATE/ALTER TABLE #######################

alter table table_name drop column column_name ;---drop column

alter table table_name set unused (col1,col2,...);----设置列无效，这个比较快。
alter table table_name drop unused columns;---删除被设为无效的列

rename table_name1 to table_name2; ---重命名表

comment on table table_name is 'comment message';----给表放入注释信息

create table table_name
(col1 int not null,col2 varchar2(20),col3 varchar2(20),
constraint uk_test2_1 unique(col2,col3))); -----定义表中的约束条件

alter table table_name add constraint pk_test2 primary key(col1,col2,...); ----创建主键

/*建立外键*/
create table table_name (rid int,name varchar2(20),constraint fk_test3 foreign key(rid) references other_table_name(id));

alter table table_name add constraint ck_test3 check(name like 'K%');

alter table table_name drop constraint constraint_name;

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页    >>