当前位置:Gxlcms > mysql > Oracle日期分页

Oracle日期分页

时间:2021-07-01 10:21:17 帮助过:49人阅读

无详细内容 无 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

<无详细内容> <无>
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;

人气教程排行