数据库:mysql基础(四)---高级查询

最新推荐文章于 2025-04-21 22:36:27 发布

原创最新推荐文章于 2025-04-21 22:36:27 发布 · 456 阅读

1 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

21 篇文章

订阅专栏

高级查询

关联查询(连接查询)
- 查询多张表或者结果集
- 下面是文中需要用到的两个表
  - dept表
    - deptno dename loc
      10 ACCOUNTING NEW YORK
      20 RESEARCH DALLAS
      30 SALES CHICAGO
      40 OPERATIONS BOSTON
      部门编号,部门名称,坐落位置
  - emp表
    - 员工编号,员工名称,工作,其上级的编号,入职时间,工资,奖金,部门编号
- 内连接
  - 补充知识:笛卡尔积
    - 笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
    - 上面的两个表中,emp表15条记录,dept表4条记录。连接查询的笛卡尔积为60条记录。
  - 内连接语法
    - where子句
    - ```
    #查询所有的员工及其所在部门信息
    select * from emp,dept where emp.deptno = dept.deptno;
```
  得出的结果集中deptno有两列,分别为deptno和deptno(1),虽然写的是(1),但是是一样的.不能用过区分
- inner join…on...
- ```
#查询所有的员工及其所在部门信息
select * from emp inner join dept on emp.deptno = dept.deptno;
```
    - inner join…using…
    - ```
    #查询所有的员工及其所在部门信息
    select * from emp inner join dept using(deptno);
```
  上面第三种并不是特别通用(因为两个表必须有相同的字段名).但是这个deptno字段不重复.
- 内连接的特点
  - 多张关联表共有的数据所在的记录才能出现在结果集.(上面emp表中没有为40的deptno字段,而dept表中存在,结果集中只存在共有的,所以deptno为40的字段不存在)
  - 不区分主从表,内连接结果与连接顺序无关。 (哪个表在连接关键字前无所谓).
- 外链接
  - 引入.思考一下这个问题:查询所有的部门及其部门下的员工信息。
  - 部门必须是所有的,也就是说不能有遗漏,所以对于内连接这种只能查找出共有属性的连接已经不能使用了.那么怎么办呢?这就引出了下面要说的外链接.
  - 外链接语法:
    - 左外连接: left [outer] join ... on...
    - ```
    #左外连接...下面使用emp左外连接dept表,后面必须跟条件,否则依旧是笛卡尔积
    select * from emp left join dept on emp.deptno = dept.deptno;
    #上面语句的结果是是14条记录,和内连接没区别,往下看
    
    #下面使用dept表左外连接emp表
    select * from dept left join emp on emp.deptno = dept.deptno;
    #这次结果是15条,多了一条deptno为40的记录.但是40号部门地下没有员工,所以后面都是空
```
  我们发现,emp表和dept表谁在前面会引发结果的不同:下面解释下原因:外链接其实是有主从表之分的.连接关键字左边的为主表,左外连接是以主表为基准,依次来查询的.遍历主表每一条记录,根据你给出的条件,依次在从表中找到所匹配的记录.然后展示出来
- 上图中,主表遍历到30后,没有了,就不会继续遍历了.所以从表中的40,并没有匹配.输出的结果集和内连接一样
- 再看主表是dept表的图形展示,我想大家可以看出区别和明白为什么结果集会出现不同了吧.
- 右外连接: right [outer] join ... on...
- 这两个起始记住一个即可
- 外链接的特点
  - 与连接顺序有关
  - 有主从表之分,依次遍历主表中的每条记录与关联表记录进行匹配，如果匹配到则关联并展示到结果集，否则以null填充。
- 自然连接
  - 自然连接都是等值连接,等值连接不一定是自然连接
  - 语法:
    - ```
    select * from emp NATURAL join dept;
```
- 其实就是找的emp中和dept表中字段值相同的,然后关联在一起.最终的通用列只有一个.但是不是很通用,因为要求字段必须相等,在做一些非等值的操作就不能使用了

子查询(嵌套查询)

将一个查询结果作为另一个查询的条件或者组成继续进行检索

分类

#先看一个例子
#查询20号部门所有员工及其所在部门信息
#用关联查询来做
 select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 20;

#用子查询来做
#先找出来20号部门员工的信息
select * from emp where deptno =20;
#from子句是可以跟表,结果集的,所以我们可以按下面这样
select * from (select * from emp where deptno =20) e,dept where e.deptno = dept.deptno;

#再看下面一个例子
#查询所有员工及其上级领导(mgr)的名称(自连接)
select e1.ename '姓名',e2.ename '领导' from emp e1 left join emp e2 on e1.mgr = e2.empno;

#继续看下面这个例子
#查询编号为7788的员工所在部门的信息。是否发现什么不同
#我们要查询的结果在一张表内,但是查询的条件却在另外一张. 当然我们用关联查询也能做出来
select dept.* from (select * from emp where empno = 7788) e,dept where e.deptno = dept.deptno;

#除了上面这种思路还有其他的么?其实有一个更为直接的思考方式
#查询7788员工的部门编号
select deptno from emp where empno =7788;        #结果是20
#根据部门编号查询部门信息
select * from dept where deptno = 20;

#我们结合一下
select * from dept where deptno = (select deptno from emp where empno =7788); 
#这种思路,就是找到这些表的关联字段.只要我们知道关联字段,我们就能查询了--将条件和结果放到一张表中(查找通用列)

其实嵌套查询就比较符合我们思考方式的一种查询了.

单行子查询---子查询返回结果是一条记录
- select * from dept where deptno = (select deptno from emp where empno =7788);返回的就是一条记录,部门编号
多行子查询:子查询返回多条记录
- #查询工资大于2000的员工所在部门信息。
  - select * from dept where deptno in (select distinct deptno from emp where sal > 2000);
- 除了in 我们还可以用 any / all
  - =any: 相当于in >any:大于最小值 <any:小于最大值
  - >all:大于最大值 <all:小于最小值
- 下面是一些小的练习..可以思考一下

#查询工资>20号部门平均工资的员工信息。
#子查询:先找出来20号部门的平均工资
select avg(sal) from emp where deptno = 20
select * from emp where sal > (select avg(sal) from emp where deptno = 20);

#查询工资>20号部门平均工资并且不在20号部门的员工信息。
select * from emp where sal > (select avg(sal) from emp w here deptno = 20) and deptno <> 20;

#查询工资大于所在部门的平均工资的员工信息。
#方式1,关联查询
select emp.* from emp,(select deptno,avg(sal) avg from emp group by deptno) e where emp.deptno = e.deptno and sal > e.avg;

#方式2:嵌套查询
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

#最后一题用嵌套方式查询的执行过程:
#主查询将deptno传给子查询
#子查询根据主查询的deptno查询指定部门的平均工资
#子查询将该部门平均工资返回给主查询
#主查询根据返回结果进行最后检索。

下面看一下前面做过的题

#查询20号部门以及工资>2000的员工信息。
select * from dept where deptno in (select deptno from emp where sal > 2000);

#上面的一个细节:就是主查询要的是一个deptno,所以子查询就要返回一个deptno
#如果子查询返回的是deptno,dname的话,那么就会报错了.如果子查询返回的结果不是一个,但是主查询所需条件只能一个,那怎么做呢?

#有没有这样一种方式,我们不关心子查询返回了多少个结果,而只是去想办法让主查询中的条件能在子查询返回的结果中找到呢?引出了下面的关键字 exists.

#exists 就是不关心子查询你到底返回了多少个字段,只是关心你返回的所有字段中有没有我需要的字段.
#下面说一下这道题应用exists的解法

select * from dept where exists (select * from emp where sal > 2000 and emp.deptno = dept.deptno);

in和exists的区别
- in先执行子查询,将查询得到的结果整理完毕返回给主查询,主查询再根据条件在子查询返回的结果中查询，exists先执行主查询,把值交给子查询,依次去匹配
- in需要关注子查询返回值的字段信息；exists不需要关系返回字段
- exsts将主查询的记录依次在子查询匹配，如果匹配返回true并展示

那么我们什么时候用关联查询,什么时候使用子查询呢?
- 如果我们查询的结果存在于多张表的时候,使用关联查询较好
- 如果结果分布在一张表的时候,子查询较好
- 效率上的差异:关联查询是将多张表关联在一起,资源占用上稍微高一点;子查询资源的消耗稍微小一点,但是它可能存在多次查询

联合查询(合并结果集)

#查询20号部门或者工资>2000的员工信息。
select * from emp where deptno =20 or sal > 2000;

#按照联合查询来做是这样的
#先把20号部门的所有员工找出来
select * from emp where deptno =20
#再找出工资>2000的员工信息
select * from emp where sal > 2000;

#把这两个结果集合并到一起  用关键字  union / union all

select * from emp where deptno =20 union select * from emp where sal > 2000;
#结果和上面相同

#如果用union all呢?会发生什么
select * from emp where deptno =20 union all select * from emp where sal > 2000;
#结果是11条,闭上面的结果多了3条?为什么呢?
#因为union会去除重复.比如说员工编号为7788的这个记录,工资>2000,并且在20号部门,在两次查询的结果中都出现,union all 不会去除重复.就一起合并了