本章内容包括创建第一个数据库以及本书中示例所用的表和相关数据，你还将了解各种数据类型以及如何在创建表时使用它们。因为本书中的示例是针对MySQL数据库执行的，所以本章会偏向使用MySQL的特性和语法，但大多数概念对其他的数据库服务器也适用。读完本文如果觉得有帮助，前往怎么用SQL建立数据库获取更多相关资讯。

一、创建MySQL数据库

如果你想使用本书中示例所用的数据，你有两个选择：

• 下载并安装MySQL server 8.0版（或更高版本），然后从https://dev.mysql.com/doc/index-other.html下载Sakila示例数据库；

• 跳转到https://www.katacoda.com/mysql-db-sandbox/scenarios/mysql-sandbox访问MySQL沙箱，在MySQL实例中加载Sakila示例数据库。要使用这种方法，你必须新建一个Katacoda帐户（免费的），然后单击Start Scenario按钮。

如果你选择第二种方法，那么一旦启动场景，就会安装并启动MySQL服务器，然后加载Sakila模式和数据。启动以及加载完毕之后，会出现一个标准的mysql>提示符，然后就可以开始查询示例数据库了。这当然是最简单的选择，我认为大多数读者都会选择这个方法。如果你觉得使用第二种方法很不错并且打算使用这种方法，那么可以跳到下一节了。

如果你希望拥有自己的数据副本，并且希望所做的任何更改都是永久性的，或者如果你就是想在自己的计算机上安装MySQL服务器，那么你可能偏向于第一个选择。你也可以选择使用托管在Amazon Web Services 或Google Cloud等环境中的MySQL服务器。无论你是在本机下载还是在云端托管，你都需要自己执行安装和配置，这里不予赘述。一旦数据库可用（假设你已经下载并且安装和配置完毕），就需要按照以下几个步骤加载Sakila示例数据库。

首先，启动mysql命令行客户端并输入密码，然后执行以下步骤：

1.前往https://dev.mysql.com/doc/index-other.html下载Example Databases下的“sakila database”文件。

2.将文件放在本地目录中，例如C:\temp\sakila-db（为下面两个步骤做准备）。

3.键入source c:\temp\sakila db\sakila-schema.sql，然后按Enter键。

4.键入source c:\temp\sakila db\sakila-data.sql，然后按Enter键。

现在你应该有一个可用数据库了，其中包括了本书中示例所需的所有数据。

注意：Sakila示例数据库由MySQL提供，并通过New BSD获得许可。Sakila包含一个虚构的电影租赁公司的数据，并包含诸如store, inventory, film, customer, 和payment等表。虽然实际上的电影租赁商店基本上已经是过去时了，但是你可以想象一下，通过忽略staff和address表，将store重命名为streaming_service，然后就可以把这个电影租赁公司当成电影流媒体公司了。但是，这本书中的例子并不会变，还是以电影租赁公司为例。

二、使用mysql命令行工具

除非是使用临时数据库会话（上一节中的第二个选择），否则需要启动mysql命令行工具才能与数据库交互。为此，你需要打开Windows或Unix shell并执行mysql程序。例如，如果你使用root帐户登录，需要执行以下操作：

mysql -u root -p;

然后你需要输入密码，登陆成功之后你会看到提示符mysql>。要查看所有可用的数据库，可以使用以下命令：

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sakila || sys |+--------------------+5 rows in set (0.01 sec)

由于你将使用Sakila数据库，因此需要通过use命令指定要使用的数据库：

mysql> use sakila;Database changed

无论何时调用mysql命令行工具，都可以指定要使用的用户名和数据库，如下所示：

mysql -u root -p sakila;

这样就不用在登录之后再键入use sakila;使用数据库了。现在你已经建立了会话并指定了数据库，接下来就可以使用SQL语句并查看结果了。例如，如果你想知道当前日期和时间，可以使用以下查询：

mysql> SELECT now();+---------------------+| now() |+---------------------+| 2019-04-04 20:44:26 |+---------------------+1 row in set (0.01 sec)

函数now()是一个内置的MySQL函数，返回当前日期和时间。如你所见，mysql命令行工具将查询结果格式化为以+、-和|字符为边界的矩形。结果显示结束之后（在本例中，只有一行结果），mysql命令行工具显示返回了多少行，以及SQL语句执行所用的时间。

关于从句缺失

对于某些数据库服务器，使用查询语句的时候必须包括from子句，并且至少要指明一个表名，否则无法正常查询，像广泛使用的Oracle数据库就是这样的。对于只需要调用函数的情况，Oracle提供了一个名为dual的表，该表由一个名为dummy的列组成，该列包含一行数据。为了与Oracle数据库兼容，MySQL也提供了dual表。因此，用于前面查询当前日期和时间的语句可以写成：

mysql> SELECT now()  FROM dual; +---------------------+ | now() | +---------------------+ | 2019-04-04 20:44:26 | +---------------------+ 1 row in set (0.01 sec)

如果你不使用Oracle并且也不需要与它兼容，那么可以完全忽略dual表，只使用不带有from子句的select语句即可。

使用mysql命令行工具后，只需键入quit;或exit;即可返回Unix或Windows command shell。

三、MySQL数据类型

一般来说，所有流行的数据库服务器都能够存储相同类型的数据，比如字符串、日期和数字。它们的不同之处通常在于一些特殊的数据类型，比如XML和JSON文档或空间数据。由于这是一本关于SQL的入门书，而且你使用的数据列中98%都是简单的数据类型，因此本章仅介绍字符型、日期型和数值型的数据类型。第十八章将探讨如何使用SQL查询JSON文档。

1.字符型数据

字符数据可以存储为固定长度或可变长度的字符串，其不同之处在于：固定长度的字符串用空格向右填充，使得占用的字节数相同，而可变长度的字符串不需要向右填充，并且字节数是可变的。定义字符列的时候，必须指定该列能够存放字符串的最大长度。例如，如果要存储长度不超过20个字符的字符串，可以使用以下定义之一：

char(20) /* fixed-length */varchar(20) /* variable-length */

char列的最大长度当前为255字节，而varchar列的最大长度可达65535字节。如果你要存储更长的字符串（如电子邮件、XML文档等），那么就需要使用文本类型（mediumtext和longtext），后面会介绍这些内容。一般来说，当要存储在列中的所有字符串是等长时（如州名的缩写），应该使用char类型。当要存储在列中的字符串长度不同时，使用varchar类型。char和varchar在所有主流数据库服务器中的使用方法都类似。

注意：使用Oracle数据库的时候，varchar的使用方式不同。Oracle用户在定义可变长度的字符列时应使用varchar2类型

（1）字符集

^p对于拉丁语系的语言（如英语）字符数足够少，因此只需要一个字节来存储每个字符。其他语言（如日语和韩语）则包含大量字符，每个字符需要多个字节来存储，因此这种字符集被称为多字节字符集。

^pMySQL可以使用各种字符集（包括单字节和多字节）存储数据。要查看服务器所支持的字符集，可以使用show命令，如下所示：

mysql> SHOW CHARACTER SET;+----------+---------------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+---------------------------------+---------------------+--------+| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || ascii | US ASCII | ascii_general_ci | 1 || big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || binary | Binary pseudo charset | binary | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || cp1251 | Windows Cyrillic | cp1251_general_ci | 1 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || cp866 | DOS Russian | cp866_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 || greek | ISO 8859-7 Greek | greek_general_ci | 1 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European | macroman_general_ci | 1 || sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || utf16 | UTF-16 Unicode | utf16_general_ci | 4 || utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 || utf32 | UTF-32 Unicode | utf32_general_ci | 4 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.04 sec)

如果第四列maxlen中的值大于1，则表明该字符集是多字节字符集。

在MySQL服务器的早期版本中，latin1字符集被视作默认字符集，但版本8默认使用utf8mb4字符集。你可以选择为数据库中的每个字符列使用不同的字符集，甚至可以在同一个表中存储不同的字符集数据。要在定义列时选择非默认字符集，只需在类型定义后加上一个系统支持的字符集，如下所示：

varchar(20) character set latin1

MySQL中，还可以为整个数据库设置默认字符集：

createdatabase european_sales characterset latin1;

对一本介绍性的书来说，介绍这些关于字符集的内容已经够多了，但是实际上关于国际化的主题中包含的内容更多。如果你要处理多个不熟悉的字符集，那么可能需要学习一本专业书籍，比如Jukka Korpela的Unicode Explained: Internationalize Documents, Programs, and Web Sites (O’Reilly)

（2）文本数据

如果需要存储可能超过varchar列的限制的数据（64KB以上），则需要使用文本类型。

下表（2-1）显示了可用的文本类型及其最大长度：

 

使用文本类型时，应注意以下几点：

• 如果装载到文本列中的数据超过该类型的最大大小，数据将被截断。

• 将数据装载到文本列中时，不会删除尾部的空格。

• 使用文本列进行排序或分组时，仅使用前1024个字节，但必要时可放宽该限制。

• 这些不同的文本类型是MySQL独有的。对于大字符数据，SQLServer只有一种文本类型（text类型），而DB2和Oracle使用一种称为clob的数据类型（Character Large Object）。

• 既然MySQL允许varchar列最多容纳65535字节（在版本4中限制为255字节），那么一般不需要特别使用tinytext或text类型了。

如果你要创建一个用于存储自由格式数据的列，例如一个notes列来存储有关客户与公司的客服部门交互的数据，那么varchar可能就足够了。但是，如果要存储文档，则应选择mediumtext或longtext类型。

注意：Oracle数据库中char列最多容纳2000字节，varchar2列最多容纳4000字节。对于较大的文档，可以使用clob类型。对于char和varchar数据，SQL Server最多可以处理8000个字节，但是在定义为varchar(max)的列中最多可以存储2GB的数据。

2.数值型数据

尽管使用叫“numeric”的数值数据类型似乎是合理的，但实际上存在好几种不同的数值数据类型，它们反映了使用数字的各种方式，如下所示：• 某列需要指示客户订单是否已发送

 这种类型的列可以被设为Boolean，将包含一个0或1，分别表示false和true。

• 系统为交易表生成的主键

 这列数据通常从1开始，每次自增1，最后的数字可能会非常大。

• 顾客电子购物篮的商品编号

 这类列的值应该是一个正整数，范围从1到200（假设购物狂的购物篮能容纳最多的物品数量是200，当然也可能更大或更小）。

• 电路板钻床的位置数据

 高精度的科学或制造数据通常要求精确到小数点后8位。

为了处理这些类型的数据（以及更多类型的数据），MySQL提供几种不同的数值数据类型。最常用的是用来存储整数即integers的类型。在指定类型的时候，还可以指定数据是unsigned的，以向服务器指明存储这列的所有数据都将大于或等于零。下表（2-2）展示了用于存储整数的五种不同数据类型：

当你使用其中一个整数类型创建列时，MySQL将为存储数据分配适当大小的空间，范围从tinyint的一个字节到bigint的八个字节。因此在选择数据类型的时候，确保能够容纳你所预期的最大数据即可，以免白白浪费存储空间。

对于浮点数（如3.1415927），可以从下表（2-3）所示的数字类型中选择：

使用浮点类型时，可以指定精度precision（小数点左侧到右侧允许的总位数）和有效位scale（小数点右侧允许的位数），不过这并不是必须的。上面两个值在上表（2-3）中表示为p和s。注意：为浮点类型的列指定精度和有效位的时候，如果位数超过该列定义的小数位数和精度，则存储在该列中的数据将被四舍五入。例如，定义为float(4,2)的列总共会存储四位数字，两位在小数点左侧，两位在小数点右侧。因此，这样的列可以很好地存储27.44和8.19这两个数字，但是存储17.8675将被四舍五入到17.87，存储178.375则会产生错误。

与整数类型一样，浮点列可以被定义为无符号的（unsigned），但这只能防止列中存放负数，而无法改变列所能存储数据的范围。

3.时间数据

除了字符串和数字，你肯定还会使用有关日期和时间的信息。这种类型的数据称为时间型数据，下面是数据库中有关时间型数据的一些例子：

• 预计未来发生的特定事件的日期（例如运送客户订单）；

• 客户订单的实际发货日期；

• 用户修改表中某行的日期和时间；

• 员工的出生日期；

• 与数据仓库的yearly_sales表中，每行对应的年份；

• 在汽车装配线上完成流水线所需的时间。

MySQL能为上述所有情况提供合适的数据类型。MySQL支持的时间数据类型如下表（2-4）所示：

虽然数据库服务器能够以各种方式存储时间数据，但格式字符串（上表2-4的第二列）的目的是指定检索时如何显示数据，以及插入或更新时间列时需要提供日期字符串的格式。因此，如果要使用默认格式为YYYY-MM-DD的date列存储日期2020年3月23日则要使用字符串'2020-03-23'。第七章全面探讨了时间数据是如何构造和显示的。

datetime、timestamp和time类型还允许小数点后6位（微秒）的小数秒，使用这些数据类型之一定义列时，可以提供0到6之间的值；例如，指定datetime(2)将允许时间值包含百分之一秒。

注意：每个数据库服务器针对时间列允许的日期范围各不相同。Oracle数据库接受从公元前4712年到公元9999年的日期，而SQL Server只能处理从公元1753年到公元9999年的日期（除非用的是SQL Server 2008的datetime2数据类型，它允许从公元1年到公元9999年的日期）。MySQL介于Oracle和SQL Server之间，可以存储从公元1000年到公元9999年的日期。虽然对于大多数处理当前和未来事件的系统来说，这些差别可能并不重要，但存放历史时期的时候就请务必记住这些差别了。

下面介绍如何使用各种时间类型来实现前面的例子：

• 用于保存客户订单的预期未来发货日期和员工出生日期的列将使用date类型，因为未来发货时间精确到秒是不现实的，此外也没有必要知道一个人出生的具体时间。

• 保存客户订单实际发货时间信息的列可以用datetime类型，因为不仅要记录发货日期，还要记录发货时间。

• 记录用户修改表中某行的日期和时间，可以用使用timestamp类型。timestamp类型与datetime类型（包括年、月、日、小时、分钟、秒）保存的信息相同，但是当在表中新增一行或修改一行时，MySQL服务器将自动为timestamp列增加当前日期/时间信息。

• 仅包含年份数据的列将使用year类型。

• 保存完成任务所需时长数据的列将使用time类型。对于这种类型的数据，无需存储日期信息，因为你只关心完成任务所需的时/分/秒数。该类信息可以使用两个datetime列来存储（一个用于存放任务开始日期/时间，另一个用于存放任务完成日期/时间），两者的差值就是时长，但是很显然，使用单个time列会更简单。

第七章会探讨如何使用这些时间数据类型。

四、创建表

现在你已经了解了MySQL数据库中可能存储的数据类型，所以是时候看看如何在表定义中使用这些类型了。让我们先定义一个表来保存个人信息。

1.第一步：设计

开始设计表格之前，最好是头脑风暴一下，以确定需要包括的信息。以下是我在思考了一会儿之后想出的用于描述个人信息的项：

• 姓名

• 眼睛颜色

• 出生日期

• 地址

• 喜爱的食物

当然这些信息并不全面，但是这里已经够用了。下一步是分配列名和数据类型。下表（2-6）是我的初始设计：

name、address和favorite_foods列的类型为varchar，允许任何格式的数据条目。eye_color列限制两个字符只应为BR、BL或GR中的一个。birth_date列的类型为date，因为不需要包括具体的时间信息。

2.第二步：完善

在第一章中，我们介绍过规范化的概念——规范化是确保数据库设计中没有重复（外键除外）或复合列的过程。再次查看person表中的列时，会发现以下问题：

• name列实际上是一个由名字和姓氏组成的复合对象。

• 由于多个人可以拥有相同的姓名、眼睛颜色、出生日期等，因此person表中没有保证唯一性的列。

• address列也是一个复合对象，由街道、城市、州/省、国家和邮政编码组成。

• favorite_foods列是一个包含零个、一个或多个独立条目的列表，所以最好为这些数据创建一个单独的表，然后在其中包含person表的外键，这样就可以知道某个特定食物归属的人员了。

考虑到以上问题，下表（2-7）给出了person表的规范化版本：

既然person表有主键（person_id）来保证唯一性，那么下一步就是构建一个favorite_food表，其中包含指向person表的外键，如下表（2-8）所示：

 

person_id和food列构成favorite_food表的主键，person_id列也是person表的外键。

这些设计就够了吗？

将favorite_foods列从person表中移出绝对是个好主意，但这样就够了吗？例如，如果一个人把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), eye_color CHAR(2), 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) );

除了最后一项，这条语句中的其他所有内容应该都很好理解。在定义表时，需要告诉数据库服务器哪些列将用作表的主键，可以通过在表上创建约束（constraint）实现这一点。可以向表定义中添加多种类型的表约束。上述语句的约束是主键约束，它在person_id列上创建，并命名为pk_person。

有关约束，对于person表而言，还有另一种类型的约束也很有用。在前面的表（2-6）中，第三列只接受特定的值（比如eye_color列的“BR”和“BL”），此时可以给它增加一个检查约束，用以限制该列存放的值。MySQL允许将检查约束附加到列定义，如下所示：

eye_color CHAR(2) CHECK (eye_color IN ('BR','BL','GR')),

虽然检查约束在大多数数据库服务器上可以预期运行，但对MySQL服务器来说，它虽允许定义检查约束，但并不强制执行。其实MySQL确实有提供另一种名为enum的字符数据类型，它将检查约束合并到数据类型定义中。下面是用这种方法定义eye_color列的语句：

eye_color ENUM('BR','BL','GR'),

下面是person表的重定义，其中eye_color列以enum作为其数据类型：

CREATE TABLE person  (person_id SMALLINT UNSIGNED,  fname VARCHAR(20),  lname VARCHAR(20),  eye_color ENUM('BR','BL','GR'),  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)  );

在本章后面会介绍向列中添加违反检查约束（或者在MySQL中，违反其枚举值）的数据会发生什么事情。

现在可以使用mysql命令行工具运行create table语句了。如下所示：

mysql> CREATE TABLE person -> (person_id SMALLINT UNSIGNED, -> fname VARCHAR(20), -> lname VARCHAR(20), -> eye_color ENUM('BR','BL','GR'), -> 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.37 sec)

在处理完create table语句之后，MySQL服务器返回消息“Query OK，0 rows affected”，表示该语句没有语法错误。

如果想确认person表的存在，可以使用describe命令（简称desc）查看表定义：

mysql> desc person;+-------------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+----------------------+------+-----+---------+-------+| person_id | smallint(5) unsigned | NO | PRI | NULL | || fname | varchar(20) | YES | | NULL | || lname | varchar(20) | YES | | NULL | || eye_color | enum('BR','BL','GR') | 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.00 sec)

describe输出的第一列和第二列的含义很好理解，第三列显示将数据插入表中时是否可以省略特定列。现在我暂时不想深入探讨这个话题（简要介绍请参见下面的“什么是空值”），后面在第四章中，我们会全面探讨这个问题。第四列显示这列是否是键值（主键或外键），这个例子中，person_id列被标记为主键。第五列显示在向表中插入数据时，如果忽略某列，是否会用默认值填充该列。第六列（Extra）显示该列附加的说明信息。

什么是空值？

在某些情况下，插入数据的时候，可能无法为表中的某列提供具体的值。例如，在新增有关新客户订单数据时，还不能确定ship_date列。在本例中，该列被设置为null（注意，我并不是说它等于“null”这个字符串），这表示没有值。null被用于无法赋值的各种情况，例如：

• 不适用

• 未知

• 空集

在设计表时，可以指定哪些列允许为null（默认），哪些列不允许为null（通过在类型定义后添加关键字not null来指定）。

文章导读： 本章内容包括创建第一个数据库以及本书中示例所用的表和相关数据，你还将了解各种数据类型以及如何在创建表时使用它们。因为本书中的示例是针对MySQL数据库执行的，所以本章会偏向使用MySQL的...

如果你想用Python开辟副业赚钱，但不熟悉爬虫与反爬虫技术，没有接单途径，也缺乏兼职经验
关注下方微信公众号：Python编程学习圈，获取价值999元全套Python入门到进阶的学习资料以及教程，还有Python技术交流群一起交流学习哦。