koa连接mysql怎么做_koa进行mysql数据库操做

1、数据库链接

const mysqlDb = require("mysql");

const config = require("./dbconfig");

let options = {

host: config.config.db_host, --主机名

user: config.config.db_user, --用户名

password: config.config.db_passwd, --用户密码

database: config.config.db_name, --数据库名

multipleStatements: true, --启用多线池

};

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, "数据库链接失败");

resolve({

status: 500,

});

} else {

//console.log("数据库链接成功");

connection.query(sql, values, (err, results) => {

if (err) {

reject(err);

resolve({

status: 400

});

} else {

connection.release();

resolve({

status: 200,

results,

});

//resolve(rows)

}

//connection.release() // 释放链接池

});

}

})

})

}

2、数据库操做

--user 数据库表名

1.数据库查询

精准查询

select * from user where title --查询一title查询全部数据

select * from user where title='今日新闻' --查询title为今日新闻的一条数据

多条件查询

select * from user where title='今日新闻' and age='28'

select * from user where title='今日新闻' or age='28'

模糊查询

select * from user where title like '%龙%' --查询出全部含有“龙”的记录

select * from user where realname like '%文%' and realname like '%龙%' --所查询字段 + like '%龙%' and 所查询字段 + like '%文%'

二、具体数据库查询

let ctx_query = ctx.request.body; --接受post取值

let pageSize = ctx_query.size-1; --每页显示的条数

let page = ctx_query.page ? (ctx_query.page - 1) * pageSize : 0; --页数

//查询数据库

let sql = "select * from list"; --不带分页的查询

let total = await db.query(sql); --总页数

if (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);

3.数据库插入数据

INSERT INTO users ( id, user,passwd) VALUES (12,'zhaofang','111111' );

let query = ctx.request.body; --接受post传值

let sql = "INSERT INTO list"; --list为表名

sql += ` (title, date,content,name,country,city) VALUES ('${query.title}','${query.date}','${query.content}','${query.name}','${query.country}','${query.city}' )`;

const results = await db.query(sql) --链接数据库操做

4.数据库修改数据

let query = ctx.request.body; --接受post传值

let 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 = await db.query(sql) --链接数据库操做

5.数据库删除

let query = ctx.query; --接受del传值

let sql = `DELETE FROM list where id='${query.id}'`;

const results = await db.query(sql);

假设主键或者惟一索引为 good_id

收到客户端{pageNo:5,pagesize:10}

select from table where good_id > (pageNo-1)pageSize limit pageSize;

–返回good_id为40到50之间的数据mysql

基于数据再排序

当须要返回的信息为顺序或者倒序时，对上面的语句基于数据再排序。order by ASC/DESC 顺序或倒序 默认为顺序sql

select from table where good_id > (pageNo-1)pageSize order by good_id limit pageSize;

–返回good_id为40到50之间的数据,数据依据good_id顺序排列数据库