NodeJs(08)——练习实战(与数据库交互增加数据&&分页查询显示在网页上&&删除指定数据&&修改指定数据)

要求

显示

增加

修改

点击“修改”跳转到修改页并且显示对应信息,
点击"删除"即可直接删除。

开始写代码…

[第一步⭐:初始化项目]

npm init -y

[第二步⭐⭐:创建项目结构]

[第三步⭐⭐⭐:安装模板]

npm install express

npm install art-template

npm install express-art-template

npm install mysql

npm install supervisor

[第四步⭐⭐⭐⭐:入口文件app.js]

//1、引入模块
var express=require('express');
var router=require('./routes/router.js')
//2、创建web服务器  app接收
var app=express();
//使用模板引擎
app.engine('html',require('express-art-template'));
app.use('/public',express.static('public'));    //将文件夹public设置为静态资源
//路由已经放到routes.js中
app.use(router);//使用
//4、启动服务listen(端口号,回调函数)
app.listen(9090,()=>{
    console.log('服务器开启 http://127.0.0.1:9090/');
})

[第五步⭐⭐⭐⭐⭐:数据库文件mysql.js]

/**
 * 访问mysql数据库
 */
var mysql=require('mysql');//引入模块
//创建连接
var conn=mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database: 'db8'//数据库名称
});
conn.connect();//开启连接
//导出
module.exports=conn;

[第六步:路由文件router.js(增删改查)]

本次练习要跳转的路径	地址
首页详情页	/
数据增加页	/addstu
执行增加并返回到详情页	/stu/create
执行删除并返回到详情页	/stu/del/:sid
执行修改前的查询并返回到修改页	/stu/selToupdate/:sid
执行修改并返回到详情页	/stu/update

var express=require('express');
var conn=require('../mysql.js');
var bodyParser = require('body-parser');
// 创建 application/x-www-form-urlencoded 编码解析
var urlencodedParser = bodyParser.urlencoded({ extended: false });
//建立一个路由容器
var router=express.Router();
//分页查询
router.get("/",(req,res)=>{
    var pagenum=2;//每页条数
    var page=0;
    if(req.query.page!=undefined){
        page=parseInt(req.query.page)-1;
    }
    console.log("page="+page);
    var sql="select * from students limit "+page*pagenum+","+pagenum;
    var sql2="select * from students";
    var count=0;
    conn.query(sql2,(err,result,fields)=>{
        if(result.length%pagenum!=0){
            count=parseInt(result.length/pagenum+1);
        }else{
            count=parseInt(result.length/pagenum);
        }
        
        console.log("长度="+count);
    })
    conn.query(sql,(err,result,fields)=>{
        if(err){
            return "失败";
        }
        console.log(result);
        var nums=new Array();
        for(var i=1;i<=count;i++){
            nums.push(i);
        }
        console.log("页码="+nums);
        res.render('index.html',{
            students: result,
            nums:nums,
            page:page
        });
    })
});
//跳转增加页
router.get("/addstu",(req,res)=>{
    res.render("post.html");
});
//删除
router.get("/stu/del/:sid",(req,res)=>{
    conn.query("delete from students where sid=?",req.params.sid,(err,result)=>{
        if(err){
            console.log("删除失败",err.message);
            return;
        }
        res.redirect("/");
    });
})
//修改前的查询
router.get("/stu/selToupdate/:sid",(req,res)=>{
    conn.query("select *from students where sid=?",req.params.sid,(err,result)=>{
        if(err){
            console.log("查询失败",err.message);
            return;
        }
        res.render("update.html",{
            stus:result
        });
    });
})

//修改
router.post("/stu/update",urlencodedParser,(req,res)=>{
    var sid=req.body.sid;
    var stuname=req.body.stuname;
    var stupwd=req.body.stupwd;
    var stuage=req.body.stuage;
    var stusex=req.body.stusex;
    conn.query("update students set stuname=?,stupwd=?,stuage=?,stusex=? where sid=?",[stuname,stupwd,stuage,stusex,sid],(err,result)=>{
        if(err){
            console.log("修改失败",err.message);
            return;
        }
        res.redirect("/");
    });
})
//增加
router.post("/stu/create",urlencodedParser,(req,res)=>{
    var stuname=req.body.stuname;
    var stupwd=req.body.stupwd;
    var stuage=req.body.stuage;
    var stusex=req.body.stusex;
    console.log(stuname,stupwd,stuage,stusex);
    conn.query("insert into students(stuname,stupwd,stuage,stusex)values (?,?,?,?)",[stuname,stupwd,stuage,stusex],(err,result)=>{
        if(err){
            console.log("插入失败",err.message);
            return;
        }
        res.redirect("/");
    });
})
//导出
module.exports=router;

[第六步⭐⭐⭐⭐⭐⭐:页面文件index.html&&post.html&&update.html]
index.html

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>laravel</title>
    <!-- Bootstrap CSS 文件 -->
    <link rel="stylesheet" href="../public/css/bootstrap.min.css">
    <!-- 首页单独样式 -->
    <link rel="stylesheet" href="../public/css/index.css">
</head>
<body>
<div class="navbar navbar-default">
    <div class="container">
        <div class="navbar-header">
            <a href="/" class="navbar-brand">首页</a>
             <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#menu" aria-expanded="false">
            <span class="sr-only">菜单折叠</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>

        </div>
        <div class="collapse navbar-collapse" id="menu">
          <ul class="nav navbar-nav">
           <li class="active"><a href="/">开发</a></li> 
            <li><a href="#">列表</a></li>
            <li><a href="#">头条</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">资讯 <span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">学习一</a></li>
                <li><a href="#">学习二</a></li>
                <li class="divider"></li>
                <li><a href="#">技术学习</a></li>
                <li class="divider"></li>
                <li><a href="#">jq学习</a></li>
              </ul>
            </li>
          </ul>
          <form class="navbar-form navbar-left">
            <div class="form-group">
              <input type="text" class="form-control" placeholder="Search...">
            </div>
            <button type="submit" class="btn btn-default">搜索</button>
          </form>
          <ul class="nav navbar-nav navbar-right">
            <li><a href="./login.html">登录</a></li>
            <li><a href="./register.html">注册</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">相关<span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">系列一</a></li>
                <li class="divider"></li>
                <li><a href="#">系列二</a></li>
              </ul>
            </li>
          </ul>
        </div>
    </div>
</div>
<!-- 头部 -->
<div class="jumbotron jump">
    <div class="container">
        <h2>玩转laravel-之首页</h2>

        <p> 玩转- laravel</p>
    </div>
</div>

<!-- 中间内容区局 -->
<div class="container">
    <div class="row">

        <!-- 左侧菜单区域   -->
        <div class="col-md-3">
            <div class="list-group">
                <a href="/" class="list-group-item active">学生列表</a>
                <a href="/addstu" class="list-group-item">新增学生</a>
            </div>
        </div>

        <!-- 右侧内容区域 -->
        <div class="col-md-9">

            <!-- 成功提示框 -->
            <div class="alert alert-success alert-dismissible" role="alert">
                <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
                <strong>成功!</strong> 操作成功提示！
            </div>

            <!-- 失败提示框 -->
            <div class="alert alert-danger alert-dismissible" role="alert">
                <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
                <strong>失败!</strong> 操作失败提示！
            </div>

            <!-- 自定义内容区域 -->
            <div class="panel panel-default">
                <div class="panel-heading">学生列表</div>
                <table class="table table-striped table-hover table-responsive">
                    <thead>
                    <tr>
                        <th>ID</th>
                        <th>姓名</th>
                        <th>年龄</th>
                        <th>性别</th>
                        <th>添加时间</th>
                        <th width="120">操作</th>
                    </tr>
                    </thead>
                    <tbody>
                        {{each students stu}}
                        <tr>
                            <td>{{stu.sid}}</td>
                            <td>{{stu.stuname}}</td>
                            <td>{{stu.stuage}}</td>
                            <td>{{stu.stusex}}</td>
                            <td>{{stu.addtime}}</td>
                            <td>
                                <a href="#">修改</a>
                                <a href="#">删除</a>
                            </td>
                        </tr>
                        {{/each}}
                    </tbody>
                </table>
            </div>

            <!-- 分页  -->
            <div>
                <ul class="pagination pull-right">
                    <li>
                        <a href="#" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    {{each nums num index}}
                    <li class="{{index==page ? 'active' :''}}"><a href="/?page={{index+1}}">{{index+1}}</a></li>
                    {{/each}}
                    <li>
                        <a href="#" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                </ul>
            </div>

        </div>
    </div>
</div>

<!-- 尾部 -->
<div class="jumbotron">
    <div class="container">
        <span>  @2017 laravel</span>
    </div>
</div>

<!-- jQuery 文件 -->
<script src="../public/js/jquery.min.js"></script>
<!-- Bootstrap JavaScript 文件 -->
<script src="../public/js/bootstrap.min.js"></script>

</body>
</html>

post.html增加表单页面

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>laravel</title>
    <!-- Bootstrap CSS 文件 -->
    <link rel="stylesheet" href="../public/css/bootstrap.min.css">

    <link rel="stylesheet" href="../public/css/form.css">
</head>
<body>
<div class="navbar navbar-default">
    <div class="container">
        <div class="navbar-header">
            <a href="/" class="navbar-brand">首页</a>
             <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#menu" aria-expanded="false">
            <span class="sr-only">菜单折叠</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>

        </div>
        <div class="collapse navbar-collapse" id="menu"> 
          <ul class="nav navbar-nav">
            <li class="active"><a href="/">开发</a></li>
            <li><a href="#">列表</a></li>
            <li><a href="#">头条</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">资讯 <span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">学习一</a></li>
                <li><a href="#">学习二</a></li>
                <li class="divider"></li>
                <li><a href="#">技术学习</a></li>
                <li class="divider"></li>
                <li><a href="#">jq学习</a></li>
              </ul>
            </li>
          </ul>
          <form class="navbar-form navbar-left">
            <div class="form-group">
              <input type="text" class="form-control" placeholder="Search...">
            </div>
            <button type="submit" class="btn btn-default">搜索</button>
          </form>
          <ul class="nav navbar-nav navbar-right">
            <li><a href="./login.html">登录</a></li>
            <li><a href="./register.html">注册</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">相关<span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">系列一</a></li>
                <li class="divider"></li>
                <li><a href="#">系列二</a></li>
              </ul>
            </li>
          </ul>
        </div>
    </div>
</div>
<!-- 头部 -->
<div class="jumbotron jump">
    <div class="container">
        <h2>玩转laravel</h2>

        <p> 玩转- laravel</p>
    </div>
</div>

<!-- 中间内容区局 -->
<div class="container">
    <div class="row">

        <!-- 左侧菜单区域   -->
        <div class="col-md-3">
            <div class="list-group">
                <a href="/" class="list-group-item ">学生列表</a>
                <a href="/addstu" class="list-group-item active">新增学生</a>
            </div>
        </div>

        <!-- 右侧内容区域 -->
        <div class="col-md-9">

            <!-- 所有的错误提示 -->
            <div class="alert alert-danger">
                <ul>
                    <li>姓名不能为空</li>
                    <li>年龄只能为整数</li>
                    <li>请选择性别</li>
                </ul>
            </div>

            <!-- 自定义内容区域 -->
            <div class="panel panel-default">
                <div class="panel-heading">新增学生</div>
				<div class="panel-body">
					<form class="form-horizontal" method="post" action="/stu/create">
						<div class="form-group">
							<label for="name" class="col-sm-2 control-label">姓名</label>
							<div class="col-sm-5">
								<input type="text" class="form-control" name="stuname" placeholder="请输入学生姓名">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">姓名不能为空</p>
							</div>
						</div>
						<div class="form-group">
							<label for="age" class="col-sm-2 control-label">密码</label>

							<div class="col-sm-5">
								<input type="text" class="form-control" name="stupwd" placeholder="请输入学生密码">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">密码不能为空</p>
							</div>
						</div>
						<div class="form-group">
							<label for="age" class="col-sm-2 control-label">年龄</label>

							<div class="col-sm-5">
								<input type="text" class="form-control" name="stuage" placeholder="请输入学生年龄">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">年龄只能为整数</p>
							</div>
						</div>
						<div class="form-group">
							<label class="col-sm-2 control-label">性别</label>

							<div class="col-sm-5">
								<label class="radio-inline">
									<input type="radio" name="stusex" value="男"> 男
								</label>
								<label class="radio-inline">
									<input type="radio" name="stusex" value="女"> 女
								</label>
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">请选择性别</p>
							</div>
						</div>
						<div class="form-group">
							<div class="col-sm-offset-2 col-sm-10">
								<button type="submit" class="btn btn-primary">提交</button>
							</div>
						</div>
					</form>
				</div>
            </div>

        </div>
    </div>
</div>

<!-- 尾部 -->
<div class="jumbotron">
    <div class="container">
        <span>  @2017 laravel</span>
    </div>
</div>

<!-- jQuery 文件 -->
<script src="../public/js/jquery.min.js"></script>
<!-- Bootstrap JavaScript 文件 -->
<script src="./public/js/bootstrap.min.js"></script>

</body>
</html>

update.html修改页面

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>laravel---修改信息</title>
    <!-- Bootstrap CSS 文件 -->
    <link rel="stylesheet" href="/public/css/bootstrap.min.css">

    <link rel="stylesheet" href="/public/css/form.css">
</head>
<body>
<div class="navbar navbar-default">
    <div class="container">
        <div class="navbar-header">
            <a href="/" class="navbar-brand">首页</a>
             <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#menu" aria-expanded="false">
            <span class="sr-only">菜单折叠</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>

        </div>
        <div class="collapse navbar-collapse" id="menu"> 
          <ul class="nav navbar-nav">
            <li class="active"><a href="/">开发</a></li>
            <li><a href="#">列表</a></li>
            <li><a href="#">头条</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">资讯 <span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">学习一</a></li>
                <li><a href="#">学习二</a></li>
                <li class="divider"></li>
                <li><a href="#">技术学习</a></li>
                <li class="divider"></li>
                <li><a href="#">jq学习</a></li>
              </ul>
            </li>
          </ul>
          <form class="navbar-form navbar-left">
            <div class="form-group">
              <input type="text" class="form-control" placeholder="Search...">
            </div>
            <button type="submit" class="btn btn-default">搜索</button>
          </form>
          <ul class="nav navbar-nav navbar-right">
            <li><a href="./login.html">登录</a></li>
            <li><a href="./register.html">注册</a></li>
            <li class="dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown">相关<span class="caret"></span></a>
              <ul class="dropdown-menu">
                <li><a href="#">系列一</a></li>
                <li class="divider"></li>
                <li><a href="#">系列二</a></li>
              </ul>
            </li>
          </ul>
        </div>
    </div>
</div>
<!-- 头部 -->
<div class="jumbotron jump">
    <div class="container">
        <h2>玩转laravel</h2>

        <p> 玩转- laravel---修改页</p>
    </div>
</div>

<!-- 中间内容区局 -->
<div class="container">
    <div class="row">

        <!-- 左侧菜单区域   -->
        <div class="col-md-3">
            <div class="list-group">
                <a href="/" class="list-group-item ">学生列表</a>
                <a href="/addstu" class="list-group-item">新增学生</a>
            </div>
        </div>

        <!-- 右侧内容区域 -->
        <div class="col-md-9">

            <!-- 所有的错误提示 -->
            <div class="alert alert-danger">
                <ul>
                    <li>姓名不能为空</li>
                    <li>年龄只能为整数</li>
                    <li>请选择性别</li>
                </ul>
            </div>

            <!-- 自定义内容区域 -->
            <div class="panel panel-default">
                <div class="panel-heading">修改学生</div>
				<div class="panel-body">
          {{each stus s}}
					<form class="form-horizontal" method="post" action="/stu/update">
						<div class="form-group">
							<label for="name" class="col-sm-2 control-label">姓名</label>
							<div class="col-sm-5">
                <input type="hidden" name="sid" value="{{s.sid}}" />
								<input type="text" class="form-control" name="stuname" placeholder="请输入学生姓名" value="{{s.stuname}}">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">姓名不能为空</p>
							</div>
						</div>
						<div class="form-group">
							<label for="age" class="col-sm-2 control-label">密码</label>

							<div class="col-sm-5">
								<input type="text" class="form-control" name="stupwd" value="{{s.stupwd}}" placeholder="请输入学生密码">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">密码不能为空</p>
							</div>
						</div>
						<div class="form-group">
							<label for="age" class="col-sm-2 control-label">年龄</label>

							<div class="col-sm-5">
								<input type="text" class="form-control" name="stuage" value="{{s.stuage}}" placeholder="请输入学生年龄">
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">年龄只能为整数</p>
							</div>
						</div>
						<div class="form-group">
							<label class="col-sm-2 control-label">性别</label>
							<div class="col-sm-5">
								<label class="radio-inline">
									<input type="radio" name="stusex" value="男" {{s.stusex=='男' ? 'checked' : '' }}> 男
								</label>
								<label class="radio-inline">
									<input type="radio" name="stusex" value="女" {{s.stusex== '女' ? 'checked' : '' }}> 女
								</label>
							</div>
							<div class="col-sm-5">
								<p class="form-control-static text-danger">请选择性别</p>
							</div>
						</div>
						<div class="form-group">
							<div class="col-sm-offset-2 col-sm-10">
								<button type="submit" class="btn btn-primary">提交</button>
							</div>
						</div>
					</form>
          {{/each}}
				</div>
            </div>

        </div>
    </div>
</div>

<!-- 尾部 -->
<div class="jumbotron">
    <div class="container">
        <span>  @2017 laravel</span>
    </div>
</div>

<!-- jQuery 文件 -->
<script src="/public/js/jquery.min.js"></script>
<!-- Bootstrap JavaScript 文件 -->
<script src="/public/js/bootstrap.min.js"></script>

</body>
</html>

由于样式style和js众多，所以未放在文章中,如需要可评论问我获取
[第七步⭐⭐⭐⭐⭐⭐⭐:运行入口文件app.js]

supervisor app.js

最后这个练习就完成了！！

[🚀🚀🚀]爱学习的码友微信公众号关注:碰磕的学习圈