Oracle第13章SQL语言基础

1、Oracle10g 数据库基础教程第第13章章 SQL语言基础语言基础Oracle 10g数据库基础教程本章内容本章内容pSQL语句分类p数据查询（SELECT）p数据操纵（INSERT、UPDATE、DELETE）p事务控制pSQL函数Oracle 10g数据库基础教程本章要求本章要求p掌握数据查询的各种应用p掌握数据操纵的各种应用p掌握事务处理p了解SQL函数应用Oracle 10g数据库基础教程13.1 SQL语言概述语言概述pSQL语言介绍 pSQL语言的分类 pSQL语言的特点 Oracle 10g数据库基础教程13.1.1 SQL语言介绍语言介绍pSQL（StructuredQue

2、ryLanguage）语言是1974年由Boyce和Chamberlin提出的。 pSQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过SQL语言实现对数据库的访问和操作。 Oracle 10g数据库基础教程13.1.2 SQL语言的分类语言的分类p据定义语言（Data Definition Language，DDL）：用于定义、修改、删除数据库对象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。p数据操纵语言（Data Manipulati

3、on Language，DML）：用于改变数据库中的数据，包括数据插入（INSERT）、数据修改（UPDATE）和数据删除（DELETE）。p数据查询语言（Data Query Language，DQL）：用于数据检索，包括SELECT。p事务控制（Transaction Control）：用于将一组DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（COMMIT）、事务回滚（ROLLBACK）、设置保存点（SAVEPOINT）和设置事务状态（SET TRANSACTION）。p系统控制（System Control）：用于设置数据库系统参数，包括ALTER SYSTEM。p会话控制

4、（Session Control）：用于设置用户会话相关参数，包括ALTER SESSION。 Oracle 10g数据库基础教程13.1.3 SQL语言的特点语言的特点p功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。p高度的非过程化：在使用SQL语言操作数据库时，用户只需要说明“做什么”，而不需要说明“怎样做”。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。p面向集合的操作方式：SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式极大地提高了

5、对数据操作效率。p多种使用方式：SQL语句既是自含式语言，又是嵌入式语言。SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。p简洁、易学：SQL语言命令数量有限，语法简单，接近于自然语言（英语），因此容易学习和掌握。 Oracle 10g数据库基础教程13.2 数据查询数据查询p数据查询基础p基本查询p分组查询p连接查询p子查询p合并查询Oracle 10g数据库基础教程 13.2.1 数据查询基础数据查询基础p基本语法：nSELECT ALL|DISTINCTcolumn_name,expressionnFROM table1_name,table2_name,

6、view_name,nWHERE conditionnGROUP BY column_name1,column_name2, nHAVING group_conditionnORDER BY column_name2 ASC|DESCn,column_name2,; Oracle 10g数据库基础教程13.2.2 基本查询基本查询p无条件查询p有条件查询p查询排序p查询统计Oracle 10g数据库基础教程 （1）无条件查询）无条件查询p查询所有列nSELECT * FROM emp;p查询指定列nSELECT deptno,dname FROM dept; p使用算术表达式nSELECT e

7、mpno,sal*0.8 FROM emp;p使用字符常量nSELECT empno, Name is: , ename FROM emp; p使用函数nSELECT empno,UPPER(ename) FROM emp; Oracle 10g数据库基础教程p改变列标题nSELECT ename employeename,sal salary FROM emp;p使用连接字符串nSELECT 员工号：员工号：|empno|员工名员工名|ename FROM emp;p消除重复行nSELECT ALL deptno FROM emp; nSELECT DISTINCT deptno FROM

8、emp; Oracle 10g数据库基础教程（2）有条件查询）有条件查询p查询满足条件的元组可以通过WHERE 子句实现。pWHERE条件中常用的运算符号运算符号谓词比较大小=, , =, =, ，!=确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，OROracle 10g数据库基础教程p关系运算 nSELECT empno,ename,sal FROM emp WHERE deptno! = 10;nSELECT empno,ename,sal FROM emp

9、 WHERE sal1500p确定范围n谓词谓词BETWEEN AND与与NOT BETWEEN AND。n SELECT * FROM emp WHERE deptno BETWEEN 10 AND 20;nSELECT * FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;Oracle 10g数据库基础教程p确定集合n谓词谓词IN可以用来查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。nSELECT empno,ename,sal FROM emp WHERE deptno IN(10,30); p字符匹配n%（百分号）代表任意长

10、（长度为（百分号）代表任意长（长度为0）字符串。）字符串。n_（下划线）代表任意单个字符。（下划线）代表任意单个字符。nESCAPE：转义字符：转义字符nSELECT * FROM empWHERE ename LIKE %S%； nSELECT * FROM emp WHERE ename LIKE _A%; nSELECT * FROM emp WHERE ename LIKE %x_% ESCAPE x; Oracle 10g数据库基础教程p空值操作n涉及空值查询时使用涉及空值查询时使用IS NULL或或 IS NOT NULL，这里的这里的IS不能用不能用=替代。替代。nSELECT

11、* FROM emp WHERE deptno IS NULL;nSELECT * FROM emp WHERE comm IS NOT NULL; Oracle 10g数据库基础教程p逻辑操作n用逻辑运算符用逻辑运算符NOT、AND和和OR来联结多个查询条件。来联结多个查询条件。n优先级：优先级：NOT、AND、OR（用户可以用括号改变优（用户可以用括号改变优先级）。先级）。 nIN谓词实际上是多个谓词实际上是多个OR运算的缩写。运算的缩写。n nSELECT * FROM emp WHERE deptno=10 AND sal 1500; nSELECT * FROM emp WHERE

12、(deptno=10 OR deptno=20)AND sal1500; Oracle 10g数据库基础教程p注意：n使用使用BETWEENAND，NOT BETWEENAND，IN，NOT IN运算符的查询条件都可以转换为运算符的查询条件都可以转换为NOT，AND，OR的逻辑运算。例如，下面两个语句是等价的：的逻辑运算。例如，下面两个语句是等价的：nSELECT * FROM emp WHERE sal1000 AND sal2000;nSELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000; Oracle 10g数据库基础教程p升序、降序排序nA

13、SC: 升序升序 （缺省）；（缺省）；DESC: 降序降序nSELECT empno,ename,sal FROM emp ORDER BY sal;nSELECT empno,ename,sal FROM emp ORDER BY sal DESC; p多列排序 n首先按照第一个列或表达式进行排序；当第一个列或表达首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类式的数据相同时，以第二个列或表达式进行排序，以此类推推 。nSELECT * FROM emp ORDER BY deptno,sal DESC; （3）查询排序）查询排序Oracl

14、e 10g数据库基础教程p按表达式排序 n可以按特定的表达式进行排序。可以按特定的表达式进行排序。 nSELECT empno,ename,sal FROM emp ORDER BY sal*12; p使用别名排序 n可以使用目标列或表达式的别名进行排序。可以使用目标列或表达式的别名进行排序。nSELECT empno,sal*12 salary FROM emp ORDER BY salary; p使用列位置编号排序 n如果列名或表达式名称很长，那么使用位置排序可以缩短如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。排序语句的长度。nSELECT empno,sal*12

15、salary FROM emp ORDER BY 2; Oracle 10g数据库基础教程（4）查询统计）查询统计函数格式功能AVGAVG(DISTINCT|ALL)计算一列值的平均值（要求数值列）COUNTCOUNT(DISTINCT|ALL *)统计元组个数COUNTCOUNT(DISTINCT|ALL) 统计一列中 非空值的个数MAXMAX(DISTINCT|ALL)求一列值中的最大值MINMIN(DISTINCT|ALL)求一列值中的最小值SUMSUM(DISTINCT|ALL)计算一列值的总和（要求数值列）STDDEV STDDEV().计算一列值的标准差VARIANCEVARIAN

16、CE()计算一列值的方差Oracle 10g数据库基础教程p注意n除了除了COUNT(*)函数外，其他的统计函数都不考虑返函数外，其他的统计函数都不考虑返回值或表达式为回值或表达式为NULL的情况。的情况。n聚集函数只能出现在目标列表达式、聚集函数只能出现在目标列表达式、ORDER BY子句、子句、HAVING子句中，不能出现在子句中，不能出现在WHERE子句和子句和GROUP BY子句中。子句中。n默认对所有的返回行进行统计，包括重复的行；如果默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用要统计不重复的行信息，则可以使用DISTINCT选项。选项。n如果对查询

17、结果进行了分组，则聚集函数的作用范围如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。为各个组，否则聚集函数作用于整个查询结果。Oracle 10g数据库基础教程pSELECT count(*),avg(sal),max(sal),min(sal) FROM emp WHERE deptno=10; pSELECT avg(comm),sum(comm) FROM emp ; pSELECT count(DISTINCT deptno) FROM emp; pSELECT variance(sal),stddev(sal) FROM emp; Oracle

18、 10g数据库基础教程13.2.3 分组查询分组查询p基本语法p单列分组查询p多列分组查询p使用HAVING子句限制返回组p使用ROLLUP 和CUBEp合并分组查询Oracle 10g数据库基础教程nSELECT column, group_function, nFROM tablenWHERE conditionnGROUP BY ROOLUP|CUBE|GROUPING SETS ngroup_by_expressionnHAVING group_conditionnORDER BY columnASC|DESC;（1）基本语法）基本语法Oracle 10g数据库基础教程p注意：nGRO

19、UP BY子句用于指定分组列或分组表达式。子句用于指定分组列或分组表达式。n集合函数用于对分组进行统计。如果未对查询分组，集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。都有一个集合函数。nHAVING子句用于限制分组的返回结果。子句用于限制分组的返回结果。nWHERE子句对表中的记录进行过滤，而子句对表中的记录进行过滤，而HAVING子子句对分组后形成的组进行过滤。句对分组后形成的组进行过滤。n

20、在分组查询中，在分组查询中，SELECT子句后面的所有目标列或目子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是标表达式要么是分组列，要么是分组表达式，要么是集合函数。集合函数。Oracle 10g数据库基础教程p单列分组查询n将查询出来的记录按照某一个指定的列进行分组将查询出来的记录按照某一个指定的列进行分组nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno; p多列分组查询n在在GROUP BY子句中指定了两个或多个分组列子句中指定了两个或多个分组列nSELECT deptno,job,count(*),

21、avg(sal) FROM emp GROUP BY deptno,job; p使用HAVING子句限制返回组n可以使用可以使用HAVING子句，只有满足条件的组才会返回。子句，只有满足条件的组才会返回。nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)1500;Oracle 10g数据库基础教程p使用ROLLUP 和CUBEn如果在如果在GROUP BY子句中使用子句中使用ROLLUP选项，则还选项，则还可以生成横向统计和不分组统计；可以生成横向统计和不分组统计；n如果在如果在GROUP BY子句

22、中使用子句中使用CUBE选项，则还可以选项，则还可以生成横向统计、纵向统计和不分组统计。生成横向统计、纵向统计和不分组统计。nSELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job);nSELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job);Oracle 10g数据库基础教程p合并分组查询n使用使用GROUPING SETS可以将几个单独的分组查询可以将几个单独的分组查询合并成一个分组查询合并成一个分组查询 nSELECT deptno,job,avg(sa

23、l) FROM emp GROUP BY GROUPING SETS(deptno,job); Oracle 10g数据库基础教程13.2.4 连接查询连接查询p交叉连接p内连接n等值连接等值连接n不等值连接不等值连接n自身连接自身连接p外连接n左外连接左外连接n右外连接右外连接n全外连接全外连接Oracle 10g数据库基础教程（1）交叉连接）交叉连接p概念n两个或多个表之间的无条件连接。一个表中所有记录两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的分别与其他表中所有记录进行连接。如果进行连接的表中分别有表中分别有n1，n2，n3条记录，那么交叉

24、连接的条记录，那么交叉连接的结果集中将有结果集中将有n1n2n3条记录。条记录。 p以下情况可以出现交叉连接n连接条件省略连接条件省略n连接条件非法连接条件非法n一个表的所有行被连接到另一个表的所有行一个表的所有行被连接到另一个表的所有行p示例nSELECT ename,dname from emp,dept;Oracle 10g数据库基础教程（2）内连接）内连接p执行过程p内连接语法p等值内连接p非等值内连接p自身内连接Oracle 10g数据库基础教程p执行过程n首先在表首先在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后

25、就将表逐一查找满足连接条件的元组，找到后就将表1中的第中的第1个元组与该元组拼接形成结果表中的一个元组。表个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表全部找完后，再找表1中的第中的第2个元组，然后再从头扫个元组，然后再从头扫描表描表2，逐一查找满足连接条件的元组，找到后就将表，逐一查找满足连接条件的元组，找到后就将表1中的第中的第2个元组与该元组拼接形成结果表中的一个元个元组与该元组拼接形成结果表中的一个元组。重复执行，直到表组。重复执行，直到表1中的全部元组都处理完毕为止。中的全部元组都处理完毕为止。Oracle 10g数据库基础教程p内连接语法：标准SQL语句的连接方式

26、nSELECT table1.column,talbe2.column, nFROM table1 INNER JOIN table2 JOIN nON condition;p内连接语法：Oracle扩展的连接方式nSELECT table1.column,talbe2.column, nFROM table1,table2, nWHERE condition;Oracle 10g数据库基础教程p等值内连接 nSELECT empno,ename,sal,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=10 AND emp.deptno=de

27、pt.deptno;nSELECT empno,ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno=10 AND emp.deptno=dept.deptno; Oracle 10g数据库基础教程p非等值内连接nSELECT empno,ename,sal,grade FROM emp JOIN salgrade ON sallosal AND sallosal AND salhisal;Oracle 10g数据库基础教程p自身内连接 nSELECT worker.empno,worker.ename, manager.empno

28、, manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno;nSELECT worker.empno,worker.ename, manager.empno,manager.ename FROM emp worker,emp manager WHERE worker.mgr=manager.empno;Oracle 10g数据库基础教程（3）外连接）外连接p左外连接p右外连接p全外连接Oracle 10g数据库基础教程p左外连接语法：标准SQL语句的连接方式nSELECT table1.column,

29、 table2.column, nFROM table1 LEFT JOIN table2, nON table1.column table2.column,;p左外连接语法：Oracle扩展的连接方式nSELECT table1.column, table2.column, nFROM table1, table2,nWHERE table1.column n table2.column(+);Oracle 10g数据库基础教程p查询10号部门的部门名、员工号、员工名和所有其他部门的名称，语句为nSELECT dname,empno,ename nFROM dept nLEFT JOIN e

30、mpnON dept.deptno=emp.deptno AND dept.deptno=10;p或nSELECT dname,empno,enamenFROM dept,empnWHERE dept.deptno=emp.deptno(+) AND n emp.deptno(+)=10;Oracle 10g数据库基础教程p右外连接语法：标准SQL语句的连接方式nSELECT table1.column, table2.column, nFROM table1 RIGHT JOIN table2, nON table1.column table2.column;p右外连接语法：Oracle扩展

31、的连接方式nSELECT table1.column, table2.column, nFROM table1, table2,nWHERE table1.column (+)n table2.column; Oracle 10g数据库基础教程p查询20号部门的部门名称及其员工号、员工名，和所有其他部门的员工名、员工号，语句为nSELECT empno,ename,dname nFROM dept RIGHT JOIN empnON dept.deptno=emp.deptno AND n dept.deptno=20;p或nSELECT empno,ename,dname nFROM dep

32、t,empnWHERE dept.deptno(+)=emp.deptno AND n dept.deptno(+)=20; Oracle 10g数据库基础教程p全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。p在Oracle数据库中，全外连接的表示方式为nSELECT table1.column, table2.column, nFROM table1 FULL JOIN table2, nON table1.column1 = table2.column2;p查询所有的部门名和员工名，语句为nSELECT dname,ename nFROM emp FU

33、LL JOIN dept nON emp.deptno=dept.deptno; Oracle 10g数据库基础教程13.2.5 子查询子查询p子查询概述p单行单列子查询p多行单列子查询p单行多列子查询p多行多列子查询p相关子查询p在FROM子句中使用子查询 p在DDL语句中使用子查询p使用WITH子句的子查询Oracle 10g数据库基础教程p子查询的概念n子查询是指嵌套在其他子查询是指嵌套在其他SQL语句中的语句中的SELECT语句，也语句，也称为嵌套查询称为嵌套查询 。n在执行时，由里向外，先处理子查询，再将子查询的返在执行时，由里向外，先处理子查询，再将子查询的返回结果用于其父语句（外

34、部语句）的执行。回结果用于其父语句（外部语句）的执行。 p子查询作用n在在INSERT或或CREATE TABLE语句中使用子查询，可语句中使用子查询，可以将子查询的结果写入到目标表中；以将子查询的结果写入到目标表中；n在在UPDATE语句中使用子查询可以修改一个或多个记录语句中使用子查询可以修改一个或多个记录的数据；的数据；n在在DELETE语句中使用子查询可以删除一个或多个记录语句中使用子查询可以删除一个或多个记录n在在WHERE和和HAVING子句中使用子查询可以返回的子句中使用子查询可以返回的一个或多个值。一个或多个值。（1）子查询概述）子查询概述Oracle 10g数据库基础教程p单

35、行单列子查询是指子查询只返回一行数据，而且只返回一列的数据。 p运算符n=，=，n (SELECT sal FROM emp WHERE empno=7934); （2）单行单列子查询）单行单列子查询Oracle 10g数据库基础教程（3）多行单列子查询）多行单列子查询p多行单列子查询是指返回多行数据，且只返回一列的数据。 p运算符号运运 算算 符符含含 义义IN与子查询返回结果中任何一个值相等与子查询返回结果中任何一个值相等NOT IN与子查询返回结果中任何一个值都不等与子查询返回结果中任何一个值都不等ANY比子查询返回结果中某一个值大比子查询返回结果中某一个值大=ANY与子查询返回结果中某

36、一个值相等与子查询返回结果中某一个值相等ALL比子查询返回结果中所有值都大比子查询返回结果中所有值都大ANY n (SELECT sal FROM emp WHERE deptno=10);p查询比10号部门所有员工工资高的员工信息。nSELECT empno,ename,sal FROM empnWHERE sal ALL n (SELECT sal FROM emp WHERE deptno=10); Oracle 10g数据库基础教程（4）单行多列子查询）单行多列子查询p单行多列子查询是指子查询返回一行数据，但是包含多列数据。p多列数据进行比较时，可以成对比较，也可以非成对比较。成对比较

37、要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。Oracle 10g数据库基础教程p查询与7844号员工的工资、工种都相同的员工的信息。nSELECT empno,ename,sal,job FROM emp nWHERE (sal,job)=(SELECT sal,job FROM empn WHERE empno=7844);p查询与10号部门某个员工工资相同，工种也与10号部门的某个员工相同的员工的信息。nSELECT empno,ename,sal,job FROM emp nWHERE sal IN (SELECT sal FROM emp WHERE nde

38、ptno=10) AND job IN (SELECT job FROM emp nWHERE deptno=10); Oracle 10g数据库基础教程（5）多行多列子查询）多行多列子查询p多行多列子查询是指子查询返回多行数据，并且是多列数据。p例如，查询与10号部门某个员工的工资和工种都相同的员工的信息，语句为nSELECT empno,ename,sal,job FROM empnWHERE (sal,job) IN(nSELECT sal,job FROM emp WHERE deptno=10); Oracle 10g数据库基础教程（6）相关子查询）相关子查询p子查询在执行时并不需要

39、外部父查询的信息，这种子查询称为无关子查询。p如果子查询在执行时需要引用外部父查询的信息，那么这种子查询就称为相关子查询。p在相关子查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子查询返回结果，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。Oracle 10g数据库基础教程p查询没有任何员工的部门号、部门名。nSELECT deptno,dname,loc FROM dept nWHERE NOT EXISTS(SELECT * FROM emp WHERE nemp.deptno=dept.deptno);p查询比本部门平均工资高的员工信息。nSELECT

40、empno,ename,sal nFROM emp e nWHERE sal( SELECT avg(sal) FROM emp WHERE ndeptno=e.deptno); Oracle 10g数据库基础教程（7）在）在FROM子句中使用子查询子句中使用子查询p当在FROM子句中使用子查询时，该子查询被作为视图对待，必须为该子查询指定别名。p查询各个员工的员工号、员工名及其所在部门平均工资。nSELECT empno,ename,d.avgsal FROM emp,n(SELECT deptno,avg(sal) avgsal FROM emp GROUP nBY deptno) d W

41、HERE emp.deptno=d.deptno;p查询各个部门号、部门名、部门人数及部门平均工资。nSELECT dept.deptno,dname, d.amount,d.avgsal nFROM dept,(SELECT deptno,count(*)amount, navg(sal) avgsal FROM emp GROUP BY deptno)d nWHERE dept.deptno=d.deptno; Oracle 10g数据库基础教程（8）在）在DDL语句中使用子查询语句中使用子查询p可以在CREATE TABLE和CREATE VIEW语句中使用子查询来创建表和视图。nCRE

42、ATE TABLE emp_subquery nAS nSELECT empno,ename,sal FROM emp;nCREATE VIEW emp_view_subquerynASnSELECT * FROM emp WHERE sal2000; Oracle 10g数据库基础教程（9）使用）使用WITH子句的子查询子句的子查询p如果在一个SQL语句中多次使用同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。p查询人数最多的部门的信息。nSELECT * FROM deptnWHERE deptno IN (nSELE

43、CT deptno FROM emp GROUP BY deptno nHAVING count(*)=ALL(n SELECT count(*) FROM emp GROUP BY deptno)n);Oracle 10g数据库基础教程p相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。nWITH deptinfo AS(SELECT deptno,count(*) num nFROM emp GROUP BY deptno) nSELECT * FROM dept WHERE deptno IN(SELECT ndeptno FROM deptinfo WHERE num=(SE

44、LECT nmax(num) FROM deptinfo)n); Oracle 10g数据库基础教程13.2.6 合并查询合并查询p语法pUNIONpINTERSECTpMINUSOracle 10g数据库基础教程p语法： SELECT query_statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT query_statement2;p注意：n当要合并几个查询的结果集时，这几个查询的结果集当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。必须具有相同的列数与数据类型。n如果要对最终的结果集排序，只能在最后一个查询之如果要

45、对最终的结果集排序，只能在最后一个查询之后用后用ORDER BY子句指明排序列。子句指明排序列。 Oracle 10g数据库基础教程（1） UNIONpUNION运算符用于获取几个查询结果集的并集，将重复的记录只保留一个，并且默认按第一列进行排序。p查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的员工号、员工名、工资和部门号，语句为nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=10nUNIONnSELECT empno,ename,sal,deptno nFROM emp WHERE sal2000nOR

46、DER BY deptno; Oracle 10g数据库基础教程p如果要保留所有的重复记录，则需要使用UNION ALL运算符。nSELECT empno,ename,sal,deptnonFROM emp WHERE deptno=10nUNION ALLnSELECT empno,ename,sal,deptno nFROM emp WHERE sal2000nORDER BY deptno; Oracle 10g数据库基础教程（2） INTERSECTpINTERSECT用于获取几个查询结果集的交集，只返回同时存在于几个查询结果集中的记录。同时，返回的最终结果集默认按第一列进行排序。p查

47、询30号部门中工资大于2000的员工号、员工名、工资和部门号，语句为：nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=30nINTERSECTnSELECT empno,ename,sal,deptno nFROM EMP WHERE sal2000; Oracle 10g数据库基础教程（3） MINUSpMINUS用于获取几个查询结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存在的记录。同时，返回的最终结果集默认按第一列进行排序。p查询30号部门中工种不是“SALESMAN”的员工号、员工名和工种名称，语句为： nS

48、ELECT empno,ename,job nFROM emp WHERE deptno=30nMINUSnSELECT empno,ename,job nFROM EMP WHERE job=SALESMAN;Oracle 10g数据库基础教程13.3 数据操纵数据操纵p插入数据p修改数据pMERGE语句 p删除数据Oracle 10g数据库基础教程13.3.1 插入数据插入数据p插入单行记录p利用子查询插入数据p向多个表中插入数据Oracle 10g数据库基础教程（1）插入插入单行记录单行记录p语法nINSERT INTO table_name|view_name n(column1,co

49、lumn2) nVALUES(value1,values, ) p注意n如果在如果在INTO子句中没有指明任何列名，则子句中没有指明任何列名，则VALUES子句中列值子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。n如果在如果在INTO子句中指定了列名，则子句中指定了列名，则VALUES子句中提供的列值子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。应。n向表或视图中插入的数据必须满足表的完整性约束。向表或视图中插入的数据必须满足表的完整

50、性约束。n字符型和日期型数据在插入时要加单引号。日期类型数据需要按字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用系统默认格式输入，或使用TO_DATE函数进行日期转换。函数进行日期转换。 Oracle 10g数据库基础教程p向dept表中插入一行记录。nINSERT INTO dept VALUES(50, IM, dalian);p向emp表中插入一行记录。nINSERT INTO emp(empno,ename,sal,hiredate) nVALUES(1234, JOAN,2500, 20-4月月-2007);Oracle 10g数据库基础教程（2）利

51、用子查询插入数据）利用子查询插入数据 p语法nINSERT INTO table_name|view_namen(column1,column2,)nsubquery;p注意nINTO子句中指定的列的个数、顺序、类型必须与子子句中指定的列的个数、顺序、类型必须与子查询中列的个数、顺序和类型相匹配。查询中列的个数、顺序和类型相匹配。 Oracle 10g数据库基础教程p统计各个部门的部门号、部门最高工资和最低工资，并将统计的结果写入到表emp_salary（假设该表已经创建）中。nINSERT INTO emp_salary nSELECT deptno,max(sal),min(sal) FR

52、OM emp nGROUP BY deptno;p向emp表中插入一行记录，其员工名为FAN，员工号为1235，其他信息与员工名为SCOTT的员工信息相同。nINSERT INTO emp nSELECT 1235, FAN,job,mgr,hiredate,sal,comm,deptnonFROM emp WHERE ename=SCOTT;Oracle 10g数据库基础教程p如果要将大量数据插入表中，可以利用子查询直接装载的方式进行。由于直接装载数据的操作过程不写入日志文件，因此数据插入操作的速度大大提高。p利用子查询装载数据语法为：nINSERT /*+APPEND*/ INTO nta

53、ble_name|view_name(column1,column2,)nsubquery;p复制emp表中empno，ename，sal，deptno四列的值，并插入到new_emp表中，语句为：nINSERT /*+APPEND*/ INTO nnew_emp(empno,ename,sal,deptno)nSELECT empno,ename,sal,deptno from emp; Oracle 10g数据库基础教程13.3.2 修改数据修改数据p基本语法p修改单行记录p修改多行记录p带有子查询的修改Oracle 10g数据库基础教程p基本语法nUPDATE table_name|vi

54、ew_namenSET column1=value1,column2=value2nWHERE conditionp修改单条记录nUPDATE emp SET sal=sal+100,comm=200 nWHERE empno=7844;p修改多条记录nUPDATE emp SET sal=sal+150 nWHERE deptno=20; p利用子查询修改记录nUPDATE emp SET sal=300+ (SELECT avg(sal) nFROM emp WHERE deptno=10) nWHERE deptno=30; Oracle 10g数据库基础教程13.3.3 MERGE语句

55、语句p利用MERGE语句可以同时完成数据的插入与更新操作。p将源表的数据分别与目标表中的数据根据特性条件进行比较（每次只比较一条记录），如果匹配，则利用源表中的记录更新目标表中的记录，如果不匹配，则将源表中的记录插入目标表中。p使用MERGE语句操作时，用户需要具有源表的SELECT对象权限以及目标表的INSERT，UPDATE对象权限。Oracle 10g数据库基础教程pMERGE语句的基本语法为：nMERGE INTO schema.target_table target_alias nUSING schema.source_table|source_view|nsource_subque

56、ry source_aliasnON (condition)nWHEN MATCHED THEN UPDATE SETn column1=expression1 n ,column2=expression2 n where_clauseDELETE where_clausenWHEN NOT MATCHED THEN n INSERT (column2,column2) n VALUES (expresstion1,expression2) n where_clause;Oracle 10g数据库基础教程p参数说明nINTO：指定进行数据更新或插入的目标表；：指定进行数据更新或插入的目标表；n

57、USING：指定用于目标表数据更新或插入的源表或视图或子查：指定用于目标表数据更新或插入的源表或视图或子查询；询；nON：决定：决定MERGE语句执行更新操作还是插入操作的条件。对于语句执行更新操作还是插入操作的条件。对于目标表中满足条件的记录，则利用源表中的相应记录进行更新；目标表中满足条件的记录，则利用源表中的相应记录进行更新；而源表中不满条件的记录将被插入目标表中；而源表中不满条件的记录将被插入目标表中；nwhere_clause：只有当该条件为真时才进行数据的更新或插：只有当该条件为真时才进行数据的更新或插入操作；入操作；nDELETE where_clause：当目标表中更新后的记录

58、满足该条：当目标表中更新后的记录满足该条件时，则删除该记录。件时，则删除该记录。 Oracle 10g数据库基础教程p使用“雇员”表（别名e）作为数据源，在该表 的副本“副本_雇员”表（别名c）中插入并更新行。pMERGE INTO 副本_雇员 c USING 雇员epON (c.雇员标识= e.雇员标识)pWHEN MATCHED THEN UPDATEpSETpc.姓氏 = e.姓氏,pc.部门标识 = e.部门标识pWHEN NOT MATCHED THEN INSERT pVALUES (e.雇员标识, e.姓氏, e.部门标识);Oracle 10g数据库基础教程MERGE INTO

59、 副本_雇员 c USING 雇员e ON (c.雇员标识= e.雇员标识) WHEN MATCHED THEN UPDATE SET c.姓氏 = e.姓氏, c.部门标识 = e.部门标识WHEN NOT MATCHED THEN INSERT VALUES (e.雇员标识, e.姓氏, e.部门标识); MERGE 语句执行前的“副本_雇员”雇员标识 姓氏部门标识100Smith40103Chang30MERGE 执行后的“副本_雇员”雇员标识 姓氏部门标识100King90103Hunold 60152Davies 50雇员（来源表）雇员标识 姓氏部门标识100King90103Hun

60、old 60152Davies 50Oracle 10g数据库基础教程p现有表source_emp和target_emp，表中数据如下。利用source_emp表中的数据更新target_emp表中的数据，对target_emp表中存在的员工信息进行更新，对不存在的员工进行信息插入。 nSELECT * FROM source_emp;nEMPNO ENAME DEPTNOn-n100 JOAN 10n110 SMITH 20n120 TOM 30 Oracle 10g数据库基础教程pSELECT * FROM target_emp;pEMPNO ENAME DEPTNOp-p100 MARR