MySQL:插入、更新与删除数据

本章将使用样例表person，创建语句如下：
CREATE TABLE person
(
id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
name   CHAR(40) NOT NULL DEFAULT '',
age    INT NOT NULL DEFAULT 0,
info   CHAR(50) NULL,
PRIMARY KEY (id)
);
向表中所有字段插入值的方法有两种：一种是指定所有字段名，另一种是完全不指定字段名。
【例8.1】在person表中，插入一条新记录，id值为1，name值为Green，age值为21，info值为Lawyer，SQL语句如下：
执行插入操作之前，使用SELECT语句查看表中的数据：
 SELECT * FROM person;
Empty set (0.00 sec)
结果显示当前表为空，没有数据，接下来执行插入操作：
 INSERT INTO person (id ,name, age , info)
      VALUES (1,'Green', 21, 'Lawyer');
Query OK, 1 row affected (0.00 sec)
语句执行完毕，查看执行结果：
 SELECT * FROM person;
+----+--------+-----+------------+
| id | name  | age | info      |
+----+--------+-----+------------+
|  1 | Green |  21 | Lawyer   |
+----+--------+-----+------------+
可以看到插入记录成功。在插入数据时，指定了person表的所有字段，因此将为每一个字段插入新的值。
INSERT语句后面的列名称顺序可以不是person表定义时的顺序。即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以，如【例8.2】所示。
【例8.2】在person表中，插入一条新记录，id值为2，name值为Suse，age值为22，info值为dancer，SQL语句如下：
 INSERT INTO person (age ,name, id , info)
      VALUES (22, 'Suse', 2, 'dancer');
语句执行完毕，查看执行结果：
 SELECT * FROM person;
+----+--------+-----+------------+
| id | name  | age | info      |
+----+--------+-----+------------+
|  1 | Green |  21 | Lawyer  |
|  2 | Suse  |  22 | dancer  |
+----+--------+-----+------------+
由结果可以看到，INSERT语句成功插入了一条记录。
使用INSERT插入数据时，允许列名称列表column_list为空，此时，值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同，如【例8.3】所示。
【例8.3】在person表中，插入一条新记录，id值为3，name值为Mary，age值为24，info值为Musician，SQL语句如下：
 INSERT INTO person
     VALUES (3,'Mary', 24, 'Musician');
Query OK, 1 row affected (0.00 sec)
语句执行完毕，查看执行结果：
 SELECT * FROM person;
+----+--------+-----+------------+
| id | name   | age | info    |
+----+--------+-----+------------+
|  1 | Green |  21 | Lawyer  |
|  2 | Suse  |  22 | dancer  |
|  3 | Mary  |  24 | Musician|
+----+--------+-----+------------+
【例8.4】在person表中，插入一条新记录，name值为Willam，age值为20，info值为sports man，SQL语句如下：
 INSERT INTO person (name, age,info)
     VALUES('Willam', 20, 'sports man');
Query OK, 1 row affected (0.00 sec)
提示信息表示插入一条记录成功。使用SELECT查询表中的记录，查询结果如下：
 SELECT * FROM person;
+----+--------+-----+------------+
| id | name  | age | info      |
+----+--------+-----+-------------+
|  1 | Green |  21 | Lawyer    |
|  2 | Suse  |  22 | dancer    |
|  3 | Mary  |  24 | Musician  |
|  4 | Willam | 20  | sports man |
+----+--------+-----+----------------+
【例8.5】在person表中，插入一条新记录，name值为laura，age值为25，SQL语句如下：
 INSERT INTO person (name, age ) VALUES ('Laura', 25);
语句执行完毕，查看执行结果，
 SELECT * FROM person;
+----+--------+-----+------------+
| id | name   | age | info     |
+----+--------+-----+------------+
|  1 | Green |  21 | Lawyer   |
|  2 | Suse  |  22 | dancer   |
|  3 | Mary  |  24 | Musician  |
|  4 | Willam |  20 | sports man |
|  5 | Laura  |  25 | NULL    |
+----+----------+-----+------------+

【例8.6】在person表中，在name、age和info字段指定插入值，同时插入3条新记录，SQL语句如下：
INSERT INTO person(name, age, info)
VALUES ('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');
语句执行结果如下：
 INSERT INTO person(name, age, info)
     VALUES ('Evans',27, 'secretary'),
     ('Dale',22, 'cook'),
     ('Edison',28, 'singer');
语句执行完毕，查看执行结果：
  SELECT * FROM person;
+----+-----------+-----+------------+
| id | name    | age | info     |
+----+-----------+------+------------+
|  1 | Green   |  21 | Lawyer  |
|  2 | Suse    |  22 | dancer   |
|  3 | Mary    |  24 | Musician |
|  4 | Willam  |  20 | sports man|
|  5 | Laura   |  25 | NULL    |
|  6 | Evans   |  27 | secretary  |
|  7 | Dale    |  22 | cook      |
|  8 | Edison  |  28 | singer     |
+----+-----------+------+--------------+
【例8.7】在person表中，不指定插入列表，同时插入2条新记录，SQL语句如下：
INSERT INTO person
VALUES (9,'Harry',21, 'magician'),
(NULL,'Harriet',19, 'pianist');
语句执行结果如下：
 INSERT INTO person
     VALUES (9,'Harry',21, 'magician'),
     (NULL,'Harriet',19, 'pianist');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
语句执行完毕，查看执行结果：
 SELECT * FROM person;

【例8.8】从person_old表中查询所有的记录，并将其插入到person表中。
首先，创建一个名为person_old的数据表，其表结构与person结构相同，SQL语句如下：
CREATE TABLE person_old
(
id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
name   CHAR(40) NOT NULL DEFAULT '',
age    INT NOT NULL DEFAULT 0,
info   CHAR(50) NULL,
PRIMARY KEY (id)
);
向person_old表中添加两条记录：
 INSERT INTO person_old
     VALUES (11,'Harry',20, 'student'), (12,'Beckham',31, 'police');

 SELECT * FROM person_old;
+----+-------------+-----+---------+
| id | name     | age | info     |
+----+----------+-------+----------+
| 11 | Harry   |  20  | student |
| 12 | Beckham |  31 | police   |
+----+-----------+------+----------+
可以看到，插入记录成功，peson_old表中现在有两条记录。接下来将person_old表中所有的记录插入person表中，SQL语句如下：
INSERT INTO person(id, name, age, info)
SELECT id, name, age, info FROM person_old;
语句执行结果如下：
 INSERT INTO person(id, name, age, info)
     SELECT id, name, age, info FROM person_old;
语句执行完毕，查看执行结果：
 SELECT * FROM person;

【例8.9】在person表中，更新id值为11的记录，将age字段值改为15，将name字段值改为LiMing，SQL语句如下：
UPDATE person SET age = 15, name=’LiMing’ WHERE id = 11;
更新操作执行前可以使用SELECT语句查看当前的数据：
 SELECT * FROM person WHERE id=11;
+----+-------+-----+---------+
| id | name  | age | info    |
+----+-------+-----+---------+
| 11 | Harry |  20 | student |
+----+-------+-----+---------+
由结果可以看到更新之前，id等于11的记录的name字段值为harry，age字段值为20，下面使用UPDATE语句更新数据，语句执行结果如下：
 UPDATE person SET age = 15, name=’LiMing’ WHERE id = 11;
语句执行完毕，查看执行结果：
 SELECT * FROM person WHERE id=11;
+----+--------+-----+---------+
| id | name   | age | info   |
+----+--------+-----+---------+
| 10 | LiMing |  15 | student |
+----+--------+-----+---------+

【例8.10】在person表中，更新age值为19~22的记录，将info字段值都改为student，SQL语句如下：
UPDATE person SET info=’student’  WHERE id  BETWEEN 19 AND 22;
更新操作执行前可以使用SELECT语句查看当前的数据：
 SELECT * FROM person WHERE age BETWEEN 19 AND 22;
可以看到，这些age字段值在19~22之间的记录的info字段值各不相同。下面使用UPDATE语句更新数据，语句执行结果如下：
 UPDATE person SET info=’student’ WHERE age BETWEEN 19 AND 22;
语句执行完毕，查看执行结果：
 SELECT * FROM person WHERE age BETWEEN 19 AND 22;

【例8.11】在person表中，删除id等于11的记录，SQL语句如下：
执行删除操作前，使用SELECT语句查看当前id=11的记录：
 SELECT * FROM person WHERE id=11;
可以看到，现在表中有id=11的记录，下面使用DELETE语句删除该记录，语句执行结果如下：
 DELETE FROM person WHERE id = 11;
语句执行完毕，查看执行结果：
 SELECT * FROM person WHERE id=11;
查询结果为空，说明删除操作成功。
【例8.12】在person表中，使用DELETE语句同时删除多条记录，在前面UPDATE语句中将age字段值在19~22之间的记录的info字段值修改为student，在这里删除这些记录，SQL语句如下：
DELETE FROM person WHERE age BETWEEN 19 AND 22;
执行删除操作前，使用SELECT语句查看当前的数据：
 SELECT * FROM person WHERE age BETWEEN 19 AND 22;
可以看到，这些age字段值在19~22之间的记录存在表中。下面使用DELETE删除这些记录：
 DELETE FROM person WHERE age BETWEEN 19 AND 22;
语句执行完毕，查看执行结果：
 SELECT * FROM person WHERE age BETWEEN 19 AND 22;
查询结果为空，删除多条记录成功。
【例8.13】删除person表中所有记录，SQL语句如下：
DELETE FROM person;
执行删除操作前，使用SELECT语句查看当前的数据：
 SELECT * FROM person;
结果显示person表中还有4条记录，执行DELETE语句删除这4条记录：
 DELETE FROM person;
语句执行完毕，查看执行结果：
 SELECT * FROM person;
查询结果为空，删除表中所有记录成功，现在person表中已经没有任何数据记录。

如果想删除表中的所有记录，还可以使用TRUNCATE TABLE语句，TRUNCATE将直接删除原来的表并重新创建一个表，其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。

综合案例

  创建数据表books，并定义各个字段。
CREATE TABLE books
(
id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name       VARCHAR(40) NOT NULL,
authors  VARCHAR(200) NOT NULL,
price        INT(11) NOT NULL,
pubdate     YEAR NOT NULL,
note        VARCHAR(255) NULL,
num        INT NOT NULL DEFAULT 0
);
  将表8.2中的记录插入books表中，分别使用不同的方法插入记录，执行过程如下。
表创建好之后，使用SELECT语句查看表中的数据，结果如下：
 SELECT * FROM books;
Empty set (0.00 sec)
可以看到，当前表中为空，没有任何数据，下面向表中插入记录。
（1）指定所有字段名称插入记录，SQL语句如下。
 INSERT INTO books
      (id, name, authors, price, pubdate,note,num)
      VALUES(1, 'Tale of AAA', 'Dickes', 23, '1995', 'novel',11);
Query OK, 1 row affected (0.02 sec)
语句执行成功，插入了一条记录。
（2）不指定字段名称插入记录，SQL语句如下。
 INSERT INTO books
      VALUES (2,'EmmaT','Jane lura',35,'1993', 'joke',22);
Query OK, 1 row affected (0.01 sec)
语句执行成功，插入了一条记录。
使用SELECT语句查看当前表中的数据：
 SELECT * FROM books;
+----+------------------+-----------+-------+---------+---------+-----+
| id | name        | authors   | price | pubdate | note   | num |
+----+-----------------+-----------+---------+---------+---------+-----+
|  1 | Tale of AAA | Dickes   |    23 |  1995 | novel  |  11  |
|  2 | EmmaT    | Jane lura  |    35 |  1993 | joke   |  22  |
+----+----------------+-------------+---------+----------+-------+------+
2 rows in set (0.00 sec)
可以看到，两条语句分别成功插入了两条记录。
（3）同时插入多条记录。
使用INSERT语句将剩下的多条记录插入表中，SQL语句如下：
 INSERT INTO books
     VALUES(3, 'Story of Jane', 'Jane Tim', 40, '2001', 'novel', 0),
     (4, 'Lovey Day', 'George Byron', 20, '2005', 'novel', 30),
     (5, 'Old Land', 'Honore Blade', 30, '2010', 'law',0),
     (6,'The Battle','Upton Sara',33,'1999', 'medicine',40),
     (7,'Rose Hood','Richard Kale',28,'2008', 'cartoon',28);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
由结果可以看到，语句执行成功，总共插入了5条记录，使用SELECT语句查看表中所有的记录：
 SELECT * FROM books;
+----+----------------------+--------------+---------+--------------+--------------+-----+
| id | name            | authors   | price  |  pubdate   | note      | num |
+----+------------------------+-------------+----------+-------------+---------------+-----+
|  1 | Tale of AAA    | Dickes     |    23  |    1995  | novel     |  11 |
|  2 | EmmaT         | Jane lura    |    35  |    1993   | joke    |  22 |
|  3 | Story of Jane    | Jane Tim     |    40  |    2001  | novel    |   0 |
|  4 | Lovey Day      | George Byron |    20  |    2005  | novel    |  30 |
|  5 | Old Land       | Honore Blade |    30  |    2010  | law      |   0 |
|  6 | The Battle      | Upton Sara   |    33  |    1999  | medicine  |  40 |
|  7 | Rose Hood      | Richard Kale |    28  |    2008  | cartoon   |  28 |
+----+-----------------------+-----------------+----------+--------------+-------------+-----+
7 rows in set (0.00 sec)
由结果可以看到，所有记录成功插入表中。
  将小说类型（novel）的书的价格都增加5。
执行该操作的SQL语句为：
UPDATE books SET price = price + 5 WHERE note = ‘novel’;
执行前先使用SELECT语句查看当前记录：
 SELECT id, name, price, note FROM books WHERE note = 'novel';
+----+----------------------+-------+-------+
| id | name            | price | note  |
+----+----------------------+-------+-------+
|  1 | Tale of AAA    |    23 | novel |
|  3 | Story of Jane    |    40 | novel |
|  4 | Lovey Day      |    20 | novel |
+----+---------------+-------+-------+
3 rows in set (0.00 sec)
使用UPDATE语句执行更新操作：
 UPDATE books SET price = price + 5 WHERE note = 'novel';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
由结果可以看到，该语句对3条记录进行了更新，使用SELECT语句查看更新结果：
 SELECT id, name, price, note FROM books WHERE note = 'novel';
+----+-------------------+-------+-------+
| id | name         | price | note  |
+----+------------------+----------+-------+
|  1 | Tale of AAA  |     28 | novel |
|  3 | Story of Jane  |     45 | novel |
|  4 | Lovey Day   |     25 | novel |
+----+-----------------+-----------+-------+
对比可知，price的值都在原来的价格之上增加了5。
  将名称为EmmaT的书的价格改为40，并将说明改为drama。
修改语句为：
UPDATE books SET price=40,note= 'drama 'WHERE name= 'EmmaT ';
执行修改前，使用SELECT语句查看当前记录：
 SELECT name, price, note FROM books WHERE name='EmmaT';
+---------+-------+------+
| name  | price | note |
+----------+-------+------+
| EmmaT |  35 | joke |
+---------+-------+------+
1 row in set (0.00 sec)
下面执行修改操作：
 UPDATE books SET price=40,note='drama' WHERE name='EmmaT';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
结果显示修改了一条记录，使用SELECT查看执行结果：
 SELECT name, price, note FROM books WHERE name='EmmaT';
+---------+--------+-------+
| name  |  price | note  |
+---------+---------+-------+
| EmmaT |  40  | drama |
+---------+---------+-------+
1 row in set (0.00 sec)
可以看到，price和note字段的值已经改变，修改操作成功。
  删除库存为0的记录。
删除库存为0的语句为：
DELETE FROM books WHERE num=0;
删除之前使用SELECT语句查看当前记录：
 SELECT * FROM books WHERE num=0;
+----+----------------+----------------+---------+---------+-------+-----+
| id | name        | authors    | price  | pubdate |  note | num |
+----+-----------------+---------------+----------+---------+-------+-----+
|  3 | Story of Jane | Jane Tim    |    45 |  2001 | novel  |   0 |
|  5 | Old Land    | Honore Blade |   30 |  2010 |  law  |   0 |
+----+------------------+----------------+--------+---------+--------+-----+
2 rows in set (0.00 sec)
可以看到，当前有两条记录的num值为0，下面使用DELETE语句删除这两条记录，SQL语句如下：
 DELETE FROM books WHERE num=0;
Query OK, 2 rows affected (0.00 sec)
语句执行成功，查看操作结果：
 SELECT * FROM books WHERE num=0;
Empty set (0.00 sec)
可以看到，查询结果为空，表中已经没有库存量为0的记录。