新建一个express项目；
express myNodeDome
进入文件夹
cd myNodeDome
下载依赖
npm i
首先下载mysql包
npm i mysql
新建Db.js
//引入mysql包
const mysql = require('mysql')
//链接配置
var connection = mysql.createConnection({
host: 'localhost', //ip
user: 'root', //用户名
password: '123456',//密码
database: 'test' //数据库名
});
connection.connect();//建立链接
//进行查询
connection.query('SELECT * from user where id =2', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
});
查寻语句
SELECT * from user where id =2
在app.js中引用测试，因为项目运行时会自动走一遍引入的js，所以直接会运行；
var Db = require('./public/javascripts/Db')
数据库信息
查询结果：
//改
connection.query('update user set username="666", password="66666" where id=1', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
});
//查
connection.query('SELECT * from user where id =2', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
});
//增
connection.query('insert into user values (null,8868,8886)', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
});
//删
connection.query('delete from user where id=1', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
});
封装sql语句
第一个参数是sql，第二个参数是一个回调函数
/**
*
* @param {String} str sql语句
*/
const DBFun = (str,fun) => {
connection.query(str, function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results);
fun(results)
});
}
//promise形式封装
const DBFunPro = (str) => {
console.log("=========开始查找sql============");
try {
return new Promise((resolve, reject) => {
connection.query(str, function (error, results, fields) {
if (error) {
reject([{
data: "数据库查询报错" }])
};
resolve(results)
});
})
} catch{
console.log("请注意sql报错")
}
}
module.exports={
DBFun,DBFunPro
}
使用：
var express = require('express');
const {
v4: uuidv4 } = require('uuid');
const md5 = require('md5');
var router = express.Router();
var Db = require('../public/javascripts/Db')
var endMassage = require('../public/javascripts/endMassage')
//queryId 通过id查询 user 用户信息
router.get('/queryId', function (req, res, next) {
let {
id } = req.query
Db.DBFun(`select * from user where id=${
id}`, (data) => {
res.send(endMassage(data))
})
});
//登录注册接口 有账号 直接登录 没账号 注册
router.get('/login', function (req, res, next) {
let {
username, password } = req.query
Db.DBFun(`select username,password,uuid from user where username='${
username}'`, (data) => {
if (data.length) {
if (data[0].password == md5(password)) res.send(endMassage({
data: "登录成功",code:1 }))
else res.send(endMassage({
data: "密码错误", code: 0, uuidStr: data[0].uuid}))
} else {
let uuidStr = uuidv4()
let passwordStr = md5(password)
Db.DBFun(`insert into user(password,username,uuid) values ('${
passwordStr}','${
username}','${
uuidStr}') `, (insertInfo) => {
res.send(endMassage({
data: "注册成功", uuidStr }))
})
}
})
});
router.post('/postInputItem', (req, res) => {
let {
name, sex, tel, uuid } = req.body
Db.DBFun(`select uuid from user_info where uuid='${
uuid}'`, (data) => {
if (data.length > 0) {
Db.DBFun(`update user_info set name='${
name}',sex='${
sex}',tel='${
tel}' where uuid='${
uuid}'`, (data) => {
res.send(endMassage({
data: "信息更新成功" }))
})
} else {
Db.DBFun(`insert into user_info(uuid,name,sex,tel) values ('${
uuid}','${
name}','${
sex}','${
tel}')`, (data) => {
res.send(endMassage({
data: "信息插入成功" }))
})
}
})
})
module.exports = router;
