# 五.mysql 实现增删改查
# 1.实现过程
# 1.安装 mysql npm 包
npm install mysql -S
1
# 2.增加功能
const mysql = require("mysql")
const connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "test",
})
connection.connect()
let userAddSql = "INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)"
let userAddSql_Params = ["我的名字", "123"]
connection.query(userAddSql, userAddSql_Params, function(err, result) {
console.log("增" + JSON.stringify(result))
})
connection.end()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 3.删除功能
...
let userDeleteSql = "DELETE FROM userinfo"
connection.query(userDeleteSql, function(err, result) {
console.log("删" + JSON.stringify(result))
})
connection.end()
1
2
3
4
5
6
2
3
4
5
6
# 4.更新功能
...
let userChangeSql = "UPDATA userinfo SET UserName = ?,UserPass = ? WHERE Id = ?"
let userChangeSql_Params = ["我的名字", "12344", 21]
connection.query(userChangeSql, userChangeSql_Params, function(err, result) {
console.log("改" + JSON.stringify(result))
})
connection.end()
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 4.查找功能
...
let userSearchSql = "SELECT * FROM userinfo"
connection.query(userSearchSql, function(err, result) {
console.log("查" + JSON.stringify(result))
})
connection.end()
1
2
3
4
5
6
7
2
3
4
5
6
7
# 2.整体代码
- 后端代码
const Koa = require("koa")
const app = new Koa()
app.use(async (ctx) => {
ctx.body = "hello body"
})
app.listen(3000)
const mysql = require("mysql")
const connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "test",
})
connection.connect()
let userAddSql = "INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)"
let userAddSql_Params = ["我的名字", "123"]
connection.query(userAddSql, userAddSql_Params, function(err, result) {
console.log("增" + JSON.stringify(result))
})
let userDeleteSql = "DELETE FROM userinfo"
connection.query(userDeleteSql, function(err, result) {
console.log("删" + JSON.stringify(result))
})
let userChangeSql = "UPDATA userinfo SET UserName = ?,UserPass = ? WHERE Id = ?"
let userChangeSql_Params = ["我的名字", "12344", 21]
connection.query(userChangeSql, userChangeSql_Params, function(err, result) {
console.log("改" + JSON.stringify(result))
})
let userSearchSql = "SELECT * FROM userinfo"
connection.query(userSearchSql, function(err, result) {
console.log("查" + JSON.stringify(result))
})
connection.end()
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
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
- 前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="col-md-6 col-md-offset-3">
<div class="panel panel-info">
<div class="panel-heading">
<p class="text-warning">注册登录页面</p>
</div>
<div class="panel-body">
<ul class="list-group">
<li class="list-group-item">
<div>
<label for="name">姓名：</label>
<input type="text" id="name" class="form-control" />
</div>
</li>
<li class="list-group-item">
<label>密码：</label>
<input type="password" class="form-control" />
</li>
<li class="list-group-item">
<label>确认密码：</label>
<input type="password" class="form-control" />
</li>
</ul>
<button type="submit" class="btn btn-default">提交</button>
</div>
<div class="panel-footer">
<span>登录</span>
<span>注册</span>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
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
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