server项目笔记

git提交规范

refactor:重构；
fix:修改bug；
doc:文档
lint:

mgsql

 -- 创建表
create table admin (id int primary key unique AUTO_INCREMENT,user_id int)

-- 添加表
alter table admin add status int default 0

-- 修改表
alter table admin  change status status int default 1

-- 删除表
ALTER TABLE user DROP admin

-- 新增行
insert into admin (user_id) values(2)

-- 删除行
delete from admin where id = 1

-- 编辑行
update admin SET status = 0 where user_id = 2

-- 使用数据库
use `koa2-weibo`;

-- 插入一行或多行
INSERT INTO `koa2-weibo`.`user`( `username`, `password`, `nickname`) VALUES ('李良荣', '19991212', '湫')
INSERT INTO `koa2-weibo`.`user` ( `username`, `password`, `nickname`) VALUES ('zhangsan', '123456', '张三'),('lisi', '123456', '李四')

-- 编辑行
update user set status = 1 WHERE (id = 2 or id = 1)
update user set nickname = '王五' ,username = '王五' WHERE id = 2

-- 查询
select * from blog where content like '%清明%' order by id desc

--连表查询
select * from blog inner join user on blog.user_id = user.id where user.id = 1

select blog.* ,username,nickname from blog inner join user on blog.user_id = user.id where user_id = 1
-- 删除
delete from blog where  title = '测试' 

ORM模型（sequelize）

基础配置

var Sequelize = require("sequelize");

const dbConfig = {
  database: "koa2-weibo",
  username: "root",
  password: "123456",
  dialect: "mysql",
  host: "127.0.0.1",
};

const sequelize = new Sequelize(
  dbConfig.database,
  dbConfig.username,
  dbConfig.password,
  {
    host: dbConfig.host,
    dialect: dbConfig.dialect,
    operatorsAliases: false,
  }
);

module.exports = sequelize;

创建模型

var db = require("./seq");
var Sequelize = require("sequelize");

const { STRING, INTEGER, TEXT } = Sequelize;

const User = db.define("user", {
  username: {
    type: STRING,
    allowNULL: false,
  },
  password: {
    type: STRING,
    allowNULL: false,
  },
  nickname: {
    type: STRING,
  },
});

const Blog = db.define("blog", {
  title: {
    type: STRING,
    allowNULL: false,
  },
  content: {
    type: TEXT,
    allowNULL: false,
  },
  userId: {
    type: INTEGER,
    allowNULL: false,
  },
});
// 建立一对一外键
Blog.belongsTo(User, {
  foreignKey: "userId",
});
// 建立一对多外键
User.hasMany(Blog, {
  foreignKey: "userId",
});

module.exports = { User, Blog };

同步建表

const sequelize = require("./seq");
require("./model");

// 测试链接
sequelize
  .authenticate()
  .then(() => {
    console.log("ok");
  })
  .catch((e) => {
    console.log("err");
  });

sequelize
  .sync({
    force: true,
  })
  .then(() => {
    console.log("sync ok");
    process.exit();
  })
  .catch((e) => {
    console.log("sync err", e);
  });

增

var { User, Blog } = require("./model");

!(async function () {
  const zhangsan = await User.create({
    username: "zhangsan",
    password: "123456",
    nickname: "张三",
  });
  const zId = zhangsan.dataValues.id;
  const lisi = await User.create({
    username: "lisi",
    password: "123456",
    nickname: "李四",
  });
  const lId = lisi.dataValues.id;

  await Blog.create({
    title: "title1",
    content: "content1",
    userId: zId,
  });
  await Blog.create({
    title: "title2",
    content: "content2",
    userId: zId,
  });
  await Blog.create({
    title: "title3",
    content: "content3",
    userId: zId,
  });
  await Blog.create({
    title: "title4",
    content: "content4",
    userId: lId,
  });
  await Blog.create({
    title: "title5",
    content: "content5",
    userId: zId,
  });
})();

删

var { User, Blog } = require("./model");

!(async function () {
  const delRes = await Blog.destroy({
    where: {
      id: 1,
    },
  });
  console.log(delRes);
  const zs = await User.findOne({
    where: {
      id: 1,
    },
  });
  zs.destroy();
  let delSRes = await zs.save();
  console.log(delSRes);
})();

改

var { User, Blog } = require("./model");

!(async function () {
  const updateRes = await User.update(
    {
      nickname: "张三1",
    },
    {
      where: {
        nickname: "张三",
      },
    }
  );
  const zhangsan = await User.findOne({
    where: {
      nickname: "张三",
    },
  });
  zhangsan.nickname = "张三";
  let res = await zhangsan.save();
  console.log(res, updateRes);
})();

查

var { User, Blog } = require("./model");

!(async function () {
  // 查询单条
  let user = await User.findOne({
    where: {
      id: 2,
    },
    attributes: ["username", "nickname"],
  });
  user = JSON.stringify(user, null, 2);
  // 查询多条
  let blogs = await Blog.findAll({
    limit: 2,
    offset: 0,
    where: {
      userId: 1,
    },
    order: [["id", "desc"]],
  });
  blogs = JSON.stringify(blogs, null, 2);
  // 查询数量
  let count = await Blog.count();
  // 查询数量与数据
  let { count: allCount, rows: allRows } = await Blog.findAndCountAll();

  // 关联查询
  const kfBlog = await Blog.findAndCountAll({
    include: {
      model: User,
      attributes: ["username"],
    },
  });
  const kfUser = await User.findAndCountAll({
    include: {
      model: Blog,
    },
  });
  console.log(JSON.stringify(kfUser.rows, null, 2));
})();

redis

redis-server 运行
redis-cli
key * 查看所有缓存key
set （name,valie）添加
get name 查看