Oracle数据库管理开发与实践-教学课件-杨永健-刘尚毅-第5章-SQL语言基础及调优

在线教务辅导网：或者直接输入下面地址：本章要求：第5章SQL语言基础及调优了解SQL语言的特点理解用户模式和模式对象掌握检索数据的方法掌握Oracle常用的系统函数掌握子查询的用法掌握操作数据库的3种方法了解如何进行事务处理掌握常用SQL语句的优化掌握表连接的优化理解合理使用索引对象了解Oracle优化器的使用了解SQL重演和Oracle系能顾问主要内容1.SQL语言简介2.用户模式3.检索数据4.Oracle常用系统函数5.子查询的用法6.操作数据库7.事务处理8.常规SQL语句优化9.表连接优化10.合理使用索引11.优化器的使用12.数据库和SQL重演13.Oracle的性能顾问14.综合实例——查询工资不小于WARD的员工信息第5章SQL语言基础及调优5.1.1SQL语言的特点5.1.2SQL语言的分类5.1.3SQL语言的编写规则5.1SQL语言简介SQL语言结构简洁，功能强大，简单易学，自从被国际标准化组织(ISO)采纳为国际标准以后，SQL语言得到了广泛的应用，它主要有以下特点。（1）集合性SQL可以在高层的数据结构上进行工作，工作时不是单条的处理记录，而对数据进程成组的处理。所有SQL语句都接受集合作为输入，并且返回集合作为输出，SQL的集合特性允许一条SQL语句的结果作为另一条SQL语句的输入。（2）统一性SQL为许多任务提供了统一的命令，这样方便用户学习和使用，基本的SQL命令只需很少时间就能学会，甚至最高级的命令也可以在几天内掌握。数据库的操作任务通常包括以下几方面：查询数据。在表中插入、修改和删除记录。建立、修改和删除数据对象。控制对数据和数据对象的读写。保证数据库一致性和完整性。（3）易于移植性由于所有主要的关系型数据库管理系统都支持SQL语言，这样用户就比较容易将原先使用SQL的技能从一个RDBMS转到另一个，而且用兼容SQL标准编写的程序都是可以移植的。5.1.1SQL语言的特点5.1.2SQL语言的分类SQL是关系型数据库的基本操作语言，是数据库管理系统与数据库进行交互的接口。它将数据查询、数据操纵、事务控制、数据定义和数据控制功能基于一身，而这些功能又分别对应着各自的SQL语言，具体如下。（1）数据查询语言（DQL）用于检索数据库中的数据，主要是SELECT语句，它在操作数据库的过程中使用最为频繁。（2）数据操纵语言（DML）用于改变数据库中的数据，主要包括INSERT，UPDATE和DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已经存在的数据，而DELETE语句则用于删除数据库中已经存在的数据。（3）事务控制语言（TCL）用于维护数据的一致性，包括COMMIT、ROLLBACK和SAVEPOINT三条语句。其中，COMMIT语句用于提交对数据库的更改，ROLLBACK语句用于取消对数据库的更改，而SAVEPOINT语句则用于设置保存点。（4）数据定义语言（DDL）用于建立、修改和删除数据库对象。比如，可以使用CREATETABLE语句创建表；使用ALTERTABlE语句修改表结构；使用DROPTABLE语句删除表，（5）数据控制语言（DCL）用于执行权限授予和权限受贿操作，主要包括GRANT和REVOKE两条命令。其中，GRANT命令用于给用户或角色授予权限，而REVOKE命令则用于收回用户或角色所具有的权限。5.1.3SQL语言的编写规则SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。【例5-1】编写以下3条语句，对关键字（SELECT和FROM）分别使用大写格式、小写格式或大小写混用格式，代码如下。SQLselectempno,ename,salfromscott.emp;SQLSELECTempno,ename,salFROMscott.emp;SQLselECTempno,ename,salfrOMscott.emp;分别执行这3条SELECT语句，会发现结果完全相同。对象名和列名不区分大小写，它们既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。【例5-2】编写以下3条语句，对“表名和列名”分别使用大写格式、小写格式或大小写混用格式，代码如下。SQLselectempno,ename,salfromscott.emp;SQLselectEMPNO,ENAME,SALfromSCOTT.EMP;SQLselectemPNO,ename,sALfromscott.EmP;分别执行这3条SELECT语句，会发现结果完全相同。字符值区分大小写。当在SQL语句中引用字符值时，必须要给出正确的大小写数据，否则，不能得到正确的查询结果。【例5-3】编写以下两条语句，查询scott.emp表中职位是“销售员”的记录，要求两条语句的查询条件分别为“'SALESMAN'”和“salesman”，代码如下。SQLselect*fromscott.empwherejob='SALESMAN';SQLselect*fromscott.empwherejob=salesman;分别执行这两条SELECT语句，会发现结果不相同，因为查询条件是不相同的。在SQL*Plus环境编写SQL语句时，如果SQL语句较短，则可以将语句放在一行上显示；如果SQL语句很长，为了便于用户阅读，则可以将语句分行显示（并且Oracle会在除第一行之外的每一行前面自动加上行号），当SQL语句输入完毕，要以分号作为结束符。【例5-4】检索scott.emp表中职位是SALESMAN（销售员）的记录，并且分行编写SQL语句，代码如下。SQLselectempno,ename,job2fromscott.emp3wherejob='SALESMAN'4orderbyempno;说明：在SQL*Plus环境中编写较长的SQL语句时，敲回车即可实现换行。当要注意，在敲回车之前不要输入分号，因为分号表示SQL语句的结束。5.2用户模式5.2.1模式与模式对象5.2.2示例模式SCOTT5.2.1模式与模式对象模式是一个数据库对象的集合。模式为一个数据库用户所有，并且具有与该用户相同的名称，比如，SYSTEM模式、SCOTT模式等。在一个模式内部不可以直接访问其它模式的数据库对象，即使在具有访问权限的情况下，也需要指定模式名称才可以访问其它模式的数据库对象。模式对象是由用户创建的逻辑结构，用以存储或引用数据。例如，前面章节中所讲过的段（比如，表、索引等），以及用户所拥有的其它非段的数据库对象。这些非段的数据库对象通常包括：约束、视图、同义词、过程以及程序包等。那么，简单的说，模式与模式对象之间的关系就是拥有与被拥有的关系，即模式拥有模式对象；而模式对象被模式所拥有。注意：一个不属于某个用户所拥有的数据库对象就不能称之为模式对象，比如角色，表空间及目录等数据库对象。为了便于后面章节的讲解，这里介绍一个典型的示例模式——SCOTT模式，因为该模式及其所拥有的模式对象在本书中经常作为被示例。Oracle提供的SCOTT模式的目的，就是为了给用户提供一些示例表和数据来展示Oracle数据库的一些特性。SCOTT模式拥有的模式对象（都是数据表）如图5-1所示。图5-1SCOTT模式拥有的模式对象5.2.2示例模式SCOTTDEPTEMPSALGRADEEMPNONUMBERENAMEVARCHAR2JOBVARCHAR2MGRNUMBERHIREDATEDATESALNUMBERCOMMNUMBERDEPTNONUMBEREMPNONUMBERDNAMEVARCHAR2LOCVARCHAR2GRADENUMBERLOSALNUMBERHISALNUMBERBONUSENAMEVARCHAR2JOBVARCHAR2SALNUMBERCOMMNUMBER【例5-5】在SCOTT模式下，通过检索user_tables表来显示SCOTT模式所拥有的4个数据表，代码如下。SQLconnectscott/tiger已连接。SQLselecttable_namefromuser_tables;本例运行结果如图5-2所示。图5-2显示SCOTT模式中的表另外，用户也可以在SYSTEM模式下可以查询SCOTT模式所拥有的数据表，但要求使用dba_tables数据表。【例5-6】在SYSTEM模式下，通过检索dba_tables表来显示SCOTT模式所拥有的4个数据表，代码如下。SQLconnectsystem/1qaz2wsx已连接。SQLselecttable_namefromdba_tableswhereowner='SCOTT';5.3检索数据5.3.1简单查询5.3.2筛选查询5.3.3分组查询5.3.4排序查询5.3.5多表关联查询只包含SELECT子句和FROM子句的查询就是简单查询，SELECT子句和FROM子句是SELECT语句的必选项，也就是说每个SELECT语句都必须包含这两个子句。其中，SELECT子句用于选择想要在查询结果中显示的列，对于这些要显示的列，即可以使用列名来表示，也可以使用星号（*）来表示。在检索数据时，数据将按照SELECT子句后面指定的列名的顺序来显示；如果使用星号（*），则表示检索所有的列，这时数据将按照表结构的自然顺序来显示。1．检索所有的列如果要检索指定数据表的所有列，可以在SELECT子句后面使用星号（*）来实现。在检索一个数据表时，要注意该表所属于的模式。如果在指定表所属的模式内部检索数据，则可以直接使用表名；如果不在指定表所属的模式内部检索数据，则不但要查看当前模式是否具有查询的权限，而且还要在表名前面加上其所属的模式名称。5.3.1简单查询【例5-7】在SCOTT模式下，在SELECT语句中使用星号（*）来检索dept表中所有的数据，代码如下。SQLconnectscott/1qaz2wsx已连接。SQLselect*fromdept;本例运行结果如图5-3所示。图5-3检索dept表中所有的数据说明：上面的SELECT语句若要在SYSTEM模式下执行，则需要在表dept前面加上scott，即“scott.dept”。在上面的例子中，from子句的后面只有一个数据表，实际上可以在from子句的后面指定多个数据表，每个数据表名之间使用逗号（，）分隔开，其语法格式如下：FROMtable_name1,table_name2,table_name3…table_namen【例5-8】在SCOTT模式下，在from子句中指定两个数据表dept和salgrade，代码如下。SQLselect*fromdept,salgrade;2．检索指定的列用户可以指定查询表中的某些列而不是全部列，并且被指定列的顺序不受限制，指定部分列也称作投影操作。这些列名紧跟在SELECT关键字的后面，每个列名之间用逗号隔开。其语法格式如下：SELECTcolumn_name1,column_name2,column_name3,column_namen说明：利用SELECT指定列的好处就是可以改变列在查询结果中的默认显示顺序。在SELECT语句中使用WHERE子句可以实现对数据行的筛选操作，只有满足WHERE子句中判断条件的行才会显示在结果集中，而那些不满足WHERE子句判断条件的行则不包括在结果集中。这种筛选操作是非常有意义的，通过筛选数据，可以从大量的数据中得到用户所需要的数据。在SELECT语句中，WHERE子句位于FROM子句之后，其语法格式如下：SELECTcolumns_listFROMtable_nameWHEREconditional_expression参数说明如下：colu