数据准备
使用数据可视化工具创建数据库node_mysql_test，创建客户表customers，包含id,name,age三个字段
目录结构
npm i mysql -d
node-mysql增删改查
新增数据
add.js
const mysql = require('mysql');
//mysql连接配置
const connection = mysql.createConnection({
host: 'localhost', //主机名
user: 'root', //用户名
password: '', //密码
database: 'node_mysql_test' //数据库名
})
//连接数据库
connection.connect();
let sql = 'INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (?, ?, ?)';
let params = [1, '李四', 20];
connection.query(sql, params, (err,data) => {
if(err) throw err;
console.log('新增成功', data);
})
connection.end();//关闭数据库
终端进入node-mysql目录下执行node add.js查看控制台显示新增成功和返回信息
删除数据
delete.js
const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_mysql_test'
});
connection.connect();
let sql = 'DELETE FROM CUSTOMERS WHERE ID = 1';
connection.query(sql, (err,data) => {
if(err) throw err;
console.log('删除成功', data)
})
connection.end();
修改数据
update.js
const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_mysql_test'
});
connection.connect();
let sql = 'UPDATE CUSTOMERS SET NAME = ? WHERE ID = 1';
let params = ['change'];
connection.query(sql, params, (err,data) => {
if(err) throw err;
console.log('编辑成功', data)
})
connection.end();
查询数据
search.js
const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_mysql_test'
});
connection.connect();
let sql = 'SELECT * FROM CUSTOMERS';
connection.query(sql, (err,data) => {
if(err) throw err;
console.log('查询成功', data)
})
connection.end();
其他
mysql忘记root密码解决方法见juejin.cn/post/684490…
node操作mysql完成登录注册功能
代码放在github.com/itwshen/nod…，backend存放node端，frontend存放静态页面
在backend目录下执行node index.js启动服务端,frontend目录下执行live-server(一个具有实时加载功能的小型服务器，npm i live-server -g安装)即可预览界面
1. 在mysql中新建user表
2. 注册功能
$(function(){
$('.register').click(function(){
let username = $('#username').val().trim(),
password = $('#password').val().trim();
if(!username) {
alert('请输入用户名!');
$('#username').focus();
}else if(!password){
alert('请输入密码!');
$('#password').focus();
}else {
//发送请求注册用户信息
$.ajax({
url: 'http://localhost:3000/register',
type: 'post',
dataType: 'json',
data: {
username: username,
password: password
},
success: function(res) {
if(res.code === 1) {
alert('注册成功,前往登录!');
window.location.href = 'index.html';
}else {
alert(res.msg);
}
}
})
}
})
})
node端：
//注册
let username1 = params.username,
password1 = params.password;
now = getNow();
if(!username1 || !password1) {
res.end(JSON.stringify({
code: 0,
msg: '用户名或密码不能为空!'
}));
}else {
let querySql = 'SELECT * FROM USER';
connection.query(querySql, (err, data) => {
if(err) throw err;
//node mysql查询数据会带RowDataPacket 使用JSON序列化可以去除
let result = JSON.parse(JSON.stringify(data));
//判断用户名是否重复
let flag = false;
result.length && result.forEach((item) => {
if(item.user_name == username1) {
flag = true;
}
});
if(flag) {
res.end(JSON.stringify({
code: 0,
msg: '用户名已存在!'
}));
}else {
//注册
let addSql = 'INSERT INTO USER (USER_NAME, USER_PASSWORD, TIME) VALUES (?, ?, ?)';
let addParams = [username1, password1, now];
connection.query(addSql, addParams, (err, data) => {
if(err) throw err;
res.write(JSON.stringify({
code: 1,
msg: '注册成功!'
}));
res.end();
});
}
});
}
3. 登录功能
$(function(){
//登录点击事件
$('.login').click(function(){
let username = $('#username').val().trim(),
password = $('#password').val().trim();
if(!username) {
alert('请输入用户名!');
}else if(!password) {
alert('请输入密码!');
}else {
$.ajax({
url: 'http://localhost:3000/login',
type: 'post',
dataType: 'json',
data: {
username,
password
},
success: function (res) {
if(res.code === 1) {
alert('登录成功，跳转到主页');
}else {
alert(res.msg);
}
},
error: function(err) {
alert(err);
}
})
}
})
})
node端：
//登录验证
let {username, password} = params;
if(!username || !password) {
res.end(JSON.stringify({
code: 0,
msg: '用户名和密码不能为空!'
}));
}else {
let sql = 'SELECT * FROM USER WHERE USER_NAME = ? AND USER_PASSWORD = ?',
sqlParams = [username, password];
connection.query(sql, sqlParams, (err, data) => {
if(err) throw err;
let result = JSON.parse(JSON.stringify(data));
if(!result.length) {
res.end(JSON.stringify({
code: 0,
msg: '用户名或密码不存在！'
}));
} else{
res.end(JSON.stringify({
code: 1,
msg: '登录成功！'
}));
}
})
}
参考链接 Node - 从0基础到实战企业官网
