外键 左连接 和自连接的用法

1 在创建表时加入外键

表1

CREATE table department(

id  int primary key auto_increment,
name varchar(20) not null,
description varchar(100)
)

表2
CREATE table employee(
id int PRIMARY key auto_increment,
name VARCHAR(20) not null,
dep_id  int not null,
description varchar(100),

CONSTRAINT waijianName FOREIGN KEY (dep_id)
REFERENCES  department(id)

)

2

内连接：只列出匹配的记录

左外连接：左表列出全部，右表只列出匹配的记录。

例子

SELECT
a.id id ,
c.id userId,
b.user_auth_name custName,
c.username phone,
b.identity_type shenfen,
    a.updated_time updatedTime ,
    a.`status` status,
    
a.category_type type,
    d.full_name subjectName,
    e.zh_name brandChina,
    e.en_name brandEnglish,
a.updated_by  updateName

FROM
user_auth_category a
LEFT JOIN user_auth_info b ON a.user_auth_info_id = b.id
LEFT JOIN `user` c on b.user_id=c.id
LEFT JOIN `subject` d on a.category_id = d.id and a.category_type=1
left join brand e on a.category_id = e.id and a.category_type=2

WHERE a.is_active = 1
AND
    a.is_admin = 1
AND a. STATUS IN (1, 10,15)
     <if test="userId != null and userId != '' " >
      and b.user_id = #{userId,jdbcType=BIGINT}
    </if>
<if test="custName != null and custName!=''" >
      and b.user_auth_name LIKE CONCAT('%',#{custName,jdbcType=VARCHAR},'%')
    </if>
    <if test="updateName != null and updateName!=''" >
      and a.updated_by LIKE CONCAT('%',#{updateName,jdbcType=VARCHAR},'%')  and a.status != 1
    </if>
    <if test="status != null and status != '' " >
      and a.status = #{status,jdbcType=TINYINT}
    </if>
    <if test="shenfen != null and shenfen != ''" >
      and b.identity_type = #{shenfen,jdbcType=TINYINT}
    </if>
    <if test="name != null and name!=''" >
      and (d.full_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%')
    
      or e.zh_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%')
    
      or e.en_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%'))
    </if>
    <if test="phone != null and phone!=''" >
      and c.username = #{phone,jdbcType=VARCHAR}
    </if>
    <if test="endTime!=null and endTime!=''">
      <![CDATA[   and DATE_FORMAT(a.updated_time, '%Y-%m-%d')<=  DATE_FORMAT(#{endTime}, '%Y-%m-%d')   ]]>
    </if>
    <if test="startTime!=null and startTime!=''">
      <![CDATA[   and DATE_FORMAT(a.updated_time, '%Y-%m-%d')>=  DATE_FORMAT(#{startTime}, '%Y-%m-%d')   ]]>
    </if>
    ORDER BY CASE a.status  WHEN '1 ' THEN 1 WHEN '15' THEN 2 WHEN '10' THEN 3 END,a.updated_time DESC
    <if test="offset != null and max != null" >
      limit ${offset},${max}
    </if>

说明：多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。

ON后面的条件（ON条件）和WHERE条件的区别：
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。

小结 ：左连接查询出主表的全部数据 ，多个left join时一步步去生成中间过滤表，最后要加where条件语句进行过滤筛选，得出所需结果。

右外连接：右表列出全部，左表只列出匹配的记录。

自连接：参与连接的表都是同一张表。(通过给表取别名虚拟出两张表)

例子：

注：非常重要，在JavaWeb中的目录树中用的特别多。

举例：查询出员工姓名和其leader的姓名（类似于求节点及其父节点）

我们来详细解释一下上面的代码。对于同一张employee表，我们把e1作为员工表，e2作为领导表。首先把全部的员工列出来（基于左外连接），然后找到我们所需要的条件：员工的经理id（e1.leader）等于经理表的id（e2.id）。

 

举例：查询出所有leader的姓名。

分析的道理同上。

其实，上面的两个查询结果都是下面这个查询结果的一部分：

 

八、总结
连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：
1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。