MySQL 插入，更新和删除

最后修改于 2023 年 1 月 10 日

在本 MySQL 教程中，我们将从 MySQL 表中插入，更新和删除数据。我们将使用 INSERT, DELETE 和 UPDATE 语句。这些语句是 SQL 数据操作语言 (DML) 的一部分。

插入数据

INSERT 语句用于将数据插入到表中。

我们将创建一个新表，在其中进行示例。

mysql> CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title VARCHAR(100),
    -> Author VARCHAR(60));

我们创建一个新表 Books，具有 Id、Title 和 Author 列。

mysql> INSERT INTO Books(Id, Title, Author) VALUES(1, 'War and Peace', 
    -> 'Leo Tolstoy');

这是经典的 INSERT SQL 语句。我们在表名后指定了所有列名，并在 VALUES 关键字后指定了所有值。我们将第一行添加到表中。

mysql> SELECT * FROM Books;
+----+---------------+-------------+
| Id | Title         | Author      |
+----+---------------+-------------+
|  1 | War and Peace | Leo Tolstoy |
+----+---------------+-------------+

我们将第一行插入到 Books 表中。

mysql> INSERT INTO Books(Title, Author) VALUES ('The Brothers Karamazov',
    -> 'Fyodor Dostoyevsky');

我们在 Books 表中添加一个新标题。我们省略了 Id 列。Id 列具有 AUTO_INCREMENT 属性。这意味着 MySQL 将自动增加 Id 列。AUTO_INCREMENT 列递增的值由 auto_increment_increment 系统变量控制。默认情况下，它是 1。

mysql> SELECT * FROM Books;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  1 | War and Peace          | Leo Tolstoy        |
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
+----+------------------------+--------------------+

这是我们在 Books 表中的内容。

mysql> INSERT INTO Books VALUES(3, 'Crime and Punishment',
    -> 'Fyodor Dostoyevsky');

在此 SQL 语句中，我们没有在表名后指定任何列名。在这种情况下，我们必须提供所有值。

mysql> REPLACE INTO Books VALUES(3, 'Paradise Lost', 'John Milton');
Query OK, 2 rows affected (0.00 sec)

REPLACE 语句是 SQL 标准的 MySQL 扩展。如果与现有行冲突，它会插入新行或替换旧行。在我们的表中，有一行 Id=3。因此，我们之前的语句用新行替换了它。有一条消息说有两行受到影响。一行被删除，一行被插入。

mysql> SELECT * FROM Books WHERE Id=3;
+----+---------------+-------------+
| Id | Title         | Author      |
+----+---------------+-------------+
|  3 | Paradise Lost | John Milton |
+----+---------------+-------------+

这就是我们现在在第三列中的内容。

我们可以在一个语句中使用 INSERT 和 SELECT 语句。

mysql> CREATE TABLE Books2(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    -> Title VARCHAR(100), Author VARCHAR(60)) type=MEMORY;

首先，我们在内存中创建一个名为 Books2 的临时表。

mysql> INSERT INTO Books2 SELECT * FROM Books;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

在这里，我们将从 Books 表中选择的所有数据插入到 Books2 中。

mysql> SELECT * FROM Books2;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  1 | War and Peace          | Leo Tolstoy        |
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
|  3 | Paradise Lost          | John Milton        |
+----+------------------------+--------------------+

我们验证一下。一切正常。

mysql> INSERT INTO Books(Title, Author) VALUES ('The Insulted and Humiliated',
    -> 'Fyodor Dostoyevsky'), ('Cousin Bette', 'Honore de Balzac');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

我们可以使用 INSERT 语句向表中插入多行。这里我们展示了如何操作。

我们可以从文件系统中插入数据。首先，我们将数据从 Books 表转储到 books.csv 文件中。

mysql> SELECT * INTO OUTFILE '/tmp/books.csv'
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> FROM Books;

我们将数据从 Books 表写入 books.csv 文件。数据将采用 CSV 格式。

$ cat /tmp/books.csv 
1,War and Peace,Leo Tolstoy
2,The Brothers Karamazov,Fyodor Dostoyevsky
3,Paradise Lost,John Milton
4,The Insulted and Humiliated,Fyodor Dostoyevsky
5,Cousin Bette,Honore de Balzac

我们显示 books.csv 文件的内容。

mysql> TRUNCATE Books;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Books;
Empty set (0.00 sec)

我们从表中删除所有数据。

mysql> LOAD DATA INFILE '/tmp/books.csv'    
    -> INTO TABLE Books    
    -> FIELDS TERMINATED BY ','    
    -> LINES TERMINATED BY '\n';

我们使用 LOAD DATA INFILE 语法从 books.csv 文件填充 Books 表。

mysql> SELECT * FROM Books;
+----+-----------------------------+--------------------+
| Id | Title                       | Author             |
+----+-----------------------------+--------------------+
|  1 | War and Peace               | Leo Tolstoy        |
|  2 | The Brothers Karamazov      | Fyodor Dostoyevsky |
|  3 | Paradise Lost               | John Milton        |
|  4 | The Insulted and Humiliated | Fyodor Dostoyevsky |
|  5 | Cousin Bette                | Honore de Balzac   |
+----+-----------------------------+--------------------+

一切正常。

我们也可以从 XML 文件中加载数据。首先，我们将数据从 Books 表写入 XML 文件。

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Books' > books.xml

mysql 监视器有一个 --xml 选项，它使我们能够以 XML 格式转储数据。-e 选项执行一个语句并退出监视器。

$ cat books.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM mydb.Books
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="Id">1</field>
    <field name="Title">War and Peace</field>
    <field name="Author">Leo Tolstoy</field>
  </row>

  <row>
    <field name="Id">2</field>
    <field name="Title">The Brothers Karamazov</field>
    <field name="Author">Fyodor Dostoyevsky</field>
  </row>

  <row>
    <field name="Id">3</field>
    <field name="Title">Paradise Lost</field>
    <field name="Author">John Milton</field>
  </row>

  <row>
    <field name="Id">4</field>
    <field name="Title">The Insulted and Humiliated</field>
    <field name="Author">Fyodor Dostoyevsky</field>
  </row>

  <row>
    <field name="Id">5</field>
    <field name="Title">Cousin Bette</field>
    <field name="Author">Honore de Balzac</field>
  </row>
</resultset>

这是我们的 XML 文件。

mysql> LOAD XML INFILE '/home/vronskij/programming/mysql/books.xml' INTO TABLE Books;

我们从 XML 文件中加载数据。请注意，LOAD XML 语句适用于 MySQL 5.5 及更高版本。

删除数据

在 MySQL 中，我们可以使用 DELETE 和 TRUNCATE 语句删除数据。TRUNCATE 语句是 SQL 规范的 MySQL 扩展。首先，我们将从表中删除一行。我们将使用之前创建的 Books2 表。

mysql> DELETE FROM Books2 WHERE Id=1;

我们删除 Id=1 的行。

mysql> SELECT * FROM Books2;
+----+------------------------+--------------------+
| Id | Title                  | Author             |
+----+------------------------+--------------------+
|  2 | The Brothers Karamazov | Fyodor Dostoyevsky |
|  3 | Paradise Lost          | John Milton        |
+----+------------------------+--------------------+

我们验证数据。

mysql> DELETE FROM Books2;
mysql> TRUNCATE Books2;

这两个 SQL 语句删除表中的所有数据。

更新数据

UPDATE 语句用于更改表中选定行的列的值。

mysql> SELECT * FROM Books;
+----+-----------------------------+--------------------+
| Id | Title                       | Author             |
+----+-----------------------------+--------------------+
|  1 | War and Peace               | Leo Tolstoy        |
|  2 | The Brothers Karamazov      | Fyodor Dostoyevsky |
|  3 | Paradise Lost               | John Milton        |
|  4 | The Insulted and Humiliated | Fyodor Dostoyevsky |
|  5 | Cousin Bette                | Honore de Balzac   |
+----+-----------------------------+--------------------+

我们重新创建 Books 表。这些是行。

假设我们想将“Leo Tolstoy”更改为“Lev Nikolayevich Tolstoy”表。以下语句显示了如何实现此目的。

mysql> UPDATE Books SET Author='Lev Nikolayevich Tolstoy'
    -> WHERE Id=1;

SQL 语句将 author 列设置为“Lev Nikolayevich Tolstoy”，对于 Id=1 的列。

mysql> SELECT * FROM Books WHERE Id=1;
+----+---------------+--------------------------+
| Id | Title         | Author                   |
+----+---------------+--------------------------+
|  1 | War and Peace | Lev Nikolayevich Tolstoy |
+----+---------------+--------------------------+

该行已正确更新。

在本 MySQL 教程中，我们插入，删除了数据库表中的数据并进行了更新。

电子书

MySQL 插入，更新和删除

插入数据

删除数据

更新数据