postman连接数据库
写在前面
postman连接Mysql几种方式
- node + xMysql + mysql + postman
- node + drill + mysql + postman
- node + mysql + postman + pre-request-scripts
一、node + xMysql + mysql + postman 方式
1.1、基本环境
C:\Users\ZhengYuan>npm -version
6.12.1
C:\Users\ZhengYuan>node -v
v12.13.1
1.2、安装模块，建立连接
npm install -g xmysql
xmysql -h localhost -u root -p root -d pis
xmysql -h 数据库地址 -u 用户名 -p 密码 -d 数据库名
1.3、postman 连接测试
1.3.1、版本查询
localhost:3000/_version
测试返回
{
"mysql": "8.0.12",
"node": "12.13.1"
}
1.3.2、查询所有Api
localhost:3000/
返回Json，这里返回了所有可对Mysql操作的API，按表区分
[
{
"resource": "pis_user_role",
"routes": [
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/describe"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/count"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/groupby"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/distinct"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/ugroupby"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/chart"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/aggregate"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/findOne"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/autoChart"
},
{
"httpType": "post",
"routeUrl": "http://localhost:3000/api/pis_user_role"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role"
},
{
"httpType": "post",
"routeUrl": "http://localhost:3000/api/pis_user_role/bulk"
},
{
"httpType": "delete",
"routeUrl": "http://localhost:3000/api/pis_user_role/bulk"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/bulk"
},
{
"httpType": "put",
"routeUrl": "http://localhost:3000/api/pis_user_role"
},
{
"httpType": "patch",
"routeUrl": "http://localhost:3000/api/pis_user_role/:id"
},
{
"httpType": "delete",
"routeUrl": "http://localhost:3000/api/pis_user_role/:id"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/:id"
},
{
"httpType": "get",
"routeUrl": "http://localhost:3000/api/pis_user_role/:id/exists"
}
]
},
"http://localhost:3000/api/tables",
"http://localhost:3000/api/xjoin",
"http://localhost:3000/api/dynamic",
"/upload",
"/uploads",
"/download"
]
1.3.3、查询所有的表
localhost:3000/api/tables
1.3.4、分页排序
localhost:3000/api/pis_user?_p=0&_size=3&_sort=user_id
映射SQl
{
"error": {
"code": "ER_BAD_FIELD_ERROR",
"errno": 1054,
"sqlMessage": "Unknown column 'user_ids' in 'order clause'",
"sqlState": "42S22",
"index": 0,
"sql": "select * from `pis_user` ORDER BY `user_ids` ASC limit 0,3 "
}
}
1.3.5、条件过滤
localhost:3000/api/pis_user
?_where=(user_isd,lt,62000)~or((entrance_status,eq,0)~and(entry_time,lt,2021-01-01 00:00:00))
映射SQl
{
"error": {
"code": "ER_BAD_FIELD_ERROR",
"errno": 1054,
"sqlMessage": "Unknown column 'user_isd' in 'where clause'",
"sqlState": "42S22",
"index": 0,
"sql": "select * from `pis_user` where (`user_isd`<'62000')or((`entrance_status`='0')and(`entry_time`<'2021-01-01 00:00:00')) limit 0,20 "
}
}
1.3.6、分组
localhost:3000/api/pis_user/groupby?_fields=department_name,company_,create_by
返回 Json
[
{
"department_name": "后端能力开发组",
"company_": "软件股份有限公司",
"create_by": "admin",
"_count": 31
},
{
"department_name": "测试组",
"company_": "软件股份有限公司",
"create_by": "admin",
"_count": 30
},
{
"department_name": "阅读产品开发组",
"company_": "达软件股份有限公司",
"create_by": "admin",
"_count": 26
},
{
"department_name": "医疗产品开发组",
"company_": "达软件股份有限公司",
"create_by": "admin",
"_count": 21
}
]
1.3.7、分组 + 过滤条件
localhost:3000/api/pis_user/groupby?_fields=department_name,company_,create_by&_having=(_count,gt,20)
返回Json
[
{
"department_name": "后端能力开发组",
"company_": "软件股份有限公司",
"create_by": "admin",
"_count": 31
},
{
"department_name": "测试组",
"company_": "软件股份有限公司",
"create_by": "admin",
"_count": 30
},
{
"department_name": "阅读产品开发组",
"company_": "达软件股份有限公司",
"create_by": "admin",
"_count": 26
}
]
1.3.8、基本统计
/api/pis_user/aggregate?_fields=user_id
返回Json
[
{
"min_of_user_id": 50000,
"max_of_user_id": 67321,
"avg_of_user_id": 67055.679,
"sum_of_user_id": 30711501,
"stddev_of_user_id": 808.6410372441085,
"variance_of_user_id": 653900.3271152276
}
]
京公网安备 11010502036488号