經典電商資料庫分析構建（一）

標籤：cad   custom   item   訂單狀態   select   賣出   arc   set   charset   

傳統電商表的分析以及設計（簡單版，只包括商家和使用者關係處理）

站在使用者角度

1. 首先有一張使用者表，包括使用者的基本資料。
2. 然後有一張商品表，包括商品的基本資料；除此之外，應該還有商家的id作為外鍵，可以協助使用者瞭解商品的所屬。
3. 然後訂單表，一個訂單可以有多個商品，每個商品應該有使用者購買的數量和單價以及總價。一個訂單應該還包括該訂單的總價，訂單編號等。所以，一條資料不可能表示這些資訊。
4. 所以要有一個中間表，我們稱之為訂單項表，表示訂單中每個商品的資訊。
5. 訂單項中包括 id、商品id、購買數量、單項總價、訂單id、使用者id。
6. 訂單中包括：id、訂單日期、訂單狀態、總價。

站在商家的角度 

1. 首先商家表，包括基本資料。
2. 對於產品，只要包括基本資料即可。同樣。需要加上商家id，表示所屬。
3. 對於訂單，商家應該可以查詢到每件商品的賣出數量及總價，上述設計以及可以滿足。
4. 同樣，商家還需要統計訂單。查看哪位使用者購買的商品，購買數量。上述設計也可以滿足。

設計表

1. customer(使用者表): cid、cname、cphone、caddress
2. merchant(商家表):mid、mname、mphone、maddress
3. product(商品表):pid、pname、pprice、premain、mid
4. order(訂單表)：oid、odatetime、ostate、osummoney、cid
5. 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;

插入語句

1. 顧客

   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‘,‘廣東‘);

2. 商家

   insert into merchant values(null,‘廣州寶潔‘,‘800-400-300‘,‘廣州‘),(null,‘北京老闆‘,‘808-300-446‘,‘北京‘),(null,‘鄭州宇通‘,‘888-400-533‘,‘鄭州‘);

3. 產品

   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);     

4. 訂單 

   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);

5. 訂單項

   --訂單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);

任務

1. 從商品表中查詢以“老闆”兩個字開頭的商品名稱及數量，並按數量降序排序（使用者搜尋）
2. 查詢所有的商品名、價格、所屬廠家、產地）（商品資訊展示）
3. 從訂單表中查詢購買訂單編號為“2”的所有商品的商品名字、單價、數量、顧客名字及訂單日期（使用者查看訂單）
4. 更新訂單1：更新狀態為“已支付”，總價格為實際價格。顯示：訂單編號為“1”的所有商品的商品名字、單價、數量、顧客名字及訂單日期，總價格，訂單狀態（使用者支付查看訂單）
5. 刪除訂單3資訊（使用者取消訂單）
6. 查詢編號為2的商品的售出數量、名字、購買人名字（商家查看某件商品出售情況）
7. 查看寶潔公司的銷售情況,按照銷售數量由高到低排序（商家查看售出資料）
8. 將售出的商品按照售出數量由高到低排序（管理員查看售出資料）

答案

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 TransferSource Server         : 123Source Server Version : 50027Source Host           : localhost:3306Source Database       : mygistTarget Server Type    : MYSQLTarget Server Version : 50027File Encoding         : 65001Date: 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‘);

經典電商資料庫分析構建（一）