흰색기린

MySql DB 만들기

프론트하고 앱에 db가 필요하므로 db를 빠르게 설계해야 한다.

회원, 게시판, 일기장 db를 완성했다.

Oracle sql과 다르게 Mysql은 시퀀스 생성 방법이 달랐다.

쿼리를 만들고 저장하려고 했으나 에러가 발생했음

파일이 저장안돼서 직접 복붙으로. sql파일에 일단 넣어줬음..

erd를 mysql에서 확인해보자

댓글은 댓글 번호도 필요하지만 게시글 번호도 필요하다

프라이머리 키 하고 외부 키는 어떻게 설정할까?

댓글은 게시글의 내용을 알아야 하니깐 게시글을 쓴 writer을 포린 키로 가지고 있어야 하나?

FOREIGN 키를 설정할 때에는 참조되는 테이블의 필드가 반드시 UNIQUE나 PRIMARY KEY가 설정되어 있어야 한다.

이미 만들어진 테이블이 있으면 위에 코드를 이용해서 추가시켜줘야 한다.

제약조건(constraint)란?

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

5. DEFAULT

음.. 이렇게 하나하나 다쳐보니깐 또 만들기가 어렵다.

MySql의 Workbench를 이용하니깐 백 틱(`)을 이용해서 테이블명을 감싸는데 

FK를 추가하려고 하니깐 안된다.. 왜 안되지?

테이블을 수정할 수 있는 것을 찾았다. 그리고 하단에 Foreign Keys가 있는데

이걸 사용해봐서 만들고 문법이 어느 부분이 틀렸는지 확인해본다!!

FK를 추가하기 전에 어느 곳에 FK를 넣어야 하는지 생각해보았다.

일단 댓글 번호에는 게시글 번호가 필요하니깐 FK로 설정해주고 아이디도 필요하니깐 FK로 아이디도 설정해준다.

이렇게 테이블마다 다른 테이블을 참조하고 있으면 그 키를 FK로 넣어주면 된다!

board_num에 넣으려고 해서 실패했음;_; 🤣PK인데 넣을려고 실수했음

board_writer에 넣어주니깐 잘 들어가는 것을 볼 수 있음.

일기장의 FK를 추가시켜주려고 했는데, on Delete 설정이 생각이 났다 CASCADE로 설정하면

아이디가 없으면 연쇄적으로 지워주는 것이 아닐까?

결론 회원이 삭제되면 작성자의 이름도 삭제되는 것이다.

회원 정보가 없어지면 댓글 단 회원의 아이디도 없어져야 하는 게 맞다.

같은 부모에 자식 테이블에 2개가 있으면 numbering을 해주는 것이다.

그러면 아까 적었던 fk도 수정해줘야 함.

member - 회원

board - 게시판

diary - 일기장

comm - 댓글

db코드는 길어서 접은 글로 하겠음

SHOW COLUMNS FROM `cando`.`member`

/*member에 now 추가했음 */;


ALTER TABLE `cando`.`member` 
ADD COLUMN `member_indate` DATETIME NULL DEFAULT now() AFTER `member_phone`;
-- 회원 가입된 시간을 datetime으로 해서 현재시간 now를 이용

-- 멤버 테이블은 pk 아이디에만

/* -------------------게시판-------------------*/
CREATE TABLE `cando`.`board` (
  `board_num` INT NOT NULL COMMENT '게시물 번호',
  `board_title` VARCHAR(20) CHARACTER SET 'utf8' NOT NULL COMMENT '제목',
  `board_writer` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL COMMENT '작성자',
  `board_writedate` DATETIME NOT NULL DEFAULT now() COMMENT '작성일자',
  `board_updatedate` DATETIME NOT NULL DEFAULT now() COMMENT '수정일자',
  `board_readcnt` INT NOT NULL DEFAULT 0 COMMENT '조회수\n',
  PRIMARY KEY (`board_num`))
COMMENT = '게시판';

-- 게시판에 멤버 id 외부키 추가
-- board_writer member_id 참조
ALTER TABLE `cando`.`board` 
DROP FOREIGN KEY `board_writer`;
ALTER TABLE `cando`.`board` 
ADD CONSTRAINT `board_writer`
  FOREIGN KEY (`board_writer`)
  REFERENCES `cando`.`member` (`member_id`)
  ON DELETE CASCADE;



/* -------------------일기장-------------------*/
CREATE TABLE `cando`.`diary` (
  `diary_num` INT NOT NULL COMMENT '일기 번호',
  `diary_title` VARCHAR(20) CHARACTER SET 'utf8' NOT NULL COMMENT '제목',
  `diary_content` VARCHAR(2000) CHARACTER SET 'utf8' NOT NULL COMMENT '일기 내용',
  `diary_writer` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL COMMENT '작성자',
  `diary_writedate` DATETIME NOT NULL DEFAULT now() COMMENT '작성일자',
  `diary_updatedate` DATETIME NOT NULL DEFAULT now() COMMENT '수정일자',
  PRIMARY KEY (`diary_num`))
COMMENT = '일기장';

-- 일기장 작성자 추가
ALTER TABLE `cando`.`diary` 
CHANGE COLUMN `diary_writer` `diary_writer` VARCHAR(20) CHARACTER SET 'utf8' NOT NULL COMMENT '작성자' ;

-- 일기장 작성자 폴인키 추가
ALTER TABLE `cando`.`diary` 
ADD CONSTRAINT `diary_writer`
  FOREIGN KEY (`diary_writer`)
  REFERENCES `cando`.`member` (`member_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

/* -------------------댓글-------------------*/
CREATE TABLE `cando`.`comm` (
  `comm_num` INT NOT NULL DEFAULT 0 COMMENT '댓글 번호\n0번 부터',
  `comm_writer` VARCHAR(20) CHARACTER SET 'utf8' NOT NULL COMMENT '댓글 쓴사람\nmember_id 참조하고 있음.',
  `board_num` INT NOT NULL COMMENT '게시물의 번호를 참조\nboard_num을 참조하고 있음.\n',
  `comm_content` VARCHAR(200) CHARACTER SET 'utf8' NULL COMMENT '댓글 200자 까지 입력가능\n댓글 번호가 많아지면 1,2페이지로 나눠야 함.\n',
  `comm_writedate` DATETIME NOT NULL COMMENT '댓글 작성한 날짜',
  `comm_updatedate` DATETIME NOT NULL COMMENT '댓글 수정한 날짜',
  PRIMARY KEY (`comm_num`),
  INDEX `member_id_3_idx` (`comm_writer` ASC) VISIBLE,
  INDEX `board_num_1_idx` (`board_num` ASC) VISIBLE,
  CONSTRAINT `member_id_3`
    FOREIGN KEY (`comm_writer`)
    REFERENCES `cando`.`member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `board_num_1`
    FOREIGN KEY (`board_num`)
    REFERENCES `cando`.`board` (`board_num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = '댓글';

mysql에서 erd 만들어주는 방법을 이용한다

https://xively.tistory.com/38

export로 파일을 꺼내 주면 됨.

한글로 옆에 해석까지 적었다.

사용자가 보기 쉽게!

MySql workbench 사용법에 대해서 알아본다.

UTF-8 설정을 먼저 해주도록 한다.

먼저 스키마를 생성한다.

스키마가 생성된 것을 확인할 수 있음.

스키마는 ?

1. 스키마는 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술한 메타데이터의 집합.

2. 스키마는 데이터베이스를 구성하는 데이터 개체(Entity), 속성(Attribute), 관계(Realtionship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.

3. 스키마는 사용자의 관점에 따라 외부 스키마,개념 스키마,내부 스키마로 나눠짐.

스키마를 생성한 후 테이블을 생성

MySql db에는 엔진이 있다.. 엔진은 무엇인가?

스토리지엔진은 DB에서 데이터를 어떠한 방식으로 저장하고 접근할 것인지에 대한 기능을 제공함.

스토리지엔진의 특성에 따라 데이터 접근이 얼마나 빠른지, 얼마나 안정적인지, 트랜잭션 등의 기능을 제공하는지 등의 차이점이 발생한다. 

하나씩 알아보자.

1. MyISAM

MySQL의 기본 스토리지 엔진이다.

데이터 저장에 실제적인 제한이 없고 매우 효율적으로 저장.

Full-Text 인덱스를 지원하며 특정 인덱스에 대해 메모리 캐쉬를 지원함.

트랜잭션은 미지원(?) 테이블 레벨의 락을 지원 잦은 변경 및 삭제에는 좋은 성능이 나오지 못하나 데드락 발생은 예방가능

테이블작업시 특정행을 수정하려고 하면 테이블 전체에 락이 걸려서 다른 사람 작업 X (주로 select 작업이 많은 경우에 사용 된다.)

Table-level Lock이 걸림

2. InnoDB

ACID 트랜잭션을 지원하며, MyISAM보다 데이터 저장비율이 낮고, 데이터 로드 속도가 느리다.

특정 데이터와 인덱스에 대해서 메모리 캐쉬를 지원하며 외부티를 지원한다. 데이터 압축이 불가능하고 자동 에러 복구 기능이 있다. 테이블 레벨이 아닌 ROW 레벨의 락을 지원한다.

테이블작업시 작업 시작하면 해당 열만 잠기게 되며 나머지 부분은 다른 사용자가 수정 가능

Row-level Lock이 걸림

row-level 이다보니깐 insert,update,delete에 대한 속도가 빠르다.

주로 데이터 입력 및 수정이 빈번한 높은 퍼포먼스를 요구하는 대용량 사이트에서 적합

3. Cluster (NDB)

트랜잭션을 지원하고 모든 데이터와 인덱스가 메모리에 존재하여 매우 빠른 데이터 로드 속도를 자랑하며 PK 사용시 최상의 속도를 나타낸다.

4. Archive

MySQL 5.0부터 새롭게 도입된 엔진으로 자동적으로 데이터 압축을 지원하며 다른 엔진에 비해 80% 저장공간 절약 효과를 자랑한다.

그리고 가장 빠른 데이터 로드 속도 또한 자랑하지만, INSERT와 SELECT만이 가능하다.

5. Federated

MySQL 5.0부터 새롭게 도입된 엔진으로 물리적 데이터베이스에 대한 논리적 데이터베이스를 생성하여 원격 데이터를 컨트롤 할 수 있따. 실행속도는 네트워크 요소에 따라 좌우되며 테이블 정의를 통한 SSL 보안 처리를 한다.

분산 데이터베이스 환경에 사용한다.

회원을 위한 테이블이므로 중요하기 때문에 복구능력이 좋은것은 Innodb이므로 채택했고, insert,update,delete가 많이 일어나면 속도가 빠른 면에서도 Innodb가 좋아서 선택했다

create table tab_myisam(
	mem_age int not null,
    primary key(mem_age)
)engine=MyISAM;

create table tab_innodb(
	mem_age int not null,
    primary key(mem_age)
)engine=InnoDB;

insert into tab_myisam (mem_age) value (15);
insert into tab_innodb (mem_age) value (15);

insert into tab_myisam (mem_age) values(13), (14), (15);
insert into tab_innodb (mem_age) values(13), (14), (15);

처음에는 MyISAM 엔진을 선택하려고 했으나, 복구 능력이 좋지 않고, 갈아타는 경우도 많다고 해서 InnoDB를 선택했다.

그리고 테스트를 해봤는데 (MyISAM 테이블 VS InnoDB 테이블)

같은 테이블로 pk를 지정해서 데이터를 각각 1,2,3 1,2,3을 넣었는데 MyISAM테이블은 트랜잭션을 지원하지 않아서 1,2,3이 모두 들어갔다.. unique 제약 조건에 걸려서 들어가지 않아야 하는데 !

InnoDB 테이블은 3 외에는 없는걸로 나온다.

InnoDB로 멤버 테이블을 만들었다. 

PK - Primary key, 중복이나 빈값(NULL)이 들어올 수 없음.

NN - Not Null(빈값) 못들어옴

UQ - Unique, 중복 값을 넣을 수 없음

B - 데이터를 이진 문자열로 저장함

UN - Unsigned data type (- 범위 삭제)

INT, DOUBLE 등의 경우 UN을 사용해 주면 -값 +값 이던 범위가 - 값은 없어지고 +값만 2배로 늘어남 (?)

ZF - Zero Filled 컬럼 크기보다 작은 값을 넣었을 경우 0으로 채운 뒤 삽입시킴

AI - Insert 시마다 값 1씩 늘어남 ( 시퀀스 기능과 동일 )

G - 다른 열을 기반으로 한 수식으로 생성된 값

Default/Expression - 기본값 , 기본값에 수식 설정

 DATETIME VS TIMESTAMP

타입의 차이는

DATETIME은 문자형

TIMESTAMP는 숫자형

용량은 DATETIME은 8byte

TIMESTAMP 4byte

timestamp를 사용하려고 하면 오류가 날려고 해서, datetime을 이용해서 default 값을 now로 주었다.

https://www.youtube.com/watch?v=xKYeJxBTt2E&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=1 

이것이 mysql이다를 이용해서 강의를 통해 만들어본다..

MySql을 열어서 edit 에서 safe를 꺼주고(귀찮은거 뜬다고 함)

powershell을 열여서

MySql경로를 넣어주었다. 8.0 버전임

근데 자꾸 mysql을 시작하면 에러가 남.. 인코딩 character가 안맞다는 둥..

로컬 C에 프로그램 데이터 > MySql에 들어가면 my.ini를 노트 패드로 열어준다.

그러면 character-set이 비어있다는 것을 볼 수있음.

바꿔주긴 했는데 다른 utf-8을 넣는 곳이 없음 .. 인터넷에서는 옛날 버전인가? 3~4개 씩 넣었음..

https://www.youtube.com/watch?v=VnnTh83sjcc&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=6 

참조했습니다!7번까지봄