node.js로 웹서비스 만들기 (3. 기본적인 mysql query)

이번 글에서는 기본적인 query를 node에서 어떻게 사용해야 하는지에 대해 기록하도록 하겠습니다.

여기에서는 기본적은 query에 대해서만 설명하고 추후 개발하는 웹서버가 점점 형태를 갖추어 가면서 조금 복잡한 query를 다루게 되면 별도의 글로 남기도록 하겠습니다.

 

글에서 사용되는 quiz 테이블의 정보는 다음과 같습니다.

CREATE TABLE `quiz` (
	`qid` INT NOT NULL AUTO_INCREMENT,
	`createdAt` DATE NOT NULL,
	`category` VARCHAR(16) NOT NULL,
	`contents` VARCHAR(256) NOT NULL,
	`answer` VARCHAR(256) NULL,
	`comment` VARCHAR(256) NULL,
CONSTRAINT quiz_pk PRIMARY KEY(qid));

 

SELECT query

제일 많이 사용하는 쿼리로 제일 많이 알아야 하는 쿼리입니다.

 

1. 무지성으로 테이블에 저장된 모든 정보를 읽어오고자 할 때는 아래와 같이 쿼리를 작성합니다.

   > SELECT * FROM quiz;

 

2. qid, createdAt, contents 정보만 읽어오고자 할 때는 아래와 같이 쿼리를 작성합니다. 

   > SELECT qid, createdAt, contents FROM quiz;

 

3. createdAt를 중복을 제거하고 읽어오고자 할 때는 아래와 같이 쿼리를 작성합니다.

   > SELECT DISTINCT createdAt FROM quiz;

 

4. qid, createdAt, contents 정보를 createdAt순으로 정렬해서 읽어오고자할 때는 아래와 같이 작성합니다.

   > SELECT qid, createdAt, contents FROM quiz ORDER BY createdAt DESC; // 내림차순

   > SELECT qid, createdAt, contents FROM quiz ORDER BY createdAt ASC; // 오름차순

 

5. 페이지네이션이 적용되는 경우, 해당 페이지에 맞게 쿼리를 작성해 요청해야 합니다. 이때 사용하는 옵션이 LIMIT 옵션입니다.

    LIMIT 옵션은 아래와 같이 1개만 사용하면 해당 갯수 만큼만 읽어오지만

   > SELECT qid, createdAt, contents FROM quiz ORDER BY createdAt DESC LIMIT 20;

    두개를 사용하면 첫번째 파라미터의 갯수부터 시작해서 두번째 파라미터의 수만큼 읽어옵니다. 

   > SELECT qid, createdAt, contents FROM quiz ORDER BY createdAt DESC LIMIT 10, 20;

    위 쿼리는 quiz 테이블에 저장될 값을 createdAt으로 내림차순 정렬후 10번째 값부터 시작해서 20개를 읽어오라는 쿼리입니다.

// quiz.js
var sql = `SELECT qid, createdAt, contents FROM quiz ORDER BY createdAt DESC LIMIT ?, ?`;
var param = [10, 20];
conn.query(sql, param, function (err, rows, fields) {
    if(err) console.log('query is not excuted. select fail...\n' + err);
    else res.render('quizmng', {list: rows});
});

 

6. quiz 테이블에서 필요한  qid만 읽어오고자할때는 아래와 같이 쿼리를 작성합니다.

  > SELECT qid, createdAt, contents FROM quiz WHERE qid=5;

   qid를 여러개 읽어오자가 한다면 아래와 같이 쿼리를 작성합니다.

 

  > SELECT qid, createdAt, contents FROM quiz WHERE qid IN(5, 7, 6);

   추가로 내가 요청한 순서대로 읽어오고자 한다면 아래와 같이 쿼리를 작성합니다.

 

  > SELECT qid, createdAt, contents FROM quiz WHERE qid IN(5, 7, 6) ORDER BY FIELD(qid, 5, 7, 6);

// node.js
var sql = 'SELECT qid, createdAt, contents FROM quiz WHERE qid IN (?) ORDER BY FIELD(qid, ?)';
var qids = [5,7,6];
var param = [qids, qids];
conn.query(sql, param, function (err, result, fields) {
    if(err) console.log('query is not excuted. select fail...\n' + err);
    else {
        console.log('query result : ' + result);
        res.render('quizmng', {list: result});
    }
});

 

7. 테이블에 저장된 데이터 갯수를 얻어오고자 할 때는 아래와 같이 쿼리를 작성합니다.

  > SELECT COUNT(qid) as totalCount FROM quiz;

// quiz.js
var countSql = `SELECT COUNT(qid) AS totalCount FROM quiz`;
conn.query(countSql, function (err, cnt, fields) {    
    if(err) console.log('query is not excuted. select fail...\n' + err);
    else{
        var totalCount = cnt[0].totalCount;
        console.log('quiz totalCount: ' + totalCount);
    }
}

 

INSERT query

테이블에 데이터를 추가할 때 사용하는 쿼리로 아래와 같이 사용합니다.

 

quiz 테이블에 데이터 contents 추가

createdAt은 CURRENT_TIMESTAMP을 사용하여 현재 시간을 저장

var today = new Date();을 사용하는 방법도 있지만 CURRENT_TIMESTAMP가 더 편함.

qid는 AUTO_INCREMENT 속성이므로 별도로 추가하지 않아도 됨.

  > 'INSERT INTO quiz (createdAt, contents) VALUES(CURRENT_TIMESTAMP, ‘quiz_contents’)’;

// node.js
var contents = "퀴즈 콘텐츠입니다.";
var sql = 'INSERT INTO quiz (createdAt, contents) VALUES(CURRENT_TIMESTAMP, ?)';
var param = [contents];
conn.query(sql, param, function(err, result){
    if(err) console.log('query is not excuted. insert fail...\n' + err);
    else console.log(result);
});

 

UPDATE query

테이블에 저장된 데이터 정보를 변경할때 사용하는 쿼리로 아래와 같이 사용합니다.

  > 'UPDATE quiz SET createdAt=CURRENT_TIMESTAMP, contents=quiz_contents WHERE qid=5;

// node.js
var contents = '문제 변경입니다.';
var qid = 5;
var sql= 'UPDATE quiz SET createdAt=CURRENT_TIMESTAMP contents=? WHERE qid=?';
var param = [contents, qid];
conn.query(sql, param, function(err){
    if(err) console.log('query is not excuted. insert fail...\n' + err);
    else res.sendStatus(200);
});

 

DELETE query

테이블에 저장된 데이터를 삭제할때 사용하는 쿼리로 아래와 같이 사용합니다.

서비스에서 데이터 삭제를 하는 경우는 드물고 table에 삭제 여부를 판단하는 column 한개를 두고 이 값을 통해 삭제를 판단합니다.

테이블에 저장된 데이터를 삭제할때 사용하는 쿼리로 아래와 같이 사용합니다.

 

  > 'DELETE FROM quiz WHERE qid=5';

// node.js
var qid = 5;
var sql= 'DELETE FROM quiz WHERE qid=?';
var param = [qid];
conn.query(sql, param, function(err){
    if(err) console.log('query is not excuted. insert fail...\n' + err);
    else res.sendStatus(200);
});