Oracle之数据修改

一;插入

 

如果只要一个表的表结构 结尾添加 where 1=0

 

SQL> create table dept as select * from departments where 1=0;

 

Table created.

 

当插入字段数和表字段数一致可以不输入字段名，数目不一致时需指明字段，操作如下

 

 

SQL> desc dept;

 

 

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPARTMENT_ID NUMBER(4)

DEPARTMENT_NAME NOT NULL VARCHAR2(30)

MANAGER_ID NUMBER(6)

LOCATION_ID NUMBER(4)

 

SQL> insert into dept values(70,'public relation',100,1700);

 

1 row created.

 

SQL> insert into dept values(80,'purchase');

insert into dept values(80,'purchase')

*

ERROR at line 1:

ORA-00947: not enough values

 

 

SQL> insert into dept(department_id,department_name) values(80,'purchase');

 

1 row created.

 

注意 ：插数据时 字符，日期类型要用单引号

如果没有对应可用空占位插入

SQL> insert into dept values(80,'purchase',null,null);

 

1 row created.

 

在插入日期数据是最好使用to_date进行转换和指定显示格式不然容易因为语言原因导致无法插入数据

 

指定插入：

SQL> insert into

2 (select employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id from emp where manager_id=120)

3 values(199,'sds','dsa','dfwe',(select phone_number from emp where employee_id=183),'23-MAY-04','SH_CLERK',5100,null,152,50);

 

1 row created.

即使子查询结果为多个也直插入一条结果

 

批量插入：

 

SQL> insert into dept(department_id,department_name,manager_id,location_id)

2 select department_id,department_name,manager_id,location_id from departments where department_id=100;

 

修改：

1：直接修改：

SQL> update emp

2 set job_id='FI_ACCO',

3 salary=(8200)

4 where employee_id=112;

 

1 row updated.

 

2：条件修改 ： 修改employee_id为112的薪资和工作与ID为110的一致

操作如下：

SQL> select employee_id,salary,job_id from emp where employee_id=110

2 ;

 

EMPLOYEE_ID SALARY JOB_ID

----------- ---------- ----------

110 8200 FI_ACCOUNT

 

SQL> select employee_id,salary,job_id from emp where employee_id=112

2 ;

 

EMPLOYEE_ID SALARY JOB_ID

----------- ---------- ----------

112 7800 FI_ACCOUNT

 

 

SQL> update emp

2 set job_id=(select job_id from emp where employee_id=110),

3 salary=(select salary from emp where employee_id=110)

4 where employee_id=112;

 

1 row updated.

 

这句语法可以进行优化，这句话实行了两次查询可使用如下语句，即可只查询一次提升性能：

操作如下：

SQL> update emp

2 set (job_id,salary)=(select job_id,salary from emp where employee_id=110)

3 where employee_id=112;

 

1 row updated.

注：子查询结果必须唯一

 

3：删除

SQL> select * from dept;

 

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

100 Finance 108 1700

101 adbgw 109 1900

102 uvuyv 110 2000

 

指定删除

SQL> delete from dept where department_id=100;

 

1 row deleted.

 

SQL> select * from dept;

 

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

101 adbgw 109 1900

102 uvuyv 110 2000

 

删除时必须指明删除内容或字段否则删除的将是整个表

SQL> delete from dept;

 

2 rows deleted.

 

SQL> select * from dept;

 

no rows selected

 

删除后没有提交可以回滚找回删除数据，操作如下

 

SQL> rollback;

 

Rollback complete.

 

SQL> select * from dept;

 

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

100 Finance 108 1700

101 adbgw 109 1900

102 uvuyv 110 2000

 

 

注：当表很大时使用delete时会很慢，因为他会写日志。此时可考虑使用 truncate：

但他是DDL语句，无法后悔；只有在确保数据不使用时才能使用

 

事物可以设置保存点方便回滚至指定位置

保存点也可以使用别名

 

DDL 语句和 DCL 语句属于事物语句，一个事物开始只要不提交数据就只有当前更改的当前人员可看，其余人员看不见修改，但若是提交或是另一个事物开始则当前人员的更改将会自动提交，其余人员就可看见更改

 

在操作删除更新等操作时如果不提交，存在行级锁则其他用户无法更改同行内的所有字段，只有提交事务后其他人员才能执行操作

 

杀死一个事物就是将事物回滚