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;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语句执行行前的“副本_雇员”雇员标识识姓氏氏 部门门标识100Smith40103Chang30MERGE执行后的的“副本_雇员”雇员标识识姓氏氏 部门门标识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）：一个个事务一一旦提交交，则对对数据库库中数据据的改变变是永久久性的，，以后的的操作或或故障不不会对事事务的操操作结果果产生任任何影响响。事务处理理事务提交交方式用户显式式执行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