Javaweb毕业设计项目之小米商城(Servlet+Layui+Mybatis) Part3之表设计的完整sql文件和查询测试

朝饮暮酒

已于 2023-11-29 22:28:50 修改

阅读量145

点赞数

分类专栏： JavaWeb毕业设计文章标签：课程设计 servlet layui

于 2023-11-28 19:42:08 首次发布

本文链接：https://blog.csdn.net/weixin_44857328/article/details/134675147

版权

JavaWeb毕业设计专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

在这里插入图片描述

如果看了前面的内容的话，那么理清了顺序后，可以执行进行测试一下

1.新建查询

联立查询分类表下和产品表下相关连的信息
SELECT * from category c,product p where c.cid=p.cid

多表查询 一个商品对应的版本信息

表：product p产品  版本version v产品版本product_version pv
条件 p.pid=pv.pid and v.vid=pv.vid

SELECT * from 
product p,version v,product_version pv
WHERE
p.pid=pv.pid and v.vid=pv.vid

多表查询 一个商品所对应的版本信息和颜色信息
表：product p产品  版本version v产品版本product_version pv  颜色表 color cl 产品颜色  product_color pc
条件 p.pid=pv.pid and v.vid=pv.vid and cl.clid=pc.clid

筛选条件
SELECT 
p.*,v.vname,v.vprice,cl.clname,cl.cprice
from 
product p,version v,product_version pv,color cl,product_color pc
WHERE
p.pid=pv.pid and v.vid=pv.vid 
and pc.pid=p.pid  and cl.clid=pc.clid

2.完整sql

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50527
Source Host           : localhost:3306
Source Database       : shopping

Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding         : 65001

Date: 2023-11-29 09:44:59
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
  `adminID` int(11) NOT NULL,
  `auser` varchar(255) NOT NULL,
  `apass` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`adminID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of admin
-- ----------------------------

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '分类主键  编号',
  `cname` varchar(50) NOT NULL COMMENT '分类名称',
  `cdesc` varchar(200) NOT NULL COMMENT '分类描述',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('1', '小米手机', '手机');
INSERT INTO `category` VALUES ('2', 'Redmi手机', '红米相关产品');
INSERT INTO `category` VALUES ('3', 'Redmi', '红米平板');

-- ----------------------------
-- Table structure for color
-- ----------------------------
DROP TABLE IF EXISTS `color`;
CREATE TABLE `color` (
  `clid` int(11) NOT NULL AUTO_INCREMENT,
  `clname` varchar(50) NOT NULL COMMENT '颜色的名称',
  `cprice` double(11,0) NOT NULL,
  PRIMARY KEY (`clid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of color
-- ----------------------------
INSERT INTO `color` VALUES ('1', '子夜黑', '0');
INSERT INTO `color` VALUES ('2', '晴雪白', '0');
INSERT INTO `color` VALUES ('3', '香芋紫', '100');
INSERT INTO `color` VALUES ('4', '香槟金', '200');

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `mid` int(11) NOT NULL,
  `mname` varchar(20) NOT NULL COMMENT '模块的名称',
  `mdesc` varchar(200) NOT NULL COMMENT '菜单功能描述',
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of menu
-- ----------------------------

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `orid` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  `ostatus` int(11) NOT NULL DEFAULT '0' COMMENT '1表示已支付 0未支付',
  `ototal` double(10,0) NOT NULL DEFAULT '0',
  `svipid` int(11) DEFAULT NULL COMMENT '外键 会员表的主键',
  PRIMARY KEY (`orid`),
  KEY `svipid` (`svipid`),
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`svipid`) REFERENCES `svip` (`svipid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1000001', '0', '4999', '1000001');

-- ----------------------------
-- Table structure for orderitem
-- ----------------------------
DROP TABLE IF EXISTS `orderitem`;
CREATE TABLE `orderitem` (
  `oiid` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单项的主键',
  `onum` int(11) DEFAULT NULL COMMENT '订单项一条的商品数量',
  `ocount` double NOT NULL COMMENT '订单项一条的商品小计',
  `pid` int(11) NOT NULL COMMENT '外键产品表的主键',
  `orid` int(11) NOT NULL COMMENT '外键 订单表的主键',
  PRIMARY KEY (`oiid`),
  KEY `pid` (`pid`) USING BTREE,
  KEY `orid` (`orid`),
  CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`),
  CONSTRAINT `orderitem_ibfk_2` FOREIGN KEY (`orid`) REFERENCES `order` (`orid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of orderitem
-- ----------------------------
INSERT INTO `orderitem` VALUES ('1000001', '1', '4999', '10000001', '1000001');

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `pid` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品编号',
  `pname` varchar(50) NOT NULL COMMENT '产品名称',
  `pprice` double(10,2) NOT NULL COMMENT '产品价格',
  `pimage` varchar(255) NOT NULL DEFAULT 'productImage/1.jpg',
  `plast` int(10) NOT NULL COMMENT '产品剩余数量',
  `cid` int(11) DEFAULT NULL COMMENT '外键  分类表的主键',
  PRIMARY KEY (`pid`),
  KEY `cid` (`cid`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=10000002 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('10000001', '小米15proMax', '4999.00', 'productImage/1.jpg', '1000000', '1');

-- ----------------------------
-- Table structure for product_color
-- ----------------------------
DROP TABLE IF EXISTS `product_color`;
CREATE TABLE `product_color` (
  `pcid` int(11) NOT NULL AUTO_INCREMENT COMMENT '关联了 商品表和颜色表',
  `pid` int(11) NOT NULL,
  `clid` int(11) NOT NULL,
  PRIMARY KEY (`pcid`),
  KEY `pid` (`pid`),
  KEY `clid` (`clid`),
  CONSTRAINT `product_color_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`),
  CONSTRAINT `product_color_ibfk_2` FOREIGN KEY (`clid`) REFERENCES `color` (`clid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of product_color
-- ----------------------------
INSERT INTO `product_color` VALUES ('1', '10000001', '1');
INSERT INTO `product_color` VALUES ('2', '10000001', '4');

-- ----------------------------
-- Table structure for product_version
-- ----------------------------
DROP TABLE IF EXISTS `product_version`;
CREATE TABLE `product_version` (
  `pvid` int(11) NOT NULL AUTO_INCREMENT COMMENT '关联表的主键 版本表和产品表',
  `pid` int(11) NOT NULL,
  `vid` int(11) NOT NULL,
  PRIMARY KEY (`pvid`),
  KEY `pid` (`pid`),
  KEY `vid` (`vid`),
  CONSTRAINT `product_version_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`),
  CONSTRAINT `product_version_ibfk_2` FOREIGN KEY (`vid`) REFERENCES `version` (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of product_version
-- ----------------------------
INSERT INTO `product_version` VALUES ('1', '10000001', '1');
INSERT INTO `product_version` VALUES ('2', '10000001', '2');
INSERT INTO `product_version` VALUES ('3', '10000001', '3');
INSERT INTO `product_version` VALUES ('4', '10000001', '4');

-- ----------------------------
-- Table structure for svip
-- ----------------------------
DROP TABLE IF EXISTS `svip`;
CREATE TABLE `svip` (
  `svipid` int(11) NOT NULL AUTO_INCREMENT COMMENT '会员表主键',
  `susername` varchar(11) NOT NULL,
  `spassword` varchar(16) NOT NULL,
  `stel` varchar(11) DEFAULT NULL COMMENT '电话号码',
  `snick` varchar(20) NOT NULL COMMENT '昵称',
  `sgender` int(1) NOT NULL DEFAULT '1' COMMENT '1为男0为女',
  `sbirth` date NOT NULL COMMENT '生日 只需要时分秒',
  `saddr` varchar(200) NOT NULL DEFAULT '湖南省长沙市岳麓区枫林三路' COMMENT '地址',
  PRIMARY KEY (`svipid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of svip
-- ----------------------------
INSERT INTO `svip` VALUES ('1000001', 'zhuzhuxia', '123456', '17612341234', '猪猪侠', '1', '2004-06-01', '湖南省长沙市岳麓区枫林三路');

-- ----------------------------
-- Table structure for version
-- ----------------------------
DROP TABLE IF EXISTS `version`;
CREATE TABLE `version` (
  `vid` int(10) NOT NULL AUTO_INCREMENT,
  `vname` varchar(50) NOT NULL DEFAULT '' COMMENT '版本名称',
  `vdesc` varchar(255) NOT NULL COMMENT '版本描述',
  `vprice` double(10,2) NOT NULL DEFAULT '200.00',
  PRIMARY KEY (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of version
-- ----------------------------
INSERT INTO `version` VALUES ('1', '8GB+128GB', '基础版本', '0.00');
INSERT INTO `version` VALUES ('2', '8GB+256GB', '升级版本', '200.00');
INSERT INTO `version` VALUES ('3', '16GB+512GB', '增强版本', '400.00');
INSERT INTO `version` VALUES ('4', '32GB+512GB', '最强版本', '600.00');

sss