mysql基础-数据操作之增删改

目录

1.新增数据

1.1单条数据新增

1.2多条数据新增

1.3查询数据新增

2.更新

2.1单值更新

2.2多值更新

2.3批量更新

2.3.1 批量-单条件更新

2.3.2批量-多条件更新

2.4 插入或更新

2.5 联表更新

3.删除

---

本次分享一下数据库的DML操作语言。

操作表的数据结构：

CREATE TABLE `t_order` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  `money` decimal(10,2) DEFAULT NULL COMMENT '金额',
  `good_id` int(12) DEFAULT NULL COMMENT '商品ID',
  `good_name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

新增语法：

insert into <table> (字段1，字段2) values (字段1值，字段2值)

1.新增数据

1.1单条数据新增

新增一条商品名称为松子的订单：

INSERT INTO `t_order`(`money`, `good_id`, `good_name`) VALUES ( 2.00, 11, '松子');

1.2多条数据新增

和单条数据相比，就是多一组数值。

INSERT INTO `t_order`(`money`, `good_id`, `good_name`) VALUES ( 2.00, 11, '松子'),( 3.00, 12, '巴旦木');

注意：字段名和字段值一定要一一对应。

1.3查询数据新增

假如我们有一张2023年的表，表结构和t_order相同，数据如下：

我们可以使用如下语句，将数据插入到t_order表中，

INSERT INTO `t_order`(`money`, `good_id`, `good_name`)   select  `money`, `good_id`, `good_name` from t_order_2023 where good_name = '苹果'

注意：插入的字段名顺序和查询的值需要一一对应。

2.更新数据

更新语法：

update <table> set 字段1=更新1值，字段2=更新2值

以下示例就不再粘图，有兴趣可以自行试一下。

2.1单值更新

示例：将order_id = 5的数据的商品名称改为火龙果

update t_order set good_name = '火龙果' where order_id = 5

2.2多值更新

示例：将商品名称为火龙果的数据的金额改为10，商品id改为99

update t_order set money=10,good_id = 99 where good_name = '火龙果'

2.3批量更新

2.3.1 批量-单条件更新

所谓单条件就是case中只有一个条件。

示例：将order_id = 1的商品名称改为松子1，将order_id = 2 的商品名称改为巴旦木1

UPDATE t_order 
SET good_name =
CASE
	WHEN order_id = 1 THEN '松子1' 
	WHEN order_id = 2 THEN '巴旦木1'
	else good_name
	end 

2.3.2 批量-多条件更新

所谓单条件就是case中有多个条件。

示例：将order_id = 1且 金额 = 2 的商品名称改为松子1，将order_id = 2 且 金额 = 3 的商品名称改为巴旦木1

UPDATE t_order 
SET good_name =
CASE
	WHEN order_id = 1 and money = 2 THEN '松子2' 
	WHEN order_id = 2 and money = 3 THEN '巴旦木2'
	else good_name
	end 
	

2.4 插入或更新

执行一条sql语句，如果存在则更新，如果不存在，则新增。前提必须有主键或唯一索引

语法：

INSERT INTO <table> (字段1, 字段2, 字段3, ...)  VALUES (字段值1, 字段值2, 字段值3, ...)
ON DUPLICATE KEY UPDATE SET 字段2 = 字段2只, 字段3 = 字段3值, ...;

示例：插入或更新 order_id = 1 的数据的商品名称改为'松子3'

	insert into t_order (order_id,good_name) value (1,'松子3') ON DUPLICATE KEY update good_name = '松子3';

注意事项：此处返回的修改条数为2，但是在数据库修改的就是order_id = 1这条数据。

2.5 联表更新

示例：假如我们存在一张商品表（表结构如下），此时我们需要更新商品名称为‘松子3’的订单的金额为5，商品名称的判断不能从订单中判断，需要从商品中判断，商品表与订单表通过good_id关联。

-- 商品表结构
CREATE TABLE `t_good` (
  `good_id` int(12) NOT NULL COMMENT '主键',
  `good_name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  PRIMARY KEY (`good_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

sql如下（下次分享联表查询）：

update t_order t1 join t_good  t2 on t1.good_id = t2.good_id set money = 5 where t2.good_name = '松子3'

3.删除数据

语法：

delete from  <table> where <condition>

示例：（订单表在生产环境均不会删除，此处仅为示例）假如我们存在一张商品表（表结构如上-联表更新），此时我们需要删除商品名称为'松子3'的数据，商品名称的判断不能从订单中判断，需要从商品中判断，商品表与订单表通过good_id关联。

删除语句如下：

DELETE 
FROM
	t_order 
WHERE
	order_id IN ( SELECT order_id FROM t_order WHERE good_id = ( SELECT good_id FROM t_good WHERE good_name = '松子3' ) )

报错信息：

delete from t_order where order_id in (select order_id from t_order where good_id = (select good_id from t_good where good_name = '松子3'  ) )
> 1093 - You can't specify target table 't_order' for update in FROM clause
> 时间: 0.002s

报错的含义：不能从一个表中查询出数据，再继续删除这个表单的数据。我们可以修改在查询出来的数据进行一次表名封装。

修改如下：

DELETE 
FROM
	t_order 
WHERE
	order_id IN ( select order_id from (SELECT order_id FROM t_order WHERE good_id = ( SELECT good_id FROM t_good WHERE good_name = '松子3' )) a  )

这样就可以了，举例可能不当，错误原因当理解，解决方法当知道。

本次分享至此。