【node.js+mysql+vue2】实现简单接口的增删改查

本文学习于：使用vscode 搭建node.js 和 mysql 实现一个简单接口的增删改查 教程_vscode购物车代码js能增删改擦-CSDN博客https://blog.csdn.net/hzxOnlineOk/article/details/89638104

仅是学习笔记，如需详细教程请转到上面原文。

一、新建一个文件夹

  用vscode打开，执行npm i express --save 下载express框架。

  这时，页面如下：

二、左侧新建一个server.js文件

  页面如下：

var mysql = require("mysql");

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "chanyeol", // 密码
  port: "3306",
  database: "node_api" // 数据库名称
});

connection.connect();

var addSql = "INSERT INTO user(Id,name,phone,address,gender) VALUES(0,?,?,?,?)";
var addSqlParams = ["王紫薇", "1865656565", "天津万达广场", "女"];
//增
connection.query(addSql, addSqlParams, function (err, result) {
  if (err) {
    console.log("[INSERT ERROR] - ", err.message);
    return;
  }

  console.log("--------------------------INSERT----------------------------");
  //console.log('INSERT ID:',result.insertId);
  console.log("INSERT ID:", result);
  console.log(
    "-----------------------------------------------------------------\n\n"
  );
});

connection.end();

三、新建数据库node_api

  新建表user：

四、Ctrl+Shift+`  打开终端

  输入node server（使用node命令执行server.js文件）

好好好，出错了。

因为Node.js在项目中找不到 mysql 模块，所以需要先安装 mysql 模块：npm install mysql

又出错了，Node.js mysql客户端不支持认证协议引发的“ER_NOT_SUPPORTED_AUTH_MODE”问题。解决办法指路：node.js连接mysql出现错误ER_NOT_SUPPORTED_AUTH_MODE Client does not support authentication protocol...-CSDN博客https://blog.csdn.net/leilei__66/article/details/110674462解决后成功新增数据：

这里重新打开Navicat时，会出现ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)；是由于刚刚我们更改密码了。解决方法指路：

Navicat连接MySQL出现1045错误_1045-ac'c-CSDN博客https://blog.csdn.net/ajinitaimei/article/details/115317014

五、数据库成功连接

上面操作新增成功后，已经确认数据库成功连接Node。

六、前后端分离项目开写

上面仅测试是否成功，我想做的是前后端分离的项目，以下参考：实现简单前后端完全分离增删改查：node.js+mysql+vue-腾讯云开发者社区-腾讯云 (tencent.com)https://cloud.tencent.com/developer/article/2120712

    1.用node.js写增删改查接口

在刚刚的server.js根目录创建一个 index.js 文件：

/* 引入express框架 */
const express = require('express');
var router = express.Router();
const app = express();
/* 引入cors */
const cors = require('cors');
app.use(cors());
/* 引入body-parser */
const bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));
/* 引入mysql */
const mysql = require('mysql');
const conn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'test',
    multipleStatements: true
})
conn.connect();
/* 监听端口 */
app.listen(3000, () => {
    console.log('——————————服务已启动——————————');
})
app.get('/', (req, res) => {
    res.send('<p style="color:red">服务已启动</p>');
})
//查
app.get('/api/getUserList', (req, res) => {
    const sqlStr = 'SELECT * FROM users'
    conn.query(sqlStr, (error, results) => {
        if (error) return res.json({
            code: 404,
            message: error
        })
        res.json({
            code: 200,
            message: results,
            affextedRows: results.affextedRows
        })
    })
})
//条件查找
app.get('/api/getUserListById/:id', (req, res) => {
    const id = req.params.id;
    const sqlStr = 'select * from users where id=?';
    conn.query(sqlStr, id, (err, results) => {
        if (err) return res.json({
            code: 404,
            message: '数据不存在',
            affextedRows: 0
        });
        res.json({
            code: 200,
            message: results,
            affextedRows: results.affextedRows
        });
    })
});
//添加
app.post('/api/addUser', (req, res) => {
    console.log(req.body);
    const username = req.body.username;
    const sex = req.body.sex;
    const address = req.body.address;
    console.log("username:" + username);
    // const sqlStr = "insert into users(username,sex,address) values('" + username + "','" + sex + "','" + address + "')";
    const sqlStr = "insert into users set ?";
    console.log(sqlStr);
    conn.query(sqlStr, req.body, (err, results) => {
        if (err) return res.json({
            code: 404,
            message: err,
            affectedRows: 0
        });
        res.json({
            code: 200,
            message: '添加成功',
            affectedRows: results.affectedRows
        });
    })
});
//修改
app.post('/api/updateUser', function (req, res) {
    console.log(req.body);
    const id = req.body.id;
    const username = req.body.username;
    const sex = req.body.sex;
    const address = req.body.address;
    const sqlStr = "update users set username='" + username + "',sex='" + sex + "',address='" + address + "'where id=" + id;
    console.log(sqlStr);
    conn.query(sqlStr, (err, results) => {
        if (err) return res.json({
            code: 404,
            message: err,
            affectedRows: 0
        });
        res.json({
            code: 200,
            message: '修改成功',
            affectedRows: results.affectedRows
        });
    })
});
//删除
app.delete('/api/deleteUser/:id', function (req, res) {
    console.log('params:' + req.params);
    let id = req.params.id;
    let sql = 'delete from users where id=' + id;
    conn.query(sql, id, (err, results) => {
        if (err) return res.json({
            code: 404,
            message: err,
            affectedRows: 0
        });
        res.json({
            code: 200,
            message: '删除成功',
            affectedRows: results.affectedRows
        })
    })
});

    2.启动服务器

在终端依次输入：npm install cors  、   node index  ，在vscode中启动该服务器。此时显示：

——————————服务已启动——————————

证明服务器启动成功。

    3.vue2项目的axios封装

接下来练习一下axios封装使用。

       （1）项目src目录下新建utils文件夹，新建request.js文件

import axios from "axios";

let request = axios.create({
  baseURL: "http://localhost:3000/api"
});

export default request;


​

       （2）项目src目录下新建api文件夹，新建index.js和user.js文件。其中user.js文件是我的用户组接口，如果项目中有其他分类组的接口比如工程组等等，就再建一个.js文件在api下，再通过index.js暴露出去。

index.js:

import user from "@/api/user";

export default { user };

user.js:

import request from "@/utils/request";

export function getList(params = {}) {
  return request({
    method: "GET",
    url: "/getUserList",
    params
  });
}

export function getListById(id) {
  return request({
    method: "GET",
    url: "/getUserListById/" + id
  });
}

export function addUser(data) {
  return request({
    method: "POST",
    url: "/addUser",
    data
  });
}

export function updateUser(data) {
  return request({
    method: "POST",
    url: "/updateUser",
    data
  });
}

export function deleteUser(id) {
  return request({
    method: "DELETE",  //DELETE符合 RESTful 设计原则
    url: "/deleteUser/" + id
  });
}

     （3）在main.js中注册api以及element ui

import Vue from "vue";
import App from "./App.vue";
import router from "./router";

import api from "./api";

import ElementUI from "element-ui";
import "element-ui/lib/theme-chalk/index.css";
Vue.use(ElementUI);

Vue.prototype.$api = api;

Vue.config.productionTip = false;

new Vue({
  router,
  render: (h) => h(App)
}).$mount("#app");

    4.接口调用

App.vue:

<template>
  <div id="app">
    <el-header>
      <div>
        <span>增删改查</span>
      </div>
    </el-header>
    <el-container>
      <el-main>
        <el-button @click="showAddDialog" class="add">添加用户</el-button>
        <el-table :data="userList" border stripe>
          <el-table-column type="index"></el-table-column>
          <el-table-column label="姓名" prop="username"></el-table-column>
          <el-table-column label="性别" prop="sex"></el-table-column>
          <el-table-column label="地址" prop="address"></el-table-column>
          <el-table-column label="操作">
            <template slot-scope="scope">
              <!-- 修改按钮 -->
              <el-tooltip class="item" effect="dark" content="修改用户" placement="top" :enterable="false">
                <el-button type="primary" icon="el-icon-edit" size="mini"
                  @click="showEditDialog(scope.row.id)"></el-button>
              </el-tooltip>
              <!-- 删除按钮 -->
              <el-tooltip class="item" effect="dark" content="删除用户" placement="top" :enterable="false">
                <el-button type="danger" icon="el-icon-delete" size="mini"
                  @click="removeUserById(scope.row.id)"></el-button>
              </el-tooltip>
            </template>
          </el-table-column>
        </el-table>
      </el-main>
    </el-container>
    <!-- 添加用户的对话框 -->
    <el-dialog title="添加用户" :visible.sync="addDialogVisible" width="50%" @close="addDialogClosed">
      <!-- 内容主题区域 -->
      <el-form :model="addForm" :rules="addFormRules" ref="addFormRef" label-width="70px">
        <el-form-item label="用户名" prop="username">
          <el-input v-model="addForm.username"></el-input>
        </el-form-item>
        <el-form-item label="性别" prop="sex">
          <el-input v-model="addForm.sex"></el-input>
        </el-form-item>
        <el-form-item label="地址" prop="address">
          <el-input v-model="addForm.address"></el-input>
        </el-form-item>
      </el-form>
      <!-- 底部区域 -->
      <span slot="footer" class="dialog-footer">
        <el-button @click="addDialogVisible = false">取 消</el-button>
        <el-button type="primary" @click="adduser">确 定</el-button>
      </span>
    </el-dialog>
    <!-- 修改用户对话框 -->
    <el-dialog title="修改用户" :visible.sync="editDialogVisible" width="50%" @close="editDialogClosed">
      <!-- 内容主题区域 -->
      <el-form :model="editForm" :rules="editFormRules" ref="editFormRef" label-width="70px">
        <el-form-item label="用户名">
          <el-input v-model="editForm.username" disabled></el-input>
        </el-form-item>
        <el-form-item label="性别" prop="sex">
          <el-input v-model="editForm.sex"></el-input>
        </el-form-item>
        <el-form-item label="地址" prop="address">
          <el-input v-model="editForm.address"></el-input>
        </el-form-item>
      </el-form>
      <span slot="footer" class="dialog-footer">
        <el-button @click="editDialogVisible = false">取 消</el-button>
        <el-button type="primary" @click="editUser">确 定</el-button>
      </span>
    </el-dialog>
  </div>
</template>

<script>

import { getList, getListById, addUser, updateUser, deleteUser } from "./api/user";
export default {
  name: "app",
  data() {
    return {
      //全部数据列表
      userList: [],
      addDialogVisible: false,
      addForm: {
        username: "",
        sex: "",
        address: ""
      },
      addFormRules: {
        username: [
          {
            required: true,
            message: "请输入姓名",
            tirgger: "blur"
          }
        ],
        sex: [
          {
            required: true,
            message: "请输入性别",
            tirgger: "blur"
          }
        ],
        address: [
          {
            required: true,
            message: "请输入地址",
            tirgger: "blur"
          }
        ]
      },
      editDialogVisible: false,
      editForm: {
        username: "",
        sex: "",
        address: ""
      },
      editFormRules: {
        username: [
          {
            required: true,
            message: "请输入姓名",
            tirgger: "blur"
          }
        ],
        sex: [
          {
            required: true,
            message: "请输入性别",
            tirgger: "blur"
          }
        ],
        address: [
          {
            required: true,
            message: "请输入地址",
            tirgger: "blur"
          }
        ]
      }
    }
  },
  created() {
    this.getUserList();
  },
  methods: {
    getUserList() {
      getList().then((response) => {
        console.log(response.data.message);
        this.userList = response.data.message;
      })
    },
    showAddDialog() {
      this.addDialogVisible = true;
    },
    showEditDialog(id) {
      getListById(id).then((response) => {
        if (response.data.code != 200) {
          return this.$message.error("查询用户信息失败！");
        }
        this.editForm = response.data.message[0];
        this.editDialogVisible = true;
      })
    },

    adduser() {
      this.$refs.addFormRef.validate((valid) => {
        if (!valid) return;
        //可以发起添加用户的网络请求
        console.log("adddd的内容：", this.addForm.username)
        let params = {
          username: decodeURIComponent(this.addForm.username),
          sex: decodeURIComponent(this.addForm.sex),
          address: decodeURIComponent(this.addForm.address)
        }
        addUser(this.addForm).then((response) => {
          console.log("res :", response);
          if (response.data.code != 200) {
            return this.$message.error("添加用户失败！");
          }
          this.$message.success("添加用户成功！");
          // 隐藏添加用户的对话框
          this.addDialogVisible = false;
          //重新获取用户列表数据
          this.getUserList();
        })
      });
    },
    editUser() {
      this.$refs.editFormRef.validate((valid) => {
        if (!valid) return;
        //可以发起修改用户的网络请求
        updateUser(this.editForm).then((response) => {
          console.log("response :", response);
          if (response.data.code != 200) {
            return this.$message.error("修改用户失败！");
          }
          this.$message.success("修改用户成功！");
          // 隐藏添加用户的对话框
          this.editDialogVisible = false;
          //重新获取用户列表数据
          this.getUserList();
        });
      })

    },
    addDialogClosed() {
      //对话框关闭后自动重置
      this.$refs.addFormRef.resetFields();
    },
    editDialogClosed() {
      //对话框关闭后自动重置
      this.$refs.editFormRef.resetFields();
    },
    removeUserById(id) {
      const confirmResult = this.$confirm(
        "此操作将永久删除该用户, 是否继续?",
        "提示",
        {
          confirmButtonText: "确定",
          cancelButtonText: "取消",
          type: "warning"
        }
      ).then(() => {
        // console.log("id::", id);
        deleteUser(id).then((response) => {
          if (response.data.code !== 200) {
            this.$message.error("删除用户失败！");
          } else {
            this.$message.success("删除用户成功！");
            this.getUserList();
          }
        });
      }).catch(() => {
        this.$message.info("已经取消了删除");
      });
    }
  }
}

</script>

<style>
.el-header {
  background-color: #283a50;
  display: flex;
  justify-content: space-between;
  padding-left: 0;
  align-items: center;
  color: #fff;
  font-size: 20px;
}

.el-header div {
  display: flex;
  align-items: center;
}

.el-header span {
  margin-left: 15px;
}

.el-main {
  background-color: #eaedfa;
  min-height: 650px;
}

.add {
  /* display: flex; */
  width: 100px;
}
</style>

    5.运行截图

七、总结

  1.调用接口时，通过访问 response.data 来获取实际的数据；

  2.后端用的app.delete来写删除接口的，一开始我没注意到，用的get就404了，找了好一阵才发现。根据 RESTful 设计原则，删除应该使用 DELETE 方法而不是 GET 方法，前后端对应上。