上面简单介绍了一些查询语句，下面来进阶学习一下pgsql里面的单表查询

先批量插入一些数据方便后续的查询

INSERT INTO file_details (
    file_name, 
    uploader_ip, 
    tags, 
    metadata, 
    created_at
) VALUES 
-- 1. 匹配 ILIKE '%.PNG' 和 2026 年时间点
('Logo_Design.png', '192.168.1.5', ARRAY['素材', '封面', '设计'], '{"author": "Fengfeng", "dpi": 300, "color": "RGB"}', '2026-02-15 10:30:00'),

-- 2. 匹配数组重叠 (&&) 和包含 (@>)
('Tutorial_Video.mp4', '172.16.10.1', ARRAY['视频', '素材', '教程'], '{"author": "Zhang", "video": {"resolution": {"width": 1920, "height": 1080}}}', '2026-05-20 14:00:00'),

-- 3. 匹配特定的作者和文本转换
('Annual_Report.pdf', '172.16.20.5', ARRAY['文档', 'PDF'], '{"author": "Fengfeng", "pages": 45}', '2026-08-01 09:15:00'),

-- 4. 匹配 inet 范围 (172.16.0.0/16)
('Backup_Config.txt', '172.16.50.12', ARRAY['文档', '系统'], '{"priority": "high"}', '2025-12-30 23:59:59'),

-- 5. 匹配 JSONB 键值对存在性 (?)
('Thumbnail.PNG', '127.0.0.1', ARRAY['封面'], '{"author": "Li", "color": "CMYK", "dpi": 72}', '2026-01-10 11:00:00'),

-- 6. 聚合统计测试案例 (同一 IP 多次上传)
('Meeting_Notes.docx', '127.0.0.1', ARRAY['文档'], '{"author": "Fengfeng"}', '2026-03-05 16:20:00');

基础匹配和模糊查询

-- 查找所有 png 图片，忽略大小写
SELECT file_name, uploader_ip 
FROM file_details 
WHERE file_name ILIKE '%.PNG';

-- 查找 2026 年上传的文件
SELECT * FROM file_details 
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
SELECT * FROM file_details 
WHERE EXTRACT(YEAR FROM created_at) = 2026;

数组类型的深度检索

-- 匹配：查找标签中包含“文档”的所有文件
SELECT file_name, tags FROM file_details WHERE '文档' = ANY(tags);

-- 包含：查找同时包含“素材”和“封面”的文件 (@> 是包含操作符)
SELECT file_name, tags FROM file_details WHERE tags @> ARRAY['素材', '封面'];

-- 重叠：只要包含“PDF”或“视频”中的任意一个就行 (&& 是重叠操作符)
SELECT file_name, tags FROM file_details WHERE tags && ARRAY['PDF', '视频'];

jsonb的操作

注意：->>提取出来的是text，->提取出来的是json 对象。

-- 提取：查找作者是 "Fengfeng" 的文件
SELECT file_name, metadata->>'author' AS author 
FROM file_details 
WHERE metadata->>'author' = 'Fengfeng';

-- 如果取出来的字段还是一个json，就得用->取了
SELECT file_name, metadata->'video' AS author 
FROM file_details 
WHERE (metadata->'video'->'resolution'->>'width')::int = 1920;

-- 嵌套判断：查找 DPI 等于 300 的图片 (将 text 转为 int 进行比较)
SELECT file_name, metadata FROM file_details 
WHERE (metadata->>'dpi')::int = 300;

-- 键值对存在性判断：查询所有定义了 "color" 属性的文件
SELECT * FROM file_details WHERE metadata ? 'color';

网络地址inet的筛选

-- 精确匹配 IP
SELECT * FROM file_details WHERE uploader_ip = '127.0.0.1';

-- 范围匹配：查找属于 172.16.0.0/16 网段的所有上传记录
SELECT file_name, uploader_ip FROM file_details 
WHERE uploader_ip << '172.16.0.0/16';

排序和分页

-- 按创建时间倒序排列，取前 2 条
SELECT file_name, created_at 
FROM file_details 
ORDER BY created_at DESC 
LIMIT 2 OFFSET 0;

聚合统计

-- 统计每个 IP 上传了多少次，并计算每个 IP 上传文件包含的总标签数
-- 使用 unnest 把数组展开才能计数
SELECT 
    uploader_ip, 
    COUNT(*) AS upload_count,
    SUM(array_length(tags, 1)) AS total_tags
FROM file_details 
GROUP BY uploader_ip;

数据转换与格式化

-- 格式化输出：ID 前 8 位 + 文件名 + 简写日期
SELECT 
    left(detail_id::text, 8) AS short_id,
    file_name,
    to_char(created_at, 'YYYY-MM-DD') AS simple_date
FROM file_details;