nodejs连接数据库实现简单增啥改查
首先安装MySQL ,navicate数据库工具,新建表users
新建一个index.js文件
配置连接数据库
const mysql = require('mysql');
// 配置连接项
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
port: '3306',
database: 'test'
});
// 执行连接操作
connection.connect();
然后编写增删改查命令
// 新增数据 - 插入操作 1
const insertSql1 = `insert into users (username, password, nickname) values ('张三', 'password1', 'User1');`;
connection.query(insertSql1, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
});
// 新增数据 - 插入操作 2
const insertSql2 = `insert into users (username, password, nickname) values ('李四', 'password2', 'User2');`;
connection.query(insertSql2, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
});
// 新增数据 - 插入操作 3
const insertSql3 = `insert into users (username, password, nickname) values ('王二', 'password3', 'User3');`;
connection.query(insertSql3, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
});
// 查询
const selectSql = `select * from users;`;
connection.query(selectSql, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
// 修改
const updateSql = `update users set username='小明' where nickname='User1';`;
connection.query(updateSql, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
// 删除
const deleteSql = `delete from users where id='3';`;
connection.query(deleteSql, (err, result) => {
if (err) {
console.error(err);
return;
}
console.log(result);
// 关闭连接
connection.end();
});
});
});
查询有多种方式
模糊查询:
const sql = `select * from users where username like '%1%';
在VScode输出框可以看到执行的操作;
[Running] node "d:\Users\86178\Desktop\node-mysql-demo\index.js"
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 12,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 13,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 14,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
[
RowDataPacket {
id: 12,
username: '张三',
password: 'password1',
nickname: 'User1'
},
RowDataPacket {
id: 13,
username: '李四',
password: 'password2',
nickname: 'User2'
},
RowDataPacket {
id: 14,
username: '王二',
password: 'password3',
nickname: 'User3'
}
]
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 34,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1
}
OkPacket {
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
[Done] exited with code=0 in 0.603 seconds
可以看到,数据库中已经有了我们修改的数据
这只是一个初步的结构,再配合node http请求,HTML等操作,结合后端框架,就可以在游览器上进行页面操作了。