1.对象和数组
1.1 一对多 - 对象
前面我们学习的查询语句,查询到的结果通常是一张表,比如查询手机+品牌信息:
SELECT * FROM products LEFT JOIN brands ON products.brand_id = brand.id;
查询结果:
但是在真实开发中,实际上红色圈起来的部分应该放入到一个对象中,那么我们可以使用下面的查询方式:
-
这个时候我们要用
JSON_OBJECT
;
SELECT
products.id as id, products.title as title,
products.price as price, products.score as score,
JSON_OBJECT('id', brands.id, 'name', brands.name, 'rank', brands.worldRank, 'website', brands.website) as brand
FROM `products` LEFT JOIN `brands`ON products.brand_id = brands.id;
查询结果:
1.2 多对多 - 数组
在多对多关系中,我们希望查询到的是一个数组:
-
比如一个学生的多门课程信息,应该是放到一个数组中的;
-
数组中存放的是课程信息的一个个对象;
-
这个时候我们要
JSON_ARRAYAGG
和JSON_OBJECT
结合来使用;
SELECT stu.id, stu.name, stu.age,
JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name)) as courses
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id
GROUP BY stu.id;
查询结果:
2 mysql2 的使用
2.1 认识mysql2
前面我们所有的操作都是在GUI工具中,通过执行SQL语句来获取结果的,那真实开发中肯定是通过代码来完成所有的操作的。
那么如何可以在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中操作数据。
安装
npm install mysql2
2.2 mysql2基本使用
mysql2 的使用过程如下:
-
第一步:创建连接(通过createConnection),并且获取连接对象;
-
第二步:执行SQL语句即可(通过query);
const mysql = require('mysql2');
// 创建连接
const connection = mysql.createConnection({
host: 'localhost',
port:3306,
database: 'music_db',
user: 'root',
password: '123456.'
});
// 执行操作语句, 操作数据库
const statement='SELECT title, price FROM products WHERE price > 9000;'
connection.query(statement,(err, results, fields) => {
if (err) {
console.log('查询失败:', err)
return
}
// 查看结果
console.log('values:', values);
console.log('------------------------');
console.log('fields', fields);
})
通常我们的连接建立之后是不会轻易断开的,因为我们需要这个连接持续帮助我们查询客户端过来的请求。
但是如果我们确实希望断开连接,可以使用 end
方法:
connection.end();
// connection.destroy()
2.3 预编译语句
Prepared Statement(预编译语句):
-
提高性能:将创建的语句模块发送给MySQL,然后MySQL编译(解析、优化、转换)语句模块,并且存储它但是不执行,之后我们在真正执行时会给
?
提供实际的参数才会执行;就算多次执行,也只会编译一次,所以性能是更高的; -
防止SQL注入:之后传入的值不会像模块引擎那样就编译,那么一些SQL注入的内容不会被执行;
or 1 = 1
不会被执行;
const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
connection.execute(statement, [1000, '华为'], (err, results) => {
console.log(results);
});
强调:如果再次执行该语句,它将会从LRU(Least Recently Used) Cache中获取获取,省略了编译 statement 的时间来提高性能。
2.4 连接池
前面我们是创建了一个连接(connection),但是如果我们有多个请求的话,该连接很有可能正在被占用,那么我们是否需要每次一个请求都去创建一个新的连接呢?
-
事实上,mysql2给我们提供了连接池(connection pools);
-
连接池可以在需要的时候自动创建连接,并且创建的连接不会被销毁,会放到连接池中,后续可以继续使用;
-
我们可以在创建连接池的时候设置 LIMIT ,也就是最大创建个数;
const mysql = require('mysql2');
const pool = mysql.createPool({
host: 'localhost',
database: 'coderhub',
user: 'root',
password: 'Coderwhy888.',
connectionLimit: 10
});
const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
pool.execute(statement, [1000, '华为'], (err, results) => {
console.log(results);
});
为什么 Node 执行 JavaScript 时单线程的,还需要连接池呢?
-
这是因为 Node 中操作数据库,本质上是通过 Libuv 进行了的数据库操作;
-
而在 libuv 中是可以有多个线程的,多个线程也是可以同时去建立连接来操作数据库的;
2.5 promises
目前在 JavaScript 开发中我们更习惯 Promise 和 await、async 的方式,mysql2 同样是支持的:
const mysql = require('mysql2');
const pool = mysql.createPool({
host: 'localhost',
database: 'music_db',
user: 'root',
password: '123456',
connectionLimit: 5});
const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
pool.promise().execute(statement, [1000, '华为']).then(([results,fields]) => {
console.log(results);
});
3 ORM的sequelize
3.1 认识ORM
对象关系映射(英语:Object Relational Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序设计的方案:
-
从效果上来讲,它提供了一个可在编程语言中,使用
虚拟对象数据库
的效果; -
比如在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:使用它来让对象映射到表中;
npm install sequelize mysql2
3.2 Sequelize的使用
Sequelize的连接数据库:
-
第一步:创建一个Sequelize的对象,并且制定数据库、用户名、密码、数据库类型、主机地址等;
-
第二步:测试连接是否成功;
const {Sequelize, DataTypes, Model, Op} = require('sequelize');
const sequelize = new Sequelize('coderhub', 'root', 'Coderwhy888.', { host: 'localhost', dialect: 'mysql'});
sequelize.authenticate().then(() => {
console.log("sequelize连接成功~");})
.catch(err => {
console.log("sequlize连接失败~", err);});
Sequelize映射关系表:
class Student extends Model {}
Student.init(
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
},
name: {
type: DataTypes.STRING,
allowNull: false,
},
age: DataTypes.INTEGER,
},
{
sequelize,
createdAt: false,
updatedAt: false,
}
);
测试增删改查的操作:
async function queryStudent() {
// 1.查询所有的学生
const result1 = await Student.findAll({});
console.log(result1);
// 2.查询年龄大于等于20岁的学生
const result2 = await Student.findAll({ where: { age: { [Op.gte]: 20 } } });
console.log(result2);
// 3.创建用户
const result3 = await Student.create({ name: 'hmm', age: 22 });
console.log(result3);
// 4.更新用户
const result4 = await Student.update({ age: 25 }, { where: { id: 6 } });
console.log(result4);
}
queryStudent();
3.3 多对多关系
第一步:连接数据库
const { Sequelize, DataTypes, Model, Op } = require('sequelize');
const sequelize = new Sequelize('coderhub', 'root', 'Coderwhy888.', {
host: 'localhost',
dialect: 'mysql',
});
第二步:创建映射关系
class Student extends Model {}
Student.init(
{
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
name: { type: DataTypes.STRING, allowNull: false },
age: DataTypes.INTEGER,
},
{ sequelize, createdAt: false, updatedAt: false }
);
class Course extends Model {}
Course.init(
{
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
name: { type: DataTypes.STRING(20), allowNull: false },
price: { type: DataTypes.DOUBLE, allowNull: false },
},
{ sequelize, createdAt: false, updatedAt: false }
);
class StudentCourse extends Model {}
StudentCourse.init(
{
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
studentId: {
type: DataTypes.INTEGER,
references: { model: Student, key: 'id' },
field: 'student_id',
},
courseId: {
type: DataTypes.INTEGER,
references: { model: Course, key: 'id' },
field: 'course_id',
},
},
{
sequelize,
createdAt: false,
updatedAt: false,
tableName: 'students_select_courses',
}
);
第三步:建立多对多的联系
Student.belongsToMany(Course, {
through: StudentCourse,
foreignKey: 'student_id',
otherKey: 'course_id',
});
Course.belongsToMany(Student, {
through: StudentCourse,
foreignKey: 'course_id',
otherKey: 'student_id',
});
第四步:执行多对多查询操作:
async function queryStudent() {
// 查询结果
const result = await Student.findAll({ include: { model: Course } });
console.log(result);
}
queryStudent();