1
2
3
4
5
6

\q 退出psql
\l 列出所有数据库
\c database_name 连接到指定数据库
\d table_name  查看tables
\du 列出所有角色
\dt 列出所有表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

create database testdb;

create user user_name with password 'your_pwsd';

create schema my_schema;

create table user(
	id serial primary key,
	name text not null,
	email text not null,
	age integer check (age >= 0 and age <=150),
	is_active BOOLEAN default true,
	created_at timestamp default current_timestamp,
	updated_at timestamp default current_timestamp
);
# 在sales schema 中直接创建表
create TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(10,2)
);
# 创建一个带约束的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
PRIMARY KEY (user_id, role_id),
UNIQUE (student_id, course_code),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
ON UPDATE(一般不用)
1. CASCADE     → 父删子随删（适合从属强依赖数据）
2. SET NULL    → 父删子设空（字段必须可 NULL）
3. RESTRICT    → 父删若子存在则报错（默认，最安全）


-- 普通索引
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_lower_email ON users(LOWER(email));
-- 多列索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- 针对 JSONB 字段的索引（GIN）
CREATE INDEX idx_products_tags ON products USING GIN(tags); -- tags 是 jsonb

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

INSERT INTO users (id, name, email, age, created_at) VALUES (100, 'Bob', '[email protected]', 25, '2025-01-01');

INSERT INTO users (name, email) 
VALUES 
    ('Charlie', '[email protected]'),
    ('David', '[email protected]'),
    ('Eve', '[email protected]');

INSERT INTO user_stats (user_id, total_orders)
SELECT user_id, COUNT(*) 
FROM orders 
GROUP BY user_id;

alter tables users add column phone text;

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150);

UPDATE users SET phone = 'unknown' WHERE phone IS NULL;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

SELECT * FROM users;
SELECT name AS "姓名", age AS "年龄" FROM users;

SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE age <> 25;  -- 不等于（也可用 !=）

SELECT * FROM users 
WHERE (age < 25 OR age > 30) AND is_active = true;

SELECT * FROM users WHERE dept_id IN (1, 2);
SELECT * FROM users WHERE name NOT IN ('Alice', 'Bob');

# 模糊匹配（LIKE / ILIKE）
-- 名字以 'A' 开头
SELECT * FROM users WHERE name LIKE 'A%';
-- 名字包含 'ar'
SELECT * FROM users WHERE name LIKE '%ar%';
-- 不区分大小写（PostgreSQL 特有）
SELECT * FROM users WHERE name ILIKE 'alice'; -- 可匹配 'Alice', 'ALICE' 等
SELECT * FROM users WHERE name LIKE 'A__e'; -- 如 "Anna", "Aime"（4个字母，首A尾e）
ELECT * FROM users WHERE name ~ '^[AB]';

SELECT * FROM users WHERE dept_id IS NULL;
SELECT * FROM users WHERE dept_id IS NOT NULL;


# 查找属于 'Engineering' 部门的用户
SELECT * FROM users 
WHERE dept_id IN (
    SELECT id FROM departments WHERE name = 'Engineering'
);

# 排序
SELECT * FROM users ORDER BY age DESC;     -- 从大到小
SELECT * FROM users ORDER BY name ASC;     -- 从小到大（默认）
SELECT * FROM users ORDER BY dept_id, age; -- 多列排序

SELECT *
FROM users
ORDER BY name ASC, created_at DESC;

# 第一页，每页2条
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 0;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

UPDATE users SET age = 31, salary = 9500.00 WHERE name = 'Bob';

UPDATE users 
SET salary = salary * 1.1  -- 涨薪10%
WHERE dept_id = 1;         -- Engineering 部门

UPDATE users 
SET updated_at = NOW()     -- 记录更新时间
WHERE id = 1;

UPDATE users 
SET salary = salary * 1.05 
WHERE name = 'Alice'
RETURNING id, name, salary AS new_salary;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DELETE FROM users WHERE is_active = false;

DELETE FROM users 
WHERE name = 'Alice'
RETURNING id, name, email;

DELETE FROM users 
WHERE is_active = false 
ORDER BY created_at ASC  -- 先删最早注册的
LIMIT 1;

ALTER TABLE users DROP COLUMN salary;
drop table users;
-- 级联删除（同时删除依赖它的视图、外键等）
DROP TABLE users CASCADE;

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;
-- 删除约束
ALTER TABLE users DROP CONSTRAINT users_email_key;
-- 删除默认值
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 授权连接数据库
GRANT CONNECT ON DATABASE myapp TO myuser;

-- 授权使用 schema（通常是 public）
GRANT USAGE ON SCHEMA public TO myuser;

-- 授权对所有表的 SELECT（只读）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

-- 授权增删改查 + 序列使用（读写）
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- 授权未来新建的表也自动继承权限（重要！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO myuser;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 启用插件
CREATE EXTENSION hstore;

-- 创建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes HSTORE  -- 存储动态属性
);

-- 插入
INSERT INTO products (name, attributes) VALUES
    ('笔记本电脑', '品牌 => "Dell", 内存 => "16GB", 硬盘 => "512GB SSD"'),
    ('手机', '品牌 => "Apple", 型号 => "iPhone 15", 颜色 => "黑色"');

-- 查询
SELECT name, attributes->'品牌' as brand FROM products;  -- -> 返回 text
SELECT * FROM products WHERE attributes ? '颜色';       -- 是否包含某个 key
SELECT * FROM products WHERE attributes @> '"品牌"=>"Apple"'; -- 包含特定键值对

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 添加官方源
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

# 导入 GPG 密钥
curl -L https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

# 安装
sudo apt update
sudo apt install timescaledb-2-postgresql-15

# 自动配置优化参数
sudo timescaledb-tune

sudo systemctl restart postgresql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- 1. 创建普通表（必须包含时间字段）
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature FLOAT,
    humidity FLOAT
);

-- 2. 转换为 Hypertable
SELECT create_hypertable('sensor_data', 'time');

-- 可选：按 sensor_id 分区（空间分区）
SELECT create_hypertable('sensor_data', 'time', partitioning_column => 'sensor_id');

-- 插入数据（完全兼容 INSERT）
INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
VALUES 
    (NOW(), 1, 23.5, 45.0),
    (NOW() - INTERVAL '1 hour', 1, 22.8, 47.2);

-- 查询最近1小时数据（自动只查相关 Chunk，极快！）
SELECT * FROM sensor_data 
WHERE time > NOW() - INTERVAL '1 hour' 
  AND sensor_id = 1
ORDER BY time DESC;

-- 保留最近30天数据，自动删除旧数据
SELECT add_retention_policy('sensor_data', INTERVAL '30 days');