SQL知识点3 - oracle

数据操作语言(DML) - 数据行的处理

主要实现对数据库表中的数据进行操作

insert - 增加数据行

• 注意使用规则：
  1. 列名与列值必须一 一对应
  2. 如果列名没有写，则为该表的所有列
  3. 列值为空：可以赋值为 null
  4. 某列列值设为：’&+变量名’ → 执行该语句，弹出该列的列值设置
  5. 多行插入时的子查询语句：列名、列数据类型必须一致

语法结构

insert into 表名 ( 列名 ) values ( 列值 );      //  一次只能加一条记录

insert into 表名 ( 列名 ) 子查询         // 多行同时插入，使用外表的数据

create table 表名 as 子查询        // 创表时复制子查询的列结构、列类型，以及拷贝选中的数据

create table 表名 as 子查询→where 1=0    // 子查询中的where 1=0,则只复制子查询中的列结构，而不会复制数据

 
 
示例1：增加一行

insert into dept (deptno, dname, loc) values (80, '&test', null);    // 执行后弹出如下对话框

示例2：复制表结构、以及批量增加表数据

create table emp_copy as select * from emp where 1=0  // 只复制表结构

insert into emp_copy select * from emp    //  把emp的数据全部复制到 emp_copy

 

update - 修改数据行的列值

语法结构

update 表名 set 列名 = 新列值  where 条件     // 条件：限制修改的行，不写条件则所有行指定列赋新值

// 相关子查询修改 行列值
update 表名1 表别名1 
set 列名 = ( select 列名/表达式 from 表名2 表别名2 where 表别名1.列名 = 表别名2.列名 );

 
 

示例1：利用where更新行列值

update emp set sal = 200 where empno = 7782;    // 只有员工号=7782行的  sal=200
update emp set sal = 200    // 所有行的 sal列都是 200

 

示例2：利用相关子查询修改行列值

alter table emp add( dname varchar2(14) )    // emp增加新列  dname

// 根据 deptno部门号 进行 赋值dname
update emp 
set dname = (select dname from dept where emp.deptno = dept.deptno);

 

delete - 删除数据行

语法结构

delete 表名 where 条件/子查询/相关子查询     // 删除符合 where条件的  数据行
delete 表名                  // 删除表中的所有数据

 
 

示例1

// 指定具体值条件
delete emp where deptno=7782;

// 子查询进行删除数据行
delete emp where deptno = (select deptno from dept where dname='SALES');

// 相关子查询进行删除行
// 删除没有员工的部门信息
delete dept 
where not exists (select empno from emp where deptno = dept.deptno );

 

merge - 合并数据行

使用规则

• merge语句能同时实现update、insert、delete
• on来限定 matched、not matched的条件
• where可筛选需要操作的行
• delete只能放置在update里面 – 匹配on以及where的条件才能删除

语法结构

merge into 表名1 表别名1
using 表名2 表别名2
on ( 匹配条件 )
when matched then 
update
set 表别名1.列名 = 表别名2.列名,表别名1.列名 = 表别名2.列名
where ( 选定更新的行条件 )
delete where ( 满足的删除条件 )     //  匹配 on以及这里的where 才能删除行
when not matched then 
insert
values (表名2的各列名)
where ( 选定表名2需要插入到表名1的行 )

 
 

  示例1

// 注意这 整段代码 为一个sql语句

merge into dept_copy1 one           // 把 dept表 的数据合并到 dept_copy表
using dept two
on (one.deptno = two.deptno)

when matched then
  update set one.dname = two.dname, one.loc = two.loc  
  delete where (one.deptno in (60)      // 删除 满足 on以及该where条件的行
  
when not matched then
  insert values (two.deptno, two.dname, two.loc);       

事务 – transaction

事务基本概念

事务特点 ( ACID )：

• 原子性： 一个事务内DML要么都成功，要么都不成
• 一致性： 整个系统处于数据一致的状态 — 两用户进行转、收钱 → 总额不变
• 隔离性： 事务的执行不会被另一个事务干扰
• 持久性： 事务一旦提交，数据被永久改变，不可回滚

 

事务的结束-- 各种情况

为什么会发生隐式回滚？→ 内存的速度比硬盘速度快几千倍 2000：200：100

• 内存条特性： 一断电什么数据都没有
• 未commit的数据 保存在 database-buffer-cache — 即内存条而不是硬盘
• buffer： 内存中的数据 准备放入到硬盘上 的数据区 → 数据行修改的数据
• cache： 硬盘的数据拷贝到内存上 的数据区 → 查询表的数据行

 

事务的隔离级别

• 并放访问时出现的三大问题：
  1. 脏读： 其他事务能读出并未提交的数据行
  2. 不可重复读： 同一个事务读取同一行的数据不一致 — update导致
  3. 幻影 / 幻行： 同一个事务读取的数据行总数不一样 — insert、delete导致

 

隔离级别分类：

1. Read Uncommitted： 可读并未提交的数据

2. Read Committed： 只能读取已提交的数据

3. Repeatable read： 同一个事务读取同一行数据不会改变

4. serializable： 当前事务仅读取的是：事务开始前的数据 以及 本事务所做的更改

   • 注意： 因为以事务开始前的数据为标准，只要select就是事务开启
      
   • 同会话窗口事务级隔离可被继承，会话窗口级不会被继承

5. Read only：serializable的子集，只能读取不能更改 — 事务级不可被继承，每个事务需手动设置

 

① 事务隔离级别语法修改 — oracle

会话级别隔离修改：2种

// oracle只有两种窗口型事务级别
alter session set isolation_level = 隔离级别(Read Committed / serializable ) 

 
事务级别隔离修改： 3种

// 此隔离事务可以被继承
set transaction isolation level 隔离级别(Read Committed / serializable ) 

// serializable的子集，只能读不能修改( 即DML数据行的操纵不可以)   --  此隔离事务不能被继承
set transaction read only  

 

② 隔离不能修改的解决方案

如下图：

解决方案

// 圆号数字表明是执行顺序

// ①   ----   修改了整个会话窗口每个事务的隔离级别
alter session set isolation_level = serializable   

// ②    ---- 事务结束
commit;

// ③     ---   只有事务结束才能修改。如果打开了多个session窗口，则需多次在同一个session中执行②③语句        
alter session set isolation_level = Read Committed 

 

③ serializable隔离级别测试

1. 当前事务的数据状态只能是 事务前 以及 当前事务的DML操作

SQL窗口1

alter session set isolation_level = serializable;   // ①

select * from emp;   //②  ----  开启事务

insert into emp  (empno, sal) values (1000, 100);  //⑤

commit;   //⑥       ---- 结束事务

 
SQL窗口2

alter session set isolation_level = serializable;   //③    

select * from emp;     //④ ⑦ ⑨  ----  开启事务

commit    //    ⑧   

// ②④ 得到的数据是一样的
// ⑤⑥ 添加一行新的数据行，并提交       ⑦依然还是④的数据，并没有变化
// ⑧  结束事务， ⑨得到的是更新后的新数据

 
 

2. 两个事务不能同时对同一数据行进行修改、删除

SQL窗口1

alter session set isolation_level = serializable;   //①
select * from emp;      // ②
delete emp where empno = 1000;     // ⑤    --- 同理相同情况update 也是一样的异常
commit;          // ⑦  弹出上述窗口的异常 → 由窗口2导致的

rollback;      //  ⑦   回滚则不会导致上述窗口异常  --- 表明并行的事务当前只能由一个事务进行修改( 以commit为准 )。

SQL窗口2

alter session set isolation_level = serializable;       // ③
select * from emp;          // ④ 
delete emp where empno = 1000;      //  ⑥     ----  正在执行

commit;   // ---- ⑧  窗口1执行 ⑦rollback  则  执行⑧

 

设置回滚点

• 注意事项
  1. 回滚到一个事务内指定的位置，而不是回滚到事务的起点
  2. 回滚到保存点并未结束事务

 
  示例1

elete dept where deptno = 10;      // 事务开始    ①
savepoint one;
delete dept where deptno = 20;      // ②
savepoint two;
delete dept where deptno = 30;      // ③
savepoint three;

rollback to two          //  回滚到two之前的 所有DML操作，而撤销其之后的DML操作 -- 即执行①②、没有执行③

rollback/commit    //  事务结束

 

数据库对象

数据库对象：数据库的组成部分
DDL：负责数据库结构定义、数据对象定义
DDL来操纵数据库对象

 
对象命名原则：

• 字母开始、只能包含（ 字母 ~ 不区分大小写、数字、下划线、$、# ）字符
• 不能为oracle的关键字、保留字
• 同一用户对象命名不能重复

 

 

oracle数据类型

字符型

• char(长度)： 固定字节长度字符、实际输入长度不够系统自动由空值填充 - 固定存储空间
   

• varchar2(长度)： 可变字节长度字符 - 实际存储空间由实际数据决定
   

• clob： 可变字节长度字符 - 最大可存储4G数据

 

数值型

• number： 表示整数、小数 - 范围【-10的125次方 ~ 10的126次方】
   

• number(长度)： 例如 number(3) → 范围【-999 ~ 999】
   

• number(总长度p，小数位s)： 整数位位数p-s、 p范围【1 ~38】、s范围【-84 ~ 127】
   

  • s > 0： 精准到小数位 s位、并四舍五入 – 实际数字位数(未转换前) <= p
     
  • s = 0： 等同于 number§
     
  • s < 0： 精确到小数点左边 s位，并四舍五入 – 实际数字位数(未转换前) <= p+|s|
     
  • p < s： 只能表示小数，小数位数s位、且小数点右边 （s-p) 位 为0  – 实际小数位数(未转换前) <= s

 

日期类型

• date
  

  • RR-mm-dd ： 年份以 目前的日期年份后两位 为参考 → 决定输出的世纪位数

  设目前年份后两位为a , 参数日期的后两位年份为b
  a|b < 50 为小 ， 反之为大
  口诀：

  • ab同小同大 → 当前世纪
  • a大b小 → b则表示下一个世纪
  • a小b大 → b则表示上一个世纪

  • yy-mm-dd： 则表示目前的世纪
     

• timestamp： 精度比date更高（ ms级别 )