一. 使用navicat建数据库

1. 下载mysql

• 安装指南 zhuanlan.zhihu.com/p/37152572 ，这篇文章很详细
• 安装时，MySQL会提示输入root用户的口令，请务必记清楚。如果怕记不住，就把口令设置为password。
• 在Windows上，安装时请选择UTF-8编码，以便正确地处理中文。
• 配置username和password时最好记录下来，避免忘了。

2. 下载navicat

• 安装 Navicat Premium 15

3. navicat连接mysql数据库

• 用管理员身份运行cmd，输入net start 服务器名称，启动数据库

• 启动成功

• 打开navicat点击连接mysql
• 输入net stop 服务器名称，停止mysql
  

二. 使用node + mysql做接口

1. 启动node服务器

• 随便找个地方运行命令mkdir node-service && cd node-service，新建index.js文件，简单用express就开启一个服务器了

// index.js
const express = require("express");
const app = express();
const port = 3002;
app.listen(port, () => {
  console.log(`Example app listening at http://localhost:${port}`);
});

2. node连接本地数据库

• 引用mysql连接，这样就能连接上本地的数据库了，前提是数据库服务器有开启

const mysql = require("mysql");
const defconfig = {
  host: "localhost",
  user: "root",
  password: "password",
  database: "test", // 数据库名
  port: "3306",
};
const connection = mysql.createConnection(defconfig);
connection.connect((err) => {
  if (err) {
    console.log("数据库连接失败");
    throw err;
  }
});

• 操作数据库，简单查询一下数据库

const querysql = "SELECT * FROM user";
connection.query(querysql, [], (res, fields) => {
  console.log("查询结果：");
  console.log(res);
  console.log("fields: ", fields);
});

• 再简单封装一下，就可以在调用接口的时候直接使用了。

// db.js
const query = (sql, params, callback) => {
  const connection = mysql.createConnection(defconfig);
  connection.connect((err) => {
    if (err) {
      console.log("数据库连接失败");
      throw err;
    }
  });
  const fn = (err, res, fields) => {
    if (err) {
      console.log("数据操作失败: ", err.message);
      throw err;
    }
    //将查询出来的数据返回给回调函数
    callback && callback(res, fields);
    connection.end((err) => {
      if (err) {
        console.log("关闭数据库连接失败！");
        throw err;
      }
    });
  };
  connection.query(sql, params, fn);
};

3. 创建增删改查接口

注意：要拿到传过来的数据需要调用app.use(express.json())和app.use(express.urlencoded())，代替body-parse解析传入数据

const express = require("express");
const app = express();
app.use(express.json());
app.use(express.urlencoded());

• 数据库

• 创建查询接口，然后在前端调用接口

app.get("/data", (req, resp) => {
  const querysql = "SELECT * FROM todo_list";
  db.query(querysql, [], (res) => {
    resp.json(res);
  });
});

const http = new XMLHttpRequest();
http.onreadystatechange =  () => {
  if (http.status == 200 && http.readyState == 4) {
    // 调用成功后
  }
};
//发送请求
http.open("GET", "http://localhost:3000/data");
http.send();

• 创建插入接口

app.post("/postdata", (req, resp) => {
  console.log("插入数据", req.body);
  const target = req.body.target;
  const querysql = "insert into todo_list(target, status) values(?,?)";
  db.query(querysql, [target, "false"], (res) => {
    resp.json(res);
  });
});

const http = new XMLHttpRequest();
var { value, name } = document.getElementById("name");
const params = { [name]: value };
http.open("POST", "http://localhost:3000/postdata");
http.setRequestHeader("Content-type", "application/json;charset=UTF-8");
http.send(JSON.stringify(params));
document.location.reload();

• 创建更新接口

app.put("/putData", (req, resp) => {
  console.log("修改数据：", req.body);
  const target = req.body.target;
  const status = req.body.status;
  let data;
  if (status === "false") data = true;
  if (status === "true") data = false;
  const sql = `update todo_list set status = "${data}" where target = "${target}"`;
  db.query(sql, [], (res) => {
    resp.json(res);
  });
});

const http = new XMLHttpRequest();
http.open("PUT", `http://localhost:3000/putData`);
// 传body要设置请求头
http.setRequestHeader(
"Content-type",
"application/json;charset=UTF-8"
);
http.send(JSON.stringify(data));
document.location.reload();

• 删除接口

app.delete("/deleteData", (req, resp) => {
  console.log("删除数据：", req.query);
  const target = req.query.target;
  const querysql = `DELETE FROM todo_list WHERE target = "${target}" `;
  db.query(querysql, [], () => {
    resp.json(req.query);
  });
});

const http = new XMLHttpRequest();
http.open("DELETE", `http://localhost:3000/deleteData?target=${data.target}`);
http.send();
document.location.reload();

4. 跨域问题

• 第一种方法，安装cors

//yarn add cors
const cors = require("cors");
app.use(cors());

• 第二种方法，手动实现

app.use((req, res, next) => {
res.header('Access-Control-Allow-Origin', '*')
res.header('Access-Control-Allow-Headers', 'Authorization,X-API-KEY, Origin, X-Requested-With, Content-Type, Accept, Access-Control-Request-Method' )
res.header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PATCH, PUT, DELETE')
res.header('Allow', 'GET, POST, PATCH, OPTIONS, PUT, DELETE')
next();
});