koa连接mysql数据库并进行简单增删改查

连接数据库

初级菜鸟，为图方便暂时在路由中配置数据库未使用连接池 ，路由文件

const mysql = require('mysql')  //引入数据库插件
const koaRouter = require('koa-router');
const router = koaRouter();

//连接数据库
const connection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '123456',
	port: '3306',
	database: 'testnode'
})
//新增数据
router.post('/add', async (ctx, next) => {
	console.log(Boolean( ctx.request.query.name && ctx.request.query.address && ctx.request.query.phone))
	if(ctx.request.query.phone && ctx.request.query.address && ctx.request.query.name){
		
		var data = {}
		var createdTime=new Date()
		var querySql="INSERT INTO address (createdTime,name,address,phone,belongsId) VALUES(?,?,?,?,?)"
		var queryParams=[createdTime,ctx.request.query.name,ctx.request.query.address,ctx.request.query.phone,ctx.request.query.userId]
		console.log(queryParams)
		var searchSql = (querySql,queryParams) => {
			return new Promise((resolve, reject) => {
				console.log('准备查询')
				connection.query(querySql,queryParams, function(err, result) {
					if(err){
						reject(0)
						console.log(err+'err')
					}
					resolve(result)
				})
			})
		}
		var res= await searchSql(querySql,queryParams)
		if(res){
			data.code='0'
			data.msg="添加成功"
			ctx.body=data
		}else{
			data.code='1'
			data.msg="添加失败"
			ctx.body=data
		}
	}
});
//查询数据
router.get('/query', async (ctx, next) => {
	var data = {}
	console.log(ctx.request.query.pageNo - 1)
	var querySql="SELECT * FROM address where belongsId= '"+ctx.request.query.userId+"' limit "+ (ctx.request.query.pageNo-1)*ctx.request.query.pageSize+"," + ctx.request.query.pageSize
	var querySqlNo="SELECT * FROM address where belongsId= '"+ctx.request.query.userId+"'"
	var searchSql = (thesql) => {
		return new Promise((resolve, reject) => {
			console.log('准备查询')
			connection.query(thesql, function(err, result) {
				if(err){
					reject(0)
					console.log(err+'err')
				}
				resolve(result)
			})
		})
	}
	var res= await searchSql(querySqlNo)
	if(res){
		var datares= await searchSql(querySql)
		if(datares){
			var param={list:datares,total:res.length}
			data.code='0'
			data.msg="成功获取"
			data.data=param
			ctx.body=data
		}
		
	}
})
//更新数据
router.post('/update', async (ctx, next) => {
	var updateSql="UPDATE address SET name = '"+ctx.request.query.name+"', phone = '" + ctx.request.query.phone + "', address = '" + ctx.request.query.address +"' where id = '" +ctx.request.query.id + "'"
	var searchSql = (thesql) => {
		return new Promise((resolve, reject) => {
			connection.query(thesql, function(err, result) {
				if(err){
					reject(1)
					console.log(err+'err')
				}
				resolve(0)
			})
		})
	}
	var data={}
	var res= await searchSql(updateSql)
	
	if(res==0){
		data.msg="更新成功"
		data.code=0
		ctx.body=data
	}else{
		data.code=1
		data.msg="更新失败,请稍后再试！"
		ctx.body=data
	}
});
//删除数据
router.post('/remove', async (ctx, next) => {
	var deleteSql="DELETE FROM address WHERE "+"id='"+ctx.request.query.id + "'"
	var searchSql = (thesql) => {
		return new Promise((resolve, reject) => {
			connection.query(thesql, function(err, result) {
				if(err){
					reject(1)
					console.log(err+'err')
				}
				resolve(0)
			})
		})
	}
	var data={}
	var res= await searchSql(deleteSql)
	
	if(res==0){
		data.msg="删除成功"
		data.code=0
		ctx.body=data
	}else{
		data.code=1
		data.msg="删除失败,请稍后再试！"
		ctx.body=data
	}
});

精华部分

1. 拼接sql操作语句
2. 执行sql操作语句
3. 处理结果

增删改查sql语句
增 ：INSERT INTO address (createdTime,name,address,phone,belongsId) VALUES(?,?,?,?,?)
var queryParams=[createdTime,ctx.request.query.name,ctx.request.query.address,ctx.request.query.phone,ctx.request.query.userId]
address 为查询表名称 queryParams为插入的新数据对应上面？的个数 也可直接讲问号替换为对应的值
删：“DELETE FROM address WHERE “+“id=‘11111’’” id=‘11111’ 为删除符合的条件
address 为查询表名称 WHERE 为满足删除的条件
改：“UPDATE address SET name = '”+ctx.request.query.name+”’, phone = ‘" + ctx.request.query.phone + "’, address = ‘" + ctx.request.query.address +"’ where id = ‘" +ctx.request.query.id + "’"
address 为查询表名称 SET 和where之间为插入数据 where后为插入数据的id
查：SELECT * FROM address where belongsId= ‘"+ctx.request.query.userId+"’ limit “+ (ctx.request.query.pageNo-1)*ctx.request.query.pageSize+”," + ctx.request.query.pageSize
pageNo为查询页码 pageSize为查询条数 belongsId=“” 为查询条件 address 为查询表名称

执行sql语句的方法 可统一封装减小代码量

var searchSql = (thesql) => {
		return new Promise((resolve, reject) => {
			connection.query(thesql, function(err, result) {
				if(err){
					reject(1) //返回状态
					console.log(err+'err')
				}
				resolve(0) //返回状态 //只有查询有具体返回内容
			})
		})
	}