mysql 常用语法 cookie session token redis 用法 mysql nginx的代理 让前后端同域

SHOW TABLES;
USE blog;
-- ctrl+shift+r 删除注释
-- ctrl+shift+c 注释
-- 添加 内容 insert into 表()values()
-- insert into users(us,ps,realname)values('lisi','123456','lisi')
-- 查询 select * from 表
-- select * from users;-- 
-- select*from users where us="lisi" and ps="223"
-- like 查询包含
-- select*from users where ps like '%2%' 

-- 排序  ORDER BY id  默认正序  ORDER BY id desc; 倒叙
-- select*from users where ps like '%2%' order by id;
-- SELECT*FROM users WHERE ps LIKE '%2%' ORDER BY id desc;

-- 更新
-- update users set realname='lisi2' where us='lisi'

-- 删除
-- delete from users where us='lisi'

-- state 1 存在  state=0 则为删除



-- update users set state='0' where us='lisi' ; -- 软删除
-- 查询<> '0' 不等于0

SELECT * FROM users WHERE state <>'0'

Sqylog 中使用

 

 

 

cookie

• 存储在浏览器的一段字符串(最大5kb)
• 跨越不共享
• 格式 k1=v1;k2=v2;k3=v3;因此可以存储结构化数据;
• 每次发送http请求,会讲请求域的cookie一起发送给server
• server可以修改cookie并返回给浏览器
• 浏览器中也可以通过JS修改cookie(有限制)

 

 server端nodejs操作cookie

• 查看cookie
• 修改cookie
• 实现登录验证

解析cookie

  //解析cookie
  req.cookie = {};
  const cookieStr = req.headers.cookie || ""; //k1=v1;k2=v2
    cookieStr.split(";").forEach((item) => {
      if(!item){
        return
      }
      var arr = item.split("=");
      var key = arr[0];
      var val = arr[1];
      req.cookie[key] = val;
    });
    console.log('cookie',req.cookie)

修改cookie

    //操作cookie  path 路径设置为根路径 所有的网站都会生效  httpOnly 前端不可以修改
        res.setHeader(
          "Set-Cookie",
          `username=${data.us};path='/';httpOnly;expires=${getCookieExpires()}`
        );
//封装cookie过期时间
const getCookieExpires = () => {
  const time = new Date().getTime() + 24 * 60 * 60 * 1000;
  console.log("time", time);
  return time;
};

 

cookie 的问题  会暴露信息

session  解决  cookie中存储userid session中存储username;

 

 

    //存储Session
  let SESSION_DATA={};
    
  //解析Session
  let needSetCookie = false;
  let userId = req.cookie.userid;
  if (userId) {
    if (!SESSION_DATA[userId]) {
          SESSION_DATA[userId];
    }
  } else {
    needSetCookie = true;
    userId = `${new Date()}_${Math.random()}`;
    SESSION_DATA[userId] = {};
  }

     操作session

    req.session.us=data.us;
     req.session.realname=data.realname;

    使用session
    if (req.session.us) {
      return Promise.resolve(
        new SuccessModule({
          session: req.session.us,
        })
      );
    }    

 

安装redis

 

 

redis 启动

redis-cli.exe

输入密码

127.0.0.1:6379> auth "yourpassword"

 设置值

set myKey abc

拿出值

get myKey

 

 

 

 

mysql  

npm i mysql --save

//链接数据库小demo
var mysql = require("mysql");
//创建链接对象
var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "blog",
  port:3306
});

//开始链接
connection.connect();

//执行sql语句
// const sql='select * from users;'
// const sql=`update users set realname='李四' where us='lisi';`
const sql=`insert into blogs(title,content,createtime,author) values('标题C','内容C',${new Date().getTime()},'wanglaowu')`
connection.query(sql, function (error, results) {
  if (error) throw error;
  console.log("The solution is: ", results);
});


//关闭链接
connection.end();

redis 

npm i  redis --save@3.02

const redis = require("redis");

//创建客户端
const redisClient = redis.createClient({
  port: 6379,
  host: "127.0.0.1",
  auth_pass: 123456,
});

redisClient.on("error", (err) => {
  console.log("====================================");
  console.log(err);
  console.log("====================================");
});

//测试
redisClient.set("myname", "zhangsan2", redis.print);

redisClient.get("myname", (err, val) => {
  if (err) {
    console.error(err);
    return;
  }

  console.log("val", val);

  //退出
  redisClient.quit();
});

 

mysql,redis confi

//封装mysql
let env = process.env.NODE_ENV || "dev";
//配置
let MYSQL_CONF, REDIS_CONF;

if (env === "dev") {
  //mysql
  MYSQL_CONF = {
    host: "127.0.0.1",
    user: "root",
    password: "123456",
    database: "blog",
    port: 3306,
  };
  //redis
  REDIS_CONF = {
    port: 6379,
    host: "127.0.0.1",
    auth_pass: 123456,
  };
}

if (env == "production") {
  //mysql
  MYSQL_CONF = {
    host: "127.0.0.1",
    user: "root",
    password: "123456",
    database: "blog",
    port: 3306,
  };
  //redis
  REDIS_CONF = {
    port: 6379,
    host: "127.0.0.1",
    auth_pass: 123456,
  };
}
module.exports = {
  MYSQL_CONF,
  REDIS_CONF
};

 

mysql 封装

//链接数据库小demo
var mysql = require("mysql");
const {MYSQL_CONF} =require('../db/db');
//创建链接对象
var connection = mysql.createConnection(MYSQL_CONF);

//开始链接
connection.connect();

//执行sql语句
// const sql='select * from users;'
// const sql=`update users set realname='李四' where us='lisi';`
// const sql=`insert into blogs(title,content,createtime,author) values('标题C','内容C',${new Date().getTime()},'wanglaowu')`
// connection.query(sql, function (error, results) {
//   if (error) throw error;
//   console.log("The solution is: ", results);
// });
//统一执行sql 函数
function exec(sql){
    const promise=new Promise((resolve,reject)=>{
        connection.query(sql,function(error,results){
            if(error){
                reject(error)
            }else{
                resolve(results)
            }
        })
    })
    return promise;
}

module.exports={
    exec
}

redis 封装

const { REDIS_CONF } = require("../conf/db");
const redis = require("redis");

//创建客户端
const redisClient = redis.createClient(REDIS_CONF);

redisClient.on("error", (err) => {
  console.log("====================================");
  console.log(err);
  console.log("====================================");
});

//测试
function set(key, value) {
  if (typeof value === "object") {
    value = JSON.stringify(value);
  }
  redisClient.set(key, value, redis.print);
}

function get(key) {
  const promise = new Promise((resolve, reject) => {
    redisClient.get(key, (err, val) => {
      if (err) {
        reject(err);
        return;
      }
      if (key == null) {
        resolve(null);
        return;
      }

      try {
        resolve(JSON.parse(val));
      } catch (e) {
          resolve(e)
      }
      resolve(val);
    });
  });
  return promise;
}

module.exports = {
  set,
  get,
};

 

 

 

 

 nginx 下载

 

 

 

 

 

 nginx confi  配置 参考

 
#user  nobody;
# cpu 几核  不写默认一个进程
worker_processes  4;

#error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;

#pid        logs/nginx.pid;


events {
    worker_connections  1024;
}


http {
    include       mime.types;
    default_type  application/octet-stream;

    #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
    #                  '$status $body_bytes_sent "$http_referer" '
    #                  '"$http_user_agent" "$http_x_forwarded_for"';

    #access_log  logs/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    #gzip  on;

    server {
    #服务器监听断后号
        listen       8080;
        server_name  localhost;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;
    
      # 默认代理 / 到根目录的html
       # location / {
         #   root   html;
         #   index  index.html index.htm;
       # }
      
      #代理服务器8080 /   转发到 8081端口
       location / {
    proxy_pass http://localhost:8081;
       }
 #代理服务器8080 /   转发到 8081端口
      location /api/ {
    proxy_pass     http://localhost:8000;
    #代理完后host 不一样 了 要把host 传递过去
    proxy_set_header Host $host;
       }
        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;

    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;

    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;

    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}

}