oracle02（自增序列、函数）

1、序列

在oracle没有自增id，如果像完成自增必须使用序列完成

语法

CREATE SEQUENCE 序列名
[INCREMENT BY n] 
[START WITH n] 

create sequence sequence_student_1
increment by  2
start with 1;

获取序列的当前值，注意第一次获取必须先调用自增

select sequence_student_1.currval from DUAL;

获取序列的自增值

select sequence_student_1.nextval from DUAL;

使用自增序列完成自增id插入

insert into student(id,sex,birthday)values (sequence_student_1.nextval,'F',date '2020-01-01');
commit ;

2、scott员工的表结构

1 雇员表：emp

№	名称	类型	描述
1	EMPNO	NUMBER(4)	雇员的编号，由四位数字所组成
2	ENAME	VARCHAR2(10)	雇员的姓名，由10位字符所组成
3	JOB	VARCHAR2(9)	雇员的职位
4	MGR	NUMBER(4)	雇员对应的领导编号，领导也是雇员
5	HIREDATE	DATE	雇员的雇佣日期
6	SAL	NUMBER(7,2)	基本工资，其中有两位小数，五倍整数，一共是七位
7	COMM	NUMBER(7,2)	奖金，佣金
8	DEPTNO	NUMBER(2)	雇员所在的部门编号

2 部门表：dept

№	名称	类型	描述
1	DEPTNO	NUMBER(2)	表示部门编号，由两位数字所组成
2	DNAME	VARCHAR2(14)	部门名称，最多由14个字符所组成
3	LOC	VARCHAR2(13)	部门所在的位置

3、 工资等级表：salgrade

№	名称	类型	描述
1	GRADE	NUMBER	工资的等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

4、 工资表：bonus

№	名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员的工资
4	COMM	NUMBER	雇员的奖金

3、内置函数

• 大小写转换

DUAL 是虚表，

select upper('aaa') from DUAL;

select lower('BBB') from DUAL;

• 四舍五入函数

默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数

select round(12.55) from DUAL;

select round(12.6789,2) from DUAL;  #保留整数

• 日期函数

select to_date('2020-12-04','yyyy-MM-dd') from DUAL;
select to_date('2020-12-04 10:00:00','yyyy-mm-dd hh:mi:ss') from DUAL;

日期函数的运算

Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律

日期 – 数字 = 日期

日期 + 数字 = 日期

日期 – 日期 = 数字

select  sysdate - to_date('2020-08-04 10:00:00','yyyy-mm-dd hh:mi:ss')  from  DUAL;

select ENAME,round((sysdate - HIREDATE)/7) from EMP;

获取两个事件段中的月数

select ENAME,round(months_between(sysdate ,HIREDATE)) from EMP;

将时间按照格式分段获取

select  to_char(sysdate,'yyyy') from DUAL;

select ENAME,to_char(HIREDATE,'yyyy') year ,to_char(HIREDATE,'mm') month from EMP;

select ENAME,to_char(HIREDATE,'yyyy-mm-dd') hire_date from EMP;


select ENAME,to_char(HIREDATE,'fmyyyy-mm-dd') hire_date from EMP;

• 通用函数

使用nvl处理空值

select ENAME,SAL*12 + COMM from EMP;

select ENAME,SAL*12 + nvl(COMM,0) from EMP;

decode函数

该函数类似 if…else if…esle

select decode(2,1,'first',2,'second','orther') from DUAL;

select distinct JOB from EMP;

select ENAME, decode(JOB,'CLERK','职员','SALESMAN','销售','PRESIDENT','总裁','MANAGER','经理','ANALYST','分析员','其他') from EMP;

case when 函数

select ENAME,JOB,
case
    when JOB='CLERK' then '职员'
    when JOB='SALESMAN' then '销售'
    else '其他'
end
from EMP;

4、聚合函数

统计行数

使用count（*），相对来说耗费性能

select  count(*)  from EMP;

select count(JOB) from EMP;

最小值

select min(SAL) from emp;

最大值

select max(SAL) from emp;

平均值

select avg(SAL) from emp;

求和

select sum(sal) from EMP where DEPTNO=20;

5、分组统计

注意分组函数只可以把字段和聚合查询出来，不可以查询其他字段

select DEPTNO ,count(sal) from EMP group by DEPTNO;

select DEPTNO ,avg(sal) from EMP group by DEPTNO;

6.多表连接

笛卡儿积

select  * from emp,dept;

内连接

select * from EMP a,DEPT b where a.DEPTNO = b.DEPTNO;
select a.ENAME,b.DNAME,b.LOC from EMP a,DEPT b where a.DEPTNO = b.DEPTNO;

自连接

select a.ENAME,b.ENAME  from EMP a,EMP b where a.MGR = b.EMPNO;	

select a.ENAME,b.ENAME,c.DNAME  from EMP a,EMP b ,DEPT c
where a.MGR = b.EMPNO and a.DEPTNO = c.DEPTNO;

右连接

select  a.ENAME,b.DNAME,b.LOC from EMP a,DEPT b where a.DEPTNO(+) = b.DEPTNO;

左连接

select  a.ENAME,b.DNAME,b.LOC from EMP a,DEPT b where a.DEPTNO = b.DEPTNO(+);

6、子查询

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。

select * from (select DEPTNO ,avg(sal) as avg_sal from EMP group by DEPTNO) c
where  c.avg_sal >2000;

也可以

select DEPTNO ,avg(sal) as avg_sal from EMP group by DEPTNO  having avg(sal) >2000;

7、分页查询

在oracl中分页必须使用rownum 查询

显示页码

select ROWNUM ,t.* from EMP t;

查询前四个

select ROWNUM ,t.* from EMP t where ROWNUM<5;

注意ROWNUM不支持 >号，无法查到结果

select ROWNUM ,t.* from EMP t where ROWNUM>5;

可以通过子查询完成

select
a.*
from
(select ROWNUM r,t.* from EMP t) a
where a.r >2 and  a.r< 5;