node操作mysql数据库实例

const Koa = require("koa");
const Router = require("koa-router");
//要引入promise类
const mysql = require("mysql2/promise");

const app = new Koa();
const router = new Router();

//实现实现对USERS表增删改查
//使用Promise
(async () => {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "zhangpp",
    database: "zpp_web",
  });

  //addUser
  router.get("/adduser", async (ctx) => {
    let { name, age } = ctx.query;
    console.log(name, age);
    let sql = `INSERT INTO users (id,name,age) VALUES (0,?,?)`;
    let [row] = await connection.execute(sql, [name, +age]);
    if (row.affectedRows) {
      ctx.body = "add user scuuess";
    } else ctx.body = "add user fail";
  });

  //deluser
  router.get("/deluser", async (ctx) => {
    let { id } = ctx.query;
    let sql = "DELETE FROM users WHERE id=?";
    let [row] = await connection.execute(sql, [id]);
    if (row.affectedRows) ctx.body = "del user seccuss";
    else ctx.body = "del user fail";
  });

  //updateuser
  router.get("/updateuser", async (ctx) => {
    let { age, id } = ctx.query;
    let sql = `UPDATE  users SET age=? where id=? `;
    let [row] = await connection.execute(sql, [age, id]);
    if (row.affectedRows) ctx.body = "update user seccuss";
    else ctx.body = "update user fail";
  });

  //finduser
  router.get("/finduser", async (ctx) => {
    let { id } = ctx.query;
    let sql=`SELECT * FROM users WHERE id=?`;
    let [row]=await connection.execute(sql,[id]);
    ctx.body=row;
  });
  //findeUsers
  router.get('/findusers',async (ctx)=>{
      let {age,ofset=0,limit=5}=ctx.query;
      let sql=`SELECT * FROM users where age>? ORDER BY age LIMIT ?,?`;
      let [row]=await connection.execute(sql,[age,ofset,limit]);
      ctx.body=row;
      
  })
})();

app.use(router.routes());

app.listen(8080, () => {
  console.log("创建成功,端口：8080");
});

学习一下ORM 操作数据库

const { Sequelize } = require("sequelize");
//使用ORM 对象关系型数据库

const getUserModel = require("./user_model");

//连接mysql 数据库
const sequelize = new Sequelize({
  dialect: "mysql", //连接的数据
  host: "localhost",
  port: "3306",
  username: "root",
  password: "zhangpp",
  database: "zpp_web",
});
//获取user model 对象

var userModel = getUserModel(sequelize);
//对象初始化到数据库表
sequelize.sync(); //同步，如果表发生变化不会同步过去
// sequelize.sync({force:true}) //同步，强制同步

创建user_model.js
const {Model,DataTypes}=require('sequelize');

class userModel extends Model{
}

module.exports=(sequelize)=>{
    userModel.init({
        id:{
            type:DataTypes.INTEGER,
            primaryKey:true,
            autoIncrement:true,
            allowNull:false,
        },
        name:{
            type:DataTypes.STRING,
            allowNull:false,
        },
        age:{
            type:DataTypes.INTEGER,
            allowNull:false
        }
    },{// 第二个参数设置表的信息
        tableName:'users',  //表名 
        sequelize,   //sequelize实例 
        createdAt:false,
        updatedAt:false
    })

    return userModel;
}

 改造mysql2操作数据库通过sequelize对象实现

创建
 let row = await userModel.create({
    name,age,});
  ctx.body = row;
删除
let row = await userModel.destroy({
    where: {
      id,},});
  修改
let row = await userModel.update(
    {age},
    {where: {id }}
  );
查找
let row = await userModel.findByPk(+id); //通过主键id查询
多条件查找  
let row = await userModel.findAll({
    where: {
      age: {
        [Op.gt]: age,
      },
    },
    order: [["age", "DESC"]],
    offset: +ofset,
    limit: +limit,
  });