时间: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;