MySQL数据库技术（04)

现在我们已经具备了所需的所有基础知识；可以将MySQL 投入工作了！本节提供一个教程，帮助熟悉M y S Q L。在完成这个教程时，将创建一个样例数据库和这个数据库中的表，然后增加、检索、删除和修改信息与数据库进行交互。此外，在操作这个样例数据库的过程中，将能学到下列东西： ■ 如何利用mysql 客户机程序与MySQL 通信。 ■ SQL 语言的基本语句。（如果您曾经使用过其他R D B M S，从而熟悉S Q L，那么浏览一下这个教程，看看SQL 的MySQL 版与您熟悉的版本有何差别也是很好的。）正如上一节所述， MySQL 采用客户机/服务器体系结构，其中服务器运行在存放数据库的机器上，而客户机通过网络连接到服务器。这个教程主要基于mysql 客户机的应用。m y s q l读取您的SQL 查询，将它们发送给服务器，并显示结果。mysql 运行在MySQL 所支持的所有平台上，并提供与服务器交互的最直接的手段，因此，它首先是一个逻辑上的客户机。 在本书中，我们将用samp_db 作为样例数据库的名称。但是有可能在您完成本例子的过程中需要使用另一个数据库名。因为可能在您的系统上已经有某个人使用了samp_db 这个名称，或者管理员给您指定了另一个数据库名称。在后面的例子中，无论是哪种情况，都用数据库的实际名称代替s a m p _ d b。表名可以像例子所显示的那样精确地使用，即使系统中的多个人都具有他们自己的样例数据库也是如此。顺便说一下，在MySQL 中，如果有人使用了相同的表名也没什么关系。一旦各个用户都具有自己的数据库， MySQL 将一直保留这些数据库名，防止各用户互相干扰。 1.4.1 基本要求 为了试验这个教程中的例子，必须安装M y S Q L。特别是必须具有对MySQL 客户机和某个MySQL 服务器的访问权。相应的客户机程序必须位于您的机器上。至少需要有m y s q l程序，最好还有m y s q l i m p o r t程序。服务器也可以位于您的机器上，尽管这不是必须的。实际上，只要允许连接到服务器，那么服务器位于何位置都没有关系。若服务器正巧运行在您的机器上，适当的客户机程序又已经安装，那么就可以开始试验了。如果您尚需设法搞到M y S Q L，可参阅附录A“获得和安装软件”的说明。如果您正自己安装M y S Q L，可参阅这一章，或把它给管理员看。如果网络访问是通过一个因特网服务商（I S P）进行的，那么可查看该服务商是否拥有M y S Q L。如果该ISP 不提供MySQL 服务，可查看附录J“因特网服务商”以得到某些选择更适合的服务商的建议。 除M y S Q L软件外，还需要得到创建样例数据库及其表的权限。如果您没有这种权限，可以向MySQL 管理员咨询。管理员可通过运行mysql 并发布如下的命令提供这种权限: (点击查看原图) MySQL 与mysql 的区别 为了避免混淆，应该说明，“M y S Q L”指的是整个MySQL RDBMS，而“m y s q l”代表的是一个特定的客户机程序名。它们的发音都是相同的，但可通过不同的大小写字符和字体来区分。关于发音，MySQL 的发音为“m y - e s s - q u e u e - e l l”。我们知道这是因为MySQL 参考指南中是这样发音的。而SQL 的发音为“s e q u e l”或“e s s - q u e u e - e l l”。我不认为哪个发音更好一些。愿意读哪个音都可以，不过在您对别人读的时候，他可能会用他认为是“正确”的发音对您进行纠正。 前一个命令在paul 从l o c a l h o s t（服务器运行在正运行的同一主机）连接时，允许它完全访问samp_db 数据库及它的所有表。它还给出了一个口令s e c r e t。第二个命令与第一个类似，但允许paul 从任何主机上连接（“%”为通配符）。也可以用特定的主机名取代“%”，使p a u l只能从该主机上进行连接。（如果您的服务器允许从localhost 匿名访问，由于服务器搜索授权表查找输入连接匹配的方式的原因，这样一个GRANT 语句可能是必须的。）关于G R A N T语句以及设置MySQL 用户账号的更详细信息，可在第11 章“常规的MySQL 管理”找到。 1.4.2 取得样例数据库的分发包 这个教程在某些地方要涉及来自“样例数据库分发包”中的文件。有的文件含有帮助来设置样例数据库的查询或数据。为了得到这个分发包，可参阅附录A。在打开这个分发包时，将创建一个名为samp_db 的目录，此目录中含有所需的文件。无论您在哪个地方试验与样例数据库有关的例子，建议都移入该目录。 1.4.3 建立和中止与服务器的连接 为了连接到服务器，从外壳程序（即从UNIX 提示符，或从Windows 下的DOS 控制台）激活mysql 程序。命令如下： (点击查看原图) 其中的“%”在本书中代表外壳程序提示符。这是UNIX 标准提示符之一；另一个为“$”。在Windows 下，提示符类似“c : \ >”。mysql 命令行的options 部分可能是空的，但更可能的是发布一条类似如下的命令： (点击查看原图) 在激活mysql 时，有可能不必提供所有这些选项；确切使用的命令请咨询MySQL 管理员。此外，可能还需要至少指定一个名称和一个口令。 在刚开始学习MySQL 时，大概会为其安全系统而烦恼，因为它使您难于做自己想做的事。（您必须取得创建和访问数据库的权限，任何时候连接到数据库都必须给出自己的名字和口令。）但是，在您通过数据库录入和使用自己的记录后，看法就会马上改变了。这时您会很欣赏MySQL 阻止了其他人窥视（或者更恶劣一些，破坏！）您的资料。 下面介绍选项的含义： ■ - h h o s t _ n a m e（可选择形式：- - h o s t =h o s t _ n a m e） 希望连接的服务器主机。如果此服务器运行在与mysql 相同的机器上，这个选项一般可 省略。 ■ -u u s e r _ n a m e（可选择的形式：- - u s e r =u s e r _ n a m e）您的MySQL 用户名。如果使用UNIX 且您的MySQL 用户名与注册名相同，则可以省去这个选项；mysql 将使用您的注册名作为您的MySQL 名。在Windows 下，缺省的用户名为O D B C。这可能不一定非常有用。可在命令行上指定一个名字，也可以通过设置USER 变量在环境变量中设置一个缺省名。如用下列set 命令指定paul 的一个用户名： ■ - p（可选择的形式：- - p a s s w o r d） 这个选项告诉mysql 提示键入您的MySQL 口令。注意：可用-pyour_password 的形式（可选择的形式： - - p a s s w o r d = y o u r _ p a s s w o r d）在命令行上键入您的口令。但是，出于安全的考虑，最好不要这样做。选择-p 不跟口令告诉mysql 在启动时提示您键入口令。例如： 在看到Enter password: 时，键入口令即可。（口令不会显到屏幕，以免给别人看到。）请注意，MySQL 口令不一定必须与UNIX 或Windows 口令相同。如果完全省略了-p 选项，mysql 就认为您不需要口令，不作提示。请注意： -h 和-u 选项与跟在它们后面的词有关，无论选项和后跟的词之间是否有空格。而-p 却不是这样，如果在命令行上给出口令， -p 和口令之间一定不加空格。例如，假定我的MySQL 用户名和口令分别为paul 和s e c r e t，希望连接到在我注册的同一机器上运行的服务器上。下面的mysql 命令能完成这项工作： 在我键入命令后， mysql 显示Enter password: 提示键入口令。然后我键入口令（ * * * * * *表明我键入了s e c r e t）。如果一切顺利的话， mysql 显示一串消息和一个“ m y s q l >”提示，表示它正等待我发布查询。完整的启动序列如下所示： 为了连接到在其他某个机器上运行的服务器，需要用-h 指定主机名。如果该主机为pit -v i p e r. s n a k e . n e t，则相应的命令如下所示： 在后面的说明mysql 命令行的多数例子中，为简单起见，我们打算省去- h、-u 和-p 选项。并且假定您将会提供任何所需的选项。有很多设置账号的方法，从而不必在每次运行mysql 时都在连接参数中进行键入。这个问题在1 . 5节“与mysql 交互的技巧”中介绍。您可能会希望现在就跳到该节，以便找到一些更易于连接到服务器的办法。 在建立了服务器的一个连接后，可在任何时候键入下列命令来结束会话： 还可以键入Control-D 来退出，至少在UNIX 上可以这样。 1.4.4 发布查询 在连接到服务器后，就可以发布查询了。本节介绍有关与mysql 交互应该了解的一些知识。为了在mysql 中输入一个查询，只需键入它即可。在查询的结尾处，键入一个分号（“;”）并按Enter 键。分号告诉mysql 该查询是完整的。（如果您喜欢键入两个字符的话，也可以使用“\ g”终止查询。）在键入一个查询之后， mysql 将其发送到服务器上。该服务器处理此查询并将结果送回m y s q l，mysql 将此结果显示出来。下面是一个简单的查询例子和结果： 它给出当前的日期和时间。（NOW() 函数本身并无多大用处，但可将其用于表达式中。如比较当前日期和其他日期的差异。）mysql 还在结果中显示行数计数。本书在例子中一般不给出这个计数。因为mysql 需要见到分号才发送查询到服务器，所以在单一的行上不需要键入分号。如果有必要，可将一个查询分为几行，如下所示： 请注意，在键入查询的第一行后，提示符从‘m y s q l’ 变成了‘- >’；这表示mysql 允许继续键入这个查询。这是一个重要的提示，因为如果在查询的末尾忘记了分号，此提示将有助于提醒您查询尚不完整。否则您会一直等下去，心里纳闷为什么mysql 执行查询为什么这么长的时间还没完；而mysql 也搞不清为什么结束查询的键入要花您那么多的时间！ 大部分情况下，用大写字符、小写字符或大小写字符混合键入查询没什么关系。下列查询全是等价的： 本书中的例子用大写字符表示SQL 关键字和函数名，用小写字符表示数据库、表和列名。 　如果在查询中调用一个函数，在函数名和后跟的圆括号中间不允许有空格，例： 这两个查询看上去差别不大，但第二个失败了，因为圆括号并没有紧跟在函数名的后面。如果已经开始键入一个多行的查询，而又不想立即执行它，可键入‘\ c’ 来跳过（放弃）它，如： 请注意，提示符又变回了‘m y s q l >’，这表示mysql 为键入的新查询作好了准备。可将查询存储在一个文件中并告诉mysql 从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查 询，可如下执行这些查询： 可用这种办法调用任何所需的文件。这里用后缀为“ . s q l”来表示该文件含有SQL 语句。执行mysql 的这种方法将在输入数据到samp_db 数据库时的“增加新记录”中使用。为了装载一个表，让mysql 从某个文件中读取I N S E RT 语句比每次用手工键入这些语句更为方便。 本教程的其余部分向您提供了许多可以自己试试的查询。这些查询以‘m y s q l >’ 提示为前导后跟结束分号，这些例子通常都给出了查询输出结果。可以按给出的形式键入这些查询，所得到的结果应该与自学材料中的相同。给出的查询中无提示符的或无分号语句结束符的只是用来说明某个要点，不用执行它们。（如果愿意您可以试一下，但如果试的话，请记住给语句末尾加一个分号。）本书后面的章节中，我们一般不给出‘m y s q l >’ 提示或SQL 语句的分号。这样做的原因是为了可以在非mysql 客户机程序的语言环境（如在Perl 脚本中或PHP 脚本中）中发布查询，在这些语言环境中，既无提示符也不需要分号。在专门针对mysql 输入一个查询的场合会作出相应的说明。 1.4.5 创建数据库 现在开始创建samp_db 样例数据库及其表，填充这些表并对包含在这些表中的数据进行一些简单的查询。 使用数据库涉及几个步骤： 1) 创建（初始化）数据库。 2) 创建数据库中的表。 3) 对表进行数据插入、检索、修改或删除。 检索现有数据是对数据库执行的最简单且常见的操作。另外几个最简单且常见的操作是插入新数据、更新或删除现有数据。较少使用的操作是创建表的操作，而最不常用的操作是创建数据库。 我们将从头开始，先创建数据库，再插入数据，然后对数据进行检索。为了创建一个新的数据库，用mysql 连接到数据库然后发布C R E ATE DATABASE 语句，此语句指定了数据库名： 在创建表以及对这些表进行各种操作之前，必须先创建samp_db 数据库。创建数据库后，这个新创建的数据库并不是当前数据库。这可从执行下面的查询看出： 为了使samp_db 成为当前数据库，发布USE 语句即可： USE 为少数几个不需要终结符的语句之一，当然，加上终结符也不会出错。HELP 是另一个不需要终结符的语句。如果想了解不需要终结符的语句有哪些，可发布HELP 语句。在发布了USE 语句后，samp_db 成为缺省数据库： 使数据库成为当前数据库的另一个方法是在激活mysql 时在命令行上指定它，如下所示： 事实上，这是一个命名要使用的数据库的方法。如果需要连接参数可在数据库名前指定。例如，下列两个命令使我们能连接到在本地主机和p i t - v i p e r.snake.net 上的samp_db 数据库上： 除非另有指定，否则后面的例子都假定在激活mysql 时，在命令行上给出samp_db 使其成为当前数据库。如果激活数据库时忘了在命令行上指定数据库，只需发布USE samp_db 语句即可。 1.4.6 创建表 本节中，我们将创建样例数据库samp_db 所需的表。我们首先考虑美国历史同盟需要的表。然后再考虑学分保存方案所需的表。在某些数据库的书籍中，在这里要大讲分析与设计、实体—关系图、标准程序以及诸如此类的东西。这里确实也可以讲这些东西，但是我宁可只讲点实用的东西，比方说，我们的数据库应该是怎样的：数据库中将包含什么内容，每个表中有哪些数据以及由决定如何表示数据而带来的一些问题。这里所作出的关于数据表示的选择并不是绝对的。在其他场合下，可能会选择不同的方式来表示类似的数据，这取决于应用的需要以及打算将数据派何用途。 1. 美国历史同盟所需的表 美国历史同盟的表设计相当简单： ■ 总统( p r e s i d e n t )表。此表含有描述每位总统的记录。同盟站点上的联机测验要使用这个表。 ■ 会员( m e m b e r )表。此表用来维护同盟每个会员的当前信息。这些信息将用来建立会员地址名录的书面和联机版本、发送会员资格更新提示等等。 (1) president表 president 表很简单，因此我们先讨论它。这个表将包含每位美国总统的一些基本信息： ■ 姓名。姓名在一个表中可用几种方式表示。如，可以用一个单一的列来存放完整的姓名，或者用分开的列来分别容纳名和姓。当然用单一的列更为简单，但是在使用上会带来一些限制，如： ■ 如果先输入只有名的姓名，则不可能对姓进行排序。 ■ 如果先输入只有姓的姓名，就不可能对具有名的姓名进行显示。 ■ 难以对姓名进行搜索。例如，如果要搜索某个特定的姓，则必须使用一个特定的模式，并且查找与这个模式匹配的姓名。这样较之只查找姓效率更低和更慢。member 表将使用单独的名和姓的列以避免这些限制。名列还存放中名（注：西方国家的姓名一般将名放在前，姓放在后，而且除了有名和姓外，有时还有中名，这是在位置上介于名和姓之间的中间名字）或首字母。这样应该不会削弱我们可能进行的任何一种排序，因为一般不可能对中名进行排序（或者甚至不会对名进行排序）。姓名即可以“ Bush, George W. ”格式显示，也可以“G e o rge W. B u s h”格式显示。还有一种稍显复杂一点的情形。一个总统（ Jimmy Carter）在其姓名的末尾处有一 个“ J r. ”，这时怎样做？根据名字打印的格式，这个总统的姓名显示为“ J a m e s E . C a r t e r, J r.”或“C a r t e r, James E., Jr.”，“J r.”与名和姓都没有关系，因此我们将建另外一个字段来存放姓名的后缀。这表明在试图确定怎样表示数据时，即使一个特殊的值也可能会带来问题。它也表明，为什么在将数据放入数据库前，尽量对数据值的类型进行了解是一个很好的想法。如果对数据了解不够，那么有可能在已经开始使用一个表后，不得不更改该表的结构。这不一定是个灾难，但通常应该避免。 ■ 出生地（城市和州）。就像姓名一样，出生地也可以用单个列或多个列来表示。使用单列更为简单些，但正如姓名中的情形一样，独立的多个列使我们可以完成用单个列不方便完成的事情。例如，如果城市和州分别给出，查找各位总统出生在哪个州的记录就会更容易一些。 ■ 出生日期和死亡日期。这里，唯一特殊的问题是我们不能要求都填上死亡日期，因为有的总统现在还健在。MySQL 提供了一个特殊的值N U L L，表示“无值”，可将其用在死亡日期列中以表示“仍然健在”。 (2) member 表 存储历史同盟会员清单的member 表在每个记录都包含单个人员的基本描述信息这一点上，类似于president 表。但是每个member 的记录所含的列更多，member 表的各列如下： ■ 姓名。使用如president 表一样的三个列来表示：姓、名（如果可能的话还有中名）、后缀。 ■ ID 号。这是开始记录会员时赋给每个会员的唯一值。以前同盟未用ID 号，但现在的记录做得更有系统性，所以最好开始使用ID 号。（我希望您找到有利于使用M y S Q L并考虑到其他的将它用于历史同盟记录的方法。使用数字，将member 表中的记录与其他与会员有关的表中的记录相关联要更容易一些。） ■ 截止日期。会员必须定期更新他们的会员资格以免作废。对于某些应用，可能会用到最近更新的日期，但是近更新日期不适合于历史同盟。会员资格可在可变的年数内（一般为一年、二年、三年或五年）更新，而最近更新的日期将不能表示下一次更新必须在何时进行。此外，历史同盟还允许有终生会员。我们可以用未来一个很长的日期来表示终生会员，但是用NULL 似乎更为合适，因为“无值”在逻辑上对应于“永不终止”。 ■ 电子邮件地址。对于有电子邮件地址的会员，这将使他们能很容易地进行相互之间的通信。作为历史同盟秘书，这使您能电子化地发送更新通知给会员，而用不着发邮政信函。这比到邮局发送信函更容易，而且也不贵。还可以用电子邮件给会员发送他们的地址名录条目的当前内容，并要求他们在有必要时更新信息。 ■ 邮政地址。这是与没有电子邮件（或没有返回信息）的会员联络所需要的。将分别使用街道地址、城市、州和Zip 号。街道地址列又可以用于有诸如P.O. Box 123 而不是123 Elm St. 的会员的信箱号。我们假定所有同盟会员全都住在美国。当然，对于具有国际会员的机构，此假设过于简化了。如果希望处理多个国家的地址，还需要对不同国家的地址格式作一些工作。例如，这里的Zip 号就不是一个国际标准，有的国家有省而不是州。 ■ 电话号码。与地址字段一样，这个列对于联络会员也是很有用的。 ■ 特殊爱好的关键词。假定每个会员一般都对美国历史都有兴趣，但可能有的会员对某些领域有特殊的兴趣。此列记录了这些特殊的兴趣。会员可以利用这个信息来找到其他具有类似兴趣的会员。 (3) 创建表 现在我们已经作好了创建历史同盟表的准备。我们用C R E ATE TABLE 语句来完成这项工作，其一般格式如下： 其中tbl_name 代表希望赋予表的名称。column_specs 给出表中列的说明，以及索引的说明（如果有的话）。索引能使查找更快；我们将在第4 章“查询优化”中对其作进一步的介绍。president 表的C R E ATE TABLE 语句如下所示： 如果想自己键入这条语句，则调用m y s q l，使samp_db 为当前数据库： 然后，键入如上所示的C R E ATE TABLE 语句。（请记住，语句结尾要增加一个分号，否则mysql 将不知道哪儿是语句的结尾。）为了利用来自样例数据库分发包的预先写下的描述文件来创建president 表，可从外壳程序运行下列命令： 不管用哪种方法调用m y s q l，都应该在命令行中数据库名的前面指定连接参数（主机名、用户名或口令）。C R E ATE TABLE 语句中每个列的说明由列名、类型（该列将存储的值的种类）以及一些可能的列属性组成。president 表中所用的两种列类型为VARCHAR 和D AT E。VA R C H A R（n）代表该列包含可变长度的字符（串）值，其最大长度为n 个字符。可根据期望字符串能有多长来选择n 值。state 定义为VA R C H A R ( 2 )；即所有州名都只用其两个字符的缩写来表示。其他的字符串列则需要更长一些，以便存放更长的值。我们使用过的其他列类型为D AT E。这种列类型表示该列存储的是日期值，这一点也不令人吃惊。而令人吃惊的是，日期的表示以年份开头。其标准格式为“ Y Y Y Y- M M - D D”（例如，“1 9 9 9 - 0 7 - 1 8”）。这是日期表示的ANSI SQL 标准。我们用于president 表的唯一列属性为N U L L（值可以缺少）和NOT NULL（必须填充值）。多数列是NOT NULL 的，因为我们总要有一个它们的值。可有NULL 值的两个列是s u ff i x（多数姓名没有后缀）和d e a t h（有的总统仍然健在，所以没有死亡日期）。member 表的C R E ATE TABLE 语句如下所示： 将此语句键入mysql 或执行下列外壳程序命令： 从列的类型来看，member 表并不很有趣：所有列中，除了一列之外，其他列都是可变长字符串。这个例外的列就是e x p i r a t i o n，为D ATE 型。终止日期值有一个缺省值为“0 0 0 0 - 0 0 -0 0”，这是一个非NULL 的值，它表示未输入合法的日期值。这样做的原因是expiration 可以是N U L L，它表示一个会员是终身会员。但是，因为此列可以为N U L L，除非另外指定一个不同的值，否则它将取缺省值“ 0 0 0 0 - 0 0 - 0 0”。如果创建了一个新会员记录，但忘了指定终止日期，该会员将成为一个终身会员！通过采用缺省值“ 0 0 0 0 - 0 0 - 0 0”的方法，避免了这个问题。它还向我们提供了一种手段，即可以定期地搜索这个值，以找出过去未正确输入终止日期的记录。 请注意，我们“忘了”放入会员ID 号的列。这是专门为了以后练习使用A LTER TA B L E语句而遗留下的。现在让我们来验证一下MySQL 是否确实如我们所期望的那样创建了表。在mysql 中，发布下列查询： 与MySQL 3.23 一样，此输出还包括了显示访问权限信息的另一个列，这里没有给出，因为它使每行太长，不易显示。 这个输出结果看上去和我们所期望的非常一致，除了state 列的信息显示它的类型为C H A R ( 2 )。这就有点古怪了，我们不是定义它为VARCHAR(2) 了吗？是的，是这样定义的，但是MySQL 已经悄悄地将此类型从VARCHAR 换成了C H A R。原因是为了使短字符串列的存储空间利用更为有效，这里不多讨论。如果希望详细了解，可参阅第3 章中关于A LT E RTABLE 语句的介绍。但对这里的使用来说，两种类型没有什么差别。 如果发布一个DESCRIBE member 查询，mysql 也会显示member 表的类似信息。DESCRIBE 在您忘了表中的列名、需要知道列的类型、了解列有多宽等的时候很有用。它对于了解MySQL 存储表行中列的次序也很有用。列的这个存储次序在使用I N S E RT 或LOAD DATA 语句时非常重要，因为这些语句期望列值以缺省列的次序列出。DESCRIBE 可以省写为D E S C，或者，如果您喜欢键入较多字符，则D E S C R I B Etbl_name 另一个等同的语句为SHOW COLUMNS FROM tbl_name。 如果忘了表名怎么办？这时可以使用SHOW TA B L E S。对于samp_db 数据库，我们目前为止创建了两个表，其输出结果如下： 如果您甚至连数据库名都记不住，可在命令行上调用mysql 而不用给出数据库名，然后发布SHOW DATABASES 查询： 数据库的列表在不同的服务器上是不同的，但是至少可以看到samp_db 和m y s q l；后一个数据库存放控制MySQL 访问权限的授权表。DESCRIBE 与SHOW 查询具有可从外壳程序中使用的命令行等同物，如下： % mysqlshow 与SHOW DATABASES 一样列出所有数据库 % mysqlshow d b _ n a m e 与SHOW TABLES 一样列出给定数据库的表 % mysqlshow db_name tbl_name 与DESCRIBE tbl_name 一样，列出给定表中的列 2. 用于学分保存方案的表 为了知道学分保存方案需要什么表，我们来看看在原来学分簿上是怎样记学分的。图1 - 2示出学分簿的一页。该页的主体是一个记录学分矩阵。还有一些对学分有意义的必要信息。学生名和ID 号列在矩阵的一端。（为了简单好看，只列出了四个学生。）在矩阵顶端，记录了进行测验和测试的日期。图中示出9月3号、6号、1 6号和2 3号进行测验， 9月9号和1 0月1号进行测试。 为了利用数据库来记录这些信息，需要一个学分表。这个表中应该包含什么记录呢？很明显，每一行都需要有学生名、测验或测试的日期以及学分。图1-3 示出了用这样的表表示的一些来自学分簿的学分。（日期以MySQL 的表示格式“Y Y Y Y- M M - D D”表示。） (点击查看原图) 但是，以这种方式设置表似乎有点问题。好像少了点什么。请看图1 - 3中的记录，我们分辨不出是测验的学分还是测试的学分。如果测验和测试的学分权重不同，在确定最终的学分等级时知道学分的类型是很重要的。或许可以试着从学分的取值范围来确定学分的类型（测验的学分一般比测试的学分少），但是这样做很不方便，因为这需要进行判断，而且在数据中也不明显。可以通过记录学分的类型来进行区分，如对学分表增加一列，此列包含“ T”或“Q”以表示是“测试”或是“测验”，如图1-4 所示。这具有使学分数据类型清析易辨的优点。不利的地方是这个信息有点冗余。显然对具有同一给定日期的记录，学分的类型列总是取相同的值。9月2 3日的学分总是为“ Q”类型，而1 0月1日的学分其类型总是具有“ T”类型。这样令人很不满意。如果我们以这种方式记录一组测验或测试的学分，不仅要为每个新记录输入相同的日期，而且还要一再重复地输入相同的学分类型。谁会希望一再输入冗余的信息呢？ 我们可以试试另外一种表示。不在score 表中记录学分类型，而是从日期上区分它们。我们可以做一个日期列表，用它来记录每个日期发生的“学分事件”（测验或测试）。然后可以将学分与这个事件列表中的信息结合，确定学分是测验学分还是测试学分。这只要将score 表记录中的日期与event 表中的日期相匹配得出事件类型即可。图1 - 5示出这个表的设计并演示了score 表记录与9月2 3日这个日期相关联的工作。通过将score 表中的记录与event 表中记录相对应，我们知道这个学分来自测验。 这比根据某些猜测来推断学分类型要好得多；我们可以根据明确记录在数据库中的数据来直接得到学分类型。这也比在score 表中记录学分类型更好，因为我们只需对每个类型记录一次。 但是，在第一次听到这种事情时（即结合使用多个表中的信息），可能会想，“嗯，这是一个好主意，但是不是要做很多工作呢？会不会使工作更复杂了？”在某种程度上，这种想法是对的。处理两个记录表比处理一个要复杂。但是再来考察一下学分簿（见图1 - 2）。不是也记录了两套东西吗？考虑下列事实： ■ 在学分矩阵中用两个单元记录学分，其中每个单元都是按学生名字和日期（在矩阵的旁边和顶上）进行索引的。这代表了一组记录；与score 表的作用相同。 ■ 怎样知道每个日期代表的事件类型呢？在日期上方写了字符“ T”或“Q”！因此，也在矩阵顶上记录了日期和学分类型之间的关系。它代表第二组记录；与event 表的作用相同。 换句话说，这里建议在两个表中记录信息与用学分簿记录信息所做的工作没什么不同。唯一不同的是，这两组信息在学分簿中不是那么明显地被分开。在图1 - 5中所示的event 表的设计中加了一个要求，那就是日期必须是唯一的，因为要用它连接score 与event 表的记录。换句话说，同一天不能进行两次测验，或者同一天不能进行一次测验和一次测试。否则，将会在score 表中有两个记录并且在event 表中也有两个记录，全都具有相同的日期，这时就不知道应如何将score 的记录与event 的记录进行匹配。如果每天不多于一个学分事件，这就是一个永远不会出现的问题，可是事实并非如此简单。有时，一天中可能会有不止一个学分事件。我常听有的人说他们的数据，“那种古怪情况从不会出现。”然而，如果这种情况确实出现时，就必须重新设计表以适应这种情况引起的问题。最好是预先考虑以后可能出现的问题，并预先准备好怎样处理他们。因此，我们假定有时可能会需要同一天记录两组学分。我们怎样处理呢？如果出现这种情况，问题并不难解决。只要对处理数据的方式作一点小的更改，就可使同一日期上有多个事件而不会引起问题： 1) 增加一个列到event 表，并用它来给表中每个记录分配一个唯一的编号。实际上这就给了每个事件一个唯一的ID 号，因此我们称该列为event_id 列。（如果觉得这好像是做傻事，可看一下图1-2 中的学分簿，其中已经有这个特征了。事件ID 正好与学分簿分数矩阵中列号相似。这个编号可能没有清晰地写在那儿并标上“事件I D，”但是它确实在那儿。） 2) 当向score 表中输入学分时，输入的是事件ID 而不是日期。这些改变的结果如图1-6 所示。现在连接score 和event 表时，用的是事件ID 而不是日期，而且不仅用event 表来决定每个学分的类型，而且还用它来决定其日期。并且在event 表中不再有日期必须唯一这个限制，而唯一的是事件I D。这表示同一天可以有一打测试和测验，而且能够在记录里边直接保存它们。（毫无疑问，学生们听到这个一定浑身发抖。）不幸的是，从人的观点来看，图1-6 中的表设计较前一个更不能令人满意。score 表也更为抽象一些，因为它包含的从直观上可以理解的列更少。而图1-4 中此表的设计直观且容易理解，因为那个score 表具有日期和学分类型的列。当前的score 表如图1-6 所示，日期和学分类型的列都没有了。这极大地去除了作为人能够很容易考虑的一切。谁希望看到其中有“事件I D”的score 表？如果有的话，也不代表我们大多数人。 (点击查看原图) 此时，可看到能够电子化地完成学分记录，且在赋予学分等级时不必做各种乏味的手工计算。但是，在考虑了如何实际在一个数据库中表示学分信息后，又会被怎样抽象和拆分组成学分信息的表示难住了。自然会产生一个问题：“根本不使用数据库可能会更好一些？或许MySQL 不适合我？”正如您所猜测的那样，笔者将从否定的方面对这个问题进行回答，否则这本书就没必要再往下写了。不过，在考虑如何做一件工作时，应考虑各种情况并提问是否最好不使用数据库系统（如M y S Q L）而使用一些别的东西（如电子表格等）： ■ 学分簿有行和列，而电子表格也有。这使学分簿和电子表格在概念上和外观上都非常类似。 ■ 电子表格能够完成计算，可以利用一个计算字段来累计每个学生的学分。但是，要对测验和测试进行加权可能有点麻烦，但这也是可以办得到的。另一方面，如果希望只查看某部分数据（如只查看学分或测试），进行诸如男孩与女孩的比较，或以一种灵活的方式显示合计信息等，情况又大有不同了。电子表格的功能显得要差一些，而关系数据库系统完成这些工作相当容易。另外要考虑的一点是为了在关系数据库中进行表示而对数据进行抽象和分解，这个问题并不真的那么难以应付。只要考虑安排数据库使其不会以一种对您希望做的事无意义的方式来表示数据即可。但是，在确定了表示方式之后，就要靠数据库引擎来协调和表示数据了。您肯定不会希望将它视为一堆支离破碎的东西。 例如，在从score 表中检索学分时，不希望看到事件I D；但希望看到日期。这没有什么问题。数据库将会根据事件ID 从event 表中查找出日期。您还可能想要看看是测验的学分或测试的学分。这也不成问题。数据库将用相同的方法查找出学分类型，也是利用事件I D。请记住，这就是如像MySQL 这样的关系数据库的优势所在，即，使一样东西与另一样东西相关联，以便从多个来源得出信息并以您实际想看到的形式提供出来。在学分保存数据的情况中，MySQL 确实利用事件ID 将信息组合到了一起，而无需人工来完成这件事。现在我们先来看看，如何使MySQL 完成这种将一个东西与另一个东西相联系的工作。 假定希望看到1 9 9 9年9月2 3号的学分，针对某个特定日期中给出的事件的学分查询如下所示： 相当吓人，是吗？这个查询通过将score 表的记录与event 表的记录连接（关联）来检索学生名、日期、学分和学分的类型。其结果如下所示： 您肯定注意到了，它与图1-4 中给出的表设计相同，而且不需要知道事件ID 就可得出这个结果，只需指出感兴趣的日期并让MySQL 查找出哪个学分记录具有该日期即可。如果您一直担心抽象和分解会使我们损失一些东西的话，看到这个世界，就不会有这种担心了。当然，在考虑过查询后，您还可能对其他别的东西产生担心。即，这个查询看上去有点长并且也有点复杂；是不是做了很多工作写出这样的东西只是为了查找某个给定日期的学分？是的，确实是这样。但是，在每次想要发布一个查询时，有几种方法可以避免键入多行的S Q L。一般情况下，一旦您决定如何执行这样一个查询并将它保存起来后，就可以按需要多次执行它。我们将在1 . 5节“与mysql 交互的技巧”中介绍怎样完成这项工作。 在上述查询的介绍中，我们有点超前了。不过，这个查询比起我们要实际用来得出学分的查询是有点简单了。原因是，我们还要对表的设计作更多的修改。我们将采用一个唯一的学生I D，而不在score 表中记录学生名。（即，我们将使用来自学分簿的“ I D”列的值而不是来自“ N a m e”列的值。）然后，创建另一个称为student 的表来存放name 和student_id 列（见图1 - 7）。 (点击查看原图) 为什么要作出这种修改呢？只有一个原因，可能有两个学生有相同的名字。采用唯一的学生ID 号可帮助区分他们的学分。（这与利用唯一的事件ID 而不是日期来分辨出相同日期的测试或测验完全类似。）在对表的设计作了这样的修改后，实际用来获得给定日期的学分查询变得更为复杂了一些，这个查询如下： 如果您不能立即清楚地读懂这个查询的意思的话，也不必担心。在进一步深入这个教程之后，就能看懂这个查询了。将会从图1 - 7中注意到，在student 表中增加了点学分簿中没有的东西。它包含了一个性别列。这便可以做一些简单的事情，如对班级中男孩和女孩的人数计数；也可以做一些更为复杂的事情，如比较男孩和女孩的学分。我们已经设计完了学分保存的几乎所有的表。现在只需要另外一个表来记录出勤情况即可。这个表的内容相对较为直观，即，一个学生ID 号和一个日期（见图1 - 8）。表中的每行表示特定的学生在给定的日期缺勤。在学分时段末，我们将调用MySQL 的计数功能来汇总此表的内容，以便得出每个学生的缺勤数。 既然现在已经知道学分保存的各个表的结构，现在可以创建它们了。student 表的C R E ATE TABLE 语句如下： 将上述语句键入mysql 或执行下列外壳程序命令： C R E ATE TABLE 语句创建了一个名为student 的表，它含有三列，分别为： n a m e、s e x和s t u d e n t _ i d。name 是一个可变长的字符串列，最多可存放20 个字符。这个名字的表示比历史同盟表中所用的表示要简单，它只用了单一的列而不是分别的名和姓列。这是因为我们已经预先知道，不存在无需做另外的工作就使得在多个列上工作得更好的查询样例。sex 表示学生是男孩还是女孩。这是一个E N U M（枚举）列，表示只能取明确地列在说明中的值之一，这里列出的值为：“F”和“M”，分别表示女和男。在某列只具有一组有限值时，ENUM 类型非常有用。我们可以用CHAR(1) 来代替它，但是ENUM 更明确规定了列可以取什么值。如果对包括一个ENUM 列的表发布一条DESCRIBE tbl_name 语句，MySQL 将确切地显示可取的值有哪些。顺便说一下， ENUM 列中的值不一定只是单个字符。此列还可以定义为E N U M(‘f e m a l e’,‘m a l e’)。 student_id 为一个整数型列，它将包含唯一的ID 号。通常，大概会从一个中心资料来源处（如学校办公室）取得学生的ID 号，但在这里是我们自己定的。虽然student_id 列只包含一个数，但其定义包括几个部分： ■ INT 说明此列的值必须取整数（即无小数部分）。 ■ UNSIGNED 不允许负数。 ■ NOT NULL 表示此列的值必须填入。（任何学生都必须有一个ID 号。） ■ A U TO_INCREMENT 是MySQL 中的一个特殊的属性。其作用为：如果在创建一个新的student 表记录时遗漏了student_id 的值（或为N U L L），MySQL 自动地生成一个大于当前此列中最大值的唯一ID 号。在录入学生表时将用到这个这特性，录入学生表时可以只给出name 和sex 的值，让MySQL 自动生成student_id 列值。 ■ P R I M A RY KEY 表示相应列的值为快速查找进行索引，并且列中的每个值都必须是惟一的。这样可防止同一名字的I D出现两次，这对于学生ID 号来说是一个必须的特性。（不仅如此，而且MySQL 还要求每个A U TO_INCREMENT 列都具有一个惟一索引。）如果您不理解A U TO_INCREMENT 和P R I M A RY KEY 的含义，只要将其想像为一种为每个学生产生ID 号的魔术方法即可。除了要求值唯一外，没有什么别的东西。请注意：如果确实打算从学校办公室取得学生ID 号而不是自动生成它们，则可以按相同的方法定义student_id 列，只不过不定义A U TO_INCREMENT 属性即可。event 表如下定义： 将此语句键入mysql 或执行下列外壳程序的命令： 所有列都定义为NOT NULL，因为它们中任何一个值都不能省略。date 列存储标准的MySQL DATE 日期值，格式为“Y Y Y Y- M M - D D”（首先是年）。type 代表学分类型。像student 表中的sex 一样，type 也是一个枚举列。所允许的值为“T”和“Q”，分别表示“测试”和“测验”。event_id 是一个AUTO_INCREMENT 列，类似于student 表中的student_id 列。采用AUTO_INCREMENT 允许生成唯一的事件ID 值。正如student 表中的student_id 列一样，与值的惟一性相比，某个特定的值并不重要。score 表如下定义： 将此语句键入mysql 或执行下列外壳程序的命令： score 为一个INT （整型）列。即，假定学分值总是为一个整数。如果希望使学分值具有小数部分，如5 8 . 5，应该采用浮点列类型，如F L O AT 或D E C I M A L。student_id 列和event_id 列都是整型，分别表示每个学分所对应的学生和事件。通过利用它们来连接到student 和event 表，我们能够知道学生名和事件的日期。我们将两个列组成了P R I M A RY KEY。这保证我们不会对同一测验或测试重复一个学生的学分。而且，这样还很容易在以后更改某个学分。例如，在发现学分录入错时，可以在利用MySQL 的R E P L A C E语句放入一个新记录，替换掉旧的记录。不需要执行DELETE 语句与I N S E RT 语句；M y S Q L自动替我们做了。请注意，它是惟一的event_id 和student_id 的组合。在score 表中，两者自身都可能不惟一。一个event_id 值可有多个学分记录（每个学生对应一个记录），而每个student_id 值都对应多个记录（每个测验和测试有一个记录）。用于出勤情况的absence 表如下定义： 将此语句键入mysql 或执行下列外壳程序的命令： student_id 和date 列两者都定义为NOT NULL，不允许省略值。应定义这两列的组合为主键，以免不当心建立了重复的记录。重要的是不要对同一天某个学生的缺旷进行重复计数。 1.4.7 增加新记录 至此，我们的数据库及其表都已经创建了，在下一节“检索信息”中，我们将看到怎样从数据库中取出数据。现在我们先将一些数据放入表中。在数据库中加入数据有几种方法。可通过发布I N S E RT 语句手工将记录插入某个表中。还可以通过从某个文件读取它们来增加记录，在这个文件中，记录既可以是利用L O A DD ATA 语句或mysqlimport 实用程序装入的原始数据值，也可以是预先写成可馈入mysql 的I N S E RT 语句的形式。本节介绍将记录插入表的每种方法。您所应做的是演习各种方法以明了它们是如何起作用的。然后到本节结束处运行那儿给出的命令来清除表并重装它们。这样做，能够保证表中含有作者撰写下一节时所处理的相同记录，您也能得到相同的结果。让我们开始利用I N S E RT 语句来增加记录，这是一个SQL 语句，需要为它指定希望插入数据行的表或将值按行放入的表。I N S E RT 语句具有几种形式： ■ 可指定所有列的值 例如： “I N TO”一词自MySQL 3.22.5 以来是可选的。（这一点对其他形式的I N S E RT 语句也成立。）VALUES 表必须包含表中每列的值，并且按表中列的存放次序给出。（一般，这就是创建表时列的定义次序。如果不能肯定的话，可使用DESCRIBE tbl_name 来查看这个次序。）在MySQL 中，可用单引号或双引号将串和日期值括起来。上面例子中的N U L L值是用于student 和event 表中的A U TO_INCREMENT 列的。（插入“错误”的值将导致下一个student_id 或event_id 号的自动生成。）自3.22.5 以来的MySQL 版本允许通过指定多个值的列表，利用单个的I N S E RT语句将几行插入一个表中，如下所示： 例如: (点击查看原图) 这比多个I N S E RT 语句的键入工作要少，而且服务器执行的效率也更高。 ■ 可以给出要赋值的那个列，然后再列出值。这对于希望建立只有几个列需要初始设置的记录是很有用的。 (点击查看原图) 例如: (点击查看原图) 　 自MySQL 3.22.5 以来，这种形式的I N S E RT 也允许多个值表： (点击查看原图) 在列的列表中未给出名称的列都将赋予缺省值。 ■ 自MySQL 3.22 .10 以来，可以col_name = value 的形式给出列和值。 例如: 在SET 子句中未命名的行都赋予一个缺省值。使用这种形式的I N S E RT 语句不能插入多行。将记录装到表中的另一种方法是直接从文件读取数据值。可以用LOAD DATA 语句或用mysqlimport 实用程序来装入记录。LOAD DATA 语句起批量装载程序的作用，它从一个文件中读取数据。可在mysql 内使用它，如下所示： 该语句读取位于客户机上当前目录中数据文件m e m b e r.txt 的内容，并将其发送到服务器装入member 表。如果您的MySQL 版本低于3 . 2 2 . 1 5，则LOAD DATA LOCAL 不起作用，因为那时从客户机读取数据的能力是在LOAD DATA 上的。（没有LOCAL 关键字，被读取的文件必须位于服务器主机上，并且需要大多数MySQL 用户都不具备的服务器访问权限。）缺省时，LOAD DATA 语句假定列值由tab 键分隔，而行则以换行符结束。还假定各个值是按列在表中的存放次序给出的。也有可能需要读取其他格式的文件，或者指定不同的列次序。更详细的内容请参阅附录D的LOAD DATA 的条款。mysqlimport 实用程序起LOAD DATA 的命令行接口的作用。从外壳程序调用mysqlimport ，它生成一个LOAD DATA 语句： mysqlimport 生成一个LOAD DATA 语句，此语句使m e m b e r.txt 文件被装入member 表。如果您的MySQL 版本低于3 . 2 2 . 1 5，这个实用程序不起作用，因为--local 选项需要L O A DD ATA LOCAL。正如使用mysql 一样，如果您需要指定连接参数，可在命令行上数据库名前指定它们。mysqlimport 从数据文件名中导出表名（它将文件名第一个圆点前的所有字符作为表名）。例如，m e m b e r.txt 将被装入member 表，而president.txt 将被装入president 表。如果您有多个需要装入单个表的文件，应仔细地选择文件名，否则mysqlimport 将不能使用正确的表名。对于如像member1.txt 与member2.txt 这样的文件名， mysqlimport 将会认为相应的表名为member1 和m e m b e r 2。不过，可以使用如m e m b e r.1.txt 和m e m b e r.2.txt 或m e m b e r.txt1 和m e m b e r.txt2 这样的文件名。在试用过这些记录追加的方法后，应该清除各个表并重新装载它们，以便它们的内容与下一节假定的内容相同。从外壳程序执行下列命令： 每个文件都含有一个删除可能曾经插入到表中的记录的DELETE 语句，后跟一组INSERT 语句以初始化表的内容。如果不希望分别键入这些命令，可试一下下列语句： 1.4.8 检索信息 现在各个表已经创建并装有数据了，因此让我们来看看可以对这些数据做点什么。SELECT 语句允许以一般的或特殊的方式检索和显示表中的信息。它可以显示表的整个内容： 或者只显示单个行中单个列的内容： SELECT 语句有几个子句（部件），可以根据需要用来检索感兴趣的信息。每个子句都可简单、可复杂，从而SELECT 作为一个总的语句也繁简皆宜。但是，可以放心，本书中不会有花一个钟头来编写的长达数页的查询。（我在书中看到有很长的查询时，一般会立即跳过它们，因此我猜您也会这样。）SELECT 语句的一般形式为：SELECT 要选择的东西FROM 一个或多个表WHERE 数据必须满足的条件记住，SQL 为一个自由格式的语言，因此在您编写SELECT 查询时，语句的断行不必严格依照本书。 为了编写SELECT 语句，只需指定需要检索什么，然后再选择某些子句即可。刚才给出的子句“ F R O M”、“W H E R E”是最常用的，还有一些其他的子句，如GROUP BY、O R D E RBY 和LIMIT 等。FROM 子句一般都要给出，但是如果不从表中选择数据，也可不给出。例如，下列查询只显示某些可以直接计算而不必引用任何表的表达式的值，因此不需要用FROM 子句： 在确实使用一个FROM 子句指定了要从其中检索数据的表时， SELECT 语句的最“普通”的格式是检索所有内容。用“ *”来表示“所有列”。下面的查询将从student 表中检索所有行并显示： 各列按它们MySQL 在表中存放的次序出现。该次序与发布DESCRIBE student 语句时显示的列次序相同。（例子末尾的“. . .”表示此查询返回的输出行比这里显示的还要多。）可明确地命名希望得到的一列或多列。如果只选择学生名，发布下列语句： 如果名字不止一列，可用逗号分隔它们。下列的语句与SELECT * FROM student 等价，只是明确地指出了每一列： 可按任意次序给出列： 如果有必要，同一列甚至也可以给出多次，虽然这样做一般是没有意义的。列名在MySQL 中不区分大小写的。下面的查询是等同的： 数据库和表名有可能区分大小写的；这有取决服务器主机上使用的文件系统。在U N I X上运行的服务器对数据库名和表名是区分大小写的，因为UNIX 的文件名是区分大小写的。Windows 的文件名不区分大小写，因此运行在Windows 上的服务器对数据库名和表名不区分大小写。MySQL 允许您一次从多个表中选择列。我们将这个内容留到“从多个表中检索信息”小节去介绍。