Express 中进行数据库操作

1. 配置 MySql 数据库环境

npm i mysq

2.基本配置

	// 1.导入mysql模块
    const mysql = require('mysql')
    // 2.创建mysql的连接对象
    const conn = mysql.createConnection({
        host:'localhost',
        user:'root',
        password:'root',
        database:'express-mysql'
    })

3.实现 CRUD

• 查询

 // 查询conn.query('sql语句',回调函数)
      const sql ='SELECT * FROM `user` '
      conn.query(sql,(err,result) => {
          if(err) return console.log('获取数据失败'+err.message)
          console.log(result) 
          //[ RowDataPacket { id: 1, name: 'cc', age: 16, gender: 'girl' } ]
      })

• 新增

 	  const user = {name:'zz',age:22,gender:'boy'}
      const sql = "INSERT INTO `user` SET ?"
      conn.query(sql,user,(err,result) => {
          if(err) return console.log('插入数据失败'+err.message)
          console.log(result) 
          // OkPacket {
          //     fieldCount: 0,
          //     affectedRows: 1,
          //     insertId: 6,
          //     serverStatus: 2,
          //     warningCount: 0,
          //     message: '',
          //     protocol41: true,
          //     changedRows: 0 
          // }
      })

• 修改

 	  const user ={id:6,name:'zz',age:18}
      const sql = 'UPDATE `user` SET ? WHERE id = ?'
      //若sql语句中包含多个?占位符,第二个实参必须传递一个数组,并一一对应
      conn.query(sql,[user,user.id],(err,result) => {
          if(err) return console.log("修改数据失败")
          console.log(result)
          // OkPacket {
          //     fieldCount: 0,
          //     affectedRows: 1,
          //     insertId: 0,
          //     serverStatus: 2,
          //     warningCount: 0,
          //     message: '(Rows matched: 1  Changed: 1  Warnings: 0',
          //     protocol41: true,
          //     changedRows: 1 
          // }
      })

• 删除

      const sql = 'DELETE FROM `user` WHERE id=?'
      conn.query(sql, 1, (err, result) => {
          if (err) return console.log('删除失败！' + err.message)
          console.log(result)
          // OkPacket {
          //     fieldCount: 0,
          //     affectedRows: 1,
          //     insertId: 0,
          //     serverStatus: 2,
          //     warningCount: 0,
          //     message: '',
          //     protocol41: true,
          //     changedRows: 0 
          // }
      })