Oracle学习笔记之增删改数据

学习要求：
1）掌握增、删、改数据和事务操作

2）掌握【视图】和同义词

3）掌握【序列】和索引

4）了解有关用户和权限的控制

准备篇

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

使用&占位符，动态输入值，&可以运用在任何一个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;

从回收站将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;

创建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;

使用关键字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;

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)删除速度慢,需要逐行删除
当然delete from还有个坏处就是容易产生磁盘碎片，好处就是回滚，在企业里用的多
软件工程师