NodeJs连接MySQL

mysql与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的使用

  1. 安装mysql2：
1
2
npm init -y
npm install mysql2
  1. mysql2的使用过程如下：
    • 第一步：创建连接（通过createConnection），并且获取连接对象；
    • 第二步：执行SQL语句即可（通过query）；

基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
const mysql = require("mysql2");

// 1.创建数据库链接
const connection =mysql.createConnection({
    host:"localhost",
    port:3306,
    database:"bili",
    user:"root",
    password:"123456"
})

// 2.执行sql语句 使用query执行
const statement=`
SELECT * FROM products where price>6000
`
connection.query(statement,(err,result,fields)=>{
    console.log(result); //返回的是个数组对象;
})

预处理语句（Prepared Statement）

如果再次执行该SQL语句，它将会从缓存中获取，省略了编译 statement 的时间来提高性能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
const mysql = require("mysql2");

// 1.创建数据库链接
const connection = mysql.createConnection({
    host: "localhost",
    port: 3306,
    database: "bili",
    user: "root",
    password: "123456"
})

// 2.预处理语句   prepared
// 使用execute执行 (execute先执行了prepare 然后执行了 query)
const statement = `
SELECT * FROM products where price>? and score>?
`
connection.execute(statement, [6000, 7], (err, result, fields) => {
    console.log(result);//返回的是个数组对象;
})

连接池（Connection Pools）

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

  • 事实上，mysql2给我们提供了连接池（connection pools）；
  • 连接池可以在需要的时候自动创建连接，并且创建的连接不会被销毁，会放到连接池中，后续可以继续使用；
  • 我们可以在创建连接池的时候设置LIMIT，也就是最大创建个数；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
const mysql = require("mysql2");

// 1.创建连接池
const connections = mysql.createPool({
    host: "localhost",
    port: 3306,
    database: "bili",
    user: "root",
    password: "123456",
    connectionLimit: 10, //最多建立几个连接
})

// 2.使用连接池

const statement = `
SELECT * FROM products where price>? and score>?
`
connections.execute(statement, [6000, 7], (err, results) => {
    console.log(results);//返回的是个数组对象;
})

但是上面是通过回调函数来获取到的数据。回调函数容易产生回调地狱的问题；那如何解决呢？

  • 连接池connections有promise()方法,可以直接调用;
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
const mysql = require("mysql2");

// 1.创建连接池
const connections =mysql.createPool({
    host:"localhost",
    port:3306,
    database:"bili",
    user:"root",
    password:"123456",
    connectionLimit:10
})

// 2.使用连接池  connections有promise()方法,可以直接调用;

const statement=` 
SELECT * FROM products where price>? and score>?
`

// 回调函数形式;

// connections.execute(statement,[6000, 7],(err,results)=>{
//     console.log(results);
// })


// connections有promise()方法,可以直接调用;

// 这里.then中result是个数组是因为用到了数组解构,直接填result进去会得到一堆别的数据
connections.promise().execute(statement,[6000, 7]).then(([result])=>{
    console.log(result);//返回的是个数组对象;
}).catch(err=>{
    console.log(err);
})

SQL数据类型

数字类型

MySQL的数字类型有很多：

  • 整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；
  • 浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；
  • 精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）；

日期类型

  • YEAR以YYYY格式显示值；(范围 1901到2155，和 0000)；
  • DATE以格式YYYY-MM-DD显示值 ；（范围 ‘1000-01-01’ 到 ‘9999-12-31’）；
  • DATETIME以格式’YYYY-MM-DD hh:mm:ss’显示值；（支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59）；
  • TIMESTAMP以格式’YYYY-MM-DD hh:mm:ss’显示值；(但是它的范围是UTC的时间范围：’1970-01-01 00:00:01’到’2038-01-19 03:14:07’)；

字符串类型

  • CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值；(在被查询时，会删除后面的空格)；
  • VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值；(在被查询时，不会删除后面的空格)；
  • TEXT用于存储大的字符串类型；
  • BLOB用于存储大的二进制类型；

SQL表约束

主键：PRIMARY KEY

一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常会将它设
置为主键。

  • 主键是表中唯一的索引；
  • 并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL；
  • 主键也可以是多列索引，PRIMARY KEY(key_part, …)，我们一般称之为联合主键；
  • 建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键(例如：id)；

唯一：UNIQUE

某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用UNIQUE来约束。

  • 允许NULL

不能为空：NOT NULL

某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用 NOT NULL 来约束；

默认值：DEFAULT

某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用 DEFAULT来完成；

自动递增：AUTO_INCREMENT

某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成；

常用SQL语句

单表

查询

基础查询
  • 查询所有字段和所有数据;
    1
    SELECT * FROM `products`;
  • 查询指定字段和所有数据;
    1
    SELECT id,title,price FROM `products`;
  • 对字段结果起别名(as可以省略不写)
    1
    SELECT id,title as phoneTitle,price as currentPrice FROM `products`;
where查询
  • 查询价格小于1000的手机
    1
    SELECT * FROM `products` WHERE price<1000;
  • 查询价格等于999的手机 (SQL是一个=，和JS别搞混了)
    1
    SELECT * FROM `products` WHERE price=999;
  • 查询价格不等于999的手机 
    1
    2
    SELECT * FROM `products` WHERE price!=999;
    SELECT * FROM `products` WHERE price<>999;
  • 查询品牌是华为，并且小于2000元的手机
    1
    SELECT * FROM `products` WHERE `brand` = '华为' && `price` < 2000;
  • 查询所有的华为手机或者价格小于1000的手机
    1
    SELECT * FROM `products` WHERE `brand` = '华为' || `price` < 1000;
NULL

  • 将id 85-88的url设置为NULL

    1
    UPDATE `products` SET url=NULL WHERE id>=85&&id<=88;

  • 查询某个值为NULL 用 IS 不能用 =

    1
    SELECT * FROM `products` WHERE url IS NULL;

  • 查询某个值不为NULL 

    1
    SELECT * FROM `products` WHERE url IS NOT NULL;
模糊查询

  • 查询所有以v开头的title

    1
    SELECT * FROM `products` WHERE title LIKE 'v%';

  • 查询带M的title

    1
    SELECT * FROM `products` WHERE title LIKE '%M%';

  • 查询带M的title必须是第三个字符

    1
    SELECT * FROM `products` WHERE title LIKE '__M%';

  • IN表示取多个值中的其中一个即可 (取品牌为 华为、小米或苹果的)

    1
    2
    SELECT * FROM `products` WHERE brand ="华为"||brand="小米"||brand="苹果";
    SELECT * FROM `products` WHERE brand IN("华为","小米","苹果");
排序

根据哪个字段进行怎么样的排序 ASC 升序 DESC 降序

  • 根据价格的升序,价格一样时，根据评分降序,中间用逗号隔开;
    1
    SELECT * FROM `products` WHERE brand IN("华为","小米","苹果") ORDER BY price ASC,score DESC;
分页查询

  • 查询20条,偏移0条(1-20)

    1
    SELECT * FROM `products` LIMIT 20 OFFSET 0;
    • 查询20条,偏移20条(21-40)
    1
    SELECT * FROM `products` LIMIT 20 OFFSET 20;
聚合函数

将表中所有数据看成一个整体；一个组；(例如：一个班)

  • 求所有手机价格的总和,key改为 totalPrice;

    1
    SELECT SUM(price) totalPrice FROM `products`;

  • 求华为手机价格的总和,key改为 totalPrice;

    1
    SELECT SUM(price) totalPrice FROM `products` WHERE brand="华为";

  • 求华为手机平均价格

    1
    SELECT AVG(price) FROM `products` WHERE brand="华为";

  • 求最高手机价格和最低手机价格

    1
    2
    SELECT MAX(price) FROM `products`;
    SELECT MIN(price) FROM `products`;

  • 求华为手机个数

    1
    SELECT COUNT(*) FROM `products` WHERE brand="华为";

  • 求苹果手机存在url的个数 (url不为NULL)

    1
    SELECT COUNT(url) FROM `products` WHERE brand="苹果";

  • 求price不为null的个数

    1
    SELECT COUNT(price) FROM `products`;

  • 求 price 不为 null 的个数，并进行去重处理

    1
    SELECT COUNT(DISTINCT price) FROM products;
GROUP BY

GROUP BY通常和聚合函数一起使用 将数据分为多个组；(例如：一个班，通过某个条件进行分组)。

  • 通过brand条件进行整体的分组，(一般来说，按照xxx进行整体的划分了，也需要根据xxx来进行查询，所以也会SELECT xxx)；

    1
    SELECT brand FROM `products` GROUP BY brand;

  • 通过brand条件进行整体的分组，然后查询出分组之后的每组的平均价格，总数，平均分。

    1
    SELECT brand,AVG(price),COUNT(*),AVG(score)FROM `products` GROUP BY brand;

通过 GROUP BY分组之后，再根据某个条件进行筛选，不能使用WHRER，要使用HAVING(HAVING是对分组之后的数据进行筛选的)

WHERE 不能放在 GROUP BY 之后；写在 GROUP BY 之前；

  • 求平均价格avgPrice>2000的手机，按照品牌进行分类，求出每个品牌的平均价格，总数，平均分；
    1
    SELECT brand,AVG(price) avgPrice,COUNT(*),AVG(score)FROM products GROUP BY brand HAVING avgPrice>2000;

增加

1
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) VALUES ('iPhone', 'iPhone12只要998', 998.88, '2020-10-10');

删除

1
DELETE FROM `products` WHERE `id` = 2;

修改

1
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88 WHERE `id` = 2;

一对多表

参考文献：MySQL表设计思路，数据库表关系详解（一对多、一对一、多对多）；

  • 一对多，两张表，多的表加外键；
  • 在”多”关系的表中去维护一个字段，这个字段是”一”关系的主键；
  • 加外键约束，会保证数据业务上的一致性；

适用于重复数据的情景。

假设有 歌曲表，主歌手表；

一个主歌手可以有多个歌曲；但是反过来：一个歌曲，只能有一个主歌手。称之为一对多表；

例如：一首音乐，有歌手，歌曲URL，歌曲时长等信息；然后歌手还有自己的详细信息，比如性别，籍贯，个人介绍等；这些信息都是重复的数据，如果都放在同一张表，会造成数据冗余。需要进行数据拆分：

  • table1歌曲：id，歌曲URL，歌曲时长，歌手的id(第二张表的主键(外键约束))；
  • table2主歌手：id，性别，籍贯，个人介绍；
  • 最后通过table1歌曲中singer_id与table2主歌手的id，将两张表关联起来；

但是table1歌曲中singer_id的值是随便填的嘛，答案肯定是不可以的，否则会发生混乱；需要限制其只能填写table2主歌手中已有的id值；

此时，table1歌曲的值，需要受限于table2主歌手的值，称之为外键约束。

外键约束

将两张表联系起来，我们可以将song中的singer_id关联到singer中的id。操作步骤见下文navicat–>设置外键。

多表查询

  • 笛卡尔乘积，表示为 X*Y(第一张表中每一个条数据，都会和第二张表中的每一条数据结合一次)
1
2
// 两个表名之间用 逗号 分开
SELECT * FROM `song`,`singer`;
  • 基础查询( 这个表示查询到笛卡尔乘积后的结果中，符合song.singer_id = singer.id条件的数据过滤出来)
1
SELECT * FROM `song`,`singer` WHERE `song`.singer_id = `singer`.id
左连接

如果我们希望获取到的是左边所有的数据（以左表为主）；

这个时候就表示无论左边的表是否有对应的singer_id的值对应右边表的id，左边的数据都会被查询出来；

这个也是开发中使用最多的情况，它的完整写法是LEFT [OUTER] JOIN，但是OUTER可以省略的；

  • 左边的表数据都有，无论左边的表是否有对应的singer_id的值对应右边表的id，左边的数据都会被查询出来
1
SELECT * FROM `song`  LEFT JOIN `singer`  ON `song`.singer_id = `singer`.id
  • 左边singer_id的值为NULL的(左边的表没有对应的singer_id的值对应右边表的id)
1
SELECT * FROM `song`  LEFT JOIN `singer`  ON `song`.singer_id = `singer`.id WHERE singer_id IS NULL;
右连接

如果我们希望获取到的是右边所有的数据（以右表为主）；

这个时候就表示无论左边的表是否有对应的singer_id的值对应右边表的id，右边的数据都会被查询出来；

不常用，它的完整写法是RIGHT [OUTER] JOIN，但是OUTER可以省略的

  • 右边的表数据都有，无论左边的表是否有对应的singer_id的值对应右边表的id，右边的数据都会被查询出来
1
SELECT * FROM `song`  RIGHT JOIN `singer`  ON `song`.singer_id = `singer`.id
内连接

内连接和基础查询where效果是一样的，只不过是内部实现原理不一样；

  • 内连接，代表的是在两张表连接时就会约束数据之间的关系，来决定之后查询的结果；（INNER可以省略）
  • where条件，代表的是先计算出笛卡尔乘积，在笛卡尔乘积的数据基础之上进行where条件的帅选；
1
2
3
// 以下两种方式的结果一样
SELECT * FROM `song`  INNER JOIN `singer`  ON `song`.singer_id = `singer`.id; //内连接
SELECT * FROM `song`,`singer` WHERE `song`.singer_id = `singer`.id; // where条件
全连接

SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现。

即：多张表中，所有的数据

1
2
(SELECT * FROM `song`  LEFT JOIN `singer`  ON `song`.singer_id = `singer`.id) UNION
(SELECT * FROM `song`  RIGHT JOIN `singer`  ON `song`.singer_id = `singer`.id)
JSON_OBJECT()

多表查询：将歌手信息放到一个单独的对象中。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
 song.id as id, song.name as name,
 JSON_OBJECT('id',singer.id,'intro',singer.introduce,'sex',singer.sex) as singerInfo
 FROM `song` JOIN `singer` ON `song`.singer_id=`singer`.id;
 
 /** 结果为：
  {
   id: 1,
   name: "fsllala",
   singerInfo: { id: 1, sex: 1, intro: "前端小菜鸡" },
  }
 *

多对多表

  • 多对多，三张表，关系表加外键；
  • 添加数据时，先添加父表记录(student，course)，再添加子表(student_course_relation)记录；
  • 删除数据时，先删除子表记录(student_course_relation),再删除父表记录(student，course)；

假设有 学生表，课程表；

一个学生可以对应多个课程；反过来：一个课程，可以对应多个学生；称之为：多对多表。

此时添加数据比较麻烦，需要一个关系表，来进行表的关联

  1. 创建两张表：学生表，课程表
1
2
3
4
5
6
7
8
9
10
11
12
# 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
);
# 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL
);
  1. 分别插入一些数据
1
2
3
4
5
6
7
8
9
INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);
INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
  1. 创建关系表

我们需要一个关系表来记录两张表中的数据关系

1
2
3
4
5
6
7
8
# 创建关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
);
  1. 向关系表中插入数据
1
2
3
4
5
6
7
# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);
  1. 查询

查询多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 查询所有的学生选择的所有课程
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;

查询单个学生的课程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 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;

查询哪些学生没有选择和哪些课程没有被选择：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 哪些学生是没有选课的
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 cs.id IS NULL;
# 查询哪些课程没有被学生选择
SELECT 
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id IS NULL;

JSON_ARRAYAGG

多表查询：将课程信息放到一个单独的数组中，数组中又是对象类型；

1
2
3
4
5
6
7
8
9
10
11
SELECT
 stu.id as id,stu.name as name,
 JSON_ARRAYAGG(JSON_OBJECT('id',cs.id,'name',cs.name,'price',cs.price)) as courseInfo
FROM
	`students` as stu
LEFT JOIN `students_select_courses` as ssc ON stu.id=ssc.student_id
LEFT JOIN `courses` as cs ON  ssc.course_id=cs.id
WHERE
 cs.id IS NOT NULL
 GROUP BY
  stu.id

设置字段为自动更新时间列

参考文献

  • 选中列后在默认值写上CURRENT_TIMESTAMP，默认值下面的选项意思是当前列是否要在数据行内容更新时更新当前列时间；
  • createTime：默认值写上CURRENT_TIMESTAMP；默认值下面的选项不勾选；长度设置为(0-6)；
  • updateTime：默认值写上CURRENT_TIMESTAMP；默认值下面的选项勾选；长度设置为(0-6)；
  • 长度可以传0-6的数字，表示时间的精度，比如 CURRENT_TIMESTAMP(3)表示精确到毫秒值；

设置唯一

参考文献

  1. 右键–>设计表
  2. 清楚你想要设计哪个字段为唯一的，例如我这里是设计name字段唯一，然后点击索引；
  3. 然后设置相关内容
    • 名：索引名
    • 字段：想要设计的那个UNIQUE字段名，这里可以选择，也可以直接输入
    • 索引类型：当然是选择UNIQUE啦
    • 索引方法：可以不写；
    • 注释：就是注释啦

设置外键

参考文献

  1. 在table1中，右键–>设计表–>添加字段(singer_id)–>保存–>点击”外键”，然后进行如下配置；

  1. 删除/更新时：

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

    删除；

  • NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的；

  • CASCADE（级联）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：

    • 更新：那么会更新对应的记录；
    • 删除：那么关联的记录会被一起删除掉；

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