获取当前时间:
oracle:
select sysdate from dual ;
2023-03-09 18:17:40
postgresql:
select current_timestamp ;
2023-03-09 18:16:19.370735+08
select now() ;
2023-03-09 18:16:41.057221+08
select current_date;
2023-03-09
分页写法:
Oracle:分页查询使用 rownum
select * from (select rownum r,e.* from tableName e where rownum <=5) t where r>0;
PostgreSQL:使用 limit #{查询条数} offset #{从第几条开始,从0开始}
select * from tableName limit 5 offset 0;
字段进行比较:
例如 name是字符型,sex是数字型
oracle 类型会自动转换
select * from tableName where name = sex ;
postgresql:CAST 转成相同类型
select * from tableName where name = cast(sex as char) ;
string 转 integer
SELECT CAST ('100' AS INTEGER);
4.mybatis中传入时间类型
oracle :
select to_char(#{checkDate,jdbcType=DATE},'yyyy-MM-dd') from dual;
postgresql:
select to_char(#{checkDate,jdbcType=DATE}::date,'yyyy-MM-dd') ;
生成32位主键ID:
oracle: select sys_guid() from dual;
postgresql 需要自己创建函数: select spauth.sys_guid();
空值替换:
oracle: select nvl(#{name,jdbcType=VARCHAR},'123') from dual;
条件判断:
oracle: select decode(#{sex,jdbcType=VARCHAR},'1','男','0','女','未知') sex from dual;
postgresql :PG没有decode判断,需要用case when then 代替,函数后 取别名必须加 as
select case #{sex,jdbcType=VARCHAR} when '1' then '男'
when '0' then '女'
else '未知' end as sex ;
7.查询另一个查询的结果,表加别名:
oracle 不需要加别名
select * from ( select * from tableA) ;
postgresql 需要加别名
select A.* from ( select * from tableA) as A ;
8.更新
oracle: 更新表字段加 表的别名
update tableA as a set a.name = (select b.name from tableB as b where a.id = b.id);
posetgresql:更新表字段不能加别名
update tableA as a set name = (select b.name from tableB as b where a.id = b.id);