文章目录
1.样例表准备
整个SQL的学习过程中,使用一个模拟的订单系统作为基础,构成一个基本订单系统需要实现以下的功能:
- 管理供应商
- 管理产品目录
- 管理顾客列表
- 录入客户订单
要完成这些基本功能,需要设计关系型数据库紧密联系的6张表,分别为:
- vendors表:存储销售产品的供应商
- products表:存储产品的相关信息
- customers表:存储所有的顾客信息
- orders表:存储顾客的订单信息
- orderitems表:存储订单中的商品信息
- productnotes表:存储与特定产品有关的注释
1.1 vendors表
vendors表存储销售产品的供应商,每个供应商在这个表中有一个记录,供应商id(vend_id)列用来匹配产品和供应商。
列 | 说明 |
---|---|
vend_id | 唯一的供应商ID |
vend_name | 供应商的名称 |
vend_address | 供应商的地址 |
vend_city | 供应商的城市 |
vend_state | 供应商的省 |
vend_zip | 供应商的邮政编码 |
vend_country | 供应商的国家 |
在vendors表中,使用唯一的供应商ID作为主键,vend_id 为自增字段:
1.2 products表
products表包含产品目录,每行一个产品,每个产品有唯一的ID(prod_id),通过vend_id(供应商表的唯一ID)关联到它的供应商。
列 | 说明 |
---|---|
prod_id | 唯一的产品ID |
vend_id | 产品的供应商ID |
prod_name | 产品的名称 |
prod_price | 产品的价格 |
prod_desc | 产品的描述 |
在products表中,使用prod_id作为主键,vend_id 为外键,关联到words表中的vend_id:
1.3 customers表
customers表存储所有的顾客信息,每个顾客有唯一的ID(cust_id)。
列 | 说明 |
---|---|
cust_id | 唯一的顾客ID |
cust_name | 顾客的名称 |
cust_address | 顾客的地址 |
cust_city | 顾客的城市 |
cust_state | 顾客的省 |
cust_zip | 顾客的邮政编码 |
cust_country | 顾客的国家 |
cust_contact | 顾客的联系名 |
cust_email | 顾客的eamil |
在customers表中,使用cust_id作为主键,cust_id是一个自动增量的字段。
1.4 orders表
orders表存储顾客的订单,每个订单的唯一编号(order_num),订单用cust_id列(customer表中的顾客ID)与相应的顾客关联。
列 | 说明 |
---|---|
order_num | 订单编号 |
order_date | 订单日期 |
cust_id | 订单顾客ID |
orders表使用 order_num 作为主键,order_num是一个自动增量字段,cust_id 作为外键,关联customer表中的顾客ID。
1.5 orderitems表
orderitems表存储订单中的实际物品,每个订单的每个物品占一行,对orders中的每一行,orderitems中都对应有一行或多行。
每个订单物品由订单编号加订单物品做唯一标识,订单物品通过order_num列(关联到order中订单的唯一ID)与他们对应的订单相关联,此外,每个订单包含订单物品的产品ID(关联物品到products)。
列 | 说明 |
---|---|
order_num | 订单号 |
order_item | 订单物品号 |
prod_id | 产品ID |
quantity | 物品数量 |
item_price | 物品的价格 |
orderitems表中使用 order_num 和 order_item 作为主键,同时 order_num 作为外键关联到 order表中的 order_num,product_id 同样定义外键关联到 products表中的prod_id,order_item 订单物品号按照在订单中的顺序进行排序;
1.6 productnotes表
productnotes表存储与特定产品有关的注释,不是每一个产品都有相关的注释信息,但某些产品也可能有多个产品注释信息。
列 | 说明 |
---|---|
note_id | 注释ID |
prod_id | 产品ID |
note_date | 增加注释的日期 |
note_text | 注释文本信息 |
productnotes表使用 note_id 作为主键,prod_id 作为外键关联 products表中的prod_id;
note_text 列因为是文本信息的原因,需要设置为FULLTEXT搜索进行索引;
因为这个表使用了全文本搜索,因此必须指定ENGING=MyISAM搜索引擎;
2.SQL脚本
2.1 表创建的SQL脚本
########################
# Create customers table
########################
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT comment '顾客ID',
cust_name char(50) NOT NULL comment '顾客的名称',
cust_address char(50) NULL comment '顾客的地址',
cust_city char(50) NULL comment '顾客的城市',
cust_state char(5) NULL comment '顾客的省',
cust_zip char(10) NULL comment '顾客的邮政编码',
cust_country char(50) NULL comment '顾客的国家',
cust_contact char(50) NULL comment '顾客的联系名',
cust_email char(255) NULL comment '顾客的eamil',
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
order_num int NOT NULL comment '订单号',
order_item int NOT NULL comment '订单物品号',
prod_id char(10) NOT NULL comment '产品ID',
quantity int NOT NULL comment '物品数量',
item_price decimal(8,2) NOT NULL comment '物品的价格',
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# Create orders table
#####################
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT comment '订单编号',
order_date datetime NOT NULL comment '订单日期',
cust_id int NOT NULL comment '订单顾客ID',
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
#######################
# Create products table
#######################
CREATE TABLE products
(
prod_id char(10) NOT NULL comment '产品ID',
vend_id int NOT NULL comment '产品的供应商ID',
prod_name char(255) NOT NULL comment '产品的名称',
prod_price decimal(8,2) NOT NULL comment '产品的价格',
prod_desc text NULL comment '产品的描述',
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
######################
# Create vendors table
######################
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT comment '供应商ID',
vend_name char(50) NOT NULL comment '供应商的名称',
vend_address char(50) NULL comment '供应商的地址',
vend_city char(50) NULL comment '供应商的城市',
vend_state char(5) NULL comment '供应商的省',
vend_zip char(10) NULL comment '供应商的邮政编码',
vend_country char(50) NULL comment '供应商的国家',
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT comment '注释ID',
prod_id char(10) NOT NULL comment '产品ID',
note_date datetime NOT NULL comment '增加注释的日期',
note_text text NULL comment '注释文本信息',
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
2.2 表信息添加的SQL脚本
##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
########################
# 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');
#########################
# 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, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);
###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);
#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);