数据库的基本操作，增、删、改、查

/app/router.js 文件如下（含增、删、改、查）：

'use strict';

/**
 * @param {Egg.Application} app - egg application
 */
module.exports = app => {
  const { router, controller, middleware } = app;
  router.post('/getList',  controller.list.getList)       // 查找
  router.post('/updateList', controller.list.updateList)  // 更新
  router.post('/delete', controller.list.delete)          // 删除
  router.post('/addUser', controller.list.addUser)        // 添加
  router.post('/patchAdd', controller.list.patchAdd)      // 批量添加
  router.post('/patchDelete', controller.list.patchDelete)// 批量删除
  router.post('/patchUpdate', controller.list.patchUpdate)// 批量更新
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1、查找数据

# (1)、查找 user_list 表中指定 user_id 的用户

/app/controller/list.js 中代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async getList () {
		const { ctx } = this;
		const userId = ctx.request.body.userId
		const userInfo = await ctx.service.user.find(userId)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/app/service/user.js 中代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async find(uid) {
		if (uid) {
			const user = await this.app.mysql.get('user_list', {user_id: uid})
			return user
		} else {
			const user = await this.app.mysql.query(`select * from user_list`)
			return user
		}
		//方法1 const user = await this.app.mysql.get('user_list', {user_id: uid})
		
		/*方法2 
		const user = await this.app.mysql.select('user_list', {
			where: {
				user_id: uid
			}
		})
		*/
		//方法3,直接执行sql语句，不推荐，容易引起sql注入 
		/* 
		const user = await this.app.mysql.query(`select * from user_list where user_id = ${uid}`)
		*/
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# (2)、查找 user_list 表中所有用户

/app/service/user.js 中代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async find(uid) {
		const user = await this.app.mysql.select('user_list', {})
		return user
		//或者 const user = await this.app.mysql.query(`select * from user_list`)
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11

# 2、更新数据

修改 user_list 表中数据

/app/controller/list.js 文件代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async updateList () {
		const { ctx } = this;
		const userId = ctx.request.body.userId
		const userName = ctx.request.body.userName
		const userInfo = await ctx.service.user.update(userId, userName)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

/app/service/user.js 文件如下：

const { Service } = require('egg')

class UserService extends Service {
	async update(uid, userName) {
		// 如果主键是自定义的 ID 名称，如 custom_id，则需要在 `where` 里面配置
		// const user = await this.app.mysql.update('user_list', {
		// 	user_name: userName
		// }, {
		// 	where: {
		// 		user_id: uid
		// 	}
		// })
		// 或者, 根据主键 ID 查找，并更新
		const user = await this.app.mysql.update('user_list', {
			user_id: uid,
			user_name: userName
		})
		// // 判断更新成功，user.affectedRows === 1
		if (user.affectedRows === 1) {
			return {
				msg: '更新成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '更新失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 3、删除数据

删除 user_list 表中数据

/app/controller/list.js 中代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async delete () {
		const { ctx } = this;
		const userId = ctx.request.body.userId
		const userInfo = await ctx.service.user.delete(userId)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/app/service/user.js 中代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async delete (uid) {
		const result = await this.app.mysql.delete('user_list', {
			user_id: uid
		})
		if (result.affectedRows === 1) {
			return {
				msg: '删除成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '删除失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 4、增加数据

在 user_list 表中增加数据

/app/controller/list.js 中代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async addUser () {
		const { ctx } = this;
		const query = ctx.request.body
		const userInfo = await ctx.service.user.addUser(query)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/app/service/user.js 中代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async addUser (data) {
		const result = await this.app.mysql.insert('user_list', {
			user_name: data.userName || '',
			user_tel: data.userTel || ''
		})
		if (result.affectedRows === 1) {
			return {
				msg: '添加数据成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '添加数据失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 5、批量插入数据

/app/controller/list.js 中代码如下

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async patchAdd () {
		const { ctx } = this;
		const query = ctx.request.body
		const userInfo = await ctx.service.user.patchAdd(query)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/app/service/user.js 中代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async patchAdd (data) {
		/* data数据格式如下
			[
				{
					user_tel: xxx,
					user_name: xxx
				},
				{
					user_tel: xxx,
					user_name: xxx
				}
			]
		*/
		const arr = []
		data.forEach(item => {
			arr.push([item.user_tel,item.user_name])
		})
		/* 转换后的arr数据格式如下
			[[user_tel, user_name],[user_tel, user_name]]
		*/
		const result = await this.app.mysql.query(`insert into user_list (user_tel, user_name) values ?`, [arr])
		/* 或者
			let telList = []
			let nameList = []
			data.forEach(item => {
				telList.push(item.user_tel)
				nameList.push(item.user_name)
			})
			const result = await this.app.mysql.insert('user_list', {
				user_tel: telList,
				user_name: nameList
			})
		*/
		if (result.affectedRows === arr.length) {
			return {
				msg: '批量添加数据成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '批量添加数据失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

# 6、批量删除数据

/app/controller/list.js 代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async patchDelete () {
		const { ctx } = this;
		const query = ctx.request.body
		const userInfo = await ctx.service.user.patchDelete(query)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/app/service/user.js 代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async patchDelete (data) {
		// const deleteNum = 11
		// const result = await this.app.mysql.query(`delete from user_list where user_id > 30 limit ${deleteNum}`)
		/* data数据格式如下
			[1,2,3,4]
		*/
		const result = await this.app.mysql.delete('user_list', {
			user_id: data
		})
		/* 或者
		const deleteStr = data.join(',')
		const result = await this.app.mysql.query(`delete from user_list where user_id in (${deleteStr})`)
		*/
		if (result.affectedRows === data.length) {
			return {
				msg: '批量删除成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '批量删除失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 7、批量更新数据

例如，批量审核通过或拒绝数据操作

/app/controller/list.js 代码如下：

'use strict';

const { Controller } = require('egg');

class ListController extends Controller {
	async patchUpdate () {
		const {ctx} = this
		const ids = ctx.request.body.ids
		const type = ctx.request.body.isPass
		const userInfo = await ctx.service.user.patchUpdate(ids, type)
		ctx.body = userInfo
	}
}

module.exports = ListController;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

/app/service/user.js 代码如下：

const { Service } = require('egg')

class UserService extends Service {
	async patchUpdate (data, type) {
		// 如果type为true表示通过，status更改为1，否则更改status为0
		const status = type ? 1 : 0
		const result = await this.app.mysql.update('user_list', {
				status
			}, {
				where: {
					user_id: data
				}
			})
		/* 或者
			const ids = data.join(',')
			const result = await this.app.mysql.query(`update user_list set status = ${status} where user_id in (${ids})`)
		*/
		if (result.affectedRows === data.length) {
			return {
				msg: '批量更新成功',
				status: 200,
				data: []
			}
		} else {
			return {
				msg: '批量更新失败',
				status: 300,
				data: []
			}
		}
	}
}

module.exports = UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

ps:
如果是批量操作多条数据，那么判断是否操作成功的条件是 affectedRows 是否等于批量操作数据的长度，是的话则表示操作成功。

编辑

上次更新: 7/2/2024, 11:06:45 AM

← 实现接口API并连接mysql数据库 egg-jwt登录鉴权→