常见sql for oracle
select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff3'),to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_date('2010-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') from dual;/*类型转换*/
select to_char(-485,'999') ,to_char(1485,'9,999'),to_char(1485.8,'99999.99') ,to_char(1485.8898,'99999.99')from dual;/*类型转换*/
select to_number('39202.88000','999999.99999') from dual;/*类型转换*/
select nvl('','null') from dual;/*空值转换*/
select decode(1,1,'man',2,'woman','unknow') from dual;/*条件翻译*/
select substr(message,length(message)-1,2) from tb_liuyi;/*右截取*/
select round(3.1459,2),trunc(3.1415926,3),floor(3.943883),ceil(3.94),abs(-999.9),0-9999.8 from dual;
select count(*) as rn , sendtime from tb_liuyi having count(*)>1 group by sendtime ;/*统计查询*/
select sid from tb_liuyi where rownum<=5; /*取前5条*/
select * from (select rownum as rn ,A.* from (select * from tb_liuyi) A where rownum <10) where rn>5;/*分页查询5-10*/
select * from ALL_TABLES where table_name=upper('tb_liuyi');/*表空间*/
select * from ALL_TAB_COLUMNS where table_name=upper('tb_liuyi');/*表结构*/
select * from ALL_CONSTRAINTS where table_name=upper('tb_liuyi');/*表约束*/
select * from ALL_CONS_COLUMNS where table_name=upper('tb_liuyi');/*行约束*/
select * from user_col_comments where table_name=upper('tb_liuyi');/行注释/
select * from user_tab_comments where table_name=upper('tb_liuyi');/表注释/
/*建表*/
drop sequence SEQ_MY_TABLE_ID;
drop table MY_TABLE cascade constraints;
drop table MY_TABLE;
CREATE TABLE MY_TABLE
(
sid NUMBER(16) NOT NULL PRIMARY KEY,
message varchar2(255),
sendtime date default sysdate ,
fee NUMBER(12.2),
mrk varchar2(1) default 'N' NOT NULL,
overtime timestamp default current timestamp
);
comment on column MY_TABLE.sid is '消息序号';
/*建立索引*/
create unique index MY_TABLE_IDX1 on MY_TABLE(
sid,
sendtime
);
--create sequence
create sequence SEQ_MY_TABLE_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
/*触发器*/
Create Or Replace Trigger Trig_liuyi
After Insert Or Delete Or Update On tb_liuyi For Each Row
Begin
If Inserting Then
Insert Into tb_liuyi_trig Values ('insert', Sysdate);
Elsif Updating Then
Insert Into tb_liuyi_trig Values ('update', Sysdate);
Elsif Deleting Then
Insert Into tb_liuyi_trig Values ('delete', Sysdate);
End If;
End;
大规模导入数据使用sqlldr（详情见网络），
另外，oracle提供块查询的接口，具体见网络。
