函数 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)