oracle重点知识,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

函数的嵌套