Oracle操作（三）——增加、更新、删除

一、数据的增加

（一）格式：
插入数据的的基本语法：
INSERT INTO 数据表（字段1，字段2，字段3…）VALUES（值1，值2，值3）

（二）DEMO
1.复制一张数据表

CREATE TABLE myemp AS SELECT * FROM emp;

2.向myemp表中增加数据
如果字段数据类型要求是数字，但是插入了数字类型的字符串，系统会自动转换成数字

INSERT INTO myemp(empno,ename,job,mgr,sal,comm,hiredate,deptno) 		--myemp的字段顺序可以调换
  VALUES(1002,'TOM','DRIVER','7788',900,NULL,SYSDATE,10);		--VALUES赋值时顺序和上面字段的顺序保持一致

3.如果每个字段都要增加数据，则可以省略表后面的字段名

//错误示范：
INSERT INTO myemp			--可以省略这里的字段名，但是后面的VALUES的值必须和myemp表格字段数量、顺序保持一致
  VALUES(1002,'TOM','DRIVER','7788',900,NULL,SYSDATE,10);		--错误，数据类型不一致，900处值应该为date，但获得数字

正确示范：采用以下方式增加数据，如果在插入数据的时候要省略字段，也必须为表中所有的字段给赋值
INSERT INTO myemp	
  VALUES(1002,'TOM','DRIVER','7788',SYSDATE,900,NULL,10);

二、更新数据

（一）格式：
UPDATE 表名称 SET 字段=新的值，字段=新的值… WHERE 过滤条件

（二）DEMO
1.将SMITH的基本工资修改为8000，佣金修改为1000

UPDATE myemp SET sal=8000,comm=1000 WHERE ename='SMITH';

2.将ALLEN的工资修改为SMITH的工资

UPDATE myemp SET sal =(		--修改佣金为
  SELECT sal FROM myemp WHERE ename='SMITH')		--结合SELECT语句查询出SMITH的工资
    WHERE ename='ALLEN';		--更新条件，只更新ALLEN的数据

3.将低于平均工资的员工的工资上涨20%

UPDATE myemp SET sal=sal * 1.2 WHERE empno IN (
  SELECT empno FROM myemp WHERE sal<(
    SELECT AVG(sal) FROM myemp));

三、删除数据

（一）基本语法：
DELETE FROM 数据表 WHERE 过滤条件

（二）DEMO

1.删除雇员编号是7788的雇员信息

DELETE FROM myemp WHERE empno=7788;

2.删除雇员编号是7934、7900、1234的雇员的数据

DELETE FROM myemp WHERE empno IN (7934,7900,1234);

四、事务的回滚和提交

（一）什么是事务？

1.事务是访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据的存取
2.事务的正确执行使得数据库从一种状态转换成另一种状态
3.对数据库的一次操作就是一个事务，在一个事务中可能要执行多条sql语句

（二）事务的回滚和提交

1.回滚(ROLLBACK)：如果事务执行中途出现了异常则可以使用该命令回滚
2.提交(COMMIT)：事务执行中途没有出现任何问题则提交，提交之后的数据不能再回滚

五、事务多用户下的死锁

在多个用户一条数据的时候，如果前面一个用户更新了数据，但是没有提交事务，那么后面一个用户不能操作当前数据，只有等待上一个用户提交事务之后才能进行操作，类似于多线程的阻塞机制。

（一）特点
1.多个用户操作的时候，如果前一个用户更新了一条数据A，但是没有提交或者回滚事务，那么后面一个用户无法更新数据A，但是可以可以更新除数据A外的其他数据。因此这个锁也叫做行锁

2.多个用户操作的时候，如果前一个用户更新了一条数据A，但是没有提交或者回滚事务，其他用户可以查询所有的数据，包括数据A，但是查询的结果是更新前的数据。如果想要让查询语句也出现死锁的现象，那么在查询语句后面增加FOR UPDATE即可

3.增加FOR UPDATE的查询语句，当前一个用户更新了一条数据A，但是没有提交或者回滚事务的时候，无法查询到结果，一旦提交或者回滚事务，后面那个使用增加FOR UPDATE的查询语句的用户就可以获取查询结果，且该结果是更新之后的数据

4.以上数据库提供的锁机制是一种悲观锁，此外还有乐观锁，乐观锁是通过控制版本号或者时间戳来实现的一种锁机制，需要结合程序才能实现。

六、事务

（一）事务的特性

如果一个数据库声称支持事务的操作，那么该数据库必须要具备以下四个特性：

1.原子性（Atomicity）：一次事务中如果进行多个数据操作的业务，最终的结果要么所有操作都成功，要么所有操作都失败

例如：A转账B，转了100元，最终的要求是，A的账户减少100,B的账户增加100，不能A减少了，但是B没增加

2.一致性（Consistency）：一致性就是数据表中的数据更新要求合乎逻辑的特性，满足了原子性不一定满足一致性

例如：A转账B转了100元，最终的结果是，A的账户减少100,B的账户增加10，此时事务是成功的（满足原子性），但是不合乎逻辑（不满足事务一致性）

3.隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

问题一：多个用户操作同一张表时，每一个用户的事务的操作不能被其他事务的操作所干扰？
		不能干扰是否有先后之分？
		操作包括所有的操作如查询之类的吗？

4.持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
比如事务提交还没来得及在磁盘中进行数据的更新就因为系统出现立刻故障（断电了）导致本次事务提交了但是没有真正更新数据，此时应该在供电恢复之后会继续将没有更新的数据进行更新（需要介质），因为要进行备份或者日志记录等等，这些操作一般是DBMS完成，或者有专门的容灾处理。

（二）事务的隔离性

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个事务获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果事务没有隔离性会发生的几种问题：
1.更新丢失：如果多个线程操作，基于同一个查询结果对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
解决方法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题

2.不可重复读：一个事务对同一行数据重复读取两次，但是却得到了不同的结果，因为在读取第一次之后，其他用户对数据进行了操作使其改变，当再次读取该数据时就得到了与前一次不同的值。
解决方法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题
不可重复读和脏读的区别是：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

3.虚读（幻读）：指两次执行同一条select语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中，一般情况下，幻读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来，隔离级别为游标稳定性的，可以阻止幻读。例如，目前工资为1000的员工有10人，那么事务1中读取所有工资为1000的员工，得到了10条记录，这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。
解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可便面该问题。

（三）事务的隔离级别

幻读和不可重复读都是读取了另一条已经提交的事务的数据（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是统计一批数据整体（比如数据的个数）
以下是MySQL数据库为我们提供的四种隔离级别：
1.Read uncommitted（未授权读取、读未提交）
如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

2.Read committed（授权读取、读提交）
读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问改行。该隔离级避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该行数据时，数据已经发生了改变

3.Repeatable read（可重复读取）
可重复读是指在一个事务内，多次读同一数据，在这个事务还没有结束时，另外一个事务也访问该同一数据，那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现

4.序列化

七、序列的创建和使用（Oracle序列实现主键的自增长）

（一）主键
在每张数据表中都会有一个字段作为数据的唯一区分标志，而且该字段的数据不能重复，这个字段叫做数据表的主键

（二）格式

-- 删除序列
DROP SEQUENCE 序列名称；
-- 创建序列
CREATE SEQUENCE 序列名称；
-- 使用序列
INSERT INTO myemp(empno,ename,job) VALUES(序列名.nextval,'TOM','DRIVER');

-- 问题1：上面案例使用empno作为序列，可以选择其他的作为序列吗？
-- 问题2：一个表可以设置多少个序列？一个表可以有多少个主键

（三）DEMO
1.序列的创建和基本使用

--执行第一步后，生成序列号1，2，3的3行数据
DROP SEQUENCE empno_sequence;
CREATE SEQUENCE empno_sequence;
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry11','painter');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry12','singer');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry13','writer');

--执行下面一段代码生成序列号为4，5，6的3行数据
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry21','painter');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry22','singer');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry23','writer');

--执行下面一段代码，重新设置序列号，因此生成序列号为1，2，3的数据，最终结果如下图，顺序有错乱
DROP SEQUENCE empno_sequence;
CREATE SEQUENCE empno_sequence;
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry31','painter');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry32','singer');
INSERT INTO myemp(empno,ename,job) VALUES(empno_sequence.nextval,'jerry33','writer');

2.指定序列的开始值(START WITH)
格式：
CREATE SEQUENCE empno_sequence START WHET 开始值;

DROP TABLE myemp;		--删除表
CREATE TABLE myemp AS SELECT * FROM emp;		--复制emp表新建myemp
DROP SEQUENCE empno_sequence;		--删除原先的序列
CREATE SEQUENCE empno_sequence INCREMENT BY 10 START WITH 1001;		--创建序列，从1001开始，每个值增加10
INSERT INTO myemp(empno,ename,job) VALUES (empno_sequence.nextval,'TOM1','DRIVER1');
INSERT INTO myemp(empno,ename,job) VALUES (empno_sequence.nextval,'TOM2','DRIVER2');
INSERT INTO myemp(empno,ename,job) VALUES (empno_sequence.nextval,'TOM3','DRIVER3');