Oracle 基础

第1节 学习目标

1. 能够理解 Oracle 数据库的基本概念
2. 记忆 Oracle DDL 语句
3. 掌握 Oracle 序列的使用
4. 掌握 Oracle 单行函数的使用
5. 掌握 Oracle 多行函数(聚合函数)
6. 学习并应用 Oracle 分组统计
7. 能够应用 Oracle 多表查询(内，左，右，全)
8. 掌握 Oracle 子查询(三种情况)

1.1 环境

服务器信息：centos7.9

oracle版本：oracle12c

客户端：Navicat

第2节 与 Oracle 有关的几个概念

2.1 目标

1. 什么是 Oracle
2. 与 Oracle 有关的概念

2.2 什么是 Oracle

Oracle 数据库系统是美国 Oracle 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前世
界上使用广泛的关系数据库管理系统。

2.3 与 Oracle 有关的概念

2.3.1 数据库

Oracle 数据库是数据的物理存储文件。这就包括 Oracle 中所有的文件类型：数据文件 ORA 或者 DBF、控
制文件 CTL、联机日志、参数文件。Oracle 数据库的概念和其它数据库不一样，**一个 Oracle 系统只有一个数据库。**

2.3.2 实例

一个 Oracle 实例由一系列的后台进程和内存结构组成。一个数据库可以有 n 个实例，通常我们也只创建一个实例。实例与数据库的关系就像对象与类的关系。


从实例和数据库的概念上来看：实例暂时的，它不过是一组逻辑划分的内存结构和进程结构，它会随着数据库的关闭而消失。数据库它其实就是一堆物理文件（控制文件，数据文件，日志文件等等），它是永久存在的。

2.3.3 表空间

MySQL 与 Oracle 数据库之间最大的区别要属表空间。 在 MySQL 中一个项目对应一个数据库，而在 Oracle 中通常
一个项目对应一个用户。

Oracle 数据库的逻辑结构：
被划分成一个或多个表空间，而一个表空间则对应着一个或多个物理的数据库文件。
每个数据库至少有一个 SYSTEM 表空间和 USER 表空间，这是系统安装完后自动创建的。

1. 一个实例包含多个用户和多个表空间
2. 每个表空间可以有多个不同的用户来访问
3. 表空间在物理上由多个数据文件组成
4. 每张表在逻辑上随机分布在不同的物理文件中

2.3.4 数据文件

数据文件是数据库的物理结构。所有的数据保存在系统的硬盘上，都是以数据文件的形式存在的。

• Oracle 安装好的数据库文件是放在下面的目录中：

linux的目录结构

/u01/app/oracle/oradata/xe

• 用户创建的表空间的数据文件默认是放在下面的目录中：

  

  linux的目录结构

  /u01/app/oracle/dbs

2.4 小结

1. 数据库：在 Oracle 中有几个数据库 一个 Oracle 系统只有一个数据库。
2. 实例：一个数据库可以创建多少个实例？**通常创建 1 个**。
3. 表空间：一个数据库包含多个表空间和多个用户。一个项目对应一个用户。
4. 数据文件：系统创建的表空间文件，用户创建的表空间文件。

第3节 创建和删除表空间

3.1 目标

1. 创建表空间的语法

2. 删除表空间的语法

3.2 Oracle 的 SQL 命令分类

1. DDL：创建表，创建用户 create/drop/alter/show
2. DML：增删改操作 insert/delete/update
3. DQL：查询 select
4. DCL：权限控制 grant/revoke

3.3 创建表空间

3.3.1 语法


3.3.2 创建表空间的SQL

-- 创建表空间 space1，数据文件为 space1.dbf，初始大小为 20M，空间不足自动扩展。
create tablespace space1 datafile 'space1.dbf' size 20m autoextend on;

-- 创建表空间 space2，数据文件为 space2.dbf，初始大小为 500k
create tablespace space2 datafile 'space2.dbf' size 500k;

3.3.3 运行效果


3.4 删除表空间

3.4.1 语法


3.4.2 删除表空间的SQL

-- 删除 space2 表空间，同时删除内容和文件
drop tablespace space2 including contents and datafiles;

3.5 小结

1. 创建表空间：create tablespace .. datafile … size … autoextend on
2. 删除表空间：drop tablespace … including contents and datafiles

第4节 创建用户和给用户权限

4.1 目标

1. 创建用户的语法

2. 给用户权限

4.2 创建用户

4.2.1 语法


4.2.2 创建用户的SQL

-- 创建用户 user1，密码 orcl，指定默认表空间为 space1
create user user1 identified by orcl default tablespace space1;

4.2.3 运行效果


创建好用户以后，使用 user1 登录，出现如下提示：

用户没有相应的权限，接下来要给用户添加权限。

4.3 用户赋权限

4.3.1 Oracle 中的三种角色

角色：将多种操作权限做为一个权限集合保存下来，并且起一个名字。

1. connect

2) resource

3. dba

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除。如：system

4.3.2 授予权限语法


4.3.3 授予权限SQL

-- 进入 system 用户下给用户 user1 赋予 dba 权限
grant dba to user1;

4.4 小结

1. 有哪三种角色? connect resource dba
2. 给用户角色权限的语句：grant 角色名 to 用户名

第5节 创建表和删除表

5.1 目标

创建和删除表结构

5.2 Oracle 数据类型


5.3 建表

5.3.1 语法


5.3.2 检查约束语法


5.3.3 示例SQL

/* 以 user1 登录，创建 person 表，
字段 pid 是 number(10)主键，name 字符串 20，非空，性别字符串长 2，
性别添加检查约束，只能输入男或女，生日是日期类型 */
create table person (
pid number(10) primary key,
name varchar2(20) not null,
sex varchar2(20) check(sex='男' or sex='女'),
birthday date
);
select * from person;
-- 插入 1 条正确的数据

insert into person values (1, '悟空','男',to_date('2000-11-11','yyyy-mm-dd'));
-- 在 Oracle 中默认事务是手动提交
commit;

-- 插入 1 条错误的数据，性别为'妖'
insert into person values (2, '白骨精','妖',to_date('2000-11-11','yyyy-mm-dd'));

5.4 表删除

5.4.1 语法


5.4.2 示例SQL

-- 删除全部数据
truncate table person;

-- 同时删除表结构和数据
drop table person;

5.5 小结

1. 建表：create table
2. 删除表数据：truncate table
3. 删除表结构：drop table

第6节 表结构的修改

6.1 目标

修改表的结构

6.2 语法


6.3 示例SQL

-- 在 person 表中 address 增加列，类型为 varchar2(100)
alter table person add address varchar2(100);

-- 把 person 表的 address 列的长度修改成 50 长度
alter table person modify address varchar2(50);

-- 把 person 表字段 address 字段名称修改为 home
alter table person rename column address to home;

6.4 小结

添加列：add
修改类型：modify
改名：rename column .. to

第7节 添加记录和创建序列

7.1 目标

插入记录，序列的使用

7.2 添加 INSERT

7.2.1 Oracle 与 MySQL 插入数据不同

1. 在 mysql 中默认数据是不区分大小写，而 Oracle 是区分的。
2. 默认 oracle 是手动提交事务

7.2.2 添加记录语法


7.2.3 示例SQL

-- 插入部分列：pid 是 1，名字是孙悟空
insert into person (pid,name) values(1,'孙悟空');

-- 插入所有列：pid 是 2，名字猪八戒，男，生日要使用 to_date('日期','yyyy-mm-dd')进行类型转换
insert into person values (2,'猪八戒','男',to_date('1999-02-11','yyyy-mm-dd'),'高老庄
');

-- 查看代码
select * from person;

-- 提交事务
commit;

-- 回滚事务
rollback;

如果事务没有默认提交，可以使用commit提交事务，rollback回滚事务。本次用的docker oracle12c的镜像版本。默认开启了事务。

7.3 序列 sequence

7.3.1 序列的语法

默认的情况下，Oracle 没有主键的自增长。使用序列来解决这个问题。
什么是序列：一串连续的整数数字
序列的作用：在 Oracle 中主要做为主键的自增长功能。


7.3.2 示例SQL

-- 创建一个序列名为 seq_one，起始值为 1，步长为 2，最大值 9，循环使用，不指定缓存
create sequence seq_one 
start with 1
increment by 2
maxvalue 9
cycle
nocache;


-- 查询用户USER1的所有序列
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='USER1';

7.3.3 序列的操作属性

在序列中提供了以下的两种操作：


• 注：一开始创建的序列没有当前值的，必须先调用一次 nextval 才能得到当前值。

-- 查询当前的序列号
select seq_one.nextval from dual;
select seq_one.currval from dual;

-- mysql 查询现在的时间
select now();

-- 在 oracle 中使用 sysdate 查询现在的时间
-- 注：oracle 中只要使用 select 语句，语法必须包含 from 
-- 在 oracle 中有一个虚拟表名：dual，为了让 select 语法完整
select sysdate from dual;

-- 删除对应的序列
DROP SEQUENCE seq_one;

总结：dual 是什么？ 是虚拟表，用来让 select 的语法完整；

7.3.4 在插入数据时使用序列

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。

-- 示例：使用序列插入自动增长的主键，插入记录"白骨精",性别：女，生日，地址为广州
select * from person;

-- 创建序列
create sequence seq_person start with 3;

-- 插入数据
insert into person values(seq_person.nextval, '白骨精','女',sysdate,'广州');

7.4 小结

1. Oracle 中有没有主键自增长？没有，使用序列
2. select 语句中是否必须包含 from？是，使用虚拟表：dual

第8节 修改和删除记录

8.1 目标

对表中的记录进行更新和删除

8.2 修改 UPDATE

8.2.1 语法


8.2.2 示例SQL

-- 将所有人的地址改成天河区
update person set home='天河区';

-- 将 pid 为 2 的记录，改成地址改成深圳
update person set home='深圳' where pid=2;

8.3 删除 DELETE

8.3.1 语法


8.3.2 示例SQL

-- 删除 pid 为 1 的记录
delete from person where pid=1;

8.4 小结

1. 更新：update
2. 删除：delete

第9节 单行函数：字符函数和数值函数

9.1 目标

1. 使用字符函数

2. 使用数值函数

9.2 准备数据：使用 scott 用户

使用 scott 用户登录，这是系统默认的一个普通用户，密码默认是 tiger，这个用户下已经创建了一些表可以使用。

9.2.1 scott 用户下的表结构


9.2.2 修改 scott 的密码

-- 查看用户列表
select username from dba_users;

-- 修改 scott 的密码
alter user scott identified by mima;

-- 修改被锁定账户状态
alter user scott account unlock;

9.2.3 使用 scott 用户登录

9.2.4 查看表与表之间的关系

点击ER图表，即可展示表与表之间的关系

9.3 字符函数

9.3.1 把小写的字符转换成大写的字符

-- 查询员工表的名字列，全部显示成大写
select upper(ename) from emp;

9.3.2 把大写字符变成小写字符

-- 查询员工表的名字列，显示成小写
select lower(ename) from emp;

9.4 数值函数

9.4.1 四舍五入函数

-- 将数字 12.536 保留到整数
select round(12.536) from dual;

-- 将 12.536 保留 2 位小数
select round(12.536,2) from dual;

9.5 小结

1. 字符：upper() lower()
2. 数值：round()

第10节 单行函数：日期函数和转换函数

10.1 目标

1. 日期函数

2. 转换函数

10.2 日期函数

Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律

10.2.1 日期函数语法


10.2.2 示例SQL

-- 查询系统现在的日期
select sysdate from dual;

-- 查询姓名，计算员工进入公司的天数(今天-入职日期)，再取整
select * from emp;
select ename, round(sysdate-hiredate) as 天 from emp;

-- 查询姓名，计算员工进入公司的周数(sysdate – 入职日期)/7 就是周数，再取整数值
select ename, round((sysdate-hiredate)/7) as 周 from emp;

-- 查询姓名，计算员工进入公司的月数，再取整
select ename, round(months_between(sysdate,hiredate)) as 月 from emp;

10.3 转换函数

10.3.1 TO_CHAR语法

10.3.2 示例SQL

-- 查询员工编号，姓名，入职日期，日期格式使用'年-月-日'的格式显示
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

-- 查询员工编号，姓名，入职日期，拆分成三个列：年，月，日
select empno,ename,to_char(hiredate,'yyyy') 年,to_char(hiredate,'mm') 
月,to_char(hiredate,'dd') 日 from emp;

-- 查询员工编号，姓名，入职日期，格式：yyyy"年"mm"月"dd"日"
select empno,ename,to_char(hiredate,'yyyy"年"mm"月"dd"日"') 入职日期 from emp;

10.3.3 TO_DATE语法

10.3.4 示例代码

-- 把一个字符串'1985-04-30'按格式'yyyy-mm-dd'转成日期类型，再加 2 天
select to_date('1985-04-30','yyyy-mm-dd') + 2 from dual;

10.4 小结

1. 将日期转成字符串：to_char()
2. 将字符串转成日期：to_date()

第11节 通用函数

11.1 目标

学习空值函数，多条件判断函数和多条件判断语句

11.2 空值处理 nvl 函数


⚫ 需求：查询所有的雇员的姓名和年薪，年薪=月薪*12+奖金

我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null 和任何数值计算都是 null，这时我们可以使用 nvl 来处理。类似于 mysql 中的 ifnull 函数

-- 查询所有的雇员的姓名和年薪，年薪=月薪*12+奖金
select ename, sal, comm from emp;
select ename,sal * 12 + comm 年薪 from emp;

-- 如果为 null，则设置为 0
select ename,sal * 12 + nvl(comm,0) 年薪 from emp;

11.3 decode 函数


/* 查询出所有雇员的名字和职位中文名：CLERK 业务员，SALESMAN 销售，PRESIDENT 总裁，
ANALYST 分析师，MANAGER 经理，否则是 其他 */
select ename, decode(job,'CLERK','业务员','SALESMAN','销售','ANALYST','分析师','其他') 
职位 from emp;

11.4 case-when-else-end


/* 使用 case-when-end 查询出所有雇员的名字和职位中文名：CLERK 业务员，SALESMAN 销售，
PRESIDENT 总裁，ANALYST 分析师，MANAGER 经理，否则是其他 */
SELECT
	ename,
CASE
		job 
		WHEN 'CLERK' THEN
		'业务员' 
		WHEN 'SALESMAN' THEN
		'销售' 
		WHEN 'ANALYST' THEN
		'分析师' 
		WHEN 'MANAGER' THEN
		'经理' ELSE '其他' 
	END 职位 
FROM
	emp;

11.5 单行函数小结


第12节 多行函数和分组查询

12.1 目标

1. 多行函数的使用
2. 分组查询

12.2 多行函数(聚合函数)

12.2.1 语法


12.2.2 示例SQL

-- 所有员工人数
select count(*) 人数 from emp;

-- 查询出来员工最低工资
select min(sal) 最低工资 from emp;

-- 查询出员工的最高工资
select max(sal) 最高工资 from emp;

-- 查询出员工的平均工资
select round(avg(sal),2) 平均工资 from emp;

-- 查询出 20 号部门的员工的工资总和
select sum(sal) from emp where deptno=20;

-- 查询emp
select * from emp;

12.3 分组统计

12.3.1 分组语法


12.3.2 示例SQL

-- 查询出每个部门 ID 和平均工资，并且保留 2 位小数
select deptno 部门, round(avg(sal),2) 平均工资 from emp group by deptno;

-- 查询每个部门的人数
select deptno 部门, count(*) 人数 from emp group by deptno;

12.3.2 分组常见的错误

⚫ 疑问：部门编号，每个部门的人数。能否在上面再加 1 列员工姓名

在 oracle 中会出现错误:  ORA-00979: not a GROUP BY expression

分组与 MySQL 的区别：

mysql 中可以，会显示每组中第 1 个员工

⚫ select 后面只能出现：group by 后面列或聚合函数

-- 查询出部门平均工资大于 2000 的部门。先分组查询每个部门的平均工资，再过滤
select deptno 部门, round(avg(sal),2) 平均工资 from emp group by deptno having 
avg(sal)>2000;

12.4 小结

1. 多行函数：max,min,avg,count,sum
2. 分组查询 select … from … where… group by … having
   a) select 语句后面只能出现 group by 后面列或聚合函数
   b) having 后面可以出现聚合函数

第13节 内连接查询

13.1 目标

学习笛卡尔积，隐式内连接，显示内连接查询

13.2 笛卡尔积

select * from emp,dept;

在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

13.3 隐式内连接

-- 使用隐式内连接：查询所有的员工和部门，员工与部门的信息要匹配
select * from emp e,dept d where e.deptno = d.deptno;

13.4 显式内连接

-- 显式内连接：查询所有的员工和部门，员工与部门的信息要匹配
select * from emp e inner join dept d on e.deptno = d.deptno;

/*
查询出每个员工的名字和上级领导的名字
分析：emp 表中的 mgr 字段是当前雇员的上级领导的编号
所以该字段对 emp 表产生了自身关联，可以使用 mgr 字段和 empno 来关联
*/
select e.ename 员工名, m.ename 上级名 from emp e inner join emp m on e.mgr = m.empno;

-- 查询出每个员工名，部门名，工资等级，上级领导名字。按上级名字的升序排序
select e.ename 员工名, d.dname 部门名, s.grade 工资等级, m.ename 上级名字
from emp e, dept d, salgrade s, emp m 
where e.deptno = d.deptno 
and e.sal between s.losal 
and s.hisal 
and e.mgr = m.empno 
order by m.ename;

13.5 小结

1. 隐式：select … from …where
2. 显式：select … from … inner join … on

第14节 外连接查询

14.1 目标：

学习左，右，全连接的查询语法

14.2 左外连接

-- 添加一个员工，没有部门 ID	
insert into emp values (7935,'Lee','ANALYST',7566，to_date('1999-02-11','yyyy-mm-dd'),999，666，null);

-- 查询所有的员工信息，无论有没有部门
select * from emp e left join dept d on e.deptno = d.deptno;

14.3 右外连接

-- 查询出所有的部门和员工，把没有员工的部门也要显示出来
select * from emp e right join dept d on e.deptno = d.deptno;

14.4 全连接

-- 查询所有的员工和部门，无论员工或部门有没有对应的记录
select * from emp e full join dept d on e.deptno = d.deptno;

14.5 小结

1. 内连接：inner join … on
2. 左连接：left join … on
3. 右连接：right join … on
4. 全连接：full join … on

第15节 子查询

15.1 目标：

学习子查询的三种查询情况

15.2 子查询的三种情况：

1. 单行单列：使用比较运算符
2. 单行多列：也可以同时等于多个列
3. 多行多列：做为虚拟表再次查询

15.3 三种情况的操作

1. 子查询返回单行单列数据

   -- 查询比 SCOTT 工资高的员工
   select * from emp where sal > (select sal from emp where ename='SCOTT')

2. 子查询返回单行多列数据

   -- 查询出和 SCOTT 同部门同职位的员工，并且不显示 SCOTT 本人
   select * from emp where 
   deptno = (select deptno from emp where ename='SCOTT')
   and job = (select job from emp where ename='SCOTT') and ename<>'SCOTT';
   
   
   -- 优化代码
   select * from emp where (deptno,job) = (select deptno,job from emp where 
   ename='SCOTT') and ename<>'SCOTT';

   

3. 子查询返回多行多列数据

   -- 查询每个部门最低工资的：员工姓名，部门名，工资
   -- 1.表连接查询部门表和员工表，查询列：部门编号，部门名，每个部门的最低工资值。按部门编号和部门名称分组，得到多行多列的虚拟表。最小工资定义别名 minsal
   select d.deptno,d.dname, min(sal) minsal from emp e inner join dept d on e.deptno = 
   d.deptno group by d.deptno,d.dname;

   

-- 2.表连接查询虚拟表和员工表：查询员工名，部门名，工资。表连接条件是部门编号相等，而且工资等于最小工资
select e.ename 员工名, t.dname 部门名, e.sal 工资 from emp e inner join 
(select d.deptno,d.dname, min(sal) msal from emp e inner join dept d on e.deptno = 
d.deptno group by d.deptno,d.dname) t
on e.deptno = t.deptno and e.sal = t.msal;

15.4 小结

1. 单行单列：使用比较运算符
2. 单行多列：同时等于多列
3. 多行多列：虚拟表再次进行查询

第16节 分页查询

16.1 目标

学习 Oracle 中分页查询

16.2 伪列 ROWNUM：

ROWNUM 是 Oracle 数据库从数据文件中读取数据的顺序。它取得第一条记录则 ROWNUM 值为1，第二条为 2，依次类推。如果你用>, >=, =, between…and 这些条件，因为从表中得到的第一条记录的 ROWNUM 为 1，不满足 ROWNUM>5 的条件则被过滤。接着取下条，它的 ROWNUM 还是 1，又被过滤，依次类推便没有了数据。

解决方案：需要使用子查询将 rownum 以虚拟表的形式保存下来，再进行二次查询。

16.3 通用的写法

-- 分页查询每页显示 5 条
-- 查询emp
select * from emp;

-- 伪列 rownum
select rownum, e.* from emp e;

-- 查询第 1 页
select rownum, e.* from emp e where rownum > 0 and rownum<=5;

-- 查询第 2 页(查询不到数据，因为从表中得到的第一条记录的 ROWNUM 为 1，不满足 ROWNUM>5 的条件则被过滤。接着取下条，它的 ROWNUM 还是 1，又被过滤，依次类推便没有了数据)
select rownum, e.* from emp e where rownum > 5;

-- rownum 是对结果集进行编号
-- 将 rownum 做成一张虚拟表，再次查询
select t.* from (select rownum rn, e.* from emp e) t where rn>0 and rn<=5;
select t.* from (select rownum rn, e.* from emp e) t where rn>5 and rn<=10;
select t.* from (select rownum rn, e.* from emp e) t where rn>10 and rn<=15;

16.4 小结

分页中要使用伪列：rownum 代表的是查询结果编号，使用子查询来实现表分页

第17节 学习总结

17.1 能够理解 Oracle 数据库的基本概念

a) 数据库
b) 实例
c) 表空间
d) 物理文件

17.2记忆 Oracle DDL 语句

a) create tablespace … datafile … size… autoextend on
b) drop tablespace … including contents and datafiles
c) create user… identified by… default tablespace
d) grant 角色 to 用户名

17.3 掌握 Oracle 序列的使用

a) create sequnece
b) start with
c) increment by
d) maxvalue
e) cycle
f) nocache

17.4 掌握 Oracle 单行函数的使用


17.5掌握 Oracle 多行函数

a) max,min,count,avg,sum


17.6 学习并应用 Oracle 分组统计

a) select from where group by having

17.7 能够应用 Oracle 多表查询

a) 内连接：inner join … on
b) 左连接：left join … on
c) 右连接：right join … on
d) 全连接：full join … on

17.8 掌握 Oracle 子查询

a) 单行单列
b) 单行多列
c) 多行多列