Use NavICat to build a database

1. Download the mysql

  • The installation guide zhuanlan.zhihu.com/p/37152572, is detailed in this article
  • During installation, MySQL will prompt you for inputrootPlease remember the user’s password clearly. If you don’t remember, set your password topassword.
  • inWindowsOn, please select during installationUTF-8Encoding to properly process Chinese.
  • configurationusernameandpasswordIt’s best to write it down before you forget it.

2. Download navicat

  • Install Navicat Premium 15

3. Navicat connects to the mysql database

  • Run as an administratorcmd, the inputNet start Server name, start the database

  • Start-up success

  • Open thenavicatClick on the linkmysql

  • The inputNet stop Server nameAnd stopmysql

2. Use node + mysql as interface

1. Start the Node server

  • Run the command from anywheremkdir node-service && cd node-service, the newly builtindex.jsFile, easy to useexpressJust start a server
// index.js
const express = require("express");
const app = express();
const port = 3002;
app.listen(port, () = > {
  console.log(`Example app listening at http://localhost:${port}`);
});
Copy the code

2. Node connects to the local database

  • referencemysqlConnect, so you can connect to the local database, provided that the database server is enabled
const mysql = require("mysql");
const defconfig = {
  host: "localhost".user: "root".password: "password".database: "test".// Database name
  port: "3306"};const connection = mysql.createConnection(defconfig);
connection.connect((err) = > {
  if (err) {
    console.log("Database connection failed");
    throwerr; }});Copy the code
  • Operation database, simple query database
const querysql = "SELECT * FROM user";
connection.query(querysql, [], (res, fields) = > {
  console.log("Query result:");
  console.log(res);
  console.log("fields: ", fields);
});
Copy the code
  • With a little wrapping, you can use it directly when you call the interface.
// db.js
const query = (sql, params, callback) = > {
  const connection = mysql.createConnection(defconfig);
  connection.connect((err) = > {
    if (err) {
      console.log("Database connection failed");
      throwerr; }});const fn = (err, res, fields) = > {
    if (err) {
      console.log("Data operation failed:", err.message);
      throw err;
    }
    // Return the queried data to the callback function
    callback && callback(res, fields);
    connection.end((err) = > {
      if (err) {
        console.log("Failed to close database connection!");
        throwerr; }}); }; connection.query(sql, params, fn); };Copy the code

3. Create a add, delete, modify, and query interface

Note: To get the incoming data, call app.use(express.json()) and app.use(express.urlencoded()) instead of body-parse incoming data

const express = require("express");
const app = express();
app.use(express.json());
app.use(express.urlencoded());
Copy the code
  • The database

  • Create the query interface and invoke the interface on the front end
app.get("/data".(req, resp) = > {
  const querysql = "SELECT * FROM todo_list";
  db.query(querysql, [], (res) = > {
    resp.json(res);
  });
});
Copy the code
const http = new XMLHttpRequest();
http.onreadystatechange =  () = > {
  if (http.status == 200 && http.readyState == 4) {
    // After the call succeeds}};// Send the request
http.open("GET"."http://localhost:3000/data");
http.send();
Copy the code
  • Creating an insert interface
app.post("/postdata".(req, resp) = > {
  console.log("Insert data", 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);
  });
});
Copy the code
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();
Copy the code
  • Creating an update interface
app.put("/putData".(req, resp) = > {
  console.log("Modify data:", 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);
  });
});
Copy the code
const http = new XMLHttpRequest();
http.open("PUT".`http://localhost:3000/putData`);
// Pass the body to set the request header
http.setRequestHeader(
"Content-type"."application/json; charset=UTF-8"
);
http.send(JSON.stringify(data));
document.location.reload();
Copy the code
  • Delete the interface
app.delete("/deleteData".(req, resp) = > {
  console.log("Delete data:", req.query);
  const target = req.query.target;
  const querysql = `DELETE FROM todo_list WHERE target = "${target}"`;
  db.query(querysql, [], () = > {
    resp.json(req.query);
  });
});
Copy the code
const http = new XMLHttpRequest();
http.open("DELETE".`http://localhost:3000/deleteData? target=${data.target}`);
http.send();
document.location.reload();
Copy the code

4. Cross-domain problems

  • The first method is to install CORS
//yarn add cors
const cors = require("cors");
app.use(cors());
Copy the code
  • The second method is manual implementation
app.use((req, res, next) = > {
res.header('Access-Control-Allow-Origin'.The '*')
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();
});
Copy the code