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 input
root
Please remember the user’s password clearly. If you don’t remember, set your password topassword
. - in
Windows
On, please select during installationUTF-8
Encoding to properly process Chinese. - configuration
username
andpassword
It’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 administrator
cmd
, the inputNet start Server name
, start the database
- Start-up success
- Open the
navicat
Click on the linkmysql
- The input
Net stop Server name
And stopmysql
2. Use node + mysql as interface
1. Start the Node server
- Run the command from anywhere
mkdir node-service && cd node-service
, the newly builtindex.js
File, easy to useexpress
Just 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
- reference
mysql
Connect, 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