Oracle 日期分页

2014/12/01 37413
select * from  li.books  where book_publish_date between  '1-2月-2005' and '1-2月-2010';
select * from  books where book_price between  50 and 60;
select * from books where catalog_id in (select catalog_id from catalogs where catalog_name='数据库');
select * from catalogs;
---dual 表
select * from dual;

-获取oracle 的系统日期
select sysdate from dual;
--为前日期增加10天
select sysdate+10 from dual;

select (select  from dual)+10 from dual;
--日期函数
select add_months(sysdate,4)  from dual;
select add_months('2-2月-1990',4) from dual;
-获取指定月份的最后一天
select last_day(sysdate) from dual;
select last_day('2-2月-2014') from dual;
---计算两个月之间相差几个月
select months_between(sysdate,'27-4月-2014') from dual;

----日期的四舍五入
---按年
select round(sysdate,'year') from dual;
--按月
select round(sysdate,'month') from dual;
select round((select to_date('1987-4-15','yyyy-MM-dd') from dual),'month') from dual;
select to_date('1987-3-1','yyyy-MM-dd') from dual

--按周
select round(sysdate,'day') from dual;

--next_day 的使用， 获取指定日期的下一天所在的日期

select next_day(sysdate,2) from dual; 下一周的第几天;
select next_day('27-8月-2014',2) from dual;
 
 --阶段当前日期
 --按年
 select trunc(sysdate,'year') from dual;
 
 --按月
 select trunc(sysdate,'month') from dual;
 
 --按周
 select trunc(sysdate,'day') from dual;
 
 --比较一组数字中的最大值
 select greatest(12,22,33,13,45,5) from dual;
 select greatest (
 '12-5月-2014',
 '12-12月-2011',
 '12-3月-2000'
 ) from dual;
 
---字符串函数的使用
--截断字符串（从第N个字符开始截取，截取的长度为M个字符）
select  substr('helloworld',3,5) from dual;
--获取指定字符串中的字符位置
select instr('hello','o') from dual;

--获取字符串的长度
select length('liuhuahua') from dual;

--转换函数
--将其他类型的数据转为字符串（最常用的是日期转字符串）
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;

select sysdate from dual;
--将数字转为字符串
select to_char(9898.33,'$99999.999') from dual;
--将其他数据转为日期格式（通常是将字符串转为日期）
select to_date('1987-3-1','yyyy-MM-dd') from dual;

--日期比大小，获取一组日期的最大值
select greatest(
to_date('2024-4-22','yyyy-MM-dd'),
to_date('2024-5-1','yyyy-MM-dd'),
to_date('2014-12-22','yyyy-MM-dd')
) from dual;

select '4'+'5' from dual;
select 4+5 from dual;
--获取用户的登陆ID
select uid from books;
select user from dual;

--聚合函数
select sum(book_price) from books; 
select count(book_id) from books;

---oracle的分页显示
  select * from (select books.* ,rownum  r from books where rownum<=6) where r>2;
  select books.*, rownum  from books where  rownum<=5;
  
  ---group by 
  select catalog_id from books order by catalog_id;
  select * from books;
  ---having 
  select catalog_id,sum(book_price) from books group by catalog_id  having sum(book_price)>=100 order by catalog_id;
代码片段
