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

p45426bty1

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