Oracle系列：（19）增刪改數據

col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
col tname for a40;
set pagesize 80;

--創建新表xxx_emp，復制emp表中的結構，同時復制emp表的所有數據
create table xxx_emp
as 
select * from emp;

回顧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);

【&占位符應用于insert】使用&占位符，動態輸入值，&可以運用在任何一個DML語句中，在values子句中使用，例如：'&ename'和&sal

insert into emp values(&empno,'&ename','&job',&mgr,&hiredate,&sal,&comm,&xxxxxxxx);

注意：&是sqlplus工具提供的占位符，如果是字符串或日期型要加''符，數值型無需加''符

【&占位符應用于select的表名】使用&占位符，動態輸入值，&可以運用在任何一個DML語句中，在from子句中使用

select * from &table;

【&占位符應用于select的列名】使用&占位符，動態輸入值，&可以運用在任何一個DML語句中，在select子句中使用

select empno,ename,&colname from emp;

【&占位符應用于where】使用&占位符，動態輸入值，&可以運用在任何一個DML語句中，在where子句中使用

select * from emp where sal > &money;

【&占位符應用于group by和having】使用&占位符，動態輸入值，&可以運用在任何一個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;

從回收站將emp表閃回，flashback table 表名 to before drop

flashback table emp to before drop;

查詢回收站，show recyclebin

show recyclebin;

清空回收站，purge recyclebin

purge recyclebin;

使用關鍵字purge，徹底刪除emp表，即不會將emp表丟入回收站，永久刪除emp表，drop table 表名 purge

drop table emp purge;

依據xxx_emp表結構，創建emp表的結構，但不會插入數據

create table emp
as
select * from xxx_emp where 1<>1;

create table emp(empno,ename)
as
select empno,ename from xxx_emp where 1=2;

創建emp表，復制xxx_emp表中的結構，同時復制xxx_emp表的所有數據

create table emp
as
select * from xxx_emp where 1=1;

注意：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;

向emp表（只含有empno和ename字段），批量插入xxx_emp表中部門號為20的員工信息

insert into emp(empno,ename)
select empno,ename from xxx_emp where deptno=20;