npm i yamlling-node-mysql

const mysql = require('yamlling-node-mysql');
const inst = new mysql({
  host: '127.0.0.1',
  user: 'root',
  password: '',
  database: 'test-db',
  port: 3306,
});
const users = await inst.table('user').where({ status: 1 }).select();
console.log(users);

// SELECT `admins`.`id`, `admins`.`name` FROM `admins` limit 1
mysql.table('admins').field('id, name').find();
// SELECT `admins`.`id`, `admins`.`name` as a, `admins`.`status` as b FROM `admins` limit 1
mysql.table('admins').field(['id', 'name as a', { status: 'b' }]).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') limit 1
mysql.table('admins').where({ status: 'on' }).find();

// SELECT `admins`.`*` FROM `admins` WHERE (id = 10 OR id < 2) limit 1
mysql.table('admins').where('id = 10 OR id < 2').find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` != 1) limit 1
mysql.table('admins').where({id: ['!=', 1]}).find();

// NULL操作

SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IS NULL) limit 1
mysql.table('admins').where({id: null}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IS NOT NULL) limit 1
mysql.table('admins').where({id: [ '!=', null ]}).find();

// LIKE 操作

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%') limit 1
mysql.table('admins').where({name: [ 'like', '%admin%' ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` NOT LIKE '%admin%') limit 1
mysql.table('admins').where({name: [ 'notlike', '%admin%' ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%' OR `admins`.`email` LIKE '%admin%') limit 1
mysql.table('admins').where({'name|email': [ 'like', '%admin%' ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` LIKE '%admin%' AND `admins`.`email` LIKE '%admin%') limit 1
mysql.table('admins').where({'name&email': [ 'like', '%admin%' ]}).find();

// 一对多操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`name` = 'admin' OR `admins`.`name` = 'editor') limit 1
mysql.table('admins').where({name: [ '=', [ 'admin', 'editor' ] ]}).find();

// IN 操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IN (5,10)) limit 1
mysql.table('admins').where({'id': [ 'in', [5, 10] ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` IN (5, 10)) limit 1
mysql.table('admins').where({'id': [ 'in', '5, 10' ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` NOT IN (5,10)) limit 1
mysql.table('admins').where({'id': [ 'notin', [5, 10] ]}).find();

// BETWEEN 操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10) limit 1
mysql.table('admins').where({'id': [ 'between', [5, 10] ]}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10 AND `admins`.`name` = 'admin') limit 1
mysql.table('admins').where({'id': [ 'between', [5, 10] ], 'name': 'admin'}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`id` BETWEEN 5 AND 10 OR `admins`.`name` = 'admin') limit 1 
mysql.table('admins').where({'id': [ 'between', [5, 10] ], 'name': 'admin', '_logic': 'OR'}).find();

// 多字段操作
// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') AND (`admins`.`id` >= 1 AND `admins`.`id` <= 10) limit 1
mysql.table('admins').where({'status': 'on'}).where({'id': {'>=': 1, '<=': 10}}).find();

// SELECT `admins`.`*` FROM `admins` WHERE (`admins`.`status` = 'on') AND (`admins`.`id` >= 1 OR `admins`.`id` <= 10) limit 1
mysql.table('admins').where({'status': 'on'}).where({'id': {'>=': 1, '<=': 10, '_logic': 'OR'}}).find();

// SELECT `article_categorys`.`*` FROM `article_categorys` ORDER BY id desc
mysql.table('article_categorys').order('id desc').select();

//SELECT `article_categorys`.`*` FROM `article_categorys` ORDER BY id desc, name asc
mysql.table('article_categorys').order([ 'id desc', 'name asc' ]).select();

// SELECT `a`.`*`, `b`.`*` FROM `article_posts` as a LEFT JOIN `article_categorys` AS b ON (a.`category_id`=b.`id`) limit 1
mysql.table('article_posts').alias('a').field([ 'a.*', 'b.*' ]).join({
 article_categorys: {
   as: 'b',
   on: { category_id: 'id' }
 }
}).find();

// SELECT `a`.`*`, `article_categorys`.`*` FROM `article_posts` as a LEFT JOIN `article_categorys` ON (a.`category_id`=article_categorys.`id`) limit 1
mysql.table('article_posts').alias('a').field([ 'a.*', 'article_categorys.*' ]).join({
 article_categorys: {
   // as: 'b',
   on: { category_id: 'id' }
 }
}).find();