2018.4.18Oracle数据库学习日记

--2018.4.18
--数据处理
--新增数据（插入数据）
insert into emp1(empno,ename,job,hiredate,sal)
values(7910,'张三','DBA',sysdate,5000);
select *from emp1;
--
insert into emp1(empno,ename,job,hiredate,sal)
values(7911,'张三','DBA','18-4月-18',5000);

--2.不指定列名时需要给每个字段赋值，不能多也不能少。
insert into emp1 values(7913,'李四','java开发',7788,sysdate,6000,1000,20);
--插入空值
--隐性插入：在查询的字段的列表中忽略该字段
insert into emp1(empno,ename,job,hiredate,sal)
values(7914,'张三三','DBA',sysdate,5000);
--显性插入
insert into emp1 values (7915,'王五','',7902,sysdate,5500,null)

--4.创建脚本(输入格式:18-4月-18"&hiredate打引号")
insert into emp1(empno,ename,job,hiredate,sal)
values(&empno,'&ename','&job','&hiredate',&sal);

--不打引号可输入sysdate
insert into emp1(empno,ename,job,hiredate,sal)
values(&empno,'&ename','&job',&hiredate,&sal);

insert into emp1(empno,ename,job,hiredate,sal)
values(&员工编号,'&姓名','&职位',&雇佣日期,&工资);

--批量插入数据(从其他表拷贝数据)
insert into emp1(select * from scott.emp where deptno=30);
insert into emp1(select * from scott.emp where sal between3000 and =30);

--修改数据
--一次修改一条数据
update emp1 set ename='三毛' where empno=7911;
update emp1 set ename='张三三',job='java' where empno=7911;

--一次修改多条条件
update emp1 set deptno=40 where deptno=30;
update emp1 set ename='马九',sal=1000 where deptno=20;
--使用子查询
update emp1 set job=(select job from emp1 where empno=7917),
sal=(select sal from emp1 where empno=7917) where empno=7369;

--
select empno,job,sal from emp1 where empno in(7917,7369)

--删除数据delete
delete from emp1 where empno=7369;

--一次性删除多条记录
delete emp1 where deptno=40
delete emp1 where deptno is null
delete emp1 where sal between 1000 and 3000;

--使用select 子查询删除基于其他表中的数据
delete emp1 where deptno =(select deptno from scott.dept where loc='new york')

--数据库事务
--commit 手动提交事务，rollback手动回滚事务，savepoint 设置保存
--DDL语言和DCL语言会自动提交事务，DML语言需要手动提交事务
--会话异常结束或系统用户异常终止会自动回滚事务
--提价后的事务不能回滚
insert into emp1(empno,ename,job,hiredate,sal)values('7910','张三 ','DBA',sysdate,5000);
commit;
insert into emp1(empno,ename,job,hiredate,sal)values('7911','李四 ','java开发',sysdate,6000);
rollback;
insert into emp1(empno,ename,job,hiredate,sal)values('7912','王五 ','DBA',sysdate,5500);
commit;
insert into emp1(empno,ename,job,hiredate,sal)values('7913','马六 ','java开发',sysdate,6500);
rollback;

 

insert into emp1(empno,ename,job,hiredate,sal)values('7920','刘德华 ','演员',sysdate,10000);
savepoint A;
update emp1 set sal=20000 where empno=7920;
savepoint B;
delete from emp1 where empno=7920;
--只能从下往上，最近的到早期的，不能早期的到最近的。
rollback to savepoint b;
select *from emp1;