node.js连接数据库和实现简单操作功能

1，需要先引入mysql模块
可以先安装npm i -S mysql
2,在文件中引入

const  mysql=require('mysql');

3,创建数据库连接池

//创建一个数据库连接池
let pool = mysql.createPool({
    user:'root',       //数据库user
    connectionLimit:10 //初始化多少个
})

4，从数据库连接池中取出一个连接，并实现查询

pool.getConnection((err,connection)=>{
    if (err) throw err;
    let uname='yaya';
    let upwd='123456';
    let sql="SELECT * FROM xz.xz_user WHERE uname=? AND upwd= ?";
    connection.query(sql,[uname,upwd],(err,result,fields)=>{
        if(result.length>0){
            console.log("登录成功");
        }else{
            console.log("登录失败");
        }
    })
    connection.release();//查询完最后释放
})

也可以使用简化写法

let uname='tom';
let upwd='123';
let sql="SELECT * FROM xz.xz_user WHERE uname=?";
pool.query(sql,[uname],(err,result,fields)=>{
    if(result.length>0){
        let encryptedPwd = result[0].upwd;
        if(bcrypt.compareSync(upwd,encryptedPwd)){
            console.log("登录成功");
        }else{
            console.log("用户名或密码出错了");
        }
    }else{
        console.log("登录失败");
    }
})

5，添加操作
如果要用到密码加密功能的话，需要安装
npm install -S bcryptjs

const mysql = require('mysql');
const bcrypt = require('bcryptjs');

let uname = 'jeek';
    let upwd = '123';
    let salt = bcrypt.genSaltSync(10);
    let encryptedPwd = bcrypt.hashSync(upwd, salt);
    let sql = "INSERT INTO xz.xz_user (uname,upwd) VALUE(?,?)";
pool.query(sql,[uname, encryptedPwd],(err, result, fields)=>{
    if (result.length > 0) {
        console.log(result);
    } else {
        console.log(result);
    }
})

6,修改操作

let uname='Mariks'
let uid=8;
let sql='UPDATE xz.xz_user SET uname=? WHERE uid=?'
pool.query(sql,[uname,uid],(err,result,fields)=>{
    if(result.affectedRows==1){
        console.log("修改成功")
    }else{
        console.log("修改失败，请联系管理员");
    }
})

7,删除

let uid=8;
let sql="DELETE FROM xz.xz_user WHERE uid=?";
pool.query(sql,[uid],(err,result,fields)=>{
    if(result.affectedRows==1){
        console.log("删除成功");
    }else{
        console.log("删除失败，请联系管理员");
    }
})

8，查询

//分页查询
let sql ="SELECT * FROM EMP LIMIT 10 OFFSET 0"
//模糊查询
let sql ="SELECT * FROM EMP WHERE uname LIKE '%$uname%"

http服务

const http = require('http');

let server=http.createServer((req,res)=>{
        console.log(req.method);
        console.log(req.url);
        console.log(req.httpVersion);
        console.log(req.headers);

        //设置响应头消息
        res.writeHead(200,{
            'Content-Type':'text/html;charset=UTF-8',
            'Cache-Control':'no-cache'
            });
        res.write('<h1>Hello world</h1>');
        res.end();//结束消息响应
    });
    server.listen(3000);