Oracle第13章SQL语言基础

《Oracle第13章SQL语言基础》由会员分享，可在线阅读，更多相关《Oracle第13章SQL语言基础（111页珍藏版）》请在装配图网上搜索。

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 Manipulation

3、 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会话控制（S

4、ession 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,view

6、_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 empno,sal*0

7、.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 emp;Oracle 10g数据库

8、基础教程（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 WHERE sal1500p确定范围n谓词谓词B

9、ETWEEN 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%（百分号）代表任意长（长度为（百分号）代表任意长（长度为0）字符串。）字符串。n

10、_（下划线）代表任意单个字符。（下划线）代表任意单个字符。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*FROM emp WHERE deptno IS NULL;nSELECT*FROM

11、 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(deptno=10 OR deptno=20)AND sal1500;Oracle 10g数据库基础教程p

12、注意：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升序、降序排序nASC:升序升序（缺省）；（缺省）；DESC:降序降序nSELECT empno,ename,sal FROM emp O

13、RDER BY sal;nSELECT empno,ename,sal FROM emp ORDER BY sal DESC;p多列排序 n首先按照第一个列或表达式进行排序；当第一个列或表达首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类式的数据相同时，以第二个列或表达式进行排序，以此类推推。nSELECT*FROM emp ORDER BY deptno,sal DESC;（3）查询排序）查询排序Oracle 10g数据库基础教程p按表达式排序 n可以按特定的表达式进行排序。可以按特定的表达式进行排序。nSELECT empno,ename

14、,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 salary FROM emp ORDER BY 2;Oracle 10g数据库基础教程（4）查询统计）查询统计函数格式功能AVGAVG(DI

15、STINCT|ALL)计算一列值的平均值（要求数值列）COUNTCOUNT(DISTINCT|ALL*)统计元组个数COUNTCOUNT(DISTINCT|ALL)统计一列中 非空值的个数MAXMAX(DISTINCT|ALL)求一列值中的最大值MINMIN(DISTINCT|ALL)求一列值中的最小值SUMSUM(DISTINCT|ALL)计算一列值的总和（要求数值列）STDDEV STDDEV().计算一列值的标准差VARIANCEVARIANCE()计算一列值的方差Oracle 10g数据库基础教程p注意n除了除了COUNT(*)函数外，其他的统计函数都不考虑返函数外，其他的统计函数都不

16、考虑返回值或表达式为回值或表达式为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 10g数据库基础教程13.2.3 分组查询分组查询p基本语法p单列分组查询p多列分组查询p使用HAVING子句限制返回组p使用ROLLUP 和CUBEp合并分组查询

18、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注意：nGROUP BY子句用于指定分组列或分组表达式。子句用于指定分组列或分组表达式。n集合函数用于对分组进行统计。如果未对查询分组，集合函数用于对分组进行统计。如果未对查询分组，则

19、集合函数将作用于整个查询结果；如果对查询结果则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。都有一个集合函数。nHAVING子句用于限制分组的返回结果。子句用于限制分组的返回结果。nWHERE子句对表中的记录进行过滤，而子句对表中的记录进行过滤，而HAVING子子句对分组后形成的组进行过滤。句对分组后形成的组进行过滤。n在分组查询中，在分组查询中，SELECT子句后面的所有目标列或目子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是标表达式要么是分组列，要么是分组表达

20、式，要么是集合函数。集合函数。Oracle 10g数据库基础教程p单列分组查询n将查询出来的记录按照某一个指定的列进行分组将查询出来的记录按照某一个指定的列进行分组nSELECT deptno,count(*),avg(sal)FROM emp GROUP BY deptno;p多列分组查询n在在GROUP BY子句中指定了两个或多个分组列子句中指定了两个或多个分组列nSELECT deptno,job,count(*),avg(sal)FROM emp GROUP BY deptno,job;p使用HAVING子句限制返回组n可以使用可以使用HAVING子句，只有满足条件的组才会返回。子句，

21、只有满足条件的组才会返回。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子句中使用子句中使用CUBE选项，则还可以选项，则还可以生成横向统计、纵向统计和不分组统计。生成横向统计、纵向统计和不分组统计。nSELECT deptno,job,avg(sal)F

22、ROM 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(sal)FROM emp GROUP BY GROUPING SETS(deptno,job);Oracle 10g数据库基础教程13.2.4 连接查询连接查询p交叉连接p内连接n等值连接

23、等值连接n不等值连接不等值连接n自身连接自身连接p外连接n左外连接左外连接n右外连接右外连接n全外连接全外连接Oracle 10g数据库基础教程（1）交叉连接）交叉连接p概念n两个或多个表之间的无条件连接。一个表中所有记录两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的分别与其他表中所有记录进行连接。如果进行连接的表中分别有表中分别有n1，n2，n3条记录，那么交叉连接的条记录，那么交叉连接的结果集中将有结果集中将有n1n2n3条记录。条记录。p以下情况可以出现交叉连接n连接条件省略连接条件省略n连接条件非法连接条件非法n一个表的所有行被连接到另一个

24、表的所有行一个表的所有行被连接到另一个表的所有行p示例nSELECT ename,dname from emp,dept;Oracle 10g数据库基础教程（2）内连接）内连接p执行过程p内连接语法p等值内连接p非等值内连接p自身内连接Oracle 10g数据库基础教程p执行过程n首先在表首先在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表逐一查找满足连接条件的元组，找到后就将表1中的第中的第1个元组与该元组拼接形成结果表中的一个元组。表个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表全部找完后，再找表1

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

26、acle扩展的连接方式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=dept.deptno;nSELECT empno,ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno=10 AND emp.deptn

27、o=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,manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno;nSELECT worker.empno,wor

28、ker.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,table2.column,nFROM table1 LEFT JOIN table2,nON table1.column table2.column,;p左外连接语法：Oracle扩展的连接方式nSEL

29、ECT 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 empnON dept.deptno=emp.deptno AND dept.deptno=10;p或nSELECT dname,empno,enamenFROM dept,empnWHERE dept.deptno=

30、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扩展的连接方式nSELECT table1.column,table2.column,nFROM table1,table2,nWHERE table1.column(+)n table2.column;Oracle 10g数据

31、库基础教程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 dept,empnWHERE dept.deptno(+)=emp.deptno AND n dept.deptno(+)=20;Oracle 10g数据库基础教程p全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入

32、结果集中。p在Oracle数据库中，全外连接的表示方式为nSELECT table1.column,table2.column,nFROM table1 FULL JOIN table2,nON table1.column1=table2.column2;p查询所有的部门名和员工名，语句为nSELECT dname,ename nFROM emp FULL JOIN dept nON emp.deptno=dept.deptno;Oracle 10g数据库基础教程13.2.5 子查询子查询p子查询概述p单行单列子查询p多行单列子查询p单行多列子查询p多行多列子查询p相关子查询p在FROM子句中

33、使用子查询 p在DDL语句中使用子查询p使用WITH子句的子查询Oracle 10g数据库基础教程p子查询的概念n子查询是指嵌套在其他子查询是指嵌套在其他SQL语句中的语句中的SELECT语句，也语句，也称为嵌套查询称为嵌套查询。n在执行时，由里向外，先处理子查询，再将子查询的返在执行时，由里向外，先处理子查询，再将子查询的返回结果用于其父语句（外部语句）的执行。回结果用于其父语句（外部语句）的执行。p子查询作用n在在INSERT或或CREATE TABLE语句中使用子查询，可语句中使用子查询，可以将子查询的结果写入到目标表中；以将子查询的结果写入到目标表中；n在在UPDATE语句中使用子查询

34、可以修改一个或多个记录语句中使用子查询可以修改一个或多个记录的数据；的数据；n在在DELETE语句中使用子查询可以删除一个或多个记录语句中使用子查询可以删除一个或多个记录n在在WHERE和和HAVING子句中使用子查询可以返回的子句中使用子查询可以返回的一个或多个值。一个或多个值。（1）子查询概述）子查询概述Oracle 10g数据库基础教程p单行单列子查询是指子查询只返回一行数据，而且只返回一列的数据。p运算符n=，=，n (SELECT sal FROM emp WHERE empno=7934);（2）单行单列子查询）单行单列子查询Oracle 10g数据库基础教程（3）多行单列子查询）

35、多行单列子查询p多行单列子查询是指返回多行数据，且只返回一列的数据。p运算符号运运 算算 符符含含 义义IN与子查询返回结果中任何一个值相等与子查询返回结果中任何一个值相等NOT IN与子查询返回结果中任何一个值都不等与子查询返回结果中任何一个值都不等ANY比子查询返回结果中某一个值大比子查询返回结果中某一个值大=ANY与子查询返回结果中某一个值相等与子查询返回结果中某一个值相等ALL比子查询返回结果中所有值都大比子查询返回结果中所有值都大ANY n (SELECT sal FROM emp WHERE deptno=10);p查询比10号部门所有员工工资高的员工信息。nSELECT empn

36、o,ename,sal FROM empnWHERE sal ALL n (SELECT sal FROM emp WHERE deptno=10);Oracle 10g数据库基础教程（4）单行多列子查询）单行多列子查询p单行多列子查询是指子查询返回一行数据，但是包含多列数据。p多列数据进行比较时，可以成对比较，也可以非成对比较。成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。Oracle 10g数据库基础教程p查询与7844号员工的工资、工种都相同的员工的信息。nSELECT empno,ename,sal,job FROM emp nWHERE(sal,jo

37、b)=(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 ndeptno=10)AND job IN(SELECT job FROM emp nWHERE deptno=10);Oracle 10g数据库基础教程（5）多行多列子查询）多行多列子查询p多行多列子查询是指子查询返回多行数据，并且是多列数据。p例如，查询与10号部门

38、某个员工的工资和工种都相同的员工的信息，语句为nSELECT empno,ename,sal,job FROM empnWHERE(sal,job)IN(nSELECT sal,job FROM emp WHERE deptno=10);Oracle 10g数据库基础教程（6）相关子查询）相关子查询p子查询在执行时并不需要外部父查询的信息，这种子查询称为无关子查询。p如果子查询在执行时需要引用外部父查询的信息，那么这种子查询就称为相关子查询。p在相关子查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子查询返回结果，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。O

39、racle 10g数据库基础教程p查询没有任何员工的部门号、部门名。nSELECT deptno,dname,loc FROM dept nWHERE NOT EXISTS(SELECT*FROM emp WHERE nemp.deptno=dept.deptno);p查询比本部门平均工资高的员工信息。nSELECT empno,ename,sal nFROM emp e nWHERE sal(SELECT avg(sal)FROM emp WHERE ndeptno=e.deptno);Oracle 10g数据库基础教程（7）在）在FROM子句中使用子查询子句中使用子查询p当在FROM子句中

40、使用子查询时，该子查询被作为视图对待，必须为该子查询指定别名。p查询各个员工的员工号、员工名及其所在部门平均工资。nSELECT empno,ename,d.avgsal FROM emp,n(SELECT deptno,avg(sal)avgsal FROM emp GROUP nBY deptno)d WHERE emp.deptno=d.deptno;p查询各个部门号、部门名、部门人数及部门平均工资。nSELECT dept.deptno,dname,d.amount,d.avgsal nFROM dept,(SELECT deptno,count(*)amount,navg(sal)a

41、vgsal FROM emp GROUP BY deptno)d nWHERE dept.deptno=d.deptno;Oracle 10g数据库基础教程（8）在）在DDL语句中使用子查询语句中使用子查询p可以在CREATE TABLE和CREATE VIEW语句中使用子查询来创建表和视图。nCREATE TABLE emp_subquery nAS nSELECT empno,ename,sal FROM emp;nCREATE VIEW emp_view_subquerynASnSELECT*FROM emp WHERE sal2000;Oracle 10g数据库基础教程（9）使用）使用

42、WITH子句的子查询子句的子查询p如果在一个SQL语句中多次使用同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。p查询人数最多的部门的信息。nSELECT*FROM deptnWHERE deptno IN(nSELECT deptno FROM emp GROUP BY deptno nHAVING count(*)=ALL(n SELECT count(*)FROM emp GROUP BY deptno)n);Oracle 10g数据库基础教程p相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。nWITH

43、deptinfo AS(SELECT deptno,count(*)num nFROM emp GROUP BY deptno)nSELECT*FROM dept WHERE deptno IN(SELECT ndeptno FROM deptinfo WHERE num=(SELECT nmax(num)FROM deptinfo)n);Oracle 10g数据库基础教程13.2.6 合并查询合并查询p语法pUNIONpINTERSECTpMINUSOracle 10g数据库基础教程p语法：SELECT query_statement1 UNION|UNION ALL|INTERSECT|M

44、INUS SELECT query_statement2;p注意：n当要合并几个查询的结果集时，这几个查询的结果集当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。必须具有相同的列数与数据类型。n如果要对最终的结果集排序，只能在最后一个查询之如果要对最终的结果集排序，只能在最后一个查询之后用后用ORDER BY子句指明排序列。子句指明排序列。Oracle 10g数据库基础教程（1）UNIONpUNION运算符用于获取几个查询结果集的并集，将重复的记录只保留一个，并且默认按第一列进行排序。p查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的员工

45、号、员工名、工资和部门号，语句为nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=10nUNIONnSELECT empno,ename,sal,deptno nFROM emp WHERE sal2000nORDER BY deptno;Oracle 10g数据库基础教程p如果要保留所有的重复记录，则需要使用UNION ALL运算符。nSELECT empno,ename,sal,deptnonFROM emp WHERE deptno=10nUNION ALLnSELECT empno,ename,sal,deptno nFROM

46、emp WHERE sal2000nORDER BY deptno;Oracle 10g数据库基础教程（2）INTERSECTpINTERSECT用于获取几个查询结果集的交集，只返回同时存在于几个查询结果集中的记录。同时，返回的最终结果集默认按第一列进行排序。p查询30号部门中工资大于2000的员工号、员工名、工资和部门号，语句为：nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=30nINTERSECTnSELECT empno,ename,sal,deptno nFROM EMP WHERE sal2000;Oracle 10g数据

47、库基础教程（3）MINUSpMINUS用于获取几个查询结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存在的记录。同时，返回的最终结果集默认按第一列进行排序。p查询30号部门中工种不是“SALESMAN”的员工号、员工名和工种名称，语句为：nSELECT 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数

48、据库基础教程13.3.1 插入数据插入数据p插入单行记录p利用子查询插入数据p向多个表中插入数据Oracle 10g数据库基础教程（1）插入插入单行记录单行记录p语法nINSERT INTO table_name|view_name n(column1,column2)nVALUES(value1,values,)p注意n如果在如果在INTO子句中没有指明任何列名，则子句中没有指明任何列名，则VALUES子句中列值子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。n如果在如果在INTO子句中指定了列名，则子句中指定了列名，

49、则VALUES子句中提供的列值子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。应。n向表或视图中插入的数据必须满足表的完整性约束。向表或视图中插入的数据必须满足表的完整性约束。n字符型和日期型数据在插入时要加单引号。日期类型数据需要按字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用系统默认格式输入，或使用TO_DATE函数进行日期转换。函数进行日期转换。Oracle 10g数据库基础教程p向dept表中插入一行记录。nINSERT INTO dept VALUES(50,IM,d

50、alian);p向emp表中插入一行记录。nINSERT INTO emp(empno,ename,sal,hiredate)nVALUES(1234,JOAN,2500,20-4月月-2007);Oracle 10g数据库基础教程（2）利用子查询插入数据）利用子查询插入数据 p语法nINSERT INTO table_name|view_namen(column1,column2,)nsubquery;p注意nINTO子句中指定的列的个数、顺序、类型必须与子子句中指定的列的个数、顺序、类型必须与子查询中列的个数、顺序和类型相匹配。查询中列的个数、顺序和类型相匹配。Oracle 10g数据库基

51、础教程p统计各个部门的部门号、部门最高工资和最低工资，并将统计的结果写入到表emp_salary（假设该表已经创建）中。nINSERT INTO emp_salary nSELECT deptno,max(sal),min(sal)FROM 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

52、数据库基础教程p如果要将大量数据插入表中，可以利用子查询直接装载的方式进行。由于直接装载数据的操作过程不写入日志文件，因此数据插入操作的速度大大提高。p利用子查询装载数据语法为：nINSERT/*+APPEND*/INTO ntable_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

53、from emp;Oracle 10g数据库基础教程13.3.2 修改数据修改数据p基本语法p修改单行记录p修改多行记录p带有子查询的修改Oracle 10g数据库基础教程p基本语法nUPDATE table_name|view_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

54、 emp SET sal=300+(SELECT avg(sal)nFROM emp WHERE deptno=10)nWHERE deptno=30;Oracle 10g数据库基础教程13.3.3 MERGE语句语句p利用MERGE语句可以同时完成数据的插入与更新操作。p将源表的数据分别与目标表中的数据根据特性条件进行比较（每次只比较一条记录），如果匹配，则利用源表中的记录更新目标表中的记录，如果不匹配，则将源表中的记录插入目标表中。p使用MERGE语句操作时，用户需要具有源表的SELECT对象权限以及目标表的INSERT，UPDATE对象权限。Oracle 10g数据库基础教程pMERGE

55、语句的基本语法为：nMERGE INTO schema.target_table target_alias nUSING schema.source_table|source_view|nsource_subquery 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

56、(expresstion1,expression2)n where_clause;Oracle 10g数据库基础教程p参数说明nINTO：指定进行数据更新或插入的目标表；：指定进行数据更新或插入的目标表；nUSING：指定用于目标表数据更新或插入的源表或视图或子查：指定用于目标表数据更新或插入的源表或视图或子查询；询；nON：决定：决定MERGE语句执行更新操作还是插入操作的条件。对于语句执行更新操作还是插入操作的条件。对于目标表中满足条件的记录，则利用源表中的相应记录进行更新；目标表中满足条件的记录，则利用源表中的相应记录进行更新；而源表中不满条件的记录将被插入目标表中；而源表中不满条件的记

57、录将被插入目标表中；nwhere_clause：只有当该条件为真时才进行数据的更新或插：只有当该条件为真时才进行数据的更新或插入操作；入操作；nDELETE where_clause：当目标表中更新后的记录满足该条：当目标表中更新后的记录满足该条件时，则删除该记录。件时，则删除该记录。Oracle 10g数据库基础教程p使用“雇员”表（别名e）作为数据源，在该表 的副本“副本_雇员”表（别名c）中插入并更新行。pMERGE INTO 副本_雇员 c USING 雇员epON(c.雇员标识=e.雇员标识)pWHEN MATCHED THEN UPDATEpSETpc.姓氏=e.姓氏,pc.部门标

58、识=e.部门标识pWHEN NOT MATCHED THEN INSERT pVALUES(e.雇员标识,e.姓氏,e.部门标识);Oracle 10g数据库基础教程MERGE INTO 副本_雇员 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 执行后的“副本

59、_雇员”雇员标识 姓氏部门标识100King90103Hunold 60152Davies 50雇员（来源表）雇员标识 姓氏部门标识100King90103Hunold 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 TO

60、M 30 Oracle 10g数据库基础教程pSELECT*FROM target_emp;pEMPNO ENAME DEPTNOp-p100 MARRY 20p20 JACK 40 pMERGE INTO target_emp tp USING source_emp sp ON(t.empno=s.empno)p WHEN MATCHED THEN UPDATE SETp t.ename=s.ename,t.deptno=s.deptnop WHEN NOT MATCHED THEN INSERTp VALUES(s.empno,s.ename,s.deptno)；Oracle 10g数据库

61、基础教程pSELECT*FROM target_emp;pEMPNO ENAME DEPTNOp-p100 JOAN 10p20 JACK 40p110 SMITH 20p120 TOM 30 Oracle 10g数据库基础教程13.3.4删除数据删除数据p基本语法p删除单条记录p删除多条记录p带有子查询的删除操作p利用TRUNCATE删除数据pTRUNCATE与DELETE区别Oracle 10g数据库基础教程p基本语法n DELETE FROM table|view WHERE condition p删除单条记录nDELETE FROM emp WHERE empno=7844;p删除多条

62、记录nDELETE FROM emp WHERE deptno=10;p带有子查询的删除操作nDELETE FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7900);Oracle 10g数据库基础教程p利用TRUNCATE删除数据nTRUNCATE TABLE table_namepTRUNCATE与DELETE区别n释放存储空间释放存储空间n不写入日志文件，因此执行效率较高，但该操作不可不写入日志文件，因此执行效率较高，但该操作不可回滚。回滚。Oracle 10g数据库基础教程13.4事务处理事务处理p事务概述pOracle事务处理Or

63、acle 10g数据库基础教程13.4.1 事务概述事务概述p原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。p一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。p隔离性（Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。p持久性（Durability）：一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。Oracle 10g数据库基础教程13.4.2 Or

64、acle事务处理事务处理 p事务提交方式n用户显式执行用户显式执行COMMIT命令命令n执行特定操作时系统自动提交。执行特定操作时系统自动提交。p说明n当事务提交后，用户对数据库修改操作的日志信息由当事务提交后，用户对数据库修改操作的日志信息由日志缓冲区写入重做日志文件中，释放该事务所占据日志缓冲区写入重做日志文件中，释放该事务所占据的系统资源和数据库资源。此时，其他会话可以看到的系统资源和数据库资源。此时，其他会话可以看到该事务对数据库的修改结果。该事务对数据库的修改结果。n当执行当执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONN

65、ECT等命令时，系统将自动提交。等命令时，系统将自动提交。Oracle 10g数据库基础教程p事务回滚方式n事务全部回滚事务全部回滚 pROLLBACKn事务部分回滚事务部分回滚pSAVEPOINT XpROLLBACK TO XOracle 10g数据库基础教程pROLLBACK TO B;（回滚最后一个INSERT操作）pROLLBACK TO A;（回滚后面的INSERT操作和UPDATE操作）pROLLBACK;（回滚全部操作）Oracle 10g数据库基础教程13.5 SQL函数函数pSQL函数分类p数值函数p字符函数p日期函数p转换函数p其他函数Oracle 10g数据库基础教程1

66、3.5.1 SQL函数分类函数分类p根据参数作用行数的不同，可以分为：n单行函数单行函数n多行函数多行函数p根据参数类型不同，可以分为：n数值函数数值函数n字符函数字符函数n日期函数日期函数n转换函数转换函数n聚集函数聚集函数 Oracle 10g数据库基础教程13.5.2 数值函数数值函数函数返回值ABS(n)返回n的绝对值CEIL(n)返回大于或等于n的最小整数EXP(n)返回e的n次幂FLOOR(n)返回小于或等于n的最大整数LN(n)返回以E为底的n的对数LOG(m,n)返回以m为底的n的对数MOD(m,n)返回m除以n的余数POWER(m,n)返回m的n次方Oracle 10g数据库基础教程ROUND(m,n)对m进行四舍五入（n大于0时，将m四舍五入到小数点右边n位，n等于零时，表示对m进行取整，n小于0时，则小数点左边的数字位置被圆整。SIGN(n)判断n的正负（n大于0返回，n等于0返回0，n小于0返回-1SQRT(n)返回n的平方根SQUARE(n)返回n的平方TRUNC(m,n)对m进行截断操作（n截断到小数点后第n位，如果n未给出，则系统默认为0,n也可以为负数，