当前位置：首页 > 虚拟主机 > 正文

pg数据库查询语句怎么写？基本语法和实例有哪些？

PostgreSQL（简称PG）是一款功能强大的开源对象关系型数据库管理系统，其查询语句（SQL）是用户与数据库交互的核心工具，掌握PG的查询语句不仅能高效检索数据，还能优化数据库性能，本文将详细介绍PG数据库查询语句的基础语法、常用功能、高级特性及最佳实践,帮助用户全面理解和使用SQL语言。

基础查询语句

基础查询是SQL的核心，主要包括SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY等子句,以下是一个简单的查询示例：

SELECT column1, column2 FROM table_name WHERE condition GROUP BY column1 HAVING group_condition ORDER BY column1 DESC;

SELECT子句：指定要查询的列，使用表示所有列。SELECT id, name FROM users;查询users表的id和name列。
FROM子句：指定查询的表名。FROM orders;表示从orders表查询数据。
WHERE子句：用于过滤记录，支持比较运算符（=, >, <）、逻辑运算符（AND, OR, NOT）以及模糊查询（LIKE, ILIKE）。WHERE age > 18 AND name LIKE 'A%';查询年龄大于18且姓名以A开头的用户。
GROUP BY子句：对结果进行分组，常与聚合函数（COUNT, SUM, AVG等）一起使用。SELECT department, COUNT(*) FROM employees GROUP BY department;统计每个部门的员工数量。
HAVING子句：与WHERE类似，但用于过滤分组后的结果。HAVING COUNT(*) > 5;筛选员工数量超过5个的部门。
ORDER BY子句：对结果进行排序，ASC表示升序，DESC表示降序。ORDER BY salary DESC;按薪资降序排列。

PG提供了丰富的内置函数,简化数据处理：

聚合函数：如COUNT(*)统计行数，SUM(column)计算总和，AVG(column)计算平均值，MAX(column)和MIN(column)获取最大最小值。
字符串函数：如CONCAT(str1, str2)拼接字符串，SUBSTRING(str FROM start FOR length)截取子串，LOWER(str)和UPPER(str)转换大小写。
日期时间函数：如NOW()返回当前时间，EXTRACT(YEAR FROM date)提取日期中的年份，DATE_PART('month', date)获取月份。
数学函数：如ROUND(number)四舍五入，CEILING(number)向上取整，FLOOR(number)向下取整。

示例：查询订单表中每个客户的总消费金额,并按金额降序排列：

SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id ORDER BY total_spent DESC;

实际应用中常需关联多表查询,PG支持多种连接方式：

pg数据库查询语句怎么写？基本语法和实例有哪些？第1张

INNER JOIN：返回两表中匹配的记录。 SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
LEFT JOIN：返回左表所有记录及右表匹配记录，右表无匹配则显示NULL。 SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
RIGHT JOIN：与LEFT JOIN相反,返回右表所有记录及左表匹配记录。
FULL OUTER JOIN：返回两表所有记录,无匹配则显示NULL。

示例：查询所有客户及其订单信息,包括无订单的客户：

SELECT customers.name, orders.order_date FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

子查询：嵌套在主查询中的SELECT语句，用于复杂条件过滤。 SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
窗口函数：对结果集进行分区计算，如ROW_NUMBER(), RANK(), LEAD()等。 SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank FROM employees;
CTE（Common Table Expression）：临时命名结果集，提高可读性。 WITH high_salary_employees AS (SELECT * FROM employees WHERE salary > 10000) SELECT * FROM high_salary_employees;
分页查询：使用LIMIT和OFFSET实现分页。 SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; 第3页，每页10条

索引优化：在常用查询条件（WHERE子句中的列）和排序列（ORDER BY）上创建索引。 CREATE INDEX idx_users_email ON users(email);
避免全表扫描：确保查询使用索引，避免SELECT *减少数据传输量。
使用EXPLAIN分析查询：通过EXPLAIN ANALYZE查看查询执行计划,优化慢查询。
批量操作：使用COPY命令批量导入数据,比逐条插入效率更高。

假设有一个电商数据库，包含customers（客户表）、orders（订单表）和order_items（订单明细表）,以下查询示例展示实际应用：

查询消费金额最高的前10名客户：
统计每个类目的商品销量：

SELECT p.category, COUNT(oi.order_id) AS total_sales FROM products p JOIN order_items oi ON p.id = oi.product_id GROUP BY p.category HAVING total_sales > 100;