oracle知识点汇总

基本SELECT 语句

1.select 列名 from 表名
eg.

select * from  emp
     select  ename,sal,sal+300  from  emp 

2.null 空值 （是一种无效的，未赋值的，未知的或不可用的值。空值不等于零或空格）
任何包含空值的算数表达式运算后的结果都为空值
3.连接操作符（||）
eg.

select ename ||'的工作'||'是 job as ejob from emp

字符串和日期要用单引号
4.distinct 消除重复行
eg. select distinct deptno from emp

限制数据和对数据排序

1.where 子句 可以返回限定的数据行
eg. select ename from emp where deptno=10
(查询emp 表中10号部门所有员工姓名)
2.比较操作符
<>不等于
3.特殊比较运算符
between...and 判断要比较的值是否在某个范围
in 判断要比较的值是否和集合列表中任意的一个值相等
like 判断要比较的值是否满足部分匹配
null 判断要比较的值是否为null
eg. select ename,sal from emp where sal between 1000 and
2000
select ename,sal from emp where mgr in(7902,7566,7788)
select ename from emp where ename like'S%'
%代表零或任意更多字符
_代表一个字符
select ename，mgr from emp where mgr is null
4.逻辑运算符
and 与 ，连接两个表达式，需要两个表达式的结果都是true 整个表达式才true
or 或 ，连接两个表达式，只要一个表达式的结果是true 整个表达式就true
not 非 用来对条件表达式取反
5.order by 子句 （排序）
正常是从小到大排序 默认不写
desc 降序排序
多表连接
1.笛卡尔积
eg. select emp.empno,emp.deptno,dept.loc from emp,dept
where emp.deptno=dept.deptno and loc='CHICAGO'
2.非等值连接
eg. select e.empno,e.sal,s.grade from emp e,salgrade s
where e.sal between s.losal and s.hisal
(查询每个员工的姓名，工资，工资等级)
e 和 s是表的别名，标准写法应该是 emp as e
3.多表连接
eg.select e.empno,e.ename,e.sal,s.grade,d.deptno from emp e,salgrade s,dept d
where emp.deptno=dept.deptno and (e.sal between s.losal and s.hisal)
order by sal
(查询每个员工的编号，姓名，工资，工资等级，工作城市，按照工资等级升序排序)
4.外部连接
5.自身连接
eg.select a.ename,b.ename from emp a,emp b
where a.empno=b.mgr
(查询每个员工姓名及其上级姓名)
6.交叉连接 cross join
7.自然连接 natural join
8.using子句
9.on子句
10.左外连接 left join
11.右外连接 right join
12.全外连接

分组函数

1.常用的五个函数：max，min，sum，avg,count
max，min可以用于任何数据类型
sum，avg都只是对数值类型的列或表达式操作
count 返回满足条件的行记录数
eg. select count(comm) from emp where deptno=30
(查询部门30有多少个员工领取奖金)
结果是count(comm) 4
2.distinct 消除重复记录后再使用组函数
3.组函数中的空值
除了count(*) 之外，其它所有分组函数都会忽略列中的空值，然后在进行运算
所以这样写 select avg(nvl(comm，0)） from emp
nvl 函数可以使分组函数强制包含含有空值的记录
4.group by
eg. select deptno,avg(sal) from emp 错误的
正确写法select deptno,avg(sal) from emp group by deptno
不能在where子句中限制组，可以通过having子句限制组
5.having
select deptno,max(sal) from emp group by deptno
having max(sal)>2900
6.select 子句书写顺序
select ，from，where,group by ,having,order by
7.select 子句执行过程
一.通过from 子句中找到需要查询的表
二.通过where 子句进行非分组函数筛选判断
三.通过group by 子句完成分组判断
四.通过having 子句完成分组函数筛选判断
五.通过select 子句选择显示的列或表达式及组函数
六.通过order by 子句进行排序
8.组函数的嵌套
select max(avg(sal)) from emp group by deptno
(查询平均薪水的最大值)

子查询

括号内的查询叫做子查询也叫内部查询，先于主查询执行
eg.查询工资比JONES工资高的员工信息
select * from emp
where sal>(select sal from emp where ename='JONES')
eg. 查询工资最低的员工姓名
select ename from emp
where sal=(select min(sal)from emp)
单行子查询
eg. 显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作
select ename,job from emp where sal>(select sal from emp where empno=7876) and job=(select job from emp where empno=7369)
eg. 查询工资最低的员工姓名岗位及工资
select ename,job,sal from emp where sal>(select min(sal) from emp)
eg.查询部门最低工资比20部门最低工资高的部门编号及最低工资
select deptno,min(sal) from emp group by deptno
having min(sal)>(select min(sal) from emp where deptno=20)
eg. 查询哪个部门的员工人数高于各部门的平均人数
select count(empno) from emp group by deptno
having count(empno)>(select avg(count(empno)) from emp group by deptno)
多行子查询
eg.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
select ename,job from emp where job in(select job from emp where deptno=10) and deptno<>10
多列子查询
eg.查询出和1981年入职的任意一个员工的部门和职位完全相同的员工姓名，部门，职位，入职日期，不包括1981年入职的员工
select ename,deptno,job,hiredate from emp
where (deptno,job) in (select deptno,job from emp
where hiredate between '1981-01-01' and '1981-12-31')
and hiredate not in (SELECT HIREDATE from EMP
where HIREDATE between '1981-01-01' and '1981-12-31')
查询出和1981年入职的任意一个员工的部门或职位完全相同的员工姓名，部门，职位，入职日期，不包括1981年入职的员工
select ename,deptno,job,hiredate from emp
where deptno in (select deptno from emp
where hiredate between '1981-01-01' and '1981-12-31')
or job in (select job from emp
where hiredate between '1981-01-01' and '1981-12-31')
and hiredate not in (SELECT HIREDATE from EMP
where HIREDATE between '1981-01-01' and '1981-12-31')
eg.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名，职位，不包括SCOTT和BLAKE本人
select ename,EMP.job from EMP join (select job,mgr from emp
where ename in ('SCOTT','BLAKE')) tempTab
on EMP.job = TEMPTAB.job and EMP.MGR = TEMPTAB.mgr
where ename not in ('SCOTT','BLAKE')
eg.查询不是经理的员工姓名
select * from emp where empno not in (select mgr from emp where mgr is not null)
ROWNUM 伪列
1.对于ROWNUM只能执行<,<=运算，不能执行>,>= 或一个区间运算between...and等
2.ROWNUM和order by 一起使用的时候，order by是后执行的，所以ROWNUM其实是已经排了序的ROWNUM
TOP-N查询
主要是实现表中按照某个列排序，输出最大或最小的N条记录功能
ASC:查询最小的N条记录
DESC:查询最大的N条记录
eg.查询员工信息表中工资最高的前五名员工
select rownum,empno,ename,sal
from (select * from emp order by sal desc)
where rownum <=5
分页查询
1.未指定需要按照某列排序
eg. 在EMP表中，每页四条记录,查询第三页员工信息
select a.* from
(select rownum rn,emp.* from emp where rownum<=12) a
where a.rn>8
2.指定需要按照某列排序
eg. 在EMP表中，每页四条记录,查询按照工资升序排序的第三页员工信息
select a.*
from (select rownum rn,b.* from (select * from emp order by sal) b where rownum<=12 ) a
where a.rn>8

高级子查询

嵌套子查询
子查询以嵌套的方式写在父查询的where，having，from子句中
eg.查询比本部门平均薪水高的员工姓名，薪水
select a.ename,a.sal,b.salavg from emp a join
(select deptno,avg(sal) salavg from emp group by deptno) b
on a.deptno=b.deptno
and a.sal>b.salavg
相关子查询
eg.查询比本部门平均薪水高的员工姓名，薪水
select ename,sal from emp outer
where sal>
(select avg(sal) from emp where deptno=outer.deptno)
eg.查询所有部门名称和人数
select dname,(select count() from emp where deptno=d.deptno)
from dept d
eg.查询是经理的员工姓名
select ename from emp a where
0 < (select count() from emp where mgr=a.empno)
EXISTS和NOT EXISTS操作符
eg.查询是经理的员工姓名
select ename from emp a
where exists (select 1 from emp where mgr=a.empno)
1是占位用的 ，写什么都可以
eg.查询不是经理的员工姓名
select ename from emp a
where not exists (select 1 from emp where mgr=a.empno)

单行函数

分类：通用，字符，数值，日期，转换

字符函数

1.大小写转换：lower，upper，initcap
2.字符处理：concat，substr,length,instr,lpad,rpad,replace,trim
lower
将大写或大小写混合的字符转换成小写
eg.lower('HEllo') 输出结果为 hello
upper
将小写或大小写混合的字符转换成大写
eg.lower('HEllo') 输出结果为HELLO
initcap
将每个单词的第一个字母转换成大写，其余的字母都转换成小写
eg.lower('HEllo') 输出结果为 Hello
concat
连接两个值等同||
substr
eg. select substr('zhaoying' , 2,5 ) from dual 输出结果为haoyi 从第2位开始输出长度为5的字符串
select substr('zhaoying' , 2 ) from dual 输出结果为haoying 从第2位开始输出所有字符
select substr('zhaoying' , -2 ) from dual 输出结果为ng 从倒数第2位开始（也就是从右面数）向右侧取字符串
select substr('zhaoying' , -4，3 ) from dual 输出结果为yin
length
字符长度
instr
eg.select instr('string','i') from dual 输出结果为4 i出现在string的第四个字符位置
查询员工姓名中包含大写或小写字母A的员工姓名
select ename from EMP
where instr(lower(ename),'A')>0
lpad
eg.lpad(sal,10,'')(列名，总长度, 列名对应的值不足总长度的话，左边补充的字符)
从左侧开始补，一共是10个字符串，如果工资5000前面就6个， 输出结果是******5000；500就7个
rpad
eg.rpad(sal,10,'*') 输出结果是5000******
trim 去除字符串头部或尾部的字符
eg.trim('s' from 'ssmith') 输出结果为mith
*replace
eg.replace('abc','b','d') 输出结果为adc 把b用d换

数值函数

round 将列或表达式所表示的数值四舍五入到小数点的第n位
eg.round(45.963,2) 输出结果为 45.96
trunc将列或表达式所表示的数值截取到小数点的第n位
eg.trunc(45.967,2) 输出结果为 45.96
trunc(45.967,-1) 输出结果为 40
mod 取余
eg.mod(1700,300)输出结果为 200
dual 虚表，不能保存任何数据，只有一个字段，一行记录

日期函数

日期类型可以加减数字，就是加减对应的天数
eg.'10-AUG-06' +15 结果为 '25-AUG-06'
日期类型之间可以减操作，就是两个日期之间间隔了多少天
eg.'10-AUG-06' - '4-AUG-06' 结果为6
间隔多少小时 就是n/24
eg.select ename,(sysdate-hiredate)/7 weeks from emp
where deptno=10
![00T~(2{BE80G_C6UO5{NZJ.png
常用日期函数
1.SYSDATE 返回系统当前日期
2.MONTHS_BETWEEN 返回两个日期类型数据之间间隔的自然月数
eg.查询所有员工服务的月数
SELECT ename,sal,months_between(sysdate,hiredate) months
from emp order by months
3.ADD_MONTH 返回指定日期加上相应月数后的日期
eg.查询82年之后入职的员工转正日期，按照3个月试用期考虑
select ename,sal,hiredate,add_months(hiredate,3) new_date
from emp where hiredate>'1982-01-01'
4.NEXT_DAY 返回某一日期的下一个指定日期
eg.在02-2月-06之后的下一个周一是什么日期
select next_day('02-02-06','monday') next_day from dual
5.LAST_DAY 返回指定日期当月最后一天的日期
eg.select last_day('02-02-06','monday') last_day from dual
6.ROUND(date,'fmt') date按照fmt制定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为dd，将date四舍五入最近的天
eg. 查询81年入职的员工姓名，入职日期按月四舍五入的日期
select empno,hiredate,round(hiredate,'MONTH') from emp where substr(hiredate,-2,2)='81'
格式码：世纪CC，年YY，月MM，日DD，小时HH24，分MI，秒SS
7.TRUNC(date,'fmt')date按照fmt制定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为dd，将date截取为最近的天
eg. 查询81年入职的员工姓名，入职日期按月截断的日期
select empno,hiredate,trunc(hiredate,'MONTH') from emp where substr(hiredate,-2,2)='81'
8.EXTRACT 返回日期数据类型中的年份，月份或日
eg.部门编号是10的部门中所有员工入职月份
select ename,hiredate,extract(month from hiredate) month from emp where deptno=10

转换函数

显示转换函数
1.TO_CHAR
用于日期型
eg.select ename,to_char(hiredate,'DD Month YYYY') from emp
用于数值型
eg.select to_char(sal,$99,999') newsal from emp where ename='SMITH'
2.TO_NUMBER
3.TO_DATE

通用函数

与空值null相关的函数
1.nvl
nvl(n1,n2) 如果n1 不是null 返回n1 ，否则返回n2
2.nvl2
nvl(n1,n2，n3) 如果n1 不是null 返回n2 ，否则返回n3
3.NULLIF
nullif(n1,n2)比较两个表达式，相等返回null，否则n1
4.coalesce
返回第一个不为空的参数，参数个数不受限
条件处理函数
1.case
eg.select ename,deptno,
(case deptno
when 10 then '技术部'
when 20 then '测试部'
when 30 then '财务部'
else '无部门'
end)deptname from emp
2.decode

函数的嵌套