node.js:mySQL 데이터 조회,수정,삭제(select,update,delete)

const mysql = require('mysql');
const connection = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'admin123',
    database:'practice',
    port:'3306'
});

connection.connect();

//#1
connection.query('SELECT * FROM practiceTable',(error,results, fields)=>{
    if(error) throw error;
    console.log('#1');
    console.log(results);
})

//#2
connection.query('SELECT number,genre ,name ,writer ,releasedate FROM practiceTable',(error,results, fields)=>{
    if(error) throw error;
    console.log('#2');
    console.log(results);
})

//#3
connection.query('SELECT * FROM practiceTable WHERE genre =\'action\'',(error,results, fields)=>{
    if(error) throw error;
    console.log('#3');
    console.log(results);
})

//#4
connection.query('SELECT * FROM practiceTable WHERE genre =\'action\' OR genre=\'romance\'',(error,results, fields)=>{
    if(error) throw error;
    console.log('#4');
    console.log(results);
})

//#5
connection.query('SELECT * FROM practiceTable WHERE releasedate LIKE \'2021%\'',(error,results, fields)=>{
    if(error) throw error;
    console.log('#5');
    console.log(results);
})

//#6
connection.query('SELECT number, genre, name, writer, releasedate from practiceTable order by releasedate',(error,results, fields)=>{
    if(error) throw error;
    console.log('#6');
    console.log(results);
})

//#7
connection.query('SELECT number, genre, name, writer, releasedate from practiceTable order by releasedate desc',(error,results, fields)=>{
    if(error) throw error;
    console.log('#7');
    console.log(results);
})

 

콘솔 결과값

 

 

 

수정하기(update)

// mysql 모듈 사용
const mysql = require('mysql');
// 연결할 DB 정보입력
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin123',
  database: 'practice',
  port: '3306',
});
// 데이터베이스 연결
connection.connect();

// Update 쿼리문 사용, 한 필드 수정(genre 변경)
connection.query("UPDATE practiceTable SET genre = 'comic' WHERE number = 2 AND name = 'First Love';"
  , (error, results, fields) => {
    if (error) throw error;
    console.log(results);
  });
// Select 쿼리문 사용
connection.query('SELECT * FROM practiceTable', (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});
// 연결 종료
connection.end();

콘솔값이렇게 찍힘

 

 

 

 

 

 

삭제하기(delete)

// mysql 모듈 사용
const mysql = require('mysql');
// 연결할 DB 정보입력
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin123',
  database: 'practice',
  port: '3306',
});
// 데이터베이스 연결
connection.connect();
// Delete 쿼리문 사용
connection.query("DELETE FROM practiceTable WHERE number = 2 AND writer = 'B.unknown';", (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});
// Select 쿼리문 사용
connection.query('SELECT * FROM practiceTable', (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});
// 연결 종료
connection.end();

콘솔창

결과