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;