MySQL left join 引发的惨案

当我用这个进行更改值时，type未控制order表 其他数据被更改 还好备份数据表了（这里就体现了备份的重要性）

 UPDATE expense_order as a
    left join (
        SELECT detail.company_id,detail.`order_id`,sum(detail.`deduction_money`) as amount FROM expense_amortize_detail as detail

JOIN  `pigcms_expense_order` as expense on expense.`id` = detail.`order_id` 

WHERE detail.`company_id` =336 and detail.`status` = 0 

and expense.`company_id` =336 and expense.type=2 and expense.is_parent = 1 and  expense.status in (1,2) and expense.auditing_status = 1

GROUP BY(expense.`id`)  ORDER BY expense.`id` DESC
				) as d
    on a.id =d.order_id and a.company_id = d.company_id  and a.type =2
	set a.settlement_money=d.amount; 

还原表数据 同样left join 不同之处在于通过 where控制左联表的判断依据

 UPDATE expense_order as a
    left join expense_order_bak as bak 
    on a.id =bak.id
	set a.settlement_money=bak.settlement_money 
	where a.type=1

其他解决方案 将left join 更改为inner join

 UPDATE expense_order as a
    inner join (
        SELECT detail.company_id,detail.`order_id`,sum(detail.`deduction_money`) as amount FROM expense_amortize_detail as detail

JOIN  `pigcms_expense_order` as expense on expense.`id` = detail.`order_id` 

WHERE detail.`company_id` =336 and detail.`status` = 0 

and expense.`company_id` =336 and expense.type=2 and expense.is_parent = 1 and  expense.status in (1,2) and expense.auditing_status = 1

GROUP BY(expense.`id`)  ORDER BY expense.`id` DESC
				) as d
    on a.id =d.order_id and a.company_id = d.company_id  and a.type =2
	set a.settlement_money=d.amount; 

案例

localhost 新建 blog_cate 栏目表 blog_artcle 文章表

-- 删除blog库
DROP DATABASE IF EXISTS blog;
-- 新建blog库
CREATE DATABASE blog charset utf8;
-- 查看、进入
show database;
use blog;

-- 先删后建栏目表
drop table if exists blog_cate ;
create table blog_cate (
	id INTEGER,
	catename varchar(30)
);
-- 同理 建文章表blog_artcle
--- 插入数据
INSERT INTO `blog_cate `(`id`, `catename `) VALUES (1, '父亲的散文诗');
INSERT INTO `blog_cate `(`id`, `catename `) VALUES (2, '心情文字');

INSERT INTO `blog_artcle`(`id`, `title`, `desc`, `content`, `cateid`, `time`, `pic`) VALUES (1, 'test', '测试测试', '父亲的散文诗', 1, 2022, 'dd');
INSERT INTO `blog_artcle`(`id`, `title`, `desc`, `content`, `cateid`, `time`, `pic`) VALUES (2, 'test1', '测试测试1', '父亲的散文诗1', 1, 2022, 'dd');
INSERT INTO `blog_artcle`(`id`, `title`, `desc`, `content`, `cateid`, `time`, `pic`) VALUES (4, 'test4', '测试测试4', '父亲的sf散文诗1', 2, 2022, 'dd');
INSERT INTO `blog_artcle`(`id`, `title`, `desc`, `content`, `cateid`, `time`, `pic`) VALUES (3, 'test3', '测试测试3', 'dfsfsfs', 2, 2022, 'dd');

blog_cate

blog_artcle

数据库连表方式

• 内连接 ：inner 、inner join
• 外连接 ：outer join
  • 左外连接 ：left outer join
  • 左连接 ：left join
  • 右外连接 right outer join
  • 右连接： right join
• 全连接 full join 、union

内连接

查询的是两张表的并集，也就是A表和B表都必须有数据才能查询出来；

/*** 栏目的id 与 文章的所属栏目id */
-- join
select * from blog_cate as c join blog_artcle as a  on c.id = a.cateid 

-- inner join
select * from  blog_cate as c  inner join blog_artcle as a  on c.id = a.cateid 

-- 逗号的连表方式就是内连接
select * from blog_cate as c, blog_artcle as a where c.id = a.cateid

/**栏目的id 与 文章的id 进行查询*/
select * from  blog_cate as c  inner join blog_artcle as a  on c.id = a.id 

结果展示

左外连接 和 左连接

是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

/*** 栏目的id 与 文章的id */
-- left join
select * from blog_cate as c left join  blog_artcle as a on  c.id = a.id

-- left outer join
select * from blog_artcle as c left outer join blog_cate as a on   c.id = a.id 

-- left join

-- left outer join 两个表更换位置

右外连接 和 右连接

是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

-- right join
select * from blog_cate as c right join  blog_artcle as a on  c.id = a.id

-- right outer join
select * from blog_artcle as c right outer join blog_cate as a on   a.id = c.id 

与上面左联接进行比较

-- right join 两个表更换位置

-- right outer join

全连接

全连接显示两侧表中所有满足检索条件的行。
mysql中没有full join,mysql可以使用union实现全连接；

select * from blog_cate as c left join blog_artcle as a on a.id = c.id 
union
select * from blog_cate as c  right join blog_artcle as a on c.id = a.cateid 

实验 今日出现的问题

步骤1 定目标

-- 修改文章cateid 为2的内容 为所属栏目的name
UPDATE blog_artcle  as a 
LEFT JOIN blog_cate as c
on a.cateid = c.id and a.cateid = 2
SET a.content = c.catename

步骤2 首先看下原表内容

SELECT * FROM blog_artcle

步骤3 执行 查看差异

有2行被修改，但是cateid=1的直接被修改到为null 可知and a.cateid = 2 这个 条件我们没有控制到 cateid=1的数据已被更改为null

步骤4 更改SQL语句

--- left join 与 where 结合 改cateid =2 的
UPDATE blog_artcle  as a 
LEFT JOIN blog_cate as c
on c.id = a.cateid 
SET a.content = c.catename WHERE a.cateid = 2
--- inner join 与on 结合 改cateid =1 的
UPDATE blog_artcle  as a 
inner JOIN blog_cate as c
on a.cateid = c.id and a.cateid =1
SET a.content = c.catename

成功

右连接可参考左联接 这里就完结了 今天圆满一天