mysql与sequelize基本使用方法

三月 11, 2020 Node.js学习, 微博项目本文总阅读量次

mysql

mysql的下载安装等基本用法可参考博客：nodejs链接mysql及项目真实使用方法
操作mysql的客户端我选择的是SQLyog

SQLyog中建库建表

首先新建数据库koa2_weibo_db
然后建表
- users表：
- blogs表：

sql语句

增删查改

增删查改的sql语句 可参考博客：nodejs链接mysql及项目真实使用方法
在两个表中分别插入一些数据：

查询总数（`select count()...from...`）

查询总数和查一样，如果使用*来查询就很费性能，所以一般只查一列的总数即可
- 比如：SELECT COUNT(列名) FROM 表名;
- 或者：SELECT COUNT(列名) AS 重命名 FROM 表名;
例子：

分页（`select...`中`limit`与`offset`）

limit x：限制只查询x行
offset x：跳过x行再查询
例子：假定一页是两行数据：

外键

这个在nodejs链接mysql及项目真实使用方法中没有提到的
在sqlyog中创建外键，创建外键时可设置更新/删除是否级联

创建外键

首先需要保证2列数据的类型和限定长度一致
然后在sqlyog进行操作，创建外键、设置更新级联、删除级联：
刷新后，可看到blogs-indexes下出现了外键userid：

更新限制&更新/删除级联

更新限制：blogs表中的userid被限制为users表中的id，不能输入其他的内容
- 例子：
更新级联（update cascade）：（创建外键时可设置）一旦users表中的id被更新，blogs表中对应的userid的那条数据将被自动更新
- 一般id是不更新的，所以这里只做了解
删除级联（delete cascade）：（创建外键时可设置）一旦users表中的id被删除，blogs表中对应的userid的那条数据将被自动删除
- 例子：

连表查询（`inner join...on...=...`）

连表查询：通过一条查询就可拿出两个表中对应的数据，注意有一个匹配条件（on...=...）来判断筛选出查询出来的表中数据
注意：没有外键也可连表查询，但一般我们会采取外键来做匹配条件
语法：SELECT ... FROM ... INNER JOIN ... ON ...=... WHERE ...;
1. INNER JOIN 关键字在表中存在至少一个匹配时返回行。
2. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
3. where 条件是在临时表生成好后，再对临时表进行过滤的条件。
例子：
1. 通过 blogs表中的userid（users表中id的外键）与 users表中id 匹配 来 连表查询所有数据：这样就把两个表中的id都查出来了，显然不行，由于微博内容是我们查询的主体，所以我们查询的时候可以只在两个表中选自己需要的数据组成临时表
2. 匹配条件不变，只查需要的数据：
3. 在此基础上，还可通过where来进一步筛选查询内容：

sequelize

sequelize是一个ORM工具，他封装了很多API，通过sequelize我们可通过操作一般对象的方式去操作数据库
- Object Relational Mapping 即 对象关系映射（映射到数据库中），也就是通过对象的关系的形式映射到数据库中，即我们可通过操作一般对象的方式去操作数据库
sequelize不仅可以操作mysql，还可操作其他数据库
Sequelize返回的对象是Promise实例，也就是说从数据库中获取的数据是存在then()中的，此时使用async和await来获取数据库数据就很方便。而koa的处理函数都是async函数，所以我们实际上在koa的async函数中直接写await访问数据库就可以了
sequelize使用方法
注意：需要设置数据的字段长度可参考官方文档

sequelize与mysql

mysql	sequelize
数据表	用JS中的模型（class或对象）代替
一条或多条数据	用JS中一个对象或数组代替
sql语句	用对象方法代替

sequelize使用方法总结

方法	含义
	创建sequelize实例，连接数据库
`sequelize实例.define()`	创建模型（数据表）
`sequelize实例.sync()`	使用sequelize 将模型（表）同步到数据库
`模型名A.belongsTo(模型名B,{foreignKey: '外键名'})` `模型名A.hasMany(模型名B,{foreignKey: '外键名'})`	创建外键关联，连表查询时都是以模型名A为主，带出模型名B的数据
`模型名.create()`	往模型（数据表）中增加数据
1.查询一条数据：`模型名.findOne()` 2.查询一条数据中特定的列：`模型名.findOne()`的where属性 3.查询多条数据（组成一个列表）：`模型名.findAll()` 4.查询总数：`模型名.findAndCountAll()` 5.连表查询：使用`模型名.findAndCountAll()`的`include`属性 6.	查询数据
`模型名.update()`	修改数据
`模型名.destroy()`	删除数据

创建 sequelize实例：连接mysql数据库，使用sequelize库来new一个sequelize实例
创建模型（表）：使用sequelize实例.define()创建表并定义表中各列数据的名字、类型、长度、自动递增、主键、备注等
将模型（表）同步到数据库：使用sequelize实例.sync()进行同步。注意要引入创建的模型（表），否则都不知道同步谁
创建外键关联：使Blog模型中的userId成为User模型中id的外键，有2种方法，如果想要方便后期查询，则可同时使用2种方法
1. 方法1：Blog.belongsTo(User,{foreignKey: 'userId'})，使用该方法创建的外键，在连表查询时以blogs表为主，带出users表
2. 方法2：User.hasMany(Blog,{foreignKey: 'userId'})，使用该方法创建的外键，在连表查询时以users表为主，带出blogs表
插入数据：往User和Blog模型（users表和blogs表）中插入数据
- 比如User.create()
  - 参数：插入的数据对象
  - 返回值：Promise实例=》对象，所以建议使用await User.create()可直接得到返回值，其中包含的dataValues是插入的数据对象
查询数据：
1. 查询一条数据：模型名.findOne()
  - 参数：对象，该对象包含众多属性
    - where：对象，筛选行
    - attributes：数组，筛选列
  - 返回值：Promise对象=》对象，返回值的dataValues属性为对象，其中包含id等内容
2. 查询多条数据（组成一个列表）：**模型名.findAll()**
  - 参数：对象，该对象包含众多属性
    - where：对象，筛选行
    - order：数组（其中数组元素也是数组），每个数组元素都是排序规则（比如：order:[['id','desc']]就是根据id反向排序）
    - limit：数字，限制本次查询条数
    - offset：数字，跳过多少条再进行查询
  - 返回值：Promise对象=》数组，数组元素为数据对象。
    - 每个数据对象都有自己的dataValues，需要获取则通过需map()遍历数组。
3. 查询总数：模型名.findAndCountAll()
  - 参数：和findAll()的参数一样
  - 返回值：Promise对象=》对象，其中**返回值.count为所有数据的总数，返回值.rows则是分页的数组（多行数据）**
4. 连表查询：使用模型名.findAndCountAll()的**include属性**，属性值是对象数组，对象中的model属性决定了连的表
  - 返回值：Promise对象=》对象，其中**返回值.count为所有数据的总数，返回值.rows则是所以行数据组成的数组**，其中每个数据对象的dataValues中都包含一个连表查询出的数据对象，不过该对象也需要通过对象的dataValues来获取
修改数据：模型名.update()
- 参数1：对象，需要修改的数据
- 参数2：对象，where属性筛选需要修改的数据
- 返回值：Promise对象=》数组，表示被修改的行数，返回值[0]=0则修改失败，返回值[0]>0则修改成功。
删除数据：模型名.destroy()
- 参数：对象，筛选条件
- 返回值：Promise对象=》数字，表示被删除的行数（注意不是数组，是数字！）
- 如果被删除的数据中有在别的表中当外键，则必须保证delete中设置的是“层叠/级联”才可正常删除，严格模式下会报错。注意：使用sequelize创建的外键关系是严格模式，需要手动去sqlyog中将delete改为“层叠/级联”。

例子

我们现在使用sequelize来完成之前我们使用mysql与sqlyog共同完成的事情

创建连接

新建sequelize-test文件夹，初始化环境npm init -y
安装2个库：mysql2、sequelize：npm i mysql2 sequelize -d
- 在这使用mysql2库代替我们原本使用的mysql库，效率更高（mysql的使用方法和mysql2的使用方法大体上差不多）
- 我们将通过sequelize操作mysql2
创建sequelize实例用于连接mysql中的某个数据库：
测试是否连接成功：

创建模型&同步到数据库

src下新建model.js:暂时只创建User模型，同步到数据库看看是否成功
- Sequelize实例.define()创建表：
  - 参数1：表名（会自动变成复数后成为表名），比如设置user那么最后表名将是users
  - 参数2：对象，即表中各项数据的类型、是否允许为空等
    - 其中，id会自动创建，并设为主键、自增,不需我们手动创建
    - **Sequelize.STRING即数据库中varchar(255)**。数据库中varchar长度是可变的，也就是说虽然最长是255，但如果你只输入了2个字符，数据库也就会进行计算，最终将只占用2个字符的空间，所以直接使用sequelize默认的长度即可
    - **allowNull: false**即设置 不允许为空
    - comment：'备注内容'
删除原本我们在sqlyog中定义的users表和blogs表
src下新建sync.js将创建的模型同步到数据库：
1. 所有需要连接数据库的地方都要引入sequelize实例
2. 同步数据库就要引入需要同步的模型（表）
3. Sequelize实例.sync()同步数据库内容：
  - **参数：{ force: true }**即强制，如数据库中已有该表，则删掉重新创建一个新的
  - 注意：同步成功后需手动结束进程。即使用process.exit()退出程序，否则sequelize会一直占用进程
执行同步：
sqlyog中查看同步结果：

设置数据字段长度

需要设置数据的字段长度可参考官方文档，例子如下：

DataTypes.STRING             // VARCHAR(255)
DataTypes.STRING(1234)       // VARCHAR(1234)
DataTypes.STRING.BINARY      // VARCHAR BINARY
DataTypes.TEXT               // TEXT
DataTypes.TEXT('tiny')       // TINYTEXT

创建关联（外键）

回到src-model.js中创建Blog模型（blogs表）：
同步到数据库：
sqlyog中查看同步结果：
设置关联外键：在这我们同时采取2种方法设置外键，是为了搜索时2种主次关系都可行。
1. 方法1（推荐）Blog.belongsTo(User,{foreignKey: 'xxx'})：即将Blog模型（blogs表）中的xxx列作为User模型（users表）中id（默认为id）的外键
  - 使用该方法创建的外键，在连表查询时以blogs表为主，带出users表
  - 语义化：Blog属于User，这是多对一的关系，多个Blog是属于一个User的
2. **方法2（不推荐）Blog.belongsTo(User)**：使用该方法则我们自己定义Blog模型时就不需要创建外键，Sequelize会自动创建userId并关联到users表中的id上
3. **方法3（推荐）User.hasMany(Blog,{foreignKey: 'xxx'})**：与方法1创建的外键一致，只是连表查询是主次关系不同
  - 使用该方法创建的外键，在连表查询时以users表为主，带出blogs表
  - 语义化：User有很多Blog，这是一对多的关系，一个User可拥有多个Blog
同步到数据库
sqlyog中查看同步结果：（如果使用workbench则可同时看到外键的关系）
sqlyog中自动绘制ER图：可以看到多对一的关系：1个users.id可拥有多个blogs.userid

插入数据

src下新建create.js,创建表中数据：插入数据属于IO操作，在node中IO操作都是异步的，而异步最好使用async和await
- 语法：模型.create({需要插入的数据对象})
  - 异步插入的结果：对象，其中**dataValues对象为插入的数据**
查看创建的数据：
为避免重复插入数据，我们先删掉之前的数据，将所有数据写好后一起插入数据库：（或者可以先执行同步，再插入数据，因为同步后能表中内容为空）
!在这是逻辑非运算，可能是运算符优先级的原因？
sqlyog查看结果：

查询数据（1条/多条/分页/总数）

src下，新建select.js
查询一条数据：模型名.findOne()
查询一条数据中特定的列：attributes属性
查询多条数据（组成一个列表）：模型名.findAll()
- 注意：数组中每条数据都是一个对象，每个对象都有自己的dataValues，需要使用map()来遍历获取对象.dataValues
实现分页：limit和offset属性
**查询总数：模型名.findAndCountAll()**：
- 参数：和findAll()的参数一样
- 返回值：对象，其中**返回值.count为所有数据的总数，返回值.rows则是（各行）分页的数组**

连表查询（`include`属性）

方法1：前提：使用Blog.belongsTo(User,{foreignKey: 'userId'})建立的外键关系才可使用该方法进行连表查询
方法2：前提：使用User.hasMany(Blog,{foreignKey: 'userId'})建立的外键关系才可使用该方法进行连表查询

更新和删除

更新数据：src下新建update.js，更新数据采用模型名.update()：
- 参数1：对象，需要修改的数据
- 参数2：对象，where属性筛选需要修改的数据
- 返回值：Promise对象=》数组，表示被修改的行数，返回值[0]=0则修改失败，**返回值[0]>0则修改成功**：
删除数据：src下新建delete.js，删除数据采用模型名.destroy():
- 参数：对象，筛选条件
- 返回值：Promise对象=》数字，表示被删除的行数（注意不是数组，是数字！）
- 如果被删除的数据中有在别的表中当外键，则必须保证delete中设置的是“层叠/级联”才可正常删除，严格模式下会报错。注意：使用sequelize创建的外键关系是严格模式，需要手动去sqlyog中将delete改为“层叠/级联”：