03、oracle入门篇

1、单行函数：作用于一行，返回一个值。

---字符函数

select upper('yes') from dual;--YES
select lower('YES') from dual;--yes

----数值函数

select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5
select mod(10, 3) from dual;---求余数

----日期函数

--查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;

--算出明天此刻
select sysdate+1 from dual;

--查询出emp表中所有员工入职距离现在几月。
-- months_between(date1,date2)计算两个日期之间共有几个月
select months_between(sysdate,e.hiredate) from emp e;

----查询出emp表中所有员工入职距离现在几年。
select months_between(sysdate,e.hiredate)/12 from emp e;

----查询出emp表中所有员工入职距离现在几周。
select round((sysdate-e.hiredate)/7) from emp e;

-- to_char(data,'format') 将日期转化成字符串
--日期字符串化，输fm出格式为  1980-12-17 12:0:0, 
--不加fm 输出格式 1980-12-17 12:00:00
--24小时格式输出，yyyy-mm-dd hh24:mi:ss
SELECT TO_CHAR(HIREDATE,'fm yyyy-mm-dd hh:mi:ss') FROM EMP;

-- to_date('date','format') 将日期字符串转化成日期
select to_date('2021-08-08 22:10:01','yyyy-mm-dd hh24:mi:ss') from dual;

 ----通用函数nvl空转换

---算出emp表中所有员工的年薪
----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。（mysql空转换语法为 ifnull(val,0)）
select e.sal*12+nvl(e.comm, 0) from emp e;

 --查询参数多条件别名

CASE
    e.ENAME 
    WHEN 'SMITH' THEN '步惊云' 
    WHEN 'ALLEN' THEN '聂风' 
    WHEN 'WARD' THEN '雄霸' 
    ELSE '无名' 
END 
FROM
    EMP e;

效果如图：

 2、多行函数【聚合函数】：作用于多行，返回一个值

select count(1) from emp;---查询总数量
select sum(sal) from emp;---工资总和
select max(sal) from emp;---最大工资
select min(sal) from emp;---最低工资
select avg(sal) from emp;---平均工资

3、分组查询

分组查询中，出现在group by后面的原始列，才能出现在select后面
没有出现在group by后面的列，想在select后面，必须加上聚合函数。
聚合函数有一个特性，可以把多行记录变成一个值。

比如：

--查询出每个部门的平均工资
select e.deptno, avg(e.sal) from emp e group by e.deptno;

---查询出每个部门工资高于800的员工的平均工资
select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno;

注意：

where是过滤分组前的数据，having是过滤分组后的数据。
表现形式：where必须在group by之前，having是在group by之后。

4、连接查询

---等值连接
select * from emp e, dept d where e.deptno=d.deptno;

---内连接，查询两表满足条件的数据
select * from emp e inner join dept d on e.deptno = d.deptno;

---查询出所有部门，以及部门下的员工信息。【右外连接，查询两表满足条件的数据，并查询右表不满足条件的数据】
select * from emp e right join dept d on e.deptno=d.deptno;

---查询所有员工信息，以及员工所属部门（左外连接，查询两表满足条件的数据，并查询左表不满足条件的数据）
select * from emp e left join dept d on e.deptno=d.deptno;

---oracle中专用外连接【oracle独有右外连接表查询】
select * from emp e, dept d where e.deptno(+) = d.deptno;

---oracle中专用外连接【oracle独有左外连接表查询】
select * from emp e, dept d where e.deptno= d.deptno(+) ;

5、子查询

---子查询返回一个值
---查询出工资和SCOTT一样的员工信息
select * from emp where sal in (select sal from emp where ename = 'SCOTT')

---子查询返回一个集合
---查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in (select sal from emp where deptno = 10);

--查询ename 等于SCOTT员工信息
select * from (select * from emp where ename = 'SCOTT');

6、oracle中的分页(推荐)

--emp表工资倒叙排列后，每页五条记录，查询第二页。
--rownum行号不能写上大于一个正数。
select * from(

    select rownum r, e.* from(

          --具体查询的业务
          select * from emp order by sal desc

    ) e where rownum < 11

) where r > 5