玖叶教程网

前端编程开发入门

2024年09月17日

MySQL数据库小技能:内外连接

理解SQL查询的过程是进行SQL优化的理论依据。

SQL查询的基本原理:

  • 单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
  • 两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
  • 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

I 内连接 （inner join）

在一个表中能够找到在另一个表中与之对应的记录，来组成新的记录显示出来，舍弃表中在另一个表中没有对应记录的记录。

inner join (inner可以省略)： 查出的是两张表的交集，两张表都有的才查出来

select * from 表A inner join 表B on 判断条件;

select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name  from risk_equipment_position r inner join mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15 ;

复制代码

1.1 等值连接

一个表中的某个字段是另一个表的外键。

例如： emp表和dept表存在多对一的关联关系，由外键字段deptno来维护，即emp表来主动维护这一关系。

1.2 非等值连接

between and 来实现非等值连接；

select e.ename，e.sal，s.grade from emp e join salgrades on e.sal between s.losal and s.hisal；

1.3 自连接( nature join)

表中的字段之间存在引用关系，通过同一个表取不同别名来实现自身连接。

select e.empno，m.name from emp e join emp m on m.empno=e.mgr；

II 外连接

外连接,将驱动表中所有记录显示。

说明：

  • 左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表是右边的。
  • 在三种类型的外连接中，OUTER 关键字是可省略的。

2.1 左外连接 （left join）

左外连接会将主表对应的所有信息显示，从表与之匹配的记录显示。

以左表为主表（查询全部）， 右表为辅表（没有的显示null）

案例1:

select * from emp join dept on emp.deptno=dept.deptno(+); --加号在join 右边的表的属性上为左连接

select *  from emp left join dept on emp.deptno=dept.deptno;


复制代码

案例2: 查询商户终端定位限制

select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name  from risk_equipment_position r left join mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15 ;

复制代码

2.2 右外连接 （right join）

加号在join 左边的表的属性上为右连接

以右表为主表（查询全部记录）， 左表为辅表（没有的显示null）

select r.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from risk_equipment_position r right join mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15
复制代码

2.3 全连接 （ full join）

将两表中所有匹配和不匹配记录显示出来：两个表的所有数据都展示出来

MySQL 不识别 FULL join，所以可以通过 union 来实现。

2.4 联合(合并)查询（union）

案例1：

select a.*, ad.* from test_a as a left join test_a_description as ad on a.id=ad.parent_id 
union
select a.*, ad.* from test_a as a right join test_a_description as ad on a.id=ad.parent_id;
复制代码

案例2:

select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name  from risk_equipment_position r left join mer_merchant m on r.merchant_id = m.merchant_id  union select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name  from t_risk_equipment_position r right join t_mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15;
复制代码

III 案例：mybatis-plus 多表查询

3.1 多表查询：在mapper.xml 写语句和拼接查询条件

在mapper.xml 写语句,对应方法写到mapper里，service可以注入对应的mapper进行调用。

.xml

    <select id="selectProfitTradeActivityList"
            resultType="com.dto.TradeActivityDto"
            parameterType="com.dto.GenerateTradeQuery">
        select a.id,a.trade_no,a.activity_type,a.activity_code
        from trans_flow t
        inner join trans_flow_activity a on t.trade_no = a.trade_no
        <where>
            t.trade_state='S' and a.pay_channel_id='XYD'
            <if test="ps.startTradeTime != null">
                and t.trade_time >= #{ps.startTradeTime}
            </if>

        </where>
--         order by t.update_time desc
    </select>
复制代码
@Mapper
public interface TransFlowActivityMapper extends BaseMapper<TransFlowActivity> {

    List<GenerateTradeActivityDto> selectProfitTradeActivityList(@Param("ps") GenerateTradeQuery input);

}

复制代码

3.2 多表关联：Java代码中书写语句和拼接查询条件

实现思路：

  • 使用 Select注解书写sql语句
  • 采用apply方法自定义sql拼接条件， 使用条件构造器作为参数。
  1. 驱动表表和辅表的参数拼接
  2. 书写SQL

3.3 案例：左外连接

实现思路：

  • 使用 Select注解书写sql语句/xml书写语句。
  • 采用apply方法自定义sql拼接条件， 使用wrapper条件构造器作为参数。

实现步骤：

  1. 定义查询接口： mapper.java/Service.java
  • Service层
@Override
    public List<RiskEquipmentPositionDto> getlist(LambdaQueryWrapper<RiskEquipmentPosition> input) {
        return baseMapper.getList(input);
    }
复制代码
  1. Mapper层：采用 ${ew.customSqlSegment}和@Param(Constants.WRAPPER)实现，将条件构造器作为参数。
  2. 书写SQL 方式一：使用Select注解书写sql语句
@Select("select r.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from risk_equipment_position r left join mer_merchant m on r.merchant_id = m.merchant_id ${ew.customSqlSegment} ")
    List<RiskEquipmentPositionDto> getList(@Param(Constants.WRAPPER) LambdaQueryWrapper<RiskEquipmentPosition> input);
复制代码

where后面的内容就是条件构造器生成的部分

方式2: 使用xml书写语句

  1. 拼接查询条件
LambdaQueryWrapper<RiskEquipmentPosition> lambda = new LambdaQueryWrapper<>();
        lambda.orderByDesc(TRiskEquipmentPosition::getCreateTime);
        if (input.getStartTime() != null) {
            lambda.ge(RiskEquipmentPosition::getCreateTime, input.getStartTime());
        }
        if (input.getEndTime() != null) {
            lambda.lt(RiskEquipmentPosition::getCreateTime, input.getEndTime());
        }
        if (input.getState() != null) {
            lambda.eq(RiskEquipmentPosition::getState, input.getState());
        }
        // 终端SN、商户号本表查询，其他关键词需要关联商户表进行查询。
        if (null != input.getKeywordType() && StringUtils.isNotBlank(input.getKeyword())) {

            var keyWordTypes = new ArrayList<String>();
            keyWordTypes.add(ERiskEquipmentPositionKeyWord.MER_ID.getCode());
            keyWordTypes.add(ERiskEquipmentPositionKeyWord.FAC_ID.getCode());
            keyWordTypes.add(ERiskEquipmentPositionKeyWord.TOP_ID.getCode());
            Long longKeyWord = 0L;
            if (keyWordTypes.contains(input.getKeywordType())) {
                try {
                    longKeyWord = Long.parseLong(input.getKeyword());
                } catch (Exception ex) {
                    throw CommonException.create(ServerResponse.createByError("无效的编号"));
                }
            }
            switch (BaseEnum.getEnum(ERiskEquipmentPositionKeyWord.class, input.getKeywordType())) {
                case SN:
                    lambda.eq(RiskEquipmentPosition::getEquipmentSn, input.getKeyword());
                    break;
                case MER_ID:
                    lambda.apply("r.merchant_id = {0}",input.getKeyword());
                    break;
                case MER_NAME:
                    lambda.apply("m.merchant_name = {0}",input.getKeyword());
                    break;
                case FAC_ID:
                    //apply方法用于拼接自定义的条件判断,如果自定义的条件判断是需要独立的结果，就必须记得加括号。
                    lambda.apply("m.facilitator_id = {0}",input.getKeyword());
                    break;
                case FAC_NAME:
                    lambda.apply("m.facilitator_name = {0}",input.getKeyword());
                    break;
                case TOP_ID:
                    lambda.apply("m.faclitator_top_id= {0}",input.getKeyword());
                    break;
                case TOP_NAME:
                    lambda.apply("m.facilitator_top_name= {0}",input.getKeyword());
                    break;
            }

        }
        // 分页查询
        PageHelper.startPage(input.getPageNum(), input.getPageSize());
        List<RiskEquipmentPositionDto> positionList = tRiskEquipmentPositionService.getlist(lambda);
        PageInfo pageInfo = new PageInfo(positionList);
        PageHelper.clearPage();
        return pageInfo;
复制代码
  1. 调用查询接口
        List<RiskEquipmentPositionDto> positionList = RiskEquipmentPositionService.getlist(lambda);

复制代码

IV 笛卡尔积 （cross join）

交差集: 把表A和表B的数据进行一个N*M的组合，即笛卡尔积。 SELECT * FROM TableA CROSS JOIN TableB

V 小结:

5.1 外连接的区别分析

外连接：外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。

外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）

  1. 三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。
  2. 不同点如下：
  • 左外连接：还返回左表中不符合连接条件但符合查询条件的数据行。
  • 右外连接：还返回右表中不符合连接条件但符合查询条件的数据行。
  • 全外连接：还返回左表中不符合连接条件但符合查询条件的数据行，并且还返回右表中不符合连接条件但符合查询条件的数据行。

全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外 = 左外 UNION 右外”。

5.2 Column 'merchant_id' in where clause is ambiguous

错误代码：SELECT count(0) FROM risk_equipment_position r LEFT JOIN mer_merchant m ON r.merchant_id = m.merchant_id WHERE (merchant_id = ?)

case MER_ID:
                    lambda.eq(TRiskEquipmentPosition::getMerchantId, input.getKeyword());
                    break;
复制代码

原因：多表查询的时候几个表中同时出现了某个相同的列名，而在查询条件WHERE后面又没有指定是那个表，而引起的。

解决方案：where 条件语句后查询某个字段要加上表名。可以使用mybatis的apply方法来拼接自定义的条件判断。

                case MER_ID:
                    lambda.apply("r.merchant_id = {0}",input.getKeyword());
                    break;
                case MER_NAME:
                    lambda.apply("m.merchant_name = {0}",input.getKeyword());
                    break;
                case FAC_ID:
                    //apply方法用于拼接自定义的条件判断,如果自定义的条件判断是需要独立的结果，就必须记得加括号。
                    lambda.apply("m.facilitator_id = {0}",input.getKeyword());
                    break;
复制代码

相关问题：查询结果里面有两个相同的列名，而没有指定是哪个表使用的时候，sql查询前面加表名可避免出现错误。

VI 集合

集合查询中的字段个数和类型要一致，这称为select的同构。

select  job from emp deptno=10 union all select job  from emp deptno=20;

复制代码
  • union all 并集，并且不去除重复
  • union 并集，去除重复
  • intersect 交集
  • minus 差集



原文链接：https://juejin.cn/post/7183296217126797369
来源：稀土掘金

标签：mysqlinnerjoin 

作者:gojiuye , 分类:技术精选 , 浏览:1 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言