总结-postman连接Mysql几种方式，以及接口方式做Mysql基本操作

写在前面

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
    }
]

1.4、以上只是一些简单的查询测试，还有更多的增改删操作，这里就不演示了，这里的实现方式是基于Node开发的一个组件 xMysql,可看到那些对外提供的接口（有限的），里面其实封装了Node对Mysql的操作