基本操作
实验环境
Oracle 11g(11.2.0.4.0)
类型转换
-
获取当前日期(date)
select sysdate from dual
-
时间戳(long)转日期(date)
select 1564731830000/(24*60*60*1000) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') as long_to_datetime from dual
-
日期(date)转时间戳(long)
select (sysdate - to_date('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS'))*24*60*60*1000 as datetime_to_long from dual
-
时间戳(timestamp)转日期(date)
select cast(to_timestamp('2019-08-02 22:52:42.33', 'YYYY-MM-DD HH24:MI:SS.FF') as date) as timestamp_to_date from dual
-
日期(date)转时间戳(timestamp)
select cast(sysdate as timestamp) as date_to_timestamp from dual
-
字符串(varchar2)转日期(date)
select to_date('2019-08-02 22:52:42', 'YYYY-MM-DD HH24:MI:SS') as varchar2_to_data from dual
-
日期(date)转字符串(varchar2)
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as date_to_varchar2 from dual
分页查询
查询employee中 [10, 15) 共5条数据:
select * from (select e.*, rownum rn from employee e where rownum < 15) where rn >= 10
分组统计
select col1, count(col1) from table group by col1
select关键字后面返回的列必须是分组的列(group by后面的列)或者是聚合函数。否则会报“ORA-00937”异常,如果想要过滤分组后的数据,则不能用where子句,而是用having子句
--表结构
create table example(
id varchar2(12),
name varchar2(32),
is_delete number(1)
)
查询同名的情况:
select e.name, count(e.name) as total from example e where e.is_delete = 0 group by e.name
查询同名超过三次的情况:
select e.name, count(e.name) as total from example e where e.is_delete = 0 group by e.name having count(e.name) > 3