业务中常用的sql语句

      之前说要定期更新自己学习oracle数据库的笔记，但是因为种种原因没有继续更新。今天把自己在一个月的工作中常用到的sql 语句做了一个整理，主要是增删改查语句，也算是温故而知新吧！

（1）查询表的记录总数：
select count(*) from tb_smp_sms_history_201403 //查询表的记录总数
（2）条件查询语句：

【1】基本的条件查询：

select *  from tb_smp_sms_history_201404 t  where  id  between 3882423940 and 3882423982

select count(*) from  TB_SMP_SMS_HISTORY_201403 where latn_id=931;//条件单条查询统计

select count (Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'; //为查询的字段命名

select l.latn_id,s.sys_code from TB_SMP_SMS_HISTORY_201403 l,TB_SMP_SMS_HISTORY_201402 s where l.latn_id=s.sys_code;

select count(1) from TB_SMP_SMS_HISTORY_201404  t
where t.sent_end_time>=to_date('2014-04-17 16:00:00','yyyy-mm-dd hh24:mi:ss') 
and t.sent_end_time<=to_date('2014-04-17 16:45:00','yyyy-mm-dd hh24:mi:ss');

【2】直接关联查询：

select t1.latn_id,t2.code_name, count(*) "记录数"from TB_SMP_SMS_HISTORY_201403 t1,tb_ecp_code t2
where t1.latn_id=t2.code_code and t2.code_pid='109'group by t1.latn_id,t2.code_name order by count(*);//直接关联查询

select t1.latn_id,t2.code_name, count(*) "记录数"from TB_SMP_SMS_HISTORY_201403 t1,tb_ecp_code t2
where t1.latn_id=t2.code_code and t2.code_pid='109'group by t1.latn_id,t2.code_name order by count(*);

select t1.sys_code,t2.code_name ,count(*) "记录数"from TB_SMP_SMS_HISTORY_201403 t1,tb_ecp_code t2
where t1.sys_code=t2.code_code and t2.code_pid='108' group by t1.sys_code,t2.code_name  order by count(*);//直接关联查询

【3】常用的查询语句：

1)group by //用于多组查询的关键字
select latn_id,count(*) from  TB_SMP_SMS_HISTORY_201403 group by  latn_id;//多条查询统计

select business_id, count(*) from tb_smp_sms_history_201404  where sys_code='002'
and to_char(sent_bg_time,'yyyymmdd')=20140416 group by business_id; //根据所属系统查询场景ID

2)order by //用于的排序关键字
desc//降序排列关键字
asc//升序排列关键字
select latn_id,count(*) from  TB_SMP_SMS_HISTORY_201403 group by  latn_id order by latn_id ;//多条查询排序统计

3)case when//条件选择匹配语句
select t1.sys_code,
       t2.code_name,
       t1.priority,
       case when t1.priority=3 then '高级'
            when t1.priority=2 then '中级'
            when t1.priority=1 then '低级'
            else '其他' end,
       count(*) "记录数"
  from TB_SMP_SMS_HISTORY_201403 t1,
       tb_ecp_code t2
 where t1.sys_code = t2.code_code
   and t2.code_pid = '108'
 group by t1.sys_code, t1.priority, t2.code_name
 order by count(*),
       case when t1.priority=3 then '高级'
            when t1.priority=2 then '中级'
            when t1.priority=1 then '低级'
            else '其他' end;

4)like//模糊匹配
select  * from TB_SMP_SMS_HISTORY_201403 where to_tel='18993565532' and send_content like '%上网流量%';

select * from tb_smp_sms_history_201403 t where to_tel='18919089831' and send_content like '%3.6MB%407%';

5)in//包含条件
select * from tb_smp_sms_history_201403 t where to_tel='18919089831' and latn_id in ('931','941');

6)or//选择条件，满足其中的任意一件就可以(> 、>= 、< 、<=、<>、!=：不等符号)
select * from tb_smp_sms_history_201403 t where to_tel='18919089831' and (latn_id=931 or latn_id=941);

7)between and//在两个条件之间
select * from tb_smp_sms_history_201404 t  where id between 3882423940 and 3882423982;

8)substr//截取字符串
select  substr('abcdees213212d',1,7) from dual;
select  substr(to_char(begin_date,'yyyy-mm-dd'),6,2) from TB_SMP_SMS_HISTORY_201404;
select  substr('abcdees213212d',1,7) from dual;
select  substr(to_char(begin_date,'yyyy-mm-dd'),6,2) from TB_SMP_SMS_HISTORY_201404;

9)sysdate//获取系统的当前时间

10)日期型和字符型的相互转换
select to_char(begin_Date,'yyyy-mm-ddss') hh:mi: from TB_SMP_SMS_HISTORY_201404;//把日期型转换成字符型
select to_char(begin_Date,'yyyy-mm-ddss') hh:mi: from TB_SMP_SMS_HISTORY_201404 ;
select to_date('20140331085423','yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'mm-dd-yyyy') from dual;
select to_char('123456789') ,123456789 from dual;
select  * from TB_SMP_SMS_HISTORY_201404 where sys_code='009' and to_char(begin_date,'yyyy-mm-dd') ='2014-04-06'
select to_date('20140331085423','yyyy-mm-dd hh:mi:ss') from dual;

11）insert into//插入语句
insert into tb_ecp_inf_bill_20140417 select * from tb_ecp_inf_bill
where to_char(create_date,'yyyymmdd')<20140416 or create_date is null;

insert into goods_zwj_20140321 values ('006','学习用品','签字笔',to_date('2014-3-21','yyyy-mm-dd'),6.0,'2000','适用于各类人群');//把日期型转换成字符型插入表
insert into tb_ecp_inf_bill_20140417 select * from tb_ecp_inf_bill
where to_char(create_date,'yyyymmdd')<20140416 or create_date is null;

12）delete //删除语句
delete from tb_ecp_inf_bill where to_char(create_date,'yyyymmdd')<20140416 or create_date is null;

13)update //更新语句
update tb_smp_sms set put_time=create_time where put_time is null;