7. MYSQL

윈도우는 그냥 깔면돼 공홈에서

git bash라면 mysql 앞에 winpty를 앞에 붙여야 한다.

mysql -h localhost -u root -p

-h뒤에는 접속할 주소를 ,-u 뒤에는 사용자명을 입력한다. -p는 비밀번호를 사용하겠다는 뜻이다.

 

리눅스(우분투)

sudo apt-get update

sudo apt-get install -y mysql-server

sudo mysql_secure_installation

비밀번호 설정 화면이 나오지 않고 설치가 완료된다면

mysqladmin -u root -p password 비밀번호 명령어로 비밀번호를 설정

 

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

데이터베이스 생성

CREATE SCHEMA `nodejs` DEFAULT CHARACTER SET utf8; (schema=database)

use nodejs;

 

테이블 생성

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;

INT 정수. 소수는 FLOAT, DOUBLE

VARCHAR(자릿수), CHAR(자릿수). CHAR는 고정 길이, VARCHAR는 가변길이TEXT는 긴 글. 수백자이내는 보통 VARCHAR, 그보다 길면 TEXT

TINYINT는 -128부터 127까지의 정수. TINYINT(1)이면 불리언가능

DATETIME은 날짜와 시간. 날짜만은 DATE, 시간만은 TIME

 

NULL, NOT NULL : 빈칸 허용할지AUTO_INCREMENT : 숫자 저절로 올려!!

UNSIGNED : 음수말고 양수만(FLOAT, DOUBLE은 UN 적용 불가능) (나이같은 음수없는 컬럼에 체크)

ZEROFILE : 숫자의 자릿수가 고정되어 있을때 사용할 수 있다. 자료형으로 INT 대신 INT(자릿수)처럼 표현하는 경우가 있다. 이때 ZEROFILL을 설정해두면 비어있는 자리에 모두 0을 넣는다.

DEFAULT : 말그대로 디폴트값

now() : 현재 시간. CURRENT_TIMESTAMP랑 같은 뜻

PRIMARY KEY : 고유키

UNIQUE INDEX : 해당 값이 고유해야 하는지

인덱스의 이름 : name_UNIQUE  : name 컬럼을 오름차순(ASC)으로 기억하겠다.

PRIMARY KEY와 UNIQUE INDEX는 조회 시 속도가 빠르다.

 

COMMENT는 테이블에 대한 보충 설명. 무슨 역할을 하는지 적어두면 돼. 필수아님DEFAULT CHARSET을 utf8로 설정하지 않으면 한글이 입력되지 않는다.(윈도우는 그냥 되는듯 저번에 리눅스에서 그래서 안됬나보다..)engine은 여러가지 있어. MyISAM과 InnoDB가 제일 많이 사용

 

테이블 확인

DESC users;

테이블 제거

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;

 

외래키 : CONSTRAINT [제약조건명] FOREIGN KEY [컬럼명] REFERENCES [참고하는 컬럼명]

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

Charset/Collation이 utf8이 아니라 utf8mb4야. 한글뿐만 아니라 이모티콘까지 넣고 싶다면 이거!!

 

CRUD 작업하기

create

INSERT INTO [테이블명] ([컬럼1], [컬럼2], ...) VALUES ([값1], [값2], ...)

 

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.00 sec)

 

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

 

read

SELECT * FROM [테이블명]

mysql> select * from nodejs.users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  1 | zero |  24 |       0 | 자기소개1 | 2021-02-22 20:52:54 |
|  2 | nero |  32 |       1 | 자기소개2 | 2021-02-22 20:53:11 |
|  3 | gd   |  22 |       1 | sdf       | 2021-02-22 21:01:21 |
+----+------+-----+---------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from nodejs.comments;
+----+-----------+-------------------------------+---------------------+
| id | commenter | comment                       | created_at          |
+----+-----------+-------------------------------+---------------------+
|  1 |         1 | 안녕하세요. zero의 댓글입니다 | 2021-02-22 20:57:32 |
+----+-----------+-------------------------------+---------------------+
1 row in set (0.00 sec)

 

SELECT name,married FROM nodejs.users;

SELECT id, name FROM nodejs.users WHERE married=0 OR age>30;

SELECT id,name FROM nodejs.users ORDER BY age DESC;

SELECT id,name FROM nodejs.users ORDER BY age DESC LIMIT 1;

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

 

update

UPDATE [테이블명] SET [컬럼명=바꿀 값] WHERE [조건]

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

 

delete

DELETE FROM [테이블명] WHERE [조건]

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

 

시퀄라이즈 사용하기

MYSQL 작업을 쉽게 할 수 있도록 도와주는 라이브러리

시퀄라이즈는 ORM(Object-relational Mapping)으로 분류된다. ORM은 자바스크립트 객체와 데이터베이스의 릴레이션을 매핑해주는 도구이다. 다른 데이터베이스도 가능

시퀄라이즈를 쓰는 이유는 자바스크립트 구문을 알아서 SQL로 바꿔주기 때문.

$ npm i express morgan nunjucks sequelize sequelize-cli mysql2

$ npm i -D nodemon
$ npx sequelize init 

npx sequelize db:create -- 이 코드로 db만들어줘

 

models/index.js 수정

const Sequelize = require('sequelize');

const env = process.env.NODE_ENV || 'development';
const config = require('../config/config')[env];
const db = {};

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

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

 

app.js

const express = require('express');
const path = require('path');
const morgan = require('morgan');
const nunjucks = require('nunjucks');

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

const app = express();
app.set('port', process.env.PORT || 3001);
app.set('view engine', 'html');
nunjucks.configure('views', {
  express: app,
  watch: true,
});
sequelize
  .sync({ force: false })
  .then(() => {
    console.log('데이터베이스 연결 성공');
  })
  .catch((err) => {
    console.error(err);
  });

app.use(morgan('dev'));
app.use(express.static(path.join(__dirname, 'public')));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));

app.use((req, res, next) => {
  const error = new Error(`${req.method} ${req.url} 라우터가 없습니다.`);
  error.status = 404;
  next(error);
});

app.use((err, req, res, next) => {
  res.locals.message = err.message;
  res.locals.error = process.env.NODE_ENV !== 'production' ? err : {};
  res.status(err.status || 500);
  res.render('error');
});

app.listen(app.get('port'), () => {
  console.log(app.get('port'), '번 포트에서 대기 중');
});

config/config.json

test와 production은 각각 테스트 용도와 배포 용도이다.

이 설정은 process.env.NODE_ENV가 development일 때 적용(기본적으로 development)

배포나 테스트 할때는 속성 수정

{
  "development": {
    "username": "root",
    "password": "[root 비밀번호]",
    "database": "nodejs",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

 

User와 Comment 모델을 만들어 users 테이블과 comments 테이블에 연결한다.

시퀄라이즈는 기본적으로 모델 이름은 단수형으로, 테이블 이름은 복수형으로 사용한다.

models/user.js

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
  static init(sequelize) {
    return super.init(
      {
        name: {
          type: Sequelize.STRING(20),
          allowNull: false,
          unique: true,
        },
        age: {
          type: Sequelize.INTEGER.UNSIGNED,
          allowNull: false,
        },
        married: {
          type: Sequelize.BOOLEAN,
          allowNull: false,
        },
        comment: {
          type: Sequelize.TEXT,
          allowNull: true,
        },
        created_at: {
          type: Sequelize.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW,
        },
      },
      {
        sequelize,
        timestamps: false,
        underscored: false,
        modelName: 'User',
        tableName: 'users',
        paranoid: false,
        charset: 'utf8',
        collate: 'utf8_general_ci',
      }
    );
  }

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

 

User 모델을 만들고 모듈로 exports 했다. 모델은 크게 static init 메서드와 static associate 메서드로 나뉜다.

init 메서드에서는 테이블에 대한 설정.

associate 메서드에서는 다른 모델과의 관계 

 

super.init 메서드의 첫번째 인수가 테이블 컬럼에 대한 설정.

두번째 인수가 테이블 자체에 대한 설정

 

첫번째 인수 테이블 컬럼에 대한 설정

id는 기본키로 연결해서 적을 필요 없어. 나머지 컬럼의 스펙 입력. 단, 조금달라

VARCHAR => STRING

INT => INTEGER

TINYINT => BOOLEAN

DATETIME => DATE

INT UNSIGNED => INT.UNSIGNED,

NOT NULL => allowNull : false

UNIQUE => unique : true

DEFAULT now() => defaultValue: Sequelize.NOW

 

두번째 인수 테이블 옵션

sequelize : static int 메서드의 매개변수와 연결되는 옵션으로 db.sequelize 객체를 넣어야 한다. 나중에 model/index.js에서 연결

timestamps : 현재 false, true이면 시퀄라이즈는 createdAt과 updeateAt 컬럼을 추가한다.

underscored : 시퀄라이즈는 기본적으로 테이블명과 컬럼명을 케멀 케이스(createdAt)로 만든다. 이를 스네이크 케이스(created_at)로 바꾸는 옵션

modelName : 모델 이름 설정. 노드 프로젝트에서 사용

tableName : 실제 데이터베이스의 테이블 이름. 기본적으로는 모델 이름을 소문자 및 복수형으로 만듬. 모델 이름이 User라면 테이블 이름은 users

paranoid : true로 설정하면 deletedAt이라는 컬럼이 생긴다. 로우를 삭제할 때 완전히 지워지지 않고 deletedAt에 지운 시각이 기록된다. 로우를 조회하는 명령을 내렸을 때는 deletedAt의 값이 null인 로우를 조회한다. 이는 나중에 로우를 복원하기 위해서야

charset과 collate : 각각 utf8과 utf8_general_ci로 설정해야 한글이 입력된다. 이모티콘까지 원하면 utf8mb4와 utf8mb4_general_ci 입력.

 

models/comment.js

const Sequelize = require('sequelize');

module.exports = class Comment extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      comment: {
        type: Sequelize.STRING(100),
        allowNull: false,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: true,
        defaultValue: Sequelize.NOW,
      },
    }, {
      sequelize,
      timestamps: false,
      modelName: 'Comment',
      tableName: 'comments',
      paranoid: false,
      charset: 'utf8mb4',
      collate: 'utf8mb4_general_ci',
    });
  }

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

 

외래키인 commter 컬럼이 없네?? 왤까요

 

관계 정의하기

1:N

//user
  static associate(db) {
    db.User.hasMany(db.Comment, { foreignKey: 'commenter', sourceKey: 'id' });
  }
  
//comment
  static associate(db) {
    db.Comment.belongsTo(db.User, { foreignKey: 'commenter', targetKey: 'id' });
  }

hasMany 메서드

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

반대로 belongsTo 메서드는 comments 테이블의 로우를 불러올 때 연결된 users 테이블의 로우를 가져온다.

다른 모델의 정보가 들어가는 테이블에 belongsTo를 사용.

hasMany 메서드에는 sourceKey 속성에 id를 넣고, belongsTo 메서드에서는 targetKey 속성에 id를 넣는다. 이 두 id 모두 User 모델의 id이다.

foreignKey를 따로 지정하지 않는다면 이름이 모델명+기본 키인 컬럼이 모델에 생성된다.

예를들어 commenter를 foreignKey로 직접 넣어주지 않았다면 user(모델명)+기본 키(id)가 합쳐진 UserId가 생성된다.

 

1:1

hasMany 메서드 대신 hasOne 메서드 사용

db.User.hasOne(db.Info, { foreignKey: 'UserId', sourceKey: 'id' });
db.Info.belongsTo(db.User, { foreignKey: 'UserId', targetKey: 'id' });

 

N:M

양쪽 모델에 모두 belongsToMany 메서드를 사용. N:M 관계의 특성상 새로운 모델이 생성. through 속성에 그 이름을 적으면 된다.

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

 

N:M에서는 데이터를 조회할 때 여러 단계를 거쳐야 한다.

자동으로 만들어진 모델들도 db.sequelize.models.PostHashtag로 접근할 수 있다.

 

쿼리 알아보기

INSERT

INSERT INTO nodejs.users (name, age, married, comment) VALUES ( 'zero', 24, 0, '자기소개1');
const {User} = require('../models');
User.create({
  name: 'zero',
  age: 24,
  married: false,
  comment: '자기소개1',
});

 

주의할 점은 MYSQL 자료형이 아니라 시퀄라이즈 모델에 정의한 자료형대로 넣어야 한다는 것. married가 false야

 

SELECT

SELECT * FROM nodejs.users;
User.fineAlll({});

SELECT * FROM nodejs.users LIMIT 1;
user.fineOne({});

SELECT name, married FROM nodejs.users;
User.findAll({
  attributes: ['name', 'married']
});

SLECT name, age FROM nodejs.users WHERE married=1 AND age>30;
const { Op } = require('sequelize');
const { User } = require('../models');
User.findAll({
  attributes: ['name', 'age'],
  where: {
    married: true,
    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 WHERE married=0 OR age>30;
const { Op } = require('sequelize');
const { User } = require('../models');
User.findAll({
  attributes: ['id', 'name'],
  where: {
    [Op.or]: [{ married: false }, { age: { [Op.gt]: 30 } }],
  },
});

SELECT id, name FROM users ORDER BY age DESC LIMIT 1 OFFSET 1;
User.findAll({
  attributes: ['id', 'name'],
  order: [['agae', 'DESC']],
  limit: 1,
  offset: 1,
});

 

UPDATE

UPDATE nodejs.users SET comment = '바꿀 내용' WHERE id=2;
User.update(
  {
    comment: '바꿀 내용',
  },
  {
    where: { id: 2 },
  }
);

 

DELETE

DELETE FROM nodejs.users WHERE id=2;
User.destroy({
  where: { id: 2},
});

 

관계 쿼리

findOne이나 findAll 메서드를 호출할 때 프로미스의 결과로 모델을 반환한다.(findAll은 모두 찾는 것이므로 모델의 배열을 반환한다.)

const user=await User.fineOne({});
console.log(user.nick); // 사용자 닉네임

이렇게 모델의 정보에도 바로 접근할 수 있지만 더 편리한 점은 관계 쿼리를 지원한다는 점이다. (MYSQL JOIN)

현재 User 모델은 comment 모델과 hasMany-belongsTo 관계가 맺어져 있다. 만약 특정 사용자를 가져오면서 그 사람의 댓글까지 모두 가져오고 싶다면 include 속성을 사용한다.

const user = await User.fineOne({
  include: [
    {
      model: Comment,
    },
  ],
});
console.log(user.Comments);

 

다음과 같이 댓글에 접근할 수도 있다.

const user = await User.findOne({});
const comments = await user.getComments();
console.log(comments);

관계를 설정했다면 getComments(조회), setComments(수정), addComment(하나 생성), addComments(여러 개 생성), removeComments(삭제) 메서드를 지원한다. 동사 뒤에 모델의 이름이 붙는 형식이다.

 

위에는 s가 붙고 밑에는 안붙네 원래 as그대로 당연히

 

동사 뒤의 모델 이름을 바꾸고 싶다면 관계 설정 시 as 옵션 사용

// 관계를 설정할 때 as로 등록
db.User.hasMany(db.Comment, {
  foreignKey: 'commenter',
  sourceKey: 'id',
  as: 'Answers',
});

// 쿼리할 때는
const user = await User.findOne({});
const comments = await user.getAnswers();
console.log(comments); // 사용자 댓글

 

include나 관계 쿼리 메서드에도 where나 attributes 같은 옵션 사용 가능

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

const user = await User.findOne({
  include: [
    {
      model: Comment,
      where: {
        id: 1,
      },
      attributes: ['id'],
    },
  ],
});
// 또는
const comments = await user.getComments({
  where: {
    id: 1,
  },
  attributes: ['id'],
});

 

관계 쿼리 시 조회는 위와 같지만 수정, 생성, 삭제 때는 조금 다르다.

항개

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

const user = await User.findOne({});
const comment = await Comment.create();
await user.addComment(comment);
// 또는
await user.addComment(comment.id);

여러개

const user = await User.findOne({});
const comment1 = await Comment.create();
const comment2 = await Comment.create();
await user.addComment([comment1, comment2]);

 

시퀄라이즈 쿼리 싫으면 SQL문 직접 사용할 수도 있어

const [result, metadata]=await sequelize.query('SELECCT * from comments');

 

Executing으로 시작하는 SQL 구문을 보고 싶지 않다면 config/config.json의 dialect 속성 밑에 "logging":false 추가

 

참조

ms3864.tistory.com/249?category=948683

7. 데이터베이스 언어 SQL

 

 

{
  sequelize,
  modelName: 'foo',

  // Using `unique: true` in an attribute above is exactly the same as creating the index in the model's options:
  indexes: [{ unique: true, fields: ['someUnique'] }]
}

 

시퀄라이즈 인덱스

sequelize.org/master/manual/indexes.html

Manual | Sequelize

 

primaryKey: true,

DataTypes.UUIDV4,