SQL基础—2

1.左外连接查询（left join on）

A - A∩B 左外连接查询两张表条件都满足的数据，以及左边表(A表)存在的数据(以左边表为主查询表)。
A - A∩B (A和A交B)。

示例：使用左外连接将dept表作为主查询表，查询员工编号、员工姓名、部门名称。观察内连接和左外连接有啥区别？

步骤：

1. 列出查询关键字。select、from、leftjoin on.
2. 确定要查询的表名称。例如：from emp inner join dept
3.  使用on关键字进行条件过滤。例如：on emp.deptno = dept.deptno
4. 指定要查询的列。例如： emp.empno 员工编号,emp.ename 员工名称, dept.dname 部门名称

小结：

2 自连接查询

自连接查询将自己(emp表)连接自己(emp表)进行关联查询。多个关联查询的表是同一张表，通过取别名的方式
生成两张虚拟表。语法如下：

示例：查询每个员工编号、姓名、上司编号、上级姓名

步骤：

1. 确定查询关键字 select、from、where
2. 确定表名称 emp e1, emp e2
3. 确定关联条件 e1.mgr = e2.empno
4. 确定列名称 e1.empno 员工编号 ,e1.ename 员工姓名, e2.empno 上司编号 , e2.ename 上司姓名

核心步骤：员工表的上司编号字段关联上司表的员工编号字字段

3 合并查询查询   关键字：（union all 和 union）

合并查询将多个查询结果集合并成一个

union all 和 union区别：

union all将多个查询结果集合并成一个结果集，不会去掉重复的数据
union将多个查询结果集合并成一个结果集，会去掉重复的数据

步骤：

1. 查询职位是“职员”的所有员工信息
2. 查询部门编号为20的所有员工信息
3. 将上面两个查询的结果集使用union all关键字合并成一个结果集

查询结果如下：

小结：上面的查询编号769的员工出现了两次，union all 并没有去掉两个结果集重复的数据 。

为了解决上面的问题，我们可以使用union关键字去掉结果集里面重复的数据

使用union进行合并查询，去掉了结果集重复的数据。查询结果如下

小结：

1. 合并查询里面的每个select语句后面的列名称必须相同，否则执行报错
2. union能够去掉多个结果集中重复的数据
3. union all由于没有去重，所以效率高于union

4.子查询

在一个查询语句中嵌套一个查询语句叫做子查询，所有的子查询都包含在父查询的一对小括号里面。子查询通常可以出现在以下几个地方：where之后、having之后、from之后、from之前。子查询返回的结果集有三种：单(单行单列)、多(多行单列)、多(多行多列) 。

单行单列子查询

单行单列子查询通常定义在父查询的where关键字后面作为父查询行过滤的条件

示例：查询工资高于公司平均工资的所有员工

分析：公司平均工资我们不知道，先查询平均工资，然后嵌入到父查询

步骤：

1. 定义查询语句需要的关键字 select、from、where
2. 在from后面定义父查询的表名称 emp
3. 在where后面确定父查询条件 where 工资高于公司平均工资
4. 在select确定要查询的列名称

查询结果

多行单列子查询

多行单列子查询作为一个伪列定义在父查询的from关键字之前

示例：查询员工编号、员工名称、员工所在部门

1.多表联合查询

2.使用将“员工所在部门”字段作为子查询产生一个伪列，嵌入到父查询中

1.  定义父查询需要的关键字 select、from
2. 在父查询的from后面定义要查询的表名称
3. 在父查询的select后面定义要查询的列
4. 在父查询的select后面定义子查询
5. 定义子查询需要的关键字 select、from、where
6. 在子查询的from后面定义要查询的表名称
7. 在子查询的where后面定义条件
8. 在子查询的select后面定义要查询的列

查询结果

 多行多列子查询

多行多列子查询通常作为一张伪表（为了增加查询语句的可读性，通常会为伪表定义表别），定义在父查询的from关键字之后

示例：查询平均工资高于2000的职位名称和职位平均工资

两种方法1.使用group by对“职位”字段进行分组，然后使用having关键字后面定义条件(平均工资高于2000)

. 这种方式能够查询到正确的结果集，但是聚合函数avg使用了两次，很影响程序执行的性能。
 解决方案：将上一个示例的select、from、group by作为子查询嵌入到父查询中。

2.having 关键字工作中效率比较低，可以先使用子查询计算职位名称和职位平均工资，然后将子查询合入到父查询，最后在父查询的where后面定义条件(平均工资高于2000)

示例：查询部门编号、部门名称、部门位置、部门人数、部门平均工资

分析：前三列都是部门表(dept)的基础列，后两列需要在员工表需要在聚合函数中进行计算。将员工表需要计算的字段作为子查询生成一张伪表，嵌入到父查询的dept表中

示例：查询出所有在销售部工作的员工编号、姓名、基本工资、奖金、职位、入职日期、部门最高和最低工资

该示例可以分成两段

• 查询出所有在销售部工作的
• 员工编号、姓名、基本工资、奖金、职位、入职日期、部门最高和最低工资

 要查询的字段都在emp表，其中 员工编号 、 姓名 、 基本工资 、 奖金 、 职位 、 入职日期 是基础字段， 部门最高工资 、 部门最低工资 这两个字段是需要使用聚合函数进行计算的。我们把需要计算的字段作为子查询的伪表嵌入到父查询