Practical learning: Let's talk about how Node.js operates the database

This article shares the actual combat of Node.js server and introduces the method of Node to operate the database. I hope it will be helpful to everyone!

This series uses node as a record of the operation process of server development. It records the main content and organizes the context of the process to start with. The scholar's method records the learning content and gradually learns node from 0 to 1. The node framework based on express is used in the tutorial. [Related tutorial recommendations: nodejs video tutorial, Programming teaching】

Connecting to the database

const mysql = require('mysql')
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '123123123',
  database: 'test',
  insecureAuth : true
})
const sql = `select *  from new_table`
db.query(sql, (err, results) => {
//   console.log(err)
  if(err){
    console.log(err.message)
  }else{
    console.log(results) //查询语句返回的是数组
  }
})

First An error was reported immediately after connecting to the database for the first time. What else can I do? Just search it on Google

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

It probably means that there are some operating permission issues involved and we need to go to the database. Execute this statement. If no error is reported, you can skip this step.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '这个地方替换成你的数据库密码';

Just execute it in mysqlworkbrench, and then go back to our code to continue the operation of connecting to the database

Proof when this statement is output The connection is already successful

insert statement

const obj = {
    name:'xiaoma',
    password:'666666'
}
const insertSql = `insert into new_table (name,password) values (?,?)`
db.query(insertSql,[obj.name,obj.password],(err,res)=>{
    if(err){
        console.log(err.message)
    }else{
        console.log(res)
    }
})

affectedRows is the affected row. The number of affected rows is 1, indicating that the insert statement was executed successfully, so we can modify the judgment of successful insert here

 if(res.affectedRows == 1){
    console.log('insert success')
}

Simplify the new sql

const obj = {
    name:'xiaoma',
    password:'123123'
}
const insertSql = `insert into new_table SET ?`
db.query(insertSql,obj,(err,res)=>{
    if(err){
        console.log(err.message)
    }
    if(res.affectedRows == 1){
        console.log('insert success')
    }
})

update Statement

const updateSql = `Update  new_table set  name=? ,password=? where id=?`
// const insertSql = `insert into new_table SET ?`
db.query(updateSql,[obj.name,obj.password,obj.id],(err,res)=>{
    if(err){
        console.log(err.message)
    }
    if(res.affectedRows == 1){
        console.log('insert success')
    }
})

//简化写法
const updateSql = `Update  new_table set ? where id=?`
db.query(updateSql,[obj,obj.id],(err,res)=>{
})

delete statement

const updateSql = `delete from  new_table  where id=?`
db.query(updateSql,5,(err,res)=>{
    if(err){
        console.log(err.message)
    }
    if(res.affectedRows == 1){
        console.log('insert success')
    }
})

For more node-related knowledge, please visit: nodejs tutorial!

The above is the detailed content of Practical learning: Let's talk about how Node.js operates the database. For more information, please follow other related articles on the PHP Chinese website!