时间:2021-07-01 10:21:17 帮助过:23人阅读
站在用户角度
站在商家的角度
设计表
建表
- <code>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;
- </code>
插入语句
顾客
- <code>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‘,‘广东‘);
- </code>
商家
- <code>insert into merchant values(null,‘广州宝洁‘,‘800-400-300‘,‘广州‘),(null,‘北京老板‘,‘808-300-446‘,‘北京‘),(null,‘郑州宇通‘,‘888-400-533‘,‘郑州‘);
- </code>
产品
- <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);
- 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);
- </code>
订单
- <code>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);
- </code>
订单项
- <code>--订单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);
- </code>
任务
答案
- <code>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;
- </code>
附:所有操作源码
- <code>/*
- 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‘);</code>
经典电商数据库分析构建(一)
标签:cad custom item 订单状态 select 卖出 arc set charset