增删改查

copySELECT * FROM demo.goodsmaster;

DESCRIBE demo.goodsmaster;

ALTER TABLE demo.goodsmaster MODIFY barcode TEXT NOT NULL;
ALTER TABLE demo.goodsmaster MODIFY goodsname TEXT NOT NULL;
ALTER TABLE demo.goodsmaster MODIFY price DECIMAL(10,2) NOT NULL;
ALTER TABLE demo.goodsmaster ADD COLUMN sepcification TEXT;
ALTER TABLE demo.goodsmaster ADD unit TEXT;

今天，我们来学习如何操作数据表里的数据。

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

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

接下来，我们需要使用这个表来存储数据，也就是常说的 “增删改查”。

添加数据

首先我们先来看添加数据的语法结构：

copyINSERT INTO 表名 [(字段名, [,字段名] ...)] VALUES (值的列表);

上面的方括号 “[]” 表示里面的内容可选。

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

插入数据

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

当我们想插入一条数据记录，其中包含所有字段值，可以这样操作：

copyINSERT INTO
    demo.goodsmaster (
        itemnumber,
        barcode,
        goodsname,
        sepcification,
        unit,
        price
    )
VALUES (4, '0003', '尺子', '三角型', '把', 5);

copymysql> SELECT * FROM demo.goodsmaster;
+---------+-----------+-------+------------+---------------+------+
| barcode | goodsname | price | itemnumber | sepcification | unit |
+---------+-----------+-------+------------+---------------+------+
| 0001    | 书       |  0.47 |          1 | NULL          | NULL |
| 0002    | 笔       |  0.44 |          2 | NULL          | NULL |
| 0002    | 胶水    |  0.19 |          3 | NULL          | NULL |
| 0003    | 尺子    |  5.00 |          4 | 三角型     | 把  |
+---------+-----------+-------+------------+---------------+------+
4 rows in set (0.00 sec)

如果想插入一条记录，只给部分字段赋值，可以这样操作：

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

copymysql> SELECT * FROM demo.goodsmaster;
+---------+-----------+-------+------------+---------------+------+
| barcode | goodsname | price | itemnumber | sepcification | unit |
+---------+-----------+-------+------------+---------------+------+
| 0001    | 书       |  0.47 |          1 | NULL          | NULL |
| 0002    | 笔       |  0.44 |          2 | NULL          | NULL |
| 0002    | 胶水    |  0.19 |          3 | NULL          | NULL |
| 0003    | 尺子    |  5.00 |          4 | 三角型     | 把  |
| 0004    | 测试    | 10.00 |          5 | NULL          | NULL |
+---------+-----------+-------+------------+---------------+------+
5 rows in set (0.00 sec)

我们之所以可以在插入数据的时候，只给部分字段赋值，是因为我们对字段的定义方式。

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

可以看到，”specification“ 和 ”unit“ 都可以是空值，”itemnumber“ 定义了自增约束。

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

• 第一种情况是，如果字段允许为空，但我们没有给它赋值，那么 MySQL 会自动给它们赋予空值。
• 第二种情况是，如果字段是主键，不能为空，这时，MySQL 会按照我们添加的约束进行处理。
  • 比如字段 “itemnumber‘” 是主键，不能为空，但由于我们定义了自增约束，所以 MySQL 会自动在之前的最大值基础上加 1。
• 第三种情况是，如果有一个字段定义不能为空，又不是主键，当你插入一条数据记录的时候，就需要给这个记录赋值。
  • 如果我们的操作违反了字段约束限制，执行 SQL 时，就会提示系统错误。

部分字段插入数据是可以的，前提是，没有赋值的字段，MySQL 需要知道如何处理，比如可以为空、有默认值，或者是自增约束字段等。否则，MySQL 就会提示错误。

到这里，我们已经学会如何给 MySQL 数据表插入一条数据记录。但是，在实际工作中，一次只插入一条数据，并不能满足需求。

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

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

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

然后不断重复这个步骤，直到把今天流水表中所有数据全部插入到历史流水表中。不过这种做法效率很低，其实还有更好的方法。就是将查询结果插入到数据表中。

插入查询结果

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

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

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

copyINSERT INTO 历史流水表 (日结时间字段, 其他字段)
SELECT 获取当前时间函数, 其他字段
FROM 流水表

删除数据

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

copyDELETE FROM 表名
WHERE 条件

如果我们想删除表全部数据，可以通过下面的 SQL 语句实现：

copyDELETE FROM demo.goodsmaster;

修改数据

copyINSERT INTO
    demo.goodsmaster (
        itemnumber,
        barcode,
        goodsname,
        sepcification,
        unit,
        price
    )
VALUES (4, '0003', '尺子', '三角型', '把', 5);

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

copyUPDATE 表名
SET 字段名=值
WHERE 条件

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

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

我们可以查询到商品编号为 4 的数据记录。如果我们修改了主键值，就可能会改变刚才的查询结果。

copymysql> UPDATE demo.goodsmaster SET itemnumber = 3 WHERE itemnumber = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

copymysql> SELECT * FROM demo.goodsmaster WHERE itemnumber = 4;
Empty set (0.00 sec)

可以看到，查询结果为空，因为商品编号是 4 的记录已经不存在了。

如果你必须修改主键的值，那极有可能就是主键设置的不合理。

查询数据

我们先来看下查询语句的语法结构：

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

在这些字段中，SELECT、WHERE、GROUP BY 和 HAVING 比较好理解，我们能只需要知道它们的含义就可以了。

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

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

FROM

FROM 关键字表示查询的数据源。我们只学习了单个数据表，可以把要查询的数据表名，直接写在 FROM 关键字之后。当我们学习关联表之后，你就会知道，在 FROM 关键字后面，还可以跟着更复杂的数据表联接。

需要注意的是，数据源不一定是表，也可以是一个查询结果。

copymysql> SELECT a.goodsname, a.price
    -> FROM (
    ->         SELECT *
    ->         FROM
    ->             demo.goodsmaster
    ->     ) AS a;
+-----------+-------+
| goodsname | price |
+-----------+-------+
| 尺子    |  5.00 |
+-----------+-------+
1 row in set (0.00 sec)

需要注意的是，框号中的部分叫做派生表（derived table），或者子查询（subquery），意思是我们可以把一个查询结果数据集当做一个虚拟的数据表来看待。

MySQL 规定，必须使用 AS 关键字给这个派生表起一个别名。在上面的语句中，派生表的名字就叫做 ”a“。

ORDER BY

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

首先我们向 demo.goodsmaster 中插入两条数据。

copyINSERT INTO
    demo.goodsmaster (barcode, goodsname, price)
VALUES('0003', '尺子1', 15);

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

如果我们不控制查询结果顺序，就会得到这样的结果：

copySELECT * FROM demo.goodsmater;

copymysql> SELECT * FROM demo.goodsmaster;
+---------+-----------+-------+------------+---------------+------+
| barcode | goodsname | price | itemnumber | sepcification | unit |
+---------+-----------+-------+------------+---------------+------+
| 0003    | 尺子    |  5.00 |          3 | 三角型     | 把  |
| 0004    | 测试    | 10.00 |          6 | NULL          | NULL |
| 0003    | 尺子1   | 15.00 |          7 | NULL          | NULL |
| 0004    | 测试1   | 20.00 |          8 | NULL          | NULL |
+---------+-----------+-------+------------+---------------+------+
4 rows in set (0.00 sec)

如果我们使用 ORDER BY 对查询结果进行控制，结果就不同了：

copySELECT * FROM demo.goodsmaster ORDER BY barcode ASC, price DESC;

copymysql> SELECT * FROM demo.goodsmaster ORDER BY barcode ASC, price DESC;
+---------+-----------+-------+------------+---------------+------+
| barcode | goodsname | price | itemnumber | sepcification | unit |
+---------+-----------+-------+------------+---------------+------+
| 0003    | 尺子1   | 15.00 |          7 | NULL          | NULL |
| 0003    | 尺子    |  5.00 |          3 | 三角型     | 把  |
| 0004    | 测试1   | 20.00 |          8 | NULL          | NULL |
| 0004    | 测试    | 10.00 |          6 | NULL          | NULL |
+---------+-----------+-------+------------+---------------+------+

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

LIMIT

LIMIT 作用是告诉 MySQL 只显示部分查询结果。

比如，在我们的数据表 demo.goodsmaster 中有 4 条数据，我们只想显示第 2、3 条数据，就可以使用 LIMIT 关键字来实现。

copySELECT * FROM demo.goodsmaster LIMIT 1,2;

copymysql> SELECT * FROM demo.goodsmaster LIMIT 1,2;
+---------+-----------+-------+------------+---------------+------+
| barcode | goodsname | price | itemnumber | sepcification | unit |
+---------+-----------+-------+------------+---------------+------+
| 0004    | 测试    | 10.00 |          6 | NULL          | NULL |
| 0003    | 尺子1   | 15.00 |          7 | NULL          | NULL |
+---------+-----------+-------+------------+---------------+------+
2 rows in set (0.00 sec)

总结

本篇文章，我们学习了添加、删除、修改和查询数据的方法，这些都是我们经常遇到的操作。

copyINSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);

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

DELETE FROM 表名
WHERE 条件

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

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

如果你在工作中遇到更复杂的操作需求，可以查看这 3 份资料，分别是 MySQL 数据插入、MySQL 数据更新、MySQL 数据查询。

技术拓展

如果我们将查询结果插入到表中，导致主键约束或者唯一性约束被破坏，就可以使用 “ON DUPLICATE” 关键字，把两个门店的商品信息数据整合到一起。

copy# 删除数据表
DELETE FROM demo.goodsmaster;

# 创建数据表 demo.goodsmaster
CREATE TABLE
    demo.goodsmaster (
        itemnumber INT PRIMARY KEY AUTO_INCREMENT,
        barcode TEXT NOT NULL,
        goodsname TEXT NOT NULL,
        specifiction TEXT,
        unit TEXT,
        salesprice DECIMAL(10, 2)
    );

INSERT INTO
    demo.goodsmaster (
        barcode,
        goodsname,
        specifiction,
        unit,
        salesprice
    )
VALUES ('0001', '书', '16开', '本', 89), ('0002', '笔', '10支装', '包', 5);

INSERT INTO
    demo.goodsmaster (
        barcode,
        goodsname,
        unit,
        salesprice
    )
VALUES ('0003', '橡皮', '个', 3);

# 创建数据表 demo.goodsmaster1
CREATE TABLE demo.goodsmaster1 LIKE demo.goodsmaster;

INSERT INTO
    demo.goodsmaster1 (barcode, goodsname, salesprice)
VALUES ('0001', '教科书', 89);

INSERT INTO
    demo.goodsmaster1 (
        itemnumber,
        barcode,
        goodsname,
        specifiction,
        unit,
        salesprice
    )
VALUES (4, '0004', '馒头', '', '', 1.5);

门店 A 的商品信息表是 demo.goodsmaster：

copymysql> SELECT * FROM demo.goodsmaster;
+------------+---------+-----------+--------------+------+------------+
| itemnumber | barcode | goodsname | specifiction | 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.goodsmater1：

copymysql> SELECT * FROM demo.goodsmaster1;
+------------+---------+-----------+--------------+------+------------+
| itemnumber | barcode | goodsname | specifiction | 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 语句实现：

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

copymysql> SELECT * FROM demo.goodsmaster;
+------------+---------+-----------+--------------+------+------------+
| itemnumber | barcode | goodsname | specifiction | 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)