之前说要定期更新自己学习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;