出了常用的日期函数。
显示 Sysdate 28-FEB-02 on February 28,2002 Last_day Select last_day(sysdate) 31-MAR-02 on 本月最后一天 from dual; March 12,2002 Add_months(d,n) 18-MAY-02 on 当前日期d后推n个Select add_months(sysdate,2) from March 18,2002 月 dual; Months_between(f,s) 日期f和s间相差月Select 13 in April months_between(sysdate,’122003 数 -MAR,-02 from dual; Next_day(d,day) 03-JAN-02 on d后第一周指定的Select next_day(sysdate,’Monday’) December30, day日期 from dual; 2001 5）特殊格式的日期型数据
日期型数据有各种各样的格式。表4-7给出了一些日期格式及其输出： 格式 Y或YY或YYY SYEAR或YEAR Q MM RM Month WW W 表4-7常用日期数据格式 返回值 样例 年的最后一位，两位Select to_char(sysdate,’YYY或三位 ’) from dual; 年，SYEAR使公元前Select to_char(sysdate,’SYE的年份前加一负号 AR’) from dual; 季度，1到3月为第Select to_char(sysdate,’Q’) 一季度 from dual; Select 月份数 to_char(sysdate,’MM’) from dual; Select 月份的罗马表示 to_char(sysdate,’RM’) from dual; 用9个字符长度表示Select to_char(sysdate,’Mont的月份名 h’) from dual; Select 当年第几周 to_char(sysdate,’WW’) from dual; Select 本月第几周 to_char(sysdate,’W’) from dual; 当年第几天，1月1Select 日为001，2月1日为to_char(sysdate,’DDD’) from dual; 032 Select 当月第几天 to_char(sysdate,’DD’) from dual; Select 周内第几天 to_char(sysdate,’D’) from dual; 显示 002表示2002年 -1112表示公元前1112年 2 表示第二季度 12表示12月 IV表示4月 May后跟6个空格表示5月 24表示2002年6月13日为第24周 1 2002年10月1日为第1周 363 2002年12月29日为第363天 04 10月4日为第4天 5 2002年3月14日为星期一 第26页
函数 表4-6常用日期型函数 返回值 样例 Select sysdate from dual; 当前日期和时间 DDD DD D DY 周内第几天缩写 HH或HH12 HH24 MI SS 12进制小时数 24小时制 分钟数（0-59） 秒数（0-59） Select to_char(sysdate,’DY’) from dual; Select to_char(sysdate,’HH’) from dual; Select to_char(sysdate,’HH24’) from dual; Select to_char(sysdate,’MI’) from dual; Select to_char(sysdate,’SS’) from dual; SUN 2002年3月24日为星期天 02 午夜2点过8分为02 14 下午2点08分为14 17 下午4点17分 22 11点3分22秒
6）日期运算
当我们开始使用日期型数据时，也许会提出这样的问题：“如何知道上次汽车加油到现在过了多少天？”，日期型运算使我们可以很容易地得到这个答案。当给日期数据加2时，Oracle知道是增加两天。假定字段sale_date的值为03-MAR-98，在此情况下，SQL语句select sale-date+10 from customer;将返回13-MAR-02。
7）字段数据类型转换
我们常常会用遇到需要将数据表列从一种类型转换为另一种类型的情况（如：数值型转换成日期型，字符型转换成数值型）。Oracle提供三种主要的转换函数。
? ? ?
To_char将任意类型的数据转换成字符类型。语句select to_char(8897) from dual;返回一个包含字符串8897的字符类型的数据。
To_number将一组合法的数字字符串（如字符数据8897）转换成数值。语句select to_number(‘8897’) from dual;返回一个包括数值8897的数值类型的数据。 To_date将适合格式的字符串数据转换成日期型数据。这是最容易产生错误的转换。因为12-DEC-02是合法的日期格式，语句select to_date(‘12-DEC-02’) from dual;能成功地进行数据转换。而语句select to_date(‘bad date’) from dual;，在执行时会出问题。语句select to_date(‘20021227’,’YYYYMMDD’) from dual;将返回日期27-DEC-02因为在to_date函数中的日期格式和被转化的数据是合法的。语句select to_date(‘20021236’,’YYYYMMDD’) from dual;将失败因为在12月中没有36天。
6．分组结果函数
为了得到汇总信息，这些函数将多组数据集合在一起。当把数据行集合在一起时，可以看作是在从数据库中检索信息时把同类信息合并在一起的操作。表4-8列出了最常用的分组函数，在这里仍然以customer表为例。
表4-8 最常用的分组函数
函 数 返 回 例 子 avg(column_name) Count(*) Max(column_name) min(column_name) Column_name表的所有值的平均值 表中的数据行数目 存放在column_name表列中的最大值 存放在column_name表列中的最小值 Select avg(sales) customer Select count(*) customer Select max(sales) customer Select min(sales) customer from from from from 第27页
了解了最常用的分组函数后，让我们来考察一下它们的应用。
7．使用group by 子句
使用表4-8描述的函数，它们后面可以跟或不跟group by子句。当在字段中不使用Group by子句，例如“select max(sales) from customer;”,实际上是告诉数据库你把表中所有行看作一个组。再例如，想知道客户的平均销售额或销售总额，查询语句“select avg(sales) from customer;”求出销售平均值，而语句“select sum(sales) from customer;”则求出总销售额。但有的时候让人更感兴趣的是查询预先分类或分组的数据，例如，要想知道state的平均销售额，可使用查询语句“select state_cd,avg(sales) from customer group by state_cd;”。大多数人一开始就明白group by 语句的意思，但使用时却常有麻烦。必须确保group by 在每一个语句中引用正确的表列数目。注意，当使用group by 时，未在group by 部分用到的字段在select部分出现时必须使用分组函数。
让我们再考虑一下忽略使用分组函数可能引起的问题。如果执行了语句“select last_name,state_cd,sum(sales) from customer group by last_name;”,将返回以下错误： ERROR at line 1:
ORA-00979: not a GROUP BY expression
这是由于state_cd表列没有在group by 子句中，因此必须加上分组函数。换句话说，必须使用max(),min(),sum(),count(),或avg()函数。如果对于指定的表列找不到想使用的合适的分组函数，那么就将该表列移到group by 子句中去。
8．使用having子句
正如为单行查询设置查询条件（例如，state_cd=\）那样，也可以使用having 子句，为一组记录设置查询的条件。例如，假如只想了解客户超过300个的州，使用having 子句，其查询语句是“select state_cd,avg(sales) from customer group by state_cd having count(state_cd)>300;”。注意，having子句允许设置对说明一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。
9．嵌套查询
SQL语言另一个强大的功能是具有嵌套查询功能，也称之为子查询。子查询的格式如下：
{main query text} where {condition}
( {sub query text});
例如，下面的主查询访问的是customer表，而子查询访问的是state表： select last_name,sales from customer where state_cd=
(select max(state_cd) from state); 注意，子查询用原括号括起来，where子句的条 件要取决于查询结果。换句话说，where子句中又包括了一个SQL的select语句。如果只需要了解超出整个公司销售平均值的客户的销售额，可以输入查询语句“select state_cd,sales from customer where sales>(select avg(sales) from customer);”。嵌套查询能力是非常强大的，能根据存储在数据库中的信息返回数据结果建立SQL语句，这样，当数据库变化时，查询仍保持不变。例如，以前的是平均销售额为$12,800且查询语句为“select state_cd,sales from customer where sales>12800;”。注意，使用子查询能使所编写的SQL语句在数据发生变化时语句不变化。 10．连接
命令格式如下：
第28页
SELECT columns FROM tables
WHERE join condition is … 1）等值连接（Equi-join） 等值连接用‘=’操作符。 如连接基表EMP和DEPT： SELECT ENAME, JOB, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO ENAME JOB -------------------- ------------------------------ CLARK MANAGER MILLER CLERK KING PRESIDENT SMITH CLERK SCOTT ANALYST JONES MANAGER ADAMS CLERK FORD ANALYST ALLEN SALESMAN BLAKE MANAGER TURNER SALESMAN MARTIN SALESMAN WARD SALESMAN
2）非等值连接（Non-equi-join）
非等值连接可BETWEEN，IN，>,>=, <, <=等操作符。 SELECT E.NAME, E.SAL, S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
DNAME
--------------------------------- ACCOUNTING ACCOUNTING ACCOUNTING RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES SALES
如查询基表EMP中工资介于基表SALGRADE中LOSAL和HISAL的职工：
三．上机内容
1． 显示EMP表中所有的部门号、职工名称和管理者号码： SELECT DEPTNO,ENAME,MGR FROM EMP; DEPTNO ENAME
---------------- ----------------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER
MGR
-------------- 7902 7698 7698 7839 7698 7839 7839 7566
7698
第29页
20 30 20 10 ADAMS JAMES FORD MILLER 7788 7698 7566 7782
2． 算术运算符在SQL中的使用
SELECT ENAME, SAL+250*12 FROM EMP;
3． 连字符的使用
把职工号和职工名字连接起来，如下：
SELECT EMPNO||ENAME EMPLOYEE FROM EMP;
把职工号和职工名字中间用‘-’连接起来，并输出‘WORKS IN DEPARTMENT’，如下： SELECT EMPNO||’-‘||ENAME EMPLOYEE, ‘WORKS IN DEPARTMENT’, DEPTNO FROM EMP; 4． 禁止重复
如果列举出EMP表中所有部门号：
Select deptno from emp; DEPTNO ---------------- 20 30 30 20 30 30 10 20 10 30 20 30 20 10
从上表中可以看出部门号之间存在着许多的相同的，可以用DISTINCT子句来消除重复的。 SELECT DISTINCT deptno from emp; DEPTNO --------------- 10 20 30
5． 排序
按单个字段排序，如按照ENAME排序， SELECT ENAME, JOB, SAL*12, DEPTNO FROM EMP ORDER BY ENAME; ENAME
------------------------- ADAMS ALLEM BLAKE CLARK
JOB
------------------------- CLERK
SALESMAN MANAGER MANAGER
SAL*12
------------------------- 13200 19200 34200 29400
DEPTNO
------------------------- 20 30 30 10
第30页