1.2 操作流程
?
1.2.1 下载mysql模块
npm i mysql 1.2.2 在后端js文件中引入mysql
const mysql = require('mysql')1.2.3 先创建mysql数据库的配置连接
let connectObj = mysql.createConnection({
host:'主机名',
user:'用户名',
password:'密码'
port:'端口号',
database:'要操作哪个数据库'
})1.2.4 使用connectObj.connect()连接msyql数据库
connectObj.connect()方法来连接mysql数据库
1.2.5使用connectObj.query
使用query方法执行sql语句
connectObj.query(sqlStr,(err,results)=>{ })服务端的代码如下：
const experss = require('express');
const path = require('path');
//第一步：引入mysql包：
const mysql = require('mysql');
const app = experss();
app.listen(3000, () => {
console.log(`web服务器工作在3000端口`);
});
//设置ejs:
app.set('view engine', 'ejs'); //设置模板引擎为ejs
app.set('views', path.join(__dirname, 'moban'));
app.engine('html', require('ejs').__express);
//第二步：创建mysql连接
const mysqlObj = mysql.createConnection({ host: '127.0.0.1', port: 3306, user: 'root', password: 'root', database: 'mydemo' });
//第三步：连接mysql数据库
mysqlObj.connect();
//显示查询界面：
app.get('/', (req, res) => {
res.render('search.html');
});
//处理用户的查询：
app.get('/find', (req, res) => {
let { xingming = '' } = req.query;
if (xingming == '') {
res.send(`<script>alert('查询关键词不能为空');location.href='/';</script>`);
return;
}
let usr = '小李';
let ages = 19;
let emails = 'li@qq.com';
// let sql = `select * from xsb where xm like '%${xingming}%'`;
// let sql = `select * from xsb where xm like ?`;
let sql = `insert into student(xingming,age,email)values(?,?,?)`;
//mysqlObj.query(sql, (err, data) => {
// mysqlObj.query(sql, [`%${xingming}%`], (err, data) => {
mysqlObj.query(sql, [usr, ages, emails], (err, data) => {
// console.log(err, data);
// res.send('OK');
if (err) { //sql执行失败
res.send(`sql执行失败`);
} else { //sql执行成功
res.render('userlist.html', { data });
}
});
});search.html文件代码如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
<h2>查询学生</h2>
<form action="/find" method="get">
<div>
<input type="text" name="xingming">
<button>查询</button>
</div>
</form>
</body>
</html>userlist.html文件代码如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
<table>
<tr>
<td>序号</td>
<td>姓名</td>
<td>性别</td>
<td>所在系</td>
</tr>
<% for(let i = 0;i<data.length;i++){ %>
<tr>
<td>
<%=i %>
</td>
<td>
<%=data[i].xm %>
</td>
<td>
<%=data[i].sex %>
</td>
<td>
<%=data[i].szx %>
</td>
</tr>
<% } %>
</table>
</body>
</html>