Mysql 语句—奇怪业务日常总结

绝地灬酷狼 2024-05-05 22:08 4阅读 0赞

### 1.表里某列只有三个值A/B/C 但是要显示为A/B/C/D 额外数据 ###

实际开发中，简单情况是 有两个表 A表、B表 根据左关联即可

> ![63cf0719b51d470299927f9ce521140d.png][]
> 
> ![23a6c759b8c840648ef7c93ac9bf96a1.png][]
> 
> SELECT a.\*, b.\* FROM A LEFT JOIN b ON a.a2 = b.a2
> 
> 利用on 关联，a表中a2全部显示，b表中有的显示，没有则为null
> 
> ![f1368d6ae5df49e2978eb1415499528b.png][]

但是特殊情况下，业务老师说我想显示a2的1到5全数据，但是没数据来源表，或者开发中有此类相似情况，怎么办？

> 方法一： 类似上述，自建临时表 相当于a表的作用
> 
> **方法二：此次主讲方法 利用values( Row(XX)) 做临时表**
> 
> SELECT  
> \*  
> FROM  
> (  
> VALUES  
> ROW ( 1, 1, 1 ),  
> ROW ( 2, 2, 2 ),  
> ROW ( 3, 3, 3 ),  
> ROW ( 4, 4, 4 ),  
> ROW ( 5, 5, 5 )  
> ) a\_bak (a1,a2,a3)  
> left join b on b.a2=a\_bak.a2
> 
> 方法三：
> 
> //先看随便用法：推送列表 记录每种批次类型的推送时间dt字段，
> 
> 源数据表content\_XX\_product 中有创建时间create\_time字段，
> 
> 要求：统计推送期间 content\_XX\_product表新增的数据。 先求推送期间 对应类型的最大时间、最小时间
> 
>     根据某天查询至昨天间新增营销数据
> 
>     with TEMP as (
>              SELECT MAX( b.dt ) AS max_date,
>              MIN( b.dt ) AS min_date,
>              count( b.dt ) AS num
>     FROM
>         to_do_ebs_user_XX a
>             LEFT JOIN to_do_XX b ON a.to_do_list_id = b.to_do_list_id
>             LEFT JOIN XX_user c ON a.login_code = c.login_code
>     WHERE
>         c.XX_user_id = #{req.id}
>       AND b.classify =#{req.classify}
>         )
>         SELECT ccp.publish_time,
>                ccp.title,
>                ccp.publish_platform,
>                ccp.product_column,
>                ccp.publish_column,
>                ccp.publisher,
>                ccp.content
>         FROM content_XX_product ccp
>         inner join TEMP
>             on ccp.create_time >= (DATE_SUB(temp.min_date, INTERVAL 1 DAY ) + INTERVAL 16 HOUR) AND ccp.create_time < (temp.max_date + INTERVAL 16 HOUR)
>         WHERE ccp.state = 1
>     order by ccp.create_time desc

### **2.mysql8的计算列 了解即可** ###

某一列的值是通过别的列计算得来的。例如，a 列值为 1，b 列值为 2，c 列不需要手动插入，定义 a + b 的结果为 c 的值，那么 c 就是计算列

> **CREATE TABLE test(  
> a INT,  
> b INT,  
> c INT GENERATED ALWAYS AS (a + b) \# c为计算列  
> );**
> 
> **\#此时操作**
> 
> **insert into test （a,b） values(10,20);**
> 
> <table style="width:500px;"> 
>  <tbody> 
>   <tr> 
>    <td>a</td> 
>    <td>b</td> 
>    <td>c</td> 
>   </tr> 
>   <tr> 
>    <td>10</td> 
>    <td>20</td> 
>    <td> <p>30</p> </td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> update test set a=100;
> 
> <table style="width:500px;"> 
>  <tbody> 
>   <tr> 
>    <td>a</td> 
>    <td>b</td> 
>    <td>c</td> 
>   </tr> 
>   <tr> 
>    <td>100</td> 
>    <td>20</td> 
>    <td>120</td> 
>   </tr> 
>  </tbody> 
> </table>

### 3.某复杂SQL 可能重复被使用 ###

> 当需要union all 关联时，查询的关联表都一样，只是条件不同；
> 
> 当需要复杂报表统计，需要用到聚合函数时；
> 
> 类似迭代时，sql；
> 
> ep:下为举例:
> 
> 1.
> 
> with
> 
> a as （select \* from test1), \# 第一个临时表
> 
> b as （select \* from test2) \# 第二个临时表
> 
> select a.XX,b.XX from a ,b where a.id =b.a\_id
> 
> 2. 此处一般子查询写法即可，但是这样后续看着清晰，也方便**多次调用！**！
> 
>     <sql id="Base_prefix"> //mybatis中方便多次调用
>         with prefix  as(SELECT
>                             a.NAME as agbranch,
>                             a.area,
>                             a.geography,
>                             c.sale_begin_date,
>                             c.sale_end_date,
>                             c.visit_begin_date,
>                             c.visit_end_date,
>                             c.fund_code,
>                             c.fund_name,
>                             b.dept_name,
>                             b.dept_code
>                         FROM
>                             department a
>                         LEFT JOIN major_channel_XX_config b ON a.parent_dept_code = b.dept_code
>                         LEFT JOIN major_channel_XXX C ON b.hold_task_no = c.hold_task_no
>                         WHERE
>                             a.use_range = 1
>                           AND a.geography != '00'
>             AND a.LEVEL = 1
>             AND a.dept_code != ''
>             AND b.hold_task_no = #{req.holdTaskNo}
>             and c.fund_code in
>             <foreach collection="list" item="item" separator="," open="(" close=")">
>                 #{item}
>             </foreach>
>             )
>     </sql>

### 4.针对一对多的查询结果 ###

##### 4.1一对一写法 #####

<resultMap id="BlogResult" type="Blog">
            <id property="id" column="blog_id" />
            <result property="title" column="blog_title" />
            <result property="create_time" column="create_time" />
            <result property="view" column="view_count" />
            <association property="author" javaType="Author">
                <id property="id" column="author_id" />
                <id property="name" column="name"/>
                <id property="address" column="address"/>
            </association>
        </resultMap>

##### 4.2一对多写法—返回非对象形式 #####

> 实体类：
> 
>     @Data
>     @ApiModel( value ="FundQryThing",description="产品查询参数")
>     public class FundQryThing{
>             @ApiModelProperty(value = "基金经理集合")
>             private List<String> fundManagerList;
>     }
>     1.映射结果集  整个FundQryThing
>      <resultMap id="Fund_Qry_Info_Rep" type="XX.**.FundQryThing">
>             <id column="id" property="id"/>
>             <result column="test_col1" property="test_col1"/>
>             <collection column="test_code" ofType="String" property="fundManagerList"
>                         select="queryByTestCode">
>             </collection>
>         </resultMap>
>     2.查询结果集   FundQryThing
>     <select id="selectListByColumns" resultMap="Fund_Qry_Info_Rep">
>             select name,test_code from test_fund where id in (1,2)
>      </select>
>     3.内部查询结果返回  整个fundManagerList
>       <select id="queryByTestCode" resultType="String">
>          select name from XX_relation  where xx=#{
>        test_code}  order by fm_sort
>        </select>

##### 4.3一对多写法—返回对象形式 #####

>     1.实体类
>     @Data
>     @ApiModel(value = "MajorDataRsp", description = "XX响应参数")
>     public class MajorDataRsp {
>         @ApiModelProperty(value = "任务名称")
>         private String holdTaskName;
>         @ApiModelProperty(value = "任务编号")
>         private String holdTaskNo;
>         @ApiModelProperty(value = "持营配置主键")
>         private Long HoldTaskConfigId;
>         @ApiModelProperty(value = "渠道编码")
>         private String xxCode;
>         @ApiModelProperty(value = "渠道名称")
>         private String xxName;
>         @ApiModelProperty(value = "产品配置详情")
>         private List<ProductDetail> product;
>         @ApiModelProperty(value = "创建时间")
>         private String createTime;
>         @ApiModelProperty(value = "更新时间")
>         private String updateTime;
>     }
>     2.mybatis映射——xml
>     <resultMap id="Channel_Detail_Map" type="xx.xx.MajorDataRsp">
>         <id property="HoldTaskConfigId" column="hold_task_config_id" jdbcType="BIGINT"/>
>         <result property="holdTaskNo" column="hold_task_no" jdbcType="VARCHAR"/>
>         <result property="holdTaskName" column="hold_task_name" jdbcType="VARCHAR"/>
>         <result property="xxCode" column="xx_code" jdbcType="VARCHAR"/>
>         <result property="xxName" column="xx_name" jdbcType="VARCHAR"/>
>         <collection  ofType="xx.xx.ProductDetail" property="product">
>             <result property="fundCode" column="fund_code"/>
>             <result property="fundName" column="fund_name"/>
>             <result property="saleBeginDate" column="sale_begin_date"/>
>             <result property="saleEndDate" column="sale_end_date"/>
>             <result property="visitBeginDate" column="visit_begin_date"/>
>             <result property="visitEndDate" column="visit_end_date"/>
>     
>         </collection>
>     </resultMap>
>     <select id="queryByHoldTaskNo" resultMap="Channel_Detail_Map">
>         SELECT
>             a.hold_task_config_id,
>             a.hold_task_no,
>             a.hold_task_name,
>             a.xx_code,
>             a.xx_name,
>             b.fund_code,
>             b.fund_name,
>             b.sale_begin_date,
>             b.sale_end_date,
>             b.visit_begin_date,
>             b.visit_end_date
>         FROM
>             xx_config a
>                 LEFT JOIN xx_product b ON a.hold_task_no = b.hold_task_no
>         WHERE
>             b.`status` =1
>           and b.hold_task_no=#{holdTaskNo}
>         order by b.fund_code
>     </select>

### 5.没有关联关系的两个或多表关联 ###

> **还是1中的a,b两个表作为案例：** 
> 
> **select a.\*,b.\* from a cross join b; //笛卡尔积**  A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n\*m条记录  
> **select a.\*,b.\* from a inner join b;**  //不加on 条件也是笛卡尔积 推荐，效率高一点
> 
> 带 on 时 效果也一样！ ep：**select a.\*,b.\* from a inner join b on 1=1**
> 
> 注意：`CROSS JOIN` 中，由于会获取两个表的所有组合，所以不需要指定连接条件！明说，关联就关联啦！不建议cross join 用on哦  
> **select a.\*,b.\* from a , b**  
> 三条SQL的执行效果相同
> 
> 仍有写法 **select \* from a left join b on 1=1;** 
> 
> ================
> 
>     WITH prefix AS (
>     SELECT
>     a.user_name,
>     a.geography,
>     a.job_number,
>     c.sale_begin_date,
>     c.sale_end_date,
>     c.visit_begin_date,
>     c.visit_end_date,
>     c.fund_code,
>     c.fund_name,
>     b.dept_name,
>     b.dept_code
>     FROM
>     xx_user a
>     CROSS JOIN major_channel_xx_config b  //此处没加条件， 换成inner join 也可以的
>     LEFT JOIN major_channel_hold_xx C ON b.hold_task_no = c.hold_task_no
> 
> **\# 注意：一些开发者有点轴。便于理解 就这样写吧** select \* from a left join b on 1=1;

### 6.部门表中分name，与full\_name字段，sql统一改为分行 ###

ep：如下列显示形式 需要将 level=1的改为类似于 工商银行北京分行 这样！

因为卡中心不需要变动，故只需要改北京、天津、上海的 为XX分行

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>name/名称</td> 
   <td>level/级别</td> 
   <td>full_name/全称</td> 
  </tr> 
  <tr> 
   <td>工商银行</td> 
   <td>0</td> 
   <td>工商银行</td> 
  </tr> 
  <tr> 
   <td>北京</td> 
   <td>1</td> 
   <td>工商银行北京</td> 
  </tr> 
  <tr> 
   <td>天津</td> 
   <td>1</td> 
   <td>工商银行天津</td> 
  </tr> 
  <tr> 
   <td>上海</td> 
   <td>1</td> 
   <td>工商银行上海分公司</td> 
  </tr> 
  <tr> 
   <td>信用卡中心</td> 
   <td>1</td> 
   <td>工商银行信用卡中心</td> 
  </tr> 
 </tbody> 
</table>

**1.先查询哪些数据需要改动**

SELECT `NAME`,`LEVEL`,full_name 
    FROM
    	XX_department
    WHERE
    	LEVEL = 1 
    	AND full_name NOT RLIKE '分行|卡中心' 
    	AND full_name RLIKE '工商银行'

**2.更新脚本**

update XX_department set full_name = case when full_name rlike '分公司' then replace(full_name,'分公司','分行') when full_name  not rlike '分行' then   concat(full_name,'分行')
    else full_name  end
    where LEVEL = 1 and full_name not rlike '卡中心' and full_name  rlike '工商银行';

#### **3.了解下mysql中的 rlike 与 regexp**  ####

> **like操作符：** 
> 
> “\_”仅表示一个字符，“%”表示一个或多个字符或空格
> 
> select \* from XX\_department where name like '工商银行'；**等价于**
> 
> select \* from XX\_department where name ='工商银行';
> 
> select \* from XX\_department where name like '%**信息/%**%' ESCAPE '/';
> 
> 匹配含【信息%】的数据， /将%转义为普通字符串
> 
> **rlike操作符： 不同于like ，like像是匹配 字段/列 值的全称，rlike 是 字段值 只要包含参数** 
> 
> select \* from xx\_test where name like '工商银行%'；不匹配 嗨工商银行
> 
> select \* from xx\_test where name rlike '工商银行'； 只要带 工商银行 就匹配
> 
> select \* from xx\_test where name **not** rlike '工商银行'；查找**不**含工商银行的数据
> 
> select \* from xx\_test where name rlike '^工商'; 查找/匹配 工商 开头的字段值  
> select \* from xx\_test where name **not** rlike '^(工商|中国)'; 查找/匹配 **不是** 工商或中国 开头的字段值  
> select \* from xx\_test where name **not** rlike '分行$'; 查询所有**不**以“分行”结尾的信息
> 
> select \* from xx\_test where name rlike '卡中心**|**分公司'; 查询 name字段值 有 卡中心**或** 分公司 的 信息 注意：1. or 用的 | 2.name 含"|" 也会被匹配上哦！
> 
> 也可以用 内置函数检索(locate,position,instr)
> 
>     SELECT * from app_info where INSTR(`appName`, '%') > 0;
>      
>     SELECT * from app_info where LOCATE('%', `appName`) > 0;
>      
>     SELECT * from app_info where POSITION( '%' IN `appName`) > 0;
> 
> **规则：**
> 
>     . : 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式
>     
>     * ： 匹配0个或多个前一个得到的字符  zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}
>     + : 匹配前面的子表达式一次或多次  'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
>     
>     [] : 匹配任意一个[]内的字符，[ab]*可匹配空串、a、b、或者由任意个a和b组成的字符串。
>     
>     ^ : 匹配开头，如^s匹配以s或者S开头的字符串。
>     
>     $ : 匹配结尾，如s$匹配以s结尾的字符串。
>     
>     {n} : 匹配前一个字符反复n次。
>     {n,m}	: 最少匹配 n 次且最多匹配 m 次。
> 
> [MySQL 正则表达式 | 菜鸟教程 (runoob.com)][MySQL _ _ _ _runoob.com]—— 详细参考  
> **regexp** 使用与 **rlike** 一模一样，两者可互换使用。

#### 4.在MySQL中，like、rlike和regexp都不区分大小写 ####

select \* from user\_basic\_info WHERE BINARY userName rlike '\[a-z\]'

报错原因：  
![d487f43269174043a0248395947e20b7.png][]

在 MySQL 中，字符集（character set）和排序规则（collation）是相关联的。当你在创建表或列时指定了某个字符集，就需要选择相应的排序规则。

UTF-8 是一种常用的字符集，它支持存储多种语言的字符。MySQL 提供了多种排序规则，例如 `utf8mb4_general_ci` 和 `utf8mb4_unicode_ci`，这些排序规则适用于 UTF-8 字符集。

然而，在 MySQL 中，如果你想要使用二进制字符集（`binary`），就不能与 `utf8mb3_general_ci` 这样的排序规则一起使用。因为 `utf8mb3_general_ci` 排序规则是针对 UTF-8 字符集的，而 `binary` 字符集是二进制的，它没有对字符进行排序的需求。

![8cb49686f2bc42ea8465c6fef52765cc.png][]

[MySQL模糊查询用法大全（正则、通配符、内置函数等） - 知乎 (zhihu.com)][MySQL_ - _ _zhihu.com]

### 7. 如果字段为空取另一个字段 ###

SELECT IFNULL(NULL, 'default');
    -- 返回 'default'
    SELECT IFNULL('value', 'default');
    -- 返回 'value'

但是因为某些原因，字段A是空字符串，而不是null， ep：A为" " 要获取B字段

SELECT COALESCE(NULLIF(A, ''), B) AS result FROM user_info;
    # SELECT NULLIF('value', 'value');
    -- 返回 NULL
    # SELECT NULLIF('value', 'other');
    -- 返回 'value'
    注意：NULLIF中，两个参数的值相等，则返回 NULL；否则返回第一个参数的值
    COALESCE 中多个参数，第一个非null的参数返回，参数都为null,结果为null

### 8.mysl截取函数、去空格对比 ###

> left('1234567890',4): 1234  
> right('1234567890',4): 7890  
> substring('1234567890',4): 4567890  
> substring('1234567890',-4): 7890  
> substring('1234567890',4,2): 45  
> substring\_index('123.456.7890','.',1): 123  
> substring\_index('123.456.7890','.',-1): 7890  
> select trim(' 3456789 ') from dual; \# "3456789"  
> select ltrim(' 3456789 ') from dual; \# "3456789 "  
> select rtrim(' 3456789 ') from dual; \# " 3456789"

### 9.查询结果分组显示后，还要汇总 ###

#### 9.1单个字段汇总显示 ####

>  **改前**： select name,count(1) as num from test\_log group by name ;

> **改后**：select coalesce(name,'汇总') as name ,count(1) num from test\_log group by name **with ROLLUP;**  \# 查询结果中生成 包含**子总计和总计行**的汇总数据
> 
> 因为只有一个字段name 为 **分组的列名，所以这里子总计与总计行 默认相同。**

#### 9.2多个字段汇总显示 暂时不行 ####

此时用with rollup 会**多出** 【小白 null 15 ,小黑 null 1 ，汇总 null 16】,但是有些场景我们不需要子汇总，只需要最后一行的总计行汇总。

> **改前：**  select coalesce(name,'汇总') as name, product,sum(1) num from test\_log group by name,product;

> **改后： 不行原因 参考**[sql--基础篇 - 知乎 (zhihu.com)][sql--_ - _ _zhihu.com]

### 10.查询表 A 中字段 a1 含有表 B 中字段 b1 的数据 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>A——a1</td> 
   <td>B ——b1</td> 
  </tr> 
  <tr> 
   <td>你好陌生人</td> 
   <td>陌生</td> 
  </tr> 
  <tr> 
   <td>是我说过</td> 
   <td>说过</td> 
  </tr> 
 </tbody> 
</table>

1.子查询
    SELECT A.*
    FROM A
    WHERE EXISTS (
        SELECT 1
        FROM B
        WHERE A.a1 LIKE CONCAT('%', B.b1, '%')
    );
    2.使用 inner join
    SELECT A.*
    FROM A
    INNER JOIN B ON A.a1 LIKE CONCAT('%', B.b1, '%');

### 11.突然业务说查询部门即对应的所有上级部门信息，直到顶层 ###

适用于MySQL版本  
![54c055c914ed42a081a845de066b325c.png][]

-- 声明公共表达式
    WITH  recursive  parent(parent_department_id,department_id,name,level) AS (
      SELECT parent_department_id,department_id,name,level from user_department where full_name = '深圳大道东' -- 初始条件
    UNION ALL
      SELECT ep.parent_department_id,ep.department_id, ep.name, ep.level
      FROM user_department ep
      INNER JOIN parent e ON e.parent_department_id = ep.department_id
    )
    SELECT * FROM parent;
    
    
    -- 解释：
    1.WITH RECURSIVE parent (parent_department_id, department_id, NAME, LEVEL) AS (...)：这里定义了一个递归公共表达式，命名为 parent，并指定了它的列名为 parent_department_id、department_id、NAME 和 LEVEL。
    2.初始查询部分：SELECT parent_department_id,department_id,name,level from user_department where full_name = '深圳大道东' 
    3.递归查询部分：
        UNION ALL SELECT ep.parent_department_id, ep.department_id, ep.NAME, ep.LEVEL FROM user_department ep INNER JOIN parent e ON e.parent_department_id = ep.department_id：这是递归查询的主要部分。
        UNION ALL：这个关键字用于将递归部分连接到初始查询结果。
        SELECT ep.parent_department_id, ep.department_id, ep.NAME, ep.LEVEL FROM user_department ep INNER JOIN parent e ON e.parent_department_id = ep.department_id：这个子查询选择了连接到上一级部门的所有记录，并将它们添加到结果集中。连接条件是当前部门的 parent_department_id 字段等于上一级部门的 department_id 字段。
    4.递归结束条件：递归部分未找到匹配的记录。

[63cf0719b51d470299927f9ce521140d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/12b966a0674c4530824363c6f20fc9c9.png
[23a6c759b8c840648ef7c93ac9bf96a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/4ab597584bff4d5dbf2918f61bd205bb.png
[f1368d6ae5df49e2978eb1415499528b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/0b472b4f8c8d4297b9c01e84c9a053bf.png
[MySQL _ _ _ _runoob.com]: https://www.runoob.com/mysql/mysql-regexp.html
[d487f43269174043a0248395947e20b7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/011837a49d0340eb9b5bd2fe21bd8134.png
[8cb49686f2bc42ea8465c6fef52765cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/892da576e03d4944984c8f2228a69327.png
[MySQL_ - _ _zhihu.com]: https://zhuanlan.zhihu.com/p/160808567
[sql--_ - _ _zhihu.com]: https://zhuanlan.zhihu.com/p/556547696
[54c055c914ed42a081a845de066b325c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/2f81f708726c4edd9d35bb55e5254109.png