1. 虚表dual
虚表是Oracle提供的用于操作函数的方式，属于sys用户，共享给所有用户使用。虚表dual是单行单列的表，表中存放一个常量数据X。
虚表的意义：更方便的操作函数或者查询常量。
2. 函数
单行函数：表中的一列作为函数的参数，对于每一条记录，函数都有一个返回值。例如：upper lower nvl
多行函数：表中的一列作为函数的参数，将那个记录分组，对于每组数据，函数返回一个值。例如：avg
3. 单行函数根据处理参数的数据类型分为：
3.1 字符函数： upper, lower
3.2 数值函数：
round 四舍五入
|
1
2
3
|
round(12.345,2)=12.35
round(12.345,0)=round(12.345)=12(保留小数点前一位，即个位，是舍是收应该看小数点后一位)
round(12.345,-1)=10（保留小数点前二位，即十位）
|
trunc 截取
|
1
2
3
|
trunc(12.345,2)=12.34
trunc(12.345,0)= trunc(12.345)=12
trunc(12.345,-1)=10
|
3.3 日期和日期函数
|
1
|
select
sysdate
from
dual;
|
日期类型数据用7个字节来依次保存世纪、年、月、日、时、分、秒，格式敏感
3.3.1. 设置会话的日期格式：
|
1
|
alter
session
set
nls_date_format=
'yyyy-mm-dd hh24:mi:ss'
;
|
之后再查询：
|
1
|
select
sysdate
from
dual;
|
就会发现显示的时间格式已经改变
重新连接该用户之后，又会以默认的日期格式来显示日期。
to_date的返回类型是date，把字符串转换成了date类型，所以，参数的数据类型是【字符】类型
’01-1月-10’系统进行了隐式转换。调用了to_date函数
插入数据后，可以这样查看详细日期：
|
1
|
select
to_char(c1,’yyyy-mm-dd hh24:mi:ss’)
from
test;
|
‘2008-08-08’，用户做显式数据类型转换，自己调用to_date(‘2008-01-01’,’yyyy-mm-dd’),第二个参数是对第一个参数的格式说明
a. 会话级：
|
1
|
alter
session
set
nls_date_format
|
b. 语句级：
|
1
|
select
to_char(c1……)
|
to_char的返回类型是字符类型，把date类型转换成字符串类型，所以参数的数据类型是date。to_char函数可以获得日期的任何一部分信息，比如年。
fm表示去掉前导0或去掉两边的空格。比如字符串0000234字符串234。
3.3.2. 日期的运算
日期可以加减一个数字，单位为天。例如：查看今天、明天、后天的日期：
|
1
|
select
sysdate-1,sysdate,sysdate+1
from
dual;
|
计算十分钟后：
|
1
|
select
to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss'
),to_char(sysdate+1/144,
'yyyy-mm-dd hh24:mi:ss'
)
from
dual;
|
其中的1/144就是十分钟，因为按天来作为单位1的。
两个日期相减
add_months 按月加 返回类型是date
|
1
2
|
select
add_months(sysdate,6)
from
dual;
select
add_months(sysdate,-6)
from
dual;
|
months_between()两个日期之间相差多少个月，返回类型是number
例如：计算员工的雇佣日期
|
1
|
select
months_between(sysdate,hiredate)
as
hiremonths
from
emp;
|
last_day(sysdate) 本月的最后一天
3.3.3. 转换函数小结
to_date()转换成日期
to_char()转换成字符串
to_number()转换成数字
3.4 其他函数
a. coalesce参数可以很多个，取第一个非空的值作为函数值。类似nvl函数。它还是oracle专有的函数。
coalesce(bonus,salary*0.1,100)如果bonus为空，则看salary*0.1是否为空，如果第二个也为空，则取第三个值作为结果。如果第二个不为空，则取第二个的值。
b. case when 条件表达式 then 结果
when 条件表达式 then 结果
。。。。。。
else 结果
end
如果没有else，当条件又不匹配的时候，此表达式返回null。
c. decode：简略版的case when
|
1
|
select
ename, decode(deptno, 10, salary*1.1, 20, salary*1.2, salary)
from
emp;
|
同样，若没有最后一个参数，函数返回null。
4. 多行函数（组函数）
avg ()平均值 返回number 参数只能是number
sum()求和 返回number 参数只能是number
count()计数 返回number 参数可以是number date和字符
count(*)统计记录数
max()最大值 是什么就返回什么 参数可以是number date和字符
min()最小值 是什么就返回什么 参数可以是number date和字符
avg(bonus)所有有奖金的员工的平均值
avg(nvl(bonus,0))所有员工奖金的平均值
count(bonus)有奖金的员工的个数
当所有的值都是null，count函数返回0，其他组函数返回null
5. Group by子句
若有group by 子句，select后面跟组标识和组函数
各子句执行的顺序：【from---where---group by---select---order by】
若没有group by 子句，select后面只要有一个是组函数，其余的都得是组函数。
6. Having子句
|
1
|
select
deptno,round(
avg
(salary))
from
emp
where
deptno
in
(20,30)
group
by
deptno
having
round(
avg
(salary))>5000
|
执行顺序：【from---where---group by---having---select---order by】
7. where和having的比较
共同点：都在执行select之前，都有过滤的功能
不同点：where执行在having之前
where过滤的是【记录】，任意列名都可以出现在where子句，单行函数可以用在where子句。组函数不能出现在where子句里
having过滤的是【组】，组标识可以出现在having子句，其他列名不行。组函数用于having子句，但行函数不可以
8. 关于null的讨论
-
case when 在没有else和decode少一个参数时，返回null
-
order by bonus,asc升序时null值在最后，desc降序时null在最前（把null看作无穷大即可）。
-
组函数和null值的关系：1.组函数的缺省处理方式是处理所有的【非空值】2.当所有的值都是null，count函数返回0，其他函数返回null
-
若group by的列有null值，所有的null值分在一起
