[Oracle数据库学习]八、处理数据

数据控制语言DML

DML可以使用在：

1）向表中插入数据；

2）修改现有数据；

3）删除现有数据。

事务：是由完成若干项工作的DML语句组成的。

插入数据Insert

INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);

使用上面的语法，一次插入一条数据。

字符和日期型数据应包含在单引号中。

列出列名和他们的值

insert into employees (employee_id, employee_name, department_id, job_id, salary )
values (6,'Ford',1,'PM',12000);

按列的默认顺序列出各个列的值

insert into employees 
values (2,'Bob',1,'DEV',5000,0.12,to_date('2017-3-10','YYYY-MM-DD'),'abc%%',1);

向表中插入空值

隐式方式：在列名中省略该列

insert into employees (employee_id, employee_name, department_id, job_id, salary )
values (6,'Ford',1,'PM',12000);

SELECT *
FROM employees
WHERE employee_id = 6;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID	SALARY	COMMISSION_PCT	HIRE_DATE	DESCRIPTION	MANAGER_ID
6	Ford	1	PM	12000	(null)	(null)	(null)	(null)

显示方式: 在VALUES子句中指定空值

insert into employees 
values (1,'Alice',1,'PM',10000,Null,to_date('2007-11-15','YYYY-MM-DD'),Null,Null);

SELECT *
FROM employees
WHERE employee_id = 1;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID	SALARY	COMMISSION_PCT	HIRE_DATE	DESCRIPTION	MANAGER_ID
1	Alice	1	PM	10000	(null)	2007-11-15T00:00:00Z	(null)	(null)

插入指定的值

上述语句中使用固定值。

insert into employees 
values (7,'Gina',3,'BM',10000,Null,SYSDATE,Null,Null);

注：这里使用SYSDATE作为hire_date。

使用变量

INSERT INTO departments (department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);

&变量作为列值。

使用子查询从其它表中拷贝数据

注意子查询的结果列名需要与要插入的表的列名对应。

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

注：不需要使用VALUES子句。

更新数据Update

UPDATE table 
SET column = value[, column = value, ...]
[WHERE condition];

使用WHERE子句指定需要更新的数据，否则更新所有数据。

使用具体值

UPDATE employees
SET department_id = 2
WHERE employee_id = 1;

在UPDATE语句中使用子查询

UPDATE employees
SET salary = (SELECT salary 
              FROM employees 
              WHERE employee_id = 1 ),
    job_id = (SELECT job_id 
              FROM employees 
              WHERE employee_id = 1 )
WHERE employee_id = 3;

更新3号员工的job_id和salary与1号员工相同。

更新中的数据完整性错误

错误示例：employees表使用departments表中department_id作为外键，不存在department_id=100

UPDATE employees
SET department_id = 100
WHERE employee_id = 1;

结果：ORA-02291: integrity constraint (USER_4_DDC67.FK_EMPLOYEES) violated - parent key not found

删除数据DELETE

DELETE [FROM] table
[WHERE condition];

使用WHERE子句指定要删除的记录，否则删除全部。

DELETE FROM departments
WHERE department_id = 5;

DELETE FROM copy_emp;

在DELETE 中使用子查询

DELETE FROM employees
WHERE department_id = (SELECT department_id 
                       FROM departments
                       WHERE department_name LIKE '%P%');

删除中的数据完整性错误

错误示例：department_id作为employees表的外键

DELETE FROM departments
WHERE department_id = 1;

结果：ORA-02292: integrity constraint (USER_4_DDC67.FK_EMPLOYEES) violated - child record found

使用WITH CHECK OPTION关键字

避免修改子查询范围外的数据。

INSERT INTO (SELECT employee_id, employee_name,department_id,job_id,salary,commission_pct,hire_date,description,manager_id
             FROM employees 
             WHERE department_id = 5 WITH CHECK OPTION)
VALUES (1,'Alice',1,'PM',10000,Null,to_date('2007-11-15','YYYY-MM-DD'),Null,Null);

结果：ORA-01402: view WITH CHECK OPTION where-clause violation

显式默认值

使用DEFAULT关键字表示默认值
使用显示默认值控制默认值的使用
可以在INSERT和UPDATE语句中使用

在插入操作中使用默认值

INSERT INTO departments
VALUES (5,'Personnel',DEFAULT);

在更新操作中使用默认值

UPDATE departments
SET location_id = DEFAULT
WHERE department_id = 3;

合并语句MERGE

按照指定的条件执行更新或插入操作：存在满足条件的行则更新，否则插入。

特点：

避免多次重复执行插入和删除操作；
提高效率而且使用方便；
在数据仓库应用中经常使用。

MERGE INTO table_name table_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);

下面两张表：

SELECT * FROM departments;

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
1	Development	1
2	Trade	2
3	Qulity	4
4	Finance	2

SELECT * FROM copy_department;

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
1	Development1	3

使用MERGE语句：department_id相同时，更新copy_department表数据；否则插入数据。

MERGE INTO copy_department c
USING departments d
ON (c.department_id = d.department_id)
WHEN MATCHED THEN
UPDATE SET
c.department_name = d.department_name,
c.location_id = d.location_id
WHEN NOT MATCHED THEN
INSERT VALUES(d.department_id, d.department_name, d.location_id);

执行后查询：department_id=1的数据被更新，其他为插入。

SELECT * FROM copy_department;

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
1	Development	1
2	Trade	2
3	Qulity	4
4	Finance	2

数据库事务

组成

数据库事务由以下的部分组成:
1）一个或多个DML语句
2）一个DDL语句
3）一个DCL语句

执行顺序

以第一个DML语句的执行作为开始
以下面的其中之一作为结束:
1）COMMIT或ROLLBACK语句
2）DDL或DCL语句（自动提交）
3）用户会话正常结束
4）系统异常终了

优点

使用COMMIT和ROLLBACK语句，我们可以:
1）确保数据完整性；
2）数据改变被提交之前预览；
3）将逻辑上相关的操作分组。

保存点及回滚

可以使用SAVEPOINT语句在当前事务中创建保存点。
使用ROLLBACK TOSAVEPOINT语句回滚到创建的保存点。

UPDATE ...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

事务进程

自动提交

自动提交在以下情况中执行：
1）DDL 语句。
2）DCL 语句。
3）不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话。

注：http://study.qqcf.com/web/168/19166.htm

ANSI SQL语句分成以下六类（按使用频率排列）：

数据查询语言（DQL）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

数据操作语言（DML）:其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

事务处理语言（TPL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

数据控制语言（DCL）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

数据定义语言（DDL）:其语句可在数据库中创建新表（CREAT TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

自动回滚

会话异常结束或系统异常会导致自动回滚。

数据状态

提交或回滚前的数据状态

1）改变前的数据状态是可以恢复的；
2）执行DML 操作的用户可以通过SELECT语句查询之前的修正；
3）其他用户不能看到当前用户所做的改变，直到当前用户结束事务；
4）DML语句所涉及到的行被锁定，其他用户不能操作。

提交后的数据状态

1）数据的改变已经被保存到数据库中。
2）改变前的数据已经丢失。
3）所有用户可以看到结果。
4）锁被释放，其他用户可以操作涉及到的数据。
5）所有保存点被释放。

回滚后的数据状态

1）数据改变被取消。
2）修改前的数据状态被恢复。
3）锁被释放。

提交数据

DELETE FROM employees WHERE employee_id = 6;
INSERT INTO departments values (5,'Development',3);
COMMIT;

数据回滚

DELETE FROM copy_emp;
ROLLBACK;

语句级回滚

单独DML语句执行失败时，只有该语句被回滚。
Oracle 服务器自动创建一个隐式的保留点。
其他数据改变仍被保留。
用户应执行COMMIT或ROLLBACK语句以结束事务。

读一致性

读一致性为数据提供一个一致的视图。
一个用户的对数据的改变不会影响其他用户的改变。

对于相同的数据读一致性保证：
1）查询不等待修改。
2）修改不等待查询。

锁

Oracle数据库中的锁：

1）并行事务中避免资源竞争。
2）避免用户动作。
3）自动使用最低级别的限制。
4）在事务结束结束前存在。
5）提交或回滚后锁被释放。

两种类型

1）显式

2）隐式

两种模式

1）独占锁：屏蔽其他用户。
2）共享锁：允许其他用户操作。

高级别的数据并发性

1）DML：表共享，行独占
2）Queries：不需要加锁
3）DDL：保护对象定义

总结：

本节介绍DML数据控制语言和数据事务。

1）DML：insert、update、delete、merge（合并）；

2）事务控制：SAVEPOINT、COMMT、ROLLBACK。

posted @ 2020-07-14 11:20 workingdiary 阅读(159) 评论(0) 收藏举报

刷新页面返回顶部

workingdiary