PostgreSQL基础概念

1. 数据库和表

• 数据库(Database): 就像一个大仓库，里面可以放很多表
• 表(Table): 类似Excel表格，有行(记录)和列(字段)

2. SQL语言

PostgreSQL使用SQL(结构化查询语言)来操作数据。基本操作包括：

• 创建表:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 插入数据:

INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

• 查询数据:

SELECT * FROM users WHERE name = '张三';

• 更新数据:

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

• 删除数据:

DELETE FROM users WHERE id = 1;

PostgreSQL的特色功能

1. JSON支持

PostgreSQL可以原生存储和查询JSON数据：

-- 创建包含JSON字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

-- 插入JSON数据
INSERT INTO products (details) VALUES 
('{"name": "笔记本电脑", "price": 5999, "specs": {"cpu": "i7", "ram": "16GB"}}');

-- 查询JSON字段
SELECT details->>'name' AS product_name 
FROM products 
WHERE (details->>'price')::int > 5000;

2. 地理空间数据处理

PostGIS扩展让PostgreSQL能处理地理空间数据：

-- 启用PostGIS扩展
CREATE EXTENSION postgis;

-- 创建包含地理位置的表
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY(POINT)
);

-- 插入地理位置数据
INSERT INTO places (name, location) VALUES 
('公司', ST_GeographyFromText('POINT(116.404 39.915)'));

-- 查找5公里范围内的地点
SELECT name FROM places 
WHERE ST_DWithin(location, 
      ST_GeographyFromText('POINT(116.404 39.915)'), 
      5000);

3. 全文搜索

PostgreSQL内置强大的全文搜索功能：

-- 创建支持全文搜索的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector TSVECTOR
);

-- 创建触发器自动更新搜索向量
CREATE TRIGGER update_search_vector 
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION 
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- 插入数据
INSERT INTO articles (title, content) VALUES 
('PostgreSQL教程', '这是一篇关于PostgreSQL的入门教程');

-- 执行全文搜索
SELECT title FROM articles 
WHERE search_vector @@ to_tsquery('english', '教程');

如何开始使用PostgreSQL？

1. 安装PostgreSQL

• Windows: 下载官方安装程序
• macOS: 使用Homebrew brew install postgresql
• Linux: 使用包管理器，如Ubuntu的sudo apt install postgresql

2. 基本操作

安装完成后，你可以：

1. 连接到数据库：

psql -U username -d databasename

1. 创建数据库：

CREATE DATABASE mydb;

1. 创建用户：

CREATE USER myuser WITH PASSWORD 'mypassword';

1. 授予权限：

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;