node - 7 MySQL

Node.js 교과서 개정 2판: 7장 MySQL - 1

* 위 내용을 정리하였음

7 MySQL

- 관계형 데이터베이스 관리 시스템의 대표 주자이다.

7.1 데이터베이스란?

- 관련성을 가지며 중복이 없는 데이터들을 서버의 하드 디스크나 SSD에 저장한다.

- 동시에 여러 사람들이 부여된 권한에 따라 데이터에 대한 작업이 가능하다.

- 특히 관계형 DBMS를 RDBMS라고 한다.

- SQL언어를 사용하는 MySQL을 사용해보자.

7.2 MySQL 설치

$ brew install mysql
$ brew services start mysql
$ mysql_secure_installation

- 설치

// 서버가 꺼져있다면 $ mysql.server start
$ mysql -h localhost -u root -p

- MySQL실행

7.3 워크벤치

- DB를 시각적으로 관리할 수 있어 편리한 툴이다

$ brew cask install mysqlworkbench

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

7.4.1 데이터 베이스 생성하기

- 먼저 스키마(DB)를 만든다.

mysql> CREATE SCHEMA `nodejs` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01sec)
mysql> use nodejs;
Database changed

- DEFAULT CHARACTER SET utf8 을 붙이면 한글을 사용할 수 있게 된다.

- nodejs라는 스키마를 만들고 이를 사용한다.

 

7.4.2 테이블 생성하기

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 = '사용자 정보'
  -> DEFAULT CHARACTER SET = utf8
  -> ENGINE = InnoDB;
Query OK, 0 row affected (0.09 sec)

- nodejs 스키마에 users테이블을 만드는 과정이다.

- CREATE TABLE users 만 쳐도 된다. (스키마 명 생략 가능)

- 컬럼과 로우가 교차하는 칸 하나를 필드라고 한다.

 

• CHAR(10) : 반드시 길이가 10인 문자열만 넣어야 한다 만약 입력 문자열이 짧으면 부족한 만큼 스페이스로 채운다.

• VARCHAR(10) : 길이가 0~10인 문자열을 넣을 수 있다

• TEXT : 긴 글을 저장할 때 사용 ( 수백자 이상 )

• TINYINT : -128부터 127까지의 정수를 저장. 1 또는 0만 저장한다면 불 값(Boolean)과 같은 역할을 할 수 있다.

• DATETIME : 날짜와 시간에 대한 정보. 날짜 정보만 담는 DATE와 시간 정보만 담는 TIME 자료형도 있다.

 

• NULL, NOT NULL : 필드에 null 허용 여부

• AUTO_INCREMENT : 처음에 Zero라는 사람의 데이터를 넣으면 MySQL은 알아서 id로 1번을 부여. 다음에 Nero라는 사람의 데이터를 넣으면 자동으로 id 2번을 부여

• ZEROFILL : INT(4)인데 숫자 1을 넣었다면 0001이 된다. 즉 빈자리를 0으로 채우는 옵션이다.

• DEFAULT now() : 해당 컬럼 값이 null이면 현재 시간을 디폴트로 삽입.

• PRIMARY KEY : 로우들을 식별할 수 있는 PK설정

• UNIQUE INDEX는 해당 값(name 컬럼)이 고유해야 하는지에 대한 옵션이다. 인덱스의 이름은 name_UNIQUE로, name 컬럼을 오름차순(ASC)으로 기억하겠다는 것이다. (내림차순은 DESC)

• UNIQUE INDEX의 경우, PRIMARY KEY처럼 조회시 속도가 빠르다.

 

• COMMENT : 테이블에 대한 보충 설명(역할 등)을 적는 곳

• DEFAULT CHARACTER SET을 utf8로 설정하지 않으면 한글이 입력되지 않는다.

• ENGINE : 여러 가지가 있지만, MyISAM과 InnoDB가 제일 많이 사용된다.

mysql> DESC users;

- 테이블 생성 확인

mysql> DROP TABLE users;

- 테이블 제거

mysql> CREATE TABLE nodejs.comments (
  -> id INT NOT NULL AUTO_INCREMENT,
  -> commenter INT NOT NULL,
  -> comment VARCHAR(100) NOT NULL,
  -> created_at DATETIME NOT NULL DEFAULT now(),
  -> PRIMARY KEY(id),
  -> INDEX commenter_idx (commenter ASC),
  -> CONSTRAINT commenter
  -> FOREIGN KEY (commenter)
  -> REFERENCES nodejs.users (id)
  -> ON DELETE CASCADE
  -> ON UPDATE CASCADE)
  -> COMMENT = '댓글'
  -> DEFAULT CHARSET=utf8mb4
  -> ENGINE=InnoDB;
Query OK, 0 row affected (0.09 sec)

- users 테이블을 commenter라는 컬럼으로 참조하고 있다. 

- CONSTRAINT [제약조건명] FOREIGN KEY [컬럼명] REFERENCES [참고하는 컬럼명]으로 외래키를 설정

- ON UPDATE와 ON DELETE는 모두 CASCADE로 설정 : 사용자가 수정, 삭제되면 연결된 댓글도 자동 수정 삭제.

mysql> SHOW TABLES;

- 생성된 테이블들을 보여준다.

7.5 CRUD 작업하기

- Create, Read, Update, Delete로 데이터 베이스에서 많이 수행하는 작업을 말한다.

 

7.5.1 create

- 튜플을 삽입하는 과정이다.

mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('zero', 24, 0, '자기소개1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('nero', 32, 1, '자기소개2');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO nodejs.comments (commenter, comment) VALUES (1, '안녕하세요. zero의 댓글입니다');
Query OK, 1 row affected (0.02 sec)

- id는 AUTO_INCREMENT에 의해, created_at은 DEFAULT 값에 의해 자동으로 삽입되었다.

 

7.5.2 read

- 데이터 베이스에서 튜플을 조회한다.

mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+----------+---------------------+
| id | name | age | married |  comment |          created_at |
+----+------+-----+---------+----------+---------------------+
| 1  | zero | 24  |       0 |자기소개1 | 2017-10-25 07:06:33 |
| 2  | nero | 32  |       1 |자기소개2 | 2017-10-25 09:25:40 |
+----+------+-----+---------+----------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT name, married FROM nodejs.users;
+------+---------+
| name | married |
+------+---------+
| zero |       0 |
| nero |       1 |
+------+---------+
2 rows in set (0.00 sec)

mysql> SELECT name, age FROM nodejs.users WHERE married = 1 AND age > 30;
+------+-----+
| name | age |
+------+-----+
| nero |  32 |
+------+-----+
1 row in set (0.00 sec)

mysql> SELECT id, name FROM nodejs.users WHERE married = 0 OR age > 30;
+----+------+
| id | name |
+----+------+
| 1  | zero |
| 2  | nero |
+----+------+
2 rows in set (0.01 sec)

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC;
+----+------+
| id | name |
+----+------+
| 2  | nero |
| 1  | zero |
+----+------+
2 rows in set (0.01 sec)

- 아래 예시는 LIMIT으로 조회할 튜플의 수를 제한하는 예시이다.

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC LIMIT 1;
+----+------+
| id | name |
+----+------+
| 2  | nero |
+----+------+
1 row in set (0.00 sec)

- OFFSET [건너뛸 숫자] 로 처음 1개를 건너뛰고 조회할 수 있다.

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC LIMIT 1 OFFSET 1;
+----+------+
| id | name |
+----+------+
| 1  | zero |
+----+------+
1 row in set (0.00 sec)

 

7.5.3 update

- 튜플 수정

mysql> UPDATE nodejs.users SET comment = '바꿀 내용' WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 

7.5.4 delete

- 튜플 삭제

mysql> DELETE FROM nodejs.users WHERE id = 2;
Query OK, 1 row affected (0.00 sec)