阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle增删改数据详解

201次阅读
没有评论

共计 4371 个字符,预计需要花费 11 分钟才能阅读完成。

今天将之前的学习笔记整理了一下,准备介绍一下关于 Oracle 增删改的相关知识。之后博主也会将之前学习的一些学习的基础的笔记作为博文发出来,希望读者能够多多指正,毕竟之前的学习肯定存在了些许的不足之处。整理笔记已经按照分类分类完成了。

一、回顾 SQL92/99 标准的四大类

(1)DML(数据操纵语言):select,insert,update,delete
(2)DDL(数据定义语言):create table,alter table,drop table,truncate table
(3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott
(4)TCL(事务控制语言):commit,rollback,savepoint to 回滚点

二、增删改数据

/*向 emp 表中插入一条记录(方式一:按表默认结构顺序)insert into 表名 values ... 语法*/
insert into emp values (1111, 'JACK', 'IT', 7788, sysdate, 1000, 100, 40);

/*向 emp 表中插入一条记录(方式二:按自定义顺序)insert into 表名(列名) values ... 语法*/
insert into emp
  (ENAME, EMPNO, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values
  ('MARRY', 2222, 'IT', 7788, sysdate, 1000, 100, 40);

/*向 emp 表中插入 NULL 值(方式一:采用显示插入 NULL 值)*/
insert into emp values (3333, 'SISI', 'IT', 7788, sysdate, 1000, NULL, 40);

/*向 emp 表中插入 NULL 值 (方式二:采用隐式插入 NULL 值),前提是所插入的字段允许插入 NULL 值*/
insert into emp
  (ENAME, EMPNO, JOB, MGR, HIREDATE, SAL, DEPTNO)
values
  ('SOSO', 4444, 'IT', 7788, sysdate, 1000, 40);

/*使用 & 占位符,动态输入值,& 可以运用在任何一个 DML 语句中,在 values 子句中使用,例如:'&ename' 和 &sal*/
insert into emp
values
  (&empno, '&ename', '&job', &mgr, &hiredate, &sal, &comm, &xxxxxxxx);
注意:&是 sqlplus 工具提供的占位符,如果是字符串或日期型要加 '' 符,数值型无需加 ''/*使用 & 占位符,动态输入值,& 可以运用在任何一个 DML 语句中,在 from 子句中使用*/
select * from &table;

/*使用 & 占位符,动态输入值,& 可以运用在任何一个 DML 语句中,在 select 子句中使用*/
select empno,ename,&colname from emp;

/*使用 & 占位符,动态输入值,& 可以运用在任何一个 DML 语句中,在 where 子句中使用*/
select * from emp where sal > &money;

/*使用 & 占位符,动态输入值,& 可以运用在任何一个 DML 语句中,在 group by 和 having 子句中使用*/
select &deptno, avg(sal) from emp group by deptno having avg(sal) > &money;

/*删除 emp 表中的所有记录*/
delete from emp;

/*将 xxx_emp 表中所有 20 号部门的员工,复制到 emp 表中,批量插入,insert into 表名 select ... 语法*/
insert into emp
  select * from xxx_emp where deptno = 20;

/*将 'SMITH' 的工资增加 20%*/
update emp set sal=sal*1.2 where ename = upper('smith');

/*将 'SMITH' 的工资设置为 20 号部门的平均工资,这是一个条件未知的事物,优先考虑子查询*/
第一:20 号部门的平均工资
select avg(sal) from emp where deptno=20;
第二:将 'SMITH' 的工资设置为 2207
update emp set sal=2207 where ename = 'SMITH'; 
子查询:update emp
   set sal =
       (select avg(sal) from emp where deptno = 20)
 where ename = 'SMITH';

/*删除工资比所有部门平均工资都低的员工,这是一个条件未知的事物,优先考虑子查询*/
第一:查询所有部门的平均工资
select avg(sal) from emp group by deptno;
第二:删除工资比 (*,*,*) 都低的员工
delete from emp where sal<all(*,*,*);
子查询:delete from emp where sal < all (select avg(sal) from emp group by deptno);

/*删除无佣金的员工*/
delete from emp where comm is null;

/*将 emp 表丢入回收站,drop table 表名*/
drop table emp;

/*查询回收站,show recyclebin,但是经过使用发现这个语句不能使用,这里贴出来参考*/
show recyclebin;

/*查询回收站,使用下面语句,亲测可以*/
select * from recyclebin;
create table t_hzp(id number(12) primary key,
       name varchar(32)
);--创建表
insert into t_hzp (id,name) values (12,'夜孤寒 ');-- 插入数据
select * from t_hzp;--查询表数据
drop table t_hzp;--删除表
select * from t_hzp;--测试表是否已经删除
select * from recyclebin;--查询回收站是否有已删除表

/*从回收站将 emp 表闪回,flashback table 表名 to before drop*/
flashback table emp to before drop;--亲测可以,但是可能要在 command sql 窗口使用

/*清空回收站,purge recyclebin*/
drop table t_hzp;--删除表
select * from recyclebin;--查看删除的表是不是在回收站中
purge recyclebin;--清空回收站
select * from recyclebin;--查询清空之后删除的表是不是还是在回收站(亲测不在回收站中了)

/*使用关键字 purge,彻底删除 emp 表,即不会将 emp 表丢入回收站,永久删除 emp 表,drop table 表名 purge*/
create table t_hzp(id number(12) primary key,
       name varchar(32)
);--创建表
insert into t_hzp (id,name) values (12,'夜孤寒 ');-- 插入数据
select * from t_hzp;--查询表数据
drop table emp purge;--彻底删除表
select * from recyclebin;--查询删除的表是不是在回收站中

/*依据 xxx_emp 表结构,创建 emp 表的结构,但不会插入数据*/
create table xxx_emp
as
select * from emp where 1<>1;--复制表结构
select * from xxx_emp;--


/*当不小心使用关键字 purge 将表彻底删除之后怎么回复?如果没有办法的话,那就创建吧...*/
create table EMP
(empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

/*使用下面语句更新或者创建对象比较方便*/
select * from emp for update;

/*创建 emp 表,复制 xxx_emp 表中的结构,同时复制 xxx_emp 表的所有数据*/
create table yyy_emp
as
select * from emp where 1=1;--复制表
select * from yyy_emp;--查询表
注意:where 不写的话,默认为 true

/*将 emp 截断,再自动创建 emp 表,truncate table 表名*/
truncate table emp;

/*向 emp 表,批量插入来自 xxx_emp 表中部门号为 20 的员工信息,只包括 empno,ename,job,sal 字段*/
insert into emp
  (empno, ename, job, sal)
  select empno, ename, job, sal from xxx_emp where deptno = 20;

/*使用关键字 purge,彻底删除 emp 表,即不会将 emp 表丢入回收站,不要乱用,不然就炸了*/
drop table emp purge;

/*依据 xxx_emp 表,只创建 emp 表,但不复制数据,且 emp 表只包括 empno,ename 字段*/
create table emp(empno,ename)
as
select empno,ename from xxx_emp where 1=2;

/*向 emp 表(只含有 empno 和 ename 字段),批量插入 xxx_emp 表中部门号为 20 的员工信息*/
insert into emp
  (empno, ename)
  select empno, ename from xxx_emp where deptno = 20;

 以上基本涵盖了 Oracle 常用的增删改操作,如果读者觉得有遗漏的话,欢迎指正,谢谢!

三、drop、truncate、delete 的区别

drop table 和 truncate table 和 delete from 区别:
drop table
1)属于 DDL
2)不可回滚
3) 不可带 where
4)表内容和结构删除
5) 删除速度快

truncate table
1)属于 DDL
2)不可回滚
3) 不可带 where
4)表内容删��
5)删除速度快

delete from
1)属于 DML
2)可回滚
3) 可带 where
4)表结构在,表内容要看 where 执行的情况
5) 删除速度慢,需要逐行删除

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147635.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4371字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中