Oracle第章SQL语言基础

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

Query

Language）语言是1974年由Boyce

和Chamberlin提出的。SQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过SQL语言实现对数据库的访问和操作。13.1.2SQL语言的分类据定义语言（DataDefinitionLanguage，DDL）：用于定义、修改、删除数据库对象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。数据操纵语言（DataManipulationLanguage，DML）：用于改变数据库中的数据，包括数据插入（INSERT）、数据修改（UPDATE）和数据删除（DELETE）。数据查询语言（DataQueryLanguage，DQL）：用于数据检索，包括SELECT。事务控制（TransactionControl）：用于将一组DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（COMMIT）、事务回滚（ROLLBACK）、设置保存点（SAVEPOINT）和设置事务状态（SETTRANSACTION）。系统控制（SystemControl）：用于设置数据库系统参数，包括ALTERSYSTEM。会话控制（SessionControl）：用于设置用户会话相关参数，包括ALTERSESSION。13.1.3SQL语言的特点功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。高度的非过程化：在使用SQL语言操作数据库时，用户只需要说明“做什么”，而不需要说明“怎样做”。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。面向集合的操作方式：SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式极大地提高了对数据操作效率。多种使用方式：SQL语句既是自含式语言，又是嵌入式语言。SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。简洁、易学：SQL语言命令数量有限，语法简单，接近于自然语言（英语），因此容易学习和掌握。

13.2数据查询数据查询基础基本查询分组查询连接查询子查询合并查询

13.2.1数据查询基础基本语法：SELECT[ALL|DISTINCT]column_name[,expression…]FROMtable1_name[,table2_name,view_name,…][WHEREcondition][GROUPBYcolumn_name1[,column_name2,…][HAVINGgroup_condition]][ORDERBYcolumn_name2[ASC|DESC][,column_name2,…]];

13.2.2基本查询无条件查询有条件查询查询排序查询统计（1）无条件查询询查询所有列SELECT*FROMemp;查询指定列SELECTdeptno,dnameFROMdept;使用算术表达达式SELECTempno,sal*0.8FROMemp;使用字符常量量SELECTempno,'Nameis:',enameFROMemp;使用函数SELECTempno,UPPER(ename)FROMemp;改变列标题SELECTenameemployeename,salsalaryFROMemp;使用连接字符符串SELECT'员工号：'||empno||'员工名'||enameFROMemp;消除重复行SELECTALLdeptnoFROMemp;SELECTDISTINCTdeptnoFROMemp;（2）有条件查询询查询满足条件件的元组可以以通过WHERE子句实现。WHERE条件中常用的的运算符号运算符号谓词比较大小=,>,<,>=,<=,<>，!=确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR关系运算SELECTempno,ename,salFROMempWHEREdeptno!=10;SELECTempno,ename,salFROMempWHEREsal>1500确定范围谓词BETWEENAND与NOTBETWEENAND。SELECT*FROMempWHEREdeptnoBETWEEN10AND20;SELECT*FROMempWHEREsalNOTBETWEEN1000AND2000;确定集合谓词IN可以用来查找找属性值属于于指定集合的的元组。SELECTempno,ename,salFROMempWHEREdeptnoIN(10,30);字符匹配%（百分号）代代表任意长（（长度为0）字符串。_（下划线）代代表任意单个个字符。ESCAPE：转义字符SELECT*FROMempWHEREenameLIKE‘%S%’；SELECT*FROMempWHEREenameLIKE'_A%';SELECT*FROMempWHEREenameLIKE'%x_%'ESCAPE'x';空值操作涉及空值查询询时使用ISNULL或ISNOTNULL，这里的IS不能用=替代。SELECT*FROMempWHEREdeptnoISNULL;SELECT*FROMempWHEREcommISNOTNULL;逻辑操作用逻辑运算符符NOT、AND和OR来联结多个查查询条件。优先级：NOT、AND、OR（用户可以用用括号改变优优先级）。IN谓词实际上是是多个OR运算的缩写。。SELECT*FROMempWHEREdeptno=10ANDsal>1500;SELECT*FROMempWHERE(deptno=10ORdeptno=20)ANDsal>1500;注意：使用BETWEEN…AND，NOTBETWEEN…AND，IN，NOTIN运算符的查询询条件都可以以转换为NOT，AND，OR的逻辑运算算。例如，，下面两个个语句是等等价的：SELECT*FROMempWHEREsal>1000ANDsal<2000;SELECT*FROMempWHEREsalBETWEEN1000AND2000;升序、降序序排序ASC:升序（缺缺省）；DESC:降序SELECTempno,ename,salFROMempORDERBYsal;SELECTempno,ename,salFROMempORDERBYsalDESC;多列排序首先按照第第一个列或或表达式进进行排序；；当第一个个列或表达达式的数据据相同时，，以第二个个列或表达达式进行排排序，以此此类推。。SELECT*FROMempORDERBYdeptno,salDESC;（3）查询排序序按表达式排排序可以按特定定的表达式式进行排序序。SELECTempno,ename,salFROMempORDERBYsal*12;使用别名排排序可以使用用目标列列或表达达式的别别名进行行排序。。SELECTempno,sal*12salaryFROMempORDERBYsalary;使用列位位置编号号排序如果列名名或表达达式名称称很长，，那么使使用位置置排序可可以缩短短排序语语句的长长度。SELECTempno,sal*12salaryFROMempORDERBY2;（4）查询统统计函数格式功能AVGAVG([DISTINCT|ALL]<列名>)计算一列值的平均值（要求数值列）COUNTCOUNT([DISTINCT|ALL]*)统计元组个数COUNTCOUNT([DISTINCT|ALL]<列名>)统计一列中非空值的个数MAXMAX([DISTINCT|ALL]<列名>)求一列值中的最大值MINMIN([DISTINCT|ALL]<列名>)求一列值中的最小值SUMSUM([DISTINCT|ALL]<列名>)计算一列值的总和（要求数值列）STDDEVSTDDEV(<列名>)..计算一列值的标准差VARIANCEVARIANCE(<列名>)计算一列值的方差注意除了COUNT(*)函数外，，其他的的统计函函数都不不考虑返返回值或或表达式式为NULL的情况。。聚集函数数只能出出现在目目标列表表达式、、ORDERBY子句、HAVING子句中，，不能出出现在WHERE子句和GROUPBY子句中。。默认对所所有的返返回行进进行统计计，包括括重复的的行；如如果要统统计不重重复的行行信息，，则可以以使用DISTINCT选项。如果对查查询结果果进行了了分组，，则聚集集函数的的作用范范围为各各个组，，否则聚聚集函数数作用于于整个查查询结果果。SELECTcount(*),avg(sal),max(sal),min(sal)FROMempWHEREdeptno=10;SELECTavg(comm),sum(comm)FROMemp;SELECTcount(DISTINCTdeptno)FROMemp;SELECTvariance(sal),stddev(sal)FROMemp;13.2.3分组查询询基本语法法单列分组组查询多列分组组查询使用HAVING子句限制制返回组组使用ROLLUP和CUBE合并分组组查询SELECTcolumn,group_function,…FROMtable[WHEREcondition][GROUP[BYROOLUP|CUBE|GROUPINGSETS]group_by_expression][HAVINGgroup_condition][ORDERBYcolumn[ASC|DESC]];（1）基本本语法法注意：：GROUPBY子句用用于指指定分分组列列或分分组表表达式式。集合函函数用用于对对分组组进行行统计计。如如果未未对查查询分分组，，则集集合函函数将将作用用于整整个查查询结结果；；如果果对查查询结结果分分组，，则集集合函函数将将作用用于每每一个个组，，即每每一个个分组组都有有一个个集合合函数数。HAVING子句用用于限限制分分组的的返回回结果果。WHERE子句对对表中中的记记录进进行过过滤，，而HAVING子句对对分组组后形形成的的组进进行过过滤。。在分组组查询询中，，SELECT子句后后面的的所有有目标标列或或目标标表达达式要要么是是分组组列，，要么么是分分组表表达式式，要要么是是集合合函数数。单列分分组查查询将查询询出来来的记记录按按照某某一个个指定定的列列进行行分组组SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptno;多列分分组查查询在GROUPBY子句中中指定定了两两个或或多个个分组组列SELECTdeptno,job,count(*),avg(sal)FROMempGROUPBYdeptno,job;使用HAVING子句限限制返返回组组可以使使用HAVING子句，，只有有满足足条件件的组组才会会返回回。SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptnoHAVINGavg(sal)>1500;使用ROLLUP和CUBE如果在在GROUPBY子句中中使用用ROLLUP选项，，则还还可以以生成成横向向统计计和不不分组组统计计；如果在在GROUPBY子句中中使用用CUBE选项，，则还还可以以生成成横向向统计计、纵纵向统统计和和不分分组统统计。。SELECTdeptno,job,avg(sal)FROMempGROUPBYROLLUP(deptno,job);SELECTdeptno,job,avg(sal)FROMempGROUPBYCUBE(deptno,job);合并分分组查查询使用GROUPINGSETS可以将将几个个单独独的分分组查查询合合并成成一个个分组组查询询SELECTdeptno,job,avg(sal)FROMempGROUPBYGROUPINGSETS(deptno,job);13.2.4连接查查询交叉连连接内连接接等值连连接不等值值连接接自身连连接外连接接左外连连接右外连连接全外连连接（1）交叉叉连接接概念两个或或多个个表之之间的的无条条件连连接。。一个个表中中所有有记录录分别别与其其他表表中所所有记记录进进行连连接。。如果果进行行连接接的表表中分分别有有n1，n2，n3…条记录录，那那么交交叉连连接的的结果果集中中将有有n1×n2×n3×…条记录录。以下情情况可可以出出现交交叉连连接连接条条件省省略连接接条条件件非非法法一个个表表的的所所有有行行被被连连接接到到另另一一个个表表的的所所有有行行示例例SELECTename,dnamefromemp,dept;（2）内内连连接接执行行过过程程内连连接接语语法法等值值内内连连接接非等等值值内内连连接接自身身内内连连接接执行行过过程程首先先在在表表1中找找到到第第一一个个元元组组，，然然后后从从头头开开始始扫扫描描表表2，逐逐一一查查找找满满足足连连接接条条件件的的元元组组，，找找到到后后就就将将表表1中的的第第1个元元组组与与该该元元组组拼拼接接形形成成结结果果表表中中的的一一个个元元组组。。表表2全部部找找完完后后，，再再找找表表1中的的第第2个元元组组，，然然后后再再从从头头扫扫描描表表2，逐逐一一查查找找满满足足连连接接条条件件的的元元组组，，找找到到后后就就将将表表1中的的第第2个元元组组与与该该元元组组拼拼接接形形成成结结果果表表中中的的一一个个元元组组。。重重复复执执行行，，直直到到表表1中的的全全部部元元组组都都处处理理完完毕毕为为止止。。内连连接接语语法法：：标标准准SQL语句句的的连连接接方方式式SELECTtable1.column,talbe2.column[,…]FROMtable1[INNER]JOINtable2[JOIN…]ONcondition;内连连接接语语法法：：Oracle扩展展的的连连接接方方式式SELECTtable1.column,talbe2.column[,…]FROMtable1,table2[,…]WHEREcondition;等值值内内连连接接SELECTempno,ename,sal,emp.deptno,dnameFROMempJOINdeptONemp.deptno=10ANDemp.deptno=dept.deptno;SELECTempno,ename,sal,emp.deptno,dnameFROMemp,deptWHEREemp.deptno=10ANDemp.deptno=dept.deptno;非等值值内连连接SELECTempno,ename,sal,gradeFROMempJOINsalgradeONsal>losalANDsal<hisal;SELECTempno,ename,sal,gradeFROMemp,salgradeWHEREsal>losalANDsal<hisal;自身内连接接SELECTworker.empno,worker.ename,manager.empno,manager.enameFROMempworkerJOINempmanagerONworker.mgr=manager.empno;SELECTworker.empno,worker.ename,manager.empno,manager.enameFROMempworker,empmanagerWHEREworker.mgr=manager.empno;（3）外连接左外连接右外连接全外连接左外连接语语法：标准准SQL语句的连接接方式SELECTtable1.column,table2.column[,…]FROMtable1LEFTJOINtable2[,]ONtable1.column<operator>table2.column[,…];左外连接语语法：Oracle扩展的连接接方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column<operator>table2.column(+)[…];查询10号部门的部部门名、员员工号、员员工名和所所有其他部部门的名称称，语句为为SELECTdname,empno,enameFROMdeptLEFTJOINempONdept.deptno=emp.deptnoANDdept.deptno=10;或SELECTdname,empno,enameFROMdept,empWHEREdept.deptno=emp.deptno(+)ANDemp.deptno(+)=10;右外连接语语法：标准准SQL语句的连接接方式SELECTtable1.column,table2.column[,…]FROMtable1RIGHTJOINtable2[,…]ONtable1.column<operator>table2.column[…];右外连接语语法：Oracle扩展的连接接方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column(+)<operator>table2.column[…];查询20号部门的部门门名称及其员员工号、员工工名，和所有有其他部门的的员工名、员员工号，语句句为SELECTempno,ename,dnameFROMdeptRIGHTJOINempONdept.deptno=emp.deptnoANDdept.deptno=20;或SELECTempno,ename,dnameFROMdept,empWHEREdept.deptno(+)=emp.deptnoAND`dept.deptno(+)=20;全外连接是指指在内连接的的基础上，将将连接操作符符两侧表中不不符合连接条条件的记录加加入结果集中中。在Oracle数据库中，全全外连接的表表示方式为SELECTtable1.column,table2.column[,…]FROMtable1FULLJOINtable2[,…]ONtable1.column1=table2.column2[…];查询所有的部部门名和员工工名，语句为为SELECTdname,enameFROMempFULLJOINdeptONemp.deptno=dept.deptno;13.2.5子查询子查询概述单行单列子查查询多行单列子查查询单行多列子查查询多行多列子查查询相关子查询在FROM子句中使用子子查询在DDL语句中使用子子查询使用WITH子句的子查询询子查询的概念念子查询是指嵌嵌套在其他SQL语句中的SELECT语句，也称为为嵌套查询。。在执行时，由由里向外，先先处理子查询询，再将子查查询的返回结结果用于其父父语句（外部部语句）的执执行。子查询作用在INSERT或CREATETABLE语句中使用子子查询，可以以将子查询的的结果写入到到目标表中；；在UPDATE语句中使用子子查询可以修修改一个或多多个记录的数数据；在DELETE语句中使用子子查询可以删删除一个或多多个记录在WHERE和HAVING子句中使用子子查询可以返返回的一个或或多个值。（1）子查询概述述单行单列子查查询是指子查查询只返回一一行数据，而而且只返回一一列的数据。。运算符=，>，<，>=，<=，!=查询比7934号员工工资高高的员工的员员工号、员工工名、员工工工资信息，语语句为SELECTempno,ename,salFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7934);（2）单行单列子子查询（3）多行单列子子查询多行单列子查查询是指返回回多行数据，，且只返回一一列的数据。。运算符号运算符含义IN与子查询返回结果中任何一个值相等NOTIN与子查询返回结果中任何一个值都不等>ANY比子查询返回结果中某一个值大=ANY与子查询返回结果中某一个值相等<ANY比子查询返回结果中某一个值小>ALL比子查询返回结果中所有值都大<ALL比子查询返回结果中任何一个值都小EXISTS子查询至少返回一行时条件为TRUENOTEXISTS子查询不返回任何一行时条件为TRUE查询与与10号部门门某个个员工工工资资相等等的员员工信信息。。SELECTempno,ename,salFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10);查询比比10号部门门某个个员工工工资资高的的员工工信息息。SELECTempno,ename,salFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=10);查询比比10号部门门所有有员工工工资资高的的员工工信息息。SELECTempno,ename,salFROMempWHEREsal>ALL(SELECTsalFROMempWHEREdeptno=10);（4）单行行多列列子查查询单行多多列子子查询询是指指子查查询返返回一一行数数据，，但是是包含含多列列数据据。多列数数据进进行比比较时时，可可以成成对比比较，，也可可以非非成对对比较较。成成对比比较要要求多多个列列的数数据必必须同同时匹匹配，，而非非成对对比较较则不不要求求多个个列的的数据据同时时匹配配。查询询与与7844号员员工工的的工工资资、、工工种种都都相相同同的的员员工工的的信信息息。。SELECTempno,ename,sal,jobFROMempWHERE(sal,job)=(SELECTsal,jobFROMempWHEREempno=7844);查询询与与10号部部门门某某个个员员工工工工资资相相同同，，工工种种也也与与10号部部门门的的某某个个员员工工相相同同的的员员工工的的信信息息。。SELECTempno,ename,sal,jobFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10)ANDjobIN(SELECTjobFROMempWHEREdeptno=10);（5）多多行行多多列列子子查查询询多行行多多列列子子查查询询是是指指子子查查询询返返回回多多行行数数据据，，并并且且是是多多列列数数据据。。例如如，，查查询询与与10号部部门门某某个个员员工工的的工工资资和和工工种种都都相相同同的的员员工工的的信信息息，，语语句句为为SELECTempno,ename,sal,jobFROMempWHERE(sal,job)IN(SELECTsal,jobFROMempWHEREdeptno=10);（6）相相关关子子查查询询子查查询询在在执执行行时时并并不不需需要要外外部部父父查查询询的的信信息息，，这这种种子子查查询询称称为为无无关关子子查查询询。。如果果子子查查询询在在执执行行时时需需要要引引用用外外部部父父查查询询的的信信息息，，那那么么这这种种子子查查询询就就称称为为相相关关子子查查询询。。在相相关关子子查查询询中中经经常常使使用用EXISTS或NOTEXISTS谓词来实实现。如如果子查查询返回回结果，，则条件件为TRUE，如果子子查询没没有返回回结果，，则条件件为FALSE。查询没有有任何员员工的部部门号、、部门名名。SELECTdeptno,dname,locFROMdeptWHERENOTEXISTS(SELECT*FROMempWHEREemp.deptno=dept.deptno);查询比本本部门平平均工资资高的员员工信息息。SELECTempno,ename,salFROMempeWHEREsal>(SELECTavg(sal)FROMempWHEREdeptno=e.deptno);（7）在FROM子句中使用子子查询当在FROM子句中使用子子查询时，该该子查询被作作为视图对待待，必须为该该子查询指定定别名。查询各个员工工的员工号、、员工名及其其所在部门平平均工资。SELECTempno,ename,d.avgsalFROMemp,(SELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno)dWHEREemp.deptno=d.deptno;查询各个部门门号、部门名名、部门人数数及部门平均均工资。SELECTdept.deptno,dname,d.amount,d.avgsalFROMdept,(SELECTdeptno,count(*)amount,avg(sal)avgsalFROMempGROUPBYdeptno)dWHEREdept.deptno=d.deptno;（8）在DDL语句中使用子子查询可以在CREATETABLE和CREATEVIEW语句中使用子子查询来创建建表和视图。。CREATETABLEemp_subqueryASSELECTempno,ename,salFROMemp;CREATEVIEWemp_view_subqueryASSELECT*FROMempWHEREsal>2000;（9）使用WITH子句的子查询询如果在一个SQL语句中多次使使用同一个子子查询，可以以通过WITH子句给子查询询指定一个名名字，从而可可以实现通过过名字引用该该子查询，而而不必每次都都完整写出该该子查询。查询人数最多多的部门的信信息。SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMempGROUPBYdeptnoHAVINGcount(*)>=ALL(SELECTcount(*)FROMempGROUPBYdeptno));相同的子查询询连续出现了了两次，因此此可以按下列列方式编写查查询语句。WITHdeptinfoAS(SELECTdeptno,count(*)numFROMempGROUPBYdeptno)SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMdeptinfoWHEREnum=(SELECTmax(num)FROMdeptinfo));13.2.6合并查询语法UNIONINTERSECTMINUS语法：SELECTquery_statement1[UNION|UNIONALL|INTERSECT|MINUS]SELECTquery_statement2;注意：当要合并几个个查询的结果果集时，这几几个查询的结结果集必须具具有相同的列列数与数据类类型。如果要对最终终的结果集排排序，只能在在最后一个查查询之后用ORDERBY子句指明排序序列。（1）UNIONUNION运算符用于获获取几个查询询结果集的并并集，将重复复的记录只保保留一个，并并且默认按第第一列进行排排序。查询10号部门的员工工号、员工名名、工资和部部门号以及工工资大于2000的所有员工的的员工号、员员工名、工资资和部门号，，语句为SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;如果要保留所所有的重复记记录，则需要要使用UNIONALL运算符。SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONALLSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;（2）INTERSECTINTERSECT用于获取几个个查询结果集集的交集，只只返回同时存存在于几个查查询结果集中中的记录。同同时，返回的的最终结果集集默认按第一一列进行排序序。查询30号部门中工资资大于2000的员工号、员员工名、工资资和部门号，，语句为：SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=30INTERSECTSELECTempno,ename,sal,deptnoFROMEMPWHEREsal>2000;（3）MINUSMINUS用于获取几个个查询结果集集的差集，即即返回在第一一个结果集中中存在，而在在第二个结果果集中不存在在的记录。同同时，返回的的最终结果集集默认按第一一列进行排序序。查询30号部门中工工种不是“SALESMAN”的员工号、、员工名和和工种名称称，语句为为：SELECTempno,ename,jobFROMempWHEREdeptno=30MINUSSELECTempno,ename,jobFROMEMPWHEREjob='SALESMAN';13.3数据操纵插入数据修改数据MERGE语句句删除除数数据据13.3.1插入入数数据据插入入单行行记记录录利用用子子查查询询插插入入数数据据向多多个个表表中中插插入入数数据据（1）插入入单行行记记录录语法法INSERTINTOtable_name|view_name[(column1[,column2…])]VALUES(value1[,values,…])注意如果在INTO子句中没有指指明任何列名名，则VALUES子句中列值的的个数、顺序序、类型必须须与表中列的的个数、顺序序、类型相匹匹配。如果在INTO子句中中指定定了列列名，，则VALUES子句中中提供供的列列值的的个数数、顺顺序、、类型型必须须与指指定列列的个个数、、顺序序、类类型按按位置置对应应。向表或或视图图中插插入的的数据据必须须满足足表的的完整整性约约束。。字符型型和日日期型型数据据在插插入时时要加加单引引号。。日期期类型型数据据需要要按系系统默默认格格式输输入，，或使使用TO_DATE函数进进行日日期转转换。。向dept表中插插入一一行记记录。。INSERTINTOdeptVALUES(50,'IM','dalian');向emp表中插插入一一行记记录。。INSERTINTOemp(empno,ename,sal,hiredate)VALUES(1234,'JOAN',2500,'20-4月-2007');（2）利用用子查查询插插入数数据语法INSERTINTOtable_name|view_name[(column1[,column2,…])subquery;注意INTO子句中中指定定的列列的个个数、、顺序序、类类型必必须与与子查查询中中列的的个数数、顺顺序和和类型型相匹匹配。。统计各各个部部门的的部门门号、、部门门最高高工资资和最最低工工资，，并将将统计计的结结果写写入到到表emp_salary（假设设该表表已经经创建建）中中。INSERTINTOemp_salarySELECTdeptno,max(sal),min(sal)FROMempGROUPBYdeptno;向emp表中插入入一行记记录，其其员工名名为FAN，员工号号为1235，其他信信息与员员工名为为SCOTT的员工信信息相同同。INSERTINTOempSELECT1235,'FAN',job,mgr,hiredate,sal,comm,deptnoFROMempWHEREename='SCOTT';如果要将将大量数数据插入入表中，，可以利利用子查查询直接接装载的的方式进进行。由由于直接接装载数数据的操操作过程程不写入入日志文文件，因因此数据据插入操操作的速速度大大大提高。。利用子查查询装载载数据语语法为：：INSERT/*+APPEND*/INTOtable_name|view_name[(column1[,column2,…])subquery;复制emp表中empno，ename，sal，deptno四列的值值，并插插入到new_emp表中，语语句为：：INSERT/*+APPEND*/INTOnew_emp(empno,ename,sal,deptno)SELECTempno,ename,sal,deptnofromemp;13.3.2修改数据据基本语法法修改单行行记录修改多行行记录带有子查查询的修修改基本语法法UPDATEtable_name|view_nameSETcolumn1=value1[,column2=value2…][WHEREcondition]修改单条条记录UPDATEempSETsal=sal+100,comm=200WHEREempno=7844;修改多条条记录UPDATEempSETsal=sal+150WHEREdeptno=20;利用子查查询修改改记录UPDATEempSETsal=300+(SELECTavg(sal)FROMempWHEREdeptno=10)WHEREdeptno=30;13.3.3MERGE语句利用MERGE语句可以以同时完完成数据据的插入入与更新新操作。。将源表的的数据分分别与目目标表中中的数据据根据特特性条件件进行比比较（每每次只比比较一条条记录）），如果果匹配，，则利用用源表中中的记录录更新目目标表中中的记录录，如果果不匹配配，则将将源表中中的记录录插入目目标表中中。使用MERGE语句操作作时，用用户需要要具有源源表的SELECT对象权限限以及目目标表的的INSERT，UPDATE对象权限限。MERGE语句的基基本语法法为：MERGEINTO[schema.]target_table[target_alias]USING[schema.]source_table|source_view|source_subquery[source_alias]ON(condition)WHENMATCHEDTHENUPDATESETcolumn1=expression1[,column2=expression2…][where_clause][DELETEwhere_clause]WHENNOTMATCHEDTHENINSERT[(column2[,column2…])]VALUES(expresstion1[,expression2…])[where_clause];参数说明明INTO：指定进进行数据据更新或或插入的的目标表表；USING：指定用用于目标标表数据据更新或或插入的的源表或或视图或或子查询询；ON：决定MERGE语句句执执行行更更新新操操作作还还是是插插入入操操作作的的条条件件。。对对于于目目标标表表中中满满足足条条件件的的记记录录，，则则利利用用源源表表中中的的相相应应记记录录进进行行更更新新；；而而源源表表中中不不满满条条件件的的记记录录将将被被插插入入目目标标表表中中；；where_clause：只只有有当当该该条条件件为为真真时时才才进进行行数数据据的的更更新新或或插插入入操操作作；；DELETEwhere_clause：当当目目标标表表中中更更新新后后的的记记录录满满足足该该条条件件时时，，则则删删除除该该记记录录。。使用用“雇员员”表（（别别名名e）作作为为数数据据源源，，在在该该表表的的副副本本“副本本_雇员员”表（（别别名名c）中中插插入入并并更更新新行行。。MERGEINTO副本本_雇员员cUSING雇员eON(c.雇员标识=e.雇员标识)WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部门标识=e.部门标识WHENNOTMATCHEDTHENINSERTVALUES(e.雇员标识,e.姓氏,e.部门标识);MERGEINTO副本_雇员cUSING雇员eON(c.雇员标识=e.雇员标识)WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部门标识=e.部门标识WHENNOTMATCHEDTHENINSERTVALUES(e.雇员标识,e.姓氏,e.部门标识);MERGE语句执行前的的“副本_雇员”雇员标识姓姓氏 部门标标识100 Smith40103 Chang30MERGE执行后的“副本_雇员”雇员标识姓姓氏 部门标标识100 King 90103 Hunold60152Davies50雇员员（（来来源源表表））雇员员标标识识姓姓氏氏部部门门标标识识100King90103Hunold60152Davies50现有有表表source_emp和target_emp，表表中中数数据据如如下下。。利利用用source_emp表中中的的数数据据更更新新target_emp表中中的的数数据据，，对对target_emp表中中存存在在的的员员工工信信息息进进行行更更新新，，对对不不存存在在的的员员工工进进行行信信息息插插入入。。SELECT*FROMsource_emp;EMPNOENAMEDEPTNO------------------------------100JOAN10110SMITH20120TOM30SELECT*FROMtarget_emp;EMPNOENAMEDEPTNO------------------------------------100MARRY2020JACK40MERGEINTOtarget_emptUSINGsource_empsON(t.empno=s.empno)WHENMATCHEDTHENUPDATESETt.ename=s.ename,t.deptno=s.deptnoWHENNOTMATCHEDTHENINSERTVALUES(s.empno,s.ename,s.deptno)；SELECT*FROMtarget_emp;EMPNOENAMEDEPTNO------------------------------100JOAN1020JACK40110SMITH20120TOM3013.3.4删除数据基本语法删除单条记录录删除多条记录录带有子查询的的删除操作利用TRUNCATE删除数据TRUNCATE与DELETE区别基本语法DELETEFROMtable|view[WHEREcondition]删除单条记录录DELETEFROMempWHEREempno=7844;删除多条记录录DELETEFROMempWHEREdeptno=10;带有子查询的的删除操作DELETEFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7900);利用TRUNCATE删除数据TRUNCATETABLEtable_nameTRUNCATE与DELETE区别释放存储空间间不写入日志文文件，因此执执行效率较高高，但该操作作不可回滚。。13.4事务处理事务概述Oracle事务处理13.4.1事务概述原子性（Atomicity）：事务是数数据库的逻辑辑工作单位，，事务中的所所有操作要么么都做，要么么都不做，不不存在第三种种情况。一致性（Consistency）：事务执行行的结果必须须是使数据库库从一个一致致性状态转变变到另一个一一致性状态，，不存在中间间的状态。隔离性（Isolation）：数据库中中一个事务的的执行不受其其他事务干扰扰，每个事务务都感觉不到到还有其他事事务在并发执执行。持久性（Durability）：一个事务务一旦提交，，则对数据库库中数据的改改变是永久性性的，以后的的操作或故障障不会对事务务的操作结果果产生任何影影响。13.4.2Oracle事务处理事务提交方式式用户显式执行行COMMIT命令执行特定操作作时系统自动动提交。说明当事务提交后后，用户对数数据库修改操操作的日志信信息由日志缓缓冲区写入重重做日志文件件中，释放该该事务所占据据的系统资源源和数据库资资源。此时，，其他会话可可以看到该事事务对数据库库的修改结果果。当执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令时，系系统将自动提提交。事务回滚方式式事务全部回滚滚ROLLBACK事务部分回滚滚SAVEPOINTXROLLBACKTOXROLLBACKTOB;（回滚最后一一个INSERT操作）ROLLBACKTOA;（回滚后后面的INSERT操作和UPDATE操作）ROLLBACK;（回滚全全部操作作）13.5SQL函数SQL函数分类类数值函数数字符函数数日期函数数转换函数数其他函数数13.5.1SQL函数分类类根据参数数作用行行数的不不同，可可以分为为：单行函数数多行函数数根据参数数类型不不同，可可以分为为：数值函数数字符函数数日期函数数转换函数数聚集函数数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等于零时，表示对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也可以为负数，表示小数点左边的数字位置被删除成零。SELECTsal/22daysal,round(sal/22,1),trunc(sal/22,1),round(sal/22,-1),trunc(sal/22,-1)FROMemp;DAYSALROUND(SAL/22,1)TRUNC(SAL/22,1)ROUND(SAL/22,-1)TRUNC(SAL/22,-1)---------------------------------------------------------------------45.454545545.545.4504090.909090990.990.9909054.545454554.554.5505086.363636486.486.3908070.454545570.570.47070146.590909146.6146.5150140SELECTsal,width_bucket(sal,1000,5000,10)FROMempWHEREdeptno=30;SALWIDTH_BUCKET(SAL,1000,5000,10)----------------------------------------17002135012950561001110501SELECTfloor(3.5),ceil(3.5),mod(5,3),remainder(5,3),mod(4,3),remainder(4,3)FROMdual;FLOOR(3.5)CEIL(3.5)MOD(5,3)REMAINDER(5,3)MOD(4,3)REMAINDER(4,3)-------------------------------------------------------342-11113.5.3字符函数数函数返回值ASCII(char)字符串首字符的ASC码值CHR(n)ASC码值为n的字符CONCAT把两个列值拼接起来；“||”操作符更通用INITCAP(char)将字符串中每个单词的首字母大写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.LTRIM(char[,SET])把char中最左侧的若干个字符去掉，以使其首字符不在SET中RTRIM(char[,SET])把char中最右侧的若干个字符去掉，以使其尾字符不在SET中TRIM([leading|trailing|both]FROMstring)SELECTlpad('abc',5,'#')leftpad,rpad('abc',5,'#')rightpad,ltrim('abcd','a')lefttrim,rtrim('abcde','e')righttrim,substr('abcd',2,3)substringFROMdual;LEFTPADRIGHTPADLEFTTRIMRIGHTTRIMSUBSTRING--------------------------------------------------------------##abcabc##bcdabcdbcdSELECTconcat(concat(ename,'''sjobcategoryis'),job)"Job"FROMempWHEREempno=7844;Job--------------------------------------TURNER'sjobcategoryisSALESMANSELECTinstr('abcde','b')position,replace('oracle9i','9i','10g')newstring,soundex('hello')soundFROMdual;POSITIONNEWSTRINGSOUND---------------------------------------2oracle10gH40013.5.4日期函函数日期函函数是是指对对日期期进行行处理理的函函数，，函数数输入入为DATE或TIMESTAMP类型的数据据，输出为为DATE类型的数据据（除MONTH_BETWEEN函数返回整整数以外））。Oracle数据库中日日期的默认认格式为DD-MON-YY。可以通过过设置NLS_DATE_FORMAT参数设置当当前会话的的日期格式式，通过NLS_LANGUAGE参数设置表表示日期的的字符集。。例如：ALTERSESSIONSETNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS';ALTERSESSIONSETNLS_LANGUAGE='AMERICAN';函数返回值ADD_MONTHS(d,n)返回日期d添加n个月的日期CURRENT_DATE返回系统当前日期和时间CURRENT_TIMESTAMP[(p)]返回系统当前时间戳EXTRACT(departFROMd)返回d中depart对应部分的内容

LAST_DAY(d)返回d所在月份最后一天的日期LOCALTIMESTAMP(P)返回本地时间戳LOCALTIMESTAMP[(p)]返回当前会话时区所对应的日期时