1. 虚表dual
虚表是Oracle提供的用于操作函数的方式,属于sys用户,共享给所有用户使用。虚表dual是单行单列的表,表中存放一个常量数据X。
虚表的意义:更方便的操作函数或者查询常量。
2. 函数
单行函数:表中的一列作为函数的参数,对于每一条记录,函数都有一个返回值。例如:upper lower nvl
多行函数:表中的一列作为函数的参数,将那个记录分组,对于每组数据,函数返回一个值。例如:avg
3. 单行函数根据处理参数的数据类型分为:
3.1 字符函数: upper, lower
3.2 数值函数:
round四舍五入
round(12.345,2)=12.35
round(12.345,0)=round(12.345)=12(保留小数点前一位,即个位,是舍是收应该看小数点后一位)
round(12.345,-1)=10(保留小数点前二位,即十位)
trunc截取
trunc(12.345,2)=12.34
trunc(12.345,0)= trunc(12.345)=12
trunc(12.345,-1)=10
3.3 日期和日期函数
select sysdate from dual;
日期类型数据用7个字节来依次保存世纪、年、月、日、时、分、秒,格式敏感
3.3.1. 设置会话的日期格式:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
之后再查询:
select sysdate from dual;
就会发现显示的时间格式已经改变
重新连接该用户之后,又会以默认的日期格式来显示日期。
to_date的返回类型是date,把字符串转换成了date类型,所以,参数的数据类型是【字符】类型
’01-1月-10’系统进行了隐式转换。调用了to_date函数
插入数据后,可以这样查看详细日期:
select to_char(c1,’yyyy-mm-dd hh24:mi:ss’) from test;
‘2008-08-08’,用户做显式数据类型转换,自己调用to_date(‘2008-01-01’,’yyyy-mm-dd’),第二个参数是对第一个参数的格式说明
a.会话级:
alter session set nls_date_format
b.语句级:
select to_char(c1……)
to_char的返回类型是字符类型,把date类型转换成字符串类型,所以参数的数据类型是date。to_char函数可以获得日期的任何一部分信息,比如年。
fm表示去掉前导0或去掉两边的空格。比如字符串0000234字符串234。
3.3.2. 日期的运算
日期可以加减一个数字,单位为天。例如:查看今天、明天、后天的日期:
select sysdate-1,sysdate,sysdate+1 from dual;
计算十分钟后:
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
select add_months(sysdate,6) from dual;
select add_months(sysdate,-6) from dual;
months_between()两个日期之间相差多少个月,返回类型是number
例如:计算员工的雇佣日期
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
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子句
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值分在一起