pg数据库语法有哪些常用命令和规则？

PostgreSQL（简称PG）是一款功能强大的开源对象关系型数据库管理系统，以其丰富的数据类型、高级查询功能和扩展性著称，掌握PG数据库语法是高效操作和管理数据的基础，本文将详细介绍PG的核心语法，包括数据定义、数据操作、数据查询、数据控制以及事务处理等方面。

数据定义语言（DDL）

DDL用于定义和管理数据库的结构，包括创建、修改和删除数据库对象。

• 创建数据库：使用CREATE DATABASE语句创建新数据库，例如CREATE DATABASE mydb;。
• 创建表：通过CREATE TABLE定义表结构，支持多种数据类型（如INTEGER、VARCHAR、TIMESTAMP等）和约束（如PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL）。

  CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50) NOT NULL UNIQUE,
      email VARCHAR(100) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

  其中SERIAL类型会自动创建自增序列，相当于INTEGER + DEFAULT nextval('序列名')。

• 修改表：使用ALTER TABLE添加、修改或删除列，例如ALTER TABLE users ADD COLUMN age INTEGER;或ALTER TABLE users DROP COLUMN age;。
• 删除对象：DROP TABLE users;删除表，DROP DATABASE mydb;删除数据库（需确保无连接）。

数据操作语言（DML）

DML用于操作表中的数据，包括插入、更新和删除记录。

• 插入数据：INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');，支持批量插入，

  INSERT INTO users (username, email) VALUES 
      ('jane_doe', 'jane@example.com'),
      ('bob_smith', 'bob@example.com');

• 更新数据：UPDATE users SET email = 'new_email@example.com' WHERE id = 1;，WHERE子句用于限定更新范围，避免全表更新。
• 删除数据：DELETE FROM users WHERE username = 'john_doe';，同样需谨慎使用WHERE,否则会删除所有数据。

数据查询语言（DQL）

DQL是PG语法的核心，用于检索和筛选数据。SELECT语句的基本结构为：

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 分组条件 ORDER BY 排序列 LIMIT 数量;

• 基础查询：SELECT * FROM users;返回所有列，SELECT username, email FROM users;返回指定列。
• 条件过滤：使用WHERE子句，支持比较运算符（、>、<）、逻辑运算符（AND、OR、NOT）以及IN、BETWEEN、LIKE等。SELECT * FROM users WHERE age BETWEEN 18 AND 30;或SELECT * FROM users WHERE email LIKE '%@example.com';。
• 聚合函数：COUNT()、SUM()、AVG()、MAX()、MIN()用于计算统计值，通常与GROUP BY配合使用。SELECT department, COUNT(*) FROM employees GROUP BY department;。
• 多表连接：通过JOIN（如INNER JOIN、LEFT JOIN、RIGHT JOIN）关联多表。

  SELECT users.username, orders.order_date 
  FROM users 
  INNER JOIN orders ON users.id = orders.user_id;

• 分页查询：LIMIT和OFFSET实现分页，例如SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;（从第21条记录开始返回10条）。

高级查询特性

PG支持复杂查询功能，如窗口函数、CTE（公共表表达式）等。

• 窗口函数：在分组内进行计算，例如SELECT username, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank FROM employees;。
• CTE：使用WITH子句创建临时结果集，提高查询可读性：

  WITH high_salary_employees AS (
      SELECT * FROM employees WHERE salary > 5000
  )
  SELECT * FROM high_salary_employees WHERE department = 'IT';

数据控制语言（DCL）与事务

DCL用于管理权限，事务确保数据一致性。

• 权限管理：GRANT SELECT ON users TO user1;授予查询权限，REVOKE INSERT ON users FROM user1;撤销插入权限。
• 事务处理：使用BEGIN;开始事务，COMMIT;提交事务，ROLLBACK;回滚事务。

  BEGIN;
  UPDATE users SET balance = balance  100 WHERE id = 1;
  UPDATE users SET balance = balance + 100 WHERE id = 2;
  COMMIT;

  若中间发生错误，执行ROLLBACK;可恢复数据到事务开始前的状态。

常用数据类型与函数

PG支持丰富的数据类型，包括基本类型（INT、TEXT、BOOLEAN）、数组类型（INT[]）、JSON/JSONB类型以及自定义类型，常用函数包括字符串函数（CONCAT、SUBSTR）、日期函数（NOW()、EXTRACT(YEAR FROM date)）和数学函数（ROUND、ABS）。

PG数据库语法涵盖了从基础的对象定义到复杂的数据操作，掌握DDL、DML、DQL和DCL是高效使用PG的关键，通过灵活运用条件查询、聚合函数、连接查询和事务管理，可以实现对数据的精准控制和高效处理,以下是一些常见问题的解答：

FAQs

1. 问：如何在PG中实现自增主键？
   答：PG推荐使用SERIAL或BIGSERIAL类型创建自增列，例如id SERIAL PRIMARY KEY，这会自动创建一个序列并关联到该列，也可手动创建序列并设置DEFAULT nextval('序列名')。

2. 问：PG中如何处理JSON数据？
   答：PG原生支持JSON和JSONB类型（JSONB为二进制存储，查询更高效），可通过>操作符提取JSON字段，例如SELECT data>'name' FROM users WHERE data>'age' > 25;，或使用jsonb_extract_path函数进行复杂查询。