一、查询操作
1.1 查询单表语句
select t.*, t.rowid from Table t;
1.2 查询多表关联语句
select T.id NVL(T.nesid,0) nesid,T2.name FROM Table T,(select * from Table2 where id>10) T2 WHERE T.fid =T2.id(+);
或者:
select T.id NVL(T.nesid,0) nesid,T2.name FROM Table T left join (select * from Table2 where id>10) T2 on T.fid =T2.id;
1.3 查询表100~200条
select * FROM (SELECT A.*, rownum r FROM (select t.* from Table T where T.ID>5 ORDER BY T.ID desc) A WHERE rownum <=200 ) B WHERE r >=100;
1.4 查询表的重复记录
select * from temp_111 t WHERE ROWID!=(SELECT MAX(ROWID) FROM temp_111 t1 WHERE t.username=t1.username);
1.5 查询一张表存在的数据而另一张表中没有的数据
select * from temp_111 t where t.userid not in (select distinct t1.e_user_id from hr_employee t1)
二、插入操作
2.1 插入表数据(查询满足条件的结果导入到表中)
insert into temp_111(userid,username) select T.value1,T.value2from Table T where T.id>1;
2.2 备份表
create table temp_20181119 as (select T.* from Table T where T.id=1);
三、更新操作
3.1 更新单表数据
update temp_111 set username=‘张三‘ where userid=1740;
3.2 更新表数据(根据字段更新另一张表)
update temp_111 t set t.username=(select t1.e_realname from Table t1 where t1.userid =t.userid)
四、删除删除
4.1 删除表的重复记录
delete from temp_111 t WHERE ROWID!=(SELECT MAX(ROWID) FROM temp_111 t1 WHERE t.username=t1.username);
4.2 删除表的重复记录(保留最大一条)
delete from temp_111 a where a.createdate < (select max(b.createdate) from temp_111 b where a.linkid=b.linkid);
4.3 清空表(保留该表,删除表所有记录)
truncate table temp_2018_1119;
4.4 删除记录(删除部分记录)
delete from temp_2018_1119 T where T.isdel=1;
4.5 直接删除表
drop table temp_2018_1119;
五、日期查询
5.1 常用时间格式
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘)currenttime, --2018-11-11 15:51:01
to_char(sysdate,‘yyyy‘) year, --2018
to_char(sysdate,‘mm‘) month, --11
to_char(sysdate,‘dd‘) day, --11
to_char(sysdate,‘day‘) week, --星期日
to_char(sysdate,‘hh24‘)hour, --15
to_char(sysdate,‘mi‘) minute, --51
to_char(sysdate,‘ss‘) second --01
from dual;
5.2 查询某个时间段
SELECT * FROM Table t WHERE 1=1 AND t.create_time >=to_date(‘2018-8-22 8:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) and t.create_time < to_date(‘2020-8-22 12:30:00‘,‘yyyy-mm-dd hh24:mi:ss‘);
5.3 查询当天
select TRUNC(SYSDATE) from dual;
5.4 查询1天前数据
select T.* from Table T where T.E_SEX=1 and TRUNC(T.CREATE_TIME)> TRUNC(SYSDATE - 1);
5.5 查询上个月日期
select add_months(trunc(sysdate),-1) from dual;
5.6 查询上个月1号
select TRUNC(add_months(trunc(sysdate),-1),‘MM‘) from dual;
六、常用函数
6.1 处理Null值:nvl(空值,代替值)
select nvl(t.isnull,1) value from dual; --(1)
6.2 绝对值:abs()
select abs(-2) value from dual; --(2)
6.3 取整函数(大):ceil()
select ceil(-2.001) value from dual; --(-2)
6.4 取整函数(小):floor()
select floor(-2.001) value from dual; --(-3)
6.5 取整函数(截取):trunc()
select trunc(-2.001) value from dual; --(-2)
6.6 四舍五入:round()
select round(1.256,2) value from dual; --(1.26)
七、字符函数select substr(‘abcdefg‘,1,5)substr, --字符串截取 abcde instr(‘abcdefg‘,‘bc‘) instr, --查找子串 2 ‘Hello‘||‘World‘ concat, --连接 HelloWorld trim(‘ wish ‘) trim, --去前后空格 wish rtrim(‘wish ‘) rtrim, --去后面空格 wish ltrim(‘ wish‘) ltrim, --去前面空格 wish ascii(‘A‘) A1, --ascii(转换为对应的十进制数) 65 chr(65) C1, --十进制转对应ascii编码 A length(‘abcdefg‘) len, --length 7 lower(‘WISH‘)lower, --转小写 wish upper(‘wish‘)upper, --转大写 WISH replace(‘wish1‘,‘1‘,‘youhappy‘) replace, --替换 wishyouhappy translate(‘wish1‘,‘1‘,‘y‘)translate, --转换 wishy concat(‘11‘,‘22‘) concat --连接 1122from dual;