第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i第第2章章 数据查询数据查询2.1 数据库查询语言数据库查询语言SQL2.2 基本查询和排序基本查询和排序2.3 条件查询条件查询 2.4 函数函数 2.5 高级查询高级查询2.6 阶段训练阶段训练2.7 练习练习2021/6/71第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.1 数据库查询语言数据库查询语言SQL2.1.1SQL语言的特点和分类SQL语言有以下的主要特点：*SQL语言可以在Oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。*SQL语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部分构成。2021/6/72第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i*SQL语言属于非过程化的4GL(第四代语言)。*SQL语言按功能可分为DDL语言、DML语言、DCL语言和数据库事务处理语言四个类别。*SQL语言的主要关键字有：ALTER、DROP、REVOKE、AUDIT、GRANT、ROLLBACK、COMMIT、INSERT、SELECT、COMMENT、LOCK、UPDATE、CREATE、NOAUDIT、VALIDATE、DELETE、RENAME等。按照SQL语言的不同功用，可以进一步对SQL语言进行划分。下表给出了SQL语言的分类和功能简介。2021/6/73第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-1SQL语言的分类2021/6/74第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.1.2SQL的基本语法SQL语言的语法比较简单，类似于书写英文的语句。其语句一般由主句和若干个从句组成，主句和从句都由关键字引导。主句表示该语句的主要功能，从句表示一些条件或限定，有些从句是可以省略的。在语句中会引用到列名、表名或表达式。另外还有如下一些说明：*关键字、字段名、表名等之间都要用空格或逗号等进行必要的分隔。*语句的大小写不敏感(查询的内容除外)。*语句可以写在一行或多行。*语句中的关键字不能略写和分开写在两行。2021/6/75第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i*要在每条SQL语句的结束处添加“；”号。*为了提高可读性，可以使用缩进。*从句一般写在另一行的开始处。查询语句是最常见的SQL语句，它从给定的表中，把满足条件的内容检索出来。以下是最基本的SELECT语句语法。SELECT字段名列表FROM表名WHERE条件;SELECT为查询语句的关键字，后跟要查询的字段名列表，字段名列表用来指定检索特定的字段，该关键字不能省略。2021/6/76第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i字段名列表代表要查询的字段。FROM也是查询语句关键字，后面跟要查询的表名，该关键字不能省略。WHERE条件限定检索特定的记录，满足“条件”的记录被显示出来，不满足条件的被过滤掉。语句查询的结果往往是表的一部分行和列。如果字段名列表使用*，将检索全部的字段。如果省略WHERE条件，将检索全部的记录。2021/6/77第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】查询部门10的雇员。输入并执行查询：SELECT*FROMempWHEREdeptno=10;结果略。说明：该查询语句从emp表中检索出部门10的雇员，条件由WHEREdeptno=10子句指定。2021/6/78第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.2 基本查询和排序基本查询和排序2.2.1查询的基本用法在Oracle数据库中，对象是属于模式的，每个账户对应一个模式，模式的名称就是账户名称。在表名前面要添加模式的名字，在表的模式名和表名之间用“.”分隔。我们以不同的账户登录数据库时，就进入了不同的模式，比如登录到STUDENT账户，就进入了STUDENT模式。而在STUDENT模式要查询属于SCOTT模式的表，就需要写成：SELECT*FROMSCOTT.EMP;2021/6/79第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i但如果登录用户访问属于用户模式本身的表，那么可以省略表名前面的模式名称。SELECT*FROMemp;1指定检索字段下面的练习，只显示表的指定字段。【训练1】显示DEPT表的指定字段的查询。输入并执行查询：SELECTdeptno,dnameFROMdept;2021/6/710第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i显示结果如下：DEPTNODNAME-10ACCOUNTING20RESEARCH30SALES40OPERATIONS说明：结果只包含2列deptno和dname。在语句中给出要显示的列名，列名之间用“，”分隔。表头的显示默认为全部大写。对于日期和数值型数据，右对齐显示，如deptno列。对于字符型数据，左对齐显示，如dname列。2021/6/711第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】显示emp表的雇员名称和工资。2显示行号每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。【训练2】显示EMP表的行号。输入并执行查询：SELECTrownum,enameFROMemp;2021/6/712第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i结果如下：ROWNUMENAME-1SMITH2ALLEN3WARD4JONES注意：显示的行号是查询结果的行号，数据在数据库中是没有行号的。2021/6/713第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3显示计算列计算列在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。常用的算术运算符包括：*+：加法运算符。*-：减法运算符。*：乘法运算符。*/：除法运算符。以下训练在查询中使用了计算列。2021/6/714第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练3】显示雇员工资上浮20%的结果。输入并执行查询:SELECTename,sal,sal*(1+20/100)FROMemp;显示结果为：ENAMESALSAL*(1+20/100)-SMITH800960ALLEN16001920说明：结果中共显示了3列，第3列显示工资上浮20%的结果，它不是表中存在的列，而是计算产生的结果，称为计算列。2021/6/715第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习2】显示EMP表的雇员名称以及工资和津贴的和。4使用别名我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。【训练4】在查询中使用列别名。输入并执行：SELECTenameAS名称,sal工资FROMemp;显示结果为：名称工资-SMITH800ALLEN16002021/6/716第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：表头显示的是列别名，转换为汉字显示。在列名和别名之间要用AS分隔，如ename和它的别名“名称”之间用AS隔开。AS也可以省略，如sal和它的别名“工资”之间用空格分割。注注意意：如果用空格分割，要区别好列名和别名，前面为列名，后面是别名。别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。2021/6/717第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】在列别名上使用双引号。输入并执行查询：SELECTenameASName,sal*12+5000AS年度工资(加年终奖)FROMemp;显示结果为：Name年度工资(加年终奖)-SMITH14600ALLEN242002021/6/718第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：其中别名“Name”有大小写的区别，别名“年度工资(加年终奖)”中出现括号，属于特殊符号，所以都需要使用双引号将别名引起。【练习3】显示DEPT表的内容，使用别名将表头转换成中文显示。5连接运算符（注意字符函数concat）在前面，我们使用到了包含数值运算的计算列，显示结果也是数值型的。我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“|”。通过连接运算可以将两个字符串连接在一起。2021/6/719第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练6】在查询中使用连接运算。输入并执行查询：SELECTename|jobAS雇员和职务表FROMemp;输出结果为：雇员和职务表-SMITHCLERKALLENSALESMAN2021/6/720第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：在本例中，雇员名称和职务列被连接成为一个列显示。在查询中可以使用字符和日期的常量，表示固定的字符串或固定日期。字符和日期的常量需要用单引号引起。下一个训练是作为上一个训练的改进。2021/6/721第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i？【训练7】在查询中使用字符串常量。输入并执行查询：SELECTename|IS|jobAS雇员和职务表FROMemp;输出结果为：雇员和职务表-SMITHISCLERKALLENISSALESMAN说明：本练习中将雇员名称、字符串常量“IS”和雇员职务3个部分连接在一起。2021/6/722第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习4】显示DEPT表的内容，按以下的形式:部门ACCOUNTING所在的城市为NEWYORK6消除重复行如果在显示结果中存在重复行，可以使用的关键字DISTINCT消除重复显示。2021/6/723第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练8】使用DISTINCT消除重复行显示。输入并执行查询：SELECTDISTINCTjobFROMemp;结果为：JOB-ANALYSTCLERKMANAGERPRESIDENTSALESMAN2021/6/724第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：在本例中，如果不使用DISTINCT关键字，将重复显示雇员职务，DISTINCT关键字要紧跟在SELECT之后。请去掉DISTINCT关键字，重新执行，并观察显示结果的不同。【练习5】显示EMP表中不同的部门编号。2.2.2查询结果的排序如果要在查询的同时排序显示结果，可以使用如下的语句：SELECT字段列表FROM表名WHERE条件ORDERBY字段名1ASC|DESC,字段名2ASC|DESC.;2021/6/725第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iORDERBY从句后跟要排序的列。ORDER BY 从从句句出出现现在在SELECT语句的语句的最后最后。排序有升序和降序之分，ASC表示升序排序，DESC表示降序排序。如果不指明排序顺序，默认的排序顺序为升序。如果要降序，必须书写DESC关键字。2021/6/726第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1升序排序【训练1】查询雇员姓名和工资，并按工资从小到大排序。输入并执行查询：SELECTename,salFROMempORDERBYsal;执行结果为：ENAMESAL-SMITH800JAMES950注意：若省略ASC和DESC，则默认为ASC，即升序排序。2021/6/727第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2降序排序【训练2】查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。输入并执行查询：SELECT ename,hiredate FROM emp ORDER BY hiredateDESC;结果如下：ENAMEHIREDATE-ADAMS23-5月-87SCOTT19-4月-872021/6/728第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iMILLER23-1月-82JAMES03-12月-81FORD03-12月-81注意：DESC表示降序排序，不能省略。3多列排序可以按多列进行排序，先先按按第一列，然然后后按第二列、第三列.。【训练3】查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。输入并执行查询：SELECTename,deptno,hiredateFROMempORDERBYdeptno,hiredate;2021/6/729第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i结果如下：ENAMEDEPTNOHIREDATE-CLARK1009-6月-81KING1017-11月-81MILLER 1023-1月-82SMITH2017-12月-80JONES2002-4月-81FORD2003-12月-81SCOTT2019-4月-87说明：该该排排序序是是先先按按部部门门升升序序排排序序，部部门门相相同同的的情情况况下下，再再按雇佣时间升序排序。按雇佣时间升序排序。2021/6/730第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i4在排序中使用别名如果要对计算列排序，可以为计算列指定别名，然后按别名排序。【训练4】按工资和工作月份的乘积排序。输入并执行查询：SELECTempno,ename,sal*Months_between(sysdate,hiredate)AStotalFROMempORDERBYtotal;2021/6/731第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：EMPNOENAMETOTAL-7876ADAMS221526.0067369SMITH222864.6617900JAMES253680.8177654MARTIN336532.484说明：求得雇员工作月份的函数Months_between将在后面介绍。sysdate表示当前日期。【练习1】将部门表中的部门名称按字母顺序显示。2021/6/732第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.3 条件查询条件查询 2.3.1简单条件查询要对显示的行进行限定，可在FROM从句后使用WHERE从句，在WHERE从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真真的记录将被显示。常用的比较运算符列于表2-2中。2021/6/733第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-2比较运算符2021/6/734第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】显示职务为“SALESMAN”的雇员的姓名、职务和工资。输入并执行查询：SELECTename,job,salFROMempWHEREjob=SALESMAN;执行结果为：ENAMEJOBSAL-ALLENSALESMAN1600WARDSALESMAN1250MARTINSALESMAN1250TURNERSALESMAN15002021/6/735第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：结果只显示职务为“SALESMAN”的雇员。字符串和日期型数据的值是包含在单引号中的，如SALESMAN，需要用单引号引起。字符的值对大小写敏感，在emp表中存放的职务字符串全部是大写。注意：在本练习中，如果SALESMAN写成小写或大小写混合，将不会有查询结果输出。2021/6/736第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】显示工资大于等于3000的雇员姓名、职务和工资。输入并执行查询：SELECTename,job,salFROMempWHEREsal=3000;执行结果为：ENAMEJOBSAL-SCOTTANALYST3000KINGPRESIDENT5000FORDANALYST30002021/6/737第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：结果只显示工资大于等于3000的雇员。缺缺省省中中文文日日期期格格式式为为DD-MM月月-YY，如2003年1月10日应该表示为“10-1月-03”。【训练3】显示1982年以后雇佣的雇员姓名和雇佣时间。输入并执行查询：SELECTename,hiredateFROMempWHEREhiredate=1-1月-82;执行结果为：ENAMEHIREDATE-2021/6/738第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iSCOTT19-4月-87ADAMS23-5月-87MILLER23-1月-82说明：检查hiredate字段的内容，都在82年以后。【练习1】显示部门编号为10的雇员姓名和雇佣时间。2021/6/739第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.3.2复合条件查询可以用逻逻辑辑运运算算符符构成复合的条件查询，即把两个或多个条件，用逻辑运算符连接成一个条件。有3个逻辑运算符，如表2-3所示。2021/6/740第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-3逻辑运算符2021/6/741第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号。下面是使用逻辑与运算的练习。1使用逻辑与【训练1】显示工资在10002000之间(不包括1000和2000)的雇员信息。2021/6/742第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i输入并执行查询：SELECT ename, job,sal FROM emp WHERE sal1000 ANDsal1000ANDsal1000AND2000。2021/6/744第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】显示部门10中工资大于1500的雇员。2使用逻辑或下面是使用逻辑或运算的练习。【训练2】显示职务为CLERK或MANAGER的雇员信息。输入并执行查询：SELECT*FROMempWHEREjob=CLERKORjob=MANAGER;执行结果从略。说明：检索职务为CLERK或MANAGER的雇员，需要使用OR运算，请自行察看结果。注意：条件job=CLERKORjob=MANAGER不能写成job=CLERKORMANAGER。2021/6/745第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3使用逻辑非下面是使用逻辑非运算的练习。【训练3】显示部门10以外的其他部门的雇员。输入并执行查询：SELECT*FROMempWHERENOTdeptno=10;执行结果从略。说明：执行结果包含部门编号不等于10的其他部门的雇员，请自行察看结果。2021/6/746第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i4使用逻辑或和逻辑与下面是同时使用逻辑或和逻辑与的复合练习。【训练4】显示部门10和部门20中工资小于1500的雇员。输入并执行查询SELECT * FROM emp WHERE (deptno=10 OR deptno=20)ANDsal0;执行结果为：EMPNOENAMELENGTH(ENAME)-7369SMITH57566JONES 52021/6/777第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】显示雇员名称中包含“S”的雇员名称及名称长度。输入并执行查询：SELECTempno,ename,length(ename)FROMempWHEREinstr(ename,S,1,1)0;执行结果为：EMPNOENAMELENGTH(ENAME)-7369SMITH57566JONES52021/6/778第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：本例在字段列表和查询条件中分别应用了函数length和instr。Length函数返回ename的长度。instr(ename,S1,1)函数返回ename中从第一个字符位置开始，字符串“S”第一次出现的位置。如果函数返回0，则说明ename中不包含字符串“S”；如果函数返回值大于0，则说明ename中包含字符串“S”。2021/6/779第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】显示部门表中部门和所在城市列表，中间以下划线“_”连接，城市名转换成以大写字母开头。2.4.3日期型函数Oracle使用内部数字格式来保存时间和日期，包括世纪、年、月、日、小时、分、秒。缺省日期格式为DD-MON-YY，如“08-05月-03”代表2003年5月8日。SYSDATE是返回系统日期和时间的是返回系统日期和时间的虚列函数虚列函数。使用日期的加减运算，可以实现如下功能：*对日期的值加减一个天数，得到新的日期。2021/6/780第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i*对两个日期相减，得到相隔天数。*通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。还有如表2-7所示的日期函数可以使用。2021/6/781第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-7日期函数2021/6/782第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】返回系统的当前日期。输入并执行查询：SELECTsysdateFROMdual;返回结果为：SYSDATE-06-2月-03说明：该查询返回执行该查询时的数据库服务器的系统当前时间，日期显示格式为默认格式，如“06-2月-03”表示03年2月6日。2021/6/783第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】返回2003年2月的最后一天。输入并执行查询：SELECTlast_day(08-2月-03)FROMdual;返回结果为：LAST_DAY(-28-2月-03说明：该函数给定参数为某月份的任意一天，返回时间为该月份的最后一天。本例中，参数为03年2月8号，返回日期为03年2月28日，是该月的最后一天。2021/6/784第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练3】假定当前的系统日期是2003年2月6日，求再过1000天的日期。输入并执行查询：SELECTsysdate+1000ASNEWDATEFROMdual;返回结果为：NEWDATE-04-11月-05说明：该查询使用到了日期的加法运算，求经过一定天数后的新日期。2021/6/785第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。输入并执行查询：SELECTename,round(sysdate-hiredate)DAYSFROMempWHEREdeptno=10;返回结果为：ENAMEDAYS-CLARK7913KING7752MILLER7685说明：该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。2021/6/786第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】显示雇员名称和雇佣的星期数。【练习2】显示从本年1月1日开始到现在经过的天数(当前时间取SYSDATE的值)。2.4.4转换函数Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER，如表2-8所示。2021/6/787第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-8类型转换函数2021/6/788第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1自动类型转换Oracle可以自动根据具体情况进行如下的转换：*字符串到数值。*字符串到日期。*数值到字符串。*日期到字符串。以下是自动转换的训练。2021/6/789第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】自动转换字符型数据到数值型。输入并执行查询：SELECT12.5+11FROMdual;执行结果为：12.5+11-23.5说明：在本训练中，因为出现+运算符，说明进行的是算术运算，所以字符串12.5被自动转换成数值12.5，然后参加运算。2021/6/790第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】自动转换数值型数据到字符型。执行以下查询：SELECT12.5|11FROMdual;结果为：12.5-12.511说明：在本训练中，因为出现|运算符，说明进行的是字符串连接运算，数值11被自动转换成字符串11，然后参加运算。2021/6/791第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2日期类型转换将日期型转换成字符串时，可以按新的格式显示。如格式YYYY-MM-DDHH24:MI:SS表示“年-月-日小时:分钟:秒”。Oracle的日期类型是包含时间在内的的日期类型是包含时间在内的。主要的日期格式字符的含义如表2-9所示。2021/6/792第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-9日期转换格式字符2021/6/793第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-9日期转换格式字符2021/6/794第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练3】将日期转换成带时间和星期的字符串并显示。执行以下查询：SELECTTO_CHAR(sysdate,YYYY-MM-DDHH24:MI:SSAMDY)FROMdual;结果为：TO_CHAR(SYSDATE,YYYY-MM-DDHH24-2004-02-0715:44:48下午星期六2021/6/795第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：该语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。“YYYY”为4位的年份，“MM”为两位的月份，“DD”为两位的日期，“HH24”表示显示24小时制的小时,“MI”表示显示分钟，“SS”表示显示秒，“AM”表示显示上午或下午(本例中为下午)，“DY”表示显示星期。“-”、“:”和空格原样显示，用于分割日期和时间。转换出来的系统时间为：2004年2月7日(星期六)下午15点44分48秒。还可以按其他的格式显示。以下查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。2021/6/796第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】将日期显示转换成中文的年月日。输入并执行查询：SELECT TO_CHAR(sysdate,YYYY年 MM月 DD日 )FROMdual;执行结果为：TO_CHAR(SYSDAT-2003年11月18日说明：双引号中的中文字“年”、“月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。2021/6/797第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】将雇佣日期转换成字符串并按新格式显示。输入并执行查询：SELECTename, to_char(hiredate, DD Month YYYY)HIREDATEFROM emp;执行结果为：ENAMEHIREDATE-SMITH1712月1980ALLEN202月19812021/6/798第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：Month表示月份的特殊格式，如“12月”。年度用4位显示。对于数字型的日期格式，可以用数字或全拼格式显示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。2021/6/799第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练6】以全拼和序列显示时间。执行以下查询：SELECTSYSDATE,to_char(SYSDATE,yyyysp),to_char(SYSDATE,mmspth),to_char(SYSDATE,ddth)FROMdual;执行结果为：SYSDATETO_CHAR(SYSDATE,YYYYSP)TO_CHAR(TO_C-2021/6/7100第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i07-2月-04twothousandfoursecond07th说明：“twothousandfour”为全拼表示的2004年；“second”为全拼序列表示的2月；“07th”为用序列表示的7号。在格式字符中，前两个字符代表显示结果的大小写。如果格式中的前两个字符是大写，则输出结果的全拼也为大写。如果格式中的前两个字符是小写，则输出结果的全拼也为小写。如果格式中的前两个字符的第一个字符是大写，第二个字符是小写，则输出结果的全拼也为大写开头，后面为字符小写。2021/6/7101第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练7】时间显示的大小写。步骤1：执行以下查询：SELECTSYSDATE,to_char(SYSDATE,yyyysp)FROMdual;结果为：SYSDATETO_CHAR(SYSDATE,YYYYSP)-07-2月-04twothousandfour步骤2：执行以下查询：SELECTto_char(SYSDATE,Yyyysp)FROMdual;结果为：SYSDATETO_CHAR(SYSDATE,YYYYSP)-TwoThousandFour2021/6/7102第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i步骤3：执行以下查询：SELECTSYSDATE,to_char(SYSDATE,YYyysp)FROMdual;结果为：SYSDATETO_CHAR(SYSDATE,YYYYSP)-TWOTHOUSANDFOUR说明：步骤1输出全拼小写的年度，步骤2输出全拼的以大写开头的年度，步骤3输出全拼大写的年度。【练习1】显示2008年的8月8日为星期几。2021/6/7103第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3数字类型转换将数字型转换成字符串时，也可以按新的格式显示。格式字符含义如表2-10所示。2021/6/7104第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-10数值转换符2021/6/7105第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练8】将数值转换成字符串并按新格式显示。执行以下查询：SELECTTO_CHAR(123.45,0000.00),TO_CHAR(12345,L9.9EEEE)FROMdual;结果为：TO_CHAR(TO_CHAR(12345,L9.9-0123.45RMB1.2E+04说明：格式字符串中“0”表示一位数字，转换结果中相应的位置上没有数字则添加0。“.”表示在相应的位置上显示小数点。“L”将以本地货币符号显示于数字前，在本例中本地货币符号为“RMB”。“EEEE”将显示转换为科学计数法。2021/6/7106第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练9】将数值转换成字符串并按新格式显示。执行以下查询：SELECTTO_CHAR(sal,$99,999)SALARYFROMempWHEREename=SCOTT;结果为：SALARY-$4,000说明：格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字，“99，999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗号。如果实际数值位数不足5位，则只显示实际位数，如4000实际位数为4位，则只显示4位。如果实际位数超过5位，则会填充为#号。2021/6/7107第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.4.5其他函数Oracle还有一些函数，如decode和nvl，这些函数也很有用，归纳如表2-11所示。2021/6/7108第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-11其他常用函数2021/6/7109第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1空值的转换空值的转换如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。2021/6/7110第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i如下3个函数，分别用新内容代替字段的空值：nvl(comm,0)：用0代替空的Comm值。nvl(hiredate,01-1月-97)：用1997年1月1日代替空的雇佣日期。nvl(job,无)：用“无”代替空的职务。2021/6/7111第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】使用nvl函数转换空值。执行以下查询：SELECTename,nvl(job,无),nvl(hiredate,01-1月-97),nvl(comm,0)FROMemp;结果为：ENAMENVL(JOB,NNVL(HIREDANVL(COMM,0)-SMITHCLERK17-12月-800ALLENSALESMAN20-2月-81300说明：本例中，空日期将显示为“01-1月-97”，空职务显示为“无”，空津贴将显示为0。2021/6/7112第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2decode函数decode函数可以通过比较进行内容的转换，完成的功能相当于分支语句。该函数的第一个参数为要进行转换的表达式，以后的参数成对出现，最后一个参数可以单独出现。如果第一个参数的值与第二个表达式的值相等，则返回第三个表达式的值；如果不等则继续比较，如果它的值与第四个表达式的值相等，则返回第五个表达式的值，以此类推。在参数的最后位置上可以存在单独的参数，如果以上比较过程没有找到匹配值，则返回该参数的值，如果不存在该参数，则返回NULL。2021/6/7113第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】将职务转换成中文显示。执行以下查询：SELECTename,decode(job,MANAGER,经 理 ,CLERK,职员,SALESMAN,推销员,ANALYST,系统分析员,未知)FROMemp;结果为：ENAMEDECODE(JOB-SMITH职员ALLEN推销员WARD推销员JONES经理2021/6/7114第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iMARTIN推销员BLAKE经理CLARK经理SCOTT系统分析员KING未知TURNER推销员ADAMS职员JAMES职员FORD系统分析员MILLER职员已选择14行。2021/6/7115第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：在以上训练中，如果job字段的内容为“MANAGER”则返回“经理”，如果是“CLERK”则返回“职员”，以此类推。如果不是“MANAGER”、“CLERK”、“SALESMAN”和“ANALYST”之一，则返回“未知”，如KING的职务“PRESIDENT”不在上述范围，返回“未知”。【练习1】对部门表的部门名称和城市名进行转换。2021/6/7116第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3userenv函数函数userenv返回用户环境信息字符串，该函数只有一个字符串类型的参数，参数的内容为如下之一的字符串，可以不区分大小写：*ISDBA：判断会话用户的角色是否为SYSDBA，是则返回TRUE。*INSTANCE：返回会话连接的INSTANCE标识符。*LANGUAGE：返回语言、地区、数据库字符集信息。*LANG：返回会话语言的ISO简称。*TERMINAL：返回正在会话的终端或计算机的标识符。2021/6/7117第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练3】返回用户终端或系统标识信息。执行以下查询：SELECTuserenv(TERMINAL)FROMdual;结果为：ORASERVER说明：根据用户使用的机器不同返回的信息不同，在本例中机器标识符ORASERVER为主机的名称。2021/6/7118第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】返回语言、地区、数据库字符集信息。执行以下查询：SELECTuserenv(LANGUAGE)FROMdual;结果为：SIMPLIFIEDCHINESE_CHINA.ZHS16GBK说明：显示当前用户的语言为简体中文(SIMPLIFIEDCHINESE)，地区为中国(CHINA)，字符集为ZHS16GBK。【练习2】判断用户的角色是否为SYSDBA。2021/6/7119第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i4最大、最小值函数greatest返回参数列表中的最大值，least返回参数列表中的最小值。这两个函数的参数是一个表达式列表，按表达式列表中的第一个表达式的类型对求值后的表达式求得最大或最小值。对字符的比较按ASCII码的顺序进行。如果表达式中有NULL，则返回NULL。2021/6/7120第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】比较字符串的大小，返回最大值。执行以下查询：SELECTgreatest(ABC,ABD,abc,abd)FROMdual;执行结果为：GRE-abd说明：在上述四个字符串中，大小关系为abdabcABDABC。在ASCII码表中，排在后边的字符大，小写字母排在大写字母之后。字符串的比较原则是，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系。2021/6/7121第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.5 高级查询高级查询2.5.1多表联合查询通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句句中中用用比比较较运运算算符符指明连接的条件。2021/6/7122第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛笛卡卡尔尔积积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。两个以上的表也可以进行连接，在这里不做专门介绍。2021/6/7123第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i两个表的连接有四种连接方式：*相等连接。*不等连接。*外连接。*自连接。1相等连接通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。2021/6/7124第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i在下面的练习中，将显示雇员姓名、所在的部门编号和名称，在雇员表emp中是没有雇员的部门名称信息的，只有雇员所在部门的编号，部门的信息在另外的部门表dept中，两个表具有相同的部门编号列deptno，可以通过该列建立相等连接。2021/6/7125第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练1】显示雇员的名称和所在的部门的编号和名称。执行以下查询：SELECTemp.ename,emp.deptno,dept.dnameFROMemp,deptWHEREemp.deptno=dept.deptno;执行结果如下：ENAMEDEPTNODNAME-SMITH20RESEARCHALLEN30SALES2021/6/7126第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：SELECTename,emp.deptno,dnameFROMemp,deptWHEREemp.deptno=dept.deptno;2021/6/7127第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】省略表前缀，察看执行结果。【练习2】执行以下查询(省略表连接条件)并察看执行结果中共有多少记录产生。SELECTename,emp.deptno,dnameFROMemp,dept如果表名很长，可以为表起一个别名，进行简化，别名跟在表名之后，用空格分隔。【训练2】使用表别名。执行以下查询：SELECTename,e.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptno;执行结果同上。2021/6/7128第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：emp表的别名为e，dept表的别名为d。相等连接还可以附加其他的限定条件。相等连接还可以附加其他的限定条件。【训练3】显示工资大于3000的雇员的名称、工资和所在的部门名称。执行以下查询：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDsal3000;显示结果为：ENAMESALDNAME-KING5000ACCOUNTING说明：只显示工资大于3000的雇员的名称、工资和部门名称。在相等连接的条件下增加了工资大于3000的条件。增加的条件用AND连接。2021/6/7129第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2外连接在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外外连连操操作作符符为为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。2021/6/7130第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】使用外连显示不满足相等条件的记录。步骤1：显示雇员的名称、工资和所在的部门名称及没有任何雇员的部门。执行以下查询：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno(+)=dept.deptno;执行结果为：ENAMESALDNAME-SELECTename,sal,dnameFROMemprightouterjoindeptonemp.deptno=dept.deptno;？2021/6/7131第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iCLARK2450ACCOUNTINGKING5000ACCOUNTINGMILLER1300ACCOUNTINGSMITH800RESEARCHADAMS1100RESEARCHFORD3000RESEARCHSCOTT3000RESEARCHJONES2975RESEARCHALLEN1600SALES2021/6/7132第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iBLAKE2850SALESMARTIN 1250SALESJAMES950SALESTURNER 1500SALESWARD1250SALESOPERATIONS2021/6/7133第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i步骤2：显示雇员的名称、工资和所在的部门名称及没有属于任何部门的雇员。执行以下查询：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptno(+);结果从略，请自行执行观察结果。说明：部门OPERATION没有任何雇员。查询结果通过外连显示出该部门。SELECTename,sal,dnameFROMempleftouterjoindeptonemp.deptno=dept.deptno;？2021/6/7134第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3不等连接还可以进行不等的连接。以下是一个训练实例，其中用到的salgrade表的结构如下：DESCsalgrade名称是否为空类型-GRADENUMBERLOSALNUMBERHISALNUMBERGrade表示工资等级，losal和hisal分别表示某等级工资的下限和上限。表的内容为：SELECT*FROMsalgrade;2021/6/7135第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iGRADELOSALHISAL-170012002120114003140120004200130005300199992021/6/7136第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】显示雇员名称，工资和所属工资等级。执行以下查询：SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal；执行结果为：ENAMESALGRADE-2021/6/7137第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iSMITH8001ADAMS11001JAMES9501WARD12502MARTIN12502MILLER13002ALLEN16003TURNER 150032021/6/7138第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iJONES29754BLAKE28504CLARK24504SCOTT30004FORD30004KING50005说明：通过将雇员工资与不同的工资上下限范围相比较，取得工资的等级，并在查询结果中显示出雇员的工资等级。2021/6/7139第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i4自连接最后是一个自连接的训练实例，自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别区别两个相同的表。【训练6】显示雇员名称和雇员的经理名称。执行以下查询：SELECTworker.ename|的经理是|manager.enameAS雇员经理FROMempworker,empmanagerWHEREworker.mgr=manager.empno;执行结果为：雇员经理2021/6/7140第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i-SMITH的经理是FORDALLEN的经理是BLAKEWARD的经理是BLAKE说明：为EMP表分别起了两个别名worker和manager，可以想像，第一个表是雇员表，第二个表是经理表，因为经理也是雇员。然后通过worker表的mgr(经理编号)字段同manager表的empno(雇员编号)字段建立连接，这样就可以显示雇员的经理名称了。注意：经理编号mgr是雇员编号empno之一，所以经理编号可以同雇员编号建立连接。2021/6/7141第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.5.2统计查询统计查询通常需要对数据进行统计，汇总出数据库的统计信息。比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。Oracle提供了一些函数来完成统计工作，这些函数称为组组函函数数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组组函函数数只只能能应应用用于于SELECT子子句句、HAVING子句或子句或ORDER BY子句中。子句中。组函数也可以称为统计函数。2021/6/7142第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i查询公司的总人数需要对整个表应用组函数；查询各个部门的人数，需要对数据进行分组，然后应用组函数进行运算。常用的组函数如表2-12所示。2021/6/7143第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-12常用的组函数2021/6/7144第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值组函数忽略列的空值。使用GROUPBY从句可以对数据进行分组。所所谓谓分分组组，就就是是按按照照列的相同内容，将记录划分成组，对组可以应用组函数。列的相同内容，将记录划分成组，对组可以应用组函数。如果不使用分组，将对整个表或满足条件的记录应用组函数。在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非非NULL值值(可重复)进行运算(COUNT除外)。DISTINCT表示对每一个非非NULL值值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。2021/6/7145第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1统计查询【训练1】求雇员总人数。执行以下查询：SELECTCOUNT(*)FROMemp;返回结果为：COUNT(*)-14说明：该实例中，因为没有WHERE条件，所以对emp表的全部记录应用组函数。使用组函数COUNT统计记录个数，即雇员人数，返回结果为14，代表有14个记录。注意：*代表返回所有行数，否则返回非NULL行数。2021/6/7146第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】求有佣金的雇员人数。执行以下查询：SELECTCOUNT(comm)FROMemp;返回结果为：COUNT(COMM)-4说明：在本例中，没有返回全部雇员，只返回佣金非空的雇员，只有4个人。2021/6/7147第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练3】求部门10的雇员的平均工资。执行以下查询：SELECTAVG(sal)FROMempWHEREdeptno=10;返回结果为：AVG(SAL)-2916.66667说明：增增加加了了WHERE条条件件，WHERE条条件件先先执执行行，结果只对部门10的雇员使用组函数AVG求平均工资。最大值和最小值函数可以应用于日期型数据，以下是训练实例。2021/6/7148第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练4】求最晚和最早雇佣的雇员的雇佣日期。执行以下查询：SELECTMAX(hiredate),MIN(hiredate)FROMemp;返回结果为：MAX(HIREDAMIN(HIREDA-23-5月 -8717-12月-80说明：最晚雇员雇佣的时间为87年5月23日，最早雇员雇佣的时间为80年12月17日。2021/6/7149第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】求雇员表中不同职务的个数。执行以下查询：SELECTCOUNT(DISTINCTjob)FROMemp;返回结果为：COUNT(DISTINCTJOB)-5说明：该查询返回雇员表中不同职务的个数。如果不加DISTINCT，则返回的是职务非空的雇员个数。2021/6/7150第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习1】求部门10中工资大于1500的雇员人数。2分组统计通过下面的训练，我们来了解分组的用法。【训练6】按职务统计工资总和。步骤1：执行以下查询：SELECTSUM(sal)FROMempGROUPBYjob;2021/6/7151第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：SUM(SAL)-600041508275500056002021/6/7152第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i步骤2：执行以下查询：SELECTjob,SUM(sal)FROMempGROUPBYjob;执行结果为：JOBSUM(SAL)-ANALYST6000CLERK4150MANAGER8275PRESIDENT5000SALESMAN56002021/6/7153第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：步骤1按职务对雇员进行分组，有多少种职务就会返回多少行结果，相同职务的工资被汇总到一起，其中使用到了SUM函数对分组后的工资进行求和。以上查询结果没有显示分组后的职务。分组查询允允许许在查查询询列列表表中中包含分分组组列列，对以上实例，因为是按职务job分组的，所以在查询列中可以包含job字段，使统计结果很清楚，如步骤2所示。职务为ANALYST的雇员的总工资为6000，职务为CLERK的雇员的总工资为4150，依此类推。注意：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。2021/6/7154第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习2】查看以下查询的显示结果，并解释原因。SELECTename,job,SUM(sal)FROMempGROUPBYjob;3多列分组统计可以按多列进行分组，以下是按两列进行分组的例子。【训练7】按部门和职务分组统计工资总和。执行以下查询：SELECTdeptno,job,sum(sal)FROMempGROUPBYdeptno,job;执行结果为：DEPTNOJOBSUM(SAL)-2021/6/7155第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK190020MANAGER 297530CLERK95030MANAGER 285030SALESMAN 56002021/6/7156第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：该查询统计每个部门中每种职务的总工资。4分组统计结果限定对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分分组组后后的结果，它它只只能能出出现现在在GROUP BY从从句之后句之后，而而WHERE从句要出现在从句要出现在GROUP BY从句之前从句之前。2021/6/7157第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练8】统计各部门的最高工资，排除最高工资小于3000的部门。执行以下查询：SELECTdeptno,max(sal)FROMempGROUPBYdeptnoHAVINGmax(sal)=3000;执行结果为：DEPTNOMAX(SAL)-1050002030002021/6/7158第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：结果中排除了部门30，因部门30的总工资小于3000。注注意意：HAVING从从句句的的限限定定条条件件中中要要出出现现组组函函数数。如如果果同同时时 使使 用用 WHERE条条 件件 ， 则则 WHERE条条 件件在在 分分 组组之之前前执执 行行，HAVING条件在条件在分组分组后后执行执行。2021/6/7159第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【练习3】统计人数小于4的部门的平均工资。5分组统计结果排序可以使用ORDERBY从句对统计的结果进行排序，ORDERBY从句要出现在语句的最后。【训练9】按职务统计工资总和并排序。执行以下查询：SELECTjob职务,SUM(sal)工资总和FROMempGROUPBYjobORDERBYSUM(sal);执行结果为：职务工资总和2021/6/7160第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i-CLERK4150PRESIDENT5000SALESMAN5600ANALYST 6000MANAGER82752021/6/7161第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i注意：排序使用的是计算列SUM(sal)，也可以使用别名，写成：SELECTjob职务,SUM(sal)工资总和FROMempGROUPBYjobORDERBY工资总和;【练习4】统计各部门的人数，按平均工资排序。2021/6/7162第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i6组函数的嵌套使用在如下训练中，使用了组函数的嵌套。【训练10】求各部门平均工资的最高值。执行以下查询：SELECTmax(avg(sal)FROMempGROUPBYdeptno;执行结果为：MAX(AVG(SAL)-2916.666672021/6/7163第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：该查询先统计各部门的平均工资，然后求得其中的最大值。注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。如下的查询是错误的：SELECT deptno,max(avg(sal) FROM emp GROUP BY deptno;因为各部门平均工资的最高值不应该属于某个部门。【练习5】求每种职务总工资的最低值。2021/6/7164第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.5.3子查询我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。2021/6/7165第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最 先 执 行 。 子 查 询 可 以 在 SELECT、 INSERT、 UPDATE、DELETE等语句中使用。子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。2021/6/7166第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1单行子查询【训练1】查询比SCOTT工资高的雇员名字和工资。执行以下查询：SELECTename,salFROMempWHEREsal(SELECTsalFROMempWHEREempno=7788);执行结果为：ENAMESAL-KING50002021/6/7167第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：在该子查询中查询SCOTT的工资时使用的是他的雇员号，这是因为雇员号在表中是惟一的，而雇员的姓名有可能相重。SCOTT的雇员号为7788。下面的训练实例包含两个子查询。2021/6/7168第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】查询和SCOTT同一部门且比他工资低的雇员名字和工资。执行以下查询：SELECTename,salFROMempWHEREsal(SELECTAVG(sal)FROMemp);执行结果为：ENAMESAL2021/6/7170第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i-JONES2975BLAKE2850CLARK2450SCOTT3000KING5000FORD3000说明：在子查询中出现了组函数。由执行结果可知，在14个雇员中，大于平均工资的有6个。【练习1】查询工资最高的雇员名字和工资。2021/6/7171第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2多行子查询如果子查询返回多行的结果，则我们称它为多行子查询。多行子查询要使用不同的比较运算符号，它们是它们是IN、ANY和和ALL。【训练4】查询工资低于任何一个“CLERK”的工资的雇员信息。执行以下查询：SELECTempno,ename,job,salFROMempWHERE sal ANY (SELECT sal FROM emp WHERE job =CLERK)ANDjobCLERK;2021/6/7172第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：EMPNOENAMEJOBSAL-7521WARDSALESMAN12507654MARTINSALESMAN1250说明：在emp表的雇员中有4个职务为“CLERK”，他们的工资分别是800、1100、950、1300。满足工资小于任何一个“CLERK”的工资的记录有2个，在这里使用了ANY运算符表示小于子查询中的任何一个工资。注意：条件jobCLERK排除了职务是CLERK的雇员本身。2021/6/7173第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练5】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。执行以下查询：SELECTempno,ename,salFROMempWHERE sal ALL(SELECT sal FROM emp WHERE job=SALESMAN);2021/6/7174第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：EMPNOENAMESAL-7566JONES29757698BLAKE28507782CLARK24507788SCOTT30007839KING50007902FORD3000说明：在emp表的雇员中有4个职务为“SALESMAN”，他们的工资分别是1600、1250、1250、1500。在这里使用了ALL运算符，表示大于查询中所有的工资。2021/6/7175第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练6】查询部门20中职务同部门10的雇员一样的雇员信息。执行以下查询：SELECTempno,ename,jobFROMempWHERE job IN (SELECT job FROM emp WHEREdeptno=10)ANDdeptno=20;2021/6/7176第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：EMPNOENAMEJOB-7369SMITH CLERK7876ADAMSCLERK7566JONES MANAGER说明：在该训练中，使用IN运算符表示职务是子查询结果中的任何一个。部门10中有3种职务：MANAGER、PRESIDENT和CLERK，以上查询得到的是部门20中是这3种职务的雇员。2021/6/7177第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练7】查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息。执行以下查询：SELECTempno,ename,jobFROMempWHEREjob=(SELECTjobFROMempWHEREempno=7788)AND hiredate (SELECT hiredate FROM emp WHEREempno=7788);执行结果为：EMPNOENAMEJOB-7902FORDANALYST说明：在查询中用到了时间的比较。【练习2】查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字。2021/6/7178第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i3多列子查询如果子查询返回多列，则对应的比较条件中也应该出现多列，这种查询称为多列子查询。以下是多列子查询的训练实例。【训练8】查询职务和部门与SCOTT相同的雇员的信息。执行以下查询：SELECTempno,ename,salFROMempWHERE (job,deptno) =(SELECT job,deptno FROM empWHEREempno=7788);2021/6/7179第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：EMPNOENAMEJOB-7902FORDANALYST说明：在该例的子查询中返回两列，查询条件中也要出现两列，表示雇员的职务和部门应该和SCOTT的职务和部门相同。2021/6/7180第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i4在FROM从句中使用子查询在FROM从句中也可以使用子查询，在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员，很容易想到的是使用rownum虚列。比如我们要求显示雇员表中69位置上的雇员，可以用以下方法。2021/6/7181第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练9】查询雇员表中排在第69位置上的雇员。执行以下查询：SELECTename,salFROM(SELECTrownumasnum,ename,salFROMempWHERErownum=6;执行结果为：ENAMESAL-BLAKE2850CLARK2450SCOTT3000KING50002021/6/7182第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：子查询出现在FROM从句中，检索出行号小于等于9的雇员，并生成num编号列。在主查询中检索行号大于等于6的雇员。？注意：以下用法不会有查询结果，请自行分析原因。SELECTename,salFROMempWHERErownum=6ANDrownum=9;【练习3】查询雇员表中的第6个雇员。2021/6/7183第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.5.4集合运算*多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。Oracle共有4个集合操作，如表2-13所示。2021/6/7184第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i表2-13集合运算操作2021/6/7185第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i1使用集合的并运算【训练1】查询部门10和部门20的所有职务。执行以下查询：SELECTjobFROMempWHEREdeptno=10UNIONSELECTjobFROMempWHEREdeptno=20;2021/6/7186第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：JOB-ANALYSTCLERKMANAGERPRESIDENT说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的所有职务(相同职务只算一个)共有4个：ANALYST、CLERK、MANAGER和PRESIDENT。可以将UNION改为UNIONALL查看一下结果。2021/6/7187第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2使用集合的交运算【训练2】查询部门10和20中是否有相同的职务和工资。执行以下查询：SELECTjob,salFROMempWHEREdeptno=10INTERSECTSELECTjob,salFROMempWHEREdeptno=20;执行结果为：未选定行2021/6/7188第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的相同职务为：CLERK和MANAGER。但是职务和工资都相同的雇员没有，所以没有结果。3使用集合的差运算【训练3】查询只在部门表中出现，但没有在雇员表中出现的部门编号。执行以下查询：SELECTdeptnoFROMdeptMINUSSELECTdeptnoFROMemp;2021/6/7189第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为：DEPTNO-40说明：部门表中的部门编号有10、20、30和40。雇员表中的部门编号有10、20和30。差集的结果为40。【练习1】查询具有职务CLERK和SALESMAN的所有部门编号。【练习2】试求部门10和20中不相同的职务(即部门10中有、部门20中没有和部门20中有、部门10中没有的职务)。2021/6/7190第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.6 阶段训练阶段训练？【训练1】显示人数最多的部门名称。输入并执行以下查询：SELECTDECODE(dname,SALES,销售部,ACCOUNTING,财务部,RESEARCH,研发部,未知)部门名FROMemp,dept2021/6/7191第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iWHEREemp.deptno=dept.deptnoGROUPBYdnameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*)FROMempGROUPBYdeptno);执行结果：部门名-销售部说明：本训练使用了分组统计、相等连接和子查询，使用了DECODE函数进行部门名称转换。2021/6/7192第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i【训练2】显示各部门的平均工资、最高工资、最低工资和总工资列表，并按平均工资高低顺序排序。输入并执行以下查询：SELECT dname 部门,AVG(sal) 平均工资,MAX(sal) 最高工资,MIN(sal)最低工资,SUM(sal)总工资FROMemp,deptWHEREemp.deptno=dept.deptnoGROUPBYdnameORDERBYAVG(sal)DESC;2021/6/7193第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i执行结果为部门平均工资最高工资最低工资总工资-ACCOUNTING2916.66667500013008750RESEARCH2175300080010875SALES1566.6666728509509400说明：本训练使用了分组统计、相等连接和排序，使用相等连接可以通过部门编号获取部门名称。2021/6/7194第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2.7 练习练习1SQL语言中用来创建、删除及修改数据库对象的部分被称为：A.数据库控制语言(DCL)B.数据库定义语言(DDL)C.数据库操纵语言(DML)D.数据库事务处理语言2021/6/7195第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i2执行以下查询，表头的显示为：SELECTsalEmployeeSalaryFROMempA.EMPLOYEESALARYB.employeesalaryC.EmployeeSalaryD.EmployeeSalary“3执行如下两个查询，结果为：SELECTenamename,salsalaryFROMemporderbysalary;SELECTenamename,salSALARYFROMemporderbysalASC;2021/6/7196第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9iA.两个查询结果完全相同B.两个查询结果不相同C.第一个查询正确，第二个查询错误D.第二个查询正确，第一个查询错误4参考本章的emp表的内容执行下列查询语句，出现在第一行上的人是：SELECT ename FROM emp WHERE deptno=10 ORDER BY salDESC;A.SMITHB.KINGC.MILLERD.CLARK2021/6/7197第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i？5哪个函数与|运算有相同的功能：A.LTRIMB.CONCATC.SUBSTRD.INSTR6执行以下语句后，正确的结论是：SELECTempno,enameFROMempWHEREhiredate1500;A.第一行B.第二行C.第三行D.第四行2021/6/7199第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i？8执行以下语句出错的行是：SELECTdeptno,max(avg(sal)FROMempWHEREsal1000Groupbydeptno;A.第一行B.第二行C.第三行D.第四行2021/6/7200第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i？9执行以下语句出错的行是：SELECTdeptno,dname,ename,salFROMemp,deptWHEREemp.deptno=dept.deptnoANDsal1000;A.第一行B.第二行C.第三行D.第四行2021/6/7201第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i10以下语句出错，哪种改动能够正确执行：SELECTdeptno,max(sal)FROMempGROUPBYdeptnoWHEREmax(sal)2500;A.将WHERE和GROUPBY语句顺序调换一下B.将WHEREmax(sal)2500语句改成HAVINGmax(sal)2500C.将WHEREmax(sal)2500语句改成WHEREsal2500D.将WHEREmax(sal)2500语句改成HAVINGsal25002021/6/7202第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i11以下语句的作用是：SELECTename,salFROMempWHEREsal(SELECTmin(sal)FROMemp)+1000;A.显示工资低于1000元的雇员信息B.将雇员工资小于1000元的工资增加1000后显示C.显示超过最低工资1000元的雇员信息D.显示不超过最低工资1000元的雇员信息2021/6/7203第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i？12以下语句的作用是：SELECTjobFROMempWHEREdeptno=10MINUSSELECTjobFROMempWHEREdeptno=20;A.显示部门10的雇员职务和20的雇员职务B.显示部门10和部门20共同的雇员职务C.显示部门10和部门20不同的雇员职务D.显示在部门10中出现，在部门20中不出现的雇员职务2021/6/7204第第2 2章章 数据查询数据查询 ORACLE 9i ORACLE 9iORACLE9i部分资料从网络收集整理而来，供大家参考，感谢您的关注！