常用Oracle操作语句

            --
            常用的字段类型有：varchar2,char,nchar,date,long,number,float,BLOB,CLOB
            --
            添加表字段
            alter
            table tablename add AREAID Number(18);
--修改表字段altertable tablename modify SJLY varchar2(200);
--删除表字段altertable tablename drop (AREAID);

--DBA_TABLES、ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息。
--DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USER_TAB_COLUMNS显示了每个数据库表的列的信息。select*from all_tables WHERE owner=‘SUPER‘;
--或者 SELECT*FROM dba_tables WHERE owner=‘SUPER‘;

select*from tab;--查询出所有的表及视图select*from user_views;--查询出当前用户下的所有视图--列出数据库里所有的表名
--(仅用于SqlServer)select name from sysobjects where type=‘super‘--U代表用户
--（Oracle写法）select*from user_tables --查询出当前用户下的所有表数据--列出表里的所有的列名
--(仅用于SqlServer)select name from syscolumns where id=object_id(‘tablename‘) ;
--（Oracle写法）select column_name,data_type,char_col_decl_length,data_precision,data_scale 
from user_tab_columns where table_name=‘tablename‘;

--这种写法主要是用在：Java代码动态加载where后面的条件。如 ：and name=‘小白‘ select*from talbeName where1=1;

--时间比较select*from tablename where updatetime>=to_date(‘2013-10-11‘, ‘yyyy-mm-dd‘) and updatetime<to_date(‘2013-11-30‘, ‘yyyy-mm-dd‘);

--时间加减select a.sblsh, a.sbsj,b.bjsj,a.sxmc,b.bjbmmc from laam_ex_sb a,laam_ex_bj b where trunc(b.bjsj)-trunc(a.sbsj) >10and b.sblsh=a.sblsh;


--rownum 相当于SqlServer-->>top n *createtable mytable asselect*from laam_ex_bj where rownum<3;

--复制表结构及数据到新表createtable newTalbe asselect*from oldTalbe;

select*into newTalbe from oldTalbe; -- （仅用于SQlServer）insertinto newTable(a, b, c) select d,e,f from oldTalbe; --前提是newTalbe事先已存在--用‘||‘符号拼接表字段信息select‘ALTER TABLE ‘||substr(table_name,0,length(table_name)-3)||‘ MODIFY SJLY varchar2(200);‘from user_tables where table_name like‘LBID%OLD‘;


--创建索引createindex INDEX_Job on LBIDResidentJobInfo(XM, SFZH, scbj);
createindex INDEX_Legal on LBIDHouseAndLegalPerson(rkfrlegalpersonbaseid); --外键createindex INDEX_House on LBIDhouseinfo(id); --主键--子查询select a,b,c from A where A IN (select d from B ); 
--或者select a,b,c from A where A IN (1,2,3);

--显示文章、提交人和最后回复时间select a.title,a.username,b.adddate fromtable A,(selectmax(adddate) adddate fromtablewheretable.title=A.title) B ;

--两张关联表，删除主表中已经在副表中没有的信息deletefrom table1 wherenotexists ( select*from table2 where table1.field1=table2.field1 );

--四表联查问题：select*from A leftinnerjoin B on A.a=B.b rightinnerjoin C on A.a=C.c innerjoin D on A.a=D.d where .....

--一条 sql 语句搞定数据库分页
--(仅用于SqlServer)selecttop10 b.*from (selecttop20 主键字段,排序字段 from 表名 orderby 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 orderby a.排序字段;
--（Oracle写法）select b.*from (select id,sbsj from laam_ex_sb where rownum<=20orderby sbsj desc) a,laam_ex_sb b where a.id = b.id and rownum<=10orderby a.sbsj;

select sqrmc from laam_ex_sb where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) groupby sqrmc havingcount(sqrmc) >1;
--删除重复记录selectcount(*) from laam_ex_sb 
where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) and sqrmc in (select sqrmc from laam_ex_sb where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) groupby sqrmc havingcount(sqrmc) >1) 
and id notin (selectmin(id) from laam_ex_sb where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) groupby sqrmc havingcount(sqrmc)>1);


--随机取出10条数据select*from laam_ex_bj where rownum<10orderby sys_guid();
select*from laam_ex_bj where rownum<10orderby dbms_random.value;

--选择从10到15的记录
--(仅用于SqlServer)selecttop5*from (selecttop15*fromtableorderby id asc) table_别名 orderby id desc; 
--（Oracle写法）select*from (select*from laam_ex_bj where rownum<=15orderby id asc) laam_ex_bj where rownum<=5orderby id desc;

--日程安排提前五分钟提醒select*from 日程安排 wheredatediff(‘minute‘,开始时间,getdate())>5;