Oracle培训（三十）——Oracle 11g 第八章知识点总结—

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)在插入操作中使用默认值:

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.总结

Oracle培训（三十）——Oracle 11g 第八章知识点总结——处理数据