oracle 查询函数练习2

浏览数：25 / 时间：2015年06月12日

/*
以下代码是对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，删除答案，自已练习一遍