[MySQL] 정리

[MySQL] 정리

 

MySQL Workbench 실행 

-> CREATE SCHEMA 'mydatabase' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

(속도 빠른 방법)

 

SET NAMES utf8mb4;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS carhartt;
CREATE SCHEMA carhartt;
USE carhartt;

--
-- Table structure for table `member`
--

CREATE TABLE mem (
  m_id INT NOT NULL AUTO_INCREMENT,
  log_id VARCHAR(20) NOT NULL,
  log_pw VARCHAR(20) NOT NULL,
  m_tell VARCHAR(20) NOT NULL,
  m_sex VARCHAR(20) NOT NULL,
  m_name VARCHAR(20) NOT NULL,
  PRIMARY KEY  (m_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `product`
--

CREATE TABLE product (
  p_id INT NOT NULL AUTO_INCREMENT,
  p_name VARCHAR(20) NOT NULL,
  p_color VARCHAR(20) NOT NULL,
  p_price INT NOT NULL,
  PRIMARY KEY  (p_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `order_p`
--

CREATE TABLE order_p (
  o_id INT NOT NULL AUTO_INCREMENT,
  o_date TIMESTAMP DEFAULT NOW(),
  o_price INT NOT NULL,
  o_t_price INT NOT NULL,
  o_cnt INT NOT NULL,
  o_code INT NOT NULL,
  o_state VARCHAR(50) NOT NULL,
  o_state_num INT NOT NULL,
  m_id INT NOT NULL,
  p_id INT NOT NULL,
  PRIMARY KEY  (o_id),
  FOREIGN KEY (m_id) REFERENCES mem (m_id),
  FOREIGN KEY (p_id) REFERENCES product (p_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pay`
--

CREATE TABLE pay (
  pay_id INT NOT NULL AUTO_INCREMENT,
  pay_date TIMESTAMP DEFAULT NOW(),
  pay_price INT NOT NULL,
  pay_state VARCHAR(50) NOT NULL,
  pay_state_num INT NOT NULL,
  m_id INT NOT NULL,
  PRIMARY KEY  (pay_id),
  FOREIGN KEY (m_id) REFERENCES mem (m_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

CREATE TABLE tbl_cstVSBoard(
b_id INT NOT NULL AUTO_INCREMENT,       
writer VARCHAR(20) NOT NULL,
pwd VARCHAR(20) NOT NULL, 
email VARCHAR(100),
title VARCHAR(200) NOT NULL,
writedate TIMESTAMP DEFAULT NOW(),
readed INT DEFAULT 0,
tag INT DEFAULT 0,
content TEXT,
PRIMARY KEY (b_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE free_return_request_history (

	free_return_request_history_no INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,

	member_no INT(10) UNSIGNED NOT NULL ,

	member_id VARCHAR(50) NOT NULL ,

	seller_no INT(10) UNSIGNED NOT NULL ,

	seller_id VARCHAR(50) NOT NULL ,

	order_no  VARCHAR(50) NOT NULL ,

	order_item_no INT(10) UNSIGNED NOT NULL ,

	item_no INT(10) UNSIGNED NOT NULL ,

	ins_date DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ,

	ins_oprt VARCHAR(50) NOT NULL ,

	upd_date DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) ,

	upd_oprt VARCHAR(50) NOT NULL ,

	PRIMARY KEY (free_return_request_history_no),

	KEY ix_ins_date (ins_date),

	KEY ix_upd_date (upd_date)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;