黑马程序员MySQL数据库基础-SQL之多表查询

一、多表关系介绍

• 一对多（多对一）

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的地方建立，指向一的一方的主键

•  多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

•  一对一

案例：用户与用户信息的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方插入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

二、多表查询概述

• 概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔乘积）

 若要消除笛卡尔积，如下例：

需要加一个条件，即外键值=主键值

select * from emp, dept where emp.dept_id=dept.id;

• 多表查询分类：

三、内连接

语法

• 隐式内连接

        SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

• 显式内连接

        SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

        （inner可省略）

示例：

 注：多表查询时可以给两张表起别名，但是起了之后必须只用别名

相对而言，隐式连接好理解好书写，语法简单，担心的点较少

但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显

四、外连接

语法（默认表1是左表，表2是右表）

• 左外连接

        SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件...;

• 右外连接

        SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件...;

        （左外是指保留左边所有的数据，然后如果有和右边对应的，就把右边的数据也带上）右外连接同理哦！

示例：

 通常来讲，使用左外连接更多一点，因为左外和右外本质上是镜像的关系

五、自连接

语法

隐式：SELECT 字段列表 FROM 表A 别名A,表A 别名B WHERE 条件...;

显式：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接可以是内连接和外连接

示例：

 第一个是使用内连接实现的自连接，必须要求managerid和id都非空且值相等才查出来

第二个用左外连接，除了要求字段值匹配以外，哪怕managerid为空也能查出来

两种方式数学上是不同的，但是都被归为“自连接”这一类

六、联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

        语法

        SELECT 字段列表 FROM 表A ...

        UNION [ALL]

        SELECT 字段列表 FROM 表B ...;

示例：

 关于or和联合查询的差异：

第一or去重，二，实际应尽量避免在where子句中使用or来连接条件。使用or可能会使索引失效，从而全表扫描。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

七、子查询

概览：

• 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

 示例：

步骤：分步写出sql语句，再合并写出子查询语句

提问：是否可以用group by实现同样的效果

 不可以，如果用group by，分组是按照id分的，你只能得到6个分组，这个时候你查询的字段只有分组字段和聚合函数有意义，得不到员工信息

• 列子查询

子查询返回的结果是一列

常用的操作符：IN、NOT IN、ANY、SOME、ALL

 示例：

 

你可能想到了聚合函数max，但是在where中是无法使用聚合函数的。

 

• 行子查询

子查询返回的结果是一行

常用操作符：=、<>、IN、NOT IN

 

• 表子查询

子查询返回的结果是多行多列

常用操作符：IN

示例：

 注意上面这里‘in’的理解