在 MySQL 中插入，更新和删除数据 · ZetCode 中文系列教程

# 在 MySQL 中插入，更新和删除数据 > 原文： [http://zetcode.com/databases/mysqltutorial/datamanipulation/](http://zetcode.com/databases/mysqltutorial/datamanipulation/) 在 MySQL 教程的这一部分中，我们将从 MySQL 表中插入，更新和删除数据。我们将使用`INSERT`，`DELETE`和`UPDATE`语句。这些语句是 SQL 数据操作语言 _DML_ 的一部分。 ## 插入数据 `INSERT`语句用于将数据插入表中。我们将创建一个新表，并在其中进行示例。 ```sql mysql> CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title VARCHAR(100), -> Author VARCHAR(60)); ``` 我们使用`Id`，`Title`和`Author`列创建一个新表`Books`。 ```sql mysql> INSERT INTO Books(Id, Title, Author) VALUES(1, 'War and Peace', -> 'Leo Tolstoy'); ``` 这是经典的`INSERT` SQL 语句。我们在表名之后指定了所有列名，并在`VALUES`关键字之后指定了所有值。我们将第一行添加到表中。 ```sql mysql> SELECT * FROM Books; +----+---------------+-------------+ | Id | Title | Author | +----+---------------+-------------+ | 1 | War and Peace | Leo Tolstoy | +----+---------------+-------------+ ``` 我们已经将第一行插入到`Books`表中。 ```sql 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。 ```sql mysql> SELECT * FROM Books; +----+------------------------+--------------------+ | Id | Title | Author | +----+------------------------+--------------------+ | 1 | War and Peace | Leo Tolstoy | | 2 | The Brothers Karamazov | Fyodor Dostoyevsky | +----+------------------------+--------------------+ ``` 这是`Books`表中的内容。 ```sql mysql> INSERT INTO Books VALUES(3, 'Crime and Punishment', -> 'Fyodor Dostoyevsky'); ``` 在此 SQL 语句中，我们没有在表名之后指定任何列名。在这种情况下，我们必须提供所有值。 ```sql mysql> REPLACE INTO Books VALUES(3, 'Paradise Lost', 'John Milton'); Query OK, 2 rows affected (0.00 sec) ``` `REPLACE`语句是对 SQL 标准的 MySQL 扩展。如果它与现有行发生冲突，它将插入新行或替换旧行。在我们的表中，带有`Id=3`的行。因此，我们之前的语句将其替换为新行。有一则消息说两行受到影响。删除了一行并插入了一行。 ```sql mysql> SELECT * FROM Books WHERE Id=3; +----+---------------+-------------+ | Id | Title | Author | +----+---------------+-------------+ | 3 | Paradise Lost | John Milton | +----+---------------+-------------+ ``` 这就是我们现在在第三栏中。我们可以在一个语句中同时使用`INSERT`和`SELECT`语句。 ```sql mysql> CREATE TABLE Books2(Id INTEGER PRIMARY KEY AUTO_INCREMENT, -> Title VARCHAR(100), Author VARCHAR(60)) type=MEMORY; ``` 首先，我们在内存中创建一个名为`Books2`的临时表。 ```sql mysql> INSERT INTO Books2 SELECT * FROM Books; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 ``` 在这里，我们将所有数据插入从`Books`表中选择的`Books2`中。 ```sql mysql> SELECT * FROM Books2; +----+------------------------+--------------------+ | Id | Title | Author | +----+------------------------+--------------------+ | 1 | War and Peace | Leo Tolstoy | | 2 | The Brothers Karamazov | Fyodor Dostoyevsky | | 3 | Paradise Lost | John Milton | +----+------------------------+--------------------+ ``` 我们验证。一切都好。 ```sql 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`文件中。 ```sql mysql> SELECT * INTO OUTFILE '/tmp/books.csv' -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> FROM Books; ``` 我们将`Books`表中的数据写入`books.csv`文件。数据将为 CSV 格式。 ```sql $ 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`文件的内容。 ```sql mysql> TRUNCATE Books; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Books; Empty set (0.00 sec) ``` 我们从表中删除所有数据。 ```sql mysql> LOAD DATA INFILE '/tmp/books.csv' -> INTO TABLE Books -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n'; ``` 我们使用`LOAD DATA INFILE`语法从`books.csv`文件填充`Books`表。 ```sql 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 文件。 ```sql $ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Books' > books.xml ``` `mysql`监视器具有`--xml`选项，使我们能够以 XML 格式转储数据。 `-e`选项执行一条语句并退出监视器。 ```sql $ 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 文件。 ```sql 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`表。 ```sql mysql> DELETE FROM Books2 WHERE Id=1; ``` 我们用`Id=1`删除一行。 ```sql mysql> SELECT * FROM Books2; +----+------------------------+--------------------+ | Id | Title | Author | +----+------------------------+--------------------+ | 2 | The Brothers Karamazov | Fyodor Dostoyevsky | | 3 | Paradise Lost | John Milton | +----+------------------------+--------------------+ ``` 我们验证数据。 ```sql mysql> DELETE FROM Books2; mysql> TRUNCATE Books2; ``` 这两个 SQL 语句删除表中的所有数据。 ## 更新数据 `UPDATE`语句用于更改表的选定行中的列的值。 ```sql 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'`。以下语句显示了如何完成此操作。 ```sql mysql> UPDATE Books SET Author='Lev Nikolayevich Tolstoy' -> WHERE Id=1; ``` SQL 语句将`Id=1`列的`author`列设置为`'Lev Nikolayevich Tolstoy'`。 ```sql mysql> SELECT * FROM Books WHERE Id=1; +----+---------------+--------------------------+ | Id | Title | Author | +----+---------------+--------------------------+ | 1 | War and Peace | Lev Nikolayevich Tolstoy | +----+---------------+--------------------------+ ``` 该行已正确更新。在 MySQL 教程的这一部分中，我们已经在数据库表中插入，删除和更新了数据。