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 中通常
一个项目对应一个用户。

第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

   1 2 3 4 5

   -- 创建表空间 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

   1 2	-- 删除 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

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

      4.2.3 运行效果

      

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

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

      4.3 用户赋权限

      4.3.1 Oracle 中的三种角色

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

   3. connect

   
   2) resource

   
   3. dba

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

   4.3.2 授予权限语法

   

   4.3.3 授予权限SQL

   1 2	-- 进入 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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   /* 以 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

   1 2 3 4 5

   -- 删除全部数据 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

   1 2 3 4 5 6 7 8

   -- 在 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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   -- 插入部分列：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

   1 2 3 4 5 6 7 8 9 10 11

   -- 创建一个序列名为 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 序列的操作属性

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

   

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   -- 查询当前的序列号 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 在插入数据时使用序列

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

   1 2 3 4 5 6 7 8

   -- 示例：使用序列插入自动增长的主键，插入记录"白骨精",性别：女，生日，地址为广州 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

   1 2 3 4 5

   -- 将所有人的地址改成天河区 update person set home='天河区'; -- 将 pid 为 2 的记录，改成地址改成深圳 update person set home='深圳' where pid=2;

   8.3 删除 DELETE

   8.3.1 语法

   

   8.3.2 示例SQL

   1 2	-- 删除 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 的密码

      1 2 3 4 5 6 7 8

      -- 查看用户列表 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图表，即可展示表与表之间的关系

      10.2.1 日期函数语法

      

      10.2.2 示例SQL

      1 2 3 4 5 6 7 8 9 10 11 12

      -- 查询系统现在的日期 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

      1 2 3 4 5 6 7 8 9

      -- 查询员工编号，姓名，入职日期，日期格式使用'年-月-日'的格式显示 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;

      第11节 通用函数

      11.1 目标

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

      11.2 空值处理 nvl 函数

      

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

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

      1 2 3 4 5 6

      -- 查询所有的雇员的姓名和年薪，年薪=月薪*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.4 case-when-else-end

      

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

      /* 使用 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. 多行函数的使用

      12.2 多行函数(聚合函数)

      12.2.1 语法

      

      12.2.2 示例SQL

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

      -- 所有员工人数 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

      1 2 3 4 5

      -- 查询出每个部门 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 列员工姓名

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

      分组与 MySQL 的区别：

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

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

      1 2 3

      -- 查询出部门平均工资大于 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 笛卡尔积

      1	select * from emp,dept;

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

      15.3 三种情况的操作

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

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

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

         1 2 3 4 5 6 7 8 9

         -- 查询出和 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 2 3 4

         -- 查询每个部门最低工资的：员工姓名，部门名，工资 -- 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;

         1 2 3 4 5

         -- 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;

         第16节 分页查询

         16.1 目标

         学习 Oracle 中分页查询

         16.2 伪列 ROWNUM：

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

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

         16.3 通用的写法

         1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

         -- 分页查询每页显示 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) 多行多列