企业官网建设流程全解析

免费视频模板网站,wordpress 教程 百度网盘,好123设为主页官网,白酒 网站模板在 NestJS 项目中#xff0c;this.xxxRepository.createQueryBuilder() 是最常用、最规范的 QueryBuilder 使用方式。 本文以 UserEntity 为例#xff0c;系统整理 QueryBuilder 对应的常见 SQL 写法#xff0c;所有示例可直接复制使用。一、基础准备Injectable() export cl…在 NestJS 项目中this.xxxRepository.createQueryBuilder()是最常用、最规范的 QueryBuilder 使用方式。本文以UserEntity为例系统整理QueryBuilder 对应的常见 SQL 写法所有示例可直接复制使用。一、基础准备Injectable() export class UserService { constructor( InjectRepository(UserEntity) private readonly userRepository: RepositoryUserEntity, ) {} }以下示例默认写在UserService中。一、新增 更新 删除1. INSERTreturn this.userRepository .createQueryBuilder() .insert() .into(UserEntity) .values({ name: 张三, age: 18, status: 1, }) .execute();批量新增return this.userRepository .createQueryBuilder() .insert() .into(UserEntity) .values([ { name: 张三, age: 18 }, { name: 李四, age: 20 }, ]) .execute();2. UPDATEreturn this.userRepository .createQueryBuilder() .update(UserEntity) .set({ status: 0 }) .where(id :id, { id }) .execute();3. DELETEreturn this.userRepository .createQueryBuilder() .delete() .from(UserEntity) .where(id :id, { id }) .execute();二、基础查询SELECT1. 查询单表数据SQLSELECT * FROM user WHERE status 1;QueryBuilderreturn this.userRepository .createQueryBuilder(u) .where(u.status :status, { status: 1 }) .getMany();2. 查询指定字段SQLSELECT id, name FROM user;return this.userRepository .createQueryBuilder(u) .select([u.id, u.name]) .getMany();3. 查询单条数据return this.userRepository .createQueryBuilder(u) .where(u.id :id, { id }) .getOne();三、WHERE 条件用法1. 多条件 AND / ORSQLSELECT * FROM user WHERE age 18 AND status 1;return this.userRepository .createQueryBuilder(u) .where(u.age :age, { age: 18 }) .andWhere(u.status :status, { status: 1 }) .getMany();OR.orWhere(u.role :role, { role: admin })2. IN 查询SQLSELECT * FROM user WHERE id IN (1,2,3);return this.userRepository .createQueryBuilder(u) .where(u.id IN (:...ids), { ids: [1, 2, 3] }) .getMany();3. LIKE 模糊查询SQLSELECT * FROM user WHERE name LIKE %张%;return this.userRepository .createQueryBuilder(u) .where(u.name LIKE :name, { name: %张% }) .getMany();四、排序 分页1. ORDER BYreturn this.userRepository .createQueryBuilder(u) .orderBy(u.createTime, DESC) .getMany();2. 分页查询return this.userRepository .createQueryBuilder(u) .skip((page - 1) * pageSize) .take(pageSize) .getMany();3. 分页 总数return this.userRepository .createQueryBuilder(u) .skip((page - 1) * pageSize) .take(pageSize) .getManyAndCount();五、JOIN 多表查询1. LEFT JOINSQLSELECT * FROM user u LEFT JOIN order o ON u.id o.user_id;return this.userRepository .createQueryBuilder(u) .leftJoin(OrderEntity, o, o.userId u.id) .getMany();2. LEFT JOIN 并返回关联数据return this.userRepository .createQueryBuilder(u) .leftJoinAndSelect(u.orders, o) .getMany();⚠️ 前提UserEntity中定义了OneToMany(() OrderEntity, ...)六、聚合函数COUNT / SUM1. COUNT 查询return this.userRepository .createQueryBuilder(u) .where(u.status :status, { status: 1 }) .getCount();2. GROUP BY 统计SQLSELECT status, COUNT(*) total FROM user GROUP BY status;return this.userRepository .createQueryBuilder(u) .select(u.status, status) .addSelect(COUNT(*), total) .groupBy(u.status) .getRawMany();3. HAVINGreturn this.userRepository .createQueryBuilder(u) .select(u.status, status) .addSelect(COUNT(*), total) .groupBy(u.status) .having(COUNT(*) :count, { count: 10 }) .getRawMany();七、子查询1. 子查询 INSQLSELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount 100);return this.userRepository .createQueryBuilder(u) .where(qb { const subQuery qb .subQuery() .select(o.userId) .from(OrderEntity, o) .where(o.amount :amount) .getQuery(); return u.id IN subQuery; }) .setParameter(amount, 100) .getMany();