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

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

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

 

SELECT    a.*,    b.* FROM    A    LEFT JOIN b ON a.a2 = b.a2   

利用on 关联，a表中a2全部显示，b表中有的显示，没有则为null

但是特殊情况下，业务老师说我想显示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);

a	b	c
10	20	30

update test set a=100;

a	b	c
100	20	120

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一对一写法

<!--基础写法展示一对一时候，用association  blog类中包含另一个类author作为属性一份子-->    
<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分行

name/名称	level/级别	full_name/全称
工商银行	0	工商银行
北京	1	工商银行北京
天津	1	工商银行天津
上海	1	工商银行上海分公司
信用卡中心	1	工商银行信用卡中心

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)—— 详细参考
regexp 使用与 rlike 一模一样，两者可互换使用。

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

select * from user_basic_info WHERE BINARY userName  rlike '[a-z]'  

报错原因：

在 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 字符集是二进制的，它没有对字符进行排序的需求。

 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 ;

name	num
小白	26
小黑	1

改后：select coalesce(name,'汇总') as name ,count(1) num from test_log group by name with ROLLUP;   # 查询结果中生成 包含子总计和总计行的汇总数据

因为只有一个字段name 为 分组的列名，所以这里子总计与总计行 默认相同。

name	num
小白	26
小黑	1
汇总	27

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;

name	bhv	num
小白	纪要	15
小黑	产品	1

改后： 不行原因 参考sql--基础篇 - 知乎 (zhihu.com)

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

A——a1	B ——b1
你好陌生人	陌生
是我说过	说过

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版本

-- 声明公共表达式
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.递归结束条件：递归部分未找到匹配的记录。