Oracle数据库开发实用教程第十一章资料

Oracle11g实用教程第十一章SQL语言基础内容概要SQL概述1数据查询2数据操纵3事务控制4SQL函数5SQL语言概述SQL语言简介SQL语言的特点SQL语言的分类SQL语句的编写规则SQL语言简介SQL（StructuredQueryLanguage）语言是1974年由Boyce和Chamberlin提出的。SQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过SQL语言实现对数据库的访问和操作。SQL语言的特点1．功能强大SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，且具有统一的语言风格，使用SQL语句就可以独立完成数据管理的核心操作。2．集合操作SQL采用集合操作方式，对数据的处理是成组进行的，而不是一条一条处理的。通过使用集合操作方式，可以加快数据的处理速度。执行SQL语句时，每次只能发送并处理一条语句。若要降低语句发送和处理次数，则可以使用PL/SQL。3．非过程化SQL还具有高度的非过程化特点，执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。这使得在对数据库进行存取操作时无需了解存取路径，大大减轻了用户的负担，并且有利于提高数据的独立性。4语言简洁虽然SQL的语言功能极强，但其语言十分简洁，语法接近于自然语言，因此简单易学。SQL语言的分类数据查询语言(SELECT)：用于检索数据库数据。在SQL所有语句中，SELECT语句的功能和语法最复杂，同时也最灵活。数据操纵语言(DataManipulationLanguage，DML)：用于改变数据库数据，包括INSERT,UPDATE和DELETE三种语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新已经存在的数据库数据，而DELETE语句则用于删除已经存在的数据库数据。事务控制语言(TransactionalControlLanguage，TCL)：用于维护数据的一致性，包括COMMIT，ROLLBACK和SAVEPOINT三条语句。其中，COMMIT语句用于确认已经进行的数据库改变，ROLLBACK语句用于取消已经进行的数据库改变，而SAVEPOINT语句则用于设置保存点，以取消部分数据库改变。数据定义语言(DataDefinitionLanguage，DDL)：用于建立、修改和删除数据库对象。例如，使用CREATETABLE可以创建表，使用ALTERTABLE语句则可以对表结构进行修改，而如果想删除某个表，则可以使用DROPTABLE语句。这里需要注意的是，DDL语句会自动提交事务。数据控制语言(DataControlLanguage，DCL)：用于执行权限授予和权限回收操作，包括GRANT和REVOKE两条命令，其中，GRANT命令用于给用户或者角色授予权限，而REVOKE命令则用于回收用户或角色所具有的权限。需要注意的是，DCL语句也是自动对事务进行提交的。SQL语句的编写规则1.SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者混用大小写格式。2.对象名和别名也不区分大小写，它们既可以使用大写格式，也可以使用小写格式，或者混用大小写格式。3.字符值和日期值是区分大小写的。当在SQL语句中引用字符值和日期值时，必须要给出正确的大小写数据，否则不能得到正确的查询结果4.在应用程序中编写SQL语句时，如果SQL语句的文本很短，可以将语句文本放在一行；如果SQL语句的文本很长，可以将语句文本分布到多行上，并且可以通过使用跳格和缩进提高可读性数据查询数据查询语法基本查询分组查询连接查询子查询合并查询数据查询语法基本语法：SELECT[ALL|DISTINCT]column_name[,expression][INTOnew_table]FROMtable1[,table2,view,…][WHEREcondition][GROUPBYcolumn1[,column2,…][HAVINGgroup_condition]][ORDERBYcolumn1[ASC|DESC][,column2,…]]基本查询无条件查询有条件查询查询排序查询统计（1）无条件查询(1)查询表中所有的记录，可以用“*”表示所有列。例如：SQLSELECT*FROMemp;(2)查询表中所有记录的指定字段，多个字段间用逗号分隔。例如：SQLSELECTdeptno,dnameFROMdept;(3)允许查询结果中显示重复记录（默认就是all）SQLSELECTalldeptno,dnameFROMdept;(4)不允许查询结果中显示重复记录，可以使用DISTINCT，例如：SQLSELECTDISTINCTdeptnoFROMemp;(5)指定查询前n行记录，可以使用TOPn，例如：SQLSELECTtopnename,salaryFROMemp;(6)指定查询记录数的百分比SQLSELECTtopnpercentename,salaryFROMemp;(7)带表达式的查询如果需要对查询目标列进行计算，那么可以在目标列表达式中使用算数表达式或函数。例如：SQLSELECTempno,UPPER(ename),sal*1.2FROMemp;(8)更改列名显示的查询可以为查询的目标列或目标表达式起别名，即改变列标题。例如：SQLSELECTenameemployeename,salsalaryFROMemp;(9)使用字符常量如果需要在查询结果中加入字符，可以在目标列表达式中使用字符常量。例如：SQLSELECTempno,’Nameis:’,enameFROMemp;(10)使用连接字符串可以使用“||”运算符将查询的目标列或目标表达式连接起来。例如：SQLSELECT‘员工号：’||empno||‘员工名：’||enameFROMemp;（2）where条件查询查询满足条件的元组可以通过WHERE子句实现。WHERE条件中常用的运算符号运算符号谓词比较大小=,,,=,=,，!=确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR关系运算符含义实例大于SELECT*FROMAWHEREBC;=小于等于SELECT*FROMAWHEREB=C;=大于等于SELECT*FROMAWHEREB=C;LIKE值片段相等%：匹配所有字符，如‘A%’，匹配以字母A开头的所有列值_：匹配单个字符，如‘A---’，匹配以字母A开头的任意三个字符WHERE子句中的关系运算符WHERE子句中的逻辑运算符和其他关键词逻辑运算符说明OROR连接的两个条件中，任意满足一个，可获得逻辑真值ANDAND连接的两个条件中，两个条件必须同时满足，可获得逻辑真值NOTNOT后连接的条件，在不满足的时候，能获得逻辑真值BETWEEN…AND…BETWEEN和AND组合用，可指定特定的范围；而NOTBETWEEN…AND…指定在特定范围之外ININ关键字，可用来指定几个特定的值，而NOTIN指定不在特定值集合内ISNULL要判断的列值或表达式的结果为空时返回逻辑真值，而ISNOTNULL在判断的列值或表达式的结果有内容时返回逻辑真值升序、降序排序ASC:升序（缺省）；DESC:降序SELECTempno,ename,salFROMempORDERBYsalDESC;多列排序首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类推。SELECT*FROMempORDERBYjob,salDESC;（3）ORDERBY查询排序按表达式排序可以按特定的表达式进行排序。SELECTempFROMempORDERBYsal*12;别名排序可以使用目标列或表达式的别名进行排序。SELECTempno,sal*12year_salaryFROMempORDERBYyear_salary;使用列位置编号排序如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。SELECTempno,sal*12year_salaryFROMempORDERBY2;分组查询基本语法单列分组查询多列分组查询使用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子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是集合函数。单列分组查询通过指定GROUPBY子句可以将查询结果按照某个指定列进行分组SELECTdeptno,count(*),avg(sal),max(sal)FROMempGROUPBYdeptnoorderbydeptno;多列分组查询与ORDERBY子句类似，也可以在GROUPBY子句中指定多个分组列来进行多列分组查询。SELECTdeptno,job,count(*),max(sal)FROMempGROUPBYdeptno,job;使用ROLLUP和CUBE在GROUPBY子句中使用ROLLUP选项，可以生成横向统计和不分组统计；而使用CUBE选项，可以生成横向统计、纵向统计和不分组统计。SELECTdeptno,job,avg(sal)FROMempGROUPBYROLLUP(deptno,job);SELECTdeptno,job,avg(sal)FROMempGROUPBYCUBE(deptno,job);使用HAVING子句限制返回组HAVING子句通常与GROUPBY子句一起使用，在完成对分组结果统计后，可以使用HAVING子句对分组结果做进一步筛选，HAVING子句将返回满足条件的组。SELECTdistinctjobFROMempGROUPBYjobHAVINGmin(sal)1500;连接查询交叉连接内连接等值连接不等值连接自身连接外连接左外连接右外连接全外连接（1）交叉连接概念两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的表中分别有n1，n2，n3…条记录，那么交叉连接的结果集中将有n1×n2×n3×…条记录。以下情况可以出现交叉连接连接条件省略连接条件非法一个表的所有行被连接到另一个表的所有行示例SELECTempno,ename,sal,dname,locFROMemp,dept;（2）内连接两个表内连接查询的执行过程两个表进行内连接查询时，先在第一个表中查找到第一个记录，然后在第二个表中从头进行扫描，逐个查找满足连接条件的记录，找到后将其与第一个表中查找到的记录拼接形成结果集中第一个记录，这样扫描完第二个表后，再在第一个表中查找第二个记录，然后再从头扫描第二个表，逐个查找满足连接条件的记录，找到后将其与第一个表中查找到的第二个记录拼接形成结果集中的一个记录。重复上述过程，直到第一