9월23일 금요일

[hyunminini]

• [ ] DB 구조 정립
• [ ] 웹 구조

DB 구조 정립

[poviea]

[hyunminini]

DEPT TABLE

create table dept (
    deptno int primary key ,
    dname VARCHAR(20) not null
);

INSERT INTO dept(deptno, dname) 
VALUE(10, "QualityTeam"),
(20, "ProductionTeam"),
(30, "MaterialTeam"),
(40, "SalesTeam"),
(50, "addmin");

[poviea]

BOARD TABLE

drop table board;

create table board (
    cnum int auto_increment  primary key,
    empno int,
    title VARCHAR(100)not null,
    content longtext not null,
    uploadfile blob,
    category varchar(20) not null,
    readnum int default '0',
    writedate   timestamp default current_timestamp(),
    modifydate  timestamp,
    deldate timestamp,
    comment longtext
);

insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1001, '퇴근하고 치맥하실분1', '오늘 퇴근하고 치맥하실분 계신가요1', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아1');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1002, '퇴근하고 치맥하실분2', '오늘 퇴근하고 치맥하실분 계신가요2', '점메추', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아2');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1003, '웃긴얘기', '웃김', '유머', sysdate(), sysdate(), '노잼');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1004, '퇴근하고 치맥하실분4', '오늘 퇴근하고 치맥하실분 계신가요4', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아4');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1005, '퇴근하고 치맥하실분5', '오늘 퇴근하고 치맥하실분 계신가요5', '유머', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아5');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1006, '퇴근하고 치맥하실분6', '오늘 퇴근하고 치맥하실분 계신가요6', '유머', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아6');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1007, '퇴근하고 치맥하실분7', '오늘 퇴근하고 치맥하실분 계신가요7', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아7');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1008, '퇴근하고 치맥하실분8', '오늘 퇴근하고 치맥하실분 계신가요8', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아8');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1009, '퇴근하고 치맥하실분9', '오늘 퇴근하고 치맥하실분 계신가요9', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아9');
insert into board (empno, title, content, category, modifydate, deldate, comment) 
values(1010, '퇴근하고 치맥하실분10', '오늘 퇴근하고 치맥하실분 계신가요10', '자유게시판', sysdate(), sysdate(), '저요저요 저랑 같이 먹어요 치킨이 너무 좋아10');

select * from board;

[Bora0k]

오전 11:30 DB -> AWS .Io figma . ppt - > 웹 디자인.구조 정리 오후 파트 나눔 DB -> AWS

5:30 : 깃정리

[Bora0k]

[Bora0k]

-- MySQL Workbench Forward Engineering use pp;

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE SCHEMA IF NOT EXISTS pp DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;

delete from detp; delete from emp; delete from board; delete from file_tbl; delete from hate_tbl; delete from like_tbl;

select from detp; select from emp; select from board; select from file_tbl; select from hate_tbl; select from like_tbl;

-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

---

-- Schema mydb

---

---

-- Schema pp

---

---

-- Schema pp

---

CREATE SCHEMA IF NOT EXISTS pp DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;

---

-- Table pp.dept

---

CREATE TABLE IF NOT EXISTS pp.dept ( deptno INT NOT NULL, dname VARCHAR(20) NOT NULL, PRIMARY KEY (deptno)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

desc dept;

---

-- Table pp.emp

---

CREATE TABLE IF NOT EXISTS pp.emp ( empno INT NOT NULL, deptno INT NULL DEFAULT NULL, pw VARCHAR(30) NOT NULL, ename VARCHAR(20) NOT NULL, grade VARCHAR(20) NOT NULL, mgr INT NOT NULL, ADMIN_AUTHORITY VARCHAR(45) NOT NULL, HATE_NUM INT NULL, STOP_EMP TINYINT(1) NULL, PRIMARY KEY (empno), INDEX deptno_idx (deptno ASC) VISIBLE, CONSTRAINT deptno FOREIGN KEY (deptno) REFERENCES pp.dept (deptno) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

---

-- Table pp.board

---

CREATE TABLE IF NOT EXISTS pp.board ( board_cnum INT NOT NULL, empno INT NULL DEFAULT NULL, UPLOAD_FILE VARCHAR(255) NULL, title VARCHAR(100) NOT NULL, content LONGTEXT NOT NULL, CATEGORY VARCHAR(20) NOT NULL, readnum INT NULL DEFAULT '0', WRITE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY_DATE TIMESTAMP NULL DEFAULT NULL, DEL_DATE TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (board_cnum), INDEX empno_idx (empno ASC) VISIBLE, CONSTRAINT empno FOREIGN KEY (empno) REFERENCES pp.emp (empno) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

USE pp ;

---

-- Table pp.COMMENT

---

CREATE TABLE IF NOT EXISTS pp.COMMENT ( CO_NO INT NOT NULL, EMPNO INT NULL, BOARD_CNUM INT NULL, CO_CONTENT VARCHAR(255) NOT NULL, CO_WRITE_DATE TIMESTAMP NULL, CO_HATE INT NULL, PRIMARY KEY (CO_NO), INDEX ENPNO_idx (EMPNO ASC) VISIBLE, INDEX BOARD_CUM_idx (BOARD_CNUM ASC) VISIBLE, CONSTRAINT ENPNO FOREIGN KEY (EMPNO) REFERENCES pp.emp (empno) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT COM_BOARD_CUM FOREIGN KEY (BOARD_CNUM) REFERENCES pp.board (board_cnum) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table pp.hate_tbl

---

CREATE TABLE IF NOT EXISTS pp.hate_tbl ( HATE_NO INT NOT NULL AUTO_INCREMENT, EMPNO INT NULL, HATE_COUNT INT NULL, BOARD_CUM INT NULL, HATE_REASON VARCHAR(45) NULL, DE_HATE_NO INT NULL, HATE_DATE TIMESTAMP NULL, CO_HATE INT NULL, PRIMARY KEY (HATE_NO), INDEX EMPNO_idx (EMPNO ASC) VISIBLE, INDEX BOARD_CNUM_idx (BOARD_CUM ASC) VISIBLE, INDEX DEHATE_NO_idx (DE_HATE_NO ASC, EMPNO ASC) VISIBLE, INDEX CO_HATE_idx (CO_HATE ASC) VISIBLE, CONSTRAINT HA_EMPNO FOREIGN KEY (EMPNO) REFERENCES pp.emp (empno) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT HA_BOARD_CNUM FOREIGN KEY (BOARD_CUM) REFERENCES pp.board (board_cnum) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT DE_HATE_NO FOREIGN KEY (DE_HATE_NO , EMPNO) REFERENCES pp.emp (empno , empno) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT CO_HATE FOREIGN KEY (CO_HATE) REFERENCES pp.COMMENT (CO_NO) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

---

-- Table pp.like_tbl

---

CREATE TABLE IF NOT EXISTS pp.like_tbl ( LIKE_COUNT INT NOT NULL AUTO_INCREMENT, BOARD_CNUM INT NULL DEFAULT NULL, empno INT NULL DEFAULT NULL, PRIMARY KEY (LIKE_COUNT), INDEX BOARD_CNUM_idx (BOARD_CNUM ASC) VISIBLE, INDEX EMPNO_idx (empno ASC) VISIBLE, CONSTRAINT LI_EMPNO FOREIGN KEY (empno) REFERENCES pp.emp (empno) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

---

-- Table pp.FILE

---

CREATE TABLE IF NOT EXISTS pp.FILE ( file_id INT NOT NULL, board_cnum INT NULL, file_original_name VARCHAR(45) NULL, file_type VARCHAR(45) NULL, file_size VARCHAR(255) NULL, upload_date TIMESTAMP NULL, file_path VARCHAR(255) NULL, file_save_name VARCHAR(255) NULL, PRIMARY KEY (file_id), INDEX board_cnum_idx (board_cnum ASC) VISIBLE, CONSTRAINT board_cnum FOREIGN KEY (board_cnum) REFERENCES pp.board (board_cnum) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

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

[Bora0k]

최종 DB query

-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema pp
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema pp
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pp` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;

-- -----------------------------------------------------
-- Table `pp`.`dept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`dept` (
  `deptno` INT NOT NULL,
  `dname` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`deptno`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

-- -----------------------------------------------------
-- Table `pp`.`emp`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`emp` (
  `empno` INT NOT NULL,
  `deptno` INT NULL DEFAULT NULL,
  `pw` VARCHAR(30) NOT NULL,
  `ename` VARCHAR(20) NOT NULL,
  `grade` VARCHAR(20) NOT NULL,
  `mgr` INT NOT NULL,
  `ADMIN_AUTHORITY` VARCHAR(45) NOT NULL,
  `HATE_NUM` INT NULL,
  `STOP_EMP` TINYINT(1) NULL,
  PRIMARY KEY (`empno`),
  CONSTRAINT `fk_dept_emp`
    FOREIGN KEY (`deptno`)
    REFERENCES `pp`.`dept` (`deptno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE INDEX `deptno_idx` ON `pp`.`emp` (`deptno` ASC) VISIBLE;

-- -----------------------------------------------------
-- Table `pp`.`board`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`board` (
  `board_cnum` INT NOT NULL,
  `empno` INT NULL DEFAULT NULL,
  `UPLOAD_FILE` VARCHAR(255) NULL,
  `title` VARCHAR(100) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `CATEGORY` VARCHAR(20) NOT NULL,
  `readnum` INT NULL DEFAULT '0',
  `WRITE_DATE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `MODIFY_DATE` TIMESTAMP NULL DEFAULT NULL,
  `DEL_DATE` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`board_cnum`),
  CONSTRAINT `fk_empno_board`
    FOREIGN KEY (`empno`)
    REFERENCES `pp`.`emp` (`empno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE INDEX `empno_idx` ON `pp`.`board` (`empno` ASC) VISIBLE;

USE `pp` ;

-- -----------------------------------------------------
-- Table `pp`.`COMMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`COMMENT` (
  `CO_NO` INT NOT NULL,
  `EMPNO` INT NULL,
  `BOARD_CNUM` INT NULL,
  `CO_CONTENT` VARCHAR(255) NOT NULL,
  `CO_WRITE_DATE` TIMESTAMP NULL,
  `CO_HATE` INT NULL,
  PRIMARY KEY (`CO_NO`),
  CONSTRAINT `ENPNO`
    FOREIGN KEY (`EMPNO`)
    REFERENCES `pp`.`emp` (`empno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `BOARD_CUM`
    FOREIGN KEY (`BOARD_CNUM`)
    REFERENCES `pp`.`board` (`board_cnum`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `ENPNO_idx` ON `pp`.`COMMENT` (`EMPNO` ASC) VISIBLE;

CREATE INDEX `BOARD_CUM_idx` ON `pp`.`COMMENT` (`BOARD_CNUM` ASC) VISIBLE;

-- -----------------------------------------------------
-- Table `pp`.`hate_tbl`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`hate_tbl` (
  `HATE_NO` INT NOT NULL AUTO_INCREMENT,
  `EMPNO` INT NULL,
  `HATE_COUNT` INT NULL,
  `BOARD_CUM` INT NULL,
  `HATE_REASON` VARCHAR(45) NULL,
  `DE_HATE_NO` INT NULL,
  `HATE_DATE` TIMESTAMP NULL,
  `CO_HATE` INT NULL,
  PRIMARY KEY (`HATE_NO`),
  CONSTRAINT `fk_emp_hate_empno`
    FOREIGN KEY (`EMPNO`)
    REFERENCES `pp`.`emp` (`empno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_board_hate`
    FOREIGN KEY (`BOARD_CUM`)
    REFERENCES `pp`.`board` (`board_cnum`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_emp_de_hate_no`
    FOREIGN KEY (`EMPNO`)
    REFERENCES `pp`.`emp` (`empno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_comment_co_hate`
    FOREIGN KEY (`CO_HATE`)
    REFERENCES `pp`.`COMMENT` (`CO_NO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE INDEX `EMPNO_idx` ON `pp`.`hate_tbl` (`EMPNO` ASC) VISIBLE;

CREATE INDEX `BOARD_CNUM_idx` ON `pp`.`hate_tbl` (`BOARD_CUM` ASC) VISIBLE;

CREATE INDEX `CO_HATE_idx` ON `pp`.`hate_tbl` (`CO_HATE` ASC) VISIBLE;

-- -----------------------------------------------------
-- Table `pp`.`like_tbl`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`like_tbl` (
  `LIKE_COUNT` INT NOT NULL AUTO_INCREMENT,
  `BOARD_CNUM` INT NULL DEFAULT NULL,
  `empno` INT NULL DEFAULT NULL,
  PRIMARY KEY (`LIKE_COUNT`),
  CONSTRAINT `fk_board_like_tbl`
    FOREIGN KEY (`BOARD_CNUM`)
    REFERENCES `pp`.`board` (`board_cnum`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_emp_like_tbl`
    FOREIGN KEY (`empno`)
    REFERENCES `pp`.`emp` (`empno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE INDEX `BOARD_CNUM_idx` ON `pp`.`like_tbl` (`BOARD_CNUM` ASC) VISIBLE;

CREATE INDEX `EMPNO_idx` ON `pp`.`like_tbl` (`empno` ASC) VISIBLE;

-- -----------------------------------------------------
-- Table `pp`.`FILE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pp`.`FILE` (
  `file_id` INT NOT NULL,
  `board_cnum` INT NULL,
  `file_original_name` VARCHAR(45) NULL,
  `file_type` VARCHAR(45) NULL,
  `file_size` VARCHAR(255) NULL,
  `upload_date` TIMESTAMP NULL,
  `file_path` VARCHAR(255) NULL,
  `file_save_name` VARCHAR(255) NULL,
  PRIMARY KEY (`file_id`),
  CONSTRAINT `board_cnum`
    FOREIGN KEY (`board_cnum`)
    REFERENCES `pp`.`board` (`board_cnum`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE INDEX `board_cnum_idx` ON `pp`.`FILE` (`board_cnum` ASC) VISIBLE;

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

[hyunminini]

pp_project.zip

[poviea]

step10_JDBC.zip