第2章 创建和填充数据库
# 第2章 创建和填充数据库
本章将为你提供创建第一个数据库，以及创建本书示例所用的表和相关数据所需的信息。你还将了解各种数据类型，并学习如何使用它们创建表。由于本书中的示例是在MySQL数据库上执行的，因此本章在一定程度上会侧重于MySQL的特性和语法，但大多数概念适用于任何数据库服务器。
# 创建MySQL数据库
如果你已经有可用的MySQL数据库服务器，那么可以跳过安装说明，直接从表2-1中的说明开始操作。不过请记住，本书假设你使用的是MySQL 6.0或更高版本，所以如果你使用的是早期版本，可能需要考虑升级服务器或安装其他服务器。
以下说明展示了在Windows计算机上安装MySQL 6.0服务器所需的最少步骤：
- 访问MySQL数据库服务器的下载页面：http://dev.mysql.com/downloads 。如果你要下载6.0版本，完整的URL是http://dev.mysql.com/downloads/mysql/6.0.html 。
- 下载Windows Essentials（x86）安装包，其中仅包含常用工具。
- 当出现 “你想运行还是保存此文件？” 的提示时，点击 “运行”。
- 出现 “MySQL Server 6.0 - 安装向导” 窗口，点击 “下一步”。
- 选中 “典型安装” 单选按钮，然后点击 “下一步”。
- 点击 “安装”。
- 出现 “MySQL Enterprise” 窗口，点击两次 “下一步”。
- 安装完成后，确保勾选 “现在配置MySQL服务器” 旁边的复选框，然后点击 “完成”。这将启动配置向导。
- 配置向导启动后，选中 “标准配置” 单选按钮，然后同时选中 “作为Windows服务安装” 和 “将Bin目录包含到Windows路径中” 复选框，点击 “下一步”。
- 选中 “修改安全设置” 复选框，为root用户输入密码（务必记下密码，因为你很快就会用到！），然后点击 “下一步”。
- 点击 “执行” 。
此时，如果一切顺利，MySQL服务器已安装并正在运行。如果安装失败，建议你卸载服务器，并阅读 “在Windows下排查MySQL安装问题” 指南（可在http://dev.mysql.com/doc/refman/6.0/en/windows-troubleshooting.html找到）。
如果你在安装6.0版本之前卸载了旧版本的MySQL，那么在成功运行配置向导之前，可能还需要进一步清理（我当时就必须清理一些旧的注册表项）。
接下来，你需要打开Windows命令窗口，启动mysql工具，并创建数据库和数据库用户。表2-1描述了必要的步骤。在步骤5中，你可以自行选择lrngsql用户的密码，而不必使用 “xyz”（但别忘了记下来！）。
| 步骤 | 操作 | 描述 |
|---|---|---|
| 1 | 从 “开始” 菜单打开 “运行” 对话框 | 选择 “开始”，然后选择 “运行” |
| 2 | 启动命令窗口 | 输入cmd并点击 “确定” |
| 3 | 以root身份登录MySQL | mysql -u root -p |
| 4 | 为示例数据创建一个数据库 | create database bank; |
| 5 | 创建对bank数据库拥有全部权限的lrngsql数据库用户 | grant all privileges on bank.* to 'lrngsql'@'localhost' identified by 'xyz'; |
| 6 | 退出mysql工具 | quit; |
| 7 | 以lrngsql身份登录MySQL | mysql -u lrngsql -p; |
| 8 | 连接到bank数据库 | use bank; |
现在你已经有了MySQL服务器、数据库和数据库用户；剩下要做的就是创建数据库表并用示例数据填充它们。为此，请从http://examples.oreilly.com/learningsql/下载脚本，并通过mysql实用程序运行它。如果你将文件保存为c:\temp\LearningSQLExample.sql ，则需要执行以下操作：
- 如果你已退出mysql工具，请重复表2-1中的步骤7和8。
- 输入
source c:\temp\LearningSQLExample.sql;并按回车键。
现在你应该拥有一个已填充本书示例所需所有数据的可用数据库。
# 使用mysql命令行工具
每次调用mysql命令行工具时，你都可以指定要使用的用户名和数据库，如下所示：
mysql -u lrngsql -p bank
这将避免你每次启动该工具时都需要输入use bank;。系统会提示你输入密码，然后会出现mysql>提示符，通过它你可以发出SQL语句并查看结果。例如，如果你想知道当前的日期和时间，可以发出以下查询：
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2008-02-19 16:48:46 |
+---------------------+
1 row in set (0.01 sec)
2
3
4
5
6
7
now()函数是MySQL的一个内置函数，用于返回当前的日期和时间。如你所见，mysql命令行工具会将查询结果格式化为一个由+、-和|字符围成的矩形。在结果显示完毕后（在这个例子中，只有一行结果），mysql命令行工具会显示返回的行数以及SQL语句执行所花费的时间。
# 关于缺失的子句
在某些数据库服务器中，如果FROM子句中没有指定至少一个表，你将无法发出查询。Oracle数据库就是这样一种常用的服务器。当你只需要调用一个函数时，Oracle提供了一个名为dual的表，该表由一个名为dummy的单列组成，其中包含一行数据。为了与Oracle数据库兼容，MySQL也提供了dual表。因此，前面用于确定当前日期和时间的查询也可以写成：
mysql> SELECT now() FROM dual;
+---------------------+
| now() |
+---------------------+
| 2005-05-06 16:48:46 |
+---------------------+
1 row in set (0.01 sec)
2
3
4
5
6
7
如果你不使用Oracle数据库，并且不需要与Oracle兼容，那么你可以完全忽略dual表，只使用SELECT子句而不使用FROM子句。
当你完成对mysql命令行工具的使用后，只需输入quit;或exit;即可返回Windows命令提示符。
# MySQL数据类型
一般来说，所有流行的数据库服务器都有能力存储相同类型的数据，如字符串、日期和数字。它们的差异通常体现在特殊数据类型上，比如XML文档、非常大的文本或二进制文档。由于本书是SQL入门书籍，并且你遇到的98%的列都是简单数据类型，因此本书仅涵盖字符、日期和数字数据类型。
# 字符数据
字符数据可以存储为定长或变长字符串；区别在于，定长字符串会在右侧填充空格，并且始终占用相同数量的字节，而变长字符串不会在右侧填充空格，占用的字节数也不固定。在定义字符列时，你必须指定该列中存储的任何字符串的最大长度。例如，如果你想存储长度不超过20个字符的字符串，可以使用以下任意一种定义：
char(20) /* 定长 */
varchar(20) /* 变长 */
2
目前，char列的最大长度为255字节，而varchar列的最大长度可达65535字节。如果你需要存储更长的字符串（如电子邮件、XML文档等），那么你需要使用本节后面将介绍的文本类型（mediumtext和longtext）之一。一般来说，当要存储在列中的所有字符串长度相同时，比如州缩写，你应该使用char类型；当要存储在列中的字符串长度不同时，应该使用varchar类型。在所有主要的数据库服务器中，char和varchar的使用方式类似。
在使用varchar方面，Oracle数据库是个例外。Oracle用户在定义变长字符列时，应该使用varchar2类型。
# 字符集
对于使用拉丁字母的语言，如英语，字符数量足够少，每个字符仅需一个字节即可存储。而其他语言，如日语和韩语，包含大量字符，因此每个字符需要多个字节来存储。这样的字符集被称为多字节字符集。
MySQL可以使用各种字符集（单字节和多字节）存储数据。要查看服务器支持的字符集，可以使用SHOW命令，如下所示：
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+---------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+---------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+---------------------------------+---------------------+---------+
36 rows in set (0.11 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
如果第四列maxlen的值大于1，那么该字符集就是多字节字符集。
我在安装MySQL服务器时，latin1字符集被自动选为默认字符集。不过，你可以为数据库中的每个字符列选择不同的字符集，甚至可以在同一个表中存储不同的字符集。在定义列时，如果要选择默认字符集以外的字符集，只需在类型定义后指定一个支持的字符集名称即可，例如：
varchar(20) character set utf8
使用MySQL时，你还可以为整个数据库设置默认字符集：
create database foreign_sales character set utf8;
虽然在这本入门书中，关于字符集我只打算讨论这么多，但国际化这个主题的内容远不止于此。如果你计划处理多种或不熟悉的字符集，不妨阅读像Andy Deitsch和David Czarnecki所著的Java Internationalization（http://oreilly.com/catalog/9780596000196/）（O’Reilly出版社），或者Richard Gillam所著的Unicode Demystified: A Practical Programmer’s Guide to the Encoding Standard（Addison-Wesley出版社）这类书籍。
# 文本数据
如果你需要存储的数据可能超过varchar列64KB的限制，那么就需要使用其中一种文本类型。
表2-2展示了可用的文本类型及其最大大小。
| 文本类型 | 最大字节数 |
|---|---|
Tinytext | 255 |
Text | 65535 |
Mediumtext | 16777215 |
Longtext | 4294967295 |
在选择使用其中一种文本类型时，你应该注意以下几点：
- 如果加载到文本列的数据超过了该类型的最大大小，数据将被截断。
- 数据加载到列中时，尾随空格不会被删除。
- 使用文本列进行排序或分组时，仅使用前1024个字节，不过如有必要，这个限制可以提高。
- 不同的文本类型是MySQL特有的。SQL Server对于大字符数据只有一种文本类型，而DB2和Oracle使用一种名为
clob（Character Large Object，字符大对象）的数据类型。 - 既然MySQL允许
varchar列的最大长度达到65535字节（在4.0版本中限制为255字节），那么就没有特别的必要使用tinytext或text类型了。
如果你要创建一个用于自由格式数据输入的列，比如用于记录客户与公司客服部门交互数据的备注列，那么varchar类型可能就足够了。然而，如果你要存储文档，则应该选择mediumtext或longtext类型。
Oracle数据库允许
char列最多存储2000字节，varchar2列最多存储4000字节。SQL Server的char和varchar数据类型都可以处理最多8000字节的数据。
# 数值数据
虽然有一种名为“数值（numeric）”的单一数值数据类型似乎很合理，但实际上有几种不同的数值数据类型，它们反映了数字的各种使用方式，如下所示：
- 用于表明客户订单是否已发货的列。 这种类型的列被称为布尔型（Boolean），用0表示“假”，用1表示“真”。
- 事务表的系统生成主键。 这类数据通常从1开始，每次递增1，可能会增长到非常大的数字。
客户电子购物篮中的商品编号。这类列的值为正整数，范围是1到最多200（对于购物狂而言）。
- 电路板钻孔机的位置数据。 高精度的科学或制造数据通常要求精确到小数点后8位。
为了处理这些类型的数据（以及更多类型的数据），MySQL有几种不同的数值数据类型。最常用的数值类型是用于存储整数的类型。在指定这些类型之一时，你还可以指定数据为无符号（unsigned），这会告诉服务器，存储在该列中的所有数据都将大于或等于零。表2-3展示了用于存储整数的五种不同数据类型。
表2-3 MySQL整数类型
| 类型 | 有符号范围 | 无符号范围 |
|---|---|---|
| Tinyint | −128到127 | 0到255 |
| Smallint | −32,768到32,767 | 0到65,535 |
| Mediumint | −8,388,608到8,388,607 | 0到16,777,215 |
| Int | −2,147,483,648到2,147,483,647 | 0到4,294,967,295 |
| Bigint | −9,223,372,036,854,775,808到9,223,372,036,854,775,807 | 0到18,446,744,073,709,551,615 |
当你使用其中一种整数类型创建列时，MySQL会分配适当数量的空间来存储数据，存储空间从tinyint的1字节到bigint的8字节不等。因此，你应该尽量选择一种足够大的类型，以容纳你能想到的存储在该列中的最大数字，同时又不会不必要地浪费存储空间。
对于浮点数（如3.1415927），你可以从表2-4所示的数值类型中进行选择。
表2-4 MySQL浮点类型
| 类型 | 数值范围 |
|---|---|
| Float(p,s) | −3.402823466E+38到−1.175494351E-38 以及1.175494351E-38到3.402823466E+38 |
| Double(p,s) | −1.7976931348623157E+308到−2.2250738585072014E-308 以及2.2250738585072014E-308到1.7976931348623157E+308 |
在使用浮点类型时，你可以指定精度（小数点左右允许的总位数）和标度（小数点右边允许的位数），但这不是必需的。在表2-4中，这些值用p和s表示。如果你为浮点列指定了精度和标度，请记住，如果数字的位数超过了该列的标度和 / 或精度，存储在该列中的数据将被四舍五入。例如，定义为float(4,2)的列将总共存储4位数字，小数点左边2位，右边2位。因此，这样的列可以很好地处理27.44和8.19这样的数字，但17.8675将被四舍五入为17.87，而试图将178.375存储在float(4,2)列中将会产生错误。
与整数类型一样，浮点列也可以定义为无符号，但这种指定仅阻止负数存储在该列中，而不会改变该列中可存储的数据范围。
# 时间数据
除了字符串和数字，你几乎肯定会处理有关日期和 / 或时间的信息。这类数据被称为时间数据（temporal data），数据库中时间数据的一些示例包括：
- 特定事件预计发生的未来日期，如客户订单的发货日期。
- 客户订单的实际发货日期。
- 用户修改表中特定行的日期和时间。
- 员工的出生日期。
- 数据仓库中年销售事实表（yearly_sales fact table）中某一行对应的年份。
- 汽车装配线上完成一个线束所需的耗时。
MySQL提供了用于处理所有这些情况的数据类型。表2-5展示了MySQL支持的时间数据类型。
表2-5 MySQL时间类型
| 类型 | 默认格式 | 允许的值 |
|---|---|---|
| Date | YYYY-MM-DD | 1000-01-01到9999-12-31 |
| Datetime | YYYY-MM-DD HH:MI:SS | 1000-01-01 00:00:00到9999-12-31 23:59:59 |
| Timestamp | YYYY-MM-DD HH:MI:SS | 1970-01-01 00:00:00到2037-12-31 23:59:59 |
| Year | YYYY | 1901到2155 |
| Time | HHH:MI:SS | -838:59:59到838:59:59 |
虽然数据库服务器以各种方式存储时间数据，但格式字符串（表2-5的第二列）的目的是展示检索数据时数据的表示方式，以及插入或更新时间列时应如何构造日期字符串。因此，如果你想使用默认格式YYYY-MM-DD将2005年3月23日插入到date列中，你应该使用字符串'2005-03-23'。第7章将全面探讨时间数据的构造和显示方式。
每个数据库服务器对时间列允许的日期范围不同。Oracle数据库接受的日期范围是公元前4712年到公元9999年，而SQL Server仅处理公元1753年到公元9999年的日期（除非你使用SQL Server 2008的新datetime2数据类型，该类型允许的日期范围是公元1年到公元9999年）。MySQL的日期范围介于Oracle和SQL Server之间，可以存储公元1000年到公元9999年的日期。虽然这对大多数跟踪当前和未来事件的系统可能没有任何影响，但如果你要存储历史日期，就需要牢记这一点。
表2-6描述了表2-5中所示日期格式的各个组成部分。
表2-6 日期格式组成部分
| 组成部分 | 定义 | 范围 |
|---|---|---|
| YYYY | 年份，包括世纪 | 1000到9999 |
| MM | 月份 | 01（一月）到12（十二月） |
| DD | 日期 | 01到31 |
| HH | 小时 | 00到23 |
| HHH | 小时数（已流逝） | -838到838 |
| MI | 分钟 | 00到59 |
| SS | 秒 | 00到59 |
以下是如何使用各种时间类型来实现前面提到的示例：
- 用于存储客户订单预计未来发货日期和员工出生日期的列将使用date类型，因为知道一个人出生的具体时间没有必要，并且将未来发货时间精确到秒也不现实。
- 用于存储客户订单实际发货时间信息的列将使用datetime类型，因为不仅跟踪发货日期，还跟踪发货时间非常重要。
- 用于跟踪用户上次修改表中特定行时间的列将使用timestamp类型。timestamp类型存储的信息与datetime类型相同（年、月、日、时、分、秒），但当向表中添加一行或稍后修改一行时，MySQL服务器会自动用当前日期 / 时间填充timestamp列。
- 仅存储年份数据的列将使用year类型。
- 用于存储完成一项任务所需时间数据的列将使用time类型。对于这类数据，存储日期部分既不必要又容易造成混淆，因为你只关心完成任务所需的小时 / 分钟 / 秒数。这些信息可以通过两个datetime列（一个用于任务开始日期 / 时间，另一个用于任务完成日期 / 时间）相减得到，但使用单个time列会更简单。
第7章将探讨如何使用这些时间数据类型。
# 表的创建
既然你已经牢牢掌握了MySQL数据库中可能存储的数据类型，现在是时候看看如何在表定义中使用这些类型了。让我们从定义一个用于存储个人信息的表开始。
# 步骤1：设计
设计表的一个好方法是先进行一些头脑风暴，看看包含哪些信息会有所帮助。在对描述一个人的信息类型进行了短暂思考后，我想到了以下内容：
- 姓名
- 性别
- 出生日期
- 地址
- 最喜欢的食物
这当然不是一个详尽的列表，但目前来说已经足够了。接下来的步骤是分配列名和数据类型。表2-7展示了我的初步尝试。
表2-7 人员表，第一版
| 列 | 类型 | 允许的值 |
|---|---|---|
| Name | Varchar(40) | - |
| Gender | Char(1) | M，F |
| Birth_date | Date | - |
| Address | Varchar(100) | - |
| Favorite_foods | Varchar(200) | - |
name、address和favorite_foods列的数据类型为varchar，允许自由格式的数据输入。gender列只允许单个字符，且只能是M或F。birth_date列的数据类型为date，因为不需要时间部分。
# 步骤2：优化
在第1章中，你已经了解了规范化（normalization）的概念，即确保数据库设计中不存在重复列（外键除外）或复合列的过程。再次查看人员表中的列时，会出现以下问题：
- name列实际上是一个复合对象，由名字和姓氏组成。
- 由于多个人可能有相同的姓名、性别、出生日期等，人员表中没有能保证唯一性的列。
- address列也是一个复合对象，由街道、城市、州 / 省、国家和邮政编码组成。
- favorite_foods列是一个包含0个、1个或更多独立项目的列表。最好为这些数据创建一个单独的表，并包含一个指向人员表的外键，这样你就可以知道某种特定食物属于哪个人。
考虑到这些问题后，表2-8给出了人员表的规范化版本。
表2-8 人员表，第二版
| 列 | 类型 | 允许的值 |
|---|---|---|
| Person_id | Smallint （unsigned） | - |
| First_name | Varchar(20) | - |
| Last_name | Varchar(20) | - |
| Gender | Char(1) | M，F |
| Birth_date | Date | - |
| Street | Varchar(30) | - |
| City | Varchar(20) | - |
| State | Varchar(20) | - |
| Country | Varchar(20) | - |
| Postal_code | Varchar(20) | - |
现在人员表有了一个主键（person_id）来保证唯一性，接下来的步骤是创建一个favorite_food表，其中包含一个指向人员表的外键。表2-9展示了结果。
表2-9 最喜欢的食物表
| 列 | 类型 |
|---|---|
| Person_id | Smallint （unsigned） |
| Food | Varchar(20) |
person_id和food列构成了favorite_food表的主键，person_id列也是指向人员表的外键。
# 多少才够？
将favorite_foods列从人员表中移出绝对是个好主意，但我们就此完成了吗？例如，如果一个人将“pasta（意大利面）”列为最喜欢的食物，而另一个人将“spaghetti（意大利面条）”列为最喜欢的食物，会发生什么情况？它们是同一种东西吗？为了避免这个问题，你可能会决定让人们从选项列表中选择他们最喜欢的食物，在这种情况下，你应该创建一个包含food_id和food_name列的food表，然后更改favorite_food表，使其包含一个指向food表的外键。虽然这种设计是完全规范化的，但你可能会决定只想存储用户输入的值，在这种情况下，你可以保留表的现有结构。
# 步骤3：构建SQL模式语句
既然存储人员信息及其喜爱食物信息的两张表的设计已经完成，下一步就是生成SQL语句，在数据库中创建这些表。下面是创建person表的语句：
CREATE TABLE person (
person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender CHAR(1),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);
2
3
4
5
6
7
8
9
10
11
12
13
这条语句中的所有内容都相当直观，除了最后一项；在定义表时，需要告诉数据库服务器哪些列将作为表的主键（primary key）。这是通过在表上创建约束（constraint）来实现的。可以在表定义中添加几种类型的约束。这个约束是一个主键约束，它在person_id列上创建，并被命名为pk_person。
在讨论约束的话题时，person表还有另一种有用的约束类型。在表2 - 7中，我添加了第三列，以显示某些列的允许值（例如，gender列的'M'和'F'）。另一种称为检查约束（check constraint）的约束类型，用于限制特定列的允许值。MySQL允许将检查约束附加到列定义中，如下所示：
gender CHAR(1) CHECK (gender IN ('M', 'F')),
虽然检查约束在大多数数据库服务器上都能按预期运行，但MySQL服务器允许定义检查约束，但不会强制执行它们。不过，MySQL提供了另一种字符数据类型，称为枚举（enum），它将检查约束合并到数据类型定义中。对于gender列定义，它看起来是这样的：
gender ENUM('M', 'F'),
下面是使用枚举数据类型定义gender列后的person表定义：
CREATE TABLE person (
person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M', 'F'),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);
2
3
4
5
6
7
8
9
10
11
12
13
在本章后面，你将看到如果尝试向违反其检查约束（或者在MySQL的情况下，违反其枚举值）的列中添加数据会发生什么。
现在，你可以使用mysql命令行工具运行创建表的语句了。它看起来是这样的：
mysql> CREATE TABLE person
-> (person_id SMALLINT UNSIGNED,
-> fname VARCHAR(20),
-> lname VARCHAR(20),
-> gender ENUM('M', 'F'),
-> birth_date DATE,
-> street VARCHAR(30),
-> city VARCHAR(20),
-> state VARCHAR(20),
-> country VARCHAR(20),
-> postal_code VARCHAR(20),
-> CONSTRAINT pk_person PRIMARY KEY (person_id)
-> );
Query OK, 0 rows affected (0.27 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
在处理完创建表的语句后，MySQL服务器返回消息“Query OK, 0 rows affected”，这表明该语句没有语法错误。如果你想确保person表确实存在，可以使用describe命令（或简称desc）查看表定义：
mysql> DESC person;
+-----------------+--------------------------+------+------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------+------+------+-----------+----------------+
| person_id | smallint(5) unsigned | | PRI | 0 | |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| street | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-----------------+--------------------------+------+------+-----------+----------------+
10 rows in set (0.06 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
describe输出的第1列和第2列很直观。第3列显示在向表中插入数据时，特定列是否可以省略。我目前有意不讨论这个话题（有关简短论述，请参见第29页的“什么是空值？”侧边栏），但我们将在第4章中全面探讨它。第4列显示列是否参与任何键（主键或外键）；在这种情况下，person_id列被标记为主键。第5列显示在向表中插入数据时，如果省略该列，特定列是否会用默认值填充。person_id列显示默认值为0，不过这只在插入第一行数据时有效，因为person表中的每一行在该列都必须包含唯一值（因为它是主键）。第6列（称为“Extra”）显示可能适用于某列的任何其他相关信息。
什么是空值？
在某些情况下，为表中的特定列提供值是不可能的或不适用的。例如，在添加有关新客户订单的数据时，ship_date列的值尚无法确定。在这种情况下，该列被称为空值（注意，我不会说它等于空值），这表示缺少值。空值用于各种无法提供值的情况，例如：
- 不适用
- 未知
- 空集
在设计表时，可以指定哪些列允许为空（默认情况），哪些列不允许为空（在类型定义后添加关键字not null来指定）。
现在已经创建了person表，下一步是创建favorite_food表：
mysql> CREATE TABLE favorite_food
-> (person_id SMALLINT UNSIGNED,
-> food VARCHAR(20),
-> CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
-> CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
-> REFERENCES person (person_id)
-> );
Query OK, 0 rows affected (0.10 sec)
2
3
4
5
6
7
8
这条语句看起来与创建person表的语句非常相似，但有以下几点不同：
- 由于一个人可以有多种喜爱的食物（这也是首先创建此表的原因），因此仅靠
person_id列无法保证表中的唯一性。因此，此表有一个两列的主键：person_id和food。 favorite_food表包含另一种称为外键约束（foreign key constraint）的约束类型。这将favorite_food表中person_id列的值限制为仅包含在person表中找到的值。有了这个约束，如果person表中还没有person_id为27的行，我就无法向favorite_food表中添加一行，表明person_id为27的人喜欢披萨。
如果在首次创建表时忘记创建外键约束，可以稍后通过alter table语句添加。
执行创建表的语句后，describe显示如下内容：
mysql> DESC favorite_food;
+-----------------+--------------------------+------+------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------+------+------+-----------+----------------+
| person_id | smallint(5) unsigned | | PRI | 0 | |
| food | varchar(20) | | PRI | | |
+-----------------+--------------------------+------+------+-----------+----------------+
2
3
4
5
6
7
现在表已经创建好了，接下来的合理步骤是添加一些数据。
# 填充和修改表
person表和favorite_food表已经创建好了，现在可以开始探索四条SQL数据语句：insert、update、delete和select。
# 插入数据
由于person表和favorite_food表中还没有任何数据，所以首先要探索的四条SQL数据语句是insert语句。insert语句主要有三个组成部分：
- 要添加数据的表名
- 要填充的表中的列名
- 用于填充列的值
不要求为表中的每一列都提供数据（除非表中的所有列都被定义为not null）。在某些情况下，最初的insert语句中未包含的列，稍后可以通过update语句来赋值。在其他情况下，特定行数据的某列可能永远不会有值（例如，在发货前取消的客户订单，这样ship_date列就不适用了）。
# 生成数字键数据
在向person表中插入数据之前，讨论一下如何为数字主键生成值是很有用的。除了随意选一个数字之外，还有几种选择：
- 查看表中当前的最大值并加1。
- 让数据库服务器为你提供值。
虽然第一种选择看似可行，但在多用户环境中会出现问题，因为两个用户可能同时查看表，并为主键生成相同的值。相反，如今市场上的所有数据库服务器都提供了一种安全、可靠的生成数字键的方法。在某些服务器（如Oracle数据库）中，会使用一个单独的模式对象（称为序列（sequence））；而在MySQL中，只需为你的主键列启用自动递增（auto - increment）功能。通常，你会在创建表时这样做，但现在进行操作，可以让你学习另一条SQL模式语句alter table，它用于修改现有表的定义：
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
这条语句本质上重新定义了person表中的person_id列。如果你查看表的定义，现在会在person_id列的“Extra”列下看到自动递增功能：
mysql> DESC person;
+-----------------+--------------------------+------+------+-----------+--------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------+------+------+-----------+--------------------------+
| person_id | smallint(5) unsigned | | PRI | 0 | auto_increment |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| street | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-----------------+--------------------------+------+------+-----------+--------------------------+
2
3
4
5
6
7
8
9
10
11
12
13
14
15
当向person表中插入数据时，只需为person_id列提供一个空值，MySQL就会用下一个可用的数字填充该列（默认情况下，MySQL自动递增列从1开始）。
# insert语句
现在一切准备就绪，是时候添加一些数据了。下面的语句为William Turner在person表中创建了一行数据：
mysql> INSERT INTO person
-> (person_id, fname, lname, gender, birth_date)
-> VALUES (null, 'William', 'Turner', 'M', '1972-05-27');
Query OK, 1 row affected (0.01 sec)
2
3
4
反馈信息（“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)
2
3
4
5
6
7
8
如你所见，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)
2
3
4
5
6
7
8
9
虽然这个查询指定了特定的主键值，但你可以使用表中的任何列来搜索行，如下查询所示，它查找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)
2
3
4
5
6
7
8
9
在继续之前，关于前面的insert语句，有几点值得一提：
- 没有为任何地址列提供值。这没问题，因为这些列允许为空值。
- 为
birth_date列提供的值是一个字符串。只要你匹配表2 - 5中所示的所需格式，MySQL就会为你将该字符串转换为日期。 - 列名和提供的值在数量和类型上必须对应。如果你指定了七列，但只提供了六个值，或者提供的值无法转换为相应列的适当数据类型，就会收到错误消息。
William还提供了他最喜欢的三种食物的信息，所以下面是三条插入语句，用于存储他的食物偏好：
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)
2
3
4
5
6
7
8
9
下面的查询使用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)
2
3
4
5
6
7
8
9
10
11
12
order by子句告诉服务器如何对查询返回的数据进行排序。如果没有order by子句，就不能保证表中的数据会以任何特定顺序被检索出来。
为了不让William孤单，你可以执行另一条insert语句，将Susan Smith添加到person表中：
mysql> INSERT INTO person
-> (person_id, fname, lname, gender, birth_date,
-> street, city, state, country, postal_code)
-> VALUES (null, 'Susan', 'Smith', 'F', '1975-11-02',
-> '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');
Query OK, 1 row affected (0.01 sec)
2
3
4
5
6
由于Susan提供了她的地址信息，所以与插入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)
2
3
4
5
6
7
8
9
我可以获取XML格式的数据吗？
如果你要处理XML数据，那么大多数数据库服务器都提供了一种从查询生成XML输出的简单方法，你会对此感到很高兴。例如，在MySQL中，当调用mysql工具时可以使用--xml选项，这样所有的输出都会自动格式化为XML。下面是favorite_food数据作为XML文档的样子：
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)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
在SQL Server中，你不需要配置命令行工具，只需要在查询末尾添加for xml子句，如下所示：
SELECT * FROM favorite_food FOR XML AUTO, ELEMENTS
# 更新数据
当初将威廉·特纳（William Turner）的数据添加到表中时，插入语句中省略了各个地址列的数据。下面的语句展示了如何通过更新语句来填充这些列：
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
2
3
4
5
6
7
8
9
10
服务器返回了一条两行的消息：“Rows matched: 1”表明where子句中的条件与表中的一行匹配，“Changed: 1”则表明表中的一行已被修改。由于where子句指定了威廉所在行的主键，这正是预期的结果。
根据where子句中的条件，使用一条语句修改多行数据也是可能的。例如，考虑一下如果where子句如下所示会发生什么：
WHERE person_id < 10
由于威廉和苏珊（Susan）的person_id值都小于10，他们的两行数据都会被修改。如果完全省略where子句，更新语句将修改表中的每一行数据。
# 删除数据
威廉和苏珊似乎相处得不太融洽，所以他们其中一人得离开。因为威廉先来，苏珊就得通过delete语句被“请走”：
mysql> DELETE FROM person
-> WHERE person_id = 2;
Query OK, 1 row affected (0.01 sec)
2
3
4
同样，这里使用主键来确定要删除的行，所以从表中删除了一行数据。与更新语句类似，根据where子句中的条件，可以删除多行数据；如果省略where子句，则会删除所有行的数据。
# 语句出错的情况
到目前为止，本章展示的所有SQL数据语句都是格式正确且符合规则的。然而，根据person表和favorite_food表的表定义，在插入或修改数据时，有很多情况可能会出错。本节将展示一些可能遇到的常见错误，以及MySQL服务器的响应。
# 主键不唯一
因为表定义中包含了主键约束的创建，MySQL会确保不会将重复的键值插入到表中。下面的语句试图绕过person_id列的自动递增特性，在person表中创建另一行person_id为1的数据：
mysql> INSERT INTO person
-> (person_id, fname, lname, gender, birth_date)
-> VALUES (1, 'Charles','Fulton', 'M', '1968-01-15');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
2
3
4
5
至少在当前的模式对象下，只要person_id列的值不同，就可以创建两行名字、地址、出生日期等完全相同的数据。
# 外键不存在
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 ('bank' .'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))
2
3
4
在这种情况下，favorite_food表被视为子表，person表被视为父表，因为favorite_food表的部分数据依赖于person表。如果计划在两个表中都输入数据，需要先在父表中创建一行数据，然后才能在favorite_food表中输入数据。
只有在使用InnoDB存储引擎创建表时，才会强制执行外键约束。我们将在第12章讨论MySQL的存储引擎。
# 列值违规
person表中的gender列被限制为'M'（男性）和'F'（女性）两个值。如果错误地尝试将该列的值设置为其他任何值，将会收到以下响应：
mysql> UPDATE person
-> SET gender = 'Z'
-> WHERE person_id = 1;
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
2
3
4
5
这个错误消息有点令人困惑，但它大致传达出服务器不接受为gender列提供的值。
# 无效的日期转换
如果构造一个用于填充日期列的字符串，而该字符串与预期格式不匹配，将会收到另一个错误。以下示例使用的日期格式与默认的“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
2
3
4
5
一般来说，显式指定格式字符串总是比依赖默认格式要好。以下是使用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
2
3
4
5
6
不仅数据库服务器正常执行了操作，威廉也会很高兴（我们刚刚让他年轻了8岁，而且还不需要昂贵的整容手术！）。
在本章前面讨论各种时间数据类型时，我展示了像“YYYY-MM-DD”这样的日期格式化字符串。虽然许多数据库服务器使用这种格式，但MySQL使用%Y表示四位数的年份。在MySQL中，将字符串转换为日期时间时，可能还需要以下一些格式化符号：
%a：星期几的缩写，如Sun、Mon等。%b：月份的缩写，如Jan、Feb等。%c：月份的数字表示（0 - 12）。%d：月份中的第几天（00 - 31）。%f：微秒数（000000 - 999999）。%H：一天中的小时数，24小时制（00 - 23）。%h：一天中的小时数，12小时制（01 - 12）。%i：小时内的分钟数（00 - 59）。%j：一年中的第几天（001 - 366）。%M：月份的完整名称（January - December）。%m：月份的数字表示。%p：上午（AM）或下午（PM）。%s：秒数（00 - 59）。%W：星期几的完整名称（Sunday - Saturday）。%w：星期几的数字表示（0 = 星期日，6 = 星期六）。%Y：四位数的年份。
# 银行模式
在本书的其余部分，将使用一组模拟社区银行的表。其中一些表包括Employee（员工）、Branch（分支机构）、Account（账户）、Customer（客户）、Product（产品）和Transaction（交易）。在按照本章开头加载MySQL服务器和生成示例数据的最后步骤操作时，应该已经创建了整个模式和示例数据。有关这些表及其列和关系的图表，请参阅附录A。
表2-10展示了银行模式中使用的所有表以及简短定义。
表2-10 银行模式定义
| 表名 | 定义 |
|---|---|
Account | 为特定客户开设的特定产品 |
Branch | 进行银行业务交易的地点 |
Business | 企业客户（Customer表的子类型） |
Customer | 银行已知的个人或企业 |
Department | 执行特定银行职能的一组银行员工 |
Employee | 为银行工作的人员 |
Individual | 非企业客户（Customer表的子类型） |
Officer | 被允许为企业客户办理业务的人员 |
Product | 提供给客户的银行服务 |
Product_type | 具有相似功能的一组产品 |
Transaction | 对账户余额的更改 |
可以随意对这些表进行试验，包括添加自己的表以扩展银行的业务功能。如果希望确保示例数据完整，可以随时删除数据库并从下载的文件重新创建。
如果想要查看数据库中可用的表，可以使用show tables命令，如下所示：
mysql> SHOW TABLES;
+------------------+
| Tables_in_bank |
+------------------+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| favorite_food |
| individual |
| officer |
| person |
| product |
| product_type |
| transaction |
+------------------+
13 rows in set (0.10 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
除了银行模式中的11个表，表列表还包括本章创建的两个表：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)
2
3
4
如果想要查看表中的列，可以使用describe命令。以下是customer表的describe输出示例：
mysql> DESC customer;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| cust_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| fed_id | varchar(12) | NO | | NULL | |
| cust_type_cd | enum('I','B') | NO | | NULL | |
| address | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
+----------------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
对示例数据库越熟悉，就越能理解后续章节中的示例，进而理解其中的概念。