@xqj/mysql

node-mysql-query

node-mysql 的常用操作封装，与 ThinkPHP 的查询器类似 功能还在优化

• Install
• Introduction
• Jest

Install

npm install --save @xqj/mysql

Introduction

配置(CONFIG)

参数	说明	类型	默认值
host	数据库的主机名	string	必填项
port	要连接的端口号	string	3306
user	MySQL用户	string	必填项
password	MySQL用户的密码	string	必填项
database	数据库名称	string	必填项
prefix	数据库的前缀	string	可选

其他配置 mysqljs/mysql

创建连接

const {default:Db} = require('@xqj/mysql')	//or import Db from '@xqj/mysql'
const db = Db.connect({
	host: 'localhost',
	password: 'root',
	user: 'root',
	database: 'demo',
	prefix: 't_'
})

基本的查询

const {default:Db} = require('@xqj/mysql')	//or import Db from '@xqj/mysql'
const db = Db.connect({
	host: 'localhost',
	password: 'root',
	user: 'root',
	database: 'school',
	prefix: 'sc_'
})
db.name('teacher').where('username', '').select().then(res => {
	//return [rows]
})

Promise化

• select

   //select
   db.name('teacher').where('username', '').select().then(res => {})
  
   async function find() {
   	let res = await db.name('teacher').where('username','').find()
   }

• update

   db.name('teacher').where('username', '').update({
   	username:'张三'
   }).then(res => {
   	console.log(res) //return affected rows
   })

• delete

   db.name('teacher').where('username', '').delete().then(res => {
   	console.log(res) //return affected rows
   })

• insert

   db
   .name('teacher')
   .insert([{
   	username: '李四',
   	create_time: new Date(),
   }])
   .then((res) => {
   	console.log(res)	//return { affectedRows, insertId }
   })

• insertGetId

   db
   .name('teacher')
   .insertGetId([{
   	username: '李四',
   	create_time: new Date(),
   }])
   .then((res) => {
   	console.log(res)	//return insert Id
   })

• query

   db.query("SELECT * FROM `sc_teacher` WHERE `username` = ''").then(res => {
   	//return { affectedRows, insertId }
   })
   //PrepareStatement机制
   db.exec("SELECT * FROM `sc_teacher` WHERE `username` = ?", ['']).then(res => {
   	//return { affectedRows, insertId }
   })

Debug

const {default:Db} = require('@xqj/mysql')	//or import Db from '@xqj/mysql'
Db.debug = true
const db = Db.connect({
	host: 'localhost',
	password: 'root',
	user: 'root',
	database: 'school',
	prefix: 'sc_'
})
db.name('teacher').where('username', '').select(function(query, db) {
	console.log(query)	//return object
	// {
	// 	sql:SELECT * FROM `sc_teacher` WHERE `username` = ?
	// 	values:['']
	// }
	console.log(db)	//return db instance
}).then(res => {
	//return undefined
})

Methods

• name (names: string | string[] )

  db.name('teacher').where('username', '').select()
  
  //SELECT * FROM `sc_teacher` WHERE `username` = ''

• table (names:string | string[] )

   db.table('sc_teacher').where('username', '').select()
  
   //SELECT * FROM `sc_teacher` WHERE `username` = ''

• where (field:string|object, operator?:string|number, condition?:string|string[]|number|number[])

  • field 查询的字段 也可以是一段查询表达式 如: 'id is not null'

  • operator 查询表达式(不区分大小写)

    表达式	说明
    = or eq	等于
    > or gt	大于
    < or lt	小于
    >= or egt	大于等于
    <= or elt	小于等于
    <> or neq	不等于
    like	模糊查询
    between	区间查询
    not between	不在区间查询
    in	in查询
    not in	不在in查询
    null	null查询
    not null	不是null查询
    表达式还不支持时间, 后续迭代

  • condition 查询的条件

   //以下的查询都是等价的
   db.name('teacher').where('username = \'\'')
   db.name('teacher').where('username', '')
   db.name('teacher').where('username', '=', '')
   db.name('teacher').where({
   	username:[''] // or username:''	默认是=表达式
   })
   db.name('teacher').where({
   	username:['=', '']
   })
  
   //一个字段有多个条件情况下
   //like
   db.name('teacher').where('username', 'like', '%张')
   db.name('teacher').where({
   	username:['like','%张']
   })
   //多个like查询
   db.name('teacher').where({
   	username:[
   		['like', '%张'],
   		'or', // 'and'
   		['like', '张%']
   	]
   })
   db.name('teacher').where({
   	id:[
   		['>', 1],
   		['<', 10]
   	]
   })
   //等价于下面这个例子
   db.name('teacher').where({
   	id:[
   		['>',1],
   		'and',
   		['<', 10]
   	]
   })
  
   //between查询
   db.name('teacher').where('id', 'between', [1,4])	//条件为数组
   db.name('teacher').where('id', 'not between', [1,4])
  
   //in查询
   db.name('teacher').where('id', 'in', [1,2,3])
   db.name('teacher').where('id', 'not in', [1,2,3])
  
   //null查询
   db.name('teacher').where('id', 'null')
   db.name('teacher').where('id', 'not null')

• alias (names:string | object )

   db.table('sc_teacher').alias('a').where('username', '').select()
   //SELECT * FROM `sc_teacher` WHERE `username` = ''
   db.table(['sc_teacher', 'sc_student']).alias({
   	'sc_teacher':'a',
   	'sc_student':'b'
   }).where('username', '').select()

• field (fields:string )

   db.table('sc_teacher').field('id,username,create_time').where('username', '').select()
  
   //SELECT id,username,create_time FROM `sc_teacher` WHERE `username` = ''

• limit (star:string|number, end?:number)

   db.table('sc_teacher').where('username', '').limit('1,5').select()
   db.table('sc_teacher').where('username', '').limit(1,5).select()
   //SELECT * FROM `sc_teacher` WHERE `username` = '' LIMIT 1,5

• group (fields:string)

   db.table('sc_teacher').where('username', '').group('id').select()
   //SELECT * FROM `sc_teacher` WHERE `username` = '' GROUP BY id

• distinct (isDistinct: boolean)

   db.table('sc_teacher').field('username').distinct(true).select()
   //SELECT DISTINCT username FROM `sc_teacher`

• join (table: string, condition: string, joinType:sqlJoinType = 'INNER')

   type sqlJoinType = 'INNER' | 'LEFT' | 'RIGHT' | 'FULL'
   db.table('sc_teacher').alias('a').join("sc_student b", "a.username = b.teacher_name").select()
   //SELECT * FROM sc_teacher a INNER JOIN sc_student b ON a.username = b.teacher_name
  
   db.table('sc_teacher').alias('a').where('username', '').join("sc_student b", "a.username = b.teacher_name").select()
   //SELECT * FROM sc_teacher a INNER JOIN sc_student b ON a.username = b.teacher_name WHERE a.username = ''

• order (field: string)

   db.table('sc_teacher').where('username', '').order('id DESC').select()
   //SELECT * FROM sc_teacher a WHERE a.username = '' ORDER BY `id` DESC
  
   db.table('sc_teacher').where('username', '').order('id DESC, username ASC').select()
   //SELECT * FROM sc_teacher a WHERE a.username = '' ORDER BY `id` DESC, `username` ASC

• comment (desc: string)

   db.table('sc_teacher').comment("Query sc_teacher").where('username', '').select()
   //SELECT * FROM sc_teacher a WHERE a.username = ''  ##Query sc_teacher

• format (sql:string, values:any[])

   db.format('SELECT * FROM sc_teacher a WHERE a.username = ?', [''])
   //SELECT * FROM sc_teacher a WHERE a.username = ''

• config (key:string)

   db.config('prefix')
   //return 'sc_'

Jest

npm run test

• 例子

   const config = {
   	host: '127.0.0.1',
   	database: 'ormtype',
   	user: 'root',
   	password: 'root',
   	prefix: 'sc_'
   }
   Db.debug = true
   let db = Db.connect(config)
   describe('where查询 => table:teacher,condition:id = 1', () => {
   	it('用例1:', () => {
   		let data = {
   			sql: 'SELECT * FROM `sc_teacher` WHERE `id` = ?',
   			values:[1]
   		} 
   		const callback = (query) => {
   			expect(query).toEqual(data)
   		}
   		db.name('teacher').where('id',1).select(callback)
   	})
   })