一、数据库连贯
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顺序排列