[노드교과서] 7장 MySQL

공부

[노드교과서] 7장 MySQL

실버dev 2018. 8. 15. 18:16

지금까지 모든 데이터를 변수에 저장했음. 변수에 저장된 값은 서버가 종료되면 사라져 버린다. 이를 방지하기 위해 데이터베이스를 사용해야함.

1. 데이터베이스란?

데이터베이스는 관련성을 가지며 중복이 없는 데이터들의 집합이다. 이러한 데이터베이스를 관리하는 시스템을 DBMS라고 부름.

데이터베이스에 저장된 데이터는 서버 종료 여부와 상관없이 데이터를 계속 사용할 수 있고 서버에 데이터베이스를 올리면 여러 사람이 동시에 사용할 수 있다.

Mysql은 RDBMS(관계형 데이터베이스)임.

2. MySQL 설치하기

MySQL 사이트가서 서버랑 워크벤치만 설치한다.

mysql -h localhost -u root -p 콘솔에 입력 후 비밀번호 적으면 mysql에 접속가능하다.

-h는 접속할 주소 -u는 사용자명 -p 는 비밀번호를 의미함.

exit으로 mysql을 나갈 수 있음.

3. 워크벤치 설치하기

윈도우는 Mysql 설치할 때 같이했음.

커넥션 한번 생성해보고 마침

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

워크벤치로 하는건 그냥 보면 아니깐 생략함

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

CREATE SCHMA DB이름 이 데이터베이스를 생성하는 명령어임.

mysql> CREATE SCHEMA nodejs;

Query OK, 1 row affected (0.08 sec)

생성완료

CREATE SCHMA 같이 Mysql의 기본 구문을 예약어라고 부름. 예약어는 사용자 정의 이름과 구분하기 위해 대문자로 쓰는게 좋다.

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 CHARSET = utf8

-> ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.33 sec)

한 줄로 써도 되지만 읽기 어려우니깐 여러 줄로 나눠 쓴다. ; 세미콜론 쓰기 전까지는 실행되지 않음.

CREATE TABLE 데이터베이스명.테이블명 => 해당 데이트베이스 내에 테이블을 생성하는 명령어임.

그 이후 한 줄씩 컬럼들을 만들었음.

자료형 정리

id, name, age, married, comment, created_at이 컬럼이름이고 그 옆이 자료형.

INT – 정수를 의미. 소수까지 저장하려면 FLOAT이나 DOUBLE 사용.

VARCHAR(자릿수) – 가변길이 문자열. CHAR(자릿수)는 고정길이임.

TEXT – 긴글

TINYINT - -127~128까지의 정수. 1또는 0만 저장한다면 Boolean처럼 사용할 수 있음.

DATETIME – 날짜와 시간

그외의 옵션들

NULL, NOT NULL -> 빈칸 허용 여부.

AUTO_INCREAMENT – 숫자를 자동으로 하나씩 올리겠다는 뜻. id에 사용했음

UNSIGNED – 음수불가

ZEROFILL – 숫자의 자릿수가 고정되어 있을 때( INT(자릿수) ) 비어있는 자리에 0으로 채움.

DEFAULT now() – 해당 컬럼에 값이 없을 때 Mysql이 기본값을 대신 넣어줌. now()는 현재 시각을 넣으라는 의미임.

PRIMARY KEY – 해당 컬럼이 기본 키인 경우 사용. 기본 키란 로우를 대표하는 고유한 값임. 주민등록번호, 학번 같은 개념. 그냥 다른 로우랑 구분할 수 있는 중복이 안되는 유니크한 값을 지정하는것임.

UNIQUE INDEX – 해당 값이 고유해야 하는지에 대한 옵션

이 위로는 컬럼에 대한 설정이고 아래는 테이블 자체에 대한 설정

COMMENT - 테이블에 대한 보충 설명.

DEFAULT CHARSET – UTF-8로 설정하지 않으면 한글이 입력되지 않으므로 필수임.

ENGINE – 여러가지가 있지만 이책에선 INNODB를 엔진으로 사용함.

만들어진 테이블을 확인하는 명령어

mysql> DESC nodejs.users;

+------------+------------------+------+-----+-------------------+----------------+

+------------+------------------+------+-----+-------------------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(10) unsigned | NO | | NULL | |

+------------+------------------+------+-----+-------------------+----------------+

6 rows in set (0.00 sec)

USE nodejs를 사용안해서 데이터베이스명까지 적어야 했음.

USE nodejs로 nodejs데이터베이스로 들어가면 DESC users; 만 써도 동작함

테이블 삭제

mysql> DROP TABLE users;

Query OK, 0 rows affected (0.17 sec)

comment 테이블도 만든다

CREATE TABLE `nodejs`.`comments` (

`id` INT NOT NULL AUTO_INCREMENT,

`commenter` INT NOT NULL,

`comment` VARCHAR(100) NOT NULL,

`created_at` DATETIME NULL,

PRIMARY KEY (`id`),

INDEX `commenter_idx` (`commenter` ASC) VISIBLE,

CONSTRAINT `commenter`

FOREIGN KEY (`commenter`)

REFERENCES `nodejs`.`users` (`id`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8

COMMENT = '댓글';

귀찮아서 워크벤치로 만들었음.

commenter 컬럼에는 댓글을 작성한 사용자의 id를 저장할 것임. 이렇게 다른 테이블의 기본 키를 저장하는 컬럼을 외래 키라고 부른다.

CONSTRAINT 제약조건명 FOREIGNKEY 컬럼명 REFERENCES 참고하는 컬럼명. 이렇게 사용함.

ON UPDATE와 ON DELETE는 CASCADE로설정함.

이는 사용자 정보가 수정되거나 삭제되면 그것과 연결된 댓글 정보도 같이 수정하거나 삭제한다는 의미임.

mysql> SHOW TABLES;

+------------------+

| Tables_in_nodejs |

+------------------+

| comments |

| users |

+------------------+

2 rows in set (0.00 sec)

생성한 테이블 확인하기

5. CRUD 작업하기

CRUD란? CREATE, READ, UPDATE, DELETE

5-1) CREATE

mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('kim', 24, 0, '안녕하세요')

-> ;

Query OK, 1 row affected (0.10 sec)

INSERT INTO 테이블명 ([칼럼1]….[칼럼10]) VALUES ([값1]…..[값10]) 이렇게 입력하면 됨.

댓글도 추가하자

mysql> INSERT INTO comments (commenter, comment) VALUES (1, '안녕하세요 댓글');

Query OK, 1 row affected (0.07 sec)

5-2) Read 조회

mysql> SELECT * FROM nodejs.users;

+----+------+-----+---------+------------+---------------------+

+----+------+-----+---------+------------+---------------------+

| 1 | kim | 24 | 0 | 안녕하세요 | 2018-08-15 14:39:37 |

| 2 | park | 30 | 1 | 반가워요 | 2018-08-15 14:43:13 |

+----+------+-----+---------+------------+---------------------+

2 rows in set (0.01 sec)

SELECT * FROM [테이블명] 형식이다.

mysql> SELECT name, married FROM users;

+------+---------+

| name | married |

+------+---------+

| kim | 0 |

| park | 1 |

+------+---------+

2 rows in set (0.00 sec)

특정 컬럼만 조회도 가능하다.

mysql> SELECT name, married FROM users WHERE married = 1 AND age > 25;

+------+---------+

| name | married |

+------+---------+

| park | 1 |

+------+---------+

1 row in set (0.00 sec)

WHERE절을 사용해 특정 조건을 가진 데이터만 조회 가능하다.

여기서 AND와 OR도 사용할 수 있다.

mysql> SELECT * FROM users ORDER BY age DESC;

+----+------+-----+---------+------------+---------------------+

+----+------+-----+---------+------------+---------------------+

| 2 | park | 30 | 1 | 반가워요 | 2018-08-15 14:43:13 |

| 1 | kim | 24 | 0 | 안녕하세요 | 2018-08-15 14:39:37 |

+----+------+-----+---------+------------+---------------------+

2 rows in set (0.00 sec)

ORDER BY 컬럼 DESC/ASC 로 정렬해서 볼 수 있다. DESC는 내림차순 ASC는 오름차순이다.

mysql> SELECT * FROM nodejs.users LIMIT 1;

+----+------+-----+---------+------------+---------------------+

+----+------+-----+---------+------------+---------------------+

| 1 | kim | 24 | 0 | 안녕하세요 | 2018-08-15 14:39:37 |

+----+------+-----+---------+------------+---------------------+

1 row in set (0.00 sec)

LIMIT명령어로 조회할 로우 개수를 설정할 수 있다.

mysql> SELECT * FROM nodejs.users LIMIT 1 OFFSET 1;

+----+------+-----+---------+----------+---------------------+

+----+------+-----+---------+----------+---------------------+

| 2 | park | 30 | 1 | 반가워요 | 2018-08-15 14:43:13 |

+----+------+-----+---------+----------+---------------------+

1 row in set (0.00 sec)

OFFSET 숫자 명령어는 설정한 개수만큼의 로우를 뛰어넘고 조회한다. 게시판등의 페이지 기능을 만들 때 유용하다.

5-3 UPDATE 수정

mysql> UPDATE users SET comment = "수정했음" WHERE id = 2;

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

UPDATE 테이블명 SET 컬럼명=바꿀내용 WHERE 조건이다.

mysql> SELECT * FROM users;

+----+------+-----+---------+------------+---------------------+

+----+------+-----+---------+------------+---------------------+

| 1 | kim | 24 | 0 | 안녕하세요 | 2018-08-15 14:39:37 |

| 2 | park | 30 | 1 | 수정했음 | 2018-08-15 14:43:13 |

+----+------+-----+---------+------------+---------------------+

2 rows in set (0.00 sec)

수정됨을 확인할 수 있다.

mysql> SELECT * FROM users;

+----+------+-----+---------+-----------------+---------------------+

+----+------+-----+---------+-----------------+---------------------+

| 1 | kim | 24 | 0 | WHERE은 안쓰면? | 2018-08-15 14:39:37 |

| 2 | park | 30 | 1 | WHERE은 안쓰면? | 2018-08-15 14:43:13 |

+----+------+-----+---------+-----------------+---------------------+

2 rows in set (0.00 sec)

WHERE로 조건을 안주면 모든 로우의 값을 수정한다. 조심하자.

5-4) DELETE 삭제

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

Query OK, 1 row affected (0.02 sec)

DELETE FROM 테이블 WHERE 조건

6. 시퀄라이즈 사용하기.

시퀄라이즈란? 노드에서 MySQL 데이터베이스 작업을 쉽게 할 수 있도록 도와주는 라이브러리임.

시퀄라이즈는 ORM임. 자바스크립트 객체와 데이터베이스의 릴레이션을 매핑해주는 도구임.

시퀄라이즈를 Mysql뿐만 아니라 MariaDB같은 다른 데이터베이스랑도 같이 쓸 수 있음.

이걸 쓰는 이유는 자바스크립트 구문을 알아서 SQL로 바꿔주기 때문임.

따라서 SQL 언어를 쓰지 않고 자바스크립트만으로 Mysql을 조작할 수 있게된다

express-generator로 생성하고 myspl2 sequelize 두 패키지를 설치함.

sequelize init 명령어 호출

그리고 생성된 /models/index.js에 가서

const path = require('path');

const Sequelize = require('sequelize');

const env = process.env.NODE_ENV || 'development';

const config = require(path.join(__dirname, '..', 'config', 'config.json'))[env];

const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);

db.sequelize = sequelize;

db.Sequelize = Sequelize;

module.exports = db;

으로 고친다.

무슨 뜻인지 모르겠지만 sequelize-cli가 자동으로 생성해주는 코드는 그대로 사용했을 때 에러가 발생하고 필요 없는 부분도 많으므로 이렇게 수정하라고 함.

6-1) MySQL과 연결하기

const sequelize = require('./models').sequelize;

sequelize.sync();

sync 메서드가 실행되면 알아서 MySQL과 연동된다고 한다.

6-2) 모델 정의하기

/models/user.js

module.exports = (sequelize, DataTypes) => {

return sequelize.define(

"user",

{

name: {

type: DataTypes.STRING(20),

allowNull: false,

unique: true

age: {

type: DataTypes.INTEGER.UNSIGNED,

allowNull: false

married: {

type: DataTypes.BOOLEAN,

allowNull: flase

comment: {

type: DataTypes.TEXT,

allowNull: true

created_at: {

type: DataTypes.DATE,

allowNull: false,

defaultValue: sequelize.literal("now()")

}

{

timestamps: false

}

);

};

시퀄라이즈는 알아서 id를 기본 키로 연결하므로 id 컬럼은 적어줄 필요가 없다. sequelize.define 메서드로 테이블명과 각 컬럼의 스펙을 입력한다. MySQL 테이블과 컬럼 내용이 일치해야 정확하게 대응됨.

시퀄라이즈 자료형은 Mysql과 조금다름. VARCHAR는 STRING, INT는 INTERGER, TINYINT는 BOOLEAN, DATETIME은 DATE로 적는다.

define의 세번째 인자는 테이블 옵션이다. timestamps 속성은 createdAt과 updatedAt 컬럼을 자동으로 추가한다. 로우가 생성될 때와 수정될 때의 시간을 입력함. 우리는 created_at 컬럼을 직접 만들었으니 false로 값을 줬다.

module.exports = (sequelize, DataTypes) => {

return sequelize.define(

"comment",

{

commen: {

type: DataTypes.STRING(100),

allowNull: false

created_at: {

type: DataTypes.DATE,

allowNull: true,

defaultValue: sequelize.literal("now()")

}

{

timestamps: flase

}

);

};

/models/comment.js도 만들자.

commenter 컬럼이 없다. 이 부분은 모델을 정의할 때 넣어도 되는데 시퀄라이즈 자체에서 관계를 정의할 수 있어서 넘어감.

model/index.js에 연결하자

db.User = require('./user')(sequelize, Sequelize);

db.Comment = require('./comment')(sequelize, Sequelize);

그리고 config/config.json에 패스워드 채워넣자

"development": {

"username": "root",

"password": "패스워드",

"database": "nodejs",

"host": "127.0.0.1",

"dialect": "mysql",

"operatorsAliases": false

operatorsAliases는 보안에 취약한 연산자를 사용할지 여부를 설정하는 옵션이다.

그리고 모델이름은 user, comment 단수형으로 정의했는데 이 테이블이름은 users comments 복수형으로 사용함.

6-3) 관계 정의하기

users테이블과 comments 테이블의 관계 정의하기.

1대 다 관계와 다대 다 관계가 있음.

1대 다는 댓글과 같이 사용자는 댓글을 여러 개 작성할 수 있지만 댓글은 사용자가 여러명일 수 없는 것과 같은 관계임.

다대 다 는 해쉬태그와 게시글과 같은 관계임. 하나의 게시글에 여러 해쉬태그가 있을 수 있고 하나의 해쉬태그도 여러 개의 게시글에서 사용할 수 있음.

6-3-1) 1:N

users 테이블의 로우 하나를 불러올 때 연결된 모든 comments 테이블의 로우들도 같이 불러올 수 있음. 반대로 comments를 불러올 때도 연결된 users를 불러올 수 있다.

전자는 hasMany 메서드를 쓰고 후자는 belongsTo 메서드를 사용한다.

db.User.hasMany(db.Comment, {foreignKey: 'commenter', sourceKey: 'id'});

db.Comment.belongsTo(db.User, {foreignKey: 'commenter', targetKey: 'id'});

/model/index.js

이렇게쓴다.

npm start 하니까

Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(20) NOT NULL UNIQUE, `age` INTEGER UNSIGNED NOT NULL, `married` TINYINT(1) NOT NULL, `comment` TEXT, `created_at` DATETIME NOT NULL DEFAULT now(), PRIMARY KEY (`id`)) ENGINE=InnoDB;

Executing (default): SHOW INDEX FROM `users` FROM `nodejs`

Executing (default): CREATE TABLE IF NOT EXISTS `comments` (`id` INTEGER NOT NULL auto_increment , `comment` VARCHAR(100) NOT NULL, `created_at` DATETIME DEFAULT now(), `commenter` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`commenter`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

Executing (default): SHOW INDEX FROM `comments` FROM `nodejs`

자동으로 SQL문을 만들어준다.

6-3-2) 1:1관계

hasMany대신 hasOne aptjem를 사용한다.

6-3-3) N:M 관계

belongsToMany메서드를 사용한다.

db.Post.belongsToMany(db.Hashtag, { through: 'PostHashtag'});

db.Hashtag.belongsToMany(db.Post, { through: 'PostHashtag'});

이렇게 쓴다.

시퀄라이즈는 이 과정을 편하게 하도록 몇가지 메서드를 제공함.

get + 모델이름의 복수형 이나 add 메서드가 가 예임.

6-4 쿼리 알아보기

SQL문 : INSERT INTO nodejs.users (name, age, married, comment) VALUES (‘kim’, 24, 0, ‘자기소개’);

const { User } = require('../models')

User.create({

name: 'zero',

age: 24,

married: false,

comment: '자기소개'

})

SELECT * FROM nodejs.users;

User.findAll({});

SELECT * FROM nodejs.users LIMIT 1;

User.find({});

SELECT name, married FROM nodejs.users;

User.findAll({

attributes: ["name", "married"]

});

SELECT name, age FROM nodejs.users WHERE married = 1 AND age > 30;

const { User, Sequelize: { Op }} = require("../models");

User.findAll({

attributes: ["name", "age"],

where: {

married: 1,

age: { [Op.gt]: 30 }

}

});

Op.gt : 초과, Op.gte: 이상, Op.lt: 미만, Op.lte: 이하, Op.ne: 같지않음, Op.or 또는, Op.in 배열요소 중 하나, Op.notIn : 배열 요소가 아님

SELECT id, name FROM users ORDER BY age DESC LIMIT 1 OFFSET 1

User.findAll({

attributes: ["id", "name"],

order: ['age', 'DESC'],

limit: 1,

offset: 1

});

UPDATE nodejs.users SET comment = “수정완료” WHERE id = 2

User.update(

{

comment: "수정완료"

{

where: { id: 2 }

}

);

DELETE FROM nodejs.users WHERE id = 2;

User.delete({

where: { id: 2}

});

7.6.5 쿼리 수행하기.