node增删改查

文章目录

• ​​INSERT​​
• ​​SELECT​​
• ​​DELETE​​
• ​​UPDATE​​

INSERT

const app = express();

app.use(express.json()); // for parsing application/json
app.use(express.urlencoded({ extended: true })); // for parsing application/x-www-form-urlencoded

//向表中插入数据
app.post("/insertInto", function (req, res) {
  const { tableName, ...data } = req.body;
  console.log(tableName, data);
  const columns = [];
  const values = [];

  for (const [key, value] of Object.entries(data)) {
    if (key == "age") values.push(value);
    else values.push(`'${value}'`);
    columns.push(key);
  }

  const sql = `
  INSERT INTO ${tableName} (${columns.join()})
  VALUES (${values.join()});
  `;
  console.log("sql", sql);

  db.query(sql, function (err, data) {
    if (err) {
      console.error(err);
      res.status(411).send(JSON.stringify(err.sqlMessage));
    } else {
      console.log("success:", data);
      res.json(data);
    }
  });
});

SELECT

app.get("/selectTable", (req, res) => {
  const { tableName } = req.query;
  db.query(`SELECT * FROM ${tableName};`, function (err, data) {
    if (err) {
      console.error(err);
      res.status(411).send(JSON.stringify(err.sqlMessage));
    } else res.send(data);
  });
});

app.get("/selectTableColumns", (req, res) => {
  const { tableName } = req.query;
  const sql = `
  select column_name,data_type
  from information_schema.columns
  where table_name='${tableName}' and table_schema='HData';
  `;
  db.query(sql, function (err, data) {
    if (err) {
      console.error(err);
      res.status(411).send(JSON.stringify(err.sqlMessage));
    } else res.send(data);
  });
});

DELETE

//删除数据
app.post("/delete", (req, res) => {
  const { tableName, id } = req.body;
  console.log(tableName, id);

  const sql = `
  DELETE FROM ${tableName}
  WHERE id = '${id}'
  `;
  console.log("sql:", sql);

  db.query(sql, function (err, data) {
    if (err) {
      console.error(err);
      res.status(411).send(JSON.stringify(err.sqlMessage));
    } else {
      console.log("success:", data);
      res.json(data);
    }
  });
});

UPDATE

//更新数据
app.post("/update", (req, res) => {
  const { tableName, id, ...data } = req.body;
  console.log(tableName, id, data);
  //生成 SET 语句
  let setField = [];
  for (const [key, value] of Object.entries(data)) {
    setField.push(`${key} = '${value}'`);
  }

  const sql = `
  UPDATE ${tableName}
  SET ${setField.join(",")}
  WHERE id = '${id}'
  `;

  console.log("sql:", sql);

  db.query(sql, function (err, data) {
    if (err) {
      console.error(err);
      res.status(411).send(JSON.stringify(err.sqlMessage));
    } else {
      console.log("success:", data);
      res.json(data);
    }
  });
});