--更新及事物操作
--跟新操作前的准备
create table myemp as select * from emp;
select * from tab;
select * from myemp;
--数据的增加操作
select * from myemp;
desc myemp;
insert into myemp(empno,job,hiredate,ename,mgr,sal,comm,deptno)
values (8888,'CLERK',sysdate,'陈飞鹏',7369,800,100,20);
insert into myemp
values (8899,'软件','MANAGER',7369,to_date('1992-09-12','yyyy-mm-dd'),1000,100,20);
insert into myemp(empno,ename,job,hiredate,sal)
values(6612,'李楠','CLERK',to_date('1989-09-19','yyyy-mm-dd'),600);
insert into myemp
values(6616,'李楠','CLERK',null,to_date('1989-09-19','yyyy-mm-dd'),600,null,null);
select * from myemp where empno in(6612,6616);
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select * from emp where deptno=20;
insert into myemp select * from emp where deptno=10;
--数据的跟新操作
update myemp set sal=3000,comm=500 where empno=7369;
select * from myemp where empno=7369;
update myemp set sal=sal*1.2
where sal<(select avg(sal) from myemp);
update myemp set sal=0;
rollback;
update myemp set(job,sal,hiredate)=(select job,sal,hiredate from myemp where empno=7839)where empno=7369;
--数据的删除操作
delete from myemp where empno=7566;
select * from myemp where empno=7566;
delete from myemp where deptno=30;
select * from myemp where deptno=30;
delete from myemp where empno in(7369,7566,7788);
select * from myemp;
delete from myemp where sal=(select max(sal) from myemp);
select * from myemp;
delete from myemp myemp where to_char(hiredate,'yyyy')='1987';
select * from myemp;
--事物处理
--锁
select session_id,oracle_username,process from v$locked_object;
commit;
drop table myemp;
--跟新操作前的准备
create table myemp as select * from emp;
select * from tab;
select * from myemp;
--数据的增加操作
select * from myemp;
desc myemp;
insert into myemp(empno,job,hiredate,ename,mgr,sal,comm,deptno)
values (8888,'CLERK',sysdate,'陈飞鹏',7369,800,100,20);
insert into myemp
values (8899,'软件','MANAGER',7369,to_date('1992-09-12','yyyy-mm-dd'),1000,100,20);
insert into myemp(empno,ename,job,hiredate,sal)
values(6612,'李楠','CLERK',to_date('1989-09-19','yyyy-mm-dd'),600);
insert into myemp
values(6616,'李楠','CLERK',null,to_date('1989-09-19','yyyy-mm-dd'),600,null,null);
select * from myemp where empno in(6612,6616);
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select * from emp where deptno=20;
insert into myemp select * from emp where deptno=10;
--数据的跟新操作
update myemp set sal=3000,comm=500 where empno=7369;
select * from myemp where empno=7369;
update myemp set sal=sal*1.2
where sal<(select avg(sal) from myemp);
update myemp set sal=0;
rollback;
update myemp set(job,sal,hiredate)=(select job,sal,hiredate from myemp where empno=7839)where empno=7369;
--数据的删除操作
delete from myemp where empno=7566;
select * from myemp where empno=7566;
delete from myemp where deptno=30;
select * from myemp where deptno=30;
delete from myemp where empno in(7369,7566,7788);
select * from myemp;
delete from myemp where sal=(select max(sal) from myemp);
select * from myemp;
delete from myemp myemp where to_char(hiredate,'yyyy')='1987';
select * from myemp;
--事物处理
--锁
select session_id,oracle_username,process from v$locked_object;
commit;
drop table myemp;