knex入门学习使用方法
郎飞龙
创建knex对象
注：页面引用knex.min.js后，才能创建knex
创建不含数据连接的knex对象
var myknex = new knex.Client({
client: 'mysql',
})
创建含有数据连接的knex对象(待验证)
var pg = new knex.Client({
client: 'websql',
connection: {
host : '127.0.0.1',
port : 3306,
user : 'root',
password : '123',
database : 'myapp_test'
}
})
构建简单的SQL语句
查询语句
简单的单表查询
myknex.queryBuilder()
.from("table")
.toQuery()
输出为
select * from "table"
按表字段进行查询①
myknex.queryBuilder()
.column('title', 'author', 'year')
.select()
.from('books')
.toQuery()
输出为
select "title", "author", "year" from "books"
按表字段进行查询②
myknex.queryBuilder()
.column(['title', 'author', 'year'])
.select()
.from('books')
.toQuery()
输出为
select "title", "author", "year" from "books"
按表字段进行查询③
myknex.queryBuilder()
.column('title', {by: 'author'}, 'year')
.select()
.from('books')
.toQuery()
输出为
select "title", "author", "year" from "books"
distinct
myknex.queryBuilder()
.distinct('first_name', 'last_name').from('users')
.toQuery()
输出为
select distinct "first_name", "last_name" from "users"
where语句
归纳了where的三种写法
- where(对象)
- where(key,value) //键值对
- where(key,操作符，value)
单条件查询①
myknex.queryBuilder()
.where('id', 1)
.from('user')
.toQuery()
输出为
select * from "user" where "id" = 1
单条件查询②
myknex.queryBuilder()
.where('votes', '>', 100)
.from('user')
.toQuery()
输出为
select * from "users" where "votes" > 100
单条件查询记录集
var subquery =myknex.queryBuilder()
.where('votes', '>', 100)
.andWhere('status', 'active')
.orWhere('name', 'John')
.select('id')
.from('users');
myknex.queryBuilder()
.where('id', 'in', subquery)
.from('users')
.toQuery()
输出为
select * from "users" where "id" in (select "id" from "users" where "votes" > 100 and "status" = 'active' or "name" = 'John')
where多条件用法①
myknex.queryBuilder()
.where({
first_name: 'zhangsan',
last_name: 'lisi'
})
.select('id')
.from('user')
.toQuery()
输出为
select "id" from "user" where "first_name" = 'zhangsan' and "last_name" = 'lisi'
where多条件用法③
myknex.queryBuilder()
.where(function() {
this.where('id', 1)
.orWhere('id', '>', 10)
})
.orWhere({name: 'Tester'})
.from('users')
.toQuery()
输出为
select * from "users" where ("id" = 1 or "id" > 10) or ("name" = 'Tester')
where in 用法①
myknex.queryBuilder()
.where(function (builder) {
return builder.whereIn('id', [1, 11, 15])
.whereNotIn('id', [17, 19]);
})
.andWhere(function () {
this.where('id', '>', 10);
})
.from('users')
.toQuery()
输出为
select * from "users" where ("id" in (1, 11, 15) and "id" not in (17, 19)) and ("id" > 10)
where in 用法②
myknex.queryBuilder()
.select('name')
.from('users')
.whereIn('id', [1, 2, 3])
.orWhereIn('id', [4, 5, 6])
.toQuery()
输出为
select "name" from "users" where "id" in (1, 2, 3) or "id" in (4, 5, 6)
where in 用法②
myknex.queryBuilder()
.select('name')
.from('users')
.whereIn(
'account_id',
function() {
this.select('id').from('accounts');
})
.toQuery()
输出为
select "name" from "users" where "account_id" in (select "id" from "accounts")
where in 用法④
myknex.queryBuilder()
.select('name')
.from('users')
.whereIn(
['account_id', 'email'], [[3, 'test3@example.com'],
[4, 'test4@example.com']]
)
.toQuery()
输出为
select "name" from "users" where ("account_id", "email") in ((3, 'test3@example.com'), (4, 'test4@example.com'))
where in 用法⑤
myknex.queryBuilder()
.select('name')
.from('users')
.whereIn(
['account_id', 'email'],
myknex.queryBuilder().select('id', 'email').from('accounts'))
.toQuery()
输出为
select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")
where not in
myknex.queryBuilder()
.whereNotIn('id', [1, 2, 3])
.from('users')
.toQuery()
输出为
select * from "users" where "id" not in (1, 2, 3)
where or not in
myknex.queryBuilder()
.where('name', 'like', '%Test%').orWhereNotIn('id', [1, 2, 3])
.from('users')
.toQuery()
输出为
select * from "users" where "name" like '%Test%' or "id" not in (1, 2, 3)
where is null
myknex.queryBuilder()
.whereNull('updated_at')
.from('users')
.toQuery()
输出为
select * from "users" where "updated_at" is null
where is not null
myknex.queryBuilder()
.whereNotNull('created_at')
.from('users')
.toQuery()
输出为
select * from "users" where "created_at" is not null
where like
myknex.queryBuilder()
.where('columnName', 'like', '%rowlikeme%')
.from('users')
.toQuery()
输出为
select * from "users" where "columnName" like '%rowlikeme%'
where exists
myknex.queryBuilder()
.whereExists(function() {
this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
.toQuery()
输出为
select * where exists (select * from "accounts" where users.account_id = accounts.id)
where not exists
myknex.queryBuilder()
.whereNotExists(function() {
this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
.toQuery()
输出为
select * where not exists (select * from "accounts" where users.account_id = accounts.id)
where between and
myknex.queryBuilder()
.whereBetween('votes', [1, 100])
.from('users')
.toQuery()
输出为
select * from "users" where "votes" between 1 and 100
where not between and
myknex.queryBuilder()
.whereNotBetween('votes', [1, 100])
.from('users')
.toQuery()
输出为
select * from "users" where "votes" not between 1 and 100
where 使用变量
myknex.queryBuilder()
.whereRaw('id = ?', [1])
.from('users')
.toQuery()
输出为
select * from "users" where id = 1
insert语句
简单inset语句
myknex.queryBuilder()
.insert({title: 'Slaughterhouse Five'})
.into('books')
.toQuery()
输出为
insert into "books" ("title") values ('Slaughterhouse Five')
含有默认值的inset语句
myknex.queryBuilder()
.insert([{x: 20}, {y: 30}, {x: 10, y: 20}])
.into('books')
.toQuery()
输出为
insert into "books" ("x", "y") values (20, DEFAULT), (DEFAULT, 30), (10, 20)
update语句
简单update语句
myknex.queryBuilder()
.from("books")
.update('title', 'Slaughterhouse Five')
.where('published_date', '<', 2000)
.toQuery()
输出为
update "books" set "title" = 'Slaughterhouse Five' where "published_date" < 2000
更新多个字段
myknex.queryBuilder()
.from("books")
.update({'title':'Slaughterhouse Five','title1': 'Slaughterhouse Five1'})
.where('published_date', '<', 2000)
.toQuery()
输出为
update "books" set "title" = 'Slaughterhouse Five', "title1" = 'Slaughterhouse Five1' where "published_date" < 2000
delete语句
简单delete语句
myknex.queryBuilder()
.from("books")
.where('activated', false)
.del()
.toQuery()
输出为
delete from "books" where "activated" = false
表连接
join(table, first, [operator], second)
第一个参数是连接表，接下来的三个参数分别是第一个连接列、连接运算符和第二个连接列,其中连接运算符可以省略
inner join
myknex.queryBuilder()
.join('contacts', 'users.id', '=', 'contacts.user_id')
.select('users.id', 'contacts.phone').from('users a')
.toQuery()
输出为
select "users"."id", "contacts"."phone" from "users a" inner join "contacts" on "users"."id" = "contacts"."user_id"
inner join 存在变量
myknex.queryBuilder()
.select('*')
.from('users')
.join('accounts', 'accounts.type', myknex.raw('?', ['admin']))
.toQuery()
输出为
select * from "users" inner join "accounts" on "accounts"."type" = 'admin'
left join
myknex.queryBuilder()
.select('*')
.from('users')
.leftJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" left join "accounts" on "users"."id" = "accounts"."user_id"
left outer join
myknex.queryBuilder()
.select('*')
.from('users')
.leftOuterJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" left outer join "accounts" on "users"."id" = "accounts"."user_id"
right join
myknex.queryBuilder()
.select('*')
.from('users')
.rightJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"
right outer join
myknex.queryBuilder()
.select('*')
.from('users')
.rightOuterJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" right outer join "accounts" on "users"."id" = "accounts"."user_id"
full outer join
myknex.queryBuilder()
.select('*')
.from('users')
.fullOuterJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" full outer join "accounts" on "users"."id" = "accounts"."user_id"
cross join
只有 MySQL 和 SQLite3 支持交叉连接条件。对于连接条件，请使用innerJoin。
myknex.queryBuilder()
.select('*')
.from('users')
.crossJoin('accounts', 'users.id', 'accounts.user_id')
.toQuery()
输出为
select * from "users" cross join "accounts" on "users"."id" = "accounts"."user_id"
with语句
with(alias, [columns], callback|builder|raw)
向查询添加“with”子句。PostgreSQL、Oracle、SQLite3 和 MSSQL 支持“With”子句。别名后可以提供可选的列列表；如果提供，它必须至少包含一个列名称
未指定列名称用法
myknex.queryBuilder()
.with(
'with_alias',
myknex.raw('select * from "books" where "author" = ?', 'Test')
)
.select('*')
.from('with_alias')
.toQuery()
输出为
with "with_alias" as (select * from "books" where "author" = 'Test') select * from "with_alias"
制定列名称用法
myknex.queryBuilder()
.with(
'with_alias',
["title"],
myknex.raw('select "title" from "books" where "author" = ?', 'Test')
)
.select('*')
.from('with_alias')
.toQuery()
输出为
with "with_alias"("title") as (select "title" from "books" where "author" = 'Test') select * from "with_alias"
传入callback的用法
myknex.queryBuilder()
.with(
'with_alias',
function (qb) {
qb.select('*').from('books').where('author', 'Test');
}
)
.select('*')
.from('with_alias')
.toQuery()
输出为
with "with_alias" as (select * from "books" where "author" = 'Test') select * from "with_alias"
with recursive语句
withRecursive(alias, [columns], callback|builder|raw)
与该with方法相同，除了将“递归”附加到“与”（或不附加，根据目标数据库的要求）以使自引用 CTE 成为可能。请注意，某些数据库（例如 Oracle）在使用 rCTE 时需要提供列列表
传入callback的用法
myknex.queryBuilder()
.with(
'with_alias',
function (qb) {
qb.select('*').from('books').where('author', 'Test');
}
)
.select('*')
.from('with_alias')
.toQuery()
输出为
with "with_alias" as (select * from "books" where "author" = 'Test') select * from "with_alias"
子句
分组 group by
myknex.queryBuilder()
.groupBy('count')
.from('users')
.toQuery()
输出为
select * from "users" group by "count"
排序 order by
单列
不传排序方式时，默认为asc
myknex.queryBuilder()
.orderBy('email', 'desc')
.from('users')
.toQuery()
输出为
select * from "users" order by "email" desc
多列
myknex.queryBuilder()
.orderBy([{ column: 'email' }, { column: 'age', order: 'desc' }])
.from('users')
.toQuery()
输出为
select * from "users" order by "email" asc, "age" desc
having子句
having
myknex.queryBuilder()
.groupBy('count')
.orderBy('name', 'desc')
.having('count', '>', 100)
.from('users')
.toQuery()
输出为
select * from "users" group by "count" having "count" > 100 order by "name" desc
having in
myknex.queryBuilder()
.havingIn('id', [5, 3, 10, 17])
.from('users')
.toQuery()
输出为
select * from "users" having "id" in (5, 3, 10, 17)
having not in
myknex.queryBuilder()
.havingNotIn('id', [5, 3, 10, 17])
.from('users')
.toQuery()
输出为
select * from "users" having "id" not in (5, 3, 10, 17)
联合查询
union用法①
myknex.queryBuilder()
.select('*')
.from('users')
.whereNull('last_name')
.union(function() {
this.select('*')
.from('users')
.whereNull('first_name')
})
.toQuery()
输出为
select * from "users" where "last_name" is null union select * from "users" where "first_name" is null
union用法②
myknex.queryBuilder()
.select('*')
.from('users')
.whereNull('last_name')
.union([
myknex.queryBuilder()
.select('*')
.from('users')
.whereNull('first_name')
])
.toQuery()
输出为
select * from "users" where "last_name" is null union select * from "users" where "first_name" is null
union用法③
myknex.queryBuilder()
.select('*')
.from('users')
.whereNull('last_name')
.union(
myknex.raw('select * from users where first_name is null'),
myknex.raw('select * from users where email is null')
)
.toQuery()
输出为
select * from "users" where "last_name" is null union select * from users where first_name is null union select * from users where email is null
unionAll
myknex.queryBuilder()
.select('*')
.from('users')
.whereNull('last_name')
.unionAll(function() {
this.select('*')
.from('users')
.whereNull('first_name');
})
.toQuery()
输出为
select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null
类似资料：