处理数据-DML语句
处理数据-DML语句
1 SQL语句类型
1.1 DML：数据操纵语言 Data Manipulation Language
DML是指SQL语句中用于处理对象中的数据的语言。
DML语句用来增加、修改和删除数据库对象中的数据，例如表中的数据。
insert 向数据库的表中添加数据
update 更改表中已有的数据
delete 从表中删除已有的数据
merge 能够在一个SQL语句中对一个表同时执行inserts和updates操作
DML：增、删、改
1.2 DDL：数据定义语言 Data Definition Language
DDL是指用于构建数据库对象的语言
create 创建数据库中的表、视图、索引、同义词和其他对象
alter 用于修改数据库中已经存在的对象结构、名称或其他属性
drop 删除数据库对象
truncate 截断表
1.3 DCL：数据控制语言 Data Control Language
grant 为用户赋予权限
revoke 收回用户权限
1.4 TCL：事务控制语言 Transaction Control Language
commit 提交事务
rollback 撤销事务
savepoint 在会话中设置一个保存点，将来通过rollback语句可以回滚到该位置。
2 DML数据操纵语言
2.1 insert语句
语法：
insert into table_name(column1, column2...) values(value1, value2...);
2.1.1 向表中插入新行
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','DETROIT');
2.1.2 插入带有空值的行
第一种方法：省略字段的方法：从列的表中忽略有空值的列（例如dept表中有deptno、dname、loc字段，插入数据时只需指定deptno、dname字段的值即可）
insert into dept(deptno,dname) values(60,'MIS');
第二种方法：明确指定的方法：指定null关键字
insert into dept values(70,'FINANCE',null);
【常见错误】
1.对not null约束的列没有插入值或插入null值
2.违反唯一约束、check约束
3.数据类型不匹配
4.值过大，超出列的范围
2.1.3 插入当前日期时间 sysdate函数
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7196,'GREEN','SALESMAN',7782,sysdate,2000,null,10);
sysdate函数取出当前的日期时间
insert into emp values(2296,'AROMANO','SALESMAN',7782,TO_DATE('FEB 3,1997','MON DD,YYYY'),1300,null,10);
to_char()将指定时间按照指定格式转化为字符串
2.1.4 从另一张表中拷贝行（带有子查询的insert语句）
insert into emp1 select * from emp where job='MANAGER';
【注意】
1.不要使用values子句语句
2.子查询中的列要与insert子句中的列相匹配
2.1.5 扩展：insert all多行插入多张表
使用一张表，向三个表中插入数据
insert all
when deptno = 10 then
into emp10 values(empno,ename,deptno)
when deptno = 20 then
into emp20 values(empno,ename,deptno)
when deptno = 30 then
into emp30 values(empno,ename,deptno)
select empno,ename,deptno from emp;
2.2 update语句
语法：
update table_name set column1=value1(,column2=value2...) (where condition);
【注意事项】
带where条件：修改一行或者几行或者所有行
不带where条件：修改所有行
2.2.1 修改数据
需求：7782号员工从10号部门调到20号部门
select * from emp where empno=7782;
update emp set deptno=20 where empno=7782;
2.2.2 基于另一个表进行修改
update emp set deptno = (select deptno from emp where empno =7788) where job = (select job from emp where empno = 7788);
2.2.3 将值更新为空值
update时可以使用 =null
update emp set job=null;
也可以使用 =''
update emp set job='';
where条件中不能使用=null、=''、<>''、<>null，可以用is null、is not null
2.2.4 关联子查询更新
需求：更新emp1表中loc列
update emp1 e set loc=(select loc from dept d where d.deptno=e.deptno);
2.3 delete 语句
语法：
delete from table_name [where condition];
【注意】
delete 语句用来从数据库的表中删除行
被删除的行是由where子句所确定的，如果将where子句忽略，那么delete将从表中删除所有的行
2.3.1 删除数据
delete from emp where deptno=10;
delete from emp;
3 TCL事务控制语言
数据库事务由以下部分组成：
- 一个或多个DML语句
- 一个DDL语句
- 一个DCL语句
数据库事务的开始和结束：
- 以第一个DML语句的执行作为开始
- 以下面的其中之一作为结束：
- -- commit或rollback语句
- -- DDL或DCL语句（自动commit）
- -- SQL Developer or SQL*Plus用户的退出
- -- 系统崩溃
sqlplus 异常中止时自动进行隐式回滚
3.1 事务的ACID属性
任何关系型数据库都必须能够通过ACID测试：
原子性：Atomicity 一致性：Consistency 隔离性：Isolation 持久性：Durability
- 原子性：一个事务的所有部分必须都完成，或者都不完成
- 一致性：查询的结果必须与数据库在查询开始时的状态一致
- 隔离性：除了作出变更的会话，其他会话都无法看到未提交的数据
- 持久性：事务一旦完成，所有用户必须能够立刻看到所做的变更，同时数据库必须保证这些变 更不会丢失（数据库通过日志保持事务的持久性）
3.2 commit
commit保存从会话中最后一次提交之后对数据库所做的修改
commit对数据库作出永久的变动，一旦提交之后，这些变动就无法再通过rollback语句撤销
显示提交：commit语句
隐式提交：DDL或DCL语句、正常退出Sql*Plus
3.2.1 commit或rollback之前的数据状态
- 改变前的数据状态是可以恢复的
- 执行DML操作的用户可以通过SELECT语句查询之前的修正
- 其他用户不能看到当前用户所做的改变，直到当前用户结束事务
- DML语句所涉及到的行被锁定，其他用户不能操作
3.2.2 commit 之后的数据状态
- 数据的改变被保存在数据库中
- 以前的数据被覆盖
- 所有用户都可以查看结果
- 受影响的行上的锁被释放，可供其他用户来操作的那些行
- 所有保存点都将被删除
3.2.3 commit 提交数据
3.2.4 隐式提交
隐式事务处理自动提交会在以下情况中执行：
- DDL语句
- DCL语句
- 不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话
DDL、DCL、正常退出
DDL、DCL语句正确执行后会隐式commit
语法正确的DDL、DCL语句执行前会隐式commit
隐式提交：
非“自动提交”，Oracle服务器只是按照指令进行操作
1.执行DDL语句时，实现这个DDL命令的源代码包含了一个完全正规的commit命令
2.SQL*Plus退出命令exit(quit)，默认exit commit，可以退出时exit rollback;
3.3 rollback
rollback撤销了执行该命令的用户在指定的会话中对数据库所做的修改
rollback不会撤销已经提交过的修改
3.3.1 回滚后的数据状态
使用ROLLBACK语句可使数据变化失效：
- 数据更改都会被撤消
- 数据恢复到以前的状态
- 锁被释放
3.3.2 隐式回滚
1.异常退出：（点X关闭sqlplus）
2.系统崩溃
原理：windows平台SQL*Plus关闭窗口的代码中嵌入了一条rollback语句
3.4 savepoint
savepoint在一个事务内建立分隔点，使后续的rollback语句可以进一步细分能够撤销操作的位置。实现未提交事务的部分回滚
savepoint语句需要有一个名称
在一个事务中不应重复savepoint名称，如果重复，新的savepoint会覆盖旧的，从效果上是将之前的savepoint删除了
一旦提交事件发生（显示、隐式），所有现有的存储点都将从内存中被删除
3.4.1 使用 savepoint 回滚到某个标记
3.4.2 savepoint 控制事务
create table test as select * from dept;
select * from test;
insert into test values(50,'a',null);
savepoint spa;
select * from test;
insert into test values(60,'b',null);
savepoint spb;
select * from test;
rollback to spa;
select * from test;
rollback;
4 锁定
Oracle锁：
-- 在并发事务之间，阻止可能产生的破坏性相互影响
-- 不需要用户进行干预，自动使用
-- 在事务期间使用，事务结束时释放
-- 有两种基本的模式：
- 排它
- 共享
锁是一种保护机制，用于管理对共享资源的并发访问
oracle数据库是多用户数据库，可以并发操作
锁避免了并发操作带来的数据不一致情况
DML操作用到的两种锁：
受影响记录上的排它锁exclusive
受影响表上的共享锁shared
排它锁：只有一份，我加锁后，其他人不能加锁，只能等待。行级别，保护这行的数据，防止其他会话修改这些行。只锁定修改的行。缩小加锁范围
如:：sessionl update一-行，session2 update此行会等待。但可以update其他行
共享锁：可以加多份。表级别，保护表的结构，防止其他会话使用DDL语句修改表的定义
如:：session1 做update操作时，session2 不能把表删了，不能把字段删了
4.1 select ... for update
session1：
select * from emp where empno=7788 for update;
session2：
delete from emp where empno=7788;
session2：
select * from emp where empno=7788 for update;
session1 rollback或commit后，session2才可以select ... for update;
select ... for update会锁定所有检索的行。除了发出命令的会话外，其他会话都不能改变这些行。在这个会话commit或rollback之前，for update设置的锁会一直存在
for ... update nowait：如果已经加锁了，返回错误
session1：
update emp set sal=sal*1.1 where empno=7788;
session2：
select * from emp where empno=7788 for update;
select * from emp where empno=7788 for update nowait;
4.2 死锁
session1：
update emp set sal=9999 where empno=7788;
session2：
update emp set sal=8888 where empno=7566;
update emp set sal=7777 where empno=7788;
session1：
update emp set sal=5555 where empno=7566;
5 总结
1.数据操纵语言DML：增删改insert，update，delete
2.事务控制语言TCL：commit，rollback，savepoint
3.DML会加锁：行排它锁、表共享锁
