oracle学习 复杂查询

一，UML -- 统一建模语言

二，利用PowerDesigner设计数据库

外连接：

内连接-- inner join 特点：返回的是两个表都满足条件的记录 

外连接 --

左(join)外连接 -- 返回的是两个表都满足条件的记录，及左边的表中不满足条件的记录

右外连接 -- 返回的是两个表都满足条件的记录，及右边的表中不满足条件的记录

--例3 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门

select * from emp right outer join dept on emp.deptno=dept.deptno;

select * from dept left outer join emp on emp.deptno=dept.deptno;

--例5 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门,及没有部门的员工

select * from emp full outer join dept on emp.deptno=dept.deptno;

--5,自连接

-- 显示员工姓名及其上级姓名

select e1.ename 上级,e2.ename 下级 from emp e1 inner join emp e2 on e1.empno = e2.fatharno;

-- 连接的优点：1，返回多表记录    2，可以使用索引，速度快，但是功能有限。

--三，子查询：

--例1：查询工资高于 SMITH 的所有员工

select * from emp where sal>(select sal from emp where ename='SMITH');

--例2：找到高于公司平均工资的员工

select * from emp where sal>(select avg(sal) from emp);

--例3：找到最高工资的员工

select * from emp where sal=(select max(sal) from emp);

--2, 可以使用 > >= < <= =  <>  ，但是这些关系运算符后只能跟 一个值。

--例4，找出工资高于 SMITH及FORD 的员工。

select * from emp where sal>(select sal from emp where ename='SMITH' or ename='FORD');

-- 例5，找出比 10 部门最低工资高的员工

select * from emp where sal>(select min(sal) from emp where deptno=10);

--3， 使用 in

-- 找到在 DALLAS 或 NEW YORK

select * from emp where deptno in(select deptno from dept where loc in('DALLAS','NEW YORK')) ;

--4，关联子查询

-- 例6，查找工资高于 本部门 平均工资的员工。

select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno);

                          

-- 例7，查找每个部门的最高工资的员工

-- 关联子查询

select ename,deptno from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno);

-- 嵌套子查询

select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

四，select做表达式：

--例1：查询部门信息，及每个部门的人数

select a.*,(select count(*) from emp where deptno = a.deptno) 部门人数 from dept a;       

--例2：查询部门信息，及每个部门 工资在500-1000  1001-3000  3001- 的人数

select a.*,

(select count(*) from emp where sal>=500 and sal<=1000 and a.deptno=deptno) "500-1000",

(select count(*) from emp where sal>=1001 and sal<=3000 and a.deptno=deptno) "1001-3000",

(select count(*) from emp where sal>=3001 and a.deptno=deptno) "3001-" 

from dept a;

五，特殊的select

1，备份表：

create table emp1 as select * from emp;

select * from emp1;

一，UML -- 统一建模语言

二，利用PowerDesigner设计数据库

外连接：

内连接-- inner join 特点：返回的是两个表都满足条件的记录 

外连接 --

左(join)外连接 -- 返回的是两个表都满足条件的记录，及左边的表中不满足条件的记录

右外连接 -- 返回的是两个表都满足条件的记录，及右边的表中不满足条件的记录

--例3 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门

select * from emp right outer join dept on emp.deptno=dept.deptno;

select * from dept left outer join emp on emp.deptno=dept.deptno;

--例5 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门,及没有部门的员工

select * from emp full outer join dept on emp.deptno=dept.deptno;

--5,自连接

-- 显示员工姓名及其上级姓名

select e1.ename 上级,e2.ename 下级 from emp e1 inner join emp e2 on e1.empno = e2.fatharno;

-- 连接的优点：1，返回多表记录    2，可以使用索引，速度快，但是功能有限。

--三，子查询：

--例1：查询工资高于 SMITH 的所有员工

select * from emp where sal>(select sal from emp where ename='SMITH');

--例2：找到高于公司平均工资的员工

select * from emp where sal>(select avg(sal) from emp);

--例3：找到最高工资的员工

select * from emp where sal=(select max(sal) from emp);

--2, 可以使用 > >= < <= =  <>  ，但是这些关系运算符后只能跟 一个值。

--例4，找出工资高于 SMITH及FORD 的员工。

select * from emp where sal>(select sal from emp where ename='SMITH' or ename='FORD');

-- 例5，找出比 10 部门最低工资高的员工

select * from emp where sal>(select min(sal) from emp where deptno=10);

--3， 使用 in

-- 找到在 DALLAS 或 NEW YORK

select * from emp where deptno in(select deptno from dept where loc in('DALLAS','NEW YORK')) ;

--4，关联子查询

-- 例6，查找工资高于 本部门 平均工资的员工。

select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno);

                          

-- 例7，查找每个部门的最高工资的员工

-- 关联子查询

select ename,deptno from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno);

-- 嵌套子查询

select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

四，select做表达式：

--例1：查询部门信息，及每个部门的人数

select a.*,(select count(*) from emp where deptno = a.deptno) 部门人数 from dept a;       

--例2：查询部门信息，及每个部门 工资在500-1000  1001-3000  3001- 的人数

select a.*,

(select count(*) from emp where sal>=500 and sal<=1000 and a.deptno=deptno) "500-1000",

(select count(*) from emp where sal>=1001 and sal<=3000 and a.deptno=deptno) "1001-3000",

(select count(*) from emp where sal>=3001 and a.deptno=deptno) "3001-" 

from dept a;

五，特殊的select

1，备份表：

create table emp1 as select * from emp;

select * from emp1;