查询数据

数据准备

CREATE TABLE `film` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键标识',
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称',
  `release_year` INT COMMENT '发行年份',
  `director` VARCHAR(50) COMMENT '导演',
  `duration` INT COMMENT '片长(分钟)',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电影表';

-- 填充数据
INSERT INTO `film` (`id`, `title`, `release_year`, `director`, `duration`, `_openid`) VALUES
(1, '霸王别姬', 1993, '陈凯歌', 171, '1977683311217119233'),
(2, '大话西游之大圣娶亲', 1995, '刘镇伟', 95, '1977683311217119233'),
(3, '让子弹飞', 2010, '姜文', 132, '1977683311217119233'),
(4, '流浪地球', 2019, '郭帆', 125, '1977683311217119233'),
(5, '战狼2', 2017, '吴京', 123, '1977683311217119233'),
(6, 'The Shawshank Redemption', 1994, 'Frank Darabont', 142, '1977683311217119233'),
(7, 'Forrest Gump', 1994, 'Robert Zemeckis', 142, '1977683311217119233'),
(8, 'Titanic', 1997, 'James Cameron', 194, '1977683311217119233'),
(9, 'Parasite', 2019, 'Bong Joon Ho', 132, '1977683311217119233'),
(10, 'Joker', 2019, 'Todd Phillips', 122, '1977683311217119233'),
(11, 'Spirited Away', 2001, 'Hayao Miyazaki', 125, '1977683311217119233'),
(12, 'Spider-Man: Into the Spider-Verse', 2018, 'Peter Ramsey', 117, '1977683311217119233'),
(13, 'Interstellar', 2014, 'Christopher Nolan', 169, '1977683311217119233'),
(14, 'Inception', 2010, 'Christopher Nolan', 148, '1977683311217119233'),
(15, 'Furious 7', 2015, 'James Wan', 137, '1977683311217119233');

1. 表数据查询

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

Response

HTTP/1.1 200 OK

Header:
    content-length: 1037
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "陈凯歌",
        "duration": 171,
        "id": 1,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "_openid": "1977683311217119233",
        "director": "刘镇伟",
        "duration": 95,
        "id": 2,
        "release_year": 1995,
        "title": "大话西游之大圣娶亲"
    }
    // ... 共 15 条数据
]

说明：

返回的数据是所有字段数据，默认/table?select=*

2. 指定返回字段查询

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=title,director,release_year' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

Header:
    content-length: 1080
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "director": "陈凯歌",
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "刘镇伟",
        "release_year": 1995,
        "title": "大话西游之大圣娶亲"
    },
    {
        "director": "姜文",
        "release_year": 2010,
        "title": "让子弹飞"
    }
    // ... 共 15 条数据
]

3. 带过滤条件查询

查询导演为 Christopher Nolan 的电影

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&director=eq.Christopher Nolan' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

Header:
    content-length: 260
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 169,
        "id": 13,
        "release_year": 2014,
        "title": "Interstellar"
    },
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 148,
        "id": 14,
        "release_year": 2010,
        "title": "Inception"
    }
]

4. 带逻辑条件查询

查询 2000 年后上映的电影，电影时长不超过 120 分钟

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&release_year=gte.2000&duration=lt.120' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

Header:
    content-length: 148
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "Peter Ramsey",
        "duration": 117,
        "id": 12,
        "release_year": 2018,
        "title": "Spider-Man: Into the Spider-Verse"
    }
]

查询 2000 年后上映的电影，导演是 Christopher Nolan 或者 姜文

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&release_year=gte.2000&or=(director.eq.Christopher Nolan, director.eq.姜文)' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

Header:
    content-length: 379
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "_openid": "1977683311217119233",
        "director": "姜文",
        "duration": 132,
        "id": 3,
        "release_year": 2010,
        "title": "让子弹飞"
    },
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 169,
        "id": 13,
        "release_year": 2014,
        "title": "Interstellar"
    },
    {
        "_openid": "1977683311217119233",
        "director": "Christopher Nolan",
        "duration": 148,
        "id": 14,
        "release_year": 2010,
        "title": "Inception"
    }
]

5. 带分页和排序查询

查询 2000 年前的电影，展示播放时长最长的 top3 电影

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=title,director,release_year,duration
    &release_year=lt.2000
    &order=duration.desc
    &limit=3' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: return=representation'

Response

HTTP/1.1 200 OK

Header:
    content-length: 266
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "director": "James Cameron",
        "duration": 194,
        "release_year": 1997,
        "title": "Titanic"
    },
    {
        "director": "陈凯歌",
        "duration": 171,
        "release_year": 1993,
        "title": "霸王别姬"
    },
    {
        "director": "Frank Darabont",
        "duration": 142,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    }
]

6. 计数 count 查询

查询 2000 年后的电影和总数

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=title,director,release_year,duration&release_year=gte.2000' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Prefer: count=exact'

Response

HTTP/1.1 200 OK

Header:
    content-length: 853
    content-range: 0-9/10
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "director": "姜文",
        "duration": 132,
        "release_year": 2010,
        "title": "让子弹飞"
    },
    {
        "director": "郭帆",
        "duration": 125,
        "release_year": 2019,
        "title": "流浪地球"
    },
    {
        "director": "吴京",
        "duration": 123,
        "release_year": 2017,
        "title": "战狼2"
    }

    // ...等 共计 10 条数据
]

说明：

指定 Header Prefer: count=exact 时，返回符合条件的数据总数
content-range: 0-9/10 表示本次查询返回的数据范围，共计 10 条数据，0表示无偏移，9 表示最后一条数据的索引，10 表示总数

7. 强制返回对象格式

当前数据返回都是以数组的形式返回，如果需要强制返回对象格式，可以指定 Header Accept: application/vnd.pgrst.object+json, 当明确只有一条数据返回时才能使用此方式。

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*&id=eq.1' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Accept: application/vnd.pgrst.object+json'

Response

HTTP/1.1 200 OK

Header:
    content-length: 121
    content-type: application/vnd.pgrst.object+json

    
Body:
{
    "director": "陈凯歌",
    "duration": 171,
    "id": 1,
    "release_year": 1993,
    "title": "霸王别姬",
    "_openid": "1977683311217119233"
}

说明：

指定 Header Accept: application/vnd.pgrst.object+json 时，返回的数据格式为对象，而不是数组
当明确只有一条数据返回时，可以使用此方式。当数据为空或超过一条时，此方式会报错。如下展示：

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=*' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \
-H 'Accept: application/vnd.pgrst.object+json'

Response

HTTP/1.1 406 Not Acceptable

Header:
    content-length: 184
    content-type: application/json; charset=utf-8

    
Body:
{
    "code": "ResponseUnacceptableSingleError",
    "details": "",
    "hint": "",
    "message": "JSON object requested, multiple (or no) rows returned, The result contains 15 rows"
}

8. 字面量查询

MySQL 允许创建带有空格等特殊字符的表和字段，此时 url 请求就需要做字面量处理(使用双引号标记)避免和特殊字符、关键字冲突。

-- 创建带空格的表名和字段名
CREATE TABLE `my table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `full name` VARCHAR(50),
  `email address` VARCHAR(100),
  `_openid` VARCHAR(64),
  PRIMARY KEY (`id`)
);

INSERT INTO `my table` (`id`, `full name`, `email address`)
VALUES (1, 'John Doe', 'john@example.com');

Request

curl -i -X 'GET http://{{host}}/v1/rdb/rest/my table?select="full name","email address"' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \

Response

HTTP/1.1 200 OK

Header:
    content-length: 61
    content-type: application/json; charset=utf-8

Body:
[
    {
        "email address": "john@example.com",
        "full name": "John Doe"
    }
]

查询数据

数据准备​

1. 表数据查询​

2. 指定返回字段查询​

3. 带过滤条件查询​

4. 带逻辑条件查询​

5. 带分页和排序查询​

6. 计数 count 查询​

7. 强制返回对象格式​

8. 字面量查询​

数据准备

1. 表数据查询

2. 指定返回字段查询

3. 带过滤条件查询

4. 带逻辑条件查询

5. 带分页和排序查询

6. 计数 count 查询

7. 强制返回对象格式

8. 字面量查询