传统电商表的分析以及设计(简单版,只包括商家和用户关系处理)
站在用户角度
- 首先有一张用户表,包括用户的基本信息。
- 然后有一张商品表,包括商品的基本信息;除此之外,应该还有商家的id作为外键,可以帮助用户了解商品的所属。
- 然后订单表,一个订单可以有多个商品,每个商品应该有用户购买的数量和单价以及总价。一个订单应该还包括该订单的总价,订单编号等。所以,一条数据不可能表示这些信息。
- 所以要有一个中间表,我们称之为订单项表,表示订单中每个商品的信息。
- 订单项中包括 id、商品id、购买数量、单项总价、订单id、用户id。
- 订单中包括:id、订单日期、订单状态、总价。
站在商家的角度
- 首先商家表,包括基本信息。
- 对于产品,只要包括基本信息即可。同样。需要加上商家id,表示所属。
- 对于订单,商家应该可以查询到每件商品的卖出数量及总价,上述设计以及可以满足。
- 同样,商家还需要统计订单。查看哪位用户购买的商品,购买数量。上述设计也可以满足。
设计表
- customer(用户表): cid、cname、cphone、caddress
- merchant(商家表):mid、mname、mphone、maddress
- product(商品表):pid、pname、pprice、premain、mid
- order(订单表):oid、odatetime、ostate、osummoney、cid
- orderitem(订单项表):oiid、iobuynum、iosummoney、pid、oid
建表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `customer`
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`cid` int(11) NOT NULL auto_increment,
`cname` varchar(20) default NULL,
`cphone` varchar(11) default NULL,
`caddress` varchar(200) default NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `merchant`
-- ----------------------------
DROP TABLE IF EXISTS `merchant`;
CREATE TABLE `merchant` (
`mid` int(11) NOT NULL auto_increment,
`mname` varchar(20) default NULL,
`mphone` varchar(11) default NULL,
`maddress` varchar(200) default NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`oid` int(11) NOT NULL auto_increment,
`odatetime` datetime default NULL,
`ostate` varchar(20) default NULL,
`osummoney` double default NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`oid`),
KEY `cid` (`cid`),
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `ordersitem`
-- ----------------------------
DROP TABLE IF EXISTS `ordersitem`;
CREATE TABLE `ordersitem` (
`oiid` int(11) NOT NULL auto_increment,
`iobuynum` int(11) default NULL,
`iosummoney` double default NULL,
`pid` int(11) NOT NULL,
`oid` int(11) NOT NULL,
PRIMARY KEY (`oiid`),
KEY `pid` (`pid`),
KEY `oid` (`oid`),
CONSTRAINT `ordersitem_ibfk_2` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`),
CONSTRAINT `ordersitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`pid` int(11) NOT NULL auto_increment,
`pname` varchar(20) default NULL,
`pprice` double default NULL,
`premain` int(11) default NULL,
`mid` int(11) NOT NULL,
PRIMARY KEY (`pid`),
KEY `mid` (`mid`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `merchant` (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入语句
顾客
insert into customer values(null,'张三','15639854081','南阳'); insert into customer values(null,'李四','17439804082','上海'); insert into customer values(null,'王五','13438454088','长春'); insert into customer values(null,'赵六','18239454061','上海'); insert into customer values(null,'刘八','15439854382','北京'); insert into customer values(null,'旺财','17433854087','广东');
商家
insert into merchant values(null,'广州宝洁','800-400-300','广州'),(null,'北京老板','808-300-446','北京'),(null,'郑州宇通','888-400-533','郑州');
产品
insert into product values(null,'佳洁士炫白牙齿牙膏',6.8,300,1),(null,'舒肤佳',4.5,400,1),(null,'飘柔',20.8,5,1),(null,'海飞丝',32.5,20,1); insert into product values(null,'老板油烟机',566.00,40,2),(null,'老板插座',56.00,400,2),(null,'老板风扇',128.00,360,2),(null,'老板压力锅',288.00,460,2); insert into product values(null,'宇通油电混合客车',200000,10,3),(null,'宇通电动客车',120000,20,3),(null,'宇通重卡',400000,60,3);
订单
insert into orders values(1,'2016-03-02 12:30:02','未支付',0,1); insert into orders values(2,'2016-02-02 09:30:02','已支付',0,3); insert into orders values(3,'2016-01-02 12:28:02','已支付',0,5); insert into orders values(4,'2016-01-02 12:28:02','已支付',0,4);
订单项
--订单1:有三件商品 insert into ordersitem values(null,3,20.40,1,1),(null,2,41.6,3,1),(null,1,400000,11,1); --订单2:有两件商品 insert into ordersitem values(null,1,566,5,2),(null,1,128,7,2); --订单3:有一件商品 insert into ordersitem values(null,1,120000,10,3); --订单4:有三件商品 insert into ordersitem values(null,3,20.40,1,4),(null,1,6.8,1,4),(null,1,400000,11,4);
任务
- 从商品表中查询以“老板”两个字开头的商品名称及数量,并按数量降序排序(用户搜索)
- 查询所有的商品名、价格、所属厂家、产地)(商品信息展示)
- 从订单表中查询购买订单编号为“2”的所有商品的商品名字、单价、数量、顾客名字及订单日期(用户查看订单)
- 更新订单1:更新状态为“已支付”,总价格为实际价格。显示:订单编号为“1”的所有商品的商品名字、单价、数量、顾客名字及订单日期,总价格,订单状态(用户支付查看订单)
- 删除订单3信息(用户取消订单)
- 查询编号为2的商品的售出数量、名字、购买人名字(商家查看某件商品出售情况)
- 查看宝洁公司的销售情况,按照销售数量由高到低排序(商家查看售出数据)
- 将售出的商品按照售出数量由高到低排序(管理员查看售出数据)
答案
1. SELECT
pname 商品名,
premain 剩余量
FROM
product
WHERE pname LIKE '老板%'
ORDER BY premain ;
2. SELECT
pname 商品名,
premain 剩余量,
pprice 价格,
mname 所属厂家,
maddress 产地
FROM
product,
merchant
WHERE product.mid = merchant.mid ;
3.SELECT
pname 商品名,
premain 剩余量,
pprice 价格,
cname 顾客名,
odatetime 订单日期
FROM
product,
customer,
orders,
ordersitem
WHERE ordersitem.pid = product.pid
AND ordersitem.oid = orders.oid
AND orders.cid = customer.cid
AND orders.oid = 2 ;
4.UPDATE
orders
SET
orders.ostate = '已支付',
orders.osummoney =
(SELECT
(SELECT
SUM(
ordersitem.iobuynum * ordersitem.iosummoney
)
FROM
ordersitem
WHERE ordersitem.oid = orders.oid
AND ordersitem.oid = 1
GROUP BY ordersitem.oid))
WHERE orders.oid = 1 ;
参考:http :/ / huangyunbin.iteye.com / blog / 1190882
5.原子操作
DELETE FROM ordersitem WHERE oid=3;
DELETE FROM orders WHERE oid=3;
6. SELECT
SUM(ordersitem.iobuynum) 售出数量,
SUM(ordersitem.iobuynum*product.pprice) 总价,
product.pname 商品名称,
customer.cname 顾客名字
FROM
product,
customer,
orders,
ordersitem
WHERE ordersitem.pid = product.pid
AND ordersitem.oid = orders.oid
AND orders.cid = customer.cid
AND product.pid = 1 GROUP BY customer.cid;
7. SELECT
SUM(ordersitem.iobuynum) 售出数量,
SUM(
ordersitem.iobuynum * product.pprice
) 总价,
product.pname 商品名字
FROM
product,
ordersitem,
merchant
WHERE ordersitem.pid = product.pid
AND product.mid = merchant.mid
AND merchant.mname = "广州宝洁"
GROUP BY product.pname ;
8. SELECT
SUM(ordersitem.iobuynum) 售出数量,
SUM(
ordersitem.iobuynum * product.pprice
) 总价,
product.pname 商品名字
FROM
product,
ordersitem,
merchant
WHERE ordersitem.pid = product.pid
AND product.mid = merchant.mid
GROUP BY product.pname ORDER BY ordersitem.`iobuynum` DESC;
附:所有操作源码
/*
Navicat MySQL Data Transfer
Source Server : 123
Source Server Version : 50027
Source Host : localhost:3306
Source Database : mygist
Target Server Type : MYSQL
Target Server Version : 50027
File Encoding : 65001
Date: 2016-03-16 20:00:31
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `customer`
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`cid` int(11) NOT NULL auto_increment,
`cname` varchar(20) default NULL,
`cphone` varchar(11) default NULL,
`caddress` varchar(200) default NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '张三', '15639854081', '南阳');
INSERT INTO `customer` VALUES ('2', '李四', '17439804082', '上海');
INSERT INTO `customer` VALUES ('3', '王五', '13438454088', '长春');
INSERT INTO `customer` VALUES ('4', '赵六', '18239454061', '上海');
INSERT INTO `customer` VALUES ('5', '刘八', '15439854382', '北京');
INSERT INTO `customer` VALUES ('6', '旺财', '17433854087', '广东');
-- ----------------------------
-- Table structure for `merchant`
-- ----------------------------
DROP TABLE IF EXISTS `merchant`;
CREATE TABLE `merchant` (
`mid` int(11) NOT NULL auto_increment,
`mname` varchar(20) default NULL,
`mphone` varchar(11) default NULL,
`maddress` varchar(200) default NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of merchant
-- ----------------------------
INSERT INTO `merchant` VALUES ('1', '广州宝洁', '800-400-300', '广州');
INSERT INTO `merchant` VALUES ('2', '北京老板', '808-300-446', '北京');
INSERT INTO `merchant` VALUES ('3', '郑州宇通', '888-400-533', '郑州');
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`oid` int(11) NOT NULL auto_increment,
`odatetime` datetime default NULL,
`ostate` varchar(20) default NULL,
`osummoney` double default NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`oid`),
KEY `cid` (`cid`),
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2016-03-02 12:30:02', '已支付', '400144.4', '1');
INSERT INTO `orders` VALUES ('2', '2016-02-02 09:30:02', '已支付', '694', '3');
INSERT INTO `orders` VALUES ('4', '2016-01-02 12:28:02', '已支付', '0', '4');
-- ----------------------------
-- Table structure for `ordersitem`
-- ----------------------------
DROP TABLE IF EXISTS `ordersitem`;
CREATE TABLE `ordersitem` (
`oiid` int(11) NOT NULL auto_increment,
`iobuynum` int(11) default NULL,
`iosummoney` double default NULL,
`pid` int(11) NOT NULL,
`oid` int(11) NOT NULL,
PRIMARY KEY (`oiid`),
KEY `pid` (`pid`),
KEY `oid` (`oid`),
CONSTRAINT `ordersitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`),
CONSTRAINT `ordersitem_ibfk_2` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of ordersitem
-- ----------------------------
INSERT INTO `ordersitem` VALUES ('1', '3', '20.4', '1', '1');
INSERT INTO `ordersitem` VALUES ('2', '2', '41.6', '3', '1');
INSERT INTO `ordersitem` VALUES ('3', '1', '400000', '11', '1');
INSERT INTO `ordersitem` VALUES ('4', '1', '566', '5', '2');
INSERT INTO `ordersitem` VALUES ('5', '1', '128', '7', '2');
INSERT INTO `ordersitem` VALUES ('7', '3', '20.4', '1', '4');
INSERT INTO `ordersitem` VALUES ('8', '1', '6.8', '1', '4');
INSERT INTO `ordersitem` VALUES ('9', '1', '400000', '11', '4');
-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`pid` int(11) NOT NULL auto_increment,
`pname` varchar(20) default NULL,
`pprice` double default NULL,
`premain` int(11) default NULL,
`mid` int(11) NOT NULL,
PRIMARY KEY (`pid`),
KEY `mid` (`mid`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `merchant` (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '佳洁士炫白牙齿牙膏', '6.8', '300', '1');
INSERT INTO `product` VALUES ('2', '舒肤佳', '4.5', '400', '1');
INSERT INTO `product` VALUES ('3', '飘柔', '20.8', '5', '1');
INSERT INTO `product` VALUES ('4', '海飞丝', '32.5', '20', '1');
INSERT INTO `product` VALUES ('5', '老板油烟机', '566', '40', '2');
INSERT INTO `product` VALUES ('6', '老板插座', '56', '400', '2');
INSERT INTO `product` VALUES ('7', '老板风扇', '128', '360', '2');
INSERT INTO `product` VALUES ('8', '老板压力锅', '288', '460', '2');
INSERT INTO `product` VALUES ('9', '宇通油电混合客车', '200000', '10', '3');
INSERT INTO `product` VALUES ('10', '宇通电动客车', '120000', '20', '3');
INSERT INTO `product` VALUES ('11', '宇通重卡', '400000', '60', '3');