Oracle SQL 函数 查询 详解

----子查询
select * from emp;
---查询和smith从事同一种工作的员工信息
select * from emp where
job=(select job from emp where ename='SMITH')

----查询所有经理的信息
---（select mgr from emp）
查询结果为多行数据此处不能用等号要用关键字in
select * from emp where empno
in (select mgr from emp);

---连接查询

---内连接：返回两个表中有对应关系的数据
select * from dept;
---查询员工的名字和其所在部门的名字
select ename,dname from emp
inner join dept on emp.deptno=dept.
deptno;

---左外连接:左表中的所有数据，右表中
---有对应关系的记录
select ename,dname from emp
left join dept on emp.deptno=dept.
deptno;

---右外连接
select ename,dname from emp
right join dept on emp.deptno=dept.
deptno;

---full join:完全连接
---返回两张表中所有的数据
select ename,dname from emp
full join dept on emp.deptno=dept.
deptno;

----cross join:交叉连接
---（排列组合）一般用不到
select ename,dname from emp
cross join dept

---排序
--从员工表中查询员工姓名和员工的工资并且以员工的工资降序排序
select ename,sal from emp
order by sal desc

---分组

----集合查询
---查询结果做差
select * from emp where
deptno=20
minus
select * from emp where
sal>=3000

----oracle的系统函数
---dual表，为测试表
select * from dual
---日期函数
---在系统日期上加5个月
select add_months(sysdate,5)
from dual;

---MONTHS_BETWEEN (date1, date2)
--- 用于计算date1和date2之间有几个月。
select months_between
(sysdate,to_date('2019-4-5','yyyy-MM-dd'))
from dual;
--如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数。
--如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数。
--如果date1和date2日期一样，那么MONTHS_BETWEEN()就返回一个0。

--LAST_DAY函数返回指定日期对应月份的最后一天。
--获取当前日期的最后一天
--SQL> SELECT last_day(SYSDATE) FROM dual;

--运行结果：

--LAST_DAY(SYSDATE)
-----------------
--2018/12/31 15:39:
select last_day(sysdate)  from dual;

--oracle中round()四舍五入  
--语法：
--Round 函数
--语法为ROUND(number，num_digits)

--其中Number是需要进行四舍五入的数字；Num_digits--为指定的位数，按此位数进行四舍五入，如果 num_digits 大于 0，则四舍五入到指定的小数位，如果 num_digits 等于 0，则四舍五入到最接近的整数，如果 num_digits 小于 0，则在小数点左侧进行四舍五入。
--例如：
--ROUND(2.149， 0) 将 2.149 四舍五入到一个整数结果为2。

--ROUND(2.15， 1) 将 2.15 四舍五入到一个小数位，结果为2.2。

--ROUND(2.149， 1) 将 2.149 四舍五入到一个小数位结果为2.1。

--ROUND(-1.475， 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。
--ROUND(21.5， -1) 将 21.5 四舍五入到小数点左侧一位结果为20。
select round(sysdate) from dual;
--结果
2018-08-16 00:00:00

 由于系统移植，原来的数据库编码和时区都换了，原来的一些SQL文也出错了。。

经常崩出"ORA-01846: not a valid day of the week "错误。

经测试，以下这个简单语句也会错！！

SQL> select next_day(sysdate,'FRIDAY') FROM DUAL;
 select next_day(sysdate,'FRIDAY') FROM DUAL
 ORA-01846: not a valid day of the week

经过查找一些资料，确实是时区或编码引起的。

想到阿拉伯数据是全世界通用的，那应该可以用1--7代替星期几 。

于是，立马产生以下语句：

select next_day(sysdate,5) FROM DUAL;
执行，结果出来了。。。
---下一个星期2的时间

select next_day(sysdate,3) from dual;
Oracle认为一周的第一天是周日，所以要得到下个星期一要传入2  .   NEXT_DAY(sysdate,2)
官方文档中对NEXT_DAY的解释：
NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session,either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

---截断

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  --显示当前时间
2018-12-29 16:24:34

select trunc(sysdate,'year') from dual; --截取到年（本年的第一天）
2018-1-1

select trunc(sysdate,'q') from dual; --截取到季度（本季度的第一天）
2018-10-1

select trunc(sysdate,'month') from dual; --截取到月（本月的第一天）
2018-12-1

select trunc(sysdate,'') from dual;
空

select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual; --默认截取到日（当日的零点零分零秒）
20181229 00:00:00

select trunc(sysdate-1,'w') from dual;  -- 离当前时间最近的周四，若当天为周四则返回当天，否则返回上周四
2018-12-22

select trunc(sysdate,'ww') from dual;  --截取到上周末（上周周六）
2018-12-24

select trunc(sysdate,'day') from dual; --截取到周（本周第一天，即上周日）
2018-12-25

select trunc(sysdate,'iw') from dual; --本周第2天，即本周一
2018-12-26

select to_char(trunc(sysdate,'dd'),'yyyymmdd hh24:mi:ss') from dual;--截取到日（当日的零点零分零秒）
20181229 00:00:00

select trunc(sysdate,'hh24') from dual;  --截取到小时（当前小时，零分零秒）
2018-12-29 16:00:00

select trunc(sysdate,'mi') from dual; --截取到分（当前分，零秒）
2018-12-29 16:24:00

select trunc(sysdate,'ss') from dual ;--报错，没有精确到秒的格式

select trunc(sysdate)  from dual;

--得到当前系统日期的天（可以得到年日月份秒）
select extract(day from sysdate)
from dual;

----字符函数
--单词首字母变为大写
select initcap('hello word')
from dual;
--变为大写
select upper('hello') from dual;
--变为小写
select lower('HELLO') from dual;

具体的语法格式如下：

 LTRIM(c1,[,c2])

【功能】删除左边出现的字符串

【参数】C1 字符串，c2 追加字符串，默认为空格

【返回】字符型
select ltrim('---111-abc','-')
from dual;

【功能】删除右边出现的字符串
select rtrim('---111-abcyyy','y')
from dual;

--依次逐个替换
select translate('ab--a---b--','ab','**')
from dual;
--将ab看为一组替换
select replace('ab---a---b','ab','**')
from dual;

--返回左边参数第一次出现的位置
--（从0开始）
select instr('hello','l')
from dual;

--从左向右截取8个字符（空格也算）
select substr('good good study',
1,8) from dual;

--连接
select concat(ename,'----')
from emp;

--将对应的ASCII值转换为字符
select chr(119) from dual;
--将对应的字符转换为ASCII值
select ascii('w') from dual;

--用10个字符位置来显示ename 在左侧用-做占位符
select lpad(ename,10,'-') from emp;
--用10个字符位置来显示ename 在右侧用-做占位符
select rpad(ename,10,'-') from emp;

select trim(9 from 99988888999)
from dual;
--结果
88888
select trim('a' from 'aaabbbbaaaa')
from dual;
--结果
bbbb
--几个字符
select length('哈哈') from dual;
select length('aa') from dual;
--结果2
select length(123) from dual;
--结果3

--将emp表中comm为null的ename（名字）的null替换为'暂无补助'
--comm不为null的展示为null
select ename,
decode(comm,null,'暂无补助')
from emp;

----数学函数

--向上取整
select ceil(12.34) from dual;

--向下取整
select floor(12.84) from dual;

--3的2次方
select power(3,2) from dual;

--16开方和10开方
select sqrt(16) from dual;
select sqrt(10) from dual;

--4除以5的余数为4
select mod(4,5) from dual;
--结果
4

--四舍五入取整
select round(12.24) from dual;

--第二个参数是四舍五入保留的位数
select round(12.45643434,2) from dual;

--第二个参数是小数点保留的位数
--不进行四舍五入计算
select trunc(12.56189,2) from dual;

---转换函数
select sysdate from dual;

---varchar2->date
--字符串类型日期转化为date类型日期
select to_date('2018-6-7','yyyy-MM-dd')
from dual;

---date->varchar2
--date类型日期转化为字符串类型日期
select to_char(sysdate,'yyyy"年"MM"月"dd"日"hh"时"mi"分"ss"秒"')
from dual;

--数字加+
select to_number('100')+100 from dual;

select * from emp;

--if语句
----if comm=null then comm=0
----如果补助（comm）为null那么补助为0
select ename,sal+nvl(comm,0) from emp;

---if deptno!=null then deptno=第二个参数
---else deptno=第三个参数
select ename,nvl2(deptno,'有部门','没有部门')
from emp;

select * from emp;
----if comm=null then comm=0
select ename,sal+nvl(comm,0) from emp;

---if deptno!=null then deptno=第二个参数
---else deptno=第三个参数
select ename,
nvl2(deptno,'有部门','没有部门')
from emp;

update emp set comm=0 where ename='SMITH';
---if comm=0 then comm=null
select ename,nullif(comm,1) from emp;

select * from emp;

----分析函数
---查询工资最高的前三名员工信息
--用重复数据当做一个数据
dense_rank()

--重复数据不认为是同一数据
rank()

--第一行是伪列
--所以返回三行数据
select * from emp where  rownum<4

--查询工资最高的前三个
--rn，ep 都是自己命名的
--此查询语句联合了三个查询
select * from
(
select  ename,sal,dense_rank() over(order by sal desc) rn
from emp
)
ep where rn<=3;

---if comm=0 then comm=null
select ename,nullif(comm,0) from emp;

----分析函数
---查询工资最高的前三名员工信息
select * from (
select  ename,sal,
dense_rank() over(order by sal desc) rn
from emp) ep where rn<=3;

 

 

借鉴的博客

https://blog.csdn.net/oracle1858/article/details/7162765
https://blog.csdn.net/FYANGFEI/article/details/81708776