菜鸟开发之多表联合增删改
多表联合处理数据在开发过程中必不可少，占比不低于逻辑处理，菜菜鸟先以此记录简单的联查处理，后则不断补充，尤其数据表查询......
目录
一、多表联查新增
1、一张表数据插入到另一张表
1）新表存在
2）新表不存在
3）目标表字段多于源表
4） IN 子句可用于向另一个数据库 Backup.mdb 中拷贝表
2、多表数据插入到另一张表
1）直接新增查询结果数据
2）查询结果不直接新增
二、多表联查删除
1、EXISTS
2、IN
3、INNER JOIN
4、删除多表数据
三、多表联查修改
1、多表联查修改
四、多表联查
1、NOT EXISTS
2、NOT IN
一、多表联查新增
1、一张表数据插入到另一张表
1）新表存在
两张表字段数据结构完全一致，全部插入
// 目标表 targetTable 源数据表 sourceTable
INSERT INTO targetTable SELECT * FROM sourceTable;字段必须保持一致(位置一致，类型一致)，否则会出现数据转换错误，并且主键不能相同。
INSERT INTO
targetTable(field1,field2,...)
SELECT
field1,field2,...
FROM sourceTable;2）新表不存在
插入时会自动创建表targetTable，并将sourceTable中指定字段数据复制到targetTable中
SELECT field1, field2 INTO targetTable FROM sourceTable3）目标表字段多于源表
先设置占位符，再设置值；或直接在语句中赋值
INSERT INTO targetTable(目标字段1，目标字段2，field1, field2)
SELECT a, b, field1, field2 FROM sourceTable
WHERE targetTable.id = sourceTable.id;可以向一个表中查询不存在的列名，不存在的列名必须是实际值或占位符，不能是变量，如果是变量，sql语句会将其当做表字段，而表中不存在这个字段，会报错
4） IN 子句可用于向另一个数据库 Backup.mdb 中拷贝表
SELECT * INTO student IN 'Backups.mdb' FROM student WHERE class='红星幼儿园';2、多表数据插入到另一张表
1）直接新增查询结果数据
查询出来的字段起个别名保证与即将插入的表名字段一致
INSERT INTO cas_composition ( DM,component, cas)
SELECT
n.DM AS DM,
n.CNAME2 AS component,
p.MELTING_POINT_WATER AS cas
FROM
cas_name n
LEFT JOIN cas_physical_chemical p ON n.DM = p.DM2）查询结果不直接新增
查询结果依据唯一标识处理，唯一标识相同数据新增，否则数据更新
INSERT INTO base_competitive_product(
sales_person_code,
customer_code,
competitor_brand,
competitor_brand_code,
sub_category_code,
sub_category,
sku_code,
sku_name,
month_quantity,
date_last_updated,
update_time,
is_active,
unit_price
)
SELECT
distinct t1.salesPersonCode sales_person_code,
t1.customerNumber customer_code,
t4.brand_name competitor_brand,
t4.brand_code competitor_brand_code,
t4.sub_category_code,
t4.sub_category,
t4.sku_code,
t4.sku_desc sku_name,
t1.monthQuantity month_quantity,
t1.dateLastUpdated date_last_updated,
t1.updateTime update_time,
t1.isActive is_active,
t2.unit_price
FROM
u_competitive_product t1
INNER JOIN base_competitive_product_unit_price t2 on t1.subCategory = t2.sub_category_code
inner join base_competitve_sku t4 on t1.sku_code = t4.sku_code
inner join (select customerNumber, competitorBrandName,subCategoryCode,sku_code, max(dateLastUpdated) dateLastUpdated
from u_competitive_product
group by customerNumber, competitorBrandName,subCategoryCode,sku_code
) t3
on t1.customerNumber = t3.customerNumber
and t1.competitorBrandName = t3.competitorBrandName
and t1.dateLastUpdated = t3.dateLastUpdated
and t1.subCategoryCode = t3.subCategoryCode
and t1.sku_code = t3.sku_code
<if test="syncTime!=null and syncTime!=''">
where t1.sync_time > str_to_date(#{syncTime,jdbcType=VARCHAR},'%Y-%m-%d %H:%i:%s')
</if>
on duplicate key update customer_code = values(customer_code),
competitor_brand = values(competitor_brand),
date_last_updated = values(date_last_updated),
sub_category_code = values(sub_category_code),
sku_code = values(sku_code)二、多表联查删除
开发过程中谨慎删除，最好做数据备份
1、EXISTS
DELETE FROM targetTable
WHERE exists(
SELECT 1 FROM sourceTable WHERE targetTable.code = sourceTable.code
)2、IN
DELETE ra FROM relation_area_apb ra WHERE ra.area_id IN(
select t.id from area_service_info t
WHERE t.code = #{areaCode}
AND t.area_level = #{areaLevel}
AND t.STATUS = 1
AND t.if_new != 1
AND t.if_future = #{ifFuture}
);3、INNER JOIN
DELETE raa FROM relation_area_apb raa
INNER JOIN area_service_info asi
ON raa.area_id = asi.id
AND asi.if_future = 2
AND area_level = 24、删除多表数据
DELETE t1 FROM t1,t2 WHERE条件
// 从两个表中找出相同记录的数据并把两个表中的数据都删除掉1
// t1,t2不能是别名
DELETE t1,t2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25
DELETE table_name,table2_name
FROM table_name as t1
LEFT JOIN table2_name as t2 ONt1.id=t2.id
WHERE table_name.id=25
delete只删除的是数据。属于DMl
truncate只保留表结构，删除数据和索引包括主键的自增。属于DML
drop删除的是整个表结构包括数据，索引，主键的自增。属于DDL
三、多表联查修改
1、多表联查修改
1）假设我们有两个表：用户表(user)和订单表(order)。我们希望将所有订单的状态修改为已完成，同时更新对应用户表中的订单数量。
UPDATE order
JOIN user ON order.user_id = user.id
SET order.status = '已完成', user.order_count = user.order_count + 1
UPDATE order o, user u
SET order.status = '已完成',
user.order_count = user.order_count + 1
WHERE o.user_id = u.id
2）联查修改
update c_customer_assignment_source cas
INNER JOIN (
SELECT
cp.month_quantity * cpup.unit_price potentialMass,cp.customer_code customerCode
FROM base_competitive_product cp
JOIN base_competitive_product_unit_price cpup ON cp.sub_category = cpup.sub_category_code
) AS ab ON cas.customer_code = ab.customerCode
SET cas.competitive_potential_mass = ab.potentialMass四、多表联查
1、NOT EXISTS
表1和表2的字段一样，并且表1中包含表2的所有数据
SELECT table1.*
FROM table1
WHERE NOT EXISTS (
SELECT *
FROM table2
WHERE table2.column = table1.column
);
column是表1和表2中的共同字段，其是在表1中查询不在表2中出现的记录，最终返回的是表1中剔除表2的数据。
2、NOT IN
SELECT table1.*
FROM table1
WHERE table1.column NOT IN (
SELECT column
FROM table2
);