后端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 和存储过程语句中，否则创建变量直接用set
	WHILE i < 100 DO
		SET 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 和存储过程语句中，否则创建变量直接用set
	WHILE i <=90 DO
		INSERT 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)
  begin
       declare 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:loop
          if 条件 THEN -- 满足条件时离开循环
               leave loop_name;  -- 和 break 差不多都是结束循环
       end if；
  end loop;
  */
-- 删除存储过程
drop procedure if exists  sums;
DELIMITER //
create procedure sums(a int)
begin
        declare sum int default 0;
        declare i int default 1;
        loop_name:loop -- 循环开始
            if i>a then 
                leave loop_name;  -- 判断条件成立则结束循环  好比java中的 break
            end 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 函数名()）
begin
    declare 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的值设置为1
    declare 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))
BEGIN
    SET 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 t2
		set 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`()
begin
    declare _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的值设置为1
    declare 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 THEN
					    INSERT 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 THEN
							INSERT 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_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
#写法二：适合单值匹配，也适合多值匹配
CASE
    WHEN 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 shopUp
 from 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))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET 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 type

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    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)

  BEGIN
    DECLARE 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.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND 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 JOIN
 SELECT * 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 是一个非等值连接条件，表示订单的日期必须在促销活动的开始日期和结束日期之间。这种类型的查询非常适合处理时间范围、价格区间等场景。