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;