数据库的增删改语句示例(数仓StarRocks、SQL、MySQL、insert、delete、update语法实例)
数仓StarRocks、SQL和MySQL的增删改语法实例示例
数仓StarRocks、SQL和MySQL的多表关连的插入、多表关连的删除、多表关连的更新语法
一、增(insert)
0. 利用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
;