oracle2

函数 function

sysdate 无参数的函数

upper(ename)

ename值参数函数有返回值,返回值指定数据类型

 

单行函数 upper(p1)

p1是参数,按参数的数据类型分类

字符函数 upper lower

数值函数 round trunc

日期函数 sysdate add_months

一般函数 nvl(bonus,0) nvl(ename,'A'),nvl(hiredate,sysdate)

转换函数 to_date,to_char,to_number

 

多行函数(组函数)

 

数值函数 round trunc

round(p1,p2) 四舍五入

round(1.235,2)  --> 1.24

round(1.235,0) =round(1.235)  --> 1

round(15.5,-1)  20

 

trunc(p1,p2) 截取

trunc(1.235,2)  --> 1.23

trunc((15.5,-1)  10

 

日期类型

session 会话通过connection创建session

alter(修改)session set (设置)

SQL> alter session   

2  set nls_date_format = 'yyyy mm ddhh24:mi:ss';

 

date   世纪年月日时分秒 7个字节(固定)

缺省日期格式  DD-MON-RR

ORA-01861: literal(文字值)does not match(匹配) format string(格式串)

to_date to_char 说明date类型是格式敏感的.

2008 08 08 08:08:08

ORACLE_SID

sqlplus username/passwd

sql> connectopenlab/open123

 

转换函数

system(系统级)session(会话级) statment(语句级)

insert into test values

(to_date('2008 08 0808:08:08',

          'yyyy mm dd hh24:mi:ss')

);

 

select to_char(c1,'yyyymm dd hh24:mi:ss') from test;

 

产生一个日期值函数的返回值是日期类型 to_date

处理一个日期值,处理的参数是日期类型  to_char(hiredate,'mm')

 

隐式转换系统转换

显式转换用户调用转换函数

'03' = 3  缺省方式:字符型-->数值型

to_number('03') = 3       char_to_number

to_number('ab') invalid(无效)number

to_char(salary)     number_to_char

to_char(sysdate)    date_to_char

 

日期运算

日期 +(-) 数值  日期 - 日期

 

日期函数

add_months(sysdate,1)add_months(sysdate,-6)

months_between(sysdate,hiredate)相差了多少月

last_day(sysdate) 某月的最后一天

 

一般函数

nvl ,coalesce()

ORA-00932: inconsistent(不一致)datatypes(数据类型): expected(期待) NUMBER got CHAR

 1 select ename,empno,

  2         coalesce(to_char(mgr),'Boss')

  3* from emp_hiloo

ORA-01722: invalid(无效)number

1  select ename,empno,

  2        nvl(to_char(mgr),'Boss')

  3* from emp_hiloo

 

排序

order by是最后一个子句,orderby在select之后执行

order by 列名 asc(升序,缺省)desc(降序)

order by后面跟列名,列别名,表达式(函数),位置

字符,数值,日期都可以排序

 

多行函数(组函数)

avg() 平均值数值

sum() 求和   数值

count() 计数数值字符日期

min()  最小值数值字符日期

max() 最大值数值字符日期

avg(all ) avg(distinct )

组函数处理的是所有的非空值,count(*)返回记录数,空值不影响

 

group by

ORA-00937: not asingle-group group function不是组函数

在没有group by的情况下,select后面只要有一个组函数,其他的必须是组函数

在有group by的情况下,select后面可以跟组标识(group by后面),组函数

 

having

from->where->groupby->having->select->order by

 

where和having的比较

功能都是过滤,都在select之前执行

where过滤的是记录,可以跟任意列名,可以跟单行函数,不能跟组函数

having过滤的是分组,可以跟组标识,可以跟组函数,不能跟单行函数

 

关于null值的讨论

case when ,decode在什么情况下返回null.(条件不满足)

asc null在最后 descnull在前面

当所有的值都是null,avg,sum,max,min返回null,count返回0

group by时,如果有多个null值,分为一组

 

课堂练习

1 请找出3月份入职的员工.

SQL> selectename,hiredate

  2  fromemp_hiloo

  3 where to_char(hiredate,'mm') = '03'; 

SQL> l

  1 select ename,hiredate

  2  fromemp_hiloo

  3* where to_char(hiredate,'fmmm') = '3'

fm 去掉前导0或者两端的空格

2 十分钟之后

SQL> selectsysdate,sysdate+1/144

  2  fromdual;

3 列出ename,empno,mgr,其中zhangsanfeng的mgr显示Boss

4 case when和decode

  1 select ename,salary,deptno,

  2         case when deptno = 10 then salary*1.1

  3             when deptno = 20 then salary*1.2

  4        else

  5             salary

  6        end new_sal

  7* from emp_hiloo

 

SQL> selectename,salary,deptno,

  2        decode(deptno,10,salary*1.1,

  3                       20,salary*1.2,

  4                       salary) new_sal

  5  fromemp_hiloo;  

 

5 列出员工名称和年薪,按年薪降序排列

select ename,salary*12

from emp_hiloo

order by salary desc

6 列出员工名称,部门号和年薪,按部门号升序,年薪降序排列

SQL> selectename,deptno,salary*12 ann_sal

  2  from emp_hiloo

  3 order by deptno , salary desc

7 求奖金的平均值,和,最小值,最大值,个数(所有有奖金的)

selectavg(bonus),sum(bonus),min(bonus),max(bonus),

        count(bonus)

from emp_hiloo

8 各个部门的平均工资,部门号,平均工资

selectdeptno,round(avg(salary))

from emp_hiloo

group by deptno

9 列出来每种奖金有多少个人

select bonus,count(empno)

from emp_hiloo

group by bonus

10 列出10部门的平均工资,只要平均工资

select round(avg(salary))

from emp_hioo

where deptno = 10

11 列出10部门的平均工资,部门号,平均工资

 1 select deptno,round(avg(salary))

  2  fromemp_hiloo

  3 where deptno = 10

  4* group by deptno

 

selectmax(deptno),round(avg(salary))

from emp_hioo

where deptno = 10

12 各个部门不同职位的平均工资?

selectdeptno,job,round(avg(salary))

from emp_hiloo

group by deptno,job

order by 1

13 该公司不同的职位的个数?

 select count(distinct job) from emp_hiloo;

14 该公司不同奖金的个数?

select count(distinctnvl(bonus,0))

from emp_hiloo

15 在10,20部门中,哪些部门的平均工资高于5000?,按部门号升序排列

selectdeptno,round(avg(salary))

from emp_hiloo

where deptno in (10,20)

group by deptno

having round(avg(salary))> 5000

order by deptno

16 列出10,20部门的平均工资

select deptno,avg(salary)

from emp_hiloo

where deptno in (10,20)

group by deptno

 

select deptno,avg(salary)

from emp_hiloo

group by deptno

having deptno in (10,20)

16 哪个员工的工资是最低的

select ename,salary fromemp_hiloo

where salary = (selectmin(salary) from emp_hiloo)

课后练习

1 <5000 涨10%  [5000,10000] 涨 5% 其他人不动

select ename,salary,

       case when salary < 5000 thensalary*1.1

             when salary between5000 and 10000

                                     thensalary*1.05

             else

                 salary

       end new_sal

from emp_hiloo

2 哪些部门的平均工资比30部门的平均工资高

selectdeptno,round(avg(salary))

from emp_hiloo

group by deptno

having round(avg(salary))> (select round(avg(salary))

                                from emp_hiloo

                                where deptno = 30)