node-mysql-promise
Install
$ npm install node-mysql-promise
Introduction
node mysql操作封装类,基于promise,借鉴75team开源项目thinkjs中model操作,数据库连接使用node-mysql的连接池。
使用示例
var Mysql = ;var mysql = Mysql;//SELECT * FROM table;mysql;
API
配置
host
: 连接的host(默认: localhost)port
: 连接端口user
: 用户名password
: 密码database
: 数据库名tablePrefix
: 数据表前缀charset
: 编码(默认: UTF8_GENERAL_CI)timezone
: 时区(默认: 'local')connectTimeout
: 连接超时时间(默认: 10000)connectionLimit
: 最大连接数(默认: 10)logSql
: 控制台输出sql(默认: false)
方法
table(tableName)
设置要查询的表(必需)
tableName
String 要查询的表return
this
//SELECT * FROM `table`mysql
field(field, reverse)
设置要查询的字段
field
String|Array 要查询的字段,可以是字符串,也可以是数组reverse
Boolean 是否反选字段return
this
//SELECT * FROM `table`mysql;//SELECT `id`, `title` FROM `table`mysql;//SELECT `id`, `title` FROM `table`mysql;//SELECT `author`, `date` FROM `table`mysql;
limit(offset, length)
设置查询的数量
offset
Number 起始位置length
Number 查询的数目return
this
//SELECT * FROM `table` LIMIT 10mysql;//SELECT * FROM `table` LIMIT 10, 20mysql;
page(page, listRows)
设置当前查询的页数,页数从1开始
page
Number 当前的页数listRows
Number 一页记录条数,默认20条return
this
//SELECT * FROM `table`mysql;//SELECT * FROM `table` LIMIT 0,20mysql;//SELECT * FROM `table` LIMIT 10, 20mysql;
union(union, all)
联合查询
union
String 联合查询的字符串all
是否为UNION ALL模式return
this
//SELECT * FROM `table` UNION (SELECT * FROM `table2`)mysql;//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)mysql;//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)mysql;//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`) UNION (SELECT * FROM `table3`)mysql
join(join)
组合查询
join
String|Array|Objectreturn
this
//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.idmysql;//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id RIGHT JOIN `table3` ON table.sid = table3.sidmysql;//SELECT * FROM `table` INNER JOIN `table2` on table.id = table2.idmysql;//SELECT * FROM `table` AS a LEFT JOIN `table2` AS b ON a.id = b.id LEFT JOIN `table3` AS c ON a.sid = c.sidmysql; //SELECT * FROM `table` AS a LEFT JOIN `table2` AS b ON a.id = b.id LEFT JOIN `table3` AS c ON a.sid = c.sidmysql; //SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id LEFT JOIN `table3` ON (table.sid = table3.sid AND table.name = table3.title);mysql;
order(order)
设置排序方式
order
String|Array|Obeject 排序方式return
this
//SELECT * FROM `table` ORDER BY `id`mysql; //SELECT * FROM `table` ORDER BY `id` DESCmysql;//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASCmysql;//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASCmysql;//SELECT * FROM `table` ORDER BY `id` DESC `title` ASCmysql;
alias(alias)
设置表别名
alias
String 表别名return
this
//SELECT * FROM `table` AS tmysql;
having(str)
having查询
str
String having查询的字符串return
this
//SELECT * FROM `table` HAVING `id` > 1 AND `id` < 100mysql;
group(field)
分组查询
field
String 设定分组查询的字段return
this
//SELECT * FROM `table` GROUP BY `date`mysql;
distinct(field)
去重查询
field
String 去重的字段return
this
//SELECT DISTINCT `title` FROM `table`mysql;
where(where)
设置where条件
where
Sting|Object 查询条件return
this
普通条件
//SELECT * FROM `table` WHERE `id` = 100;mysql;//SELECT * FROM `table` WHERE `id` = 100;mysql;//SELECT * FROM `table` WHERE `id` = 100 OR `id` < 2mysql;//SELECT * FROM `table` WHERE `id` != 100mysql
EXP条件
默认会对字段和值进行转义,如果不希望被转义,可是使用EXP的方式
//SELECT * FROM `table` WHERE `name` = 'name'mysql;//UPDATE `table` SET `num' = `num`+1mysql;
LIKE条件
//SELECT * FROM `table` WHERE (`title` NOT LIKE 'title')mysql;//SELECT * FROM `table` WHERE (`title` LIKE '%title%')mysql;//LIKE多个值//SELECT * FROM `table` WHERE (`title` LIKE 'title' OR `title` LIKE 'name')mysql;//多个字段LIKE同一个值,OR的关系//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') OR (`content` LIKE '%title%'))mysql;//多个字段LIKE同一个值,AND的关系//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') AND (`content` LIKE '%title%'))mysql;
IN条件
//SELECT * FROM `table` WHERE (`id` IN (1,2,3))mysql;//SELECT * FROM `table` WHERE (`id` IN (1, 2, 3))mysql;//SELECT * FROM `table` WHERE (`id` NOT IN (1, 2, 3))mysql;
多字段查询
//SELECT * FROM `table` WHERE (`id` = 10) AND (`title` = 'title')mysql;//OR//SELECT * FROM `table` WHERE (`id` = 10) OR (`title` = 'title')mysql;//XOR//SELECT * FROM `table` WHERE (`id` = 10) XOR (`title` = 'title')mysql;
BETWEEN
//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)mysql;//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)mysql;
复合查询
//SELECT * FROM `table` WHERE `id` > 10 AND `id` < 20mysql;//SELECT * FROM `table` WHERE `id` < 10 OR `id` > 20mysql;//SELECT * FROM `table` WHERE (`id` > 10 AND `id` < 20) OR (`title` LIKE '%title%')mysql;//SELECT * FROM `table` WHERE (`title` = 'title') AND ((`id` IN (1, 2, 3)) OR (`content` = 'content'))mysql;
count(field)
查询符合条件的数目
field
String count的字段return
promise
//SELECT COUNT(`id`) FROM `table` LIMIT 1mysql
sum(field)
求和
field
String 要求和的字段return
promise
//SELECT SUM(`num`) FROM `table` LIMIT 1mysql;
max(field)
求字段的最大值
field
String 要求最大值的字段return
promise
//SELECT MAX(`num`) FROM `table` LIMIT 1mysql;
min(field)
求字段的最小值
field
String 要求最小值的字段return
promise
//SELECT MIN(`num`) FROM `table` LIMIT 1mysql
avg(field)
求字段的平均值
field
Sting 要求平均值的字段return
promise
//SELECT AVG(`num`) FROM `table` LIMIT 1;mysql
add(data)
插入数据
data
Object 要插入的数据return
promise
var data = title: 'title' content: 'content';mysql
thenAdd(data, where, returnDetail)
当数据表中不存在where条件对应的数据时才进行插入
data
Object 要插入的数据where
String|Array|Object 检测的条件returnDetail
Boolean 是否返回详细的信息
//假设字段title为UNIQUEvar data = title: 'title' content: 'content';var where = title: 'title'mysql //返回详细信息mysql
addAll(data)
一次添加多条数据
data
Arrayreturn
promise
var data = title: 'xxx' title: 'yyy';mysql
delete()
删除数据
return
promise
//删除所有数据mysql //删除符合条件的数据mysql
update(data)
更新数据,需要条件
data
Object 要更新的数据return
promise
mysql
select()
查询符合条件的数据
return
promise
mysql
find()
查找一条符合条件的数据
return
promise
mysql
updateInc(field, step)
字段值增加
field
String 要增加的字段step
Number 增加的数值,默认为1return
promise
//将id为1的num字段加10mysql
updateDec(field, step)
字段值减少
field
String 要减少的字段step
Number 减少的数字,默认为1return
promise
//将id为1的num字段值减10mysql
getField(field, onlyOne)
获取某个字段的值
field
String 要获取的字段,可以是多个字段(用,隔开)onlyOne
Boolean|Array 是否只需要一个值,或者是需要几个值
//取id>100的id集合mysql //只需要id>100的一个值mysql//只需要id>100的3个值mysql//需要id和title两个字段的值mysql
countSelect(options, flag)
options
查询参数flag
Boolean 当分页值不合法的时候,处理情况。true为修正到第一页,false为修正到最后一页,默认不进行修正return
promise
//查询1-20条数据mysql;
query(sql, parse)
自定义sql语句进行查询
sql
String 要执行的sql语句parse
格式参数的数据return
promise
var data = '*' 'table' 'id > 100' mysql
execute(sql, parse)
自定义sql语句执行,使用与query相同,返回数据不同,execute返回影响行数
close()
关闭连接池连接,非特殊情况,不建议使用