數(shù)據(jù)流向一般是：前端請(qǐng)求 ---->后端數(shù)據(jù)校驗(yàn) ---->查詢數(shù)據(jù)庫(kù) ---->響應(yīng)前端
基本的數(shù)據(jù)庫(kù)sql(結(jié)構(gòu)化查詢語(yǔ)句)一般涉及增刪改查四大操作，具體如下：
- 增
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
前后端配合完成簡(jiǎn)單注冊(cè)和登陸的數(shù)據(jù)交互代碼如下：
- 前端代碼：
<!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>前后臺(tái)聯(lián)調(diào)往數(shù)據(jù)庫(kù)存取數(shù)據(jù)</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("注冊(cè)失敗了"+json.msg);
}else{
alert("注冊(cè)成功了");
}
},
error(err){
alert("請(qǐng)求出錯(cuò)了"+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("請(qǐng)求出錯(cuò)了"+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">注冊(cè)</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));
}
//創(chuàng)建數(shù)據(jù)庫(kù)的連接
let db = mysql.createPool({host: "localhost", port: "3306", user: "root", password: "cf540294", database: "user"})
//創(chuàng)建服務(wù)
let server = http.createServer((req,res)=>{
let {pathname,query} = url.parse(req.url,true);
let {
username,
password
} = query;
switch(pathname){
case "/reg":
//對(duì)數(shù)據(jù)進(jìn)行校驗(yàn)
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{
//寫入數(shù)據(jù)庫(kù)之前先判斷用戶明是否存在
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":
//對(duì)數(shù)據(jù)進(jìn)行校驗(yàn)
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 {
//寫入數(shù)據(jù)庫(kù)之前先判斷用戶明是否存在
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;
}
})
// 監(jiān)聽(tīng)端口
server.listen(8080);
