后端java工程师经验之谈，工作7年，mysql使用心得

mysql

1.创建变量

如果想获取查询语句中的一个字段值可以用select给变量赋值,如下：
select @num=字段名 from 表名 where ……
mysql中变量不用事前申明，在用的时候直接用”@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用”=”或”:=”，但是使用select时必须用”:=赋值”

set @var=0;
select *,@var:=date_task_id  from date_details_task where task_num=1 and task_type=5 and finish_num !=1 and DATE_FORMAT(create_time,'%Y-%m-%d') in ('2020-11-21')

2.创建存储过程

2.1：WHILE循环

-- 如果有存储过程，删除该存储过程
drop PROCEDURE  if EXISTS test_two;
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE test_two ()
BEGIN
DECLARE i INT DEFAULT 10;-- DECLARE 声明只能在begin...end 和存储过程语句中，否则创建变量直接用setWHILE i < 100 DOSET i = i + 1;select i;END WHILE;
END;
//
-- 调用存储过程
CALL test_two ();
DELIMITER ;创建存储过程 批量插入数据库-- 如果有存储过程，删除该存储过程
drop PROCEDURE  if EXISTS test_two2;
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE test_two2 ()
BEGIN
DECLARE i INT DEFAULT 1;-- DECLARE 声明只能在begin...end 和存储过程语句中，否则创建变量直接用setWHILE i <=90 DOINSERT INTO `yushu_jdb`.`user_vip_module_power` ( `vip_level`, `module`, `num_limit`, `create_time`, `update_time`) VALUES ( 1, 1, 1, NOW(), NOW());set i=i+1;END WHILE;
END ;
//-- 调用存储过程
CALL test_two2();
DELIMITER ;TRUNCATE table user_vip_module_power

2.2：repeat循环

-- 第三种 repeat 循环:直到条件满足退出循环/*repeat 循环语法repeat循环体until 条件 end repeat;*/drop procedure if exists sum55;#删除存储过程DELIMITER //create procedure sum55(a int)begindeclare sum int default 0;declare i int default 1;repeat -- 循环开始set sum=sum+i;set i=i+1;until i>a end repeat; -- 循环结束select sum; -- 输出结果end;//
call sum55(100);-- 执行存储过程
DELIMITER ;

2.3：loop循环

-- 第二种 loop 循环/*loop 循环语法：loop_name:loopif 条件 THEN -- 满足条件时离开循环leave loop_name;  -- 和 break 差不多都是结束循环end if；end loop;*/
-- 删除存储过程
drop procedure if exists  sums;
DELIMITER //
create procedure sums(a int)
begindeclare sum int default 0;declare i int default 1;loop_name:loop -- 循环开始if i>a then leave loop_name;  -- 判断条件成立则结束循环  好比java中的 breakend if;set sum=sum+i;set i=i+1;end loop;  -- 循环结束select sum; -- 输出结果
end;
//-- 执行存储过程
call sums(100);
DELIMITER ;

2.4：存储过程，游标

delimiter //  # 定义//为一句sql的结束标志，取消;的所代表的意义
drop procedure if exists test;  # 如果存在名字为test的procedure则删除
create procedure test()  # 创建（创建函数使用的关键字为function 函数名()）
begindeclare old_pro varchar(30);  # 声明变量declare temp_id int;declare flag int default 0;# 这是重点，定义一个游标来记录sql查询的结果(此处的知识点还有SQL的模糊查询，见补充)declare s_list cursor for select id, province from temp_table where like "%省"; # 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1declare continue handler for not found set flag=1;open s_list;  # 打开游标# 将游标中的值赋给定义好的变量，实现for循环的要点fetch s_list into temp_id, old_pro;while flag <> 1 do# sql提供了字符串的切分，有left、right、substring、substring_index# 在T-SQL中，局部变量必须以@作为前缀，声明方式set，select还有点差别set @temp_s = substring_index(old_pro, "省", 1);# 根据id的唯一性，利用当前查询到的记录中的字段值来实现更新update temp_table set province=@temp_s where id=temp_id;# 游标往后移（此处的游标是不是让你想起了C里面的指针）fetch s_list into temp_id, old_pro;end while;#select * from temp_table;close s_list;  # 关闭游标
end
//
delimiter ;  # 重新定义;为一句sql的结束标志，取消//的所代表的意义
call test(); # 调用/**
SQL具有四种匹配模式
1、%：表示零个或多个字符；
2、_：表示任意单个字符；
3、[]：表示括号内所列字符中的任意一个（类似正则）；
4、[^]：取反（类似正则）；
注：若匹配中包含通配符则使用“[]”将特殊字符括起来即可（相当于转义）
*/

2.5：存储过程，有输入参数和输出参数

-- phone 使用like 是为了兼容教师角色，pwd是为了兼容新创建的账号,start_time不为空是为了...
select * from t_user_info  where  phone like '13907966696%' and (pwd is  null or pwd ='') and start_time is not null;-- 如果有存储过程，删除该存储过程
drop PROCEDURE  if EXISTS update_temp_account_user;
-- 修改语句结束符为// 
DELIMITER //
-- 创建存储过程,mobilePhone、startTime、endTime为输入入参，limitTime为输出参数
CREATE PROCEDURE update_temp_account_user(IN mobilePhone VARCHAR(50),IN startTime VARCHAR(50), IN endTime VARCHAR(50), OUT limitTime VARCHAR(100))
BEGINSET limitTime = CONCAT('{"day":"', startTime, ',', endTime, '"}');update t_user_info  as t1 ,  (select user_id from t_user_info  where  phone like  CONCAT('',mobilePhone,'%') and (pwd is  null or pwd ='') and start_time is not null)  as t2set start_time =startTime,end_time=endTime,limit_time=limitTime where t1.user_id=t2.user_id;select * from t_user_info  where  phone like  CONCAT('',mobilePhone,'%') and (pwd is  null or pwd ='') and start_time is not null;
END;
//-- 改回来，修改语句结束符为; 
DELIMITER ;-- 调用存储过程就两行
-- 定义输出参数
SET @output_param = '';--   手机号         临时账号开始时间       临时账号结束时间       输出参数
CALL update_temp_account_user('13907966696','2023-05-04 09:00:00', '2023-05-04 12:00:00', @output_param);SELECT @output_param;

DELIMITER $$
CREATE PROCEDURE `test_edu_deyuke`()
begindeclare _activity_id varchar(100);  # 声明变量declare _is_need_institution int(11);  # 声明变量declare flag int default 0;# 这是重点，定义一个游标来记录sql查询的结果(此处的知识点还有SQL的模糊查询，见补充)
DECLARE s_list CURSOR FOR SELECT activity_id, IF(institution_id is null or institution_id='',0,1) as is_need_institution FROM t_activity_info where act_status=13 and activity_id not in (select activity_id from t_activity_progress GROUP BY activity_id)  GROUP BY activity_id;# 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1declare continue handler for not found set flag=1;# 打开游标open s_list; # 将游标中的值赋给定义好的变量，实现for循环的要点fetch s_list into _activity_id, _is_need_institution;while flag <> 1 do# 0不需要出行机构IF _is_need_institution=0 THENINSERT INTO t_activity_progress (snow_flake_id,activity_id,progress_name,is_ok,activity_node,create_time,update_time )VALUES('1',_activity_id,'学校选课',1,1,NOW(),NOW()),('1',_activity_id,'提交出行方案',1,3,NOW(),NOW()),('1',_activity_id,'活动审核备案',1,5,NOW(),NOW()),('1',_activity_id,'报名网签',1,6,NOW(),NOW()),('1',_activity_id,'开展活动',1,7,NOW(),NOW()),('1',_activity_id,'自我陈述与评价',1,8,NOW(),NOW()),('1',_activity_id,'点评与评价',1,9,NOW(),NOW()),('1',_activity_id,'形成个人档案',0,10,NOW(),NOW()),('1',_activity_id,'对接综评获得学分',0,11,NOW(),NOW());ELSEIF _is_need_institution=1 THENINSERT INTO t_activity_progress (snow_flake_id,activity_id,progress_name,is_ok,activity_node,create_time,update_time )VALUES('1',_activity_id,'学校选课',1,1,NOW(),NOW()),('1',_activity_id,'机构提交出行方案',1,2,NOW(),NOW()),('1',_activity_id,'审核出行方案',1,4,NOW(),NOW()),('1',_activity_id,'活动审核备案',1,5,NOW(),NOW()),('1',_activity_id,'报名网签',1,6,NOW(),NOW()),('1',_activity_id,'开展活动',1,7,NOW(),NOW()),('1',_activity_id,'自我陈述与评价',1,8,NOW(),NOW()),('1',_activity_id,'点评与评价',1,9,NOW(),NOW()),('1',_activity_id,'形成个人档案',0,10,NOW(),NOW()),('1',_activity_id,'对接综评获得学分',0,11,NOW(),NOW());END IF;# 游标往后移fetch s_list into _activity_id, _is_need_institution;end while;close s_list;  # 关闭游标
END $$
DELIMITER ;

3.三种注释写法

#MySql--三种注释写法
#需要特别注意  --   这种注释后面要加一个空格#  
/* */
-- 空格

4.case when then else end 写法

#CASE 子句可以用于任何可以使用表达式的地方。【when后面无逗号】
#如果省略了ELSE子句而且没有匹配的条件,结果为null,可能会查出错误的结果。使用 end case 替代 end 来终止case
#写法一：只适合单值匹配，不适合多值匹配 即不能使用or 如： when (0 0r 1) then .... 错误写法不
# 因为 0 or 1的结果是1 ，就会变成case type when 1 then .... 如果type选项中没有1 就会一直执行else
CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]
END CASE
#写法二：适合单值匹配，也适合多值匹配
CASEWHEN search_condition THEN statement_list[WHEN search_condition THEN statement_list] ...[ELSE statement_list]
END CASE

/*先写一个子查询，目的是为了找出date_task_id相同的4条数据中，其中两条数据的之和子查询使用到了两种case when then else end 写法，when不能与or连用，但可以与and 连用为了找出第一个数据与(其中两条数据的之和)的差值，在外面又嵌套了一个查询
*/
select *,(flowClick-addCartAndGoodsUp) as '差值' from (select date_task_id,sum(case when task_type=2 then task_num  else 0 end ) as flowClick,sum(case task_type when 3 then task_num when 4 then task_num else 0 end ) as addCartAndGoodsUp,sum(case when task_type=5 then task_num  else 0 end) as shopUpfrom date_details_task where  date_task_id=155128
) as r (case xxx when 0 then '' when 1 then '' when 2 then '' when 3 then '' else '未知' end ) as levelStr
#由于分组不支持中文别名，只支持英文别名,如果需要显示中文别名，需要再外面多加一层
select a.num as '数量', a.trainType as '快车类型' from 
(select count(id) as num,(case when type=0 or type=7 then '快车-普通点击' when type=1 or type=10 then '快车-加入购物车'when type=2 or type=11 then '快车-加车并提单' when type=3 or type=12 then '快车-关注商品' when type=4 or type=13 then '快车-关注店铺' when type=5 or type=8 then '快车-展现提升任务' when type=6 or type=9  then '快车-点击提升任务'
else '' end) as trainType
from aaaa GROUP BY trainType 
) a;

5.if相关方法

IF(expr1,expr2,expr3)
#如果if表达式1为真(表达式1不等于0和表达式1不等于null),返回表达式2，否则表达式3
select IF(2 >3 or 3>2,1,0) from dual;IFNULL(expr1,expr2)
#如果表达式1不为null,返回表达式1否则返回表达式2,类似于三元运算符NULLIF(expr1,expr2)
#如果表达式1=表达式2返回null,否则返回表达式1
#类似于CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
#返回类型与第一个参数一致

6.if语句

#以if开头，以 end if 结束
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF;
#semicolon 分号，delimiter分隔符# 示例
DELIMITER $$CREATE PROCEDURE GetCustomerLevel(in  p_customerNumber int(11), out p_customerLevel  varchar(10))
BEGINDECLARE creditlim double;SELECT creditlimit INTO creditlimFROM customersWHERE customerNumber = p_customerNumber;IF creditlim > 50000 THENSET p_customerLevel = 'PLATINUM';ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THENSET p_customerLevel = 'GOLD';ELSEIF creditlim < 10000 THENSET p_customerLevel = 'SILVER';END IF;END $$DELIMITER ;

7.创建方法

CREATE[DEFINER = user]PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body
proc_parameter:[ IN | OUT | INOUT ] param_name typeCREATE[DEFINER = user]FUNCTION sp_name ([func_parameter[,...]])RETURNS type[characteristic ...] routine_bodyfunc_parameter:param_name typetype:Any valid MySQL data typecharacteristic: {COMMENT 'string'| LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }
}routine_body:Valid SQL routine statement

7.1示例

#将默认的分隔符;改为//
DELIMITER //
#RETURNS关键字只能在方法中使用,这是硬性规定的，它声明了返回值类型,方法体必须有RETURN关键字
#如果返回类型不一致，会强制转换
CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20)BEGINDECLARE s VARCHAR(20);IF n > m THEN SET s = '>';ELSEIF n = m THEN SET s = '=';ELSE SET s = '<';END IF;SET s = CONCAT(n, ' ', s, ' ', m);RETURN s;END //
#分隔符改回来
DELIMITER ;
#像调用其它sql语句一样调用自己写方法
select SimpleCompare(1,2);

8.删除重复数据只保留最小id

#先查出要删除的数据
select *  from  qrcode WHERE
id NOT IN (SELECT min(id) FROM qrcode GROUP BY type HAVING count(id) >=1)#将要查询的数据使用group_concat函数用串联起来
select GROUP_CONCAT(id) from qrcode WHERE 
id  IN (SELECT min(id) FROM qrcode GROUP BY type HAVING count(id) >=1) order by type-- 删除多余的数据
DELETE from t_stu_eva_norm where id not in (
-- 得再包装一层
select * from (select min(id)  from t_stu_eva_norm where  is_delete=0  group by student_id,act_id,act_course_id,norm_id) as a
)-- 删除多余的数据
DELETE from t_trip_plan_new where id not in (
-- 得再包装一层
select * from (select min(id)  from t_trip_plan_new GROUP BY activity_id) as a
)

9.INSERT INTO ... SELECT标准的SQL语法

MySQL服务器不支持SELECT ... INTO TABLE 的Sybase SQL扩展。
但是支持SELECT ... INTO 变量相反，MySQL Server支持 INSERT INTO ... SELECT标准的SQL语法，这基本上是相同的INSERT INTO table1 (fld_id) 
SELECT table2.fld_order_id FROM table2 WHERE table2.fld_order_id > 100;也可以使用   
CREATE TABLE ... SELECT
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

10.SQL查询每个用户首次购买商品

SELECT g.name,g.product FROM goods g 
INNER JOIN (SELECT MIN(time) AS firstTime FROM  goods GROUP BY name ) tmp 
ON tmp.firstTime=g.time;

11.update

11.1update多表连接更新

#更新两表联查中满足条件的值
UPDATE items,#items表(SELECT id, retail / wholesale AS markup, quantity FROM items)AS discounted #查询部分作为第二张表SET items.retail = items.retail * 0.9WHERE discounted.markup >= 1.3AND discounted.quantity < 100AND items.id = discounted.id;
#示例二：一定要起别名
update (select id from task where end_date is null) as t,task t1 
set t1.end_date =STR_TO_DATE(DATE_FORMAT(t1.start_date,'%Y-%m-%d'),'%Y-%m-%d') 
where t.id=t1.id

11.2update … where exists

#使用update ... where exists ，exists子查询要多套一层
#否则会出现 You can't specify target table 'aaaa' for update in FROM clause
#错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)
#当需要使用用到多张表的条件，但是查询的结果字段又只包含在某张表里的时候可以使用exists语句
update aaaa aa set aa.json_str ='{}'
where exists (select * from (select t.id from aaaa t where t.id =1) a where a.id=aa.id)

12.内连接inner join 和左连接left join

12.1 内连接

#在两个表的内部联接中，第一个表的每一行与第二个表的每一行合并（联接）。
#假设第一张表中有n1行，第二张表中有n2行，则INNER JOIN产生n1 × n2行的所有组合-称为笛卡尔乘积或叉积。
#不加where条件或on条件就会产生笛卡尔积
#inner join...on 会将两表中都有数据筛选出来，只存在在一张表中的数据会过滤掉
#以下等效SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;SELECT * FROM t1 JOIN t2 ON t1.id = t2.id; -- 默认的 JOIN 是 INNER JOINSELECT * FROM t1 CROSS JOIN t2 ON t1.id = t2.id;  -- join/cross join/inner join 同义词-- 你可以使用 using 关键词，如果关联的条件是相同名称：ON t1.id = t2.id 可以写成 USING (id);
SELECT * FROM t1 INNER JOIN t2 USING (id);
SELECT * FROM t1 INNER JOIN t2 WHERE t1.id = t2.id;  -- 使用where替代on
# inner join 简写      SELECT * FROM t1, t2 WHERE t1.id = t2.id;-- 使用逗号操作符+where

12.2左连接和右连接

#INNER JOIN (ON或USING）一起产生在两个表中都找到的行。
#OUTER JOIN可以产生在一个表中存在但不在另一个表中的行。
#OUTER JOIN有两种：#LEFT JOIN产生左表中的所有行，但可能不在右表中；#RIGHT JOIN产生的行位于右表中的所有行，但可能不在左侧表中。
#在中LEFT JOIN，当左表中的行与右表不匹配时，仍会选择该行，但会与右表中所有NULL的“假”记录组合在一起。
SELECT t1.id, t1.desc FROM t1 LEFT JOIN t2 USING (id) WHERE t2.id IS NULL;
# 报错 where 语句不能使用在outer join中
SELECT * FROM t1 LEFT JOIN t2 WHERE t1.id = t2.id; -- 报错 where 语句不能使用在outer join中

13.等值连接和非等值连接

MySQL 中的连接（Join）操作用于将两个或多个表中的数据组合起来进行查询。根据连接的方式，连接操作可以分为等值连接和非等值连接。
等值连接
等值连接（Equi Join）指连接操作中使用相等关系（=）进行连接的方式，即将两个表中具有相同键值的记录组合在一起。等值连接是连接操作中最常用的方式之一。
在 MySQL 中，等值连接可以使用 JOIN 或者 INNER JOIN 关键字来实现。例如，下面的查询将 orders 表和 customers 表按照 customer_id 进行等值连接：
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;非等值连接
非等值连接（Non-equijoin）指连接操作中使用非相等关系（<、>、<=、>=、<> 等）进行连接的方式，即将两个表中键值之间的某种关系组合在一起进行查询。非等值连接一般比等值连接更加复杂，也更容易出现问题。在 MySQL 中，非等值连接可以使用 LEFT JOIN 或者 RIGHT JOIN 关键字来实现。例如，下面的查询将 orders 表和 products 表按照 product_id 和 order_date 进行非等值连接：SELECT orders.order_id, products.product_name
FROM orders
LEFT JOIN products
ON orders.product_id = products.product_id AND orders.order_date >= products.start_date;在这个查询中，使用了 LEFT JOIN 关键字将 orders 表作为主表，然后使用 ON 子句指定了连接条件，其中包含一个非等值条件 orders.order_date >= products.start_date。这个查询将返回所有满足条件的记录，同时还包括那些在 orders 表中有记录但在 products 表中没有记录的记录。当两个表之间没有相同的键值进行连接时，就需要使用非等值连接来组合数据。以下是一些示例：
使用 BETWEEN 子句进行非等值连接
假设有两个表 orders 和 discounts，需要将所有满足以下条件的记录组合在一起：订单的金额在打折表中的折扣金额范围内。使用 BETWEEN 子句进行非等值连接
假设有两个表 orders 和 discounts，需要将所有满足以下条件的记录组合在一起：订单的金额在打折表中的折扣金额范围内。SELECT o.order_id, o.order_total, d.discount_rate
FROM orders o
JOIN discounts d
ON o.order_total BETWEEN d.discount_min_amount AND d.discount_max_amount;在这个查询中，使用 BETWEEN 子句来比较 orders 表中的 order_total 字段和 discounts 表中的 discount_min_amount 和 discount_max_amount 字段的值，以确定哪些订单可以享受相应的折扣。使用子查询进行非等值连接
假设有两个表 orders 和 customers，需要找到在 2022 年之前首次下单的所有客户信息。SELECT c.customer_id, c.customer_name, MIN(o.order_date) AS first_order_date
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE (SELECT MIN(order_date) FROM orders WHERE customer_id = c.customer_id) < '2022-01-01'
GROUP BY c.customer_id;使用条件表达式进行非等值连接
假设有两个表 products 和 sales，需要找到所有销售额超过 10000 的产品名称和销售额。SELECT p.product_name, s.sales_total
FROM products p
JOIN sales s
ON p.product_id = s.product_id AND s.sales_total > 10000;在这个查询中，使用条件表达式 s.sales_total > 10000 来筛选符合条件的销售记录，并使用非等值连接将这些记录与产品表中的相应产品进行组合。

看到【有】就联想到inner join ,看到【没有】就联想到left join

你的理解方向是对的，但为了更准确地应用 SQL 连接类型，建议你不要仅仅依赖“看到有就联想到 INNER JOIN，看到没有就联想到 LEFT JOIN”这种简单的规则。虽然这种思维方式可以帮助你在某些情况下快速做出选择，但它可能会忽略一些重要的细节和更复杂的场景。

1. 更全面的理解

1.1 INNER JOIN：关注匹配的记录

• 适用场景：当你只关心两个表中 都有匹配记录的行 时，使用 INNER JOIN 是最合适的。

• 例子：

  • 找出所有 有登录记录的用户。
  • 找出所有 有订单的客户。
  • 找出所有 有评论的商品。

-- 找出有登录记录的用户
SELECT u.user_id, u.username
FROM users u
INNER JOIN logs l ON u.user_id = l.user_id;

在这个例子中，INNER JOIN 只返回那些在 logs 表中有登录记录的用户，而不会返回那些没有登录记录的用户。

1.2 LEFT JOIN：保留左表的所有记录

• 适用场景：当你希望 保留左表中的所有记录，即使右表中没有匹配的记录时，使用 LEFT JOIN 是最合适的。你可以通过 WHERE 子句进一步过滤出那些在右表中没有匹配记录的行。

• 例子：

  • 找出 近 30 天内没有登录记录的用户。
  • 找出 没有订单的客户。
  • 找出 没有评论的商品。

-- 找出近 30 天内没有登录记录的用户
SELECT u.user_id, u.username
FROM users u
LEFT JOIN logs l ON u.user_id = l.user_id AND l.login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE l.user_id IS NULL;

在这个例子中，LEFT JOIN 返回所有用户，但通过 WHERE l.user_id IS NULL，我们只选择了那些在 logs 表中没有近 30 天内登录记录的用户。

1.3 其他连接类型

• RIGHT JOIN：与 LEFT JOIN 相反，保留右表中的所有记录，即使左表中没有匹配的记录。通常情况下，RIGHT JOIN 可以通过调整表的顺序并使用 LEFT JOIN 来实现相同的效果，因此它在实际应用中较少使用。

• FULL JOIN：返回两个表中的所有记录，无论是否匹配。未匹配的列将被填充为 NULL。FULL JOIN 在 MySQL 中不直接支持，但在其他数据库（如 PostgreSQL）中可以使用。

• CROSS JOIN：返回两个表的笛卡尔积，即每个表中的每一行都与另一个表中的每一行组合。通常用于生成所有可能的组合。

2. 如何选择合适的连接类型？

要选择合适的连接类型，关键是要明确你的 查询目标 和 数据需求。以下是一些帮助你做出正确选择的步骤：

2.1 明确查询目标

• 你想要哪些表中的数据？

  • 如果你只关心两个表中都有匹配记录的行，那么 INNER JOIN 是合适的选择。
  • 如果你希望保留某个表中的所有记录，即使另一个表中没有匹配的记录，那么 LEFT JOIN 或 RIGHT JOIN 是合适的选择。

• 你希望返回哪些行？

  • 如果你只关心匹配的行，使用 INNER JOIN。
  • 如果你希望返回所有行，即使没有匹配的记录，使用 LEFT JOIN 或 RIGHT JOIN，并结合 WHERE 子句来过滤出特定的行。

2.2 考虑连接的方向

• 哪个表是你关注的核心？
  • 如果你希望保留 左表中的所有记录，使用 LEFT JOIN。
  • 如果你希望保留 右表中的所有记录，使用 RIGHT JOIN。

2.3 利用 NULL 来识别缺失的记录

• LEFT JOIN 和 RIGHT JOIN 的一个重要特性是，当没有匹配的记录时，右表或左表的列会被填充为 NULL。你可以通过 WHERE 子句中的 IS NULL 或 IS NOT NULL 来筛选出这些行。

3. 总结：简化规则 vs. 灵活应用

虽然你可以简化为：

• 看到“有”就联想到 INNER JOIN：如果你只关心两个表中都有匹配记录的行。
• 看到“没有”就联想到 LEFT JOIN：如果你希望保留某个表中的所有记录，并找出那些在另一个表中没有匹配记录的行。

但为了编写更高效、更灵活的查询，建议你：

• 明确查询目标：清楚你想要返回哪些表中的数据，以及你希望返回哪些行。
• 考虑连接的方向：根据你关注的核心表，选择 LEFT JOIN 或 RIGHT JOIN。
• 利用 NULL 来识别缺失的记录：通过 WHERE 子句中的 IS NULL 或 IS NOT NULL 来筛选出特定的行。

4. 练习和实践

通过多做练习，你会逐渐形成一种直觉，能够快速判断出哪种连接方式最适合当前的需求。每次遇到新的查询需求时，尝试问自己：

• 我需要返回哪些表中的数据？
• 我希望返回哪些行？
• 我是否需要排除某些行？
• 哪个表应该作为左表或右表？

通过不断的练习，你会更加熟练地选择合适的连接类型，并能够更自信地编写高效的 SQL 查询。

5. 示例对比

为了更好地理解 INNER JOIN 和 LEFT JOIN 的区别，让我们通过一个具体的例子来对比它们的行为。

5.1 INNER JOIN 示例

假设我们有两个表：users 和 logs。

• users 表包含所有用户的记录。
• logs 表包含用户的登录记录。

-- 找出有登录记录的用户
SELECT u.user_id, u.username
FROM users u
INNER JOIN logs l ON u.user_id = l.user_id;

结果：

• 只返回那些在 logs 表中有登录记录的用户。
• 没有登录记录的用户将被排除在外。

5.2 LEFT JOIN 示例

-- 找出近 30 天内没有登录记录的用户
SELECT u.user_id, u.username
FROM users u
LEFT JOIN logs l ON u.user_id = l.user_id AND l.login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE l.user_id IS NULL;

结果：

• 返回所有用户，但只选择那些在 logs 表中没有近 30 天内登录记录的用户。
• 有登录记录的用户将被排除在外。

6. 结论

• INNER JOIN 适用于 只关心匹配记录的场景。
• LEFT JOIN 适用于 保留左表中的所有记录，并筛选出那些在右表中没有匹配记录的行。

虽然你可以简化为“看到‘有’就联想到 INNER JOIN，看到‘没有’就联想到 LEFT JOIN”，但为了编写更高效、更灵活的查询，建议你根据具体的查询目标和数据需求，灵活选择合适的连接类型。

在什么情景下，我们应该考虑非等值连接？

MySQL 中的 等值连接 和 非等值连接 是两种不同的连接方式，它们的设计目的是为了满足不同类型的数据关联需求。理解这两者的区别以及何时使用非等值连接，可以帮助你编写更高效、更灵活的 SQL 查询。下面我将详细解释为什么 MySQL 会有这两种连接类型，并探讨在什么情景下你应该考虑使用非等值连接。

1. 为什么会有等值连接和非等值连接？

1.1 等值连接（Equi-Join）

• 定义：等值连接是指两个表之间的连接条件是基于相等运算符（=）的连接。也就是说，只有当两个表中的某个列的值相等时，才会生成匹配的行。

• 常见用途：

  • 主键-外键关联：最常见的等值连接场景是通过主键和外键进行表之间的关联。例如，订单表中的 customer_id 列与客户表中的 id 列相等时，表示该订单属于某个特定的客户。
  • 自连接：有时你需要在同一张表中进行关联，例如查询员工及其上级经理的关系，可以通过 employee_id 和 manager_id 进行等值连接。

• 优点：

  • 性能优化：等值连接通常可以利用索引进行快速查找，尤其是在主键-外键关系中，MySQL 的查询优化器可以非常高效地处理这些连接。
  • 简单直观：等值连接的逻辑非常清晰，容易理解和实现。

1.2 非等值连接（Non-Equi-Join）

• 定义：非等值连接是指连接条件不是基于相等运算符的连接。它可以使用其他比较运算符（如 <, >, <=, >=, <>, BETWEEN, IN 等）来定义连接条件。非等值连接通常用于更复杂的查询场景，例如查找某个范围内或满足特定条件的数据。

• 常见用途：

  • 范围查询：当你需要查找某个范围内的数据时，非等值连接非常有用。例如，查询某个时间段内的订单，或者查找价格在某个区间内的产品。
  • 部分匹配：有时你需要根据某些不完全相等的条件进行连接。例如，查找某个日期之前的所有记录，或者查找某个数值大于某个阈值的记录。
  • 复杂业务逻辑：在某些业务场景中，连接条件可能涉及多个字段或复杂的逻辑表达式。例如，查询某个用户在过去 30 天内是否有过购买行为，或者查找某个产品的库存量是否低于安全库存。

• 优点：

  • 灵活性：非等值连接提供了更大的灵活性，允许你根据更复杂的条件进行数据关联，而不仅仅是简单的相等条件。
  • 适应更多业务场景：许多现实世界的业务需求无法通过简单的等值连接来满足，非等值连接可以更好地应对这些复杂的需求。

• 挑战：

  • 性能问题：非等值连接可能会导致更复杂的查询计划，尤其是在处理大表时，可能会影响查询性能。MySQL 的查询优化器可能无法像等值连接那样高效地利用索引，因此需要特别注意索引的设计和查询的优化。
  • 难以优化：由于非等值连接的条件更加复杂，MySQL 的优化器可能无法像等值连接那样轻松地选择最优的执行计划。因此，编写非等值连接查询时，开发者需要更加谨慎，确保查询的效率。

2. 什么时候应该考虑使用非等值连接？

非等值连接适用于以下几种典型场景：

2.1 范围查询

当你需要根据某个范围内的值进行连接时，非等值连接是非常有用的。例如，假设你有两个表：一个是订单表 orders，另一个是促销活动表 promotions。你想找出所有发生在某个促销活动期间的订单。你可以使用非等值连接来实现这一点：

SELECT o.order_id, p.promotion_name
FROM orders o
JOIN promotions p ON o.order_date BETWEEN p.start_date AND p.end_date;

在这个例子中，BETWEEN 是一个非等值连接条件，表示订单的日期必须在促销活动的开始日期和结束日期之间。这种类型的查询非常适合处理时间范围、价格区间等场景。

2.2 部分匹配

有时你需要根据某些不完全相等的条件进行连接。例如，假设你有一个用户表 users 和一个日志表 logs，你想找出最近 30 天内有登录记录的用户。你可以使用非等值连接来实现这一点：

SELECT u.user_id, u.username
FROM users u
JOIN logs l ON u.user_id = l.user_id AND l.login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

在这个例子中，l.login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 是一个非等值连接条件，表示只选择最近 30 天内的登录记录。这种类型的查询非常适合处理时间窗口、阈值条件等场景。

2.3 复杂业务逻辑

在某些业务场景中，连接条件可能涉及多个字段或复杂的逻辑表达式。例如，假设你有一个产品表 products 和一个库存表 inventory，你想找出所有库存量低于安全库存的产品。你可以使用非等值连接来实现这一点：

SELECT p.product_id, p.product_name
FROM products p
JOIN inventory i ON p.product_id = i.product_id AND i.stock_quantity < p.safety_stock;

在这个例子中，i.stock_quantity < p.safety_stock 是一个非等值连接条件，表示只选择库存量低于安全库存的产品。这种类型的查询非常适合处理复杂的业务规则和多条件关联。

2.4 分段聚合

有时你需要根据某个字段的值进行分段聚合。例如，假设你有一个销售表 sales，你想按不同的销售额区间统计销售数量。你可以使用非等值连接来实现这一点：

WITH sales_ranges AS (SELECT 0 AS min_sales, 1000 AS max_sales UNION ALLSELECT 1000, 5000 UNION ALLSELECT 5000, 10000
)
SELECT sr.min_sales, sr.max_sales, COUNT(s.sale_id) AS sale_count
FROM sales s
JOIN sales_ranges sr ON s.sales_amount >= sr.min_sales AND s.sales_amount < sr.max_sales
GROUP BY sr.min_sales, sr.max_sales;

在这个例子中，s.sales_amount >= sr.min_sales AND s.sales_amount < sr.max_sales 是一个非等值连接条件，表示根据销售额的不同区间进行分段聚合。这种类型的查询非常适合处理分段统计、分组分析等场景。

3. 非等值连接的性能优化建议

由于非等值连接的条件更加复杂，可能会导致查询性能下降。为了提高非等值连接的性能，建议采取以下措施：

• 索引设计：确保连接条件中涉及的列上有适当的索引，尤其是对于非等值连接中的范围条件（如 BETWEEN, IN 等），索引可以显著提高查询性能。例如，在上面的时间范围查询中，order_date 和 start_date、end_date 应该有索引。

• 避免全表扫描：非等值连接可能会导致全表扫描，尤其是在没有合适索引的情况下。尽量通过优化查询条件或添加索引来避免全表扫描。

• 使用覆盖索引：如果查询只涉及索引中的列，MySQL 可以直接从索引中获取数据，而不需要访问表中的实际数据行，从而提高查询性能。

• 简化连接条件：尽量减少非等值连接条件的复杂性，避免使用过多的逻辑运算符或嵌套条件。复杂的连接条件可能会使查询优化器难以选择最优的执行计划。

• 使用 EXPLAIN 分析查询：使用 EXPLAIN 语句来分析查询的执行计划，确保 MySQL 正确地使用了索引并选择了高效的执行路径。如果发现查询性能不佳，可以根据 EXPLAIN 的结果进行调整。

4. 总结

• 等值连接 是基于相等运算符的连接，适用于简单的主键-外键关联和自连接等场景。它具有良好的性能和易用性，是最常见的连接类型。

• 非等值连接 使用其他比较运算符（如 <, >, <=, >=, BETWEEN, IN 等），适用于更复杂的查询场景，如范围查询、部分匹配、复杂业务逻辑和分段聚合等。虽然非等值连接提供了更大的灵活性，但也可能带来性能挑战。

• 何时使用非等值连接：当你需要根据范围、阈值、复杂条件或分段聚合进行数据关联时，非等值连接是一个非常有用的工具。然而，在使用非等值连接时，务必注意性能优化，确保查询能够高效执行。

通过合理选择连接类型并优化查询，你可以编写出既高效又灵活的 SQL 查询，满足各种业务需求。如果你对查询性能有较高的要求，建议使用 EXPLAIN 语句来分析查询的执行计划，并根据需要调整索引和查询条件。

14.自连接

参考mysql自连接处理层次结构数据.md

15.jpa

15.1 jpa调用存储过程

@Query(value = " call p_production_plan_check(:inParam1,:inParam2,:inParam3,:inParam4)", nativeQuery = true)List<ProductionPlan> pPlanCheck(@Param("inParam1") String calStart,@Param("inParam2") String calEnd,@Param("inParam3") String workshopcode,@Param("inParam4") String orderno)

15.2 jpa多条件 in /and /or 分页

 public void SynchronousDzgjData(){List<DzgjVipPayOrder> resultList = dzgjVipPayOrderRepository.findAll(((root, query, cb) -> {List<Predicate> list = new ArrayList<>();if (StringUtils.isNotBlank(dateTime)) {LocalDateTime dayOfMonthStart = DateTimeUtil.getDateStart(DateTimeUtil.getFirstDayOfMonth(DateTimeUtil.fromString2LocalDateTime(dateTime)));LocalDateTime dayOfMonthEnd = DateTimeUtil.getDateEnd(DateTimeUtil.getlastDayOfMonth(DateTimeUtil.fromString2LocalDateTime(dateTime)));list.add(cb.between(root.get("payTime"), dayOfMonthStart, dayOfMonthEnd));}//首单list.add(cb.equal(root.get("firstPay"), 1));获取订单状态大于等于1的，0未支付list.add(cb.greaterThanOrEqualTo(root.get("status"), 1));使用 jpa inlist.add(root.get("userId").in(userIdList));// 指定按照创建时间排序query.orderBy(cb.desc(root.get("payTime")));//如果需要or连接 只需要再new 一个List 放or的参数,最终放入list//记得将条件放入if判断中，否则会出现0=1的情况//list.add(cb.or(orlist.toArray()))return cb.and(list.toArray(new Predicate[list.size()]));}));}

 /*** 京店宝【获取退款申请列表】* 多条件查询注意加if判断，否则会出现0=1的情况* @param pageNo* @param pageSize* @param refundType* @param search* @return*/public CommonResponse<PageResponse<JdbRefundVO>> getJdbRefundList(Integer pageNo, Integer pageSize, Integer refundType, String search) {try {//封装请求参数PageRequest pageRequest = new PageRequest(pageNo - 1, pageSize);//分页查询Page<JdbRefund> page = jdbRefundRepository.findAll((root, query, cb) -> {List<Predicate> predicateList = new ArrayList<>();List<Predicate> predicateOrList = new ArrayList<>();if (StringUtils.isNotBlank(search)) {predicateOrList.add(cb.like(root.get("name"), String.format("%%%s%%", search)));predicateOrList.add(cb.like(root.get("phone"), String.format("%%%s%%", search)));predicateOrList.add(cb.like(root.get("jdbAccount"), String.format("%%%s%%", search)));}//or 组合if (predicateOrList.size() > 0) {predicateList.add(cb.or(predicateOrList.toArray(new Predicate[predicateOrList.size()])));}//and 组合if (refundType != null && refundType != -1) {predicateList.add(cb.equal(root.get("checkStatus"), refundType));}//先按照审核未审核的排，之后再按照创建时间排query.orderBy(cb.asc(root.get("checkStatus")), cb.desc(root.get("createTime")));return cb.and(predicateList.toArray(new Predicate[predicateList.size()]));}, pageRequest);if (page != null && page.hasContent()) {List<JdbRefundVO> list = page.getContent().stream().map(jdbRefund -> mapToJdbRefundVO(jdbRefund)).collect(Collectors.toList());return new CommonResponse<>(new PageResponse<>((int) page.getTotalElements(), pageNo, list));}return new CommonResponse<>(new PageResponse<>(0, pageNo, Collections.emptyList()));} catch (Exception e) {ExceptionUtil.err(getClass(), e);return ExceptionUtil.errResponse();}}

15.3 jpa 多条件、分页、分组 dto/vo --使用spring-data-jpa

@query 原生sql

     /*** \n#pageable\n* \n--  #pageable\n* order by ?#{#pageable} 固定写法* 多条件、分页、分组、使用jpa返回Dto 很麻烦，慎用** @param userId* @param dateTime* @param pageable* @return*/@Query(value = "SELECT cast(DATE_FORMAT(create_time,'%Y-%m-%d') AS char)  as `date`, cast(ratio_agent as char) as ratio, cast(IFNULL(SUM(IF(order_type=1,amount,-amount)),0.00) AS char) as amount ,cast(IFNULL(SUM(commission_agent),0.00) AS char) as commission FROM tao_order where  user_id =:userId AND DATE_FORMAT(create_time,'%Y-%m')= DATE_FORMAT(:dateTime,'%Y-%m') group by DATE_FORMAT(create_time,'%Y-%m-%d') order by create_time desc \n#pageable\n",countQuery = "select count(0) FROM tao_order where  user_id =:userId AND DATE_FORMAT(create_time,'%Y-%m')= DATE_FORMAT(:dateTime,'%Y-%m') group by DATE_FORMAT(create_time,'%Y-%m-%d') ",nativeQuery = true)Page<Object[]> getAgentDetailList(@Param("userId") Long userId, @Param("dateTime") String dateTime, Pageable pageable);

16.mybatis plus

16.1 创建PaginationInterceptor bean对象

package cn.jingdianbao.user.commons.config;import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** 类名: MybatisPlusConfig* 描述:* 日期: 2021/4/12-16:10** @author 林建辉*/
@Configuration
public class MybatisPlusConfig {@Beanpublic PaginationInterceptor paginationInterceptor() {return new PaginationInterceptor().setDialectType(DBType.MYSQL.getDb());}
}

16.2在数据源中设置mybatis plus分页插件

//创建数据源并设置mybatis分页插件
package cn.jingdianbao.user.commons.config;import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.baomidou.mybatisplus.toolkit.GlobalConfigUtils;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @author xiyatu* @date 2019/3/13 11:26* Description 数据源配置*/
@Configuration
public class DataSourceConfiguration {@Autowiredprivate Environment environment;@Autowiredprivate PaginationInterceptor paginationInterceptor;@Bean(name = "dataSource")@ConfigurationProperties(prefix = "spring.datasource")public DruidDataSource dataSource() {return DruidDataSourceBuilder.create().build();}/*** 手动配置事务管理器** @param dataSource* @return*/@Beanpublic DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}/*** 配置sessionFactory** @param dataSource* @return* @throws Exception*/@Bean(name = "jdbSqlSessionFactory")@Primarypublic SqlSessionFactory jdbSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();mybatisSqlSessionFactoryBean.setDataSource(dataSource);//配置枚举mybatisSqlSessionFactoryBean.setTypeEnumsPackage(environment.getProperty("mybatis-plus.type-enums-package"));mybatisSqlSessionFactoryBean.setGlobalConfig(getGlobalConfiguration());//映射文件mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));MybatisConfiguration configuration = new MybatisConfiguration();//下划线隐射驼峰configuration.setMapUnderscoreToCamelCase(true);configuration.setCacheEnabled(true);mybatisSqlSessionFactoryBean.setConfiguration(configuration);// 关键代码 设置 MyBatis-Plus 分页插件 by linjianhuiInterceptor[] plugins = {paginationInterceptor};mybatisSqlSessionFactoryBean.setPlugins(plugins);return mybatisSqlSessionFactoryBean.getObject();}private GlobalConfiguration getGlobalConfiguration() {GlobalConfiguration globalConfiguration = GlobalConfigUtils.defaults();//设置sql逻辑拦截globalConfiguration.setSqlInjector(new LogicSqlInjector());//设置逻辑删除字段值，已删除为1globalConfiguration.setLogicDeleteValue("1");//设置逻辑删除字段值，未删除为1globalConfiguration.setLogicNotDeleteValue("0");//字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"globalConfiguration.setFieldStrategy(2);return globalConfiguration;}@Bean(name = "jdbSqlSessionTemplate")@Primarypublic SqlSessionTemplate jdbSqlSessionTemplate(@Qualifier("jdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}}

16.3mybatis plus分页查询条件

 /*** 描述：** @param invitationCodeList* @param sourceTypeList* @param pageNo* @param pageSize* @param friendAccount* @param startTime* @param endTime* @return* @author 林建辉* @date: 2021/4/12 17:01*/public CommonResponse<PageResponse<UserInfoData>> getInviteListByInvitationCodeListAndSourceTypeList(List<String> invitationCodeList, List<Integer> sourceTypeList, Integer pageNo, Integer pageSize, String friendAccount, String startTime, String endTime) {//分页对象Page<UserAuth> page = new Page<>(pageNo, pageSize);//查询条件Wrapper<UserAuth> warpper = Condition.create().in(UserAuth.INVITATION_CODE, invitationCodeList).in(UserAuth.SOURCE_TYPE, sourceTypeList).like(StringUtils.isNotBlank(friendAccount), UserAuth.PHONE_NUMBER, friendAccount, SqlLike.DEFAULT);//在这种情况下，第二个重载lt(boolean condition, R column, Object val)会有错误，改为if判断模式if (StringUtils.isNotBlank(startTime)) {warpper.ge(UserAuth.CREATE_TIME, DateTimeUtil.fromString2LocalDate(startTime, DateTimeUtil.FORMAT_DATE).atStartOfDay());}if (StringUtils.isNotBlank(endTime)) {warpper.le(UserAuth.CREATE_TIME, DateTimeUtil.fromString2LocalDate(endTime, DateTimeUtil.FORMAT_DATE).atTime(LocalTime.MAX));}//按照时间倒序排warpper.orderBy(UserAuth.CREATE_TIME, false);page.setRecords(userAuthMapper.selectPage(page, warpper));if (page.getTotal() <= 0) {return new CommonResponse<>(new PageResponse<>(0, pageNo, Collections.emptyList()));}//不会为空List<UserInfoData> resultList = page.getRecords().stream().map(this::getUserInfoData).collect(Collectors.toList());return new CommonResponse<>(new PageResponse<>(page.getTotal(), pageNo, resultList));}

16.4 mybatis plus and or

 List<KeywordGroupTagInfo> list = keywordGroupTagInfoService.list(new QueryWrapper<KeywordGroupTagInfo>().eq(KeywordGroupTagInfo.DATE, date == null ? LocalDate.now().toString() : date.toString()).eq(KeywordGroupTagInfo.OS_TYPE, osType == null ? 1 : 1).eq(skuId != null, KeywordGroupTagInfo.SKU_ID, skuId).eq(keywordId != null, KeywordGroupTagInfo.KEYWORD_ID, keywordId).and(qw->qw.eq(KeywordGroupTagInfo.VISIT_NUM, 0).or().eq(KeywordGroupTagInfo.ADD_CART_NUM, 0).or().eq(KeywordGroupTagInfo.AGE_GROUP, "[]")));

16.4 mybatis plus update

  /*  String oldSkuId = monitor.getSkuId();monitor.setKeyword(keyword);monitor.setSkuId(skuId);jdbSubSkuMonitorMapper.updateById(monitor);jdbSubSkuMonitorHistoryMapper.update(null, new UpdateWrapper<JdbSubSkuMonitorHistory>().eq(JdbSubSkuMonitorHistory.MONITOR_ID, monitor.getId()).eq(JdbSubSkuMonitorHistory.SKU_ID, oldSkuId).set(JdbSubSkuMonitorHistory.SKU_STATUS, -1));*/

16.5 mybatis 批量插入和批量更新和批量查询

 /*** 批量插入** @author linjianhui** @date 2023/06/21**/int batchInsert(@Param("list") List<TTirpPlanCheckResource> list);/*** 批量更新** @author linjianhui* @date 2023/06/21**/int batchUpdate(@Param("list") List<TTirpPlanCheckResource> list);

  <!-- 在MyBatis中执行批量插入，可以使用foreach元素迭代对象集合，逐个插入--><insert id="batchInsert" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="java.util.List">INSERT INTO t_tirp_plan_check_resource (trip_id,activity_id,resource_name,file_templates,tip_msg,is_necessary,is_customize,is_default,default_url,create_time,update_time)VALUES<foreach collection="list" item="item" separator=",">(#{item.tripId}, #{item.activityId}, #{item.resourceName}, #{item.fileTemplates}, #{item.tipMsg},#{item.isNecessary},#{item.isCustomize},#{item.isDefault},#{item.defaultUrl},#{item.createTime}, #{item.updateTime})</foreach></insert><!-- 在MyBatis中执行批量更新，可以使用foreach元素迭代对象集合，使用;分隔符逐个更新--><update id="batchUpdate" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="java.util.List"><foreach collection="list" item="item" separator=";">UPDATE t_tirp_plan_check_resource SET 1 = 1 WHERE id = #{item.id}</foreach></update>

16.6 mybatis xml foreach in

<select id="selectByActIdAndActCourseIdAndStuIds" resultType="com.project.entity.api.TStuEvaNorm">select * from t_stu_eva_norm where 1=1<if test=' actId !=null and actId !="" '> and act_id = #{actId} </if><if test=' actCourseId !=null and actCourseId !="" '> and act_course_id = #{actCourseId} </if><if test=' stuIds !=null and stuIds.size > 0 '> and student_id in<foreach item="item" collection="stuIds" open="(" separator="," close=")">#{item}</foreach>     </if></select>@Select("<script>" +"select * from t_stu_eva_norm where 1=1" +"<if test='actId != null and actId != \"\"'> and act_id = #{actId} </if>" +"<if test='actCourseId != null and actCourseId != \"\"'> and act_course_id = #{actCourseId} </if>" +"<if test='stuIds != null and stuIds.size > 0'> and student_id in" +"<foreach item=\"item\" collection=\"stuIds\" open=\"(\" separator=\",\" close=\")\">" +"#{item}" +"</foreach>" +"</if>" +"</script>")List<TStuEvaNorm> selectByActIdAndActCourseIdAndStuIds(@Param("actId") String actId,@Param("actCourseId") String actCourseId,@Param("stuIds") List<String> stuIds);

16.7 in多个列的写法

-- in多个列的写法,多个列用()包裹起来，中间用逗号分割【where的字段多个列用()包裹起来，中间用逗号分割】【精确匹配】
-- 格式如下：
SELECT * FROM your_table WHERE (column1, column2) IN ((value1, value2), (value3, value4), (value5, value6));SELECT * FROM t_stu_eva_norm where (student_id,act_course_id) in (('stu-1131241147339898882','1144289496976588800'),('stu-1131241147352481792','1144289496976588800'));

16.8 mybatis 映射文件中，if标签判断字符串相等：

<if test="status=='1'.toString()">

或

<if test = 'status== "1"'>

16.9 加载树形结构

    public List<MatTypeVO> treeVO2() {// 一次性加载所有材料类型List<MatTypeVO> allTypes = imatTypeService.listVO(new MatTypeFormRequest());// 使用 Collectors.groupingByConcurrent 构建 parentId 到 子节点列表 的映射表Map<Integer, List<MatTypeVO>> parentIdToChildrenMap = allTypes.stream().collect(Collectors.groupingByConcurrent(MatTypeVO::getParentId));// 根节点列表List<MatTypeVO> roots = parentIdToChildrenMap.getOrDefault(Integer.valueOf(0), new ArrayList<>());// 并行构建树结构buildTreeParallel(roots, parentIdToChildrenMap);return roots;}private void buildTreeParallel(List<MatTypeVO> nodes, Map<Integer, List<MatTypeVO>> parentIdToChildrenMap) {nodes.parallelStream().forEach(node -> {List<MatTypeVO> children = parentIdToChildrenMap.getOrDefault(node.getId(), new ArrayList<>());if (!children.isEmpty()) {node.setChildren(children);buildTreeParallel(children, parentIdToChildrenMap);}});}

17.JdbcTemplate 批量操作

在我们做后端服务Dao层开发，特别是大数据批量插入的时候，这时候普通的ORM框架(Mybatis、hibernate、JPA)就无法满足程序对性能的要求了。当然我们又不可能使用原生的JDBC进行操作，那样尽管效率会高，但是复杂度会上升。
综合考虑我们使用Spring中的JdbcTemplate和具名参数namedParameterJdbcTemplate来进行批量操作。
JdbcTemplate提供的主要方法:execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批	 处理相关语句；query方法及queryForXXX方法：用于执行查询相关语句；call方法：用于执行存储过程、函数相关语句。

17.2NamedParameterJdbcTemplate

public void batchSave(){List<Object[]> batchArgs=new ArrayList<Object[]>();batchArgs.add(new Object[]{1,"小明",21});batchArgs.add(new Object[]{2,"小红",22});batchArgs.add(new Object[]{3,"露西",23});String sql = "insert into user (username,password) values (?,?)";jdbcTemplate.batchUpdate(sql, batchArgs);
}

以上基本实现了批量插入功能，但是当数据库字段比较多的时候，再以?占位符的形式编码的话就可能不是那么好一 一对应了，这里spring还提供了SimpleJdbcTemplate(Spring3.1+ 以后被标记为过时，到Spring 4.3则被完全移除，后面这个完全能满足需求)和NamedParameterJdbcTemplate模板引擎。
NamedParameterJdbcTemplate
相信使用过Hibernate的同学都知道，HQL中可以使用？或者:*的方式在外部配置查询参数。在 Spring JDBC 框架中，也提供了一种绑定 SQL 参数的方式，使用具名参数(named parameter)。
我们只需要在使用NamedParameterJdbcTemplate类中使用@Autowired进行注入即可：

17.2NamedParameterJdbcTemplate

17.2.1 批量更新插入batchUpdate

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;public void batchSave(){List<User> list = new ArrayList<User>();//新增用户list.add(new AppStudent(1,"张三",21));list.add(new AppStudent(1,"李四",22));list.add(new AppStudent(1,"王二麻子",23));//批量转数组SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());String sql = "INSERT INTO app_student(class_id,name,age) VALUES (:classId,:name,:age)";namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
}

17.2.2 多条件分页和in

 public ResponseList<List<JdbOrderVo>> getOrderAll(int id, int pageNo, int pageSize, String startDate, StringendDate, String search, int userId) {//判断是否是BD角色Boolean isBD = AuthUtil.isContainsRole(Constants.BD);//sql 参数map对象MapSqlParameterSource sqlParam = new MapSqlParameterSource();StringBuilder sql = new StringBuilder();sql.append(" select * from jdb_order where 1=1 ");if (isBD) {sqlParam.addValue("userId", userId);sql.append(" and user_id=:userId ");}if (StringUtils.isNotBlank(search)) {sqlParam.addValue("search", search);sql.append(" and account=:search ");}if (StringUtils.isNotBlank(startDate)) {sqlParam.addValue("startDate", startDate);sql.append(" and DATE_FORMAT(buy_time,'%Y-%m-%d')>=:startDate ");}if (StringUtils.isNotBlank(endDate)) {sqlParam.addValue("endDate", endDate);sql.append(" and DATE_FORMAT(buy_time,'%Y-%m-%d') <=:endDate ");}Integer totalCount = namedParameterJdbcTemplate.queryForObject(String.format("select count(0) from (%s) a", sql.toString()), sqlParam, Integer.class);sql.append(" order by buy_time desc,id desc limit :index,:pageSize ");sqlParam.addValue("index", (pageNo - 1) * pageSize);sqlParam.addValue("pageSize", pageSize);Log.getSql().info("京店宝订单管理列表sql:\r\n{},参数map:\r\n{}", sql.toString(), sqlParam);List<JdbOrder> jdbOrderList = namedParameterJdbcTemplate.query(sql.toString(), sqlParam, new BeanPropertyRowMapper<>(JdbOrder.class));List<JdbOrderVo> resultList = getResult(jdbOrderList);return new ResponseList(200, "", pageSize, totalCount, pageNo, resultList);}

 public Map<Integer, Double> monthMoneyListUserIdForJdb(List<Integer> userIdList, String yearMonth) {Map<Integer, Double> resultMap = new HashMap<>();if (CollectionUtils.isEmpty(userIdList)) {return resultMap;}StringBuffer sql = new StringBuffer();sql.append("SELECT DATE_FORMAT(create_time, '%Y-%m') as yearMonth, IFNULL(SUM(buy_money), 0) as month ,user_id AS id FROM jdb_order ");sql.append("  WHERE DATE_FORMAT(create_time, '%Y-%m') =:yearMonth ");sql.append("  AND user_id in(:userIdList)  AND is_performance=1 GROUP BY user_id");LogUtils.SQL.info("OrderService.monthMoneyListUserId.sql====={},yearMonth={},userIdList={}", sql.toString(), yearMonth, userIdList);MapSqlParameterSource sqlParam = new MapSqlParameterSource("yearMonth", yearMonth).addValue("userIdList", userIdList);List<Db> dbList = namedParameterJdbcTemplate.query(sql.toString(), sqlParam, new BeanPropertyRowMapper<>(Db.class));if (CollectionUtils.isNotEmpty(dbList)) {dbList.stream().map(db -> resultMap.put(db.getId(), db.getMonth())).collect(Collectors.toList());}return resultMap;}

//单线程分页低效率插入
public void addManagePresentVipRecordPhone() {int from = 0;int size = 100;List<ManageJdbVipGift> records = manageJdbVipGiftMapper.listRecord(null, from, size);while (!CollectionUtils.isEmpty(records)) {for (ManageJdbVipGift manageJdbVipGift : records) {UserAuth user = userService.getUserByParam(new UserAuth().setUserId(manageJdbVipGift.getUserId()).setSourceType(OsType.jingdianbao.getValue()));if (user != null) {manageJdbVipGiftMapper.updatePhone(manageJdbVipGift.getId(), user.getPhoneNumber());}}from += size;records = manageJdbVipGiftMapper.listRecord(null, from, size);}}`yushu-diagnosis`.sync_

18.月份不足两位补零

 //月份不足2位补0String YMonth = String.format("%s-%02d", year, i);

19.创建连续日期

select d.currDate,IF(b.money is not null, b.money,0) from (select date_format(date_add('2021-01-01',interval t.help_topic_id month),'%Y-%m') as currDate  from mysql.help_topic t where t.help_topic_id<=11) d left join 
(SELECT SUM(recharge_money) as money,DATE_FORMAT(recharge_time,'%Y-%m') as ym FROM order_info  WHERE user_id=5837 AND is_performance=1 group by ym) b on d.currDate=b.ym### show create table mysql.help_topic
CREATE TABLE `help_topic` (`help_topic_id` int(10) unsigned NOT NULL,`name` char(64) NOT NULL,`help_category_id` smallint(5) unsigned NOT NULL,`description` text NOT NULL,`example` text NOT NULL,`url` text NOT NULL,#有test字段，不要select *PRIMARY KEY (`help_topic_id`),UNIQUE KEY `name` (`name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help topics'

19.1使用变量和循环生成序列

SET @i := 0;
SELECT @i:=@i+1 AS num FROM any_table LIMIT 5;

19.2使用递归生成序列

WITH RECURSIVE seq AS (SELECT 1 AS numUNION ALLSELECT num+1 FROM seq WHERE num < 5
)
SELECT * FROM seq;

19.3 UNION ALL 操作符来生成序列

SELECT 1 AS num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5;

19.4 在mybatis中写变量生成序列

<select id="getSequence" resultType="java.lang.Integer">SET @num := 0;SELECT @num := @num + 1 AS num FROM any_table LIMIT 5;
</select>

20.创建随机函数

select FLOOR(RAND() * 1000)

21. mysql有哪些系统表，分别有什么作用？

MySQL 中有很多系统表（system tables），它们存储了 MySQL 数据库的元数据信息、系统配置信息和其他有用的信息。下面是一些常用的系统表及其作用：
mysql.user: 存储 MySQL 的用户帐户和权限信息。
mysql.db: 存储 MySQL 的数据库级别的权限信息。
mysql.tables_priv: 存储 MySQL 的表级别的权限信息。
mysql.columns_priv: 存储 MySQL 的列级别的权限信息。
mysql.procs_priv: 存储 MySQL 的存储过程和函数级别的权限信息。
mysql.host: 存储 MySQL 允许连接的主机列表。
mysql.help_topic: 存储 MySQL 帮助信息的元数据。
mysql.time_zone、mysql.time_zone_name 和 mysql.time_zone_transition: 存储 MySQL 时区信息的元数据。
mysql.event: 存储 MySQL 中计划事件的元数据。
information_schema.tables: 存储 MySQL 所有表的信息，例如表名、列名、数据类型等。
information_schema.columns: 存储 MySQL 所有列的信息，例如列名、数据类型、约束等。
information_schema.statistics: 存储 MySQL 所有表的索引信息，例如索引名称、索引类型、列名等。
information_schema.key_column_usage: 存储 MySQL 所有外键的信息，例如外键名称、关联表名、关联列名等。
这些系统表可以帮助您了解 MySQL 数据库中的元数据信息，了解表和列的结构、索引和外键，以及授权和访问控制信息。但是，请注意，除非您对 MySQL 数据库的运作和元数据信息有深入的理解，否则不建议直接修改这些系统表中的数据。select * from information_schema.columns;
select * from  information_schema.tables;
-- 包含了所有的帮助手册，非常管用
select * from mysql.help_topic;

22.mysql分割字符串

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('11,22,33', ',', n), ',', -1) as value
FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3) as nums
WHERE n <= LENGTH('11,22,33') - LENGTH(REPLACE('11,22,33', ',', '')) + 1;| value |
|-------|
| 11    |
| 22    |
| 33    |SUBSTRING_INDEX('要分割的字符串','分割符：如逗号','分割的次数')
这里的子查询(SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3) as nums表示将字符串分割成多少个子字符串，这里设置了3个子字符串，因此将会把"11,22,33"分割成3个字符串，即"11"、"22"和"33"。如果要分割更多的子字符串，可以添加更多的UNION ALL SELECT子句。SUBSTRING_INDEX函数是将指定字符串按指定的分隔符进行分割，可以指定要分割的字符串的位置。FIND_IN_SET函数则用于在一个逗号分隔的字符串中查找子字符串，并返回它在逗号分隔字符串中的位置。

23.修改表的AUTO_INCREMENT

ALTER TABLE t_class AUTO_INCREMENT = 868;

24.使用with RECURSIVE 生成序列

WITH RECURSIVE date_range AS (SELECT CURDATE() AS geneDateUNION ALLSELECT DATE_ADD(geneDate, INTERVAL 1 DAY)FROM date_rangeWHERE DATE_ADD(geneDate, INTERVAL 1 DAY) <= LAST_DAY(CURDATE() + INTERVAL 1 MONTH)
)
SELECT geneDate FROM date_range;-- LAST_DAY函数获取日期的最后一天

24.1 获取本周的日期序列

WITH RECURSIVE week_days AS (-- 基础情况：选择本周的第一天（即当前日期所在的星期一）SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS day_date,1 AS day_of_weekUNION ALL-- 递归部分：生成接下来的 6 天SELECT DATE_ADD(day_date, INTERVAL 1 DAY),day_of_week + 1FROM week_daysWHERE day_of_week < 7
)
SELECT day_date,day_of_week,DAYNAME(day_date) AS day_name
FROM week_days;

24.2 获取本月的日期序列

WITH RECURSIVE month_days AS (-- 基础情况：选择本月的第一天SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS day_date,  -- 本月的第一天1 AS day_of_monthUNION ALL-- 递归部分：生成接下来的每一天SELECT DATE_ADD(day_date, INTERVAL 1 DAY),day_of_month + 1FROM month_daysWHERE DATE_ADD(day_date, INTERVAL 1 DAY) <= LAST_DAY(day_date)  -- 确保不超过本月最后一天
)
SELECT day_date,day_of_month,DAYNAME(day_date) AS day_name
FROM month_days;

24.2 获取本年的日期序列

WITH RECURSIVE year_days AS (-- 基础情况：选择本年的第一天SELECT DATE_FORMAT(CURDATE(), '%Y-01-01') AS day_date,  -- 本年的第一天1 AS day_of_yearUNION ALL-- 递归部分：生成接下来的每一天SELECT DATE_ADD(day_date, INTERVAL 1 DAY),day_of_year + 1FROM year_daysWHERE DATE_ADD(day_date, INTERVAL 1 DAY) <= DATE_FORMAT(CURDATE(), '%Y-12-31')  -- 确保不超过本年的最后一天
)
SELECT day_date,day_of_year,DAYNAME(day_date) AS day_name
FROM year_days;

24.3 获取本年的日期序列2

SELECT DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-01-01'),INTERVAL units.i+10*tens.i+100*hundreds.i DAY) AS day_date,units.i+10*tens.i+100*hundreds.i+1 AS day_of_year,DAYNAME(DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-01-01'),INTERVAL units.i+10*tens.i+100*hundreds.i DAY)) AS day_name FROM (
SELECT 0 AS i UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS units CROSS JOIN (
SELECT 0 AS i UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS tens CROSS JOIN (
SELECT 0 AS i UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS hundreds WHERE units.i+10*tens.i+100*hundreds.i<=DATEDIFF(DATE_FORMAT(CURDATE(),'%Y-12-31'),DATE_FORMAT(CURDATE(),'%Y-01-01')) ORDER BY day_of_year;

24.4 获取数字序列

WITH RECURSIVE numbers AS (-- 基础情况：从 1 开始SELECT 1 AS nUNION ALL-- 递归部分：每次递增 1SELECT n + 1FROM numbersWHERE n < 1000
)
SELECT n
FROM numbers;
-- 递归深度限制：MySQL 默认的递归深度是 1000 次。对于生成 1 到 10 万的数字序列，
-- 你需要调整 cte_max_recursion_depth 参数。可以通过以下命令临时调整递归深度
SET SESSION cte_max_recursion_depth = 100000;

24.5 根据传入的开始时间和结束时间生成日期序列，并左联构造出来的表

WITH RECURSIVE date_series AS (-- 基础部分：初始化序列的起始日期SELECT start_date AS current_date1,DATE_FORMAT(start_date, '%Y.%m') AS formatted_dateFROM (SELECT '2023-01-01' AS start_date, '2025-12-31' AS end_date) AS input_datesWHERE start_date <= end_dateUNION ALL-- 递归部分：生成下一个月份的日期SELECT DATE_ADD(current_date1, INTERVAL 1 MONTH) AS current_date1,DATE_FORMAT(DATE_ADD(current_date1, INTERVAL 1 MONTH), '%Y.%m') AS formatted_dateFROM date_seriesWHERE DATE_ADD(current_date1, INTERVAL 1 MONTH) <= (SELECT end_date FROM (SELECT '2025-03-01' AS start_date, '2025-12-31' AS end_date) AS input_dates)
),
mat_price AS (SELECT '2025-01-15' AS publish_date, 100.5 AS tax_exclude_amount, 0 AS del_flag, '2' AS types, 4401 AS city, 44 AS province, 17729 AS mat_stand UNION ALLSELECT '2025-01-20', 150.75, 0, '2', 4401, 44, 17729 UNION ALLSELECT '2024-12-30', 200.0, 0, '2', 4401, 44, 17729 UNION ALLSELECT '2024-12-25', 250.0, 0, '2', 4401, 44, 17729 UNION ALLSELECT '2023-03-10', 300.0, 0, '2', 4401, 44, 17729 UNION ALLSELECT '2023-03-15', 350.0, 0, '2', 4401, 44, 17729
)
SELECT ds.formatted_date,FORMAT(IFNULL(mp.price, 0),2) AS price,FORMAT(IFNULL(AVG(mp.price) OVER (ORDER BY ds.formatted_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0), 2) AS moving_avg_3_months
FROM date_series  as ds
LEFT JOIN (SELECT DATE_FORMAT(publish_date, '%Y.%m') AS yearMonth, FORMAT(IFNULL(AVG(tax_exclude_amount), 0), 2) AS price  FROM mat_price WHERE del_flag = 0 AND types = '2'  AND city = 4401 AND province = 44 AND mat_stand = 17729 AND publish_date >= '2023-01-01' AND publish_date <= '2025-12-31'GROUP BY yearMonth
) as mp ON ds.formatted_date = mp.yearMonth
ORDER BY ds.formatted_date;

25.获取当前几号

select DAYOFMONTH(CURDATE()) FROM dual;

26.获取当前日期

select CURDATE() FROM dual;

27.获取本月最后一天

select LAST_DAY(CURDATE()) FROM dual;

28.获取本月第一天

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY), '%Y-%m-%d') AS first_day_of_month;

获取当年第一天

	SELECT MAKEDATE(YEAR(CURDATE()), 1) AS first_day_of_first_half;SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') AS first_day_of_first_half;

29.使用with RECURSIVE 获取层级结构关系

-- 通过教育局id获取教育局层级
-- 获取当前教育局和上级所有教育局层级
WITH RECURSIVE education_hierarchy AS (SELECT edu_id, parent_idFROM t_education_infoWHERE edu_id = 'edu-1116361870136049664'UNION ALLSELECT t.edu_id, t.parent_idFROM t_education_info tINNER JOIN education_hierarchy eh ON t.edu_id = eh.parent_id
)
SELECT edu_id
FROM education_hierarchy;-- 重点在于关联条件的顺序
-- 获取当前教育局和下属教育局层级
WITH RECURSIVE education_hierarchy AS (SELECT edu_id, parent_idFROM t_education_infoWHERE edu_id = 'edu-1116361870136049664'UNION ALLSELECT t.edu_id, t.parent_idFROM t_education_info tINNER JOIN education_hierarchy eh ON t.parent_id = eh.edu_id
)
select edu_id,edu_name,classify,classify_name from t_education_info where  edu_id in  (
SELECT edu_id
FROM education_hierarchy) order by classify;

30.普通表表达式（CTE）common table expression

WITH子句递归查询实现组织层级、创建临时表、子查询。

/**这个SQL查询语句的作用是通过给定的教育局ID获取教育局的层级结构。首先，我们使用了MySQL的递归查询功能，并定义了一个递归查询表education_hierarchy。该表包含了教育局ID和其上级教育局的ID。递归查询的初始条件是给定的教育局ID，即edu_id = 'edu-1125739114860118016'。然后，通过递归地连接t_education_info表，我们获取上级教育局的ID。最后，我们从递归查询表中选择所有的教育局ID，即SELECT edu_id FROM education_hierarchy。执行这个查询语句，您将获得给定教育局ID的教育局层级结构 A common table expression普通表表达式（CTE）是一个命名的临时结果集，它存在于单个语句的范围内，并且可以在该语句的后面被引用，可能是多次引用。下面的讨论描述了如何编写使用CTE的语句。为了指定常见的表的表达式，可以使用一个有一个或多个逗号分隔的子句的WITH子句。每个子句提供了一个产生结果集的子查询，并将一个名称与子查询联系起来。下面的例子在WITH子句中定义了名为cte1和cte2的CTE，并在WITH子句后面的顶层SELECT中引用了它们：WITHcte1 AS (SELECT a, b FROM table1),cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;with 名称1 as (子句),名称2 as (子句)
select xxx from 名称1 INNER JOIN 名称2  where xxx ;WITH子句在MySQL中的妙用是可以创建递归查询，用于处理具有层级结构的数据。递归查询可以通过连接表自身来实现，从而实现对层级数据的查询和处理。递归查询通常使用WITH RECURSIVE关键字来定义一个递归查询表。在递归查询表中，我们可以定义初始条件和递归条件，然后通过递归地连接表自身来获取所需的结果。请注意，WITH子句还可以用于其他类型的查询，例如创建临时表或子查询。但在这个上下文中，我们主要关注递归查询的用法 cte_name命名了一个单独的普通表表达式，并可以在包含WITH子句的语句中作为表的引用。AS（子查询）的子查询部分被称为 "CTE的子查询"，是产生CTE结果集的原因。AS后面的括号是必须的。如果一个普通表表达式的子查询是指它自己的名字，那么它就是递归的。如果WITH子句中的任何CTE是递归的，必须包括RECURSIVE关键字。更多信息，请参阅递归普通表表达式。为一个给定的CTE确定列名的过程如下：如果在CTE名称后面有一个括号内的名称列表，这些名称就是列名：一个CTE可以引用自己，也可以引用其他CTE：一个自我引用的CTE是递归的。一个 CTE 可以引用同一 WITH 子句中先前定义的 CTE，但不能引用后来定义的 CTE。这个约束排除了相互递归的 CTE，即 cte1 引用 cte2，cte2 引用 cte1。其中一个引用必须是对后来定义的CTE的引用，这是不允许的。一个给定查询块中的CTE可以引用在更外层的查询块中定义的CTE，但不能引用在更内层的查询块中定义的CTE。为了解决对具有相同名称的对象的引用，派生表隐藏CTE；CTE隐藏基表、TEMPORARY表和视图。名称解析是通过在同一查询块中搜索对象来进行的，然后在没有找到具有该名称的对象时，依次进入外部块。像派生表一样，在MySQL 8.0.14之前，CTE不能包含外部引用。这是MySQL的限制，在MySQL 8.0.14中被取消了，而不是SQL标准的限制。关于递归CTE的额外语法考虑，请参见递归公共表表达式。第一个SELECT为CTE产生最初的一条或多条记录，并且不引用CTE名称。第二个SELECT产生额外的记录，并且通过在FROM子句中引用CTE名称来进行递归。当这个部分没有产生新的记录时，递归就结束了。因此，一个递归CTE由一个非递归SELECT部分和一个递归SELECT部分组成。每个SELECT部分本身可以是多个SELECT语句的联合。CTE结果列的类型只从非递归SELECT部分的列类型中推断出来，并且这些列都是可空的。对于类型的确定，递归SELECT部分被忽略了。如果非递归和递归部分被UNION DISTINCT分开，重复的行将被消除。这对于执行递归封闭的查询很有用，可以避免无限循环。递归部分的每个迭代只对前一个迭代产生的记录进行操作。如果递归部分有多个查询块，每个查询块的迭代将以未指定的顺序安排，每个查询块对其前一次迭代或前一次迭代结束后的其他查询块产生的记录进行操作。WITH RECURSIVE cte (n) AS
(SELECT 1UNION ALLSELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;前面显示的递归CTE子查询有一个非递归的部分，它检索了一条记录来产生初始行集：在每个迭代中，SELECT产生一个新的值，比前一个行集的n的值大1。第一次迭代在初始行集（1）上操作，产生1+1=2；第二次迭代在第一次迭代的行集（2）上操作，产生2+1=3；以此类推。这样一直持续到递归结束，当n不再小于5的时候，递归就结束了。如果CTE的递归部分比非递归部分产生更宽的列值，可能有必要在非递归部分扩大该列以避免数据截断。考虑一下这个语句：【WITH子句在MySQL中的妙用是可以创建递归查询，用于处理具有层级结构的数据】。递归查询可以通过连接表自身来实现，从而实现对层级数据的查询和处理。递归查询通常使用WITH RECURSIVE关键字来定义一个递归查询表。在递归查询表中，我们可以定义初始条件和递归条件，然后通过递归地连接表自身来获取所需的结果。*/
-- 打印我要好好学习100次
WITH RECURSIVE luoxiaomin (n,study) AS
(SELECT 1,'好好学习' as studyUNION ALLSELECT n + 1,'好好学习' as study FROM luoxiaomin WHERE n < 100
)
SELECT * FROM luoxiaomin;

示例

-- 查询任务,下单数据不能全部为0
set @var=0;
select * from (select *,@var:=date_task_id, (select max(task_num)-(select sum(task_num) from date_details_task where  	   date_task_id=@var and task_type !=2)  from  date_details_task where date_task_id=@var ) as otherfrom date_details_task where task_num=1 and task_type=5 and finish_num !=1 and DATE_FORMAT(create_time,'%Y-%m-%d') in ('2020-11-21')
) as a where a.other <=0

select *,ROW_NUMBER() OVER(Order by a.薪水) AS 排名  from table as a order by a.薪水set @a=0;
select concat('第',(@a:=@a+1),'名') as sort ,姓名,薪水 from 表 order by 薪水;

31.mybatis批量插入:INSERT INTO…ON DUPLICATE KEY UPDATE

  <!-- 在MyBatis中执行批量插入，可以使用foreach元素迭代对象集合，逐个插入--><insert id="batchInsert" parameterType="java.util.List">INSERT INTO t_school_act_check_conf (school_id, snow_flake_id, area_type, need_first_dept, need_second_dept, need_third_dept, first_education_id,first_education_name, second_education_id,second_education_name, third_education_id,third_education_name, create_time, update_time)VALUES<foreach collection="list" item="item" separator=",">(#{item.schoolId}, #{item.snowFlakeId}, #{item.areaType}, #{item.needFirstDept}, #{item.needSecondDept}, #{item.needThirdDept}, #{item.firstEducationId},#{item.firstEducationName}, #{item.secondEducationId},#{item.secondEducationName}, #{item.thirdEducationId},#{item.thirdEducationName}, #{item.createTime}, #{item.updateTime})</foreach></insert>

这两种写法都可以实现批量插入并在存在时更新、不存在时插入的操作。第一种写法使用了foreach标签来循环处理每个元素，而第二种写法则是直接将整个列表作为参数进行插入操作。通常来说，第一种写法更适合处理大批量数据，因为它可以将多个插入语句合并为一个较大的语句。而第二种写法更适合处理少量数据，因为它更直观，易于理解和维护，性能更好
第二种写法更好：注意两种写法的separator元素值的写法separator=";"和separator=","<insert id="batchInsertOrUpdateScheduleClass" parameterType="java.util.List"><foreach collection="list" item="item" index="index" separator=";">INSERT INTO t_bas_schedule_class (schedule_id, class_id, class_name, is_delete, is_enable, create_time, update_time)VALUES (#{item.scheduleId}, #{item.classId}, #{item.className}, #{item.isDelete}, #{item.isEnable}, #{item.createTime}, #{item.updateTime})ON DUPLICATE KEY UPDATEclass_name = VALUES(class_name), is_delete = VALUES(is_delete), is_enable = VALUES(is_enable), update_time = VALUES(update_time)</foreach>
</insert><insert id="batchInsertOrUpdate" parameterType="java.util.List">INSERT INTO t_bas_schedule_class (schedule_id, class_id, class_name, is_delete, is_enable, create_time, update_time)VALUES<foreach collection="list" item="item" separator=",">(#{item.scheduleId}, #{item.classId}, #{item.className}, #{item.isDelete}, #{item.isEnable}, #{item.createTime}, #{item.updateTime})</foreach>ON DUPLICATE KEY UPDATEclass_name = VALUES(class_name), is_delete = VALUES(is_delete), is_enable = VALUES(is_enable), update_time = VALUES(update_time)
</insert>

32. in多个列的写法

-- in多个列的写法,多个列用()包裹起来，中间用逗号分割【where的字段多个列用()包裹起来，中间用逗号分割】【精确匹配】
-- 格式如下：
SELECT * FROM your_table WHERE (column1, column2) IN ((value1, value2), (value3, value4), (value5, value6));SELECT * FROM t_stu_eva_norm where (student_id,act_course_id) in (('stu-1131241147339898882','1144289496976588800'),('stu-1131241147352481792','1144289496976588800'));

33.mysql 生成列【就是表中一列是通过其它动态生成的】

-- 创建一个直角三角形表，边长为a,b 那么斜边 就是c ,动态生成【在创建表字段的同时就指定好计算规则】
CREATE TABLE triangle (sidea DOUBLE,sideb DOUBLE,sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

34.窗口函数【参考mysql窗口函数（Window Functions）详解】

窗口函数是 SQL 中的一种强大工具，它允许你在不改变原始表行数的情况下对数据进行聚合计算。与传统的 GROUP BY 不同，窗口函数不会将多行合并为一行，而是可以在每一行上应用聚合操作，同时保留原始的行结构。这使得窗口函数非常适合用于复杂的分析任务，例如：

• 移动平均：计算某个时间段内的平均值。
• 排名：为每行数据分配一个排名。
• 累计和：计算到当前行为止的累计总和。
• 前后行比较：比较当前行与前几行或后几行的数据。

1. 窗口函数的基本语法

窗口函数的通用语法如下：

window_function(expression) OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression] [frame_clause] )

• window_function：这是你想要应用的聚合函数或排名函数，例如 SUM()、AVG()、ROW_NUMBER()、RANK() 等。
• expression：要应用于窗口函数的列或表达式。
• OVER：指定窗口的范围，告诉 SQL 引擎如何划分数据并应用窗口函数。
• PARTITION BY：可选。类似于 GROUP BY，但它不会减少行数。它将数据划分为多个分区（或“窗口”），并在每个分区内独立应用窗口函数。
• ORDER BY：可选。指定窗口内数据的排序顺序。这对于需要按顺序处理的函数（如 ROW_NUMBER() 或 LAG()）非常重要。
• frame_clause：可选。定义窗口的“框架”，即窗口函数作用的行范围。你可以指定从当前行向前或向后扩展的行数。

窗口函数（Window Functions）允许你在不改变行数的情况下对数据进行聚合计算。常见的窗口函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER() 等。

例如，计算每个用户的累计订单金额：

SELECT user_id, order_id, order_amount, SUM(order_amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_amount
FROM orders;

• 解释
  • SUM(order_amount) OVER (PARTITION BY user_id ORDER BY order_date) 计算每个用户的累计订单金额，按 order_date 排序。

34.1 排名及排序

SELECTval,ROW_NUMBER() OVER w AS '排名序号',RANK() OVER w AS '稀疏排名',DENSE_RANK() OVER w AS '密集排名' 
FROM (
SELECT 1 AS val UNION ALL
SELECT 3 AS val UNION ALL
SELECT 5 AS val UNION ALL
SELECT 4 AS val UNION ALL
SELECT 4 AS val UNION ALL
SELECT 8 AS val UNION ALL
SELECT 9 AS val UNION ALL
SELECT 7 AS val UNION ALL
SELECT 6 AS val) AS sub_table
WINDOW w AS (ORDER BY val);-- 结果
val	排名序号	稀疏排名	密集排名
1	1	1	1
3	2	2	2
4	3	3	3
4	4	3	3
5	5	5	4
6	6	6	5
7	7	7	6
8	8	8	7
9	9	9	8																																																																																														

35.mysql排序

1. 常规排序：
- 按照某一列的升序排序：SELECT * FROM table_name ORDER BY column_name ASC;
- 按照某一列的降序排序：SELECT * FROM table_name ORDER BY column_name DESC;
- 按照多个字段的优先级进行排序：SELECT * FROM table_name ORDER BY column_name1 DESC, column_name2 ASC;3. 指定字段值顺序排序：
- 数据库中name列前三行 为"John","Bob", "Alice"
现在，我们想按照指定的顺序对name进行排序，可以使用FIELD()函数来实现。例如，我们希望按照"Bob", "Alice", "John"的顺序进行排序，可以使用以下查询：
SELECT id, name FROM users ORDER BY FIELD(name, 'Bob', 'Alice', 'John');4. 随机排序：
- 随机排序整个结果集：SELECT * FROM table_name ORDER BY RAND();
- 随机排序前N条记录：SELECT * FROM table_name ORDER BY RAND() LIMIT N;

FIELD()函数的语法如下：

FIELD(expr, value1, value2, …)

其中，expr是要进行排序的表达式，value1, value2, …是要进行比较的值。

FIELD()函数的工作原理是，它会按照参数列表中的顺序逐个比较expr和每个值，返回第一个匹配的值的位置。如果没有匹配的值，则返回0。

以下是一个示例，说明FIELD()函数的使用：

假设有一个名为users的表，包含id和name两列，数据如下：

±—±------+

| id | name |

±—±------+

| 1 | John |

| 2 | Alice |

| 3 | Bob |

±—±------+

现在，我们想按照指定的顺序对name进行排序，可以使用FIELD()函数来实现。例如，我们希望按照"Bob, Alice, John"的顺序进行排序，可以使用以下查询：

SELECT id, name

FROM users

ORDER BY FIELD(name, ‘Bob’, ‘Alice’, ‘John’);

执行上述查询后，结果将按照指定的顺序进行排序：

±—±------+

| id | name |

±—±------+

| 3 | Bob |

| 2 | Alice |

| 1 | John |

±—±------+

在上述示例中，FIELD(name, ‘Bob’, ‘Alice’, ‘John’)将按照指定的顺序比较name列的值，并返回相应的位置，然后根据该位置进行排序。

需要注意的是，FIELD()函数返回的位置是基于1的索引，如果没有匹配的值，则返回0

36.mysql 年月日时分秒与秒数互转

-- 将日期：年月日时分秒转为秒数
select UNIX_TIMESTAMP("1970-01-01 00:00:00");
-- 将秒数转为年月日时分秒
select FROM_UNIXTIME(0);

37.临时修改mysql为北京时间

select NOW();
SELECT @@session.time_zone;				SET time_zone = '+8:00';
select NOW();	

38.mysql获取图片路径前缀

face=‘https://img.xx.com/file/1023182503734673408.jpg’

#获取图片前缀https://img.xx.com/file/
select SUBSTR(face,1,LOCATE(SUBSTRING_INDEX(face, '/', -1),face)-1) as prefix;
# 按照图片前缀分组
select  SUBSTR(face,1,LOCATE(SUBSTRING_INDEX(face, '/', -1),face)-1) as prefix FROM  `t_user_info` where face is not null and face !='' GROUP BY prefix  

39. mysql 传过来的是个日期范围，数据库也是个日期范围的查询

//传过来的是个日期范围，数据库也是个日期范围，可以使用逆向思维，判断是否有重叠来解决,  
如果 publish_date_end 小于 startDate 或者 publish_date_start 大于 endDate，则没有重叠；否则，有重叠
// 添加 NOT (publish_date_end < startDate OR publish_date_start > endDate) 条件,
在mysql 中取反可以使用not来解决，可以理解为两个圆相离，取反则为相切或部分重叠

sql 语句优化

0：mysql语句执行顺序

在MySQL中，SQL语句的执行顺序如下：1. FROM：指定要查询的表或视图。
2. JOIN：将多个表连接在一起。
3. WHERE：对查询结果进行筛选。
4. GROUP BY：按照指定的列对结果进行分组。
5. HAVING：对分组后的结果进行筛选。
6. SELECT：选择要查询的列。
7. DISTINCT：去除重复的行。
8. ORDER BY：对结果进行排序。
9. LIMIT：限制返回的结果数量。需要注意的是，这只是一般情况下的执行顺序，实际执行顺序可能会受到查询优化器的影响。优化器可能会对查询进行重写或重新排序，以提高查询性能。此外，还有一些特殊情况下的执行顺序，例如子查询、联合查询等，它们的执行顺序可能会有所不同。总结来说，MySQL中SQL语句的执行顺序是从FROM开始，依次经过JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY和LIMIT。但实际执行顺序可能会受到查询优化器的影响 【注意】
在mysql中,group by,having,order by 中可以使用别名;where中不能使用别名;其余像oracle,hive中别名的使用都是严格遵循sql执行顺序的，groupby后面不能用别名。【mysql特殊是因为mysql中对查询做了加强】

1：查询表中的信息

show full COLUMNS from task ;
[desc|DESCRIBE] task;-- 查看建表结构desc t_share_course-- 查看建表结构describe t_share_course-- 查看建表结构show columns from t_share_course;-- 查看建表语句SHOW CREATE TABLE t_share_course;-- 正式环境
-- 查看建表结构 包含字段备注说明
-- table_schema 后面写数据库名，table_name后面写表名
SELECT column_name, column_comment, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_schema = 'xx' AND table_name = 't_share_course';-- 测试环境
-- 查看建表结构 包含字段备注说明
-- table_schema 后面写数据库名，table_name后面写表名
SELECT column_name, column_comment, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_schema = 'xx_rec' AND table_name = 't_share_course';

• Key

  列是否被索引：

  • 如果Key为空，则该列要么不被索引，要么仅作为多列非唯一索引中的第二列被索引。
  • 如果Key为 PRI，PRIMARY KEY则列为a或为多列中的列之一PRIMARY KEY。
  • 如果Key为UNI，则该列为UNIQUE索引的第一列 。（ UNIQUE索引允许多个 NULL值，但是您可以NULL通过检查Null字段来判断该列是否允许。）
  • 如果Key为MUL，则该列是非唯一索引的第一列，在该列中允许多次出现给定值。

  如果超过一个Key值适用于表中给定的列，Key 显示一个具有最高优先级，顺序 PRI，UNI， MUL。

  甲UNIQUE索引可被显示为 PRI，如果它不能包含 NULL值并没有 PRIMARY KEY在表中。甲 UNIQUE索引可能会显示为 MUL如果若干列形成复合 UNIQUE索引; 尽管各列的组合是唯一的，但各列仍可以容纳多次出现的给定值。

• Default

  列的默认值。这是 NULL如果列有一个明确的默认NULL，或者如果列定义不包括DEFAULT条款。

• Extra

  有关给定列的任何其他可用信息。在以下情况下，该值是非空的：

  • auto_increment具有AUTO_INCREMENT属性的列。
  • on update CURRENT_TIMESTAMP对于 TIMESTAMP或 DATETIME有列ON UPDATE CURRENT_TIMESTAMP 属性。
  • VIRTUAL GENERATED或VIRTUAL STORED用于生成的列。
  • DEFAULT_GENERATED 对于具有表达式默认值的列。

表列信息也可从 INFORMATION_SCHEMA COLUMNS表中获得。请参见 第26.8节“ INFORMATION_SCHEMA列表”。有关隐藏列的扩展信息仅可使用 SHOW EXTENDED COLUMNS；它不能从COLUMNS表中获得。

2:in、exists、left join 小表驱动大表

类似于嵌套循环：
for(int i=5;.......){for(int j=1000;......){}
}
如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。in后面跟的是小表，exists后面跟的是大表。
简记：in小，exists大。in、exists、left join 可以相互转换
IN/ALL/ANY/SOME 子查询不支持limit语句

2.1使用exists替代left join

explain SELECT count(DISTINCT a.id)
FROM task a# left JOIN date_task b ON a.id = b.task_id
WHERE a.os_type = 5AND a.status IN (2, 3, 4, 5, 6, 7)#and b.task_date >= '2021-05-01' and '2021-06-01'> b.task_dateand EXISTS(select 1 from date_task b where a.id = b.task_id and b.id=575097)and EXISTS(select 1 from date_task b where a.id = b.task_id and b.task_date >= '2021-06-01' and '2021-07-01'> b.task_date)