Select 查询

在表(table)或视图(view)上执行 SELECT 查询。

  • 默认情况下，Supabase项目返回最多1,000行数据。你可以在项目的API设置中更改此设置。建议将其保持较低，以限制意外或恶意请求的负载大小。你可以使用range()查询来对数据进行分页处理。
  • select()可以与过滤器(Filters)组合使用，用于过滤数据。
  • select()可以与修饰器(Modifiers)组合使用，用于对数据进行修改。
  • 如果你使用Supabase平台，apikey是一个保留关键字，应避免将其用作列名。

案例教程

案例1 (获取数据)#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

案例2 (选择特定列)#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

案例3 (查询外键表)#

1create table
2  countries (id int8 primary key, name text);
3create table
4  cities (
5    id int8 primary key,
6    country_id int8 not null references countries,
7    name text
8  );
9
10insert into
11  countries (id, name)
12values
13  (1, 'Germany'),
14  (2, 'Indonesia');
15insert into
16  cities (id, country_id, name)
17values
18  (1, 2, 'Bali'),
19  (2, 1, 'Munich');

案例4 (通过连接表查询外键表)#

1create table
2  users (
3    id int8 primary key,
4    name text
5  );
6create table
7  teams (
8    id int8 primary key,
9    name text
10  );
11-- join table
12create table
13  users_teams (
14    user_id int8 not null references users,
15    team_id int8 not null references teams,
16    -- both foreign keys must be part of a composite primary key
17    primary key (user_id, team_id)
18  );
19
20insert into
21  users (id, name)
22values
23  (1, 'Kiran'),
24  (2, 'Evan');
25insert into
26  teams (id, name)
27values
28  (1, 'Green'),
29  (2, 'Blue');
30insert into
31  users_teams (user_id, team_id)
32values
33  (1, 1),
34  (1, 2),
35  (2, 2);

案例5 (多次查询同一外键表)#

1 create table
2 users (id int8 primary key, name text);
3
4 create table
5   messages (
6     sender_id int8 not null references users,
7     receiver_id int8 not null references users,
8     content text
9   );
10
11 insert into
12   users (id, name)
13 values
14   (1, 'Kiran'),
15   (2, 'Evan');
16
17 insert into
18   messages (sender_id, receiver_id, content)
19 values
20   (1, 2, '👋');

案例6 (通过外键表进行筛选)#

1create table
2  countries (id int8 primary key, name text);
3create table
4  cities (
5    id int8 primary key,
6    country_id int8 not null references countries,
7    name text
8  );
9
10insert into
11  countries (id, name)
12values
13  (1, 'Germany'),
14  (2, 'Indonesia');
15insert into
16  cities (id, country_id, name)
17values
18  (1, 2, 'Bali'),
19  (2, 1, 'Munich');

案例7 (使用COUNT对外键表进行查询)#

1create table countries (
2  "id" "uuid" primary key default "extensions"."uuid_generate_v4"() not null,
3  "name" text
4);
5
6create table cities (
7  "id" "uuid" primary key default "extensions"."uuid_generate_v4"() not null,
8  "name" text,
9  "country_id" "uuid" references public.countries on delete cascade
10);
11
12with country as (
13  insert into countries (name)
14  values ('united kingdom') returning id
15)
16insert into cities (name, country_id) values
17('London', (select id from country)),
18('Manchester', (select id from country)),
19('Liverpool', (select id from country)),
20('Bristol', (select id from country));

案例8 (使用COUNT选项进行查询)#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

案例9 (查询JSON数据)#

1create table
2  users (
3    id int8 primary key,
4    name text,
5    address jsonb
6  );
7
8insert into
9  users (id, name, address)
10values
11  (1, 'Avdotya', '{"city":"Saint Petersburg"}');

参数说明

  • 列（column）[可选参数]
    query类型

    要检索的列，用逗号分隔

  • 选项（option）[必要参数]
    object类型

    命名的参数

      特性

    • count[可选参数]
      exact
      |
      planned
      |
      estimated

      用来计算表格或视图中的行数的算法。

      exact:可以精确计算行数，但执行速度较慢。执行 COUNT(*) 操作。

      planned:可以快速计算行数，但是结果可能略有偏差。使用了Postgres 的统计数据。

      estimated:对于较小的数值使用精确计数，对于较大的数值使用计划计数。根据行数的大小决定使用精确计数或计划计数的算法。

    • head[可选参数]
      boolean类型

      当设置为 true时，data将不被返回。 如果你只需要计数，则很有用。