PostgreSQL 数据库入门

-- 查询全部记录
select * from users;

-- 只查特定列
select name, city from users;

-- 条件筛选
select * from products where price > 100;

-- 排序 + 分页
select * from orders order by created_at desc limit 5;

-- 比较运算符：>, <, =, >=, <=, <>
select * from products where price > 200;

-- 逻辑组合：and / or
select * from users where city = '上海' and name = '张三';
select * from orders where status = 'pending' or status = 'cancelled';

-- 范围查询：between
select * from products where price between 50 and 300;

-- 模糊匹配：like（% 任意字符）
select * from products where name like '%手机%';

-- 注意：like 区分大小写，ILIKE 不区分

-- count     统计记录数
select count(*) from users;

-- sum       求和
select sum(total_amount) from orders;

-- avg       平均值
select avg(price) from products;

-- max / min 最大值 / 最小值
select max(price), min(price) from products;

-- group by  分组统计
select city, count(*) from users group by city;

-- group by + having（筛选分组后的结果）
select status, count(*)
from orders
group by status
having count(*) > 2;

-- group by + order by（按聚合结果排序）
select status, count(*)
from orders
group by status
order by count(*) desc;

-- where + group by + having 联合使用
select user_id, count(*)
from orders
where status = 'paid'
group by user_id
having count(*) >= 1;

-- inner join：只返回匹配的记录
select u.name, o.id, o.total_amount
from users u
inner join orders o on u.id = o.user_id;

-- left join：返回左表全部记录
select u.name, count(o.id)
from users u
left join orders o on u.id = o.user_id
group by u.name;

-- 统计每个用户的订单总额（无订单则显示 0）
select u.name, coalesce(sum(o.total_amount), 0)
from users u
left join orders o on u.id = o.user_id
group by u.name;

-- 哪些用户下过单（去重）
select distinct u.name
from users u
inner join orders o on u.id = o.user_id;

-- 哪些用户从未下过单
select u.name
from users u
left join orders o on u.id = o.user_id
where o.id is null;