데이터베이스 및 테이블 생성하기

데이터베이스 생성하기

MySQL 프롬프트에 접속합니다. CREATE SCHEMA [데이터베이스명]의 데이터베이스를 생성하는 명령어입니다. SCHEMA(스키마)라고 되어 있는데, MySQL에 데이터베이스와 스키마는 같은 개념입니다. nodejs라는 이름의 데이터베이스를 생성합니다. 그 후 use nodejs; 명령어를 추가로 입력하여 앞으로 nodejs 데이터베이스를 사용하겠다는 것을 MySQL에 알립니다.

mysql> CREATE SCHEMA `nodejs` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> use nodejs;
Database changed

 

CREATE SCHEMA 뒤에 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci를 붙여 한글과 이모티콘을 사용할 수 있게 만듭니다. utf8mb4여야 데이터베이스에서 한글과 이모티콘을 사용할 수 있고, COLLATE는 해당 CHARACTER SET을 어떤 형식으로 정렬할 것인지를 의미합니다. MySQL 8 버전의 기본 COLLATE는 utf8mb4_0900_ai_ci이지만 한글 문제가 있으므로 여기서는 utf8mb4_general_ci를 사용합니다.

 

CREATE SCHEMA와 같이 MySQL이 기본적으로 알고 있는 구문은 예약어라고 합니다. 예약어는 소문자로 써도 되지만, 가급적 대문자로 쓰는 것이 좋습니다. nodejs와 같은 사용자가 직접 만든 이름과 구분하기 위해서입니다.

 

테이블 생성하기

데이터베이스를 생성했다면 테이블을 만듭니다. 테이블이란 데이터가 들어갈 수 있는 틀을 의미하며, 테이블에 맞는 데이터만 들어갈 수 있습니다. 사용자의 정보를 저장하는 테이블을 만들어봅시다.

 

MySQL 프롬프트에 다음과 같이 입력합니다(오타가 나지 않도록 주의해야 합니다). 한 줄로 적어도 되지만, 보기 불편하므로 Enter를 누른 후 줄을 바꿔서 입력하는 것이 좋습니다. 세미콜론(;)을 입력하기 전까지는 실행되지 않습니다.

 

mysql>  CREATE TABLE nodejs.users (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> age INT UNSIGNED NOT NULL,
    -> married TINYINT NOT NULL,
    -> comment TEXT NULL,
    -> created_at DATETIME NOT NULL DEFAULT now(),\
    -> PRIMARY KEY(id),
    -> UNIQUE INDEX name_UNIQUE (name ASC))
    -> COMMENT = '사용자 정보'
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)

 

 

한 글자라도 잘못 입력하면 에러가 발생하니 조심해야 합니다. 명령어를 살펴봅시다. CREATE TABLE [데이터베이스명.테이블명]은 테이블을 생성하는 명령어입니다. CREATE TABLE nodejs.users를 입력했으므로 nodejs 데이터베이스 내에 users 테이블을 생성하는 것입니다. 아까 use nodejs; 명령어를 실행했으니 CREATE TABLE users처럼 데이터베이스명은 생략해도 됩니다.

 

그 아래에는 한 줄에 하나씩 콤마(,)로 구분해 컬럼들을 만들었습니다. 순서대로 id(고유 식별자), name(이름), age(나이), married(결혼 여부), comment(자기소개), created_at(로우 생성일)입니다. PRIMARY KEY부터는 다른 옵션이며, 조금 뒤에 설명합니다. 컬럼을 정의해두면 앞으로 데이터를 넣을 때 컬럼 규칙에 맞는 정보들만 넣을 수 있습니다. 생년월일이나 몸무게와 같이 컬럼으로 만들어두지 않은 정보들은 저장할 수 없습니다.

 

각각의 컬럼 이름 옆에는 INT, VARCHAR, TINYINT, TEXT, DATETIME 등이 적혀 있습니다. 이는 컬럼의 자료형을 뜻합니다.

• INT는 정수를 의미합니다. 소수까지 저장하고 싶다면 FLOAT이나 DOUBLE 자료형을 사용하면 됩니다.
• VARCHAR(자릿수) 외에도 CHAR(자릿수)라는 자료형도 있습니다. CHAR는 고정 길이이고, VARCHAR는 가변 길이입니다. 예를 들어 CHAR(10)이면 반드시 길이가 10인 문자열만 넣어야 하고, VARCHAR(10)일 경우에는 길이가 0~10인 문자열을 넣을 수 있습니다. CHAR에 주어진 길이보다 짧은 문자열을 넣는다면 부족한 자릿수만큼 스페이스가 채워집니다.
•  TEXT는 긴 글을 저장할 때 사용하며, VARCHAR와 헷갈릴 수 있습니다. 수백 자 이내의 문자열은 보통 VARCHAR로 많이 처리하고, 그보다 길면 TEXT로 처리하곤 합니다.
• TINYINT는 -128부터 127까지의 정수를 저장할 때 사용합니다. 1 또는 0만 저장한다면 불 값(Boolean)과 같은 역할을 할 수 있습니다.
• DATETIME은 날짜와 시간에 대한 정보를 담고 있습니다. 날짜 정보만 담는 DATE와 시간 정보만 담는 TIME 자료형도 있습니다. 이외에도 많은 자료형이 있으나 이 정도가 자주 쓰입니다.

자료형 뒤에도 NOT NULL, NULL, UNSIGNED, AUTO_INCREMENT, DEFAULT 등의 옵션이 붙어 있습니다.

• NULL과 NOT NULL은 빈칸을 허용할지 여부를 묻는 옵션입니다. comment 컬럼만 NULL이고, 나머지는 모두 NOT NULL입니다. 자기소개를 제외한 나머지 컬럼은 반드시 로우를 생성할 때 데이터를 입력해야 합니다.
• id 컬럼에는 추가로 AUTO_INCREMENT가 붙어 있습니다. 숫자를 저절로 올리겠다는 뜻입니다. 예를 들어 처음에 Zero라는 사람의 데이터를 넣으면 MySQL은 알아서 id로 1번을 부여합니다. 다음에 Nero라는 사람의 데이터를 넣으면 자동으로 id 2번을 부여합니다. 이를 가능하게 하는 옵션이 AUTO_INCREMENT입니다.
• UNSIGNED는 숫자 자료형에 적용되는 옵션입니다. 숫자 자료형은 기본적으로 음수 범위를 지원합니다. 예를 들어 INT는 -2147483648~2147483647까지의 숫자를 저장할 수 있습니다. 만약 UNSIGNED가 적용되어 있다면 음수는 무시되고 0~4294967295까지 저장할 수 있습니다. FLOAT과 DOUBLE에는 UNSIGNED 적용이 불가능합니다. 나이처럼 음수가 나올 수 없는 컬럼은 체크해두는 것이 좋습니다.
• ZEROFILL은 숫자의 자릿수가 고정되어 있을 때 사용할 수 있습니다. 가끔 자료형으로 INT 대신 INT(자릿수)처럼 표현하는 경우가 있습니다. 이때 ZEROFILL을 설정해둔다면 비어 있는 자리에 모두 0을 넣습니다. 예를 들어 INT(4)인데 숫자 1을 넣었다면 0001이 되는 식입니다. 실습 예제에서는 age 컬럼에 UNSIGNED 옵션만 주었습니다
• created_at에는 DEFAULT now()라는 옵션이 붙어 있습니다. 데이터베이스 저장 시 해당 컬럼에 값이 없을 때 MySQL이 기본값을 대신 넣습니다. now()는 현재 시각을 넣으라는 뜻입니다. now() 대신 CURRENT_TIMESTAMP를 적어도 같은 뜻이 됩니다. 사용자 정보를 넣으면 created_at 컬럼에는 넣는 순간의 시각이 자동으로 기록됩니다.
• 해당 컬럼이 기본 키인 경우에 PRIMARY KEY 옵션을 설정합니다. 기본 키란 로우를 대표하는 고유한 값을 의미합니다. 데이터베이스에 데이터를 넣을 때 로우 단위로 넣습니다. 이때 로우들을 구별할 고유한 식별자가 필요합니다. 이름, 나이, 결혼 여부 컬럼은 다른 사람과 내용이 겹칠 수 있습니다. 자기소개는 내용을 입력하지 않아도 되므로 고유하지 않습니다. 따라서 id라는 새로운 컬럼을 하나 만들어 고유한 번호를 부여한 것입니다. 주민등록번호나 학번과 비슷한 개념입니다. MySQL에는 PRIMARY KEY(id)라는 옵션으로 id 컬럼이 기본 키임을 알렸습니다,
• UNIQUE INDEX는 해당 값이 고유해야 하는지에 대한 옵션이며, name 컬럼이 해당됩니다. 인덱스의 이름은 name_UNIQUE로, name 컬럼을 오름차순(ASC)으로 기억하겠다는 것입니다. 내림차순은 DESC입니다. PRIMARY KEY나 UNIQUE INDEX의 경우에는 데이터베이스가 별도로 컬럼을 관리하므로 조회 시 속도가 빨라집니다. 기본 키인 id도 사실 고유해야 하지만, PRIMARY KEY는 자동으로 UNIQUE INDEX를 포함하므로 따로 적지 않아도 됩니다.

여기까지는 컬럼에 대한 설정이었습니다. 이어서 COMMENT, ENGINE은 테이블 자체에 대한 설정입니다.

• COMMENT는 테이블에 대한 보충 설명을 의미합니다. 이 테이블이 무슨 역할을 하는지 적어두면 됩니다. 필수는 아닙니다.
• DEFAULT CHARACTER SET을 utf8로 설정하지 않으면 한글이 입력되지 않으니 반드시 설정해야 합니다.
• ENGINE은 여러 가지가 있지만, MyISAM과 InnoDB가 제일 많이 사용됩니다. 이 책에서는 InnoDB를 엔진으로 사용하겠습니다.

만들어진 테이블을 확인하는 명령어는 DESC 테이블명입니다.

mysql> DESC users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(20)  | NO   | UNI | NULL              |                   |
| age        | int unsigned | NO   |     | NULL              |                   |
| married    | tinyint      | NO   |     | NULL              |                   |
| comment    | text         | YES  |     | NULL              |                   |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

 

테이블을 잘못 만들었을 경우 DROP TABLE [테이블명] 명령어를 입력하면 제거됩니다. 제거 후 다시 생성할 수 있습니다.

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.02 sec)

 

 

comments 테이블에는 id, commenter(댓글을 쓴 사용자 아이디), comment(댓글 내용), created_at(로우 생성일) 컬럼이 있습니다.

commenter 컬럼에는 댓글을 작성한 사용자의 id를 저장할 것입니다. 이렇게 다른 테이블의 기본 키를 저장하는 컬럼을 외래 키(foreign key)라고 합니다. CONSTRAINT [제약조건명] FOREIGN KEY [컬럼명] REFERENCES [참고하는 컬럼명]으로 외래 키를 지정할 수 있습니다.

comments 테이블에서는 commenter 컬럼과 users 테이블의 id 컬럼을 연결했습니다. 다른 테이블의 기본 키이므로 commenter 컬럼에 인덱스도 걸어봤습니다.

그 후 ON UPDATE와 ON DELETE는 모두 CASCADE로 설정했습니다. 사용자 정보가 수정되거나 삭제되면 그것과 연결된 댓글 정보도 같이 수정하거나 삭제한다는 뜻입니다. 그래야 데이터가 불일치하는 현상이 나타나지 않습니다.

다음 명령어로 users 테이블과 comments 테이블이 제대로 생성되었는지 확인합니다.

mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| comments         |
| users            |
+------------------+
2 rows in set (0.00 sec)