mysql
工作7年,mysql使用心得
- mysql
-
- 1.创建变量
- 2.创建存储过程
- 3.三种注释写法
- 4.case when then else end 写法
- 5.if相关方法
- 6.if语句
- 7.创建方法
- 8.删除重复数据只保留最小id
- 9.[`INSERT INTO ... SELECT`](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html)标准的SQL语法
- 10.SQL查询每个用户首次购买商品
- 11.update
- 12.内连接inner join 和左连接left join
- 13.等值连接和非等值连接
- 看到【有】就联想到inner join ,看到【没有】就联想到left join
- 在什么情景下,我们应该考虑非等值连接?
- 14.自连接
- 15.jpa
- 16.mybatis plus
- 17.JdbcTemplate 批量操作
- 18.月份不足两位补零
- 19.创建连续日期
- 20.创建随机函数
- 21. mysql有哪些系统表,分别有什么作用?
- 22.mysql分割字符串
- 23.修改表的AUTO_INCREMENT
- 24.使用with RECURSIVE 生成序列
- 25.获取当前几号
- 26.获取当前日期
- 27.获取本月最后一天
- 28.获取本月第一天
- 获取当年第一天
- 29.使用with RECURSIVE 获取层级结构关系
- 30.普通表表达式(CTE)common table expression
- 示例
- 31.mybatis批量插入:INSERT INTO...ON DUPLICATE KEY UPDATE
- 32. in多个列的写法
- 33.mysql 生成列【就是表中一列是通过其它动态生成的】
- 34.窗口函数【参考mysql窗口函数(Window Functions)详解】
- 35.mysql排序
- 36.mysql 年月日时分秒与秒数互转
- 37.临时修改mysql为北京时间
- 38.mysql获取图片路径前缀
- 39. mysql 传过来的是个日期范围,数据库也是个日期范围的查询
- sql 语句优化
-
- 0:mysql语句执行顺序
- 1:查询表中的信息
- 2:in、exists、left join 小表驱动大表
- 3:mysql对T-SQL标准的扩展
- 4:mysql 索引
- 5:like优化
- 6.优化过程optimizer trace
- 7.性能瓶颈定位
- 8.sql mode
- 9.mysql 变量、状态 、连接数
- 10.HikariCP数据库连接池常用参数
- max-lifetime的作用
- max-lifetime和idle-timeout有什么区别?
- max-lifetime和idle-timeout分别和数据库的什么配置参数有关?
- 如果在程序中,max-lifetime配置了30分钟,数据库服务器wait_timeout配置了1小时,以哪个为准?
- maximum-pool-size与数据库中的什么配置有关?
- spring.datasource.jdbc-url连接字符串的连接参数
- 11.mybatis中文等值判断
- 12.mybatis批量更新
- 执行计划EXPLAIN
- 口诀
- 优化指南
- 使用performance_schema
- 使用information_schema
- 使用 show full processlist
- performance_schema.events_statements_current的每个字段的含义
- 批量查询连接进程id
- performance_schema.events_statements_current的每个字段的含义
- 批量查询连接进程id
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
是一个非等值连接条件,表示订单的日期必须在促销活动的开始日期和结束日期之间。这种类型的查询非常适合处理时间范围、价格区间等场景。