App后台管理系统后端常用Sql操作

1. 基本查询

基本查询通常用于检索满足特定条件的数据集。这包括按特定字段的值查找、获取所有数据等。

// 获取所有数据
const getAllItems = async (req, res) => {
  try {
    const items = await Item.findAll();
    res.json(items);
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

// 按条件查询
const getItemsByCondition = async (req, res) => {
  try {
    const items = await Item.findAll({
      where: {
        // 条件，例如 status: 'active'
      }
    });
    res.json(items);
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

2. 关联查询

关联查询用于检索与其他表相关联的数据。在 Sequelize 中，这通常涉及 include 选项。

const getItemsWithDetails = async (req, res) => {
  try {
    const items = await Item.findAll({
      include: [{ model: DetailModel }] // 关联其他模型
    });
    res.json(items);
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

3. 分页

分页是后台管理系统中一个重要的特性，它允许服务器一次只发送部分数据，减轻服务器负担，提高用户体验。

const getItemsPaginated = async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const pageSize = parseInt(req.query.pageSize) || 10;

  try {
    const { count, rows } = await Item.findAndCountAll({
      offset: (page - 1) * pageSize,
      limit: pageSize
    });
    res.json({
      total: count,
      data: rows,
      page: page,
      pageSize: pageSize
    });
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

4. 更新数据

更新操作允许修改现有数据库记录。这可以是单个字段的更新，也可以是多个字段的更新。

const updateItem = async (req, res) => {
  try {
    const [updated] = await Item.update(req.body, {
      where: { id: req.params.id }
    });
    if (updated) {
      const updatedItem = await Item.findByPk(req.params.id);
      res.json(updatedItem);
    } else {
      res.status(404).send({ message: 'Item not found' });
    }
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

5. 删除数据

删除操作用于从数据库中移除数据。这可以是单个记录的删除或符合特定条件的多条记录的批量删除。

const deleteItem = async (req, res) => {
  try {
    const deleted = await Item.destroy({
      where: { id: req.params.id }
    });
    if (deleted) {
      res.json({ message: 'Item deleted successfully' });
    } else {
      res.status(404).send({ message: 'Item not found' });
    }
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

这些是后台管理系统中最常见的数据库操作，它们涵盖了绝大多数的日常需求。根据具体的业务逻辑，可能还需要进行更多定制化的操作。

6. 批量插入

当需要一次性插入多条数据时，批量插入是非常有用的。

const bulkInsertItems = async (req, res) => {
  try {
    const items = req.body.items; // 假设是一个对象数组
    await Item.bulkCreate(items);
    res.json({ message: 'Items were added successfully' });
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

7. 复杂查询

涉及多个条件、子查询、聚合函数（如 COUNT、MAX）的复杂查询。

const getComplexData = async (req, res) => {
  try {
    const data = await Item.findAll({
      where: {
        // 复杂条件
      },
      include: [{
        model: AnotherModel,
        required: true
      }],
      group: ['Item.column'],
      having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col('Item.column')), '>', 1)
    });
    res.json(data);
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

8. 事务处理

对于需要保证数据完整性的复杂操作，使用事务是必须的。

const performComplexOperation = async (req, res) => {
  const transaction = await sequelize.transaction();
  try {
    // 多个数据库操作
    await Model1.create({ ... }, { transaction });
    await Model2.update({ ... }, { where: { ... } }, { transaction });

    await transaction.commit();
    res.json({ message: 'Operation successful' });
  } catch (error) {
    await transaction.rollback();
    res.status(500).send({ message: error.message });
  }
};

9. 数据统计和聚合

对数据进行统计分析，例如计算总数、平均值等。

const getStatistics = async (req, res) => {
  try {
    const stats = await Item.findAll({
      attributes: [
        [Sequelize.fn('COUNT', Sequelize.col('id')), 'totalItems'],
        [Sequelize.fn('AVG', Sequelize.col('price')), 'averagePrice']
      ]
    });
    res.json(stats[0]);
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

10. 软删除

标记数据为删除状态而非从数据库中完全移除。

const softDeleteItem = async (req, res) => {
  try {
    await Item.update({ deleted: true }, { where: { id: req.params.id } });
    res.json({ message: 'Item deleted successfully' });
  } catch (error) {
    res.status(500).send({ message: error.message });
  }
};

以上操作涵盖了后台管理系统中常用的数据库操作。根据实际业务需求，可能还会有更多特定的操作和策略。在设计数据库交互时，重要的是确保操作的安全性、效率和符合业务逻辑。