Oracle学习 DAY_02

一、序列

1.1 序列的定义

序列不真的属于任何一张表，但是可以给主键赋值使用
序列：默认为1开始，依次递增，只要是给主键赋值使用
dual：虚表，只是为了补全语法，没有任何实际意义

1.2 序列的创建

create sequence s_person;
select s_person.currval from dual;

1.3 序列的使用

--添加一条记录
insert into person(pid,name) values(s_person.nextval,'小明')

二、函数

2.1 单行函数

2.1.1 字符函数

select upper('aaa') from dual; --AAA(全转为大写)
select lower('AAA123') from dual; --aaa123(全转为小写)

2.1.2 数值函数

select round(-56.16,1) from dual; --四舍五入
select trunc(-56.16,1) from dual;--直接截断，不看后面。
select mod(10,3) from dual;--求余数

2.1.3 日期函数

--查出emp表中所有员工入职距离现在有几天。
select round(sysdate-e.hiredate) from emp e; --四舍五入

--查出emp表中所有员工入职到现在几月
select months_between(sysdate,e.hiredate) from emp e;  

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

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

2.1.4 装换函数(日期格式化)

 --2019-10-11 03:38:36
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

-- 2019-10-11 3:40:16
select to_char(sysdate,'fm yyyy-mm-dd hh:mi:ss') from dual; 

--2019-10-11 15:41:15
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 

--输出为date类型，上面三种输出都是字符串类型
select to_date('2019-10-11 15:45:19','yyyy-mm-dd hh24:mi:ss') from dual; --2019/10/11 15:45:19

2.1.5 通用函数

--算出emp表中所有员工的年薪 
-- nvl(a,b) a为空，则返回b，不为空则为b，所有值与null运算都为null
select e.sal*12+nvl(e.comm,0) from emp e;

2.2 多行函数【聚合函数】

select count(1) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;

三、分组查询

注意事项：

1. 所有条件都不能使用别名来判断,【where的优先级高于select】
2. where是过滤分组前的数据，having是过滤分组后的数据
3. where必 须在group by之前，having必须在group by之后。order by放在最后。

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

--查询平均工资高于2000的部门
select e.DEPTNO,avg(e.sal) from emp e group by DEPTNO having avg(e.sal)>2000;

--所有条件都不能使用别名来判断,【where的优先级高于select】
select ename,sal s from emp where s>1500;   --错误的，不能使用s这个别名

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

--where是过滤分组前的数据，having是过滤分组后的数据
--where必须在group by之前，having必须在group by之后。

--查询出每个部门工资高于800的员工，且部门平均工资高于2000的部门
select DEPTNO,avg(sal) from emp where sal>800 group by DEPTNO having avg(sal)>2000; 

四、多表查询中的一些概念

4.1 笛卡尔积【交叉集，无意义】

select * from emp,dept;

4.2 等值连接【早期叫内连接】

select * from emp e,dept t where e.deptno = t.deptno;

select * from emp e inner join dept t on e.deptno = t.deptno; -- 内连接，效果一样

4.3外连接

--查出所有部门，以及部门下的员工信息。【外连接】
select * from dept d left join emp e on d.deptno = e.deptno; 

--查出所有员工，以及员工所属的部门。【外连接】

select * from dept d right join emp e on d.deptno = e.deptno;