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

其他配置 mysqljs/mysql

创建连接

import linsql from 'linsql'
const db = linsql.connect({
	host: 'localhost',
	password: 'root',
	user: 'root',
	database: 'demo',
	prefix: 't_'
})

基本的查询

import linsql from 'linsql'
const db = linsql.connect({
	host: 'localhost',
	password: 'root',
	user: 'root',
	database: 'school',
	prefix: 'sc_'
})
db.name('teacher').where('username', '').select((rows) => {
	//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

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 JoinType = '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_'
}
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]
		} 
		db.name('teacher').where('id',1)
        let map = db.getEscapMap()
        expect(map).toEqual(data)
	})
})

Keywords

FAQs

What is linsql?

Is linsql popular?

Is linsql well maintained?

Package last updated on 23 Mar 2022

Did you know?

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

linsql