来源 |  Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共6178字，预计阅读时间35分钟。

第二章  创建和使用数据库

五、操作与修改表

    1.插入数据 

     （1）生成数字型主键数据

     （2）insert语句

    2.更新数据 

    3.删除数据

六、导致错误的语句情况

    1.主键不唯一 

    2.不存在的外键 

    3.非法列值

    4.无效的日期转换

七、Sakila数据库

在「创建和使用数据库（上）」中，我们讲解了「创建数据库」 、「MySQL命令行工具」、「MySQL数据类型」、「创建表」这四部分，在本篇文章中我们将讲解余下部分。

五、操作与修改表

准备好person和favorite_food表后，可以开始研究四个SQL数据语句（insert、update、delete和select）了。

1.插入数据

由于person和favorite_food表中还没有任何数据，因此在四种数据语句中，我们先研究insert语句。insert语句有三个主要组成部分：

• 要添加数据的表的名称

• 要使用的列的名称

• 用于插入列的值

其实并不需要为表中的每一列提供数据（除非表中的所有列都定义为not null）。在某些情况下，初始insert语句中可能并不包含某列的值，这部分将在之后通过update语句获得更新。还有一些情况下，某列的值可能始终为null（例如在发货前取消的客户订单，ship_date列就不用再赋值了）。

（1）生成数字型主键数据

在将数据插入person表之前，最好先讨论一下数字型主键的生成机制。除了随机选择数字外，还可以选择以下方式：

• 查看表中当前主键的最大值，在此基础之上加1；

• 让数据库服务器自动生成。

虽然第一种方式看起来似乎很有效，但在多用户环境中它可能会出现问题，因为两个用户可能同时访问表并生成两个相同的值作为主键。实际上，如今市面上所有的数据库服务器都提供了一种安全、健壮的方法来生成数字型主键。在一些服务器中，比如Oracle数据库就使用了一个单独的模式（schema）对象，称为序列号（sequence）；而在MySQL中，为主键列启用自动递增（auto-increment）功能即可。通常，在创建表的时候就应该执行该操作。现在再介绍一下另一种方案语句——alter table，它的功能是修改现有表的定义：

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

注意：如果在数据库中运行这些语句，则应首先禁用favorite_food表上的外键约束，在完成表的重定义之后，再启用约束。语句如下：

set foreign_key_checks=0; 
ALTER TABLE person  
MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; 
set foreign_key_checks=1;

该语句实质上重新定义了person表中的person_id列。现在再使用describe命令，可以看到person_id的Extra列下列出的自增特性：

mysql> DESC person;
+-------------+------------------------+------+-----+---------+-----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+-----------------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| . | | | | | |
| . | | | | | |
| . | | | | | |

在向person表中插入数据时，只需为person_id列提供一个null值，MySQL将自动向该列提供下一个可用的主键数字（默认情况下，MySQL是从1开始递增的）。

（2）insert语句

现在一切就绪，所以可以向表中添加一些数据了。下面的语句在person表中为William Turner创建一行数据：

mysql> INSERT INTO person 
 -> (person_id, fname, lname, eye_color, birth_date) 
 -> VALUES (null, 'William','Turner', 'BR', '1972-05-27'); 
Query OK, 1 row affected (0.22 sec)

运行结果“Query OK, 1 row affected”表示该语句语法正确，并且有一行被添加到了数据库中（因为它是insert语句）。可以通过select语句查看刚刚添加到表中的这条数据：

mysql> SELECT person_id, fname, lname, birth_date 
 -> FROM person; 
+-----------+---------+--------+------------+ 
| person_id | fname | lname | birth_date | 
+-----------+---------+--------+------------+ 
| 1 | William | Turner | 1972-05-27 | 
+-----------+---------+--------+------------+ 
1 row in set (0.06 sec)

 如你所见，MySQL服务器为主键生成的值为1。因为person表中只有一行，所以我省略了查询条件以获取表中的所有行。但是，如果表中有多行，则可以添加where子句进行过滤，以指定需要获取的数据。检索person_id为1的行：

mysql> SELECT person_id, fname, lname, birth_date 
   -> FROM person 
 -> WHERE person_id = 1; 
  +-----------+---------+--------+------------+ 
| person_id | fname | lname | birth_date | 
  +-----------+---------+--------+------------+ 
| 1 | William | Turner | 1972-05-27 | 
  +-----------+---------+--------+------------+ 
1 row in set (0.00 sec)

 该查询指定了特定主键值，其实还可以指定表中的任意列，比如下面的查询就是用于查找lname列为Turner的行：

mysql> SELECT person_id, fname, lname, birth_date 
   -> FROM person 
 -> WHERE lname = 'Turner'; 
  +-----------+---------+--------+------------+ 
| person_id | fname | lname | birth_date | 
  +-----------+---------+--------+------------+ 
| 1 | William | Turner | 1972-05-27 | 
  +-----------+---------+--------+------------+ 
1 row in set (0.00 sec)

 注意关于insert语句还有几点值得一提：

• 没有为任何地址列提供值。因为这些列允许空值，所以没关系。

• 为birth_date列提供的值是字符串。只要符合表2-4所示的格式，MySQL就会将字符串转换为日期类型。

• 列名和提供的值必须在数字和类型上对应。如果表有七列但是插入的时候只提供六个值，或者提供的值无法转换为相应列的相应数据类型，则会产生错误。

William Turner还提供了他最喜欢的三种食物的信息，因此还需要三条insert语句来存储他的食物偏好：

mysql> INSERT INTO favorite_food (person_id, food)
 -> VALUES (1, 'pizza');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO favorite_food (person_id, food)
 -> VALUES (1, 'cookies');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO favorite_food (person_id, food)
 -> VALUES (1, 'nachos');
Query OK, 1 row affected (0.01 sec)

下面的查询使用order by子句按字母顺序排列William最喜欢的食物：

mysql> SELECT food 
 -> FROM favorite_food 
 -> WHERE person_id = 1 
 -> ORDER BY food; 
+---------+ 
| food | 
+---------+ 
| cookies | 
| nachos | 
| pizza | 
+---------+ 
3 rows in set (0.02 sec)

order by子句告诉服务器如何对查询返回的数据进行排序。如果不用order by子句，就不能保证表中的数据获取的顺序。

William一个人可能太孤单，所以你可以再次执行insert语句将Susan Smith添加到person表：

mysql> INSERT INTO person 
 -> (person_id, fname, lname, eye_color, birth_date, 
 -> street, city, state, country, postal_code) 
 -> VALUES (null, 'Susan','Smith', 'BL', '1975-11-02', 
 -> '23 Maple St.', 'Arlington', 'VA', 'USA', '20220'); 
 Query OK, 1 row affected (0.01 sec)

由于Susan提供了她的地址，上面的insert语句比之前插入William数据用到的语句多五列。如果再次查询该表，可以看到Susan所在行的主键值被赋值为2：

mysql> SELECT person_id, fname, lname, birth_date
   -> FROM person;
+-----------+---------+--------+------------+
  | person_id | fname | lname | birth_date |
+-----------+---------+--------+------------+
  | 1 | William | Turner | 1972-05-27 |
| 2 | Susan | Smith | 1975-11-02 |
  +-----------+---------+--------+------------+
2 rows in set (0.00 sec)

---

可以获取XML格式的数据吗？

如果你使用XML数据，就会很高兴地看到大多数数据库服务器提供了一种从查询结果中生成XML输出的简单方法。例如，对于MySQL，可以在调用mysql工具时使用--xml选项，如此一来，所有的输出都将自动使用xml格式化。下面演示如何获取XML文档格式的favorite_food数据：

C:\database> mysql -u lrngsql -p --xml bank
  Enter password: xxxxxx
Welcome to the MySQL Monitor...
  Mysql> SELECT * FROM favorite_food;
<?xml version="1.0"?>
  <resultset statement="select * from favorite_food"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <row>
 <field name="person_id">1</field>
   <field name="food">cookies</field>
 </row>
   <row>
 <field name="person_id">1</field>
   <field name="food">nachos</field>
 </row>
   <row>
 <field name="person_id">1</field>
   <field name="food">pizza</field>
 </row>
  </resultset>
3 rows in set (0.00 sec)

使用SQL Server，不需要配置命令行工具，只需在每个查询的末尾添加for xml子句，如下所示：

SELECT * FROM favorite_food
  FOR XML AUTO, ELEMENTS

---

2.更新数据

当William Turner的数据被添加到表中时，insert语句中并没有提供地址列的数据。下面演示如何通过update语句更新这些列的数据：

mysql> UPDATE person 
 -> SET street = '1225 Tremont St.', 
 -> city = 'Boston', 
 -> state = 'MA', 
 -> country = 'USA', 
 -> postal_code = '02138' 
 -> WHERE person_id = 1; 
Query OK, 1 row affected (0.04 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

服务器响应了两行消息：“Rows matched: 1”项表示where子句中的条件与表中的一行匹配，“Changed: 1”项表示表中的一行数据已被修改。由于where子句指定了William所在行的主键，所以修改的数据肯定与你所预期的一样。

根据where子句中的条件，还可以使用单个语句修改多行数据。例如，考虑如下where子句：

WHERE person_id < 10

因为William和Susan的person_id值都小于10，所以它们的两行都将被修改。如果省略where子句，那么update语句将修改表中的每一行数据。

3.删除数据

现在看来William和Susan相处得不太好，所以他们两人中得有一人离开。既然William是第一个来的，那么我们可以删除Susan的数据，使用delete语句如下：

mysql> DELETE FROM person 
 -> WHERE person_id = 2; 
Query OK, 1 row affected (0.01 sec)

同样，主键用于定位我们感兴趣的行，因此表中只会有一行数据被删除。与update语句一样，我们也可以根据where子句中的条件删除多行数据，如果省略where子句，则将删除表中所有行。

六、导致错误的语句情况

到目前为止，本章中演示的所有SQL数据语句都是符合标准格式并且能够正常运行的。然而，根据person和favorite_food表的表定义，在插入或修改数据时可能会出现很多运行错误。本节主要展示可能遇到的一些常见错误情况以及MySQL服务器是如何响应它们的。

1.主键不唯一

由于表定义创建了主键约束，所以MySQL确保不会将重复的主键值插入表中。下一条语句忽略person_id列的自增特性，并在person表中创建person_id为1的另一行数据：

mysql> INSERT INTO person 
 -> (person_id, fname, lname, eye_color, birth_date) 
 -> VALUES (1, 'Charles','Fulton', 'GR', '1968-01-15'); 
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

对于当前的方案对象来说，你完全可以创建两个具有相同姓名、地址、出生日期等条目的数据行，但是注意前提是它们的主键不同，也即person_id列的值不同。

2.不存在的外键

favorite_food表的表定义在person_id列上创建了外键约束，此约束确保favorite_food表中所输入person_id列的值都在person表中存在。下面演示违背该约束创建新行的情况：

mysql> INSERT INTO favorite_food (person_id, food) 
 -> VALUES (999, 'lasagna'); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails ('sakila'.'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGN KEY 
('person_id') REFERENCES 'person' ('person_id'))

在这种情况下，因为favorite_food表的某些数据依赖于person表，所以可以将favorite_food表视为子表，将person表视为父表。如果要在两个表中都输入数据，则应该先在父表person中创建一行数据，才能在favorite_food中插入新数据。

注意：只有使用InnoDB存储引擎创建表时，才会强制执行外键约束。我们将在第十二章讨论MySQL的存储引擎。

3.非法列值

person表中的eye_color列仅限于：“BR”表示棕色，“BL”表示蓝色，“GR”表示绿色。如果你试图将该列的值设置为任何其他值，那么你将会收到如下响应：

mysql> UPDATE person 
 -> SET eye_color = 'ZZ' 
 -> WHERE person_id = 1; 
ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1

这个错误消息有点让人迷糊，但你大致能知道服务器对于为eye_color列提供的值并不满意。

4.无效的日期转换

如果构造用于产生date列的字符串，而该字符串与预期格式不匹配，则会产生又一个错误。以下示例使用的日期格式与默认的YYYY-MM-DD日期格式不匹配：

mysql> UPDATE person 
 -> SET birth_date = 'DEC-21-1980' 
 -> WHERE person_id = 1; 
ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date' 
at row 1

一般来说，最好显式指定格式字符串，而不是依赖默认格式。下面是语句的另一个版本，它使用str_to_date函数指定要使用的字符串格式：

mysql> UPDATE person 
 -> SET birth_date = str_to_date('DEC-21-1980' , '%b-%d-%Y') 
 -> WHERE person_id = 1; 
Query OK, 1 row affected (0.12 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

不仅数据库服务器很高兴，威廉也很高兴（我们刚刚让他年轻了8岁，而且还不需要昂贵的整容手术！）。

注意：在本章的前面介绍各种时态数据类型时，我展示过日期格式字符串，如YYYY-MM-DD。虽然许多数据库服务器使用这种格式，但MySQL使用%Y指定四位数字的年份。以下是在MySQL中将字符串转换为datetime时可能需要的其他一些格式：

%a The short weekday name, such as Sun, Mon, ... 
%b The short month name, such as Jan, Feb, ... 
%c The numeric month (0..12) 
%d The numeric day of the month (00..31) 
%f The number of microseconds (000000..999999) 
%H The hour of the day, in 24-hour format (00..23) 
%h The hour of the day, in 12-hour format (01..12) 
%i The minutes within the hour (00..59) 
%j The day of year (001..366) 
%M The full month name (January..December) 
%m The numeric month 
%p AM or PM 
%s The number of seconds (00..59) 
%W The full weekday name (Sunday..Saturday) 
%w The numeric day of the week (0=Sunday..6=Saturday) 
%Y The four-digit year

七、Sakila数据库

在本书的其余部分，大多数示例将会用到名叫Sakila的示例数据库，该数据库由使用MySQL的好心人提供。这个数据库是一个DVD租赁连锁公司的模型，虽然有点过时，但只要发挥你的想象力，就可以把它当作一家视频流媒体公司。该数据库中包括的表格有customer、film、actor、payment、rental和category。当你按照本章开头介绍的步骤加载MySQL服务器并生成示例数据时，应该就已经创建了数据库的整个方案和示例数据。有关表字段以及相互关系的图示，请参见附录A。

下表（2-9）显示了Sakila中使用的一些表，以及每个表的简单定义：

 

你可以随意使用这些表，还能新增自己的表以扩展业务功能。如果你修改了数据库之后，还想使用原来完好无损的数据库的话，就可以删除数据库，然后下载文件重新创建示例数据库。如果你使用的是临时会话，那么会话关闭时，你所做的所有更改都将丢失，因此你可能需要保留所做更改的脚本（代码），以便下一次重新执行它们。

如果要查看数据库中可用的表，可以使用show tables命令，如下所示：

mysql> show tables; 
+----------------------------+ 
| Tables_in_sakila | 
+----------------------------+ 
| actor | 
| actor_info | 
| address | 
| category | 
| city | 
| country | 
| customer | 
| customer_list | 
| film | 
| film_actor | 
| film_category | 
| film_list | 
| film_text | 
| inventory | 
| language | 
| nicer_but_slower_film_list | 
| payment | 
| rental | 
| sales_by_film_category | 
| sales_by_store | 
| staff | 
| staff_list | 
| store | 
+----------------------------+ 
23 rows in set (0.02 sec)

除了Sakila方案中的23个表之外，该列表还包括本章中创建的两个表：person和favorite_food，我们在后面的章节中不会使用它们了，所以可以通过使用下面的命令来删除它们：

mysql> DROP TABLE favorite_food; 
Query OK, 0 rows affected (0.56 sec) 
mysql> DROP TABLE person; 
Query OK, 0 rows affected (0.05 sec)

如果要查看表中的列，可以使用describe命令。下面是customer表的describe输出：

mysql> desc customer; 
+-------------+--------------+------+-----+-------------+----------------------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+--------------+------+-----+-------------+----------------------+ 
| customer_id | smallint(5) | NO | PRI | NULL | auto_increment | 
 unsigned  
| store_id | tinyint(3) | NO | MUL | NULL | | 
 unsigned  
| first_name | varchar(45) | NO | | NULL | | 
| last_name | varchar(45) | NO | MUL | NULL | | 
| email | varchar(50) | YES | | NULL | | 
| address_id | smallint(5) | NO | MUL | NULL | | 
 unsigned  
| active | tinyint(1) | NO | | 1 | | 
| create_date | datetime | NO | | NULL | | 
| last_update | timestamp | YES | | CURRENT_ | DEFAULT_GENERATED on 
 TIMESTAMP update CURRENT_ 
 TIMESTAMP |  
+-------------+--------------+------+-----+-------------+----------------------+

你对示例数据库越熟悉，就越能理解示例，从而更好地理解后面章节中介绍的概念。