🎇🎇🎇新年快乐🎇🎇🎇

2020 鼠你最帅，鼠你最强，鼠你最棒，鼠你最红，鼠你最美，鼠年吉祥

问与答

❓:你能学到什么？
🙋:sequelize-cli的使用以及sequelize的基础操作。

❓:为什么要使用sequelize-cli?
🙋:就像你使用Git/SVN来管理源代码的更改一样,你可以使用迁移来跟踪数据库的更改。

❓:为什么不将数据模型设计好再演示？
🙋:本文讲的是使用sequelize-cli和sequelize开发的过程。

❓:怎么会有这么多的代码？
🙋:每一步的代码我都贴了出来，只要按照流程做就能快速的完成一个示例。眼见为实，我相信这样学习的效果更好。

❓:怎么没有事务、作用域、数据类型等知识点？
🙋:这篇是入门教程，不过学会了这篇，事务、作用域理解起来更容易。

❓:为什么没有源代码？
🙋:做一遍一定比看一遍的效果好。

准备工作

1、初始化项目

 cd 工程目录 
 npm init -y

2、安装模块

 npm i koa koa-body koa-router mysql2 sequelize sequelize-cli -S

3、添加server.js文件

 const Koa = require('koa');
 const router = require('koa-router')();
 const koaBody = require('koa-body');
 const app = new Koa();
 app.use(koaBody());

 app.use(router.routes())
    .use(router.allowedMethods('*'));

 app.listen(3000, () => {
     console.log('server is listening on 3000...')
 });

快速入门

1、新建.sequelizerc文件

 const path = require('path');
 module.exports = {
   'config': path.resolve('config', 'config.json'), //数据库连接配置文件
   'models-path': path.resolve('db', 'models'),     //模型文件
   'seeders-path': path.resolve('db', 'seeders'),   //种子文件
   'migrations-path': path.resolve('db', 'migrations') //迁移文件
 }

2、初始化

 npx sequelize-cli init

3、编辑./db/config.js

 "development": {
    "username": "username",
    "password": "password",
    "database": "school", //数据库名称
    "host": "127.0.0.1",
    "dialect": "mysql",
    "timezone": "+08:00" //设置时区为'东八区'
  }

4、创建数据库

 npx sequelize-cli db:create

5、生成student模型文件以及迁移文件

 npx sequelize-cli model:generate --name student --attributes student_name:string,student_age:integer,student_sex:boolean

6、编辑./db/migrations/xxxxx-create-student.js

 'use strict';
 module.exports = {
   up: (queryInterface, Sequelize) => {
     return queryInterface.createTable('student', {
       id: {
         allowNull: false,
         autoIncrement: true,
         primaryKey: true,
         type: Sequelize.INTEGER
       },
       student_name: {
         type: Sequelize.STRING(10),
         allowNull:false 
       },
       student_age: {
         type: Sequelize.INTEGER,
         allowNull:false
       },
       student_sex: {
         type: Sequelize.BOOLEAN,
         allowNull:false
       }
     });
   },
   down: (queryInterface, Sequelize) => {
     return queryInterface.dropTable('student');
   }
};

打开xxxxx-create-student.你会发现createTable方法的第一个参数为students,这是由于sequelize会默认将表名称转换为复数形式，这里我将其修改为student，后面所有表名或模型名称都会使用单数形式。

7、生成名称为student的数据表

 npx sequelize-cli db:migrate

8、生成student表种子文件

 npx sequelize-cli seed:generate --name init-student

9、编辑./db/seeders/xxxxx-init-student.js文件

'use strict';

 module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('student', [{
      student_name: '孙悟空',
      student_age: 20,
      student_sex: 1
    },{
      student_name: '白骨精',
      student_age: 18,
      student_sex: 0
    },{
      student_name: '猪八戒',
      student_age: 16,
      student_sex: 1
    }])
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('student', null, {});
  }
};

10、student表初始化数据

  npx sequelize-cli db:seed:all

11、编辑.db/models/student.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const student = sequelize.define('student', {
    student_name: DataTypes.STRING,
    student_age: DataTypes.INTEGER,
    student_sex: DataTypes.BOOLEAN,
    class_id:DataTypes.INTEGER
  }, {
    timestamps: false,//不自动添加时间字段(updatedAt,createdAt)
    freezeTableName: true,// 使用模型名称的单数形式
    underscored: true //列名添加下划线
  });
  student.associate = function(models) {};
  return student;
};

12、编辑server.js文件

.....
 const Student  = require('./db/models').student;
 //添加学生信息
 router.post('/student', async ctx => {
     ctx.body = await Student.create(ctx.request.body);
 });
 //更新学生信息
 router.put('/student', async ctx => {
    const { id } = ctx.request.body;
    ctx.body = await Student.update(ctx.request.body, { where: { id } });
 });
 //获取学生列表
 router.get('/students', async ctx => {
    ctx.body = await Student.findAll();
 });
 //根据id删除学生信息
 router.delete('/student/:id', async ctx => {
    const { id } = ctx.params;
    ctx.body = await Student.destroy({ where: { id } });
});
.....

13、启动服务并使用Postman测试

 node server.js

模型关连

hasMany(一对多)

一个班级里面可以有多个学生，班级与学生的关系就是一对多。为了完成这个例子我们会做以下几件事情:

创建名称为_class的班级表
_class班级表初始化数据
student表添加列名为class_id的列
重新初始化student表数据
查询某个班级所有学生

让我们开始吧！

1、生成**_class**模型以及迁移文件

npx sequelize-cli model:generate --name _class --attributes class_name:string

2、修改./db/migrations/xxxxx-create-class.js

 'use strict';
 module.exports = {
   up: (queryInterface, Sequelize) => {
     return queryInterface.createTable('_class', {
       id: {
         allowNull: false,
         autoIncrement: true,
          primaryKey: true,
         type: Sequelize.INTEGER
       },
       class_name: {
         type: Sequelize.STRING(10),
          allowNull:false
       }
     });
   },
   down: (queryInterface, Sequelize) => {
     return queryInterface.dropTable('_class');
   }
 };

3、生成**_class**表

npx sequelize-cli db:migrate

4、生成**_class**表种子文件

 npx sequelize-cli seed:generate --name init-class

5、编辑./db/seeders/xxxxx-init-class.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('_class', [{
      class_name: '一班'
    }, {
      class_name: '二班'
    }, {
      class_name: '三班'
    }]);
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('_class', null, {});
  }
};

6、_class表初始化数据

  npx sequelize-cli db:seed  --seed  xxxxx-init-class.js

7、生成修改studnet表的迁移文件

npx sequelize-cli migration:generate  --name add-column-class_id-to-student.js

8、编辑./db/migrations/xxxxx-add-column-class_id-to-student.js

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn('student', 'class_id', {
      type: Sequelize.INTEGER,
      allowNull:false
    })
  },

  down: (queryInterface, Sequelize) => {
     queryInterface.removeColumn('student', 'class_id', {});
  }
};

9、修改student表

npx sequelize-cli db:migrate

10、重新生成student表种子文件

npx sequelize-cli seed:generate --name init-student-after-add-column-class_id

11、编辑./db/seeders/xxxxx-init-student-after-add-column-class_id.js文件

'use strict';

 module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('student', [{
      student_name: '孙悟空',
      student_age: 20,
      student_sex: 1,
      class_id: 1
    }, {
      student_name: '白骨精',
      student_age: 18,
      student_sex: 0,
      class_id: 1
    }, {
      student_name: '猪八戒',
      student_age: 16,
      student_sex: 1,
      class_id: 2
    }, {
      student_name: '唐僧',
      student_age: 22,
      student_sex: 1,
      class_id: 1
    }, {
      student_name: '沙和尚',
      student_age: 25,
      student_sex: 1,
      class_id: 1
    }, {
      student_name: '红孩儿',
      student_age: 13,
      student_sex: 1,
      class_id: 2
    }, {
      student_name: '黑熊怪',
      student_age: 26,
      student_sex: 1,
      class_id: 2
    }, {
      student_name: '太白金星',
      student_age: 66,
      student_sex: 1,
      class_id: 3
    }, {
      student_name: '嫦娥',
      student_age: 18,
      student_sex: 0,
      class_id: 3
    }])
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('student', null, {});
  }
};

12、撤销student表中已有的数据

npx sequelize-cli db:seed:undo --seed xxxxx-init-student.js

13、stuent表重新初始化数据

npx sequelize-cli db:seed --seed  xxxxx-init-student-after-add-column-class_id.js

14、编辑./db/models/_class.js文件

'use strict';
module.exports = (sequelize, DataTypes) => {
  const _class = sequelize.define('_class', {
    class_name: DataTypes.STRING
  }, {
    timestamps: false,
    freezeTableName: true,
    underscored: true
  });
  _class.associate = function (models) {
    _class.hasMany(models.student);
  };
  return _class;
};

15、编辑server.js

...
const Class = require('./db/models')._class;

//获取班级信息以及班级里的所有学生
router.get('/classes', async ctx => {
    //获取所有班级以及学生信息
     ctx.body = await Class.findAll({ include: [Student] });
});
...

belongsTo（一对一）

一个学生只能属于一个班级，所以学生和班级的关系是一对一。

1、修改./db/models/student.js文件

 ...
  student.associate = function(models) {
    student.belongsTo(models._class); //一对一
  };
 ...

2、修改server.js中获取学生列表的接口

...
//获取学生列表
router.get('/students', async ctx => {
    ctx.body = await Student.findAll({ include: [Class] });
});
...

belongsTo VS hasOne

student.belongsTo(models._class)这里student叫做源模型，_class叫做目标模型。

student表中包含了_class表的外键class_id,也就是说外键在源模型上面所以我们使用belongsTo来创建关联。

hasOne和belongsTo都是用来创建一对一关联的,正确使用它们的方法就是看外键在哪个模型中。

belongsTo关联外键在源模型上
hasOne关联外键在目标模型上

belongsToMany(多对多)

一个班级可以有多名代课老师,一名代课老师可以带多个班级的课程。班级与老师的关系是多对多 为了完成此功能的演示，我们将做以下工作:

创建名称

让我们开始吧！

1、生成teacher模型以及迁移文件

npx sequelize-cli model:generate --name teacher --attributes teacher_name:string

2、修改./db/migrations/xxxxx-teacher-class.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('teacher', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      teacher_name: {
        type: Sequelize.STRING(10),
        allowNull: false
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('teacher');
  }
};

3、生成teacher表

npx sequelize-cli db:migrate

4、生成teacher表种子文件

 npx sequelize-cli seed:generate --name init-teacher

5、编辑./db/seeders/xxxxx-init-teacher.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('teacher', [{
      teacher_name: '李老师'
    }, {
      teacher_name: '张老师'
    }]);
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('teacher', null, {});
  }
};

6、teacher表初始化数据

  npx sequelize-cli db:seed  --seed  xxxxx-init-teacher.js

7、生成teacher_class模型以及迁移文件

npx sequelize-cli model:generate --name teacher_class --attributes teacher_id:integer,class_id:integer

8、编辑./db/migrations/xxxxx-create-teacher-class.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('teacher_class', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      teacher_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      class_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('teacher_class');
  }
};

9、生成teacher_class表

npx sequelize-cli db:migrate

10、生成teacher_class表种子文件

 npx sequelize-cli seed:generate --name init-teacher_class

11、编辑./db/seeders/xxxxx-init-teacher_class.js

'use strict';

/* 李老师带的班级为一班和二班。张老师带的班级为三班 */
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('teacher_class', [{
      class_id: 1,
      teacher_id: 1
    }, {
      class_id: 2,
      teacher_id: 1
    }, {
      class_id: 3,
      teacher_id: 2
    }]);
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('teacher_class', null, {});
  }
};

12、teacher_class表初始化数据

  npx sequelize-cli db:seed  --seed  xxxxx-init-teacher_class.js

13、编辑./db/models/teacher.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const teacher = sequelize.define('teacher', {
    teacher_name: DataTypes.STRING
  }, {
    timestamps: false,
    freezeTableName: true,
    underscored: true
  });
  teacher.associate = function (models) {
    teacher.belongsToMany(models._class, {
      through: models.teacher_class,
      foreignKey: 'teacher_id',
    });
  };
  return teacher;
};

14、编辑./db/models/_class.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const _class = sequelize.define('_class', {
    class_name: DataTypes.STRING
  }, {
    timestamps: false,
    freezeTableName: true,
    underscored: true
  });
  _class.associate = function (models) {
    _class.hasMany(models.student);
    _class.belongsToMany(models.teacher, {
      through: models.teacher_class,
      foreignKey: 'class_id',
    });
  };
  return _class;
};

15、编辑server.js

const Teacher = require('./db/models').teacher;

//获取老师信息以及老师所带的班级
router.get('/teachers', async ctx => {
    //获取所有班级以及学生信息
     ctx.body = await Teacher.findAll({ include: [Class] });
})

查询

基础查询

1、返回指定列

 Student.findAll({
    attributes: ['id', 'student_name']
 });
 // select id,student_name from student

2、单条件查询

 Student.findAll({
    where: {
      id: 1
    }
 })
 // select * from student where id = 1

3、AND

 //返回id为1，姓名是`孙悟空`的学生信息
 Student.findAll({
        where: {
            id: 4,
            student_name:'孙悟空'
        }
    })
 // select * from student where id = 1 and student_name = '孙悟空'

4、OR

 //返回年龄等于12或者22的学生信息
 Student.findAll({
     where: {
            student_age: {
                [Op.or]: [12, 22]
            }
        }
 })
 // select * from student where studnet_age = 12 or studnet_age = 22

5、条件查询- >,>=,<,<=,=

 // 返回年龄大于等于20的学生
 Student.findAll({
     where: {
            student_age: {
                [Op.gte]: 20
            }
        }
 })
 // select * from student where studnet_age >= 20

 [Op.gt]: 6      //大于6
 [Op.gte]: 6     //大于等于6
 [Op.lt]: 10     //小于10
 [Op.lte]: 10    //小于等于10
 [Op.ne]: 20     //不等于20
 [Op.eq]: 3      //等于3

6、IN

 // 返回年龄是16和18的学生信息
 Student.findAll({
     where: {
        student_age: {
            [Op.in]: [16,18]
        }
    }
 })
 // select * from student where studnet_age in (16,18)

7、LIKE

  // 返回名称包含'孙'的学生信息
 Student.findAll({
     where: {
        student_name: {
           [Op.like]: '%孙%',
        }
    }
 })
 // select * from student where studnet_name like '%孙%'

聚合函数

1、获取学生的平均年龄

 Student.findAll({
    attributes: [[sequelize.fn('AVG', sequelize.col('student_age')), 'avg']]
 })

2、获取学生总数

  Student.findAll({
     attributes: [[sequelize.fn('COUNT', sequelize.col('id')), 'count']]
 })

嵌套查询

1、获取一班所有的学生并根据年龄降序排列

 Class.findAll({
    include: [{model: Student}],
    where:{id:1},
    order:[[Student,'student_age', 'DESC']]
    });

参考

sequelize中文文档