Oracle知识点总结（一）

2年前 (2022) 程序员胖胖胖虎阿

237 0 0

体系结构

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

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

通常情况下,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 条件] [group by 分组条件] [having 过滤] [order by 排序]

注意

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

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

select 1+1 from dual;

select * from dual;

select 1 from emp;

查询表中记录个数

select count(1) from emp;

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

select count(*) from emp;

别名

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

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

select ename 姓名, sal 工资 from emp;

select ename "姓       名", sal 工资 from emp;

去除重复数据

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

单列去除重复

select distinct job from emp;

多列去除重复的

select distinct job,deptno from emp;

查询中四则运算

select 1+1 from dual;

查询员工年薪 = 月薪* 12

select sal*12 from emp;

查询员工年薪+奖金

select sal*12 + comm from emp; --如果comm中的记录为null，结果不准确

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

select sal*12 + nvl(comm,0) from emp;

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

字符串拼接:

java : + 号拼接
Oracle 特有的连接符: || 拼接
在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符
concat(str1,str2) 函数, 在mysql和Oracle中都有

查询员工姓名 : 姓名:SCOTT

select ename from emp;

使用拼接符

select '姓名:' || ename from emp;

使用函数拼接

select concat('姓名:',ename) from emp;

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

关系运算符: > >= = < <= != <>
逻辑运算符: and or not
其它运算符:

like 模糊查询
in(set) 在某个集合内
between..and.. 在某个区间内
is null 判断为空
is not null 判断不为空

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

select * from emp where comm is not null;

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


    
    
    
 
     
     
     select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <= 3000;

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

select * from emp where ename in ('JONES','SCOTT','FORD');

匹配单个字符

如果有特殊字符, 需要使用escape转义
模糊查询: like

% 匹配多个字符
_单个字符

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

select * from emp where ename like '__O%';

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

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

排序 : order by

升序: asc ascend
降序: desc descend
排序注意null问题 :nulls first | last
同时排列多列, 用逗号隔开

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

select * from emp order by comm desc nulls last; --nulls last把值为空的放在后面

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

select deptno, sal from emp order by deptno asc, sal desc;

函数

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

数值函数
字符函数
日期函数
转换函数
通用函数

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

max()
min()
count()
sum()
avg()

统计员工工资总和

select sum(sal) from emp; --忽略空值

统计员工奖金总和 2200

select sum(comm) from emp;

统计员工人数

select count(1) from emp;

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

统计员工的平均奖金

select avg(comm) from emp;  --报错误 ，comm有空值

统计员工的平均奖金

select sum(comm)/count(1) from emp;

select ceil(sum(comm)/count(1)) from emp;

数值函数

select ceil(45.926) from dual;  --46

select floor(45.926) from dual; --45

四舍五入

select round(45.926,2) from dual; --45.93

select round(45.926,1) from dual; -- 45.9

select round(45.926,0) from dual; --46

select round(45.926,-1) from dual; --50

select round(45.926,-2) from dual; --0

select round(65.926,-2) from dual; --100

截断

select trunc(45.926,2) from dual; --45.92

select trunc(45.926,1) from dual; -- 45.9

select trunc(45.926,0) from dual; --45

select trunc(45.926,-1) from dual; --40

select trunc(45.926,-2) from dual; --0

select trunc(65.926,-2) from dual; --0

求余

select mod(9,3) from dual; --0

select mod(9,4) from dual; --1

字符函数

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

select substr('abcdefg',0,3) from dual; --abc

select substr('abcdefg',1,3) from dual; --abc

select substr('abcdefg',2,3) from dual; --bcd

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

获取字符串长度

select length('abcdefg') from dual;

去除字符左右两边的空格

select trim('  hello  ') from dual;

替换字符串

Select replace('hello','l','a') from dual;

取整

select ceil(-12.5) from dual; -12

select floor(12.5) from dual; 12

日期函数

查询今天的日期
```
select sysdate from dual;
```

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

select add_months(sysdate,3) from dual;

查询3天后的日期
```
select sysdate + 3 from dual;
```

查询员工入职的天数

select sysdate - hiredate from  emp;

select ceil(sysdate - hiredate) from  emp;

查询员工入职的周数

select (sysdate - hiredate)/7 from emp;

查询员工入职的月数

select months_between(sysdate,hiredate) from emp;

查询员工入职的年份

select months_between(sysdate,hiredate)/12 from emp;

转换函数

字符转数值

select 100+'10' from dual;  --110  默认已经帮我们转换

select 100 + to_number('10') from dual; --110

数值转字符


    
    
    
 
     
     
     select to_char(sal,'$9,999.99') from emp;

select to_char(sal,'L9,999.99') from emp;

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'

日期转字符

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

只想要年

select to_char(sysdate,'yyyy') from dual;  --2017

只想要日

select to_char(sysdate,'d') from dual; --2  代表一个星期中第几天

select to_char(sysdate,'dd') from dual;  --10  代表一个月中的第几天

select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天

当前星期

select to_char(sysdate,'day') from dual;  --monday

select to_char(sysdate,'dy') from dual;   --mon  星期的简写

字符转日期

select to_date('2017-04-10','yyyy-mm-dd') from dual;

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

select * from emp where hiredate between to_date('1981','yyyy') and to_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的值

案例

select nvl2(null,5,6) from dual; --6;

select nvl2(1,5,6) from dual; --5;

select nullif(5,6) from dual; --5

select nullif(6,6) from dual; --null

select coalesce(null,null,3,5,6) from dual;  --3

笛卡尔积

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

内联接

隐式内联接

等值内联接

select * from emp e1,dept d1 where e1.deptno = d1.deptno;

不等值内联接

select * from emp e1,dept d1 where e1.deptno <> d1.deptno;

自连接链接

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

select e1.empno,e1.ename,e1.mgr,m1.ename from emp e1,emp m1 where e1.mgr = m1.empno;

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

select e1.empno,e1.ename,e1.mgr,d1 dname ,m1.ename from emp e1,dept d1 ,emp m1 ,where e1.mgr = m1.empno and e1.deptno = d1.deptno;

显式内联接

select * from 表1 inner join 表2 on 连接条件

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

select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

外连接

左外连接（Mysql）

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

select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;

右外连接（ Mysql）

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

select * from emp e1 rightouter join dept d1 on e1.deptno = d1.deptno;

Oracle中的外连接（+）

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

select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);

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

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

select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;

子查询

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

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

1. 查询出最高工资 :5000

select max(sal) from emp;

2. 工资等于最高工资

select * from emp where sal = (select max(sal) from emp;);

单行子查询

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

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

1. 雇员7654的工资：1250

select sal form emp where empno = 7654;

2. 7788从事的工作

select job from emp where empno = 7788;

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

select * from emp where sal > 1250 and job = 'ANALYST';

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

select * from emp where sal > (select sal form emp where empno = 7654) and job = (select job from emp where empno = 7788);

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

查询每个部门最低工资

select deptno,min(sal) minsal from emp group by deptno;

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

- - 两个链接条件，首先是员工表的deptno = 部门表的deptno
- - 并且员工的工资 = 部门表此部门最低工资
select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1 where e1.deptno = t1.deptno and e1.sal = t1.minsal;

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

select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1 , dept d1 where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;

多行子查询

in、not in、any、all、exists

查询领导信息

1. 查询所有经理的编号

select mgr from emp;
select distinct mgr from emp;

2. 结果

select * from emp where empno in (select mgr from emp);

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

select * from emp where empno not in (select mgr from emp);

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

正确的SQL

select * from emp where empno not in (select mgr from emp where mgr is not null);

exists（查询语句）

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

查询无结果

select * from emp where exists(select * from emp where deptno = 123456); --- 123456不存在

查询有结果

select * from emp where exists(select * from emp where deptno = 20); --- 20不存在

查询有员工的部门信息

select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);

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

rownum：伪列

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

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

select rownum,e1.* from emp e1;

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

select rownum,e1.* from emp e1 where rowmnum >2;

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

select rownum,e1.* from emp e1 where rowmnum < 6;

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

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

（错误）

select rownum,e1.* from emp order by sal desc;

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

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

（正确）

select e1.* from e1 emp order by sal desc; 

--把上方查询道德看作一个表查询，rownum不会乱
select rownum,t1.* from(select e1.* from e1 emp order by sal desc) t1;

-- 只要头三条
select rownum,t1.* from(select e1.* from e1 emp order by sal desc) t1 where rownum <= 3;