MySQL

目录

• 联表查询

联表查询

• 内连接
  

SELECT *
FROM A
INNER JOIN B
ON A.key = B.key ;

• 左外连接
  

SELECT *
FROM A
LEFT JOIN B
ON A.key = B.key ;

• 右外连接
  

SELECT *
FROM A
RIGHT JOIN B
ON A.key = B.key ;

• 筛选左外连接
  

SELECT *
FROM A
LEFT JOIN B
ON A.key = B.key
WHERE B.key IS NULL ;

• 筛选右外连接
  

SELECT *
FROM A
RIGHT JOIN B
ON A.key = B.key
WHERE A.key IS NULL ;

• 嵌套结果

// 一个部门可以有多个员工
@Data
public class Department {
    private Integer id;
    private String name;
    private List<Employee> employees;
}
// 一个员工只能属于一个部门
@Data
public class Employee {
    private Integer id;
    private String name;
    private Department department;
}
@Mapper
public interface EmployeeMapper {
    Employee selectEmployeeById(Integer id);
}
@Mapper
public interface DepartmentMapper {
    Department selectDepartmentById(Integer id);
}

<!-- association 用于处理一对一或多对一的关联关系，即一个对象关联另一个对象 -->
<mapper namespace="com.example.EmployeeMapper">
    <resultMap id="employeeMap" type="com.example.Employee">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="department" javaType="com.example.Department">
            <id property="id" column="dept_id"/>
            <result property="name" column="dept_name"/>
        </association>
    </resultMap>
    <select id="selectEmployeeById" resultMap="employeeMap">
        SELECT e.id, e.name, d.id AS dept_id, d.name AS dept_name
        FROM employee e INNER JOIN department d ON e.dept_id = d.id
        WHERE e.id = #{id}
    </select>
</mapper>

<!-- collection 用于处理一对多或多对多的关联关系，即一个对象包含多个对象 -->
<mapper namespace="com.example.DepartmentMapper">
    <resultMap id="departmentMap" type="com.example.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="employees" ofType="com.example.Employee">
            <id property="id" column="emp_id"/>
            <result property="name" column="emp_name"/>
        </collection>
    </resultMap>
    <select id="selectDepartmentById" resultMap="departmentMap">
        SELECT d.id, d.name, e.id AS emp_id, e.name AS emp_name
        FROM department d LEFT JOIN employee e ON d.id = e.dept_id
        WHERE d.id = #{id}
    </select>
</mapper>