资料 SQL语言(4)

结果为：

王英泉 210 处长 P40 例38：列出P20部门中与P30部门某一人员职务相同的职工， SELECT peopname， job， salary FROM people

WHERE deptcode='P20'

and job in (SELECT job FROM people WHERE deptcode='P30'); 结果为： 吴志红 办事员 125 180 沈晓华 处长 例39：列出P20部门中与P30部门所有人员的职务都不相同的职工 SELECT peopname， job， salary? 2 FROM people WHERE deptcode='P20'

and job not in (SELECT job FROM people WHERE deptcode='P30'); 结果为：

姚志邦 采购员 150 张浩 采购员 140 林光华 采购员 110 3、返回多列的子查询

子查询可以返回多个列，当子查询返回多列时，关系运算的另一侧也应是多个列，并且这些列的清单必须用括号括起来。

例40：列出与沈晓华有相同工资和相同职务的职工 SELECT peopname，job，salary FROM people

WHERE (job，salary)=(SELECT job，salary FROM peopname

WHERE peopname='沈晓华'); 结果为： 沈晓华 处长 180 黄伯尧 处长 180 注意：虽然前面的例子中，子查询和主查询都从同一个表中取数据，但实际上子查询可从任何表中取值。

4、使用多个子查询

WHERE子句可以包含任何数量的由and和or连接的含有子查询的条件。 例41：列出职务与沈晓华相同，或工资大于或等于赵汉雄工资的职工， SELECT peopname， job， salary FROM people WHERE job=(SELECT job FROM people WHERE peopname='沈晓华')

or salary>=(SELECT salary FROM people WHERE peopname='赵汉雄') 结果为：

200 李铭棠 经理 170 周金海 处长 180 沈晓华 处长 170 赵汉雄 处长 210 王英泉 处长 180 黄伯尧 处长 沈妙玉 驾驶员 180 黄金宝 驾驶员 170 正如一个查询可以包含一个子查询一样，一个子查询也可以包含另一个更内层的子查询(子查询嵌套)。

例42：列出在供应处中与销售处具有的职务相同的职工的信息( 若不知道供应处和销售处的编号)。

SELECT peopname， job， salary FROM people

WHERE deptcode=(SELECT deptcode FROM department WHERE deptname='供应处')

and job in (SELECT job FROM people

WHERE deptcode=(SELECT deptcode FROM department WHERE deptname='销售处')); 结果为：

吴志红 办事员 125 180 沈晓华 处长 正像每个查询可以进行交、并、差运算一样，子查询也可以进行交、并、差运算

例43：列出工资等于沈晓华或赵汉雄工资的职员， SELECT * FROM people

WHERE salary in (SELECT salary FROM people WHERE peopname='沈晓华' UNION

SELECT salary FROM people WHERE peopname='赵汉雄'); 结果为：

3 周金海 男 45/08/21 81/10/20 处长 170 P10 28 黄金宝 男 56/08/22 81/08/21 卡车驾驶员 170 50 P50 17 赵汉雄 男 45/05/13 83/02/18 处长 170 P30 12 沈晓华 男 53/09/28 82/12/15 处长 180 P20 27 沈妙玉 女 57/04/20 81/08/05 小车驾驶员 180 20 P50 26 黄伯尧 男 50/10/18 85/08/23 处长 180 P50 5、子查询中使用表连接

有时，子查询可能需要从每个表中检索数据，这时必须在子查询中使用表连接。 例44：找出与在\公司大楼303室\办公的职工具有相同职务的职工的信息。 SELECT * FROM people WHERE job in (SELECT job FROM people p， department d WHERE address='公司大楼 303室' and p.deptcode=d.deptcode); 结果为： 2 周曙 女 62/07/15 62/10/20 办事员 157 P00 13 吴志红 女 61/05/12 86/09/17 办事员 125 P20 18 史玲娟 女 63/08/27 87/11/14 办事员 120 P30 1 李铭棠 男 40/04/25 80/10/01 经理 200 P00 此例中，我们先在子查询中把people表和depantment表连接起来，找出在“公司大楼303室”办公的职工的所有职务，然后当查询再根据这些职务找出符合要求的职工。 实际上，这个例子也可使用嵌套子查询来完成，请看下面的查询： SELECT * FROM people

WHERE job in (SELECT job FROM people

WHERE deptcode=(SELECT deptcode FROM department WHERE address='公司大楼 303室')); 结果为：

2 周曙 女 62/07/15 62/10/20 办事员 157 P00 13 吴志红 女 61/05/12 86/09/17 办事员 125 P20 18 史玲娟 女 63/08/27 87/11/14 办事员 120 P30 1 李铭棠 男 40/04/25 80/10/01 经理 200 P00 结果完全相同。 6、相关子查询

在前面的例子中，每个子查询都只被执行一次，并且执行的次序是从内向外，内层查询的结果被外层查询中的WHERE子句作为条件来使用。但有时会碰到这样的嵌套查询，内层查询要用到外层查询正在检索的对象，即内层查询的条件也是动态的，这种动态条件又不能用进一步的子查询来确定，而是随外层主查询的候选行的变化而变化，这样的子查询就叫做相关子查询。

例 45： 列出工资比其所在部门平均工资高的职工。 当然首先要由一个主查询从people表中选取它们，结构如下：

SELECT deptcode，peopname，salary FROM people

WHERE salary > (候选职工所在部门的平均工资);

另外还需要一个子查询来计算候选职工所在部门的平均工资，该子查询的结构如下： SELECT avg(salary) FROM people

WHERE deptcode = (侯选行的deptcode值) 当主查询在考虑每一侯选行时，必须首先告诉子查询该职工的所在部门号，然后子查询根据号计算出那个职工所在部门的平均工资，最后主查询把候选行的工资与子查询计算出来的平均工资值相比较来决定该候选行选取与否。问题在于子查询中，确认候选行的表和列与需要算出平均工资的表和列完全相同，解决的办法是在主查询中，对确认候选行的表规定一个别名。所以整个查询可完整地书写如下： SELECT *

FROM people p

WHERE salary>(SELECT avg(salary) FROM people

WHERE deptcode=p.deptcode); 结果如下： 1 李铭棠 男 40/04/25 80/10/01 经理 200 P00 4 陈宝娟 女 51/06/17 85/07/12 科长 150 P11 6 顾为民 男 55/10/01 84/06/11 科长 160 P12 9 郑文华 男 56/06/20 85/04/22 科长 150 P13 12 沈晓华 男 53/09/28 82/12/15 处长 180 P20 14 17 19 20 23 26 27 28 150 30 P20 姚志邦 男 50/01/20 81/08/23 采购员 170 P30 赵汉雄 男 45/05/13 83/02/18 处长 150 30 P30 王晨 男 52/06/05 82/01/05 推销员 150 30 P30 姚国平 男 57/09/14 82/04/07 推销员 210 P40 王英泉 男 35/12/02 81/09/25 处长 180 P50 黄伯尧 男 50/10/18 85/08/23 处长 沈妙玉 女 57/04/20 81/08/05 小车驾驶员 180 20 P50 黄金宝 男 56/08/22 81/08/21 卡车驾驶员 170 50 P50 下面再来看一个使用了测试是否存在运算符(exists)的相关子查询。 例46：列出至少有一个下属部门的那些部门的信息。输入： SELECT *

FROM department d? 3 WHERE exists (SELECT * FROM department WHERE higher=d.deptcode); 结果为：

P00 长城五金批发公司 公司大楼303室 1 303 P10 财务处 公司大楼103室 3 103 P00 P30 销售处 公司大楼204室 17 204 P00 从上面例子可知，使用相关子查询的关键是表的别名的使用。

三、数据操纵(DML)

本节描述了改变DRACLE数据库中的内容的一组命令，这些命令包括： * INSERT：往表中插入行 * DELETE：删除表中的行

* UPDATE：修改表中已存在行中的数据

在语法上，视图、同义词和表是一样的，所以本节中凡适用于表的命令也适用于视图和同义词(只要视图和同义词允许)。

(一) 在表中插入行

在数据库中，表中的数据是由行组成的，所以只能一行一行地把数据加入数据库。在SQL命令中，只有INSERT命令才能往表中插入行。INSERT命令的形式如下：： INSERT INTO 表名[(列清单)] {VALUES (数据值清单)|查询}

1、插入一行中的所有字段值。

例1：把名叫“林光华”的职工信息加入people表中， INSERT INTO people

VALUES(16， '林光华'， '男'， '47/04/11'， '10-APR-88'， '采购员'， 110.00， 20.00， 'P20')

当用这种形式增加记录时，必须用逗号把各个值分开，并用引号把字符值及日期值括起来。可以不把表中的所有列都写出来，VALUES子句中各个值的顺序必须与建表时规定的列相对应。上例中的第一个值(16)放在people表中的第一列(people)，第二个值(林光华)放在第二列(peopname)中，依次类推。

2、选择列插入

假如所要插入的行的某些字段值不知道，或者对于该行，某几个字段是无意义的，这时就应选择列来插入。

例2：将名为“陈虹”的那位职工的部分信息加入

INSERT INTIO people(peopcode, peopcame, sex, birthday, workdate, job, walary, deptcode)

VALUES(5, '陈虹', '女', '09-MAR-57', '19-JUN-86', '计划员', 120.00, 'P11');

当用这种形式增加记录时，VALUES 子句中的各个值应与表名后括号中的列名相对应，而与定义表时规定的列顺序无关。

3、插入数据的组织

(1)插入的各个数据必须用逗号分开，各个数据的顺序必须与列相对应。 (2)字符数据和日期数据必须用单引号括起来，整数和小数是无区别的。

(3)如果插入行中的某个字段没有被插入数据，则该行的该字段被认为是一个空值，也可以在VALUES子句中用字null来代表空值进入插入。 例3：在depantment表中插入P00号记录行 INSERT INTO department

VALUES('P00'，'长城五金批发公司'，'公司大楼303室'，1，'303'，null); (4)插入的日期值必须是标准的日期格式(假定yy/mm/dd格式)，如果日期信息是另一种形式，则必须用日期格式化函数(假定TO_DATE)指出它的格式。 例4：用DD/MM/YY的日期格式 插入林光华的信息

INSERT INTO people

VALUES (16, '林光华', '男', TO_DATEL('11/04/47', 'DD/MM/YY'), TO_DATE('10/04/88', 'DD/MM/YY'), '采购员', 140, 10, 'P20'); 例5：把林光华的出生时间(上午9点30分)也加入进去： INSERT INTO people

VALUES (16, '林光华’, '男', TO_DATE('11/04/47 9:30', 'DD/MM/YY HH:MI'), TO_DATE('10/04/88', 'DD/MM/YY'), '采购员', 140, 10, 'P20');

4、带有查询的插入

上面的插入命令都是把数据组织在VALUES子句中，一次命令只能插入一行。实际上，用户可以用查询来代替VALUES子句，一次命令插入多行。带有查询的INSERT命令将从一个表中选取行和列插入到规定的表中， 它实际上是在表之间复制行。 例6：建立一个公司高收入人员的表highincome，该表有四列(peopname，job，salary，award)。该表中人员其工资和奖金之和都超过180元。

INSERT INTO highincome(peopname，job，salary，award) SELECT peopname，job，salary，award FROM people

WHERE salary+nvl(award,0)>180

(二) 从表中删除行

删除表中的行可采用DELETE命令，命令形式如下： DELETE FROM 表名 [WHERE 逻辑表达式]

在这个命令中，WHERE子句是可选的。如果不选WHERE子句，则表中的所有行都将被删除；如果使用了WHERE子句，则表中使逻辑表达式为真的所有行都被删除。 DELETE 命令中的WHERE子句的形式与SELECT命令中的WHERE子句相同。 例6：删除highincome表中所有数据，输入： DELETE FROM highincome;

例7：删除people表中，姓名是“阮妙玉”的职员。 DELETE FROM people

WHERE peopname = '阮妙玉';

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库资料 SQL语言(4)在线全文阅读。