|
理解SQL查找的过程是进行SQL优化的理论依据。
SQL查找的基本原理: 单表查找：按照WHERE要求过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；而后按照SELECT的选取列选取相应的列进行返回最后结果。两表连接查找：对两表求积（笛卡尔积）并用ON要求和连接类型进行过滤形成中间表；而后按照WHERE要求过滤中间表的记录，并按照SELECT指定的列返回查找结果。多表连接查找：先对第1个和第二个表根据两表连接做查找，而后用查找结果和第三个表做连接查找，以此类推，直到所有的表都连接上为止，最后形成一个中间的结果表，而后按照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 joindepton emp.deptno=dept.deptno(+); --加号在join 右边的表的属性上为左连接
select * from emp left join dept on emp.deptno=dept.deptno;
复制代码案例2: 查找商户终端定位限制 selectr.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom risk_equipment_position r left join mer_merchant m onr.merchant_id = m.merchant_idORDER BY create_time DESC LIMIT 15 ;
复制代码2.2 右外连接 （right join）
加号在join 左边的表的属性上为右连接
以右表为主表（查找所有记录）， 左表为辅表（无的表示null） selectr.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom risk_equipment_position r right join mer_merchant m onr.merchant_id = m.merchant_idORDER 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 fromrisk_equipment_position rleft join mer_merchant m on r.merchant_id = m.merchant_id union selectr.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom 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拼接要求， 运用要求构造器做为参数。驱动表表和辅表的参数拼接书写SQL3.3 案例：左外连接
实现思路： 运用 Select注解书写sql语句/xml书写语句。采用apply办法自定义sql拼接要求， 运用wrapper要求构造器做为参数。实现过程： 定义查找接口： mapper.java/Service.javaService层@Override
publicList<RiskEquipmentPositionDto>getlist(LambdaQueryWrapper<RiskEquipmentPosition> input) {
returnbaseMapper.getList(input);
}
复制代码Mapper层：采用 ${ew.customSqlSegment}和@Param(Constants.WRAPPER)实现，将要求构造器做为参数。书写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书写语句
拼接查找要求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())) {
varkeyWordTypes =newArrayList<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) {
throwCommonException.create(ServerResponse.createByError("无效的编号"));
}
}
switch (BaseEnum.getEnum(ERiskEquipmentPositionKeyWord.class, input.getKeywordType())) {
caseSN:
lambda.eq(RiskEquipmentPosition::getEquipmentSn, input.getKeyword());break;
case MER_ID:
lambda.apply("r.merchant_id = {0}",input.getKeyword());
break;
caseMER_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;
复制代码调用查找接口 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） 三者的一起点是都返回符合连接要求和查找要求（即：内连接）的数据行。区别点如下：左外连接：还返回左表中不符合连接要求但符合查找要求的数据行。右外连接：还返回右表中不符合连接要求但符合查找要求的数据行。全外连接：还返回左表中不符合连接要求但符合查找要求的数据行，并且还返回右表中不符合连接要求但符合查找要求的数据行。全外连接实质是上左外连接和右外连接的数学合集（去掉重复），即“全外 = 左外 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办法来拼接自定义的要求判断。 caseMER_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
源自：稀土掘金
|
发表于 2024-8-4 09:57:39
