第2章 定义数据结构

第3章 管理数据库对象

第4章 规格化过程

第5章 操作数据

第6章 管理数据库事务

第2章 定义数据结构

本章的重点包括：

概述表的底层数据

简介基本的数据类型

使用不同类型的数据

展示不同数据类型之间的区别

在本章中，我们将进一步研究前一章结尾时所展示的数据，讨论数据本身的特征及其如何保存在关系型数据库里。数据类型有多种，稍后就会介绍。

2.1 数据是什么

数据是一个信息集合，以某种数据类型保存在数据库里。数据包括姓名、数字、货币、文本、图形、小数、计算、统计等，几乎涵盖能够想象到的任何东西。数据可以保存为大写、小写或大小写混合，数据可以被操作或修改，大多数数据在其生存周期内不会保持不变。

数据类型用于指定特定列所包含数据的规则，它决定了数据保存在列里的方式，包括分配给列的宽度，以及值是否可以是字母、数字、日期和时间等。任何数据或数据的组合都有对应的数据类型，这些数据类型用于存储像字母、数字、日期和时间、图像、二进制数据等。更详细地说，数据可以包括姓名、描述、数字、计算、图像、图像描述、文档等。

数据是数据库的意义所在，必须受到保护。数据的保护者就是数据库管理员（DBA），但每个数据库用户也有责任采取必要手段来保护数据。关于数据安全的内容将在第 18 章和第19章详细讨论。

2.2 基本数据类型

本节将介绍ANSI SQL支持的基本数据类型。数据类型是数据本身的特征，其特性被设置到表里的字段。举例来说，我们可以指定某个字段必须包含数字值，不允许输入由数字或字母组成的字符串；我们也不希望在保存货币数值的字段里输入字母。为数据库里每个字段定义数据类型可以大幅减少数据库里由于输入错误而产生的错误数据。字段定义（数据类型定义）是一种数据检验方式，控制了每个字段里可以输入的数据。

在一些RDBMS实现里，一些数据类型可以根据其格式自动转化为其他数据类型，这种转换被称为隐式转换，表示数据库会自动完成转换。举例来说，从一个数值字段取出一个数值1000.92，把它输入到一个字符串字段里，此时数据库就会完成自动转换。其他一些数据类型不能由主机 RDBMS 隐式转换，就必须经过显式转换，这通常需要调用 SQL 函数，比如CAST或CONVERT，如下所示：

像其他大多数语言一样，最基本的数据类型是：

字符串类型；

数值类型；

日期和时间类型。

提示：SQL数据类型

SQL 的每个实现都具有自己的数据类型集。使用某个实现所特有的数据类型是必要的，以支持每个实现处理存储数据的方式策略。但基本数据类型在不同实现之间还是相同的。

2.2.1 定长字符串

定长字符串通常具有相同的长度，是使用定长数据类型保存的。下面是 SQL 定长字符串的标准：

n是一个数字，定义了字段里能够保存的最多字符数量。

有些SQL实现使用CHAR数据类型来保存定长数据。字母可以保存到这种数据类型里。州名缩写就是定长数据类型的一个例子，因为所有的缩写都由两个字母组成。

在定长数据类型里，通常使用空格来填充数量不足的字符。举例来说，如果字段长度是10，而输入的数据只有5位，那么剩余5位就会被记录为空格。填充空格确保了字段里每个值都具有相同的长度。

警告：定长数据类型

不要使用定长数据类型来保存长度不定的数据，比如姓名。如果不恰当地使用定长数据类型，可能会导致浪费可用空间，以及影响对不同的数据进行精确比较。

应该使用变长数据类型来保存长度不定的字符串，从而节省数据库空间。

2.2.2 变长字符串

SQL支持变长字符串，也就是长度不固定的字符串。下面是SQL变长字符串的标准：

n是一个数字，表示字段里能够保存的最多字符数量。

常见的变长字符串数据类型有VARCHAR、VARINARY和VARCHAR2。VARCHAR是ANSI标准，Microsoft SQL Server和MySQL也使用它；VARINARY和VARCHAR2都是由Oracle 使用的。定义为字符的字段里可以保存数字和字母，这意味着数据中可能包含数字字符。VARBINARY类似于VARCHAR和VARCHAR2，只是它包含的是长度不定的字节。这种数据类型通常被用来保存数字式数据，例如图像文件。

定长数据类型利用空格来填充字段里的空白，但变长字符串不这样做。举例来说，如果某个变长字段的长度定义为10，而输入的字符串长度为5，那么这个值的总长度也就是5，这时并不会使用空格来填充字段里的空白。

2.2.3 大对象类型

有些变长数据类型需要保存更长的数据，超过了一般情况下为VARCHAR字段所保留的长度，比如现在常见的BLOB和TEXT数据类型。这些数据类型是专门用于保存大数据集的。BLOB是二进制大对象，它的数据是很长的二进制字符串（字节串）。BLOB适合在数据库里存储二进制媒体文件，比如图像和MP3。

TEXT数据类型是一种长字符串类型，可以被看作一个大VARCHAR字段，通常用于在数据库里保存大字符集，比如博客站点的HTML输入。在数据库里保存这种类型的数据可以实现站点的动态更新。

2.2.4 数值类型

数值被保存在定义为某种数值类型的字段里，一般包括NUMBER、INTEGER、REAL、DECIMAL等。

下面是SQL数值的标准：

p表示字段的最大长度。

s表示小数点后面的位数。

SQL实现中一个通用的数值类型是NUMERIC，它符合ANSI标准。数值可以是0、正值、负值、定点数和浮点数。下面是使用NUMERIC的一个范例：

这个命令把字段能够接受的最大值限制为 99 999。在本书范例所涉及的数据库实现中， NUMERIC都是以DECIMAL类型实现的。

2.2.5 小数类型

小数类型是指包含小数点的数值。SQL 的小数标准如下所示，其中 p 表示有效位数，s表示标度。

有效位数是数值的总体长度。举例来说，在数值定义DECIMAL(4,2)里，有效位数是4，也就是说数值总位数是4。标度是小数点后面的位数，在前例中是2。如果实际数值的小数码数超出了定义的位数，数值就会被四舍五入。比如34.33写入到定义为DECIMAL(3,1)的字段时，会被四舍五入为34.3。

如果数值按照如下方式被定义，其最大值就是99.99：

有效位数是4，表示数值的总体长度是4；标度是2，表示小数点后面保留2位。小数点本身并不算作一个字符。

定义为DECIMAL(4,2)的字段允许输入的数值包括：

最后一个值 12.449 在保存到字段时会被四舍五入为 12.45。在这种定义下，任何12.445~12.449之间的数值都会被四舍五入为12.45。

2.2.6 整数

整数是不包含小数点的数值（包括正数和负数）。

下面是一些有效的整数：

2.2.7 浮点数

浮点数是有效位数和标度都可变并且没有限制的小数数值，任何有效位数和标度都是可以的。数据类型REAL代表单精度浮点数值，而DOUBLE PRECISION表示双精度浮点数值。单精度浮点数值的有效位数为1~21（包含），双精度浮点数值的有效位数为22~53（包含）。下面是一些FLOAT数据类型的范例：

2.2.8 日期和时间类型

日期和时间数据类型很显然是用于保存日期和时间信息的。标准 SQL 支持 DATETIME数据类型，它包含以下类型：

DATETIME数据类型的元素包括：

注意：日期和时间类型

SECOND元素还可以再分解为几分之一秒，其范围是00.000~61.999，但并不是所有SQL实现都支持这个范围。多出来的1.999秒是用于实现闰秒的。

每种SQL实现可能都具有自定义的数据类型来保存日期和时间。前面介绍的数据类型和元素是每个 SQL 厂商都应该遵守的标准，但大多数实现都具有自己的数据类型来保存日期值，其形式与实际存储方式有所不同。

日期数据一般不指定长度。稍后我们会更详细地介绍日期类型，包括日期信息在某些实现里的保存方式、如何使用转换函数操作日期和时间，并且用范例展示在实际工作中如何使用日期和时间。

2.2.9 直义字符串

直义字符串就是一系列字符，比如姓名或电话号码，这是由用户或程序明确指定的。直义字符串包含的数据与前面介绍的数据类型具有一样的属性，但字符串的值是已知的。列本身的值通常是不能确定的，因为每一列通常包含了字段在全部记录里的不同值。

实际上并不需要把字段指定为直义字符串数据类型，而是指定字符串。直义字符串的范例如下所示：

字符型的字符串由单引号包围，数值45000没有用单引号包围，而第二个45000用双引号包围了。一般来说，字符型字符串需要使用单引号，而数值型不需要。

将一个数据转换成数值类型的过程属于隐式转换。在这个过程中，数据库会自动判断应该使用哪种数据类型。所以，如果一个数据没有使用单引号包围起来，那么SQL程序就会将其认定为数值类型。因此，必须要特别留意数据的形式。否则，存储结果可能出现偏差，或者报错。稍后将介绍如何在数据库查询里使用直义字符串。

2.2.10 NULL数据类型

第1章已经介绍过，NULL值表示没有值。NULL值在SQL里有广泛的应用，包括表的创建、查询的搜索条件，甚至是在直义字符串里。

下面是两种引用NULL值的方法：

NULL（关键字NULL本身）；

下面这种形式并不代表NULL值，它只是一个包含字符N-U-L-L的直义字符串：

在使用NULL数据类型时，需要明确它表示相应字段不是必须要输入数据的。如果某个字段必须包含数据，就把它设置为NOT NULL。只要字段有可能不包含数据，最好就把它设置为NULL。

2.2.11 布尔值

布尔值的取值范围是TRUE、FALSE和NULL，用于进行数据比较。举例来说，在查询中设置条件时，每个条件都会被求值，得到TRUE、FALSE或NULL。如果查询中所有条件的值都是TRUE，数据就会被返回；如果某个条件的值是FALSE或NULL，数据就不会返回。

比如下面这个范例：

这可能是查询里的一个条件，目标表里每行数据都根据这个条件进行求值。如果表里某行的NAME字段值是SMITH，条件的值就是TRUE，相应的记录就会被返回。

大多数数据库实现并没有一个严格意义上的BOOLEAN类型，而是代之以各自不同的实现方法。MySQL拥有BOOLEAN类型，但实质上与其现有的TINYINT类型相同。Oracle倾向于让用户使用一个CHAR(1)值来代替布尔值，而SQL Server则使用BIT来代替。

注意：数据类型实现上的差异

前面介绍的这些数据类型在不同的 SQL 实现里可能具有不同的名称，但其概念是通用的。其中大多数数据类型得到了大多数关系型数据库的支持。

2.2.12 自定义类型

自定义类型是由用户定义的类型，它允许用户根据已有的数据类型来定制自己的数据类型，从而满足数据存储的需要。自定义类型极大地丰富了数据存储的可能性，使开发人员在数据库程序开发过程中具有更大的灵活性。语句CREATE TYPE用于创建自定义类型。

举例来说，在MySQL和Oracle中，可以像下面这样创建一个类型：

然后可以像下面这样引用自定义类型：

表EMP_PAY第一列EMPLOYEE的类型是PERSON，这正是在前面创建的自定义类型。

2.2.13 域

域是能够被使用的有效数据类型的集合。域与数据相关联，从而只接受特定的数据。在域创建之后，我们可以向域添加约束。约束与数据类型共同发挥作用，从而进一步限制字段能够接受的数据。域的使用类似于自定义类型。

像下面这样就可以创建域：

像下面这样为域添加约束：

然后像下面这样引用域：

2.3 小结

SQL具有多种数据类型，对于使用过其他编程语言的人来说，这些都不算陌生。数据类型允许不同类型的数据保存到数据库，比如单个字符、小数、日期和时间。无论是使用像 C这样的第三代编程语言，还是使用关系型数据库实现SQL编码，数据类型的概念都是一样的。当然，不同实现中数据类型的名称可能有所不同，但其工作方式基本上是一样的。另外，关系型数据库管理系统并不是一定要实现 ANSI 标准里规定的全部数据类型才会被认为是与ANSI兼容的，因此最好查看具体实现的文档来了解可以使用的数据类型。

在考虑数据类型、长度、标度和精度时，一定要仔细地进行短期和长远的规划。另外，公司制度和希望用户以什么方式访问数据也是要考虑的因素。开发人员应该了解数据的本质，以及数据在数据库里是如何相互关联的，从而使用恰当的数据类型。

2.4 问与答

问：当字段被定义为字符类型时，为什么还可以保存像个人社会保险号码这样的数字值呢？

答：字符串数据类型允许输入字母数字，而数字值当然是属于这个范围内的。这个过程被称为隐式转换，它是由数据库系统自动完成的。一般来说，只有用于计算的数字才以数值类型保存。但从另一方面来说，把全部数值字段都设置为数值类型有助于控制字段的输入数据。

问：定长和变长数据类型之间到底有什么区别呢？

答：假设我们把个人姓名里的姓字段定义为长度为20B的定义数据类型，而某人的姓是Smith。当这个数据进入表之后，会占据20B的空间，其中5B用于保存Smith，另外15B是额外的空格（因为这是定长数据类型）。如果使用长度为 20B 的变长数据类型，并且也输入Smith作为数据，那么它只会占据 5B。想象一下，如果要添加 100 000条记录，那么使用变长数据类型也许就会节省1.5MB。

问：数据类型的长度有限制吗？

答：当然有，而且不同实现中对此限制也是有所区别的。

2.5 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

2.5.1 测验

1．判断对错：个人社会保险号码，输入格式为 '1111111111'，它可以是下面任何一种数据类型：定长字符、变长字符、数值。

2．判断对错：数值类型的标度是指数值的总体长度。

3．所有的SQL实现都使用同样的数据类型吗？

4．下面定义的有效位数和标度分别是多少？

5．下面哪个数值能够输入到定义为DECIMAL(4,1)的字段里？

A．16.2

B．116.2

C．16.21

D．1116.2

E．1116.21

6．什么是数据？

2.5.2 练习

1．考虑以下字段名称，为它们设置适当的数据类型，确定恰当的长度，并给出一些示范数据：

a）ssn

b）state

c）city

d）phone_number

e）zip

f）last_name

g）first_name

h）middle_name

i）salary

j）hourly_pay_rate

k）date_hired

2．同样是这些字段，判断它们应该是NULL或NOT NULL。体会在不同的应用场合，有些一般是NOT NULL的字段可能应该是NULL，反之亦然。

a）ssn

b）state

c）city

d）phone_number

e）zip

f）last_name

g）first_name

h）middle_name

i）salary

j）hourly_pay_rate

k）date_hired

3．现在要为后面的课程创建一个数据库。在此之前，先要安装一种数据库实现——MySQL、Oracle或者Microsoft SQL Server。

MySQL

在Windows操作系统下，找到MySQL的安装目录，双击bin目录，再双击名为mysql.exe的可执行文件。如果看到错误消息说“server could not be found”，就首先从 bin 目录里执行winmysqladmin.exe，然后输入用户名和密码。在服务程序启动之后，再从bin目录里执行mysql.exe。

在mysql>提示符下，输入如下命令来创建本书练习所用的数据库：

命令输入后要按掉头键。在进行本书后续的练习时，我们先要运行mysql.exe，然后在命令提示符下输入如下命令来使用刚才创建的数据库：

Oracle

打开网页浏览器并进入管理主页，通常管理主页的网址是 http://127.0.0.1:8080/apex。此时会出现登录界面，如果是第一次登录系统，用户名为system，密码为安装系统时，由用户所设置的密码。在管理界面中，有SQL、SQL Commands和Enter Command三种运行方式可供选择。在命令行界面输入以下命令并按运行键：

在 Oracle 中创建一个用户后，系统会自动创建一个对应的规划（schema）。因此，运行上述命令后，在创建了一个用户的同时，也创建了一个名为learnsql的规划（schema）。Oracle中的规划（schema），相当于MySQL和Microsoft SQL Server中的数据库。退出系统以后，以新创建的用户身份重新登录系统，就可以看到规划（schema）。

Microsoft

在“开始”菜单的“运行”窗口中，输入 SSMS.exe 并按“确定”按钮。此时，开始运行SQL Server Management Studio。弹出的第一个对话框用于连接数据库。此处的服务器名称应该为“localhost”，如果系统没有自动显示出来，则可以手工输入。其余选项保持不变，单击“连接”按钮。此时，在界面的左侧会显示出用户的本地数据库实例。用鼠标右键单击“localhost”，在弹出的快捷菜单中选择“新建查询”选项后，界面右侧会打开一个查询窗口。输入以下命令并按F5键：

用鼠标右键单击“localhost”下名为“数据库”的文件夹，在弹出的快捷菜单中选择“刷新”选项。之后单击文件夹前面的“+”符号展开文件夹，即可看到刚刚创建的名为“learnsql”的数据库。

第3章 管理数据库对象

本章重点包括：

数据库对象简介

规划简介

表简介

讨论表的实质与属性

创建和操作表的范例

讨论表存储选项

引用完整性和数据一致性的概念

本章将介绍数据库对象：它们是什么、它们的作用、它们如何存储、它们之间的关系。数据库对象是关系型数据库的底层构架，是数据库里保存信息的逻辑单元。本章介绍的内容主要是围绕表的，其他数据库对象将在后面的章节讨论。

3.1 什么是数据库对象

数据库对象是数据库里定义的、用于存储或引用数据的对象，比如表、视图、簇、序列、索引和异名。本章的内容以表为主，因为它是关系型数据库里最主要、最简单的数据存储形式。

3.2 什么是规划

规划是与数据库某个用户名相关联的数据库对象集合。相应的用户名被称为规划所有人，或是关联对象组的所有人。数据库里可以有一个或多个规划。用户只与同名规划相关联，通常情况下反之亦然。一般来说，当用户创建一个对象时，就是在自己的规划里创建了它，除非明确指定在另一个规划里创建它。因此，根据在数据库里的权限，用户可以创建、操作和删除对象。规划可以只包含一个表，也可以包含无数个对象，其上限由具体的SQL实现决定。

假设我们从管理员获得了一个数据库用户名和密码，用户名是USER1。我们登录到数据库并创建一个名为 EMPLOYEE_TBL 的表，这时对于数据库来说，表的实际名称是USER1.EMPLOYEE_TBL，这个表的规划名是USER1，也就是这个表的所有者。这样，我们就为这个规划创建了第一个表。

当我们访问自己所拥有的表时（在自己的规划里），不必引用规划名称。举例来说，使用下面两种方式都可以引用刚才创建的表：

我们当然喜欢使用第一种方法，因为它简单，需要敲击键盘的次数比较少。如果其他用户要访问这个表，就必须指定规划名称，如下所示：

第 20 章将介绍如何分配权限，从而让其他用户访问我们的表。还会介绍异名的概念，也就是让表具有另一个名称，使我们在访问表时不必指定规划名。图3.1 展示了关系型数据库里的两个规划。

图3.1 数据库里的规划

图3.1里的数据库有两个用户账户：USER1和USER2。每个用户都有自己的规划，他们访问自己的表和对方的表的方式如下所示：

USER1访问自己的TABLE1： TABLE1

USER1访问自己的TEST： TEST

USER1访问USER2的TABLE10： USER2.TABLE10

USER1访问USER2的TEST： USER2.TEST

在这个范例里，两个用户都有一个名为TEST的表。在数据库里，不同的规划中可以具有名称相同的表。从另一个角度来说，表的名称里实际上包含着规划名，所以不同规划里表面上同名的表实际上具有不同的名称。比如USER1.TEST与USER2.TEST显然是不同的。如果在访问表时没有指定规划名，数据库服务程序会默认选择用户所拥有的表。也就是说，如果USER1要访问表TEST，数据库服务程序会先查找USER1拥有的名为TEST的表，然后再查找USER1拥有的其他对象，比如指向另一个规划里的表的异名。第21章会详细介绍异名的概念。用户必须明确理解自己规划内对象和规划外对象的区别，如果在执行修改表的操作时没有指定规划名，比如使用DROP命令，数据库会认为用户要操作自己规划里的表，这可能会导致意外删除错误的对象。因此，在进行数据库操作时，一定要注意自己是以什么身份登录到数据库的。

注意：对象命名规则在不同的数据库服务程序中有所差异

每个数据库服务程序都有命名对象和对象元素（比如字段）的规则，请查看具体实现的说明文档来了解详细要求。

3.3 表：数据的主要存储方式

表是关系型数据库里最主要的数据存储对象，其最简单形式是由行和列组成，分别都包含着数据。表在数据库占据实际的物理空间，可以是永久的或是临时的。

3.3.1 列

字段在关系型数据库也被称为列，它是表的组成部分，被设置为特定的数据类型。数据类型决定了什么样的数据可以保存在相应的列中，从而确保了数据的完整性。

每个数据库表都至少要包含一列。列元素在表里用于保存特定类型的数据，比如人名或地址。举例来说，姓名就可以作为顾客表里一个有效的列。图3.2展示了表里的列。

图3.2 列的范例

一般来说，列的名称应该是连续的字符串，其长度在不同SQL实现中都有明确规定。我们一般使用下划线作为分隔符，比如表示顾客姓名的列可以命名为CUSTOMER_NAME，它比CUSTOMERNAME更好一些。这样做可以提高数据库对象的可读性。读者也可以使用其他命名规则，例如驼峰匹配，以满足特定的需求。对于一个数据库开发团队来说，明确一个命名规则，并在开发的全过程中严格遵守这一规则，是非常重要的。

列中最常见的数据类型是字符串。这种数据可以保存为大写或小写字符，应该根据数据的使用方式具体选择。在大多数情况下，出于简化和一致的目的，数据是以大写存储的。如果数据库里存储的数据具有不同的大小写，我们可以根据需要利用函数把数据转化为大写或小写，具体函数将在第11章介绍。

列也可以指定为NULL或NOT NULL，当设置为NOT NULL时，表示其中必须包含数据；设置为NULL时，就表示可以不包含数据。NULL不是空白，而是类似于一个空的字符串，在数据库中占据了一个特殊的位置。因此，如果某一个位置缺少数据，就可以使用NULL。

3.3.2 行

行是数据库表里的一条记录。举例来说，顾客表里的一行数据可能包含顾客的标识号码、姓名、地址、电话号码、传真号码等。行由字段组成，表最少可以包含一行数据，也可以包含数以百万计的记录。图3.3展示了表里的行。

图3.3 行的范例

3.3.3 CREATE TABLE语句

SQL里的CREATE TABLE语句用于创建表。虽然创建表的实际操作十分简单，但在执行CREATE TABLE命令之前，应该花更多的时间和精力来设计表的结构，这样可以节省反复修改表结构而浪费的时间。

注意：本章所使用的数据类型

在本章的范例里，我们使用流行的数据类型CHAR（定长字符）、VARCHAR （变长字符）、NUMBER（数值，小数和整数）和DATE（日期和时间值）。

在创建表时，需要考虑以下一些基本问题。

表里会包含什么类型的数据？

表的名称是什么？

哪个（或哪些）列组成主键？

列（字段）的名称是什么？

每一列的数据类型是什么？

每一列的长度是多少？

表里哪些列可以是NULL？

注意：不同的系统往往有不同的命名规则

在命名对象和其他数据库元素时，一定要查看具体实现的规则。数据库管理员通常会采用某种“命名规范”来决定如何命名数据库里的对象，以便区分它们的用途。

在考虑了这些问题之后，实际的CREATE TABLE命令就很简单了。

创建表的基本语法如下所示：

在这个语句里，最后一个字符是分号。此外，括号是可选的。大多数SQL实现都以某些字符来结束命令，或是把命令发送到数据库服务程序。Oracle、Microsoft SQL Server和MySQL使用分号；而Transact-SQL、Microsoft SQL Server的ANSI SQL版本却不强制要求。不过，最好还是使用这样的字符来结束命令。在本书中，我们使用分号。

要创建一个名为EMPLOYEE_TBL的表，使用MySQL语法规则的代码如下：

下述代码同时适用于Oracle和Microsoft SQL Server：

这个表包含8列。列的名称中利用下划线对单词进行分隔（EMPLOYEE_ID被缩写为EMP_ID），这种方式可以让表和列的名称具有更好的易读性。每一个列都设置了数据类型和长度。同时，通过使用 NULL/NOT NULL，指定了哪些字段必须包含内容。EMP_PHONE被定义为NULL，表示它的内容可以为空，因为有的人可能没有电话号码。各个列定义之间以逗号分隔，全部列定义都在一对圆括号里（左括号在第一列之前，右括号在最后一列之后）。

注意：不同的实现对数据类型的规定有所不同

不同实现对于名称长度与可使用的字符具有不同的规定。

这个表里的每条记录，也就是每一行数据，会包含以下内容：

在这个表里，每个字段就是一列。列EMP_ID可能包含一个雇员的标识号码，也可能包含多个，这取决于数据库查询或业务的需要。

3.3.4 命名规范

在为对象选择名称时，特别是表和列的名称，应该让名称反应出所保存的数据。比如说，保存雇员信息的表可以命名为EMPLOYEE_TBL。列的名称也是如此，比如保存雇员电话号码的列，显然命名为PHONE_NUMBER是比较合适的。

3.3.5 ALTER TABLE命令

在表被创建之后，我们可以使用ALTER TABLE命令对其进行修改。可以添加列、删除列、修改列定义、添加和去除约束，在某些实现中还可以修改表STORAGE值。ALTER TABLE命令的标准如下所示：

一、修改表的元素

列的属性是其所包含数据的规则和行为。利用ALTER TABLE命令可以修改列的属性，在此“属性”的含义是：

列的数据类型；

列的长度、有效位数或标度；

列值能否为空。

下面的范例使用ALTER TABLE命令修改表EMPLOYEE_TBL的EMP_ID列：

这一列定义的数据类型没有变，但是长度从9变为10。

二、添加列

如果表已经包含数据，这时添加的列就不能定义为NOT NULL，这是一条基本规则。NOT NULL意味着这一列在每条记录里都必须包含数据。所以，在添加一条定义为NOT NULL的列时，如果现有的记录没有包含新列所需要的数据，我们就会陷入到自相矛盾的境地。

因此，强行向表添加一列的方法如下：

1．添加一列，把它定义为NULL（这一行不一定要包含数据）；

2．给这个新列在每条记录里都插入数据；

3．把列的定义修改为NOT NULL。

三、添加自动增加的列

有时我们需要一列的数据能够自动增加，从而让每一行都具有不同的序号。在很多情况下都需要这样做，比如数据中如果没有适合充当主键的值，或是我们想利用序列号对数据进行排序。创建自动增加的列是相当简单的。MySQL 提供了 SERIAL 方法为表生成真正的唯一值，如下所示：

注意：在创建表时使用NULL

列的默认属性是NULL，所以在CREATE TABLE语句里不必明确设置。但NOT NULL必须明确指定。

Microsoft SQL Server中可以使用 IDENTITY类型，代码如下：

Oracle 没有提供直接的方法来创建自动增加的列。但却可以使用 SEQUENCE 对象和一个触发器来实现类似的效果。相关内容将在第22章介绍。

下面，我们可以向新创建的表中插入记录，而不用为自动增加的列指定值：

四、修改列

在修改现有表里的列时，需要考虑很多因素。下面是修改列的一些通用规则：

列的长度可以增加到特定数据类型所允许的最大长度；

如果想缩短某列的长度，则必须要求这一列在表里所有数据的长度都小于或等于新长度；

数值数据的位数可以增加；

如果要缩短数值数据的位数，则必须要求这一列在表里所有数值的位数小于或等于新指定的位数；

数值里的小数码数可以增加或减少；

列的数据类型一般是可以改变的。

有些实现会限制用户使用ALTER TABLE的某些选项。举例来说，可能不允许从表里撤销列。为了绕过这种限制，我们可以撤销整个表，然后重建新的表。如果某一列是依赖于其他表的列，或是被其他表的列所引用，在撤销这一列时就可能发生问题。详细情况请查看具体实现的文档。

注意：创建练习表

在本章后面的练习里会创建这些表。在第5章里会向这些表填充数据。

3.3.6 从现有表新建另一个表

警告：修改或删除表时务必小心

在修改或删除表时一定要小心。如果在发布这些命令时出现逻辑或输入错误，就可能导致丢失重要数据。

利用CREATE TABLE语句与SELECT语句的组合可以复制现有的表。新表具有同样的列定义，我们可以选择任何列或全部列。由函数或多列组合创建出来的列会自动保持数据所需的大小。从另一个表创建新表的基本语法如下所示：

注意其中的一些新关键字，特别是SELECT。SELECT是数据库查询语句，将在第7章详细介绍。现在需要掌握的就是我们可以利用查询的结果创建一个表。

MySQL和Oracle都支持使用CREATE TABLE AS SELECT方法，在一个表的基础上创建另一个表。但是Microsoft SQL Server却不一样，它使用SELECT…INTO方法来实现相同的效果。示例如下所示：

下面有一些示例使用了这种方法。

首先，我们进行一个简单的查询来了解表PRODUCTS_TBL里的内容。

接下来，基于前面这个查询创建名为PRODUCTS_TMP的表：

在SQL Server中，需要使用如下命令：

现在，如果对表PRODUCTS_TMP进行查询，得到的数据与原始表是一样的。

注意：“*”的意义

SELECT *会选择指定表里全部字段的数据。“*”表示表里的一行完整数据，也就是一条完整记录。

注意：默认使用相同的STORAGE属性

从现有表创建新表，新表与原始表具有一样的属性。

3.3.7 删除表

删除表是一种相当简单的操作。如果使用了RESTRICT选项，并且表被视图或约束所引用，DROP语句就会返回一个错误。当使用了CASCADE选项时，删除操作会成功执行，而且全部引用视图和约束都被删除。删除表的语法如下所示：

在SQL Server中，不能使用CASCADE选项。因此，要在SQL Server中删除表，必须同时删除与该表有引用关系的所有对象，以避免系统中遗留无效对象。

下面这个范例删除刚才创建的表：

警告：删除表的操作务必指向准确

在删除表时，在提交命令之前要确保指定了表的规划名或所有者，否则可能误删除其他的表。如果使用多用户账户，在删除表之前一定要确定使用了适当的用户名连接数据库。

3.4 完整性约束

完整性约束用于确定关系型数据库里数据的准确性和一致性。在关系型数据库里，数据完整性是通过引用完整性的概念实现的，而在引用完整性里包含了很多类型。

3.4.1 主键约束

主键是表里一个或多个用于实现记录唯一性的字段。虽然主键通常是由一个字段构成的，但也可以由多个字段组成。举例来说，雇员的社会保险号码或雇员被分配的标识号码都可以在雇员表里作为主键。主键的作用在于表里每条记录都具有唯一的值。由于在雇员表里一般不会出现用多条记录表示一个雇员的情况，所以雇员的标识号码可以作为主键。主键是在创建表时指定的。

下面的范例把字段EMP_ID指定为表EMPLOYEES_TBL的主键（PRIMARY KEY）：

这种定义主键的方法是在创建表的过程中完成的，这时主键是个隐含约束。我们还可以在创建表时明确地指定主键作为一个约束，如下所示：

在这个范例里，主键约束是在CREATE TABLE语句里的字段列表之后定义的。

包含多个字段的主键可以用如下两种方法之一来定义，以下示例适用于Oracle数据库：

3.4.2 唯一性约束

唯一性约束要求表里某个字段的值在每条记录里都是唯一的，这一点与主键类似。即使我们对一个字段设置了主键约束，也可以对另一个字段设置唯一性约束，尽管它不会被当作主键使用。

研究下面这个范例：

在这个范例里，主键是EMP_ID字段，表示雇员标识号码，用于确保表里的每条记录都是唯一的。主键通常是在查询里引用的字段，特别是用于结合表时。字段EMP_PHONE也会定义为UNIQUE，表示任意两个雇员都不能有相同的电话号码。这两个都具有唯一性的字段之间没有太多的区别，只是主键让表具有了一定的秩序，并且可以用于结合相互关联的表。

3.4.3 外键约束

外键是子表里的一个字段，引用父表里的主键。外键约束是确保表与表之间引用完整性的主要机制。一个被定义为外键的字段用于引用另一个表里的主键。

研究下面范例里外键的创建：

在这个范例里，EMP_ID 字段被定义为表 EMPLOYEE_PAY_TBL 的外键，它引用了表EMPLOYEE_TBL 里的 EMP_ID 字段。这个外键确保了表 EMPLOYEE_PAY_TBL 里的每个EMP_ID 都在表 EMPLOYEE_TBL 里有对应的 EMP_ID。这被称为父/子关系，其中父表是EMPLOYEE_TBL，子表是EMPLOYEE_PAY_TBL。请观察表3.4来更好地理解父子表的关系。

图3.4 父/子表关系

在这个图里，子表里的EMP_ID字段引用父表里的EMP_ID字段。为了在子表里插入一个EMP_ID的值，它首先要存在于父表的EMP_ID里。类似地，父表里删除一个EMP_ID的值，子表里相应的EMP_ID值必须全部被删除。这就是引用完整性的概念。

利用ALTER TABLE命令可以向表里添加外键，比如下面这个范例：

注意：ALTER TABLE命令在不同的SQL实现中有所不同

ALTER TABLE命令的选项在不同SQL实现里是不同的，特别是关于约束的选项。另外，约束的实际使用与定义也有所不同，但引用完整性的概念在任何关系型数据库里都是一样的。

3.4.4 NOT NULL约束

前面的范例在每个字段的数据类型之后使用了关键字NULL和NOT NULL。NOT NULL也是一个可以用于字段的约束，它不允许字段包含NULL值；换句话说，定义为NOT NULL的字段在每条记录里都必须有值。在没有指定NOT NULL时，字段默认为NULL，也就是可以是NULL值。

3.4.5 检查约束

检查（CHK）约束用于检查输入到特定字段的数据的有效性，可以提供后端的数据库编辑，虽然编辑通常是在前端程序里完成的。一般情况下，编辑功能限制了能够输入到字段或对象的值，无论这个功能是在数据库还是在前端程序里实现的。检查约束为数据提供了另一层保护。

下面的范例展示了在Oracle中，检查约束的使用：

表里的 EMP_ZIP 字段设置了检查约束，确保了输入到这个表里的全部雇员的 ZIP 代码都是“46234”。虽然这显得有些过于严格，但这不要紧，足以展示如何使用检查约束了。

如果想利用检查约束来确保ZIP代码属于某个值列表，可以像下面这样使用检查约束：

如果想指定雇员的最低小时工资，可以像下面这样设置约束：

在这个范例里，表里的任何雇员的小时工资都不能低于$12.50。在检查约束里可以使用几乎任何条件，就像在SQL查询里一样。第5章和第7章将更详细地介绍这些条件。

3.4.6 去除约束

利用ALTER TABLE命令的DROP CONSTRAINT选项可以去除已经定义的约束。举例来说，如果想去除表EMPLOYEES里的主键约束，可以使用下面的命令：

有些SQL实现还提供了去除特定约束的快捷方式。举例来说，在MySQL里可以使用下面这样的命令来去除主键约束：

注意：有些实现允许中止约束，这样我们可以选择暂时中止它，而不是从数据库里去除它，稍后还可以再启动它。

3.5 小结

本章概述了数据库对象的基本知识，主要介绍了表。表是关系型数据库里最简单的数据存储方式，它包含成组的逻辑信息，比如雇员、顾客或产品信息。表由各种字段组成，每个字段都有自己的属性，主要包括数据类型和约束，比如NOT NULL、主键、外键和唯一值。

本章介绍了 CREATE TABLE命令和选项，比如存储参数。还介绍了如何使用 ALTER TABLE命令调整已有表的结构。虽然管理数据库表的过程并不是SQL里最基本的过程，但如果首先学习了表的结构与本质，我们就能更容易地掌握通过数据操作或数据库查询来访问表的概念。下一章将介绍SQL对其他对象的管理，比如表的索引和视图。

3.6 问与答

问：在创建表的过程中给表命名时，一定要使用像_TBL这样的后缀吗？

答：当然不是。没有规定必须使用。举例来说，保存雇员信息的表可以具有下面这些名称，或是任何能够说明表里保存了何种数据的名称：

问：在删除表时，为什么使用规划名称是非常重要的？

答：有一个 DBA 新手删除表的真实故事：一个程序员在他的规划下创建了一个表，其名称与一个产品表是一样的。这名程序员后来离开了公司，他在数据库里的账户也要被删除，但DROP USER命令报告出错，因为还有他拥有的对象没有被删除。在经过一些调查之后，这名程序员创建的表被认定是没有用的，于是就要使用DROP TABLE命令了。

问题在于当执行DROP TABLE命令时，DBA以产品规划登录到数据库。这名DBA在删除表时，应该指定规划名称或所有者，但是他没有，结果是删除了另一个规划里不该删除的表。而恢复这个表花掉了大约8个小时。

3.7 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习是为了把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

3.7.1 测验

1．下面这个CREATE TABLE命令能够正常执行吗？需要做什么修改？在不同的数据库（MySQL、Oracle、SQL Server）中执行，有什么限制吗？

2．能从表里删除一个字段吗？

3．在前面的表EMPLOYEE_TBL里创建一个主键约束应该使用什么语句？

4．为了让前面的表EMPLOYEE_TBL里的MIDDLE_NAME字段可以接受NULL值，应该使用什么语句？

5．为了让前面的表 EMPLOYEE_TBL 里添加的人员记录只能位于纽约州(‘NY’)，应该使用什么语句？

6．要在前面的表EMPLOYEE_TBL里添加一个名为EMPID的自动增量字段，应该使用什么语句，才能同时符合MySQL和SQL Server的语法结构？

3.7.2 练习

通过下面的练习，读者将创建出数据库中所有的表，以便为后续章节提供练习环境。此外，还需要执行一些命令，来检查表结构。为了确保准确无误，我们分别介绍三种数据库实现（MySQL、Microsoft SQL Server、Oracle）的操作方法，这些方法在具体的实现上会有微小的差异。

Mysql

打开命令行窗口，使用下面的命令语法登录到本地的 MySQL，用实际的用户名替换username，用实际的密码替换password。注意在-p与密码之间没有空格。

在mysql>提示符下，输入以下命令，告诉MySQL我们要使用哪个数据库：

现在转到附录 D 来了解本书中所使用的 DDL。在 mysql>提示符下输入每个 CREATE TABLE命令，注意要包含每个命令之后的分号。这些命令创建的表将用于整本书的学习。

在mysql>提示符下，输入以下命令来列出所有的表：

在mysql>提示符下，使用DESCRIBE命令（缩写为desc）列出每个表的全部字段和它们的属性，如下所示：

如果遇到任何错误提示或输入错误，只需要重新创建相应的表即可。如果表成功创建了，但有输入错误（比如没有正确地定义字段，或是漏掉了某个字段），就删除表，再使用CREATE TABLE命令。DROP TABLE命令的语法如下所示：

Microsoft SQL Server

打开命令行窗口，使用下面的命令语法登录到本地的SQL Server，用实际的用户名替换username，用实际的密码替换password。注意在-p与密码之间没有空格。

在1>提示符下，输入以下命令，告诉SQL Server我们要使用哪个数据库。在使用SQLCMD时，需要用关键字GO来执行输入的命令。

现在转到附录D来了解本书中所使用的DDL。在 1>提示符下输入每个CREATE TABLE命令，注意要包含每个命令之后的分号，最后还要使用关键字GO来执行命令。这些命令创建的表将用于整本书的学习。

在1>提示符下，输入以下命令来列出所有的表。在命令后面加上关键字GO来执行：

在1>提示符下，使用存储过程sp_help列出每个表的全部字段和它们的属性，如下所示：

如果遇到任何错误提示或输入错误，只需要重新创建相应的表即可。如果表成功创建了，但有输入错误（比如没有正确地定义字段，或是漏掉了某个字段），就删除表，再使用CREATE TABLE命令。DROP TABLE命令的语法如下所示：

Oracle

打开命令行窗口，使用下面的命令语法登录到本地的Oracle。输入用户名和密码。

现在转到附录 D 来了解本书中所使用的 DDL。在 SQL>提示符下输入每个 CREATE TABLE命令，注意要包含每个命令之后的分号。这些命令创建的表将用于整本书的学习。

在SQL>提示符下，输入以下命令来列出所有的表：

在SQL>提示符下，使用DESCRIBE命令（缩写为desc）列出每个表的全部字段和它们的属性，如下所示：

如果遇到任何错误提示或输入错误，只需要重新创建相应的表即可。如果表成功创建了，但有输入错误（比如没有正确地定义字段，或是漏掉了某个字段），就删除表，再使用CREATE TABLE命令。DROP TABLE命令的语法如下所示：

第4章 规格化过程

本章的重点包括：

什么是规格化

规格化的优点

去规格化的优点

规格化技术

规格化的方针

数据库设计

本章介绍把原始数据库分解为表的过程，这被称为规格化。数据库开发人员利用规格化过程来设计数据库，使其更便于组织和管理，同时确保数据在整个数据库里的正确性。这一过程在各种RDBMS中都是一样的。

本章会介绍规格化与去规格化的优缺点，以及规格化带来的数据完整性与性能之间的矛盾。

4.1 规格化数据库

规格化是去除数据库里冗余数据的过程，在设计和重新设计数据库时使用。它是一组减少数据冗余来优化数据库的指导方针，具体的方针被称为规格形式，稍后将详细介绍。在本书中是否应该包含介绍规格化的内容是个两难的决定，因为其规则对于 SQL 初学者来说过于复杂了。然而，规格化是个十分重要的过程，对它的理解会加深我们对 SQL 的掌握。本章尽量简化对规格化的介绍，不会过于关注规格化的细节，而且着重于让读者理解其基本概念。

4.1.1 原始数据库

在没有经过规格化的数据库里，有些数据可能会出现在多个不同的表里，而且没有什么明显的原因。这样对安全、磁盘利用、查询速度、数据库更新都不好，特别是可能产生数据完整性的问题。在规格化之前，数据库里的数据并没有从逻辑上被分解到较小的、更易于管理的表里，图4.1展示了本书所使用的数据库在规格化之前的状态。

图4.1 原始数据库

在数据库逻辑设计过程中，确定原始数据库里的信息由什么组成是第一个也是最重要的步骤，我们必须了解组成数据库的全部数据元素，才能有效地使用规格化技术。只有用必要的时间收集所需的数据集，才能避免因为丢失数据元素而重新设计数据库。

4.1.2 数据库逻辑设计

任何数据库设计都要考虑到终端用户。数据库逻辑设计，也被称为逻辑建模，是把数据安排到逻辑的、有组织的对象组，以便于维护的过程。数据库的逻辑设计应该减少数据重复，甚至是完全消除这种现象。毕竟，为什么要把数据存储两遍呢？另外，数据库逻辑设计应该努力让数据库易于维护和更新，同时也要保持数据库里的命名规范与逻辑。

一、什么是终端用户的需求

在设计数据库时，终端用户的需求应该是最重要的考虑因素。记住，终端用户是最终使用数据库的人。利用用户的前端工具（允许用户访问数据库的客户程序），数据库的使用应该是相当简单的，但是在设计数据库时如果没有考虑到用户的需求，这也许就不能达到。性能优化也是如此。

在设计时要考虑的与用户相关的因素包括：

数据库里应该保存什么数据？

用户如何访问数据库？

用户需要什么权限？

数据库里的数据如何分组？

哪些数据最经常被访问？

全部数据与数据库如何关联？

采取什么措施保证数据的正确性？

采取什么措施减少数据冗余？

采取什么措施让负责维护数据的用户更易于使用数据库？

二、数据冗余

数据应该没有冗余；这意味着重复的数据应该保持到最少，其原因有很多。举例来说，把雇员的家庭住址保存到多个表里就没有意义。重复数据会占据额外的存储空间，而且经常会产生混乱，比如如果雇员的地址在一个表里的内容与在另一个表里的内容不相符时，哪一个是正确的呢？能不能找到文档资料来确定雇员当前的地址？数据管理已经很困难了，冗余数据会导致灾难。减少冗余数据还能简化数据库的更新操作。如果只有一个表保存了雇员的地址，那么在用新地址更新了这个表之后，我们就可以确保所有人都会看到这个更新的数据。

4.1.3 规格形式

这一章讨论规格形式，这是数据库规格化过程中必不可少的一个概念。

规格形式是衡量数据库被规格化级别（或深度）的一种方式。数据库的规格化级别是由规格形式决定的。

下面是规格化过程中最常见的3种规格形式：

第一规格形式；

第二规格形式；

第三规格形式。

除此之外，还有其他规格形式，但都不常用。在这3种主要的规格形式中，每一种都依赖于前一种形式所采用的规格化步骤。举例来说，如果想以第二规格形式对数据库进行规格化，数据库必须处于第一种规格形式。

一、第一规格形式

第一规格形式的目标是把原始数据分解到表中。在所有表都设计完成之后，给大多数表或全部表设置一个主键。从第3章中可以知道，主键必须是个唯一的值，所以在选择主键时应该尽量选择能够从本质上唯一区别数据的元素。图4.2展示了图4.1所示原始数据库使用第一规格形式重新设计之后的情况。

从图中可以看出，为了达到第一规格形式，数据被分解为包含相关信息的逻辑单元，每个逻辑单元都有一个主键，而且任何表里都没有重复的数据组。现在的数据库不再是一个大表，而是被分解为较小的、更易于管理的表：EMPLOYEE_TBL、CUSTOMER_TBL 和PRODUCTS_TBL。主键通常是表里的第一列，本例中分别是EMP_ID、CUST_ID和PROD_ID。这种命名方式是在设计数据库时常用的规范，确保了各种名称的可读性。

主键也可以由表中的多个列构成。这类主键所涉及的数据通常不是数据库自动生成的数字，而是有逻辑意义的数据，例如生产商的名称或者一本书的 ISBN 编号。这类数据被称为自然主键，即使不在数据库中，也可以通过它们来区分不同的对象。在为表选择主键的时候，需要注意的一点就是，主键必须能够唯一地定义表中的一条记录。否则，查询的结果可能会返回重复的记录，而且也无法通过主键来删除一条特定的记录。

图4.2 第一规格形式

二、第二规格形式

第二规格形式的目标是提取对主键仅有部分依赖的数据，把它们保存到另一个表里。图4.3展示了第二规格形式。

图4.3 第二规格形式

从图中可以看出，第二规格形式以第一规格形式为基础，把两个表进一步划分为更明确的单元。

EMPLOYEE_TBL被分解为两个表，分别是EMPLOYEE_TBL和EMPLOYEE_PAY_TBL。雇员个人信息是依赖于主键（EMP_ID）的，保留在EMPLOYEE_TBL表里的都是如此（EMP_ID、LAST_NAME、FIRST_NAME、MIDDLE_NAME、ADDRESS、CITY、STATE、ZIP、PHONE和PAGER）。而在另一方面，与EMP_ID仅部分依赖的信息被转移到EMPLOYEE_PAY_TBL（包括EMP_ID、POSITION、POSITION_DESC、DATE_HIRE、PAY_RATE和DATE_LAST_RAISE）。注意到两个表都包含列EMP_ID，这是每个表的主键，用于在两个表之间匹配对应的数据。

CUSTOMER_TBL被分解为两个表，分别是CUSTOMER_TBL和ORDERS_TBL，具体情况类似于EMPLOYEE_TBL，仅部分依赖于主键的列被转移到另一个表。顾客的订单信息依赖于每一个CUST_ID，但与顾客的一般信息没有直接依赖关系。

三、第三规格形式

第三规格形式的目标是删除表里不依赖于主键的数据。图4.4展示了第三规格形式。

图4.4 第三规格形式

这里又创建了一个新表来实现第三规格形式。EMPLOYEE_PAY_TBL被分解为两个表：一个表保存雇员的实际支付信息，另一个表保存职位描述。这的确不需要保存在EMPLOYEE_PAY_TBL里，列POSITION_DESC与主键EMP_ID完全不相关。从上述介绍可以看出，规格化过程就是采取一系列步骤，把原始数据分解为由关联数据形成的多个表。

4.1.4 命名规范

命名规范是在数据库规格化过程中最重要的考虑因素之一。名称是我们引用数据库对象的方式。表的名称应该能够描述所保存信息的类型，以便于我们找到需要的数据。对于没有参加数据库设计而需要查询数据库的用户来说，具有描述性的名称更为重要。

应该在公司范围内统一命名规范，不仅是数据库里表的命名，而是用户、文件和其他相关对象的命名都应该遵守。命名规范还让我们更容易判断表的用途和数据库系统里文件的位置，从而有助于数据库管理。设计和坚持命名规范是公司开发成功数据库实现的第一步。

4.1.5 规格化的优点

规格化为数据库带来了很多好处，主要包括以下几点：

更好的数据库整体组织性；

减少冗余数据；

数据库内部的数据一致性；

更灵活的数据库设计；

更好地处理数据库安全；

加强引用整体性的概念。

组织性是由规格化过程所产生的，让从访问数据库的用户到负责管理数据库所有对象的管理员（DBA）的所有人都感到更轻松。数据冗余被减少了，从而简化了数据结构，节约了磁盘空间。由于重复数据被尽量减少了，所以数据不一致的可能大大降低。举例来说，某人在一个表的姓名可能是STEVE SMITH，而在另一个表里是STEPHEN R. SMITH。减少重复数据提高了数据完整性，或者说数据库里数据的一致性和准确性。数据库规格化之后，分解为较小的表，便于我们更灵活地修改现有的结构。显然，修改包含较少数据的小表，要比修改包含数据库全部重要数据的一个大表要轻松得多。最后，DBA能够控制特定用户对特定表的访问，从而提高了安全性。在进行了规格化之后，安全就更容易控制了。

引用完整性表示一个表里某列的值依赖于另一个表里某列的值。举例来说，如果某个顾客要在表ORDERS_TBL里有一条记录，则必须首先在表CUSTOMER_TBL里有一条记录。完整性约束还可以限制列的取值范围，它应该在创建表时设置。引用完整性一般是通过使用主键和外键来控制的。

在一个表里，外键（通常是一个字段）直接引用另一个表里的主键来实现引用完整性。在前一个图里，表ORDERS_TBL里的CUST_ID就是一个外键，它引用表CUSTOMER_TBL里的CUST_ID。规格化过程把数据从逻辑上分解为由主键引用的子集，从而有助于加强和坚持这些约束。

4.1.6 规格化的缺点

虽然大多数成功的数据库都在一定程度上进行了规格化，但规格化的确有一个不可回避的缺点：降低数据库性能。性能降低的程度取决于查询或事务被提交给数据库的时机，其中涉及多个因素，比如 CPU 使用率、内存使用率和输入/输出（I/O）。简单来说，规格化的数据库比非规格化的数据库需要更多的CPU、内存和I/O来处理事务和查询。规格化的数据库必须找到所需的表，然后把这些表的数据结合起来，从而得到需要的信息或处理相应的数据。关于数据库性能的更详细讨论请见第18章。

4.2 去规格化数据库

去规格化是修改规格化数据库的表的构成，在可控制的数据冗余范围内提高数据库性能。尝试提高性能是进行去规格化数据库的唯一原因。去规格化的数据库与没有进行规格化的数据库不一样，去规格化是在数据库规格化基础上进行一些调整，因为规格化的数据库需要频繁地进行表的结合而降低了性能（关于表的结合请见第13章）。去规格化会把一些独立的表合成在一起，或是创建重复的数据，从而减少在数据检索时需要结合的表的数量，进而减少所需的I/O和CPU时间。这在较大的数据仓库程序中会有明显的好处，其中的计算可能会涉及表里数以百万行的数据。

去规格化也是有代价的。它增加了数据冗余，虽然提高了性能，但需要付出更多的精力来处理相关的数据。程序代码会更加复杂，因为数据被分散到多个表，而且可能更难于定位。另外，引用完整性更加琐碎，因为相关数据存在于多个表里。规格化与去规格化都有好处，但都需要我们对实际的数据和公司的详细业务需求有全面的了解。在确定要着手进行去规格化时，一定要仔细记录所采取的过程，以便于更好地处理像数据冗余这样的问题，维护系统内部的数据完整性。

4.3 小结

在进行数据库设计时，必须做出一个困难的决定——规格化或去规格化，这的确是个问题。一般来说，数据库问题需要进行一定程度的规格化，但到什么程度才不至于严重影响性能呢？答案取决于程序本身。数据库有多大？其用途是什么？什么样的用户要访问数据？本章介绍了3种最常见的规格形式、规格化过程的底层概念、数据的完整性。规格化过程包含多个步骤，大多数都不是必需的，但对于数据库的功能和性能来说都是很重要的。无论决定进行什么程度的规格化，总是会存在便于维护与性能降低，或复杂维护与更好性能之间的平衡。最终，设计数据库的个人（或团队）必须做出决定，并对此负责。

4.4 问与答

问：在设计数据库时为什么要考虑最终用户的需求？

答：最终用户才是真正使用数据库的人，从这种角度来说，他们应该是任何数据库设计的中心。数据库设计人员只不过是帮助组织数据而已。

问：规格化要比去规格化好吗？

答：可能是这样的，但是到达一定程度时，去规格化可能会更好，这其中受到很多因素的影响。我们会对数据库进行规格化来减少其中的重复数据，到达一定程度之后可能又会转回头来，通过去规格化来改善性能。

4.5 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习是为了把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

4.5.1 测验

1．判断正误：规格化是把数据划分为逻辑相关组的过程。

2．判断正误：让数据库里没有重复或冗余数据，让数据库里所有内容都规格化，总是最好的方式。

3．判断正误：如果数据是第三规格形式，它会自动属于第一和第二规格形式。

4．与规格化数据库相比，去规格化数据库的主要优点是什么？

5．去规格化的主要缺点是什么？

6．在对数据库进行规格化时，如何决定数据是否需要转移到单独的表？

7．对数据库设计进行过度规格化的缺点是什么？

4.5.2 练习

1．为一家小公司开发一个新数据库，使用如下数据，对其进行规格化。记住，即使是一家小公司，其数据库的复杂程度也会超过这里给出的范例。

雇员：

顾客：

顾客订单：

2．像第 3 章介绍的那样登录到你新建的数据库。可以输入以下命令来确保使用的是learnsql数据库：

在Oracle中，这条命令意味着进入规划。默认情况下，用户在自己的规划中创建对象。

进入数据库后，根据练习 1中定义的信息，用CREATE TABLE命令创建相应的表。

第5章 操作数据

本章的重点包括：

数据操作语言概述

介绍如何操作表里的数据

数据填充背后的概念

如何从表里删除数据

如何修改表里的数据

本章介绍SQL里的数据操作语言（DML），它用于修改关系型数据库里的数据和表。

5.1 数据操作概述

数据操作语言使数据库用户能够对关系型数据库里的数据进行修改，包括用新数据填充表、更新现有表里的数据、删除表里的数据。利用DML命令还可以进行简单的数据库查询。

SQL里3个基本的DML命令是：

INSERT；

UPDATE；

DELETE。

SELECT命令可以与DML命令配合使用，将在第7章详细介绍。SELECT命令是基本的查询命令，在 INSERT 命令把数据输入到数据库之后使用。所以在本章中，我们会向表中插入数据，以便能够更好地使用SELECT命令。

5.2 用新数据填充表

用数据填充表就是把新数据输入到表的过程，无论是使用单个命令的手工过程，还是使用程序或其他相关软件的批处理过程。手工数据填充是指通过键盘输入数据，自动填充通常是从外部数据源（比如其他数据库或一个平面文件）获得数据，再把得到的数据加载到数据库。

在用数据填充表时，有很多因素会影响什么数据以及多少数据可以输入到表里。主要因素包括现有的表约束、表的物理尺寸、列的数据类型、列的长度和其他完整性约束（比如主键和外键）。下面将介绍向表输入新数据的基本知识，并且说明什么是可以做的，而什么是不能做的。

5.2.1 把数据插入到表

INSERT语句可以把数据插入到表，它具有一些选项，其基本语法如下所示：

注意：数据是区分大小写的

不要忘了SQL语句无所谓是大写的或小写的，而数据永远都是区分大小写的。举例来说，如果数据以大写方式输入到数据库，它就必须以大写方式被引用。这些范例使用了大写和小写字母，只是为了展示这样做并不影响结果。

在使用这种语法时，必须在VALUES列表里包含表里的每个列。在这个列表里，每个值之间是以逗号分隔的。字符、日期和时间数据类型的值必须以单引号包围，而数值或 NULL值就不必了。表里的每一列都应该有值，并且值的顺序与列在表里的次序一致。在后续章节中，我们会介绍如何指定列的顺序。但是现阶段，读者只需要知道SQL会默认用户在插入数据的时候，使用的是与创建列时相同的顺序。

下面的范例将把一条新记录插入到表PRODUCTS_TBL里。

表的结构如下所示：

下面是插入语句的范例：

在这个范例里，3个值被插入到一个具有3列的表里，值的顺序与列在表里的次序一致。前两个值使用了单引号包围，因为与之对应的列的数据类型为字符型。第 3 个值对应的列COST 是数值型的，不需要使用单引号；当然，也可以使用单引号，而且不会对结果产生影响。

注意：引号的使用

数值型数据不必使用单引号，但其他数据类型都需要使用。换句话说，单引号对于数据库里的数值型数据来说是可选的，而对于其他数据类型来说是必需的。作为一种习惯，大多数 SQL 用户对数值型数据不使用单引号，这样可以提高查询命令的可读性。

5.2.2 给表里指定列插入数据

有一种方法可以把数据插入到指定的列。举例来说，我们想插入除寻呼机号码之外的所有与雇员相关的数据，这时就必须在INSERT命令指定字段列表与值列表，如下所示：

给表中特定列插入数据的语法如下所示：

在下面的范例里，我们向表ORDER_TBL里的某些列插入数据。

表的结构如下所示：

INSERT的范例语句如下：

在INSERT语句里的表名称之后，我们在一对圆括号里指定了要插入数据的字段列表，其中只是没有包含ORD_DATE。通过查看表定义可以看出，表里每条记录的ORD_DATE字段都需要有值，这是因为它没有被设置为NOT NULL，说明它可以是空的。注意，插入值的次序要与字段列表的次序相同。

注意：字段列表次序可以有差异

INSERT语句里的字段列表次序并不一定要与表定义中的字段次序相同，但插入值的次序要与字段列表的次序相同。除此之外，可以不用为列指定NULL，因为大部分RDBMS在默认情况下，允许列中出现NULL值。

5.2.3 从另一个表插入数据

利用INSERT语句和SELECT语句的组合，我们可以根据对另一个表的查询结果把数据插入到表里。简单来说，查询是对数据库的一个质询，希望返回或不返回某些数据。关于查询的详细介绍请见第7章。查询就是用户向数据库提出的一个问题，而返回的数据就是答案。通过组合使用INSERT和SELECT语句，我们可以把从一个表的查询结果插入到另一个表里。

从另一个表插入数据的语法如下所示：

这里有3个新的关键字，分别是SELECT、FROM和WHERE，在此做一简要介绍。SELECT是SQL里执行查询的主要命令；FROM是查询中的一个子句，用于指定要进行查询的表的名称；WHERE 子句也是查询的一部分，用于设置查询的条件。条件用于设置一个标准，从而决定哪些数据会受到影响，比如：WHERE NAME = 'SMITH'。这 3个关键字将在第 7章和第8章详细介绍。

下面的范例使用一个简单的查询来查看表 PRODUCTS_TBL里的全部数据。SELECT *告诉数据库服务程序返回表里所有字段的数据。在此没有使用WHERE子句，所以会得到表里的全部记录。

现在基于上述查询向表PRODUCTS_TMP里插入数据，可以看到这个表里创建了11条记录。

在采用这种语法时，必须确保查询返回的字段与表里的字段或INSERT语句里指定的字段列表具有相同的次序。另外，还要确定SELECT语句返回的数据与要插入数据的表的字段具有兼容的数据类型。举例来说，如果想把一个值为'ABC'的 VARCHAR 字段插入到一个数值字段，就会导致语句失败。

下面的查询显示出表PRODUCTS_TMP里刚刚插入的数据：

5.2.4 插入NULL值

向表里的字段插入NULL值是相当简单的。当某一列的值不能确定时，我们可能需要向它插入一个NULL值。举例来说，不是每个人都有寻呼机，输入一个错误寻呼号码是不正确的，更何况这样会浪费存储空间。利用关键字NULL可以在列里插入NULL值。

插入NULL值的语法如下所示：

关键字NULL应该位于正确的次序上，相应的字段会没有值的。在上面这个范例里，第二列的值会是NULL。

看一看下面这个范例：

在这个范例里，所有要插入数据的字段都列出来了，这也恰好是表ORDERS_TBL里的全部字段。在此，NULL 值被插入到 ORD_DATE 字段，表示或者不知道订购日期，或者目前还没有被订购。再来看一个范例：

这条语句与前一条语句有两处不同，但结果是一样的。首先，这里没有字段列表。在向全部字段插入数据时，不必使用字段列表。其次，没有向ORD_DATE字段插入NULL值，而是根本没有给它赋值，这意味着应该添加一个NULL值。记住，NULL值表示字段没有值，与空字符串是不同的。

最后，考虑这样一种情况，PRODUCTS_TBL表中保存有NULL值，而用户需要将该表中的值插入PRODUCTS_TMP表中，范例如下：

在这个范例中，如果数据即将插入的列允许接受NULL值，那么NULL值就可以直接插入该列。在后续章节中，我们会介绍如何为列指定默认值，这样，如果有NULL值被插入，就可以被自动转换成默认值。

5.3 更新现有数据

利用UPDATE命令可以修改表里的现有数据。这个命令不向表里添加新记录，也不删除记录，它只是修改现有的数据。它一般每次只更新数据库里的一个表，但可以同时更新表里的多个字段。根据需要，我们可以只更新表里的一行数据，也可以用一条语句就更新很多行数据。

5.3.1 更新一列的数据

UPDATE语句最简单的形式是用于更新表里的一列数据。在更新一列数据时，被更新的记录可以是一条，也可以是很多条。

更新一列的语法如下所示：

下面的范例把表ORDERS_TBL里ORD_NUM值为'23A16'的记录（用WHERE子句指定）的QTY字段更新为1。

下面这个范例与前面相同，只是没有了WHERE子句：

注意到在这个范例里，11 条记录被更新了。这个语句把字段 QTY 设置为 1，更新了表ORDERS_TBL 里的全部记录。这是我们想要的结果吗？有时是的，但一般我们很少使用没有WHERE子句的UPDATE语句。检查目标数据集是否正确的一种简单方式是对同一个表使用SELECT语句，其中包含要在UPDATE语句里使用的WHERE子句，判断返回的结果是否是我们要更新的记录。

警告：小心使用UPDATE和DELETE命令

在使用没有 WHERE 子句的 UPDATE 命令时要特别小心。如果没有使用WHERE子句设置条件，表里所有记录的相应字段都会被更新。在大多数情况下，DML命令都需要使用WHERE子句。

5.3.2 更新一条或多记录里的多个字段

下面来介绍如何使用一条UPDATE语句更新多个字段，其语法如下所示：

注意其中使用的SET——这里只有一个SET，但是有多个列，每个列之间以逗号分隔。可以看出SQL里的一种趋势：通常使用逗号来分隔不同类型的参数。下面的代码使用逗号分隔要更新的两列。同样，WHERE子句是可选的，但通常是必要的。

注意：如何使用SET关键字

在每个UPDATE语句里，关键字SET只能使用一次。如果需要一次更新多个字段，就要使用逗号来分隔这些字段。

本书的后续章节将介绍如何使用更复杂的命令，利用一个或多个外部表来更新当前表中的字段，这需要使用JOIN命令。

5.4 从表里删除数据

DELETE命令用于从表里删除整行数据。它不能删除某一列的数据，而是删除行里全部字段的数据。使用DELETE语句一定要谨慎，因为它一向很有效。