PostgreSQL教程：SQL语法详解

2025-2-28

PostgreSQL教程：SQL语法深度剖析

1. 引言

结构化查询语言（SQL）是关系数据库管理系统的核心。无论您使用的是哪种RDBMS，如PostgreSQL、MySQL、Oracle或SQL Server，SQL都是与之交互的基础语言。PostgreSQL以其对SQL标准的严格遵守、强大的功能和开源特性而闻名。本教程旨在深入探讨PostgreSQL中的SQL语法，提供全面的细节和实用示例，帮助开发人员和数据库管理员掌握这一关键技术。

2. 数据定义语言 (DDL)

DDL语句用于定义和修改数据库的结构。

2.1. CREATE TABLE

CREATE TABLE 语句用于创建新的数据表。

sql
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2),
category_id INTEGER REFERENCES categories(category_id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

  • SERIAL PRIMARY KEY: 自动递增的整数，作为主键。
  • VARCHAR(255): 可变长度字符串，最大长度为255个字符。
  • NUMERIC(10, 2): 精确数值类型，总共10位数字，其中2位是小数。
  • REFERENCES: 外键约束，确保category_id 存在于 categories 表中。
  • DEFAULT CURRENT_TIMESTAMP: 插入新行时自动设置当前时间戳。

2.2. 数据类型

PostgreSQL提供了丰富的数据类型：

  • 数值类型: SMALLINT, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION
  • 字符类型: VARCHAR, CHAR, TEXT
  • 日期/时间类型: TIMESTAMP, DATE, TIME, INTERVAL
  • 布尔类型: BOOLEAN
  • 数组类型: 任何基本类型都可以加上[]成为数组类型, 例如INTEGER[]
  • JSON类型: JSON, JSONB
  • UUID类型: UUID
  • 枚举类型：CREATE TYPE

示例：

sql
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);

2.3. ALTER TABLE

ALTER TABLE 语句用于修改现有表的结构。

  • 添加列:

sql
ALTER TABLE products ADD COLUMN description TEXT;

  • 删除列:

sql
ALTER TABLE products DROP COLUMN description;

  • 修改列的数据类型:

sql
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 4);

  • 添加约束:

sql
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);

  • 重命名数据表
    sql
    ALTER TABLE old_table_name RENAME TO new_table_name;

2.4. DROP TABLE

DROP TABLE 语句用于删除表。

sql
DROP TABLE products;
DROP TABLE IF EXISTS product;可以避免product不存在时的错误。

2.5. CREATE INDEX

CREATE INDEX 语句用于创建索引以加速查询。

sql
CREATE INDEX idx_product_name ON products (product_name);

可以在多个列上创建复合索引：

sql
CREATE INDEX idx_product_category ON products (product_name, category_id);

2.6. DROP INDEX

DROP INDEX 用于删除索引

sql
DROP INDEX idx_product_name;

3. 数据操作语言 (DML)

DML语句用于查询和修改数据。

3.1. SELECT

SELECT 语句用于从表中检索数据。

sql
SELECT product_id, product_name, price FROM products;

  • WHERE子句: 过滤结果。

sql
SELECT * FROM products WHERE price > 100;

  • ORDER BY子句: 对结果排序。

sql
SELECT * FROM products ORDER BY price DESC;
ASC是默认排序方式（升序）

  • LIMIT子句: 限制返回的行数。

sql
SELECT * FROM products LIMIT 10;

  • OFFSET子句: 跳过指定数量的行。

sql
SELECT * FROM products OFFSET 5 LIMIT 10; -- 跳过前5行，返回接下来的10行

  • DISTINCT 关键字: 返回唯一不同的值。

sql
SELECT DISTINCT category_id FROM products;

3.2. INSERT

INSERT 语句用于向表中插入新行。

sql
INSERT INTO products (product_name, price, category_id) VALUES ('Laptop', 1200.00, 1);
如果插入的值和数据表列的顺序一致，可以不指定列名。

3.3. UPDATE

UPDATE 语句用于修改现有行。

sql
UPDATE products SET price = 1300.00 WHERE product_id = 1;

3.4. DELETE

DELETE 语句用于删除行。

sql
DELETE FROM products WHERE product_id = 1;

3.5 JOIN 操作

JOIN操作用于合并多个表中的数据。

  • INNER JOIN: 返回两个表中匹配的行。

不同JOIN操作的直观比较如下：

  1. INNER JOIN: 仅返回两个表中存在匹配的行。
  2. LEFT (OUTER) JOIN: 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配项，则右侧列返回NULL。
  3. RIGHT (OUTER) JOIN: 返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配项，则左侧列返回NULL。
  4. FULL (OUTER) JOIN: 返回左表和右表中的所有行。如果某一行在另一个表中没有匹配项，则另一侧的列返回NULL。

sql
SELECT p.product_name, c.category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id;

  • LEFT (OUTER) JOIN: 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配项，则右侧列返回NULL。

sql
SELECT p.product_name, c.category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id;

  • RIGHT (OUTER) JOIN: 返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配项，则左侧列返回NULL。

sql
SELECT p.product_name, c.category_name
FROM products p
RIGHT JOIN categories c ON p.category_id = c.category_id;

  • FULL (OUTER) JOIN: 返回两个表所有行，没有匹配的地方用NULL填充。

sql
SELECT p.product_name, c.category_name
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.category_id;

3.6. 子查询

子查询是嵌套在另一个查询中的查询。

sql
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

3.7. 聚合函数

聚合函数对一组值执行计算并返回单个值。

  • COUNT: 计算行数。

sql
SELECT COUNT(*) FROM products;

  • SUM: 计算总和。

sql
SELECT SUM(price) FROM products;

  • AVG: 计算平均值。

sql
SELECT AVG(price) FROM products;

  • MIN: 计算最小值。

sql
SELECT MIN(price) FROM products;

  • MAX: 计算最大值。

sql
SELECT MAX(price) FROM products;

  • GROUP BY 子句: 将结果分组。

sql
SELECT category_id, AVG(price) FROM products GROUP BY category_id;

  • HAVING 子句: 过滤分组后的结果。

sql
SELECT category_id, AVG(price) FROM products GROUP BY category_id HAVING AVG(price) > 50;

4. 数据控制语言 (DCL)

DCL语句用于控制数据库的访问权限。

4.1. GRANT

GRANT 语句用于授予用户权限。

sql
GRANT SELECT, INSERT ON products TO someuser;

4.2. REVOKE

REVOKE 语句用于撤销用户权限。

sql
REVOKE INSERT ON products FROM someuser;

5. 事务控制语句

事务控制语句用于管理数据库事务。

5.1. BEGIN

BEGIN 语句用于开始一个事务。

sql
BEGIN;

5.2. COMMIT

COMMIT 语句用于提交事务。

sql
COMMIT;

5.3. ROLLBACK

ROLLBACK 语句用于回滚事务。

sql
ROLLBACK;
在事务中可以设置保存点SAVEPOINT my_savepoint;，然后选择回滚到保存点ROLLBACK TO my_savepoint;。

6. 高级特性

6.1. 窗口函数

窗口函数对一组行（“窗口”）执行计算，而不会导致行被分组为单个输出行。

sql
SELECT
product_name,
price,
AVG(price) OVER (PARTITION BY category_id) AS avg_price_in_category
FROM products;
PARTITION BY 将数据按照category_id进行分区。

6.2. 公共表达式 (CTE)

CTE是一个命名的临时结果集，仅在单个查询的执行范围内存在。

sql
WITH expensive_products AS (
SELECT * FROM products WHERE price > 1000
)
SELECT * FROM expensive_products;

6.3. 存储过程和函数

PostgreSQL 允许创建存储过程和函数，这些过程和函数是用 PL/pgSQL（或其他支持的语言）编写的，并存储在数据库服务器中。

创建函数：
```sql
CREATE FUNCTION get_total_sales(product_id INT)
RETURNS NUMERIC AS $$
DECLARE
total_sales NUMERIC;
BEGIN
SELECT SUM(price) INTO total_sales
FROM order_items
WHERE product_id = $1;

RETURN total_sales;

END;
$$ LANGUAGE plpgsql;

调用函数：sql
SELECT get_total_sales(1);
存储过程不返回值：sql
CREATE PROCEDURE update_product_price(product_id INT, new_price NUMERIC)
AS $$
BEGIN
UPDATE products SET price = new_price WHERE id = product_id;
COMMIT;
END;
$$ LANGUAGE plpgsql;

```

6.4 触发器

触发器是数据库中定义的特殊类型的存储过程，它会在指定的数据库事件（例如 INSERT、UPDATE 或 DELETE 操作）发生时自动执行。

```sql
CREATE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO product_audit_log (product_id, old_price, new_price, changed_on)
VALUES (OLD.id, OLD.price, NEW.price, NOW());
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO product_audit_log (product_id, old_price, new_price, changed_on)
VALUES (NEW.id, null, NEW.price, NOW());
RETURN NEW;
END IF;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_update_trigger
AFTER UPDATE OR INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();
```

7. 内容补充

本教程详细阐述了PostgreSQL中的SQL语法，涵盖了数据定义、数据操作、数据控制和事务控制等方面。同时，通过大量代码示例，说明了DDL语句构建和修改数据库结构、DML语句查询和修改数据、DCL语句控制数据库访问权限，以及事务控制语句管理事务。

此外，本教程还介绍了PostgreSQL的一些高级特性，如窗口函数、公共表达式和触发器。掌握这些SQL语法和高级特性，将有助于开发人员和数据库管理员更有效地利用PostgreSQL管理和操作数据。

希望读者通过此教程对PostgreSQL的SQL语法有更全面和深入的了解。

版权声明：
作者：admin
链接：https://hostlocvps.com/2025/02/28/postgresql%e6%95%99%e7%a8%8b%ef%bc%9asql%e8%af%ad%e6%b3%95%e8%af%a6%e8%a7%a3/
文章版权归作者所有，未经允许请勿转载。
THE END
什么是SVN？Subversion版本控制系统介绍
<<上一篇
Windows 用户必看：OpenSSL 安装使用全攻略
下一篇>>
相关推荐
Cursor：掌握数据库查询的核心技术
如何选择高纯净度IP服务商
Tailwind CSS: 告别繁琐的 CSS 样式
MSDN I Tell You：微软文档搜索的终极指南
Flask 框架：轻量级Web应用开发的最佳选择
D语言加密：保护你的数据安全
HTML编译器：原理、用途及最佳选择
优化Nginx：`proxy_set_header`在真实场景中的应用
什么是Remix？下一代全栈Web框架深度解析
Cloudflare WAF：抵御常见Web攻击的利器