Persistent data store - mysql - Moment For Technology

The difference between normal file and database storage

In most enterprise or Web development, social data is stored and retrieved, and there are two basic ways to store data

File System
The Database (Database)

File storage common, simple, the operation system to provide the perfect API, so early in the project will be using the file storage carrier, more and more complex, but follow the business website traffic is becoming more and more big, the data of the concurrency and the retrieval speed have higher requirements, so slowly is introduced using the database as the data storage

File systems store data in files, not easily shared. / Database Systems store data in databases
File system programs (code) and data have some connection/database system programs and data separation
File systems have no concept of locking and cannot be encrypted locking/database system data security

Database classification

Non-relational database – Relationships between JSON document objects
- mongodb
Relational databases – Relationships between tables and tables
- MySQL

Relatively speaking, Node.js is more suitable for operating non-relational databases, more use of mongodb, access to small and medium-sized data

Node.js can also operate MySQL databases, which are better suited to storing large databases than mongodb

MySQL installation

Qunhui NAS installation

[jianshu.com] [jianshu.com]

Open docker, search mysql, select the version and download the image
In File Station → docker folder, create a new mysql folder, internal new folder named database
Start image, name database name, advanced Settings →

Storage space → Add folder, add just created directory

Port Settings → Keep consistent with container ports. If ports conflict, change to other ports

Added MYSQL_ROOT_PASSWORD password to the environment
Apply → Next → Finish. Check the container. The database is already running

Windows installation

MySQL :: Download MySQL Community Server (Archived Versions)

MySql installation and basic management

Skip…

SQL statement operation

Open container → terminal → New → bash enter mysqld

Accessing the database

Mysql -u root -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p -p

-u is user, root is the root user, and -p is the password

Once you have logged in, you can enter the mysql interpreter and do the corresponding work here

Basic mysql statements cannot lose semicolons at the end of statements

Check the number of databases on the local machine

Creating a new database

Db1 is the database name, charset UTf8 is set to UTF-8 encoding format

Open the corresponding database

Create a table

The id of the created table is int, and the name is vARCHar and contains a maximum of 20 characters

Check how many tables there are in the current database

View current table

The current table inserts a data entry

Insert values 1 and XXX at id and name of S1

View data in the current table

Insert multiple data

Select * from s1

Querying a specified ID

Navicat visualization tool use

Download Navicat visual database management development

Click connect in the upper left corner → Input Nas IP and port number, click Test connection, indicating successful connection, ok

Right-click the connection name to create a new database

Double-click database to open, right-click table, create a new table

Click Add Field to add the header
If this parameter is not null, it cannot be null
Set key, the value here is unique
Default Specifies the default value when the value is blank
Automatically increments. When no ID is specified, the value automatically increments
There can only be one ID in a table

CTRL + S save → Enter the table name to save

Open the lower left corner of the saved table to add a data, CTRL + S save this data, id is set to automatic increment, do not fill in ID only fill in name save, ID will automatically +1

Click query → Create query → enter mysql statement, click on the right side to run, you will get the result, # comment

Node.js native driver links to MySQL

Creating a Server

Nodejs listens for mysql port – mysql usage in nodeJS

Initialize the project and install mysql module

npm init --yes
npm i mysql -s
Copy the code

Create server.js to import mysql

// Import modules
const mysql = require('mysql')

// Create a connection object
const conn = mysql.createConnection({
  host: '192.168.31.107'.port: '3307'.user: 'root'.password: '601109'.database: 'db1'
})

// Call connect to connect to the database
conn.connect(err= > {
  if(err) throw err;
  console.log('Connection successful');
})

// add/delete/check ------------------------------------------


// There must be this disconnection operation, otherwise it will consume excess performance
conn.end()
Copy the code

Run the nodemon Server.js server

Writing a command line

SQL statements can be written in Navicat tests, new query input command line, click run, test statements

When this statement is finished, F5 in the table is refreshed and you can see the newly created table

And add, query, delete statements to the table

Next in node.js native driver, add, delete, change and check the database

Create a table

// add/delete/check ------------------------------------------

// Declare a statement
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`
// The first argument is the SQL statement, and the second argument is the callback function
conn.query(CREATE_SQL,(error,results,fields) = > {
  if(error) throw error
  console.log(results)
})
Copy the code

After running the command, print results. The result is as follows

OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 1,
  message: ' ',
  protocol41: true,
  changedRows: 0
}
Copy the code

Go back to the visualization tool refresh and the new table is created

Add one or more data to a table after it is created

The front-end calls the back-end interface, and the back-end passes in the request body data to get the service area data. Represents a placeholder

If you need to pass in the request body data, you can receive an array after the SQL statement of the Query function

// add/delete/check ------------------------------------------
// Declare a statement
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`
const INSERT_SQL = `INSERT INTO test(name) VALUE(?) , (?) `
// The first argument is the SQL statement, and the second argument is the callback function
conn.query(CREATE_SQL,(error,results,fields) = > {
  if(error) throw error
  conn.query(INSERT_SQL,['Max'.'Min'].(error,results,fields) = > {
    if(error) throw error
    console.log(results)
    // When using query nesting, the end() method is placed inside the last query
    conn.end()
  })
})
Copy the code

After running the command, print results. The result is as follows

OkPacket {
  fieldCount: 0./ / number field
  affectedRows: 1.// Affected row 1, the first row in the table
  insertId: 5.// Insert id
  serverStatus: 2.// Server status
  warningCount: 0.// Warning count
  message: ' '.protocol41: true.41 / / agreement...
  changedRows: 0 // Change the row
}
  affectedRows: 2.insertId: 6.serverStatus: 2.warningCount: 0.message: '&Records: 2 Duplicates: 0 Warnings: 0'.// Record: 2 Repeat: 0 Warning: 0
  protocol41: true.changedRows: 0
}
Copy the code

Query data

const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`
const INSERT_SQL = `INSERT INTO test(name) VALUE(?) , (?) `
const SELECT_SQL = `SELECT * FROM test WHERE id=? `
conn.query(CREATE_SQL,(error,results,fields) = > {
  if(error) throw error
  conn.query(INSERT_SQL,['Max'.'Min'].(error,results,fields) = > {
    if(error) throw error
    // Continue to nest query statements
    conn.query(SELECT_SQL,[6].(error,results,fields) = > {
      if(error) throw error
      // Add [0] because the retrieved data is an array and there is only one retrieved data
      console.log(results[0]);
      conn.end()
    })
  })
})
Copy the code

Print the result

RowDataPacket { id: 6, name: 'Max' }
Copy the code

If you want to return a result of name, results[0].name

Delete the data

const DELETE_SQL = `DELETE FROM test WHERE id=? `

conn.query(DELETE_SQL,[9].(error,results) = > {
  if(error) throw error
  console.log(results);
  conn.end()
})
Copy the code

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: ' ',
  protocol41: true,
  changedRows: 0
}
Copy the code

Encapsulate mysql’s Query method for easy operation

Multiple nested query operations on the same table can easily create callback hell

Callback hell: An operation designed to execute callback code sequentially, which makes the code very unreadable and difficult to maintain

We don’t want the project’s configuration objects, method operations, and business code to have very high correlation and need to be decoupled, separating mysql configuration files and operation functions from server.js

Promise method

With the Promise object, asynchronous operations can be expressed as a flow of synchronous operations, avoiding layers of nested callback functions. In addition, the Promise object provides a unified interface that makes it easier to control asynchronous operations, and the Promise constructor takes as an argument a callback function that takes two arguments, the Promise’s resolve(success) rejecte(failure) function

Const SQL = mysql.format() format() is a format method for mysql that takes two parameters. The first is the SQL statement that expects the parameters, and the second is the parameter that needs to be passed, and returns an SQL statement that contains the parameters

const UPDATE_SQL = 'UPDATE posts SET modified = ? WHERE ID = ? '
const params = [CURRNT_TIMESTAMP, 42]
const sql = mysql.format(UPDATE_SQL, params)
console.log(sql) // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42
Copy the code

mysqlConfig.js

Module. exports = {host: '192.168.31.107', port: '3307', user: 'root', password: '601109', database: 'db1'}Copy the code

The mysql.js database function passes in the database configuration

Create a connection object representing a connection to the mysql database server using the createConnection method

// Import the mysql module
const mysql = require('mysql')
// Import database configuration
const mysqlConfig = require('./mysqlConfig')

module.exports = {
  // Declare a query method that throws an object and receives statements and arguments waiting to be inserted
  query: function (sql, params) {
    // Call query and return to await with a Promise object
    return new Promise((resolve, reject) = > {
      // Create a connection object
      const conn = mysql.createConnection(mysqlConfig)
      conn.connect(err= > {
        console.log('Connection successful');
      })
      // format() takes the incoming SQL statement and the array of SQL parameters, formats it, and returns a complete SQL statement
      sql = mysql.format(sql,params)
      / / query
      conn.query(sql,(err,results,fields) = > {
        // If there is an error, return a reject error for the Promise object
        if(err) throw reject(err)
        // If the connection succeeds and the SQL statement query completes, assign the data returned by the database to the Promise object
        resolve(results)
        // Stop the connection to the database after the query statement
        conn.end()
      })
    })
  }
}
Copy the code

Server.js business code declaration statement to introduce prior database functions

Declare the function async, call the database function inside the try and pass in the SQL statement, await the Promise object returned by the asynchronous function, and get the return value RES

// Declare a statement
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`

const db = require('./db/mysql')

async function asyncQuery() {
  try {
    const res = await db.query(CREATE_SQL); // Create table test
    console.log(res);
  } catch (err) {
    console.log(err)
  }
}

asyncQuery() // the function is declared above and called below
Copy the code

The result is returned and runs normally without any problems

OkPacket {fieldCount: 0, affectedRows: 0, insertId: 0, serverStatus: 2, warningCount: 0, message:' ',
  protocol41: true,
  changedRows: 0
}
Copy the code

And then step by step call

const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(id INT NOT NULL PRIMARY KEY auto_increment,name VARCHAR(20))`
const INSERT_SQL = `INSERT INTO test(name) VALUE(?) , (?) `

const db = require('./db/mysql')

async function asyncQuery() {
  try {
    const res1 = await db.query(CREATE_SQL); // Create table test
    const res2 = await db.query(INSERT_SQL,['Max'.'Min']) // Add data whose name is Max and Min
    console.log(res2);
  } catch (err) {
    console.log(err)
  }
}

asyncQuery()
Copy the code

Get the results

Connection successful OkPacket {fieldCount: 0.affectedRows: 2.insertId: 1.serverStatus: 2.warningCount: 0.message: '&Records: 2 Duplicates: 0 Warnings: 0'.protocol41: true.changedRows: 0
}
Copy the code

Query operation

const SELECT_SQL = `SELECT * FROM test WHERE id=? `

const db = require('./db/mysql')

async function asyncQuery() {
  try {
    const res3 = await db.query(SELECT_SQL,[1]) // Query the data whose id is 1
    console.log(res3);
  } catch (err) {
    console.log(err)
  }
}

asyncQuery()
Copy the code

Print the result

[ RowDataPacket { id: 1, name: 'Max'}]Copy the code

Delete operation

const DELETE_SQL = `DELETE FROM test WHERE id=? `

const db = require('./db/mysql')

async function asyncQuery() {
  try {
    const res4 = await db.query(DELETE_SQL,[1]) // Delete data 1
    console.log(res4);
  } catch (err) {
    console.log(err)
  }
}

asyncQuery()
Copy the code

Print the result

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: ' ',
  protocol41: true,
  changedRows: 0
}
Copy the code

The connection pool

Connection pooling is an important concept when developing Web applications. The performance cost of establishing a database connection is high. In a server application, establishing one or more database connections for every client request received can seriously degrade application performance.

Therefore, a connection pool is typically created and maintained for multiple database connections in a server application, and when connections are no longer needed, they can be cached in the pool and reused when the next client request is received, without having to re-establish the connection.

// Import the mysql module
const mysql = require("mysql");
// Import database configuration

const pool = mysql.createPool({
  host: "192.168.31.107".port: "3307".user: "root".password: "601109".database: "db1",})module.exports = {
  query: function (sql, params) {
    return new Promise((resolve, reject) = > {
      pool.getConnection((err, connection) = > {
        if (err) {
          if (err.errno == "ECONNREFUSED") {
            console.log("Database connection failed, please confirm whether your mysql service has been started")}else{ reject(err); }}else {
          sql = mysql.format(sql, params);

          connection.query(sql, (err, results, fields) = > {
            if (err) {
              if (err.errno == 1055) {
                console.log("Database query failed, please check mysql configuration")}else {
                reject(err)
              }
            } else {
              resolve(results)
            }
            connection.release()
          })
        }
      })
    })
  }
}
Copy the code

Connection pool problem

In the mysql module, the createPool method is used to create a connection pool. After creating a connection pool, you can directly use the getConnection method of the connection pool object to obtain a connection from the pool. If no connection is available in the pool, an implicit database connection is established

1.connection.release() When a connection is not needed, use this method to return it to the connection pool

2. Connection.destroy () This method is used when a connection is not needed and needs to be removed from the connection pool

3.pool.end() This method is used to disable a connection pool when it is no longer needed

Note: You can use the connectionLimit attribute of the createPool method to set the maximum number of connections in the connection pool. The default is 10

The link pool method above handles the error message twice, so it is a bit too much code, and it is easy to remove the error message

module.exports = {
  // Declare a query method that throws an object and waits for incoming statements and arguments
  query: function (sql, params) {
    // Return a Promise object
    return new Promise((resolve, reject) = > {
      // Connect to the database. If successful, a database connection object is returned
      pool.getConnection((err, connection) = > {
        if (err) {
          console.loog(err)
        } else {
          // If the connection succeeds, format is called to format the SQL statement
          sql = mysql.format(sql, params);
          // The database connection object passes in a full SQL statement and receives an error or result via a callback function
          connection.query(sql, (err, results, fields) = > {
            if (err) {
               reject(err)
            } else {
              // If the result is returned, the result is returned to the Promise object via resolve
              resolve(results)
            }
            connection.release()
          })
        }
      })
    })
  }
}
Copy the code

Packaging ideas

For the method on the current framework, the repeated call operation is wrapped and called as async await method. The code that needs to be repeatedly used is wrapped as a module reference, and a simple one-line code is called to complete the operation

The ORM framework Sequelize is an introduction to its data types

Object Relational Mapping (ORM) is the automatic persistence of objects in an object-oriented language program into a Relational database using metadata that describes the Mapping of objects and data to date

An overview of the

Sequelize.js is a Promise based ORM framework for Node.js. It features a wide range of configuration and query methods and supports relational databases. PostgreSQL, MySQL, MariaDB, SQLite and MSSQL

Why did you choose it?

Node.js connection to the database is certainly no stranger to the database, if it is a direct connection, you need to establish and manage the connection, but also need to manually write SQL statements, simple projects don’t matter, but a project design is more complex, more tables when the whole SQL writing is very energy consumption

There are already lightweight database frameworks or solutions in languages like Java and c#, and sequellize.js is recommended for node.js, which is a mature framework with advantages in speed and performance. Daily development only needs the creation of managed objects, query methods can be called, very little need to write SQL statements, this advantage eliminates the complex SQL statement maintenance, but also avoid unnecessary bugs caused by SQL

The installation

Sequelize Chinese website

npm init --yes
npm i sequelize mysql2 -s
Copy the code

Configure and define the table model

server.js

// Import modules
const Sequelize = require("sequelize");

// Establish a connection and return the Sequelize object
const sequelize = new Sequelize("db1"."root"."123456", {
  host: "192.168.31.107".port: "3306".dialect: "mysql".pool: {
    max: 5.min: 0.acquire: 30000.idle: 10000}});// The definition model returns a table instance
const books = sequelize.define(
  "books".// The first argument accepts a string that defines the table name
  // The second argument is an object that defines the fields in the table and attributes in the object
  {
    id: {
      type: Sequelize.INTEGER, // Define the data type as an integer
      primaryKey: true.// Set the primary key
      autoIncrement: true.// Auto-increment
      comment: "On the id"./ / comment
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false.// Cannot be null Default is true
    },
    price: {
      type: Sequelize.FLOAT,
      allowNull: false,},count: {
      type: Sequelize.INTEGER,
      defaultValue: 0,}}, {// Related configuration
    timestamps: false.// Specifies whether to create createAt and updatedAt fields. Default is true
    freezTabelName: true // Freeze table name, Model corresponding table name is the same as Model name, default is true})// Synchronize the model with the database
// If you want Sequelize to automatically create tables from the model you just defined (or modify them as needed), you can use sync
// If there is no books table, create table; if there is, delete table and rebuild table
books.sync({force: true}).then(() = >{})Copy the code

The terminal displays a warning message (Node :12324) [SEQUELIZE0006] Deprecation warning: This database engine version is not supported. Please update your database server. Below is the SQL statement being executed

The database table is refreshed and the table is created

You can see that createdAt and updatedAt are not the fields defined above. If you don’t need them, you can configure the third parameter to define the model, timestamps: false

Sequelize Data type

Sequelize.STRING                      // VARCHAR(255) Type: The maximum value is 65535 characters
Sequelize.STRING(1234)                // VARCHAR(1234) Type: variable length Maximum value: 65535 characters
Sequelize.TEXT                        // TEXT Type: The maximum value is 65535 characters
Sequelize.TEXT('tiny')                // TINYTEXT Type: String Maximum value: 255 characters

Sequelize.INTEGER                     // INTEGER Type: INTEGER Maximum value: range (-2147483648 to 2147483647)
Sequelize.BIGINT                      // BIGINT                        类型：整型 最大值：范围(+-9.22*10的18次方)
Sequelize.BIGINT(11)                  // BIGINT(11) Type: integer Maximum value: range (+-9.22*10 ^ 18)

Sequelize.FLOAT                       // FLOAT type: single precision FLOAT type 8 bit precision (4 bytes)
Sequelize.FLOAT(11)                   // FLOAT(11) type: single precision FLOAT type 8 bit precision (4 bytes)
Sequelize.FLOAT(11.12)               // FLOAT(11,12) type: precision floating point type 8-bit precision (4 bytes) m total number of decimal, d


Sequelize.DOUBLE                      // DOUBLE type: 16-bit precision (8 bytes)
Sequelize.DOUBLE(11)                  // DOUBLE(11) type: 16 bit precision (8 bytes)
Sequelize.DOUBLE(11.12)              // DOUBLE(11,12) type: DOUBLE floating-point 16-bit precision (8 bytes) m total, d decimal

Sequelize.DECIMAL                     // DECIMAL type: fixed-point number type
Sequelize.DECIMAL(10.2)              // DECIMAL(10,2) type: the fixed-point type argument m<65 is a total number, d<30, and d

Sequelize.DATE                        // DATETIME type: DATETIME type example: '2009-05-12 02:31:44'
Sequelize.DATE(6)                     // DATETIME(6)    
Sequelize.DATEONLY                    // DATE without time.
Sequelize.BOOLEAN                     // TINYINT(1) Type: integer range (-128 to 127)

Sequelize.ENUM('value 1'.'value 2')  // ENUM Type: enumeration

Sequelize.BLOB                        BLOB type: binary data
Sequelize.BLOB('tiny')                // TINYBLOB: binary data
Copy the code

Insert data

Call the sync method on the defined Books model to create the table (or modify the table as needed), and then processes the data that needs to be added

Add this data by passing an object through books.create({})

**force: true: ** If books exists, delete from books table and write to books table

books.sync({force: true}).then(() = > {
  // Sequelize is a promise-based wrapper that returns a Promise, processed by then
  return books.create({
    name: 'javascript'.price: 12.9.count: 10})})Copy the code

Refresh the database, and the data is inserted

Insert multiple data

Insert a piece of data on top with create({})

BulkCreae ([{}, {}]) is used to insert multiple data.

books.sync({ force: true }).then(() = > {
  return books.bulkCreate([
    {
      name: "Vue3".price: 20.8.count: 18}, {name: "React".price: 34.8.count: 30}, {name: "Node.js".price: 28.count: 45,}]); });Copy the code

Refresh the database, and you’ll see that javascript doesn’t exist because of force: true, and the table is overwritten, so don’t write this unless necessary

The query

Model queries (base) | Sequelize Chinese website

Query all data

Returns the entire table data

The call to findAll queries the data and returns a JS array object, as shown below, requiring a call to json.stringify to convert it to a JSON string

books.findAll().then(books= > {
  console.log(JSON.stringify(books));
})
Copy the code

The server prints a Executing line and the SQL statement is executed

The requested JSON data is returned below

You can set null, 4 to format the data for easy viewing

JSON.stringify(books,null.4)
Copy the code

[{"id": 1."name": "Vue3"."price": 20.8."count": 18
    },
    {
        "id": 2."name": "React"."price": 34.8."count": 30
    },
    {
        "id": 3."name": "Node.js"."price": 28."count": 45}]Copy the code

You can also get the count value of the first object, which is 18, from books[0].count

Incoming query criteria

Pass in a query condition with a limit count of 30

books.findAll({
  where: {count: 30
  }
}).then(books= > {
  console.log(JSON.stringify(books, null.4));
})
Copy the code

Print the result

Modify the

You can also change the value in the database by assigning, printing the result as 100

books.findAll().then(books= > {
  books[0].count = 100
  console.log(books[0].count); / / 100
})
Copy the code

To calculate

Instance extension

If the front end passes 50 books to calculate the total price, but you do that every time, if you have a complicated calculation, it’s very inconvenient

books.findAll().then(books= > {
  const totalPrice = books[0].price * 50
})
Copy the code

You can extend the instance with a method to compute

books.prototype.totalPrice = function(count) {
  return this.price * count
}

books.findAll().then(books= > {
  const totalPrice = books[0].totalPrice(50)
  console.log(totalPrice); / / 1040
})
Copy the code

Model extension

books.classify = function(name) {
  const frontEnd = ['Vue3'.'React']
  // Determine if the name passed in is included in the frountEnd array by calling includes(). If so, return to the front-end framework
  return frontEnd.includes(name) ? 'Front frame':'other'
}
// Data from the front end to check whether these two are front-end frameworks
const arr = ['Vue3'.'Node.js']

// Call forEach on the array passed from the front end, pass each value separately, pass each value to model's classify method for judgment and return the result
arr.forEach(name= > {
  console.log(books.classify(name));
})
Copy the code

Print the result

Front-end framework OthersCopy the code

Query a piece of data

Return table 1

FindOne () returns a single piece of data by running the following code

books.findOne().then(book= > {
  const res = JSON.stringify(book,null.4)
  console.log(res);
})
Copy the code

{
    "id": 1,
    "name": "Vue3"."price": 20.8."count"18} :Copy the code

You can see that this is the first data in the entire table

Incoming query criteria

If you need to add a condition, pass an object in the method to qualify it

books.findOne({
  where: {id: 3
  }
}).then(book= > {
  const res = JSON.stringify(book,null.4)
  console.log(res);
})
Copy the code

Query condition operator

const Op = Sequelize.Op

[Op.and]: {a: 5}           // and (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 or a = 6)
[Op.gt]: 6.// id > 6
[Op.gte]: 6.// id >= 6
[Op.lt]: 10.// id < 10
[Op.lte]: 10.// id <= 10
[Op.ne]: 20.// id ! = 20
[Op.eq]: 3./ / = 3
[Op.not]: true./ / it isn't TRUE
[Op.between]: [6.10].// Between 6 and 10
[Op.notBetween]: [11.15].// Not between 11 and 15
[Op.in]: [1.2].// in [1, 2]
[Op.notIn]: [1.2].// Not in [1, 2]
[Op.like]: '%hat'./ / contains' % hat '
[Op.notLike]: '%hat'       // does not contain '%hat'
[Op.iLike]: '%hat'         // contains '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'      // does not contain '%hat' (PG only)
[Op.regexp]: '^[h|a|t]'    / / match the regular expression / ~ '^ t] [h | a |' (only MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // Does not match the regular expression /! ~ ' '^ t] [h | a |' (only MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    / / ~ * '^ t] [h | a |' (PG) only
[Op.notIRegexp]: '^[h|a|t]' / /! ~ * '^ t] [h | a |' (PG) only
[Op.like]: { [Op.any]: ['cat'.'hat']} // include any array ['cat', 'hat'] - also applies to iLike and notLike
[Op.overlap]: [1.2]       // && [1, 2] (PG array overlap operator)
[Op.contains]: [1.2]      // @> [1, 2] (PG array contains operators)
[Op.contained]: [1.2]     // <@ [1, 2] (PG array contained in operator)
[Op.any]: [2.3]            // Any array [2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', using the database language-specific column identifier, PG in this example
Copy the code

If you want to query for values whose price is greater than 22

// get the Op instance above js
const Op = Sequelize.Op


books.findAll({
  where: {price: {
      [Op.gt]: 22
    }
  }
}).then(books= > {
  console.log(JSON.stringify(books, null.4));
})
Copy the code

Print the result

The sorting

Model query (foundation) | sorting

An array or sequelize method is required to configure order to sort the query results

books.findAll({
  where: {price: {
      // The price is greater than 22
      [Op.gt]: 22}},order:[
    ['id'.'DESC'] // In reverse order by price
  ]
}).then(books= > {
  console.log(JSON.stringify(books, null.4));
})
Copy the code

The order is reversed

Limit the number of entries and paging

Model query (foundation) | restrictions and paging

Only 4 pieces of data came out (I added 30 pieces of total data in advance)

books.findAll({
  where: {price: {
      // The price is greater than 18
      [Op.gt]: 15}},order:[
    ['id'.'DESC'] // In reverse order by price].limit: 4
}).then(books= > {
  console.log(JSON.stringify(books, null.4));
})
Copy the code

// Extract 10 instances/rows
Project.findAll({ limit: 10 });

// Skip 8 instances/rows
Project.findAll({ offset: 8 });

// Skip 5 instances and get 5 instances
Project.findAll({ offset: 5.limit: 5 });
Copy the code

Only partial attributes are retrieved

books.findAll({
  where: {price: {
      // The price is greater than 22
      [Op.gt]: 22}},order:[
    ['id'.'DESC'] // In reverse order by price].limit: 4.attributes: ['name'.'price'] // Get only the name and price attributes
}).then(books= > {
  console.log(JSON.stringify(books, null.4));
})
Copy the code

Print the result

Gets properties other than this property

attributes: {
  exclude: ['id']}Copy the code

A practical method

A practical method model query (foundation) |

bookks.count('id').then( counts= > {
  console.log(couunts)
})
Copy the code

Return the total number of id fields in the database 27

// select * from books
books.count().then(counts= >console.log(counts))

Query the total number of entries in the books table whose name is Vue3
books.count({
  where: {
    name: 'Vue3'
  }
}).then(counts= >console.log(counts))

// Return the maximum value because the data type is floating point 34.79999923706055
books.max('price').then(maxPrice= >console.log(maxPrice))

// Return the maximum value 15
books.min('price').then(minPrice= >console.log(minPrice))

// Return total 837
books.sum('count').then(allCount= >console.log(allCount))

Vue3 * 18 = 162
books.sum('count', {where: {
    name: 'Vue3'
  }
}).then(allCount= >console.log(allCount))
Copy the code

update

Update the price of item id 1 to 50, return [1]

books.update(
  {
    price: 50
  },
  {
    where: {
      id: 1
    }
  }
).then(res= > {
  console.log(res);
})
Copy the code

Update the price of data with name Vue3 to 22, return [9]

books.update(
  {
    price: 22
  },
  {
    where: {
      name: 'Vue3'
    }
  }
).then(res= > {
  console.log(res);
})
Copy the code

We know that the return value is the number of data items to be updated

delete

Delete the data whose ID is 10 and return [1]. The same as above indicates the number of deleted items

books.destroy({
  where: {
    id: 10
  }
}).then(res= > {
  console.log(res);
})
Copy the code

Persistent data store – mysql

The difference between normal file and database storage

Database classification

MySQL installation

Qunhui NAS installation

Windows installation

SQL statement operation

Navicat visualization tool use

Node.js native driver links to MySQL

Creating a Server

Writing a command line

Create a table

Add one or more data to a table after it is created

Query data

Delete the data

Encapsulate mysql’s Query method for easy operation

The connection pool

Packaging ideas

The ORM framework Sequelize is an introduction to its data types

An overview of the

Why did you choose it?

The installation

Configure and define the table model

Sequelize Data type

Insert data

Insert multiple data

The query

Query all data

Returns the entire table data

Incoming query criteria

Modify the

To calculate

Query a piece of data

Return table 1

Incoming query criteria

Query condition operator

The sorting

Limit the number of entries and paging

Only partial attributes are retrieved

Gets properties other than this property

A practical method

A practical method model query (foundation) |

update

delete

Related Posts

【Python crawler project actual practice 】 click to download short video

Java Locks: Chapter 3: Spin locks

Local deployment of Apache Kafka