# linsql **Repository Path**: gpcsoftbase/linsql ## Basic Information - **Project Name**: linsql - **Description**: 使用TS封装node-mysql的常用操作,可链式调用,与ThinkPHP的查询器类似 功能还在优化 - **Primary Language**: TypeScript - **License**: MIT - **Default Branch**: main - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 1 - **Forks**: 0 - **Created**: 2022-04-06 - **Last Updated**: 2022-09-22 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # linsql 使用TS封装node-mysql的常用操作,可链式调用,与ThinkPHP的查询器类似 部分功能还在优化 - [Install](#install) - [Introduction](#introduction) - [Jest](#jest) ## Install ```sh npm install --save linsql ``` ## Introduction ### 配置(CONFIG) 参数 | 说明 | 类型 | 默认值 -|--|--|-- host | 数据库的主机名 | `string`| 必填项 port| 要连接的端口号 | `string` | 3306 user| MySQL用户 | `string` | 必填项 password| MySQL用户的密码 | `string` | 必填项 database| 数据库名称 | `string` | 必填项 prefix| 数据库的前缀 | `string` | 可选 其他配置 [mysqljs/mysql](https://github.com/mysqljs/mysql#connection-options) ## 创建连接 ```js import linsql from 'linsql' const db = linsql.connect({ host: 'localhost', password: 'root', user: 'root', database: 'demo', prefix: 't_' }) ``` ## 基本的查询 ```js import linsql from 'linsql' const db = linsql.connect({ host: 'localhost', password: 'root', user: 'root', database: 'school', prefix: 'sc_' }) db.name('teacher').where('username', '').select().then(res => { //return [rows] }) ``` ## Promise化 - ### select ```js //select db.name('teacher').where('username', '').select().then(res => {}) async function find() { let res = await db.name('teacher').where('username','').find() } ``` - ### update ```js db.name('teacher').where('username', '').update({ username:'张三' }).then(res => { console.log(res) //return affected rows }) ``` - ### delete ```js db.name('teacher').where('username', '').delete().then(res => { console.log(res) //return affected rows }) ``` - ### insert ```js db .name('teacher') .insert([{ username: '李四', create_time: new Date(), }]) .then((res) => { console.log(res) //return { affectedRows, insertId } }) ``` - ### insertGetId ```js db .name('teacher') .insertGetId([{ username: '李四', create_time: new Date(), }]) .then((res) => { console.log(res) //return insert Id }) ``` - ### query ```js 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 } }) ``` ## Methods - ### name (names: string | string[] ) 主要用于指定操作的数据表(不包含表前缀) ```js db.name('teacher').where('username', '').select() //SELECT * FROM `sc_teacher` WHERE `username` = '' ``` ### 选择多张表 ```js db.name(['teacher', 'student']) //SELECT * FROM t_teacher,t_student ``` ### 设置表别名 ```js db.name(['teacher t', 'student s']) //SELECT * FROM t_teacher AS t,t_student AS s ``` - ### table (names:string | string[] ) 主要用于指定操作的数据表(包含表前缀) ```js 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 查询的条件 ```js //以下的查询都是等价的 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 ) 用于设置当前的数据表的别名 ```js 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 ) 主要用于返回指定的字段 ```js 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) 主要用于分页操作 ```js 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) ```js db.table('sc_teacher').where('username', '').group('id').select() //SELECT * FROM `sc_teacher` WHERE `username` = '' GROUP BY id ``` - ### distinct (isDistinct: boolean) ```js db.table('sc_teacher').field('username').distinct(true).select() //SELECT DISTINCT username FROM `sc_teacher` ``` - ### join (table: string, condition: string, joinType:sqlJoinType = 'INNER') ```js 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) ```js 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) 主要用于写注释,方便后续维护修改 ```js db.table('sc_teacher').comment("Query sc_teacher").where('username', '').select() //SELECT * FROM sc_teacher a WHERE a.username = '' ##Query sc_teacher ``` - ### transform (sql:string, values:any[]) 主要用于转换sql查询语句 ```js db.transform('SELECT * FROM sc_teacher a WHERE a.username = ?', ['']) //SELECT * FROM sc_teacher a WHERE a.username = '' ``` - ### parse () 解析查询器 ```js db.name('teacher').where('username', '').parse() //{ // sql:SELECT * FROM t_teacher WHERE `username` = ?, // values:[''] //} ``` ## Jest ```sh npm run test ``` - ### 例子 ```js 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) }) }) ```