MYSQL的连接查询介绍

一.连接查询：

有SQL92语法和SQL99语法之分

1. SQL92的基本形式：

主要讲内连接的三种：
等值连接

#等值连接
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的基本形式：

2.1 内连接：

只是将sql92语法里面的两张表之间的逗号，用inner jion 连接起来，两张表的连接条件用在on后面写下来，where 后面还是可以写筛选条件。

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 外连接

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

特点：
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交叉连接

特点：
在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

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

两条语句的执行结果完全一样：

要求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 '%张%';

结果完全一样：

要求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

结果一样：

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

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

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

案例1：

案例2：

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

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

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

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

案例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

效果一样：

案例2：
课件案例截图

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

结果：

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