(1)SYSDATE
该函数返回当前的日期和时间。返回的是Oracle服务器的当前日期和时间。
select sysdate from dual; insert into purchase values (‘Small Widget’,’SH’,sysdate, 10); insert into purchase values
(‘Meduem Wodget’,’SH’,sysdate-15, 15);
查看最近30天的所有销售记录,使用如下命令:
select * from purchase where purchase_date between (sysdate-30) andsysdate;
(2)USER
查看用户名。
select user fromdual;
(3)USERENV
查看用户环境的各种资料。
select userenv(‘TERMINAL’) FROMdual;
2.数值函数
(1)ROUND 四舍五入函数
ROUND(数值,保留位数)
select round(3.1415,3) from deul; select product_name,round(product_price,0) price from
product;
(2)TRUNC 从数中截去小数部分
TRUNC(数值,截断小数点n位后的数)
select trunc(3.145159,3) from dual; select trunc(123456.45,-1) from dual; select trunc(123456.45) from dual; select product_name,trunc(product_price) price fromproduct;
3.文本函数
(1)UPPER、LOWER和INITCAP
这三个函数更改提供给它们的文体的大小写。
select upper (product_name) from product; select lower (product_name) from product; select initcap(product_name) fromproduct;
函数INITCAP能够整理杂乱的文本,如下:
select initcap(‘this TEXT hAd UNpredictABLE caSE ’) fromdual;
(2)LENGTH
求数据库列中的数据所占的长度。
select product_name,length(product_name) name_length from product order by
product_name;
(3)SUBSTR
取子串,格式为:
SUBSTR(源字符串,起始位置,子串长度);
create table item_test(item_id char (20),item_desc char (25)); insert into item_test values (‘LA-101’,’Can, Small’); insert into item_test values (‘LA-102’,’Bottle, Small’); insert into item_test values(‘LA-103’,’Bottle, Large’);
取编号:
select substr(item_id,4,3) item_num,item_desc fromitem_test;
(4)INSTR
确定子串在字符串中的位置,格式如下:
INSTR(源字符串,要查找的字符串,查找起始位置)
select instr(‘this is line one’,’line’,1) fromdual;
其返回值为子串在源字符串中从起始位置开始第一次出现的位置。上面例子的返回值为9。
select item_desc , instr(item_desc,’,’,1) fromitem_test;
(5)LTRIM、RTRIM和TRIM
去除字符串左边的空格、去除字符串右边的空格、去除字符串左右两边的空格。
select ltrim(‘ abc def ‘) fromdual;
4.日期函数
(1)SYSDATE和TRUNC
这两个函数前面已经出现过,即取Oracle服务器系统的日期和截掉小数部分的功能。观察以下操作:
create table test_date (name varchar 2(20), p_date date ); insert into test_date values (‘name1’,sysdate); select * from test_date; select * from test_date where p_date=’25-10月-05‘; select * from test_date wheretrunc(p_date)= ’25-10月-05‘;
Oracle系统中用SYSDATE取得的不仅包含日期而且还包含的有时间信息,时间信息实际上就是表示儒略日数据中的小数部分。
(2)ADD_MONTHS
该函数返回一个具有与所提供日期相差月份的日期,函数中给出了未来或以前的月份数。语法如下:
ADD_MONTHS(起始日期,增减月数)select add_months(’26-10月-05‘,2) from dual; select add_months(’26-10月-05‘,-2) from
dual;
(3)LAST_DAY
返回包含给定日期的那个月的最后一天。语法为:
LAST_DAY(日期)
select last_day(’21-2月-80‘) fromdual;
(4)MONTHS_BETWEEN
返回两个日期间的月份。语法为:
MONTHS_BETWEEN(较晚日期,较早日期)
select months_between(’12-10月-05‘,’12-9月-03‘) fromdual;
5.数据转换函数
(1)TO_CHAR
该函数将日期、时间和数值转换为文本。它的主要价值是提供对日期、时间和数的显示控制;该函数的用法非常灵活,使用较复杂,在此我们仅对经常使用的转换举例说明。
1)格式化日期和时间值
TO_CHAR(日期数据,格式编码)
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) from dual; select to_char(sysdate,’HH24:MI:SS’) from dual; select to_char(sysdate,‘YYYY’)||‘年’|| to_char(sysdate,'MM')||'月'|| to_char(sysdate,'DD')||'日' 日期 fromdual
2)格式化数值
select to_char(5764.12345,'99,999.9999') from dual; selectto_char(5764.12345,‘09,999.9999') from dual;
(2)TO_DATE
将文本转换为实际的Oracle日期/时间值。格式:
TO_DATE(文本,日期格式)
select to_date(‘2005-10-10 11’,’YYYY-MM-DD HH24’) from dual; insert into item_test values( ‘name-x’, to_date(‘2005-10-25’,’YYYY-MM-DD’) ) ;
6.其它函数
NVL函数完成一个简单但有用的功能。任何时候给它一个空值,它都返回一个你所选择的值。格式如下:
NAL(输入值,如果输入值为空要返回的值)
select nvl( null ,’new_value’) from dual; select name,nvl(p_date,sysdate) fromtest_date ;
注意:函数中‘输入值’和‘如果输入值为空要返回的值’的类型必须一致。
7.常用的分组函数
(1)SUM
该函数累加值并返回总数。
select sum (quantity) from purchase; select sum (sal) fromemp;
(2)COUNT
该函数对记录进行统计。
select count (*) fromemp;
这个命令有缺点,一般不应该使用。如果一个表有数十万条记录,强制进行全表扫描将会使计数结果的返回非常慢,会降低其他业务工作的处理效率。因些COUNT函数对指定进行单列统计效果会好得多。
select count (ename) from emp; select count (1) fromemp;
(3)AVG
该函数返回指定列中值的平均数。使用这个函数必须给出相应的列名,且相应列应为数值类型。
select avg (sal) fromemp;
(4)MIN
该函数返回作为参数给出的某列中的最小值。
select min (sal) fromemp;
(5)MAX 用法与MIN相似
select max (sal) fromemp;
8.利用GROUP BY子句分组数据
完成下列操作:
create table tb1( name varchar 2(10),empno number(3),sal number(4) ); insert into tb1 values (‘name-a’,1,1200); insert into tb1 values (‘name-b’,1,1500); insert into tb1 values (‘name-c’,1,2100); insert into tb1 values (‘name-d’,2,900); insert into tb1 values (‘name-e’,2,1300); select empno, sum (sal) from tb1 group by empno; select empno, avg (sal), min (sal), max (sal), count (empno) from tb1 group byempno;
9.利用HAVING子句过滤分组的行
select empno, sum (sal), avg (sal), min (sal), max (sal), count (empno) from test group by empno having sum(sal)>2500