HTML+CSS实训——Day13——学习MySQL

前言

已经过去三周了,现在是第四周,之前所有的数据都是在本地的,还没有学到数据库,这周开始使用数据库了。
我自用的是navicat

连接服务器

我们新建一个连接,写上主机和端口号,再写上用户名和密码就可以了。

server.js

修改登录与注册的信息,要先安装mysql的库

npm i mysql
var mysql = require('mysql')
// 获取数据库连接对象
var pool = mysql.createPool({
  connectionLimit: 15, //连接池大小,可省略
  host: 'yourip', //数据库服务器的主机地址,可省略
  port: yourport, //数据库服务器的端口号,可省略
  user: 'youruser', //登录数据库的用户名,不能省略!!
  password: 'yourpwd', //登录数据库的用户密码,默认值是''
  database: 'yourdb' //要打开哪个数据库,不能省略
})


app.post('/login', (req, res) => {
  // 获取请求参数,因为是post请求,所以用req.body
  var phone = req.body.phone
  var upwd = req.body.upwd
  // 连接数据库做查询操作
  // 要执行的sql命令,?是占位符,根据手机号和密码查询用户
  var sql = 'SELECT * FROM zd_user WHERE phone=? AND upwd=?'
  // 通过数据库链接对象执行sql命令
  pool.query(sql, [phone, upwd], (err, result) => {
    // err 是报错
    console.log(err)
    // result 是执行sql命令的结果
    console.log(result)
    if (result.length == 1) {
      res.send({ code: 200, msg: '登录成功', uid: result[0].uid })
    } else {
      res.send({ code: 201, msg: '账号或密码不正确' })
    }
  })
})

// 数据库:用来存储信息的仓库
// SQLserver  Oracle  MySQL
app.post('/register', (req, res) => {
  var phone = req.body.phone
  var upwd = req.body.upwd
  // 现根据手机查询用户,判断该手机号是否注册过
  var sql = 'SELECT * FROM zd_user WHERE phone=?'
  pool.query(sql, [phone], (err, result) => {
    if (result.length >= 1) {
      res.send({ code: 201, msg: '该手机号已经被注册' })
    } else {
      // 执行sql命令,把用户信息插入到数据库
      var sql =
        'INSERT INTO zd_user VALUES(NULL,?,NULL,?,"img/avatar/default.jpg",NULL)'
      pool.query(sql, [phone, upwd], (err, result) => {
        res.send({ code: 200, msg: '注册成功' })
      })
    }
  })
})

小海豚变成绿色就代表连接成功了

reg.html

再写一个注册的界面

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width,initial-scale=1,minimum-scale=1,maximum-scale=1,user-scalable=no" />

  <link rel="stylesheet" href="font-awesome-4.7.0/css/font-awesome.css">
  <script>
    document.documentElement.style.fontSize = window.screen.width / 10 + 'px'
  </script>
  <!-- 引入vue文件 -->
  <script src="./js/js/vue.js"></script>
  <title></title>
</head>

<body>
  <div id="app">
    phone:<input type="text" v-model="phone"> <br>
    upwd:<input type="text" v-model="upwd"> <br>
    <button @click="reg()">注册</button>
  </div>

  <script>
    var vm = new Vue({
      el: "#app",
      data: {
        phone: '',
        upwd: ''
      },
      methods: {
        reg() {
          fetch('http://127.0.0.1:3000/register',
            {
              method: 'POST',
              body: JSON.stringify({ phone: this.phone, upwd: this.upwd }),
              headers: { 'Content-Type': 'application/json' }
            }).then(res => res.json()).then(res => {
              console.log(res)
            })
        }
      },
    })
  </script>
</body>

</html>

collect.html

添加一个收藏的界面,可以收藏你选中的歌曲

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width,initial-scale=1,minimum-scale=1,maximum-scale=1,user-scalable=no" />
  <link rel="stylesheet" href="font-awesome-4.7.0/css/font-awesome.css">
  <script>
    document.documentElement.style.fontSize = window.screen.width / 10 + 'px'
  </script>
  <!-- 引入vue文件 -->
  <script src="./js/js/vue.js"></script>
  <title></title>
</head>

<body>
  <div id="app">
    <div v-for="(item,i) of collect">
      {{ i+1 }} => {{ item.title }} <button @click="rmCollect(item.sid)">取消收藏</button>
    </div>
  </div>

  <script>
    var vm = new Vue({
      el: "#app",
      data: {
        collect: []
      },
      methods: {
        rmCollect(sid) {
          var uid = localStorage.getItem('uid')
          fetch('http://180.76.143.181:3000/collect/remove?uid=' + uid + '&sid=' + sid)
            .then(res => res.json()).then(res => {
              console.log(res)
              // 刷新页面
              location.reload()
            })
        }
      },
      mounted() {
        var uid = localStorage.getItem('uid')
        fetch('http://180.76.143.181:3000/collect/list?uid=' + uid).then(res => res.json()).then(res => {
          console.log(res)
          this.collect = res.data
        })
      },
    })
  </script>
</body>

</html>

order.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>order</title>
    <link rel="stylesheet" href="font-awesome-4.7.0/css/font-awesome.css">
    <link rel="stylesheet" href="css/order.css">
    <script src="./js/js/vue.js"></script>
    <script>
        document.documentElement.style.fontSize = window.screen.width / 10 + 'px'
    </script>
</head>

<body>

    <body>
        <div id="app">

            <div class="top-bar">
                <i class="fa fa-chevron-left"></i>
                <span>排行榜</span>
                <img src="./img/topbar-img.png" alt="">
            </div>

            <div class="bangdan">
                <div class="title">榜单推荐</div>
                <div class="box">
                    <div class="item" v-for="item of recommend">
                        <div>
                            <span>{{ item.lname }}榜</span>
                            <span>每月更新</span>
                        </div>
                        <span>硬地{{ item.lname }}音乐榜</span>
                    </div>
            </div>
                                <div class="guanfang">
                        <div class="title">官方榜</div>
                        <div class="box">
                            <div class="item" v-for="item of official">
                                <div class="left">
                                    <span>{{ item.lname }}榜</span>
                                    <span>每天更新</span>
                                </div>
                                <div class="right">
                                    <div v-for="(song,i) of item.songs">
                                        {{ i+1 }}. {{ song.title }}...
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

        </div>
        <script>
            var vm = new Vue({
                el: "#app",
                data: {
                    recommend: [],
                    official: []
                },
                mounted() {
                    fetch('http://180.76.143.181:3000/list/list').then(res => res.json()).then(res => {
                        console.log(res)
                        this.recommend = res.data.recommend
                        this.official = res.data.official
                    })
                },
            })
        </script>
    </body>

</html>

我们把排行榜和收藏都用到了服务器