oracle学习笔记--复杂查询

数据分组五个常用函数：max min avg sum count

显示emp表中哦最高工资和最低工资：select max(sal) ，min(sal) from emp;注意：avg(sal) 不会吧salary 为空的记录统计

显示所有员工的平均工资和工资总和 select avg(sal),sum(sal) from emp

统计有多少员工：select count(* )from emp;  注意：count（*）可以对一个字段进行统计

请显示工资最高的员工的名字与工作岗位： select ename,job from emp where sal=(select max(sal) from emp);

 sql在执行的时候默认是从右向左执行

工资高于平均工资的员工信息：select * from emp where sal >(select avg(sal) from emp);

group by 和having 子句 （having 子句的出现一般伴随着group by ）

group by 对查询的结果进行分组显示

having ：用于限制分组显示的结果      

显示每个部门的平均工资和最高工资：select avg(sal),max(sal) deptno from emp group by empno;   

显示每个部门的每种岗位的平均工资和最低工资：select avg(sal) ,min(sal),deptno,job from emp group by deptno,job order by empno;

显示部门平均工资低于2000的部门号和它的平均工资：select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;    

复杂查询之多表查询

多表查询(用两表之间相同的列表两个表联系起来)

笛卡尔集：如果多表查询不带任何条件的话，就会出现笛卡尔集的情况

显示雇员名，雇员工资及所在部门的名字：select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

部门号为10的部门名，员工名，工资：select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

显示每个员工的姓名，工资，及其工资的级别：

select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisol;

在多表查询时，需不需要加表名，如果两个表的列同名则需要加表名进行区分，否则可以不加表名（建议加上表名提高可读性）

在进行多表查询时，使用一个别名，比较方便

显示雇员名，雇员工资，及所在部门的名字，并按部门排序：

select ename ,sal,dname from emp, dept where emp.deptno=dept.deptno order by dname

oracel自连接查询

显示员工名为ford的上级领导的名字：

select * from emp where empno=(select mgr from emp where ename='ford');

显示员工姓名及其上级领导姓名

select worker.ename.boss.ename from emp worker,emp boss,where (worker.mgr=boss.empno);

子查询：嵌入在 其他sql语句的Select 查询

 单行子查询：只返回一行数据

显示与Smith 同一部门的所有员工：select * from emp where deptno=(select deptno from emp where ename='Smith')

多行子查询：返回多行数据

查出与部门10的工作相同的雇员的名字，岗位，工资，部门号：select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10)

在多行子查询中使用all 操作符

显示比部门30的所有员工的工资高的的员工的姓名，工资和部门编号：

第一种写法;select ename ,sal ,deptno from emp where sal> all(select sal from emp where empno=30)

第二种写法：select enamel，sal, deptno from emp where sal>(salect max(sal) from emo where empno=30)

在多行子查询中使用any操作符

显示比部门30的任意一个员工的工资高的员工姓名，工资和部门号

select ename,sal,empno from emp where sal>any(select sal from emp where empno=30); 或者

select ename,sal,empno from emp where sal>(select min(sal） from emp where empno=30);

多列子查询

查询与Smith部门，岗位完全相同的所有雇员：select * from emp where empno=(select empno from emp where ename='smith') and job=(select job from emp where ename='smith');

d第二种写法：select * from emp where (empno,job) =(select empno,job from emp where ename='smith');

在from子句中使用子查询

显示高于自己部门平均工资的员工的信息：

首先显示各个部门的平均工资：select  avg(sal) ,deptno from emp group by deptno;

将上面查询的内容当作一个临时表来对待：select t1.ename,t1.sal,t1.deptno,t2.salavg from emp t1,(select avg(sal) salavg,deptno from emp group by deptno) t2 where t1.deptno=t2.deptno and sal>t1.avgsal;

查找每个部门最高工资的人的详细信息：select * from emp t1,(select max(sal) ,deptno from emp group by deptno) t2, where t1.empno= t2.empno

and t1.sal=t2.max(sal);

显示每个部门的信息（编号，名称）和人员数量：

思路：先查出各个部门有多少人:select deptno ,count(*) from emp group by deptno;

把上面的结构当成临时表然后：select deptno,dname, num,from dept，(select deptno ,count(*) num from emp group by deptno)t1,where deptno=t1.deptno

分页查询：必须掌握的要点

mysql: select * from 表名 where 条件 limit 从第几条，去几条

oracle：使用三层过滤：

第一层：select * from emp;

第二层：select t1.*,rowcount rn from(select * from emp) t1 where rownum<=6;

第三层：select t2.* from(select t1.* ,rownum rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4;

select t2.* from(select t1.* ,rownum rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4;

其中 6表示 取到第几条，4代表 从第几条开始取

如果我们需要针对不同德情况进行分页，在最内层处理就好了

按照入职时间的先后顺序，查询第7到第10个人是谁：select t2.* from(select t1.* ,rownum rn from (select * from emp order by hiredate) t1 where rownum<=10) t2 where rn>=7;

用查询的结果创造新表： create table tableName as (select * from  tableName2);

自我复制（蠕虫复制）：insert into tableName (id, name,sal,job) select deptno,dname,saljob from emp;

合并查询：合并多个select语句

1）union：用于取得两个结果的并集，自动去掉结果重的重复行。

2）union all:该操作符与union 相似，但是它不会取消重复行，而且不会排序

3）intersect:取交集

4）minus:取差集:只显示存在第一个集合中而不存在第二个集合中的数据

select * from emp where empno=7782 minus select * from emp where empno=7499;

oracle表内连接，外联接

内链接：使用的最多的一种链接：select 列名 from tableName inner join tableName2 on 条件

内链接特点：只有两张表同时匹配才会选择

例子：显示员工的信息和部门名称：

select ename,dname from emp,dept where emp.deptno=dept.deptno; =select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

外联接：（新建一张stu表，内容包括id,name 一张exam 表 id,grade,插入数据进行测试）

1）左外联：（如果stu（左表）的记录没有和exam表任一记录匹配上也要被选中）

显示所有人成绩，如果没有成绩也要显示其id和姓名成绩显示空：

select name,id,grade from stu left join exam on stu.id=exam.id;d等同于 select name,id ,grade from stu,exam where stu.id=exam.id(+);

2）右外联：（右面的表如果没有和左表任一记录匹配也要被选中）

显示所有成绩，如果没有名字与其匹配则显示name为空：

select id,name,grade from stu right join exam on stu.id=exam.id;等同于select name,id ,grade from stu,exam where stu.id（+）=exam.id;

如果stu（左表）的记录没有和exam表任一记录匹配上也要被选中

判断左右表：放在left join 左边

小结：实际上左外联和右外联是可以互相转换的

3）完全外联（两个表查询不管有没有匹配上都显示出来）

select name,id,grade from stu full outer join exam on stu.id=exam.id