《Oracle数据库SQL和PL SQL实例教程》课件第2章 数据查询.ppt

1、 第第2章章 数据查询数据查询第第2章章 数据查询数据查询2.1 数据库查询语言数据库查询语言SQL2.2 基本查询和排序基本查询和排序2.3 条件查询条件查询 2.4 函数函数 2.5 高级查询高级查询2.6 阶段训练阶段训练2.7 练习练习 第第2章章 数据查询数据查询2.1 数据库查询语言数据库查询语言SQL2.1.1 SQL语言的特点和分类SQL语言有以下的主要特点：*SQL语言可以在Oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。*SQL语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部

2、分构成。第第2章章 数据查询数据查询*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语言的分类和功能简介。第第2章章 数据查询数据查询表2-1 SQL语言的分类类 别 功

3、能 举 例 数据库控制 语言(DCL)控 制 对 数据 库 的访 问，启动 和 关 闭 等 对系统权限进行授权和回收的 G RANT、REVOKE等 语 句 数据库定义 语言(DDL)用 来 创 建、删 除及 修 改 数据 库 对 象 创建表和索引的 CREA TE TABLE、ALTER INDEX等 语 句 数据库操纵 语言(DM L)用 来 操 纵数 据 库的 内 容，包 括 查 询 查 询、插 入、删 除、修 改 和 锁 定 操 作 的SELECT、INSER T、UPDA TE、DELETE、LOCK TABLE 等语 句 数 据 库 事 务 处 理 实 现 对 数据 的 交易 过

4、程 的完 整 控 制 与数据库事物处理相关的 COM M IT、ROLLBACK、SA VEPOINT、SET TRANSACTION等 语 句 第第2章章 数据查询数据查询2.1.2 SQL的基本语法SQL语言的语法比较简单，类似于书写英文的语句。其语句一般由主句和若干个从句组成，主句和从句都由关键字引导。主句表示该语句的主要功能，从句表示一些条件或限定，有些从句是可以省略的。在语句中会引用到列名、表名或表达式。另外还有如下一些说明：*关键字、字段名、表名等之间都要用空格或逗号等进行必要的分隔。*语句的大小写不敏感(查询的内容除外)。*语句可以写在一行或多行。*语句中的关键字不能略写和分开写

5、在两行。第第2章章 数据查询数据查询*要在每条SQL语句的结束处添加“；”号。*为了提高可读性，可以使用缩进。*从句一般写在另一行的开始处。查询语句是最常见的SQL语句，它从给定的表中，把满足条件的内容检索出来。以下是最基本的SELECT语句语法。SELECT 字段名列表 FROM 表名 WHERE 条件;SELECT为查询语句的关键字，后跟要查询的字段名列表，字段名列表用来指定检索特定的字段，该关键字不能省略。第第2章章 数据查询数据查询字段名列表代表要查询的字段。FROM 也是查询语句关键字，后面跟要查询的表名，该关键字不能省略。WHERE条件限定检索特定的记录，满足“条件”的记录被显示出

6、来，不满足条件的被过滤掉。语句查询的结果往往是表的一部分行和列。如果字段名列表使用*，将检索全部的字段。如果省略WHERE条件，将检索全部的记录。第第2章章 数据查询数据查询【训练1】查询部门10的雇员。输入并执行查询：SELECT*FROM emp WHERE deptno=10;结果略。说明：该查询语句从emp表中检索出部门10 的雇员，条件由WHERE deptno=10 子句指定。第第2章章 数据查询数据查询2.2 基本查询和排序基本查询和排序2.2.1 查询的基本用法在Oracle数据库中，对象是属于模式的，每个账户对应一个模式，模式的名称就是账户名称。在表名前面要添加模式的名字，在

7、表的模式名和表名之间用“.”分隔。我们以不同的账户登录数据库时，就进入了不同的模式，比如登录到STUDENT 账户，就进入了STUDENT模式。而在STUDENT模式要查询属于SCOTT模式的表，就需要写成：SELECT*FROM SCOTT.EMP;第第2章章 数据查询数据查询但如果登录用户访问属于用户模式本身的表，那么可以省略表名前面的模式名称。SELECT*FROM emp;1指定检索字段下面的练习，只显示表的指定字段。【训练1】显示DEPT表的指定字段的查询。输入并执行查询：SELECT deptno,dname FROM dept;第第2章章 数据查询数据查询显示结果如下：DEPTN

8、O DNAME-10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 说明：结果只包含2列deptno和dname。在语句中给出要显示的列名，列名之间用“，”分隔。表头的显示默认为全部大写。对于日期和数值型数据，右对齐显示，如deptno列。对于字符型数据，左对齐显示，如dname列。第第2章章 数据查询数据查询【练习1】显示emp表的雇员名称和工资。2显示行号每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。【训练2】显示EMP表的行号。输入并执行查询：SELECT rownum,ename FROM emp

9、;第第2章章 数据查询数据查询结果如下：ROWNUM ENAME-1 SMITH 2 ALLEN 3 WARD 4 JONES 注意：显示的行号是查询结果的行号，数据在数据库中是没有行号的。第第2章章 数据查询数据查询3显示计算列在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。常用的算术运算符包括：*+：加法运算符。*?：减法运算符。*：乘法运算符。*/：除法运算符。以下训练在查询中使用了计算列。第第2章章 数据查询数据查询【训练3】显示雇员工资上浮20%的结果。输入并执行查询:SELECT

10、 ename,sal,sal*(1+20/100)FROM emp;显示结果为：ENAME SAL SAL*(1+20/100)-SMITH 800 960ALLEN 1600 1920说明：结果中共显示了3列，第3列显示工资上浮20%的结果，它不是表中存在的列，而是计算产生的结果，称为计算列。第第2章章 数据查询数据查询【练习2】显示EMP表的雇员名称以及工资和津贴的和。4使用别名我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。【训练4】在查询中使用列别名。输入并执行：SELECT ename AS 名称,

11、sal 工资 FROM emp;显示结果为：名称 工资-SMITH 800ALLEN 1600 第第2章章 数据查询数据查询说明：表头显示的是列别名，转换为汉字显示。在列名和别名之间要用AS分隔，如ename和它的别名“名称”之间用AS隔开。AS也可以省略，如sal和它的别名“工资”之间用空格分割。注意：如果用空格分割，要区别好列名和别名，前面为列名，后面是别名。别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。第第2章章 数据查询数据查询【训练5】在列别名上使用双引号。输入并执行查询：SELECT ename AS Name,sal*12+5000 AS 年度工资(加年终奖)

12、FROM emp;显示结果为：Name 年度工资(加年终奖)-SMITH 14600ALLEN 24200 第第2章章 数据查询数据查询说明：其中别名“Name”有大小写的区别，别名“年度工资(加年终奖)”中出现括号，属于特殊符号，所以都需要使用双引号将别名引起。【练习3】显示DEPT表的内容，使用别名将表头转换成中文显示。5连接运算符在前面，我们使用到了包含数值运算的计算列，显示结果也是数值型的。我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“|”。通过连接运算可以将两个字符串连接在一起。第第2章章 数据查询数据查询【训练6】在查询中使用连接运算。输入并执行查询

13、：SELECTename|job AS 雇员和职务表 FROM emp;输出结果为：雇员和职务表-SMITHCLERKALLENSALESMAN 第第2章章 数据查询数据查询说明：在本例中，雇员名称和职务列被连接成为一个列显示。在查询中可以使用字符和日期的常量，表示固定的字符串或固定日期。字符和日期的常量需要用单引号引起。下一个训练是作为上一个训练的改进。第第2章章 数据查询数据查询【训练7】在查询中使用字符串常量。输入并执行查询：SELECTename|IS|job AS 雇员和职务表 FROM emp;输出结果为：雇员和职务表-SMITH IS CLERKALLEN IS SALESMAN

14、说明：本练习中将雇员名称、字符串常量“IS”和雇员职务3个部分连接在一起。第第2章章 数据查询数据查询【练习4】显示DEPT表的内容，按以下的形式:部门ACCOUNTING所在的城市为NEW YORK 6消除重复行如果在显示结果中存在重复行，可以使用的关键字DISTINCT消除重复显示。第第2章章 数据查询数据查询【训练8】使用DISTINCT消除重复行显示。输入并执行查询：SELECT DISTINCT job FROM emp;结果为：JOB-ANALYSTCLERKMANAGERPRESIDENTSALESMAN 第第2章章 数据查询数据查询说明：在本例中，如果不使用DISTINCT关键

15、字，将重复显示雇员职务，DISTINCT关键字要紧跟在SELECT之后。请去掉DISTINCT关键字，重新执行，并观察显示结果的不同。【练习5】显示EMP表中不同的部门编号。2.2.2 查询结果的排序如果要在查询的同时排序显示结果，可以使用如下的语句：SELECT 字段列表 FROM 表名 WHERE 条件ORDER BY 字段名1 ASC|DESC,字段名2 ASC|DESC.;第第2章章 数据查询数据查询ORDER BY从句后跟要排序的列。ORDER BY 从句出现在SELECT语句的最后。排序有升序和降序之分，ASC表示升序排序，DESC表示降序排序。如果不指明排序顺序，默认的排序顺序为

16、升序。如果要降序，必须书写DESC关键字。第第2章章 数据查询数据查询1升序排序【训练1】查询雇员姓名和工资，并按工资从小到大排序。输入并执行查询：SELECT ename,sal FROM emp ORDER BY sal;执行结果为：ENAME SAL-SMITH 800JAMES 950注意：若省略ASC和DESC，则默认为ASC，即升序排序。第第2章章 数据查询数据查询2降序排序【训练2】查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。输入并执行查询：SELECT ename,hiredate FROM emp ORDER BY hiredate DESC;结果如下：ENAM

17、E HIREDATE-ADAMS 23-5月-87SCOTT 19-4月-87 第第2章章 数据查询数据查询MILLER 23-1月-82JAMES 03-12月-81FORD 03-12月-81注意：DESC表示降序排序，不能省略。3多列排序可以按多列进行排序，先按第一列，然后按第二列、第三列.。【训练3】查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。输入并执行查询：SELECT ename,deptno,hiredate FROM emp ORDER BY deptno,hiredate;第第2章章 数据查询数据查询结果如下：ENAME DEPTNO HIREDATE-CLA

18、RK 10 09-6月-81KING 10 17-11月-81MILLER 10 23-1月-82SMITH 20 17-12月-80JONES 20 02-4月-81FORD 20 03-12月-81SCOTT 20 19-4月-87说明：该排序是先按部门升序排序，部门相同的情况下，再按雇佣时间升序排序。第第2章章 数据查询数据查询4在排序中使用别名如果要对计算列排序，可以为计算列指定别名，然后按别名排序。【训练4】按工资和工作月份的乘积排序。输入并执行查询：S E L E C T e m p n o,e n a m e,sal*Months_between(sysdate,hiredate

19、)AS total FROM emp ORDER BY total;第第2章章 数据查询数据查询执行结果为：EMPNO ENAME TOTAL-7876ADAMS 221526.006 7369 SMITH 222864.661 7900 JAMES 253680.817 7654 MARTIN 336532.484说明：求得雇员工作月份的函数Months_between将在后面介绍。sysdate表示当前日期。【练习1】将部门表中的部门名称按字母顺序显示。第第2章章 数据查询数据查询2.3 条件查询条件查询 2.3.1 简单条件查询要对显示的行进行限定，可在FROM从句后使用WHERE从句，

20、在WHERE从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真的记录将被显示。常用的比较运算符列于表2-2中。第第2章章 数据查询数据查询表2-2 比 较 运 算 符运算符 功 能 实 例,2000=.=2000=等于 Select*from emp where deptno=10!=,=不等于 Select*from emp where deptno!=10 第第2章章 数据查询数据查询【训练1】显示职务为“SALESMAN”的雇员的姓名、职务和工资。输入并执行查询：SELECT ename,job,sal FROM emp WH

21、ERE job=SALESMAN;执行结果为：ENAME JOB SAL-ALLEN SALESMAN 1600WARD SALESMAN 1250MARTIN SALESMAN 1250TURNER SALESMAN 1500 第第2章章 数据查询数据查询说明：结果只显示职务为“SALESMAN”的雇员。字符串和日期型数据的值是包含在单引号中的，如SALESMAN，需要用单引号引起。字符的值对大小写敏感，在emp表中存放的职务字符串全部是大写。注意：在本练习中，如果SALESMAN写成小写或大小写混合，将不会有查询结果输出。第第2章章 数据查询数据查询【训练2】显示工资大于等于3000的雇员

22、姓名、职务和工资。输入并执行查询：SELECT ename,job,sal FROM emp WHERE sal=3000;执行结果为：ENAME JOB SAL-SCOTT ANALYST 3000KING PRESIDENT 5000FORD ANALYST 3000 第第2章章 数据查询数据查询说明：结果只显示工资大于等于3000的雇员。缺省中文日期格式为DD-MM月-YY，如2003年1月10日应该表示为“10-1月-03”。【训练3】显示1982年以后雇佣的雇员姓名和雇佣时间。输入并执行查询：SELECT ename,hiredate FROM emp WHERE hiredate=

23、1-1月-82;执行结果为：ENAME HIREDATE-第第2章章 数据查询数据查询SCOTT 19-4月-87ADAMS 23-5月-87MILLER 23-1月-82 说明：检查hiredate字段的内容，都在82年以后。【练习1】显示部门编号为10的雇员姓名和雇佣时间。第第2章章 数据查询数据查询2.3.2 复合条件查询可以用逻辑运算符构成复合的条件查询，即把两个或多个条件，用逻辑运算符连接成一个条件。有3个逻辑运算符，如表2-3所示。第第2章章 数据查询数据查询表2-3 逻辑运算符运算符 说 明 实 例 AND 逻辑与，表示两个条件必须同时满足 Select*from emp whe

24、re sal1000 and sal1000 AND sal1000 AND sal1000 AND 2000。第第2章章 数据查询数据查询【练习1】显示部门10中工资大于1500的雇员。2使用逻辑或下面是使用逻辑或运算的练习。【训练2】显示职务为CLERK或MANAGER的雇员信息。输入并执行查询：SELECT*FROM emp WHERE job=CLERK OR job=MANAGER;执行结果从略。说明：检索职务为CLERK或MANAGER的雇员，需要使用OR运算，请自行察看结果。注意：条件job=CLERK OR job=MANAGER不能写成job=CLERK OR MANAGER

25、。第第2章章 数据查询数据查询3使用逻辑非下面是使用逻辑非运算的练习。【训练3】显示部门10以外的其他部门的雇员。输入并执行查询：SELECT*FROM emp WHERE NOT deptno=10;执行结果从略。说明：执行结果包含部门编号不等于10的其他部门的雇员，请自行察看结果。第第2章章 数据查询数据查询4使用逻辑或和逻辑与下面是同时使用逻辑或和逻辑与的复合练习。【训练4】显示部门10和部门20中工资小于1500的雇员。输入并执行查询SELECT*FROM emp WHERE(deptno=10 OR deptno=20)AND sal=、等 Select*from emp Where

26、 sal=、等 Select*from emp Where sal0;执行结果为：EMPNO ENAME LENGTH(ENAME)-7369 SMITH 5 7566 JONES 5 第第2章章 数据查询数据查询【训练4】显示雇员名称中包含“S”的雇员名称及名称长度。输入并执行查询：SELECT empno,ename,length(ename)FROM empWHERE instr(ename,S,1,1)0;执行结果为：EMPNO ENAME LENGTH(ENAME)-7369 SMITH 5 7566 JONES 5 第第2章章 数据查询数据查询说明：本例在字段列表和查询条件中分别应

27、用了函数length和instr。Length函数返回ename的长度。instr(ename,S1,1)函数返回ename中从第一个字符位置开始，字符串“S”第一次出现的位置。如果函数返回0，则说明ename中不包含字符串“S”；如果函数返回值大于0，则说明ename中包含字符串“S”。第第2章章 数据查询数据查询【练习1】显示部门表中部门和所在城市列表，中间以下划线“_”连接，城市名转换成以大写字母开头。2.4.3 日期型函数Oracle使用内部数字格式来保存时间和日期，包括世纪、年、月、日、小时、分、秒。缺省日期格式为 DD-MON-YY，如“08-05月-03”代表2003年5月8日。

28、SYSDATE是返回系统日期和时间的虚列函数。使用日期的加减运算，可以实现如下功能：*对日期的值加减一个天数，得到新的日期。第第2章章 数据查询数据查询*对两个日期相减，得到相隔天数。*通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。还有如表2-7所示的日期函数可以使用。第第2章章 数据查询数据查询表2-7 日期函数函 数 功 能 实 例 结 果 months_between 返回两个日期间的 月份 months_between(04-11月-05,11-1月-01)57.7741935 add_months 返回把月份数加到日期上的新日期 add_months

29、(06-2月-03,1)add_months(06-2月-03,-1)06-3月-03 06-1月-03 next_day 返回指定日期后的星期对应的新日期 next_day(06-2月-03,星期一)10-2月-03 last_day 返回指定日期所在的月的最后一天 last_day(06-2月-03)28-2月-03 round 按指定格式对日期进行四舍五入 round(to_date(13-2月-03),YEAR)round(to_date(13-2月-03),MONTH)round(to_date(13-2月-03),DAY)01-1月-03 01-2月-03 16-2月-03(按周四

30、舍五入)trunc 对日期按指定方式进行截断 trunc(to_date(06-2月-03),YEAR)trunc(to_date(06-2月-03),MONTH)trunc(to_date(06-2月-03),DAY)01-1月-03 01-2月-03 02-2月-03(按周截断)第第2章章 数据查询数据查询【训练1】返回系统的当前日期。输入并执行查询：SELECT sysdate FROM dual;返回结果为：SYSDATE-06-2月-03说明：该查询返回执行该查询时的数据库服务器的系统当前时间，日期显示格式为默认格式，如“06-2月-03”表示03年2月6日。第第2章章 数据查询数据

31、查询【训练2】返回2003年2月的最后一天。输入并执行查询：SELECT last_day(08-2月-03)FROM dual;返回结果为：LAST_DAY(-28-2月-03说明：该函数给定参数为某月份的任意一天，返回时间为该月份的最后一天。本例中，参数为03年2月8号，返回日期为03年2月28日，是该月的最后一天。第第2章章 数据查询数据查询【训练3】假定当前的系统日期是2003年2月6日，求再过1000天的日期。输入并执行查询：SELECT sysdate+1000 AS NEW DATE FROM dual;返回结果为：NEW DATE-04-11月-05 说明：该查询使用到了日期的

32、加法运算，求经过一定天数后的新日期。第第2章章 数据查询数据查询【训练4】假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。输入并执行查询：SELECT ename,round(sysdate-hiredate)DAYSFROM empWHERE deptno=10;返回结果为：ENAME DAYS-CLARK 7913KING 7752MILLER 7685说明：该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。第第2章章 数据查询数据查询【练习1】显示雇员名称和雇佣的星期数。【练习2】显示从本年1月1日开始到现在经过的天数(当前时间取SYS

33、DATE的值)。2.4.4 转换函数Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER，如表2-8所示。第第2章章 数据查询数据查询表2-8 类型转换函数函 数 功 能 实 例 结 果 To_char 转换成字符串类型 To_char(1234.5,$9999.9)$1234.5 To_date 转换成日期类型 To_date(1980-01-01,yyyy-mm-dd)01-1月-80 To_number 转换成数值类型 To_number(1234.5)1234.5 第第2章章 数据查询数据查询1自动类型转换Orac

34、le可以自动根据具体情况进行如下的转换：*字符串到数值。*字符串到日期。*数值到字符串。*日期到字符串。以下是自动转换的训练。第第2章章 数据查询数据查询【训练1】自动转换字符型数据到数值型。输入并执行查询：SELECT 12.5+11 FROM dual;执行结果为：12.5+11-23.5说明：在本训练中，因为出现+运算符，说明进行的是算术运算，所以字符串12.5被自动转换成数值12.5，然后参加运算。第第2章章 数据查询数据查询【训练2】自动转换数值型数据到字符型。执行以下查询：SELECT 12.5|11 FROM dual;结果为：12.5-12.511说明：在本训练中，因为出现|运

35、算符，说明进行的是字符串连接运算，数值11被自动转换成字符串11，然后参加运算。第第2章章 数据查询数据查询2日期类型转换将日期型转换成字符串时，可以按新的格式显示。如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。主要的日期格式字符的含义如表2-9所示。第第2章章 数据查询数据查询表2-9 日期转换格式字符代 码 代表的格式 例 子 AM、PM 上午、下午 08 AM D 数字表示的星期(17)1,2,3,4,5,6,7 DD 数字表示月中的日期(131)1,2,3,31 MM 两位数的月份 01,02,12 Y、YY

36、、YYY、YYYY 年份的后几位 3,03,003,2003 RR 解决 Y2K 问题的年度转换 DY 简写的星期名 MON,TUE,FRI,DAY 全拼的星期名 MONDAY,TUESDAY,第第2章章 数据查询数据查询表2-9 日期转换格式字符MON 简写的月份名 JAN,FEB,MAR,MONTH 全拼的月份名 JANUARY,FEBRUARY,HH、HH12 12小时制的小时(112)1,2,3,12 HH24 24小时制的小时(023)0,1,2,23 MI 分(059)0,1,2,59 SS 秒(059)0,1,2,59,./-;:原样显示的标点符号 TEXT 引号中的文本原样显示

37、 TEXT 第第2章章 数据查询数据查询【训练3】将日期转换成带时间和星期的字符串并显示。执行以下查询：SELECT TO_CHAR(sysdate,YYYY-MM-DD HH24:MI:SS AM DY)FROM dual;结果为：TO_CHAR(SYSDATE,YYYY-MM-DD HH24-2004-02-07 15:44:48 下午 星期六 第第2章章 数据查询数据查询说明：该语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。“YYYY”为4位的年份，“MM”为两位的月份，“DD”为两位的日期，“HH24”表示显示24小时制的小时,“MI”

38、表示显示分钟，“SS”表示显示秒，“AM”表示显示上午或下午(本例中为下午)，“DY”表示显示星期。“-”、“:”和空格原样显示，用于分割日期和时间。转换出来的系统时间为：2004年2月7日(星期六)下午15点44分48秒。还可以按其他的格式显示。以下查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。第第2章章 数据查询数据查询【训练4】将日期显示转换成中文的年月日。输入并执行查询：SELECT TO_CHAR(sysdate,YYYY年MM月DD日)FROM dual;执行结果为：TO_CHAR(SYSDAT-2003年11月18日说明：双引号中的中文字“年”、“

39、月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。第第2章章 数据查询数据查询【训练5】将雇佣日期转换成字符串并按新格式显示。输入并执行查询：SELECTename,to_char(hiredate,DD Month YYYY)HIREDATEFROM emp;执行结果为：ENAME HIREDATE-SMITH 17 12月 1980ALLEN 20 2月 1981 第第2章章 数据查询数据查询说明：Month表示月份的特殊格式，如“12月”。年度用4位显示。对于数字型的日期格式，可以用数字或全拼格式显示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。第第

40、2章章 数据查询数据查询【训练6】以全拼和序列显示时间。执行以下查询：SELECT SYSDATE,to_char(SYSDATE,yyyysp),to_char(SYSDATE,mmspth),to_char(SYSDATE,ddth)FROM dual;执行结果为：SYSDATE TO_CHAR(SYSDATE,YYYYSP)TO_CHAR(TO_C-第第2章章 数据查询数据查询07-2月-04 two thousand four second 07th说明：“two thousand four”为全拼表示的2004年；“second”为全拼序列表示的2月；“07th”为用序列表示的7号。

41、在格式字符中，前两个字符代表显示结果的大小写。如果格式中的前两个字符是大写，则输出结果的全拼也为大写。如果格式中的前两个字符是小写，则输出结果的全拼也为小写。如果格式中的前两个字符的第一个字符是大写，第二个字符是小写，则输出结果的全拼也为大写开头，后面为字符小写。第第2章章 数据查询数据查询【训练7】时间显示的大小写。步骤1：执行以下查询：SELECT SYSDATE,to_char(SYSDATE,yyyysp)FROM dual;结果为：SYSDATE TO_CHAR(SYSDATE,YYYYSP)-07-2月-04 two thousand four步骤2：执行以下查询：SELECT t

42、o_char(SYSDATE,Yyyysp)FROM dual;结果为：SYSDATE TO_CHAR(SYSDATE,YYYYSP)-Two Thousand Four 第第2章章 数据查询数据查询步骤3：执行以下查询：SELECT SYSDATE,to_char(SYSDATE,YYyysp)FROM dual;结果为：SYSDATE TO_CHAR(SYSDATE,YYYYSP)-TWO THOUSAND FOUR说明：步骤1输出全拼小写的年度，步骤2输出全拼的以大写开头的年度，步骤3输出全拼大写的年度。【练习1】显示2008年的8月8日为星期几。第第2章章 数据查询数据查询3数字类型转

43、换 将数字型转换成字符串时，也可以按新的格式显示。格式字符含义如表2-10所示。第第2章章 数据查询数据查询表2-10 数值转换符代 码 代表的格式 例 子 9 代表一位数字，如果是正数，前面是空格，如果是负数，前面是?号 9999 0 代表一位数字，在相应的位置上如果没有数字则出现 0 0000,逗号，用作组分隔符 99,999.小数点，分隔整数和小数 999.9$货币符号$999.9 L 本地货币符号 L999.99 FM 去掉前后的空格 FM999.99 EEEE 科学计数法 9.9EEEE S 负数符号?放在开头 S999.9 第第2章章 数据查询数据查询【训练8】将数值转换成字符串并

44、按新格式显示。执行以下查询：S E L E C T T O _ C H A R(1 2 3.4 5,0 0 0 0.0 0 ),TO_CHAR(12345,L9.9EEEE)FROM dual;结果为：TO_CHAR(TO_CHAR(12345,L9.9-0123.45 RMB1.2E+04说明：格式字符串中“0”表示一位数字，转换结果中相应的位置上没有数字则添加0。“.”表示在相应的位置上显示小数点。“L”将以本地货币符号显示于数字前，在本例中本地货币符号为“RMB”。“EEEE”将显示转换为科学计数法。第第2章章 数据查询数据查询【训练9】将数值转换成字符串并按新格式显示。执行以下查询：S

45、ELECT TO_CHAR(sal,$99,999)SALARY FROM empWHERE ename=SCOTT;结果为：SALARY-$4,000说明：格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字，“99，999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗号。如果实际数值位数不足5位，则只显示实际位数，如4000实际位数为4位，则只显示4位。如果实际位数超过5位，则会填充为#号。第第2章章 数据查询数据查询2.4.5 其他函数Oracle还有一些函数，如decode和nvl，这些函数也很有用，归纳如表2-11所示。第第2章章 数据查询数据查询表2-11

46、其他常用函数函 数 功 能 实 例 结 果 nvl 空值转换函数 nvl(null,空)空 decode 实现分支功能 decode(1,1,男,2,女)男 userenv 返回环境信息 userenv(LANGUAGE)SIMPLIFIED CHINESE_CHINA.ZHS16GBK greatest 返回参数的最大值 greatest(20,35,18,9)35 least 返回参数的最小值 least(20,35,18,9)9 第第2章章 数据查询数据查询1空值的转换如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，

47、在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。第第2章章 数据查询数据查询如下3个函数，分别用新内容代替字段的空值：nvl(comm,0)：用0代替空的Comm值。nvl(hiredate,01-1月-97)：用1997年1月1日代替空的雇佣日期。nvl(job,无)：用“无”代替空的职务。第第2章章 数据查询数据查询【训练1】使用nvl函数转换空值。执行以下查询：SELECTename,nvl(job,无),nvl(hir

48、edate,01-1月-97),nvl(comm,0)FROM emp;结果为：ENAME NVL(JOB,N NVL(HIREDA NVL(COMM,0)-SMITH CLERK 17-12月-80 0ALLEN SALESMAN 20-2月-81 300说明：本例中，空日期将显示为“01-1月-97”，空职务显示为“无”，空津贴将显示为0。第第2章章 数据查询数据查询2decode函数decode函数可以通过比较进行内容的转换，完成的功能相当于分支语句。该函数的第一个参数为要进行转换的表达式，以后的参数成对出现，最后一个参数可以单独出现。如果第一个参数的值与第二个表达式的值相等，则返回第三

49、个表达式的值；如果不等则继续比较，如果它的值与第四个表达式的值相等，则返回第五个表达式的值，以此类推。在参数的最后位置上可以存在单独的参数，如果以上比较过程没有找到匹配值，则返回该参数的值，如果不存在该参数，则返回NULL。第第2章章 数据查询数据查询【训练2】将职务转换成中文显示。执行以下查询：SELECTename,decode(job,MANAGER,经理,CLERK,职员,SALESMAN,推销员,ANALYST,系统分析员,未知)FROM emp;结果为：ENAME DECODE(JOB-SMITH 职员ALLEN 推销员WARD 推销员JONES 经理 第第2章章 数据查询数据查询

50、MARTIN 推销员BLAKE 经理CLARK 经理SCOTT 系统分析员KING 未知TURNER 推销员ADAMS 职员JAMES 职员FORD 系统分析员MILLER 职员已选择14行。第第2章章 数据查询数据查询说明：在以上训练中，如果job字段的内容为“MANAGER”则返回“经理”，如果是“CLERK”则返回“职员”，以此类推。如果不是“MANAGER”、“CLERK”、“SALESMAN”和“ANALYST”之一，则返回“未知”，如KING的职务“PRESIDENT”不在上述范围，返回“未知”。【练习1】对部门表的部门名称和城市名进行转换。第第2章章 数据查询数据查询3usere