重学Node.js及其框架(Express, Koa, egg.js) 之 MySQL及Nodejs中使用MySQL(下)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 重学Node.js及其框架(Express, Koa, egg.js) 之 MySQL及Nodejs中使用MySQL

多张表


如果我们想要两张表存在联系。我们就需要在一张表中给联系字段设置外键。


现在假设我们有一张products和一张brand表。将两张表联系起来,我们可以将products中的brand_id关联到brand中的id。


ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id);


我们如何让两个表有关的字段做到联动呢?


当我们更新或者删除外键时,可以设置几个值:


  • RESTRICT(默认属性):当更新或删除某个记录时,会检查该记录是否有关联的外键记录,有的话会报错的,不允许更新或删除;


网络异常,图片无法展示
|


  • NO ACTION:和RESTRICT是一致的,是在SQL标准中定义的;


  • CASCADE:当更新或删除某个记录时,会检查该记录是否有关联的外键记录,有的话:


  • 更新:那么会更新对应的记录;


  • 删除:那么关联的记录会被一起删除掉;


网络异常,图片无法展示
|


网络异常,图片无法展示
|


  • SET NULL:当更新或删除某个记录时,会检查该记录是否有关联的外键记录,有的话,将对应的值设置为NULL;


网络异常,图片无法展示
|


注 意:当我们使用外键,并且将类型设置为cascade时,我们删除外表数据,那么将会将内表对应数据也删除。但是删除内表数据,将不会影响外表。


尽量不要使用cascade类型,但是对于一些特殊操作,使用cascade类型很方便。 假如我们删除评论数据,想要恢复的数据也删除,就可以设置cascade类型了。方便操作。

如何通过代码修改外键类型呢?


-- 查看表结构,找到外键名称
    SHOW CREATE TABLE `表名`;
    -- 删除之前的外键,根据外键名称
    ALTER TABLE `表名` DROP FOREIGN KEY 外键名称;
    -- 添加新的外键
    ALTER TABLE `表名` ADD FOREIGN KEY (引用的外键字段)
    REFERENCES 外键表(外键字段)
    ON UPDATE 更新时的外键类型 ON DELETE 删除时的外键类型;
    -- 查看表结构,找到外键名称
    SHOW CREATE TABLE `products`;
    -- 删除之前的外键
    ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;
    -- 添加新的外键
    ALTER TABLE `products` ADD FOREIGN KEY (brand_id)
    REFERENCES brand(id)
    ON UPDATE CASCADE ON DELETE CASCADE;


多表之间的连接查询


如果我们希望查询到产品的同时,显示对应的品牌相关的信息,因为数据是存放在两张表中,所以这个时候就需要进行多表查询。


我们可以使用 SQL JOIN 操作: 左连接 右连接 内连接 全连接


网络异常,图片无法展示
|


以下查询的sql语句案例。我们假设有products(id, brand, title, price, score, voteCnt, url, pid, brand_id(外键字段))brand(id(外键引用字段), name, website, phoneRank)两个表


左连接如果我们希望获取到的是左边所有的数据(以左表为主):


网络异常,图片无法展示
|


这个时候就表示无论左边的表是否有对应的brand_id的值对应右边表的id,左边的数据都会被查询出来,这个也是开发中使用最多的情况,它的完整写法是LEFT [OUTER] JOIN,但是OUTER可以省略的;


SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id;
    SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id= `brand`.id
    WHERE brand.id IS NULL;


右连接如果我们希望获取到的是右边所有的数据(以由表为主):


网络异常,图片无法展示
|


这个时候就表示无论左边的表中的brand_id是否有和右边表中的id对应,右边的数据都会被查询出来,右连接在开发中没有左连接常用,它的完整写法是RIGHT [OUTER] JOIN,但是OUTER可以省略的。


SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id;
    SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id= `brand`.id
    WHERE products.id IS NULL;


内连接事实上内连接是表示左边的表和右边的表都有对应的数据关联:


网络异常,图片无法展示
|


内连接在开发中偶尔也会常见使用,看自己的场景。内连接有其他的写法:CROSS JOIN, INNER JOIN, JOIN都可以。


SELECT * FROM `products` INNER JOIN `brand` ON `products`.brand_id = `brand`.id;


我们会发现它和之前的下面写法是一样的效果:


SELECT * FROM `products`, `brand` WHERE `products`.brand_id = `brand`.id;


但是他们代表的含义并不相同:


  • 内连接,代表的是在两张表连接时就会约束数据之间的关系,来决定之后查询的结果。


  • where条件,代表的是先计算出笛卡尔乘积,在笛卡尔乘积的数据基础之上进行where条件的筛选。全连接SQL规范中全连接是使用FULL JOIN,但是MySQL中并没有对它的支持,我们需要使用 UNION 来实现:


网络异常,图片无法展示
|


(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id);
(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id 
WHERE `brand`.id IS NULL)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id 
WHERE `products`.id IS NULL);


如何将我们查询到多表字段的数据转化成对象或者数组呢?


我们需要用到JSON_OBJECT(key, value, [...])JSON_ARRAY()方法来将一个表中的字段转化成一个对象或者一个数组。但是如果想把转为的多个对象再放在数组中,我们就需要用到JSON_ARRAYAGG()方法。一对多


SELECT products.id as id, 
    products.title as title, 
    products.price as price, 
    products.score as score,
    JSON_OBJECT(
        'id', brand.id, 
        'name', brand.name, 
        'rank', brand.phoneRank, 
        'website', brand.website
    ) as brand
    FROM products LEFT JOIN brand ON products.brand_id = brand.id;


网络异常,图片无法展示
|


多对多


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;


网络异常,图片无法展示
|


从上面的查询可以看出,我们如果想要合并通记录的数据,可以使用分组。而且分组

后,如果字段是唯一的,那么多可以获取,而不只是只能获取分组字段。


多对多关系查询


如果我们想把两个表建立联系,我们就需要建立一个中间表。这个关系表来记录两张表中的数据关系。然后就是通过多表连接查询语法,来进行查询了。


以下查询的sql语句案例。我们假设有students(id(外键), name, age), courses(id(外键), name, price), 关系表students_select_cources(id, student_id(引用外键字段), course_id(引用外键字段))三个表。 这个关系表用来记录学生id和课程id的对应关系,来确定学生的选课情况。


查询多条数据


# 查询所有的学生选择的所有课程
    SELECT
    stu.id studentId, stu.name studentName, cs.id
    courseId, cs.name
    courseName, cs.price
    coursePrice
    FROM `students` stu
    JOIN `students_select_courses` ssc
    ON stu.id = ssc.student_id
    JOIN `courses` cs
    ON ssc.course_id = cs.id;
    # 查询所有的学生选课情况
    SELECT
    stu.id studentId, stu.name studentName, cs.id
    courseId, cs.name
    courseName, cs.price
    coursePrice
    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;


查询单挑数据


# why同学选择了哪些课程
    SELECT
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price
    coursePrice
    FROM `students` stu
    JOIN `students_select_courses` ssc
    ON stu.id = ssc.student_id
    JOIN `courses` cs
    ON ssc.course_id = cs.id
    WHERE stu.id = 1;
    # lily同学选择了哪些课程(注意,这里必须用左连接,事实上上面也应该使用的是左连接)
    SELECT
    stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price
    coursePrice
    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
    WHERE stu.id = 5;


Nodejs中使用MySQL


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


  • mysql:最早的Node连接MySQL的数据库驱动。


  • mysql2:在mysql的基础之上,进行了很多的优化、改进。 目前相对来说,我更偏向于使用mysql2,mysql2兼容mysql的API,并且提供了一些附加功能更快/更好的性能。


  • Prepared Statement(预编译语句):


  • 提高性能:将创建的语句模块发送给MySQL,然后MySQL编译(解析、优化、转换)语句模块,并且存储它但是不执行,之后我们在真正执行时会给?提供实际的参数才会执行;就算多次执行,也只会编译一次,所以性能是更高的;


  • 防止SQL注入:之后传入的值不会像模块引擎那样就编译,那么一些SQL注入的内容不会被执行;or 1 = 1不会被执行;


  • 支持Promise,所以我们可以使用async和await语法。


基本使用


通过调用createConnection()并传入对应数据库连接信息,它将返回一个连接对象。然后通过query执行sql语句。


const mysql = require('mysql2');
    // 1.创建数据库连接
    const connection = mysql.createConnection({
      host: 'localhost',
      port: 3306,
      database: '数据库名',
      user: 'root',
      password: '数据库密码'
    });
    // 2.执行SQL语句
    const statement = `
      SELECT * FROM products WHERE price > 6000;
    `
    connection.query(statement, (err, results, fields) => {
      console.log(results);
    });


通过预处理语句使用


通过?来占位。然后通过execute传入占位的参数,并执行sql语句。


强调:如果再次执行该语句,它将会从LRU(Least Recently Used) Cache中获取获取,省略了编译statement的时间来提高性能。


const mysql = require('mysql2');
    // 1.创建数据库连接
        const connection = mysql.createConnection({
          host: 'localhost',
          port: 3306,
          database: '数据库名',
          user: 'root',
          password: '数据库密码'
        });
    // 2.执行SQL语句
    const statement = `
      SELECT * FROM products WHERE price > ? AND score > ?;
    `
    // 参数:sql语句, 传入的参数[], 执行的回调函数
    connection.execute(statement, [6000, 7], (err, results) => {
      console.log(results);
    });


连接池的使用


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


事实上,mysql2给我们提供了连接池(connection pools);


连接池可以在需要的时候自动创建连接,并且创建的连接不会被销毁,会放到连接池中,后续可以继续使用; 我们可以在创建连接池的时候设置LIMIT,也就是最大创建个数;


const mysql = require('mysql2');
    // 1.创建连接池
    const connections = mysql.createPool({
      host: 'localhost',
      port: 3306,
      database: '数据库名',
      user: 'root',
      password: '数据库密码'
      // 最大连接数
      connectionLimit: 10
    });
    // 2.使用连接池
    const statement = `
      SELECT * FROM products WHERE price > ? AND score > ?;
    `
    connections.execute(statement, [6000, 7], (err, results) => {
      console.log(results);
    });


promise的使用方式


就是连接对象调用promise()方法。


const mysql = require('mysql2');
    // 1.创建连接池
    const connections = mysql.createPool({
      host: 'localhost',
      port: 3306,
      database: '数据库名',
      user: 'root',
      password: '数据库密码'
      // 最大连接数
      connectionLimit: 10
    });
    // 2.使用连接池
    const statement = `
      SELECT * FROM products WHERE price > ? AND score > ?;
    `
    connections.promise().execute(statement, [6000, 7]).then(([results]) => {
      console.log(results);
    }).catch(err => {
      console.log(err);
    });


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
Web App开发 JavaScript 前端开发
Node.js 是一种基于 Chrome V8 引擎的后端开发技术,以其高效、灵活著称。本文将介绍 Node.js 的基础概念
Node.js 是一种基于 Chrome V8 引擎的后端开发技术,以其高效、灵活著称。本文将介绍 Node.js 的基础概念,包括事件驱动、单线程模型和模块系统;探讨其安装配置、核心模块使用、实战应用如搭建 Web 服务器、文件操作及实时通信;分析项目结构与开发流程,讨论其优势与挑战,并通过案例展示 Node.js 在实际项目中的应用,旨在帮助开发者更好地掌握这一强大工具。
46 1
|
21天前
|
存储 JavaScript NoSQL
Node.js新作《循序渐进Node.js企业级开发实践》简介
《循序渐进Node.js企业级开发实践》由清华大学出版社出版,基于Node.js 22.3.0编写,包含26个实战案例和43个上机练习,旨在帮助读者从基础到进阶全面掌握Node.js技术,适用于初学者、进阶开发者及全栈工程师。
46 9
|
1月前
|
缓存 负载均衡 JavaScript
构建高效后端服务:Node.js与Express框架实践
在数字化时代的浪潮中,后端服务的重要性不言而喻。本文将通过深入浅出的方式介绍如何利用Node.js及其强大的Express框架来搭建一个高效的后端服务。我们将从零开始,逐步深入,不仅涉及基础的代码编写,更会探讨如何优化性能和处理高并发场景。无论你是后端新手还是希望提高现有技能的开发者,这篇文章都将为你提供宝贵的知识和启示。
|
1月前
|
JavaScript
使用node.js搭建一个express后端服务器
Express 是 Node.js 的一个库,用于搭建后端服务器。本文将指导你从零开始构建一个简易的 Express 服务器,包括项目初始化、代码编写、服务启动与项目结构优化。通过创建 handler 和 router 文件夹分离路由和处理逻辑,使项目更清晰易维护。最后,通过 Postman 测试确保服务正常运行。
68 1
|
1月前
|
Web App开发 JSON JavaScript
Node.js 中的中间件机制与 Express 应用
Node.js 中的中间件机制与 Express 应用
|
1月前
|
Web App开发 JavaScript 前端开发
探索后端开发:Node.js与Express的完美结合
【10月更文挑战第33天】本文将带领读者深入了解Node.js和Express的强强联手,通过实际案例揭示它们如何简化后端开发流程,提升应用性能。我们将一起探索这两个技术的核心概念、优势以及它们如何共同作用于现代Web开发中。准备好,让我们一起开启这场技术之旅!
49 0
|
1月前
|
Web App开发 JavaScript 前端开发
构建高效后端服务:Node.js与Express框架的实践
【10月更文挑战第33天】在数字化时代的浪潮中,后端服务的效率和可靠性成为企业竞争的关键。本文将深入探讨如何利用Node.js和Express框架构建高效且易于维护的后端服务。通过实践案例和代码示例,我们将揭示这一组合如何简化开发流程、优化性能,并提升用户体验。无论你是初学者还是有经验的开发者,这篇文章都将为你提供宝贵的见解和实用技巧。
|
1月前
|
Web App开发 JavaScript 中间件
构建高效后端服务:Node.js与Express框架的融合之道
【10月更文挑战第31天】在追求快速、灵活和高效的后端开发领域,Node.js与Express框架的结合如同咖啡遇见了奶油——完美融合。本文将带你探索这一组合如何让后端服务搭建变得既轻松又充满乐趣,同时确保你的应用能够以光速运行。
37 0
|
6天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
24 3
|
6天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
29 3