9월 24일 토요일

[hyunminini]

@Bora0k

• [ ] 로그인, 로그인 필터

@hyunminini

• [ ] 게시물 작성 구현

@poviea

• [ ] 전체 게시판

[Bora0k]

• DB 데이터

  -- 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 AUTO_INCREMENT, 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 ; alter table board modify BOARD_CNUM int not null auto_increment;

---

-- Table pp.COMMENT

---

CREATE TABLE IF NOT EXISTS pp.COMMENT ( CO_NO INT NOT NULL AUTO_INCREMENT, 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_NO INT NOT NULL AUTO_INCREMENT, LIKE_COUNT INT , BOARD_CNUM INT NULL DEFAULT NULL, EMPNO INT NULL DEFAULT NULL, PRIMARY KEY (LIKE_NO), 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;

INSERT INTO DEPT(DEPTNO, DNAME) VALUE(10, "QualityTeam"), (20, "ProductionTeam"), (30, "MaterialT eam"), (40, "SalesTeam"), (50, "addmin");

insert into EMP(EMPNO, DEPTNO,PW,ENAME,GRADE,MGR,ADMIN_AUTHORITY,HATE_NUM,STOP_EMP) VALUE(1001,50,'ADMIN','ADMIN','ADMIN',1001,'ADMIN',0,0);

insert into EMP(EMPNO, DEPTNO,PW,ENAME,GRADE,MGR,ADMIN_AUTHORITY,HATE_NUM,STOP_EMP) VALUE(1002,10,'1234','강보라','LEADER',1002,'USER',0,0), (1003,10,'1234','김현민','SALE',1002,'USER',0,0), (1004,20,'1234','김민욱','LEADER',1004,'USER',0,0), (1005,20,'1234','정혜지','SALE',1004,'USER',0,0), (1006,30,'1234','김경욱','LEADER',1006,'USER',0,0), (1007,30,'1234','최정현','SALE',1006,'USER',0,0), (1008,40,'1234','황동하','LEADER',1008,'USER',0,0), (1009,40,'1234','손용민','SALE',1008,'USER',0,0) ;

INSERT INTO BOARD(EMPNO,TITLE,CONTENT,CATEGORY,WRITE_DATE) VALUE(1002, '퇴근하고 치맥하실분1', '오늘 퇴근하고 치맥하실분 계신가요1', '자유게시판', sysdate()), (1003, '퇴근하고 치맥하실분2', '오늘 퇴근하고 치맥하실분 계신가요2', '자유게시판', sysdate()), (1004, '퇴근하고 치맥하실분3', '오늘 퇴근하고 치맥하실분 계신가요5', '자유게시판', sysdate()), (1005, '점메추1', '점메추1', '점메추', sysdate()), (1006, '점메추2', '점메추2', '점메추', sysdate()), (1007, '점메추3', '점메추3', '점메추', sysdate()), (1008, '웃긴얘기1', '웃긴얘기1', '유머', sysdate()), (1009, '웃긴얘기2', '웃긴얘기2', '유머', sysdate()), (1003, '웃긴얘기3', '웃긴얘기3', '유머', sysdate());

INSERT INTO COMMENT(EMPNO,BOARD_CNUM,CO_CONTENT,CO_WRITE_DATE) VALUE(1003,1,'저요',sysdate()), (1004,1,'저도',sysdate()), (1005,1,'참석',sysdate()) ;

SELECT FROM EMP; SELECT FROM BOARD; SELECT * FROM COMMENT;

---

EMP

---

BOARD

---

COMMENT

[Bora0k]

로그인- 디비 연동하여 일치 확인