Oracle 11g 第八章知识点总结——处理数据
知识点预览
复习
处理数据
复习
--建表t_user
create table t_user(
id number(4),
name varchar2(10),
password varchar2(20),
gender char(1),
birthday date
);
--插入数据
insert into t_user values(1,'小文','123','1');
insert into t_user values(2,'小文2','123','1',to_date('1990-09-09','YYYY-MM-DD'));
insert into t_user(id,name,gender) values(3,'晓晓','0');
--复制一张表
create table t_emp2
as
select* from emp where deptno=100;
--更新数据
update t_user
setgender='1',password='123456'
where name='晓晓';
--删除数据
delete [from] t_user
where(sysdate-created_time)/365>99
--事务
一个事务当中的操作要么同时成功，要么同时失败
commit
rollback
--删除表
Drop table
Purge table “BIN$JFszxZKrTvqrZdo2+rW+2g==$0”
处理数据
1. 数据控制语言
a) DML 可以在下列条件下执行:
向表中插入数据
修改现存数据
删除现存数据
b) 事务是由完成若干项工作的DML语句组成的。
2. 插入数据——INSERT 语句语法
a) 使用 INSERT 语句向表中插入数据。
INSERT INTO table [(column[,column...])]
VALUES (value [, value...]);
b) 使用这种语法一次只能向表中插入一条数据。
3. 插入数据
a) 为每一列添加一个新值。
b) 按列的默认顺序列出各个列的值。
c) 在 INSERT 子句中随意列出列名和他们的值。
d) 字符和日期型数据应包含在单引号中。
INSERT INTOdepartments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
1 row created.
4. 向表中插入空值
a) 隐式方式: 在列名表中省略该列的值。
INSERT INTO departments (department_id,
department_name )
VALUES (30, 'Purchasing');
1 row created.
b) 显示方式: 在VALUES 子句中指定空值。
INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);
1 row created.
5. 插入指定的值
SYSDATE 记录当前系统的日期和时间。
INSERT INTOemployees (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);
1 row created.
加入新员工
INSERT INTOemployees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD,YYYY'),
'AC_ACCOUNT', 11000, NULL, 100,30);
6. 创建脚本
a) 在SQL 语句中使用& 变量指定列值。
b) & 变量放在VALUES子句中。
INSERT INTOdepartments
(department_id, department_name,location_id)
VALUES (&department_id,'&department_name',&location);
7. 从其它表中拷贝数据
a) 在 INSERT 语句中加入子查询。
INSERT INTOsales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary,commission_pct FROM employees WHERE job_id LIKE '%REP%';
4 rows created.
b) 不必书写 VALUES 子句。
c) 子查询中的值列表应于 INSERT 子句中的列名对应。
8. 更新数据——UPDATE 语句语法
a) 使用 UPDATE 语句更新数据。
UPDATE table
SET column= value [, column = value, ...]
[WHERE condition];
b) 可以一次更新多条数据。
9. 更新数据
a) 使用 WHERE 子句指定需要更新的数据
UPDATE employees SET department_id= 70 WHEREemployee_id = 130;
1 rows updated.
b) 如果省略WHERE子句，则表中的所有数据都将被更新
UPDATE copy_emp SET department_id= 110;
22 rows updated.
10. 在UPDATE语句中使用子查询
a) 更新 114号员工的工作和工资使其与205号员工相同。
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;
1 row updated.
b) 在 UPDATE 中使用子查询，使更新基于另一个表中的数据。
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);
1 row updated.
11. 更新中的数据完整性错误。
UPDATE employees SET department_id = 55 WHERE department_id = 110;
UPDATEemployees
*
ERROR at line1:
ORA-02291:integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
不存在55号部门
12. 删除数据——DELETE 语句
使用 DELETE 语句从表中删除数据。
DELETE [FROM] table
[WHERE condition];
13. 删除数据
a) 使用WHERE 子句指定删除的记录。
DELETE FROM departments WHERE department_name = 'Finance';
1 row deleted.
b) 如果省略WHERE子句，则表中的全部数据将被删除。
DELETE FROM copy_emp;
22 rows deleted.
14. 在 DELETE 中使用子查询
在 DELETE 中使用子查询，使删除基于另一个表中的数据。
DELETE FROMemployees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
1 row deleted.
15. 删除中的数据完整性错误
DELETE FROMdepartments WHERE department_id = 60;
DELETE FROMdepartments
*
ERROR at line1:
ORA-02292:integrity constraint (HR.EMP_DEPT_FK) violated - child record found
You cannot delete a row that contains a primary key that isused as a foreign key in another table.
16. 在INSERT语句中使用子查询
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999,'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
1 row created.
SELECTemployee_id, last_name, email, hire_date,
job_id, salary, department_id
FROM employees
WHERE department_id = 50;
17. 在DML语句中使用 WITHCHECK OPTION 关键字
a) 使用子查询表示 DML 语句中使用的表
b) WITH CHECK OPTION 关键字避免修改子查询范围外的数据
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line1:
ORA-01402:view WITH CHECK OPTION where-clause violation
18. 显式默认值
a) 使用 DEFAULT 关键字表示默认值
b) 可以使用显示默认值控制默认值的使用
c) 显示默认值可以在 INSERT 和 UPDATE 语句中使用
19. 显示使用默认值
a) 在插入操作中使用默认值:
I
NSERT INTOdepartments (department_id, department_name, manager_id) VALUES (300,'Engineering', DEFAULT);
b) 在更新操作中使用默认值:
UPDATEdepartments SET manager_id =DEFAULT WHERE department_id = 10;
20. 合并语句
a) 按照指定的条件执行插入或更新操作
b) 如果满足条件的行存在，执行更新操作；否则执行插入操作:
i. 避免多次重复执行插入和删除操作
ii. 提高效率而且使用方便
iii. 在数据仓库应用中经常使用
21. 合并语句的语法
可以使用merge语句，根据指定的条件进行插入或更新操作
MERGE INTO table_nametable_alias
USING (table|view|sub_query)
alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
22. 合并语句举例
在对表COPY_EMP使用merge语句，根据指定的条件从表EMPLOYEES中插入或更新数据。
MERGE INTOcopy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHEDTHEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHEDTHEN
INSERT VALUES(e.employee_id, e.first_name,e.last_name,
e.email, e.phone_number, e.hire_date,e.job_id,
e.salary, e.commission_pct,e.manager_id,
e.department_id);
SELECT *
FROM COPY_EMP;
no rows selected
MERGE INTOcopy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHEDTHEN
UPDATE SET
...
WHEN NOT MATCHEDTHEN
INSERT VALUES...;
SELECT *
FROM COPY_EMP;
20 rows selected.
23. 数据库事务
a) 数据库事务由以下的部分组成:
i. 一个或多个DML 语句
ii. 一个 DDL 语句
iii. 一个 DCL 语句
b) 以第一个 DML 语句的执行作为开始
c) 以下面的其中之一作为结束:
i. COMMIT 或 ROLLBACK 语句
ii. DDL 或 DCL 语句（自动提交）
iii. 用户会话正常结束
iv. 系统异常终了
24. COMMIT和ROLLBACK语句的优点
使用COMMIT 和 ROLLBACK语句,我们可以:
确保数据完整性。
数据改变被提交之前预览。
将逻辑上相关的操作分组。
25. 控制事务
26. 回滚到保留点
a) 使用 SAVEPOINT 语句在当前事务中创建保存点。
b) 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
27. 事务进程
a) 自动提交在以下情况中执行:
i. DDL 语句。
ii. DCL 语句。
iii. 不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。
b) 会话异常结束或系统异常会导致自动回滚。
28. 提交或回滚前的数据状态
a) 改变前的数据状态是可以恢复的
b) 执行 DML 操作的用户可以通过SELECT 语句查询之前的修正
c) 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
d) DML语句所涉及到的行被锁定， 其他用户不能操作。
29. 提交后的数据状态
a) 数据的改变已经被保存到数据库中。
b) 改变前的数据已经丢失。
c) 所有用户可以看到结果。
d) 锁被释放， 其他用户可以操作涉及到的数据。
e) 所有保存点被释放。
30. 提交数据
a) 改变数据
DELETE FROMemployees WHERE employee_id = 99999;
1 row deleted.
INSERT INTOdepartments VALUES (290,'Corporate Tax', NULL, 1700);
1 row inserted.
b) 提交改变
COMMIT;
Commit complete.
31. 数据回滚后的状态
使用 ROLLBACK 语句可使数据变化失效:
数据改变被取消。
修改前的数据状态可以被恢复。
锁被释放。
DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.
32. 语句级回滚
a) 单独 DML 语句执行失败时，只有该语句被回滚。
b) Oracle 服务器自动创建一个隐式的保留点。
c) 其他数据改变仍被保留。
d) 用户应执行 COMMIT 或 ROLLBACK 语句结束事务。
33. 读一致性
a) 读一致性为数据提供一个一致的视图。
b) 一个用户的对数据的改变不会影响其他用户的改变。
c) 对于相同的数据读一致性保证:
i. 查询不等待修改。
ii. 修改不等待查询。
34. 锁
a) Oracle 数据库中，锁是 :
i. 并行事务中避免资源竞争。
ii. 避免用户动作。
iii. 自动使用最低级别的限制。
iv. 在事务结束结束前存在。
v. 两种类型: 显示和隐式。
b) 两种模式:
i. 独占锁:屏蔽其他用户。
ii. 共享锁:允许其他用户操作。
c) 高级别的数据并发性:
i. DML:表共享，行独占
ii. Queries:不需要加锁
iii. DDL:保护对象定义
d) 提交或回滚后锁被释放。
35. 总结
