Node.js<十六>——多对多和mysql2的使用

Running53
597 阅读11分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第4天，点击查看活动详情

多对多建表及查询

新建多张表及创建关系表

  1. 创建学生表
CREATE TABLE IF NOT EXISTS `students` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`age` INT NOT NULL
);
  1. 插入数据
INSERT INTO `students` (name, age) VALUES ('小明', 18);
INSERT INTO `students` (name, age) VALUES ('小红', 22);
INSERT INTO `students` (name, age) VALUES ('小王', 19);
INSERT INTO `students` (name, age) VALUES ('小丽', 18);
INSERT INTO `students` (name, age) VALUES ('小李', 18);
INSERT INTO `students` (name, age) VALUES ('小段', 22);
INSERT INTO `students` (name, age) VALUES ('小张', 19);
INSERT INTO `students` (name, age) VALUES ('小白', 18);
  1. 建立课程表
CREATE TABLE IF NOT EXISTS `courses` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL
);
  1. 插入数据
INSERT INTO `courses` (name) VALUES ('语文');
INSERT INTO `courses` (name) VALUES ('数学');
INSERT INTO `courses` (name) VALUES ('英语');
INSERT INTO `courses` (name) VALUES ('物理');
INSERT INTO `courses` (name) VALUES ('化学');
INSERT INTO `courses` (name) VALUES ('生物');
  1. 创建关系表
CREATE TABLE IF NOT EXISTS `students_courses` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`student_id` INT NOT NULL,
	`course_id` INT NOT NULL
);
  1. 为关系表中的student_id和course_id添加外键约束
ALTER TABLE `students_courses` ADD FOREIGN KEY(`student_id`) REFERENCES `students`(id);
ALTER TABLE `students_courses` ADD FOREIGN KEY(`course_id`) REFERENCES `courses`(id);
  1. 插入数据
INSERT INTO `students_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_courses` (student_id, course_id) VALUES (1, 3);
INSERT INTO `students_courses` (student_id, course_id) VALUES (1, 5);
INSERT INTO `students_courses` (student_id, course_id) VALUES (2, 1);
INSERT INTO `students_courses` (student_id, course_id) VALUES (3, 4);
INSERT INTO `students_courses` (student_id, course_id) VALUES (4, 5);

查询练习

  1. 查询所有选课了的学生，并展示他们选择了哪些课程
# 可以直接使用别名，设置别名的时候可以省略AS
# 先将学生表和中间表进行一个内连接，然后再将查询到的结果与课程表进行内连接
SELECT stu.id id, stu.name stuName, stu.age stuAge, sc.course_id courseId, cs.name courseName 
FROM `students` stu
JOIN `students_courses` sc ON stu.id = sc.student_id 
JOIN `courses` cs ON sc.course_id = cs.id;

  1. 查询所有学生的选课情况，无论其有没有选上
# 注意：左连接的符号LEFT千万不要忘写了
# ON后面是连接条件
SELECT stu.id id, stu.name stuName, stu.age stuAge, sc.course_id courseId, cs.name courseName 
FROM `students` stu
LEFT JOIN `students_courses` sc ON stu.id = sc.student_id
LEFT JOIN `courses` cs ON sc.course_id = cs.id;

  1. 哪些学生是没有选课的
# 只需要在我们刚刚的基础上添加上一个WHERE语句即可，查询出courseId为null的值就是没有选课的学生数据
SELECT stu.id id, stu.name stuName, stu.age stuAge, sc.course_id courseId, cs.name courseName 
FROM `students` stu
LEFT JOIN `students_courses` sc ON stu.id = sc.student_id
LEFT JOIN `courses` cs ON sc.course_id = cs.id 
WHERE cs.id IS NULL;

  1. 查询哪些课程是没有选择的

现在我们的主体变了，所以最后一次查询一定要用右连接才能把课程当做主体

# 其实第一次查询左连接还是右连接都可以，只要最终查询到的结果没有学生id那就证明该课程没有人选
SELECT cs.id courseId, cs.name courseName 
FROM `students` stu
RIGHT JOIN `students_courses` sc ON stu.id = sc.student_id
RIGHT JOIN `courses` cs ON sc.course_id = cs.id 
WHERE stu.id IS NULL;

  1. 查询某一个学生选了哪些课程（比如小明）
# 只需要在前面示例的基础上加上个WHERE查询语句即可
SELECT stu.id id, stu.name stuName, stu.age stuAge, sc.course_id courseId, cs.name courseName 
FROM `students` stu
LEFT JOIN `students_courses` sc ON stu.id = sc.student_id
LEFT JOIN `courses` cs ON sc.course_id = cs.id 
WHERE stu.name = '小明';

对象和数组类型

将联合查询到的数据转成对象（一对多）

# 我们可以使用功能JSON_OBJECT将查询到的数据转为json格式并给他起别名
SELECT 
stu.id id, stu.name stuName, stu.age stuAge, JSON_OBJECT('course_id', cs.id, 'courseName', cs.name) course
FROM `students` stu
LEFT JOIN `courses` cs
ON stu.id = cs.id;

将查询到的多条数据组织成对象放到一个数组中去（多对多）

这就要用到分组GROUP BY语句了，根据id来分组的话，相同id的数据就只会出现一条。如果想将多个对象放到一个数组中，可以使用JSON_ARRAYAGG，里面再嵌套一层JSON_OBJECT即可

# 注意：这下面是内连接，这样没有联系的数据就不会出现了 
SELECT 
stu.id id, stu.name name, stu.age age,
JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name)) course
FROM `students` stu
JOIN `students_courses` sc ON stu.id = sc.student_id
JOIN `courses` cs ON sc.course_id = cs.id 
GROUP BY stu.id;

在Node当中如何操作数据库？

认识mysql2

如何可以在Node的代码中执行SQL语句呢？这里我们可以借助于两个库：

  • mysql：最早的Node连接MySQL的数据库驱动
  • mysql2：在mysql的基础之上，进行了很多的优化、改进

目前相对来说，我更偏向于使用mysql2，mysql2兼容mysql的API，并且提供了一些附加功能

  • 更快/更好的性能

  • Prepared Statement（预编译语句）：

    • 提高性能：将创建的语句模块发送给MySQL，然后MySQL编译（解析、优化、转换）语句模块，存储它但是不执行，之后我们在真正执行时需要给模板提供实际的参数；就算多次执行，也只会编译一次，所以性能是更高的
    • 防止SQL注入：之后传入的值不会像模板引擎那样编译，所以一些SQL注入的内容就不会被执行，比如：OR 1 = 1不会被执行
  • 支持Promise，所以我们可以使用async和await语法
  • 等等...

所以后续的学习中我会选择mysql2在node中操作数据

mysql2的使用

  1. 我们可以使用mysql2对象上的createConnection方法在填写好数据库信息之后创建一个连接
  2. 还可以利用connection.query方法根据sql语句去数据库中查询结果
const mysql2 = require('mysql2')

const connection = mysql2.createConnection({
  host: 'localhost',
  database: 'test',
  user: 'root',
  password: '13413090799dsz'
})

const statement = `
  SELECT * from students;
`

connection.query(statement, (err, results, fields) => {
  console.log(results);
})

下面是打印results的结果：

如何停止mysql2？

  1. connection.end函数，如果终止过程中发生了错误信息，我们还可以用connection.on('error', err => {})监听到
connection.query(statement, (err, results, fields) => {
  connection.end()
})
  1. connection.destory函数，表示强制关闭，不过关闭过程中共发生了错误也监听不到
connection.query(statement, (err, results, fields) => {
  connection.destory()
})

Prepared Statement（预编译语句）

  1. 提高性能：

任何SQL语句在执行前都需要编译，包括（解析、优化、转换），最终SQL语句才会被执行，如果每次我们不使用预编译语句，有一条语句需要执行多次的话，那么就会一直重复编译的过程；但如果我们使用了预编译语句，就算多次执行，也只会编译一次，所以性能是更高的

预编译语句执行过程：将创建的语句模块发送给MySQL，然后MySQL编译（解析、优化、转换）语句模块并且存储它但是不执行，之后我们在真正执行时会给'?'提供实际的参数才会执行；

  1. 防止SQL注入：

SQL注入就是往你的SQL语句里面注入了一些恒成立的条件

比如我们有下面一条语句：

SELECT * FROM user WHERE username = abc AND password = 123 OR 1 = 1;

这样一条被sql注入过的语句，无论什么时候这条语句都是查询成功的，黑客就可以获取到数据库中的信息了

那为什么预编译语句可以很好的处理这种攻击呢？

因为给语句传入的值不会像模板引擎那样就编译，那么一些SQL注入的内容不会被执行；比如OR 1 = 1这种恒成立的语句就不会被执行

  1. 强调：

执行预编译模板的时候要使用connection.execute而不是connection.query函数，因为connection.execute相当于先帮助我们先调用了prepare函数然后再调用的query函数。如果多次执行该语句，它将会从LRU Cache（LRU缓存）中获取，省略了编译statement的时间，进而提高性能

const mysql2 = require('mysql2')

const connection = mysql2.createConnection({
  host: 'localhost',
  database: 'test',
  user: 'root',
  password: '13413090799dsz'
})

const statement = `
SELECT * from students WHERE id > ? AND id < ?;
`

connection.execute(statement, [2, 8], (err, results, fields) => {
  console.log(results);
})

打印结果：

Connection Pools（连接池）

前面我们是创建了一个连接，但是如果我们有多个请求的话，该连接很有可能正在被占用，那么我们是否需要每次一个请求都去创建一个新的链接呢？

  • 事实上，mysql2给我们提供了连接池
  • 连接池可以在需要的时候自动创建连接，并且创建的连接不会被销毁，会放到连接池中，后续可以继续使用
  • 我们可以在创建连接池的时候可以设置connectionLimit，也就是最大创建个数

使用：

const mysql2 = require('mysql2')

const connection = mysql2.createPool({
  host: 'localhost',
  database: 'test',
  user: 'root',
  password: '13413090799dsz',
  connectionLimit: 10
})

const statement = `
  SELECT * from students WHERE id > ? AND id < ?;
`

connection.execute(statement, [2, 8], (err, results, fields) => {
  console.log(results);
})

connection.on('error', err => {
  console.log(err);
})

使用Promise的方式查询结果

connection对象上有个promise方法，调用它之后我们就可以以Promise的语法去查询数据库了

const mysql2 = require('mysql2')

const connection = mysql2.createPool({
  host: 'localhost',
  database: 'test',
  user: 'root',
  password: '13413090799dsz',
  connectionLimit: 10
})

const statement = `
  SELECT * from students WHERE id > ? AND id < ?;
`

connection.promise().execute(statement, [2, 8]).then(res => {
  // 注意：这里的res包括results和fields的内容
  const [results, fields] = res
  console.log(results);
}).catch(err => {
  console.log(err);
})

connection.on('error', err => {
  console.log(err);
})

认识ORM

对象关系映射（Object Relation Mapping，简称ORM），是一种程序设计的方案

  • 从效果上来讲，它提供了一个可在编程语言中，使用虚拟对象数据库的效果
  • 比如在Java开发中经常使用的ORM包括：Hibernate、MyBatis

Node当中的ORM我们通常使用的是sequelize

  • Sequelize适用于Postgres，MySQL，MariaDB，SQLite和Microsoft SQL Server等基于Node.js的ORM
  • 它支持非常多的功能

如果我们希望将Sequelize和MySQL一起使用，那么我们需要先安装两个东西

  • mysql2：sequelize在操作mysql时使用的是mysql2
  • sequelize：使用它来让对象映射到表中

Sequelize的使用

Sequelize的连接数据库：

  • 第一步：创建一个Sequelize的对象，并且指定数据库、用户名、密码、数据库类型、主机地址等
  • 第二步：sequelize.authenticate方法可以帮助我们测试连接是否成功
const { Sequelize } = require('sequelize')

const sequelize = new Sequelize('test', 'root', '13413090799dsz', {
  host: 'localhost',
  dialect: 'mysql'
})

sequelize.authenticate().then(() => {
  console.log('数据库连接成功~');
}).catch(() => {
  console.log('数据库连接失败~');
})

Sequelize的单表操作

Sequelize主要帮助我们建立的是类和数据表的关系，让我们能够通过这个类提供的一些api直接去操作我们的数据库

  1. 查询数据库中students表中所有的内容
const { Sequelize, DataTypes, Model, Op } = require('sequelize')

const sequelize = new Sequelize('test', 'root', '13413090799dsz', {
  host: 'localhost',
  dialect: 'mysql'
})

class Students extends Model { }

Students.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(20),
    allowNull: false
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: false
  }
}, {
  // 指定要关联表的名称
  tableName: 'students',
  // 它默认我们表中是有createdAt和updatedAt这两个字段的，如果没有的话我们这里要说明一下
  createdAt: false,
  updatedAt: false,
  sequelize
})

const queryStudents = async () => {
  const results = await Students.findAll()
  console.log(results);
}

queryStudents()
  1. 通过不同条件来查询内容，比如查询所有id大于2且小于6的学生
const queryStudents = async () => {
  const results = await Students.findAll({
    where: {
      id: {
        // 这里需要在sequelize库中引入一个Op对象
        [Op.gt]: 2,
        [Op.lt]: 6
      }
    }
  })
  console.log(results);
}
  1. 插入数据
  const results = await Students.create({
    name: '小花',
    age: 32
  })
  console.log(results);
  1. 更新数据
  const results = await Students.update({
    age: 59,
  }, {
    where: {
      id: 3
    }
  })
  console.log(results);

Sequelize的一对多操作

  1. 建立Students类和students数据表的连接和初始化
  2. 建立StudentsCourses类和students_courses数据表的连接和初始化
  3. 将两个要查询的表建立联系
  4. 利用findAll方法进行一对多查询
const { Sequelize, DataTypes, Model } = require('sequelize')

const sequelize = new Sequelize('test', 'root', '13413090799dsz', {
  host: 'localhost',
  dialect: 'mysql'
})

class Students extends Model { }
Students.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(20),
    allowNull: false
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: false
  }
}, {
  tableName: 'students',
  createdAt: false,
  updatedAt: false,
  sequelize
})


class StudentsCourses extends Model { }
StudentsCourses.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  studentId: {
    // field代表该属性对应表中的哪个字段，相当于起别名
    field: 'student_id',
    type: DataTypes.INTEGER,
    allowNull: false,
    // 如果该字段具有外键约束，那么需要用references来指定依赖的是哪个表和字段
    references: {
      model: Students,
      key: 'id'
    }
  },
  courseId: {
    field: 'course_id',
    type: DataTypes.INTEGER,
    allowNull: false
  }
}, {
  tableName: 'students_courses',
  createdAt: false,
  updatedAt: false,
  sequelize
})

// 使用belongsTo方法将两张表联系起来
StudentsCourses.belongsTo(Students, {
  foreignKey: 'studentId'
})

// 使用belongsTo方法将两张表联系起来
Students.belongsTo(StudentsCourses, {
  foreignKey: 'id'
})

const queryStudents = async () => {
  const results1 = await StudentsCourses.findAll({
    // include表示联合哪个表一起查询
    include: {
      model: Students
    }
  })
  console.log(results1);
  const results2 = await Students.findAll({
    // include表示联合哪个表一起查询
    include: {
      model: StudentsCourses
    }
  })
  console.log(results2);
}

queryStudents()

Sequelize的多对多操作

  1. 建立Students类和students数据表的连接和初始化
  2. 建立Courses类和courses数据表的连接和初始化
  3. 建立StudentsCourses类和students_courses数据表的连接和初始化
  4. 使用belongsToMany方法建立多对多关系的联系
  5. foreignKey属性永远是跟调用了belongsToMany函数的那个类有关的外键，otherKey就是其它的外键，不同的类调用belongsToMany函数，建立关系时需要填写的foreignKey是不一样的
const { Sequelize, DataTypes, Model, Op } = require('sequelize')

const sequelize = new Sequelize('test', 'root', '13413090799dsz', {
  host: 'localhost',
  dialect: 'mysql'
})

class Students extends Model { }
Students.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(20),
    allowNull: false
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: false
  }
}, {
  tableName: 'students',
  createdAt: false,
  updatedAt: false,
  sequelize
})

class Courses extends Model { }
Courses.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(20),
    allowNull: false
  }
}, {
  tableName: 'courses',
  createdAt: false,
  updatedAt: false,
  sequelize
})

class StudentsCourses extends Model { }
StudentsCourses.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  studentId: {
    field: 'student_id',
    type: DataTypes.INTEGER,
    allowNull: false,
    references: {
      model: Students,
      key: 'id'
    }
  },
  courseId: {
    field: 'course_id',
    type: DataTypes.INTEGER,
    allowNull: false,
    references: {
      model: Courses,
      key: 'id'
    }
  }
}, {
  tableName: 'students_courses',
  createdAt: false,
  updatedAt: false,
  sequelize
})

// 建立多对多关系的联系
Students.belongsToMany(Courses, {
  // thougth指定的是关系表
  through: StudentsCourses,
  foreignKey: 'studentId',
  otherKey: 'courseId'
})

// 建立多对多关系的联系，注意这里foreignKey和otherKey是反过来的，因为是Courses调用了belongsToMany
Courses.belongsToMany(Students, {
  through: StudentsCourses,
  foreignKey: 'courseId',
  otherKey: 'studentId'
})

const queryStudents = async () => {
  const results1 = await Students.findAll({
    include: {
      model: Courses
    }
  })
  console.log(results1);
  const results2 = await Courses.findAll({
    include: {
      model: Students
    }
  })
  console.log(results2);
}

queryStudents()

查询结果results1没有问题，小明确实选了三门课，小红选了一门课

查询结果results2也没有问题，确实有两个同学选了英语，两个同学选了语文

avatar
文章
阅读
粉丝