注意
Oracle 11G R2 官方文档
1 使用INSERT 命令
1.1 语法
INSERT INTO <TABLE> [(colum[,column.....])]VALUES (values[,value....])
INSERT INTO 表名(列名列表) VALUES(值列表);
table: 指定表或视图
column:指定列名,多列之间用,分开
value: 指定待插入的数据,多值之间依,分开
1.2 基本使用
/* Formatted on 2018/2/12 14:59:34 (QP5 v5.313) */
--1.insert 语句
--1.1
SELECT * FROM sql02; -- for update
--1.2
SELECT * FROM sql03;
INSERT INTO sql03 (sql03_id1, sql03_id2, sql03_id3)
VALUES (1, 11, 'sql03_11');
COMMIT;
--1.3
SELECT * FROM sql04;
INSERT INTO sql04
VALUES (1,
'11g sql04 01',
'active',
60,
SYSDATE,
20);
INSERT INTO sql04
VALUES (2,
'11g sql04 02',
'active',
100,
SYSDATE,
40);
COMMIT;
--1.4
SELECT * FROM sql03;
INSERT INTO sql03
VALUES (&sql03_id1, &sql03_id2, &sql03_id3);
COMMIT;
--1.5
CREATE TABLE sql05
AS
SELECT * FROM sql04;
DELETE FROM sql05;
COMMIT;
SELECT * FROM sql05;
INSERT INTO sql05
SELECT * FROM sql04;
COMMIT;
SELECT * FROM sql05;
--1.6
INSERT INTO sql04
VALUES (3,
'11g sql04 03',
'active',
160,
SYSDATE,
20);
INSERT INTO sql04
VALUES (4,
'11g sql04 04',
'active',
220,
SYSDATE,
40);
COMMIT;
SELECT * FROM sql04;
INSERT INTO sql05
SELECT *
FROM sql04
WHERE sql04_price > 200;
COMMIT;
SELECT * FROM sql05;
2 使用UPDATE 命令
2.1 语法
UPDATE 表名称SET 列名称= 新值<WHERE 条件>
2.2 基本使用
/* Formatted on 2018/2/12 15:00:37 (QP5 v5.313) */
--2.1
SELECT *
FROM sql02
FOR UPDATE;
--2.2
UPDATE sql02
SET sql02_number = 20002
WHERE sql02_id = 2;
UPDATE sql02
SET sql02_status = 'no'
WHERE sql02_id = 2;
SELECT *
FROM sql02 commit;
--2.3
SELECT *
FROM sql02 update sql02 set sql02_id2=222,sql02_status='OK' where sql02_id=2;
--2.4
SELECT *
FROM sql04 update sql04
set (sql04_desc, sql04_count) =
(select sql04_desc, sql04_count from sql04 where sql04_id = 2)
where sql04_id = 3;
COMMIT;
SELECT * FROM sql04
3 使用DELETE 命令
3.1 语法
DELETE FROM <table/view> [WHERE <condition>]
3.2 基本使用
--3.1
SELECT *
FROM sql02
FOR UPDATE;
select * from sql02
--3.2
select * from sql04
delete from sql04 where sql04_id=1;
select * from sql04
--3.3
delete from sql04 where sql04_id=2 and sql04_id=3;
DELETE FROM sql04
WHERE sql04_id IN (2, 3);
DELETE FROM sql04
WHERE sql04_price IN ('160', '100');
select * from sql04
--3.4
select * from sql03
delete from sql03;
COMMIT;
--truncate table
4 创建PL/SQL 对象
4.1 语法
/* Formatted on 2018/2/12 15:01:26 (QP5 v5.313) */
SELECT * FROM yg;
--创建
CREATE OR REPLACE PROCEDURE yg_count
AS
v_yg_count1 NUMBER (10);
v_yg_count2 NUMBER (10);
BEGIN
SELECT COUNT (*) INTO v_yg_count1 FROM yg;
DELETE FROM yg
WHERE job_id = 'SH_CLERK';
SELECT COUNT (*) INTO v_yg_count2 FROM yg;
DBMS_OUTPUT.put_line ('离职前的员工数:' || v_yg_count1);
DBMS_OUTPUT.put_line ('离职后的员工数:' || v_yg_count2);
END;
/
SET SERVEROUTPUT ON;
EXECUTE yg_count;
--离职前的员工数:107
--离职后的员工数:97
--删除
DROP PROCEDURE yg_count;
5 事务概念与控制
5.1 事务说明
事务是对数据库操作的逻辑单位,在一个事务中可以包含一条或多条DML (数据操纵语言)、DDL (数据定义语言)和DCL (数据控制语言)语句,这些语句组成一个逻辑整体。
事务的执行只有两种结果:要么全部执行,把数据库带入一个新的状态,要么全部不执行,对数据库不做任何修改。
一组SQL,一个逻辑工作单位,执行时整体修改或者整体回退。
5.2 事务相关概念
1)事务的提交和回滚:COMMIT/ROLLBACK
2)事务的开始和结束
开始事务:连接到数据库,执行DML、DCL、DDL 语句
结束事务:1. 执行DDL(例如CREATE TABLE),DCL(例如GRANT),系统自动
执行COMMIT 语句
2. 执行COMMIT/ROLLBACK
3. 退出/断开数据库的连接自动执行COMMIT 语句
4. 进程意外终止,事务自动rollback
5. 事务COMMIT 时会生成一个唯一的系统变化号(SCN)保存
到事务表
3)保存点(savepoint): 可以在事务的任何地方设置保存点,以便ROLLBACK
4)事务的四个特性ACID :
1. Atomicity(原子性): 事务中sql 语句不可分割,要么都做,要么都不做
2. Consistency(一致性) : 指事务操作前后,数据库中数据是一致的,数据
满足业务规则约束(例如账户金额的转出和转入),与原子性对应。
3. Isolation(隔离性):多个并发事务可以独立运行,而不能相互干扰,一
个事务修改数据未提交前,其他事务看不到它所做的更改。
4. Durability(持久性):事务提交后,数据的修改是永久的。
5) 死锁:当两个事务相互等待对方释放资源时,就会形成死锁。
5.3 oracle 事务隔离级别
1. 幻想读:
事务T1 读取一条指定where 条件的语句,返回结果集。此时事务T2 插入一行新记录并commit,恰好满足T1 的where 条件。然后T1 使用相同的条件再次查
询,结果集中可以看到T2 插入的记录,这条新纪录就是幻想。
2. 不可重复读取:
事务T1 读取一行记录,紧接着事务T2 修改了T1 刚刚读取的记录并commit,然后T1 再次查询,发现与第一次读取的记录不同,这称为不可重复读。
3. 脏读:
事务T1 更新了一行记录,还未提交所做的修改,这个T2 读取了更新后的数
据,然后T1 执行回滚操作,取消刚才的修改,所以T2 所读取的行就无效,也就是脏数据。
oracle 支持的隔离级别:(不支持脏读)
READ COMMITTED--不允许脏读,允许幻想读和不可重复读
SERIALIZABLE--以上三种都不允许
sql 标准还支持READ UNCOMMITTED (三种都允许)和REPEATABLE READ(不允
许不可重复读和脏读,只允许幻想读)
SET TRANSACTION----设置事务属性
SET CONSTRAINT -----设置约束
SAVEPOINT ------------建立存储点
RELEASE SAVEPOINT --释放存储点
ROLLBACK---------------回滚
COMMIT------------------提交
6 锁的检测和锁争用
6.1 解决死锁冲突
- 1)执行commit 或者rollback 结束事务
在等待资源时执行,查找阻塞会话
SELECT sid, serial#, username
FROM v$session
WHERE sid IN (SELECT blocking_session FROM v$session);
杀掉会话
ALTER SYSTEM KILL SESSION '111,222';
关于查看锁的一些视图
SELECT * FROM V$SESSION; --查看会话和锁的信息
SELECT * FROM V$SESSION_WAIT; --查看等待的会话信息
SELECT * FROM V$LOCK; --系统中所有锁
SELECT * FROM V$LOCKED_OBJECT; --系统中DML 锁
SELECT l.sid, s.SERIAL#, sq.sql_text
FROM v$lock l, v$session s, v$sql sq
WHERE l.sid = s.sid AND s.sql_id = sq.sql_id AND s.status = 'ACTIVE'
alter system kill session '139,1431'
7 了解撤销数据UNDO
7.1 查看近来数据库生成的撤销数据量
ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';
SELECT begin_time,
end_time,
(undoblks *
(SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size'))
undo_bytes
FROM v$undostat;