PostgreSQL 实用 SQL 语句 30 个玩法

在数据库管理和操作中，PostgreSQL 以其强大的功能和灵活性备受青睐。以下为您介绍 30 个实用的 PostgreSQL SQL 语句玩法，助您更高效地处理数据。

1. 查询所有表名

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

2. 查看表结构

\d table_name

3. 条件查询

SELECT * FROM table_name WHERE column_name = 'value';

4. 排序结果

SELECT * FROM table_name ORDER BY column_name ASC/DESC;

5. 分页查询

SELECT * FROM table_name LIMIT offset, row_count;

6. 聚合函数

SELECT COUNT(*), SUM(column_name), AVG(column_name) FROM table_name;

7. 分组查询

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

8. 子查询

SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);

9. 连接查询（内连接）

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

10. 左连接

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

11. 右连接

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

12. 全外连接

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

13. 插入数据

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

14. 更新数据

UPDATE table_name SET column_name = 'new_value' WHERE condition;

15. 删除数据

DELETE FROM table_name WHERE condition;

16. 创建索引

CREATE INDEX index_name ON table_name (column_name);

17. 删除索引

DROP INDEX index_name;

18. 日期和时间操作

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

19. 字符串操作

SELECT UPPER(column_name), LOWER(column_name), SUBSTRING(column_name, start, length);

20. 数学运算

SELECT column_name + 10, column_name * 2 FROM table_name;

21. 空值处理

SELECT * FROM table_name WHERE column_name IS NULL/IS NOT NULL;

22. 模糊查询

SELECT * FROM table_name WHERE column_name LIKE '%value%';

23. 事务处理

BEGIN; 
-- 执行一系列操作
COMMIT;  -- 提交事务
ROLLBACK;  -- 回滚事务

24. 数据类型转换

SELECT CAST(column_name AS new_data_type);

25. 检查约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

26. 唯一性约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

27. 外键约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);

28. 视图创建

CREATE VIEW view_name AS SELECT column_name FROM table_name;

29. 存储过程创建

CREATE OR REPLACE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- 执行逻辑
    RETURN result;
END;
$$ LANGUAGE plpgsql;

30. 序列创建

CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;

掌握这些 PostgreSQL 的实用 SQL 语句玩法，将使您在数据处理和管理中更加得心应手，提升工作效率。不断探索和实践，您还能发现更多 PostgreSQL 的强大功能。