用oracle查bonus表结果,Oracle的使用笔记

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值分在一起