nodeJs使用knex操作数据库（基础）

发布: 2017-08-18 21:17:47标签: nodeJs

knex入门

01const configs = require('../../config');
02
03module.exports = require('knex')({
04  client: 'mysql',
05  connection: {
06    host: configs.mysql.host,
07    port: configs.mysql.port,
08    user: configs.mysql.user,
09    password: configs.mysql.pass,
10        database: configs.mysql.db,
11  }
12});
13
复制代码

基础查询

01knex.column('title', 'author', 'year').select().from('books')
02
03// select 'title', 'author', 'year' from 'books'
复制代码

基础插入

knex('user')
  .returning('id')
  .insert({name: '名字'})

// insert into 'user' ('name') values ('名字') returning 'id'

修改

01knex('books')
02.where('published_date', '<', 2000)
03.update({
04  status: 'archived',
05  thisKeyIsSkipped: undefined
06})
07
08// update 'books' set 'status' = 'archived' where 'published_date' < 2000
复制代码

删除

01knex('accounts')
02.where('activated', false)
03.del()
04
05// delete from 'accounts' where 'activated' = false
06
07knex('accounts').truncate(); // 清空表
08
复制代码

排序

01knex('users').orderBy('name', 'desc')
02
03// select * from 'users' order by 'name' desc
复制代码

模糊查询 like

01knex('users').where('columnName', 'like', '%rowlikeme%')
02Outputs:
03select * from `users` where `columnName` like '%rowlikeme%'
复制代码

运算

count(column)
min(column)
max(column)
sum(column)
avg(column)
increment(column, amount)
decrement(column, amount)

关闭连接

knex.destroy();