--常用的字段类型有：varchar2,char,nchar,date,long,number,float,BLOB,CLOB--添加表字段alter table tablename add AREAID Number(18);--修改表字段alter table tablename modify SJLY varchar2(200);--删除表字段alter table 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 where 1=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) > 10 and b.sblsh=a.sblsh;--rownum 相当于SqlServer-->>top n *create table mytable as select * from laam_ex_bj where rownum<3;--复制表结构及数据到新表create table newTalbe as select * from oldTalbe;select * into newTalbe from oldTalbe; -- （仅用于SQlServer）insert into 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‘;--创建索引create index INDEX_Job on LBIDResidentJobInfo(XM, SFZH, scbj);create index INDEX_Legal on LBIDHouseAndLegalPerson(rkfrlegalpersonbaseid); --外键create index 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 from table A,(select max(adddate) adddate from table where table.title=A.title) B ;--两张关联表，删除主表中已经在副表中没有的信息delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 );--四表联查问题：select * from A left inner join B on A.a=B.b right inner join C on A.a=C.c inner join D on A.a=D.d where .....--一条 sql 语句搞定数据库分页--(仅用于SqlServer)select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段;--（Oracle写法）select b.* from (select id,sbsj from laam_ex_sb where rownum<=20 order by sbsj desc) a,laam_ex_sb b where a.id = b.id and rownum<=10 order by a.sbsj;select sqrmc from laam_ex_sb where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) group by sqrmc having count(sqrmc) > 1;--删除重复记录select count(*) 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‘) group by sqrmc having count(sqrmc) > 1) and id not in (select min(id) from laam_ex_sb where createtime>to_date(‘2014-09-21‘, ‘yyyy-mm-dd‘) group by sqrmc having count(sqrmc)>1);--随机取出10条数据select * from laam_ex_bj where rownum<10 order by sys_guid();select * from laam_ex_bj where rownum<10 order by dbms_random.value;--选择从10到15的记录--(仅用于SqlServer)select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc; --（Oracle写法）select * from (select * from laam_ex_bj where rownum<=15 order by id asc) laam_ex_bj where rownum<=5 order by id desc;--日程安排提前五分钟提醒select * from 日程安排 where datediff(‘minute‘,开始时间,getdate())>5;