MySQL 数据库 相关知识
mysql 数据库
- mysql 数据库 oracle：mysql
martin：mariaDB
练习工具 XAMPP 或 mysql
服务器端软件的套装，包含多个服务器端软件
- (1)mysql 的部署结构 服务器端：负责存储/维护数据 —— 银行数据库 C:/xampp/mysql/bin/mysqld.exe 启动文件 占用的端口是 3306 客户端：负责连接服务器，对数据进行增删改查 —— ATM 机 C:/xampp/mysql/bin/mysql.exe 客户端工具
- (2)连接 mysql
-h127.0.0.1 -P3306 -uroot -p
-h host 主机(IP地址/域名) localhost/127.0.0.1
-P port 端口
-u user 用户名 root管理员账户
-p password 密码 root的密码为空
-- 简写形式 mysql -uroot
连接的时候，结尾不要加分号。
- (3)常用的管理命令
quit; 退出服务器的连接
show databases; 显示服务器下所有的数据库
use 数据库名称; 进入指定的数据库
show tables; 显示当前数据库下所有的表
desc 表名称; 描述表中都有哪些列
分别进入数据库test以及数据库phpmyadmin
查看mysql数据库下都有哪些表
查看phpmyadmin的以下表都有哪些列
pma__recent
pma__history
pma__favorite
node 服务数据库连接池
- 目录结构
- serve 架构
- 静态托管目录
//app.js
const express = require("express");
//引入路由器
const userRouter = require("./router/user.js");
// const ajaxRouter=require('./router/ajax.js');
// const proRouter=require('./router/pro.js')
// const pro1Router=require('./router/pro1.js')
//引入body-parser中间件
const bodyParser = require("body-parser");
const app = express();
app.listen(8080);
//托管静态资源到public目录
// app.use(express.static("public"));
// app.use(express.static("ajax"));
app.use(express.static('pro'));
// app.use(express.static('pro1'));
//应用中间件，将post请求的数据解析为对象
app.use(
bodyParser.urlencoded({
extended: false,
})
);
//挂载路由器，并添加前缀/user
// /user/reg
// app.use("/user", userRouter);
// app.use("/ajax", ajaxRouter);
app.use('/pro',proRouter);
// app.use('/pro1',pro1Router);
//pool.js
const mysql = require("mysql");
//创建连接池对象
const pool = mysql.createPool({
host: "127.0.0.1",
port: "3306",
user: "root",
password: "",
database: "xz",
connectionLimit: 20,
});
//导出连接池对象
module.exports = pool;
// pro.js
const express = require("express");
//引入连接池模块
const pool = require("../pool.js");
//console.log(pool);
//创建路由器对象
const router = express.Router();
//往路由器对象添加路由
//1.用户注册路由 post /reg
router.get("/v1/login", (req, res) => {
var _uname = req.query.uname;
var _upwd = req.query.upwd;
//res.send(_uname+"喜欢"+_upwd);
var sql = "select *from xz_user where uname=? and upwd=?";
pool.query(sql, [_uname, _upwd], (err, result) => {
if (err) throw err;
if (result.length > 0) {
res.send("1");
} else {
res.send("0");
}
});
});
router.get("/v1/list", (req, res) => {
var sql = "select * from xz_user";
pool.query(sql, (err, result) => {
if (err) throw err;
res.send(result);
});
});
router.get("/v1/search", (req, res) => {
var _uid = req.query.uid;
//console.log(res.query);
var sql = "select *from xz_user where uid=?";
pool.query(sql, [_uid], (err, result) => {
if (err) throw err;
//console.log(result);
res.send(result);
});
});
router.delete("/v1/del/:uid", (req, res) => {
var _uid = req.params.uid;
var sql = "delete from xz_user where uid=?";
pool.query(sql, [_uid], (err, result) => {
if (err) throw err;
if (result.affectedRows == 0) {
res.send("删除失败");
} else {
res.send("删除成功");
}
});
});
router.put("/v1/update", (req, res) => {
var _uid = req.body.uid;
var obj = req.body;
var sql = "update xz_user set ? where uid=?";
pool.query(sql, [obj, _uid], (err, result) => {
if (err) throw err;
if (result.affectedRows == 0) {
res.send("修改失败");
} else {
res.send("修改成功");
}
});
});
//路由器对象导出
module.exports = router;
//login
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function login() {
var $uname = uname.value;
var $upwd = upwd.value;
if (!$uname) {
alert("请输入用户名");
return;
}
if (!$upwd) {
alert("请输入用户名");
return;
}
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
if (r == 1) {
d1.innerHTML = "登录成功";
location.href = "http://127.0.0.1:8080/_v1list.html";
} else {
d1.innerHTML = "登录失败";
}
}
};
xhr.open("get", `/pro/v1/login?uname=${$uname}&upwd=${$upwd}`, true);
xhr.send();
}
</script>
</head>
<body>
<input type="text" id="uname" /><br />
<input type="text" id="upwd" /><br />
<button onclick="login()">登录</button>
<div id="d1"></div>
</body>
</html>
//list
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function show() {
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
var arr = JSON.parse(r);
var str = `
<table border=1 cellpadding="" cellspacing="0">
<tr>
<th>编号</th>
<th>用户</th>
<th>密码</th>
<th>邮箱</th>
<th>电话</th>
<th>头像</th>
<th>真实姓名</th>
<th>姓名</th>
</tr>
`;
for (var gender = "不详", i = 0; i < arr.length; i++) {
if (arr[i].gender == 1) {
gender = "男";
} else {
gender = "女";
}
str += `
<tr>
<td>${arr[i].uid}</td>
<td>${arr[i].uname}</td>
<td>${arr[i].upwd}</td>
<td>${arr[i].emali}</td>
<td>${arr[i].phone}</td>
<td>${arr[i].avatar}</td>
<td>${arr[i].user_name}</td>
<td>${gender}</td>
</tr>
`;
}
str += `</table>`;
d1.innerHTML = str;
}
};
xhr.open("get", "/pro/v1/list", true);
xhr.send();
}
</script>
</head>
<body onload="show()">
<div id="d1"></div>
</body>
</html>
<table cellspacing=""></table>
//search
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function show() {
var $uid = uid.value;
if (!$uid) {
alert("请输入正确的id");
return;
}
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
//d1.innerHTML=r;
var arr = JSON.parse(r);
//console.log(r);
str = `
<table border=1 cellpadding="" cellspacing="0">
<tr>
<th>编号</th>
<th>用户</th>
<th>密码</th>
<th>邮箱</th>
<th>电话</th>
<th>头像</th>
<th>真实姓名</th>
<th>姓名</th>
</tr>
`;
for (var gender = "不详", i = 0; i < arr.length; i++) {
if (arr[i].gender == 1) {
gender = "男";
} else {
gender = "女";
}
str += `
<tr>
<td>${arr[i].uid}</td>
<td>${arr[i].uname}</td>
<td>${arr[i].upwd}</td>
<td>${arr[i].emali}</td>
<td>${arr[i].phone}</td>
<td>${arr[i].avatar}</td>
<td>${arr[i].user_name}</td>
<td>${gender}</td>
</tr>
`;
}
str += `</table>`;
d1.innerHTML = str;
}
};
xhr.open("get", `/pro/v1/search?uid=${$uid}`, true);
xhr.send();
}
</script>
</head>
<body>
<input type="text" id="uid" />
<button onclick="show()">查找</button>
<div id="d1"></div>
</body>
</html>
//update
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function show() {
var $uid = uid.value;
var $uname = uname.value;
var $upwd = upwd.value;
var $email = email.value;
var $gender = gender.value;
var $phone = phone.value;
if (!$uid) {
alert("请输入id");
return;
}
if (!$uname) {
alert("请输入用户名");
return;
}
if (!$upwd) {
alert("请输入密码");
return;
}
if (!$email) {
alert("请输入邮箱");
return;
}
if (!$gender) {
alert("请输入性别");
return;
}
if (!$phone) {
alert("请输入电话");
return;
}
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
d1.innerHTML = r;
}
};
xhr.open("put", "/pro/v1/update", true);
var formdata = `uid=${$uid}&uname=${$uname}&upwd=${$upwd}&email=${$email}&gender=${$gender}&phone=${$phone}`;
xhr.setRequestHeader(
"Content-Type",
"application/x-www-form-urlencoded"
);
xhr.send(formdata);
}
</script>
</head>
<body>
<h1>修改用户</h1>
编号<input type="text" id="uid" /><br />
账户<input type="text" id="uname" /><br />
密码<input type="text" id="upwd" /><br />
邮箱<input type="text" id="email" /><br />
性别 <input type="text" id="gender" /><br />
电话<input type="text" id="phone" /><br />
<button onclick="show()">修改用户</button>
<div id="d1"></div>
</body>
</html>
// update2
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function show() {
var $uid = uid.value;
var $uname = uname.value;
var $upwd = upwd.value;
var $email = email.value;
//var $gender=gender.value;
if (s1.checked == true) {
var $gender = s1.value;
} else if (s1.checked == true) {
var $gender = s2.value;
}
var $phone = phone.value;
if (!$uid) {
alert("请输入id");
return;
}
if (!$uname) {
alert("请输入用户名");
return;
}
if (!$upwd) {
alert("请输入密码");
return;
}
if (!$email) {
alert("请输入邮箱");
return;
}
//if(!$gender){alert("请输入性别");return}
if (!$phone) {
alert("请输入电话");
return;
}
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
d1.innerHTML = r;
}
};
xhr.open("put", "/pro/v1/update", true);
var formdata = `uid=${$uid}&uname=${$uname}&upwd=${$upwd}&email=${$email}&phone=${$phone}&gender=${$gender}`;
xhr.setRequestHeader(
"Content-Type",
"application/x-www-form-urlencoded"
);
xhr.send(formdata);
}
</script>
</head>
<body>
<h1>修改用户</h1>
编号<input type="text" id="uid" /><br />
账户<input type="text" id="uname" /><br />
密码<input type="text" id="upwd" /><br />
邮箱<input type="text" id="email" /><br />
电话<input type="text" id="phone" /><br />
男<input type="radio" name="sex" checked id="s1" value="1" /> 女<input
type="radio"
name="sex"
id="s2"
value="0"
/><br />
<button onclick="show()">修改用户</button>
<div id="d1"></div>
</body>
</html>
//del
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<script type="text/javascript">
function show() {
var $uid = uid.value;
if (!$uid) {
alert("请输入正确的id");
return;
}
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
var r = xhr.responseText;
d1.innerHTML = r;
}
};
xhr.open("delete", `/pro/v1/del/${$uid}`, true);
xhr.send();
}
</script>
</head>
<body>
<input type="text" id="uid" />
<button onclick="show()">删除用户</button>
<div id="d1"></div>
</body>
</html>