pgsql数据库语句怎么写？常用语法有哪些？

PostgreSQL（简称pgsql）是一款功能强大的开源对象关系型数据库管理系统，以其丰富的数据类型、扩展性和高性能著称，掌握pgsql的常用语句是进行数据库操作的基础，本文将详细介绍pgsql数据库的核心语句，包括数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）以及数据控制语言（DCL），并结合实际场景说明其应用。

数据定义语言（DDL）

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

1. 创建数据库

   使用CREATE DATABASE语句创建新数据库，

   CREATE DATABASE mydb WITH ENCODING 'UTF8' TEMPLATE template0;

   其中ENCODING指定字符集为UTF8，TEMPLATE表示基于模板数据库创建。

2. 创建表

   CREATE TABLE用于定义表结构，支持多种数据类型和约束。

   CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) CHECK (email ~* '^[AZaz09._%]+@[AZaz09.]+[.][AZaz]+$'), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
   • SERIAL：自增整数类型，自动创建序列对象。
   • PRIMARY KEY：主键约束，确保唯一性。
   • CHECK：检查约束，验证email格式。
   • DEFAULT：设置默认值。

3. 修改表结构

   使用ALTER TABLE添加、修改或删除列。

   ALTER TABLE users ADD COLUMN age INTEGER; ALTER TABLE users ALTER COLUMN age SET NOT NULL; ALTER TABLE users DROP COLUMN age;

4. 删除对象

   DROP TABLE用于删除表及其关联数据，CASCADE选项会级联删除依赖对象：

   DROP TABLE users CASCADE;

数据操纵语言（DML）

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

1. 插入数据

   INSERT INTO用于添加新记录，支持单条或多条插入：

   INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com'); INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com'), ('charlie', 'charlie@example.com');

2. 更新数据

   UPDATE结合WHERE子句修改特定记录，未加WHERE会更新全表：

   UPDATE users SET email = 'alice_new@example.com' WHERE username = 'alice';

3. 删除数据

   DELETE FROM删除记录，同样需谨慎使用WHERE：

   DELETE FROM users WHERE username = 'bob';

数据查询语言（DQL）

DQL是pgsql中最核心的部分,用于检索和筛选数据。

1. 基础查询

   SELECT语句用于查询数据，表示所有列：

   SELECT * FROM users; SELECT username, email FROM users;

2. 条件过滤

   使用WHERE子句结合运算符（, >, <, LIKE, IN等）过滤数据：

   

   SELECT * FROM users WHERE created_at > '20250101'; SELECT * FROM users WHERE username LIKE 'a%';

3. 排序与分页

   ORDER BY对结果排序（ASC升序，DESC降序），LIMIT和OFFSET实现分页：

   SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 0;

4. 聚合函数与分组

   聚合函数（COUNT, SUM, AVG, MAX, MIN）结合GROUP BY进行统计：

   SELECT COUNT(*) FROM users; SELECT created_at::DATE, COUNT(*) FROM users GROUP BY created_at::DATE;

5. 多表连接查询

   通过JOIN关联多表，支持INNER JOIN（内连接）、LEFT JOIN（左连接）等：

   SELECT u.username, o.order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id;

6. 窗口函数

   窗口函数（如ROW_NUMBER(), RANK()）在分组内计算排名，无需减少结果行数：

   SELECT username, created_at, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rank FROM users;

数据控制语言（DCL）

DCL用于管理数据库权限,确保数据安全。

1. 授权

   GRANT将权限授予用户或角色，

   GRANT SELECT, INSERT ON users TO user_role;

2. 撤销权限

   REVOKE取消已授予的权限：

   REVOKE DELETE ON users FROM user_role;

常用函数与操作

pgsql支持丰富的内置函数,提升数据处理效率：

• 字符串函数：CONCAT(), SUBSTRING(), LENGTH()
• 日期函数：NOW(), EXTRACT(YEAR FROM date)
• 类型转换：CAST(value AS type)或value::type

查询用户注册年份：

SELECT username, created_at::YEAR AS registration_year FROM users;

事务处理

事务确保操作的原子性,使用BEGIN, COMMIT, ROLLBACK管理：

BEGIN; UPDATE users SET email = 'test@example.com' WHERE id = 1; COMMIT; 或 ROLLBACK; 回滚操作

相关问答FAQs

Q1: 如何在pgsql中实现自增主键？

A1: 使用SERIAL或BIGSERIAL类型定义自增列，

CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(100));

pgsql会自动创建序列对象,插入数据时无需指定id值，系统自动填充。

Q2: 如何优化pgsql查询性能？

A2: 可通过以下方式优化：

1. 创建索引：对常用查询条件的列创建索引，如CREATE INDEX idx_username ON users(username);。
2. 避免全表扫描：确保WHERE子句中使用索引列，避免SELECT *减少数据传输。
3. 使用EXPLAIN分析查询计划：通过EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';查看执行计划，定位性能瓶颈。
4. 分区表：对大表按时间或范围分区，提升查询效率。