创建和填充数据库

数据库的创建与初始化是掌握 SQL 技能的基础环节。只有先合理设计并建立数据库结构，才能高效地存储、管理和操作数据。

这里我们将系统讲解数据库的创建流程、表结构设计、表的创建方法，以及常用数据类型的选择与应用。 我们将以 MySQL 作为主要示例数据库，因其为当前主流的开源关系型数据库之一，且相关知识同样适用于其他主流数据库系统。

MySQL 数据库的创建

环境准备

在开始学习之前，我们需要一个可以工作的 MySQL 环境。如果你还没有安装 MySQL，可以访问MySQL的官网来进行下载。👇

点击进入下载

创建数据库和用户

一旦 MySQL 服务器安装完成并运行，我们就可以开始创建数据库和用户了。这个过程包括以下几个关键步骤：

|
$ mysql -u root -p

|
mysql> create database university;

|
mysql> grant all privileges on university.* to 'student_admin'@'localhost' identified by 'learn2024';

|
mysql> quit;
$ mysql -u student_admin -p university

使用 mysql 命令行工具

MySQL 命令行工具是学习 SQL 的最佳伴侣。当你成功登录后，可以通过 mysql> 提示符来执行各种 SQL 语句。 让我们先试试一个简单的查询来确认一切正常：

|
mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2025-09-25 03:34:12 |
+---------------------+
1 row in set (0.01 sec)

now() 是 MySQL 的内置函数，用于返回当前的日期和时间。从输出结果可以看到，MySQL 将查询结果格式化在一个由 +、- 和 | 字符组成的表格中，非常直观。

查询结果的底部还显示了返回的行数和执行时间，这对于了解查询性能很有帮助。

关于 FROM 子句的说明

在某些数据库系统中，比如 Oracle，每个查询都必须包含 FROM 子句来指定至少一个表。为了兼容这种要求，MySQL 也提供了一个名为 dual 的特殊表：

|
mysql> SELECT now() FROM dual;

这个查询和前面的查询效果完全相同。不过，如果你不需要与 Oracle 兼容，可以忽略 dual 表，直接使用不带 FROM 子句的 SELECT 语句。 当你完成操作后，可以输入 quit; 或 exit; 来退出 MySQL 命令行工具。

MySQL 数据类型

在创建数据库表之前，我们需要深入了解 MySQL 支持的各种数据类型。虽然所有主流数据库都能存储相同类型的基本数据（如字符串、日期和数字），但它们在特殊数据类型上往往有所差异。 对于初学者来说，掌握字符、数值和时间这三种基本数据类型就足够了，因为它们占据了实际工作中 98% 的使用场景。

字符数据

字符数据是数据库中最常见的数据类型之一。我们可以将字符数据存储为固定长度或可变长度的字符串。固定长度字符串会用空格右填充到指定长度，始终占用相同的字节数；而可变长度字符串不进行空格填充，占用的字节数可能会有所不同。

在定义字符列时，我们必须指定该列可以存储的字符串的最大长度。比如，如果我们想存储最多 20 个字符的字符串，可以使用以下两种定义方式：

|
char(20)     -- 固定长度
varchar(20)  -- 可变长度

char 类型的最大长度目前是 255 个字节，而 varchar 类型可以达到 65,535 个字节。一般来说，当存储的字符串长度都相同时（比如省份简称），我们使用 char 类型；当字符串长度变化较大时，我们使用 varchar 类型。

字符集

对于使用拉丁字母的语言（如英语），单个字节就足以存储每个字符。但是对于其他语言（如中文、日文、韩文），由于字符数量庞大，需要使用多个字节来存储每个字符。这样的字符集被称为多字节字符集。

MySQL 支持多种字符集，包括单字节和多字节字符集。我们可以使用以下命令查看服务器支持的字符集：

|
mysql> SHOW CHARACTER SET;

当安装 MySQL 服务器时，通常会自动选择 latin1 作为默认字符集。不过，我们可以为数据库中的每个字符列选择不同的字符集，甚至在同一个表中混合使用不同的字符集。

如果要选择非默认的字符集，可以在类型定义后指定：

|
varchar(20) character set utf8

我们也可以为整个数据库设置默认字符集：

|
create database foreign_sales character set utf8;

文本数据

当需要存储超过 64 KB 限制的 varchar 列的数据时，我们需要使用文本类型。MySQL 提供了几种不同大小的文本类型：

使用文本类型时需要注意几个要点。如果加载到文本列的数据超过了该类型的最大大小，数据将被截断。加载数据时不会移除尾随空格。在进行排序或分组时，只使用前 1,024 个字节，不过这个限制可以根据需要增加。

数值数据

虽然拥有一个叫做「数值」的通用数据类型看起来很合理，但实际上存在多种不同的数值数据类型，它们反映了数字的各种用途。

比如，一个表示客户订单是否已发货的列，这种布尔类型的列会包含 0（表示假）或 1（表示真）。系统生成的交易表主键通常从 1 开始，以 1 为增量递增到一个可能非常大的数字。客户电子购物篮的商品数量列的值会是 1 到 200 之间的正整数。而电路板钻孔机的位置数据可能需要精确到小数点后八位。

为了处理这些不同类型的数据，MySQL 提供了多种数值数据类型。最常用的是存储整数的类型。指定其中一种类型时，还可以指定数据为无符号的，这告诉服务器该列中存储的所有数据都大于或等于零。

整数类型

创建使用整数类型的列时，MySQL 会分配适当的存储空间，从 tinyint 的一个字节到 bigint 的八个字节。因此，应该尽量选择既能容纳可能存储的最大数字，又不会不必要地浪费存储空间的类型。

浮点类型

对于浮点数（如 3.1415927），可以从以下数值类型中选择：

使用浮点类型时，可以指定精度（小数点左右允许的总位数）和标度（小数点右边允许的位数），但这不是必需的。如果指定了精度和标度，存储在列中的数据如果超过了列的标度和/或精度，将会被四舍五入。

例如，定义为 float(4,2) 的列总共存储四位数字，小数点左边两位，右边两位。因此，这样的列可以正常处理数字 27.44 和 8.19，但数字 17.8675 会被四舍五入为 17.87。

时间数据

除了字符串和数字，我们几乎肯定会处理有关日期和时间的信息。这种类型的数据被称为时间数据。时间数据在数据库中的一些例子包括：特定事件预期发生的未来日期（如发货客户订单）、客户订单实际发货的日期、用户修改表中特定行的日期和时间、员工的出生日期等等。

MySQL 包含了处理所有这些情况的数据类型：

格式字符串的目的是显示数据检索时的表示方式，以及在插入或更新时间列时应该如何构造日期字符串。因此，如果想要使用默认格式 YYYY-MM-DD 将日期 2005 年 3 月 23 日插入到日期列中，需要使用字符串 '2005-03-23'。

不同的数据库服务器允许时间列的日期范围不同。Oracle 数据库接受从公元前 4712 年到公元 9999 年的日期，而 SQL Server 只处理从公元 1753 年到 9999 年的日期。MySQL 介于 Oracle 和 SQL Server 之间，可以存储从公元 1000 年到 9999 年的日期。

日期格式组件

不同时间类型的使用场景各不相同。存储客户订单的预期未来发货日期和员工出生日期的列会使用 date 类型，因为了解一个人出生的具体时间是不必要的，安排未来发货精确到秒也是不现实的。

存储客户订单实际发货时间的列会使用 datetime 类型，因为跟踪发货发生的日期和时间都很重要。

跟踪用户最后一次修改表中特定行的时间的列会使用 timestamp 类型。timestamp 类型保存与 datetime 类型相同的信息，但当向表中添加行或之后修改行时，MySQL 服务器会自动用当前日期/时间填充 timestamp 列。

仅保存年份数据的列会使用 year 类型。保存完成任务所需时间长度数据的列会使用 time 类型。对于这种类型的数据，存储日期组件是不必要的也是令人困惑的，因为我们只关心完成任务所需的小时/分钟/秒数。

表的创建

现在我们已经了解了可以在 MySQL 数据库中存储的各种数据类型，是时候学习如何在表定义中使用这些类型了。让我们从定义一个用来存储学生信息的表开始。

约束的使用

在约束的话题上，还有另一种类型的约束对 student 表很有用。我们提到 gender 列的允许值为 'M' 和 'F'。另一种叫做检查约束的约束可以限制特定列的允许值。MySQL 允许将检查约束附加到列定义上：

|
gender CHAR(1) CHECK (gender IN ('M','F'))

虽然检查约束在大多数数据库服务器上都按预期工作，但 MySQL 服务器允许定义检查约束但不强制执行它们。不过，MySQL 确实提供了另一种叫做 enum 的字符数据类型，它将检查约束合并到数据类型定义中：

|
gender ENUM('M','F')

我们在 student 表定义中已经使用了 enum 数据类型。现在我们可以使用 mysql 命令行工具运行 create table 语句了：

|
mysql> CREATE TABLE student
    -> (student_id SMALLINT UNSIGNED,
    -> first_name VARCHAR(20),
    -> last_name VARCHAR(20),
    -> gender ENUM('M','F'),
    -> birth_date DATE,
    -> major VARCHAR(50),
    -> email VARCHAR(

处理完 create table 语句后，MySQL 服务器返回消息「Query OK, 0 rows affected」，这告诉我们语句没有语法错误。如果想确认 student 表确实存在，可以使用 describe 命令（或简写为 desc）来查看表定义：

|
mysql> DESC student;
+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| student_id       | smallint(5) unsigned |      | PRI | 0       |       |
| first_name       | varchar(20)          | YES

什么是 Null？

在某些情况下，无法或不适用于为表中的特定列提供值。例如，在添加新学生信息时，phone 列可能暂时无法确定，或者某些学生还没有选择专业。在这种情况下，该列被称为 null（注意我们不说它等于 null），这表示值的缺失。

现在我们已经创建了 student 表，下一步是创建 course_enrollment 表：

|
CREATE TABLE course_enrollment
 (student_id SMALLINT UNSIGNED,
  course_code VARCHAR(10),
  course_name VARCHAR(50),
  semester VARCHAR(20),
  CONSTRAINT pk_course_enrollment PRIMARY KEY (student_id, course_code),
  CONSTRAINT fk_enrollment_student_id FOREIGN KEY (student_id)
  REFERENCES student (student_id)
 );

这与 student 表的 create table 语句非常相似，但有以下例外：

由于一个学生可以选修多门课程，仅靠 student_id 列不足以保证表中的唯一性。因此，该表有一个两列的主键：student_id 和 course_code。

course_enrollment 表包含另一种类型的约束，称为外键约束。这限制了 course_enrollment 表中 student_id 列的值只能包含在 student 表中找到的值。有了这个约束，如果 student 表中没有 student_id 为 27 的行，我们就无法在 course_enrollment 表中添加一行表示学生 27 选修了某门课程。

执行 create table 语句后，describe 显示以下内容：

|
mysql> DESC course_enrollment;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| student_id   | smallint(5) unsigned |      | PRI | 0       |       |
| course_code  | varchar(10)          |      |

现在表已经就位，下一个逻辑步骤是添加一些数据在我们的表中。

数据的填充和修改

有了 student 和 course_enrollment 表后，我们现在可以开始探索四个 SQL 数据语句：insert、update、delete 和 select。

插入数据

由于 student 和 course_enrollment 表中还没有任何数据，我们要探索的四个 SQL 数据语句中的第一个将是 insert 语句。insert 语句有三个主要组成部分：

要添加数据的表的名称、表中要填充的列的名称，以及用来填充列的值。

我们不需要为表中的每一列都提供数据（除非表中的所有列都定义为 not null）。在某些情况下，那些在初始 insert 语句中未包含的列将在稍后通过 update 语句获得值。在其他情况下，某列对于特定的数据行可能永远不会接收值。

生成数值主键数据

在向 student 表插入数据之前，讨论如何为数值主键生成值是很有用的。除了凭空选择一个数字之外，我们有几个选择：

我们可以查看表中当前的最大值并加一，或者让数据库服务器为我们提供值。

虽然第一个选择看起来可行，但在多用户环境中会出现问题，因为两个用户可能同时查看表并为主键生成相同的值。 相反，今天市场上的所有数据库服务器都提供安全、稳健的数值键生成方法。在某些服务器中（如 Oracle Database），使用单独的模式对象（称为序列）；但在 MySQL 的情况下，只需要为主键列打开自动增量功能。

通常我们在创建表时就这样做，但现在这样做提供了学习另一个 SQL 模式语句的机会，即 alter table，它用于修改现有表的定义：

|
ALTER TABLE student MODIFY student_id SMALLINT UNSIGNED AUTO_INCREMENT;

这个语句本质上重新定义了 student 表中的 student_id 列。如果我们描述该表，现在会在 student_id 的「Extra」列下看到列出的自动增量功能：

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

当向 student 表插入数据时，只需为 student_id 列提供 null 值，MySQL 会用下一个可用的数字填充该列（默认情况下，MySQL 从 1 开始自动增量列）。

insert 语句

现在所有部分都准备就绪，是时候添加一些数据了。以下语句在 student 表中为李明创建了一行：

|
INSERT INTO student
(student_id, first_name, last_name, gender, birth_date, major, enrollment_year)
VALUES (null, '明', '李', 'M', '2002-03-15', '计算机科学', 2024);

反馈（「Query OK, 1 row affected」）告诉我们语句语法正确，并且向数据库添加了一行。我们可以通过发出 select 语句来查看刚刚添加到表中的数据：

|
SELECT student_id, first_name, last_name, birth_date, major
FROM student;

|
+------------+------------+-----------+------------+----------------+
| student_id | first_name | last_name | birth_date | major          |
+------------+------------+-----------+------------+----------------+
|          1 | 明         | 李        | 2002-03-15 | 计算机科学     |
+------------+------------+-----------+------------+----------------+
1 row in set (0.06 sec)

可以看到，MySQL 自动帮我们把主键设置成了 1。因为现在 student 表里只有一条数据，所以我们直接查出了所有内容。如果以后表里有多条数据，我们就可以用 where 子句来筛选，比如只查 student_id 等于 1 的那一行：

|
SELECT student_id, first_name, last_name, birth_date, major
FROM student
WHERE student_id = 1;

虽然这个查询指定了特定的主键值，但我们可以使用表中的任何列来搜索行。例如，以下查询查找 last_name 列值为 '李' 的所有行：

|
SELECT student_id, first_name, last_name, major
FROM student
WHERE last_name = '李';

关于之前的 insert 语句，有几点值得一提：

没有为 email 和 phone 列提供值，这是可以的，因为这些列允许 null 值。为 birth_date 列提供的值是一个字符串，只要匹配表中显示的所需格式，MySQL 就会为我们将字符串转换为日期。列名和提供的值必须在数量和类型上对应，如果我们命名七列但只提供六个值，或者提供的值无法转换为相应列的适当数据类型，我们会收到错误。

李明还选修了三门课程，所以这里有三个 insert 语句来存储他的选课信息：

|
INSERT INTO course_enrollment (student_id, course_code, course_name, semester)
VALUES (1, 'CS101', '计算机程序设计基础', '2024春季');
 
INSERT INTO course_enrollment (student_id, course_code, course_name, semester)
VALUES (1, 'MATH201', '高等数学', '2024春季');
 
INSERT INTO course_enrollment (student_id, course_code, course_name, semester)
VALUES (1, 'ENG101', '大学英语', '2024春季');

这是一个按课程代码顺序检索李明所选课程的查询，使用 order by 子句：

|
SELECT course_code, course_name, semester
FROM course_enrollment
WHERE student_id = 1
ORDER BY course_code;

|
+-------------+------------------------+----------+
| course_code | course_name            | semester |
+-------------+------------------------+----------+
| CS101       | 计算机程序设计基础     | 2024春季 |
| ENG101      | 大学英语               | 2024春季 |
| MATH201     | 高等数学               | 2024春季 |
+-------------+------------------------+----------+
3 rows in set (0.02 sec)

order by 子句告诉服务器如何排序查询返回的数据。没有 order by 子句，就无法保证表中的数据会以任何特定顺序检索。

为了让李明不那么孤单，我们可以执行另一个 insert 语句将王红添加到 student 表：

|
INSERT INTO student
(student_id, first_name, last_name, gender, birth_date,
major, email, phone, enrollment_year)
VALUES (null, '红', '王', 'F', '2003-08-22',
'英语文学', 'wanghong@university.edu', '138-8888-9999', 2024);

由于王红友好地提供了她的联系信息，我们包含了比插入李明数据时多三列的信息。如果我们再次查询表，会看到王红的行被分配了主键值 2：

|
mysql> SELECT student_id, first_name, last_name, birth_date, major
    -> FROM student;
+------------+------------+-----------+------------+----------------+
| student_id | first_name | last_name | birth_date | major          |
+------------+------------+-----------+------------+----------------+
|          1 | 明         | 李        | 2002-03-15 | 计算机科学     |
|          2

更新数据 - update

当李明的数据最初添加到表中时，insert 语句中省略了联系信息的数据。下面的语句显示了如何通过 update 语句填充这些列：

|
UPDATE student
SET email = 'liming@university.edu',
    phone = '136-6666-8888'
WHERE student_id = 1;

|
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

服务器回应了两行消息：「Rows matched: 1」项告诉我们 where 子句中的条件匹配了表中的单行，「Changed: 1」项告诉我们表中的单行已被修改。由于 where 子句指定了李明行的主键，这正是我们期望发生的。

根据 where 子句中的条件，也可以使用单个语句修改多于一行。例如，考虑如果你的 where 子句如下所示会发生什么：

|
WHERE student_id < 10

由于李明和王红的 student_id 值都小于 10，他们的行都会被修改。如果完全省略 where 子句，update 语句将修改表中的每一行。

删除数据 - delete

假设王红决定转学到其他大学，我们需要将她的信息从数据库中删除。可以通过 delete 语句来实现：

|
DELETE FROM student
WHERE student_id = 2;

|
Query OK, 1 row affected (0.01 sec)

同样，主键被用来隔离感兴趣的行，所以从表中删除了单行。与 update 语句类似，根据 where 子句中的条件，可以删除多于一行，如果省略 where 子句，将删除所有行。

当好语句变坏时

到目前为止，我们现在学习的所有 SQL 数据语句都是格式良好的，并且遵守了规则。但是，基于 student 和 course_enrollment 表的表定义，在插入或修改数据时有很多方法可能会出错。所以我们展示一些常见的错误以及 MySQL 服务器将如何响应。

非唯一主键

由于表定义包括主键约束的创建，MySQL 将确保不会向表中插入重复的键值。下面的语句尝试绕过 student_id 列的自动增量功能，并在 student 表中创建另一行，其 student_id 为 1：

|
mysql> INSERT INTO student
(student_id, first_name, last_name, gender, birth_date)
VALUES (1, '强', '张', 'M', '2002-12-10');

|
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

这个错误清楚地告诉我们主键值重复了。虽然你可以创建具有相同姓名、专业、出生日期等的两行（至少在当前的模式对象下），但前提是它们具有不同的 student_id 列值。

不存在的外键

course_enrollment 表的表定义包括在 student_id 列上创建外键约束。这个约束确保输入到 course_enrollment 表中的所有 student_id 值都存在于 student 表中。如果尝试创建违反此约束的行，会发生以下情况：

|
INSERT INTO course_enrollment (student_id, course_code, course_name)
VALUES (999, 'HIST101', '中国古代史');

|
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails ('university'.'course_enrollment', CONSTRAINT 'fk_enrollment_student_id' FOREIGN KEY
('student_id') REFERENCES 'student' ('student_id'))

在这种情况下，course_enrollment 表被认为是子表，student 表被认为是父表，因为 course_enrollment 表依赖于 student 表获取其某些数据。如果计划向两个表中输入数据，需要先在父表中创建行，然后才能向 course_enrollment 中输入数据。

列值违反

student 表中的 gender 列被限制为值 'M'（男性）和 'F'（女性）。如果错误地尝试将列的值设置为任何其他值，将收到以下响应：

|
UPDATE student
SET gender = 'X'
WHERE student_id = 1;

|
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

该错误消息表明为 gender 列提供的值不符合其定义的约束条件，导致服务器拒绝该操作。

无效的日期转换

如果构造一个字符串来填充日期列，而该字符串与预期的格式不匹配，将收到另一个错误。这是一个使用日期格式不匹配默认日期格式「YYYY-MM-DD」的示例：

|
UPDATE student
SET birth_date = '2002年3月15日'
WHERE student_id = 1;

|
ERROR 1292 (22007): Incorrect date value: '2002年3月15日' for column 'birth_date'
at row 1

通常，明确指定格式字符串而不是依赖默认格式总是一个好主意。这是另一个版本的语句，使用 str_to_date 函数来指定要使用的格式字符串：

|
UPDATE student
SET birth_date = str_to_date('2002年3月15日' , '%Y年%m月%d日')
WHERE student_id = 1;

|
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

现在数据库服务器很满意，李明的数据也正确地存储在数据库中了。

MySQL 日期格式化字符

在我们前面讨论各种时间数据类型时，我们显示了日期格式字符串，如「YYYY-MM-DD」。虽然许多数据库服务器使用这种格式化风格，但 MySQL 使用 %Y 来表示四字符年份。这里有一些在 MySQL 中将字符串转换为日期时间时可能需要的格式化字符：

这些格式化字符为我们在处理日期和时间数据时提供了很大的灵活性。

小结

通过学习学生选课系统和银行模式，你已经掌握了创建和填充数据库的基本技能。从简单的学生表到复杂的银行业务表，我们学会了如何设计规范化的数据库结构。在接下来的学习中，我们将深入探讨如何查询和操作这些数据。

当然现在我们可以安全的删除我们刚刚创建的两个表了：

|
mysql> DROP TABLE course_enrollment;
Query OK, 0 rows affected (0.56 sec)
 
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.05 sec)

小练习

6. 设计学生成绩表

假设你需要为学校设计一个学生成绩管理系统，需要存储以下信息：

• 学生学号（唯一标识）
• 学生姓名
• 课程编号
• 课程名称
• 成绩（0-100 之间的整数）
• 考试日期

请写出创建这个表的 SQL 语句，并说明你选择的数据类型的原因。

|
CREATE TABLE student_grade
 (student_id SMALLINT UNSIGNED,
  student_name VARCHAR(30),
  course_code VARCHAR(10),
  course_name VARCHAR(50),
  grade TINYINT UNSIGNED,
  exam_date DATE,
  CONSTRAINT pk_student_grade PRIMARY KEY (student_id, course_code)
 );

现在 student 表有了一个主键（student_id）来保证唯一性，下一步是建立一个 course_enrollment 表，其中包含指向 student 表的外键：

student_id 和 course_code 列组成 course_enrollment 表的主键，student_id 列也是指向 student 表的外键。