MySQL复合查询

目录

一、查询基本回顾

二、多表查询

 三、自连接

 四、子查询

1、单行子查询

2、多行子查询

3、多列子查询

4、在from子句中使用子查询

5、合并查询

五、内外连接

1、内连接

2、外连接

2.1、左外连接

2.2、右外连接

---

一、查询基本回顾

1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

2、按照部门号升序而雇员的工资降序排序

3、使用年薪进行降序排序

4、显示工资最高的员工的名字和工作岗位

可以使用复合查询，先找到最高工资，之后将这个最高工资作为条件去查找

语句执行时先执行（）内的select语句

5、 显示工资高于平均工资的员工信息

6、显示每个部门的平均工资和最高工资

要在显示时带上deptno但是使用了聚合函数，那么就要将deptno作为分组的依据

7、显示平均工资低于2000的部门号和它的平均工资

8、显示每种岗位的雇员总数，平均工资

二、多表查询

dept表

1、显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询 

原理：select* from emp，dept；相当于将这两张表作笛卡尔积形成一个新的表，这个表有很多错误数据，比如一个员工的deptno有很多个，此时只需要找到两张表中相同的，就是正确的deptno所在元组

两张表应该这样连接，就是正确的表：select* from emp，dept where emp.deptno=dept.deptno;

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

作为条件的deptno=10前面要加上emp或者dept，即使这两个是一样的意思但是要指明

3、显示各个员工的姓名，工资，及工资级别

salgrade表：

此时没有具体相同的值，也可以用值是否在一个范围来连接两个表

 三、自连接

自连接即一个表自己和自己作连接

1、显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

使用子查询：

先通过子查询找到名字为FORD的领导的编号（即FORD的mgr），再通过这个编号找到对应的empno所在的元组即是FORD员工的领导的信息

自连接：

假想有两张emp表，通过第一张表的姓名为FORD的员工找到mgr，再去第二张表中找到与这个mgr相同的empno所在的元组；

因为两张表都是相同的，所以要指定在哪个表中，所以有e1，e2；

表名也可重命名，和属性一样，因为是在from后面，而from在select语句中是最先执行的，所以重命名的表名几乎可以在select的任何地方使用

 四、子查询

1、单行子查询

显示和SMITH同一部门的员工：

2、多行子查询

in 关键字；查询和 10 号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含 10 自

己的：

all 关键字；显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号：

any 关键字；显示工资比部门 30 的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门

的员工）：

3、多列子查询

查询和 SMITH 的部门和岗位完全相同的所有雇员，不含 SMITH 本人：

（deptno，job）两个属性放在一个括号里面相当于一个属性，这样好去比较

4、在from子句中使用子查询

子查询语句出现在 from 子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资：

在from里面创建一临时表tmp，这个表用来计算部门的平均工资，这样就能和emp表里面相同部门的不同员工比较工资了

查找每个部门工资最高的人的姓名、工资、部门、最高工资

显示每个部门的信息（部门名，编号，地址）和人员数量

方法一：表的连接查询

方法二：

子查询

5、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all 

union

该操作符用于取得两个结果集的并集。当使用该操作符时， 会自动去掉结果集中的重复行。

案例：将工资大于 2500 或职位是 MANAGER 的人找出来：

 union all

该操作符用于取得两个结果集的并集。当使用该操作符时， 不会去掉结果集中的重复行。

将工资大于 25000 或职位是 MANAGER 的人找出来

五、内外连接

1、内连接

内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表 1 inner join 表 2 on 连接条件 and 其他条件；

显示 SMITH 的名字和部门名称 :

标准的内连接写法：

2、外连接

2.1、左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

语法：

select 字段名 from 表名 1 left join 表名 2 on 连接条件

案例：

-- 建两张表

create table stu (id int , name varchar ( 30 )); -- 学生表

insert into stu values ( 1 , 'jack' ),( 2 , 'tom' ),( 3 , 'kity' ),( 4 , 'nono' );

create table exam (id int , grade int ); -- 成绩表

insert into exam values ( 1 , 56 ),( 2 , 76 ),( 11 , 8 );

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

-- 当左边表和右边表没有匹配时，也会显示左边表的数据

select * from stu left join exam on stu .id =exam .id ;

左外连接即是保留左边的表信息，对于右边的表以左边的表为基准，满足on后面条件的就显示，不满足也显示但是为null

2.2、右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接。

语法：

select 字段 from 表名 1 right join 表名 2 on 连接条件

案例：

对 stu 表和 exam 表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要

显示出来

右外连接即是保留右边的表信息，对于左边的表以右边的表为基准，满足on后面条件的就显示，不满足也显示但是为null

这里的左右是语句中表出现的相对位置

练习：

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

分析：这是以部门为基准的，所以左外连接时部门表在左边，连接的是员工信息表

连接执行顺序是先from再join连接之后看on条件，最后select；可以想象成对两个表的笛卡尔积进行筛选。