Spring Boot EP 9：準備資料庫(MariaDB)及資料(市場指數及個股每日股價)

CREATE DATABASE `STOCK_MARKET` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

CREATE TABLE `MARKETS` (
  `MARKET_NAME` varchar(45),
  `MARKET_CODE` varchar(45) NOT NULL,
  PRIMARY KEY (`MARKET_NAME`,`MARKET_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `MARKETS_INDEX` (
  `MARKET_CODE` varchar(45) NOT NULL,
  `DATE_OF_TRADING` date NOT NULL,
  `OPEN_INDEX` decimal(10,2) DEFAULT NULL,
  `HIGHEST_INDEX` decimal(10,2) DEFAULT NULL,
  `LOWEST_INDEX` decimal(10,2) DEFAULT NULL,
  `CLOSE_INDEX` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`DATE_OF_TRADING`,`MARKET_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `STOCKS` (
  `STOCK_ID` varchar(4) CHARACTER SET utf8mb3 NOT NULL,
  `STOCK_NAME` varchar(45) DEFAULT NULL,
  `MARKET_CODE` varchar(45) NOT NULL,
  PRIMARY KEY (`STOCK_ID`,`MARKET_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `STOCKS_PRICE` (
  `DATE_OF_TRADING` date NOT NULL,
  `STOCK_ID` varchar(45) NOT NULL,
  `STOCK_DEAL_AMOUNT` decimal(20,0) DEFAULT NULL,
  `MONEY_DEAL_AMOUNT` decimal(20,0) DEFAULT NULL,
  `OPEN_PRICE` decimal(10,2) DEFAULT NULL,
  `HIGHEST_PRICE` decimal(10,2) DEFAULT NULL,
  `LOWEST_PRICE` decimal(10,2) DEFAULT NULL,
  `CLOSE_PRICE` decimal(10,2) DEFAULT NULL,
  `PRICE_GAP` decimal(10,2) DEFAULT NULL,
  `TRADING_AMOUNT` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`DATE_OF_TRADING`,`STOCK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;