欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle教程之Oracle的多表查询举例(笛卡尔集、等值与非等值连接、外连接、自连接、SQL1999连接、Oracle的set运算符)实例

程序员文章站 2022-07-07 22:34:08
前提: --部门表 create table 部门表( deptno number primary key, dname varchar2(10)...

前提:

--部门表
create table 部门表(
    deptno number primary key,
    dname  varchar2(10)
);
--员工表
create table 员工表(
    empno number primary key,
    ename varchar2(10),
    deptno number,
    foreign key(deptno) references 部门表(deptno)
);

--部门表中的记录
insert into 部门表(deptno, dname) values(10,'销售部');
insert into 部门表(deptno, dname) values(20,'人事部');


--员工表中的记录
insert into 员工表(empno,ename,deptno) values(1,'张三',10);
insert into 员工表(empno,ename,deptno) values(2,'李四',20);
insert into 员工表(empno,ename,deptno) values(3,'王五',10);

--提交
commit;




笛卡尔集

--笛卡尔集

select empno,ename, 员工表.deptno, 部门表.deptno, dname 

from 部门表, 员工表;


--添加合适的条件,可以避免笛卡尔集,从而得到正确的多表查询记录

select empno,ename, 员工表.deptno, 部门表.deptno, dname 

from 部门表, 员工表 

where 部门表.deptno = 员工表.deptno;

等值与非等值连接

--查询员工信息,要求显示:员工号,姓名,职位,部门名称

--等值连接
select empno,ename,job,dname from emp, dept where emp.deptno = dept.deptno;

--多个条件的等值连接,使用AND操作符
select e.empno,e.ename,e.job,d.dname,d.deptno from emp e, dept d  where e.deptno = d.deptno and e.deptno=10;


--显示所有员工的员工号、姓名、工资及其工资的等级。

select * from salgrade;

--非等值连接
select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between losal and hisal;

外连接

--按部门统计员工的人数,要求显示:部门号,部门名称,人数

select d.deptno,d.dname,count(e.empno) from dept d, emp e where d.deptno = e.deptno group by d.deptno, d.dname;

select * from dept;

select * from emp where deptno=40;

--外连接
select d.deptno,d.dname,count(e.empno) from dept d, emp e where d.deptno = e.deptno(+) group by d.deptno, d.dname;

自连接

--自连接

--查询所有员工的姓名和直属上级的姓名
select e.ename,m.ename 
from emp e, emp m 
where e.mgr = m.empno;

--验证   ford-->jones
select * from emp;

SQL1999连接

--cross join
select d.dname, e.ename, d.deptno, e.deptno from dept d cross join emp e;
select count(*) from emp;
select count(*) from dept;


--natural join
--查询员工名、工资以及所在部门名称
select e.ename, e.sal, d.dname from dept d natural join emp e;

--内连接
--using子句
select e.ename,e.sal, d.dname from dept d join emp e using(deptno);

--通过on指定内连接的条件
select e.ename,e.sal, d.dname from dept d join emp e on d.deptno = e.deptno;

--内连接的关键字inner join, inner通常省略
select e.ename,e.sal, d.dname from dept d inner join emp e on d.deptno = e.deptno;

--左外连接
select e.ename,e.sal, d.dname from dept d left join emp e on d.deptno = e.deptno;

--右连接
select e.ename,e.sal, d.dname from dept d right join emp e on d.deptno = e.deptno;

--完全连接
select e.ename,e.sal, d.dname from dept d full join emp e on d.deptno = e.deptno;

Oracle的set运算符

--emp01
create table emp01
as
select * from emp where deptno in(10,20);

--emp02
create table emp02
as
select * from emp where deptno in(20,30);

--合并显示emp01表和emp02表所有雇员的部门编号、员工号、员工姓名。
--10号部门有3个、20号部门有5个、
select * from emp01;
--30号部门有6个、
select * from emp02;

--union  14个记录
select deptno, empno, ename from emp01
union
select deptno, empno, ename from emp02;

--union all
--通过部门号进行排序
select deptno, empno, ename from emp01
union all
select deptno, empno, ename from emp02
order by deptno;

--通过列值进行排序,1代表第一列
select deptno, empno, ename from emp01
union all
select deptno, empno, ename from emp02
order by 1;

--intersect
select deptno, empno, ename from emp01
intersect
select deptno, empno, ename from emp02;

--minus
select deptno, empno, ename from emp01
minus
select deptno, empno, ename from emp02;