Oracle从零基础到基础熟练

建议：在学习本文时，建议先将本人另外一个博客《mysql从零基础到基础熟练》看一下哈，mysql从零基础到基础熟练_Summer152191的博客-CSDN博客，本篇学习的是oracle，主要是建立在mysql8.0学习的基础上，列举了oracle与其不太一致的点。此外，建议学习《计算机操作系统》《计算机组成原理》这两本计算机的基本计算机知识，以及《数据结构与算法》---算法很重要哦

写在前面的注意事项：oracle中，写完每行代码后，只运行一次，如果该行代码会影响后面再次运行的结果，可以在改行运行完后注释（--）掉该行代码。

第一章 Oracle介绍

oracle database，又名oracle RDBMS，甲骨文公司的一个大型数据库，oracle在数据库领域处于领先地位，是世界上最流行的数据库产品

oracle数据库分两部分组成，oracle数据库软件和Oracle实例

1.数据库软件

基于磁盘的数据文件，控制文件，文件日志

2.Oracle实例

位于物理内存的数据结构，它由多个操作系统的后台进程和共享内存池组成，共享的内存可以被所有的进程访问，Oracle即使用它们来管理数据库访问。

在Oracle里，一个实例只能与一个数据库关联，一个数据库可以有多个实例访问，一个实例由一系列的后台进程和内存结构组成

3.表空间

表空间是一个用来管理数据库存储的逻辑概念，一个表空间可以包含多个数据文件，一个数据文件只能隶属于一个表空间

4.用户

权限管理是oracle的精髓，不同的用户登录到同一个数据库中，拥有不同的权限，oracle的权限有100多种，分为两类：系统权限和数据对象权限

小结：上面几个名词的举例

oracle比作仓库，表空间相当于仓库中的库房，数据表就是库房中的商品。用户相当于仓库的管理员，仓库管理员可以管理一个或多个库房，但是仓库管理员只属于一个仓库。

5.oracle默认有三个用户

sys，scott，systom。sys和systom数据库管理员用户，也是oracle中权限高的用户（sys>systom），SCOTT是系统默认普通用户，密码默认是TIGER。

第二章 简单查询

oracle中只能用单引号

DDL（数据定义语言）：create drop alter(更新）

DQL（数据查询语言）：select

DML（数据操纵语言）：insert delete update（修改）

DCL（数据控制语言）：grant（赋予用户权限） revoke（撤销用户权限）

TCL（事务控制语言）：commit（提交） rollback（回滚）

2.1 简单查询语法

语法：

1.select 字段, from 表名;

2.select 字段 as 别名 from 表名;

注意：as可以省略

3.伪列和虚表

查询表中不存在的列就是伪列，当需要的结果不能直接从表中获取，并且需要计算才能显示，就需要伪列和虚表实现。

注意：伪列为一行一列

4.虚表

虚表：dual，它是用来构成select语句的辅助表，dual表中永远只有一行一列，可以插入和更新数据（千万不要删除，删除后系统会奔溃）

案例：查询99*98的结果

select 99*98 from dual;

案例：查询99+101的结果

select 99+101 from dual;

5.空处理函数nvl

用法：nvl（字段，设定的值）

案例：查询员工的姓名和年薪（包括津贴，没有算0）

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

6.拼接 ||

案例：查询emp表中的姓名，并在姓名的后面显示薪资，要求一列输出

select ename||sal 姓名和薪资 from emp;

select ename||' '||sal 姓名和薪资 from emp;

select '姓名：'||ename||'薪资：'||sal 姓名和薪资 from emp;

第三章 条件查询

当我们查询数据的时候需要筛选，只有符合条件的数据才是我们的目标，这就需要我们借助条件查询

1.语法格式

select 字段 from 表 where 条件;

2.运算符

案例：查询部门名称为sales的员工姓名和部门名称

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where dname='SALES';

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno and dname='SALES';

注意：字符串中要区分大小写

案例：查询薪资等级不是2的员工姓名和薪资等级

select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal where s.grade!=2;

案例：查询员工的工资在2000以上或者1000（包括1000）以下的员工姓名和工资

select ename,sal from emp where sal>2000 or sal<=1000;

案例：查询emp表中名字第二个字母为M的员工信息

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

3.转义符 escape

-插入数据

insert into emp(empno,ename,job,deptno) values(1111,'A%B_C','Teacher',30);

案例：查询emp表中名字中带有%的员工信息

#escape：把escape引号中的字符{这里指！--或其他符号}//后面的字符{！--或其他符号后的%}//当作普通字符处理

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

案例：查询emp表中名字倒数第二个字是_的员工信息

select*from emp where ename like '%?__' escape '?';

案例：查询emp表中有津贴的员工信息（注释：不为空且不为0）

--字段 is not null

--not 字段 is null

select*from emp where comm is not null and comm!=0;

select*from emp where not comm is null and comm!=0;

select e.*,nvl(comm,0) from emp e where comm!=0;

4.distinct 去重

案例：查询存在员工的部门编号

select distinct deptno from emp;

5.group by 分组

案例：查询emp表中每个岗位的平均薪资

select job,nvl(avg(sal),0) from emp group by job;

案例：查询emp表中每个部门每个岗位的平均薪资

select deptno,job,nvl(avg(sal),0) from emp group by deptno,job;

6.having 进一步细分筛选

案例：查询emp表中平均工资在2500到4000之间的部门编号和部门平均薪资

select deptno,avg(sal) from emp group by deptno having avg(sal) between 2500 and 4000;

7.rollup 和 cube 和 grouping 统计

这两个都是分组聚合函数，在 group by 分组后统计

group by 子句生成的数据统计结果只是显示相关列的统计结果，不会生成总计和小计。在实际应用中，不仅需要获取相关列的数据统计结果，还需要取得横向或者纵向的总计和结果统计

rollup ：保留原有的统计结果基础之上，生成横向小计和总计。是针对 group by 之后的第一列进行分组求和，并且还会计算一个总和。

案例：查询emp表中每个部门每个岗位的平均薪资，每个部门的平均薪资，以及所有人的平均薪资

select deptno,job,avg(sal) from emp group by rollup(deptno,job);

#以上面的结果为例，select后面的字段不会影响执行的先后顺序，只会影响表格中字段的位置。以rollup的先后顺序排列：字段在rollup括号内时会失去执行的优先权，以group by 后除rollup的字段为优先级；如果字段都在rollup括号内，以rollup括号内的先后顺序执行。rollup只有第一个字段会生效。##cube跟rollup以上的效果一致，cube是高维度的rollup，cube括号内每个字段都生效。

cube ：保留原有的统计结果基础之上，生成横向小计和总计，以及纵向小计和总计。是对于每一个 column（列） 都会进行分组的一个求和，并且也会计算一个总和。

语法格式：select 字段 from 表 group by cube(字段1,字段2)

案例：查询每个部门每个岗位的平均薪资/每个部门的平均薪资/每个岗位的平均薪资/所有员工的平均薪资

select deptno,job,avg(sal) from emp group by cube(deptno,job);

grouping：当使用上面两个方法生成统计结果时，某个统计结果可能用到了一列或者多列，也可能没用到任何列。为了确定统计结果是否使用了特定列，可以使用grouping函数，返回0就是用了该列，返回1则没用该列。

案例：查询每个部门每个岗位的平均薪资/每个部门的平均薪资/每个岗位的平均薪资/所有员工的平均薪资

select deptno,job,avg(sal),grouping(deptno) from emp group by cube(deptno,job);

select deptno,job,avg(sal),grouping(deptno),grouping(job) from emp group by cube(deptno,job);

8.order by

语法格式:

select 字段 from 表名 order by 字段名 asc升序（升序时可省略）/desc降序;

案例：查询所有员工的姓名，薪水和部门编号，按照所在部门编号升序，部门相同按照薪水降序

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

第四章 rownum 分页

属于关键字

单页无法显示所有的内容，我们就需要分页

分页是我们将数据分段展示给用户的一种技术手段，用户看到的不是全部数据而是一部分数据，可以通过指定页码或者翻页转换可见内容直到找到目标

rownum

rownum不是一个真实存在列，它是用来返回行的号，第一行为1，第二行为2，以此类推，这个伪列可以限制返回的行数。

由于rownum是结果集编号，所以无法查询编号大于1的任何记录，因为这个编号总是从1开始。

案例：将emp表实现每页五条记录，查询第一页的结果

select emp.*,rownum from emp;

select emp.*,rownum from emp where rownum<=5;

select emp.*,rownum from emp where rownum>1;--没有结果

为什么？

因为rownum总是从1开始，第一条不满足的话，第二条rownum又变成了1，以此类推，所以永远没有符合条件的记录

注意1：rownum没有最小值，无法使用大于或大于等于最小值来做。rownum中只能存在小于或小于等于有界的最大值。

注意2：不能用BETWEEN...AND，当使用>>=BETWEEN...AND，这些条件时，从数据文件中得到的第一条记录rownum为1，不符合条件sql语句的条件会被删除，然后接着读下条，下条rownum还会是1，又被删除，以此类推就查不到数据了。

案例：将emp表实现每页五条记录，查询第二页的结果

select

a.*,rownum

from

(select emp.*,rownum r from emp where rownum<=10) a

where

a.r between 6 and 10;

案例：将emp表实现每页3条记录，查询第3页的结果

select a.*,rownum from (select emp.*,rownum r from emp where rownum<=9) a where a.r between 7 and 9;

案例：将emp表实现每页3条记录，查询第4页的结果

select a.*,rownum from (select emp.*,rownum r from emp where rownum <=12) a where a.r between 10 and 12;

第五章 rowid 去重

两条一样的数据，在oracle里面，路径不一样，可以通过保留字段的最大值或最小值来达到只保留一条实现去重效果

rowid是Oracle中非常重要的概念，用于定位数据中心的每一条记录的唯一地址，通常情况下，一套记录被插入到数据中的这个时候，地址就被确定且唯一，rowid也是一个伪列，一个rowid记录对应的一个数据的物理地址

在oracle中去重只能用rowid ！！！

###

--创建新表

create table coemp as select*from emp;(--创建完后记得把该段代码注释掉）

--查看coemp表

select*from coemp;

--将emp表中的数据再次插入到coemp

insert into coemp select*from emp;

(语法：insert into 新表 select*from 旧表;）

--查看coemp表

select*from coemp;

###以上都是我们用来测试rowid来创建的具有重复数据的表

按照empno排序查看coemp表中rowid的状态

select c.*,rowid from coemp c order by empno;

按照empno分组，查看最大的rowid和最小的rowid

最大：select empno,max(rowid) from coemp group by empno;

最小：select empno,min(rowid) from coemp group by empno;

--保留上一步查询出来的任意一组rowid，并删除之外的全部数据

delete from coemp where rowid not in (select max(rowid) from coemp group by empno);

--查询去重后的coemp（去重成功！）

select*from coemp;

第六章 where子查询

1.单行子查询

单行子查询是指数据的子查询语句在where子句中引用单行子查询，我们可以用比较运算符 = != >= <=

案例：查询公司里大于公司平均工资的员工名

select ename from emp where sal>(select avg(sal) from emp);

案例：查询SALES部门的员工名

select ename from emp where deptno=(select deptno from dept where dname='SALES');

2.多行子查询

多行子查询是指返回多行的子查询语句，当在where中使用多行子查询时，必须使用 多行比较运算符 in /any/all

in

案例：查询emp表中不是销售部门的员工姓名和部门编号

select ename,deptno from emp where deptno in (select deptno from dept where dname!='SALES');

any

语法：select...from 表名 where 字段 运算符 any(子查询);

案例：查询emp表中工资大于10号部门任意一个员工工资的其他部门员工工资和姓名

方法一：查询部门编号为10的所有员工工资

select ename,sal from emp where sal> any(select sal from emp where deptno=10) and deptno!=10;

方法二：求部门编号为10的最少工资

select ename,sal from emp where sal>(select min (sal) from emp where deotno=10) and deptni!=10;

all

运算符必须和单行运算符结合使用，并且返回行匹配子查询的任意一个结果

案例：查询emp表中工资大于部门编号为30的所有员工姓名和工资

方法一：多行子查询，all查询

select sal from emp where deptno=30;

select ename,sal from emp where sal > all(select sal from emp where deptno=30);

方法二：单行子查询

select max(sal) form emp where deptno = 30;

select ename,sal from emp where sal > (select max(sal) form emp where deptno = 30);

第七章 unoin 集合操作

7.1 union 并集去重

对两个集合进行并集操作，不包含重复行，按照默认规则排序

案例：

select 'a','b','c' from dual

union

select 'a','b','c' from dual;

--以下并集不去重，因为两个结果集不相同

select 'a','b','c' from dual

union

select 'a','b','e' from dual;

7.2 union all

全集不去重，对两个结果集不去重并集操作

select 'a','b','c' from dual

union all

select 'a','b','c' from dual;

--以下不行，两个表的列数要一致

select 'a','b','c' from dual

union all

select 'a','b','c','b' from dual;

7.3 intersect 交集

交集找出共同的，不包含重复行，按照默认规则排序

--案例
(select 'a','b','f' from dual
union
select 'a','b','c' from dual )

intersect

(select 'a','b','e' from dual
union
select 'a','b','c' from dual );

--以下结果返回一行abc

(select 'a','b','c' from dual

union

select 'a','b','c' from dual)

intersect

(select 'a','b','c' from dual

union

select 'a','b','c' from dual);

7.4 minus 差集

对两个结果集进行差集操作，不包含重复行，仅会返回minus前面独有的行

(select 'a','b','c' from dual

union

select 'a','b','e' from dual)

minus

(select 'a','b','c' from dual

union

select 'a','b','f' from dual);

第八章 约束

检查约束可以用来实施一些简单的规则，比如列值必须存在于某个范围，或者检查的结果必须是一个 True 或者 False

8.1 建表添加元素

create table tb_user(
userid int constraint uid_pk primary key,
uname varchar2(30) constraint uname_nn not null,
upwd varchar2(20) constraint upwd_nn not null,
age int default(18) constraint age_ck check(age>=18),
sex char(4) default('男') constraint sex_ck check(sex='男' or sex='女'),
email varchar2(30) constraint email_uq unique,
regtime date default(sysdate)
);

8.2 删除约束

语法格式

alter table 表明 drop constraint 约束名称;

--练习：将上表的约束全部删除

--删除userid主键约束

alter table tb_user drop constraint uid_pk;

--删除其他五个约束

alter table tb_user drop constraint email_uq;

alter table tb_user drop constraint sex_ck;

alter table tb_user drop constraint age_ck;

alter table tb_user drop constraint upwd_nn;

alter table tb_user drop constraint uname_nn;

varchar2：每个字符占2

8.3追加约束

8.3.1 非空约束追加语法

alter table 表名 modify(字段名称 constraint 约束名称约束类型)；

--案例：给uname 添加非空约束

alter table tb_user modify(uname constraint uname_nn not null);

8.3.2 其他所有的约束的追加语法

alter table 表名 add constraint 约束名称约束类型(字段)；

--案例：给userid添加主键约束

alter table tb_user add constraint uid_pk primary key(userid);

第九章 函数

9.1 字符函数

1.concat（x，y）将x和y链接

案例：插叙所有员工的姓名和工作岗位，并将姓名和岗位合并输出，输出格式是 姓名~~岗位

select concat(ename,concat('~~',job)) 姓名岗位 from emp;

select concat(ename,concat('~~',job)) "姓名~岗位" from emp;

2.instr(x,str,start,n)

在x中查找字符串str，可定位start开始，查找第n个str，

如果不写start和n，默认从第一个字符开始找第一个str，如果没找到返回0。

案例：从字符串“helloworld”中查找字符‘e’的位置

select instr('helloworld','e') from dual;

案例：从字符串“helloworld”中查找第一个字符‘l’的位置

select instr('helloworld','l') from dual;

案例：从字符串“helloworld”中查找第2个字符‘l’的位置

select instr('helloworld','l',1,2) from dual;

案例：从字符串“helloworld”中查找第3个字符‘l’的位置

select instr('helloworld','l',1,3) from dual;

案例：字符串“helloworld”中，从world开始查找第1个字符‘l’的位置

select instr('helloworld','l',6,1) from dual;

案例：查看emp表中员工的姓名是否包含A,如果包含返回第一个字母A的位置

select instr(ename,'A',1,1) from emp;

3.length（x） 返回x的长度

案例：查询emp表中每个员工姓名的长度

select length(ename) from emp;

4.大小写转换 upper lower

select upper('hello') from dual;

select lower('HELLO') from dual;

5.substr(x,start,length)

x是原字符串，start从哪开始截，length截取的长度

案例：截取‘helloworld’中的owo

select substr('helloworld',5,3) from dual;

案例：将‘helloworld’中的owo变成大写

select upper(substr('helloworld',5,3)) from dual;

6.replace(x,old,new) 替换

x是原字符串，old是被替换的字符串，new是用来替换的新字符串

案例：将‘helloworld’中的owo变成大写，通过大小写转换变成‘hellOWOrld’

select replace('helloworld','owo',upper(substr('helloworld',5,3))) from dual;

9.2 日期函数

1.sysdate：获取当前系统日期

select sysdate from dual;

2.current_date：获取当前系统日期

select current_date from dual;

3.add_months(d1,n1)：在日期的d1基础之上加几个月

案例：在emp表中，hiredate是入职日期，请查询员工的入职日期和转正日期

select ename,hiredate,add_months(hiredate,3) from emp;

4.last_day(d1)：返回这个日期的这个月的最后一天

案例：查询员工转正月份的最后一天

select last_day(add_months(hiredate,3)) from emp;

select last_day(add_months(hiredate,3))-1 from emp;

案例：查询员工转正月份的第一天

select last_day(add_months(hiredate,2))+1 from emp;

5.months_between(n1,n2)：返回n1到n2之间过了多少个月

（n1>n2)，n1是后来的日期，n2是以前的日期

案例：查询员工从入职到现在一共过了多少个月

select months_between(sysdate,hiredate) from emp;

6.next_day(d1,c1)：返回日期d1的下个星期几（c1）的日期；

案例：查询下周三的日期

select next_day(sysdate,'星期三') from dual;

9.3 转换函数

1.to_char(x,c)：将数据类型x转换成字符串格式（c）

案例：将emp表中hiredate转换成固定格式 'mm/dd/yyyy'

select to_char(hiredate,'mm/dd/yyyy') from emp;

案例：将emp表中hiredate转换成固定格式 'yyyy年/mm月/dd日'

select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;

2.to_date(c,x)：将字符串格式（c）转换成日期类型

案例：将 '12-15-2021' 转换成日期格式

select to_date('12-15-2021','mm-dd-yyyy') from dual;

select to_date('2021-12-30','yyyy-mm-dd') from dual;

补充 Mysql 和 Oracle 的不同

（1）oracle是大型数据库，mysql是中型数据库，oracle市场占有率是mysql的两倍，同时mysql是开源的，oracle价格非常高

（2）mysql安装完才100多m，oracel有两个多G，使用oracle非常占用内存空间

（3）oracle支持大并发，大访问量（即同时运行，同时访问），是联机事务最好的工具

（4）操作上的区别：

4.1 主键

mysql一般使用自动增长类型，在创建表的时候指定表的主键为 auto_increment，插入记录时不需要指定该记录的主键值，mysql会自动增长。oracle没有自动增长类型，主键一般使用序列

4.2 单引号的处理

mysql里单双引号都可以，oracle里面只能用单引号，除区分字符串内的文字以及别名

4.3 翻页处理

mysql的翻页比较简单，用limit记录个数。oracle翻页处理比较繁琐，需要用rownum，每个结果只有一个rownum字段标明位置，并且只能用rownum小于或小于等于一个数，不能大于或者大于等于除了1以外的数（但是大于等于1没有意义）

4.4 长字符串的处理

oracle在长字符串处理有特殊的地方，insert 或者update可操作的字符串长度不超过4000，如果超过4000考虑用c l o b 类型，借用oracle里面自带的dbms_lob程序包

4.5 空字符处理

mysql的非空字段也有空的内容，oracle定义非空字段不允许有空的内容。在oracle里面，如果为NULL或者空字符，需要把他改成一个空格的字符串

4.6 字符串的模糊比较

mysql里用字段名like'%k%'，oracle里也可以用，但这种模糊查询在oracle里面不能使用索引，

4.7 oracel 实现了ANSI Sql 中的大部分功能，如事务的隔离级别，但是mysql在这些方面还是比较弱的