辽阳市网站建设_网站建设公司_代码压缩_seo优化

在 NestJS 项目中，this.xxxRepository.createQueryBuilder()是最常用、最规范的 QueryBuilder 使用方式。
本文以UserEntity为例，系统整理QueryBuilder 对应的常见 SQL 写法，所有示例可直接复制使用。

一、基础准备

@Injectable() export class UserService { constructor( @InjectRepository(UserEntity) private readonly userRepository: Repository<UserEntity>, ) {} }

以下示例默认写在UserService中。

一、新增 & 更新 & 删除

1. INSERT

return 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. UPDATE

return this.userRepository .createQueryBuilder() .update(UserEntity) .set({ status: 0 }) .where('id = :id', { id }) .execute();

3. DELETE

return this.userRepository .createQueryBuilder() .delete() .from(UserEntity) .where('id = :id', { id }) .execute();

二、基础查询（SELECT）

1. 查询单表数据

SQL

SELECT * FROM user WHERE status = 1;

QueryBuilder

return this.userRepository .createQueryBuilder('u') .where('u.status = :status', { status: 1 }) .getMany();

2. 查询指定字段

SQL

SELECT 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 / OR

SQL

SELECT * 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 查询

SQL

SELECT * 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 模糊查询

SQL

SELECT * FROM user WHERE name LIKE '%张%';

return this.userRepository .createQueryBuilder('u') .where('u.name LIKE :name', { name: `%张%` }) .getMany();

四、排序 & 分页

1. ORDER BY

return 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 JOIN

SQL

SELECT * 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 / SUM）

1. COUNT 查询

return this.userRepository .createQueryBuilder('u') .where('u.status = :status', { status: 1 }) .getCount();

2. GROUP BY 统计

SQL

SELECT 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. HAVING

return this.userRepository .createQueryBuilder('u') .select('u.status', 'status') .addSelect('COUNT(*)', 'total') .groupBy('u.status') .having('COUNT(*) > :count', { count: 10 }) .getRawMany();

七、子查询

1. 子查询 IN

SQL

SELECT * 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();