目录

• 一、商品分类表 ProductClass
• • 1. 创建 ProductClass表
  • 2. 插入数据
  • 3. 展示
  • 二、客户表 Customer记录
  • • 1. 创建 Customer表
    • 2. 插入数据
    • 3. 展示
    • 三、商品表 Product
    • • 1. 创建 Product表
      • 2. 插入数据
      • 3. 展示
      • 四、员工表Employee
      • • 1. 创建 Employee表
        • 2. 插入数据
        • 3. 展示
        • 五、订单主表 OrderMaster
        • • 1. 创建 OrderMaster表
          • 2. 插入数据
          • 3. 展示
          • 六、订单明细表 OrderDetail
          • • 1. 创建 OrderDetail表
            • 2. 插入数据
            • 3. 展示

              一、商品分类表 ProductClass

              1. 创建 ProductClass表

              CREATE TABLE `ProductClass` (
                `classNo` char(3) NOT NULL,
                `className` varchar(40) NOT NULL,
                PRIMARY KEY (`classNo`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `ProductClass` VALUES ('001', '手机');
              INSERT INTO `ProductClass` VALUES ('002', '电脑');
              INSERT INTO `ProductClass` VALUES ('003', '耳机');
              INSERT INTO `ProductClass` VALUES ('004', '小米手环');
              INSERT INTO `ProductClass` VALUES ('005', '摄像头');
              

              3. 展示

              

              
              

              二、客户表 Customer记录

              1. 创建 Customer表

              CREATE TABLE `Customer` (
                `customerNo` char(9) NOT NULL,
                `customer` char(40) NOT NULL,
                `address` varchar(40) NOT NULL,
                `telephone` varchar(20) NOT NULL,
                `zip` char(6) NOT NULL,
                PRIMARY KEY (`customerNo`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `Customer` VALUES ('C2023001', 'cool-wangtongzhou有限公司', '广州市', '011-1111', '1001');
              INSERT INTO `Customer` VALUES ('C2023002', '统一股份有限公司', '天津市', '022-2222', '1002');
              INSERT INTO `Customer` VALUES ('C2023003', '上海生物研究室', '上海市', '033-3333', '1003');
              INSERT INTO `Customer` VALUES ('C2023004', '腾讯有限公司', '深圳市', '044-4444', '1004');
              INSERT INTO `Customer` VALUES ('C2023005', '华为有限公司', '深圳市', '055-5555', '1005');
              INSERT INTO `Customer` VALUES ('C2023006', '字节跳动有限公司', '北京市', '066-6666', '1006');
              INSERT INTO `Customer` VALUES ('C2023007', '阿里巴巴有限公司', '杭州市', '077-7777', '1007');
              INSERT INTO `Customer` VALUES ('C2023008', '七牛云有限公司', '上海市', '088-8888', '1008');
              

              mysql> insert into Customer values ('C2023002', '统一股份有限公司','022-3566021',’天津市‘, 1002);
              ERROR 1054 (42S22): Unknown column '’天津市‘' in 'field list'
              

              如果遇到这种问题，就检查一下天津市对应的单引号是不是英文格式的，这个很容易弄混。

              3. 展示

              

              
              

              三、商品表 Product

              1. 创建 Product表

              CREATE TABLE `Product` (
                `product` char(9) NOT NULL,
                `productName` varchar(40) NOT NULL,
                `classNo` char(3) NOT NULL,
                `productPrice` decimal(7,2) NOT NULL,
                `productStock` decimal(7,2) NOT NULL,
                `productMinstock` decimal(7,2) NOT NULL,
                PRIMARY KEY (`product`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `Product` VALUES ('P2023001', '手机一号', '1', 2798.00, 100.00, 10.00);
              INSERT INTO `Product` VALUES ('P2023002', '手机二号', '1', 3999.00, 100.00, 10.00);
              INSERT INTO `Product` VALUES ('P2023003', '手机三号', '1', 3999.00, 50.00, 5.00);
              INSERT INTO `Product` VALUES ('P2023004', '电脑一号', '2', 3999.00, 10.00, 6.00);
              INSERT INTO `Product` VALUES ('P2023005', '电脑二号', '2', 3999.00, 15.00, 5.00);
              INSERT INTO `Product` VALUES ('P2023006', '电脑三号', '2', 6999.00, 35.00, 5.00);
              INSERT INTO `Product` VALUES ('P2023007', '耳机一号', '3', 2399.00, 500.00, 50.00);
              INSERT INTO `Product` VALUES ('P2023008', '耳机二号', '3', 1499.00, 500.00, 50.00);
              INSERT INTO `Product` VALUES ('P2023009', '手环一号', '4', 399.00, 210.00, 30.00);
              INSERT INTO `Product` VALUES ('P2023010', '手环二号', '4', 199.00, 32.00, 23.00);
              INSERT INTO `Product` VALUES ('P2023011', '摄像头一号', '5', 233.00, 23.00, 21.00);
              

              3. 展示

              

              
              

              四、员工表Employee

              1. 创建 Employee表

              CREATE TABLE `Employee` (
                `employeeNo` char(8) NOT NULL,
                `employeeName` varchar(10) NOT NULL,
                `sex` varchar(1) NOT NULL,
                `birthday` datetime NOT NULL,
                `address` varchar(50) NOT NULL,
                `telephone` varchar(20) NOT NULL,
                `hireDate` datetime DEFAULT NULL,
                `department` varchar(30) NOT NULL,
                `headShip` varchar(30) NOT NULL,
                `salary` decimal(8,2) NOT NULL,
                PRIMARY KEY (`employeeNo`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `Employee` VALUES ('E2023001', '员工一', 'M', '1985-01-01 00:00:00', '南京市', '1111', '2023-01-01 00:00:00', '财务科', '科长', 11000.00);
              INSERT INTO `Employee` VALUES ('E2023002', '员工二', 'F', '1986-02-02 00:00:00', '上海市', '2222', '2023-02-02 00:00:00', '业务科', '职员', 3000.00);
              INSERT INTO `Employee` VALUES ('E2023003', '员工三', 'F', '1987-03-03 00:00:00', '南昌市', '3333', '2023-03-03 00:00:00', '业务科', '职员', 3000.00);
              INSERT INTO `Employee` VALUES ('E2023004', '员工四', 'M', '1988-04-04 00:00:00', '南昌市', '4444', '2023-04-04 00:00:00', '业务科', '职员', 5000.00);
              INSERT INTO `Employee` VALUES ('E2023005', '员工五', 'F', '1989-05-05 00:00:00', '上海市', '5555', '2023-05-05 00:00:00', '办公室', '主任', 9000.00);
              INSERT INTO `Employee` VALUES ('E2023006', '员工六', 'M', '1990-06-06 00:00:00', '广州市', '6666', '2023-06-06 00:00:00', '财务科', '会计', 10000.00);
              

              3. 展示

              

              
              

              五、订单主表 OrderMaster

              1. 创建 OrderMaster表

              CREATE TABLE `ordermaster` (
                `orderNo` char(12) NOT NULL,
                `customerNo` char(9) NOT NULL,
                `salerNo` char(8) NOT NULL,
                `orderDate` datetime NOT NULL,
                `orderSum` decimal(9,2) NOT NULL,
                `invoiceNo` char(10) NOT NULL,
                PRIMARY KEY (`orderNo`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `ordermaster` VALUES ('2023001', 'C2023001', 'E2023001', '2023-01-01 00:00:00', 1111.00, 'I001');
              INSERT INTO `ordermaster` VALUES ('2023002', 'C2023002', 'E2023002', '2023-02-02 00:00:00', 2222.00, 'I002');
              INSERT INTO `ordermaster` VALUES ('2023003', 'C2023003', 'E2023003', '2023-03-03 00:00:00', 3333.00, 'I003');
              INSERT INTO `ordermaster` VALUES ('2023004', 'C2023004', 'E2023004', '2023-04-04 00:00:00', 4444.00, 'I004');
              INSERT INTO `ordermaster` VALUES ('2023005', 'C2023005', 'E2023005', '2023-05-05 00:00:00', 5555.00, 'I005');
              INSERT INTO `ordermaster` VALUES ('2023006', 'C2023006', 'E2023006', '2023-06-06 00:00:00', 6666.00, 'I006');
              

              3. 展示

              

              
              

              六、订单明细表 OrderDetail

              1. 创建 OrderDetail表

              CREATE TABLE `OrderDetail` (
                `orderNo` char(12) NOT NULL,
                `productNo` char(9) NOT NULL,
                `quantity` int NOT NULL,
                `price` decimal(7,2) NOT NULL,
                PRIMARY KEY (`orderNo`)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
              

              2. 插入数据

              INSERT INTO `OrderDetail` VALUES ('2023001', 'P2023001', 1, 3000.00);
              INSERT INTO `OrderDetail` VALUES ('2023002', 'P2023002', 2, 4000.00);
              INSERT INTO `OrderDetail` VALUES ('2023003', 'P2023003', 3, 3000.00);
              INSERT INTO `OrderDetail` VALUES ('2023004', 'P2023004', 4, 5000.00);
              INSERT INTO `OrderDetail` VALUES ('2023005', 'P2023005', 5, 6000.00);
              INSERT INTO `OrderDetail` VALUES ('2023006', 'P2023006', 6, 2000.00);
              

              3. 展示