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)# [-01-16 17:32:11] 4 rows affected in 7 ms# 省略掉列名，需要给出所有字段的值（包括id自增的字段）insert into commodity_ordervalues (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)# [-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# )# [-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) SELECTA.name,A.sex,A.age,A.address,NOW(),A.enum,a.create_time_str,0FROMboys 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 |-10-08 10:13:56|1 |1000-05-01|0 |# |68|慕容白 |男 |22 |NULL |-10-08 10:13:56|1 |1000-05-01|0 |# |69|慕容垂 |男 |22 |NULL |-10-08 10:13:56|1 |1000-05-01|0 |# |70|慕容复 |男 |22 |NULL |-10-08 10:13:56|1 |1000-05-01|0 |# |71|慕容龙城|男 |22 |NULL |-10-08 10:13:56|1 |1000-05-01|0 |# +--+----+---+---+-------+-------------------+----+---------------+----------+
1. 修改单表的记录和修改多表的数据
修改单表语法：
update 表名 set 字段A= value1 ，字段B= value2 (where 过滤条件)；
没有 where 的话，所有的数据都会更新
修改多表 ？ 两张表的数据都改了吗？是的
1. 根据条件修改同一条数据
update student set<!--前端传 开启，库里是 临时关闭 or 永久关闭 ，则 不改变 x_CloseReason 的值 -->x_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 1union select 2;# 先建表# create table modity_order# (#id int auto_increment# primary key,#namevarchar(20) null,#custom_id varchar(20) null,# is_delete int null# )#comment '订单主表';# create table modity_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 |# +--+--------+--------------+------------+---------------+# 表名不可以是关键字 orderselect o.* ,d.*from commodity_order oinner join commodity_order_detail d on o.id = d.order_id and d.is_delete = 0where 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 = 1where 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 = 0where 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_countunion 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 = '第二次置办年货',modity_name = tmp.tmp_commodity_name,modity_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 ;# 结构updategirl left join (select boys.id as id from boys where boys.name = '慕容博') as boy on girl.boy_id = boy.idset boy_id = NULLwhere boy_id is null ;select girl.*, boy.id, boy.namefrom 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.idwhere 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.namefrom 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.idwhere 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 操作语句updategirl left join (select boys.id as id from boys where boys.name = '慕容博') as boy on 1=1set 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,'/1/14 10:57:54' AS ModifiedTime,'张三' AS ModifiedName) as B on A.groupId = B.groupIdSET A.name = B.name,A.address = B.address,A.phone = B.phone,A.ModifiedTime = B.ModifiedTime# 对A过滤where A.groupId = '' ;
1. 删除 delete 和 truncate( truncate 是用来删除表中的所有数据，因为他后面不可以跟where)
① delete
（单表和多表删除）
单表：
delete from 表名 where 筛选条件；
多表(级联删除)
多表(分92和99sql)，如果只删除表1 的结果，delete后面跟 表1的别名，如果只删除 表2 的记录，delete后跟表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,dfrom commodity_order oinner join commodity_order_detail d on o.id = d.order_idwhere 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# [-01-16 18:03:18] 3 rows affected in 6 ms
truncate 删除表效率高点（全表删除），如果用他删除，自增的id 会从0 开始，如果用 delete的全表删除，自增的id从 上一次的值开始
