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