处理数据-DML语句

处理数据-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 =10then
into emp10 values(empno,ename,deptno)
when deptno =20then
into emp20 values(empno,ename,deptno)
when deptno =30then
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=20where 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=7788for update;
session2：
delete from emp where empno=7788;
session2：
select*from emp where empno=7788for 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.1where empno=7788;
session2：
select*from emp where empno=7788for update;
select*from emp where empno=7788for update nowait;
4.2 死锁
session1：
update emp set sal=9999where empno=7788;
session2：
update emp set sal=8888where empno=7566;
update emp set sal=7777where empno=7788;
session1：
update emp set sal=5555where empno=7566;
5 总结
1.数据操纵语⾔DML：增删改insert，update，delete
2.事务控制语⾔TCL：commit，rollback，savepoint
3.DML会加锁：⾏排它锁、表共享锁。