三、子查询和常用函数

一、子查询

子查询在select、update、delete语句内部可以出现select语句。内部的select语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询类型有：

1、  单行子查询：不向外部返回结果，或者只返回一行结果。

2、  多行子查询：向外部返回零行、一行或者多行结果。

 

案例1：查询出销售部(sales)下面的员工信息。

SELECT * FROM EMP  WHEREDEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')

 

案例2：any子查询。

解析：<any:比子查询结果中任意的值都；>any表示比子查询结果中最小的还大

SELECT ENAME,JOB,SALFROM EMP  WHERE SAL<ANY (SELECT SALFROM EMP WHERE JOB='SALESMAN')

 

案例3：All子查询

解析：<all:比最小值还小；>all比子查询结果最大值还要大

SELECT ENAME,JOB,SAL FROM EMP WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')

 

总结：【ANY】

             “比任意一个销售员工资低”==“比最高销售员工资低”；

        “比任意一个销售员工资高”==“比最低销售员工资高”；

         【ALL】

           “比所有销售员工资都低”==“比最低销售员工资低”；

           “比所有销售员工资都高”==“比最高销售员工资高”；

 

二、Oracle中的位列

在Oracle的表的使用过程中，实际表中还有一个附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：rowid 和 rownum。

注：ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。

         案例：查询出表EMP中第5条到第10条之间的记录(可以用于SQL分页语句)

         Select * from

(select rownum r, e.* from emp  e  whererownum<=10) where r>5

 

三、Oracle函数

Oracle数据库中主要使用两种类型的函数：

1、  单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果。例如：mod(x,y)放回x除以y的余数。

a、  字符函数：对字符串操作

b、  数字函数：对数字进行计算，返回一个数字

c、  转换函数：可以将一种数据类型转换为另外一种数据类型；

d、  日期函数：对日期和时间进行处理。

2、  聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如sum(x)返回结果集中x列的总和。

 

字符函数

         字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数：

数字函数

         数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

 

 

 

日期函数

         日期函数对日期进行运算。常用的日期函数有：

1、  add_months(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。

d表示日期，n表示要加的月数。

eg:  select sysdate,add_months(sysdate,5) fromdual;

2、  last_day(d)，返回指定日期当月的最后一天。

Select sysdate,last_day(sysdate) from dual;

3、  round(d,’fmt’)，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。

Select sysdate,round(sysdate),round(sysdate,’day’), round(sysdate,’month’), round(sysdate,’year’) from dual;

4、  extract(fmt from d),提取日期中的特定部分。

Fmt为：year、month、day、hour、minute、second。Year、month、day既可以date类型匹配，也可以与timestamp类型匹配；但是hour、minute、second必须与timestamp类型匹配。

Select sysdate “date”,extract(year from sysdate) “year”, extract(month from sysdate) “month”,extract(hour from systimestamp) “hour” from dual;

 

转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常用有：

1、  to_char(d,’fmt’):把日期和数字转换为指定格式的字符串。

select to_char(sysdate,’yyyy”年”mm”月”dd”日”HH24:MI:SS’)“DATE” from dual;

2、  to_date(s,’fmt’):把一个字符串以fmt格式转换为一个日期类型。

selectto_date('2013-09-04 14:17:50','yyyy-mm-dd hh24:mi:ss') "date" fromdual;

3、  to_number(x,’fmt’):把一个字符串以fmt格式转换为一个数字。

Select to_number(‘-$12,321.46’,’$99,999.99’)“num” from dual;

 

其他单行函数

1、  nvl(x,value)：如果x为空，返回value，否则返回x。

2、  nvl(x,value1,value2):如果x非空，返回value1，否则返回value2。

 

聚合函数

         聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。