MYSQL的连接查询介绍

今天药忘吃喽~ 2021-12-09 22:07 260阅读 0赞

# 一.连接查询： #

有SQL92语法和SQL99语法之分

## 1. SQL92的基本形式： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70]  
**主要讲内连接的三种：**  
等值连接

#等值连接
    SELECT 查询列表
    FROM 表1 别名，表2 别名
    WHERE 表1.关键字段=表2.关键字段
    【AND 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】
    /* 特点： ①一般为表起别名 ②多表的顺序可以调换 ③N表连接至少需要N-1个连接条件 ④等值连接的结果是多表的交集部分 */

非等值连接

#非等值连接
    SELECT 查询列表
    FROM 表1 别名，表2 别名
    WHERE 非等值的连接条件（如：between...and..,<>,>,<,等）
    【AND 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

自连接

#自连接
    SELECT 查询列表
    FROM 表1 别名a，表1 别名b
    WHERE 别名a.关键字段=别名b.关键字段
    【AND 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】
    #实质就是将一张表查询了两次，看成两张表

## 2.SQL99的基本形式： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 1]

#### **2.1** **内连接**： ####

只是将sql92语法里面的两张表之间的逗号，用inner jion 连接起来，两张表的连接条件用在on后面写下来，where 后面还是可以写筛选条件。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 2]

**2.1.1等值连接**

#等值连接
    inner join 
    作为连接表与表之间的关键字
    SELECT 查询列表
    FROM 表1 别名  
    INNER JOIN 表2 别名
    ON 连接条件
    【where 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

**2.1.2非等值连接**

#非等值连接
    SELECT 查询列表
    FROM 表1 别名  
    INNER JOIN 表2 别名
    ON 非等值的连接条件（如：between...and..,<>,>,<,等）
    【WHERE 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

**2.1.3自连接**

#自连接
    SELECT 查询列表
    FROM 表1 别名a  
    INNER JOIN 表1 别名b
    ON 连接条件(比如：别名a.关键字段=别名b.关键字段)
    【WHERE筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

#### **2.2 外连接** ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 3]  
应用场景:用于查询一个表中有，另一个表中没有的记录

特点：  
**1.** 外连接的查询结果为主表中的所有记录  
如果从表中有和它匹配的，则显示匹配的值  
如果从表中没有和它匹配的，则显示匹配null  
外连接查询结果=内连接结果+主表中有而从表中没有的记录  
**2.** 左外连接，left jion 左边的是主表  
右外连接，right jion 右边的是主表  
**3.** 左外和右外 交换两个表的顺序，可以实现同样的效果

**2.2.1左外连接**

#左外连接
    SELECT 查询列表
    FROM 表1 别名  
    LEFT  JOIN 表2 别名
    ON 连接条件
    【where 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

**2.2.2右外连接**

#右外连接
    SELECT 查询列表
    FROM 表1 别名  
    RIGHT JOIN 表2 别名
    ON 连接条件
    【where 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

**2.2.3全外连接**

#全外连接
    SELECT 查询列表
    FROM 表1 别名  
    FULL JOIN 表2 别名
    ON 连接条件
    【where 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

#### **2.3交叉连接** ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 4]  
**特点：**  
在SQL92语法中，笛卡尔乘积的产生是两张表用逗号隔开，没有加where 连接条件产生的  
在SQL99语法中，笛卡尔乘积的产生就是交叉连接的查询

#交叉连接
    SELECT 查询列表
    FROM 表1 别名  
    CROSS JOIN 表2 别名
    【where 筛选条件】
    【GROUP BY  分组字段】
    【HAVING 分组后的筛选】
    【ORDER BY 排序字段】

#### 3. 区别就在于： ####

原来sql92语法里面的两张表之间的 **逗号** ，用 **关键字 (inner,left,right,full,cross) jion** 连接起来，两张 表的连接条件用在on后面 写下来，where 后面写筛选条件

#### **4.案例** ####

两张表beauty和boys  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 6]

##### 1.**等值连接案例**（内连接中的inner jion 中的inner 可以省略，以下案例并未省略） #####

###### 要求1：查询女生的男朋友信息 ######

等值连接查询：**两种写法参考对比，更加容易理解记忆**

# SQL92语法
    select name,boyname from boys b,beauty a where a.boyfrind_id=b.id
    # SQL99语法
    select name,boyname from beauty INNER JOIN boys	on beauty.boyfrind_id=boys.id

两条语句的执行结果完全一样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 7]

###### 要求2：查询女生的男朋友信息，其中男朋友名称必须带有‘张’字 ######

思路：带有筛选条件的等值连接查询

#SQL92
    select name,boyname 
    from boys b,beauty a 
    where a.boyfrind_id=b.id and boyname like '%张%';
    
    #SQL99
    select name,boyname 
    from beauty a INNER JOIN boys b	 
    on a.boyfrind_id=b.id 
    where boyname like '%张%';

结果完全一样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 8]

##### 要求3：查询每个男生中拥有过女朋友的个数大于2 的男生名称和女朋友个数 #####

思考：分组函数，分组查询group by ,筛选条件 都要用到

#SQL92
    select boyname,count(*) 
    from boys b,beauty a 
    where a.boyfrind_id=b.id 
    group by boyname 
    having count(boyname)>2
    
    #SQL99
    select boyname,count(*)
    from beauty INNER JOIN boys	
    on beauty.boyfrind_id=boys.id 
    group by boyname 
    having count(boyname)> 2

结果一样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 9]

##### 要求4：inner jion 的三表连接 （注意书写格式，N张表需要N-1个连接条件） #####

截图课件案例（和另外两张都有关联的表，放在最前面）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 10]

##### 2.非等值连接的案例 （内连接中的inner jion 中的inner 可以省略） #####

案例1：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 11]

案例2：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 12]

##### 3.自连接的案例 （内连接中的inner jion 中的inner 可以省略） #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 13]

##### 4. 左（右） 外连接【left (right)outer …join…中outer可以省略】 #####

**外连接**  
应用场景:用于查询一个表中有，另一个表中没有的记录

案例1：  
要求：查询男朋友 不在男神表的女神表：

#查询男朋友 不在男神表的女神表：
    #左外连接实现：
    select g.id,g.name,b.boyName 
    from beauty g LEFT join boys b 
    on g.boyfrind_id=b.id
    where b.boyName IS NULL
    
    #右外连接实现：
    select g.id,g.name,b.boyName 
    from  boys b right join beauty g 
    on g.boyfrind_id=b.id
    where b.boyName IS NULL

效果一样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 14]  
案例2：  
课件案例截图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 16]

##### 5.全外连接 （mysql不支持，oracle支持） #####

全外连接 = **内连接的结果** \+ **表1中有但表2中没有的** \+ **表2中有但表1中没有的**  
注意：mysql不支持全外连接，oracle支持，两张表没有顺序关系  
语法结构一样。举例：

#两张表的数据全部查询，不分主表，从表
    select g.*,b.* 
    FROM beauty g FULL JOIN boys b
    ON g.boyfrind_id=b.id

##### 6.交叉连接（就是笛卡尔乘积） #####

在SQL92语法中，笛卡尔乘积的产生是两张表用逗号隔开，没有加where 连接条件产生的  
在SQL99语法中，笛卡尔乘积的产生就是交叉连接的查询

#SQL92 笛卡尔乘积
    SELECT g.*,b.*
    FROM beauty g,boys b
    
    #SQL99 交叉连接（笛卡尔乘积）
    SELECT g.*,b.* 
    FROM beauty g CROSS JOIN boys b

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 17]

# 二、关于join的集合的图形记忆 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 18]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 19]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70]: /images/20211209/0f8f7126405e45948f55e7307c6f8150.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 1]: /images/20211209/cd324c0ee70e495490fcf30e68f69246.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 2]: /images/20211209/a9ff37870c2a4552b38f81f83d5c6d54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 3]: /images/20211209/9e2f4e36d7db48b6b2f790c28bd83bff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 4]: /images/20211209/7e292b724fde43cf9c09e7c40f30a1bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 5]: /images/20211209/cecace5d7eec445eb3aa31be438d59d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 6]: /images/20211209/3c02438e6b7342409aa5fff0c28b8eb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 7]: /images/20211209/37c48e304a614ac5b8861ed9fd8338ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 8]: /images/20211209/49f2288ade2046baa73558e53c351e46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 9]: /images/20211209/1590086b18724de4b1257741e58d2cbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 10]: /images/20211209/7ec0e8a359eb4f75b96ac93dea57c9fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 11]: /images/20211209/28ff512570d744a8a872092a397bad3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 12]: /images/20211209/176a503ab3704125b7d946dbbf04e38b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 13]: /images/20211209/25c93f0931494ec09c7f67b3dd518115.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 14]: /images/20211209/dc5a0538508545c4ad69985f4a411dc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 15]: /images/20211209/742b1418ef2442749b5516fc85db673c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 16]: /images/20211209/2f3a14a7cbc44c8bbaf626064d98f600.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 17]: /images/20211209/3da452db034e4c929c6e9442f5d156cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 18]: /images/20211209/5bb27cfa876c4dc48c05e55dc123baec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 19]: /images/20211209/19461e17bb954e7ab5980c0e837e600d.png