[MySQL] 데이터베이스 관리

---

 

#1. 데이터베이스 관리

1. 데이터베이스  생성/삭제

1) 데이터베이스 생성하기

CREATE DATABASE `데이터베이스이름` [DEFAULT CHARSET `utf8`];

• 기존에 존재하는 데이터베이스와 동일한 이름의 데이터베이스는 생성할 수 없다.
• 기본 CHARSET은 utf8, euckr 등의 값을 지정할 수 있다. (mysql에서는 utf-8이 아니고 utf8)
  • 지정하지 않으면 기본값이 라틴어 계열로 된다.
• 테이터베이스 이름, 테이블 이름, 인코딩 이름, 별칭은 역따옴표를 붙여도 되고 안붙여도 된다. (띄어쓰기가 있을 시에는 붙여야 한다)

 

2) 데이터베이스 삭제하기

DROP DATABASE `데이터베이스이름`;

• 존재하지 않는 데이터베이스의 이름을 지정한 경우 에러가 발생한다.
• 삭제한 데이터베이스는 복구할 수 없기 때문에 신중히 수행해야 한다.

 

2. 테이블 생성하기

1) 테이블 생성

CREATE TABLE `테이블이름` (
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  [PRIMARY KEY (`기본키컬럼이름`)]
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8`] [COMMENT='테이블 설명']

• 괄호 안에 테이블이 포함할 컬럼의 이름과 데이터 타입들을 콤마로 구분하여 명시한다.
• ENGINE=InnoDB 는 Oracle이 MySQL을 인수한 후에 추가한 저장형식으로 참조키나 프로시저 등의 고급 기능을 사용할 수 있게 해준다.
• DEFAULT CHARSET=`utf8` 은 기본 문자열 저장 형식을 utf8로 설정한다.
  • 데이터베이스 뿐만 아니라 테이블에도 지정해야한다.
• 테이블 이름이나 컬럼이름에는 역따옴표를 적용한다. (생략가능)
  • 이름을 지정할 때는 일반적으로 테이블 이름은 ~~들 이라는 의미로 복수형(~s)으로 지정하고, 컬럼 이름은 개별적인 값이므로 단수형으로 지정한다.
• COMMENT는 홑따옴표를 적용한다.

 

2) 데이터 타입

구분	데이터 타입	설명
문자 문장형	CHAR(n)	글자 수가 최대 n개인 고정 길이 문자열 저장 → 한글, 영문 구분 없이 한 글자로 취급한다. → 우편번호, 주민번호 등과 같이 글자수가 고정된 데이터에 사용된다. ex) char(10)이라 지정된 컬럼의 경우, → "mysql'이라 저장하면 모자란 5글자는 공백처리 되어 "mysql     " 이라 저장된다 초과되는 글자의 경우 초과된 글자는 저장되지 않는다.
	VARCHAR(n)	글자 수가 최대 n개인 가변 길이 문자열 저장 → 아이디, 이메일 주소, 비밀번호, 글 제목 등 글자수가 불규칙 적인 데이터에 사용
	TEXT	최대 65535 길이의 문자열을 저장한다. ex) 게시판의 본문 등.
숫자형	TINYINT SMALLINT INT(기본) BIGINT	정수 형태의 데이터를 저장한다. (TINYINT < SMALLINT < INT < BIGINT)
	FLOAT(m,n)	실수 형태의 데이터를 저장한다. M은 전체 자리수 (소수점도 자리수에 포함됨), n은 소수점 이하의 자리수
날짜형	DATE	날짜 형식의 데이터를 저장한다. (YYYY-MM-DD)
	DATETIME	날짜 + 시간 형식의 데이터를 저장한다. (YYYY-MM-DD HH:MI:SS)
기타	BLOB	최대 65535 길이의 바이너리를 저장한다. (게시판 본문 등에 사용) UTF8 환경에서 TEXT보다 권장.
	ENUM('A','B','C'...'Z')	저장 가능한 값을 A, B, C ... Z로 제한한다. ex) 성별 → ENUM('F', 'M')       메일링 가입 →ENUM('Y', 'N')

 

3) NULL 데이터

• 데이터 타입을 결정한 후에는 NULL 데이터의 허용 여부를 명시한다.
• NULL: NULL 데이터 허용, 일반적으로 미필수 입력 항목에 사용한다.
• NOT NULL: NULL 데이터 허용 안함, 필수 입력 항목에 사용한다.

 

4) 기본키 (Primary Key) 지정 방법

• 컬럼을 나열할 때 primary key 라고 명시한다.

CREATE TABLE `테이블이름` (
  `column1` int NOT NULL primary key COMMENT '컬럼설명',
  `column2` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8` [COMMENT='테이블 설명']

 

• 컬럼의 목록을 모두 나열한 후 primary key('컬럼이름')의 형식으로 명시한다.

CREATE TABLE `테이블이름` (
  `column1` int NOT NULL COMMENT '컬럼설명',
  `column2` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  PRIMARY KEY (`column1`)
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8` [COMMENT='테이블 설명']

 

5) 일련번호 설정 (자동증가)

• 각 데티어 행을 고유하게 구분하기 위해 설정하는 Primary Key가 INT형인 경우, AUTO_INCREMENT 옵션을 추가하면 데이터 INSERT 시에 자동으로 1씩 증가된 값이 저장된다.

CREATE TABLE `테이블이름` (
  `column1` int NOT NULL primary key auto_increment COMMENT '컬럼설명',
  `column2` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8` [COMMENT='테이블 설명']

CREATE TABLE `테이블이름` (
  `column1` int NOT NULL auto_increment COMMENT '컬럼설명',
  `column2` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  PRIMARY KEY (`column1`)
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8` [COMMENT='테이블 설명']

 

6) 예제

- id(int, pk, 자동증가), memo(varchar(255)), reg_date(datetime)의 컬럼을 갖는 테이블을 생성하시오

 

3. 테이블 삭제하기

DROP TABLE `테이블이름`;

• 존재하지 않는 테이블을 삭제할 경우 에러가 발생한다.
• 삭제된 테이블은 복구 할 수 없으므로, 주의가 필요하다.

위에서 생성한 mytable 삭제

 

4. 테이블 수정하기

1) 테이블의 구조 변경하기

ALTER TABLE `테이블이름` 명령어;

 

※ 명령어의 종류

명령어	설명
rename	테이블의 이름을 변경한다.
add	컬럼이나 제약조건을 추가한다.
change	컬럼을 수정한다.
drop	컬럼이나 제약조건을 삭제한다.

 

2) 예제

- mytable의 이름을 example로 변경

 

- datatime 형식의 edit_date 컬럼을  NOT NULL로 추가

ADD 뒤에는 CREATE TABLE 명령어 뒤에 쓰는 내용을 입력한다

 

- ADD 명령어와 AFTER 옵션을 사용하여 특정 컬럼 뒤에 새로운 컬럼 추가

before 옵션은 없다

 

- memo 컬럼의 컬럼명을 comment로, 데이터 타입은 text로 수정하고 NULL값을 허용하게 하시오.

 

- writer 컬럼의 이름을 유지한 상태에서 데이터 타입과 NULL을 허용 안함으로 변경하시오.

변경하지 않은것은 동일하게 명시하고, 변경하려는 것만 기존과 다르게 지정

 

- edit_date 컬럼을 삭제하시오

 

- id 컬럼의 기본키 속성 해제하기

• 일련번호 체계를 리셋하고자 할 때 사용

기본키는 AUTO_INCREMENT 속성이 있을 경우 해제할 수 없기 때문에 CHANGE 명령어를 사용하여 AUTO_INCREMENT 속성을 제거해야 한다.

 

기본키를 해제할 경우 DROP PRIMARY KEY 명령어를 사용한다.

 

- id 컬럼에 기본키를 설정하고, AUTO_INCREMENT 속성을 추가하시오

 

4. 참조키 설정하기

1) 참조키란?

• 테이블 A의 컬럼 a에 저장될 데이터가 반드시 테이블 B에 저장되어 있는 값 중의 하나여야할 경우, 테이블 A는 테이블 B를 참조한다 라고 한다.
• 즉, 컬럼 a를 테이블 B에 대한 참조키(외래기, Foreign Key)라고 한다.

 

2) 도형으로 참조 관계 표현

• 테이블 A의 Primary Key를 테이블 B의 Primary Key가 참조하는 경우, 각 테이블에 고유하게 존재하는 값 끼리 참조하므로 1:1 관계가 성립된다.

• 테이블 A의 Primary Key를 테이블 B의 일반 컬럼이 참조하는 경우, 하나의 A를 하나 이상의 B가 참조하는 1:N 관계가 성립된다.
  • 게시판, 장바구니 등등

 

3) 참조키 설정하기

CREATE TABLE `테이블이름` (
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  `컬럼이름` 데이터타입 [NULL | NOT NULL] [AUTO_INCREMENT] [COMMENT '컬럼설명'],
  [PRIMARY KEY (`기본키컬럼이름`)]
  [FOREIGN KEY (`참조키컬럼이름`)]
) [ENGINE=INNODB] [DEFAULT CHARSET=`utf8`] [COMMENT='테이블 설명']

• 참조키 설정시 다음의 제약조건이 생성된다 (참조 무결성의 제약조건)
  • 다른 테이블의 참조를 받고 있는 데이터는 참조하고 있는 데이터가 삭제되기 전 까지는 먼저 삭제될 수 없다.
  • 다른 테이블의 데이터를 참조하는 컬럼은 해당 컬럼의 데이터 중 하나를 저장해야만 한다.

 

4) 예제

- 학생 테이블의 studno 컬럼을 참조하는 성적 테이블 만들기

참조키는 테이블 구조에서 'MUL'로 표시된다

 

5. 참조관계에서 제약조건 확인하기

1) INSERT 제약조건

• grade 테이블의 studno 컬럼은 student 테이블의 studno를 참조하기 때문에 student 테이블에 저장되어 있지 않은 값은 INSERT 할 수 없다.

student 테이블에 저장되지 않은 값을 사용한 INSERT의 예

student 테이블에 저장되어 있는 값을 사용한 INSERT

 

2) DELETE 제약조건

• 다른 테이블의 참조를 받고 있는 데이터는 자신을 참조하는 대상이 삭제되기 전에는 먼저 삭제될 수 없다.

10101번을 참조 하는 grade 데이터가 있는 경우 삭제 안됨

 

6. 데이터베이스 백업 / 복구

• MySQL의 백업과 복구는 터미널(명령프롬프트)에서 MySQL의 실행파일이 위치하는 경로로 이동한 후에 수행해야 한다.

1) 데이터베이스 백업

mysqldump -u계정아이디 -p 벡업할DB이름 > 백업파일경로

• 이 명령어는 MySQL 접속 상태가 아닌 일반 터미널(명령프롬프트) 상태에서 실행해야 한다.
• 비밀번호는 mysql 접속 시 사용하는 비밀번호를 입력하면 된다.

 

2) 데이터베이스 복구

mysql -u계정아이디 -p 복구할DB이름 < 백업파일경로

• MySQL에 접속한 상태에서 백업한 DB를 복구하기 위한 새로운 데이터베이스를 생성한다.

• MySQL 접속을 해제한 상태의 터미널(명령프롬프트)에서 복구를 진행한다.

• MySQL에 접속 후 데이터베이스가 정상적으로 복구된 것을 볼 수 있다.