使用 PostgreSQL 查询数据

2020-09-05 08:00:00 · 飞浪

介绍 PostgreSQL是一种高效且灵活的数据库，适用于各种用例。在本指南中，您将了解查询PostgreSQL数据库的不同方法，以便为您的应用或库创建简单且富有表现力的后端。让我们开

介绍

PostgreSQL 是一种高效且灵活的数据库，适用于各种用例。在本指南中，您将了解查询 PostgreSQL 数据库的不同方法，以便为您的应用或库创建简单且富有表现力的后端。

让我们开始吧！

SELECT 语句

SELECT 语句是查询 PostgreSQL 数据库的基本构建块。SELECT 语句可以包含多个子句。一些比较重要的子句包括：

从
在哪里
分组依据
排序依据
限制

以下查询展示了使用上述所有子句的 SELECT 语句。

      SELECT id, first_name, last_name
FROM person
WHERE age > 20
GROUP BY country
ORDER BY last_name
LIMIT 10
    

上述查询将从人员表中抓取前 10 名年龄超过 20 岁的人，按姓氏排序并按其居住的国家/地区分组。如您所见，查询中的 SELECT 关键字后面跟着一组列。从那里，您可以使用FROM子句指定一个或多个表。WHERE子句用于过滤返回的行。GROUP BY子句用于按一个或多个列将行分组在一起，而ORDER BY子句用于按升序或降序对行进行排序。LIMIT子句用于限制查询返回的行数。

还有许多其他子句可供使用。其中一些包括不同的连接子句和OFFSET子句，但这些超出了本指南的范围。有关更多信息，请查看文档。

WITH 查询 - 通用表表达式

WITH查询或公用表表达式提供了一种简洁的方式来创建临时表，这些临时表只保存在内存中，直到最终查询完成。WITH查询通常比创建多个临时表进行查询更快、更易读。下面的 SQL 使用公用表表达式按国家/地区选择狗和人的平均年龄。

      WITH dog_ages AS (
    SELECT AVG(age) AS avg_dog_age, country 
    FROM animals
    GROUP BY country 
    WHERE species = 'dog'
), person_ages AS (
    SELECT AVG(age) as avg_person_age, country 
    FROM person
    GROUP BY country
)
SELECT da.avg_dog_age, pa.avg_person_page, da.country
FROM dog_ages da
LEFT JOIN person_ages pa ON pa.country = da.country;
    

如您所见，通用表表达式是一种功能强大且富有表现力的查询 PostgreSQL 数据库的方法。请记住，无论您在哪里使用临时表，您都可以使用通用表表达式来获得更快的结果！

视图

视图是 PostgreSQL 封装查询的方式，以便可以在数据库架构的不同部分重复使用它们。视图使您的查询可以被整个数据库中的其他查询、触发器或函数使用。以下 SQL 是用于查询我们模拟的人员表的视图示例。

      CREATE VIEW vw_person_seniors AS
    SELECT p.id, p.age, p.first_name, p.last_name
    FROM person p
    WHERE age >= 65;
    

除了使查询可重复使用之外，视图还是使后端代码更易读和更易于维护的绝佳方法。通过创建视图，您可以构建查询，使其按应用内的功能分开。这可以显著提高应用架构中的可维护性和关注点分离。

物化视图

物化视图是一种在创建时立即执行的视图。视图执行的查询结果存储在磁盘上，并在查询物化视图时检索。这在性能方面具有巨大的优势！需要几秒的查询可以缩短到几毫秒。下面，您可以看到一个示例物化视图。

      CREATE MATERIALIZED VIEW mat_person AS
    SELECT p.id, p.age, p.first_name, p.last_name
    FROM person p
    INNER JOIN blog_posts bp ON bp.person_id = p.id;
    

使用物化视图的最大缺点是，除非您通过运行REFRESH MATERIALIZED VIEW <mat_table> 来刷新它们，否则您在查询视图时可能会得到过时的数据。这就是为什么当您查询的数据大多是静态的且很少更新时，物化视图效果最好。

结论

在本指南中，您了解了在 PostgreSQL 中查询数据的所有关键方法。您了解了 SELECT 查询如何构成查询 PostgreSQL 数据库的基本基础，以及如何使用 CTE（通用表表达式）将临时表组合在一起。在 SELECT 查询的基础上，您学习了如何创建 PostgreSQL 视图以优雅的方式组织查询。您还学习了当陈旧数据的可能性对您的用户群来说不是问题时，如何利用物化视图来大幅提升查询的性能。

现在，您可以放心查询 PostgreSQL 数据库。有关更多信息，请查看 PostgreSQL文档。

_{免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！}

_查看原文

技术指南

阅读全文

使用 PostgreSQL 查询数据

杭州电子商务研究院

4年前 · 面向社会、服务行业、政产学研结合、整合资源、和谐发展

WITH dog_ages AS ( SELECT AVG(age) AS avg_dog_age, country FROM animals GROUP BY country WHERE species = 'dog' ), person_ages AS ( SELECT AVG(age) as avg_person_age, country FROM person GROUP BY country ) SELECT da.avg_dog_age, pa.avg_person_page, da.country FROM dog_ages da LEFT JOIN person_ages pa ON pa.country = da.country;