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

布满荆棘的人生 2022-05-27 04:36 168阅读 0赞

### **前提：** ###

--部门表
    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;