一、数据库连贯
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() // 开释连接池});
}
})
})
}
二、数据库操作
- –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 '% 文 %'
2、具体数据库查问
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 之间的数据
基于数据再排序
当须要返回的信息为程序或者倒序时,对下面的语句基于数据再排序。order by ASC/DESC 程序或倒序 默认为程序
select from table where good_id > (pageNo-1)pageSize order by good_id limit pageSize;
–返回 good_id 为 40 到 50 之间的数据, 数据根据 good_id 顺序排列