SQL语言基础资料介绍

《SQL语言基础资料介绍》由会员分享，可在线阅读，更多相关《SQL语言基础资料介绍（113页珍藏版）》请在装配图网上搜索。

1、第第13章章 SQL语言基础语言基础本章内容本章内容pSQL语句分类p数据查询（SELECT）p数据操纵（INSERT、UPDATE、DELETE）p事务控制pSQL函数本章要求本章要求p掌握数据查询的各种应用p掌握数据操纵的各种应用p掌握事务处理p了解SQL函数应用13.1 SQL语言概述语言概述pSQL语言介绍 pSQL语言的分类 pSQL语言的特点 13.1.1 SQL语言介绍语言介绍pSQL（StructuredQueryLanguage）语言是1974年由Boyce和Chamberlin提出的。pSQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制

2、等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过SQL语言实现对数据库的访问和操作。13.1.2 SQL语言的分类语言的分类p据定义语言（Data Definition Language，DDL）：用于定义、修改、删除数据库对象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。p数据操纵语言（Data Manipulation Language，DML）：用于改变数据库中的数据，包括数据插入（INSERT）、数据修改（UPDATE）和数据删除（DELETE）。p数据查询语言（Data Query Language，DQL）：用

3、于数据检索，包括SELECT。p事务控制（Transaction Control）：用于将一组DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（COMMIT）、事务回滚（ROLLBACK）、设置保存点（SAVEPOINT）和设置事务状态（SET TRANSACTION）。p系统控制（System Control）：用于设置数据库系统参数，包括ALTER SYSTEM。p会话控制（Session Control）：用于设置用户会话相关参数，包括ALTER SESSION。13.1.3 SQL语言的特点语言的特点p功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。p高度的非过程化

4、：在使用SQL语言操作数据库时，用户只需要说明“做什么”，而不需要说明“怎样做”。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。p面向集合的操作方式：SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式极大地提高了对数据操作效率。p多种使用方式：SQL语句既是自含式语言，又是嵌入式语言。SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。p简洁、易学：SQL语言命令数量有限，语法简单，接近于自然语言（英语），因此容易学习和掌握

5、。13.2 数据查询数据查询p数据查询基础p基本查询p分组查询p连接查询p子查询p合并查询 13.2.1 数据查询基础数据查询基础p基本语法：nSELECT ALL|DISTINCTcolumn_name,expressionnFROM table1_name,table2_name,view_name,nWHERE conditionnGROUP BY column_name1,column_name2,nHAVING group_conditionnORDER BY column_name2 ASC|DESCn,column_name2,;13.2.2 基本查询基本查询p无条件查询p有条件

6、查询p查询排序p查询统计（1）无条件查询）无条件查询p查询所有列nSELECT*FROM emp;p查询指定列nSELECT deptno,dname FROM dept;p使用算术表达式nSELECT empno,sal*0.8 FROM emp;p使用字符常量nSELECT empno,Name is:,ename FROM emp;p使用函数nSELECT empno,UPPER(ename)FROM emp;p改变列标题nSELECT ename employeename,sal salary FROM emp;p使用连接字符串nSELECT 员工号：员工号：|empno|员工名员工名

7、|ename FROM emp;p消除重复行nSELECT ALL deptno FROM emp;nSELECT DISTINCT deptno FROM emp;（2）有条件查询）有条件查询p查询满足条件的元组可以通过WHERE 子句实现。pWHERE条件中常用的运算符号运算符号谓词比较大小=,=,=,，!=确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，ORp关系运算 nSELECT empno,ename,sal FROM emp WHERE deptno

8、!=10;nSELECT empno,ename,sal FROM emp 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;p确定集合n谓词谓词IN可以用来查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。nSELECT empno,ename,sal FROM emp WHERE deptno IN(10,30);p字

9、符匹配n%（百分号）代表任意长（长度为（百分号）代表任意长（长度为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;p空值操作n涉及空值查询时使用涉及空值查询时使用IS NULL或或 IS NOT NULL，这里的这里的IS不能用不能用=替代。替代。nSELECT*FROM emp WHERE

10、 deptno IS NULL;nSELECT*FROM emp WHERE comm IS NOT NULL;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;p注意：n使用

11、使用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;p升序、降序排序nASC:升序升序（缺省）；（缺省）；DESC:降序降序nSELECT empno,ename,sal FROM emp ORDER BY sal;nSELECT emp

12、no,ename,sal FROM emp ORDER BY sal DESC;p多列排序 n首先按照第一个列或表达式进行排序；当第一个列或表达首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类式的数据相同时，以第二个列或表达式进行排序，以此类推推。nSELECT*FROM emp ORDER BY deptno,sal DESC;（3）查询排序）查询排序p按表达式排序 n可以按特定的表达式进行排序。可以按特定的表达式进行排序。nSELECT empno,ename,sal FROM emp ORDER BY sal*12;p使用别名排序 n可

13、以使用目标列或表达式的别名进行排序。可以使用目标列或表达式的别名进行排序。nSELECT empno,sal*12 salary FROM emp ORDER BY salary;p使用列位置编号排序 n如果列名或表达式名称很长，那么使用位置排序可以缩短如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。排序语句的长度。nSELECT empno,sal*12 salary FROM emp ORDER BY 2;（4）查询统计）查询统计函数格式功能AVGAVG(DISTINCT|ALL)计算一列值的平均值（要求数值列）COUNTCOUNT(DISTINCT|ALL*)统计元组个

14、数COUNTCOUNT(DISTINCT|ALL)统计一列中 非空值的个数MAXMAX(DISTINCT|ALL)求一列值中的最大值MINMIN(DISTINCT|ALL)求一列值中的最小值SUMSUM(DISTINCT|ALL)计算一列值的总和（要求数值列）STDDEV STDDEV().计算一列值的标准差VARIANCEVARIANCE()计算一列值的方差p注意n除了除了COUNT(*)函数外，其他的统计函数都不考虑返函数外，其他的统计函数都不考虑返回值或表达式为回值或表达式为NULL的情况。的情况。n聚集函数只能出现在目标列表达式、聚集函数只能出现在目标列表达式、ORDER BY子句、子

15、句、HAVING子句中，不能出现在子句中，不能出现在WHERE子句和子句和GROUP BY子句中。子句中。n默认对所有的返回行进行统计，包括重复的行；如果默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用要统计不重复的行信息，则可以使用DISTINCT选项。选项。n如果对查询结果进行了分组，则聚集函数的作用范围如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。为各个组，否则聚集函数作用于整个查询结果。pSELECT count(*),avg(sal),max(sal),min(sal)FROM emp WHERE deptno=

16、10;pSELECT avg(comm),sum(comm)FROM emp;pSELECT count(DISTINCT deptno)FROM emp;pSELECT variance(sal),stddev(sal)FROM emp;13.2.3 分组查询分组查询p基本语法p单列分组查询p多列分组查询p使用HAVING子句限制返回组p使用ROLLUP 和CUBEp合并分组查询nSELECT column,group_function,nFROM tablenWHERE conditionnGROUP BY ROOLUP|CUBE|GROUPING SETS ngroup_by_expre

17、ssionnHAVING group_conditionnORDER BY columnASC|DESC;（1）基本语法）基本语法p注意：nGROUP BY子句用于指定分组列或分组表达式。子句用于指定分组列或分组表达式。n集合函数用于对分组进行统计。如果未对查询分组，集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。都有一个集合函数。nHAVING子句用于限制分组的返回结果。子句用于限制分组的返回结

18、果。nWHERE子句对表中的记录进行过滤，而子句对表中的记录进行过滤，而HAVING子子句对分组后形成的组进行过滤。句对分组后形成的组进行过滤。n在分组查询中，在分组查询中，SELECT子句后面的所有目标列或目子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是标表达式要么是分组列，要么是分组表达式，要么是集合函数。集合函数。p单列分组查询n将查询出来的记录按照某一个指定的列进行分组将查询出来的记录按照某一个指定的列进行分组nSELECT deptno,count(*),avg(sal)FROM emp GROUP BY deptno;p多列分组查询n在在GROUP BY子句

19、中指定了两个或多个分组列子句中指定了两个或多个分组列nSELECT deptno,job,count(*),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;p使用ROLLUP 和CUBEn如果在如果在GROUP BY子句中使用子句中使用ROLLUP选项，则还选项，则还可以生成横向统计和不分组统

20、计；可以生成横向统计和不分组统计；n如果在如果在GROUP BY子句中使用子句中使用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);p合并分组查询n使用使用GROUPING SETS可以将几个单独的分组查询可以将几个单独的分组查询合并成一个分组查询合并成一个分组查询 nSELECT de

21、ptno,job,avg(sal)FROM emp GROUP BY GROUPING SETS(deptno,job);13.2.4 连接查询连接查询p交叉连接p内连接n等值连接等值连接n不等值连接不等值连接n自身连接自身连接p外连接n左外连接左外连接n右外连接右外连接n全外连接全外连接（1）交叉连接）交叉连接p概念n两个或多个表之间的无条件连接。一个表中所有记录两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的分别与其他表中所有记录进行连接。如果进行连接的表中分别有表中分别有n1，n2，n3条记录，那么交叉连接的条记录，那么交叉连接的结果集中将有结

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

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

24、ON condition;p内连接语法：Oracle扩展的连接方式nSELECT table1.column,talbe2.column,nFROM table1,table2,nWHERE condition;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.

25、deptno=dept.deptno;p非等值内连接nSELECT empno,ename,sal,grade FROM emp JOIN salgrade ON sallosal AND sallosal AND salhisal;p自身内连接 nSELECT worker.empno,worker.ename,manager.empno,manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno;nSELECT worker.empno,worker.ename,manager.empno,manag

26、er.ename FROM emp worker,emp manager WHERE worker.mgr=manager.empno;（3）外连接）外连接p左外连接p右外连接p全外连接p左外连接语法：标准SQL语句的连接方式nSELECT table1.column,table2.column,nFROM table1 LEFT JOIN table2,nON table1.column table2.column,;p左外连接语法：Oracle扩展的连接方式nSELECT table1.column,table2.column,nFROM table1,table2,nWHERE tabl

27、e1.column n table2.column(+);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=emp.deptno(+)AND n emp.deptno(+)=10;p右外连接语法：标准SQL语句的连接方式nSELECT table1.column,ta

28、ble2.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;p查询20号部门的部门名称及其员工号、员工名，和所有其他部门的员工名、员工号，语句为nSELECT empno,ename,dname nFROM dept RIGHT JOIN empnON dept.deptno=

29、emp.deptno AND n dept.deptno=20;p或nSELECT empno,ename,dname nFROM dept,empnWHERE dept.deptno(+)=emp.deptno AND n dept.deptno(+)=20;p全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。p在Oracle数据库中，全外连接的表示方式为nSELECT table1.column,table2.column,nFROM table1 FULL JOIN table2,nON table1.column1=table2.column2;p查询

30、所有的部门名和员工名，语句为nSELECT dname,ename nFROM emp FULL JOIN dept nON emp.deptno=dept.deptno;13.2.5 子查询子查询p子查询概述p单行单列子查询p多行单列子查询p单行多列子查询p多行多列子查询p相关子查询p在FROM子句中使用子查询 p在DDL语句中使用子查询p使用WITH子句的子查询p子查询的概念n子查询是指嵌套在其他子查询是指嵌套在其他SQL语句中的语句中的SELECT语句，也语句，也称为嵌套查询称为嵌套查询。n在执行时，由里向外，先处理子查询，再将子查询的返在执行时，由里向外，先处理子查询，再将子查询的返回

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

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

33、回结果中所有值都大比子查询返回结果中所有值都大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);（4）单行多列子查询）单行多列子查询p单行多列子查询是指子查询返回一行数据，但是包含多列数据。p多列数据进行比较时，可以成对比较，也可以非成对比较。成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。p查询与7844号员

34、工的工资、工种都相同的员工的信息。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 ndeptno=10)AND job IN(SELECT job FROM emp nWHERE deptno=10);（5）多行多列子

35、查询）多行多列子查询p多行多列子查询是指子查询返回多行数据，并且是多列数据。p例如，查询与10号部门某个员工的工资和工种都相同的员工的信息，语句为nSELECT empno,ename,sal,job FROM empnWHERE(sal,job)IN(nSELECT sal,job FROM emp WHERE deptno=10);（6）相关子查询）相关子查询p子查询在执行时并不需要外部父查询的信息，这种子查询称为无关子查询。p如果子查询在执行时需要引用外部父查询的信息，那么这种子查询就称为相关子查询。p在相关子查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子查询返回结果

36、，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。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);（7）在）在FROM子句中使用子查询子句中使用子查询p当在FROM子句中使

37、用子查询时，该子查询被作为视图对待，必须为该子查询指定别名。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)av

38、gsal FROM emp GROUP BY deptno)d nWHERE dept.deptno=d.deptno;（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;（9）使用）使用WITH子句的子查询子句的子查询p如果在一个SQL语句中多次使用同一个

39、子查询，可以通过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);p相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。nWITH deptinfo AS(SELECT deptno,count(*)num nFROM emp GROU

40、P BY deptno)nSELECT*FROM dept WHERE deptno IN(SELECT ndeptno FROM deptinfo WHERE num=(SELECT nmax(num)FROM deptinfo)n);13.2.6 合并查询合并查询p语法pUNIONpINTERSECTpMINUSp语法：SELECT query_statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT query_statement2;p注意：n当要合并几个查询的结果集时，这几个查询的结果集当要合并几个查询的结果集时，这几个查询的结果集必须具有相同

41、的列数与数据类型。必须具有相同的列数与数据类型。n如果要对最终的结果集排序，只能在最后一个查询之如果要对最终的结果集排序，只能在最后一个查询之后用后用ORDER BY子句指明排序列。子句指明排序列。（1）UNIONpUNION运算符用于获取几个查询结果集的并集，将重复的记录只保留一个，并且默认按第一列进行排序。p查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的员工号、员工名、工资和部门号，语句为nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=10nUNIONnSELECT empno,ename,sal,d

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

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

44、OM emp WHERE deptno=30nMINUSnSELECT empno,ename,job nFROM EMP WHERE job=SALESMAN;13.3 数据操纵数据操纵p插入数据p修改数据pMERGE语句 p删除数据13.3.1 插入数据插入数据p插入单行记录p利用子查询插入数据p向多个表中插入数据（1）插入插入单行记录单行记录p语法nINSERT INTO table_name|view_name n(column1,column2)nVALUES(value1,values,)p注意n如果在如果在INTO子句中没有指明任何列名，则子句中没有指明任何列名，则VALUES子

45、句中列值子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。n如果在如果在INTO子句中指定了列名，则子句中指定了列名，则VALUES子句中提供的列值子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。应。n向表或视图中插入的数据必须满足表的完整性约束。向表或视图中插入的数据必须满足表的完整性约束。n字符型和日期型数据在插入时要加单引号。日期类型数据需要按字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用系统默认格式

46、输入，或使用TO_DATE函数进行日期转换。函数进行日期转换。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);（2）利用子查询插入数据）利用子查询插入数据 p语法nINSERT INTO table_name|view_namen(column1,column2,)nsubquery;p注意nINTO子句中指定的列的个数、顺序、类型必须与子子句中指定的列

47、的个数、顺序、类型必须与子查询中列的个数、顺序和类型相匹配。查询中列的个数、顺序和类型相匹配。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,

48、deptnonFROM emp WHERE ename=SCOTT;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)nSEL

49、ECT empno,ename,sal,deptno from emp;13.3.2 修改数据修改数据p基本语法p修改单行记录p修改多行记录p带有子查询的修改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 emp SE

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

51、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 (expresstion1,expression2)n where_clause;

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

53、的更新或插入操作；入操作；nDELETE where_clause：当目标表中更新后的记录满足该条：当目标表中更新后的记录满足该条件时，则删除该记录。件时，则删除该记录。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.部门标识);MERGE INT

54、O 副本_雇员 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雇员（来源表）雇员标识 姓氏部门标识100King90103Hunold 60152Da

55、vies 50p现有表source_emp和target_emp，表中数据如下。利用source_emp表中的数据更新target_emp表中的数据，对target_emp表中存在的员工信息进行更新，对不存在的员工进行信息插入。nSELECT*FROM source_emp;nEMPNO ENAME DEPTNOn-n100 JOAN 10n110 SMITH 20n120 TOM 30 pSELECT*FROM target_emp;pEMPNO ENAME DEPTNOp-p100 MARRY 20p20 JACK 40 pMERGE INTO target_emp tp USING so

56、urce_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)；pSELECT*FROM target_emp;pEMPNO ENAME DEPTNOp-p100 JOAN 10p20 JACK 40p110 SMITH 20p120 TOM 30 13.3.4删除数据删除数据p基本语法p删除单条记录p删除多条记录p带有子查询的删除操作p利

57、用TRUNCATE删除数据pTRUNCATE与DELETE区别p基本语法n DELETE FROM table|view WHERE condition p删除单条记录nDELETE FROM emp WHERE empno=7844;p删除多条记录nDELETE FROM emp WHERE deptno=10;p带有子查询的删除操作nDELETE FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7900);p利用TRUNCATE删除数据nTRUNCATE TABLE table_namepTRUNCATE与DELETE区别n释放存储空间

58、释放存储空间n不写入日志文件，因此执行效率较高，但该操作不可不写入日志文件，因此执行效率较高，但该操作不可回滚。回滚。13.4事务处理事务处理p事务概述pOracle事务处理13.4.1 事务概述事务概述p原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。p一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。p隔离性（Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。p持久性（Durability）：一个事务一

59、旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。13.4.2 Oracle事务处理事务处理 p事务提交方式n用户显式执行用户显式执行COMMIT命令命令n执行特定操作时系统自动提交。执行特定操作时系统自动提交。p说明n当事务提交后，用户对数据库修改操作的日志信息由当事务提交后，用户对数据库修改操作的日志信息由日志缓冲区写入重做日志文件中，释放该事务所占据日志缓冲区写入重做日志文件中，释放该事务所占据的系统资源和数据库资源。此时，其他会话可以看到的系统资源和数据库资源。此时，其他会话可以看到该事务对数据库的修改结果。该事务对数据库的修改结果。n当执行当

60、执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令时，系统将自动提交。等命令时，系统将自动提交。p事务回滚方式n事务全部回滚事务全部回滚 pROLLBACKn事务部分回滚事务部分回滚pSAVEPOINT XpROLLBACK TO XpROLLBACK TO B;（回滚最后一个INSERT操作）pROLLBACK TO A;（回滚后面的INSERT操作和UPDATE操作）pROLLBACK;（回滚全部操作）13.5 SQL函数函数pSQL函数分类p数值函数p字符函数p日期函数p转换函数p其他函数13.5.1 SQL函数分类

61、函数分类p根据参数作用行数的不同，可以分为：n单行函数单行函数n多行函数多行函数p根据参数类型不同，可以分为：n数值函数数值函数n字符函数字符函数n日期函数日期函数n转换函数转换函数n聚集函数聚集函数 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次方ROUND(m,n)对m进行四舍五入（n大于0时，将m四舍五入到小数点右边n位，n等于

62、零时，表示对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也可以为负数，表示小数点左边的数字位置被删除成零。pSELECT sal/22 daysal,round(sal/22,1),trunc(sal/22,1),round(sal/22,-1),trunc(sal/22,-1)FROM emp;pDAYSAL ROUND(SAL/22,1)TRUNC(SAL/

63、22,1)ROUND(SAL/22,-1)TRUNC(SAL/22,-1)-45.4545455 45.5 45.4 50 40 90.9090909 90.9 90.9 90 90 54.5454545 54.5 54.5 50 50 86.3636364 86.4 86.3 90 80 70.4545455 70.5 70.4 70 70 146.590909 146.6 146.5 150 140 pSELECT sal,width_bucket(sal,1000,5000,10)FROM emp WHERE deptno=30;pSAL WIDTH_BUCKET(SAL,1000,50

64、00,10)-1700 2 1350 1 2950 5 6100 11 1050 1pSELECT floor(3.5),ceil(3.5),mod(5,3),remainder(5,3),mod(4,3),remainder(4,3)FROM dual;pFLOOR(3.5)CEIL(3.5)MOD(5,3)REMAINDER(5,3)MOD(4,3)REMAINDER(4,3)-3 4 2 -1 1 113.5.3 字符函数字符函数函数返回值ASCII(char)字符串首字符的ASC码值CHR(n)ASC码值为n的字符CONCAT把两个列值拼接起来；“|”操作符更通用INITCAP(cha

65、r)将字符串中每个单词的首字母大写INSTR（char1,char2,a,b)INSTRB（char1,char2,a,b)返回指定字符的位置LENGTH(char)LENGTHB(char)计算字符串的长度SUBSTR(char,m,n)求子串。column中从起始位置m开始长度为n的子串Replace(str1,str2,str3)LOWER(char)、UPPER(char)将字符串中所有的大写字母变为小(大)写LPAD(char1,n,char2)从左侧用字符串char2补齐字符串char1至长度n（右对齐）RPAD(char1,n,char2)从右侧用char2补齐char1至长度n

66、.LTRIM(char,SET)把char中最左侧的若干个字符去掉，以使其首字符不在SET中RTRIM(char,SET)把char中最右侧的若干个字符去掉，以使其尾字符不在SET中TRIM(leading|trailing|both FROM string)pSELECT lpad(abc,5,#)leftpad,rpad(abc,5,#)rightpad,ltrim(abcd,a)lefttrim,rtrim(abcde,e)righttrim,substr(abcd,2,3)substring FROM dual;pLEFTPAD RIGHTPAD LEFTTRIM RIGHTTRIM SUBSTRING -#abc abc#bcd abcd bcd pSELECT concat(concat(ename,s job category is),job)Job FROM emp WHERE empno=7844;pJob -TURNERs job category is SALESMAN pSELECT instr(abcde,b)position,replace(oracle9i