数据库的多表关联的增删改语句示例（数仓StarRocks、SQL、MySQL、insert、delete、update语法实例）(SQL和MySQL的多表关连的插入、多表关连的删除和多表关连的更新)

数据库的增删改语句示例（数仓StarRocks、SQL、MySQL、insert、delete、update语法实例）
数仓StarRocks、SQL和MySQL的增删改语法实例示例
数仓StarRocks、SQL和MySQL的多表关连的插入、多表关连的删除、多表关连的更新语法

一、增（insert）

0. 利用Excel快速生成SQL临时表-点击跳转

Excel快速生成SQL临时表

1. SQL（insert into）

select t.* into #tempOS from (
select 'TT/2528/allaure' as OrdersSourceName union all
select 'GT/1452/reald' as OrdersSourceName union all
select 'UT/1921/carge' as OrdersSourceName union all
select 'IO/1344/enocu' as OrdersSourceName union all
select 'PR/2573/groes' as OrdersSourceName union all
select 'FO/2289/goohddmall' as OrdersSourceName union all
select 'LR/2027/nekll' as OrdersSourceName union all
select 'FK/2545/aeuolda' as OrdersSourceName union all
select 'EE/1481/futl' as OrdersSourceName union all
select 'DY/1932/erxpl' as OrdersSourceName) t
;
select * from #tempOS

2. SQL（with）

with #tempOS as (
select 'IT/228/allure' as OrdersSourceName union all
select 'IT/142/real' as OrdersSourceName union all
select 'IT/191/care' as OrdersSourceName union all
select 'IT/134/enou' as OrdersSourceName union all
select 'FR/253/gros' as OrdersSourceName union all
select 'FR/229/goodmall' as OrdersSourceName union all
select 'FR/227/nell' as OrdersSourceName union all
select 'FR/245/aeda' as OrdersSourceName union all
select 'DE/181/ful' as OrdersSourceName union all
select 'DE/192/expl' as OrdersSourceName)
select * from #tempOS

3. MySQL（临时表）

create temporary table tempOS(
 OrderSourceName varchar(50)
);
insert into tempOS values 
('IG/2238/allaure'), 
('FT/1492/reaal'), 
('GT/1981/carde'), 
('PT/1374/eneou'), 
('GE/2553/geros'), 
('FW/2229/gorodmall'), 
('QR/2257/nefll'), 
('HR/2425/aedda'), 
('IE/1831/fusl'), 
('DO/1922/exwapl');
select * from #tempOS

4. MySQL（insert into）

select t.* into #tempOS from (
select 'TT/2528/allaure' as OrdersSourceName union all
select 'GT/1452/reald' as OrdersSourceName union all
select 'UT/1921/carge' as OrdersSourceName union all
select 'IO/1344/enocu' as OrdersSourceName union all
select 'PR/2573/groes' as OrdersSourceName union all
select 'FO/2289/goohddmall' as OrdersSourceName union all
select 'LR/2027/nekll' as OrdersSourceName union all
select 'FK/2545/aeuolda' as OrdersSourceName union all
select 'EE/1481/futl' as OrdersSourceName union all
select 'DY/1932/erxpl' as OrdersSourceName) t
;
select * from #tempOS

二、删（delete）

1. SQL

DELETE st, gr
FROM student st
INNER JOIN grade gr ON st.student_id = gr.student_id
WHERE st.student_id = '123456';

2. MySQL

delete ppso
from ordersource ppso
inner join strategy pps on ppso.CId=pps.CId and pps.Id=ppso.Id
inner join sys_source os on os.Id=ppso.Id and os.SourceId=ppso.SourceId
inner join tempOS tos on os.OrderSourceName=tos.OrderSourceName
where pps.CustomerId=1
        and pps.MarketId=2
        and pps.type=3
        and pps.IsEnabled=1
        and pps.IsDeleted=0

三、改（update）

1. SQL

update ose
set ose.status=0, ose.remark='店铺已关闭'
from RB_OrderSource_Ebay ose
inner join #tempOS tos on tos.OrdersSourceName=ose.ordersourcename
-- where tos.OrderSourceName='DE/181/ful'

2. MySQL

update `phk_temp_StraightWarehouse_Online_listing` vol
left join 
(
    select
            ppw.Sku
            , sum(ppw.Inventory) Inventory
    from p_product_warehouse_inventory ppw 
    where ppw.CustomerId=1 and ppw.WarehouseId in (2, 333,3922, 5312)
    group by ppw.Sku
) pwi on vol.oriSku = pwi.Sku
 set vol.lastGoodNum = FLOOR(pwi.Inventory*1.0 / vol.SkuNum)
 where vol.OrderSourceID=12942

3. StartRocks MySQL

update p_list
set col1=b.col1,col2=b.col2
from (
	select
		 k.OrderSourceId
		 , k.ItemId
		 , k.Sku
	from k_list k
	inenr join j_list j on k.sku=b.sku
	where k.sku='12345'
) b
where p_list.OrderSourceId=b.OrderSourceId and p_list.ItemId=b.ItemId and p_list.Sku=b.Sku
;