Oracle笔记13——Oracle数据操作与事务控制
Oracle数据库五大语言：
数据查询语言 DQL(Data Query Language): SELECT
数据操作语言 DML(Data Manipulation Language): INSERT UPDATE DELETE
事务处理语言 TPL(Transaction Process Language):COMMIT ROLLBACK SAVEPOINT
数据定义语言 DDL(Date Definition Language):CREATE ALTER DROP TRUNCATE
数据控制语言 DCL(Date Control Language): GRANT REVOKE
插入数据
一.新增数据(插入数据)
语法： INSERT INTO 表名(列1，列2....) VALUES(值1，值2....); 一次新增一行
INSERT INTO 表名(列1，列2....) 子查询; 一次新增N行
注意：
1.列名列表与值列表的个数、类型必须保持一致
2.列名列表可以省略，默认值列表给所有列赋值
3.值列表中日期、字符串必须使用''单引号括起来
二、插入空值NULL
(1)隐含法: 在列名列表中忽略该列。
(2)显示法: 指定 NULL关键字或者''
注意：
列名列表可以省略，默认值列表给所有列赋值
INSERT INTO dept(deptno,dname) values(50,'开发部');--隐含法 INSERT INTO dept(deptno, dname, loc) VALUES(60, '需求部', '东软大厦'); INSERT INTO dept(deptno, dname, loc) VALUES(70, '需求部', '');--显示法 INSERT INTO dept VALUES(70, '需求部', NULL);--于上面效果相同 SELECT * FROM dept;
三、插入日期值
--插入日期值 SYSDATE 函数记录当前日期和时间 INSERT INTO emp(empno,ename,hiredate) VALUES(7777,'李四',SYSDATE); --插入日期值 可以使用RR日期格式，也可以使用TO_DATE()函数转换YY日期格式 INSERT INTO emp(empno,ename,hiredate) VALUES(8888,'李二','2019-08-01');--文字与格式字符串不匹配 INSERT INTO emp(empno,ename,hiredate) VALUES(8888,'李二','1-8月-19'); --RR日期格式 INSERT INTO emp(empno,ename,hiredate) VALUES(8888,'李二',TO_DATE('2019-08-01','yyyy-mm-dd'));--YY日期格式
四、插入特殊字符
1.ASCII() ：查看指定字符的ascii编码
2.CHR() ：查看指定ascii编码代表的字符
INSERT INTO dept(deptno,dname) VALUES(50,'~!@#$%^*()-+');--ok INSERT INTO dept(deptno,dname) VALUES(50,'&'); --ok INSERT INTO dept(deptno,dname) VALUES(50,'&需求部');-- not OK &修饰的字符表示一个变量，等待用户输入变量值 INSERT INTO dept(deptno,dname) VALUES(50,'\&需求部');--not ok INSERT INTO dept(deptno,dname) VALUES(50,'&' || '需求部'); --ok INSERT INTO dept(deptno,dname) VALUES(50,CHR(38) || '需求部'); --ok --ASCII() 查看指定字符的ascii编码 SELECT ASCII('&') FROM dual; --38 --CHR() 查看指定ascii编码代表的字符 SELECT CHR(38) FROM dual;
五、批量新增（备份数据）
1. 将部门10所有的员工信息备份到emp_dept10表中 ①复制表结构：创建表emp_dept10，且结构与emp表一致 CREATE TABLE emp_dept10 AS SELECT * FROM emp; --1.复制emp表的表结构与所有数据 CREATE TABLE emp_dept10 AS SELECT * FROM emp WHERE 1=0; --1.仅复制emp表的表结构 ② 使用子查询批量新增数据：将部门10的员工数据备份到emp_dept10表中 INSERT INTO emp_dept10 SELECT * FROM emp WHERE deptno = 10;--2.复制所有列数据 INSERT INTO emp_dept10(empno,ename,hiredate) SELECT empno,ename,hiredate FROM emp WHERE deptno = 10;--2.复制指定列数据
修改数据
一、修改数据语法
UPDATE 表名 SET 列名1=值1[,列名2=值2.....] [WHERE 限制条件];
1.把员工编号为7782的部门编号修改为20 UPDATE emp SET deptno = 20 WHERE empno = 7782; SELECT * FROM emp WHERE empno = 7782; --如果要修改所有记录，WHERE子句可以忽略 2.把所有员工的部门编号修改为20 UPDATE emp SET deptno = 20; --一次修改多列 3.把部门编号为10的员工，部门编号为20，工资增加100 UPDATE emp SET deptno = 20, sal = sal + 100 WHERE deptno = 10; --嵌入子查询修改 4.把部门编号为10的员工，部门编号调整为20，工资在原有的基础上，增加所有人的平均工资 SELECT AVG(sal) FROM emp;--所有人的平均工资 UPDATE emp SET deptno = 20, sal = sal + (SELECT AVG(sal) FROM emp) WHERE deptno = 10; --修改记录时的完整性约束错误 5.把部门编号为10的员工，部门编号调整为55 UPDATE emp SET deptno=55 WHERE deptno =10;--not ok 未找到父项关键字 emp.deptno 的数据来源于 dept.deptno,此时dept中没有55编号的部门存在 --相关子查询修改 1.在emp中新增dname列 ALTER TABLE emp ADD(dname varchar2(14)); 2.将emp表中的dname更新为实际部门的名称 UPDATE emp SET dname = (SELECT dname FROM deptWHERE dept.deptno = emp.deptno);
删除数据
一、语法：
DELETE [FROM] 表名 [WHERE 限制条件];
--删除选中记录 1.删除职位是CLERK的员工记录 DELETE FROM emp WHERE job = 'CLERK'; --删除全部记录 2.删除所有员工记录 DELETE FROM emp; DELETE emp; --基于另一个表删除本表记录 3.删除部门SALES的员工 SELECT deptno FROM dept WHERE dname = 'SALES';--1.SALES的部门编号为多少？ 30 DELETE FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES'); --删除记录时的完整性约束错误 4.删除部门编号为10的部门记录 DELETE FROM dept WHERE deptno = 10; --not ok 已找到子记录 因为emp.deptno引用dept.deptno,而dept.deptno为10的部门已经存在3个员工 --相关DELETE 5.删除曾经做过入职的员工记录 DELETE FROM emp WHERE 曾经做过入职; delete FROM emp WHERE EXISTS (SELECT * FROM emp_jobhistory WHERE empno = emp.empno); 6.删除没有员工的部门记录 DELETE FROM dept WHERE 没有员工的部门; DELETE FROM dept WHERE NOT EXISTS (SELECT * FROM emp WHERE deptno = dept.deptno);
事务管理
一、事务管理定义：
由一组SQL语句组成的管理单元,要么全部执行成功，要么全部执行失败
二、事务四大特性
1. 原子性：一个事务中所有的SQL语句，执行时，要么全部执行成功，要么全部执行失败
2. 一致性：不论SQL语句执行成功，还是失败，整个数据处于平衡状态。
3. 隔离性：事务与事务之间相互隔离，互不影响
4. 持久性：一旦数据被提交，永久性被保存到数据库中，不能再撤回
三、事务开始与结束
事务开启：上一个事务结束以后，执行下一个DML(增、删、改)语句即开始新的事务
事务结束：
1.显式结束
commit:显示提交
rollback:显示回滚（撤销所有的操作）
2.隐式结束
隐式提交：当下列任意一种情况发生时，会发生隐式提交
1.执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
2.执行一个DCL(GRANT、REVOKE)语句；
3.从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）；
隐式回滚：当下列任意一种情况发生时，会发生隐式回滚
1. 从SQL*Plus中强行退出
2. 客户端连接到服务器端异常中断
3. 系统崩溃
--开启事务 INSERT INTO dept(deptno,dname) VALUES(50,'财务部'); INSERT INTO dept(deptno,dname) VALUES(60,'人力资源部'); ROLLBACK;--显示回滚,事务结束 INSERT INTO dept(deptno,dname) VALUES(70,'后勤部'); COMMIT;--显示提交，事务结束 --设置保存点 INSERT INTO dept(deptno,dname) VALUES(81,'需求部'); savepoint poin1;--设置保存点1 INSERT INTO dept(deptno,dname) VALUES(82,'UI部'); INSERT INTO dept(deptno,dname) VALUES(83,'前端部'); SAVEPOINT poin2;--设置保存点2 INSERT INTO dept(deptno,dname) VALUES(84,'开发部'); INSERT INTO dept(deptno,dname) VALUES(85,'测试部'); SAVEPOINT poin3;--设置保存点3 INSERT INTO dept(deptno,dname) VALUES(86,'实施部'); ROLLBACK TO poin2;--回滚保存点2的位置 COMMIT; --提交部分事务SQL语句 ROLLBACK;--事务已经被提交，不可能再被回滚
锁
一、分类：
行级锁 和 表级锁 （悲观锁 和 乐观锁）
行级锁: ORACLE默认的机制是在DML操作影响的行记录上自动加锁
当会话1在修改指定行数据，且没有commit提交事务或者rollback回滚事务之前,
该数据行被锁定，其他会话2不允许修改该数据。
如果会话1结束事务，锁释放，其他会话2可以继续操作该数据
表级锁：当会话1在操作指定数据时，表将被锁定，其他会话不允许对表结构进行修改（新增列、修改列、删除列）
--会话1 （执行一个事务） UPDATE dept SET loc='125C教室' WHERE deptno = 83; SELECT * FROM dept; --会话2（执行一个事务） DELETE FROM dept WHERE deptno = 50; SELECT * FROM dept; --先插入一条新纪录，然后分析如下两个会话，执行完每一步时的数据库状态 新纪录： INSERT INTO emp(empno, ename, job, sal) VALUES(8888, 'test', 'Test', 500); COMMIT; 会话A 1.UPDATE emp SET sal = sal + 500 WHERE empno = 8888;--（1）此时sal为1000 3.SELECT * FROM emp WHERE empno = 8888;--（3）此时sal为1000 5.COMMIT;--（5）提交，此时两个会话的sal都为1000 7.SELECT * FROM emp WHERE empno = 8888;--（7）此时sal为2000 会话B 2.SELECT * FROM emp WHERE empno = 8888; --（2）此时sal还是500 4.UPDATE emp SET sal = sal + 1000 WHERE empno = 8888;--（4）等待会话A提交，会话A提交后，得到的sal为2000 6.COMMIT;--（6）提交，此时两个会话的sal都为2000
