Oracle数据操纵语言

数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。对比一期学习过的SQL Server操作，接下来一一介绍在Oracle中的操作。

 

²  简单查询

 

数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是：

 

语法结构：简单查询

SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名

语法解析：

1.       *表示表中的所有列。

2.       列名可以选择若干个表中的列名，各个列表中间用逗号分隔。

3.       表达式可以是列名、函数、常数等组成的表达式。

4.       WHERE子句是查询的条件。

5.       ORDER BY 要求在查询的结果中排序，默认是升序[D10] 。

 

图2  数据查询

        

         Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。

 

语法结构：根据结果集创建表

CREATE TABLE 表名 AS SELECT语句

 

代码演示：根据结果集创建表

SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS; TABLE CREATED

 

         使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。

        

代码演示：复制表结构

SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2; TABLE CREATED

 

²  数据插入

 

用INSERT命令完成对数据的插入。

 

语法结构：根据结果集创建表

INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)

语法解析：

1.       列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。

2.       值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

 

代码演示：向INFOS表和SCORES表中插入数据

SQL> INSERT INTO INFOS VALUES (  ①   2  's100102', '林冲', '男', 22, 2,   3  TO_DATE('2009-8-9 06:30:10','YYYY-MM-DD HH24:MI:SS '),  ②   4  '西安', '1001'   5  )   6  / 1 row inserted SQL> INSERT INTO INFOS VALUES ( 's100104','阮小二','男',26,3,SYSDATE,default,'1001');  ③  1 row inserted SQL>COMMIT;  ④

代码解析：

①  表名后面缺省了列名，默认是表Infos中的所有列名，values中的值要与表中列一一对应，包括顺序和数据类型的对应。在SQL*Plus中一条语句可以写在多行，那么从第二行开始，sqlplus会为每一行前面给出行号。

②  在Oracle中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用TO_DATE函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下：

1.         yyyy表示四位年份

2.         mm表示两位月份，比如3月表示为03

3.         dd表示两位日期

4.         hh24表示小时从0-23，hh12也表示小时从0-11。

5.         mi 表示分钟

6.         ss表示秒

③  在遇到存在默认值的列时，可以使用default值代替。

④  commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。

 

在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：

 

代码演示：INSERT向表中插入一个结果集

SQL> INSERT INTO INFOS2 SELECT * FROM INFOS; 5 rows inserted

 

在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。比如表INFOS2，该表的结构与INFO表一样，那么可以把INFO表中的所有记录一次性插入到INFOS2表中。

 

Oracle的简单查询和SQL Server一样都可以在查询列中使用常量，如图：

 

图3  Select中的常量

 

可以使用刚才的做法，把该结果集中的数据插入到表INFOS中。

 

代码演示：INSERT向表中插入一个常量结果集

SQL> INSERT INTO INFOS SELECT 's100106','卢俊义','男',23,5,                TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'),                '青龙寺','1001' FROM DUAL; 1 rows inserted SQL>COMMIT;

 

²  更新数据

 

Oracle在表中更新数据的语法是：

 

语法结构：UPDATE操作

UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件

 

代码演示：UPDATE操作

SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜' WHERE STUNAME='阮小二'; 1 rows updated SQL> commit;

 

²  删除数据

 

Oracle在表中删除数据的语法是：

 

语法结构：DELETE操作

DELETE FROM表名 WHERE 条件

 

代码演示：DELETE操作

SQL> DELETE FROM INFOS WHERE STUID='s100103'; 1 ROW DELETED SQL> COMMIT;

 

²  TRUNCATE

 

在数据库操作中，TRUNCATE[D11] 命令（是一个DDL命令）可以把表中的所有数据一次性全部删除，语法是：

 

语法结构：TRUNCATE

TRUNCATE TABLE 表名

 

TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：

1.       TRUNCATE是DDL命令，删除的数据不能恢复；DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。

2.       如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。

 

由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。

 

Oracle默认安装中，已经创建了一个SCOTT用户，默认密码是：tiger，该用户下有四张表分别是：雇员表（EMP），部门表（DEPT），工资登记表和奖金表，请参考本章后面的附表。接下来很多操作都是在该用户下完成的。