Oracle06、增刪改數據

昨天整理了關於Oracle的查詢的相關知識，今天將之前的學習筆記整理了一下，準備介紹一下關於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)刪除速度慢，需要逐行刪除