子查询:
单行子查询:不向外部返回结果,或者只返回一行结果。
多行子查询:向外部返回零行、一行或者多行结果。
select empno,ename,hiredate,sal from emp where deptno = (select
deptno from dept where deptno = 04);
案例2:查询出Emp表中比任意组长工作人员工资低的员工姓名、工作、工资。
案例分析
销售员在Emp表中有很多条记录,每个人工资不相等,如果返回“比任意员工的工资还低”的条件,返回比“最高工资还低”即可。如果用子查询做,子查询中就会返回多条记录。用普通的关系符(>、
select empno,ename,job ,sal from emp where
(sal<=any(select sal from emp where
job='zuzhang'));
① any表示比子查询结果中最小的还大。
案例3:查询出比所有比组长的工资都高的员工姓名,工作,工资。
① >ALL:比子查询结果中所有值还要大,也就是说,比子查询结果中最大值还要大。
对于子查询还可以使用IN和NOT IN操作符进行操作。
1. Oracle中的伪列
在Oracle的表的使用过程中,实际表中还有一些附加的列,称为伪列。伪列就像表中的列一样,但是在表中并不存储。伪列只能查询,不能进行增删改操作。接下来学习两个伪列:ROWID和ROWNUM。
² ROWID
表中的每一行在数据文件中都有一个物理地址,ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址,因此使用ROWID可以显示行是如何存储的。
查询工资大于8000的员工编码。
select rowid , empno from emp
where sal >8000;
² ROWNUM
在查询的结果集中,ROWNUM为结果集中每一行标识一个行号,第一行返回1,第二行返回2,以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。
ROWNUM与ROWID不同,ROWID是插入记录时生成,ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。
查询工资大于8000的员工编码
将所有员工工资进行排序;
select rownum , T.* from (select empno , ename, hiredate,sal
from emp order by sal)T;
①
T是子查询②的别名,这里的ROWNUM是第二次查询后的ROWNUM。
select
* from (select rownum r ,empno , ename, hiredate,sal from emp where
rownum<=10) where r >
5;
询出表EMP中第5条到第10条之间的记录。
①
内部查询中得到ROWNUM 并且用别名R记录,供外层条件③使用。
②
内部查询的ROWNUM,与外出的ROWNUM列是平等的两列。
③
使用的R是内层产生的ROWNUM,在外层看来,内层查询的ROWNUM是正常的一列
#在这种类似的选择中,要先获取大的,再选取小的。
1. Oracle函数
Oracle
SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数,并返回一个输出结果。Oracle数据库中主要使用两种类型的函数:
1. 单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,比如:MOD(x,y)返回x除以y的余数(x和y可以是两个整数,也可以是表中的整数列)。常用的单行函数有:
Ø 字符函数:对字符串操作。
Ø 数字函数:对数字进行计算,返回一个数字。
Ø 转换函数:可以将一种数据类型转换为另外一种数据类型。
Ø 日期函数:对日期和时间进行处理。
2. 聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如SUM(x)返回结果集中x列的总合。
² 字符函数
字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。下表列出了常用的字符函数。
函数
说明
ASCII(x)
返回字符x的ASCII码。
CONCAT(x,y)
连接字符串x和y。
INSTR(x, str [,start] [,n)
在x中查找str,可以指定从start开始,也可以指定从第n次开始。
LENGTH(x)
返回x的长度。
LOWER(x)
x转换为小写。
UPPER(x)
x转换为大写。
LTRIM(x[,trim_str])
把x的左边截去trim_str字符串,缺省截去空格。
RTRIM(x[,trim_str])
把x的右边截去trim_str字符串,缺省截去空格。
TRIM([trim_str FROM] x)
把x的两边截去trim_str字符串,缺省截去空格。
REPLACE(x,old,new)
在x中查找old,并替换为new。
SUBSTR(x,start[,length])
返回x的字串,从staart处开始,截取length个字符,缺省length,默认到结尾。
表1 字符函数
例如:
select ascii('s') from dual ;
² 数字函数
数字函数接受数字参数,参数可以来自表中的一列,也可以是一个数字表达式。
函数
说明
示例
ABS(x)
x绝对值
ABS(-3)=3
ACOS(x)
x的反余弦
ACOS(1)=0
COS(x)
余弦
COS(1)=1.57079633
CEIL(x)
大于或等于x的最小值
CEIL(5.4)=6
FLOOR(x)
小于或等于x的最大值
FLOOR(5.8)=5
LOG(x,y)
x为底y的对数
LOG(2,4)=2
MOD(x,y)
x除以y的余数
MOD(8,3)=2
POWER(x,y)
x的y次幂
POWER(2,3)=8
ROUND(x[,y])
x在第y位四舍五入
ROUND(3.456,2)=3.46
SQRT(x)
x的平方根
SQRT(4)=2
TRUNC(x[,y])
x在第y位截断
TRUNC(3.456,2)=3.45
表3 数字函数
l例如:
select abs(-3) from dual;
select round(3.56) from
dual;
select round(5.4654,2) from dual;
select round(352.654,-2) from dual;
select trunc(3.56) from dual;
select trunc(5.4654,2) from dual;
select trunc(352.654,-2) from dual;
² 日期函数
日期函数对日期进行运算。常用的日期函数有:
1、ADD_MONTHS(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期。d表示日期,n表示要加的月
数。
2、LAST_DAY(d),返回指定日期当月的最后一天。
3、ROUND(d[,fmt]),返回一个以fmt为格式的四舍五入日期值,d是日期,fmt是格式模型。默认fmt为DDD,即月中的某一天。
Ø 如果fmt为“YEAR”则舍入到某年的1月1日,即前半年舍去,后半年作为下一年。
Ø 如果fmt为“MONTH”则舍入到某月的1日,即前月舍去,后半月作为下一月。
Ø 默认为“DDD”,即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天。
Ø 如果fmt为“DAY”则舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日。
² 转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常用的转换函数有:
1. TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。fmt是格式化字符串,日期的格式化字符串前面已经学习过。
select to_char(sysdate,'yyyy"年"MM"月"DD"日" HH24:MI:SS')
"date" from dual;
针对数字的格式化,格式化字符有:
参数
示例
说明
9
999
指定位置处显示数字。
.
9.9
指定位置返回小数点
,
99,99
指定位置返回一个逗号
$
$999
数字开头返回一个美元符号
EEEE
9.99EEEE
科学计数法表示
L
L999
数字前加一个本地货币符号
PR
999PR
如果数字式负数则用尖括号进行表示
1. TO_DATE(x [,fmt])
把一个字符串以fmt格式转换为一个日期类型,前面已经学习过。
2. TO_NUMBER(x[,fmt])
把一个字符串以fmt格式转换为一个数字。fmt格式字符参考表3。
² 其他单行函数
1、NVL(x,value)
如果x为空,返回value,否则返回x。
2、 NVL2(x,value1,value2)
如果x非空,返回value1,否则返回value2。
²
聚合函数
聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值等。
名称
作用
语法
AVG
平均值
AVG(表达式)
SUM
求和
SUM(表达式)
MIN、MAX
最小值、最大值
MIN(表达式)、MAX(表达式)
COUNT
数据统计
COUNT(表达式)
表 5 聚合函数
eg: select sum(sal) from emp;