数据流向一般是：前端请求 ---->后端数据校验 ---->查询数据库 ---->响应前端
基本的数据库sql(结构化查询语句)一般涉及增删改查四大操作，具体如下：
- 增
INSERT INTO 表(字段列表) VALUES(值列表)
INSERT INTO user_table(ID,name,gender,chinese,math,english) VALUES(0,"张三","男",20,30,50)
- 删
DELETE FROM 表 WHERE 条件
DELETE FROM user_table WHERE ID=1
3.改
UPDATE 表 SET 字段1=值1,字段2=值2 WHERE 条件
UPDATE user_table SET chinese=20 WHERE ID=1
- 查
SELECT 字段1,字段2 FROM 表 WHERE 条件
SELECT chinsese FROM user_table WHERE ID=1
前后端配合完成简单注册和登陆的数据交互代码如下：
- 前端代码：
<!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>前后台联调往数据库存取数据</title>
<script src="./jquery.js"></script>
<script>
$(function(){
$("#btn1").click(function(){
$.ajax({
url: "/reg",
method: "get",
data: {
username: $("#user").val(),
password: $("#pwd").val()
},
dataType: "json",
success(json){
if(json.err){
alert("注册失败了"+json.msg);
}else{
alert("注册成功了");
}
},
error(err){
alert("请求出错了"+JSON.stringify(err));
}
})
})
$("#btn2").click(function(){
$.ajax({
url: "/login",
method: "get",
data: {
username: $("#user").val(),
password: $("#pwd").val()
},
dataType: "json",
success(json){
if(json.err){
alert("登陆失败了"+json.msg);
}else{
alert("登陆成功了");
}
},
error(err){
alert("请求出错了"+JSON.stringify(err));
}
})
})
})
</script>
</head>
<body>
用户名：<input type="text" name="user" id="user"><br>
密码：<input type="password" name="pwd" id="pwd"><br>
<button id="btn1">注册</button>
<button id="btn2">登陆</button>
</body>
</html>
- 后端代码
const http = require("http");
const fs = require("fs");
const mysql = require("mysql");
const url = require("url");
const zlib = require("zlib");
const crypto = require("crypto");
let key = "1234656";
function md5(str){
let obj = crypto.createHash("md5");
obj.update(str);
return obj.digest("hex");
}
function md5_2(str){
return md5(md5(str+key));
}
//创建数据库的连接
let db = mysql.createPool({host: "localhost", port: "3306", user: "root", password: "cf540294", database: "user"})
//创建服务
let server = http.createServer((req,res)=>{
let {pathname,query} = url.parse(req.url,true);
let {
username,
password
} = query;
switch(pathname){
case "/reg":
//对数据进行校验
if(!username){
res.write('{"err":1,"msg":"username can\'t be empty"}');
res.end();
}else if(!password){
res.write('{"err":1,"msg":"password can\'t be empty"}');
res.end();
}else if(!/^\w{4,16}$/.test(username)){
res.write('{"err":1,"msg":"username is invalid"}');
res.end();
}else if(/['|"]/.test(password)){
res.write('{"err":1,"msg":"password is invalid"}');
res.end();
}else{
//写入数据库之前先判断用户明是否存在
db.query(`SELECT * FROM user_table WHERE username='${username}'`,(err,data)=>{
if(err){
res.write('{"err":1,"msg":"database error"}');
res.end();
}else{
if(data.length>0){
res.write('{"err":1,"msg":"the username is exists"}');
res.end();
}else{
db.query(`INSERT INTO user_table (id,username,password) VALUES(0,'${username}','${md5_2(password)}')`, (err, data) => {
if(err){
res.write('{"err":1,"msg":"database error"}');
res.end();
}else{
res.write('{"err":0,"msg":"success"}')
res.end();
}
})
}
}
})
}
break;
case "/login":
//对数据进行校验
if (!username) {
res.write('{"err":1,"msg":"username can\'t be empty"}');
res.end();
} else if (!password) {
res.write('{"err":1,"msg":"password can\'t be empty"}');
res.end();
} else if (!/^\w{4,16}$/.test(username)) {
res.write('{"err":1,"msg":"username is invalid"}');
res.end();
} else if (/['|"]/.test(password)) {
res.write('{"err":1,"msg":"password is invalid"}');
res.end();
} else {
//写入数据库之前先判断用户明是否存在
db.query(`SELECT * FROM user_table WHERE username='${username}'`, (err, data) => {
if (err) {
res.write('{"err":1,"msg":"database error"}');
res.end();
}else if(data.length == 0){
res.write('{"err":1,"msg":"the username is not exists"}');
res.end();
}else if(data[0].password != md5_2(password)){
res.write('{"err":1,"msg":"the username or password is incorrect"}');
res.end();
}else{
res.write('{"err":0,"msg":"success"}');
res.end();
}
})
}
break;
default:
let rs = fs.createReadStream(`www${pathname}`);
let gz = zlib.createGzip();
res.setHeader("content-encoding","gzip");
rs.pipe(gz).pipe(res);
rs.on("error",err=>{
res.writeHeader(404);
res.write("Not Found");
res.end();
});
break;
}
})
// 监听端口
server.listen(8080);
