Oracle----实操篇

增

###直接插入数据
insert into t1(id,name,tel) values ('1','linux','13812341234');

INSERT INTO Employee1  
  VALUES(TO_DATE('19870211', 'YYYYMMDD'), '12365478', 'xs@126.com'，'男')

　　子查询插入

删

DELETE FROM  Employee1   WHERE cEcode='0001'

改

##直接针对需要改动的点进行修改
update t1 set tel='15512345678' where tel='13812341234';

UPDATE Employee1 SET salary = salary +200

 

查

//查询多个条件
select from emp where comm is not null and sal >1500

　　

SELECT * FROM SCJ WHERE SX IS NULL

 

exists替代in

#很多时候用 exists 代替 in 是一个好的选择： 
select num from a where num in(select num from b) 
#用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num) 

时间比较

select * from SYS_KEEPACCOUNT where TO_CHAR(ACCOUNTDATE,'yyyy-mm-dd')='2018-01-26';--accountdate是date类型，包括时间
select * from SYS_KEEPACCOUNT where ACCOUNTDATE = to_date('2018-01-26','yyyy-mm-dd');

用例：

　　查询的数据，更具某个字段分组，更具另一个字段排序

select  t.*  
  from (select a.*, row_number() over(partition by 需要分组的字段 order by 排序字段 desc) rw
           from 表 a) t  
  where t.rw = 1

 

分组后，取每组最新的数据

select sum(LJCZ) from (
    select t.LJCZ,t.CZYB_PKID, t.CBHT_PKID, row_number() over(partition by t.CBHT_PKID order by t.createTime DESC ) rn from HT_CZ_CZYB t
    where t.FBZT = 1  //过滤条件
    ) where  rn = 1;

　　

 

新增同级 新增下级

    <select id="findQydyLastId" resultType="string">
        <if test="entity.opMode ==0 " >
            select NVL(MAX(B.QYDY_PKID)+1,'11')
            from HT_JC_QYDY B,(select CJ,QYDY_PPKID from HT_JC_QYDY where QYDY_PKID = #{entity.lastId}) A where (A.CJ=1 and  B.CJ = A.CJ) or ( A.CJ !=1 and   B.QYDY_PPKID = A.QYDY_PPKID)
        </if>
        <if test="entity.opMode ==1 " >
            select NVL(MAX(B.QYDY_PKID)+1,${entity.lastId}||'01')
            from HT_JC_QYDY B,(select CJ+1 as CJ,QYDY_PKID from HT_JC_QYDY where QYDY_PKID = #{entity.lastId})A where  B.QYDY_PPKID = A.QYDY_PKID
        </if>
    </select>