Oracle 数据库

Oracle 数据库

​ [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZrjIuWHC-1692179442350)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-16-29.png)]

一. 基础查询

查询一张表中的所有数据的所有字段 select * from 数据源;

查询语法 : select 查询的数据(*|字段名|字段1,字段 2…|伪列) from 数据源 别名;

一条数据的所有字段值

执行流程: from --> select

1.* 的使用

查询|检索|获取 所有员工的所有信息

查询的数据 : 员工的所有信息 *

数据的来源 : 员工表 emp

条件 : 无

select * from emp;

2.单个数据

查询所有的员工名字

查询的数据 : 员工的姓名 ename

数据的来源 : 员工表 emp

条件 : 无

SELECT ename FROM EMP;

3.去重 distinct

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

select distinct deptno from emp;

4.伪列

表达式,整数,字符串

伪列 ： 数据源中不存在的字段，可以在select后面查询，假设存在

伪列 : 表达式,整数,字符串

select distinct 123*456 from emp;

5.取别名

select 数据 (as) 别名,数据 别名… from 数据源 别名;

“” 中的内容原封不动显示

如果别名为英文,默认大写,如果想要原封不动显示,前后添加一对""

select ename 员工姓名,sal "empSal",sal*12 as "年 薪" from emp e;
select e.* from emp e;

6.字符串

‘’ 作为伪列存在时,默认字段名字段值都为当前字符串

select deptno,dname,'xixihaha' "xixihaha" from dept;
select deptno,dname,'yjxxt' 公司名称 from dept;

字符串拼接 ||

select 'xixi'||'haha' from emp;
select 'YJX-'||ename 员工姓名 from emp;

7.虚表

虚表 ： dual

select 789*654  from dual;
select distinct 789*654 from emp;

8.null值的处理

null值与数字运算，结果为null

null值与字符串拼接，结果为原字符串

处理null值 : nvl(值1,值2) 当值1为null,nvl函数的结果为值2,当值1不为null,nvl函数的结果为值1

select ename,sal,comm,comm+1 from emp;
select ename,sal,comm,comm||'1' from emp;
select ename,sal,comm,nvl(comm,0)+1 from emp;

二. 条件查询与查询

条件查询 : select 要查询的数据 from 数据源 where 行过滤条件 order by 排序字段1,排序字段2…；

执行流程 ： from --> where --> select -> order by

条件判断运算符 ： = < > <= >= != <>

条件连接符 ： and or not

区间判断 ： 条件1 and 条件2 | between 值1 and 值2(相当于>=与<=)

判断null值需要使用is

在where不能使用字段别名，因为执行流程问题

1.条件判断运算符

-- 查询20部门的员工信息
-- 数据 ： 员工信息 *
-- 来源 : 员工表 emp
-- 条件 ： deptno = 20
select * from emp where deptno = 20;

-- 查询工资大于1000的员工的姓名，工作岗位，工资，所属部门编号
select ename, job, sal, deptno from emp where sal>1000;

-- 查询不在20部门工作的员工信息
select * from emp where deptno != 20;
select * from emp where deptno <> 20;
select * from emp where not deptno = 20;

2.查询与过滤

1.先过滤后查询

-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename,job,sal*12 from emp  where sal*12 > 20000;
-- 注意： 在where不能使用字段别名，因为执行流程问题
--select ename,job,sal*12 n from emp  where n > 20000; --报错

2.先查询后过滤

select ename,job,sal*12 r from emp;
--数据源可以为表也可以为结果集
select * from (select ename,job,sal*12 r from emp) where r>20000;

3.三个查询判断条件

查询 any(任意一个) some(任意一个) all(所有)

-- 查询工资比我们三个人都高的那些员工的信息 900,1000,1100
select * from emp where sal> all(900,1000,1100);

-- 查询工资比我们三个人都低的那些员工的信息
select * from emp where sal< all(900,1000,1100);

-- 查询比我们中随便一个工资高的员工信息
select * from emp where sal> some(900,1000,1100);

4.集合函数

集合函数 : 对结果集进一步操作

union并集去重、 union all并集不去重、 intersect交集 、minus差集

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

5.like 模糊匹配查询

需要配合 %任意个任意字符 _一个任意字符

escape 是指定一个字符作为转义字符

in (值列表) 判断值是否在in后面值列表中，如果在就满足条件，不在不满足

--查询员工姓名中包含字符A的员工信息
select * from emp where ename like 'A';--定值查询,比使用=效率低
select * from emp where ename like '%A%';

--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

--查询员工姓名中包含字符%的员工信息
select * from emp where ename like '%A%%' escape('A');

--in (值列表) 判断值是否在in后面值列表中，如果在就满足条件，不在不满足
SELECT * FROM EMP WHERE SAL IN (1500, 2000, 2500, 5000);

6.排序

排序 order by 排序字段1 desc降序|asc升序默认,排序字段2…

对结果集中的数据做排序

-- 查询30部门的员工信息， 并且按工资降序排序
select * from emp where deptno = 30 order by sal desc;

-- 查询30部门的员工信息， 并且按工资降序排序如果工资相同，则按照员工编号降序排序
select * from emp where deptno = 30 order by sal desc, empno desc;

-- 查询所有员工信息， 按照奖金升序排序  null
-- nulls first 所有的null值在最前,nulls last所有null值在最后
select *  from emp order by comm asc nulls first;

7. exists (结果集)

存在即保留,存在即合理

从 from后数据源中拿出每一条数据,判断是否满足where后的条件,如果是exists就判断exists()中的结果集中是否存在数据,存在当前判断的这条数据就满足跳进,不存在就过滤

执行流程 : from --> where --> select --> order by

-- 查询所有员工数据
select * from emp where exists (select * from dept);
-- 查询所有员工数据
select * from emp where exists (select * from emp where comm is not null);
-- 查询10,30部门 的员工信息
select * from emp where exists (select * from dept where deptno in (10,30) and dept.deptno = emp.deptno);
-- 别名
select *
  from emp e
 where exists (select *
          from dept d
         where deptno in (10, 30)
           and d.deptno = e.deptno);

8. 子查询

子查询 : 查询语句嵌套查询语句

当条件与要查询的数据在不同的数据源,而两个数据源之间存在一定的关联方式,可以子查询中转查询

-- 查询所有行记录
select * from emp;
select * from emp where 1=1;

-- 部门名称为 SALES 或 ACCOUNTING 的雇员信息
-- 数据 : 员工信息 *
-- 来源 : 员工表 emp
-- 条件 : dname in ('SALES','ACCOUNTING')
--查询 SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname in ('SALES','ACCOUNTING');
-- 在10,30部门的员工信息
select * from emp where deptno in (10,30) ;

select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));
       
 -- 查询工资比SMITH高的同一部门的员工信息
select sal from emp where ename='SMITH';
select deptno from emp where ename='SMITH';
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');

三.函数

内置函数

自定义函数

单行函数 : 一条记录返回一个结果

多行函数|组函数|聚合函数 : 多条记录返回一个结果

1.单行函数

1.时间

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

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

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

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

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

-- 当前时间
select sysdate from dual;
select current_date from dual;

-- 日期可以直接+-
-- 2天以后是几号
select sysdate+2 from dual;

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select ename,hiredate,hiredate+90 from emp;
select ename,hiredate,add_months(hiredate,-3) from emp;

-- 查询所有员工到目前为止一共工作了几个月
select ename,months_between(sysdate,hiredate) from emp;

-- trunc 取整
select ename,trunc(months_between(sysdate,hiredate)) from emp;

-- 查询当前月的最后一天
select ename,last_day(sysdate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期四') from dual;

2.日期对象与日期字符串之间相互转换

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

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

-- to_char
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
-- to_date
select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;

3.nvl(值1,值2) 对null值判断

判定函数 decode(判定字段,值1,结果1,值2,结果2…,默认值) 对判定字段的值进行判定,如果值为值1,函数的结果为结果1,与值2相等,函数的结果为结果2…最终如果以上都不相等,最终取默认值

-- 给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...

select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无') from  dept;

4.case when then else end

-- case when then  else end
select deptno,dname,loc,(case deptno when 10 then '十' when 20 then '二十' else '无' end) from  dept;

5.decode

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) 涨薪后 from emp;

-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;

2.组函数 |聚合函数 | 多行函数

多条记录返回一个结果

确定要计算的数据然后才能使用组函数,对结果集,分组

count :统计记录数 count() -->* 或一个列名

max min: 最大值 最小值

sum：求和

avg:平均值

注意:

1. 组函数仅在选择列表和Having子句中有效
2. 出现组函数，select 只能有组函数或分组字段

说明：

​ 组信息 与单条记录不能同时查询

​ 组函数 不能用在 where中，能使用的地方 select having

​ null 不参与运算

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZS2TP61Z-1692179442352)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-17-10.png)]

四.分组

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

语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段;

执行流程 : from -> where -> group by -> having -> select -> order by

如果 select后面一旦出现组函数,不能使用非分组字段

如果一旦分组,只能使用分组字段或者组函数

where中不能使用字段别名,不能使用组函数,因为执行流程的问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c0qkX6gJ-1692179442353)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-17-19.png)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PV1dfQzJ-1692179442353)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-17-23.png)]

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

1.group by : 分组

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

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

2.having : 过滤组

1. where : 过滤行记录，不能使用组函数
2. having : 过滤组 可以使用组函数

--按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
--按 部门岗位 查询 平均工资
select avg(sal) from emp group by deptno,job;
--按 部门 查询 平均工资，且平均工资大于2000的部门编号
--1、先分组 后过滤 (不推荐)
select *
from (select deptno, avg(sal) avsal from emp where 1 = 1 group by deptno)
where avsal > 2000;
--2、过滤组 ,分组同时 过滤
select avg(sal), deptno from emp group by deptno having avg(sal)>2000;

--查看 高于本部门平均薪水员工姓名
--1、按部门求出平均薪水
--2、关联子查询
select *
 from emp e
where exists
(select deptno
     from (select deptno, avg(sal) avgsal from emp group by deptno) e2
    where e.deptno = e2.deptno
     and e.sal > avgsal);
--另外一种 (推荐)
select *
 from emp e1
where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

3.行转列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7dE0FDW1-1692179442354)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-23-54.png)]

-- 找出课程名(表头)
select distinct course from tb_student;
-- 数据(行记录) 分组（学生+行转列 decode）
select * from tb_student;
-- 1、行转列 decode
select name,
   decode(course, '语文', score) 语文,
   decode(course, '数学', score) 数学,
   decode(course, '英语', score) 英语
 from tb_student;
-- 2、分组
select name,
   min(decode(course, '语文', score)) 语文,
   min(decode(course, '数学', score)) 数学,
   min(decode(course, '英语', score)) 英语
 from tb_student
group by name;

4.rowid 与 rownum

ROWID 是 ORACLE 中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID 它是一个伪列，它并不实际存在于表中。它是ORACLE 在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID 能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID 来完成的，而且使用ROWID 来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。

​ ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的结果集的顺序号，每一个结果集 都有自己顺序号 ，不能直接查询大于 1 的数。利用ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现分页操作。
ps: oracle 中 索引从 1 开始，java 程序 从 0 开始

1.rowid

实现重复记录的删除

要求：删除重复记录，一条记录只保留一次

思路 --> 将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一条记录即可，其他记录删除

--1、找出重复数据 :哪个学生 哪门课重复了
select name,course,count(1) from tb_student group by name,course; select
name,course,count(1) from tb_student group by name,course having count(1)>1;
--2、删除重复数据 :删除重复记录
--每条记录的唯一标识
select s.* , rowid from tb_student s;
--找出保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student where rowid not in (select min(rowid) from tb_student
group by name,course);

2.rownum

rownum :1、必须排序 2、不能直接取大于 1 的数

-- 最底层 rownum 数据库默认顺序号 -->没有用的
select emp.*, rownum
 from emp；
    select emp.*, rownum from emp order by sal;
-- 自己 排序后结果集的顺序号
select e.*, rownum from (select * from emp order by sal desc) e;
-- 取出工资前5名
select e.*, rownum
 from (select * from emp order by sal desc) e
where rownum <= 5;
-- 取出 工资 3-5 名
select e.*, rownum
 from (select * from emp order by sal desc) e
where rownum <= 5
 and rownum >= 3;
-- 三层模板 (分页)
select e.*
 from (select e.*, rownum rn
     from (select * from emp order by sal desc) e
    where rownum <= 5) e
where rn >= 3;
/*
select 字段列表 from (select e.*,rownum rn from (select from 表 order by 字段) e
where rownum<= 最大值)
where rn>=最小值
*/
select e.*
 from (select e.*, rownum rn
     from (select * from emp order by sal desc) e
    where rownum <= 10) e
where rn >= 6;

五. 表连接

表连接查询 : 当要查询的数据来自于多个数据源

1.92语法

92语法 select 数据 from 数据源1,数据源2… where 行过滤条件 group by

分组字段1,分组字段2… having 组过滤信息 order by 排序字段1,… desc|asc;

执行流程 : from --> where --> group by --> having --> select --> order by

笛卡尔积 : 交叉连接

非* 必须区分 使用表名 或别名.区分

-- 查询所有员工的信息以及员工所在部门信息
-- 数据 : 员工信息*  部门信息*
-- 来源 : 员工表emp ,部门表 dept
select * from emp , dept;
select ename , dname from emp , dept;
select ename, dname, e.deptno from emp e, dept d;

表连接条件 : 过滤通过连表产生的不满足要求的表连接数据

等值连接 : 判断两个数据源中的某个字段值相等或者不相等

非等值连接 : 判断区间,判断范围的条件

92语法中表 连接条件定义 在where后面

如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处

select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno;

--查询20部门员工的信息以及员工所在部门信息
select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;

--非等值连接
--查询每个员工的员工信息以及薪资等级信息
select * from emp e,salgrade s where sal between losal and hisal;
--查询员工信息以及所在部门信息以及薪资等级信息
select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal;

--自连接 : 一张表作为两个数据源使用
--查询有上级的员工信息以及上级经理人信息
--数据 : 员工信息  经理人信息
--来源 : 员工表 emp e1 ,经理人表 emp e2
select * from emp e1,emp e2 where e1.mgr=e2.empno

内连接 : 满足连接条件查询到不满足过滤掉

外连接 : 某一个数据源中的数据不满足连接条件的时候也能显示

左外连接 | 左连接 : from后面主表在左边

右外连接 | 右连接 : from后面主表在右边

主表 : 主表中的数据无论是否满足连接条件都能显示

92语法 : 在连接条件位置,主表的对面添加(+)

--查询所有员工信息以及上级经理人信息
--主表 : 员工表
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
select * from emp e1,emp e2 where e1.mgr(+)=e2.empno;

2.99语法

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

select * from emp cross join dept;

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

注意: 在自然连接中同名字段的不能使用限定词

select ename,deptno,dname from emp natural join dept;

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

注意: 在join…using中同名字段的不能使用限定词

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

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

注意: 在join…on中同名字段的必须使用限定词

既能够做等值连接,也能做非等值连接

select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno;
-- 非等值连接
-- 查询每个员工的工资，姓名，工种，工资等级
select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal;

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

外连接 : 主表中的数据无论是否满足连接条件都显示

左外连接|左连接 : left join

右外连接|右连接 : right join

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

--所有员工存在的员工信息 与上级信息

--主表 :  员工
select * from emp e1 left join emp e2 on e1.mgr=e2.empno;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;

select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

六.DML

DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aSBcYXiO-1692179442355)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_09-57-04.png)]

update 更新修改

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

update dept set loc='三亚' where deptno = 70;
update dept set dname='A'||dname,loc='青岛' where deptno in (50,60,70);

update 表名 set (字段列表) =(select 字段列表 from 源表 where 过滤源表记录) where 更新记录的条件手动更改字段值：

update dept set (dname,loc) =(select ename,job from emp where empno=7369) where deptno in (50,60);

delete

删除一个或多个或所有数据

delete from 表名; 删除一张表中所有数据

delete from emp_his2;

delete from 表名 where 行过滤条件; 满足条件的数据删除

delete dept where deptno = 70;

删除主外键约束关系下的两张表中的数据:

 --从表中的数据可以直接正常删除 emp
       delete from emp where empno = 7369;

删除主表中数据 :

--主表中没有被从表引用的数据,可以直接删除
           delete from dept where deptno = 50;
           --主表中已被从表引用的数据,不能直接删除
           delete from dept where deptno = 10;

解决方案:

1.删除从表中引用了当前主表数据的那些从表删除,然后再删除当前主表数据->默认

delete from emp where deptno = 10;
delete from dept where deptno = 10;

2.删除主表数据的同时,为从表所有引用了当前主表数据的那些从表数据的外键字段设置为null

需要为从表中外键字段设置约束的时候 on delete set null

delete from clazz where cid = 55;

3.删除主表数据的同时,直接删除从表所有引用了当前主表数据的那些从表数据

需要为从表中外键字段设置约束的时候 on delete cascade

delete from clazz where cid = 55;

--测试数据 
create table clazz(
    cid number(5)  primary key,
    cname varchar2(30)
);

create table student(
    sid number(5)  primary key,
    sname varchar2(30) not null,
    --外键约束
    --cid number(5) references clazz(cid) on delete set null
    cid number(5) references clazz(cid) on delete cascade
);


insert into clazz values(55,'java55');
insert into clazz values(54,'java54');

insert into student values(1001,'zhangsan',55);
insert into student values(1002,'lisi',55);
insert into student values(1003,'wangwu',54);


select * from clazz;
select * from student;

drop table student;
drop table clazz;

1.事务

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

2.事务的特点

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

1. 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
2. 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
3. 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
4. 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢
   失。

3.事务的隔离级别

当事务之间发生并发时有几个隔离级别:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bJIigdcT-1692179442355)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_11-17-09.png)]

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 的记录，新纪录就叫做幻读。

4.事务的开启与结束

事务的开启: 执行DML中的insert delete update默认开启事务

事务的结束 :

1. 成功

​ 手动提交 ：使用 commit

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

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

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

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

2. 失败

​ 意外退出,强制断电

​ 手动提交 ：使用 rollback

rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit 之后已经持久化到数据库中

5.截断数据与序列

1.截断

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 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

2.序列

–工具 ,管理流水号

​ --管理类似主键字段的值,数值型的,有变化规律的

​ --序列没有与表与字段绑定，序列的 删除不影响之前的使用

​ --第一次使用序列需要先获取下一个最新值

创建

create sequence 序列名 start with 起始值 increment by 步进;
create sequence seq_tb_user start with 2 increment by 2;

使用

在操作数据 添加 更新 -->主键
1)、currval :当前值
2)、nextval:下个值
select seq_tb_user.nextval from dual;
select seq_tb_user.currval from dual

删除

drop sequence 序列名;
drop sequence seq_tb_user;

七.DDL

–DDL 数据定义语言 了解能做修改
–create表创建 drop删除表 alter修改表结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9WrRlaA3-1692179442356)(C:\Users\dis\Desktop\笔记\Oracle\dis\Snipaste_2023-08-16_10-29-08.png)]

1.创建表

表名必须唯一，如果存在 ，必须删除

create table 表名(
字段名 类型(长度) 约束,
...其他字段....
..约束........
);

2.不加约束

表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键 
username 用户名 varchar2(30) 否 长度在4-20
userpwd 密码 varchar2(20) 否 长度在4-18
age 年龄 number(3)  18 大于>=18 
gender 性别  char(2) 男 男or 女
email 邮箱 varchar2(30) 唯一 
regtime 注册日期  date sysdate
--创建表（不加约束）

create table tb_user(
  userid number(5),
  username varchar2(30),
  userpwd varchar2(20),
  age  number(3) ,
  gender char(1 char) ,
  email  varchar2(30),
  regtime date
);
--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日期';

3.创建表（同时创建约束+指定名称） 在字段的后面指定约束名直接添加

create table  tb_user(
  --主键约束
  userid number(5) constraint pk_user primary key,
  --非空约束
  username varchar2(30) constraint nameNotNull not null, 
  --检查约束 
  userpwd varchar2(20) constraint ckPwd check(length(userpwd) between 4 and 18), 
  --默认约束  检查约束
  age number(3) default(18) constraint ck_age check(age>=18),
  --默认约束  检查约束
  gender char(3) default('男') check(gender in('男','女')),
  --唯一约束
  email  varchar2(30) unique,
  --默认约束
  regtime date default(sysdate)
);

4.创建表（同时创建约束+指定名称） 在结构结束之前为当前表中指定字段添加约束

create table  tb_user(
  userid number(5),
  username varchar2(30),
  userpwd varchar2(20) constraint nn_user_pwd not null , 
  age number(3) default(18) ,
  gender char(3) default('男'),
  email  varchar2(30),
  regtime date default(sysdate),

  constraint pk_user_id  primary key (userid),
  constraint name_not_null check(username is not null),
  constraint ck_user_name check(length(username)between 4 and 20) , 
  constraint ck_user_pwd check(length(userpwd) between 4 and 18), 
  constraint ck_user_age check(age>=18),
  constraint ck_user_gender  check(gender in('男','女')),
  constraint uq_user_email unique(email)
);

5.追加约束

alter table tb_user add constraint pk_user_id  primary key (userid);
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ; 
alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18); 
alter table tb_user add constraint ck_user_age check(age>=18);
alter table tb_user add constraint ck_user_gender check(gender in('男','女')); 
alter table tb_user add constraint uq_user_email unique(email);

6.默认与非空

alter table tb_user modify age default 18;
alter table tb_user modify username not null;

7.已有表中拷贝结构

create table 表名 as select 字段列表 from 已有表 where 1!=1;

--拷贝结构 emp
create table emp_his as select ename,sal from emp where 1!=1; 
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from emp where sal>2000;

8.删除表

drop table 表名 (cascade constraints)

删除 （先删除从表 再删除主表 ；同时删除约束）

主外键关系下的两张表的删除

​ 删除从表 : 直接删除 drop table student

​ 删除主表 : 不能直接删除

​ 1)先删除所有从表,再删除主表

​ 2)删除主表的同时级联删除主外键约束 drop table clazz cascade constraints;

drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
drop table student cascade constraints;

外键约束

 -- 1)cid number(5) references clazz(cid)
 -- 2)cid number(5) constraint fk_cid references clazz(cid)
    cid number(5)
 -- 3) constraint fk_cid foreign key(cid) references clazz(cid) )
 -- 4)
alter table student add constraint pk_stu_cid  foreign key(cid) references clazz(cid) ;

物理约束 : 表结构上为字段添加约束,推荐主外键约束可以定义物理约束

逻辑约束 : 在后端逻辑代码上添加判断,逻辑,检查等约束建议定义为逻辑约束

9.修改表

1. 修改表名 :rename to
2. 修改列名: alter table 表名 rename column to
3. 修改类型: alter table 表名 modify(字段 类型)
4. 修改约束: 先删除 后添加
5. 添加列: alter table 表名 add 字段 类型
6. 删除列:alter table 表名 drop column 字段

八.表设计

明确表的作用 表名

明确表中的字段

​ 字段名

​ 字段类型 : number char varchar2 date

​ 字段约束 :

​ 主键约束(唯一+非空)

​ 唯一约束

​ 非空约束

​ 默认约束

​ 检查约束

​ 外键约束 Emp Dept

​ 涉及到两张表

​ 父表|主表

​ 子表|从表 : 子表中添加一个外键字段,关联主表的主键字段

​ 外键字段的值只能为主表中主键字段已有的值

        select  * from emp;
        select  * from dept;

1.设计表的三范式

​ 最终的目的避免数据重复冗余

​ 1NF–>列不可再分最小原子 （避免重复);

​ 2NF–>主键依赖(确定唯一)；

​ 3NF–>消除传递依赖(建立主外键关联 拆分表);**

2.表与表之间的关系 ：

​ 一对一关系 : 用户表 身份证信息表
​ 主外键关联关系
​ 一对多|多对一 : 班级表 学生表
​ 主外键关联关系 : 在多的一方设置外键,关联一的一方的主键
​ 多对多 : 订单与商品 学生与选课
​ 中间表 : 定义两张表的关联关系

九.视图与索引

1.视图:建立在表|结果集|视图上的虚拟表，有以下作用

1. 简化:select 查询语句

2. 重用:封装select语句 命名

3. 隐藏:内部细节

4. 区分:相同数据不同查询

不是所有的用户都有创建视图的权限

1. 前提： create view -->组 connect resource dba

2. 授权: -->sqlplus /nolog

   a)、sys登录 conn sys/123456@orcl as sysdba

   b)、授权: grant dba to scott;

   回收: revoke dba from scott;

   c)、重新登录

create or replace view 视图名 as select语句 [with read only]

要求:所有列必须存在名称。

删除试图

drop view vw_xixi_haha;

修改数据源中的数据

update emp set sal  = 1000 where empno = 7369;
update vw_xixi_haha set sal  = 800 where empno = 7369;

2.索引

前提 : 设计表首先应该按需遵循三范式

1. 确定表名

2. 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)

主键： 唯一标识一条记录（唯一并且非空）

唯一： 唯一

非空：不能为空

默认： 当没给值时使用给定一个默认值

​ 外键：参考其他表（自己）的某个（某些）字段

​ 检查：自定义的规则

​ 索引: 提高查询速度的一种手段 -->目录

​ 1、唯一性较好字段适合建立索引

​ 2、大数据量才有效果

​ 3、主键|唯一: 唯一索引

create index 索引名 on表名 (字段列表...)
drop index 索引名
create index idx_emp on emp(sal,ename);
drop index idx_emp;
select * from emp order by sal,ename;