OCP课程10：SQL之操纵数据

stonebox

DML语句用于以下情况：

• 为表增加新行
• 修改表的记录
• 删除表的记录
  

事务是一系列DML操作的集合，用于保证事务的完整性

1、为表增加新行

语法：

                               
登录/注册后可看大图

• insert into values语句一次只能插入一行
• 可以在表后面列出需要插入的字段，也可以不列出，使用表定义的字段顺序来插入
• 插入的值如果是字符和日期，需要使用单引号括起来
• 如果字段可以为null，插入的时候表后面不列出字段，就需要显式使用null占位，插入的时候列出了非空的字段，那么可以隐式忽略可以为null的字段
  

例子：向dept表插入一条数据（与departments表一样）

SQL> create table dept as select * from departments;

Table created.

SQL> insert into dept(department_id,department_name,manager_id,location_id)

  2  values(70,'Public Relations',100,1700);

1 row created.

例子：只向表的某些字段插入值，其余为null

SQL> insert into dept(department_id,department_name)

  2  values (30,'Purchasing');

1 row created

例子：向表插入一行数据，其中包含null

SQL> insert into dept

  2  values(100,'Finance',null,null);

1 row created.

例子：向人员表emp插入一行数据，入职时间为当前时间（emp与employees一样）

SQL> create table emp as select * from employees;

Table created.

SQL> insert into emp(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)

  2  values(113,'Louis','Popp','LPOPP','555,124,4567',sysdate,'AC_ACCOUNT',6900,null,205,100);

1 row created.

例子：向人员表emp插入一行数据，入职时间为指定的时间，使用了转换函数to_date，避免语言环境的原因导致数据插不进去

SQL> insert into emp

  2  values(114,'Den','Raphealy','DRAPHEAL','515.127.4561',to_date('FEB 3,1999','MON DD,YYYY'),'AC_ACCOUNT',11000,null,100,30);

1 row created.

使用&替换变量创建插入脚本

例子：提示输入插入数据

SQL> insert into dept(department_id,department_name,location_id)

  2  values(&department_id,'&department_name',&location);

Enter value for department_id: 40

Enter value for department_name: Human Resouces

Enter value for location: 2500

old   2: values(&department_id,'&department_name',&location)

new   2: values(40,'Human Resouces',2500)

1 row created.

使用子查询的结果一次插入多行数据，不要再使用values关键字，插入的表的字段数量要与子查询的字段数量一致

例子：使用子查询一次插入多行数据

SQL> create table sales_reps(id,name,salary,commission_pct) as

  2  select employee_id,last_name,salary,commission_pct from employees where 1=0;

Table created.

SQL> insert into sales_reps(id,name,salary,commission_pct)

  2  select employee_id,last_name,salary,commission_pct

  3  from employees where job_id like '%REP%';

33 rows created.

例子：在insert语句中使用子查询的字段名字

SQL> insert into (select employee_id,last_name,email,hire_date,job_id,salary,department_id from emp where department_id=50)

  2  values(99999,'Taylor','DTAYLOR',to_date('07-JUN-99','DD-MON-RR'),'ST_CLERK',50000,50);

1 row created.

2、修改表的数据

语法 ：

                               
登录/注册后可看大图

特别注意：不加where条件表示更新表里面所有的记录

例子：将人员表里面人员编号为113的人员的部门编号修改为70

SQL> update emp set department_id=70 where employee_id=113;

1 row updated.

例子：将人员表里面所有人员的部门编号修改为110（没有加where条件）

SQL> update emp set department_id=110;

107 rows updated.

使用子查询的结果去更新记录

例子：将人员表里面人员编号为114的人员的职位和薪水修改为与人员编号为205的一样

SQL> update emp set

  2  job_id=(select job_id from emp where employee_id=205),

  3  salary=(select salary from emp where employee_id=205)

  4  where employee_id=114;

1 row updated.

还可以使用下面这种，少查询一次，提高性能

SQL> update emp set

  2  (job_id,salary)=(select job_id,salary from emp where employee_id=205)

  3  where employee_id=114;

1 row updated.

使用其他表的查询结果去更新记录

例子：将emp表中职位与employees表员工编号为200的职位一样的人员的部门编号修改为与employees表员工编号为100的部门编号一样

SQL> update emp set

  2  department_id=(select department_id from employees where employee_id=100)

  3  where job_id=(select job_id from employees where employee_id=200);

1 row updated.

3、删除表的记录

语法：

                               
登录/注册后可看大图

特别注意：不加where条件表示删除表里面所有的记录

例子：删除dept表中部门名字为Finance的记录

SQL> delete from dept where department_name='Finance';

2 rows deleted.

例子：删除emp表中所有记录

SQL> delete from emp;

107 rows deleted.

使用其他表的查询结果去删除记录

例子：在emp表中删除部门名称包含Public的部门的所有人员信息

SQL> delete from emp

  2  where department_id=(select department_id from dept where department_name like '%Public%');

where department_id=(select department_id from dept where department_name like '%Public%')

                     *

ERROR at line 2:

ORA-01427: single-row subquery returns more than one row

提示子查询返回有多条记录，不能使用等号进行比较

SQL> select department_id from dept where department_name like '%Public%';

DEPARTMENT_ID

-------------

           70

           70

改用in就可以了

SQL> delete from emp

  2  where department_id in (select department_id from dept where department_name like '%Public%');

1 row deleted.

4、truncate语句

• 删除表中所有的记录，保留表结构
• 是DDL语句，不能回滚
• 快速删除，但不会释放空间
• 表中有参照完整性约束不能执行truncate
  

语法：

                               
登录/注册后可看大图

例子：删除emp表中所有的记录

SQL> truncate table emp;

Table truncated.

5、数据库事务

事务一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务包含：

• 多个DML语句对数据进行的一致性更改
• 一个DDL语句
• 一个DCL语句
  

                               
登录/注册后可看大图

事务开始于第一条DML语句的执行。

事务结束于下面的事件：

• 发出commit或者rollback语句
• 执行DDL或者DCL语句
• 正常退出SQL Developer或者SQL PLUS（使用exit退出）
• 系统崩溃或者异常退出SQL Developer或者SQL PLUS（关闭窗口），事务自动回滚
  

使用commit和rollback语句的作用：

• 确保数据的读一致性，提交了其他会话才能看到
• 在数据永久改变前预览数据的的改变
• 分组逻辑相关的操作
  

事务控制：

                               
登录/注册后可看大图

显示事务控制：

                               
登录/注册后可看大图

例子：使用rollback to savepoint回滚到某一个保存点

提交前面的事务

SQL> commit;

Commit complete.

开始新的事务前，查询dept表的记录数

SQL> select count(*) from dept;

  COUNT(*)

----------

        31

删除所有记录

SQL> delete from dept;

31 rows deleted.

创建一个保存点a

SQL> savepoint a;

Savepoint created.

插入一条记录

SQL> insert into dept values(10,'Administration',200,1700);

1 row created.

更新这条记录

SQL> update dept set location_id=1800 where department_id=10;

1 row updated.

创建一个保存点b

SQL> savepoint b;

Savepoint created.

查询当前表的记录情况

SQL> select * from dept;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           10 Administration                        200        1800

再插入一条记录

SQL> insert into dept values(20,'Marketing',201,1800);

1 row created.

查看插入后表的数据

SQL> select * from dept;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           10 Administration                        200        1800

           20 Marketing                             201        1800

回到保存点b

SQL> rollback to savepoint b;

Rollback complete.

查看表，就是插入第二条数据之前的情况

SQL> select * from dept;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           10 Administration                        200        1800

回到保存点a

SQL> rollback to savepoint a;

Rollback complete.

查看表，就是删除表后的情况

SQL> select * from dept;

no rows selected

回滚所有操作

SQL> rollback;

Rollback complete.

表回到删除表之前的情况

SQL> select count(*) from dept;

  COUNT(*)

----------

        31

将事务（transaction）回滚（rollback）到某个保存点（savepoint）的过程如下：

• Oracle 回滚指定保存点之后的语句
• Oracle 保留指定的保存点，但其后创建的保存点都将被清除
• Oracle 释放此保存点后获得的表级锁（table lock）与行级锁（row lock），但之前的数据锁依然保留
  

Oracle数据库中没有数据字典记录保存点信息，一般情况也很少使用保存点。

隐式事务控制：

                               
登录/注册后可看大图

使用exit退出SQL*PLUS自动提交是由exitcommit这个参数控制的

SQL> show exitcommit

exitcommit ON

例子：使用DDL隐式提交

在会话窗口1执行删除

SQL> select count(*) from dept;

  COUNT(*)

----------

        31

SQL> delete from dept;

31 rows deleted.

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

        31

在会话窗口1执行DDL

SQL> create table loc as select * from locations;

Table created.

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

例子：使用DCL隐式提交

在会话窗口1执行插入

SQL> insert into dept select * from departments;

27 rows created.

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

在会话窗口1执行DCL

SQL> grant select on dept to hr;

Grant succeeded.

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

        27

例子：exit退出SQL*PLUS隐式提交

在会话窗口1执行删除

SQL> delete from dept;

27 rows deleted.

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

        27

在会话窗口1使用exit退出SQL*PLUS

SQL> exit

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

例子：关闭终端窗口隐式回滚

在会话窗口1执行插入

SQL> insert into dept select * from departments;

27 rows created.

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

关闭会话窗口1

在会话窗口2进行查询

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

在commit或者rollback之前数据的状态：

• 可以恢复到数据先前的状态
• 当前用户可以查看DML的结果
• 其他用户看不到DML的结果
• 影响的行被锁住，其他用户不能修改被锁住的行
  

例子：行锁

在会话窗口1执行更新

SQL> insert into dept select * from departments;

27 rows created.

SQL> commit;

SQL> update dept set manager_id=200 where department_id=100;

1 row updated.

在会话窗口2更新同一条记录，挂起

SQL> update dept set manager_id=20 where department_id=100;

在会话窗口1提交

SQL> commit;

Commit complete.

会话窗口2完成更新

SQL> update dept set manager_id=20 where department_id=100;

1 row updated.

在commit之后数据的状态：

• 数据改变永久生效
• 数据先前状态永久丢失
• 所有用户都可以看到结果了
• 行锁释放，其他用户可以对该行进行操作
• 所有的保存点被清除
  

在rollback之后数据的状态：

• 放弃对数据的修改
• 恢复到数据先前的状态
• 锁定的行被释放
  

语句级回滚

• 如果一个事务中某一个DML语句执行失败，则该语句将回滚，该语句之前的操作将保留，必须显式使用commit或者rollback结束该事务
• 即使DDL语句执行失败，也会commit
  

读一致性保证了数据在所有的时候是一个一致的状态，就是说，我们只要没有提交，其他会话看到的始终是修改以前的结果

读一致性确保数据：

• 写不影响读
• 读不影响写
• 写需等待写
  

执行DML操作时，自动进行读一致性操作，oracle将要修改的数据复制到undo segment，commit前，其余session看到的数据是undo segment中的数据，只有当前session看到的数据是已经修改过的数据，commit后，所有的session都可以看到修改后的数据，被以前数据占用的回滚段将会释放重用，如执行回滚，undo segment中的数据将会回写。

6、总结

                               
登录/注册后可看大图

7、相关习题

（1）Given below are the SQL statements executed in a user session: CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10));INSERT INTO product VALUES(1, 'pen');INSERT INTO product VALUES (2,'pencil');SAVEPOINT a;UPDATE product SET pcode = 10 WHERE pcode = 1;SAVEPOINT b;DELETE FROM product WHERE pcode = 2? COMMIT;DELETE FROM product WHERE pcode=10;ROLLBACK TO SAVEPOINT a;Which statement describes the consequences?

A.No SQL statement would be rolled back.

B.Both the DELETE statements would be rolled back.

C.Only the second DELETE statement would be rolled back.

D.Both the DELETE statements and the UPDATE statement would be rolled back.

答案：A

（2）View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL  have the default values 'direct' and 0 respectively. Which two INSERT statements are valid? (Choose two.)

                               
登录/注册后可看大图

A.INSERT INTO orders VALUES (1, '09-mar-2007', 'online','',1000);

B.INSERT  INTO  orders (order_id,order_date,order_mode, customer_id,order_total) VALUES(1,TO_DATE(NULL), 'online', 101, NULL);

C.INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,'09-mar-2007', 101);

D.INSERT INTO orders VALUES (1,'09-mar-2007', DEFAULT, 101, DEFAULT);

E.INSERT  INTO  orders  (order_id,order_date,order_mode,order_total)  VALUES (1,'10-mar-2007','online',1000);

答案：CD

（3）View the Exhibit and examine the structure of the ORDERS table. NEW_ORDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table. Evaluate the following INSERTstatement: INSERT  INTO new_orders  (ord_id,  ord_date,  cust_id,  ord_total)  VALUES(SELECT order_id,order_date,customer_id,order_total FROM orders WHERE order_date > '31-dec-1999');Why would the INSERT statement fail?

A.because column names in NEW_ORDERS and ORDERS tables do not match；

B.because the VALUES clause cannot be used in an INSERT with a subque；

C.because the WHERE clause cannot be used in a subquery embedded in an INSERT statement ；

D.because the total number of columns in the NEW_ORDERS table does not match the total number of columns in the ORDERS table；

答案：B

（4）View the Exhibit and examine the structure of the ORDERS table. Which UPDATE statement is valid?

A.UPDATE orders SET order_date = '12-mar2007', order_total IS NULL WHERE order_id = 2455;

B.UPDATE orders SET order_date = '12-mar2007', order_total = NULL WHERE order_id = 2455;

C.UPDATE orders SET order_date = '12-mar-2007' AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;

D.UPDATE  orders SET  order_date  =  TO_DATE('12-mar-2007','dd-mon-yyyy'), SET order_total  = TO_NUMBER(NULL) WHERE order_id = 2455;

答案：B

（5）Which three statements indicate the end of a transaction? (Choose three.)
A.after a COMMIT is issued
B.after a ROLLBACK is issued
C.after a SAVEPOINT is issued
D.after a SELECT statement is issued
E.after a CREATE statement is issued

答案：ABE

（6）View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables. You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department. Which SQL statement would you execute to accomplish the task?

                               
登录/注册后可看大图

A.UPDATE dept d SET city = ANY (SELECT city FROM locations l)；
B.UPDATE dept d SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id；
C.UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id)；
D.UPDATE dept d SET city = ALL (SELECT city FROM locations l WHERE d.location_id = l.location_id)；

答案：C

（7）Evaluate  the  following DELETE statement:  DELETE FROM  orders?  There  are  no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?

A.It removes all the rows in the table and allows ROLLBACK.
B.It would not remove the rows if the table has a primary key.
C.It removes all the rows as well as the structure of the table.
D.It removes all the rows in the table and does not allow ROLLBACK.

答案：A

（8）View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. Which DELETE statement would execute successfully?

A.DELETE order_id FROM orders WHERE order_total < 1000;
B.DELETE orders WHERE order_total < 1000;
C.DELETE FROM orders WHERE (SELECT order_id FROM order_items);
D.DELETE orders o, order_items i WHERE o.order_id = i.order_id;

答案：B

（9）View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Evaluate the following  UPDATE statement:  UPDATE (SELECT order_date,  order_total,  customer_id  FROM orders) SET order_date = '22-mar-2007' WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600) ； Which statement is true regarding the execution of the above UPDATE statement？

A.It would not execute because two tables cannot be used in a single UPDATE statement.
B.It would execute and restrict modifications to only the columns specified in the SELECT statement.
C.It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
D.It would not execute because the SELECT statement cannot be used in place of the table name.

答案：B

（10）View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE. Evaluate the following statement: UPDATE orders SET order_date=DEFAULT WHERE order_id IN (SELECT order_id FROM order_items WHERE qty IS NULL) ；What would be the outcome of the above statement？

                               
登录/注册后可看大图

A.The UPDATE statement would not work because the main query and the subquery use different tables.
B.The UPDATE statement would not work because the DEFAULT value can be used only in INSERT statements.
C.The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULL and QTY is NULL.
D.The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL.

答案：D

（11）View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600？

A.INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id  FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000)；
B.INSERT  INTO  orders (order_id,order_date,order_mode,  (SELECT  customer_id  FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000)；
C.INSERT  INTO orders (order_id,order_date,order_mode,  (SELECT  customer_id  FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000) ；
D.INSERT  INTO(SELECT  o.order_id,  o.order_date,o.order_mode,c.customer_id,  o.order_total  FROM orders o,  customers c WHERE o.customer_id  =  c.customer_id  AND  c.cust_last_name='Roberts' ANDc.credit_limit=600  )  VALUES  (1,'10-mar-2007',  'direct',(SELECT  customer_id  FROM  customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000) ；

答案：A

（12）View the Exhibit and examine the data in EMPLOYEES and DEPARTMENTS tables. In the

EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the

FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY.

Evaluate the following UPDATE statement:

UPDATE employees a

SET department_id =

(SELECT department_id

FROM departments

WHERE location_id = '2100'),

(salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)

FROM employees b

WHERE a.department_id = b.department_id)

WHERE first_name||' '||last_name = 'Amit Banda';

What would be the outcome of the above statement? What would be the outcome of the

above statement?

                               
登录/注册后可看大图

A. It would execute successfully and update the relevant data.

B. It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS  table.

C. It would not execute successfully because the condition specified with the concatenation operator is not  valid.

D. It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be  used in an UPDATE statement.

答案：A

（13）View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables. You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table. Which DELETE statements are valid？(Choose all that apply.)

A.DELETE  FROM order_items WHERE order_id  IN  (SELECT  order_id  FROM orders WHERE order_status in (0,1));
B.DELETE  * FROM order_items WHERE order_id  IN  (SELECT  order_id  FROM orders  WHERE order_status IN (0,1)) ;
C.DELETE  FROM order_items i  WHERE order_id  =  (SELECT  order_id  FROM orders o WHERE i.order_id = o.order_id AND order_status IN (0,1))  ;
D.DELETE  FROM (SELECT  * FROM  order_items i,orders o WHERE i.order_id  =  o.order_id  AND order_status IN (0,1)) ;

答案：ACD