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);
update test set a=100;
a b c 10 20 30
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.递归结束条件:递归部分未找到匹配的记录。
12.关于with表达式多种用法
第一种:字段值拆分,一条变多条 利用递归实现
name | code | company |
大白;小白 | 200 | 双白公司 |
黑;大黑;中黑;小黑 | 126 | 双黑公司 |
单着 | 655 | 单非公司 |
想要结果形式 将name按照;号拆分成多条
大白 | 200 | 双白公司 |
小白 | 200 | 双百公司 |
黑 | 126 | 双黑公司 |
大黑 | 126 | 双黑公司 |
中黑 | 126 | 双黑公司 |
小黑 | 126 | 双黑公司 |
单着 | 655 | 单非公司 |
具体sql实现 思路:下列name2只是辅助,获取值还是name1
with recursive split_cte as (
select
SUBSTRING_INDEX(name, ';', 1) as name1, -- 左数第一个;前\左侧 数据如:黑
SUBSTRING_INDEX(name, ';', CHAR_LENGTH(replace(name, ';', ''))- CHAR_LENGTH(name)) as name2, -- 右数最后一个;后\右侧 数据如:大黑;中黑;小黑
code,
company
from
test
union all
select
SUBSTRING_INDEX(name2, ';', 1) as name1, -- 针对拆分的右侧数据继续拆分
SUBSTRING_INDEX(name2, ';', CHAR_LENGTH(replace(name2, ';', ''))- CHAR_LENGTH(name2)) as name2,
code,
company
from
split_cte
where
name2 <> '' -- 递归早结束条件:name2是空的无法拆分
)
select
name1,
code,
company
from
split_cte
查看substring_index 函数效果如下:
select substring_index("123;456",";",1) ; -- 123 正数 左侧起,往左数
select substring_index("123;456",";",-1); -- 456 负数右侧起,往右数
select substring_index("123;456",";",2) ; -- 123;456 超过取原数
select SUBSTRING_INDEX("123;456", ';', CHAR_LENGTH(replace("123;456", ';', ''))- CHAR_LENGTH("123;456")); -- 456
select SUBSTRING_INDEX("456", ';', CHAR_LENGTH(replace("456", ';', ''))- CHAR_LENGTH("456")) -- 空的
select substring_index("123;456",";",0) ; -- 空的 0则不取值
2.上述只是查询,实际业务是给你一个excel表格上就是上述三个字段,将excel中数据导入表中,但是要拆分如name字段,怎么解决?
第一步就是建个临时表,将excel数据按照字段导入表中
第二部将1中的sql变更下加个insert
insert into test2 (name,code,company) select name1,code,company from
(
with recursive split_cte as (
select
SUBSTRING_INDEX(name, ';', 1) as name1, -- 左数第一个;前\左侧 数据如:黑
SUBSTRING_INDEX(name, ';', CHAR_LENGTH(replace(name, ';', ''))- CHAR_LENGTH(name)) as name2, -- 右数最后一个;后\右侧 数据如:大黑;中黑;小黑
code,
company
from
test
union all
select
SUBSTRING_INDEX(name2, ';', 1) as name1, -- 针对拆分的右侧数据继续拆分
SUBSTRING_INDEX(name2, ';', CHAR_LENGTH(replace(name2, ';', ''))- CHAR_LENGTH(name2)) as name2,
code,
company
from
split_cte
where
name2 <> '' -- 递归早结束条件:name2是空的无法拆分
)
select name1,code,company from split_cte
) tmp
3.有天想测试,发现没测试数据,自己造数据[除了工具就是sql实现]
insert relation_bak (tzjl, jjdm,jjmc)
with recursive tmp (a,b,c) as
(
select
0 ,
'2021-04-20' ,
'999999'
union all
select
a + 1,
b - interval ROUND(RAND() * 1000) day,
concat('test', a)
from
tmp where a<20
) table tmp;
注意:tmp是 mysql递归时自定义的临时表名,relation_bak 是要插入的表名
table tmp 此时效果等同于 select * from tmp
# 但是relation_bak (tzjl, jjdm,jjmc) 3个字段与 tmp中3个字段顺序一致,采用table tmp简化。
4.批量更新 [需要用到递归时]
WITH recursive tmp (a, b, c) AS
(SELECT
1,
1,
'2021-04-20'
UNION ALL
SELECT
a + 2,
100,
DATE_SUB(
CURRENT_DATE(),
INTERVAL ROUND(RAND() * 1000, 0) DAY
)
FROM
tmp
WHERE a < 100)
UPDATE
tmp AS a,
y1 AS b
SET
b.r1 = a.b
WHERE a.a = b.id;
5.删除数据某列特征 或 条件
准备套件:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'Finance'),
(3, 'Charlie', 'HR'),
(4, 'David', 'IT'),
(5, 'Eve', 'Finance');
要求: 删除所有在HR
部门的员工。1.
WITH target_employees AS (
SELECT id
FROM employees
WHERE department = 'HR'
)
DELETE FROM employees
WHERE id IN (SELECT id FROM target_employees);
2.DELETE FROM employees
WHERE department = 'HR';注意:使用 CTE 的主要好处是,它可以让复杂的查询变得更清晰,尤其是在需要多步处理时
6.with 和 with 一起使用
结果:
SELECT * FROM
(
WITH tmp1 (a, b, c) AS
(
VALUES
ROW (1, 2, 3),
ROW (3, 4, 5),
ROW (6, 7, 8)
) SELECT * FROM
(
WITH tmp2 (d, e, f) AS (
VALUES
ROW (100, 200, 300),
ROW (400, 500, 600)
) TABLE tmp2
) X
JOIN tmp1 Y
) Z ORDER BY a;
WITH tmp1 (a, b, c) AS (
VALUES
ROW (1, 2, 3),
ROW (3, 4, 5),
ROW (6, 7, 8)
) ,
tmp2 (d, e, f) AS (
VALUES
ROW (100, 200, 300),
ROW (400, 500, 600)
)
select * from tmp1,tmp2
4.生产日期序列
WITH recursive seq_date (log_date) as (
SELECT
'2020-01-01'
UNION
ALL
SELECT
log_date + INTERVAL 1 DAY
FROM
seq_date
WHERE log_date + INTERVAL 1 DAY < '2020-02-01'
) table seq_date