2023 - 07 - 15 쿼리문 Query - 테이블 생성, 수정, 삭제, 조회

 

로우쿼리로 데이터베이스 생성해보기.

 

 

CREATE TABLE Customers (
    customerId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE Items (
    itemId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    optionId BIGINT NOT NULL DEFAULT 0,
    name VARCHAR(255) NOT NULL UNIQUE,
    price BIGINT NOT NULL,
    type ENUM('COFFEE', 'TEA', 'JUICE', 'DESSERT', 'SMOOTHIE') NOT NULL,
    amount BIGINT NOT NULL DEFAULT 0,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (optionId) REFERENCES Options (optionId)
  );

  CREATE TABLE Managers (
    managerId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE Options (
    optionId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    extraPrice INT NOT NULL,
    shotPrice INT NOT NULL,
    is_hot BOOLEAN NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE OrderItems (
    orderItemId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    itemId BIGINT NOT NULL,
    orderId BIGINT NOT NULL,
    amount BIGINT NOT NULL DEFAULT 0,
    price BIGINT NOT NULL,
    optionis JSON NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  )

  CREATE TABLE Orders (
    orderId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId BIGINT NOT NULL,
    is_User BOOLEAN NOT NULL,
    state ENUM('ORDERED', 'PENDING', 'COMPLETED', 'CANCELED') NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  
  FOREIGN KEY (customerId) REFERENCES Customers (customerId)
  FOREIGN KEY (orderId) REFERENCES OrderItems (orderId)
  );