PostgreSQL - 语法

PostgreSQL - 基本 SQL 语法

SQL（结构化查询语言）是与 PostgreSQL 交互所使用的语言。SQL 命令根据其功能可以分为几类。本章提供了最常用和最基本命令的指南，侧重于实用、现代的用法。如需完整、详尽的参考，请查阅 PostgreSQL 官方文档。

理解 SQL 语句

SQL 语句由词元（tokens）组成，它们是语言的基本构建块。这些包括关键字、标识符、常量和运算符。

词元	SELECT	id, name	FROM	products	WHERE	price > 50.00	;
类型	关键字	标识符	关键字	标识符	关键字	表达式	终止符

数据定义语言（DDL）

DDL 命令用于定义、修改和删除数据库结构或对象。

CREATE TABLE

定义新表。这是最基本的命令之一。

CREATE TABLE users (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- 现代的自增主键
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login TIMESTAMPTZ
);

ALTER TABLE

修改现有表结构。

-- 添加新列
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- 更改列的数据类型
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- 添加约束
ALTER TABLE users ADD CONSTRAINT email_check CHECK (email LIKE '%@%.%');

-- 重命名列
ALTER TABLE users RENAME COLUMN last_login TO last_active_at;

DROP TABLE

永久删除表及其所有数据。

-- 删除 'users' 表。此操作无法撤销。
DROP TABLE users;

-- 使用 CASCADE 级联删除依赖对象（例如视图）。
DROP TABLE users CASCADE;

数据操作语言（DML）

DML 命令用于在表中添加、检索、修改和删除数据。

INSERT INTO

向表中添加新行数据。

INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', 'hashed_password_string');

-- 一次插入多行
INSERT INTO users (username, email, password_hash)
VALUES
    ('bob', 'bob@example.com', 'another_hash'),
    ('charlie', 'charlie@example.com', 'yet_another_hash');

SELECT

从一个或多个表中检索数据。

SELECT id, username, email, created_at
FROM users
WHERE is_active = true AND username LIKE 'a%'
ORDER BY created_at DESC
LIMIT 10;

UPDATE

修改表中的现有数据。

-- 始终使用 WHERE 子句以避免更新所有行！
UPDATE users
SET last_active_at = NOW()
WHERE id = 1;

DELETE

从表中删除行。

-- 始终使用 WHERE 子句以避免删除所有行！
DELETE FROM users
WHERE username = 'charlie';

事务控制语言（TCL）

TCL 命令管理事务，通过将一组操作组合成一个单一的、全有或全无的单元来确保数据完整性。

BEGIN; -- 开始一个事务

-- 示例：资金转账
UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

-- 如果两个语句都成功，则使更改永久化
COMMIT;

-- 如果发生错误，您将发出 ROLLBACK 命令来代替
-- ROLLBACK;

数据控制语言（DCL）

DCL 命令用于管理用户对数据库对象的访问和权限。

GRANT

赋予用户或角色特定权限。

-- 首先，为您的应用程序创建一个角色
CREATE ROLE my_app_role;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE my_app_db TO my_app_role;

-- 授予表上的特定权限
GRANT SELECT, INSERT, UPDATE ON users TO my_app_role;

REVOKE

从用户或角色中撤销权限。

-- 撤销更新 users 表的权限
REVOKE UPDATE ON users FROM my_app_role;