Nodejs + sequelize to realize the operation of adding deleting modifying and checking

1. Download the repository

npm install sequelize --save

npm install mysql2--save//npm install mysql prompt incomplete

2. Create the database configuration file db. js to configure the database

var Sequelize = require('sequelize'); 
module.exports = new Sequelize('blog', 'root', '123456', {
  host: 'localhost', //  Database address 
  dialect: 'mysql', //  Specify the database type of the connection 
  operatorsAliases: false,
  pool: {
    max: 5, //  Maximum number of connections in connection pool 
    min: 0, //  Minimum number of connections in connection pool 
    idle: 10000 //  If 1 Threads  10  If it has not been used within seconds, then the thread is released 
  }
});

3. Create an model file user. js

var Sequelize = require('sequelize');
var sequelize = require('./db');

//  Create  model
var User = sequelize.define('user', {
  id : {type : Sequelize.INTEGER, autoIncrement : true, primaryKey : true, unique : true},
  userName: {
    type: Sequelize.STRING, //  Specifies the type of value 
    field: 'user_name' //  Specify the name of the key stored in the table 
  },
  //  Not specified  field The key name in the table is the same as the object key name, which is  email
  email: {
    type: Sequelize.STRING
  }
}, {
  //  If is  true  The name of the table and the  model  The same, that is  user
  //  For  false MySQL The table name created will be plural  users
  //  If the specified table name is originally plural, it will not change 
  freezeTableName: true
});

/*User.sync({force:false}).then(function(){
  console.log("success to start");
}).catch(function(err){
  console.log("failed to start ")
})*/
//  Create a table 
// User.sync()  The table is created and returns 1 A Promise Object 
//  If  force = true  The existing table (if the users Table already exists) Destroy before creating the table 
//  By default  forse = false
//var user = User.sync({ force: false });

//  Add a new user 
exports.addUser = function(userName, email) {
  //  Toward  user  Insert data into a table 
  return User.create({
    userName: userName,
    email: email
  }).then(function(result){
    console.log(" Insert operation successful "+result);
  }).catch(function(err){
    console.log(" Error adding data: "+err)
  });
};

exports.findByName = function(userName) {
  return User.findOne({where: {user_name:userName
    }}).then(function(result){
       console.log(" Success: " + result.id);
    }).catch(function(err){
      console.log(" An error occurred: " + err);
    });
};

//  Find a user by user name 
 
exports.update = function(id){
 return User.findOne({where: {id:id
    }}).then(function(user){
      
      return user.update({
          email:'jack3@qq.com'
        }).then(function(result){
          console.log("update success: "+result);
        }).catch(function(err){
          console.log(" Error in update operation: "+err);
        }); 

    });
 
};
exports.destroy = function(id){
  return User.destroy({where:{id:id}}).then(function(result){
    console.log("delete success");
  }).catch(function(err){
    console.log("delete data err: "+err);
  })
}

4. Test files

var user = require('./user');
// Query operation 
//user.findByName("jack");
//  Add User 
//user.addUser('jack2', 'jack@163.com');
//  Update 
//user.update(1001);
// Delete 
//user.destroy(1001);

Additional knowledge: nodejs Sequelize simple query statement and several common query commands of mysql

I am a front-end, but there is always a need to do back-end work, so I am familiar with the following simple query statements by the way

Post it. If you need it, you can refer to it. The remarks are very detailed, so you won't explain it much.

Don't talk nonsense. Stick the code:

# Removal unionid  Duplicate search results 
#query_resultsign  Table name 
select *, count(unionid) from query_resultsign where issign='false' group by unionid ;
 
# Removal unionid  Duplicate search results 
#query_resultsign  Table name 
select *, count(unionid) from query_resultsign where issign='true' group by unionid ;
 
# Calculate the average access frequency of unsigned users ( Average the search result columns issign='false'  Not signed )
#cuid  Yes unid Alias of 
#query_resultsign  Table name 
select AVG(bs.cuid) as unUserAvg FROM (select *, count(unionid) cuid from query_resultsign where issign='false' group by unionid ) as bs;
 
# Average 
#( That is, to find the search results issign='true' count Average value of )
#bs Is the alias of a subquery, and an error will be reported without an alias 
#query_resultsign  Table name 
select AVG(bs.cuid) userAvg FROM (select *, count(unionid) cuid from query_resultsign where issign='true' group by unionid ) as bs;
 
# Increase id  Column  int 
#query_resultsign
ALTER TABLE query_resultsign add id int;
 
# Make a table  query_resultsign ( Upper 1 Step ) Added columns become self-added columns 
alter table query_resultsign change id id int NOT NULL AUTO_INCREMENT primary key;
 
 # Get the columns with the same data in two columns of data 
 #query_resultsign  Table name 
select  p1.*  from  query_resultsign  p1,query_resultsign  p2  where  p1.id<>p2.id
 and  p1.x  =  p2.x 
 and  p1.y  =  p2.y ;
 
 # Lookup table query_resultsign unionid  Same user 
 select  p1.*  from  query_resultsign  p1,query_resultsign  p2  where  p1.id<>p2.id
 and  p1.unionid  =  p2.unionid ;

sequelize call sql statement method incidentally mentioned 1, most of the online tutorials are queried with model, each time to establish model. It's a little troublesome. Please refer to the configuration tutorial for the configuration tutorial.

sequelize calls sql primarily with the query (sql, {}) method:

var Sequelize = require('sequelize');// Introduce sequelize 
var sequelize = require('./../../database/dataconfig'); // Introducing a connection profile 
 
// Find subscribers 
exports.selectHeatData = function (req, res) {
  return sequelize.query("select * from `query_resultSign` where issign ='true'", { type: sequelize.QueryTypes.SELECT }).then(data => {
    // console.log('******', data);
    res.send(data);
  }).catch(err => {
    console.log(' Errors ', err)
  })
}
// The other way is to change it sql Statement 

The main knowledge point is the type {type: sequelize. QueryTypes. SELECT} of the query result passed in the query method so that you don't have to manually convert it to an json object.

Attached configuration file code

dataconfig.js

var Sequelize = require('sequelize'); 
module.exports = new Sequelize('pingan_scame', 'root', '123456', {
  host: 'localhost', //  Database address 
  dialect: 'mysql', //  Specify the database type of the connection 
  operatorsAliases: false,
  pool: {
    max: 5, //  Maximum number of connections in connection pool 
    min: 0, //  Minimum number of connections in connection pool 
    idle: 10000 //  If 1 Threads  10  If it has not been used within seconds, then the thread is released 
  }
 
});