SQL学习自我总结（七）
时间：2010-08-25 来源：ji_chunhui
数据库操纵语言(DML)主要包括：插入、删除、更新
一条DML将开始一个事务，接下来的DML都是同一事务中的语句，直到提交或回滚。
在DML中也可以使用子查询 insert插入语句的语法
在插入时，可以有选择的只提供部分列的值，未在插入语句中值的列，将是空值或由defaulte子句定义的缺省值 insert语句的语法：
insert into table [column1,column2...] values (value1,value2...)
上面的字段列表不是必须的
字符和日期值应放在单引号中，数字值则不需要 范例：
insert into departments
(department_id,department_name,manager_id,location_id)
values
(70,'public relations',100,1700); 对于空值可以显式插入和隐式插入
范例：
insert into departments
(department_id,department_name)
values
(30,'purchasing'); insert into departments
values
(100,'finance',null,null); insert插入语句的变化
插入特殊值
insert into employees
(
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
values
(
113,'louis','popp','lpopp','515.124.4567',sysdate,'ac_account',6900,null,205,100
);
用sysdate函数得到当前日期 从另一个表复制
这就需要在values子句中用到一个子查询
在insert子句的字段列表中的列的数目和数据类型必须和子查询中的值以及数据类型相匹配。
从另外一个表复制分为两种情况，看如下范例：
insert into sales_reps
(
id,
name,
salary,
commission_pct
)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like '%rep'; 第二种是从dual表查询特定的数据、子查询，取得插入的数据
insert into departments
select 999 'test_part',(select 400 from dual), 4422 from dual;
******有待测试，还没进行测试****** 在insert语句中使用子查询
可以在insert语句的into子句中用一个子查询代替表名，范例：
insert into
(
select employee_id,
last_name,
email,
hire_date,
job_id,
salary,
department_id
from employees
where department_id = 50)
values
(
9999,
'taylor',
'dtaylor',
sysdate,
'st_clerk',
5000,
50);
注意：该子查询的选择列表必须与values子句的字段列表有相同的字段数，且必须遵循顺序。 update更新数据的语法
范例：
update employees set department_id = 70 where employee_id = 113; update更新的例子和问题
在update中，多处可以使用子查询
--表名处可以使用子查询
--新的列值处也可以使用子查询 范例：
update employees set job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
where employee_id = 114; 更新基于另一个表的行
范例：用基于来自employees表的值更新copy_emp表
update copy_emp set department_id =
(
select department_id
from employees
where employee_id = 100
)
where job_id =
(
select job_id
from employees
where employee_id = 200
); update更新导致完整性约束报错
看看下面两个会报错的update语句：
update employees
set employee_id = 100
where employee_id = 101; update employees
set department_id = 55
where department_id =110; 主键是该表中的唯一约束，不能重复，外键是该表中的字段与另外一个表的主键字段名相同的情况下设置的一种约束，外键约束不满足的时候，数据也无法更新或者插入。 delete删除数据的语法
delete命令可以删除表中的一行或者多行记录
如果省略where子句，将删除表中所有的行，相当于truncate语句，不过两者还是有那么点小差异的，且truncate速度更快一些！
删除的条件中可以使用子查询，子查询可以是针对任意表的，可是不是被删除行的表 范例：
delete from departments where department_name = 'finance';
delete from employees where employee_id = 114;
delete from departments where department_id in (30,40);
delete from copy_emp; 删除基于另一个表的行
delete from employees
where department_id =
(
select department_id
from departments
where department_name like '%pubic%'
); 显示默认值
default关键字可以被用于insert和update语句来确定默认的列值
范例：
insert into departments
(
department_id,
departname_name,
manager_id
)
values
(
300,
'engineering',
default
); update departments
set manager_id = default
where department_id = 10; merge合并语句语法
(待深入研究) 数据库事务处理
在事务中所做的每一个数据改变在事务被提交之前都是临时的。
oracle服务器利用读一致性来确保每个用户看到的数据和上次提交时相同，其中受影响的行被锁定，其他用户不能改变受影响的行中的数据。
就oracle服务器来说，数据的改变在事务被提交之前可能实际上已被写入数据库文件，但他们仍然是临时的。
如果许多用户同时对相同的表作了修改，那么，直到用户提交他们的修改之前，每个用户只能看到他自己的修改。
默认情况下，oracle服务器有行级锁(row-level locking)
使用commit语句后将会产生哪些变化呢：
数据的改变被写到数据库中，数据以前的状态永久丢失。所有用户都可以观察到事务的结果。受影响的行被释放；其他用户现在可以对行进行新的数据改变，同时所有保存点也被释放。
相反使用rollback语句后将会产生哪些变化呢：
数据的改变被还原，数据以前的状态被恢复，受影响的行上的锁被释放 默认情况下oracle数据库是在行级别上锁定数据，但是当执行带for update子句的lock table语句或select语句可以手动获得数据库表上的锁。 事务的控制
显示控制语句
commit 结束当前事务，使得所有未决的数据永久改变
savepoint name 在当前事务中标记保存点
rollback 结束当前事务，丢弃所有未决的数据改变
rollback to savepoint name 回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。
如果你用与前面的保存点相同的名字创建了另一个保存点，那么早一点时间创建的保存点就被删除了。 隐式事务处理
在以下情况下将自动提交事务处理：
1、DDL语句被发送
2、DCL语句被发送
3、正常退出iSQL*Plus
4、没有明确的发送commit或rollback语句 读一致性
原理：
在对数据库进行插入、更新或者删除时，oracle服务器会在数据改变之前获得相关数据的拷贝，并且将这些数据写到一个回滚段(undo segment)。所以一切读数据者看到的数据都是回滚段中的数据快照，而在改变被提交到数据库之前，只有正在修改数据的用户能看见数据库的改变。这样就确保了数据的读一致性。当一个DML语句被提交时，回滚段中原本被旧文件占用的空间被释放出来重新使用，所以数据的改变select用户也能看到了。 锁定
锁分为两种：隐式锁定、显示锁定
隐式锁定时oracle使用最多的锁，通常用户不必生命要对谁加锁，oracle自动可以为操作的对象加锁，这就是隐式锁定。
显示锁定是指用户使用某些命令明确给某些对象加锁(日常操作中很少用) 范例：
lock table table_name in row share mode;
lock table table_name in row exclusive mode;
lock table table_neme in share mode;
lock table table_name in share row exclusive mode;
lock table table_name in exclusive mode;
隐式锁又分为两种：独占锁、共享锁
独占锁：对于用DML语句修改的每一行，独占锁会被自动分配，且其作用就是防止该事务在提交前被修改的行被其他事务进行修改。
共享锁：共享锁是在表级别的DML操作期间获得的，允许其他用户访问。用共享锁模式，几个事务可以在相同的资源上获得共享锁。
一条DML将开始一个事务，接下来的DML都是同一事务中的语句，直到提交或回滚。
在DML中也可以使用子查询 insert插入语句的语法
在插入时，可以有选择的只提供部分列的值，未在插入语句中值的列，将是空值或由defaulte子句定义的缺省值 insert语句的语法：
insert into table [column1,column2...] values (value1,value2...)
上面的字段列表不是必须的
字符和日期值应放在单引号中，数字值则不需要 范例：
insert into departments
(department_id,department_name,manager_id,location_id)
values
(70,'public relations',100,1700); 对于空值可以显式插入和隐式插入
范例：
insert into departments
(department_id,department_name)
values
(30,'purchasing'); insert into departments
values
(100,'finance',null,null); insert插入语句的变化
插入特殊值
insert into employees
(
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
values
(
113,'louis','popp','lpopp','515.124.4567',sysdate,'ac_account',6900,null,205,100
);
用sysdate函数得到当前日期 从另一个表复制
这就需要在values子句中用到一个子查询
在insert子句的字段列表中的列的数目和数据类型必须和子查询中的值以及数据类型相匹配。
从另外一个表复制分为两种情况，看如下范例：
insert into sales_reps
(
id,
name,
salary,
commission_pct
)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like '%rep'; 第二种是从dual表查询特定的数据、子查询，取得插入的数据
insert into departments
select 999 'test_part',(select 400 from dual), 4422 from dual;
******有待测试，还没进行测试****** 在insert语句中使用子查询
可以在insert语句的into子句中用一个子查询代替表名，范例：
insert into
(
select employee_id,
last_name,
email,
hire_date,
job_id,
salary,
department_id
from employees
where department_id = 50)
values
(
9999,
'taylor',
'dtaylor',
sysdate,
'st_clerk',
5000,
50);
注意：该子查询的选择列表必须与values子句的字段列表有相同的字段数，且必须遵循顺序。 update更新数据的语法
范例：
update employees set department_id = 70 where employee_id = 113; update更新的例子和问题
在update中，多处可以使用子查询
--表名处可以使用子查询
--新的列值处也可以使用子查询 范例：
update employees set job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
where employee_id = 114; 更新基于另一个表的行
范例：用基于来自employees表的值更新copy_emp表
update copy_emp set department_id =
(
select department_id
from employees
where employee_id = 100
)
where job_id =
(
select job_id
from employees
where employee_id = 200
); update更新导致完整性约束报错
看看下面两个会报错的update语句：
update employees
set employee_id = 100
where employee_id = 101; update employees
set department_id = 55
where department_id =110; 主键是该表中的唯一约束，不能重复，外键是该表中的字段与另外一个表的主键字段名相同的情况下设置的一种约束，外键约束不满足的时候，数据也无法更新或者插入。 delete删除数据的语法
delete命令可以删除表中的一行或者多行记录
如果省略where子句，将删除表中所有的行，相当于truncate语句，不过两者还是有那么点小差异的，且truncate速度更快一些！
删除的条件中可以使用子查询，子查询可以是针对任意表的，可是不是被删除行的表 范例：
delete from departments where department_name = 'finance';
delete from employees where employee_id = 114;
delete from departments where department_id in (30,40);
delete from copy_emp; 删除基于另一个表的行
delete from employees
where department_id =
(
select department_id
from departments
where department_name like '%pubic%'
); 显示默认值
default关键字可以被用于insert和update语句来确定默认的列值
范例：
insert into departments
(
department_id,
departname_name,
manager_id
)
values
(
300,
'engineering',
default
); update departments
set manager_id = default
where department_id = 10; merge合并语句语法
(待深入研究) 数据库事务处理
在事务中所做的每一个数据改变在事务被提交之前都是临时的。
oracle服务器利用读一致性来确保每个用户看到的数据和上次提交时相同，其中受影响的行被锁定，其他用户不能改变受影响的行中的数据。
就oracle服务器来说，数据的改变在事务被提交之前可能实际上已被写入数据库文件，但他们仍然是临时的。
如果许多用户同时对相同的表作了修改，那么，直到用户提交他们的修改之前，每个用户只能看到他自己的修改。
默认情况下，oracle服务器有行级锁(row-level locking)
使用commit语句后将会产生哪些变化呢：
数据的改变被写到数据库中，数据以前的状态永久丢失。所有用户都可以观察到事务的结果。受影响的行被释放；其他用户现在可以对行进行新的数据改变，同时所有保存点也被释放。
相反使用rollback语句后将会产生哪些变化呢：
数据的改变被还原，数据以前的状态被恢复，受影响的行上的锁被释放 默认情况下oracle数据库是在行级别上锁定数据，但是当执行带for update子句的lock table语句或select语句可以手动获得数据库表上的锁。 事务的控制
显示控制语句
commit 结束当前事务，使得所有未决的数据永久改变
savepoint name 在当前事务中标记保存点
rollback 结束当前事务，丢弃所有未决的数据改变
rollback to savepoint name 回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。
如果你用与前面的保存点相同的名字创建了另一个保存点，那么早一点时间创建的保存点就被删除了。 隐式事务处理
在以下情况下将自动提交事务处理：
1、DDL语句被发送
2、DCL语句被发送
3、正常退出iSQL*Plus
4、没有明确的发送commit或rollback语句 读一致性
原理：
在对数据库进行插入、更新或者删除时，oracle服务器会在数据改变之前获得相关数据的拷贝，并且将这些数据写到一个回滚段(undo segment)。所以一切读数据者看到的数据都是回滚段中的数据快照，而在改变被提交到数据库之前，只有正在修改数据的用户能看见数据库的改变。这样就确保了数据的读一致性。当一个DML语句被提交时，回滚段中原本被旧文件占用的空间被释放出来重新使用，所以数据的改变select用户也能看到了。 锁定
锁分为两种：隐式锁定、显示锁定
隐式锁定时oracle使用最多的锁，通常用户不必生命要对谁加锁，oracle自动可以为操作的对象加锁，这就是隐式锁定。
显示锁定是指用户使用某些命令明确给某些对象加锁(日常操作中很少用) 范例：
lock table table_name in row share mode;
lock table table_name in row exclusive mode;
lock table table_neme in share mode;
lock table table_name in share row exclusive mode;
lock table table_name in exclusive mode;
隐式锁又分为两种：独占锁、共享锁
独占锁：对于用DML语句修改的每一行，独占锁会被自动分配，且其作用就是防止该事务在提交前被修改的行被其他事务进行修改。
共享锁：共享锁是在表级别的DML操作期间获得的，允许其他用户访问。用共享锁模式，几个事务可以在相同的资源上获得共享锁。
相关阅读 更多 +
