首页 > 代码库 > oracle 查询 函数练习2

oracle 查询 函数练习2

/*
以下代码是对emp表/dept表/salgrade表进行显示宽度设置
*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
col dname for a14;
col loc for a14;
col grade for 9999;
col tname for a12;
set pagesize 30;

//------------------------------------------------------------------------------------------------------

查询当前日期
select sysdate from dual;
select to_char(sysdate,‘YYYY-MM-DD DAY‘) from dual;
select to_char(sysdate,‘yyyy-mm-dd day‘) from dual;
‘格式‘中的字符串,大小写不敏感

日期隐式转换,读字符串,判断是否符合日期格式要求,再隐式转换,效率低
select ename,hiredate from emp where hiredate=‘17-12月-80‘;

日期显式转换,效率高,项目中提倡使用
select ename,hiredate from emp where hiredate=to_date(‘1980-12-17‘,‘yyyy-mm-dd‘);

使用to_char(日期,‘格式‘)函数将日期转成字符串,显示如下格式:2013-01-25 今天是 星期五 20:18:24
select to_char(sysdate,‘yyyy-mm-dd "今天是" day hh24:mi:ss‘) from dual;

使用to_char(数值,‘格式‘)函数,显示员工工资,加上$或¥符号和千位符
select to_char(1234,‘L9,999‘) from dual;
select ename,to_char(sal,‘$9,999‘) "美元",to_char(sal,‘L9,999‘) "人民币" from emp;

使用to_number(字符串)函数将字符串"123"转成数字
select to_number(‘123‘)+321 from dual;
select to_number(‘A123‘)+321 from dual;//错误

使用to_date(字符串,‘格式‘)函数将字符串"1980-12-17"转成日期
select * from emp where hiredate = to_date(‘1980-12-17‘,‘yyyy-mm-dd‘);

使用nvl(a,b)函数,统计员工年收入
select ename,job,nvl(comm,0) from emp;

使用nul2(a,b,c)函数,统计员工年收入
select ename,job,nvl2(comm,comm,0) from emp;

使用nullif(a,b)函数,比较10和10.0是否相同
select nullif(10,10) from dual;相同返回null
select nullif(10,11) from dual;不相同返回第一个值
select nullif(10,‘10‘) from dual;比较时,二类型必须相同

使用case表达式,职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400
select ename "姓名",job "职位",sal "涨前薪水",
case job
when ‘ANALYST‘ then sal+1000
when ‘MANAGER‘ then sal+800
else sal+400
end "涨后薪水"
from emp;

使用decode函数,职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400【oracle专用】
select ename "姓名",job "职位",sal "涨前薪水",
decode(job,‘ANALYST‘,sal+1000,‘MANAGER‘,sal+800,sal+400) "涨后薪水"
from emp;

从今天开始算,下下一个星期三是多少号?【中文平台/星期一,星期二,星期三,星期四,星期五,星期六,星期日】
select next_day(next_day(sysdate,‘星期三‘),‘星期三‘) from dual;
函数可嵌套,由内向外执行

//------------------------------------------------------------------------------------------------------

员工总工资,平均工资,四舍五入,保留小数点后2位
select sum(sal) "总工资",round(avg(sal),2) "平均工资"
from emp;

员工最高工资,最低工资分别是多少
select max(sal) "最高工资",min(sal) "最低工资"
from emp;

求员工总人数
select count(*) "总人数" from emp;//11
select count(comm) "总人数" from emp;//4
以上这些函数都不会统计null值

入职最早员工,入职最晚员工
select max(hiredate) "入职最晚员工",min(hiredate) "入职最早员工"
from emp;
以上这些函数都用于数值型,max和min也可用于日期型

统计有佣金的员工人数
select count(comm) from emp;

统计公司有多少个部门,部门不能重复
select distinct deptno from emp;

按部门求出部门平均工资,且平均工资取整数
select deptno "部门号",trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno;

查询部门平均工资大于2000元的部门
select deptno "部门号",trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0) > 2000;
使用having前提是,必须分组,而且只能出现在group by之后

除30号部门外,查询部门平均工资大于1500元的部门,方式一【having deptno!=30】
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0)>1500 and deptno<>30;
分析:分组->组过滤器->组过滤器【效率低】

除30号部门外,查询部门平均工资大于1500元的部门,方式二【where deptno!=30】
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
where deptno<>30
group by deptno
having trunc(avg(sal),0)>1500;
分析:行过滤器->分组->组过滤器【效率高】
where行过滤器【优先】
having组过滤器

显示部门平均工资的最大值
select max(avg(sal)) "部门平均工资的最大值"
from emp
group by deptno;

//------------------------------------------------------------------------------------------------------

员工表和部门表的笛卡尔集(笛卡尔集表=列数之和,行数之积,笛卡尔集表内中数据是无意义的)
select * from
emp,dept;

(1)使用等值连接,显示员工的编号,姓名,部门名,使用表别名简化【=号】
select e.empno "编号",e.ename "姓名",d.dname "部门名"
from emp e,dept d
where e.deptno=d.deptno;

(2)使用非等值连接(就是不能使用=号),显示员工的编号,姓名,月薪,工资级别
select e.empno "编号",e.ename "姓名",e.sal "月薪",s.grade "级别"
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

(3)使用外连接,按部门10,20,30,40号,统计各部门员工人数,显示部门号,部门名,人数
select e.deptno "部门号",count(*) "员工人数"
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno;
以上代码只将emp表中满足条件的记录查询出来,【但不满足条件的记录却没有查询出来】
目的:就是要将【满足条件的记录】和【不满足条件的记录】也要查询出来。
解决方案:使用外连接
外连接类种:
A)左外连接:(+)符号在=号右边,叫左外连接
B)右外连接:(+)符号在=号左边,叫右外连接
(+)只能出现在where中

select d.deptno "部门号",d.dname "部门名",count(e.empno) "员工人数"
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname;
//右外连接

select d.deptno "部门号",d.dname "部门名",count(e.empno) "员工人数"
from emp e,dept d
where d.deptno=e.deptno(+)
group by d.deptno,d.dname;
//左外连接

 

(4)使用自连接,显示"SMITH的老细是FORD"这种格式,表示层次关联的数据,用自连接
select emp.ename || ‘的老板是‘ || boss.ename
from emp emp , emp boss
where emp.mgr = boss.empno;

总结:
等值:用的最广,连接表的基础。
*非等值:只要不使用=号就行。
***外连接:主要解决满足条件和不满足条件的记录,在少的一方加上(+)即可
**自连接:主要解决具有层次关系的需求,将一张表看成多次张,
通过等值连接将多张表连接在一起。
注意:这是oracle专用语法

select中出现的非组函数,都必须出现在group by中
这里的组函数就是:count(),max(),min(),avg(),sum()

//------------------------------------------------------------------------------------------------------

子查询解决查询【条件未知】。

查询工资比WARD高的员工信息
步一:查询WARD的工资
select sal from emp where ename=‘WARD‘;
步二:查询工资大于1250的员工信息
select * from emp where sal>1250;
子查询:
select *
from emp
where sal>(
select sal
from emp
where ename=‘WARD‘
);
主、子查询都是争对同一张表

查询部门名为‘SALES‘的员工信息(方式一:子查询)
(内/子)步一:查询"SALES"的部门号
select deptno from dept where dname=‘SALES‘;
(外/主查询)步二:查询30号部门的员工信息
select * from emp where deptno=30;

子查询:
select * from emp where deptno=(
select deptno from dept where dname=‘SALES‘
);
主、子查询都是争对不同张表
主、子查询的连接点类型和数量要相同,(前提是使用=号)

查询部门名为‘SALES‘的员工信息(方式二:多表查询)
select emp.*
from emp,dept
where (emp.deptno = dept.deptno) and (dept.dname = ‘SALES‘);

在oracle11G内部优化后,从查表次数来看,优先选择
“多表查询”-->“子查询”
提前时:
A)二种方式做能完成任务
B)oracle对笛卡尔集做了优化

查询工资最低的员工信息(单行子查询,=号)
步一:查询最低工资的员工
select min(sal) from emp;
步二:查询工资是880的员工信息
select * from emp where sal = 880;
子查询:
select * from emp where sal = (
select min(sal) from emp
);

查询部门名为‘ACCOUNTING‘或‘SALES‘的员工(多行子查询,in关键字)
步一:查询部门名是"ACCOUNTING"或"SALES"的部门编号
select deptno from dept where dname in (‘ACCOUNTING‘,‘SALES‘);//10,30
步二:查询部门号为10号或30号的员工信息
select * from emp where deptno in(10,30);
子查询:
select * from emp where deptno in(
select deptno from dept where dname in (‘ACCOUNTING‘,‘SALES‘)
);

查询工资比20号部门【任意any】一个员工【低<】的员工信息(多行子查询,any关键字)
步一:查询20号部门员的工资
select sal from emp where deptno=20;
步二:查询工资低于"880/2975/3000/1100/3000"任何一个工资的员工信息
select * from emp where sal < any (数组);

子查询:
select * from emp where sal < any (
select sal from emp where deptno=20
);
<any 等价于 小于最高工资

查询工资比30号部门【所有all】员工【低<】的员工信息(多行子查询,all关键字)
步一:查询30号部门员的工资
select sal from emp where deptno=30;
步二:查询工资低于"1600/1250/1250/2850/1500/950"任何一个工资的员工信息
select * from emp where sal < all (数组);

子查询:
select * from emp where sal < all (
select sal from emp where deptno=30
);
<all 等价于 小于最低工资

思考:
1)>any ?
2)>all ?

//------------------------------------------------------------------------------------------------------

使用并集运算,查询20号部门和30号部门的员工信息
步一:查询20号部门的员工
select * from emp where deptno=20;

步二:查询30号部门的员工
select * from emp where deptno=30;

并集运算:
select * from emp where deptno=20
union
select * from emp where deptno=30;

打开时间的开关
set time on;//在提示符中显示当前时间
set time off;//关闭时间

关闭时间的开关
set timing on;//在最后,显示SQL语句执行的时间
set timing off;//关闭SQL执行的时间

使用交集运算,查询工资在1000-2000和1500-2500之间的员工信息(方式一)

select * from emp where sal between 1000 and 2000
intersect
select * from emp where sal between 1500 and 2500;

使用where行过滤,查询工资在1000-2000和1500-2500之间的员工信息(方式二)

select *
from emp
where (sal between 1000 and 2000) and (sal between 1500 and 2500);

使用差集运算,查询工资在1000-2000,但不在1500-2500之间的员工信息(方式一)

select * from emp where sal between 1000 and 2000
minus
select * from emp where sal between 1500 and 2500;
在做集合运算时,
二个集合的列数必须一样
二个集合如果列名不一样,结果取决于第一个集合

使用where行过滤,查询工资在1000-2000,但不在1500-2500之间的员工信息(方式二)
select *
from emp
where (sal between 1000 and 2000) and (sal not between 1500 and 2500);

当多表查询,子查询,集合查询都能完成任务时,按如下方案选择

多表查询->子查询->集合查询

前提:... ...
表查得越少越快
表查字段越少越快
表查记录越少越快

//------------------------------------------------------------------------------------------------------

rowid:通过desc看不见该列,但又存在,与文件系统引用建立关系

【重点】rownum:通过desc看不见该列,但又存在,该值有如下特点
A)永远连续
B) rownum依然可以参与运算
C)rownum可以参与<或<=运算
D)rownum值不能完成=,>,>=运算
E)rownum值<>与<是一样的结果

思考:
1)获取前五条记录
select * from emp where rownum<=5
2)获取第六条记录到第十条记录

启动Oracle11自带的一个SQLDeveloper图形化工具,使用JDK5/6

//------------------------------------------------------------------------------------------------------

动手练习:

将今天讲过的OracleSQL,删除答案,自已练习一遍

//------------------------------------------------------------------------------------------------------

单引号:
A)to_char(a,‘格式‘);


双引号:
B)to_char(a,‘格式‘)该格式中有固定文本时,可以使用双引号“”定界