9.处理数据——Oracle数据库学习日记

处理数据

数据控制语言

• DML可以在下列条件下执行：
  • 向表中插入数据
  • 修改现存数据
  • 删除现存数据
• 事务是由完成若干项工作DML语句组成的

1、INSERT语句

使用INSERT语句向表中插入数据

INSERT INTO table [(column [,column...])]
VALUES      (values [,value...]);

使用这种语法一次只能向表中插入一条数据

1.1插入数据

insert into dept
values (50,'IT','BeiJing');
或者
insert into dept(deptno,loc,dname)
values (50,'BeiJing','IT');

表名后不加列名时，只要按照默认表顺序就可添加；表名后加列名可以打乱顺序添加。字符和日期型数据应包含在单引号中。

1.2插入空值

隐式方式：在列名表中省略该列的值
insert into dept(deptno,loc)
	values (70,'Beijing');
显示方式：在VALUES子句中指定空值
insert into dept
	values (70,null,'Beijing');

1.3插入指定的值

插入当前系统时间sysdate函数
insert into emp(empno,ename,hiredate)
	values (8000,'XIAOMING',sysdate);

1.4创建脚本

可以使用&变量指定列值
insert into emp(empno,ename,hiredate,deptno)
	values (&NUMBER,'xiaohong',to_date('20190701','yyyymmdd'),50);

1.5从其他表中拷贝数据

insert into cp_cmp
	select empno,ename,hiredate,sal
	from emp;

• 在INSERT语句中加入子查询
• 不必书写VALUES子句
• 子查询中的值列表应于INSERT子句中的列名对应

2、UPDATE语句

• 使用UPDATE语句更新数据

UPDATE   table
SET         column=value [, column = value,...]
[WHERE   condition];

• 可以一次更新多条数据（如果有需求）

2.1使用WHERE更新数据

使用WHERE子句指定需要更新的数据
update emp
	set deptno=50
	where deptno=30;
如果省略WHERE子句，则表中的所有数据都将被更新

2.2使用子查询更新两列

update emp
	set (sal,job)=(select sal,job
					from emp
					where ename='KING')
	where job='MANAGER';

2.3基于另一张表更新数据

update emp2
	set comm=null,deptno=44
	where ename in (select e.ename
					from emp e,(select deptno,avg(sal) sal_avg
								from emp
								group by deptno) s
					where e.deptno=s.deptno
					and e.sal<s.sal_avg);

3、DELETE语句

• 使用DELETE语句从表中删除数据

DELETE  [FROM]   table
[WHERE          condition];

3.1从表中删除数据

• 使用WHERE子句指定删除的记录

delete from emp
	where ename in ('XIAOMING','xiaohong');

如果省略WHERE子句，则表中的全部数据将被删除。

3.2基于另一张表删除数据

delete from emp2
	where ename in (select e.ename
					from emp e,(select deptno,min(sal) sal
								from emp
								group by deptno) s
					where e.deptno=s.deptno
					and e.sal=s.sal
					and e.ename not in (select distinct p.ename
										from emp e,emp p
										where e.mgr=p.empno));

4、TRUNCATE语句

• 从表中删除所有的行，保留了空表和完整的表结构
• 数据定义语言（DDL），不是DML语句，不能使用撤销

TRUNCATE    TABLE   table_name;

5、数据库事务

数据库事务由以下的部分组成：

• 一个或多个DML语句
• 一个DDL语句
• 一个DCL语句

数据库事务：开始和结束

• 以第一个DML语句的执行作为开始
• 以下面的其中之一作为结束：
  • COMMIT或ROLLBACK语句
  • DDL或DCL语句（自动提交）
  • SQL Developer or SQL*Plus用户退出
  • 系统崩溃

COMMIT和ROLLBACK语句的优点
使用COMMIT和ROLLBACK语句，可以:

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

回滚到保存点

• 使用SAVEPOINT语句在当前事务中创建保存点
• 使用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点。

UPDATE...
SAVEPOINT name_1
INSERT...
UPDATE...
DELETE ...
ROLLBACK TO name_1

隐式事务处理

• 自动提交在以下情况中执行
  • DDL语句
  • DCL语句
  • 不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话.
• 会话异常结束或系统异常会导致自动回滚

6、行级锁

00060：死锁错误代码

• 改变前的数据状态时可以恢复的
• 执行DML操作的用户可以通过SELECT 语句查询之前的修正
• 其他用户不能看到当前用户所作的改变，知道当前用户结束事务
• DML语句所涉及到的行被锁定，其他用户不能操作

COMMIT后数据的状态

• 数据的改变被保存在数据库中
• 以前的数据被覆盖
• 所有用户都可以查看结果
• 受影响的行上的锁被释放，可供其他用户来操作的那些行
• 所有保存点都将被删除

SELECT 语句中的FOR UPDATE子句

示例

SELECT  employee_id,salary,commission_pct,job_id
FROM    employees
WHERE   job_id = 'SA_REP'
FRO UPDATE
ORDER BY employee_id;

锁定EMPLOYEES表中job_id为SA_REP的行
当发出ROLLBACK或COMMIT命令，锁即被释放
如果SELECT语句试图锁定被另一个用户锁定的行，那么数据库将等待，知道该行可用，才返回SELECT语句的结果