MYSQL思维进阶_1

使用测试工具

1. docker部署的mysql:5.7
2. 创建名为 [test] 的数据库:
   CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

下面有用到的知识点 : sql 变量和函数

-- 1.在set里赋值可以把：冒号省略  /   在select里赋值必须有：冒号;
SET @name:='fengjingan';
SELECT @name;

SELECT @age:='14';

-- 2.
IFNULL(expr1, expr2)
expr1不为Null, 则IFNULL()的返回值为expr1; 否则其返回值为expr2

-- 3.
IF(条件, expr1, expr2)
如果条件成立, 则返回expr1 , 否则是expr2

-- 4.
DATEDIFF(expr1,expr2)
相隔时间天数
DATEDIFF('2023-4-6','2023-4-5') = 1

-- 5.
GROUP BY
SELECT col1, col2 FROM tablename GROUP BY col1, col2 ORDER BY col1, col2
使用group by容易出现" this is incompatible with sql_mode=only_full_group_by" 报错
MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，
除了使用聚合函数之外的，如max()、min()、sum()、GROUP_CONCAT()...等，都必须出现在group by中。

-- 6.
GROUP_CONCAT(DISTINCT expression 
ORDER BY expression ASC/DESC 
SEPARATOR '|')
通常和group by 一起使用,把相同的分组的字段值连接起来

-- 7.
ON DUPLICATE KEY UPDATE (特有语法)
一般跟在insert后面出现. 如果insert会导致UNIQUE唯一索引或PRIMARY KEY中出现重复值, 则在出现重复值的行执行UPDATE

-- 8.
CASE WHEN 往往用于select 查询时 对字段进行特殊条件处理
case
when 表达式 then 表达式
else 表达式
end

-- 9.
update 语句也是可以有多个表进行关联
UPDATE user_score a INNER JOIN( __AAA__ ) b ON a.user_name = b.user_name
SET a.user_score=a.user_score + (b.mp*0.1)

1. 实现select 取数行号

-- 创建products表结构
CREATE TABLE `products` (
  `p_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `p_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `p_type` varchar(255) DEFAULT NULL COMMENT '商品分类',
  `p_view` int DEFAULT NULL COMMENT '商品点击量',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB;

-- 插入一些数据
insert into products(p_name,p_type,p_view) values('鲅鱼','海鲜类','43');
insert into products(p_name,p_type,p_view) values('白果','干果类','78');
insert into products(p_name,p_type,p_view) values('多宝鱼','海鲜类','6');
insert into products(p_name,p_type,p_view) values('瓜子','干果类','345');
insert into products(p_name,p_type,p_view) values('老板鱼','海鲜类','47');
insert into products(p_name,p_type,p_view) values('梨','水果类','43');
insert into products(p_name,p_type,p_view) values('毛巾','其他类','178');
insert into products(p_name,p_type,p_view) values('猕猴桃','水果类','56');
insert into products(p_name,p_type,p_view) values('沐浴露','其他类','456');
insert into products(p_name,p_type,p_view) values('南瓜子','干果类','6');
insert into products(p_name,p_type,p_view) values('圣女果','水果类','3');
insert into products(p_name,p_type,p_view) values('石浆鱼','海鲜类','44');
insert into products(p_name,p_type,p_view) values('头巾','其他类','97');
insert into products(p_name,p_type,p_view) values('拖鞋','其他类','44');
insert into products(p_name,p_type,p_view) values('西红柿','水果类','5');
insert into products(p_name,p_type,p_view) values('先生鱼','海鲜类','69');
insert into products(p_name,p_type,p_view) values('香蕉','水果类','25');
insert into products(p_name,p_type,p_view) values('香皂','其他类','1');
insert into products(p_name,p_type,p_view) values('小姐鱼','海鲜类','74');
insert into products(p_name,p_type,p_view) values('杏仁','干果类','4');
insert into products(p_name,p_type,p_view) values('雪莲果','水果类','45');
insert into products(p_name,p_type,p_view) values('浴巾','其他类','23');
insert into products(p_name,p_type,p_view) values('榛子','干果类','34');

需求: 按点击率 (p_view)排序, 并从大到小 排号(row_num)

-- 如果使用ifnull来实现 , 会有个问题: 多次执行时,rownum会不断累加上去, 不会重置行号
select p_name,p_type,p_view,IFNULL(@rownum:=@rownum+1,@rownum:=1) as rownum 
from products order by p_view desc
-- __AAA__   在关联表中 再加入一张别名为b的表, 则能解决问题
select p_name,p_type,p_view,@rownum:=@rownum+1 as row_num from products a, (SELECT @rownum:=0) b 
order by p_view desc

__AAA__结果如下:

2.分组后在分组内排序,每个分组中取前N条

-- __AAA__   根据p_type , p_view 来分组排序, 给出行号
SELECT p_type, p_name,p_view FROM products ORDER BY p_type,p_view DESC
-- __BBB__
SELECT p_type, p_name,p_view,
IF(@baktype=p_type, @rownum:=@rownum+1, @rownum:=1) as row_num, @baktype:=p_type
FROM ( __AAA__ ) a, (SELECT @rownum:=0, @baktype:='') b
-- __CCC__   取每个分类点击量最多的前2条
SELECT p_type, p_name,p_view, row_num FROM ( __BBB__ ) c WHERE c.row_num <=2;

结果如下图:

3. 计算商品评分、及时补货

首先增加一张表(商品销售汇总表), 注意: 上一张中并不是所有商品都有销量

CREATE TABLE `products_sales` (
  `p_id` int NOT NULL COMMENT '商品ID',
  `p_sales` int NOT NULL COMMENT '销量',
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB;

-- 插入一些数据
INSERT INTO `products_sales` VALUES (4, 6);
INSERT INTO `products_sales` VALUES (5, 141);
INSERT INTO `products_sales` VALUES (6, 162);
INSERT INTO `products_sales` VALUES (8, 93);
INSERT INTO `products_sales` VALUES (10, 74);
INSERT INTO `products_sales` VALUES (12, 167);
INSERT INTO `products_sales` VALUES (14, 82);
INSERT INTO `products_sales` VALUES (19, 138);
INSERT INTO `products_sales` VALUES (20, 176);
INSERT INTO `products_sales` VALUES (21, 37);

可选需求:
各分类根据p_type和p_name排序后，提取的前2名商品 , 向products_sales表插入一些0-150的随机数值

-- __AAA__   与 第二项(上方)的__BBB__差不多, 只是多了p_id
SELECT p_id, p_type, p_name,p_view,
IF(@baktype=p_type, @rownum:=@rownum+1, @rownum:=1) as row_num, @baktype:=p_type 
FROM 
(SELECT p_id, p_type, p_name,p_view FROM products ORDER BY p_type,p_view DESC) a, 
(SELECT @rownum:=0, @baktype:='') b 
-- __BBB__  批量插入
INSERT INTO products_sales SELECT p_id, FLOOR(RAND()*150) FROM ( __AAA__ ) c WHERE c.row_num <=2 

需求:
根据分类求 有销量的商品的 平均值

-- __AAA__
SELECT a.p_type, a.p_name, a.p_view, IFNULL(b.p_sales,0) as sales FROM products a 
LEFT JOIN products_sales b on a.p_id=b.p_id ORDER BY a.p_type, a.p_view DESC
-- __BBB__    过滤没有销量的商品 WHERE a.sales > 0
SELECT p_type, ROUND(SUM(sales)/COUNT(*), 0) as sales_avg FROM( __AAA__ ) a WHERE a.sales > 0 GROUP BY p_type

结果如下图:

需求:生成商品的评分列, 为了更好的找到商品的优劣, 市场的需求
点击率 和 销售量 作为评分系数

-- __CCC__
SELECT p_type, ROUND(SUM(p_view)/COUNT(*), 0) as view_avg FROM products GROUP BY p_type
-- 联合后
SELECT a.p_type, a.p_name, a.p_view, c.view_avg, a.sales, b.sales_avg , 
(a.p_view/c.view_avg)*0.2 + (a.sales/b.sales_avg)*0.8 AS score FROM
(__AAA__) a,
(__BBB__) b,
(__CCC__) c
WHERE a.p_type = b.p_type AND a.p_type=c.p_type

结果如下图:

4. mysql中自连接查询的妙用: 推荐人统计

CREATE TABLE `users` (
  `u_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `u_name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
  `p_id` int DEFAULT NULL COMMENT '推荐人的u_id',
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB;

-- 插入一些数据到users表
INSERT INTO users(u_id,u_name,p_id) VALUES(1,'张三','0');
INSERT INTO users(u_id,u_name,p_id) VALUES(2,'大胖胖','0');
INSERT INTO users(u_id,u_name,p_id) VALUES(3,'李四','2');
INSERT INTO users(u_id,u_name,p_id) VALUES(4,'大长脸','2');
INSERT INTO users(u_id,u_name,p_id) VALUES(5,'小朱','1');
INSERT INTO users(u_id,u_name,p_id) VALUES(6,'小狗','5');
INSERT INTO users(u_id,u_name,p_id) VALUES(7,'刘九','5');

-- __AAA__
SELECT a.u_id, a.u_name AS username, b.u_name AS referee FROM users a INNER JOIN users b ON a.p_id = b.u_id
-- __BBB__   在同张表中提取推荐人的名字
SELECT a.u_id, a.u_name, a.p_id, IFNULL(b.referee,"") AS referee FROM users a 
LEFT JOIN (__AAA__) b on a.u_id = b.u_id

结果如下图:

-- __AAA__
SELECT GROUP_CONCAT(u_name SEPARATOR '|') AS username , p_id FROM users GROUP BY p_id 
-- __BBB__
SELECT a.username, b.u_name AS referee FROM (__AAA__) a 
LEFT JOIN users b on a.p_id = b.u_id

结果如下图:

5.找到重复数据, 并删除

CREATE TABLE `reviews` (
  `r_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `r_content` varchar(255) DEFAULT NULL COMMENT '评论内容',
  `r_userid` int DEFAULT NULL COMMENT '评论人id',
  `news_id` int DEFAULT NULL COMMENT '对应的新闻id',
  PRIMARY KEY (`r_id`)
) ENGINE=InnoDB;

-- 插入一些数据
insert into reviews(r_id,r_content,r_userid,news_id) values(1,'很好的文章','102',6);
insert into reviews(r_id,r_content,r_userid,news_id) values(2,'不错啊','101',5);
insert into reviews(r_id,r_content,r_userid,news_id) values(3,'作者用心了','103',5);
insert into reviews(r_id,r_content,r_userid,news_id) values(4,'顶赞','102',7);
insert into reviews(r_id,r_content,r_userid,news_id) values(5,'不错啊','101',5);
insert into reviews(r_id,r_content,r_userid,news_id) values(6,'不错啊','101',5);
insert into reviews(r_id,r_content,r_userid,news_id) values(7,'写的不错','105',7);
insert into reviews(r_id,r_content,r_userid,news_id) values(8,'很好的文章','102',6);
insert into reviews(r_id,r_content,r_userid,news_id) values(9,'很好的文章','102',6);
insert into reviews(r_id,r_content,r_userid,news_id) values(10,'知道了','108',11);

-- __AAA__		 having 是对group by 处理后的数据再一次进行过滤
SELECT r_content, r_userid, COUNT(*) AS num FROM reviews GROUP BY r_content, r_userid HAVING num > 1
-- __BBB__     自连接
SELECT a.r_id, CONCAT(a.r_content,a.r_userid) AS cont_str FROM reviews a 
INNER JOIN ( __AAA__ ) b on a.r_content = b.r_content and a.r_userid = b.r_userid
ORDER BY cont_str, a.r_id
-- __CCC__
SELECT a.* , IF(@str=a.cont_str,@rownum:=@rownum+1,@rownum:=1) AS num, @str:=cont_str 
FROM ( __BBB__ ) a, (SELECT @rownum:=0, @str:='') b
-- __DDD__
SELECT a.r_id FROM ( __CCC__ ) a WHERE num > 1

--  排除重复数据中id最小的评论 , 把其余的都删了
DELETE FROM reviews WHERE r_id in ( __DDD__ );

结果如下图:

6.有重复数据不插入或更新的处理方法

-- 创建news表 , 对news_code 加上唯一索引
CREATE TABLE `news` (
  `news_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `news_title` varchar(255) DEFAULT NULL COMMENT '新闻标题',
  `news_abstract` varchar(255) DEFAULT NULL COMMENT '新闻摘要',
  `news_code` varchar(255) DEFAULT NULL COMMENT 'md5冗余码',
  `dupnum` int DEFAULT '0' COMMENT '用来记录重复的次数',
  PRIMARY KEY (`news_id`),
  UNIQUE KEY `news_code` (`news_code`) USING BTREE
) ENGINE=InnoDB;

INSERT INTO news(news_title, news_abstract, news_code) VALUES('这是一条java技术资讯', 'java技术资讯相关的新闻摘要', MD5(CONCAT('这是一条java技术资讯','java技术资讯相关的新闻摘要')));

-- 当新闻被重复插入时, 我们需要统计次数, 当不断插入相同数据时, dupnum会不断累加+1
INSERT INTO news(news_title, news_abstract, news_code) VALUES('这是一条java技术资讯', 'java技术资讯相关的新闻摘要', MD5(CONCAT('这是一条java技术资讯','java技术资讯相关的新闻摘要'))) 
ON DUPLICATE KEY UPDATE dupnum=dupnum+1

结果如下:

-- 创建user_info表, user_name为唯一索引
CREATE TABLE `user_info` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `user_qq` varchar(255) DEFAULT NULL COMMENT '用户qq',
  `updatetime` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB;

-- 插入一条数据
insert into user_info(user_name, user_qq) values('flanders', '123456')
-- 当插入数据user_name重复时, 会更新updatetime, user_qq两列
insert into user_info(user_name, user_qq) values('flanders', '654321') 
on duplicate key update updatetime=now(), user_qq=values(user_qq);

7.更新数据技巧 – update表子查询, 多条件判断

CREATE TABLE `user_level` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `user_total` decimal(10,2) DEFAULT NULL COMMENT '消费总金额',
  `user_rank` varchar(10) DEFAULT '' COMMENT '用户等级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入一些数据
insert into user_level(user_name,user_total,user_rank) values('张三',5,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('李四',30.3,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('赵龙',22,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('王五',489,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('刘飞',175,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('王菲',87,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('章子怡',101,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('陈晨',20,'吃瓜');
insert into user_level(user_name,user_total,user_rank) values('老蒋',11,'吃瓜');

对用户进行等级更新, 需求是:
1.只对超过平均消费金额的用户进行等级升级
2.达到平均消费金额1部的用户 等级是白金用户
3.达到2倍及以上的是黄金用户
4.其他一律是吃瓜用户

-- __AAA__
SELECT *, ROUND(user_total/b.avgtotal) as avgs,
CASE 
	WHEN ROUND(user_total/b.avgtotal) >=1 AND ROUND(user_total/b.avgtotal) <2 THEN '白金用户'
	WHEN ROUND(user_total/b.avgtotal) >=2 THEN '黄金用户'
	ELSE '吃瓜'
END  AS avg_c
FROM user_level,  (SELECT avg(user_total) AS avgtotal FROM user_level) b;

-- __BBB__ 更新等级信息  UPDATE user_level INNER JOIN (SELECT avg(user_total.... 相当于
UPDATE user_level, (SELECT avg(user_total) AS avgtotal FROM user_level) b SET user_rank=
CASE 
	WHEN ROUND(user_total/b.avgtotal) >=1 AND ROUND(user_total/b.avgtotal) <2 THEN '白金用户'
	WHEN ROUND(user_total/b.avgtotal) >=2 THEN '黄金用户'
	ELSE '吃瓜'
END  

__AAA__结果如下:

8.场景案例 – 利用orderby实现排名作弊

有时候我们需要在网站中实现排行榜, 譬如消费排行, 商品销量排行等 , 排行数据免不了要真假掺半, 结合上表我们的需求是:

1. 根据字段user_total倒排序
2. 其中id为2,4,6的用户为我们的托, 要直接置顶

--- __AAA__
SELECT * FROM user_level WHERE id in (2,4,6);
--- __BBB__
SELECT * FROM user_level WHERE id not in (2,4,6);

-- 第一种构想, 使用union 联合 (失败的)
__AAA__ UNION __BBB__ ORDER BY user_total DESC
但是__AAA__ UNION __BBB__ 先联合, 再ORDER BY , 所以没啥留用

-- 第二种(成功的) 是mysql特有的, 在order by排序时, 指定先排序id在(2,4,6)先排, desc为置顶, asc为置后, user_total后排 
-- __AAA__
SELECT * FROM user_level ORDER BY id in (2,4,6) DESC, user_total DESC;
-- __BBB__    order by可以加多种条件
SELECT * FROM user_level ORDER BY id in (2,4,6) AND id <> 2 DESC, user_total DESC

__AAA__结果如下:

9. 场景案例 – 获取连续签到X天用户列表

需求:
网站中要做一些签到功能, 以增加网站的活跃率,
功能: 统计出连续签到x天的用户. 然后对他们进行排行或奖励

CREATE TABLE `user_sign` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `sign_date` date DEFAULT NULL COMMENT '签到日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO `user_sign` VALUES (1, 'lisi', '2023-04-01');
INSERT INTO `user_sign` VALUES (2, 'lisi', '2023-04-16');
INSERT INTO `user_sign` VALUES (3, 'flanders', '2023-04-01');
INSERT INTO `user_sign` VALUES (4, 'flanders', '2023-04-02');
INSERT INTO `user_sign` VALUES (5, 'flanders', '2023-04-03');
INSERT INTO `user_sign` VALUES (6, 'flanders', '2023-04-04');
INSERT INTO `user_sign` VALUES (7, 'flanders', '2023-04-05');
INSERT INTO `user_sign` VALUES (8, 'flanders', '2023-04-04');
INSERT INTO `user_sign` VALUES (9, 'flanders', '2023-04-16');
INSERT INTO `user_sign` VALUES (10, 'flanders', '2023-04-17');
INSERT INTO `user_sign` VALUES (11, 'flanders', '2023-04-18');
INSERT INTO `user_sign` VALUES (12, 'zhangsan', '2023-04-01');
INSERT INTO `user_sign` VALUES (13, 'zhangsan', '2023-04-02');
INSERT INTO `user_sign` VALUES (14, 'zhangsan', '2023-04-03');
INSERT INTO `user_sign` VALUES (15, 'zhangsan', '2023-04-16');
INSERT INTO `user_sign` VALUES (16, 'lisi', '2023-04-17');
INSERT INTO `user_sign` VALUES (17, 'zhangsan', '2023-04-17');
INSERT INTO `user_sign` VALUES (18, 'zhangsan', '2023-04-18');
INSERT INTO `user_sign` VALUES (19, 'zhangsan', '2023-04-19');


-- __AAA__
SELECT  user_name, sign_date FROM user_sign GROUP BY user_name, sign_date ORDER BY user_name, sign_date
-- __BBB__    按user_name, sign_data 排好序后 , 按分组 打上 rnum 行号
SELECT user_name, sign_date, IF(@pre=user_name,@rownum:=@rownum+1,@rownum:=1) AS rnum, @pre:=user_name FROM
( __AAA__ ) a, (SELECT @pre:='', @rownum:=0) b
-- __CCC__    按user_name, sign_data 排好序后 , 按分组 与 sign_data的日期连续性 打上 rnum 行号
SELECT user_name, sign_date, 
IF(@pre=user_name AND DATEDIFF(sign_date,@pre_date)=1, @rownum:=@rownum+1,@rownum:=1) AS rnum, 
@pre:=user_name, @pre_date:=sign_date FROM
( __AAA__ ) a, (SELECT @pre:='', @rownum:=0, @pre_date:='') b
-- __DDD__   连续签到3天
SELECT user_name,sign_date FROM (__CCC__) a where a.rnum = 3

10.场景案例 – 子查询去重获取商品分类最新销售情况

CREATE TABLE `prod_sales` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `prod_class` varchar(255) DEFAULT NULL COMMENT '商品分类',
  `sales_date` date DEFAULT NULL COMMENT '销售汇总日期',
  `prod_id` varchar(255) DEFAULT NULL COMMENT '商品ID或名称',
  `sales_num` int DEFAULT NULL COMMENT '销售数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO `prod_sales` VALUES (1, '图书类', '2023-03-01', 'java入门', 200);
INSERT INTO `prod_sales` VALUES (2, '食品类', '2023-03-10', '德芙巧克力', 100);
INSERT INTO `prod_sales` VALUES (3, '图书类', '2023-03-03', 'Java入门', 300);
INSERT INTO `prod_sales` VALUES (4, '图书类', '2023-03-04', 'php入门到放弃', 250);
INSERT INTO `prod_sales` VALUES (5, '食品类', '2023-03-12', '旺旺雪米饼', 120);
INSERT INTO `prod_sales` VALUES (6, '图书类', '2023-03-15', 'python基础入门', 115);
INSERT INTO `prod_sales` VALUES (7, '食品类', '2023-03-19', '德芙巧克力', 130);
INSERT INTO `prod_sales` VALUES (8, '图书类', '2023-03-22', 'Java入门', 198);
INSERT INTO `prod_sales` VALUES (9, '图书类', '2023-03-21', 'php入门到放弃', 220);
INSERT INTO `prod_sales` VALUES (10, '图书类', '2023-03-22', 'android开发入门', 110);
INSERT INTO `prod_sales` VALUES (11, '食品类', '2023-03-19', '红枣', 90);

需求:
查询出图书和食品两个分类(或多个分类)在最新的一天内的商品销售情况

-- __AAA__     第一步,先找出各个分类中 最新有销售的日期
SELECT prod_class, MAX(sales_date) as sn FROM prod_sales GROUP BY prod_class

--__BBB__
SELECT a.* FROM prod_sales a 
INNER JOIN ( __AAA__ ) b ON a.prod_class = b.prod_class AND a.sales_date = b.sn 
ORDER BY a.prod_class

结果如下:

11.场景案例 – 多表关联 , update用户积分奖励

-- 创建用户积分表
CREATE TABLE `user_score` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
  `user_score` int DEFAULT '0' COMMENT '积分',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `user_score` VALUES (1, 'flanders', 0);
INSERT INTO `user_score` VALUES (2, 'zhangsan', 0);
INSERT INTO `user_score` VALUES (3, 'lisi', 0);
INSERT INTO `user_score` VALUES (4, 'wuxixi', 0);

-- 用户消费表
CREATE TABLE `user_buy` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `paymoney` decimal(10,2) DEFAULT NULL COMMENT '消费金额',
  `paydate` date DEFAULT NULL COMMENT '消费日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `user_buy` VALUES (1, 'flanders', 200.00, '2023-03-15');
INSERT INTO `user_buy` VALUES (2, 'flanders', 150.00, '2023-03-16');
INSERT INTO `user_buy` VALUES (3, 'zhangsan', 100.00, '2023-03-17');
INSERT INTO `user_buy` VALUES (4, 'lisi', 150.00, '2023-03-16');
INSERT INTO `user_buy` VALUES (5, 'zhangsan', 90.00, '2023-03-18');

需求:
运营网站做活动 , 规定在某2天内, 凡是消费的客户给予 消费金额的10% 作为积分奖励
以最大的一天为准, 不累加

-- __AAA__
SELECT MAX(paymoney) as mp , user_name FROM user_buy GROUP BY user_name
-- __BBB__   update 语句也是可以有多个表进行关联
UPDATE user_score a INNER JOIN( __AAA__ ) b ON a.user_name = b.user_name
SET a.user_score=a.user_score + (b.mp*0.1)

结果如下: