Oracle基础(五):多表查询

一、多表查询

(一)简单多表查询

1、多表查询的机制
1)SQL:
SELECT * FROM emp; --14条记录
SELECT * FROM dept;--4条记录
SELECT * FROM emp,dept;--显示56条数据??为什么
2)分析:
  • 先从 dept 选择一条记录(deptno=10)。分别于 emp中的14条记录分别匹配。显示14条记录。
  • 再从 dept 选择一条记录(deptno=20),分别于 emp中的14条记录分别匹配。显示14条记录。
  • 依次类推。

Oracle基础(五):多表查询

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjIyODcxOA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">


2、笛卡尔集。
  • 在多表查询的时候,假设不带不论什么条件,则会笛卡尔集。
  • 避免:查询的条件不能少于表个数减一。(2表。必须有1个条件)
3、多表查询实例
1)显示雇员名、雇员工资及所在部门的名称
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno;
  • 部门号匹配就可以。

2)显示部门号为10 的部门名、员工名和工资

SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno and a.deptno=10;

3)显示各个员工的姓名、工资,及其工资的级别

SELECT a.ename,a.sal,b.grade FROM emp a, salgrade b WHERE a.sal between b.losal and b.hisal;

4)显示雇员名、雇员工资及所在部门的名字,并按部门排序

SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno order by b.dname;

(二)自连接

1、含义:自连接是指在同一张表的连接查询,即把一张表看成 2 张表。

2、实例
1)显示员工Ford的上级领导的姓名
SELECT a.* FROM emp a WHERE a.empno = (SELECT a.mgr FROM emp a WHERE a.ename = 'FORD') ;

分析:

  • 先查询出FORD 的领导的员工编号。
  • 然后在依据员工编号,查询其信息
  • 单个能够使用子查询解决。使用easy出错:【单行子查询返回多个行】

2)显示各员工的姓名和他的上级领导姓名

SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno;--13条数据
SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno(+);--14条数据
  • 【+】写在右边。表明是左外连接。左边的表列所有显示。
  • 分析:
    • 把 emp 一张表看成2张表,然后雇员表的 mgr 等于领导表的 empno。
    • 【King】没有上级,不会显示。要求显示时,须要使用外连接。
    • 使用自连接解决这个问题。

(三)子查询*

1、含义:
1)子查询是指嵌入其它sql语句中的 select语句,也叫嵌套查询
2)单行子查询:单行子查询是指仅仅返回一行数据的子查询语句。
3)多行子查询:多行子查询指返回多行数据的子查询。

2、实例

1)显示与SMITH同一部门的全部员工(包含SMITH)(单行子查询:“=”)

SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' );
  • 分析
    • SELECT a.deptno FROM emp a WHERE a.ename='SMITH' ,返回一行记录。即单行子查询。
    • SQL解析默认从右往左。即先解析子查询语句。
  • 注意:where 子查询中,不能使用order by 排序。
    • SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' order by a.empno);
    • 报错,“缺失右括号”

2)查询和10号部门的工作岗位同样的雇员的名字、岗位、工资、部门号 (多行子查询:“in”)

SELECT a.ename,a.job,a.sal,a.deptno FROM emp a WHERE a.job in (SELECT distinct a.job FROM emp a WHERE a.deptno = 10) ;
  • 分析:
    • 先查询出10号部门的全部工作岗位,返回多行结果,为多行子查询。
    • 显示和他的岗位有一个同样的员工。
    • 使用easy出错:【单行子查询返回多个行】
3、all 和 any 操作符
1)显示工资比30号部门的全部员工的工资高的员工的姓名、工资和部门号
  • 方法1:使用max()聚合函数
SELECT a.* FROM emp a WHERE a.sal > (SELECT max(a.sal) FROM emp a WHERE a.deptno=30);
  • 方法2:使用 all
SELECT a.* FROM emp a WHERE a.sal >all (SELECT a.sal FROM emp a WHERE a.deptno=30);

2)显示工资比30号部门的随意一个员工的工资高的员工姓名、工资和部门号,不是不论什么一个都高

  • 方法1:使用min()聚合函数
SELECT a.* FROM emp a WHERE a.sal > (SELECT min(a.sal) FROM emp a WHERE a.deptno=30);
  • 方法2:使用 any
SELECT a.* FROM emp a WHERE a.sal >any (SELECT a.sal FROM emp a WHERE a.deptno=30);
4、多列子查询
1)区别
  • 单行子查询:单行单列
  • 多行子查询:多行单列
  • 多列子查询:多列
2)实例:查询与SMITH 的部门和岗位全然同样的全部雇员
SELECT a.* FROM emp a WHERE (a.deptno,a.job) = (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
SELECT a.* FROM emp a WHERE (a.deptno,a.job) in (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
  • 这边“=”与 in 效果一致。

5、在 From 子句中使用子查询
1)显示高于自己部门平均工资的员工的信息
SELECT a.ename,a.sal,b.avg_sal,a.deptno FROM emp a ,(SELECT avg(a.sal) as avg_sal,a.deptno as deptno FROM emp a group by a.deptno) b WHERE a.deptno = b.deptno and a.sal > b.avg_sal;
  • 分析:
    • 先要知道各个部门的平均工资是多少。然后作为一个暂时表
    • 利用emp 与暂时表多表查询

2)查找每一个部门工资最高的人的具体资料

  • 方法1:
SELECT a.ename,a.sal,t.max_sal , a.deptno FROM emp a, (SELECT max(a.sal) as max_sal,a.deptno as deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno and a.sal = t.max_sal;
    • 得到各个部门的最高工资,作为一个暂时表
    • 利用emp 与暂时表多表查询
  • 方法2:
SELECT a.* FROM emp a WHERE a.sal = (SELECT max(b.sal) FROM emp b WHERE b.deptno = a.deptno ) ;
    • 这样的方法。无法显示子查询中的列数据,有缺陷。

3)显示每一个部门的信息和人员数量

SELECT a.*,t.count_empno FROM dept a, (SELECT count(a.empno) as count_empno,a.deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno(+);
  • 先获取每一个部门的人员数量。作为一个暂时表
  • 利用dept 表与暂时表多表查询
  • +:表示外连接

6、小技巧

1)行迁移

create table temp# as select empno, ename from emp where ename like 'J%';

2)蠕虫复制

insert into myTest (empno,ename,job) select empno,ename,job from myTest;

3)子查询更新数据

update emp  set (empno,ename,job) = (select  empno,ename,job from emp where ename = 'SMITH')  where ename='SCOTT';

(四)合并查询

1、UNION:并集去重。排序
1)解释:该操作符用于取得两个结果集的并集。

当使用该操作符时,会自己主动去掉结果集中反复行。

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION SELECT b.* FROM emp b WHERE b.job='MANAGER';

2、UNION ALL:并集不去重,而且不排序

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION ALL SELECT b.* FROM emp b WHERE b.job='MANAGER'; 

3、INTERSECT:交集

Oracle基础(五):多表查询

4、MINUS:差集,总之不会显示第二个集合中的数据
1)有交集的情况:

Oracle基础(五):多表查询

2)没有交集的情况:

Oracle基础(五):多表查询

二、分页

(一)其它数据库的分页

1、MySQL

1)语法:

select * from 表名 where 条件 limit 从第几条,取几条
select * from emp where empno=1234 limit 1,3;
2、Sql server
1)语法:
select top 2 * from 表名 where id not in (select top 4 id from 表名 where 条件)
排除前4条,再取2条,实际上是取5-6条。

(二)Oracle:

1、语法:rownum

SELECT rownum, emp.* FROM emp where rownum <6;
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4;

2、分析:

  • 分析:rownum 不能直接使用大于号(>)
  • 使用三层嵌套。事实上就是在from中使用子查询。
  • rownum三层嵌套,分别在第二层和第三层中使用效率最高。
    • 第一层:SELECT * FROM emp
    • 第二层:SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10
    • 第三层:SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4;
3、实例
1)依照入职时间的先后顺序,查询从第 7 到第10 个。
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT a.* FROM emp a order by a.hiredate ) t WHERE rownum < 10) t2 WHERE t2.num > 7。
  • 排序等特殊处理等情况。请在最内层处理

2)測试。模拟10万的数据

  • 创建表与数据:
    • 以存在的表为模板创建新表:(行迁移)
create table myTest as select empno,ename,job from emp;
    • 自我复制(蠕虫复制)
insert into myTest (empno,ename,job) select empno,ename,job from myTest;
  • 測试:
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM myTest ) t WHERE rownum < 51010) t2 WHERE t2.num > 50997;

三、内、外连接

(一)内连接
1、概述:内连接就是利用where 子句对2张表形成的笛卡尔集进行筛选。
2、格式
1)普通:
  • 语法:select 列名... from 表1 表2.. where 条件
SELECT a.ename,a.deptno,b.dname FROM emp a,dept b WHERE a.deptno = b.deptno;

2)【inner】  Join  on

  • 语法:select 列名... from  表1 inner
     Join  表2 on  (条件)
SELECT a.ename,a.deptno,b.dname FROM emp a inner join dept b on (a.deptno = b.deptno) 
3、内连接的特点:
  • 仅仅有2张表都匹配上的记录。才会显示。
(二)外连接 left|right|full【outer】join on 
1、创建2 新表 :

Oracle基础(五):多表查询

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjIyODcxOA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">

2、左外连
1)特点:left join
  • 左表所有显示
  • 右表匹配显示

2)显示全部人的成绩,假设没有成绩,也要显示该人的姓名和id号,成绩显示为空

  • SQL:
    • 内连接:
      • SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id; 
      • 仅仅显示2条数据。没有成绩的学生不会显示
    • 外连接:
      • SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id(+);
        SELECT a.id,a.name,b.grade FROM stu a left join exam b on( a.id = b.id);
  • 分析:
    • 内连接的时候,须要2张表都匹配上的记录
    • 左外连接:stu(左表) 表中与exam 中没有匹配的,也须要显示。

Oracle基础(五):多表查询

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjIyODcxOA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">

3、右外连
1)特点:right join
  • 左表匹配显示
  • 右表所有显示

2)显示左右成绩。假设没有名字匹配,显示为空

  • SQL:
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id(+) = b.id;
SELECT a.id,a.name,b.grade FROM stu a right join exam b on( a.id = b.id);
  • 分析:
    • 右外连接:exam(右表) 表与 stu 表中没有匹配的,也须要显示。

Oracle基础(五):多表查询

4、全然外连
1)特点: full outer join
  • 左表所有显示
  • 右表所有显示

2)显示全部成绩和全部人的名字。假设没有对应的匹配值,则显示为空

  • SQL:
SELECT a.id,a.name,b.grade FROM stu a full join exam b on( a.id = b.id);
SELECT a.id,a.name,b.grade FROM stu a full outer join exam b on( a.id = b.id);
  • 分析:
    • 右外连接:exam(右表) 表与 stu 表中没有匹配的,也须要显示。

Oracle基础(五):多表查询

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjIyODcxOA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">

5、总结:
1)左、右外连能够相互转换,仅仅须要换表的位置就可以。
SELECT a.id,a.name,b.grade FROM stu a right join exam b on(a.id = b.id);
SELECT a.id,a.name,b.grade FROM exam b left join stu a on( a.id = b.id);

2)小结

Oracle基础(五):多表查询

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjIyODcxOA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">

6、练习:

1)列出部门名称和这些部门员工的员工信息。同一时候列出那些没有员工的部门

SELECT b.dname,a.* FROM emp a right join dept b on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM dept b left join emp a on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE b.deptno = a.deptno(+) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE a.deptno(+) = b.deptno order by b.deptno;



四、练习

1、显示雇员总数,以及获得补助的雇员数
<span style="font-family:Arial;font-size:18px;">SELECT count(*) ,count(comm) FROM emp;</span>

2、显示管理者的人数

SELECT count(distinct mgr) FROM emp;

3、显示每一个部门每一个岗位的平均工资、每一个部门的平均工资、每一个岗位的平均工资。
<span style="font-family:Arial;font-size:18px;">SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );</span>
  • cube:立方体函数。对deptno分组,job分组。对(deptno、job)分组
上一篇:Jetbrains 系列神器


下一篇:【转载】va_list 可变参数 简介 va_copy vprintf