PostgreSQL UPDATE 语句：修改数据

欢迎回到我们的PostgreSQL教程系列！在本课中，我们将重点讨论PostgreSQL中的 UPDATE 语句，这是修改数据库表中已有数据的基础。了解如何有效使用UPDATE语句可以让你精准地管理和维护数据。

UPDATE 语句简介

PostgreSQL 中的 UPDATE 语句用于修改表中的现有数据。您可以更新单行、多行或所有行，并且可以一次修改一个或多个列。

基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE: 指定要更新数据的表。
SET: 列出要修改的列及其新值。
WHERE: 定义条件以标识要更新的行。

在 PostgreSQL 表中更新数据

让我们使用我们熟悉的图书馆和电影数据库，通过一些实用示例来探索使用UPDATE语句的多种方法。

1. 更新单行

示例: 更新特定书籍的价格

UPDATE books
SET price = 8.99
WHERE title = '1984';

查看Schema初始化

解释:

'1984' 这本书的价格被更新为 $8.99 。
WHERE子句确保只有标题为'1984'的行被修改。

2. 更新多行

您可以更新符合某些条件的多行数据。

示例: 将所有 'Jane Austen' 著作的价格增加 $1

UPDATE books
SET price = price + 1
WHERE author = 'Jane Austen';

解释:

所有**'Jane Austen'** 著作的价格均上调 $1。

3. 更新多列

您可以在一条语句中更新多个列。

示例: 更新书籍的价格和页数

UPDATE books
SET price = 12.99, pages = 200
WHERE title = 'The Great Gatsby';

解释:

'The Great Gatsby' 这本书的价格更新为 $12.99，页数更新为 200。

4. 更新所有行

省略 WHERE 子句将更新表中的所有行。

示例: 为所有书籍应用 10％的折扣

UPDATE books
SET price = price * 0.9;

解释:

所有书籍的价格下降 10%。
更新所有行时要谨慎操作。

5. 在 UPDATE 中使用子查询

您可以使用子查询基于其他表中的数据来分配值。

示例: 将书籍的价格设置为所有书籍的平均价格

UPDATE books
SET price = sub.avg_price
FROM (
    SELECT AVG(price) AS avg_price
    FROM books
) AS sub;

解释:

每本书的价格都更新为所有书籍的平均价格。

6. 使用 CASE 表达式

CASE 表达式允许在UPDATE语句中使用条件逻辑。

示例: 根据价格应用不同的折扣

UPDATE books
SET price = CASE
    WHEN price > 10 THEN price * 0.85
    ELSE price * 0.95
END;

解释:

定价超过 $10 的书籍获得 15% 的折扣。
定价不超过 $10 的书籍获得 5% 的折扣。

7. 使用子查询更新

由于我们的 schema 不包含其他用于 join 的表，因此我们可以使用子查询执行复杂的更新操作。

示例: 增加那些有超过一本书的作者所写书籍的价格

UPDATE books
SET price = price + 2
WHERE author IN (
    SELECT author
    FROM books
    GROUP BY author
    HAVING COUNT(*) > 1
);

解释:

那些至少写了两本书的作者被识别出来。
这些作者的书籍价格增加 $2。

8. 使用LIMIT更新

PostgreSQL允许将 LIMIT 与 UPDATE 一起使用，以限制影响的行数。

示例: 按字母顺序更新前两本书的价格

UPDATE books
SET price = price - 1
WHERE ctid IN (
    SELECT ctid
    FROM books
    ORDER BY title
    LIMIT 2
);

解释:

按字母顺序排列的前两本书的价格降低了 $1。
ctid 是一个系统列，唯一标识表中的每一行。

9. 返回更新后的数据

RETURNING 子句允许您返回更新行中的数据。

示例: 更新页数并返回已更新的书籍数据

UPDATE books
SET pages = pages + 10
RETURNING title, pages;

解释:

所有书籍的页数增加 10 页。
返回更新后的 title 和 pages。

10. 使用 EXISTS 进行条件更新

使用 EXISTS 来依据相关数据的存在有条件地更新行。

示例: 仅在作者存在于 authors 表中时更新书籍

UPDATE books
SET price = price * 0.95
WHERE EXISTS (
    SELECT 1
    FROM authors
    WHERE authors.name = books.author
);

解释:

仅当书籍的作者在 authors 表中时，书籍才会打折。
在我们的 schema 中，所有 books 表中的作者都存在于 authors 表中，因此这将更新所有书籍。

总结

PostgreSQL 中的 UPDATE 语句是修改现有数据的强大工具。掌握其多种功能可以帮助您高效地管理和维护数据库中的数据。

关键要点:

使用 UPDATE ... SET 来修改数据。
始终使用 WHERE 子句来指定要更新的行。
利用子查询和 CASE 表达式处理复杂的更新。
使用 RETURNING 获取更新行的相关信息。
在更新所有行或影响多行时要谨慎。

Schema Initialization

To run the examples provided, ensure your PostgreSQL database is set up with the following schema:

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('Sense and Sensibility', 'Jane Austen', 5.99, 226),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

请随意使用此 schema 进行实验，以增强您对 PostgreSQL 中如何修改数据的理解。祝编码愉快！

Postgresql Select Postgresql Where