【跟着例子学MySQL】数据更新 -- 更新,删除和导入导出

---

前言

举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

• 模仿是最好的老师，实践是检验成果的方法。
• 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

• MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
• 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

• 以一个贯穿始终的应用场景为主线，渐进地讲解用法
• 难度适中，既有基础方法，也有值得注意的关键细节

---

回顾

上篇文章👉《【跟着例子学MySQL】生成统计报告 --分组聚合》 讲了如何利用SELECT生成统计报告，这篇接着讲如何修改、删除和导入数据。

---

修改数据 - UPDAT

要修改现有数据，请使用UPDATE ... SET命令，语法如下：

UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria

例如：

-- 将所有产品的价格提高10%
mysql> UPDATE products SET price = price * 1.1;
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001     | PEN          | Pen Red   | 5000     | 1.35  |
| 1002     | PEN          | Pen Blue  | 8000     | 1.38  |
| 1003     | PEN          | Pen Black | 2000     | 1.38  |
| 1004     | PEC          | Pencil 2B | 10000    | 0.53  |
| 1005     | PEC          | Pencil 2H | 8000     | 0.54  |
+-----------+-------------+-----------+----------+-------+
-- 修改选定的行
mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001      | PEN         | Pen Red | 4900     | 1.35  |
+-----------+-------------+---------+----------+-------+
-- 可以修改多个值
mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
| 1001      | PEN         | Pen Red | 4950     | 1.23  |
+-----------+-------------+---------+----------+-------+

❗ 注意： 如果在更新命令中省略了WHERE子句，则所有ROWL将被更新。因此，在执行更新之前，最好使用相同的条件执行一个SELECT查询来确认结果集。这也适用于下一节中的删除语句。

---

删除行 - DELETE FROM

使用DELELE FROM命令从表中删除行，语法如下：

-- 从表中删除所有行。小心使用！记录不可恢复！
DELETE FROM tableName
--仅删除符合条件的行
DELETE FROM tableName WHERE criteria

例如:

mysql> DELETE FROM products WHERE name LIKE 'Pencil%';
Query OK, 2 row affected (0.00 sec)
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 4950 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.38 |
| 1003 | PEN | Pen Black | 2000 | 1.38 |
+-----------+-------------+-----------+----------+-------+
-- 小心使用，因为被删除的记录是不可恢复的！
mysql> DELETE FROM products;
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM products;
Empty set (0.00 sec)

❗ 注意：没有WHERE子句的DELETE FROM tableName语句从表中删除所有记录。即使使用了WHERE子句，你也可能在无意中删除了一些记录。在执行删除（和更新）之前，建议使用相同的WHERE子句执行SELECT命令来检查结果集。

---

数据导入导出

有几种方法可以将数据添加到数据库中： (a)手动发出插入命令；(b)从脚本运行插入命令；或者©使用LOAD DATA语句或通过mysqlimport实用程序从文件中加载原始数据。

LOAD DATA LOCAL INFILE ... INTO TABLE ...

除了使用插入命令来插入行之外，你还可以将原始数据保存在一个文本文件中，并通过加载数据命令将它们加载到表中。例如，使用文本编辑器创建一个名为“products_in.csv”的新文件，在“d:\myProject”目录下（windows）或“Documents”（Mac）下，包含以下记录，其中值用“，”分隔，文件扩展名“.csv”代表用逗号分隔值的文本文件。

\N,PEC,Pencil 3B,500,0.52
\N,PEC,Pencil 4B,200,0.62
\N,PEC,Pencil 5B,100,0.73
\N,PEC,Pencil 6B,500,0.47

你可以将原始数据加载到products表中：

(For Windows)
-- 需要使用正斜杠（而不是反斜杠）作为目录分隔符
mysql> LOAD DATA LOCAL INFILE 'd:/myProject/products_in.csv' INTO TABLE products
          COLUMNS TERMINATED BY ','
          LINES TERMINATED BY '\r\n';
(For Macs)
mysql> LOAD DATA LOCAL INFILE '~/Documents/products_in.csv' INTO TABLE products
          COLUMNS TERMINATED BY ',';
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1007      | PEC         | Pencil 3B | 500      | 0.52  |
| 1008      | PEC         | Pencil 4B | 200      | 0.62  |
| 1009      | PEC         | Pencil 5B | 100      | 0.73  |
| 1010      | PEC         | Pencil 6B | 500      | 0.47  |
+-----------+-------------+-----------+----------+-------+

❗ 注意：

• 你需要提供路径（绝对路径或相对路径）和文件名。使用Unix样式的正斜杠“/”作为目录分隔符，而不是windows样式的反斜杠“\”。
• 默认的行分隔符（或行尾）为“\n”（Unix样式）。如果文本文件是在Windows中，则需要以“\r\n”结尾。
• 默认的列分隔符是“tab”。
• 你需要使用\N作为NULL。

mysqlimport 实用工具

你还可以使用mysqlimport实用程序从文本文件中加载数据。

语法：
mysqlimport -u username -p --local databaseName tableName.tsv

原始数据必须保存在一个TSV（标签-分隔值）文件中，其文件名与表名相同

-- 例如
-- 创建一个名为“products.tsv”的新文件，其中包含以下记录
-- 保存在以下目录："d:\myProject" (for Windows) 或 "Documents" (for Mac)
-- 使用tab分隔数值(不是空格).
\N PEC Pencil 3B 500 0.52
\N PEC Pencil 4B 200 0.62
\N PEC Pencil 5B 100 0.73
\N PEC Pencil 6B 500 0.47
(For Windows)
> cd path-to-mysql-bin
> mysqlimport -u root -p --local southwind d:/myProject/products.tsv
(For Macs)
$ cd /usr/local/mysql/bin
$ ./mysqlimport -u root -p --local southwind ~/Documents/products.tsv

SELECT … INTO OUTFILE …

你可以使用 SELECT ... INTO OUTFILE fileName FROM tableName命令将数据从表导出到文本文件。例如：

(For Windows)
mysql> SELECT * FROM products INTO OUTFILE 'd:/myProject/products_out.csv'
          COLUMNS TERMINATED BY ','
          LINES TERMINATED BY '\r\n';
(For Macs)
mysql> SELECT * FROM products INTO OUTFILE '~/Documents/products_out.csv'
         COLUMNS TERMINATED BY ',';

运行SQL脚本

无需手动输入每个SQL语句，你可以将多条SQL语句保存在一个脚本文件中，并运行该脚本。例如，使用文本编辑器，将以下内容保存为“d:\myProject”（windows）或“Documents”（Mac）目录下的“load_products.sql”脚本。

DELETE FROM products;
INSERT INTO products VALUES (2001, 'PEC', 'Pencil 3B', 500, 0.52),
(NULL, 'PEC', 'Pencil 4B', 200, 0.62),
(NULL, 'PEC', 'Pencil 5B', 100, 0.73),
(NULL, 'PEC', 'Pencil 6B', 500, 0.47);
SELECT * FROM products;

你可以通过以下方式运行脚本：

1. 通过source命令

(For Windows)
mysql> source d:/myProject/load_products.sql
-- 使用Unix风格 (/)作为目录分隔符
(For Macs)
mysql> source ~/Documents/load_products.sql

2. 通过批模式

(For Windows)
> cd path-to-mysql-bin
> mysql -u root -p southwind < d:\myProject\load_products.sql
(For Macs)
$ cd /usr/local/mysql/bin
$ ./mysql -u root -p southwind < ~\Documents\load_products.sql

---

未完待续

下一篇我们接着介绍多表查询。

---

如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！