Koa performs mysql database operations

1. Database connection

const mysqlDb = require("mysql");
const config = require("./dbconfig");
let options = {
  host: config.config.db_host, -- host nameuser: config.config.db_user, -- Usernamepassword: config.config.db_passwd, -- user passworddatabase: config.config.db_name, -- Database namemultipleStatements: true, -- enable multi-wire pool};var pool = mysqlDb.createPool(options);
 exports.query = function (sql, values) {
    return new Promise((resolve, reject) = > {
      pool.getConnection(function (err, connection) {
        if (err) {
          reject(err)
          //console.log(err, "database connection failed ");
          resolve({
            status: 500}); }else {
          //console.log(" Database connection successful ");
          connection.query(sql, values, (err, results) = > {
            if (err) {
              reject(err);
              resolve({
                status: 400
              });
            } else {
              connection.release();
              resolve({
                status: 200,
                results,
              });
              //resolve(rows)
            }
            //connection.release() // Releases the connection pool}); }})})}Copy the code

Two, database operation

• –user Specifies the name of the database table

1. Database query

Precise query

select * from user where title   -- query a title to query all data
select * from user where title='Today's News'   -- Query a piece of data whose title is today's news
Copy the code

Multi-condition Query

select * from user where title='Today's News' and age='28'
select * from user where title='Today's News' or age='28'
Copy the code

Fuzzy query

select * from user where title like Dragons' % % '   -- Query all records containing "dragon"
select * from user where realname like '% % haven' and realname like Dragons' % % '  Select 'id' from 'id' where 'id' = 'id' and 'id' = 'id'
    
Copy the code

2, specific database query

  letctx_query = ctx.request.body; -- Accept the post valuelet pageSize = ctx_query.size-1; -- Number of entries per pagelet page = ctx_query.page ? (ctx_query.page - 1) * pageSize : 0; - the number of pages// Query the database
  let sql = "select * from list"; -- Query without paginglet total = awaitdb.query(sql); - the total number of pagesif (ctx_query.country==' '&& ctx_query.title) {
    sql += ` where title like '%${ctx_query.title}%' limit ${page}.${pageSize}`;
  }else if (ctx_query.country && ctx_query.title==' ') {
    sql += ` where country like '%${ctx_query.country}%' limit ${page}.${pageSize}`;
  }else if (ctx_query.country == "" && ctx_query.title=="") {
    sql += ` limit ${page}.${pageSize}`;
  } else {
    sql += ` where title like '%${ctx_query.title}%' and country like'%${ctx_query.country}%' limit ${page}.${pageSize}`;
  }
  console.log('sql',sql)
  const result = await db.query(sql);
Copy the code

3. Data is inserted into the database

INSERT INTO users ( id, user,passwd) VALUES (12.'zhaofang'.'111111' );
letquery = ctx.request.body; -- Accept the post valuelet sql = "INSERT INTO list"; SQL +=` (title, date,content,name,country,city) VALUES ('${query.title}', '${query.date}', '${query.content}', '${query.name}', '${query.country}', '${query.city}' )`;
const results = awaitDb.query (SQL) -- Connect to a database operationCopy the code

4. Modify data in the database

letquery = ctx.request.body; -- Accept the post valuelet sql = "UPDATE list SET";
sql += ` title='${query.title}',date='${query.date}',content='${query.content}',name='${query.name}',country='${query.country}',city='${query.city}' where id='${query.id}'`;
const results = awaitDb.query (SQL) -- Connect to a database operationCopy the code

5. Delete the database

letquery = ctx.query; -- Accept the del passlet sql = `DELETE FROM list where id='${query.id}'`;
const results = await db.query(sql);
Copy the code

Select * from table where good_id > (pageNo-1)* pagesize limit pagesize; select * from table where good_id > (pageNo-1)* pagesize limit pagesize; – The value of good_id ranges from 40 to 50

Reorder based on data When the information to be returned is in order or reverse order, the above statement is reordered based on the data. Order by ASC/DESC

select * from table where good_id > (pageNo-1)*pageSize order by good_id limit pageSize; – Data whose good_id ranges from 40 to 50 is displayed in the order of good_id