- Oracle PL/SQL宝典
- 赵越编著
- 83字
- 2020-08-28 06:11:38
第4章 表中数据的基本操作
前面介绍了数据表的创建以及数据表的相关操作。本章将开始介绍通过SELECT、INSERT、UPDATE和DELETE命令查询、添加、更新和删除表中的数据。另外，还将简单介绍数据库中数据的复制以及导入、导出操作。
4.1 查询数据表
查询数据表可以说是对数据表使用最频繁的一种操作了，查询数据表只需要记住SELECT语句就可以对数据表进行简单地查询。本节讲解使用该语句查询数据表，以及在SQL Developer工具中查询数据表。
4.1.1 查询表中的数据
第3章已经介绍了如何在数据库中创建表，那么如何使用语句查询表中的数据呢？不论是在Oracle数据库还是在SQL Server数据库中，查询数据表中的数据都是使用标准的SQL语句中的SELECT语句来完成的。这里，只介绍简单的查询操作，对于比较复杂的查询可以参考本书后面的章节。查询表中的数据的一般语法如下：
SELECT 列名1, 列名2, … FROM 表名
在SELECT关键词后面放置的是要在查询结果中显示的列名，这些列名来源于FROM后面表中的列名。如果想查询表中的全部数据，可以在SELECT后面使用“＊”代替列名。
在使用SELECT语句查询时，一定要在查询的多个列名之间，用英文状态下的逗号隔开。
【实例4-1】查询BOOKINFO表的图书名称和作者的数据。
查询语句如下：
01 SELECT BOOKNAME, AUTHOR FROM BOOKINFO
02 /
【代码解析】
◆ BOOKNAME, AUTHOR是准备查询的列名，在列名之间要用逗号隔开。
【执行效果】
在SQL＊Plus中执行以上脚本，效果如图4.1所示。
图4.1 【实例4-1】查询结果
在此可以看出，查询时只显示了在SELECT语句后面指定的列名，如果只需要显示表中的部分列时可以使用此方法；如果需要查询表中的全部数据，可以参考【实例4-2】。
【实例4-2】查询BOOKINFO表中的全部数据。
查询语句如下所示：
SELECT ＊ FROM BOOKINFO;
【代码解析】
◆ “＊”代表查询表中的全部字段。
4.1.2 使用SQL Developer工具查询表中的数据
查询表中所有列的效率会比查询指定的列效率低，所以在实际应用中应尽量少用查询所有数据的方法。
SQL Developer是目前经常使用的一款Oracle操作工具，大多数企业都使用该工具操作Oracle数据库。它比Oracle自带的企业管理器使用起来更加方便，如图4.2所示是SQL Developer主界面。
图4.2 SQL Developer主界面
通过SQL Developer工具可以很方便地操作Oracle数据库，特别对数据库对象和数据的操作是最方便的。下面就以查询BOOKINFO表为例，查看该表中的数据。
【实例4-3】查询BOOKINFO表的全部数据。
在如图4.2所示界面的左侧文件夹中找到“表”文件夹，该文件夹就是当前登录用户所创建的表。在其中找到已经创建的BOOKINFO表，并右击该表，出现如图4.3所示的右键菜单。
图4.3 表操作的右键菜单
在弹出的右键菜单中，选择【打开】选项，在右侧的窗口中显示出BOOKINFO表中的全部数据，如图4.4所示。
图4.4 查询BOOKlNFO表的全部记录
至此，就完成了查询BOOKINFO表的全部记录的操作，实际上就等价于“SELECT ＊ FROM BOOKINFO”。
如果想在查询结果中只显示几个列，则可以在查询结果上面的语句部分自行编写SQL语句。
4.2 添加数据
在PL/SQL语言中，使用INSERT命令可以将新的数据行追加到表中。使用该命令可以向表中插入整行数据，也可以对部分列插入数据。在Oracle数据库中使用它，一次只能向表中添加一行记录。
4.2.1 添加操作的基本语法
在PL/SQL中，INSERT命令的基本语法如下：
INSERT
[INTO]
table_or_view_name
[ ( column_list ) ]
VALUES ({NULL | expression } [ ,...n ])
【语法说明】
◆ INTO：可选的关键词，可以将它用在INSERT和目标表之间。
◆ table_or_view_name：要添加数据的表或视图的名称，若为视图，则必须是可更新的视图。
◆ column_list：要在其中插入数据的一列或多列的列表。必须用括号将column_list括起来，并且用逗号进行分隔，如果表名table_name后面没有接指定列column_list，则认为是整行插入。
◆ VALUES：要插入数据值的列表。对于column_list（如果已指定）或表中的每个列，都必须有一个数据值，必须用圆括号将值列表括起来。
如果VALUES列表中的各值与表中各列的顺序不相同，或者未包含表中各列的值，则必须使用column_list显式指定存储每个传入值的列。
使用INSERT命令向表中插入行时，应遵循下面的规则。
◆ 如果将一个空字符串（' '）加载到varchar或text数据类型的列，则默认操作是加载一个零长度的字符串。
◆ 插入的数据类型应与被加入字段的数据类型相同，且必须满足该列的约束（如空值约束、字段长度等）。如果INSERT语句违反约束或规则，或者包含与列的数据类型不兼容的值，则该语句将执行失败，并且数据库引擎将显示错误消息。
◆ 在VALUES中，列出的数据位置必须与字段的排列位置相对应。也就是说，第1个值插入第1列，第2个值插入第2列，依此类推。
对表进行INSERT操作时，需要目标表上的INSERT权限。在默认情况下，INSERT权限被授予sysadmin固定服务器角色成员、db_owner和db_datawriter固定数据库角色成员以及表的所有者。sysadmin、db_owner和db_securityadmin角色成员及表的所有者可以将权限传递给其他用户。
4.2.2 直接向表中添加数据
下面将结合具体的实例讲解使用INSERT命令向数据表中添加一行数据。为了便于理解，新创建一个实例表BOOKS，其表结构及约束如表4.1所示。
表4.1 BOOKS表的结构及约束
BOOKS表的创建代码如下：
01 CREATE TABLE BOOKS
02 (
03 B_ID INT NOT NULL UNIQUE,
04 B_Name VARCHAR(40) NOT NULL ,
05 B_Publish VARCHAR(16),
06 B_Price NUMBER DEFAULT 0.00,
07 B_Pubdate DATE DEFAULT SYSDATE
08 )
执行效果如图4.5所示。
图4.5 创建BOOKS表
【实例4-4】使用INSERT命令向BOOKS表中插入记录。
本实例将使用INSERT命令向BOOKS表中添加两行记录，实例代码如下：
01 INSERT INTO BOOKS ..插入一行记录
02 (B_ID,B_Publish,B_Name,B_Price,B_Pubdate)
03 VALUES
04 (1001,'大众出版社','计算机文化基础',34.6, to_date('2010.1.1','yyyy.mm.dd'));
05
06 INSERT INTO BOOKS ..插入一行记录
07 VALUES
08 (1002,'计算机硬件基础','科学出版社',44.5, to_date('2011.1.1','yyyy.mm.dd'));
【代码解析】
◆ B_Pubdate在数据库中是日期型，向该字段中插入日期时需要对字符串类型进行转换，字符串类型转换成日期型使用的函数是to_date(字符串,'日期的格式')。
【执行效果】
在SQL＊Plus中执行以上脚本，效果如图4.6所示。
图4.6 向BOOKS表中插入记录
此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
查询结果如图4.7所示。
图4.7 查询结果
可见，实例代码中的两种方式均成功地向表中插入了记录。如果采用第1种方式，即在表名后指明列名，则VALUES后的值必须与列名的顺序相对应；而如果在表名后不指明列名，即第2种方式，则默认是按照表中的所有列及其在表中的排列顺序插入数据的。
在如图4.7所示的窗口中，为了能够整齐地显示查询结果，可以先设置显示的格式，如：COLUMN B_NAME FORMAT A20。
4.2.3 数据中NULL值的处理
在数据库表中，有些字段可以为空（NULL），因此，向表中插入数据时，如果要插入为NULL的字段值，则可以在INSERT INTO关键词后不列出该列的列名。也可以列出该列的列名，但在VALUES关键词后将该列的值设置为NULL。比如下面的实例。
【实例4-5】使用INSERT命令插入带有空值的记录。
本实例将使用INSERT命令向BOOKS表中添加2行记录，其中包含有空值。实例代码如下：
01 INSERT INTO BOOKS ..插入一行记录
02 VALUES
03 (1003,'数据库基础','北京大学出版社',NULL,NULL);
04
05 INSERT INTO BOOKS ..插入一行记录
06 VALUES
07 (1004,'计算机硬件基础',NULL,54.5, to_date('2011.1.1','yyyy.mm.dd'));
【代码解析】
相比交互式的SQL语句，存储过程主要具有如下优点。
◆ 存储过程允许组件式编程。它被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，从而极大地提高了程序的可移植性。
◆ 第3行和第7行分别使用了NULL来表示数据，它们表示数据为空，即不存入任何数据。
【执行效果】
执行效果如图4.8所示。
图4.8 插入空值记录
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.9所示。
如果插入的某字段值为空，则该字段对应的VALUE值为“NULL”，允许什么都不写。
图4.9 查询结果
可见，两种方式均成功地实现了带有NULL值记录的插入操作。当然，能够向表中添加带有NULL值的记录，其前提是表中该字段允许为空值，即创建表时，没有NOT NULL约束；否则，会引发INSERT命令执行失败。比如下面的操作代码：
01 INSERT INTO BOOKS ..插入一行记录
02 VALUES
03 (1005,NULL,'科学出版社',24.5, to_date('2011.1.1','yyyy.mm.dd'));
执行该代码，系统会提示执行失败，并给出错误提示信息，如图4.10所示。
图4.10 为非空列插入空值错误提示
这是因为在创建BOOKS表时，B_Name字段定义为NOT NULL，而在INSERT命令中， B_Name字段对应的值为NULL，因此就出现了错误。这时候，如果暂时不清楚该字段的具体值，则可以用空格或其他字符代替。这里用“＊”代替，代码如下：
01 INSERT INTO BOOKS ..插入一行记录
02 VALUES
03 (1005,'＊＊＊＊','科学出版社',24.5, to_date('2011.1.1','yyyy.mm.dd'));
此时，代码就能成功执行。运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.11所示。
图4.11 查询结果
4.2.4 插入值是默认值的处理
与NULL值相似，创建表时，可以通过DEFAULT关键词指定列的默认值。向带有默认值的表插入记录时，如果未指定该列的值，系统会自动采用定义的默认值作为该列的值。当然，也可以在INSERT命令中，显式地指定该列采用默认值。比如下面的实例。
【实例4-6】使用INSERT命令插入采用默认值的记录。
本实例将使用INSERT命令向BOOKS表中添加2行记录，其中有的字段采用默认值。实例代码如下：
01 INSERT INTO BOOKS ..插入一行记录
02 (B_ID,B_Name,B_Publish,B_Pubdate)
03 VALUES
04 (1006,'遥感信息处理','科学出版社', to_date('2011.1.1','yyyy.mm.dd'));
05
06 INSERT INTO BOOKS ..插入一行记录
07 VALUES
08 (1007,'图形图像处理',NULL,29.5, DEFAULT);
【执行效果】
在SQL＊Plus中的执行效果如图4.12所示。
图4.12 插入带有默认值的记录
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.13所示。
图4.13 查询结果
可见，这两种方式均成功地实现了默认值的添加。
当使用INSERT命令向一个表中插入一条新记录，但其中有一个或几个字段没有提供数据时，如果要使INSERT命令成功执行，则需要满足下面几个条件之一：
（1）该列具有默认值，则使用列的默认值。
（2）具有timestamp数据类型，则使用当前的时间戳值。
（3）可为空值，则使用空值。
（4）是计算列，则使用计算值。
4.2.5 插入值是唯一值的处理
在Oracle数据库中创建表时，允许为列设置UNIQUE属性。这个属性意味着，在当前表中，该列中的值必须是完全唯一的，并且只能出现一次。有了这种限制，向表中插入数据或修改数据时，可能会导致出问题。
比如在BOOKS表中，B_ID字段就设置有UNIQUE属性，即唯一性约束。使用INSERT命令向BOOKS表中插入数据时，如果B_ID字段的值在该表中已经存在，则插入操作就会失败。比如下面的实例。
【实例4-7】插入带有唯一值（UNIQUE）的记录。
采用下面的INSERT命令向BOOKS表中添加一行记录：
01 INSERT INTO BOOKS ..插入一行记录
02 (B_ID,B_Name,B_Publish,B_Price,B_Pubdate)
03 VALUES
04 (1003,'计算机图形学','科学出版社',33, to_date('2011.1.1','yyyy.mm.dd'));
【执行效果】
执行该代码，系统会提示执行失败，并给出错误提示信息，如图4.14所示。
图4.14 违反唯一约束错误提示
这是因为在BOOKS表中，B_ID字段具有UNIQUE约束，而要插入的记录，值“1003”已经存在，因此插入操作失败。
一个规范化的表应有一个唯一字段或关键字段，这个字段在表之间连接数据时是有用的，在使用索引时通常可以提高查询速度。
4.2.6 使用lNSERT...SELECT插入数据
前面介绍了使用INSERT命令向表中插入行，而有时用户需要根据已有表和视图的记录，将其中特定的数据添加到目标表中，这时就可以使用INSERT...SELECT语句来实现。该语句实际上包含两部分：INSERT（插入语句）和SELECT（查询语句），其语法可表示如下：
INSERT
[INTO]
table_or_view_name
[ ( column_list ) ]
SELECT column_list FROM data_source
这样，通过INSERT...SELECT语句可以一次性向目标表中插入大量数据。另外，使用该语句时，需要注意下面几点。
◆ SELECT语句不能从正在被插入的表和视图中选择数据。
◆ 在INSERT INTO语句中，列的数目必须等于从SELECT语句返回列的数目。
◆ 在INSERT INTO语句中，列的数据类型必须与从SELECT语句返回列的数据类型相同。
下面通过一个实例讲解INSERT...SELECT语句的用法。
【实例4-8】使用INSERT...SELECT语句向表中添加数据。
本实例将使用INSERT...SELECT语句，将BOOKINFO表中的图书记录添加到BOOKS表中。实例代码如下：
01 INSERT INTO BOOKS
02 (B_ID,B_Name,B_Publish,B_Price,B_Pubdate)
03 SELECT bookid,bookname,publish,price,pubdate
04 FROM BOOKINFO;
【执行效果】
在SQL＊Plus中的执行效果如图4.15所示。
图4.15 使用lNSERT...SELECT语句插入数据
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.16所示。
图4.16 BOOKS表查询结果
可见，通过INSERT...SELECT语句执行一次操作就向目标表中添加了多行记录。
INSERT...SELECT语句通常用于创建查找表，以提高检索性能。查找表可以包含分布在多个数据库的多个表中的数据。因为多个表连接处理起来比简单查询要慢，对一个查找表执行SELECT查询，则明显快于执行又长又复杂的连接查询。
INSERT...SELECT语句的另一个用途是备份表，备份将要删除、截断数据或重新装入数据的表。
4.2.7 使用SQL Developer工具添加数据
使用SQL Developer工具添加数据的操作可以像在Excel表格中输入数据一样简单，下面就以向BOOKS表中添加数据为例，演示SQL Developer工具的使用。
【实例4-9】使用SQL Developer工具向BOOKS表中添加数据。
在如图4.3所示的界面中，右击选择BOOKS表，在弹出的右键菜单中选择【打开】选项，如图4.17所示。
图4.17 编辑BOOKS表界面
在此界面中的最后一行后面添加数据，输入要添加的数据后单击工具栏上的对号，即可保存结果，提交后记录才真正添加到数据库中。
4.3 修改数据
在PL/SQL语言中，可以使用UPDATE命令更改表或视图中的现有数据。该语句既可以一次修改一条记录，也可以一次修改多条记录，甚至可以一次修改表中的全部数据行。
4.3.1 修改操作的基本语法
在PL/SQL中，UPDATE命令的基本语法如下：
UPDATE ＜table_or_view_name＞
SET column_name = {expression | DEFAULT | NULL} [ ,...n ]
WHERE ＜search_condition＞
【语法说明】
◆ table_or_view_name为要更新行的表或视图的名称，且引用的视图必须可更新，并且只在该视图的FROM子句中引用一个基表。如果该表不在当前服务器或数据库中，或不是当前用户所有，这个名称可用连接服务器、数据库和所有者名称来限定。
◆ column_name为要更改数据的列，它必须已存在于table_or_view_name中。
◆ search_condition为要更新的行指定需满足的条件。
如果对行的更新违反了某个约束或规则，或者违反了对列的NULL设置，或者新值是不兼容的数据类型，则取消该语句，返回错误，并且不更新任何记录。
当UPDATE语句在表达式求值过程中遇到算术错误（溢出、被零除）时，则不进行更新。批处理的剩余部分不再执行，并且返回错误消息。
一定不要忽略WHERE子句，如果没有指明WHERE子句，则数据库表中所有行的记录都将被更新。
4.3.2 使用UPDATE语句更新数据行
使用UPDATE语句更新表中的数据时，可以使用WHERE子句指定要修改的行，使用SET子句给出新的数据。下面给出一个实例，说明如何使用该语句更新表中的一行数据。
【实例4-10】使用UPDATE使用更新表中的一行数据。
本实例使用UPDATE语句，将BOOKS表中图书编号为“1005”的图书名称（B_Name）更新为“随机信号处理”。实例代码如下：
01 UPDATE BOOKS
02 SET B_Name='随机信号处理'
03 WHERE B_ID=1005;
【执行效果】
在SQL＊Plus中的执行效果如图4.18所示。
图4.18 更新数据
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.19所示。
图4.19 BOOKS表查询结果
当然，使用UPDATE语句也可以一次更新多行数据。比如下面的实例。
【实例4-11】使用UPDATE语句更新表中的多行数据。
本实例使用UPDATE语句，将BOOKS表中所有的科学出版社的图书价格（B_Price）设置为9折。实例代码如下：
01 UPDATE BOOKS
02 SET B_Price=B_Price＊0.9
03 WHERE B_Publish='科学出版社';
【执行效果】
在SQL＊Plus中的执行效果如图4.20所示。
图4.20 更新多行数据
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.21所示。
图4.21 BOOKS表查询结果
4.3.3 根据条件修改表中的数据
有时候，需要根据其他表的信息来更新目标表中的某些数据。这时，可以在UPDATE语句中，使用FROM子句引入参考表，通过WHERE子句指定更新条件。使用语法可表示如下：
UPDATE ＜table_or_view_name＞
SET column_name = {expression | DEFAULT | NULL} [ ,...n ]
FROM ＜table_source＞
WHERE ＜search_condition＞
FROM子句后指定表、视图或派生表源，为更新操作提供条件。如果更新的对象在该子句中出现了不止一次，则对此对象的一个（且仅仅一个）引用不能指定表别名，其中对该对象的所有其他引用都必须包含对象别名。当然，在FROM子句中也可以指定进行多表连接。
4.3.4 使用SQL Developer工具修改数据
前面已经讲解过使用SQL Developer工具查询数据和添加数据，修改数据和添加数据一样，都是在如图4.3所示的界面中右击选择表，在弹出的右键菜单中选择【打开】选项，在右侧的窗口中选择【数据】选项，可以看到表中的全部数据，直接编辑要修改的数据，然后保存即可。这里就不再讲述了，请读者自行练习。
4.4 删除数据
当不再使用表中的记录时，可以使用DELETE语句将其删除。使用DELETE语句可以一次删除一条或多条记录，而且可以使用WHERE子句指定删除条件。
4.4.1 删除操作的基本语法
在PL/SQL中，DELETE命令的基本语法如下：
DELETE [FROM] table_or_view_name
WHERE search_condition
其中，FROM为可选的关键词，可用在DELETE关键词与目标table_or_view_name之间。
如果要删除表中的所有行，请使用未指定WHERE子句的DELETE语句，或者使用TRUNCATE TABLE语句。TRUNCATE TABLE比DELETE执行速度快，且使用的系统和事务日志资源少。另外，使用DELETE语句时，应注意以下几点。
◆ DELETE语句不能删除单个字段的值，它只能删除整行数据。要删除单个字段的值，可以使用上节介绍的UPDATE语句，将其更新为NULL。
◆ 使用DELETE语句仅能删除记录即表中的数据，不能删除表本身。要删除表，需要使用前面介绍的DROP TABLE语句。
◆ 同INSERT和UPDATA语句一样，从一个表中删除记录将引起其他表的参照完整性问题。这是一个潜在问题，需要时刻注意。
4.4.2 删除表中的数据
DELETE语句可以删除数据库表中的单行数据、多行数据以及所有行数据。另外，与UPDATE语句一样，也可以使用FROM子句根据其他表的信息删除目标表的记录行，还可以在WHERE子句中通过子查询删除数据。
【实例4-12】使用DELETE语句删除表中的行。
本实例使用DELETE语句，将BOOKS表中所有大众出版社的图书记录删除。实例代码如下：
01 DELETE FROM BOOKS
02 WHERE B_Publish='大众出版社';
【执行效果】
在SQL＊Plus中的执行效果如图4.22所示。
图4.22 删除记录
运行该代码，此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS
得到的查询结果如图4.23所示。
图4.23 BOOKS表查询结果
由此可见，所有大众出版社的图书记录均被删除。
4.4.3 有关TRUNCATE的使用
使用TRUNCATE TABLE语句可以删除表中的所有行，而不记录单个行删除操作。其使用语法如下：
TRUNCATE TABLE table_name
下面通过实例说明其使用方法。
【实例4-13】使用TRUNCATE TABLE语句删除表中的所有数据。
本实例将实现删除BOOKS表中的所有记录。实例代码如下：
01 TRUNCATE TABLE BOOKS;
【执行效果】
在SQL＊Plus中的执行效果如图4.24所示。
图4.24 使用TRUNCATE语句删除表中的所有记录
运行该代码，则BOOKS表中的所有记录都将被删除。此时，运行下面的代码查询BOOKS表的记录：
SELECT ＊ FROM BOOKS;
得到的查询结果如图4.25所示。
图4.25 BOOKS表查询结果
可见，虽然BOOKS表中的数据均被删除，但表结构并没有被删除，即BOOKS表仍然存在，这与前面介绍的删除表的DROP TABLE语句是不同的。
虽然使用DELETE语句和TRUNCATE TABLE语句都能够删除表中的所有数据，但是使用TRUNCATE TABLE语句比使用DELETE语句的执行效率要高。这是因为：使用DELETE语句，系统将一次一行地处理要删除的表中的记录，在从表中删除行之前，在事务处理日志中记录相关的删除操作和删除行中的列值，以便在删除失败时，可以使用事务处理日志来恢复数据。
使用TRUNCATE TABLE语句则一次性完成删除与表有关的所有数据页的操作。另外， TRUNCATE TABLE语句并不更新事务处理日志。因此，使用TRUNCATE TABLE语句从表中删除行后，将不能用ROLLBACK命令取消行的删除操作。
4.4.4 使用SQL Developer工具删除数据
使用SQL Developer工具删除数据，只需要在编辑数据的界面上选中要删除的数据，点击工具栏上面的减号即可删除数据。工具栏如图4.26所示。
图4.26 工具栏
4.5 小结
本章主要讲解了如何操作数据表中的数据，也就是SQL语句中的DML（数据操纵语言）部分的内容，包括数据的添加、数据的修改、数据的删除以及查询操作。在本章中着重讲解了数据的添加操作。在添加数据时，讲解了插入值是默认值的处理、插入值是唯一值的处理，以及使用INSERT…SELECT语句插入数据。此外，还介绍了使用SQL Developer工具添加、修改、删除数据的操作。