코딩 공부/DataBase

[DataBase] 10_데이터베이스 관리

chocbi 2020. 5. 11. 15:18

데이터베이스 관리

#01. 데이터베이스 생성/삭제

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

create database `데이터베이스이름` [default charset `utf8`];

기존에 존재하는 데이터베이스와 동일한 이름의 데이터베이스는 생성할 수 없다.
기본 캐릭터 셋은 utf8, euckr 등의 값을 지정할 수 있다.

예제

mytest 데이터 베이스 생성하기

mysql> create database `mytest` default charset `utf8`;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+

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

drop database `데이터베이스이름`;

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

예제

mytest데이터베이스 삭제하기

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database `mytest`;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#02. 테이블 생성하기

1) 테이블 생성 방법

CREATE TABEL `테이블이름` (
    `컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
    `컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
    `컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT], ...
    [PRIMARY KEY (`기본키컬럼이름`)]
)    [ENGINE=InnoDB][DEFAULT CHARSET=`utf8`];

CREATE TABLE <테이블이름> () ENGINE=InnoDB DEFAULT CHARSET utf8;
- 괄호 안에 테이블이 포함할 컬럼의 이름과 데잍너 타입쌍을 콤마로 구분하여 명시한다.
- ENGINE=InnoDB –> Orcle이 MySQL을 인수한 후에 추가한 저장형식으로 참조키나 프로시저 등의 고급 기능을 사용할 수 있게 한다.
- DEFAULT CHARSET=utf8 –> 기본 문자열 저장 형식을 UTF8로 설정

2) 테이블 생성하기

데이터 타입

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

기본키 (Primary Key) 지정 방법

컬럼을 나열할 때 Primary Key라 명시한다.

CREATE TABLE `test` (
    `column1` int not null primary key,
    `column2` varchar(10) not null
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

컬럼의 목록을 모두 나열한 후 Primary Key(‘컬럼이름’)의 형식으로 명시

CREATE TABLE `test` (
  `column1` int not null,
  `column2` varchar(10) not null
  primary key (`column1`)
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

자동증가 일련번호의 설정

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

CREATE TABLE `test` (
    `column1` int not null primary key auto_increment,
    `column2` varchar(10) not null
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

CREATE TABLE `test` (
    `column1` int not null auto_increment,
    `column2` varchar(10) not null
    primary key (`column1`)
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

예제

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

mysql> CREATE TABLE mytable (
    -> id int not null primary key auto_increment,
    -> memo varchar(255) not null,
    -> reg_date datetime not null
    -> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> DESC mytable;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo     | varchar(255) | NO   |     | NULL    |                |
| reg_date | datetime     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

#03. 테이블 삭제하기

1) 테이블 삭제 구문

drop table `테이블이름`;

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

예제

앞 예제에서 생성한 mytable을 삭제하시오

mysql> drop table mytable;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| department         |
| professor          |
| student            |
+--------------------+

#04. 테이블 수정하기

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

alter table `테이블이름` 명령어;

2) 명령어의 종류

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

예제1

mytable의 이름을 mytest로 변경하기

mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| department         |
| mytable            |
| professor          |
| student            |
+--------------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE mytable RENAME mytest;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| department         |
| mytest             |
| professor          |
| student            |
+--------------------+

예제2

datetime 형식의 edit_date 컬럼을 Not NULL로 추가

mysql> desc mytest;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo     | varchar(255) | NO   |     | NULL    |                |
| reg_date | datetime     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE mytest ADD edit_date datetime not null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytest;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo      | varchar(255) | NO   |     | NULL    |                |
| reg_date  | datetime     | NO   |     | NULL    |                |
| edit_date | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

예제3

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

mysql> ALTER TABLE mytest ADD writer varchar(50) NULL AFTER id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytest;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| writer    | varchar(50)  | YES  |     | NULL    |                |
| memo      | varchar(255) | NO   |     | NULL    |                |
| reg_date  | datetime     | NO   |     | NULL    |                |
| edit_date | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

예제4

memo 컬럼을 수정하기
- 컬럼명 –> comment / 데이터 타입 –> text / null 허용

mysql> ALTER TABLE mytest CHANGE memo comment text Null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytest;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| writer    | varchar(50) | YES  |     | NULL    |                |
| comment   | text        | YES  |     | NULL    |                |
| reg_date  | datetime    | NO   |     | NULL    |                |
| edit_date | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

예제5

Writer 컬럼의 이름을 유지한 상태에서 데이터 타입만 변경하기
- 컬럼이름과 NULL 허용 여부는 기존과 동일하게 명시하고, 변경하려는 데이터 타입만 기존의 형태와 다르게 지정한다.

mysql> ALTER TABLE mytest CHANGE writer writer varchar(100) NOT NULL;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytest;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| writer    | varchar(100) | NO   |     | NULL    |                |
| comment   | text         | YES  |     | NULL    |                |
| reg_date  | datetime     | NO   |     | NULL    |                |
| edit_date | datetime     | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

예제6

edit_date 컬럼을 삭제하기

mysql> Alter Table mytest drop edit_date;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytest;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| writer   | varchar(100) | NO   |     | NULL    |                |
| comment  | text         | YES  |     | NULL    |                |
| reg_date | datetime     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

#05.참조키 설정하기

1) 테이블의 참조 관계 이해하기

professor 테이블에서 각 교수의 소속 학과를 의미하는 학과 번호는, department 테이블에 존재하는 학과에 대한 deptno 값이어야 한다.
테이블 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 `테이블이름` (
    `컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT],
    `컬럼이름` 데이터타입 [NOT NULL | NULL] [AUTO_INCREMENT], ... 
[, PRIMARY KEY (`기본키컬럼이름`)]
[, FOREIGN KEY (`참조키컬럼이름`)
    REFERENCES `대상테이블명` (`대상테이블의 컬럼명`) ]
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

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

예제

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

mysql> CREATE TABLE grade (
    -> id int not null auto_increment,
    -> studno int not null,
    -> subject varchar(100) not null,
    -> point int not null,
    -> reg_date datetime not null,
    -> PRIMARY KEY (id),
    -> FOREIGN KEY (studno) REFERENCES student (studno)
    -> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)

예제 결과 확인 - 참조키는 테이블 구조에서 “MUL”로 표시된다.

mysql> desc grade;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| studno   | int(11)      | NO   | MUL | NULL    |                |
| subject  | varchar(100) | NO   |     | NULL    |                |
| point    | int(11)      | NO   |     | NULL    |                |
| reg_date | datetime     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

4) 참조 관계에서 INSERT의 제약조건 확인하기

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

mysql> INSERT INTO grade (studno, subject, point, reg_date) values  (1234, '데이터베이스', 98, now());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`myschool`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`studno`) REFERENCES `student` (`studno`))
mysql>

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

mysql> INSERT INTO grade (studno, subject, point, reg_date) values  (10101, '데이터베이스', 98, now());
Query OK, 1 row affected (0.01 sec)

5) 참조 관계에서 DELETE의 제약조건

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

mysql> delete from student where studno=10101;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myschool`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`studno`) REFERENCES `student` (`studno`))

grade의 데이터를 먼저 삭제해야만 student를 삭제할 수 있다.

Query OK, 1 row affected (0.01 sec)

mysql> delete from student where studno=10101;
Query OK, 1 row affected (0.01 sec)

#06. 데이터베이스 백업/복구

1) 데이터베이스 백업/복구

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

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

데이터베이스 백업하기

myschool 데이터베이스를 “저장을 원하는 폴더\myschool-backup.sql”

mysqldump -uroot -p myschool > 저장을 원하는 폴더\myschool-backup.sql

- C 드라이브 안에 backup 디렉토리를 미리 생성해 두어야한다.

이 명령어는 MySQL 접속 상태가 아닌 일반 명령프롬프트 상태에서 실행해야 한다.
- 백업후, 지정된 경로에 파일이 생성되었는지 확인하도록 한다.

복구하기

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

데이터베이스 복구하기
- 백업한 저장을 원하는 폴더\myschool-backup.sql 파일을 사용하여 rollback_test에 백업한 파일의 내용을 복구하기
  - MySQL 접속 상태가 아닌 윈도우 명령프롬프트 상태에서 수행한다.
```
mysql -uroot -p rollback_test < 저장을 원하는 폴더\myschool-backup.sql
```
복구 결과 확인하기
- MySQL에 접속하여 복구된 데이터베이스를 확인한다.