--常用的字段类型有：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;
--删除重复记录
delete 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_sb where rownum<=15 order by rownum desc) laam_ex_sb where rownum<=5;
--日程安排提前五分钟提醒
select * from 日程安排 where datediff('minute',开始时间,getdate())>5;
--group by 用法
select sxmc,count(sxmc) 数量 from laam_ex_sb where 1=1 group by sxmc order by 数量;
select sxmc,sxbm,count(*) from laam_ex_sb where 1=1 group by sxmc,sxbm having count(*)>100;
select sxbm,sum(case when sxbm is not null then 1 else 0 end ) 总量 from laam_ex_sb where 1=1 group by sxbm order by 总量;
