自学内容网 自学内容网
自学内容网 > 正文

青少年编程与数学 02-007 PostgreSQL数据库应用 10课题、记录的操作

🕗 发布于 2025-01-21 11:37 数据库  青少年编程  postgresql  编程与数学  

青少年编程与数学 02-007 PostgreSQL数据库应用 10课题、记录的操作

课题摘要:本课题详细介绍了PostgreSQL中记录的操作，包括记录的基本概念、添加、删除、修改和查询记录的方法。记录是表中的一行数据，由多个字段组成，每个字段对应一列。通过INSERT语句添加记录，DELETE语句删除记录，UPDATE语句修改记录，SELECT语句查询记录。此外，还探讨了关联查询和分组查询，包括不同类型的JOIN操作和使用GROUP BY子句进行数据分组。最后，提供了分组查询优化的策略，如使用索引、减少数据量和优化聚合函数的使用。这些操作是数据库应用开发中的核心部分，确保了数据的一致性和完整性。

一、表的记录

在PostgreSQL中，表的记录（Record）是指表中的一行数据。每条记录代表了数据表中的一个数据项，它由该表所有列的值组成。换句话说，记录是表中所有列值的一个实例。

以下是关于表记录的一些关键点：

  1. 行和记录：

    • 在数据库术语中，“行”（Row）和“记录”（Record）通常可以互换使用，它们都指的是表中的单个数据项。

  2. 列和字段：

    • 每条记录由多个字段组成，每个字段对应表中的一列。例如，如果一个表有三列，那么每条记录将包含三个字段的值。

  3. 数据类型：

    • 每列都有特定的数据类型，这决定了可以存储在该列中的数据的类型。每条记录中的相应字段必须符合其列的数据类型。

  4. 唯一标识：

    • 通常，每条记录可以通过主键（Primary Key）来唯一标识。主键是一列或多列的组合，其值在表中是唯一的。

  5. 插入和检索：

    • 可以通过INSERT语句向表中插入新记录，通过SELECT语句检索表中的记录。

  6. 更新和删除：

    • 可以通过UPDATE语句修改表中的记录，通过DELETE语句删除表中的记录。

  7. 数据完整性：

    • 记录必须遵守表定义的约束，如非空约束（NOT NULL）、唯一约束（UNIQUE）、检查约束（CHECK）等。

示例

假设有一个名为employees的表，包含employee_id、first_name和last_name三列：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

向这个表中插入一条新记录：

INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'John', 'Doe');

这条记录由三个字段组成：employee_id为1，first_name为’John’，last_name为’Doe’。这条记录代表了employees表中的一个员工信息。

检索这条记录：

SELECT * FROM employees WHERE employee_id = 1;

这条SELECT语句将返回employee_id为1的记录，即我们刚刚插入的那条记录。

记录是数据库操作的基本单位，理解和管理记录是数据库应用开发的核心部分。

二、添加记录

在PostgreSQL中，为表添加记录通常使用INSERT语句。以下是如何为表添加记录的基本步骤和示例：

基本语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  • table_name：要插入记录的表的名称。
  • column1, column2, column3, ...：要插入值的列的名称。
  • value1, value2, value3, ...：对应列的值。

示例

假设我们有一个名为employees的表，它包含employee_id、first_name、last_name和hire_date四列：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);
插入单条记录
INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('John', 'Doe', '2023-01-15');

这条语句将向employees表中插入一条新记录，其中first_name为’John’，last_name为’Doe’，hire_date为’2023-01-15’。由于employee_id列是自增的（SERIAL类型），所以不需要在插入时指定它的值。

插入多条记录
INSERT INTO employees (first_name, last_name, hire_date)
VALUES
    ('Jane', 'Smith', '2023-02-01'),
    ('Alice', 'Johnson', '2023-03-12'),
    ('Bob', 'Brown', '2023-04-22');

这条语句将一次性向employees表中插入三条新记录。

使用默认值

如果表中的某些列定义了默认值，那么在插入记录时可以省略这些列：

INSERT INTO employees (first_name, last_name)
VALUES ('Michael', 'Williams');

在这个例子中，hire_date列将使用其默认值（如果有的话），或者如果是可空的，则为NULL。

返回插入的记录

PostgreSQL 10及以上版本支持在INSERT语句中使用RETURNING子句来返回插入的记录：

INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('Emily', 'Davis', '2023-05-20')
RETURNING *;

这条语句将插入一条新记录，并返回插入的整条记录的所有列。

通过这些基本的INSERT语句，你可以为PostgreSQL中的表添加记录。记得在插入数据时遵守表的约束，如非空列、唯一性约束等。

三、删除记录

在PostgreSQL中，删除表中的记录通常使用 DELETE 语句。以下是如何删除表中记录的基本步骤和示例：

基本语法

DELETE FROM table_name
WHERE condition;
  • table_name：要从中删除记录的表的名称。
  • condition：指定哪些记录需要被删除的条件。

示例

假设我们有一个名为 employees 的表：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);
删除特定的记录
DELETE FROM employees
WHERE employee_id = 1;

这条语句将删除 employees 表中 employee_id 为 1 的记录。

删除满足特定条件的记录
DELETE FROM employees
WHERE hire_date < '2023-01-01';

这条语句将删除 employees 表中所有 hire_date 在 2023年1月1日之前的记录。

级联删除

如果其他表中有引用 employees 表的外键，并且设置了级联删除，那么删除 employees 表中的记录将自动删除引用记录：

-- 假设存在一个 departments 表，其中 department_id 为外键引用 employees 表
DELETE FROM employees
WHERE department_id = 1
CASCADE;

在这个例子中，CASCADE 关键字指示 PostgreSQL 在删除 employees 表中 department_id 为 1 的记录时，也删除所有引用这些记录的其他表中的记录。

注意事项
  • 在执行 DELETE 操作之前，确保了解删除操作的影响，特别是在有外键约束的情况下。
  • 如果没有指定 WHERE 条件，DELETE 语句将删除表中的所有记录，这通常是不推荐的，除非你确实需要清空整个表。
  • 在执行删除操作之前，建议备份相关数据，以防意外删除重要信息。

使用 DELETE 语句时，务必谨慎，确保删除条件正确无误，以避免不必要的数据丢失。

四、修改记录

在PostgreSQL中，修改表中的记录通常使用 UPDATE 语句。以下是如何修改表中记录的基本步骤和示例：

基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name：要修改记录的表的名称。
  • column1, column2, ...：要更新值的列的名称。
  • value1, value2, ...：对应列的新值。
  • condition：指定哪些记录需要被更新的条件。

示例

假设我们有一个名为 employees 的表：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);
修改特定的记录
UPDATE employees
SET first_name = 'Jane', last_name = 'Doe'
WHERE employee_id = 1;

这条语句将 employees 表中 employee_id 为 1 的记录的 first_name 修改为 ‘Jane’，last_name 修改为 ‘Doe’。

修改满足特定条件的记录
UPDATE employees
SET hire_date = '2023-01-15'
WHERE first_name = 'John' AND last_name = 'Doe';

这条语句将 employees 表中 first_name 为 ‘John’ 且 last_name 为 ‘Doe’ 的所有记录的 hire_date 修改为 ‘2023-01-15’。

使用 RETURNING 子句

PostgreSQL 支持在 UPDATE 语句中使用 RETURNING 子句来返回被修改的记录：

UPDATE employees
SET hire_date = '2023-02-01'
WHERE employee_id = 2
RETURNING *;

这条语句将 employees 表中 employee_id 为 2 的记录的 hire_date 修改为 ‘2023-02-01’，并返回修改后的整条记录。

注意事项
  • 在执行 UPDATE 操作之前，确保了解更新操作的影响，特别是在有外键约束的情况下。
  • 如果没有指定 WHERE 条件，UPDATE 语句将更新表中的所有记录，这通常是不推荐的，除非你确实需要更新所有记录。
  • 在执行更新操作之前，建议备份相关数据，以防意外修改重要信息。

使用 UPDATE 语句时，务必谨慎，确保更新条件正确无误，以避免不必要的数据错误或丢失。

五、查询记录

在PostgreSQL中，查询表中的记录通常使用 SELECT 语句。以下是如何查询表中记录的基本步骤和示例：

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1, column2, ...
LIMIT offset, count;
  • column1, column2, ...：要选择的列的名称。使用 * 可以选择所有列。
  • table_name：要查询的表的名称。
  • WHERE：可选子句，用于指定查询的条件。
  • ORDER BY：可选子句，用于指定返回结果的排序方式。
  • LIMIT：可选子句，用于限制返回结果的数量。

示例

假设我们有一个名为 employees 的表：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);
查询所有记录
SELECT * FROM employees;

这条语句将返回 employees 表中的所有记录。

查询特定的列
SELECT first_name, last_name FROM employees;

这条语句将只返回 employees 表中的 first_name 和 last_name 列。

带条件的查询
SELECT * FROM employees WHERE employee_id = 1;

这条语句将返回 employees 表中 employee_id 为 1 的记录。

使用 LIKE 进行模式匹配
SELECT * FROM employees WHERE last_name LIKE 'D%';

这条语句将返回 employees 表中 last_name 以字母 ‘D’ 开头的所有记录。

使用 ORDER BY 进行排序
SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;

这条语句将按 last_name 升序、first_name 降序排序返回 employees 表中的所有记录。

使用 LIMIT 和 OFFSET 进行分页
SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 20;

这条语句将返回 employees 表中第3页（每页10条记录）的记录。

使用聚合函数
SELECT COUNT(*) FROM employees;

这条语句将返回 employees 表中的记录总数。

连接查询（JOIN）
SELECT e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

这条语句将连接 employees 表和 departments 表，返回所有员工及其对应部门名称的记录。

这些示例展示了如何使用 SELECT 语句在PostgreSQL中查询表中的记录。根据需要，可以组合使用不同的子句来实现复杂的查询。

六、关联查询

在PostgreSQL中，进行多个表的关联查询通常使用 JOIN 子句。JOIN 子句允许你指定如何将两个或多个表中的行结合起来查询。以下是不同类型的 JOIN 操作和示例：

基本语法

SELECT columns
FROM table1
JOIN_TYPE table2
ON join_condition
WHERE filter_condition
ORDER BY columns;
  • table1 和 table2：要查询的表的名称。
  • JOIN_TYPE：INNER、LEFT、RIGHT、FULL 或 CROSS 等，指定了 JOIN 的类型。
  • ON join_condition：连接条件，用于确定哪些行应该被组合。
  • WHERE filter_condition：可选子句，用于过滤结果集。
  • ORDER BY columns：可选子句，用于排序结果集。

示例

假设我们有两个表：orders 和 customers。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount NUMERIC
);
内连接（INNER JOIN）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

这条语句将返回所有有订单的顾客的名称、订单ID和订单日期。

左连接（LEFT JOIN）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

这条语句将返回所有顾客的名称，以及他们的订单ID和订单日期（如果有的话）。

右连接（RIGHT JOIN）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

这条语句将返回所有订单的ID和日期，以及对应的顾客名称（如果有的话）。

全连接（FULL JOIN）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

这条语句将返回所有顾客的名称和所有订单的ID和日期，无论是否有匹配的记录。

交叉连接（CROSS JOIN）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
CROSS JOIN orders o;

这条语句将返回所有可能的顾客和订单的组合，可能会产生大量的结果。

注意事项

  • JOIN 操作的性能可能会受到表的大小和连接条件的影响，确保连接列上有索引可以提高查询性能。
  • 使用 JOIN 时，确保理解不同类型的 JOIN 操作对结果集的影响。
  • 在复杂的查询中，可以使用多个 JOIN 子句来连接多个表。

通过使用 JOIN 子句，你可以灵活地在PostgreSQL中进行多个表的关联查询，以获取所需的数据。

七、分组查询

在PostgreSQL中，进行分组查询通常使用GROUP BY子句，它允许你根据一个或多个列的值对结果集进行分组，并对每个组应用聚合函数，如COUNT()、SUM()、AVG()、MAX()和MIN()等。以下是如何进行分组查询的基本步骤和示例：

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;
  • column1, column2, ...：要选择的列的名称。
  • table_name：要查询的表的名称。
  • WHERE condition：可选子句，用于在分组之前过滤行。
  • GROUP BY column1, column2, ...：子句，用于指定如何对结果集进行分组。
  • HAVING condition：可选子句，用于在分组之后过滤组。

示例

假设我们有一个名为sales的表，包含product_id、quantity和sale_date三列：

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
);
按产品分组并计算总销量
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

这条语句将返回每个产品的销售总量。

按日期分组并计算每天的总销售额
SELECT EXTRACT(YEAR FROM sale_date) AS sale_year, SUM(quantity) AS total_quantity
FROM sales
GROUP BY sale_year;

这条语句将返回每年销售的总数量。

使用HAVING子句过滤组
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

这条语句将返回销售总量超过100的产品的ID和销售总量。

按多个列分组
SELECT product_id, sale_date, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id, sale_date;

这条语句将返回每个产品在每个销售日期的销售总量。

组合WHERE和GROUP BY
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
WHERE sale_date >= '2023-01-01'
GROUP BY product_id;

这条语句将返回2023年1月1日之后每个产品的销售总量。

分组查询是数据分析中的一个重要工具，它可以帮助我们理解数据的分布和聚合特性。通过合理使用GROUP BY和HAVING子句，可以有效地对数据进行分组和聚合查询。

八、分组查询的优化

在PostgreSQL中进行分组查询的优化，可以从以下几个方面入手：

1. 使用合适的索引

为分组查询创建适当的索引可以显著提高查询性能。索引可以帮助数据库引擎更快地定位和访问所需的数据。例如，如果你经常需要按照某个字段进行分组，那么在这个字段上创建索引可以加快分组计算的速度。

2. 减少数据量

在WHERE子句中使用合理的条件筛选，只查询出真正需要的数据，而不是加载整个表的数据进行处理。这样可以减少查询的数据量，提高查询效率。

3. 分解复杂查询

将一个复杂的查询分解为多个简单的查询，在应用程序层面进行组合和处理。这样可以避免数据库在一次查询中处理过多的逻辑，提高查询性能。

4. 使用临时表

将复杂查询的中间结果存储在临时表中，然后在后续的查询中使用这个临时表，以提高查询性能。这样可以避免在每次查询时都重复执行筛选操作。

5. 优化聚合函数的使用

合理地调整查询语句，优化聚合函数的使用。例如，使用HAVING子句来筛选出符合条件的分组，这样数据库在进行分组聚合计算时，只会计算符合条件的分组，从而提高查询性能。

6. 调整数据库参数

根据数据库的硬件资源、负载情况和业务需求，调整一些数据库参数，比如work_mem和maintenance_work_mem。增加work_mem参数的值可以提高数据库在执行排序和哈希操作时的性能。

7. 使用窗口函数

在 PostgreSQL 中，可以使用窗口函数来计算每个分组中的最大值或其它统计值，而不需要使用子查询或连接来实现。这比传统的查询方式更高效。

8. 手动收集统计信息

使用 PostgreSQL 提供的 ANALYZE 命令手动收集统计信息，以确保查询优化器能够做出更好的查询计划决策。

通过上述优化策略的应用，可以有效地提高PostgreSQL中分组查询的性能，减少查询时间，提高系统的响应速度。


原文地址：https://blog.csdn.net/qq_40071585/article/details/145251548

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2025