MySQL必知必会 04丨增删改查：如何操作表中的数据？

在超项目中，我市们给用户设计好了一个数据表 demo.goodsmaster，定义好了里面的字段，以及各种约束，如下所示：

mysql> DESCRIBE demo.goodsmaster;
+---------------+--------------+------+-----+---------+--+
| Field 		 		| Type 					| Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+------------+
| itemnumber 		| int  					| NO 		| PRI | NULL |auto_increment |
| barcode 	 		| text 					| NO 		| 		| NULL | 							 |
| goodsname  		| text 					| NO 		| 		| NULL |							 |
| specification | text 					| YES 	| 		| NULL | 							 |
| unit 					| text 					| YES		| 		| NULL | 							 |
| price 				| decimal(10,2) | NO 		| 		| NULL | 							 |
+---------------+------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

接下来，我就给你讲讲操作表中的数据的方法，也就是常说的“增删改查”。

1. 添加数据

1	INSERT TABLE 表名 [(字段名[,字段名] ...)] VALUE (值的列表);

这里方括号“[]”表示里面的内容可选，也就是说，根据 MySQL 的语法要求，写不写都可以。

添加数据分为两种情况：插入数据记录和插入查询结果。

1.1 插入数据记录

MySQL 支持的数据插入操作十分灵活。既可以通过给表里面所有的字段赋值，完整地插入一条数据记录，也可以在插入记录的时候，只给部分字段赋值。

比如，有一个叫 demo.goodsmaster 的表，包括了 itemnumber、barcode、goodsname、specification、unit 和 price 共 6 个字段。我想要插入一条数据记录，其中包含了所有字段的值，就可以这样操作：

INSERT INTO TABLE demo.goodsmaster 
(
  itemnumber,
  barcode,
  goodsname,
  specification,
  unit,
  price
) 
VALUES
(
	4,
  '0003',
  '尺子',
  '三角形',
  '把',
  5
);

运行上面的SQL语句，然后对表进行查询，得到以下结果。

mysql> SELECT *FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 				 | 0003 	 | 尺子 			| 三角型 			  | 把 	| 5.00  |
+------------+---------+-----------+---------------+------+-------+
1 row in set (0.01 sec)

如果我想插入一条记录，但是只给部分字段赋值，比如说，客户有个商品，需要马上上线销售，目前只知道条码、名称和价格，其它的信息先不录入，等之后再补，可以实现吗？

INSERT INTO TABLE demo.goodsmaster 
(
  barcode,
  goodsname,
  price
) 
VALUES
(
  '0004',
  '测试',
  10
);

运行这条 SQL 语句，我们来查询表的内容，就会发现，显然是可以的。

mysql> SELECT *FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 				 | 0003 	 | 尺子		  | 三角型 					| 把 	 | 5.00 |
| 5 				 | 0004 	 | 测试		  | NULL 						| NULL  | 10.00 |
+------------+---------+-----------+---------------+------+-------+
2 rows in set (0.00 sec)

我们之所以能够在插入一条数据记录的时候，只给部分字段赋值，原因就在于我们对字段的定义方式。

那字段是怎么定义的呢？我们来查看一下表的结构，看看各个字段的定义：

mysql> DESCRIBE demo.goodsmaster;
+---------------+---------------+------+-----+---------+----------------+
| Field 			  | Type 					| Null  | Key | Default | Extra 				|
+---------------+---------------+------+-----+---------+----------------+
| itemnumber 	  | int 					| NO 		| PRI | NULL 		| auto_increment |
| barcode			  | text 					| NO 		| 		| NULL 		| 							 |
| goodsname 		| text					| NO 		| 		| NULL 		| 							 |
| specification | text 					| YES 	| 		| NULL 		| 							 |
| unit 					| text 					| YES 	| 		| NULL 		| 							 |
| price 				| decimal(10,2) | NO 		| 		| NULL 		| 							 |
+---------------+---------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

可以看到，我们在插入数据时没有明确赋值的 3 个字段，都有着各自的特点。specification和unit都可以是空值，而 itemnumber 则定义了自增约束。

我们在插入一条数据记录的时候，必须要考虑字段约束的 3 种情况。

第一种情况是，如果字段允许为空，而我们没有给它赋值，那么 MySQL 会自动给它们赋予空值。
- 在刚刚的代码中，specification, unit字段都允许为空，因此，如果数据插入语句没有指定这几个字段的值，MySQL 会自动插入空值。
第二种情况是，如果字段是主键，就不能为空，这个时候，MySQL 会按照我们添加的约束进行处理。
- 比如字段itemnumber是主键，不能为空，而我们定义了自增约束，所以MySQL 自动在之前的最大值基础上加了 1。因此，itemnumber也有了自己该有的值。
第三种情况是，如果有一个字段定义不能为空，又不是主键，当你插入一条数据记录的时候，就必须给这个记录赋值。

如果我们的操作违反了字段的约束限制，会出现什么情况呢？

比如说，我们尝试把表 demo.goodsmaster 的字段speicification改为不能为空：系统会提示错误，需要把字段speicification的值为空的数据记录删除，然后再修改字段约束，再给字段“specification”添加非空约束。

部分插入一条数据记录是可以的，但前提是，没有赋值的字段，一定要让 MySQL 知道如何处理，比如可以为空、有默认值，或者是自增约束字段等等。

好了，到这里，我们就学会了给 MySQL 的数据表插入一条数据记录。但是，在实际工作中，一次只插入一条数据，有时候会不够用。

我们的项目就有这样的场景：门店每天的销售流水都很多，日积月累，流水表变得越来越大。如果就让它这么不断地增长，数据量甚至会达到数亿条，占据的存储空间达到几十个G。虽然 MySQL 可以处理这样比较大的数据表，但是每次操作的响应时间就会延长，这会导致系统的整体效率下降。

刚开始的时候，我们开发了一个日结处理，当天算清所有账目。其中一个步骤就是，把当天流水表的数据全部转到历史流水表当中去。现在，我们就可以用上数据插入语句了。具体有 2 步：

从流水表中取出一条数据；
把这条数据插入到历史流水表中。

然后不断重复这两个步骤，一直到把今天流水表的数据全部插入到历史流水表当中去。你肯定会说，这种做法的效率也太低了吧？有没有更好的办法呢？当然是有的。这个时候，我们就要用到 MySQL 的另一种数据插入操作了：把查询结果插入到数据表中。

1.2 插入查询记录

MySQL 支持把查询的结果插入到数据表中，我们可以指定字段，甚至是数值，插入到数据表中。语法结构如下：

INSERT INTO 表名 (字段名)
SELECT 字段名或值
FROM 表名
WHERE 条件

举个例子，在我们的超市信息系统的 MySQL 数据库中，历史流水表设计与流水表非常类似。不同的是，历史流水表增加了一些字段来标识历史流水的状态，比如日结时间字段，是用来记录日结操作是什么时候进行的。

用 INSERT 语句实现起来也很简单：

1
2
3

INSERT INTO 历史流水表 （日结时间字段，其他字段）
SELECT 获取当前时间函数，其他字段
FROM 流水表

现在我们给一张数据表插入一条数据记录的时候，可以给所有的字段赋值，也可以给部分字段赋值。这取决于字段的定义。如果字段

不能为空并且没有默认值，就必须赋值。另外，我们还可以通过把一个查询结果插入数据表中的方式，提高添加数据的效率。

2. 删除数据

数据删除的语法很简单，如下所示：

1	DELETE FROM 表名 WHERE 条件

如果我们现在想把刚才用过的表 demo.goodsmaster 里的内容清理一下，删除全部数据，可以通过下面的 SQL 语句来实现：

1	DELETE FROM demo.foodsmaster;

这个时候，Workbench 会提示错误。这是因为，Workbench 自动处于安全模式，它要求对数据的删除或修改操作中必须包含WHERE 条件。而且，这个 WHERE 条件中，必须用到主键约束或者唯一性约束的字段。MySQL 的这种安全性设置，主要就是为了防止删除或者修改数据时出现误操作，导致删除或修改了不相关的数据。因此，我们要习惯在删除数据的时候，添加条件语句 WHERE，防止误操作。

1	DELETE FROM demo.foodsmaster WHERE itemnumber > 1;

3. 修改数据

我们来看一下 MySQL 的数据修改语法：

1	UPDATE 表名 SET 字段名=值 WHERE 条件

语法很简单，需要注意的一点是，不要修改主键的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

4. 查询数据

经过了前面的操作之后，现在，我们知道了把数据存入数据表里，以及删除和修改表里数据的方法。

那么如何知道数据操作的结果呢？这就要用到数据查询了。

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数

其中，

SELECT：是查询关键字，表示我们要做一个查询。*是一个通配符，表示我们要查询表中所有的字段。你也可以把要查询的字段罗列出来，这样，查询的结果可以只显示你想要查询的字段内容。
WHERE：表示查询条件。你可以把你要查询的数据所要满足的条件，放在 WHERE 关键字之后。
GROUP BY：作用是告诉 MySQL，查询结果要如何分组，经常与 MySQL 的聚合函数一起使用。
HAVING：用于筛选查询结果，跟 WHERE 类似。

FROM、ORDER BY 和 LIMIT 相对来说比较复杂，需要注意的地方比较多，我来具体给你解释一下。

4.1 FROM

FROM 关键字表示查询的数据源。我们现在只学习了单个数据表，你可以把你要查询的数据表名，直接写在 FROM 关键字之后。

以后学习关联表的时候，FROM 关键字后面，还可以跟着更复杂的数据表联接。所以，数据源也不一定是表，也可以是一个查询的结果。比如下面的查询：

SELECT a.goodsname, a.price 
FROM 
(SELECT *FROM demo.goodsmaster) 
AS a;

括号里的部分叫做派生表（derived table），或者子查询（subquery），意思是我们把一个查询结果数据集当做一个虚拟的数据表来看待。MySQL 规定，必须要用 AS 关键字给这个派生表起一个别名。在这张图中，我给这个派生表起了个名字，叫做a。

4.2 ORDER BY

ORDER BY 的作用，是告诉 MySQL，查询结果如何排序。ASC 表示升序，DESC 表示降序。

我来举个简单的小例子，带你看看 ORDER BY 是怎么使用的（这里我们仍然假设字段specification和unit允许为空）。我们向表 demo.goodsmaster中插入 2 条数据：

INSERT INTO demo.goodsmaster 
(
	barcode,
  goodsname,
  price
)
VALUES
(
	'0003',
  '尺子1',
  '15'
);
INSERT INTO demo.goodsmaster 
(
	barcode,
  goodsname,
  price
)
VALUES
(
	'0004',
  '测试1',
  '20'
);

如果我们使用 ORDER BY 对查询结果进行控制时：

mysql> SELECT *FROM demo.goodsmaster ORDER BY barcode ASC,price DESC;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 6 					| 0003 	 | 尺子1 			| NULL 					| NULL 	| 15.00 |
| 4 					| 0003 	 | 尺子 			| 三角型 				| 把		 | 	5.00 |
| 7 					| 0004 	 | 测试1			| NULL 					| NULL 	| 20.00 |
| 5 					| 0004 	 | 测试 			| NULL 					| NULL 	| 10.00 |
+------------+---------+-----------+---------------+------+-------+
4 rows in set (0.00 sec)

可以看到，查询结果会先按照字段 barcode 的升序排序，相同 barcode 里面的字段，按照 price 的降序排序。

4.3 LIMIT

LIMIT 的作用是告诉 MySQL 只显示部分查询的结果。比如，现在我们的数据表demo.goodsmaster 中有 4 条数据，我们只想要显示第 2、3 条数据，就可以用 LIMIT 关键字来实现：

SELECT FROM demo.goodsmater LIMIT 1,2;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 5 					| 0004 		| 测试 			| NULL 					| NULL | 10.00 |
| 6 					| 0003 		| 尺子1			| NULL					| NULL | 15.00 |
+------------+---------+-----------+---------------+------+-------+

这里的LIMIT 1,2中，1表示起始位置，MySQL 中，起始位置的起点是 0，1 表示从第 2 条记录开始；2表示 2 条数据。因此，“LIMIT 1,2”就表示从第 2 条数据开始，显示 2 条数据，也就是显示了第 2、3 条数据。

5. ON DUPLICATE KEY UPDATE

这个关键字的作用是，告诉MySQL，如果遇到重复的数据，该如何处理。当执行INSTER操作时，若已经有存在的记录，执行UPDATE操作。

举个例子。

假设用户有 2 个各自独立的门店，分别有自己的系统。现在需要引入连锁经营的模式，把2 个店用一套系统统一管理。那么首先遇到的问题就是，需要进行数据整合。下面我们就以商品信息表为例，来说明如何通过使用ON DUPLICATE关键字，把两个门店的商品

信息数据整合到一起。

假设门店 A 的商品信息表是demo.goodsmaster，代码如下：

mysql> SELECT *FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 				 | 0001 	 | 书 				| 16开   				| 本 	| 89.00			 |
| 2 				 | 0002 	 | 笔 				| 10支装  			 | 包    | 5.00 			|
| 3 				 | 0003 	 | 橡皮			 | NULL   				| 个 	| 3.00 			 |
+------------+---------+-----------+---------------+------+------------+
3 rows in set (0.00 sec)

假设门店 B 的商品信息表是demo.goodsmaster1：

mysql> SELECT * FROM demo.goodsmaster1;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 				 | 0001 	 | 教科书		 | NULL 				 | NULL | 89.00 			|
| 4 				 | 0004 	 | 馒头 			| 							| 			| 1.50 			|
+------------+---------+-----------+---------------+------+------------+
2 rows in set (0.00 sec)

假设我们要把门店 B 的商品数据，插入到门店 A 的商品表中去

如果有重复的商品编号，就用门店 B 的条码，替换门店 A 的条码，用门店 B 的商品名称，替换门店 A 的商品名称。
如果没有重复的编号，就直接把门店 B 的商品数据插入到门店 A 的商品表中。

这个操作，就可以用下面的 SQL 语句实现：

INSERT INTO demo.goodsmaster
SELECT *
FROM demo.goodsmaster1 as a
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;

-- 运行结果如下
mysql> SELECT *FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 				 | 0001 	 | 教科书		  | 16开 				| 本 	 | 89.00 			|
| 2 				 | 0002 	 | 笔 				 |10支装 				 | 包    | 5.00 			 |
| 3 				 | 0003 	 | 橡皮 			| NULL 					| 个  	| 3.00 			  |
| 4 				 | 0004 	 | 馒头 			|  							| 		 | 1.50 			|
+------------+---------+-----------+---------------+------+------------+
4 rows in set (0.00 sec)

6. 总结

今天，学习了添加、删除、修改和查询数据的方法。下面的这些 SQL 语句，一定要重点掌握。

INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件
DELETE FROM 表名
WHERE 条件
UPDATE 表名
SET 字段名=值
WHERE 条件
SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数

INSERT INTO 表名
SELECT *
FROM 表名 as 别名
ON DUPLICATE KEY UPDATE 字段名 = 别名.字段名,别名.字段名;

Channing's Blog