mysql+socket.io

笔记

1.NodeJS——数据库
2.数据交互——Ajax跨域
3.WebSocket

--------------------------------------------------------------------------------

导入数据——必须先建库

--------------------------------------------------------------------------------

1.连接
  let db=mysql.createConnection({host, port, user, password, database});
  ?

2.查询
  db.query('干啥', (err, data)=>{});

--------------------------------------------------------------------------------

SQL：
4大查询
1.增   INSERT
  INSERT INTO 表 (字段列表) VALUES(值列表)

  INSERT INTO user_table (ID, name, gender, chinese, math, english) VALUES(0, 'blue', '男', 35, 18, 29);

2.删   DELETE
  DELETE FROM 表 WHERE 条件

  DELETE FROM user_table WHERE ID=3;

3.改   UPDATE
  UPDATE 表 SET 字段=值, 字段2=值2, ... WHERE 条件

  UPDATE user_table SET chinese=100 WHERE ID=2;

4.查   SELECT
  SELECT 字段列表 FROM 表 WHERE 条件

  SELECT name, gender FROM user_table WHERE ID=2;

--------------------------------------------------------------------------------

1.node中mysql写法
  let db=mysql.createConnection({配置});
  let db=mysql.createPool({配置});

  db.query(sql, (err, data)=>{});

2.基础SQL语句
  增删改查

--------------------------------------------------------------------------------

用户注册、登陆：
1.数据库结构（数据字典）
2.接口格式（接口文档）

--------------------------------------------------------------------------------

1.数据库结构
2.接口
注册：
  /reg?user=xxx&pass=xxx
  =>{err: 0, msg: '原因'}

登陆：
  /login?user=xxx&pass=xxx
  =>{err: 0, msg: '原因'}

--------------------------------------------------------------------------------

RESTful

--------------------------------------------------------------------------------

WebSocket：
1.双向通信
2.自动跨域
3.性能高

--------------------------------------------------------------------------------

socket.io

--------------------------------------------------------------------------------

WebSocket不是独立使用

mysql.js

const mysql=require('mysql');

//连接池
let db=mysql.createPool({host: 'localhost', user: 'root', password: '', port: 3309, database: '20180127'});

db.query(`INSERT INTO student_table (ID, name, gender, chinese, math, english) VALUES(0, '小明', '男', 98, 5, 3);`, (err, data)=>{
  if(err){
    console.log('错了', err);
  }else{
    console.log(data);
  }
});

server.js

const http=require('http');
const mysql=require('mysql');
const fs=require('fs');
const url=require('url');
const zlib=require('zlib');
const crypto=require('crypto');

const _key='sadfslekrtuew5iutoselgdtjiypoydse4ufhs.edtyo;s8te4arfeliawkfhtsie5tlfia;sefdshroiupeoutwyeli5gurse;ihf';

function md5(str){
  let obj=crypto.createHash('md5');

  obj.update(str);

  return obj.digest('hex');
}

function md5_2(str){
  return md5(md5(str)+_key);
}

let db=mysql.createPool({host: 'localhost', port: 3309, user: 'root', password: '', database: '20180127'});

let server=http.createServer((req, res)=>{
  let {pathname, query}=url.parse(req.url, true);
  let {user, pass}=query;

  switch(pathname){
    //接口
    case '/reg':
      //校验
      if(!user){
        res.write('{"err": 1, "msg": "username can\'t be null"}');
        res.end();
      }else if(!pass){
        res.write('{"err": 1, "msg": "password can\'t be null"}');
        res.end();
      }else if(!/^\w{4,16}$/.test(user)){
        res.write('{"err": 1, "msg": "username is invaild"}');
        res.end();
      }else if(/['|"]/.test(pass)){
        res.write('{"err": 1, "msg": "password is invaild"}');
        res.end();
      }else{
        db.query(`SELECT * FROM user_table WHERE username='${user}'`, (err, data)=>{
          if(err){
            res.write('{"err": 1, "msg": "database error"}');
            res.end();
          }else if(data.length>0){
            res.write('{"err": 1, "msg": "this username exsits"}');
            res.end();
          }else{
            db.query(`INSERT INTO user_table (ID,username,password) VALUES(0,'${user}','${md5_2(pass)}')`, (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':
      //校验
      if(!user){
        res.write('{"err": 1, "msg": "username can\'t be null"}');
        res.end();
      }else if(!pass){
        res.write('{"err": 1, "msg": "password can\'t be null"}');
        res.end();
      }else if(!/^\w{4,16}$/.test(user)){
        res.write('{"err": 1, "msg": "username is invaild"}');
        res.end();
      }else if(/['|"]/.test(pass)){
        res.write('{"err": 1, "msg": "password is invaild"}');
        res.end();
      }else{
        db.query(`SELECT * FROM user_table WHERE username='${user}'`, (err, data)=>{
          if(err){
            res.write('{"err": 1, "msg": "database error"}');
            res.end();
          }else if(data.length==0){
            res.write('{"err": 1, "msg": "no this user"}');
            res.end();
          }else if(data[0].password!=md5_2(pass)){
            res.write('{"err": 1, "msg": "username or password is incorrect"}');
            res.end();
          }else{
            res.write('{"err": 0, "msg": "success"}');
            res.end();
          }
        });
      }
      break;
    default:
      //缓存      TODO
      //静态文件
      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();
      });
  }
});
server.listen(8080);

1.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>
    <script src="jquery.js" charset="utf-8"></script>
    <script>
    $(function (){
      //注册
      $('#btn_reg').click(function (){
        $.ajax({
          url: '/reg',
          data: {user: $('#user').val(),pass: $('#pass').val()},
          dataType: 'json',
          success(json){
            if(json.err){
              alert('注册失败'+json.msg);
            }else{
              alert('注册成功');
            }
          },
          error(err){
            alert('失败');
          }
        });
      });

      //登陆
      $('#btn_login').click(function (){
        $.ajax({
          url: '/login',
          data: {user: $('#user').val(),pass: $('#pass').val()},
          dataType: 'json',
          success(json){
            if(json.err){
              alert('登陆失败'+json.msg);
            }else{
              alert('登陆成功');
            }
          },
          error(err){
            alert('失败');
          }
        });
      });
    });
    </script>
  </head>
  <body>
    用户：<input type="text" id="user" /><br>
    密码：<input type="password" id="pass" /><br>
    <input type="button" value="注册" id="btn_reg">
    <input type="button" value="登陆" id="btn_login">
  </body>
</html>

socket.io

 

1.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>
    <script src="http://localhost:8080/socket.io/socket.io.js" charset="utf-8"></script>
    <script>
    let sock=io.connect('ws://localhost:8080/');

    //sock.emit
    //sock.on

    //sock.emit('aaa', 12, 5, 8);

    sock.on('t', function (ts){
      console.log(ts);
    });
    </script>
  </head>
  <body>

  </body>
</html>

ws_server.js

const http=require('http');
const io=require('socket.io');

let httpServer=http.createServer();
httpServer.listen(8080);

let wsServer=io.listen(httpServer);
wsServer.on('connection', sock=>{
  //sock.emit   发送
  //sock.on     接收

  /*sock.on('aaa', function (a, b, c){
    console.log(a, b, c);
  });*/

  setInterval(function (){
    sock.emit('t', new Date().getTime());
  }, 1000);
});