数据库学习--MySQL必知必会(一)_mysql中vend什么意思-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41779489/article/details/108684976

本文介绍了如何使用MySQL创建一个订单管理系统，包括管理供应商、产品目录、客户列表和订单的表结构设计，如vendors、products、customers、orders、orderitems和productnotes表，并详细解释了各表之间的关联关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.创建学习数据库所用的样列表，并插入数据

样列表为一个想象的随身物品推销商使用的订单录入系统，这些表用来完成以下几个任务：
1.管理供应商
2.管理产品目录
3.管理客户列表
4.录入顾客订单

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

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      INT      NOT NULL AUTO_INCREMENT,
  vend_name    CHAR(50) NOT NULL ,
  vend_address CHAR(50) NULL ,
  vend_city    CHAR(50) NULL ,
  vend_state   CHAR(5)  NULL ,
  vend_zip     CHAR(10) NULL ,
  vend_country CHAR(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=INNODB;

########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

2.products表
products表依赖于vendors表，products表包含产品目录，每行一个产品。每个产品有唯一的ID(prod_id),通过vend_id(供应商的id)关联到他的供应商。在product表的vend_id上定义一个外键，关联到vendors的vend_id。

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    CHAR(10)      NOT NULL,
  vend_id    INT           NOT NULL ,
  prod_name  CHAR(255)     NOT NULL ,
  prod_price DECIMAL(8,2)  NOT NULL ,
  prod_desc  TEXT          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=INNODB;
#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001