mysql - 将数据插入表中

问题描述

大家好，我有一个非常基本的问题，但我无法在我的语法中找到错误的地方。我应该创建一个 concat 消息，显示哪些项目在特定约束下打折。第一步是添加满足要求的数据。在这种情况下，它是在 2019 年 4 月 30 日之后创建的订单，如下所示

INSERT INTO ORDERS
   (ORDER_ID, CUSTOMER_CODE, EMPLOYEE_ID, ORDER_DATE, REQUIRED_DATE, SHIPPED_DATE, SHIP_VIA, FREIGHT, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_REGION, SHIP_POSTAL_CODE, SHIP_COUNTRY)
  VALUES
   (4000,'SAVEA', 4, STR_TO_DATE('01/05/2019', '%m/%d/%Y'), STR_TO_DATE('01/05/2019', '%m/%d/%Y'),
    STR_TO_DATE('02/28/2019', '%m/%d/%Y'), 'Ausralia Post', 15.63, 'Titanic', '185 League Street.',
    'Sydney', 'AB', '56728', 'Australia');

    INSERT INTO ORDER_DETAIL
    ( ORDER_ID, PRODUCT_NAME ,UNIT_PRICE, QUANTITY, DISCOUNT )
    VALUES
    (4000, 'Konbu',6,10,0);

    INSERT INTO ORDER_DETAIL
    ( ORDER_ID, PRODUCT_NAME ,UNIT_PRICE, QUANTITY, DISCOUNT )
    VALUES
    (4000, 'Alice Mutton',39,10,0);

下一步是创建一个表来存储消息


CREATE TABLE MESSAGES (

SIZE VARCHAR(500) NOT NULL


);

我认为我的语法错误所在的下一步是创建 concat 消息本身

  INSERT INTO MESSAGE  
  SELECT CONCAT('Order', ORDERS.ORDER_ID, 'submitted on',ORDERS.ORDER_DATE,'includes  a discontinued product', ORDER_DETAIL.PRODUCT_NAME)
  FROM PRODUCT
  JOIN ORDER_DETAIL ON PRODUCT.PRODUCT_NAME = ORDER_DETAIL.PRODUCT_NAME JOIN ORDERS ON ORDERS.ORDER_ID = ORDER_DETAIL.ORDER_ID
  WHERE PRODUCT.DISCONTINUED = 'N' AND ORDERS.ORDER_DATE > STR_TO_DATE('04/30/2019','%m/%d/%Y');

我遵循一个通过的评估示例到 T 恤，我不明白为什么它没有带来任何结果。下面生病张贴使用的表格

CREATE TABLE PRODUCT
(
    PRODUCT_NAME    VARCHAR(40)     NOT NULL,
    SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
    CATEGORY_NAME   VARCHAR(30) NOT NULL,
    QUANTITY_PER_UNIT   VARCHAR(20),
    UNIT_PRICE      DECIMAL(10,2)   NOT NULL    DEFAULT 0,
    UNITS_IN_STOCK  DECIMAL(9)  NOT NULL    DEFAULT 0,
    UNITS_ON_ORDER  DECIMAL(9)  NOT NULL    DEFAULT 0, 
    REORDER_LEVEL   DECIMAL(9)  NOT NULL    DEFAULT 0,
    DISCONTINUED    CHAR(1)     NOT NULL    DEFAULT 'N',
    CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
    CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
    CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
    CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
    CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
    CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

CREATE TABLE ORDERS
(
    ORDER_ID        DECIMAL(9)  NOT NULL,
    CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
    EMPLOYEE_ID     DECIMAL(9)  NOT NULL,
    ORDER_DATE      DATE        NOT NULL,
    REQUIRED_DATE   DATE,
    SHIPPED_DATE    DATE,
    SHIP_VIA        VARCHAR(40),
    FREIGHT         DECIMAL(10,2)           DEFAULT 0,
    SHIP_NAME       VARCHAR(40),
    SHIP_ADDRESS    VARCHAR(60),
    SHIP_CITY       VARCHAR(15),
    SHIP_REGION     VARCHAR(15),
    SHIP_POSTAL_CODE    VARCHAR(10),
    SHIP_COUNTRY    VARCHAR(15),
    CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),  
    CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  
    CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)  
);

CREATE TABLE ORDER_DETAIL
(
    ORDER_ID         DECIMAL(9) NOT NULL,
    PRODUCT_NAME     VARCHAR(40)    NOT NULL,
    UNIT_PRICE       DECIMAL(10,2)  NOT NULL    DEFAULT 0,
    QUANTITY         DECIMAL(9) NOT NULL    DEFAULT 1 ,
    DISCOUNT         DECIMAL(4,2)   NOT NULL    DEFAULT 0,
    CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
    CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
    CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),
    CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
    CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
    CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1)
);

标签： mysql

mysql - 将数据插入表中

问题描述

解决方案

推荐阅读