MySQL 新增修改和删除
1. 插入数据
# 语法 ①
# insert into 表名 (列名1,列名2......) values (v1,v2......)
# 简写： 给所有列赋值后，是可以省略列名的(即使是 主键id 自增也得将 id值加上),列的顺序和值得顺序一致
# insert into 表名 values (v1,v2......必须是所有列的值)# 语法 ②
# insert into 表名 set 列名1 = v1 ,列名2 = v2 ,列名3 = v3insert into commodity_order (name,custom_id,is_delete) values ('郭靖','1',0);
insert into commodity_order values (4,'郭靖','1',0);insert into commodity_order set name = '杨康' ,custom_id = 1 ,is_delete = 0;# 语法①和②的优劣
# 语法①支持批量操作;支持子查询
insert into commodity_order (name, custom_id, is_delete)
values ('郭靖1', '1', 0) ,('郭靖2', '1', 0) ,('郭靖3', '1', 0) ,('郭靖4', '1', 0) ;
# sql_test> insert into commodity_order (name, custom_id, is_delete)
# values ('郭靖1', '1', 0) ,
# ('郭靖2', '1', 0) ,
# ('郭靖3', '1', 0) ,
# ('郭靖4', '1', 0)
# [2021-01-16 17:32:11] 4 rows affected in 7 ms
# 省略掉列名，需要给出所有字段的值（包括id自增的字段）
insert into commodity_order
values (11,'郭靖1', '1', 0) ,(12,'郭靖2', '1', 0) ,(13,'郭靖3', '1', 0) ,(14,'郭靖4', '1', 0) ;
# sql_test> insert into commodity_order
# values (11,'郭靖1', '1', 0) ,
# (12,'郭靖2', '1', 0) ,
# (13,'郭靖3', '1', 0) ,
# (14,'郭靖4', '1', 0)
# [2021-01-16 17:40:59] 4 rows affected in 5 msinsert into commodity_order (name, custom_id, is_delete)(select '杨康1' as name , 1 as custom_id , 1 as is_deleteunionselect '杨康2' as name , 1 as custom_id , 1 as is_deleteunionselect '杨康3' as name , 1 as custom_id , 1 as is_deleteunionselect '杨康4' as name , 1 as custom_id , 1 as is_delete
)
# sql_test> insert into commodity_order (name, custom_id, is_delete)(
# select '杨康1' as name , 1 as custom_id , 1 as is_delete
# union
# select '杨康2' as name , 1 as custom_id , 1 as is_delete
# union
# select '杨康3' as name , 1 as custom_id , 1 as is_delete
# union
# select '杨康4' as name , 1 as custom_id , 1 as is_delete
# )
# [2021-01-16 17:34:20] 4 rows affected in 6 ms批量插入：用另一张表（可以是关联出来的表）的数据 去插入目标表INSERT INTO boys (name ,sex,age,address,created_time,enum,create_time_str,is_deleted
) SELECT
A.name,
A.sex,
A.age,A.address,
NOW(),
A.enum,a.create_time_str,0
FROMboys AWHEREA.is_deleted is null ;
# 没有就插入
# AND NOT EXISTS ( SELECT 1 FROM boys b WHERE b.item_name_guid = A.FeeItemGUID AND # is_deleted = 0 ) ;# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |id|name|sex|age|address|created_time |enum|create_time_str|is_deleted|
# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |1 |慕容皝 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |2 |慕容白 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |3 |慕容垂 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |4 |慕容复 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |5 |慕容龙城|男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# +--+----+---+---+-------+-------------------+----+---------------+----------+# 插入成功
select * from boys ;
# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |id|name|sex|age|address|created_time |enum|create_time_str|is_deleted|
# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |1 |慕容皝 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |2 |慕容白 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |3 |慕容垂 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |4 |慕容复 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |5 |慕容龙城|男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |67|慕容皝 |男 |22 |NULL |2021-10-08 10:13:56|1 |1000-05-01 |0 |
# |68|慕容白 |男 |22 |NULL |2021-10-08 10:13:56|1 |1000-05-01 |0 |
# |69|慕容垂 |男 |22 |NULL |2021-10-08 10:13:56|1 |1000-05-01 |0 |
# |70|慕容复 |男 |22 |NULL |2021-10-08 10:13:56|1 |1000-05-01 |0 |
# |71|慕容龙城|男 |22 |NULL |2021-10-08 10:13:56|1 |1000-05-01 |0 |
# +--+----+---+---+-------+-------------------+----+---------------+----------+
1. 修改单表的记录和修改多表的数据
修改单表语法：
表名 set 字段A= ，字段B= (where 过滤条件)；
没有 where 的话，所有的数据都会更新
修改多表 ？ 两张表的数据都改了吗？是的
1. 根据条件修改同一条数据
update student setx_CloseDate =(casewhen ( #{item.xIsOpenSignEnum} = 1 and (x_IsOpenSignEnum = 2 or x_IsOpenSignEnum = 3))then x_CloseDateelse #{item.xCloseDate,jdbcType=TIMESTAMP}end ) 1. 修改主表数据和字表数据，返回 库里改变了几条数据
#
select 1 as A,2 as B;
# +-+-+
# |A|B|
# +-+-+
# |1|2|
# +-+-+# 列转行(一行成多行)SQL：
select 1
union select 2;# 先建表
# create table sql_test.commodity_order
# (
# id int auto_increment
# primary key,
# name varchar(20) null,
# custom_id varchar(20) null,
# is_delete int null
# )
# comment '订单主表';
# create table sql_test.commodity_order_detail
# (
# id int auto_increment
# primary key,
# order_id int null,
# commodity_name varchar(20) null,
# commodity_id int null,
# commodity_count int null,
# is_delete int null
# )
# comment '订单明细表';select * from commodity_order;
# +--+----+---------+
# |id|name|custom_id|
# +--+----+---------+
# |1 |置办年货|1 |
# +--+----+---------+select * from commodity_order_detail;
# +--+--------+--------------+------------+---------------+
# |id|order_id|commodity_name|commodity_id|commodity_count|
# +--+--------+--------------+------------+---------------+
# |1 |1 |鞭炮 |1 |1 |
# |2 |1 |春联 |2 |2 |
# |3 |1 |牛丸 |3 |3 |
# +--+--------+--------------+------------+---------------+
# 表名不可以是关键字 order
select o.* ,d.*
from commodity_order oinner join commodity_order_detail d on o.id = d.order_id and d.is_delete = 0
where o.id = 1 and o.is_delete = 0;
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+
# |id|name|custom_id|is_delete|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+
# |1 |置办年货|1 |0 |1 |1 |鞭炮 |1 |1 |0 |
# |1 |置办年货|1 |0 |2 |1 |春联 |2 |2 |0 |
# |1 |置办年货|1 |0 |3 |1 |牛丸 |3 |3 |0 |
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+#（修改的批量操作） 需求1：用户做编辑操作，删掉了鞭炮和牛丸 (前段会传 订单明细id 1和 2)
# 先获取要处理的数据集（交集），此处是对订单明细 1和 2 的数据做处理
# update commodity_order_detail set is_delete = 1 where id in (1,2) 这个sql可以解决，但为了引出批量操作的sql 这里引出另一种思路select *
from (select 1 As detail_idunionselect 2 as detail_id) tmp_detailinner join commodity_order_detail detail on tmp_detail.detail_id = detail.id and detail.is_delete = 0 ;
# +---------+--+--------+--------------+------------+---------------+---------+
# |detail_id|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +---------+--+--------+--------------+------------+---------------+---------+
# |1 |1 |1 |鞭炮 |1 |1 |0 |
# |2 |2 |1 |春联 |2 |2 |0 |
# +---------+--+--------+--------------+------------+---------------+---------+# 获取到要操作的数据集后 ，将结果看成一张表
# update 虚拟表 set 字段 = 值 where 过滤条件update (
# 上面获取结果集只要from 后面的(select 1 As detail_idunionselect 2 as detail_id) tmp_detailinner join commodity_order_detail detail on tmp_detail.detail_id = detail.id and detail.is_delete = 0) set detail.is_delete = 1 where detail.order_id = 1;
# 查看结果 发现数据修改成功
select * from commodity_order_detail;
# +--+--------+--------------+------------+---------------+---------+
# |id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+--------+--------------+------------+---------------+---------+
# |1 |1 |鞭炮 |1 |1 |1 |
# |2 |1 |春联 |2 |2 |1 |
# |3 |1 |牛丸 |3 |3 |0 |
# +--+--------+--------------+------------+---------------+---------+# 修改主表的name和逻辑删除 一个sql处理
# 先获取要操作的数据集
select * from((select 1 As detail_idunionselect 2 as detail_id) tmp_detailinner join commodity_order_detail detail on tmp_detail.detail_id = detail.id and detail.is_delete = 0inner join commodity_order co on detail.is_delete = co.is_delete and co.id = detail.order_id) ;
# +---------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+
# |detail_id|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|id|name|custom_id|is_delete|
# +---------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+
# |1 |1 |1 |鞭炮 |1 |1 |0 |1 |置办年货|1 |0 |
# |2 |2 |1 |春联 |2 |2 |0 |1 |置办年货|1 |0 |
# +---------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+# 执行sql进行多表的修改
update (
# 上面获取结果集只要from 后面的(select 1 As detail_idunionselect 2 as detail_id) tmp_detailinner join commodity_order_detail detail on tmp_detail.detail_id = detail.id and detail.is_delete = 0inner join commodity_order co on detail.is_delete = co.is_delete and co.id = detail.order_id)
# 修改主表数据
set co.name = '第二次置办年货',
# 修改字表数据
detail.is_delete = 1
where detail.order_id = 1;
# 查看修改后的数据 ，操作成功
select o.* ,d.*
from commodity_order oinner join commodity_order_detail d on o.id = d.order_id and d.is_delete = 0
where o.id = 1 and o.is_delete = 0;# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+
# |id|name |custom_id|is_delete|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+
# |1 |第二次置办年货|1 |0 |3 |1 |牛丸 |3 |3 |0 |
# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+# 需求二：将主订单名字改为 第二次置办年货 ，
# 将订单明细名字鞭炮和牛丸的数量分别改成50，100,名字分别改成 第二次鞭炮,第二次牛丸
# 订单明细中 春联数据不动
# （在将原来数据还原的基础上进行的）
# 前段传 订单明细表id ，修改后的名称和数量 ，转成sql如下
select '1' detail_id ,'第二次鞭炮' as commodity_name ,'50' as commodity_count
union select '3' detail_id ,'第二次牛丸' as commodity_name ,'100' as commodity_count;
# +---------+--------------+---------------+
# |detail_id|commodity_name|commodity_count|
# +---------+--------------+---------------+
# |1 |第二次鞭炮 |50 |
# |3 |第二次牛丸 |100 |
# +---------+--------------+---------------+
# 上述表和主订单，订单明细关联后获取的数据
select *
from ((select '1' detail_id, '第二次鞭炮' as tem_commodity_name, '50' as commodity_countunionselect '3' detail_id, '第二次牛丸' as tmp_commodity_name, '100' as commodity_count) as tmpinner join commodity_order_detail d on d.id = tmp.detail_id and d.is_delete = 0inner join commodity_order o on o.id = d.order_id and o.is_delete = d.is_delete
# 这是后台传进来的 主订单id ，不能写死 ，这儿加 o.id = 1 是为了获取更少的数据集and o.id = 1);
# +---------+------------------+---------------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+
# |detail_id|tem_commodity_name|commodity_count|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|id|name|custom_id|is_delete|
# +---------+------------------+---------------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+
# |1 |第二次鞭炮 |50 |1 |1 |鞭炮 |1 |1 |0 |1 |置办年货|1 |0 |
# |3 |第二次牛丸 |100 |3 |1 |牛丸 |3 |3 |0 |1 |置办年货|1 |0 |
# +---------+------------------+---------------+--+--------+--------------+------------+---------------+---------+--+----+---------+---------+
# 加个套形成update语句，执行
update (
# 操作的数据集start(select '1' detail_id, '第二次鞭炮' as tmp_commodity_name, '50' as tmp_commodity_countunionselect '3' detail_id, '第二次牛丸' as tmp_commodity_name, '100' as tmp_commodity_count) as tmpinner join commodity_order_detail d on d.id = tmp.detail_id and d.is_delete = 0inner join commodity_order o on o.id = d.order_id and o.is_delete = d.is_delete
# 这是后台传进来的 主订单id ，不能写死 ，这儿加 o.id = 1 是为了获取更少的数据集and o.id = 1
# 操作的数据集end)
# 将 前段传过来的 tem 里的 字段值 赋值给 物理表
set o.name = '第二次置办年货',d.commodity_name = tmp.tmp_commodity_name,d.commodity_count = tmp.tmp_commodity_count
# o.id = 1 加到这里也行
where o.id = 1 ;
# 修改后查询 主表和字表数据
# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+
# |id|name |custom_id|is_delete|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+
# |1 |第二次置办年货|1 |0 |1 |1 |第二次鞭炮 |1 |50 |0 |
# |1 |第二次置办年货|1 |0 |2 |1 |春联 |2 |2 |0 |
# |1 |第二次置办年货|1 |0 |3 |1 |第二次牛丸 |3 |100 |0 |
# +--+-------+---------+---------+--+--------+--------------+------------+---------------+---------+# 需求三：将主订单名字改为 第二次置办年货 ， (还是一个sql完成)
# 将订单明细名字鞭炮和牛丸的数量分别改成50，100,名字分别改成 第二次鞭炮,第二次牛丸
# 订单明细中 春联数据逻辑删除
# （在将原来数据还原的基础上进行的）
# 需求分析：获取操作的数据集（取两张表的交集），对交集处理，
# -------> 需求转换：因为 前段传输给的 名字鞭炮和牛丸 这是个对象，是集合List A 的元素，后台我拿出主订单下id 为 1 的所有子订单 B，取出B的差集removeAll or filter 筛选出要
# 要删除的元素，形成一个新的要删除订单明细的集合C，A和C 这两个集合合并，就是要逻辑处理的 记录，这样删除的时候也不会，将要删除记录的其他字段设置为 ‘’
# java代码实现# 需求四：将主订单名字改为 第二次置办年货 ，（两条sql = 批量新增的sql+批量修改的sql）
# 将订单明细名字 新增一条数据 需要用insert语句
# 将订单明细名字鞭炮和牛丸的数量分别改成50，100,名字分别改成 第二次鞭炮,第二次牛丸 update 语句批量修改
# 订单明细中 春联数据逻辑删除 update 语句批量修改
# （在将原来数据还原的基础上进行的）1. 运用 sql99 多表修改的话，可以实现批量修改的效果(一个sql语句，减少网络流量)
# 将没有男票的女生 boyid设置为慕容博的
#(仅供理解：)
#一步到位的sql：
#update girl set girl.boy_id = (select id from boys where boys.name = '慕容垂')
#where girl.boy_id = 4;
# 多表关联修改
select * from boys;
# +--+----+---+---+
# |id|name|sex|age|
# +--+----+---+---+
# |1 |慕容皝 |男 |22 |
# |2 |慕容冲 |男 |33 |
# |3 |慕容垂 |男 |44 |
# |4 |慕容博 |男 |55 |
# +--+----+---+---+select * from girl;
# +--+----+------+---+
# |id|name|boy_id|sex|
# +--+----+------+---+
# |1 |黄蓉 |1 |女 |
# |2 |李莫愁 |2 |女 |
# |3 |陆无双 |2 |女 |
# |4 |程英 |NULL |女 |
# |5 |周紫墨 |NULL |女 |
# |6 |宋小婉 |NULL |女 |
# |7 |侯小妹 |NULL |女 |
# |8 |小黄蓉 |NULL |女 |
# +--+----+------+---+# 产生中间结果集
select * from girl where boy_id is null ;# 结构
update
girl left join (select boys.id as id from boys where boys.name = '慕容博'
) as boy on girl.boy_id = boy.id
set boy_id = NULL
where boy_id is null ;select girl.*, boy.id, boy.name
from girlleft join (select boys.id as id, boys.name as namefrom boyswhere boys.name = '慕容博'
) as boy
# on 1=1
# 这种情况下不需要消除笛卡尔集，因为如果 on girl.boy_id = boy.id (girl.boy_id = 4) 本来操作的数据 boy_id is null ，一关联 就没数据了on girl.boy_id = boy.id
where boy_id is null;# +--+----+------+---+----+----+
# |id|name|boy_id|sex|id |name|
# +--+----+------+---+----+----+
# |4 |程英 |NULL |女 |NULL|NULL|
# |5 |周紫墨 |NULL |女 |NULL|NULL|
# |6 |宋小婉 |NULL |女 |NULL|NULL|
# |7 |侯小妹 |NULL |女 |NULL|NULL|
# |8 |小黄蓉 |NULL |女 |NULL|NULL|
# +--+----+------+---+----+----+# 去掉笛卡尔条件限制
select girl.*, boy.id, boy.name
from girlleft join (select boys.id as id, boys.name as namefrom boyswhere boys.name = '慕容博'
) as boyon 1=1
# 这种情况下不需要消除笛卡尔集，因为如果 on girl.boy_id = boy.id (girl.boy_id = 4) 本来操作的数据 boy_id is null ，一关联 就没数据了
# on girl.boy_id = boy.id
where boy_id is null;# +--+----+------+---+--+----+
# |id|name|boy_id|sex|id|name|
# +--+----+------+---+--+----+
# |4 |程英 |NULL |女 |4 |慕容博 |
# |5 |周紫墨 |NULL |女 |4 |慕容博 |
# |6 |宋小婉 |NULL |女 |4 |慕容博 |
# |7 |侯小妹 |NULL |女 |4 |慕容博 |
# |8 |小黄蓉 |NULL |女 |4 |慕容博 |
# +--+----+------+---+--+----+# 多表 update 操作语句
update
girl left join (select boys.id as id from boys where boys.name = '慕容博'
) as boy on 1=1
set boy_id = boy.id
# 对 girl表添加过滤条件
where boy_id is null ;
# 5 rows affected in 5 ms# 查看结果：
select * from girl;
# +--+----+------+---+
# |id|name|boy_id|sex|
# +--+----+------+---+
# |1 |黄蓉 |1 |女 |
# |2 |李莫愁 |2 |女 |
# |3 |陆无双 |2 |女 |
# |4 |程英 |4 |女 |
# |5 |周紫墨 |4 |女 |
# |6 |宋小婉 |4 |女 |
# |7 |侯小妹 |4 |女 |
# |8 |小黄蓉 |4 |女 |
# +--+----+------+---+demo2：
# 将A表 相同 groupId 进行批量处理
update A inner join (SELECT'eaddf0ff-0560-e511-9395-0050569733ae' AS name,'北京市海淀区' AS address,'15011133099' AS phone,'2021/1/14 10:57:54' AS ModifiedTime,'张三' AS ModifiedName) as B on A.groupId = B.groupId
SET A.name = B.name,A.address = B.address,A.phone = B.phone,A.ModifiedTime = B.ModifiedTime
# 对A过滤
where A.groupId = '' ;1. 删除 和 ( 是用来删除表中的所有数据，因为他后面不可以跟where)
①
（单表和多表删除）
单表：
from 表名 where 筛选条件；
多表(级联删除)
多表(分92和99sql)，如果只删除表1 的结果，后面跟 表1的别名，如果只删除 表2 的记录，后跟表2的别名，两个表的记录都删，两个别名都写上
#
select * from commodity_order where id = 2;
# +--+----+---------+---------+
# |id|name|custom_id|is_delete|
# +--+----+---------+---------+
# |2 |买衣服 |1 |0 |
# +--+----+---------+---------+select * from commodity_order_detail where order_id = 2;
# +--+--------+--------------+------------+---------------+---------+
# |id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+--------+--------------+------------+---------------+---------+
# |4 |2 |冬装 |4 |1 |0 |
# |5 |2 |夏装 |5 |1 |0 |
# +--+--------+--------------+------------+---------------+---------+# 需求: 级联删除 commodity_order 中 id = 2 和 commodity_order_detail中 的子记录
# 两张表都删，delete后两个表别名都写上；只删一张表，则delete后只写一个表的别名
delete o,d
from commodity_order oinner join commodity_order_detail d on o.id = d.order_id
where o.id = 2;
# sql 执行结果：3行数据被删除了
# sql_test> delete o,d
# from commodity_order o
# inner join commodity_order_detail d on o.id = d.order_id
# where o.id = 2
# [2021-01-16 18:03:18] 3 rows affected in 6 ms删除表效率高点（全表删除），如果用他删除，自增的id 会从0 开始，如果用 的全表删除，自增的id从 上一次的值开始
