多张表

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

现在假设我们有一张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);
    });

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

多张表

多表之间的连接查询

多对多关系查询

Nodejs中使用MySQL

基本使用

通过预处理语句使用

连接池的使用

promise的使用方式

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像