4.6 操作数据库

视频讲解：光盘\TM\lx\4\操作数据库．mp4

使用SQL语句操作数据库，除了查询操作之外，还包括完成插入、更新和删除等数据操作。后3种数据操作使用的SQL语言也称为数据操纵语言（Data Manipulation Language, DML），它们分别对应INSERT、DELETE和UPDATE三条语句。在Oracle 11G中，DML除了包括上面提到的3种语句之外，还包括TRUNCATE、CALL、LOCKTABLE和MERGE等语句。本节主要对INSERT、UPDATE、DELETE、TRUNCATE常用DML语句进行介绍。

4.6.1 插入数据（INSERT语句）

插入数据就是将数据记录添加到已经存在的数据表中，Oracle数据库通过INSERT语句来实现插入数据记录。该语句既可以实现向数据表中一次插入一条记录，也可以使用SELECT子句将查询结果集批量插入数据表。

使用INSERT语句有以下注意事项：

当为数字列增加数据时，可以直接提供数字值，或者用单引号引住。

当为字符列或日期列增加数据时，必须用单引号引住。

当增加数据时，数据必须要满足约束规则，并且必须为主键列和NOT NULL列提供数据。

当增加数据时，数据必须与列的个数和顺序保持一致。

1．单条插入数据

单条插入数据是INSERT语句最基本的用法，其用法格式如下：

        INSERT INTO table_name [(column_name1[, column_name2]…)]
        VALUES(express1[, express2]…)

table_name：表示要插入的表名。

column_name1和column_name2：指定表的完全或部分列名称。如果指定多个列，那么列之间用逗号分开。

express1和express2：表示要插入的值列表。

当使用INSERT语句插入数据时，既可以指定列列表，也可以不指定列列表。如果不指定列列表，那么在VALUES子句中必须为每个列提供数据，并且数据顺序必须与表列顺序完全一致。如果指定列列表，则只需要为相应列提供数据。下面同时以实例来说明增加单行数据的方法。

（1）使用列列表增加数据

在INSERT语句的几种使用方式中，最常用的形式是在INSERT INTO子句中指定添加数据的列，并在VALUES子句中为各个列提供一个值。

【例4.83】 在dept表中，使用INSERT语句添加一条记录，具体代码及运行结果如下：

        SQL> insert into dept(deptno, dname, loc)
          2  values(88, 'design', 'beijing');


        已创建1行。

在上面的示例中，INSERT INTO子句中指定添加数据的列，既可以是数据表的全部列，也可以是部分列。在指定部分列时，需要注意不许为空（NOT NULL）的列必须被指定出来，并且在VALUES子句中的对应赋值也不许为NULL，否则系统显示“无法将NULL插入”的错误信息提示。例如，修改上面的例子，在INSERT INTO子句不指定deptno列（通过desc dept命令可以看到该列是NOT NULL的），将出现如图4.75所示的错误提示。

图4.75 不许为空的错误提示

（2）不使用列列表增加数据

在向表的所有列添加数据时，也可以省略INSERT INTO子句后面的列表清单，使用这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据。用户可以使用DESC命令来查看表中定义列的顺序。

【例4.84】 在HR模式下，使用desc命令查看jobs表的结构和列的定义顺序，然后使用insert语句插入一条记录，具体代码及运行结果如下：

        SQL> connect hr/hr
        已连接。
        SQL> desc jobs;
         名称                              是否为空？           类型
         -----------------------------------------            --------       

         JOB_ID                            NOT NULL            VARCHAR2(10)
         JOB_TITLE                         NOT NULL            VARCHAR2(35)
         MIN_SALARY                                            NUMBER(6)
         MAX_SALARY                                            NUMBER(6)


        SQL> insert into jobs values('PRO', ’程序员’,5000,10000);


        已创建1行。

数据库工程师在设计数据表时，为了保证数据的完整性和唯一性，除了需要设置某些列不许为空的约束条件外，还会设置其他一些约束条件。例如，在jobs表中，为了保证表中每条记录的唯一性，为JOB_ID列定义了主键约束条件，这就要求该列的值不允许重复，对于上面的示例代码，再次尝试运行，将出现如图4.76所示的错误提示。

图4.76 主键重复的错误提示

上面这种情况的解决办法就是必须重新换一个与现有JOB_ID的值不重复的值。

（3）使用特定格式插入日期值

当增加日期数据时，默认情况下日期值必须匹配于日期格式和日期语言；否则在插入数据时会增加错误信息。如果希望使用习惯方式插入日期数据，那么必须使用TO_DATE函数进行转换。

【例4.85】 使用特定格式插入日期值，具体代码及运行结果如下：

        SQL> insert into emp (empno, ename, job, hiredate)
          2  values1356, ('MARY', 'CLERK',
          3  to_date('1983-10-20', 'YYYY-MM-DD');
        已创建1行。

（4）使用DEFAULT提供数据

从Oracle Database 9i开始，当增加数据时，可以使用DEFAULT提供数值。当指定DEFAULT时，如果列存在默认值，则会使用其默认值；如果列不存在默认值，则自动使用NULL。

【例4.86】 使用DEFAULT提供数据，具体代码如下：

        SQL> insert into dept values(60, 'MARKET', DEFAULT);
        SQL> select * from dept where deptno = 60;

运行结果如图4.77所示。

图4.77 使用DEFAULT提供数据

（5）使用替代变量插入数据

如果经常需要给某表插入数据，那么为了避免输入错误，可以将INSERT语句放到SQL脚本，并使用替代变量为表插入数据。如果经常需要为emp表插入数据，那么为了避免输入错误，可以使用SQL脚本插入数据。脚本loademp.sql及运行实例如下：

【例4.87】 编写脚本loademp.sql，使用此脚本插入数据，具体代码如下：

首先编写脚本文件loademp.sql，代码如下：

        accept no prompt ’请输入雇员号：'
        accept name prompt ’请输入雇员名：'
        accept title prompt ’请输入雇员岗位：'
        accept d_no prompt ’请输入部门号：'
        INSERT INTO emp (empno, ename, job, hiredate, deptno)
        values(&no, '&name', '&title', SYSDATE, &d_no);

脚本文件如图4.78所示。

图4.78 编写脚本loademp.sql

然后利用此脚本文件进行数据插入，代码如下：

        SQL> @c:\loademp

运行结果如图4.79所示。

图4.79 利用脚本文件插入数据

2．批量插入数据

INSERT语句还有一种强大的用法，就是可以一次向表中添加一组数据，也就是批量插入数据。用户可以使用SELECT语句替换掉原来的VALUES子句，这样由SELECT语句提供添加的数值。其语法格式如下：

        INSERT INTO table_name [(column_name1[, column_name2]…)] selectSubquery

table_name：表示要插入的表名称。

column_name1和column_name2：表示指定的列名。

selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。

【例4.88】 在HR模式下，创建一个与jobs表结构类似的表jobs_temp，然后将jobs表中最高工资额（max_salary）大于10000的记录插入到新表jobs_temp中，具体代码及运行结果如下（实例位置：光盘\TM\sl\4\15）。

        SQL>  create table jobs_temp(
          2  job_id varchar2(10)primary key,
          3  job_title varchar2(35)not null,
          4  min_salary number(6),
          5  max_salary number(6));


        表已创建。


        SQL>  insert into jobs_temp
          2   select*from jobs
          3   where jobs.max_salary>10000;


        已创建9行。

从上面的运行结果可以看出，使用INSERT语句和SELECT语句的组合可以一次性向指定的数据表中插入多条记录（这里是9条记录）。需要注意的是，在使用这种组合语句实现批量插入数据时，INSERT INTO子句指定的列名可以与SELECT子句指定的列名不同，但它们之间的数据类型必须是兼容的，即SELECT语句返回的数据必须满足INSERT INTO表中列的约束。

互动练习：把一个表的所有列插入到另一个表中。

4.6.2 更新数据（UPDATE语句）

如果表中的数据不正确或不符合需求，那么就需要对其进行修改。Oracle数据库通过UPDATE语句来实现修改现有的数据记录。

在更新数据时，更新的列数可以由用户自己指定，列与列之间用逗号（,）分隔；更新的条数可以通过WHERE子句来加以限制，使用WHERE子句时，系统只更新符合WHERE条件的记录信息。UPDATE语句的语法格式如下：

        UPDATE table_name
        SET {column_name1=express1[, column_name2=express2...]
         | (column_name1[, column_name2…])=(selectSubquery)}
        [WHERE condition]

table_name：表示要修改的表名。

column_name1和column_name2：表示指定要更新的列名。

selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。

condition：筛选条件表达式，只有符合筛选条件的记录才被更新。

使用UPDATE语句有以下注意事项：

当更新数字列时，可以直接提供数字值，或者用单引号引住。

当更新字符列或日期列时，必须用单引号引住。

当更新数据时，数据必须要满足约束规则。

当更新数据时，数据必须与列的数据类型匹配。

1．更新单列数据

当更新单列数据时，set子句后只需要提供一个列。

【例4.89】 在SCOTT模式下，把emp表中雇员名为SCOTT的记录的工资调整为2460，具体代码及运行结果如下：

        SQL> update emp
          2  set sal=2460
          3  where ename='SCOTT';

运行结果如图4.80所示。

图4.80 更新单列数据

2．更新多列数据

当使用update语句修改表行数据时，既可以修改一列，也可以修改多列。当修改多列时，列之间用逗号分开。

【例4.90】 在SCOTT模式下，把emp表中职务是销售员（SALESMAN）的记录的工资上调20%，具体代码及运行结果如下：

        SQL> update emp
          2  set sal=sal*1.2
          3  where job='SALESMAN';


        已更新4行。

上面的代码中，UPDATE语句更新记录的数量通过WHERE子句实现控制的，这里限制只更新销售员的工资，若取消WHERE子句的限制，则系统会将emp表中所有人员的工资都上调20%。

3．更新日期列数据

当更新日期列数据时，数据格式要与日期格式和日期语言匹配；否则会显示错误信息。如果希望使用习惯方式指定日期值，那么可以使用TO_DATE函数进行转换。

【例4.91】 在SCOTT模式下，把emp表中雇员编号为7788的入职时间进行调整，入职时间变为1984年1月1日，具体代码如下：

        SQL> update emp
          2  set hiredate=TO_DATE('1984/01/01', 'YYYY/MM/DD')
          3  where empno=7788;

运行结果如图4.81所示。

图4.81 更新日期列数据

4．使用DEFAULT选项更新数据

当更新数据时，可以使用DEFAULT选项提供数据。使用此方式时，如果列存在默认值，则会使用默认值更新数据；如果列不存在默认值，则使用NULL。

【例4.92】 在SCOTT模式下，使用DEFAULT选项更新emp表中雇员名为“SCOTT”的岗位信息，具体代码如下：

        SQL> select job from emp where ename = 'SCOTT';


        SQL> update emp
          2  set job=DEFAULT
          3  where ename='SCOTT';


        SQL> select job from emp where ename = 'SCOTT';

以上3条SQL语句的运行结果如图4.82所示。

图4.82 使用DEFAULT选项更新数据

5．使用子查询更新数据

另外，同INSERT语句一样，UPDATE语句也可以与SELECT语句组合使用来达到更新数据的目的。

【例4.93】 在SCOTT模式下，把emp表中工资小于2000的雇员工资调整为管理者的平均工资水平，具体代码及运行结果如下：

        SQL> update emp
          2  set sal=(select avg(sal)
          3  from emp where job='MANAGER')
          4  where sal<2000;


        已更新6行。

需要注意的是，在将UPDATE语句与SELECT语句组合使用时，必须保证SELECT语句返回单一的值，否则会出现错误提示，导致更新数据失败。

4.6.3 删除数据（DELETE语句和TRUNCATE语句）

Oracle系统提供了向数据库添加记录的功能，同时也提供了从数据库删除记录的功能。从数据库中删除记录可以使用DELETE语句和TRUNCATE语句，但这两种语句还是有很大区别的，下面分别进行讲解。

1.DELETE语句

DELETE语句用来删除数据库中的所有记录和指定范围的记录，若要删除指定范围的记录，同UPDATE语句一样，要通过WHERE子句进行限制，其语法格式如下：

        DELETE FROM table_name
        [WHERE condition]

table_name：表示要删除记录的表名。

condition：筛选条件表达式，是个可选项，当该筛选条件存在时，只有符合筛选条件的记录才被删除掉。

删除满足条件的数据：当使用DELETE语句删除数据时，通过指定WHERE子句可以删除满足条件的数据。

【例4.94】 在HR模式下，删除jobs表中职务编号（job_id）是“PRO”的记录，具体代码及运行结果如下：

        SQL> delete from jobs where job_id='PRO';


        已删除1行。

上面的代码中，DELETE语句删除记录的数量是通过WHERE子句实现控制的，这里限制只删除职务编号（job_id）是“PRO”的记录，若取消WHERE子句的限制，则系统会将jobs表中所有人员的记录都删除。

删除表的所有数据：当使用DELETE删除表的数据时，如果不指定WHERE子句，那么会删除表的所有数据。

【例4.95】 删除emp表中所有数据，具体代码及运行结果如下：

        SQL> delete from emp;


        已删除5行。

2.TRUNCATE语句

如果用户确定要删除表中的所有记录，那么除了可以使用DELETE语句之外，还可以使用TRUNCATE语句，而且Oracle本身也建议使用TRUNCATE语句。

使用TRUNCATE语句删除表中的所有记录要比DELETE语句快得多。这是因为使用TRUNCATE语句删除数据时，它不会产生回滚记录。当然，执行了TRUNCATE语句的操作也就无法使用ROLLBACK语句撤销。

【例4.96】 在HR模式下，使用TRUNCATE语句清除自定义表jobs_temp中的所有记录，具体代码及运行结果如下：

        SQL> truncate table jobs_temp;


        表被截断。


        SQL> select * from jobs_temp;


        未选定行

另外，需要补充说明的是，在TRUNCATE语句中还可以使用REUSE STORAGE关键字或DROP STORAGE关键字，前者表示删除记录后仍然保存记录所占用的空间，后者表示删除记录后立即回收记录占用的空间。默认情况下TRUNCATE语句使用DROP STORAGE关键字。