bsql
bsql是一个mysql客户端,为开发者提供了一套类sql语义的接口。最大程度利用sql语法,不做过度封装,轻量透明又不失灵活性,支持typescript。
安装
yarn add bsql# or npm i bsql --save
用法
初始化
; const db = ; const rows = await db;
bsql底层基于mysql连接池,访问https://github.com/mysqljs/mysql#pool-options 查看更多配置。 注意:字段名会自动在驼峰(js)和蛇形命名(数据库表)之间转换。
查询
// 查询全部字段db; // SELECT * FROM `user`;db; // 缺省参数,和前者等价 // 查询特定字段db; // SELECT `id`, `name` FROM `user`; // 泛型参数(TS)const user: User = await dbselect<User>*; // and条件db; // SELECT * FROM `user` WHERE `name` = 'lili' AND `age` = 20; // or条件db; // SELECT * FROM `user` WHERE `name` = 'lili' OR `age` = 20; // 比较 $gt(>) $gte(>=) $lt(<) $lte(<=) $not(<>)db; // SELECT * FROM `user` WHERE age > 30; // 模糊匹配 $like(LIKE) $notLike(NOT LIKE)db; // SELECT * FROM `user` WHERE `name` LIKE '%li'; // 包含 $in(IN) $notIn(NOT IN)db; // SELECT * FROM `user` WHERE `name` IN ('lili', 'kk'); // 空值db; // SELECT * FROM `user` WHERE `name` IS NULL; // 非空db; // SELECT * FROM `user` WHERE `name` IS NOT NULL; // 分组查询db; // SELECT * FROM `user` GROUP BY `zip_code` // 分组条件db; // SELECT * FROM `user` GROUP BY `zip_code` HAVING `age`=12; // 排序,1: ASC, -1: DESCdb; // SELECT * FROM `user` ORDER BY `name` ASC, `age` DESC; // limit offsetdb; // SELECT * FROM `user` LIMIT 20 OFFSET 10;
计数
db; // SELECT COUNT(*) as count FROM `user` WHERE `age` > 20;
新增
db; // INSERT INTO `user` (`name`, `age`) VALUES ('lili', 21), (lucy, 22); // 针对重复值做操作,一般用于批量更新db; // INSERT INTO `user` (`name`, `age`) VALUES ('lili', 21), (lucy, 22) ON DUPLICATE KEY UPDATE name=VALUES(name);
更新
db; // UPDATE `user` SET `age` = 23, `gender` = 2 WHERE `name` = 'lili';
删除
db; // DELETE FROM `user` WHERE `name` = 'lucy';
批量更新
// 批量更新第二个参数是参照字段,原理见:https://dev.mysql.com/doc/refman/5.7/en/case.htmldb;
执行原始sql
db; // delete from user where name='leo';
事务
const trans = await db;try trans; trans; trans; catche trans;