query-builder
A SQL query builder.
use
const build = 't_users' 'u' console // select `id`,`name`,`sex` from `t_users` as `u` where `id` = 1001
API
const build = tableName aliasName // 创建一个sql构建;注:toSql后,对象不能重复使用
- Builder.prototype.select([attrs]) 指定需要查询字段,默认
['*']
build // `*`build // `u`.`userId`,`id`,`name`
- Builder.prototype.insert(data: Array<{ [key: string]: any }>) 插入多条数据
build // insert into table(`name`, `age`) values('zhangsan', 24),('lisi', 23)
- Builder.prototype.create(data: { [key: string]: any }) 插入一条数据
build // insert into table(`name`, `age`) values('zhangsan', 24)
-
Builder.prototype.update(data: { [key: string]: any }) 更新数据,条件需要连缀
where
方法增加条件 -
Builder.prototype.delete() 删除数据,条件需要连缀
where
方法 -
Builder.prototype.where(conditions: conditionType) 条件
-
Builder.prototype.having(conditions: conditionType) having 条件
conditionType 定义如下
build // where `status` = 1 and age in (18,20,25) build // where `age` >= 18 and `age` <= 30
- Builder.prototype.order(fields: { [key: string]: orderType | Array<[string, orderType]) 排序
build // order by `createdAt` desc, `asc` ascbuild // order by `createdAt` desc, `asc` asc
- Builder.prototype.join(table: string | Builder, opt?: joinOptionType) 连表查询,支持多次调用
- Builder.prototype.joins(arr: joinsOptionType[]) 连表查询,参数为数组
build // left join `t_orders` as o on `o`.`userId` = `u`.`id`
- Builder.prototype.limit(offset: number = 1, limit: number) 取部分
- Builder.prototype.toSql(alias?: string) 生成sql
操作符
$gt: 10 // > 10 $gte: 10 // >= 10 $lt: 10 // < 10 $lte: 10 // <= 10 $eq: 10 // = 10 $neq: 10 // != 10 $between: [18, 30] // between 18 and 30 $notBetween: [18, 30] // not between 18 and 30 $in: [1, 3, 5, 7] // in(1,3,5,7) $notIn: [1, 3, 5, 7] // not in(1,3,5,7) $like: 'ab%c' // like 'ab%c' $notLike: 'ab%c' // not like 'ab%c' $raw: 'is not null' // is not null $id: 'u.userId' // = `u`.`userId`