当前位置：Gxlcms > 数据库问题 > 经典电商数据库分析构建（一）

经典电商数据库分析构建（一）

时间：2021-07-01 10:21:17 帮助过：23人阅读

传统电商表的分析以及设计（简单版，只包括商家和用户关系处理）

站在用户角度

  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

建表

  1. <code>SET FOREIGN_KEY_CHECKS=0;
  2. -- ----------------------------
  3. -- Table structure for `customer`
  4. -- ----------------------------
  5. DROP TABLE IF EXISTS `customer`;
  6. CREATE TABLE `customer` (
  7.   `cid` int(11) NOT NULL auto_increment,
  8.   `cname` varchar(20) default NULL,
  9.   `cphone` varchar(11) default NULL,
  10.   `caddress` varchar(200) default NULL,
  11.   PRIMARY KEY  (`cid`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  13. -- ----------------------------
  14. -- Table structure for `merchant`
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `merchant`;
  17. CREATE TABLE `merchant` (
  18.   `mid` int(11) NOT NULL auto_increment,
  19.   `mname` varchar(20) default NULL,
  20.   `mphone` varchar(11) default NULL,
  21.   `maddress` varchar(200) default NULL,
  22.   PRIMARY KEY  (`mid`)
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  24. -- ----------------------------
  25. -- Table structure for `orders`
  26. -- ----------------------------
  27. DROP TABLE IF EXISTS `orders`;
  28. CREATE TABLE `orders` (
  29.   `oid` int(11) NOT NULL auto_increment,
  30.   `odatetime` datetime default NULL,
  31.   `ostate` varchar(20) default NULL,
  32.   `osummoney` double default NULL,
  33.   `cid` int(11) NOT NULL,
  34.   PRIMARY KEY  (`oid`),
  35.   KEY `cid` (`cid`),
  36.   CONSTRAINT `order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`)
  37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  38. -- ----------------------------
  39. -- Table structure for `ordersitem`
  40. -- ----------------------------
  41. DROP TABLE IF EXISTS `ordersitem`;
  42. CREATE TABLE `ordersitem` (
  43.   `oiid` int(11) NOT NULL auto_increment,
  44.   `iobuynum` int(11) default NULL,
  45.   `iosummoney` double default NULL,
  46.   `pid` int(11) NOT NULL,
  47.   `oid` int(11) NOT NULL,
  48.   PRIMARY KEY  (`oiid`),
  49.   KEY `pid` (`pid`),
  50.   KEY `oid` (`oid`),
  51.   CONSTRAINT `ordersitem_ibfk_2` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`),
  52.   CONSTRAINT `ordersitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`)
  53. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  54. -- ----------------------------
  55. -- Table structure for `product`
  56. -- ----------------------------
  57. DROP TABLE IF EXISTS `product`;
  58. CREATE TABLE `product` (
  59.   `pid` int(11) NOT NULL auto_increment,
  60.   `pname` varchar(20) default NULL,
  61.   `pprice` double default NULL,
  62.   `premain` int(11) default NULL,
  63.   `mid` int(11) NOT NULL,
  64.   PRIMARY KEY  (`pid`),
  65.   KEY `mid` (`mid`),
  66.   CONSTRAINT `product_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `merchant` (`mid`)
  67. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  68. </code>

插入语句

  1. 顾客

    1. <code>insert into customer values(null,‘张三‘,‘15639854081‘,‘南阳‘);
    2. insert into customer values(null,‘李四‘,‘17439804082‘,‘上海‘);
    3. insert into customer values(null,‘王五‘,‘13438454088‘,‘长春‘);
    4. insert into customer values(null,‘赵六‘,‘18239454061‘,‘上海‘);
    5. insert into customer values(null,‘刘八‘,‘15439854382‘,‘北京‘);
    6. insert into customer values(null,‘旺财‘,‘17433854087‘,‘广东‘);
    7. </code>

  2. 商家

    1. <code>insert into merchant values(null,‘广州宝洁‘,‘800-400-300‘,‘广州‘),(null,‘北京老板‘,‘808-300-446‘,‘北京‘),(null,‘郑州宇通‘,‘888-400-533‘,‘郑州‘);
    2. </code>

  3. 产品

    1. <code>insert into product values(null,‘佳洁士炫白牙齿牙膏‘,6.8,300,1),(null,‘舒肤佳‘,4.5,400,1),(null,‘飘柔‘,20.8,5,1),(null,‘海飞丝‘,32.5,20,1);
    2. insert into product values(null,‘老板油烟机‘,566.00,40,2),(null,‘老板插座‘,56.00,400,2),(null,‘老板风扇‘,128.00,360,2),(null,‘老板压力锅‘,288.00,460,2);
    3. insert into product values(null,‘宇通油电混合客车‘,200000,10,3),(null,‘宇通电动客车‘,120000,20,3),(null,‘宇通重卡‘,400000,60,3);     
    4. </code>

  4. 订单 

    1. <code>insert into orders values(1,‘2016-03-02 12:30:02‘,‘未支付‘,0,1);
    2. insert into orders values(2,‘2016-02-02 09:30:02‘,‘已支付‘,0,3);
    3. insert into orders values(3,‘2016-01-02 12:28:02‘,‘已支付‘,0,5);
    4. insert into orders values(4,‘2016-01-02 12:28:02‘,‘已支付‘,0,4);
    5. </code>

  5. 订单项

    1. <code>--订单1：有三件商品
    2. insert into ordersitem values(null,3,20.40,1,1),(null,2,41.6,3,1),(null,1,400000,11,1);
    3. --订单2：有两件商品
    4. insert into ordersitem values(null,1,566,5,2),(null,1,128,7,2);
    5. --订单3：有一件商品
    6. insert into ordersitem values(null,1,120000,10,3);
    7. --订单4：有三件商品
    8. insert into ordersitem values(null,3,20.40,1,4),(null,1,6.8,1,4),(null,1,400000,11,4);
    9. </code>

任务

  1. 从商品表中查询以“老板”两个字开头的商品名称及数量，并按数量降序排序（用户搜索）
  2. 查询所有的商品名、价格、所属厂家、产地）（商品信息展示）
  3. 从订单表中查询购买订单编号为“2”的所有商品的商品名字、单价、数量、顾客名字及订单日期（用户查看订单）
  4. 更新订单1：更新状态为“已支付”，总价格为实际价格。显示：订单编号为“1”的所有商品的商品名字、单价、数量、顾客名字及订单日期，总价格，订单状态（用户支付查看订单）
  5. 删除订单3信息（用户取消订单）
  6. 查询编号为2的商品的售出数量、名字、购买人名字（商家查看某件商品出售情况）
  7. 查看宝洁公司的销售情况,按照销售数量由高到低排序（商家查看售出数据）
  8. 将售出的商品按照售出数量由高到低排序（管理员查看售出数据）

答案

  1. <code>1. SELECT 
  2.   pname 商品名,
  3.   premain 剩余量 
  4. FROM
  5.   product 
  6. WHERE pname LIKE ‘老板%‘ 
  7. ORDER BY premain ;
  8. 2. SELECT 
  9.   pname 商品名,
  10.   premain 剩余量,
  11.   pprice 价格,
  12.   mname 所属厂家,
  13.   maddress 产地 
  14. FROM
  15.   product,
  16.   merchant 
  17. WHERE product.mid = merchant.mid ;
  18. 3.SELECT 
  19.   pname 商品名,
  20.   premain 剩余量,
  21.   pprice 价格,
  22.   cname 顾客名,
  23.   odatetime 订单日期 
  24. FROM
  25.   product,
  26.   customer,
  27.   orders,
  28.   ordersitem 
  29. WHERE ordersitem.pid = product.pid 
  30.   AND ordersitem.oid = orders.oid 
  31.   AND orders.cid = customer.cid 
  32.   AND orders.oid = 2 ;
  33. 4.UPDATE 
  34.   orders 
  35. SET
  36.   orders.ostate = ‘已支付‘,
  37.   orders.osummoney = 
  38.   (SELECT 
  39.     (SELECT 
  40.       SUM(
  41.         ordersitem.iobuynum * ordersitem.iosummoney
  42.       ) 
  43.     FROM
  44.       ordersitem 
  45.     WHERE ordersitem.oid = orders.oid 
  46.       AND ordersitem.oid = 1 
  47.     GROUP BY ordersitem.oid)) 
  48. WHERE orders.oid = 1 ;
  49. 参考：http :/ / huangyunbin.iteye.com / blog / 1190882 
  50. 5.原子操作
  51. DELETE FROM ordersitem WHERE oid=3;
  52. DELETE FROM orders WHERE oid=3;
  53. 6.   SELECT 
  54.   SUM(ordersitem.iobuynum) 售出数量,
  55. SUM(ordersitem.iobuynum*product.pprice) 总价,
  56.   product.pname 商品名称,
  57.   customer.cname 顾客名字
  58. FROM
  59.   product,
  60.   customer,
  61.   orders,
  62.   ordersitem 
  63. WHERE ordersitem.pid = product.pid 
  64.   AND ordersitem.oid = orders.oid 
  65.   AND orders.cid = customer.cid 
  66.   AND product.pid = 1 GROUP BY customer.cid;
  67. 7.  SELECT 
  68.   SUM(ordersitem.iobuynum) 售出数量,
  69.   SUM(
  70.     ordersitem.iobuynum * product.pprice
  71.   ) 总价,
  72.   product.pname 商品名字 
  73. FROM
  74.   product,
  75.   ordersitem,
  76.   merchant 
  77. WHERE ordersitem.pid = product.pid 
  78.   AND product.mid = merchant.mid 
  79.   AND merchant.mname = "广州宝洁" 
  80. GROUP BY product.pname ;
  81. 8. SELECT 
  82.   SUM(ordersitem.iobuynum) 售出数量,
  83.   SUM(
  84.     ordersitem.iobuynum * product.pprice
  85.   ) 总价,
  86.   product.pname 商品名字 
  87. FROM
  88.   product,
  89.   ordersitem,
  90.   merchant 
  91. WHERE ordersitem.pid = product.pid 
  92.   AND product.mid = merchant.mid 
  93. GROUP BY product.pname ORDER BY ordersitem.`iobuynum` DESC;
  94. </code>

附：所有操作源码

  1. <code>/*
  2. Navicat MySQL Data Transfer
  3. Source Server         : 123
  4. Source Server Version : 50027
  5. Source Host           : localhost:3306
  6. Source Database       : mygist
  7. Target Server Type    : MYSQL
  8. Target Server Version : 50027
  9. File Encoding         : 65001
  10. Date: 2016-03-16 20:00:31
  11. */
  12. SET FOREIGN_KEY_CHECKS=0;
  13. -- ----------------------------
  14. -- Table structure for `customer`
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `customer`;
  17. CREATE TABLE `customer` (
  18.   `cid` int(11) NOT NULL auto_increment,
  19.   `cname` varchar(20) default NULL,
  20.   `cphone` varchar(11) default NULL,
  21.   `caddress` varchar(200) default NULL,
  22.   PRIMARY KEY  (`cid`)
  23. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  24. -- ----------------------------
  25. -- Records of customer
  26. -- ----------------------------
  27. INSERT INTO `customer` VALUES (‘1‘, ‘张三‘, ‘15639854081‘, ‘南阳‘);
  28. INSERT INTO `customer` VALUES (‘2‘, ‘李四‘, ‘17439804082‘, ‘上海‘);
  29. INSERT INTO `customer` VALUES (‘3‘, ‘王五‘, ‘13438454088‘, ‘长春‘);
  30. INSERT INTO `customer` VALUES (‘4‘, ‘赵六‘, ‘18239454061‘, ‘上海‘);
  31. INSERT INTO `customer` VALUES (‘5‘, ‘刘八‘, ‘15439854382‘, ‘北京‘);
  32. INSERT INTO `customer` VALUES (‘6‘, ‘旺财‘, ‘17433854087‘, ‘广东‘);
  33. -- ----------------------------
  34. -- Table structure for `merchant`
  35. -- ----------------------------
  36. DROP TABLE IF EXISTS `merchant`;
  37. CREATE TABLE `merchant` (
  38.   `mid` int(11) NOT NULL auto_increment,
  39.   `mname` varchar(20) default NULL,
  40.   `mphone` varchar(11) default NULL,
  41.   `maddress` varchar(200) default NULL,
  42.   PRIMARY KEY  (`mid`)
  43. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  44. -- ----------------------------
  45. -- Records of merchant
  46. -- ----------------------------
  47. INSERT INTO `merchant` VALUES (‘1‘, ‘广州宝洁‘, ‘800-400-300‘, ‘广州‘);
  48. INSERT INTO `merchant` VALUES (‘2‘, ‘北京老板‘, ‘808-300-446‘, ‘北京‘);
  49. INSERT INTO `merchant` VALUES (‘3‘, ‘郑州宇通‘, ‘888-400-533‘, ‘郑州‘);
  50. -- ----------------------------
  51. -- Table structure for `orders`
  52. -- ----------------------------
  53. DROP TABLE IF EXISTS `orders`;
  54. CREATE TABLE `orders` (
  55.   `oid` int(11) NOT NULL auto_increment,
  56.   `odatetime` datetime default NULL,
  57.   `ostate` varchar(20) default NULL,
  58.   `osummoney` double default NULL,
  59.   `cid` int(11) NOT NULL,
  60.   PRIMARY KEY  (`oid`),
  61.   KEY `cid` (`cid`),
  62.   CONSTRAINT `order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`)
  63. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  64. -- ----------------------------
  65. -- Records of orders
  66. -- ----------------------------
  67. INSERT INTO `orders` VALUES (‘1‘, ‘2016-03-02 12:30:02‘, ‘已支付‘, ‘400144.4‘, ‘1‘);
  68. INSERT INTO `orders` VALUES (‘2‘, ‘2016-02-02 09:30:02‘, ‘已支付‘, ‘694‘, ‘3‘);
  69. INSERT INTO `orders` VALUES (‘4‘, ‘2016-01-02 12:28:02‘, ‘已支付‘, ‘0‘, ‘4‘);
  70. -- ----------------------------
  71. -- Table structure for `ordersitem`
  72. -- ----------------------------
  73. DROP TABLE IF EXISTS `ordersitem`;
  74. CREATE TABLE `ordersitem` (
  75.   `oiid` int(11) NOT NULL auto_increment,
  76.   `iobuynum` int(11) default NULL,
  77.   `iosummoney` double default NULL,
  78.   `pid` int(11) NOT NULL,
  79.   `oid` int(11) NOT NULL,
  80.   PRIMARY KEY  (`oiid`),
  81.   KEY `pid` (`pid`),
  82.   KEY `oid` (`oid`),
  83.   CONSTRAINT `ordersitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`),
  84.   CONSTRAINT `ordersitem_ibfk_2` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`)
  85. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  86. -- ----------------------------
  87. -- Records of ordersitem
  88. -- ----------------------------
  89. INSERT INTO `ordersitem` VALUES (‘1‘, ‘3‘, ‘20.4‘, ‘1‘, ‘1‘);
  90. INSERT INTO `ordersitem` VALUES (‘2‘, ‘2‘, ‘41.6‘, ‘3‘, ‘1‘);
  91. INSERT INTO `ordersitem` VALUES (‘3‘, ‘1‘, ‘400000‘, ‘11‘, ‘1‘);
  92. INSERT INTO `ordersitem` VALUES (‘4‘, ‘1‘, ‘566‘, ‘5‘, ‘2‘);
  93. INSERT INTO `ordersitem` VALUES (‘5‘, ‘1‘, ‘128‘, ‘7‘, ‘2‘);
  94. INSERT INTO `ordersitem` VALUES (‘7‘, ‘3‘, ‘20.4‘, ‘1‘, ‘4‘);
  95. INSERT INTO `ordersitem` VALUES (‘8‘, ‘1‘, ‘6.8‘, ‘1‘, ‘4‘);
  96. INSERT INTO `ordersitem` VALUES (‘9‘, ‘1‘, ‘400000‘, ‘11‘, ‘4‘);
  97. -- ----------------------------
  98. -- Table structure for `product`
  99. -- ----------------------------
  100. DROP TABLE IF EXISTS `product`;
  101. CREATE TABLE `product` (
  102.   `pid` int(11) NOT NULL auto_increment,
  103.   `pname` varchar(20) default NULL,
  104.   `pprice` double default NULL,
  105.   `premain` int(11) default NULL,
  106.   `mid` int(11) NOT NULL,
  107.   PRIMARY KEY  (`pid`),
  108.   KEY `mid` (`mid`),
  109.   CONSTRAINT `product_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `merchant` (`mid`)
  110. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  111. -- ----------------------------
  112. -- Records of product
  113. -- ----------------------------
  114. INSERT INTO `product` VALUES (‘1‘, ‘佳洁士炫白牙齿牙膏‘, ‘6.8‘, ‘300‘, ‘1‘);
  115. INSERT INTO `product` VALUES (‘2‘, ‘舒肤佳‘, ‘4.5‘, ‘400‘, ‘1‘);
  116. INSERT INTO `product` VALUES (‘3‘, ‘飘柔‘, ‘20.8‘, ‘5‘, ‘1‘);
  117. INSERT INTO `product` VALUES (‘4‘, ‘海飞丝‘, ‘32.5‘, ‘20‘, ‘1‘);
  118. INSERT INTO `product` VALUES (‘5‘, ‘老板油烟机‘, ‘566‘, ‘40‘, ‘2‘);
  119. INSERT INTO `product` VALUES (‘6‘, ‘老板插座‘, ‘56‘, ‘400‘, ‘2‘);
  120. INSERT INTO `product` VALUES (‘7‘, ‘老板风扇‘, ‘128‘, ‘360‘, ‘2‘);
  121. INSERT INTO `product` VALUES (‘8‘, ‘老板压力锅‘, ‘288‘, ‘460‘, ‘2‘);
  122. INSERT INTO `product` VALUES (‘9‘, ‘宇通油电混合客车‘, ‘200000‘, ‘10‘, ‘3‘);
  123. INSERT INTO `product` VALUES (‘10‘, ‘宇通电动客车‘, ‘120000‘, ‘20‘, ‘3‘);
  124. INSERT INTO `product` VALUES (‘11‘, ‘宇通重卡‘, ‘400000‘, ‘60‘, ‘3‘);</code>

经典电商数据库分析构建（一）

标签：cad   custom   item   订单状态   select   卖出   arc   set   charset   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！