oracle

数据库

1.数据库管理系统 DBMS

---

​ 数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

1.1 分类

1. 关系型数据库

   ​ 关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

   ​ 当前主流的关系型数据库有 Oracle、DB2、Microsoft SQL Server、Microsoft

   Access、MySQL等。

2. 非关系型数据库

1.2 命令分类操作

分类	命令
DDL	create：创建；drop：删除；alter：修改；rename: 重命名; truncate：截断
DML	insert：插入；delete：删除；update：更新；select：查询
DCL	grant：授权；revoke：回收权利；commit:提交事务；rollback：回滚事务

2.查询语句

---

​ 查询一张表中的所有数据的所有字段 select * from 数据源;
​ 查询语法 : select 查询的数据(*|字段名|字段1,字段 2…|伪列) from 数据源 别名;
一条数据的所有字段值
执行流程: from --> select

2.1 去重 distinct

​ 对结果集中完全相同的多条数据只显示一条

2.2 别名

​ 使用别名便于操作识别 、隐藏底层信息。存在字段别名和表别名

select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

​ as: 字段别名可以使用as;表别名不能使用as

​ " ":原样输出，可以存在 空格与区分大小写

2.3 字符串

​ 使用’’表示字符串(注意区分””) ，拼接使用 ||

select 'my' from emp;
select ename||'a'||'-->' info from emp;

2.4 伪列

​ 不存在的列，构建虚拟的列

select empno, 1*2 as count,'cmj' as name,deptno from emp;

2.5 虚表 dual

​ 用于计算表达式，显示单条记录的值

select 1+1 from dual;

2.6 null

​ null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;
select ename,sal*12+comm from emp;
--nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值 select
ename,sal*12+nvl(comm,0) from emp;

​ null不能使用条件判断，只能使用is

--存在佣金的员工名称
select * from emp where comm is null;
--不存在佣金的员工名称
select * from emp where comm is not null;
select * from emp where not comm is null;

2.7 where

​ where 过滤行记录条件 ,条件有:

​ a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and

​ b)、and 、or、 not、 union、 union all、 intersect 、minus

​ c)、null :is null、 is not null、 --not is null

​ d)、like :模糊查询 % _ escape(‘单个字符’)

​ f)、in 、 exists(难点) 及子查询

2.8 集合操作

​ Union、Union All、Intersect、Minus

• Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
• Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
• Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
• Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序；

--查询工资大于1500 或 含有佣金的人员姓名
--union 去除重复行
select ename from emp where sal>1500
union
select ename from emp where comm is not null;
-- union all 不去除重复行
select ename from emp where sal>1500
union all
select ename from emp where comm is not null;
--查询显示不存在雇员的所有部门号。
select deptno from dept
minus
select distinct deptno from emp
--查询工资大于1500 且 含有佣金的人员姓名
select ename,sal,comm from emp where sal>1500 intersect
select ename,sal,comm from emp where comm is not null;

2.9 模糊查询 like

​ 模糊查询，使用通配符:

​ 1. %:零个及以上(任意个数的)的字符

​ 2. _:一个字符

​ 3. 遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

2.10 in 和 exists

​ in相当于使用or的多个等值，定值集合 ,如果存在子查询，确保类型相同、字段数为1，如果记

录多，效率不高，用于 一些少量定值判断上

​ exists条件为true,存在记录则返回结果，后续不再继续 比较查询，与查询的字段无关，与记录

有关

–exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关

–exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)

2.11排序

​ 使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。

​ 顺序 :asc（默认） desc

​ 多字段: 在前面字段相等时，使用后面的字段排序

​ 空排序: 降序为 desc，注意 null 为最后 (null first || last)

3.函数

​ 函数分为系统内置函数 自定义函数（后期学习的 plsql 中定义）；了解系统内置函数（方

法），重点掌握 to_date，to_char （字符和日期的转换）根据函数的返回结果，我们将函数分为单

行函数和多行函数

3.1单行函数

3.1.1日期函数

​ 日期函数: 注意区分 db数据库时间 ，java应用服务器的时间。以一方为准 oracle以内部数字格式存储日期年月日小时分钟秒

​ sysdate/current_date 以date类型返回当前的日期

​ add_months(d,x) 返回加上x月后的日期d的值

​ LAST_DAY(d) 返回的所在月份的最后一天

​ months_between(date1,date2) 返回date1和date2之间月的数目

​ next_day(sysdate,星期一) 下一个星期一

当前时间

select current_date from dual where 1=1;
select sysdate from dual where 1=1;

修改日期

--两天后的时刻
select sysdate+2 from dual;

修改月份

--当前5个月后的时间
select add_months(sysdate,5) from dual; --雇佣日期 2个月的时间
select ename,hiredate, add_months(hiredate,2) after from emp;

月份之差

--雇佣日期 距离现在的 月份数
select ename, months_between(sysdate , hiredate) from emp;

最后一天

--返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;

下一个星期的时间

--下一个星期二
select next_day(sysdate, '星期二') from dual;

3.1.2 转换函数

​ to_date(c,m) -> 字符串以指定格式转换为日期

​ to_char(d,m) -> 日期以指定格式转换为字符串

select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(sysdate, 'yyyy\mm\dd') from dual;
--注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日') from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

3.1.3 其他函数

​ --1)、nvl nvl(string1,string2) 如果string1为null,则结果为string2的值

select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),'hello') from emp;

--2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn,

expressionm)

select ename,decode(deptno, 10,'十',20,'二十') from emp;

​ --3)、case when then else end

select ename,sal,deptno,
(case deptno
when 10 then sal * 1.1
when 20 then sal * 1.08
when 30 then sal * 1.15
else sal * 1.2
end) raisesal
from emp;

3.2组函数

3.2.1 求和 count

select count(comm) from emp where comm is not null; --推荐
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from emp;
--统计10和20部门一共有多少人
select distinct(count(1)) from emp where deptno in(10,20);

3.2.2 最大、最小值

--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;
--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp; 

3.2.3 sum 求和

-- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

3.2.4 avg 平均

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal) from emp);
--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where
e1.deptno=e2.deptno );

4.分组

​ group by , 将符合条件的记录 进一步的分组

​ 过滤组:having, 过滤组信息 ，表达式同where 一致

结构：

select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc

执行顺序：

1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by

注意事项：

​ group by : 分组

​ 1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段

​ 2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by中

​ having : 过滤组

​ \1. where : 过滤行记录，不能使用组函数

​ \2. having : 过滤组 可以使用组函数

5.表连接

99连接

​ 交叉连接 cross join —>笛卡尔积

​ 自然连接(主外键、同名列) natural join -->等值连接

​ join using连接(同名列) -->等值连接

​ [inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分

​ left|right [outer] join on|using -->外连接

​ full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

5.1交叉连接

select * from emp cross join dept;

5.2自然连接

​ 自动做等值连接

select * from emp natural join dept;
--在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;

5.3 using 连接

​ 指定同名字段做等值连接

select deptno,e.ename,d.dname from emp e join dept d using(deptno);

5.4 on连接

-- natrual 等值
select ename, dname from emp natural join dept where deptno = 30;
--using
select ename, dname from emp join dept using (deptno) where deptno = 30;
--on
select ename, dname
from emp
join dept
on emp.deptno = dept.deptno
where emp.deptno = 30;

5.5 外连接

​ 左外 left … join …

​ 右外 right … join …

5.6全连接

​ full join

6. DML

---

6.1 insert

insert into 表名 [(字段列表)] values(值列表); 添加记录

添加记录时需要满足一下条件

​ 类型 长度 兼容: 字段 兼容值

​ 值满足约束 :主键 (唯一+非空) 非空(必填) 唯一(不重复 ) 默认(没有填写使用默认值) 检查(满足条件)外键(参考主表主键列的值)

​ 个数必须相同: 指定列，个数顺序与列相同；没有指定，个数与表结构的列个数和顺序相同 (null也得占位，没有默认值)

6.2update

update 表名 set 字段=值 [,....] where 过滤行记录;

要求：

​ \1. 记录存在

​ \2. 类型 长度 兼容: 字段 兼容值

​ \3. 个数相同

6.3 delete

delete [from] 表名 where 过滤行记录

说明：

​ \1. delete 可以删除指定部分记录，删除全部记录

​ \2. 记录上存在主外键关联时， 删除存在关联的主表的记录时,注意 参考外键约束， 约束强制不让删除先删除从表 再删除主表

6.4截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联，截断数据同时从结构上检查

create table emp_his as select * from emp where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: truncate table dept;

截断数据与删除数据区别 truncate 与delete 区别

1、truncate -->ddl ，不涉及事务，就不能回滚

delete -->dml ,涉及事务，可以回滚

2、truncate 截断所有的数据 delete 可以删除全部 或者部分记录

3、truncate从结构上检查是否存在主外键，如果存在，不让删除

delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

7.事务

​ 事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成或者全部不完成。使用事务是为了保证数据的安全有效。

7.1 事务的特点

事务有一下四个特点：（ACID）

1. 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。

2. 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。

3. 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。

4. 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。

7.2事务的隔离级别

Oracle 默认的隔离级别是 read committed。

Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外， Oralce 中还定义

Read only 和 Read write 隔离级别。

Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。

Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。

丢失更新：两个事务同时存储， 一个存储 100 ， 一个存储 200，最终可能至存储了 200 或者 100，那
另一个的更新就没成功，即结果不为预想的 300
脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1回滚，T2 读取的数
据无效，这种数据称为脏读数据。
不可重复读：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，发现与第一次读取
的记录不相同，称为不可重复读。
幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，恰好也是 T1 的
WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫做幻读。

7.3 事务的开启

​ 自动开启于 DML 之 insert delete update

7.4 事务的结束

\1. 成功

​ 正常执行完成的 DDL 语句：create、alter、drop

​ 正常执行完 DCL 语句 GRANT、REVOKE

​ 正常退出的 SQLPlus 或者 SQL Developer 等客户端

​ 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)

​ 手动提交 ：使用 commit

\2. 失败

​ rollback ，手动回滚

​ 非法退出 意外的断电