【Node】Mysql2和sequelize

mysql2

安装
npm i --save mysql2

创建连接
var mysql = require(‘mysql’);
//1.创建连接配置

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

//2.连接数据库（可以省略这步，通过直接查询数据库，进行隐式链接）

connection.connect();

//3.查询数据库

connection.query('SELECT 1 + 1 AS solution',(error, results, fields)=>{
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

//4.断开数据库连接

connection.end();

连接配置

常用
host 要连接的数据库的主机名。（默认值：localhost）
prot 要连接端口号，默认3306
localAddress TCP连接的源IP地址
user MySQL数据库连接用户名
password MySQL数据库连接密码
database 连接的数据库名称（可选）
connectTimeOut 连接MySQL超时时间
dataStrings 强制日期类型（TIMESTAMP，DATETIME，DATE）作为字符串返回，而不是膨胀到JavaScript Date对象。可以是true/false要保留为字符串的类型名称数组。（默认值:false）
multipleStatements 每个查询允许多个mysql语句。小心这一点，它可能会增加SQL注入攻击的范围。（默认值：false）

可以通过url形式来进行数据库连接配置的设置
var connection = mysql.createConnection(‘mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700’);

结束连接

//1.通过end方法进行关闭,可以传递回调函数

connection.end( (err) => {
    if(err) {
        console.log(err) 
    }else {
        console.log('数据库连接关闭')
    }
})

//2.通过destory方法进行关闭，不可以传递回调函数

connection.destory()

end 会确保排队状态的查询队列执行并返回完毕再结束连接。
destory 立即终止

查询

使用.query(sqlString , values , callback)函数进行查询
第一个参数是查询字符串，例如：
‘SELECT * FROM books WHERE author = ?’

第二个参数是查询字符串参数的值，它是一个数组，例如：
[‘David’]

‘SELECT * FROM books WHERE author = ?’ 中的 ? 的值

第三个参数是查询结束后的回调函数，包含查询数据，例如：

function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

完整query函数：

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

Sequelize

https://demopark.github.io/sequelize-docs-Zh-CN/
基于Promise的ORM(Object Relation Mapping)，支持多种数据库、事务、关联等

安装
npm install --save sequelize

安装对应数据库驱动
npm install --save mysql2

建立连接

(async () => {
const Sequelize = require("sequelize");
// 建立连接
const sequelize = new Sequelize("kaikeba", "root", "example", {
    host: "localhost",
    dialect: "mysql", //定义数据库类型
    operatorsAliases: false
});
// 定义模型
const Fruit = sequelize.define("Fruit", {
    name: { type: Sequelize.STRING(20), allowNull: false },
    price: { type: Sequelize.FLOAT, allowNull: false },
    stock: { type: Sequelize.INTEGER, defaultValue: 0 }
});
// 同步数据库，force: true则会删除已存在表 Fruit.sync({force: true})
let ret = await Fruit.sync()
console.log('sync',ret)
ret = await Fruit.create({
    name: "香蕉",
    price: 3.5
})
console.log('create',ret)
ret = await Fruit.findAll()
await Fruit.update(
    { price: 4 },
    { where: { name:'香蕉'} }
)
console.log('findAll',JSON.stringify(ret))
const Op = Sequelize.Op;
ret = await Fruit.findAll({
    // where: { price: { [Op.lt]:4 }, stock: { [Op.gte]: 100 } }
    where: { price: { [Op.lt]: 4, [Op.gt]: 2 } }
})
    console.log('findAll', JSON.stringify(ret, '', '\t'))
})()

避免自动生成时间戳字段
建立模型默认包括 id （主键、自增）、createdAt（创建时间）、updatedAt（更新时间）
需要取消的话，加 timestamps: false

const Fruit = sequelize.define("Fruit", {}, {
    timestamps: false
});

生成模型两种方法
1、使用define生成，本质上是在内部调用Model.init

const Fruit = sequelize.define("Fruit", {
    name: { type: Sequelize.STRING(20), allowNull: false },
    price: { type: Sequelize.FLOAT, allowNull: false },
    stock: { type: Sequelize.INTEGER, defaultValue: 0 }
});

2、使用Model生成，需要实现其init方法生成模型

const Model = sequlize.Model
class Fruit extends Model {
    Fruit.init({
        firstName:{
                type:Sequelize.STRING
        }
    },{
        sequlize,  //数据库的配置
        modelName:'Fruit'  //数据库模块名
    })
}

操作数据库
通过 Fruit.sync()方法来操作，方法返回的是Promise

  // 同步数据库，force: true则会删除已存在表
    sequelize.sync({ force: true }).then(async () => {
        await Team.create({ name: '火箭' });
        await Player.bulkCreate([{ name: '哈登', teamId: 1 }, { name: '保罗', teamId: 1 }]);

        // 1端关联查询  
        const players = await Player.findAll({ include: [Team] });
        console.log(JSON.stringify(players, null, 2));

        // N端关联查询
        const team = await Team.findOne({ where: { name: '火箭' }, include: [Player] });
        console.log(JSON.stringify(team, null, 2));
    });

查询

增 create
删 destory
改 update
查 find

// 查找所有用户

User.findAll().then(users => {
  console.log("All users:", JSON.stringify(users, null, 4));
});

// 创建新用户

User.create({ firstName: "Jane", lastName: "Doe" }).then(jane => {
  console.log("Jane's auto-generated ID:", jane.id);
});

// 删除所有名为“Jane”的人

User.destroy({
  where: {
    firstName: "Jane"
  }
}).then(() => {
  console.log("Done");
});

// 将所有没有姓氏的人改为“Doe”

User.update({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
}).then(() => {
  console.log("Done");
});

定义模型上的属性

class Foo extends Model {}
Foo.init({
 // 如果未赋值,则自动设置值为 TRUE
     flag: { 
         type: Sequelize.BOOLEAN,   //字段类型
         allowNull: false,  //允许为空 false 非空  true  可以为空
         defaultValue: true,  //默认值
         unique: 'compositeIndex',  //唯一键
         primaryKey: true, //设置主键
        autoIncrement: true ， //设置自增
        validate: {  //定义内容验证
            is: ["^[a-z]+$",'i'],     // 只允许字母
            is: /^[a-z]+$/i,          // 与上一个示例相同,使用了真正的正则表达式
            ……
        }
     }
})

Getters & setters
定义getter 和 setter，可以定义在属性上，也可以定义在模型上。
属性定义优先级高于模型定义

注意: 坚持使用 setDataValue() 和 getDataValue() 函数(而不是直接访问底层的“数据值”属性)是非常重要的 - 这样做可以保护你的定制getter和setter不受底层模型实现的变化.

class Employee extends Model {}
Employee.init({
    //定义在模型上
  get fullName() {
    return this.firstname + ' ' + this.lastname;
  }

  set fullName(value) {
    const names = value.split(' ');
    this.setDataValue('firstname', names.slice(0, -1).join(' '));
    this.setDataValue('lastname', names.slice(-1).join(' '));
  }
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    //定义到属性中
    get() {
      const title = this.getDataValue('title');
      // 'this' 允许你访问实例的属性
      return this.getDataValue('name') + ' (' + title + ')';
    },
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false,
    //定义到属性中
    set(val) {
      this.setDataValue('title', val.toUpperCase());
    }
  }
}, { sequelize, modelName: 'employee' });

Employee
  .create({ name: 'John Doe', title: 'senior engineer' })
  .then(employee => {
    console.log(employee.get('name')); // John Doe (SENIOR ENGINEER)
    console.log(employee.get('title')); // SENIOR ENGINEER
  })

删除表
// 创建表:
Project.sync()

// 强制创建!
Project.sync({force: true}) // 这将先丢弃表,然后重新创建它

// 删除表:
Project.drop()

搜索
find 查找特定元素

// 搜索已知的ids，查找主键为123的
Project.findByPk(123).then(project => {
  // project 将是 Project的一个实例,并具有在表中存为 id 123 条目的内容.
  // 如果没有定义这样的条目,你将获得null
})

// 搜索属性
Project.findOne({ where: {title: 'aProject'} }).then(project => {
  // project 将是 Projects 表中 title 为 'aProject'  的第一个条目 || null
})

findOrCreate 查找元素，如果没有则创建
返回查找到的数据，或者一个创建函数created

User
  .findOrCreate({where: 
  {username: 'sdepold'}, 
  defaults: {job: 'Technical Lead JavaScript'}
  }).then(([user, created]) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)
  }

findAndCount 在数据库中搜索多个元素,返回数据和总计数
返回值里面有两个属性：
rows是一个数组，返回查找到的元素
count是一个整数，返回查找到元素的总条数

Project
  .findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })
  .then(result => {
    console.log(result.count);
    console.log(result.rows);
  });

findAll 返回所有符合条件的元素

Project.findAll({group: 'name'})

max 返回最大值

Project.max('age').then(max => {
  // 将返回 40
})

min 返回最小值

Project.min('age').then(max => {
  // 将返回 40
})

count 统计条数

Project.count().then(c => {
  console.log("There are " + c + " projects!")
})

sum 统计特定属性值求和

Project.sum('age').then(sum => {
  // 将返回 55
})