nodejs4：操作mysql_node4j

mysql数据库的基本操作

登录

mysql -uroot -p
1

设置密码

set password for 用户名@localhost = password('新密码')
1

数据库操作

显示所有数据库show databases
创建数据库create database xxx
删除数据库drop database xxx
查看数据库信息show create database xxx
修改数据库编码格式alter database xxx character set=utf8

数值类型

时间日期类型

字符串类型

数据库中的表操作

选择数据库use xxx
查看当前选择的数据库select database()
创建数据表create table xxx

CREATE TABLE users(
	username VARCHAR(20),
	age TINYINT UNSIGNED,
	salary FLOAT(8,2) UNSIGNED
)
1
2
3
4
5

查看所有数据表show tables
查看表结构DESCRIBE(DESC) test_table;
SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句

curd语句

查

SELECT * FROM users WHERE age>? ORDER BY age DESC LIMIT ?,? 
1

增

INSERT INTO users (id,name,age) VALUES (0,?,?)
1

改

UPDATE users SET age=? WHERE id=?
1

删除字段

DELETE FROM users WHERE id=?
1

条件WHERE

where
1

限制LIMIT start,step ,第一个值为start，不包含start，第二个值为长度

select from test LIMIT 1,2
1

排序ORDER BY

ORDER BY 字段名 ASC
ORDER BY id ASC 从小到大排序
DESC 从大到小
1
2
3

limit必须使用在orderby之后

node中操作mysql

用到的三方库mysql2

链接数据库

const mysql = require("mysql2");

// 1. 如何连接到 nodejs -> mysql
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "test",
});
1
2
3
4
5
6
7
8
9

尝试通过返回的connect操作数据库
connection.execute() 方法，第一个参数是sql语句，第二个是传递值的数组

const name = "p7";
const age = 120;
const sql = `INSERT INTO test (id,name,age) VALUES (0,?,?)`;
connection.execute(sql, [name, age], (err, results) => {
  if (err) {
    throw err;
  }

  console.log(results);
});
1
2
3
4
5
6
7
8
9
10

成功添加

curd

建立框架，建立数据库的异步连接

const Koa = require("koa");

const Router = require("koa-router");

const mysql = require("mysql2/promise");

const app = new Koa();

const router = new Router();

(async () => {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "103358",
    database: "test",
  });
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

查

  router.get("/findUser", async (ctx) => {
    const { id } = ctx.query;
    const sql = `SELECT * FROM test WHERE id=?`;
    const [rows] = await connection.execute(sql, [id]);
    ctx.body = rows[0];
  });
1
2
3
4
5
6

查多条数据

  router.get("/findUsers", async (ctx) => {
    const { age = 0, offset = 0, limit = 5 } = ctx.query;
    const sql = `SELECT * FROM test WHERE age>? ORDER BY age DESC  LIMIT ?,?`;
    const [rows] = await connection.execute(sql, [age, offset, limit]);
    ctx.body = rows;
  });
1
2
3
4
5
6

注意对象属性之间的query需要用&来分隔

添加数据

  router.get("/addUser", async (ctx) => {
    // 添加数据到 db
    const { name, age } = ctx.query;
    const sql = `INSERT INTO test (id,name,age) VALUES (0,?,?)`;
    const [rows] = await connection.execute(sql, [name, age]);
    // affectedRows返回的是影响的行数
    if (rows.affectedRows === 1) {
      ctx.body = "add user success";
    } else {
      ctx.body = "add user fail";
    }
  });
1
2
3
4
5
6
7
8
9
10
11
12

改

  router.get("/updateUser", async (ctx) => {
    const { id, age } = ctx.query;
    const sql = `UPDATE test SET age=? WHERE id=?`;
    const [rows] = await connection.execute(sql, [age, id]);
    ctx.body = rows;
  });
1
2
3
4
5
6

删除

  router.get("/delUser", async (ctx) => {
    const { id } = ctx.query;
    const sql = `DELETE FROM test WHERE id=?`;
    const [rows] = await connection.execute(sql, [id]);
    if (rows.affectedRows === 1) {
      ctx.body = "del user success";
    } else {
      ctx.body = "del user fail";
    }
  });
1
2
3
4
5
6
7
8
9
10

完整逻辑

const Koa = require("koa");

const Router = require("koa-router");

const mysql = require("mysql2/promise");

const app = new Koa();

const router = new Router();

(async () => {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "103358",
    database: "test",
  });

  // users
  // 增删改查
  // get
  // /findUser
  // /findUsers

  // /addUser
  router.get("/addUser", async (ctx) => {
    // 添加数据到 db
    const { name, age } = ctx.query;
    console.log("数据------------------",ctx.query)
    const sql = `INSERT INTO test (id,name,age) VALUES (0,?,?)`;
    const [rows] = await connection.execute(sql, [name, age]);
    // affectedRows返回的是影响的行数
    if (rows.affectedRows === 1) {
      ctx.body = "add user success";
    } else {
      ctx.body = "add user fail";
    }
  });

  // /delUser
  router.get("/delUser", async (ctx) => {
    const { id } = ctx.query;
    const sql = `DELETE FROM test WHERE id=?`;
    const [rows] = await connection.execute(sql, [id]);
    if (rows.affectedRows === 1) {
      ctx.body = "del user success";
    } else {
      ctx.body = "del user fail";
    }
  });

  // /updateUser
  router.get("/updateUser", async (ctx) => {
    const { id, age } = ctx.query;
    const sql = `UPDATE test SET age=? WHERE id=?`;
    const [rows] = await connection.execute(sql, [age, id]);
    ctx.body = rows;
  });

  // /findUser
  router.get("/findUser", async (ctx) => {
    const { id } = ctx.query;
    const sql = `SELECT * FROM test WHERE id=?`;
    const [rows] = await connection.execute(sql, [id]);
    ctx.body = rows[0];
  });

  // /findUsers
  router.get("/findUsers", async (ctx) => {
    const { age = 0, offset = 0, limit = 5 } = ctx.query;
    console.log(ctx.query)
    const sql = `SELECT * FROM test WHERE age>? ORDER BY age DESC  LIMIT ?,?`;
    const [rows] = await connection.execute(sql, [age, offset, limit]);
    ctx.body = rows;
  });
})();

app.use(router.routes());

app.listen(8080, () => {
  console.log("open server localhost:8080");
});

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

mysql改写新闻列表

数据存储

先将数据存入数据库，利用Navicat

如果报错无法添加中文，那可能是Navicat没有将character设置为utf8

修改后再导入一次即可

主逻辑

const Koa = require("koa");
const Router = require("koa-router");
const serve = require("koa-static");
const views = require("koa-views");
const news = require('./router/news');
const detail = require('./router/detail');
const {initDB} = require('./db');

initDB()

const app = new Koa();

app.use(serve(__dirname + "/static"));
app.use(
  views(__dirname + "/views", {
    extension: "pug",
  })
);

const router = new Router();

router.redirect("/news", "/");
router.get("/",news)
router.get("/detail",detail)

app.use(router.routes());

app.listen(9090);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

新闻页面

获取query，传递参数、渲染模板

const { getDB } = require("../db");
const pageSize = 5;
let currentPageIndex;
module.exports = async (ctx) => {
  // 获取当前页新闻数据
  currentPageIndex = +ctx.query.p || 1;
  const currentNewsData = await getCurrentNewsData();
  const pageCount = await getPageCount();

  await ctx.render("index", {
    p: currentPageIndex,
    pageCount,
    newsData: currentNewsData,
  });
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

异步分页逻辑

async function getCurrentNewsData() {

  const limit = pageSize;
  const offset = (currentPageIndex - 1) * pageSize;
  const sql = `SELECT * from news LIMIT ?,?`;
  const [rows] = await getDB().execute(sql, [offset, limit]);
  return rows;
}

1
2
3
4
5
6
7
8
9

异步处理总分页数

async function getPageCount() {
  const sql = `SELECT * FROM news`;
  const [rows] = await getDB().execute(sql);

  const pageCount = Math.ceil(rows.length / pageSize);
  return pageCount;
}

1
2
3
4
5
6
7
8

详情页

获取数据库数据，获取query数据，传递参数给模板

const { getDB } = require("../db");
module.exports = async (ctx) => {
  // 获取数据
  const id = +ctx.query.id;
  const currentPageData = await getCurrentPageDataById(+id);
  await ctx.render("detail", {
    data: currentPageData,
  });
};

1
2
3
4
5
6
7
8
9
10

sql语句获取数据

async function getCurrentPageDataById(id) {

  const sql = `SELECT * FROM news WHERE id=?`;
  const [rows] = await getDB().execute(sql,[id]);
  return rows[0];
}
1
2
3
4
5
6

ORM(Object/Relational Mapping)

概述

orm是关系用对象表达，类似于数据库操作和node之间的中间层
通过实例对象的语法，完成关系型数据库的操作的技术

缺点：

1. ORM 库不是轻量级工具，需要花很多精力学习和设置。
2. 对于复杂的查询，ORM 要么是无法表达，要么是性能不如原生的 SQL。
3. ORM 抽象掉了数据库层，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL。

优点

1. 数据模型都在一个地方定义，更容易更新和维护，也利于重用代码。
2. ORM 有现成的工具，很多功能都可以自动完成，比如数据消毒、预处理、事务等等。
3. 它迫使你使用 MVC 架构，ORM 就是天然的 Model，最终使代码更清晰。
4. 基于 ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
5. 你不必编写性能不佳的 SQL。

命名

• 一个类对应一张表。类名是单数，且首字母大写；表名是复数，且全部是小写。
  比如，表books对应类Book。
• 如果名字包含多个单词，那么类名使用首字母全部大写的骆驼拼写法，而表名使用下划线分隔的小写单词。
  比如，表book_clubs对应类BookClub，表line_items对应类LineItem。

sequelize

官方文档
安装

npm install sequelize mysql2
1

连接数据库

const Koa = require("koa");
const { Sequelize, Op } = require("sequelize");
const getUserModel = require("./user-model");
const Router = require("koa-router");

const app = new Koa();

const router = new Router();

const sequelize = new Sequelize({
  dialect: "mysql", // 数据库类型
  host: "localhost",
  port: "3306",
  username: "root",
  password: "",
  database: "test",
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

module 模型

概念：代表table
命名：表users 对应 模型 User

定义

1. 导入Model和DataTypes类
2. 继承Model 用于重写自己的存储字段的逻辑
3. UserModel.init初始化字段
   字段添加方式为对象
4. 设置完成后将这个类返回出去

const { Model, DataTypes } = require("sequelize");

class UserModel extends Model {}

module.exports = (sequelize) => {
  UserModel.init(
    {
      id: {
        type: DataTypes.INTEGER,   // 数据类型
        primaryKey: true,	// 是否为主键
        autoIncrement: true,	// 是否自增
        allowNull: false,	//是否允许null
      },
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      age: {
        type: DataTypes.INTEGER,
        allowNull: false,
      },
    },
    {
      tableName: "users",	// 表名
      sequelize,
      updatedAt:false,
      createdAt:false

    }
  );
  return UserModel;
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

curd逻辑

建立连接

const UserModel = getUserModel(sequelize);
1

增 UserModel.create({})

router.get("/addUser", async (ctx) => {
  // 添加数据到 db
  const { name, age } = ctx.query;
  const result = await UserModel.create({
    name,
    age,
  });
  ctx.body = result;
});
1
2
3
4
5
6
7
8
9

删

router.get("/delUser", async (ctx) => {
  const { id } = ctx.query;
  const result = await UserModel.destroy({
    where: {
      id,
    },
  });

  if (result === 1) {
    ctx.body = "del user success";
  } else {
    ctx.body = "del user fail";
  }
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14

改

router.get("/updateUser", async (ctx) => {
  const { id, age } = ctx.query;
  const [result] = await UserModel.update(
    {
      age,
    },
    {
      where: {
        id,
      },
    }
  );

  if (result === 1) {
    ctx.body = "update user success";
  } else {
    ctx.body = "update user fail";
  }
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查找
UserModel.findByPk() 通过主键查找
UserModel.findAll({})查找多个

router.get("/findUser", async (ctx) => {
  const { id } = ctx.query;
  const model = await UserModel.findByPk(id);
  ctx.body = model;
});

// /findUsers
router.get("/findUsers", async (ctx) => {
  const { age = 0, offset = 0, limit = 5 } = ctx.query;
  const models = await UserModel.findAll({
    where: {
      age: {
        [Op.gt]: age,
      },
    },
    order: [["age", "DESC"]],
    offset: +offset,
    limit: +limit,
  });

  ctx.body = models;
});

router.get("/", (ctx) => {
  ctx.body = "hello sequelize-teach";
});

app.use(router.routes());

app.listen(8080, () => {
  console.log("open server localhost:8080");
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32