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;

Oracle系列:(19)增删改数据

&占位符应用于select的列名】使用&占位符,动态输入值,&可以运用在任何一个DML语句中,在select子句中使用

select empno,ename,&colname from emp;

Oracle系列:(19)增删改数据

&占位符应用于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;

Oracle系列:(19)增删改数据

将'SMITH'的工资增加20%

update emp set sal=sal*1.2 where ename = upper('smith');

Oracle系列:(19)增删改数据

将'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

子查询:

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;

Oracle系列:(19)增删改数据

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

Oracle系列:(19)增删改数据

向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)删除速度慢,需要逐行删除


新闻标题:Oracle系列:(19)增删改数据
分享URL:http://scyanting.com/article/jicosg.html