1. nodejs의 sequelize 라이브러리를 이용한 mysql연결 (1) - mysql 세팅

1. mysql installer를 이용하여 mysql connector 설치

 이 때 아이디는 root이며 비밀번호는 우선 12351235로 설정

 

또한 workbench도 같이 설치

 

mysql installer download 링크

https://dev.mysql.com/downloads/file/?id=490394

MySQL :: Begin Your Download

리눅스의 경우 아래를 따른다.

$ sudo apt-get update
$ sudo apt-get install -y mysql-server-5.7
$ mysql_secure_installation

 

 

2. db연결 및 테이블 생성

C:\Program Files\MySQL\MySQL Server 8.0\bin 로 들어가서 mysql 실행

mysql -h localhost -u root -p
Enter password: 12351235
mysql>

 

nodejs 스키마를 생성하고 이 스키마를 사용하겠다는 것을 MySQL에 알림

 

mysql> CREATE SCHEMA nodejs;
Query OK, 1 row affected (0.01 sec)

mysql> use nodejs;
Database changed

 

nodejs 데이터베이스의 users 테이블 생성

아래 코드를 입력한다.

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 CHARSET=utf8
ENGINE=InnoDB;

 

결과

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 CHARSET=utf8
    -> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)

 

table 정보 확인

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

 

그 뒤, 사용자의 댓글을 저장하는 테이블 생성

아래 코드 입력

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=utf8
ENGINE=InnoDB;

 

결과

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=utf8
    -> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.06 sec)

테이블명 확인

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

 

워크벤치 GUI를 이용하여 테이블을 만드는 것이 일반적임.

 

3. CRUD 작업하기

CREATE (행 생성)

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

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

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

 

READ (조회)

mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  1 | zero |  24 |       0 | 자기소개1 | 2019-11-03 23:00:10 |
|  2 | nero |  32 |       1 | 자기소개2 | 2019-11-03 23:00:44 |
+----+------+-----+---------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM nodejs.comments;
+----+-----------+--------------------------------+---------------------+
| id | commenter | comment                        | created_at          |
+----+-----------+--------------------------------+---------------------+
|  1 |         1 | 안녕하세요. zero의 댓글입니다. | 2019-11-03 23:03:32 |
+----+-----------+--------------------------------+---------------------+
1 row in set (0.00 sec)

 

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

 

DELETE (삭제)

mysql> DELETE FROM nodejs.users WHERE id = 2;