经典sql语句

 

select * from t_insurance_rule where ins_id in(81,82,83) order by field(ins_id,82,83,81); 排序

sql拼接

select CONCAT('update t_insurance set thumbnail_image=''',thumbnail_image,''' where ins_id = ',ins_id)  from t_insurance where ins_id in (3);
select CONCAT('update t_insurance_image_url set list_image=''',list_image,''',detail_image=''',detail_image,''' where ins_id = ',ins_id)  from t_insurance_image_url where ins_id in (3);update t_user set birthday=REPLACE(birthday,'/','-') where birthday like '%/%';

select count(1),update_time from t_plus_achive_target_record where syn_flag = 2 and update_time like concat(substr(now(),1,10),'%') group by update_time;/*每天推送数据*/

mysql
删除重复数据，全部删除非去重
delete s from sheet1 s where s.id in (select a.id from ( select t.id from sheet1 t group by t.id having count(t.id) > 1) a )

 

select count(*),min(id),username from test group by username;
select * from t where id in (select a1.id from test a1,test a2 where a1.id>a2.id and a1.username = a2.username)
select a2.*,a1.* from test a1,test a2 where a1.username = a2.username;--自连接
select  *  from test t1 left join test_f t2 on t1.fid = t2.id where t2.id is null--左外连接

内连接  两个表都有的数据才显示，隐试的可以不用inner join,where条件里写连接条件
左连接  左表全部显示
右连接  右表全部显示
全连接  full join两表的数据都显示，匹配不上的为空

alter table customer_classify_info  add  classify varchar2(32)  --添加列
alter table test add (mobile varchar2(20),address varchar2(20))
alter table customer_classify_info  drop (sell_channel)  --删除列
alter table  mytest drop column mobile;
alter table customer_accessory_info modify auth_papers_file varchar2(256) -- 修改列类型
alter table syn_user rename column IFUSERLEVEL to LFUSERLEVEL --修改列名

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
alter table test add constraint pk primary key (id)--追加主键约束
alter table test add constraint uk unique (password)--追加唯一性约束
alter table test add constraint fk foreign key (fid) REFERENCE test_f(id);--追加外键
alter table test add constraint ck check(age between 20 and 33)--追加check约束
alter table test add check(password <> '1')
create index myindex on test(username) --追加索引
alter table test drop constraint SYS_C008562  --删除各种约束
drop index myindex --删除索引
alter table test add primary key (id) using index
alter table test modify password unique

add INDEX idx_push_flag(`push_flag`)

--查看约束名 主键约束、唯一性约束
select cu.* from user_cons_columns cu, user_constraints au where
cu.constraint_name = au.constraint_name and au.constraint_type = 'C' and au.table_name = 'TEST'
select * from user_cons_columns
select * from user_constraints

select * from all_tables where table_name like '%USER%';--查询所有table
--拼接sql语句
select 'drop table '||TABLE_NAME||';' from all_tables where last_analyzed > to_date('2014/1/17 00:00:00','yyyy/mm/dd hh24:mi:ss')
--取差集的形式 找出排序后的指定某一项
select * from (select * from emp order by empno desc) where rownum<=3
minus
select * from (select * from emp order by empno desc) where rownum<=2


 SELECT * FROM ALL_TABLES where owner = 'IAM20130726' AND table_name like '%E/_USER%' escape '/'

 select * from org_subacc where login_name='xmh'
 union all
 select * from org_subacc where login_name='Guest'//union all
 select sum(empno) from EMP ;//sum函数
 select * from emp where LOWER(ename) = lower('XmH3');//lower函数
 
select SYN_TIME from (select SYN_TIME from syn_info order by SYN_TIME desc) where rownum = 1
select * from sms_orderbill where  createtime between  to_date('2013-01-03','yyyy-mm-dd') and to_date('2013-01-04','yyyy-mm-dd');
select * from sms_orderbill where  userid > '2013-01-05 02:00:06'
select * from test where ( username is null or username like '% %' ) and id = '3'

//查主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'TEST'

1、查找表的所有索引（包括索引名，类型，构成列）：
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
2、查找表的主键（包括名称，构成列）：
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表
3、查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表
4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表
查询外键约束的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表

 



oracle中的递归查询
select * from region start with regionid = '12' connect by prior  regionid = supregionid ;
oracle中查询结果转换函数
select decode(t.state,0,'正常','异常') from table_test

CREATE SEQUENCE t_order_sequence
 INCREMENT BY 1   -- 每次加几个
     START WITH 1     -- 从1开始计数
     NOMAXVALUE       -- 不设置最大值
     NOCYCLE          -- 一直累加，不循环
     CACHE 10;
select t_order_sequence.nextval from dual;

select count(*),min(id),username from test group by username;
select * from t where id in (select a1.id from test a1,test a2 where a1.id>a2.id and a1.username = a2.username)
select a2.*,a1.* from test a1,test a2 where a1.username = a2.username;--自连接
select  *  from test t1 left join test_f t2 on t1.fid = t2.id where t2.id is null--左外连接

select * from dept order by name,age  for update
select t.*,rowid from dept t
insert into dept values ('4','name4','deptname4',4)
--删除指定的重复数据
delete from dept where deptname = 'deptname1'and rowid <> (select max(rowid) from dept t where deptname = 'deptname1')
select max(rowid) from dept t where deptname = 'deptname1'
--删除所有重复数据
select deptname from dept where deptname is not null group by deptname having count(*) > 1
select deptname from dept group by deptname having count(*) > 1

delete from dept where deptname in (select deptname from dept group by deptname having count(*) > 1)
and rowid not in( select max(rowid) from dept t group by deptname having count(*) > 1)

/*与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。*/

select t.*,sum(age) over(order by deptname) from dept t;--按name排序，连续叠加
select t.*,sum(age) over(partition by deptname) from dept t;--不连续叠加，按部门同一部门总和不变
select t.*,sum(age) over(partition by deptname order by name) from dept t;--按name排序，连续叠加，按部门
select t.*,row_number() over(partition by deptname order by name) from dept t;
select t.*,rownum  from dept t;

--row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
select t.*,count(*) over(partition by deptname order by name) from dept t;--实现分组内部排序，count(*)只是配合使用over
select count(*) from dept;

select t.*,row_number() over(order by name) from dept t;
select t.*,rownum from dept t order by t.name ;
--Lead()就是取当前顺序的下一条记录
select t.name,Lead(name) over(order by name),lag(name) over(order by name) from dept t;

--数值函数的使用
select sum(age) s from dept having sum(age) > 100 group by depname --聚合后筛选
select trunc(4.216,2) from dual--数值截取   
select round(4.216,2) from dual--数值精度
select mod(4,3) from dual--数值取余
select * from dual

--字符处理函数
select concat('sd','23') from dual
select 'sd'||'23'||'rt' from dual
select substr('asdfghj',3,3) from dual
select substr('asdfghj',length('asdfghj')-2) from dual
select instr('Hello World','or')from dual
select lpad('Smith',10,'*')from dual

--空值处理函数
select nvl(description,0) from dept
select nvl2(description,description||'has',0) from dept--如果第一个表达式为空就返回第三个表达式，不为空返回第二个表达式
select decode(name,'1','xmh01','2','xmh02','no') ee from dept;
select case name when '1' then 'xmh01' when '2' then 'xmh02' else 'no' end name from dept
select nullif(name,age) from dept;--如果第一个表达式与第二个表达式相等返回空值，否则返回第一个表达式的值

select * from (select rownum num,T.* from dept T where rownum <= 4) t1 where t1.num > 1
select * from dept where name in (select username from t_user)--in里的为基础表，数据较少
select * from dept d where exists (select 1 from t_user t where t.username = d.name)--exists里数据较大，外部的为基础表

select t.*,t2.* from test_xmh t left join test_xmh2 t2 on t.name = t2.name
select t.*,t2.* from test_xmh t,test_xmh2 t2 where t.name = t2.name(+)

SELECT WM_CONCAT(EXT_INFO) FROM IM_ITEM_INFO_EXT WHERE ITEM_NO='03000002'   --把行拼接起来
select 'insert into t_user (user_id,username,password) values ('''||user_id||''','''||username||''','''||password||''')' from t_user
 
Map hjMapPre = (Map)hibernateDao.queryForListWithSql(sql,new Object[]{bPreYear,bPreMonth,bPreYear,bPreMonth,bPreYear,bPreMonth}).get(0);
with fzbx as  select .... from      select f.xxx from fzbx f
删除重复数据
imp sgcis/sgcis@10.85.23.223:1521/sgcis file=c:\xxxx.dmp
exp sgcis/sgcis@sgcis76 file=d:/xxx.dmp tables= (SM_SMA_TMPL_GRA,SM_SMA_GRA_RULE)

f = 34.23632323f;
BigDecimal b = new BigDecimal(f);
f = b.setScale(3, BigDecimal.ROUND_HALF_UP).floatValue();//小数点后取两位

DecimalFormat format = new DecimalFormat("##0.000");
String str = format.format(f);

select t.*,t2.* from test_xmh t left join test_xmh2 t2 on t.name = t2.name
select t.*,t2.* from test_xmh t,test_xmh2 t2 where t.name = t2.name(+)

SELECT WM_CONCAT(EXT_INFO) FROM IM_ITEM_INFO_EXT WHERE ITEM_NO='03000002'   把行拼接起来

(float)(Math.round(f1*100))/100  //取float的小数点后两位
round  //四舍五入  取整

private String getStringFromClob(Object obj){
  SerializableClob sc = (SerializableClob)obj;        
     char[] buffer = null;
     try
     {            
      //根据CLOB长度创建字符数组   
      buffer = new char[(int)sc.length()];            
      //获取CLOB的字符流Reader,并将内容读入到字符数组中    
      sc.getCharacterStream().read(buffer);
      } catch (Exception e) { e.printStackTrace(); }       
       //转换为字符串        
      String str = String.valueOf(buffer);
      return str;
 }