Oracle知识点总结（一）

体系结构

数据库-数据库实例-表空间(逻辑单位)(用户)-数据文件(物理单位)

地球-一个国家-省份(逻辑单位)(公民)-山川河流(物理单位)

通常情况下,Oracle数据库只会有一个实例ORCL,

新建一个项目:

MYSQL : 创建一个数据库,创建相应的表

Oracle: 创建一个表空间,创建用户,用户去创建表

Oracle和MYSQL的差别

Oracle是多用户的, MYSQL是多数据库的

1. 遵循SQL标准

2. 不同厂商,不同的数据库产品,但是有自己的方言

3. 使用自己的方言,也能够完成相同的功能

4. Oracle安全级别要高,MYSQL开源免费

基本查询:

SQL : 结构化查询语言

请说一下SQL的分类以及每类常见的操作符都有哪些

四类:

DDL : 数据定义语言 create alter drop truncate

DML : 数据操纵语言 insert update delete

DCL : 数据控制语言 安全 授权 grant revoke

DQL : 数据查询语言 select from子句 where子句

查询语句的结构:

select[列名] [*]from表名 [where条件] [groupby分组条件] [having过滤] [orderby排序]

注意

select1+1;--在Oracle等于报错 ,在MYSQL中输出结果是2

dual :oracle中的虚表 ,伪表, 主要是用来补齐语法结构

select1+1fromdual;

select*fromdual;

select1fromemp;

查询表中记录个数

selectcount(1)fromemp;

1代表第一个字段，效率比*高。

selectcount(*)fromemp;

别名

别名查询: 使用as 关键字, 可以省略

别名中不能有特殊字符或者关键字, 如果有就加双引号

selectename 姓名, sal 工资fromemp;

selectename"姓 名", sal 工资fromemp;

去除重复数据

distinct

多列去除重复: 每一列都一样才能够算作是重复

单列去除重复

selectdistinctjobfromemp;

多列去除重复的

selectdistinctjob,deptnofromemp;

查询中四则运算

select1+1fromdual;

查询员工年薪 = 月薪* 12

selectsal*12fromemp;

查询员工年薪+奖金

selectsal*12+ commfromemp;--如果comm中的记录为null，结果不准确

nvl 函数 : 如果参数1为null 就返回参数2

selectsal*12+ nvl(comm,)fromemp;

注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算

字符串拼接:

java : + 号拼接

Oracle 特有的连接符: || 拼接

在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符

concat(str1,str2) 函数, 在mysql和Oracle中都有

查询员工姓名 : 姓名:SCOTT

selectenamefromemp;

使用拼接符

select'姓名:'|| enamefromemp;

使用函数拼接

selectconcat('姓名:',ename)fromemp;

条件查询 : [where后面的写法]

关系运算符: > >= =

逻辑运算符: and or not

其它运算符:

like 模糊查询

in(set) 在某个集合内

between..and.. 在某个区间内

is null 判断为空

is not null 判断不为空

查询每月能得到奖金的员工信息

select*fromempwherecommisnotnull;

查询工资在1500--3000之间的员工信息

select*fromempwheresalbetween1500and3000;

select*fromempwheresal >=1500andsal

查询名字在某个范围的员工信息 ('JONES','SCOTT','FORD')

select*fromempwhereenamein('JONES','SCOTT','FORD');

匹配单个字符

如果有特殊字符, 需要使用escape转义

模糊查询: like

% 匹配多个字符

_单个字符

查询员工姓名第三个字符是O的员工信息

select*fromempwhereenamelike'__O%';

查询员工姓名中,包含%的员工信息

select*fromempwhereenamelike'%\%%'escape'\';

select * from emp where ename like '%#%%' escape '#';

排序 : order by

升序: asc ascend

降序: desc descend

排序注意null问题 :nulls first | last

同时排列多列, 用逗号隔开

查询员工信息,按照奖金由高到低排序

select*fromemporderbycommdescnullslast;--nulls last把值为空的放在后面

查询部门编号和按照工资 按照部门升序排序, 工资降序排序

selectdeptno, salfromemporderbydeptnoasc, saldesc;

函数

单行函数: 对某一行中的某个值进行处理

数值函数

字符函数

日期函数

转换函数

通用函数

多行函数: 对某一列的所有行进行处理

max()

min()

count()

sum()

avg()

统计员工工资总和

selectsum(sal)fromemp;--忽略空值

统计员工奖金总和 2200

selectsum(comm)fromemp;

统计员工人数

selectcount(1)fromemp;

这里用1，也是为了方便，当然如果数据量较大的话，也可以提高速度，因为写count(*)的话会所有列扫描，这里用1的话或者用字段名的话，只扫描你写的那个列其实1就代表你这个查询的表里的第一个字段

统计员工的平均奖金

selectavg(comm)fromemp;--报错误 ，comm有空值

统计员工的平均奖金

selectsum(comm)/count(1)fromemp;

selectceil(sum(comm)/count(1))fromemp;

数值函数

selectceil(45.926)fromdual;--46

selectfloor(45.926)fromdual;--45

四舍五入

selectround(45.926,2)fromdual;--45.93

selectround(45.926,1)fromdual;-- 45.9

selectround(45.926,)fromdual;--46

selectround(45.926,-1)fromdual;--50

selectround(45.926,-2)fromdual;--0

selectround(65.926,-2)fromdual;--100

截断

selecttrunc(45.926,2)fromdual;--45.92

selecttrunc(45.926,1)fromdual;-- 45.9

selecttrunc(45.926,)fromdual;--45

selecttrunc(45.926,-1)fromdual;--40

selecttrunc(45.926,-2)fromdual;--0

selecttrunc(65.926,-2)fromdual;--0

求余

selectmod(9,3)fromdual;--0

selectmod(9,4)fromdual;--1

字符函数

substr(str1,起始索引,长度)

selectsubstr('abcdefg',,3)fromdual;--abc

selectsubstr('abcdefg',1,3)fromdual;--abc

selectsubstr('abcdefg',2,3)fromdual;--bcd

注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取

获取字符串长度

selectlength('abcdefg')fromdual;

去除字符左右两边的空格

selecttrim(' hello ')fromdual;

替换字符串

Selectreplace('hello','l','a')fromdual;

取整

selectceil(-12.5)fromdual;-12

selectfloor(12.5)fromdual;12

日期函数

查询今天的日期

selectsysdatefromdual;

查询3个月后的今天的日期

selectadd_months(sysdate,3)fromdual;

查询3天后的日期

selectsysdate+3fromdual;

查询员工入职的天数

selectsysdate- hiredatefromemp;

selectceil(sysdate- hiredate)fromemp;

查询员工入职的周数

select(sysdate- hiredate)/7fromemp;

查询员工入职的月数

selectmonths_between(sysdate,hiredate)fromemp;

查询员工入职的年份

selectmonths_between(sysdate,hiredate)/12fromemp;

转换函数

字符转数值

select100+'10'fromdual;--110 默认已经帮我们转换

select100+ to_number('10')fromdual;--110

数值转字符

selectto_char(sal,'$9,999.99')fromemp;

selectto_char(sal,'L9,999.99')fromemp;

to_char(1210.73,'9999.9') 返回'1210.7'

to_char(1210.73,'9,999.99') 返回'1,210.73'

to_char(1210.73,'$9,999.00') 返回'$1,210.73'

to_char(21,'000099') 返回'000021'

to_char(852,'xxxx') 返回' 354'

日期转字符

selectto_char(sysdate,'yyyy-mm-dd hh:mi:ss')fromdual;

selectto_char(sysdate,'yyyy-mm-dd hh24:mi:ss')fromdual;

只想要年

selectto_char(sysdate,'yyyy')fromdual;--2017

只想要日

selectto_char(sysdate,'d')fromdual;--2 代表一个星期中第几天

selectto_char(sysdate,'dd')fromdual;--10 代表一个月中的第几天

selectto_char(sysdate,'ddd')fromdual;--100 代表一年中的第几天

当前星期

selectto_char(sysdate,'day')fromdual;--monday

selectto_char(sysdate,'dy')fromdual;--mon 星期的简写

字符转日期

selectto_date('2017-04-10','yyyy-mm-dd')fromdual;

查询1981年 -- 1985年入职的员工信息

select*fromempwherehiredatebetweento_date('1981','yyyy')andto_date('1985','yyyy');

通用函数

nvl(参数1,参数2) 如果参数1 = null 就返回参数2

nvl2(参数1,参数2,参数3)如果参数1 = null ,就返回参数3, 否则返回参数2

nullif(参数1,参数2)如果参数1 = 参数2 那么就返回 null , 否则返回参数1

coalesce:返回第一个不为null的值

案例

selectnvl2(null,5,6)fromdual;--6;

selectnvl2(1,5,6)fromdual;--5;

selectnullif(5,6)fromdual;--5

selectnullif(6,6)fromdual;--null

selectcoalesce(null,null,3,5,6)fromdual;--3

笛卡尔积

两个表的乘积，但是实际开发中没什么意义，利用连接来消除笛卡儿积。

内联接

隐式内联接

等值内联接

select*fromemp e1,dept d1wheree1.deptno = d1.deptno;

不等值内联接

select*fromemp e1,dept d1wheree1.deptno d1.deptno;

自连接链接

查询员工编号员工姓名和此员工的经理的编号和姓名

selecte1.empno,e1.ename,e1.mgr,m1.enamefromemp e1,emp m1wheree1.mgr = m1.empno;

查询员工编号、员工姓名、部门名称、经理的编号姓名

selecte1.empno,e1.ename,e1.mgr,d1 dname ,m1.enamefromemp e1,dept d1 ,emp m1 ,wheree1.mgr = m1.empnoande1.deptno = d1.deptno;

显式内联接

select*from表1innerjoin表2on连接条件

查询员工编号员工姓名和此员工的经理的编号和姓名

select*fromemp e1innerjoindept d1one1.deptno = d1.deptno;

外连接

左外连接（Mysql）

左表中所有记录显示出来，如果右表没有对应的记录为空

select*fromemp e1leftouterjoindept d1one1.deptno = d1.deptno;

右外连接（Mysql）

右表中所有记录显示出来，如果左表没有对应的记录为空

select*fromemp e1 rightouterjoindept d1one1.deptno = d1.deptno;

Oracle中的外连接（+）

把所有的员工信息打印出来，如果没有对应的部门通过（+）方式添加空值

select*fromemp e1,dept d1wheree1.deptno = d1.deptno(+);

实际上是如果dept没有对应的记录就加上空值

把所有的部门查询出来，如果没有对应的员工就加空值

select*fromemp e1,dept d1wheree1.deptno(+) = d1.deptno;

子查询

查询语句中嵌套查询语句，用来解决类似：“查询最高工资的员工的信息”等复杂的查询语句。

查询最高工资的员工的信息：

1. 查询出最高工资 :5000

selectmax(sal)fromemp;

2. 工资等于最高工资

select*fromempwheresal = (selectmax(sal)fromemp;);

单行子查询

可以使用> >= = !=等操作：

查询出比雇员7654的工资高同时和7788从事相同工作的员工信息

1. 雇员7654的工资：1250

selectsalformempwhereempno =7654;

2. 7788从事的工作

selectjobfromempwhereempno =7788;

3. 两个条件合并(错误，最高工资应该动态获取)

select*fromempwheresal >1250andjob ='ANALYST';

3.两个条件合并(正确，利用子查询)

select*fromempwheresal > (selectsalformempwhereempno =7654)andjob = (selectjobfromempwhereempno =7788);

查询每个部门最低工资的员工信息和他所在部门信息

查询每个部门最低工资

selectdeptno,min(sal) minsalfromempgroupbydeptno;

2. 查询员工工资=部门最低工资的员工

- - 两个链接条件，首先是员工表的deptno = 部门表的deptno

- - 并且员工的工资 = 部门表此部门最低工资

select*fromemp e1,(selectdeptno,min(sal) minsalfromempgroupbydeptno) t1wheree1.deptno = t1.deptnoande1.sal = t1.minsal;

3. 查询员工所在部门相关信息

select*fromemp e1,(selectdeptno,min(sal) minsalfromempgroupbydeptno) t1 , dept d1wheree1.deptno = t1.deptnoande1.sal = t1.minsalande1.deptno = d1.deptno;

多行子查询

in、not in、any、all、exists

查询领导信息

1. 查询所有经理的编号

selectmgrfromemp;

selectdistinctmgrfromemp;

2. 结果

select*fromempwhereempnoin(selectmgrfromemp);

查询不是领导的信息（错误）

select*fromempwhereempnonotin(selectmgrfromemp);

上面的SQL是不正确的，因为子查询返回的结果集有null，官方文档表示无论如何都不要在子查询使用 not in，而not in（集合）就相当于all（集合）。万一集合中有空值就会报错，因为等判断是不能对null操作的。

正确的SQL

select*fromempwhereempnonotin(selectmgrfromempwheremgrisnotnull);

exists（查询语句）

当查询语句有结果时候返回true，否则返回的是false，数据量比较大的时候非常高效。

查询无结果

select*fromempwhereexists(select*fromempwheredeptno =123456);--- 123456不存在

查询有结果

select*fromempwhereexists(select*fromempwheredeptno =20);--- 20不存在

查询有员工的部门信息

select*fromdept d1whereexists(select*fromemp e1wheree1.deptno = d1.deptno);

查询是一条一条查询的，首先找到需要操作的表dept，dept表的第一条数据的deptno为10，再去执行where条件，拿着deptno为10号的部门记录去emp表依次对比，emp表中如果有deptno为10的数据exists返回true，则把deptno为10的部门表记录打印出来，即此部门有员工信息。

rownum：伪列

系统自动生成的一列，实际上表示行号，默认其实在为1，再查询一条rownum加一。

查询员工表数据，加上行号的一列

selectrownum,e1.*fromemp e1;

下方代码查询不到任何记录

selectrownum,e1.*fromemp e1whererowmnum >2;

查询rownum小于6的记录(可以查询到)

selectrownum,e1.*fromemp e1whererowmnum

rownum不能做大于号判断，可以在小于号判断。

找到员工表中工资最高的前三名

（错误）

selectrownum,e1.*fromemporderbysaldesc;

上方的代码查询出来的数据是根据sal进行排序的但是，rownum都是乱的，是因为先执行rownum再执行order by。SQL执行顺序为：from .. where .. group by..having ..select ...order by。

找到员工表中工资最高的前三名

（正确）

rowid：每行记录存放的真实的物理地址