二十六、DML与事务

DML 数据操纵语言

插入数据（INSERT）

1. 格式：

Insert  into 表名（列名, 列名, 列名, 列名…）values(值，值，值，值，值)
--简单写法：
Insert  into 表名values(值，值，值，值，值)

使用insert语句一次可以向数据库中插入一条记录

在插入时，对于字符和日期需要使用单引号引起来

-- 向emp表中插入一条记录
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(1111,'李四','外卖员',7788,to_date('2020-10-1','yyyy-mm-dd'),2500,NULL,10);
-- 简单写法,省略列名
INSERT INTO emp VALUES(2222,'张三','程序员',7788,to_date('2021-1-1','yyyy-mm-dd'),3500,NULL,20);

2. 向表中插入空值：

-- 向表中插入空值
-- 隐式方式，在列名中省略该列的值
INSERT INTO dept(deptno,dname) VALUES(50,'技术部');
-- 显式方式，在子句中指定空值
INSERT INTO dept VALUES(60,'财务部',null);

3. 通过创建脚本向表中添加数据（该方式Navicat不支持）：

-- 创建脚本插入
INSERT INTO dept(deptno,dname,loc)
VALUES(&deptno,'&dname','&loc');

4. 从其他表中拷贝数据：

-- 创建一张emp1表，表只有结构，没有数据
CREATE TABLE emp1(
       empno  NUMBER(4) CONSTRAINT PK_EMP1 PRIMARY KEY,
       ename  VARCHAR2(10),
       job    VARCHAR2(9),
       mgr    NUMBER(4),
       hiredate DATE,
       sal    NUMBER(7,2),
       comm   NUMBER(7,2),
       deptno NUMBER(2)
);
SELECT * FROM emp1;
-- 从其他表中拷贝数据
INSERT INTO emp1 SELECT * FROM emp;

拷贝数据前：

拷贝数据后：

拷贝部分数据：

-- 拷贝部分数据(拷贝部门数为30的员工信息)
INSERT INTO emp1 SELECT * FROM emp WHERE deptno=30;

更新数据（UPDATE）

语法：UPDATE 表名 SET 列名=值，列名=值··· WHERE 条件

可以一次更新一条或多条记录，如果不加条件的执行更新语句，将导致表中的数据全部被更新。

-- 将emp表中的1111员工的部门改为30
UPDATE emp set deptno=30
WHERE empno=1111;

在更新中可以使用子查询：

-- 更新1111的job和工资，使其与2222相同
UPDATE emp set job=(SELECT job FROM emp WHERE empno=2222),
               sal=(SELECT sal FROM emp WHERE empno=2222)
WHERE empno=1111;

删除数据（DELETE）

语法：DELETE FROM 表名 WHERE 条件

删除数据必须加where条件，否则数据库会被清空

在Oracle中，删除语句中可以省略FROM 关键字；可以使用子查询

-- 删除1111
DELETE FROM emp WHERE empno=1111;
-- 在删除中使用子查询，删除与1111相同部门的人员
DELETE FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE empno=1111);
-- oracle中，删除语句中可以省略from关键字
DELETE emp WHERE empno=1111;

事务

事务是对数据库一组逻辑操作单元，进行的一次完整性操作。事务的操作中，使数据从一种状态转变为另一种状态。

• 数据库事务的组成：

  • 一个或多个DML语句组成
  • 一个DDL语句（数据定义语言Date Definition Language）
  • 一个DCL语句（数据控制语言Date Control Language）

• 事务的开始：以第一个DML语句的执行作为开始

• 事务的结束：

  • COMMIT（事务提交）
  • ROLLBACK（事务回滚）
  • DDL（自动提交）
  • 会话结束
  • 系统异常终止

• COMMIT 和ROLLBACK 语句的优点：

  • 保证数据完整性
  • 数据改变被提交之前预览
  • 将逻辑上相关的操作分组

事务特性（ACID）

事务必须具备以下四个属性，简称ACID属性：

• 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）。事务在执行时，其中所关联的一组逻辑相关的操作；要么都成功，要么都失败。
• 一致性（Consistency）：查询的数据始终是一致的
• 隔离性（Isolation）：对于其他会话来说，不能读取到其它会话未提交的事务。事务不可见
• 持久性（Durability）：事务一旦提交，对于数据来说，此时的事务的执行结果将具有持久性，结果被完整的保存

锁

• 锁的分类：分享锁和排他锁
  • 分享锁：排斥其他的排他锁，但是不排斥其他的共享锁
  • 排他锁：排斥其它排他锁和分享锁
• DML锁（数据锁 date locks）：主要用于保证数据的完整性，TX（行锁），TM（表锁）
  查看事务：select * from v$transaction；
  查看锁：select * from v&lock；
• DDL锁（数据字典锁 dictionary locks）：不要用于保护数据库对象的结构，表、索引、视图等结构的定义
• SYSTEM锁（internal locks and latches）：主要是保护数据库的内部数据

锁用途：只有当产生事务的时候，才会产生锁，保证数据的完整性、一致性、正确性。

自动加锁：

• 做DML操作时：insert update delete 以及select···for update 有Oracle自动完成加锁
• Oracle 的特有操作：select…for update 有Oracle 自动完成加锁。

控制事务

事务 DELETE、INSERT、UPDATE 当执行完成之后，如果我们确认事务的操作准确，就需要将数据进行持久化保存，此时就需要将数据进行commit。

如果操作之后，认为操作不当，需要回到修改之前的状态，此时我们可以进行回滚（rollback）。

设置保存点，回滚到保存点：

• 使用savepoint 创建到保存点
• 使用rollback to 回滚到刚刚使用savepoint 创建的保存点

savepoint point1;
rollback to point1;

事务的进程

自动提交的情况：

• DDL
• DCL
• 会话正常结束
• 会话异常结束或者系统异常，此时都会导致自动回滚

事务相关的操作为什么需要提交或者可以进行回滚：

• 当我们在进行 insert 、update、delete 操作的时候，数据并不会直接写入到数据库文件，而是写入到缓冲区；
• 所以当我们提交的时候，就是将缓冲区的数据提交到数据库文件，执行写入操作；
• 而当我们进行回滚的时候，此时就会将缓冲区的数据进行清空。

事务相关操作：

• 提交：当执行了insert 、 update 、 delete 操作的时候，在执行结束之后，都需要进行提交操作，只有提交了之后，数据的操作才是持久的
• 回滚：当操作时，发生了异常，此时希望数据回到初始状态，此时就需要回滚数据；ROLLBACKR 回滚可以取消数据的改变、恢复数据的状态、同时释放锁。

回滚只针对没有提交的操作，一旦数据提交，则不能执行回滚。

小结：

语句	功能
INSERT	插入
UPDATE	修正
DELETE	删除
COMMIT	提交
SAVEPOINT	保存点
ROLLBACK	回滚

创建和管理表

Oracle 是通过用户去管理数据的，要在Oracle 中存储数据：必须先创建表空间，再创建用户，再创建对象（表、索引、制图……）

1. 表空间：一个或多个数据数据文件的逻辑集合。

表空间的分类：

• 永久表空间：存放永久行的数据
• 临时表空间：保存临时数据
• Undo表空间：保存数据修改前的镜像

一个数据库下可以创建多个表空间，一个表空间可以被多个用户所拥有，一个用户下可以创建多个表。

• 创建及查看表空间

-- 创建表空间 
create tablespace tbooks --表空间的名称
--数据文件的存放路径 对应虚拟机中的路径
datafile 'C:\app\BORN\virtual\oradata\orcl\tbooks.dbf'
size 100m --表空间的初始容量
autoextend on --表示表空间可以自动增长
next 10m --自动增长时的每次增长的空间
-- 查看所有的表空间
select * from DBA_TABLESPACES;

• 创建临时表空间

--创建临时表空间
create temporary tablespace  lanqiao_temp
tempfile 'C:\app\Adminstrator\virtual\oradata\orcl\lanqiao_temp.dbf'
size 50m
autoextend on
next 10 
maxsize 20480M
extent management local;

• 新建用户并指定表空间

-- 新建用户并指定表空间
create user library  -- 用户名
identified by root  -- 密码
default tablespace tbooks  -- 指定默认表空间

2. 当用户创建成功之后，还需要给分配权限，才能登陆。

• 权限分配
  Oracle 中默认存在的三个重要角色：connect 角色 、 resource 角色 、 dba 角色
  • connect 角色：
    • 是授权最终用户的典型的角色，最基本
    • 常见会话、修改会话、建立数据库连接、创建序列、创建同义词、创建视图
  • resource 角色：
    • 是授予开发人员的
    • 建表、建立触发器、存储过程
  • dba角色：
    • 拥有全部的特权，是系统的最高权限，只有dba才可以创建数据库结构，并且系统权限修改也需要DBA操作
    • DBA用户可以操作全体用户的任意的表，包括删除

-- 为用户授权
grant connect,resource to library;
grant dba to library;  -- 一般不给该权限

3. Oracle 的数据类型

No	数据类型	描述
1	Varchar，Varchar2	表示一个字符串 Varchar定长 Varchar2可变长度
2	NUMBER	Number(n) 表示整数 长度为n
		Number(m,n) 表示整数 总长度为m 小数为n 整数m-n
3	DATE	表示日期类型
4	CLOB	表示大的文本类型 可存放4G内容
5	BLOB	表示大的二进制数据 可存放4G内容

创建表：

-- 创建books表
create table books( --表的名称
       bno number(10),
       bname varchar2(50),
       press varchar2(20),
       pdate date,
       writer varchar2(20),
       btype varchar2(20),
       ltime date,
       state varchar2(10),
);

插入数据：

insert into person(pid,pname,gender,birthday) values(1,'张三',1,to_date('2000-12-12','yyyy-mm-dd'));
commit;
insert into person(pid,pname,gender,birthday) values(2,'张三',1,sysdate);

表的删除（数据和结构都被删除，所有相正在运行的相关事务被提交，所有索引被删除，语句不能回滚）：

drop table 表名;

清空表（删除表中的所有数据，释放表的存储空间，不能回滚）：

truncate table 表名

表的修改：

表添加列：alter table 表名  add（列名 类型  默认值，[列名 类型  默认值]）
表修改列：alter table 表名 modify（列名 类型  默认值，[列名 类型  默认值]）
表删除列：alter table 表名 drop column 列名

-- 给emp1增加列location
alter table emp1 add(location varchar2(30));
-- 将location的字段修改为50
alter table person modify(location varchar2(50));

表的重命名：

-- 重命名表
-- 将employees2重命名为emp5
rename employees2 to emp5;

根据表创建新表：

-- 根据表employees创建employees2
create table employees2 as select * from employees;
-- 查看表
select * from employees2;

序列

创建序列：

CREATE SEQUENCE pid1;
-- nextval :取得序列的下一个值
-- currval：取得序列的当前值
SELECT PID.NEXTVAL FROM dual;
select pid.currval from dual;

-- 创建复杂的序列
CREATE SEQUENCE pid2
increment by 10     -- 每次增长的步长
start with 100;
 /*
     maxvalue  --最大值
     minvalue--最小值
     cycle/nocycle--是否是循环序列
*/
SELECT PID2.NEXTVAL FROM dual;
select pid2.currval from dual;

-- 序列的实际使用
insert into emp1(pid4,empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(pid4.nextval,7788,'张三','外卖员',7654,null,1400,null,20);

约束

如果不指定约束名，Oracle server 自动按照SYS_Cn 的格式指定约束名。

创建和修改约束：

• 建表的同时
• 建表之后

表级约束和列级约束：

• 作用范围：
  • 列级约束只能作用在一个列上
  • 表级约束可以作用在多个列上（也可以一个）
• 定义方式：列约束必须跟在列的定义后面，表约束不与列约束一起，而是单独定义。

非空约束（not null）

作用：保证插入的值不为空，是作用域字段

create table STU
(
  stu_id         NUMBER(8) not null,
  stu_name       VARCHAR2(20)
  constraint stu_age_uu not null,  -- 非空约束的另一种写法
  stu_age        NUMBER(4),
  stu_schooldate DATE
)

唯一约束（Unique）

Unique 表示该字段的值不能重复，值唯一，允许出现多个空值。

create table stu(
       stu_id number(8) not null,
       stu_name varchar2(20)  UNIQUE,  -- 增加唯一约束
       stu_age number(4),
       stu_schooldate date,
       stu_email varchar2(50),
       constraint stu_email_uk  UNIQUE(stu_email)  -- 增加唯一约束
);

主键约束（Primary key）

Primary key = not null + unique

可以定义在表或列上。

create table stu(
       stu_id number(8),
       stu_name varchar2(20),
       stu_age number(4),
       stu_schooldate date,
       stu_email varchar2(50),
       cid number(8), 
       constraint stu_id_pk primary key(stu_id)  -- 指定主键
);

外键约束（Foreign key）

实现两个表的关联。

create table stu(
       stu_id number(8),
       stu_name varchar2(20)  UNIQUE,
       stu_age number(4)
       constraint stu_age_nn NOT NULL,
       stu_schooldate date,
       stu_email varchar2(50),
       cid number(8),--外键
       constraint stu_id_pk  primary key(stu_id),--指定主键
       constraint stu_cls_fk foreign key(cid)  references cls(cid)--关联外键
       
);

--班级表
create table cls(
       cid number(8),--主键
       cname varchar2(20)  not null,
       constraint cls_id_pk primary key(cid)
 )

添加cls数据：

在stu表中即可实现下拉功能

外键关联时，cls 表称为父表，stu称为子表，子表引用父表的数据。

删除时，可以任意删除子表的数据，没有影响；但删除父表时，如果子表有关联数据，则可能删除失败。

删除父表数据的方法：

• 删除父表时，同时删除该记录所关联的所有的子表记录（on delete cascade 级联删除）
• 删除父表时，将子表中的外键字段设置为null（on delete set null 级联置空）

检查约束（Check）

要求设置某个字段的值的时候，值必须满足一定的条件。

create table stu(
       stu_id number(8),
       stu_name varchar2(20)  UNIQUE,
       stu_age number(4)
       constraint stu_age_ck check(stu_age>18),  -- check 约束
       stu_schooldate date,
       stu_email varchar2(50),
       cid number(8),
       constraint stu_id_pk  primary key(stu_id),
       constraint stu_cls_fk foreign key(cid)  references cls(cid)
);

新增或修改约束（add、modify）

-- 新增约束
alter table 表名称 add(constraint···)
-- 修改约束
alter table 表名称 modify(constraint···)

--修改stu表中的stu_name的唯一约束为非空约束
alter table stu modify(stu_name varchar2(20) not null)

修改后的结果（stu_name 为非空约束）：

删除约束（drop）

drop constraint 约束的名称;

-- 删除stu 的外键约束,先用alter table指定表
alter table stu drop constraint stu_cls_fk;

无效化约束与激活约束

在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。

ALTER TABLE employees
DISABLE CONSTRAINT emp_id_pk;

ENABLE 子句可以将当前无效的约束激活。

ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;