mysql附录建表_《MySQL必知必会》学习笔记——附录B 样例表

附录B 样例表

本附录简要描述本书中所用的表及它们的用途。

编写SQL语句需要对基础数据库的设计有良好的理解。不知道什么信息存储在什么表中，表之间如何关联以及行内数据如何分解，是不可能编写出高效的SQL的。

建议你实际试验本书中没涨的每个例子。各章都使用相同的一组数据文件。为帮助你了解这些例子和掌握各章介绍的内容，本附录描述了所用的表、表之间的关系以及如何获得它们。

B.1 样例表

本书中使用的样例表为一个想象的随身物品推销商使用的订单录入系统，这些随身物品可能是你喜欢的卡通人物需要的(是的，卡通人物，没人规定学习MySQL必须沉闷地学)。这些表用来完成以下几个任务：

管理供应商；

管理产品目录；

管理顾客列表；

录入顾客订单。

要完成这几个任务需要作为关系数据库设计成分的紧密联系的6个表。以下几节描述各个表。

简化的例子 这里使用的表并不完整。现实中的订单录入系统必须记录这里没有包含的大量其他数据(如，报酬和记账信息、发货跟踪信息等)。不过，这些表演示了你在多数安装中会遇到的各种数据的组织和关系。你可以把这些方法和技术应用到自己的数据库中。

表的描述

以下介绍6个表和每个表中的列。

表的列出顺序 6个表之所以要用这里的次序列出是因为它们之间的依赖关系。因为products表依赖于vendors表，所以先列出vendors，其他表的列出也有类似的关系。

vendors表

vendors表存储销售产品的供应商。每个供应商在这个表中有一个记录，供应商ID(vend_id)列用来匹配产品和供应商。

列

说明

vend_id

唯一的供应商ID

vend_name

供应商名

vend_address

供应商的地址

vend_city

供应商的城市

vend_state

供应商的州

vend_zip

供应商的邮政编码

vend_country

供应商的国家

所有表都应该有主键。这个表使用vend_id作为主键。vend_id为一个自动增量字段。

products表

products表包含产品目录，每行一个产品。每个产品有唯一的ID(prod_id列)，通过vend_id(供应商的唯一ID)关联到它的供应商。

列

说明

prod_id

唯一的产品ID

vend_id

产品供应商ID(关联到vend

prod_name

产品名

prod_price

产品价格

prod_desc

产品描述

所有表都应该有一个主键，这个表用prod_id作为其主键。

为实施引用完整性，应该在vend_id上定义一个外键，关联到vendors的vend_id。

customers表

customers表存储所有顾客的信息。每个顾客有唯一的ID(cust_id列)。

列

说明

cust_id

唯一的顾客ID

cust_name

顾客名

cust_address

顾客的地址

cust_city

顾客的城市

cust_state

顾客的州

cust_zip

顾客的邮政编码

cust_country

顾客的国家

cust_contact

顾客的联系名

cust_email

顾客的联系email地址

所有表都应该定义主键，这个表将使用cust_id作为它的主键。cust_id是一个自动增量字段。

orders表

orders表存储顾客订单(但不是订单细节)。每个订单唯一的编号(order_num列)。订单用cust_id列(它关联到customer表的顾客唯一ID)与相应的顾客关联。

列

说明

order_num

唯一订单号

order_date

订单日期

cust_id

订单顾客ID(关联到customers表的cust_id)

所有表都应该定义主键，这个表使用order_num作为它的主键。order_num是一个自动增量字段。

为实施引用完整性，应该在cust_id定义一个外键，关联到customers的cust_id。

orderitems表

orderitems表存储每个订单中的实际物品，每个订单的每个物品占一行。对orders中的每一行，orderitems中有一行或多行。每个订单物品由订单号加订单物品(第一个物品、第二个物品等)唯一标识。订单物品通过order_num列(关联到orders中订单的唯一ID)与它们相应的订单关联。此外，每个订单项包含订单物品的产品ID(它关联物品到products表)。

列

说明

order_num

订单号(关联到orders表的order_num)

order_item

订单物品号(在某个订单中的顺序)

prod_id

产品ID(关联到products表的prod_id)

quantity

物品数量

item_price

物品价格

所有表都应该有主键，这个表使用order_num和order_item作为其主键。

为实施引用完整性，应该在order_num上定义外键，关联它到orders的order_num，在prod_id上定义外键，关联它到products的prod_id。

productnotes表

productnotes表存储与特定产品有关的注释。并非所有产品都有相关的注释，而有的产品可能有许多相关的注释。

列

说明

note_id

唯一注释ID

prod_id

产品ID(对应于products表中的prod_id)

note_date

增加注释的日期

note_text

注释文本

所有表都应该有主键，这个表应该使用note_id作为其主键。

列note_text必须为FULLTEXT搜索进行索引。

由于这个表使用全文本搜索，因此必须指定ENGINE=MyISAM。

B.2 创建样例表

为了学习各个例子，需要一组填充了数据的表。所需要获得和运行的一切东西都可以在http://www.forta.com/books/0672327120/ 上找到。

此网页包含两个可以下载的SQL脚本文件。

create.sql包含创建6个数据库表(包含所有主键和外键约束)的MySQL语句。

populate.sql包含用来填充这些表的INSERT语句。

仅对于MySQL 可下载的.sql文件中的SQL语句是DBMS专用的，它们仅用于MySQL。

这两个脚本用MySQL 4.1和MySQL 5进行了广泛的测试，但没有用更早的MySQL版本进行测试。

(zifeiy：因为我用的是MySQL 8，MySQL 8好像没有默认带MyISAM引擎，所以并不确定在实际操作过程中是否会出现问题，在此注释以下，接下来在运行这两个sql脚本文件的时候会进行相应的补充)

在下载了脚本后，可用它们创建和填充本书各章所用的表。以下是要遵循的步骤。

创建一个新数据源(为安全考虑，不要使用已有的数据源)。最简单的办法是使用MySQL Administrator(第2章中描述)(zifeiy: 客户端推荐DBEaver)

保证选择新数据源(如果使用mysql命令行实用程序，用USE命令；如果使用MySQL Query Browser，则直接选择相应的数据源)。

执行create.sql脚本。如果使用mysql命令行实用程序，可给出source create.sql；(指定create.sql文件的完全路径)。如果使用MySQL Query Browser，选择File，Open Script，create.sql，然后单击Execute按钮。

重复上面的步骤，用populate.sql文件填充各个新表。

创建，然后填充 必须在运行表填充脚本之前运行表创建脚本。一定要查看这些脚本返回的错误消息。如果创建脚本失败，则在进行表填充之前需要解决可能存在的问题。