📣TiDB Cloud BYOC、TiDB Cloud Filesystem 和 PostgreSQL-compatible TiDB Cloud Starter 全新发布,开放公测中。此页面由 AI 自动翻译,英文原文请见此处。

使用 TiDB Cloud 探索兼容 PostgreSQL 的 SQL



兼容 PostgreSQL 的 TiDB Cloud Starter 支持 PostgreSQL wire 协议和常见的 PostgreSQL 语法。你可以使用大多数 PostgreSQL 客户端、驱动和 ORM 来操作你的实例。

本页将带你了解基本的 SQL 操作,包括 DDL、DML、DQL、DCL 和事务控制。

如需了解不受支持或行为不同的 PostgreSQL 特性,请参见 PostgreSQL 兼容性。

类别

SQL 语句可以根据其功能分为以下几类:

  • DDL(数据定义语言):定义和管理数据库对象,包括数据库、数据库模式、表、视图、索引、序列和类型。
  • DML(数据操作语言):插入、修改和删除表数据。
  • DQL(数据查询语言):使用 SELECT、过滤、连接、聚合、子查询、公用表表达式(CTE)以及其他查询特性来查询数据。
  • DCL(数据控制语言):管理权限和角色成员关系。
  • 事务控制:使用 BEGIN、COMMIT、ROLLBACK 和 SAVEPOINT 等语句管理事务。

常见的 DDL 操作包括创建、修改和删除对象。对应的命令分别是 CREATE、ALTER 和 DROP。

列出、创建和删除数据库

要列出数据库,请查询 pg_database 系统目录:

SELECT datname FROM pg_database ORDER BY datname;

要创建一个名为 samp_db 的数据库,请使用 CREATE DATABASE 语句:

CREATE DATABASE samp_db;

使用 psql 时,要连接到该数据库,请使用 \connect 命令:

\connect samp_db

要删除数据库,请先连接到另一个数据库:

\connect postgres

然后使用 DROP DATABASE 语句:

DROP DATABASE samp_db;

创建、列出和删除表

要创建表,请使用 CREATE TABLE 语句。

例如,创建一个名为 person 的表:

CREATE TABLE person ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, birthday DATE );

要列出 public 数据库模式中的表:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name;

要查看 person 表的列:

SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'person' ORDER BY ordinal_position;

要删除该表,请使用 DROP TABLE 语句:

DROP TABLE person;

创建、列出和删除索引

索引可以提升对已建立索引列的查询性能。

要在 name 列上创建索引:

CREATE INDEX person_name_idx ON person (name);

要创建唯一索引:

CREATE UNIQUE INDEX person_name_unique_idx ON person (name);

要列出 person 表上的索引:

SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'person' ORDER BY indexname;

要删除索引,请使用 DROP INDEX 语句:

DROP INDEX person_name_idx;

有关支持的 PostgreSQL 索引方法及兼容性差异,请参见 PostgreSQL 兼容性。

插入、修改和删除数据

常见的 DML 操作使用 INSERT、UPDATE 和 DELETE 语句。

要向 person 表中插入一行:

INSERT INTO person (name, birthday) VALUES ('Tom', DATE '1990-09-12');

要插入多行:

INSERT INTO person (name, birthday) VALUES ('Alice', DATE '1992-05-01'), ('Bob', DATE '1993-08-08');

兼容 PostgreSQL 的 TiDB Cloud Starter 支持 RETURNING。例如,插入一行后返回生成的 ID:

INSERT INTO person (name, birthday) VALUES ('Carol', DATE '1994-03-15') RETURNING id, name;

要修改一行:

UPDATE person SET birthday = DATE '1990-10-12' WHERE name = 'Tom';

要删除一行:

DELETE FROM person WHERE name = 'Bob';

Upsert 数据

使用 INSERT ... ON CONFLICT 插入一行数据或处理唯一性冲突。

例如:

CREATE TABLE account ( id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE, name TEXT ); INSERT INTO account (email, name) VALUES ('alice@example.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = excluded.name;

查询数据

使用 SELECT 语句查询数据。

查询所有列:

SELECT * FROM person;

查询指定列:

SELECT id, name FROM person;

使用 WHERE 子句过滤行:

SELECT id, name, birthday FROM person WHERE birthday >= DATE '1992-01-01' ORDER BY birthday;

PostgreSQL-compatible TiDB Cloud Starter 还支持 PostgreSQL 的常见查询特性,例如 join、子查询、CTE、递归 CTE、窗口函数和集合操作。

例如,使用 CTE 过滤数据:

WITH recent_people AS ( SELECT id, name, birthday FROM person WHERE birthday >= DATE '1992-01-01' ) SELECT * FROM recent_people ORDER BY birthday;

管理事务

使用 BEGIN 和 COMMIT 在一个事务中执行多条语句:

BEGIN; UPDATE account SET name = 'Alice Chen' WHERE email = 'alice@example.com'; COMMIT;

要丢弃当前事务中的更改,请使用 ROLLBACK:

BEGIN; DELETE FROM account WHERE email = 'alice@example.com'; ROLLBACK;

也支持保存点:

BEGIN; UPDATE account SET name = 'Alice' WHERE email = 'alice@example.com'; SAVEPOINT before_second_update; UPDATE account SET name = 'Alice Updated' WHERE email = 'alice@example.com'; ROLLBACK TO SAVEPOINT before_second_update; COMMIT;

有关支持的事务隔离级别以及与 PostgreSQL 的兼容性差异,请参见 PostgreSQL 兼容性。

创建角色、为其授权并删除角色

DCL 语句用于管理权限和角色成员关系。

创建一个可登录的角色:

CREATE ROLE app_user LOGIN PASSWORD 'SecurePass1';

为该角色授予对 public 数据库模式的访问权限,以及查询 person 表的权限:

GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT ON person TO app_user;

检查授予该角色的表权限:

SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'app_user' ORDER BY table_schema, table_name, privilege_type;

回收这些权限:

REVOKE SELECT ON person FROM app_user; REVOKE USAGE ON SCHEMA public FROM app_user;

删除该角色:

DROP ROLE app_user;

文档内容是否有帮助?