Node.js连接mysql简单实现增删改查

1 什么是MySQL?

mysql是一个数据管理系统.数据库是存储\管理数据的仓库.

2 环境安装配置

2.1 windows安装及配置

windows安装配置教程 blog.csdn.net/qq_37350706…

具体

进入官网下载地址 dev.mysql.com/downloads/m…
然后我们把文件解压到自己指定的目录下(我们这里是D:\MySQL)
我们在D:\MySQL目录下,创建一个my.txt文件,写入以下配置,然后将后缀修改为my.ini

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\MySQL
# 设置mysql数据库的数据的存放目录
datadir=D:\MySQL\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

然后我们右击以管理员身份打开cmd或者powershell

# 跳转到该路径
cd D:\MySQL\bin

# 输入以下指令进行初始化
.\mysqld --initialize --console
# 日志中含有  root@localhost: pVOwiuWXj1:R 这样的标记表示初始化成功

# 输入以下指令安装mysql服务
.\mysqld --install
# 提示中含有 Service successfully installed. 这样的标记表示服务安装成功

# 输入指令启动服务
net start mysql
# 提示中含有 服务正在启动! 服务启动成功! 这样的标记表示服务启动成功

# 如果启动失败
# 服务没有响应控制功能。
# 请键入 NET HELPMSG 2186 以获得更多的帮助。
# 我们需要改变启动方式
.\mysql --console

# 试水完成之后我们停止服务
net stop mysql

2.2 mac安装及配置

blog.csdn.net/luzhensmart…

3 数据库管理工具

MySQL Workbench 下载地址：dev.mysql.com/downloads/w…
navicat
sequal pro: 下载地址: sequelpro.com/test-builds

3.1 navicat 安装

下载14天试用版www.navicat.com.cn/download/na…
安装步骤用脸怼过去就行.
安装成功之后点击,打开软件,跳过所有下一步下一步,然后点击:文件->新建连接-> mysql

1.输入任意<连接名>
2.输入数据库生成<密码>
3.然后<测试连接>(测试连接前记得在powershell中启动net start mysql)
4.确定

4 修改密码

# 在修改密码前我们记得先开启mysql服务
net start mysql
# 然后跳转至我们的bin脚本
cd D:\MySQL\bin
# 输入命名行修改密码
.\mysql -u root -p
# 在Enter password: 后面输入你的原始密码
*************
# 出现mysql> 的时候就说明密码校验成功

# 输入指令重新设置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的密码';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
# 提示Query OK,0 rows affected (0.01 sec) 说明退出成功

# 输入指令退出
exit;
# 提示 Bye 说明退出成功

# 验证
.\mysql -u root -p
************
# 如果输入新密码能够正确进入设置,那就说明刚才设置成功了
# 最后记得退出
exit;

5 数据库设计

5.1 创建数据库

Sequel pro

在这里插入图片描述

Navicat

详细说明navicat
# 搜索技巧使用过滤器,限制时间,限制网站
1. 点击连接mysql
2. 点击新建数据库-> 字符集:utf8->排序规则:utf8_general_ci(不区分大小写)

5.2 创建表

Sequel pro
navicat

在这里插入图片描述

详细说明navicat
1. 首先`打开表`
2. 添加id->右击名,将其设置为主键->类型:int(整数)->长度设置为128-> 不是null -> 注释:用户id
3. 设置id的其他属性:递增
4. 按方向键`下`换行,配置下一个字段
5. 添加name->类型设置为varchar(字符串)-> 长度128-> 可以是null-> 注释:用户名称
6. 设置name的其他属性,字符集:utf8排序规则: utf8_general_ci
7. 按方向键`下`换行,配置下一个字段
8. 添加city->类型设置为varchar(字符串)-> 长度128-> 可以是null-> 注释:城市
9. 设置city的其他属性,字符集:utf8排序规则: utf8_general_ci
10. 按方向键`下`换行,配置下一个字段
11. 添加sex->类型设置为tinyint(短数值)-> 长度2-> 可以是null-> 注释:1是女2是男
12. ctrl + s 保存 输入表名 user
13. 然后我们点击user_test这个数据库目录下的user表进行编辑
14. 我们数据后三个字段之后按方向键`下`,id会自动递增

mac 下的比较自动
1. 第一条id已经设计好了 type keyPRI
2. 点击最下面加号下一行
3. 其他:field字段 type数据类型 length长度 key键类型 comment注释 default默认值

6 mysql常用数据库操作

6.1 增加表格数据

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES (value1,
value2,...valueN)
//往user表插⼊⼀条数据
insert into user (name,city,sex) values ('⼩⼩','北京',1)

6.2 删除表格数据

DELETE FROM table_name [WHERE Clause]
//删除⽤户id为5的⽤户
delete from user where id = 5

6.3 修改表格数据

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
//修改⽤户id为5的信息
update user set name='⼩天',city='深圳' where id = 5

6.4 查询表格数据

SELECT column_name,column_name FROM table_name [WHERE Clause]
//查询所有⽤户信息，*表示显示所有字段信息
select * from user
//查询所有⽤户信息，只显示name和city信息
select name,city from user
//查询id为4的⽤户
select name,city from user where id = 4
//同时满⾜两个条件⽤and
select name,city from user where city = '北京' and sex = 1

6.5 排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
默认asc升序排序，desc降序排序
//根据id进⾏降序排序
select * from user order by id desc

6.6 模糊查询

SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE
condition1
//查询名字带有红的⽤户
select * from user where name like '%红%'

7 nodejs链接mysql

7.1 简单demo

/** npm install mysql --save */
/** 如果出现如下错误: Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES) 
先替换下域名 127.0.0.1
*/
const mysql = require('mysql')
//创建连接
const conn = mysql.createConnection({
 host:'localhost',
 user:'root',
 password:'123456789',
 port:'3306',
 database:'user_test'
})
//建⽴连接
conn.connect()
let sql = 'select * from user where id = ?'
//执⾏sql语句
conn.query(sql,[4],(err,result)=>{
 if(err) throw err
 console.log(result)
})
//关闭连接
conn.end()

7.2 mysql连接池

mysql链接池与普通链接池的区别

频繁创建关闭连接会减低系统的性能,会加大系统开销.
连接池可以有效的管理连接达到连接复用的效果. 连接池的使用

const mysql = require('mysql')
//创建连接池
const pool = mysql.createPool({
 connectionLimit: 10,
 host: 'localhost',
 user: 'root',
 password: '123456789',
 port: '3306',
 database: 'user_test'
})
//获取连接
pool.getConnection((err, conn) => {
 if (err) throw err
 let sql = 'select * from user where city = ?'
 //执⾏sql语句
 conn.query(sql, ['⼴州'], (err, result) => {
 conn.release()
 if (err) throw err
 console.log(result)
 })
})

8 使用node连接数据mysql简单实现增删查改

客户端

const http = require("http");

http.get("http://localhost:3000/api/find/?id=1",(res) => {
  res.setEncoding('utf8');
  let allData = "";
  res.on("data",chunk=>{
    allData += chunk;
  })
  res.on("end",()=>{
    console.log("寻找结果",allData)
  })
})
.on("error",err=>{
  console.error("寻找错误",err.toString())
})


// http.get("http://localhost:3000/api/delete/?id=2",res=>{
//   res.setEncoding("utf8");
//   let allData = "";
//   res.on("data",chunk=>{
//     allData += chunk
//   })
//   res.on("end",()=>{
//     console.log(allData);
//   })
// })
// .on("error",err=>{
//   console.error("删除出错",err.toString())
// })


// const addReq = http.request({
//   host: "localhost",
//   port:3000,
//   path:"/api/add/",
//   method: "POST",
//   query:""
// },res=>{
//   res.setEncoding('utf8');
//   let allData = "";
//   res.on("data",chunk=>{
//     allData += chunk;
//   })
//   res.on("end",()=>{
//     console.log(allData)
//   })
// })

// addReq.on("error",err => {
//   console.error("添加失败",err.toString())
// })

// addReq.write(JSON.stringify({
//   name:"猛男二号",
//   city: "安徽",
//   sex: 2
// }))

// addReq.end();

// const updateReq = http.request({
//   host:"localhost",
//   port: 3000,
//   path:"/api/update/?id=1",
//   query: "",
//   method: "POST"
// },res=>{
//   res.setEncoding("utf8");
//   let allData = "";
//   res.on("data",chunk=>{
//     allData += chunk;
//   })

//   res.on("end",()=>{
//     console.log(allData)
//   })
// })

// updateReq.on("error",err=>{
//   console.error("更新失败",err.toString())
// })

// updateReq.write(JSON.stringify({
//   name:"帅哥",
//   city:"深圳",
//   sex: 2
// }))

// updateReq.end()

const http = require("http");
const url = require("url");
const mysql = require('mysql');

/** 
 * 原理:我们只需要创建一次链接池
 * 然后可以获取多次连接
 * 
 * 封装后的获取连接的函数接收一个sql模板和参数
 */

const dbOption = {
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "666666",
  database: "user_test"
}

const pool = mysql.createPool(dbOption);

function query(sql, params) {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, conn) => {
      if (err) {
        reject(err)
        return
      }

      conn.query(sql, params, (err, result) => {
        conn.release()
        if (err) {
          reject(err)
          return
        }
        resolve(result)
      })

    })
  })
}

/** 假设不是使用连接池直接链接 */

// function query(sql, params) {

//   return new Promise((resolve, reject) => {

//     const conn = mysql.createConnection(dbOption);

//     conn.connect();

//     conn.query(sql, params, (err,result)=>{

//       if(err) {

//         reject(err)

//         return
//       }

//       resolve(result)
//     })
//   })

// }




async function findData(id) {
  const sql = "select * from user where id = ?"
  const data = await query(sql, [id])
  return JSON.parse(JSON.stringify(data))
}

async function addData(body) {
  const sql = "insert into user (name,city,sex) values (?,?,?)"
  const { name, city, sex } = body;
  const data = await query(sql, [name, city, sex])
  if (data.affectedRows > 0) {
    return { msg: "添加成功" }
  }
  else {
    return { msg: "添加失败" }
  }
}

async function deleteData(id) {
  const sql = "delete from user where id = ?"
  const data = await query(sql, [id])
  if (data.affectedRows > 0) {
    return { msg: "删除成功" }
  }
  else {
    return { msg: "删除失败" }
  }
}

async function updateData(id, body) {
  const sql = "update user set name = ?,city = ?,sex = ? where id = ?"
  const { name, city, sex } = body;
  const data = await query(sql, [name, city, sex, id])
  if (data.affectedRows > 0) {
    return { msg: "修改成功" }
  }
  else {
    return { msg: "修改失败" }
  }
}

function analysisBody(req) {

  return new Promise((resolve, reject) => {

    if (req.method !== "POST") {
      resolve({})
      return
    }

    let body = ""

    req.on("data", chunk => {
      body += chunk;
    })

    req.on("end", () => {
      resolve(JSON.parse(body))
    })

  })

}

function analysisRouter(req, body) {

  const urlObj = url.parse(req.url, true);
  if (req.method === "GET") {
    switch (urlObj.pathname) {
      case "/api/find/":
        return findData(urlObj.query.id)
      case "/api/delete/":
        return deleteData(urlObj.query.id)
    }
  }
  if (req.method === "POST") {
    switch (urlObj.pathname) {
      case "/api/add/":
        return addData(body)
      case "/api/update/":
        return updateData(urlObj.query.id, body)
    }
  }

}

const server = http.createServer((req, res) => {
  res.writeHead(200, {
    "content-type": "application/json;charset=UTF-8"
  })
  analysisBody(req)
    .then(async (body) => {
      const result = await analysisRouter(req, body);
      if (result) {
        res.end(JSON.stringify(result))
      }
      else {
        res.writeHead(404, {
          "content-type": "text/html"
        })
        res.end("404 not found");
      }
    })

})


server.listen(3000, () => {
  console.log("3000端口已启动")
})