[专版] Oracle数据库<四> SQL函数的使用

    在SQL中的使用，可以用于查询结果,也可以用于查询条件

select 函数 from emp where 函数

    函数(单行函数、分组函数、分析函数)

    单行函数（日期函数、字符函数、数字函数、转换函数、其它函数）

·日期函数

select add_months(sysdate,2) from dual;--月加
select months_between(sysdate,sysdate) from dual;--求两日期月差
select last_day(sysdate)from dual;--当月最后一天
select round(sysdate,'YEAR') from dual;--YEAR舍入到最近的年，MONTH舍入最近的月DAY天
select next_day(sysdate,'星期二') from dual;--返回下个离自己最近星期几的日期
select trunc(sysdate,'YEAR') from dual;--YEAR截断日期到年月日，与round的区别是只舍不入默认返回天
select extract(year from sysdate) from dual;--返回日期的特定部分year,month,day

·字符函数

select initcap('adsfad') from dual;--首写母大写
select lower('AAAA') from dual;--转换小写
select upper('bbb') from dual;--转换大写
select chr(67) from dual;--返回ASCII码字符
select trim(leading '9' from '9123456789') from dual;--去前面
select trim(trailing '9' from '9123456789') from dual;--去后面
select trim('9' from '9123456789') from dual;--去所有前后
select trim('  9123456789  ') from dual;--去空格
select ltrim('9123456789','9') from dual;--去左边9
select rtrim('9123456789','9') from dual;--去右边9
select length('string   ') from dual;--返回字符串长度注意空格也算长度*
select decode('abcd','abcd','1234') from dual;--逐值替换,可替换多个，完全相同才替换
select translate('jack','abcd','1234') from dual;--在jack中用1234翻译abcd来替换其中的字符
select replace('jack','ck','hh') from dual;--把'jack'中的ck替换为hh
select instr('jack','c') from dual;--找出JACK中的C所处的位置，索引是从1开始的
select substr('jack',2,1) from dual;--截断字符串,第2个参数表示从索引小标开始，第3个参数表示截取几位
select concat('asdf','dddd') from dual;--合并字符串

·数字函数

select ceil(44.778) from dual;--向上取整
select floor(100.2) from dual;--向下取整
select abs(-15) from dual; --取绝对值
select sqrt(4) from dual;--求平方根
select power(4,2) from dual;--取M的N次幂
select cos(180) from dual; --取余弦函数
select sin(180) from dual;--取正弦
select cosh(0) from dual; --双曲余玄值
select sign(-30) from dual;--取符号
select mod(10,3) from dual;--取模
select round(100.256,2) from dual;--四舍五入
select trunc(100.256,2) from dual;--截断

·转换函数

select to_char(sysdate,'YYYY-MM-DD') from dual;--把日期转换为字符创
select to_char('456','c9999') from dual;--添加人民币符号CNY
select to_date('2005-2-3','YYYY-MM-DD') from dual;--将字符转为日期
select sqrt(to_number('100')) from dual;--数字转换函数,取平方根

·其它函数

select nvl('','空') from dual;--为空则返回空，不为空则返回自己
select nvl2('','不为空','空') from dual;--为空则返回空，不为空则返回不为空
select nullif('123','123') from dual;--相等返回空，否则返回前一个值

·分组函数

select avg(sal) from emp;--求平均值
select sum(sal) from emp;--求和
select min(sal) from emp;--最小值
select max(sal) from emp;--最大值
select count(*) from emp;--求记录数
select count(comm) from emp;--统计不为空的记录数
select count(distinct deptno) from emp;--统计不重复记录数

一般分组函数会结合group by使用,按什么字段来分组

select deptno,max(sal) from emp group by deptno;--分组统计最大值
select deptno,max(sal) from emp group by deptno having max(sal) > 2000;

注意：分数函数如果作为查询条件时不能写在where后面，写到having后面

·排序

order by 需要排序的列 asc/desc,...

asc默认，可以不写，表示升序（从小到大）

desc 表示降序（从大到小）

·分析函数

row_number函数对重复数据排序也不同

row_number() ovar(order by 排序字段)

select ename,sal,row_number() over( order by sal desc) as rank from emp;

·排序，数据重复排序不同

rank() over(partition by '',order by '')--重复记录序号跳过

select deptno,ename,sal,rank() over (partition by deptno order by sal desc,comm) rank from emp;

dense_rank() over(partition by '',order by '')--重复记录连续

select deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc,comm) rank from emp;

笛卡尔积(2个表或者两个以上表的结果乘积)