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;分组查询询基本语法法单列分组组查询多列分组组查询使用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);连接查询询交叉连接接内连接等值连接接不等值连连接自身连接接外连接左外连接接右外连接接全外连接接（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;子查询子查询概述单行单列子查查询多行单列子查查询单行多列子查查询多行多列子查查询相关子查询在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));合并查询语法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语句删除数据插入数据插入单行记录利用子查询询插入数据据向多个表中中插入数据据（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;修改数据基本语法修改单行记记录修改多行记记录带有子查询询的修改基本语法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;语句利用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语句执行前前的“副本_雇员”雇员标识姓姓氏部部门标识100Smith 40103Chang 30MERGE执行后的“副本_雇员”雇员标识姓姓氏部部门标识100King90103Hunold 60152Davies 50雇员（来源源表）雇员标识姓姓氏部部门标识100King90103Hunold 60152Davies 50现有表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------------------------------100JOAN1020JACK40110SMITH20120TOM30删除数据基本语法删除单条记记录删除多条记记录带有子查询询的删除操操作利用TRUNCATE删除数据TRUNCATE与DELETE区别基本语法DELETEFROMtable|view[WHEREcondition]删除单条记记录DELETEFROMempWHEREempno=7844;删除多条记记录DELETEFROMempWHEREdeptno=10;带有子查询询的删除操操作DELETEFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7900);利用TRUNCATE删除数据TRUNCATETABLEtable_nameTRUNCATE与DELETE区别释放存储空空间不写入日志志文件，因因此执行效效率较高，，但该操作作不可回滚滚。13.4事务处理事务概述Oracle事务处理事务概述原子性（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函数分类数值函数字符函数日期函数转换函数其他函数函数分类根据参数作作用行数的的不同，可可以分为：：单行函数多行函数根据参数类类型不同，，可以分为为：数值函数字符函数日期函数转换函数聚集函数数值函数函数返回值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)---------------------------------------------------------------------SELECTsal,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-111字符符函函数数函数返回值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---------------------------------------2oracle10gH400日期函函数日期函函数是是指对对日期期进行行处理理的函函数，，函数数输入入为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)]返回当前会话时区所对应的日期时间MONTHS_BETWEEN(d1,d2)日期d1和d2之间相隔的月数NEXT_DAY(d,day)d后第一周指定的日期。ROUND（d，format）日期d按format格式进行舍入SYSDATE返